bin/rename_stocks.pl

   1 #!/usr/bin/perl
   2
   3 =head1
   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 =head1 COMMAND-LINE OPTIONS
  12   ARGUMENTS
  13  -H host name (required) e.g. "localhost"
  14  -D database name (required) e.g. "cxgn_cassava"
  15  -i path to infile (required)
  16  -s stock type (default: accession)
  17  -t test mode, do not commit changes.
  18
  19 =head1 DESCRIPTION
  20
  21 This script rename stocks in bulk. The infile provided has two columns, in 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 on the infile and the infile is .xls. The stock.name field is untouched.
  22
  23 =head1 AUTHOR
  24
  25  Guillaume Bauchet (gjb99@cornell.edu)
  26
  27  Adapted from a cvterm renaming script by:
  28  Nicolas Morales (nm529@cornell.edu)
  29
  30 =cut
  31
  32 use strict;
  33
  34 use Getopt::Std;
  35 use Data::Dumper;
  36 use Carp qw /croak/ ;
  37 use Pod::Usage;
  38 use Spreadsheet::ParseExcel;
  39 use Bio::Chado::Schema;
  40 use CXGN::DB::InsertDBH;
  41 use Try::Tiny;
  42
  43 our ($opt_H, $opt_D, $opt_i, $opt_s, $opt_t);
  44
  45 getopts('H:D:i:s:t');
  46
  47 if (!$opt_H || !$opt_D || !$opt_i) {
  48     pod2usage(-verbose => 2, -message => "Must provide options -H (hostname), -D (database name), -i (input file)\n");
  49 }
  50
  51 my $dbhost = $opt_H;
  52 my $dbname = $opt_D;
  53 my $stock_type = $opt_s || "accession";
  54 my $parser   = Spreadsheet::ParseExcel->new();
  55 my $excel_obj = $parser->parse($opt_i);
  56
  57 my $dbh = CXGN::DB::InsertDBH->new({
  58         dbhost=>$dbhost,
  59         dbname=>$dbname,
  60         dbargs => {AutoCommit => 0, RaiseError => 1}
  61 });
  62
  63 my $schema= Bio::Chado::Schema->connect(  sub { $dbh->get_actual_dbh() } );
  64 $dbh->do('SET search_path TO public,sgn');
  65
  66
  67 my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
  68 my ( $row_min, $row_max ) = $worksheet->row_range();
  69 my ( $col_min, $col_max ) = $worksheet->col_range();
  70
  71 my $stock_type_row = $schema->resultset("Cv::Cvterm")->find( { name => $stock_type });
  72
  73 if (! $stock_type_row) { die "The stock type $stock_type is not in the database."; }
  74
  75 my $stock_type_id = $stock_type_row->cvterm_id();
  76
  77 my $coderef = sub {
  78     for my $row ( 0 .. $row_max ) {
  79
  80         my $db_uniquename = $worksheet->get_cell($row,0)->value();
  81         my $new_uniquename = $worksheet->get_cell($row,1)->value();
  82
  83         print STDERR "$db_uniquename -> $new_uniquename\n";
  84
  85         my $old_stock = $schema->resultset('Stock::Stock')->find({ uniquename => $db_uniquename, type_id => $stock_type_id });
  86
  87         if (!$old_stock) {
  88             print STDERR "Warning! Stock with uniquename $db_uniquename was not found in the database.\n";
  89             next();
  90         }
  91         my $new_stock = $old_stock->update({ name => $new_uniquename, uniquename => $new_uniquename});
  92
  93     }
  94 };
  95
  96 my $transaction_error;
  97 try {
  98     eval($coderef->());
  99 } catch {
 100     $transaction_error =  $_;
 101 };
 102
 103 if ($opt_t) {
 104     print STDERR "Not storing with test flag (-t). Rolling back.\n";
 105     $schema->txn_rollback();
 106 }
 107 elsif ($transaction_error) {
 108     print STDERR "Transaction error storing terms: $transaction_error. Rolling back.\n";
 109     $schema->txn_rollback();
 110 } else {
 111     print STDERR "Everything looks good. Committing.\n";
 112     $schema->txn_commit();
 113     print STDERR "Script Complete.\n";
 114 }