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
  17 =head1 DESCRIPTION
  18
  19 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.
  20
  21 =head1 AUTHOR
  22
  23  Guillaume Bauchet (gjb99@cornell.edu)
  24
  25  Adapted from a cvterm renaming script by:
  26  Nicolas Morales (nm529@cornell.edu)
  27
  28 =cut
  29
  30 use strict;
  31
  32 use Getopt::Std;
  33 use Data::Dumper;
  34 use Carp qw /croak/ ;
  35 use Pod::Usage;
  36 use Spreadsheet::ParseExcel;
  37 use Bio::Chado::Schema;
  38 use CXGN::DB::InsertDBH;
  39 use Try::Tiny;
  40
  41 our ($opt_H, $opt_D, $opt_i);
  42
  43 getopts('H:D:i:');
  44
  45 if (!$opt_H || !$opt_D || !$opt_i) {
  46     pod2usage(-verbose => 2, -message => "Must provide options -H (hostname), -D (database name), -i (input file)\n");
  47 }
  48
  49 my $dbhost = $opt_H;
  50 my $dbname = $opt_D;
  51 my $parser   = Spreadsheet::ParseExcel->new();
  52 my $excel_obj = $parser->parse($opt_i);
  53
  54 my $dbh = CXGN::DB::InsertDBH->new({
  55         dbhost=>$dbhost,
  56         dbname=>$dbname,
  57         dbargs => {AutoCommit => 1, RaiseError => 1}
  58 });
  59
  60 my $schema= Bio::Chado::Schema->connect(  sub { $dbh->get_actual_dbh() } );
  61 $dbh->do('SET search_path TO public,sgn');
  62
  63
  64 my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
  65 my ( $row_min, $row_max ) = $worksheet->row_range();
  66 my ( $col_min, $col_max ) = $worksheet->col_range();
  67
  68 my $coderef = sub {
  69     for my $row ( 0 .. $row_max ) {
  70
  71         my $db_uniquename = $worksheet->get_cell($row,0)->value();
  72         my $new_uniquename = $worksheet->get_cell($row,1)->value();
  73
  74         print STDERR "$db_uniquename -> $new_uniquename\n";
  75
  76         my $old_stock = $schema->resultset('Stock::Stock')->find({ uniquename => $db_uniquename });
  77
  78         if (!$old_stock) {
  79             print STDERR "Warning! Stock with uniquename $db_uniquename was not found in the database.\n";
  80             next();
  81         }
  82         my $new_stock = $old_stock->update({ uniquename => $new_uniquename});
  83
  84     }
  85 };
  86
  87 my $transaction_error;
  88 try {
  89     $schema->txn_do($coderef);
  90 } catch {
  91     $transaction_error =  $_;
  92 };
  93
  94 if ($transaction_error) {
  95     print STDERR "Transaction error storing terms: $transaction_error\n";
  96 } else {
  97     print STDERR "Script Complete.\n";
  98 }