From ec88291d1b0c212777568bbed31f19156ee10dcd Mon Sep 17 00:00:00 2001 From: nickmorales Date: Tue, 30 Aug 2016 18:20:14 +0000 Subject: [PATCH] can download plant phenotype data in the same way as plot phenotype data --- db/00062/PlantEntriesInheritPlotProperties.pm | 166 ++++++++++++++++++++++++++ lib/CXGN/BreederSearch.pm | 14 +-- lib/CXGN/Trial.pm | 24 ++++ lib/CXGN/Trial/TrialCreate.pm | 8 ++ 4 files changed, 204 insertions(+), 8 deletions(-) create mode 100644 db/00062/PlantEntriesInheritPlotProperties.pm diff --git a/db/00062/PlantEntriesInheritPlotProperties.pm b/db/00062/PlantEntriesInheritPlotProperties.pm new file mode 100644 index 0000000000..65803985ad --- /dev/null +++ b/db/00062/PlantEntriesInheritPlotProperties.pm @@ -0,0 +1,166 @@ +#!/usr/bin/env perl + +=head1 NAME + +PlantEntriesInheritPlotProperties.pm + +=head1 SYNOPSIS + +mx-run PlantEntriesInheritPlotProperties [options] -H hostname -D dbname -u username [-F] + +this is a subclass of L +see the perldoc of parent class for more details. + +=head1 DESCRIPTION + +This patch updates the way that plant entries were being created and stored. Previously, plant entries did not inherit plot properties (block, replicate, plot number), as well as a relationship to the accession. Now they are created with these associations. + + +=head1 AUTHOR + +Nicolas Morales + +=head1 COPYRIGHT & LICENSE + +Copyright 2010 Boyce Thompson Institute for Plant Research + +This program is free software; you can redistribute it and/or modify +it under the same terms as Perl itself. + +=cut + + +package PlantEntriesInheritPlotProperties; + +use Moose; +use Bio::Chado::Schema; +use Try::Tiny; +extends 'CXGN::Metadata::Dbpatch'; +use SGN::Model::Cvterm; + +has '+description' => ( default => <<'' ); +This patch updates the way that plant entries were being created and stored. Previously, plant entries did not inherit plot properties (block, replicate, plot number), as well as a relationship to the accession. Now they are created with these associations. + +sub patch { + my $self=shift; + + print STDOUT "Executing the patch:\n " . $self->name . ".\n\nDescription:\n ". $self->description . ".\n\nExecuted by:\n " . $self->username . " ."; + + print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n"; + + print STDOUT "\nExecuting the SQL commands.\n"; + + my $chado_schema = Bio::Chado::Schema->connect( sub { $self->dbh->clone } ); + + my $coderef = sub { + + my $plant_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plant', 'stock_type')->cvterm_id(); + my $plot_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plot', 'stock_type')->cvterm_id(); + my $plant_of_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plant_of', 'stock_relationship')->cvterm_id(); + my $plot_of_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plot_of', 'stock_relationship')->cvterm_id(); + my $block_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'block', 'stock_property')->cvterm_id(); + my $plot_number_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plot number', 'stock_property')->cvterm_id(); + my $replicate_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'replicate', 'stock_property')->cvterm_id(); + my $field_layout_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'field_layout', 'experiment_type')->cvterm_id(); + + my $plots_of_plants = $chado_schema->resultset("Stock::StockRelationship")->search({ type_id=>$plant_of_cvterm_id }); + + while (my $p = $plots_of_plants->next() ) { + + #The plant inherits the properties of the plot. + if ($p->subject()->type_id() == $plot_cvterm && $p->object()->type_id() == $plant_cvterm) { + my $plot_props = $chado_schema->resultset("Stock::Stockprop")->search({ stock_id => $p->subject()->stock_id(), type_id => [$block_cvterm, $plot_number_cvterm, $replicate_cvterm] }); + while (my $prop = $plot_props->next() ) { + print $p->subject()->uniquename()." ".$prop->type_id()." ".$p->object()->uniquename()."\n"; + my $plantprop = $chado_schema->resultset("Stock::Stockprop")->find( { + stock_id => $p->object()->stock_id(), + type_id => $prop->type_id(), + }); + if ($plantprop) { + $plantprop->delete(); + } + $plantprop = $chado_schema->resultset("Stock::Stockprop")->create( { + stock_id => $p->object()->stock_id(), + type_id => $prop->type_id(), + value => $prop->value(), + }); + } + } + + my $plot_accession = $chado_schema->resultset("Stock::StockRelationship")->find({subject_id=>$p->subject()->stock_id(), type_id=>$plot_of_cvterm_id }); + if ($plot_accession) { + my $stock_relationship = $chado_schema->resultset("Stock::StockRelationship")->find_or_create({ + subject_id => $p->object()->stock_id(), + object_id => $plot_accession->object()->stock_id(), + type_id => $plant_of_cvterm_id, + }); + } + } + + + #For greenhouse trials + my $greenhouses = $chado_schema->resultset("Project::Projectprop")->search({ value=>'greenhouse' }); + while(my $g = $greenhouses->next() ) { + my $number = 1; + my $project_id = $g->project_id(); + my $field_layout_experiment = $chado_schema->resultset("Project::Project")->search( { 'me.project_id' => $project_id }, {select=>['nd_experiment.nd_experiment_id']})->search_related('nd_experiment_projects')->search_related('nd_experiment', { type_id => $field_layout_cvterm })->single(); + my $plant_nd_experiment_stocks = $chado_schema->resultset("NaturalDiversity::NdExperimentStock")->search({ + nd_experiment_id => $field_layout_experiment->nd_experiment_id(), + type_id => $field_layout_cvterm, + }); + while (my $s = $plant_nd_experiment_stocks->next() ) { + print STDERR $s->search_related('stock')->single()->uniquename()."\n"; + my $plantprop = $chado_schema->resultset("Stock::Stockprop")->find( { + stock_id => $s->stock_id(), + type_id => $block_cvterm, + }); + if ($plantprop) { + $plantprop->delete(); + } + $plantprop = $chado_schema->resultset("Stock::Stockprop")->create( { + stock_id => $s->stock_id(), + type_id => $block_cvterm, + value => 1, + }); + $plantprop = $chado_schema->resultset("Stock::Stockprop")->find( { + stock_id => $s->stock_id(), + type_id => $replicate_cvterm, + }); + if ($plantprop) { + $plantprop->delete(); + } + $plantprop = $chado_schema->resultset("Stock::Stockprop")->create( { + stock_id => $s->stock_id(), + type_id => $replicate_cvterm, + value => 1, + }); + $plantprop = $chado_schema->resultset("Stock::Stockprop")->find( { + stock_id => $s->stock_id(), + type_id => $plot_number_cvterm, + }); + if ($plantprop) { + $plantprop->delete(); + } + $plantprop = $chado_schema->resultset("Stock::Stockprop")->create( { + stock_id => $s->stock_id(), + type_id => $plot_number_cvterm, + value => $number, + }); + $number ++; + } + } + }; + + try { + $chado_schema->txn_do($coderef); + } catch { + die "Patch failed! Transaction exited." . $_ . "\n" ; + }; + + print "You're done!\n"; + +} + +#### +1; # +#### diff --git a/lib/CXGN/BreederSearch.pm b/lib/CXGN/BreederSearch.pm index 3de2256c30..e4c8b2f746 100644 --- a/lib/CXGN/BreederSearch.pm +++ b/lib/CXGN/BreederSearch.pm @@ -251,6 +251,7 @@ sub get_phenotype_info { my $block_number_type_id = $self -> get_stockprop_type_id("block"); my $year_type_id = $self->get_projectprop_type_id("project year"); my $plot_type_id = $self->get_stock_type_id("plot"); + my $plant_type_id = $self->get_stock_type_id("plant"); my $accession_type_id = $self->get_stock_type_id("accession"); my @where_clause = (); @@ -262,7 +263,7 @@ sub get_phenotype_info { if (@where_clause>0) { $where_clause .= $rep_type_id ? "WHERE (stockprop.type_id = $rep_type_id OR stockprop.type_id IS NULL) " : "WHERE stockprop.type_id IS NULL"; - $where_clause .= "AND plot.type_id = $plot_type_id AND stock.type_id = $accession_type_id"; + $where_clause .= "AND (plot.type_id = $plot_type_id OR plot.type_id = $plant_type_id) AND stock.type_id = $accession_type_id"; $where_clause .= $block_number_type_id ? " AND (block_number.type_id = $block_number_type_id OR block_number.type_id IS NULL)" : " AND block_number.type_id IS NULL"; $where_clause .= $year_type_id ? " AND projectprop.type_id = $year_type_id" :"" ; $where_clause .= " AND " . (join (" AND " , @where_clause)); @@ -404,6 +405,7 @@ sub get_extended_phenotype_info_matrix { }; $traits{$cvterm}++; } + #print STDERR Dumper \%plot_data; my @info = (); my $line = join "\t", qw | studyYear studyDbId studyName locationDbId locationName germplasmDbId germplasmName plotDbId plotName rep blockNumber |; @@ -421,14 +423,10 @@ sub get_extended_phenotype_info_matrix { my $count2 = 0; my @unique_plot_list = (); - my $previous_plot = ""; - foreach my $d (@$data) { - my $plot = $d->[6]; - if ($plot ne $previous_plot) { - push @unique_plot_list, $plot; - } - $previous_plot = $plot; + foreach my $d (keys \%plot_data) { + push @unique_plot_list, $d; } + #print STDERR Dumper \@unique_plot_list; foreach my $p (@unique_plot_list) { #$line = join "\t", map { $plot_data{$p}->{metadata}->{$_} } ( "year", "trial_name", "location", "accession", "plot", "rep", "block_number" ); diff --git a/lib/CXGN/Trial.pm b/lib/CXGN/Trial.pm index 8887bc4b3a..5a0d17dc2f 100644 --- a/lib/CXGN/Trial.pm +++ b/lib/CXGN/Trial.pm @@ -1293,8 +1293,12 @@ sub create_plant_entities { my $plant_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plant', 'stock_type')->cvterm_id(); my $plot_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plot', 'stock_type')->cvterm_id(); + my $plot_relationship_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plot_of', 'stock_relationship')->cvterm_id(); my $plant_relationship_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plant_of', 'stock_relationship')->cvterm_id(); my $plant_index_number_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plant_index_number', 'stock_property')->cvterm_id(); + my $block_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'block', 'stock_property')->cvterm_id(); + my $plot_number_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plot number', 'stock_property')->cvterm_id(); + my $replicate_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'replicate', 'stock_property')->cvterm_id(); my $has_plants_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'project_has_plant_entries', 'project_property')->cvterm_id(); my $field_layout_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'field_layout', 'experiment_type')->cvterm_id(); #my $plants_per_plot_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plants_per_plot', 'project_property')->cvterm_id(); @@ -1336,12 +1340,32 @@ sub create_plant_entities { value => $number, }); + #The plant inherits the properties of the plot. + my $plot_props = $chado_schema->resultset("Stock::Stockprop")->search({ stock_id => $parent_plot, type_id => [$block_cvterm, $plot_number_cvterm, $replicate_cvterm] }); + while (my $prop = $plot_props->next() ) { + #print STDERR $plant->uniquename()." ".$prop->type_id()."\n"; + $plantprop = $chado_schema->resultset("Stock::Stockprop")->find_or_create( { + stock_id => $plant->stock_id(), + type_id => $prop->type_id(), + value => $prop->value(), + }); + } + + #the plant has a relationship to the plot my $stock_relationship = $self->bcs_schema()->resultset("Stock::StockRelationship")->create({ subject_id => $parent_plot, object_id => $plant->stock_id(), type_id => $plant_relationship_cvterm, }); + #the plant has a relationship to the accession + my $plot_accession = $self->bcs_schema()->resultset("Stock::StockRelationship")->find({subject_id=>$parent_plot, type_id=>$plot_relationship_cvterm })->object(); + $stock_relationship = $self->bcs_schema()->resultset("Stock::StockRelationship")->create({ + subject_id => $plant->stock_id(), + object_id => $plot_accession->stock_id(), + type_id => $plant_relationship_cvterm, + }); + #link plant to project through nd_experiment. also add nd_genolocation_id of plot to nd_experiment for the plant my $field_layout_experiment = $chado_schema->resultset("Project::Project")->search( { 'me.project_id' => $self->get_trial_id() }, {select=>['nd_experiment.nd_experiment_id']})->search_related('nd_experiment_projects')->search_related('nd_experiment', { type_id => $field_layout_cvterm })->single(); my $plant_nd_experiment_stock = $chado_schema->resultset("NaturalDiversity::NdExperimentStock")->create({ diff --git a/lib/CXGN/Trial/TrialCreate.pm b/lib/CXGN/Trial/TrialCreate.pm index 563586f5b0..9cf950936f 100644 --- a/lib/CXGN/Trial/TrialCreate.pm +++ b/lib/CXGN/Trial/TrialCreate.pm @@ -30,6 +30,7 @@ use CXGN::BreedersToolbox::Projects; use CXGN::People::Person; use CXGN::Trial; use SGN::Model::Cvterm; +use Data::Dumper; has 'chado_schema' => ( is => 'rw', @@ -243,6 +244,8 @@ sub save_trial { my $stock_id_checked; my $organism_id_checked; + #print STDERR Dumper \%design; + my $number = 1; foreach my $key (sort { $a cmp $b} keys %design) { #print STDERR "Check 01: ".localtime(); @@ -381,6 +384,11 @@ sub save_trial { value => 1, }); + $plant->create_stockprops({'plot number' => $number}, {autocreate => 1}); + $plant->create_stockprops({'replicate' => 1}, {autocreate => 1} ); + $plant->create_stockprops({'block' => 1}, {autocreate => 1} ); + $number ++; + #create the stock_relationship of the accession with the plant, if it does not exist already if (!$stock_relationship_data{$stock_id_checked, $plot_of->cvterm_id(), $plant->stock_id()} ) { my $parent_stock = $chado_schema->resultset("Stock::StockRelationship")->create({ -- 2.11.4.GIT