bin/rename_trials.pl

   1 #!/usr/bin/perl
   2
   3 =head1 NAME
   4
   5 rename_trials.pl - a script for renaming trials
   6
   7 =head1 SYNOPSIS
   8
   9 rename_trials.pl -H [dbhost] -D [dbname] -i [infile]
  10
  11 =head2 Command-line options
  12
  13 =over 5
  14
  15 =item -H
  16
  17 host name (required) e.g. "localhost"
  18
  19 =item -D
  20
  21 database name (required) e.g. "cxgn_cassava"
  22
  23 =item -i
  24
  25 path to infile (required)
  26
  27 =item -s
  28
  29 stock type (default: accession)
  30
  31 =item -n
  32
  33 don't store old name as a synonym
  34
  35 =item -t
  36
  37 test mode, do not commit changes.
  38
  39 =back
  40
  41 =head1 DESCRIPTION
  42
  43 This script renames trials in bulk using an xls and xlsx files as input with two columns: the first column is the old projectname as it is in the database, and in the second column is the new projectname. There is no header line. Both stock.name and stock.projectname fields will be changed to the new name.
  44
  45 #The oldname will be stored as a synonym unless option -n is given.
  46
  47 =head1 AUTHORS
  48
  49 Guillaume Bauchet (gjb99@cornell.edu)
  50
  51 Lukas Mueller <lam87@cornell.edu> (added -n option)
  52
  53 Adapted from a cvterm renaming script by:
  54
  55 Nicolas Morales (nm529@cornell.edu)
  56
  57 Srikanth Kumar K (sk2783@cornell.edu)
  58
  59 =cut
  60
  61 use strict;
  62
  63 use Getopt::Std;
  64 use Data::Dumper;
  65 use Carp qw /croak/ ;
  66 use Pod::Usage;
  67 use Spreadsheet::ParseExcel;
  68 use Spreadsheet::ParseXLSX;
  69 use Bio::Chado::Schema;
  70 use CXGN::DB::InsertDBH;
  71 use Try::Tiny;
  72 use SGN::Model::Cvterm;
  73
  74 our ($opt_H, $opt_D, $opt_i, $opt_s, $opt_t, $opt_n);
  75
  76 getopts('H:D:i:s:tn');
  77
  78 if (!$opt_H || !$opt_D || !$opt_i) {
  79     pod2usage(-verbose => 2, -message => "Must provide options -H (hostname), -D (database name), -i (input file)\n");
  80 }
  81
  82 my $dbhost = $opt_H;
  83 my $dbname = $opt_D;
  84 my $stock_type = $opt_s || "accession";
  85
  86 # Match a dot, extension .xls / .xlsx
  87 my ($extension) = $opt_i =~ /(\.[^.]+)$/;
  88 my $parser;
  89
  90 if ($extension eq '.xlsx') {
  91         $parser = Spreadsheet::ParseXLSX->new();
  92 }
  93 else {
  94         $parser = Spreadsheet::ParseExcel->new();
  95 }
  96
  97 my $excel_obj = $parser->parse($opt_i);
  98
  99 my $dbh = CXGN::DB::InsertDBH->new({
 100         dbhost=>$dbhost,
 101         dbname=>$dbname,
 102         dbargs => {AutoCommit => 0, RaiseError => 1}
 103 });
 104
 105 my $schema= Bio::Chado::Schema->connect(  sub { $dbh->get_actual_dbh() } );
 106 $dbh->do('SET search_path TO public,sgn');
 107
 108 my $synonym_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'stock_synonym', 'stock_property')->cvterm_id();
 109
 110
 111 my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
 112 my ( $row_min, $row_max ) = $worksheet->row_range();
 113 my ( $col_min, $col_max ) = $worksheet->col_range();
 114
 115 my $coderef = sub {
 116     for my $row ( 0 .. $row_max ) {
 117
 118         my $db_projectname = $worksheet->get_cell($row,0)->value();
 119         my $new_projectname = $worksheet->get_cell($row,1)->value();
 120
 121         print STDERR "processing row $row: $db_projectname -> $new_projectname\n";
 122
 123         my $old_project = $schema->resultset('Project::Project')->find({ name => $db_projectname, projectname => $db_projectname });
 124
 125         if (!$old_project) {
 126             print STDERR "Warning! Stock with projectname $db_projectname was not found in the database.\n";
 127             next();
 128         }
 129
 130         my $new_project = $old_project->update({ name => $new_projectname });
 131     }
 132 };
 133
 134 my $transaction_error;
 135 try {
 136     eval($coderef->());
 137 } catch {
 138     $transaction_error =  $_;
 139 };
 140
 141 if ($opt_t) {
 142     print STDERR "Not storing with test flag (-t). Rolling back.\n";
 143     $schema->txn_rollback();
 144 }
 145 elsif ($transaction_error) {
 146     print STDERR "Transaction error storing terms: $transaction_error. Rolling back.\n";
 147     $schema->txn_rollback();
 148 } else {
 149     print STDERR "Everything looks good. Committing.\n";
 150     $schema->txn_commit();
 151     print STDERR "Script Complete.\n";
 152 }