db/CopyAccessionToStock.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  CopyAccessionToStock.pm
   7
   8 =head1 SYNOPSIS
   9
  10 mx-run ThisPackageName [options] -H hostname -D dbname -u username [-F]
  11
  12 this is a subclass of L<CXGN::Metadata::Dbpatch>
  13 see the perldoc of parent class for more details.
  14
  15 =head1 DESCRIPTION
  16
  17 This is a patch for copying sgn accessions into the stock module.
  18 sgn.accession should be deprecated! This will require some code refactoring like the genetic maps.
  19
  20 This subclass uses L<Moose>. The parent class uses L<MooseX::Runnable>
  21
  22 =head1 AUTHOR
  23
  24  Naama Menda<nm249@cornell.edu>
  25
  26 =head1 COPYRIGHT & LICENSE
  27
  28 Copyright 2010 Boyce Thompson Institute for Plant Research
  29
  30 This program is free software; you can redistribute it and/or modify
  31 it under the same terms as Perl itself.
  32
  33 =cut
  34
  35
  36 package CopyAccessionToStock;
  37
  38 use Moose;
  39 extends 'CXGN::Metadata::Dbpatch';
  40 use Bio::Chado::Schema;
  41 use Try::Tiny;
  42
  43 sub init_patch {
  44     my $self=shift;
  45     my $name = __PACKAGE__;
  46     print "dbpatch name is ':" .  $name . "\n\n";
  47     my $description = 'populate the chado stock table with sgn accessions';
  48     my @previous_requested_patches = ('AddStockLinks');
  49     $self->name($name);
  50     $self->description($description);
  51     $self->prereq(\@previous_requested_patches);
  52
  53 }
  54
  55 sub patch {
  56     my $self=shift;
  57
  58     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  59
  60     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  61
  62     my $schema = Bio::Chado::Schema->connect( sub { $self->dbh->clone } );
  63
  64     my $coderef = sub {
  65     my $q = "SELECT accession.accession_id, common_name, chado_organism_id , accession_name FROM sgn.accession JOIN sgn.accession_names USING (accession_name_id)";
  66     my $sth = $self->dbh->prepare($q);
  67     $sth->execute();
  68
  69     my $accession_cvterm = $schema->resultset("Cv::Cvterm")->create_with( {
  70         name => 'accession' ,
  71         cv     => 'stock type',
  72         db     => 'null',
  73         dbxref => 'accession', });
  74
  75     my $accession_cvterm_id = $accession_cvterm->cvterm_id;
  76
  77     while ( my ($accession_id, $cname, $organism_id , $acc_name)  = $sth->fetchrow_array) {
  78         print "Storing new stock $acc_name ($cname)\n";
  79         $cname = " ($cname)" if $cname;
  80         my $stock = $schema->resultset("Stock::Stock")->find_or_create(
  81             { name   => $acc_name,
  82               uniquename => $acc_name.$cname,
  83               organism_id     => $organism_id,
  84               type_id  => $accession_cvterm_id,
  85             });
  86         my $stockprop = $stock->create_stockprops( { stock_synonym => $cname } , {autocreate => 1 } );
  87         #add the stock_id to the accession table
  88         my $stock_id = $stock->stock_id;
  89         my $q = "UPDATE sgn.accession SET stock_id = ? WHERE accession_id = ?";
  90         my $sth = $self->dbh->prepare($q);
  91         $sth->execute($stock_id, $accession_id);
  92
  93     }
  94     print "You're done!\n";
  95     if ($self->trial) {
  96         print "Trial mode! Rolling back transaction\n\n";
  97         $schema->txn_rollback;
  98     }
  99     return 1;
 100     };
 101
 102     try {
 103         $schema->txn_do($coderef);
 104         print "Data committed! \n";
 105     } catch {
 106         die "Load failed! " . $_ . "\n" ;
 107     };
 108 }
 109
 110
 111 ####
 112 1; #
 113 ####
 114