bin/load_l2l_batch_associations.pl

   1 use strict;
   2 use warnings;
   3
   4
   5 #use CXGN::DB::Connection;
   6 #use CXGN::Login;
   7 #use CXGN::Contact;
   8 #use CXGN::People::Person;
   9 use CXGN::Phenome::Locus2Locus;
  10 use CXGN::Phenome::Locus;
  11 use CXGN::DB::InsertDBH;
  12
  13 use Getopt::Std;
  14
  15 our ($opt_H, $opt_D, $opt_x);
  16
  17 getopts('H:D:x');
  18
  19 print STDERR "opt_H = $opt_H\n";
  20
  21 ######data that need to be provided #####################3
  22 my $locus_name = 'locus name with put the suffix number'; #locus name with out the suffix number
  23 my $locus_symbol = 'locus symbol'; #locus symbol without the number suffix
  24 my $organism = 'tomato'; #well, you know what..:
  25 #my $subject_id = 5584;
  26
  27 my $relationship_type= 'homolog'; # eg. 'homolog'
  28 my $evidence_type = 'sequence or structural similarity'; # eg. 'sequence or structural similarity'
  29 my $pubmed_id = 12514239;
  30 my $sp_person_id = 865;
  31
  32 #################################
  33
  34 my ($evidence_id,  $reference_id, $relationship_id);
  35 my $count=0;
  36
  37 $locus_name =~ /(\w+)/;
  38 $locus_name =$1;
  39 print "$locus_name\n";
  40 $locus_symbol =~ /(\D+)/;
  41 $locus_symbol =$1;
  42
  43 print "$locus_symbol\n";
  44
  45
  46 my $dbh = CXGN::DB::InsertDBH->new({dbname=>$opt_D, dbhost=>$opt_H, dbuser=>"postgres"});
  47 my (@all_locus_id, @all_symbol, @all_name, @all_organism);
  48
  49
  50 ###### retreiving relationship id, evidence_id, and reference_id (dbxrefs)
  51 my $sth_rel = $dbh->prepare("SELECT cvterm.dbxref_id FROM public.cvterm
  52                                     WHERE cvterm.name ILIKE ?
  53                                     AND cvterm.is_obsolete = 0");
  54
  55 $sth_rel->execute($relationship_type);
  56 ($relationship_id) = $sth_rel->fetchrow_array();
  57
  58 my $sth_evi = $dbh->prepare("SELECT dbxref_id FROM public.cvterm where cv_id = 19 AND cvterm.name ILIKE '%$evidence_type'");
  59 $sth_evi->execute();
  60 ($reference_id) = $sth_evi->fetchrow_array();
  61
  62 my $sth_ref = $dbh->prepare("SELECT dbxref_id FROM public.dbxref WHERE accession = ?");
  63 $sth_ref->execute($pubmed_id);
  64 ($reference_id) = $sth_ref->fetchrow_array();
  65
  66 ###############################################
  67
  68
  69 if (($locus_name) && ($organism)) {
  70     my $locus_query = "SELECT locus_id, locus_symbol, locus_name, common_name FROM phenome.locus
  71                                    JOIN sgn.common_name USING (common_name_id)
  72                                    WHERE locus_symbol ILIKE '$locus_symbol%' AND locus_name ILIKE '$locus_name%'
  73                                    AND common_name ILIKE '$organism' AND locus.obsolete='f'";
  74
  75
  76 #AND (locus_id != $object_id)
  77     my $sth = $dbh->prepare($locus_query);
  78     $sth->execute();
  79
  80     while ( my ($all_locus_id, $all_symbol, $all_name, $all_organism) = $sth->fetchrow_array()) {
  81
  82         push @all_locus_id, $all_locus_id;
  83         push @all_symbol, $all_symbol;
  84         push @all_name, $all_name;
  85         push @all_organism, $all_organism;
  86
  87         print "retreiving locus_id: $all_locus_id\t symbol: $all_symbol\t name: $all_name\t organism: $all_organism\n";
  88     }
  89 }
  90
  91
  92 for (my $i=0; $i<@all_name; $i++) {
  93     my $subj_id = $all_locus_id[$i];
  94     my $subj_name = $all_name[$i];
  95     my $subj_symbol = $all_symbol[$i];
  96     my $subj_name_full = $subj_name;
  97
  98     $subj_name =~ /(\w+)/;
  99     $subj_name =$1;
 100
 101     $subj_symbol =~/(\D+)/;
 102     $subj_symbol =$1;
 103
 104     my $locus_query = "SELECT locus_id, locus_symbol, locus_name, common_name FROM phenome.locus
 105                              LEFT JOIN sgn.common_name USING (common_name_id)
 106                              WHERE (locus_name ILIKE '$subj_name%'
 107                              AND locus_symbol ILIKE '$subj_symbol%'
 108                              AND locus_name NOT ILIKE '$subj_name_full')
 109                              AND (sgn.common_name.common_name ILIKE '$organism')
 110                              AND locus.obsolete='f'";
 111
 112         #if (!$reference_id) {$reference_id = undef};
 113
 114     my $sth = $dbh->prepare($locus_query);
 115     $sth->execute();
 116     my (@obj_id, @symbol, @name, @common_name);
 117     my ($obj_id, $symbol, $name, $common_name);
 118
 119
 120
 121     while (($obj_id, $symbol, $name, $common_name) = $sth->fetchrow_array()) {
 122         my $sth1 = $dbh->prepare("SELECT locus2locus_id FROM phenome.locus2locus
 123                                   WHERE (subject_id = $subj_id AND object_id  = $obj_id)
 124                                   OR (subject_id = $obj_id AND object_id  = $subj_id) ");
 125         $sth1->execute();
 126         my ($l2l_id1) = $sth1->fetchrow_array();
 127
 128         print "l2l_id1: $l2l_id1\n";
 129         if ($l2l_id1)  {print "l2l association already exists for locus: $subj_id\n";}
 130         else {
 131                 my $locus2locus=CXGN::Phenome::Locus2Locus->new($dbh);
 132                 $locus2locus->set_subject_id($subj_id);
 133                 $locus2locus->set_object_id($obj_id);
 134                 $locus2locus->set_relationship_id($relationship_id);
 135                 $locus2locus->set_evidence_id($evidence_id);
 136                 $locus2locus->set_reference_id($reference_id);
 137                 $locus2locus->set_sp_person_id($sp_person_id);
 138
 139                 print STDERR "Storing...object: $obj_id to subject: $subj_id\n";
 140
 141                 my $locus2locus_id=$locus2locus->store();
 142                 $count++;
 143
 144                 if ($@) { warn "Locus to locus  association failed! $@"; }
 145
 146 #       }#else {print "l2l association already exists for locus: $subj_id\n";}
 147 #    }
 148 #}
 149
 150
 151 #           my $person = CXGN::People::Person->new($sp_person_id);
 152
 153
 154 #           my $user_link = qq |http://www.sgn.cornell.edu/solpeople/personal-info.pl?sp_person_id=$sp_person_id |;
 155 #           my $subject_locus_link= qq |http://www.sgn.cornell.edu/phenome/locus_display.pl?locus_id=$subj_id |;
 156 #           my $object_locus_link= qq |http://www.sgn.cornell.edu/phenome/locus_display.pl?locus_id=$obj_id |;
 157 #           my $subject="[New locus2locus association created]";
 158 #           my $username= $person->get_first_name()." ".$person->get_last_name();
 159 #           my $fdbk_body="$username ($user_link) has associated locus $object_locus_link \n to locus $subject_locus_link \n ";
 160
 161 #           CXGN::Contact::send_email($subject,$fdbk_body, 'iyt2@cornell.edu');
 162         }
 163
 164     }
 165 }
 166
 167 print "$count associations created\n";
 168 if ($@ || $opt_x) {
 169    $dbh->rollback();
 170   print STDERR "An error occurred: $@\n";
 171
 172 }
 173 else {
 174   print STDERR "Committing...\n";
 175    $dbh->commit();
 176 }
 177
 178 #$dbh->rollback();
 179 #print STDERR "Done.  \n";
 180 #}