db/00062/PlantEntriesInheritPlotProperties.pm

   1 #!/usr/bin/env perl
   2
   3 =head1 NAME
   4
   5 PlantEntriesInheritPlotProperties.pm
   6
   7 =head1 SYNOPSIS
   8
   9 mx-run PlantEntriesInheritPlotProperties [options] -H hostname -D dbname -u username [-F]
  10
  11 this is a subclass of L<CXGN::Metadata::Dbpatch>
  12 see the perldoc of parent class for more details.
  13
  14 =head1 DESCRIPTION
  15
  16 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.
  17
  18
  19 =head1 AUTHOR
  20
  21 Nicolas Morales<nm529@cornell.edu>
  22
  23 =head1 COPYRIGHT & LICENSE
  24
  25 Copyright 2010 Boyce Thompson Institute for Plant Research
  26
  27 This program is free software; you can redistribute it and/or modify
  28 it under the same terms as Perl itself.
  29
  30 =cut
  31
  32
  33 package PlantEntriesInheritPlotProperties;
  34
  35 use Moose;
  36 use Bio::Chado::Schema;
  37 use Try::Tiny;
  38 extends 'CXGN::Metadata::Dbpatch';
  39 use SGN::Model::Cvterm;
  40
  41 has '+description' => ( default => <<'' );
  42 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.
  43
  44 sub patch {
  45     my $self=shift;
  46
  47     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  48
  49     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  50
  51     print STDOUT "\nExecuting the SQL commands.\n";
  52
  53     my $chado_schema = Bio::Chado::Schema->connect( sub { $self->dbh->clone } );
  54
  55     my $coderef = sub {
  56
  57         my $plant_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plant', 'stock_type')->cvterm_id();
  58                 my $plot_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plot', 'stock_type')->cvterm_id();
  59         my $plant_of_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plant_of', 'stock_relationship')->cvterm_id();
  60         my $plot_of_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plot_of', 'stock_relationship')->cvterm_id();
  61         my $block_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'block', 'stock_property')->cvterm_id();
  62                 my $plot_number_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'plot number', 'stock_property')->cvterm_id();
  63                 my $replicate_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'replicate', 'stock_property')->cvterm_id();
  64         my $field_layout_cvterm = SGN::Model::Cvterm->get_cvterm_row($chado_schema, 'field_layout', 'experiment_type')->cvterm_id();
  65
  66         my $plots_of_plants = $chado_schema->resultset("Stock::StockRelationship")->search({ type_id=>$plant_of_cvterm_id });
  67
  68         while (my $p = $plots_of_plants->next() ) {
  69
  70             #The plant inherits the properties of the plot.
  71             if ($p->subject()->type_id() == $plot_cvterm && $p->object()->type_id() == $plant_cvterm) {
  72                 my $plot_props = $chado_schema->resultset("Stock::Stockprop")->search({ stock_id => $p->subject()->stock_id(), type_id => [$block_cvterm, $plot_number_cvterm, $replicate_cvterm] });
  73                 while (my $prop = $plot_props->next() ) {
  74                     print $p->subject()->uniquename()." ".$prop->type_id()." ".$p->object()->uniquename()."\n";
  75                     my $plantprop = $chado_schema->resultset("Stock::Stockprop")->find( {
  76                         stock_id => $p->object()->stock_id(),
  77                         type_id => $prop->type_id(),
  78                     });
  79                     if ($plantprop) {
  80                         $plantprop->delete();
  81                     }
  82                     $plantprop = $chado_schema->resultset("Stock::Stockprop")->create( {
  83                         stock_id => $p->object()->stock_id(),
  84                         type_id => $prop->type_id(),
  85                         value => $prop->value(),
  86                     });
  87                 }
  88             }
  89
  90             my $plot_accession = $chado_schema->resultset("Stock::StockRelationship")->find({subject_id=>$p->subject()->stock_id(), type_id=>$plot_of_cvterm_id });
  91             if ($plot_accession) {
  92                 my $stock_relationship = $chado_schema->resultset("Stock::StockRelationship")->find_or_create({
  93                     subject_id => $p->object()->stock_id(),
  94                     object_id => $plot_accession->object()->stock_id(),
  95                     type_id => $plant_of_cvterm_id,
  96                 });
  97             }
  98         }
  99
 100
 101         #For greenhouse trials
 102         my $greenhouses = $chado_schema->resultset("Project::Projectprop")->search({ value=>'greenhouse' });
 103         while(my $g = $greenhouses->next() ) {
 104             my $number = 1;
 105             my $project_id = $g->project_id();
 106             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();
 107             my $plant_nd_experiment_stocks = $chado_schema->resultset("NaturalDiversity::NdExperimentStock")->search({
 108                 nd_experiment_id => $field_layout_experiment->nd_experiment_id(),
 109                 type_id => $field_layout_cvterm,
 110             });
 111             while (my $s = $plant_nd_experiment_stocks->next() ) {
 112                 print STDERR $s->search_related('stock')->single()->uniquename()."\n";
 113                 my $plantprop = $chado_schema->resultset("Stock::Stockprop")->find( {
 114                     stock_id => $s->stock_id(),
 115                     type_id => $block_cvterm,
 116                 });
 117                 if ($plantprop) {
 118                     $plantprop->delete();
 119                 }
 120                 $plantprop = $chado_schema->resultset("Stock::Stockprop")->create( {
 121                     stock_id => $s->stock_id(),
 122                     type_id => $block_cvterm,
 123                     value => 1,
 124                 });
 125                 $plantprop = $chado_schema->resultset("Stock::Stockprop")->find( {
 126                     stock_id => $s->stock_id(),
 127                     type_id => $replicate_cvterm,
 128                 });
 129                 if ($plantprop) {
 130                     $plantprop->delete();
 131                 }
 132                 $plantprop = $chado_schema->resultset("Stock::Stockprop")->create( {
 133                     stock_id => $s->stock_id(),
 134                     type_id => $replicate_cvterm,
 135                     value => 1,
 136                 });
 137                 $plantprop = $chado_schema->resultset("Stock::Stockprop")->find( {
 138                     stock_id => $s->stock_id(),
 139                     type_id => $plot_number_cvterm,
 140                 });
 141                 if ($plantprop) {
 142                     $plantprop->delete();
 143                 }
 144                 $plantprop = $chado_schema->resultset("Stock::Stockprop")->create( {
 145                     stock_id => $s->stock_id(),
 146                     type_id => $plot_number_cvterm,
 147                     value => $number,
 148                 });
 149                         $number ++;
 150             }
 151         }
 152     };
 153
 154     try {
 155         $chado_schema->txn_do($coderef);
 156     } catch {
 157         die "Patch failed! Transaction exited." . $_ .  "\n" ;
 158     };
 159
 160     print "You're done!\n";
 161
 162 }
 163
 164 ####
 165 1; #
 166 ####