bin/rename_stocks.pl

   1 #!/usr/bin/perl
   2
   3 =head1 NAME
   4
   5 rename_stocks.pl - a script for renaming stocks
   6
   7 =head1 SYNOPSIS
   8
   9 rename_stocks.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 stocks in bulk using an xls file as input with two columns: the first column is the stock uniquename as it is in the database, and in the second column is the new stock uniquename. There is no header line. Both stock.name and stock.uniquename 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 =cut
  58
  59 use strict;
  60
  61 use Getopt::Std;
  62 use Data::Dumper;
  63 use Carp qw /croak/ ;
  64 use Pod::Usage;
  65 use Spreadsheet::ParseExcel;
  66 use Bio::Chado::Schema;
  67 use CXGN::DB::InsertDBH;
  68 use Try::Tiny;
  69 use SGN::Model::Cvterm;
  70
  71 our ($opt_H, $opt_D, $opt_i, $opt_s, $opt_t, $opt_n);
  72
  73 getopts('H:D:i:s:tn');
  74
  75 if (!$opt_H || !$opt_D || !$opt_i) {
  76     pod2usage(-verbose => 2, -message => "Must provide options -H (hostname), -D (database name), -i (input file)\n");
  77 }
  78
  79 my $dbhost = $opt_H;
  80 my $dbname = $opt_D;
  81 my $stock_type = $opt_s || "accession";
  82 my $parser   = Spreadsheet::ParseExcel->new();
  83 my $excel_obj = $parser->parse($opt_i);
  84
  85 my $dbh = CXGN::DB::InsertDBH->new({
  86         dbhost=>$dbhost,
  87         dbname=>$dbname,
  88         dbargs => {AutoCommit => 0, RaiseError => 1}
  89 });
  90
  91 my $schema= Bio::Chado::Schema->connect(  sub { $dbh->get_actual_dbh() } );
  92 $dbh->do('SET search_path TO public,sgn');
  93
  94 my $synonym_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'stock_synonym', 'stock_property')->cvterm_id();
  95
  96
  97 my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
  98 my ( $row_min, $row_max ) = $worksheet->row_range();
  99 my ( $col_min, $col_max ) = $worksheet->col_range();
 100
 101 my $stock_type_row = $schema->resultset("Cv::Cvterm")->find( { name => $stock_type });
 102
 103 if (! $stock_type_row) { die "The stock type $stock_type is not in the database."; }
 104
 105 my $stock_type_id = $stock_type_row->cvterm_id();
 106
 107 my $coderef = sub {
 108     for my $row ( 0 .. $row_max ) {
 109
 110         my $db_uniquename = $worksheet->get_cell($row,0)->value();
 111         my $new_uniquename = $worksheet->get_cell($row,1)->value();
 112
 113         print STDERR "processing row $row: $db_uniquename -> $new_uniquename\n";
 114
 115         my $old_stock = $schema->resultset('Stock::Stock')->find({ name => $db_uniquename, uniquename => $db_uniquename, type_id => $stock_type_id });
 116
 117         if (!$old_stock) {
 118             print STDERR "Warning! Stock with uniquename $db_uniquename was not found in the database.\n";
 119             next();
 120         }
 121
 122         my $new_stock = $old_stock->update({ name => $new_uniquename, uniquename => $new_uniquename});
 123         if (! $opt_n) {
 124             print STDERR "Storing old name ($db_uniquename) as synonym or stock with id ".$new_stock->stock_id()." and type_id $synonym_id...\n";
 125             my $synonym = { value => $db_uniquename,
 126                             type_id => $synonym_id,
 127                             stock_id => $new_stock->stock_id(),
 128             };
 129
 130             print STDERR "find_or_create...\n";
 131             $schema->resultset('Stock::Stockprop')->find_or_create($synonym);
 132             print STDERR "Done.\n";
 133         }
 134     }
 135 };
 136
 137 my $transaction_error;
 138 try {
 139     eval($coderef->());
 140 } catch {
 141     $transaction_error =  $_;
 142 };
 143
 144 if ($opt_t) {
 145     print STDERR "Not storing with test flag (-t). Rolling back.\n";
 146     $schema->txn_rollback();
 147 }
 148 elsif ($transaction_error) {
 149     print STDERR "Transaction error storing terms: $transaction_error. Rolling back.\n";
 150     $schema->txn_rollback();
 151 } else {
 152     print STDERR "Everything looks good. Committing.\n";
 153     $schema->txn_commit();
 154     print STDERR "Script Complete.\n";
 155 }