bin/merge_stocks.pl

   1
   2 =head1 NAME
   3
   4 merge_stocks.pl - merge stocks using a file with stocks to merge
   5
   6 =head1 DESCRIPTION
   7
   8 merge_stocks.pl -H [database host] -D [database name]  [ -x ] mergefile.txt
   9
  10 Options:
  11
  12  -H the database host
  13  -D the database name
  14  -x flag; if present, delete the empty remaining accession
  15  -P password
  16
  17 mergefile.txt: A tab-separated file with two columns. Include the following header as the first line: bad name  good name
  18
  19 All the metadata of bad name will be transferred to good name.
  20 If -x is used, stock with name bad name will be deleted.
  21
  22 =head1 AUTHOR
  23
  24 Lukas Mueller <lam87@cornell.edu>
  25
  26 =cut
  27
  28 use strict;
  29
  30 use Getopt::Std;
  31 use CXGN::DB::InsertDBH;
  32 use CXGN::DB::Schemas;
  33 use CXGN::Stock;
  34
  35
  36 our($opt_H, $opt_D, $opt_x, $opt_P);
  37 getopts('H:D:xP:');
  38
  39 my $pw = $opt_P;
  40
  41 if (! $pw) {
  42     print "Password for $opt_H / $opt_D: \n";
  43     $pw = (<STDIN>);
  44     chomp($pw);
  45 }
  46
  47 my $delete_merged_stock = $opt_x;
  48
  49 print STDERR "Note: -x: Deleting stocks that have been merged into other stocks.\n";
  50
  51 print STDERR "Connecting to database...\n";
  52 my $dsn = 'dbi:Pg:database='.$opt_D.";host=".$opt_H.";port=5432";
  53
  54 my $dbh = DBI->connect($dsn, "postgres", $pw, { AutoCommit => 0, RaiseError=>1 });
  55
  56 print STDERR "Connecting to DBI schema...\n";
  57 my $bcs_schema = Bio::Chado::Schema->connect($dsn, "postgres", $pw);
  58
  59 my $s = CXGN::DB::Schemas->new({ dbh => $dbh });
  60 my $schema = $s->bcs_schema();
  61 my $file = shift;
  62
  63 open(my $F, "<", $file) || die "Can't open file $file.\n";
  64
  65 my $header = <$F>;
  66
  67 my @merged_stocks_to_delete = ();
  68 my @merge_errors = ();
  69
  70 print STDERR "Skipping header line $header\n";
  71 eval {
  72     while (<$F>) {
  73         print STDERR "Read line: $_\n";
  74         chomp;
  75         my ($merge_stock_name, $good_stock_name) = split /\t/;
  76         print STDERR "bad name: $merge_stock_name, good name: $good_stock_name\n";
  77
  78         # for now, only allow accessions to be merged!
  79         my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession', 'stock_type')->cvterm_id();
  80
  81         print STDERR "Working with accession type id of $accession_type_id...\n";
  82
  83         my $stock_row = $schema->resultset("Stock::Stock")->find( { uniquename => $good_stock_name, type_id=>$accession_type_id } );
  84         if (!$stock_row) {
  85             print STDERR "Stock $good_stock_name (of type accession) not found. Skipping...\n";
  86
  87             next();
  88         }
  89
  90         my $merge_row = $schema->resultset("Stock::Stock")->find( { uniquename => $merge_stock_name, type_id => $accession_type_id  } );
  91         if (!$merge_row) {
  92             print STDERR "Stock $merge_stock_name (of type accession) not available for merging. Skipping\n";
  93             next();
  94         }
  95
  96         my $good_stock = CXGN::Stock->new( { schema => $schema, stock_id => $stock_row->stock_id });
  97         my $merge_stock = CXGN::Stock->new( { schema => $schema, stock_id => $merge_row->stock_id });
  98
  99         print STDERR "Merging stock $merge_stock_name into $good_stock_name... ";
 100         my $merge_error = $good_stock->merge($merge_stock->stock_id());
 101
 102         if ( $merge_error ) {
 103                 push @merge_errors, "ERROR: Could not merge $merge_stock_name into $good_stock_name [$merge_error]";
 104                 next();
 105         }
 106
 107         if ($delete_merged_stock) {
 108             push @merged_stocks_to_delete, $merge_stock->stock_id();
 109         }
 110
 111         print STDERR "Done.\n";
 112     }
 113
 114
 115     if ($delete_merged_stock) {
 116         print STDERR "Delete merged stocks ( -x option)...\n";
 117         foreach my $remove_stock_id (@merged_stocks_to_delete) {
 118             my $q = "delete from phenome.stock_owner where stock_id=?";
 119             my $h = $dbh->prepare($q);
 120             $h->execute($remove_stock_id);
 121
 122             $q = "delete from phenome.stock_image where stock_id=?";
 123             $h = $dbh->prepare($q);
 124             $h->execute($remove_stock_id);
 125
 126             my $row = $schema->resultset('Stock::Stock')->find( { stock_id => $remove_stock_id });
 127             print STDERR "Deleting stock ".$row->uniquename." (id=$remove_stock_id)\n";
 128             $row->delete();
 129         }
 130         print STDERR "Done with deletions.\n";
 131     }
 132
 133 };
 134 if ($@) {
 135     print STDERR "An ERROR occurred ($@). Rolling back changes...\n";
 136     $dbh->rollback();
 137 }
 138 else {
 139     print STDERR "Script is done. Committing... ";
 140     $dbh->commit();
 141
 142         if ( scalar(@merge_errors) > 0 ) {
 143                 print STDERR "WARNING: THE FOLLOWING STOCKS COULD NOT BE MERGED!\n";
 144                 foreach (@merge_errors) {
 145                         print STDERR "$_\n";
 146                 }
 147         }
 148     print STDERR "Done.\n";
 149 }