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 and .xlsx.
  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 Spreadsheet::ParseXLSX;
  36 use Bio::Chado::Schema;
  37 use CXGN::DB::InsertDBH;
  38 use Try::Tiny;
  39
  40 our ($opt_H, $opt_D, $opt_i, $opt_c);
  41
  42 getopts('H:D:i:c:');
  43
  44 if (!$opt_H || !$opt_D || !$opt_i || !$opt_c) {
  45     pod2usage(-verbose => 2, -message => "Must provide options -H (hostname), -D (database name), -i (input file), -c CVNAME \n");
  46 }
  47
  48 my $dbhost = $opt_H;
  49 my $dbname = $opt_D;
  50
  51 # Match a dot, extension .xls / .xlsx
  52 my ($extension) = $opt_i =~ /(\.[^.]+)$/;
  53 my $parser;
  54
  55 if ($extension eq '.xlsx') {
  56     $parser = Spreadsheet::ParseXLSX->new();
  57 }
  58 else {
  59     $parser = Spreadsheet::ParseExcel->new();
  60 }
  61
  62 my $excel_obj = $parser->parse($opt_i);
  63
  64 my $dbh = CXGN::DB::InsertDBH->new({
  65         dbhost=>$dbhost,
  66         dbname=>$dbname,
  67         dbargs => {AutoCommit => 1, RaiseError => 1}
  68 });
  69
  70 my $schema= Bio::Chado::Schema->connect(  sub { $dbh->get_actual_dbh() } );
  71 $dbh->do('SET search_path TO public,sgn');
  72
  73
  74 my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
  75 my ( $row_min, $row_max ) = $worksheet->row_range();
  76 my ( $col_min, $col_max ) = $worksheet->col_range();
  77
  78 my $coderef = sub {
  79     my $cv = $schema->resultset('Cv::Cv')->find({ name => $opt_c });
  80     for my $row ( 0 .. $row_max ) {
  81
  82         my $db_cvterm_name = $worksheet->get_cell($row,0)->value();
  83         my $new_cvterm_name = $worksheet->get_cell($row,1)->value();
  84         print STDERR $db_cvterm_name."\n";
  85
  86         my $old_cvterm = $schema->resultset('Cv::Cvterm')->find({ name => $db_cvterm_name, cv_id => $cv->cv_id() });
  87         my $new_cvterm = $old_cvterm->update({ name => $new_cvterm_name});
  88
  89     }
  90 };
  91
  92 my $transaction_error;
  93 try {
  94     $schema->txn_do($coderef);
  95 } catch {
  96     $transaction_error =  $_;
  97 };
  98
  99 if ($transaction_error) {
 100     print STDERR "Transaction error storing terms: $transaction_error\n";
 101 } else {
 102     print STDERR "Script Complete.\n";
 103 }