db/00103/FixTrialPluralTypes.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  FixTrialPluralTypes.pm
   7
   8 =head1 SYNOPSIS
   9
  10 mx-run FixTrialPluralTypes [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 This patch fixes the odd "Preliminary Yield Trials" and "Advanced Yeld Trials" and "Advanced Yield Trials" terms that should be singular terms
  17 This subclass uses L<Moose>. The parent class uses L<MooseX::Runnable>
  18
  19 =head1 AUTHOR
  20
  21
  22 =head1 COPYRIGHT & LICENSE
  23
  24 Copyright 2010 Boyce Thompson Institute for Plant Research
  25
  26 This program is free software; you can redistribute it and/or modify
  27 it under the same terms as Perl itself.
  28
  29 =cut
  30
  31
  32 package FixTrialPluralTypes;
  33
  34 use Moose;
  35 use Bio::Chado::Schema;
  36 use Try::Tiny;
  37 use SGN::Model::Cvterm;
  38 extends 'CXGN::Metadata::Dbpatch';
  39
  40
  41 has '+description' => ( default => <<'' );
  42 This patch fixes the odd "Preliminary Yield Trials" and "Advanced Yeld Trials" and "Advanced Yield Trials" terms that should be singular terms
  43
  44 has '+prereq' => (
  45         default => sub {
  46         [],
  47     },
  48
  49   );
  50
  51 sub patch {
  52     my $self=shift;
  53
  54     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  55
  56     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  57
  58     print STDOUT "\nExecuting the SQL commands.\n";
  59     my $schema = Bio::Chado::Schema->connect( sub { $self->dbh->clone } );
  60
  61
  62     print STDERR "INSERTING CV TERMS...\n";
  63
  64     my $correct_ayt_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'Advanced Yield Trial', 'project_type')->cvterm_id();
  65     my $correct_pyt_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'Preliminary Yield Trial', 'project_type')->cvterm_id();
  66
  67     my $previously_saved_wrong_ayt_projectprops_sql = "SELECT projectprop_id FROM projectprop join cvterm on(type_id=cvterm_id) where name='Advanced Yeld Trials' OR name='Advanced Yield Trials';";
  68     my $update_previously_saved_wrong_ayt_projectprops_sql = "UPDATE projectprop SET type_id = $correct_ayt_cvterm_id WHERE projectprop_id = ?;";
  69
  70     my $h1 = $schema->storage->dbh()->prepare($previously_saved_wrong_ayt_projectprops_sql);
  71     my $h_update = $schema->storage->dbh()->prepare($update_previously_saved_wrong_ayt_projectprops_sql);
  72
  73     $h1->execute();
  74     while (my ($projectprop_id) = $h1->fetchrow_array()) {
  75         $h_update->execute($projectprop_id);
  76     }
  77
  78     my $previously_saved_wrong_pyt_projectprops_sql = "SELECT projectprop_id FROM projectprop join cvterm on(type_id=cvterm_id) where name='Preliminary Yield Trials';";
  79     my $update_previously_saved_wrong_pyt_projectprops_sql = "UPDATE projectprop SET type_id = $correct_pyt_cvterm_id WHERE projectprop_id = ?;";
  80
  81     my $h2 = $schema->storage->dbh()->prepare($previously_saved_wrong_pyt_projectprops_sql);
  82     my $h2_update = $schema->storage->dbh()->prepare($update_previously_saved_wrong_pyt_projectprops_sql);
  83
  84     $h2->execute();
  85     while (my ($projectprop_id) = $h2->fetchrow_array()) {
  86         $h2_update->execute($projectprop_id);
  87     }
  88
  89     my $wrong_trial_types_rs = $schema->resultset("Cv::Cvterm")->search({
  90         name => {-in => ["Advanced Yeld Trials", "Advanced Yield Trials", "Preliminary Yield Trials"]}
  91     });
  92     while (my $r = $wrong_trial_types_rs->next){
  93         $r->delete;
  94     }
  95
  96     print "You're done!\n";
  97 }
  98
  99
 100 ####
 101 1; #
 102 ####