bin/rename_cvterms.pl

   1 #!/usr/bin/perl
   2
   3 =head1
   4
   5 rename_cvterms.pl - for renaming cvterms in bulk
   6
   7 =head1 SYNOPSIS
   8
   9     rename_cvterms.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 cvterms in bulk. The infile provided has two columns, in the first column is the cvterm name as it is in the database, and in the second column is the new cvterm name. There is no header on hte infile and the infile is .xls
  20
  21
  22 =head1 AUTHOR
  23
  24  Nicolas Morales (nm529@cornell.edu)
  25
  26 =cut
  27
  28 use strict;
  29
  30 use Getopt::Std;
  31 use Data::Dumper;
  32 use Carp qw /croak/ ;
  33 use Pod::Usage;
  34 use Spreadsheet::ParseExcel;
  35 use Bio::Chado::Schema;
  36 use CXGN::DB::InsertDBH;
  37 use Try::Tiny;
  38
  39 our ($opt_H, $opt_D, $opt_i, $opt_c);
  40
  41 getopts('H:D:i:c:');
  42
  43 if (!$opt_H || !$opt_D || !$opt_i || !$opt_c) {
  44     pod2usage(-verbose => 2, -message => "Must provide options -H (hostname), -D (database name), -i (input file), -c CVNAME \n");
  45 }
  46
  47 my $dbhost = $opt_H;
  48 my $dbname = $opt_D;
  49 my $parser   = Spreadsheet::ParseExcel->new();
  50 my $excel_obj = $parser->parse($opt_i);
  51
  52 my $dbh = CXGN::DB::InsertDBH->new({
  53         dbhost=>$dbhost,
  54         dbname=>$dbname,
  55         dbargs => {AutoCommit => 1, RaiseError => 1}
  56 });
  57
  58 my $schema= Bio::Chado::Schema->connect(  sub { $dbh->get_actual_dbh() } );
  59 $dbh->do('SET search_path TO public,sgn');
  60
  61
  62 my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
  63 my ( $row_min, $row_max ) = $worksheet->row_range();
  64 my ( $col_min, $col_max ) = $worksheet->col_range();
  65
  66 my $coderef = sub {
  67     my $cv = $schema->resultset('Cv::Cv')->find({ name => $opt_c });
  68     for my $row ( 0 .. $row_max ) {
  69
  70         my $db_cvterm_name = $worksheet->get_cell($row,0)->value();
  71         my $new_cvterm_name = $worksheet->get_cell($row,1)->value();
  72         print STDERR $db_cvterm_name."\n";
  73
  74         my $old_cvterm = $schema->resultset('Cv::Cvterm')->find({ name => $db_cvterm_name, cv_id => $cv->cv_id() });
  75         my $new_cvterm = $old_cvterm->update({ name => $new_cvterm_name});
  76
  77     }
  78 };
  79
  80 my $transaction_error;
  81 try {
  82     $schema->txn_do($coderef);
  83 } catch {
  84     $transaction_error =  $_;
  85 };
  86
  87 if ($transaction_error) {
  88     print STDERR "Transaction error storing terms: $transaction_error\n";
  89 } else {
  90     print STDERR "Script Complete.\n";
  91 }