db/00047/UpdateMembersOf.pm

   1
   2 #!/usr/bin/env perl
   3
   4
   5 =head1 NAME
   6
   7  UpdateMembersOf
   8
   9 =head1 SYNOPSIS
  10
  11 mx-run ThisPackageName [options] -H hostname -D dbname -u username [-F]
  12
  13 this is a subclass of L<CXGN::Metadata::Dbpatch>
  14 see the perldoc of parent class for more details.
  15
  16 =head1 DESCRIPTION
  17
  18 This patch moves all stock_relationship rows with type_id of  "members of" to a "member_of" cvterm, cv , and name dbxref.
  19 This is done to eliminate duplicates of members of cvterms loaded previously in the different databases from the load_genotypes.pl loading script.
  20
  21
  22
  23 This subclass uses L<Moose>. The parent class uses L<MooseX::Runnable>
  24
  25 =head1 AUTHOR
  26
  27  Guillaume Bauchet<gjb99@cornell.edu>
  28
  29 =head1 COPYRIGHT & LICENSE
  30
  31 Copyright 2010 Boyce Thompson Institute for Plant Research
  32
  33 This program is free software; you can redistribute it and/or modify
  34 it under the same terms as Perl itself.
  35
  36 =cut
  37
  38
  39 package UpdateMembersOf;
  40
  41 use Moose;
  42 use Bio::Chado::Schema;
  43 use Try::Tiny;
  44
  45 extends 'CXGN::Metadata::Dbpatch';
  46
  47
  48 has '+description' => ( default => <<'' );
  49 This patch will find_or_create a member_of cvterm
  50 with cv of stock_relationship
  51 Then all stock_relationship rows  of type_id matching the word members of will be associated with the member_of cvterm
  52 this is important for making stock_relationship member_of term unified across the different databases and eliminating redundancy
  53
  54 has '+prereq' => (
  55     default => sub {
  56         [ 'UnderlineCvNames' ],
  57     },
  58   );
  59
  60 sub patch {
  61     my $self=shift;
  62
  63     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  64
  65     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  66
  67     print STDOUT "\nExecuting the SQL commands.\n";
  68     my $schema = Bio::Chado::Schema->connect( sub { $self->dbh->clone } );
  69
  70
  71 #find or create cvterm with members of name and cv
  72 #make sure it has db = null
  73 ##there might be an existing dbxref with members of = autocreated:members of
  74 #
  75     my $coderef = sub {
  76
  77         my $member_of_cvterm = $schema->resultset("Cv::Cvterm")->create_with(
  78     { name   => 'member_of',
  79       cv     => 'stock_relationship',
  80     });
  81
  82         my $member_of_cvterm_id = $member_of_cvterm->cvterm_id;
  83         print "***member_of_cvterm_id is $member_of_cvterm_id \n";
  84
  85
  86 #find all stock_relationship rows that have a type_id  ilike %members of%' and change it to the
  87 #stock_relationship member of cvterm
  88 # delete the old cvterm 'members of' that was created by the load_genotypes.pl script
  89
  90         my $stock_rel_rs = $schema->resultset("Stock::StockRelationship")->search(
  91             {
  92                 'type.name' => { ilike => 'members of%' },
  93             },
  94             {
  95                 join => 'type'
  96             }
  97             );
  98
  99         print "** found " . $stock_rel_rs->count . " stock_relationship rows \n\n";
 100         print "**Changing type_id to cvterm_id of member_of, cv= stock_relationship \n";
 101         $stock_rel_rs->update( { type_id => $member_of_cvterm_id});
 102
 103         my $old_cvterm_rs = $schema->resultset("Cv::Cvterm")->search(
 104             {
 105                 'me.name' => 'members of',
 106                 'cv.name' => 'stock_relationship' ,
 107             },
 108             {
 109                 join => 'cv'
 110             }
 111             );
 112         print "Found . " . $old_cvterm_rs->count . " cvterm(s) with name = 'members of' \n\n";
 113         if ($old_cvterm_rs->count ) {
 114             print "Found cvterm_id for term 'members of' = " . $old_cvterm_rs->first->cvterm_id . "\n DELETING...\n";
 115             $old_cvterm_rs->delete();
 116         } else {
 117             print "nothing to delete here \n\n";
 118         }
 119
 120         if ($self->trial) {
 121             print "Trial mode! Rolling back transaction\n\n";
 122             $schema->txn_rollback;
 123             return 0;
 124         }
 125         return 1;
 126     };
 127
 128
 129     try {
 130         $schema->txn_do($coderef);
 131
 132     } catch {
 133         die "Load failed! " . $_ .  "\n" ;
 134     };
 135
 136
 137     print "You're done!\n";
 138 }
 139
 140
 141
 142
 143
 144 ####
 145 1; #
 146 ####