db/00051/FixRedundantCvterms.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  FixRedundantCvterms.pm
   7
   8 =head1 SYNOPSIS
   9
  10 mx-run ThisPackageName [options] -H hostname -D dbname -u username [-F]
  11
  12 this is a subclass of L<CXGN::Metadata::Dbpatch>
  13 see the perldoc of parent class for more details.
  14
  15 =head1 DESCRIPTION
  16
  17 This patch cleans up system cvterms terms that are
  18 1. not being used
  19 2. confusing
  20 3. are used in the wrong context (e.g. stock_relationship cvterm used in the nd_experimentprop table)
  21
  22 This subclass uses L<Moose>. The parent class uses L<MooseX::Runnable>
  23
  24 =head1 AUTHOR
  25
  26  Naama Menda<nm249@cornell.edu>
  27
  28 =head1 COPYRIGHT & LICENSE
  29
  30 Copyright 2010 Boyce Thompson Institute for Plant Research
  31
  32 This program is free software; you can redistribute it and/or modify
  33 it under the same terms as Perl itself.
  34
  35 =cut
  36
  37
  38 package FixRedundantCvterms;
  39
  40 use Moose;
  41 use Bio::Chado::Schema;
  42 use Try::Tiny;
  43
  44 extends 'CXGN::Metadata::Dbpatch';
  45
  46
  47 has '+description' => ( default => <<'' );
  48 This patch will do the following:
  49 1. Set cv_id = nd_experiment_property for the cvterm cross_name
  50 2. Update the type_id of nd_experiment rows to cross_experiment where the type_id = cross , and then obsolete this stock_relationship cross cvterm
  51 3. Create a new term called cross_relationship cv= stock_relationship to be used
  52 in the stock_relationship table instead of the term cross_name which now
  53 has a nd_experiment_property cv and is used as type_id in nd_experimentprop
  54 this is important for making CVterms uniform and less room for errors when using these
  55
  56 has '+prereq' => (
  57     default => sub {
  58         [],
  59     },
  60   );
  61
  62 sub patch {
  63     my $self=shift;
  64
  65     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  66
  67     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  68
  69     print STDOUT "\nExecuting the SQL commands.\n";
  70     my $schema = Bio::Chado::Schema->connect( sub { $self->dbh->clone } );
  71
  72     my $coderef = sub {
  73         my $cvterm_rs = $schema->resultset("Cv::Cvterm");
  74         my $cv_rs = $schema->resultset("Cv::Cv");
  75
  76         #############
  77
  78         #############1
  79         my $nd_experiment_property_cv = $cv_rs->find_or_create( { name => 'nd_experiment_property' } ) ;
  80
  81         my $cross_name_cvterm = $cvterm_rs->find(
  82             {
  83                 name => 'cross_name' ,
  84             });
  85         if ($cross_name_cvterm) {
  86             print "UPDATING cv_id of cvterm cross_name to nd_experiment_property\n";
  87             $cross_name_cvterm->update( { cv_id => $nd_experiment_property_cv->cv_id } ) ;
  88         }
  89         ###############2
  90
  91         my $cross_experiment_cvterm = $cvterm_rs->create_with(
  92             {
  93                 name => 'cross_experiment',
  94                 cv   => 'experiment_type',
  95             } ) ;
  96
  97         my $nd_experiment_rs = $schema->resultset("NaturalDiversity::NdExperiment")->search(
  98             {
  99                 'type.name' => 'cross'
 100             },
 101             {
 102                 join => 'type',
 103             } );
 104         if ( $nd_experiment_rs->count ) {
 105             print "UPDATING nd_experiment with type_id = cross to type_id = cross_experiment\n";
 106             $nd_experiment_rs->update( { type_id => $cross_experiment_cvterm->cvterm_id }  );
 107         }
 108         ### OBSOLETE name of cross cvterm cv = stock_relationship
 109         my $cross_cvterm = $cvterm_rs->find(
 110             {
 111                 'me.name' => 'cross',
 112                 'cv.name' => 'stock_relationship',
 113             },
 114             {
 115                 join => 'cv' ,
 116             } );
 117         if ( $cross_cvterm ) {
 118             print "UPDATING term cross cv= stock_relationship to name = OBSOLETE_cross. No one should use this term. There is a cross term with stock_type cv\n";
 119             $cross_cvterm->update( { name => 'OBSOLETE_cross' } ) ;
 120         }
 121         ##################3
 122
 123         my $cross_relationship_cvterm = $cvterm_rs->create_with(
 124             {
 125                 name => 'cross_relationship' ,
 126                 cv   => 'stock_relationship',
 127             } ) ;
 128
 129         my $stock_relationship_rs = $schema->resultset("Stock::StockRelationship")->search(
 130             {
 131                 'type.name' => 'cross_name',
 132             } ,
 133             { join => 'type', }
 134             );
 135         if ( $stock_relationship_rs->count ) {
 136             print "UPDATING stock_relationships with type = cross_name to new cvterm = cross_relationship. You should not use the term cross in stock_relationship. It should be only a stock.type \n";
 137             $stock_relationship_rs->update( { type_id => $cross_relationship_cvterm->cvterm_id } );
 138         }
 139
 140         ###################
 141         if ($self->trial) {
 142             print "Trial mode! Rolling back transaction\n\n";
 143             $schema->txn_rollback;
 144             return 0;
 145         }
 146         return 1;
 147     };
 148
 149
 150     try {
 151         $schema->txn_do($coderef);
 152
 153     } catch {
 154         die "Load failed! " . $_ .  "\n" ;
 155     };
 156
 157
 158     print "You're done!\n";
 159 }
 160
 161
 162
 163 ####
 164 1; #
 165 ####