lib/CXGN/Accession/Tools.pm

   1
   2 =head1 NAME
   3
   4 CXGN::Accession::Tools
   5
   6 =head1 AUTHOR
   7
   8 John Binns <zombieite@gmail.com>
   9
  10 =head1 DESCRIPTION
  11
  12 Non-object-oriented tools for dealing with accession data.
  13
  14 =head2 all_accessions_extra_verbose
  15
  16     print CXGN::Accession::Tools::all_accessions_extra_verbose();
  17
  18 =head2 partial_name_to_ids
  19
  20     #get all accession ids for accessions with name like...
  21     my @accession_ids=CXGN::Accession::Tools::partial_name_to_ids('LA7');
  22
  23 =head2 insert_accession
  24
  25     CXGN::Accession::Tools::insert_accession($dbh,$accname,$species,$common_name);
  26
  27 =cut
  28
  29 use strict;
  30 use CXGN::DB::Connection;
  31 use CXGN::Accession;
  32
  33 package CXGN::Accession::Tools;
  34
  35 sub all_accessions
  36 {
  37     my ($dbh)=@_;
  38     my @accessions;
  39     my $accession_query=$dbh->prepare('select accession_id from accession');
  40     $accession_query->execute();
  41     while(my ($accession_id) = $accession_query->fetchrow_array())
  42     {
  43         #warn"$accession_id\n";
  44         my %hash;
  45         $hash{accession_id}=$accession_id;
  46         my $accession=CXGN::Accession->new($dbh,$accession_id);
  47         $hash{verbose_name}=$accession->verbose_name();
  48         #warn"$hash{verbose_name}\n";
  49         push(@accessions,\%hash);
  50     }
  51     return @accessions;
  52 }
  53
  54 sub partial_name_to_ids
  55 {
  56     my($dbh,$partial_name)=@_;
  57     $partial_name=$dbh->quote('%'.$partial_name.'%');
  58     my $query="select accession_id from accession_names where accession_name ilike $partial_name";
  59     my $id_query=$dbh->prepare($query);
  60     $id_query->execute();
  61     my @ids;
  62     while(my($id)=$id_query->fetchrow_array()){push(@ids,$id);}
  63     return @ids;
  64 }
  65
  66 sub insert_accession {
  67
  68   my ($dbh, $accname, $species, $common) = @_;
  69
  70   # does the common name exist? if not, insert it.
  71   my $common_id;
  72   ($common_id) = $dbh->selectrow_array("SELECT common_name_id FROM common_name WHERE common_name.common_name ilike '$common'");
  73
  74   unless ($common_id) {
  75     $dbh->do("INSERT INTO common_name (common_name) VALUES ('$common')");
  76     $common_id = $dbh->last_insert_id('common_name','sgn');
  77     warn "inserting $common\n";
  78   }
  79
  80   warn "using $common ($common_id)\n";
  81
  82   # does the species exist? if not, insert it.
  83   my $organism_id;
  84   ($organism_id) = $dbh->selectrow_array("SELECT organism_id FROM organism WHERE organism_name ilike '$species\%'");
  85
  86   unless($organism_id){
  87     $dbh->do("INSERT INTO organism (organism_name) VALUES ('$species')");
  88     $organism_id = $dbh->last_insert_id('organism','sgn');
  89
  90     warn "inserting $species\n";
  91   }
  92
  93   warn "using $species ($organism_id)\n";
  94
  95   # does there exist an accession with this name? If so, do nothing
  96   # and return.
  97   my $accession_name_id;
  98   ($accession_name_id) = $dbh->selectrow_array("SELECT accession_name_id FROM accession_names WHERE accession_name ILIKE '$accname'");
  99
 100   if($accession_name_id){
 101
 102     # the accession is already in there, so no problem!
 103     warn "found $accname ($accession_name_id)\n";
 104     return;
 105
 106   } else {
 107     # If not, insert the name and the accession.
 108
 109
 110     $dbh->do("INSERT INTO accession_names (accession_name) VALUES ('$accname')");
 111     $accession_name_id = $dbh->last_insert_id('accession_names','sgn');
 112     warn "using $accname ($accession_name_id)\n";
 113     $dbh->do("INSERT INTO accession (organism_id, accession_name_id) VALUES ($organism_id, $accession_name_id)");
 114     my $accession_id = $dbh->last_insert_id('accession','sgn');
 115     $dbh->do("UPDATE accession_names SET accession_id = $accession_id WHERE accession_name_id = $accession_name_id");
 116
 117   }
 118
 119
 120 }
 121
 122 1;