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