Merge pull request #2754 from solgenomics/topic/fix_homepage_add_accessions_dialog
[sgn.git] / bin / rename_stocks.pl
blob9b06e846d7ccb7066c49e73a4a7361e8e6fa0c6a
1 #!/usr/bin/perl
3 =head1
5 rename_stocks.pl - a script for renaming stocks
7 =head1 SYNOPSIS
9 rename_stocks.pl -H [dbhost] -D [dbname] -i [infile]
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)
17 =head1 DESCRIPTION
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.
21 =head1 AUTHOR
23 Guillaume Bauchet (gjb99@cornell.edu)
25 Adapted from a cvterm renaming script by:
26 Nicolas Morales (nm529@cornell.edu)
28 =cut
30 use strict;
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;
41 our ($opt_H, $opt_D, $opt_i);
43 getopts('H:D:i:');
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");
49 my $dbhost = $opt_H;
50 my $dbname = $opt_D;
51 my $parser = Spreadsheet::ParseExcel->new();
52 my $excel_obj = $parser->parse($opt_i);
54 my $dbh = CXGN::DB::InsertDBH->new({
55 dbhost=>$dbhost,
56 dbname=>$dbname,
57 dbargs => {AutoCommit => 1, RaiseError => 1}
58 });
60 my $schema= Bio::Chado::Schema->connect( sub { $dbh->get_actual_dbh() } );
61 $dbh->do('SET search_path TO public,sgn');
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();
68 my $coderef = sub {
69 for my $row ( 0 .. $row_max ) {
71 my $db_uniquename = $worksheet->get_cell($row,0)->value();
72 my $new_uniquename = $worksheet->get_cell($row,1)->value();
74 print STDERR "$db_uniquename -> $new_uniquename\n";
76 my $old_stock = $schema->resultset('Stock::Stock')->find({ uniquename => $db_uniquename });
78 if (!$old_stock) {
79 print STDERR "Warning! Stock with uniquename $db_uniquename was not found in the database.\n";
80 next();
82 my $new_stock = $old_stock->update({ uniquename => $new_uniquename});
87 my $transaction_error;
88 try {
89 $schema->txn_do($coderef);
90 } catch {
91 $transaction_error = $_;
94 if ($transaction_error) {
95 print STDERR "Transaction error storing terms: $transaction_error\n";
96 } else {
97 print STDERR "Script Complete.\n";