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 and xlsx files 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 Spreadsheet::ParseXLSX;
  67 use Bio::Chado::Schema;
  68 use CXGN::DB::InsertDBH;
  69 use Try::Tiny;
  70 use SGN::Model::Cvterm;
  71
  72 our ($opt_H, $opt_D, $opt_i, $opt_s, $opt_t, $opt_n);
  73
  74 getopts('H:D:i:s:tn');
  75
  76 if (!$opt_H || !$opt_D || !$opt_i) {
  77     pod2usage(-verbose => 2, -message => "Must provide options -H (hostname), -D (database name), -i (input file)\n");
  78 }
  79
  80 my $dbhost = $opt_H;
  81 my $dbname = $opt_D;
  82 my $stock_type = $opt_s || "accession";
  83
  84 # Match a dot, extension .xls / .xlsx
  85 my ($extension) = $opt_i =~ /(\.[^.]+)$/;
  86 my $parser;
  87
  88 if ($extension eq '.xlsx') {
  89         $parser = Spreadsheet::ParseXLSX->new();
  90 }
  91 else {
  92         $parser = Spreadsheet::ParseExcel->new();
  93 }
  94
  95 my $excel_obj = $parser->parse($opt_i);
  96
  97 my $dbh = CXGN::DB::InsertDBH->new({
  98         dbhost=>$dbhost,
  99         dbname=>$dbname,
 100         dbargs => {AutoCommit => 0, RaiseError => 1}
 101 });
 102
 103 my $schema= Bio::Chado::Schema->connect(  sub { $dbh->get_actual_dbh() } );
 104 $dbh->do('SET search_path TO public,sgn');
 105
 106 my $synonym_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'stock_synonym', 'stock_property')->cvterm_id();
 107
 108
 109 my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
 110 my ( $row_min, $row_max ) = $worksheet->row_range();
 111 my ( $col_min, $col_max ) = $worksheet->col_range();
 112
 113 my $stock_type_row = $schema->resultset("Cv::Cvterm")->find( { name => $stock_type });
 114
 115 if (! $stock_type_row) { die "The stock type $stock_type is not in the database."; }
 116
 117 my $stock_type_id = $stock_type_row->cvterm_id();
 118
 119 my $coderef = sub {
 120     for my $row ( 0 .. $row_max ) {
 121
 122         my $db_uniquename = $worksheet->get_cell($row,0)->value();
 123         my $new_uniquename = $worksheet->get_cell($row,1)->value();
 124
 125         print STDERR "processing row $row: $db_uniquename -> $new_uniquename\n";
 126
 127         my $old_stock = $schema->resultset('Stock::Stock')->find({ name => $db_uniquename, uniquename => $db_uniquename, type_id => $stock_type_id });
 128
 129         if (!$old_stock) {
 130             print STDERR "Warning! Stock with uniquename $db_uniquename was not found in the database.\n";
 131             next();
 132         }
 133
 134         my $new_stock = $old_stock->update({ name => $new_uniquename, uniquename => $new_uniquename});
 135         if (! $opt_n) {
 136             print STDERR "Storing old name ($db_uniquename) as synonym or stock with id ".$new_stock->stock_id()." and type_id $synonym_id...\n";
 137             my $synonym = { value => $db_uniquename,
 138                             type_id => $synonym_id,
 139                             stock_id => $new_stock->stock_id(),
 140             };
 141
 142             print STDERR "find_or_create...\n";
 143             $schema->resultset('Stock::Stockprop')->find_or_create($synonym);
 144             print STDERR "Done.\n";
 145         }
 146     }
 147 };
 148
 149 my $transaction_error;
 150 try {
 151     eval($coderef->());
 152 } catch {
 153     $transaction_error =  $_;
 154 };
 155
 156 if ($opt_t) {
 157     print STDERR "Not storing with test flag (-t). Rolling back.\n";
 158     $schema->txn_rollback();
 159 }
 160 elsif ($transaction_error) {
 161     print STDERR "Transaction error storing terms: $transaction_error. Rolling back.\n";
 162     $schema->txn_rollback();
 163 } else {
 164     print STDERR "Everything looks good. Committing.\n";
 165     $schema->txn_commit();
 166     print STDERR "Script Complete.\n";
 167 }