Archive Ensembl HomeArchive Ensembl Home
DBEntryAdaptor.pm
Go to the documentation of this file.
00001 =head1 LICENSE
00002 
00003   Copyright (c) 1999-2012 The European Bioinformatics Institute and
00004   Genome Research Limited.  All rights reserved.
00005 
00006   This software is distributed under a modified Apache license.
00007   For license details, please see
00008 
00009     http://www.ensembl.org/info/about/code_licence.html
00010 
00011 =head1 CONTACT
00012 
00013   Please email comments or questions to the public Ensembl
00014   developers list at <dev@ensembl.org>.
00015 
00016   Questions may also be sent to the Ensembl help desk at
00017   <helpdesk@ensembl.org>.
00018 
00019 =cut
00020 
00021 =head1 NAME
00022 
00023 Bio::EnsEMBL::DBSQL::DBEntryAdaptor -
00024 MySQL Database queries to load and store external object references.
00025 
00026 =head1 SYNOPSIS
00027 
00028   $db_entry_adaptor =
00029     $registry->get_adaptor( 'Human', 'Core', 'DBEntry' );
00030 
00031   $db_entry = $db_entry_adaptor->fetch_by_dbID($id);
00032 
00033   my $gene_adaptor = $registry->get_adaptor( 'Human', 'Core', 'Gene' );
00034 
00035   my $gene = $gene_adaptor->fetch_by_stable_id('ENSG00000101367');
00036 
00037   @db_entries = @{ $db_entry_adaptor->fetch_all_by_Gene($gene) };
00038   @gene_ids   = $db_entry_adaptor->list_gene_ids_by_extids('BAB15482');
00039 
00040 =head1 METHODS
00041 
00042 =cut
00043 
00044 package Bio::EnsEMBL::DBSQL::DBEntryAdaptor;
00045 
00046 use Bio::EnsEMBL::DBSQL::BaseAdaptor;
00047 
00048 use Bio::EnsEMBL::DBEntry;
00049 use Bio::EnsEMBL::IdentityXref;
00050 use Bio::EnsEMBL::OntologyXref;
00051 
00052 use Bio::EnsEMBL::Utils::Exception qw(deprecate throw warning);
00053 
00054 use vars qw(@ISA);
00055 use strict;
00056 
00057 @ISA = qw( Bio::EnsEMBL::DBSQL::BaseAdaptor );
00058 
00059 =head2 fetch_by_dbID
00060 
00061   Arg [1]    : int $dbID
00062                the unique database identifier for the DBEntry to retrieve
00063   Example    : my $db_entry = $db_entry_adaptor->fetch_by_dbID($dbID);
00064   Description: Retrieves a dbEntry from the database via its unique
00065                identifier.
00066   Returntype : Bio::EnsEMBL::DBEntry
00067   Exceptions : none
00068   Caller     : general
00069   Status     : Stable
00070 
00071 =cut
00072 
00073 sub fetch_by_dbID {
00074   my ( $self, $dbID ) = @_;
00075 
00076   my $sth = $self->prepare(
00077     "SELECT  xref.xref_id,
00078             xref.dbprimary_acc,
00079             xref.display_label,
00080             xref.version,
00081             exDB.priority,
00082             exDB.db_name,
00083             exDB.db_display_name,
00084             exDB.db_release,
00085             es.synonym,
00086             xref.info_type,
00087             xref.info_text,
00088             exDB.type,
00089             exDB.secondary_db_name,
00090             exDB.secondary_db_table,
00091             xref.description
00092     FROM    (xref, external_db exDB)
00093     LEFT JOIN external_synonym es ON
00094             es.xref_id = xref.xref_id
00095     WHERE   xref.xref_id = ?
00096     AND     xref.external_db_id = exDB.external_db_id" );
00097 
00098   $sth->bind_param( 1, $dbID, SQL_INTEGER );
00099   $sth->execute();
00100 
00101   my $exDB;
00102 
00103   my $max_rows = 1000;
00104 
00105   while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
00106       #$description refers to the external_db description, while $desc was referring the xref description
00107     while ( my $arrayref = shift( @{$rowcache} ) ) {
00108       my ( $refID,               $dbprimaryId,
00109            $displayid,           $version,
00110            $priority,
00111            $dbname,              $db_display_name,
00112            $release,             $synonym,
00113            $info_type,           $info_text,
00114            $type,                $secondary_db_name,
00115            $secondary_db_table,  $description
00116       ) = @$arrayref;
00117 
00118       if ( !defined($exDB) ) {
00119         $exDB =
00120           Bio::EnsEMBL::DBEntry->new(
00121                            -adaptor             => $self,
00122                            -dbID                => $dbID,
00123                            -primary_id          => $dbprimaryId,
00124                            -display_id          => $displayid,
00125                            -version             => $version,
00126                            -release             => $release,
00127                            -dbname              => $dbname,
00128                            -priority            => $priority,
00129                            -db_display_name     => $db_display_name,
00130                            -info_type           => $info_type,
00131                            -info_text           => $info_text,
00132                            -type                => $type,
00133                            -secondary_db_name   => $secondary_db_name,
00134                            -secondary_db_table  => $secondary_db_table,
00135                -description         => $description
00136           );
00137 
00138 
00139       }
00140 
00141       if ( defined($synonym) ) { $exDB->add_synonym($synonym) }
00142 
00143     } ## end while ( my $arrayref = shift...
00144   } ## end while ( my $rowcache = $sth...
00145 
00146   $sth->finish();
00147 
00148   return $exDB;
00149 } ## end sub fetch_by_dbID
00150 
00151 
00152 sub _get_all_dm_loc_sth {
00153   my ($self, $constraint ,$ensembl_object ) = @_;
00154   my $object_type;
00155   if($ensembl_object->isa("Bio::EnsEMBL::Gene")){
00156     $object_type = "Gene";
00157   }
00158   elsif($ensembl_object->isa("Bio::EnsEMBL::Transcript")){
00159     $object_type = "Transcript";
00160   }
00161   elsif($ensembl_object->isa("Bio::EnsEMBL::Translation")){
00162     $object_type = "Translation";
00163   }
00164   elsif($ensembl_object->isa("Bio::EnsEMBL::Operon")){
00165     $object_type = "Operon";
00166   }
00167   elsif($ensembl_object->isa("Bio::EnsEMBL::OperonTranscript")){
00168     $object_type = "OperonTranscript";
00169   }
00170   else{
00171     warn(ref($ensembl_object)." is not a Gene Transcript or Translation object??\n");
00172     return undef;
00173   }
00174   my $sql = "SELECT xref.xref_id,
00175                xref.dbprimary_acc,
00176             xref.display_label,
00177             xref.version,
00178             exDB.priority,
00179             exDB.db_name,
00180             exDB.db_display_name,
00181             exDB.db_release,
00182             es.synonym,
00183             xref.info_type,
00184             xref.info_text,
00185             exDB.type,
00186             exDB.secondary_db_name,
00187             exDB.secondary_db_table,
00188             xref.description
00189     FROM    (xref, external_db exDB, dependent_xref dx, object_xref ox)
00190     LEFT JOIN external_synonym es ON
00191             es.xref_id = xref.xref_id
00192     WHERE   xref.external_db_id = exDB.external_db_id AND
00193             ox.xref_id = xref.xref_id AND
00194             ox.ensembl_object_type = \'$object_type\' AND
00195             ox.ensembl_id = ".$ensembl_object->dbID();
00196 
00197   if($constraint){
00198     $sql .= " AND $constraint";
00199   }
00200   else{
00201     die "NO constraint???\n";
00202   }
00203 
00204   my $sth = $self->prepare($sql) || die "Could not prepare $sql";
00205 
00206   return $self->_get_all_dm($sth);
00207 }
00208 
00209 sub _get_all_dm_sth {
00210   my ( $self, $constraint) = @_;
00211 
00212  my $sql = "SELECT xref.xref_id,
00213                xref.dbprimary_acc,
00214             xref.display_label,
00215             xref.version,
00216             exDB.priority,
00217             exDB.db_name,
00218             exDB.db_display_name,
00219             exDB.db_release,
00220             es.synonym,
00221             xref.info_type,
00222             xref.info_text,
00223             exDB.type,
00224             exDB.secondary_db_name,
00225             exDB.secondary_db_table,
00226             xref.description
00227     FROM    (xref, external_db exDB, dependent_xref dx)
00228     LEFT JOIN external_synonym es ON
00229             es.xref_id = xref.xref_id
00230     WHERE   xref.external_db_id = exDB.external_db_id ";
00231 
00232   if($constraint){
00233     $sql .= "AND $constraint";
00234   }
00235   else{
00236     die "NO constraint???\n";
00237   }
00238 
00239   my $sth = $self->prepare($sql) || die "Could not prepare $sql";
00240 
00241   return $self->_get_all_dm($sth);
00242 }
00243 
00244 
00245 sub _get_all_dm{
00246 
00247   my ($self, $sth) = @_;
00248 
00249 #  $sth->bind_param( 1, $dm_dbid, SQL_INTEGER );
00250 
00251 #  print $sth."\n";
00252   $sth->execute() || die "Not able to execute statement handle";
00253 
00254   my @list =();
00255   my %seen;
00256 
00257   my $max_rows = 1000;
00258   while ( my $rowcache = $sth->fetchall_arrayref(undef, $max_rows) ) {
00259     while ( my $arrayref = shift( @{$rowcache} ) ) {
00260       my ( $dbID,                $dbprimaryId,
00261            $displayid,           $version,
00262            $priority,
00263            $dbname,              $db_display_name,
00264            $release,             $synonym,
00265            $info_type,           $info_text,
00266            $type,                $secondary_db_name,
00267            $secondary_db_table,  $description
00268       ) = @$arrayref;
00269 
00270       if ( !defined($seen{$dbID}) ) {
00271        my $exDB =
00272           Bio::EnsEMBL::DBEntry->new(
00273                            -adaptor             => $self,
00274                            -dbID                => $dbID,
00275                            -primary_id          => $dbprimaryId,
00276                            -display_id          => $displayid,
00277                            -version             => $version,
00278                            -release             => $release,
00279                            -dbname              => $dbname,
00280                            -priority            => $priority,
00281                            -db_display_name     => $db_display_name,
00282                            -info_type           => $info_type,
00283                            -info_text           => $info_text,
00284                            -type                => $type,
00285                            -secondary_db_name   => $secondary_db_name,
00286                            -secondary_db_table  => $secondary_db_table,
00287                -description         => $description
00288           );
00289 
00290     if ($synonym) { $exDB->add_synonym($synonym) };
00291     $seen{$dbID} = 1;
00292     push @list, $exDB;
00293       }
00294 
00295 
00296 
00297     } ## end while ( my $arrayref = shift...
00298   } ## end while ( my $rowcache = $sth...
00299 
00300   $sth->finish();
00301 
00302   return \@list;
00303 
00304 }
00305 
00306 
00307 =head2 get_all_dependents
00308 
00309   Args[1]    : dbID of the DBentry to get the dependents of.
00310   Args[2]    : (optional) Bio::EnsEMBL::Gene, Transcript or Translation object
00311   Example    : my @dependents = @{ $dbe_adaptor->get_all_dependents(1234) };
00312   Description: Get a list of DBEntrys that are depenednet on the DBEntry.
00313                if an ensembl gene transcript or translation is given then only
00314                the ones on that object will be given
00315   Returntype : listref of DBEntrys. May be empty.
00316   Exceptions : none
00317   Caller     : DBEntry->get_all_dependnets
00318   Status     : UnStable
00319 
00320 =cut
00321 
00322 sub get_all_dependents {
00323   my ( $self, $dbid, $ensembl_object) = @_;
00324   
00325   if(defined($ensembl_object) and !($ensembl_object->isa("Bio::EnsEMBL::Feature") or $ensembl_object->isa("Bio::EnsEMBL::Translation"))){
00326     die ref($ensembl_object)." is not an Gene Transcript or Translation";
00327   }
00328   
00329   my $constraint = " dx.master_xref_id = $dbid AND  dx.dependent_xref_id = xref.xref_id";
00330   if(defined($ensembl_object)){
00331     return $self->_get_all_dm_loc_sth($constraint, $ensembl_object);
00332   }
00333   else{
00334     return $self->_get_all_dm_sth($constraint, $ensembl_object);
00335   }
00336 
00337 }
00338 
00339 =head2 get_all_masters
00340 
00341   Args[1]    : dbID of the DBentry to get the masters of.
00342   Args[2]    : (optional) Bio::EnsEMBL::Gene, Transcript or Translation object
00343   Example    : my @masters = @{ $dbe_adaptor->get_all_masters(1234) };
00344   Description: Get a list of DBEntrys that are the masters of the DBEntry.
00345                if an ensembl gene transcript or translation is given then only
00346                the ones on that object will be given.
00347   Returntype : listref of DBEntrys. May be empty.
00348   Exceptions : none
00349   Caller     : DBEntry->get_all_masters
00350   Status     : UnStable
00351 
00352 =cut
00353 
00354 sub get_all_masters {
00355   my ( $self, $dbid, $ensembl_object ) = @_;
00356   
00357   if(defined($ensembl_object) and !($ensembl_object->isa("Bio::EnsEMBL::Feature") or $ensembl_object->isa("Bio::EnsEMBL::Translation"))){
00358     die ref($ensembl_object)." is not an Gene Transcript or Translation";
00359   }
00360   
00361   my $constraint = "dx.dependent_xref_id = $dbid AND  dx.master_xref_id = xref.xref_id";
00362 
00363   if(defined($ensembl_object)){
00364     return $self->_get_all_dm_loc_sth($constraint, $ensembl_object);
00365   }
00366   else{
00367     return $self->_get_all_dm_sth($constraint, $ensembl_object);
00368   }
00369 #  return $self->_get_all_dm($constraint, $ensembl_object);
00370 }
00371 
00372 
00373 =head fetch_all_by_name
00374 
00375   Arg [1]    : string $name - The name of the external reference.
00376                found in accession, display_label or synonym
00377   Arg [2]    : (optional) string $dbname  - The name of the database which 
00378                the provided name is for.
00379 
00380   Example    : my $xref = @{$dbea->fetch_all_by_name('BRAC2','HGNC')}[0];
00381                print $xref->description(), "\n" if($xref);
00382   Description: Retrieves list of DBEntrys (xrefs) via a name.
00383                The accesion is looked for first then the synonym and finally
00384                the display_label.
00385                NOTE $dbname this is optional but adding this speeds the
00386                process up if you know what you are looking for.
00387 
00388                NOTE:  In a multi-species database, this method will
00389                return all the entries matching the search criteria, not
00390                just the ones associated with the current species.
00391   Returntype : Bio::EnsEMBL::DBSQL::DBEntry
00392   Exceptions : thrown if arguments are incorrect
00393   Caller     : general, domainview
00394   Status     : Stable
00395 
00396 =cut
00397 
00398 sub fetch_all_by_name {
00399   my ( $self, $name, $dbname ) = @_;
00400 
00401   my $sql = (<<SQL);
00402   SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label, xref.version,
00403          exDB.priority, exDB.db_name, exDB.db_display_name, exDB.db_release,
00404             es.synonym, xref.info_type, xref.info_text,
00405             exDB.type, exDB.secondary_db_name, exDB.secondary_db_table,
00406             xref.description
00407     FROM    (xref, external_db exDB)
00408     LEFT JOIN external_synonym es ON
00409             es.xref_id = xref.xref_id
00410     WHERE  (xref.dbprimary_acc = ? or xref.display_label = ?)
00411     AND    xref.external_db_id = exDB.external_db_id
00412 SQL
00413 
00414   if(defined $dbname){
00415     $sql .= " AND    exDB.db_name = ?";
00416   }
00417   my $sth = $self->prepare($sql);
00418   $sth->bind_param( 1, $name, SQL_VARCHAR );
00419   $sth->bind_param( 2, $name, SQL_VARCHAR );
00420   if(defined $dbname){
00421     $sth->bind_param( 3 , $dbname,    SQL_VARCHAR );
00422   }
00423   $sth->execute();
00424 
00425 
00426   if ( !$sth->rows() && lc($dbname) eq 'interpro' ) {
00427   # This is a minor hack that means that results still come back even
00428   # when a mistake was made and no interpro accessions were loaded into
00429   # the xref table.  This has happened in the past and had the result of
00430   # breaking domainview
00431 
00432     $sth->finish();
00433     $sth = $self->prepare(
00434       "SELECT   NULL,
00435                 i.interpro_ac,
00436                 i.id,
00437                 NULL,
00438                 NULL,
00439                 'Interpro',
00440                 NULL,
00441                 NULL
00442         FROM    interpro i
00443         WHERE   i.interpro_ac = ?" );
00444 
00445     $sth->bind_param( 1, $name, SQL_VARCHAR );
00446     $sth->execute();
00447   }
00448 
00449   my %exDB;
00450   my @exDBlist;
00451   my $max_rows = 1000;
00452 
00453   while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
00454     while ( my $arrayref = shift( @{$rowcache} ) ) {
00455       my ( $dbID,                $dbprimaryId,
00456            $displayid,           $version,
00457            $priority,
00458            $dbname,              $db_display_name,
00459            $release,             $synonym,
00460            $info_type,           $info_text,
00461            $type,                $secondary_db_name,
00462            $secondary_db_table,  $description
00463       ) = @$arrayref;
00464 
00465       if ( !defined $exDB{$dbID} ) {
00466     my $entrie = 
00467           Bio::EnsEMBL::DBEntry->new(
00468                            -adaptor             => $self,
00469                            -dbID                => $dbID,
00470                            -primary_id          => $dbprimaryId,
00471                            -display_id          => $displayid,
00472                            -version             => $version,
00473                            -release             => $release,
00474                            -dbname              => $dbname,
00475                            -priority            => $priority,
00476                            -db_display_name     => $db_display_name,
00477                            -info_type           => $info_type,
00478                            -info_text           => $info_text,
00479                            -type                => $type,
00480                            -secondary_db_name   => $secondary_db_name,
00481                            -secondary_db_table  => $secondary_db_table,
00482                -description         => $description
00483           );
00484     $exDB{$dbID} = $entrie;
00485     push @exDBlist, $entrie;
00486       }
00487       if ($synonym) { $exDB{$dbID}->add_synonym($synonym) }
00488 
00489     } ## end while ( my $arrayref = shift...
00490   } ## end while ( my $rowcache = $sth...
00491 
00492   $sth->finish();
00493 
00494   return \@exDBlist;
00495 } ## end sub fetch_all_by_name
00496 
00497 
00498 
00499 =head2 fetch_by_db_accession
00500 
00501   Arg [1]    : string $dbname - The name of the database which the provided
00502                accession is for.
00503   Arg [2]    : string $accession - The accesion of the external reference to
00504                retrieve.
00505   Example    : my $xref = $dbea->fetch_by_db_accession('Interpro','IPR003439');
00506                print $xref->description(), "\n" if($xref);
00507   Description: Retrieves a DBEntry (xref) via the name of the database
00508                it is from and its primary accession in that database.
00509                Undef is returned if the xref cannot be found in the
00510                database.
00511                NOTE:  In a multi-species database, this method will
00512                return all the entries matching the search criteria, not
00513                just the ones associated with the current species.
00514   Returntype : Bio::EnsEMBL::DBSQL::DBEntry
00515   Exceptions : thrown if arguments are incorrect
00516   Caller     : general, domainview
00517   Status     : Stable
00518 
00519 =cut
00520 
00521 sub fetch_by_db_accession {
00522   my ( $self, $dbname, $accession ) = @_;
00523 
00524   my $sth = $self->prepare(
00525     "SELECT xref.xref_id,
00526             xref.dbprimary_acc,
00527             xref.display_label,
00528             xref.version,
00529             exDB.priority,
00530             exDB.db_name,
00531             exDB.db_display_name,
00532             exDB.db_release,
00533             es.synonym,
00534             xref.info_type,
00535             xref.info_text,
00536             exDB.type,
00537             exDB.secondary_db_name,
00538             exDB.secondary_db_table,
00539             xref.description
00540     FROM    (xref, external_db exDB)
00541     LEFT JOIN external_synonym es ON
00542             es.xref_id = xref.xref_id
00543     WHERE  xref.dbprimary_acc = ?
00544     AND    exDB.db_name = ?
00545     AND    xref.external_db_id = exDB.external_db_id" );
00546 
00547   $sth->bind_param( 1, $accession, SQL_VARCHAR );
00548   $sth->bind_param( 2, $dbname,    SQL_VARCHAR );
00549   $sth->execute();
00550 
00551   if ( !$sth->rows() && lc($dbname) eq 'interpro' ) {
00552   # This is a minor hack that means that results still come back even
00553   # when a mistake was made and no interpro accessions were loaded into
00554   # the xref table.  This has happened in the past and had the result of
00555   # breaking domainview
00556 
00557     $sth->finish();
00558     $sth = $self->prepare(
00559       "SELECT   NULL,
00560                 i.interpro_ac,
00561                 i.id,
00562                 NULL,
00563                 NULL,
00564                 'Interpro',
00565                 NULL,
00566                 NULL
00567         FROM    interpro i
00568         WHERE   i.interpro_ac = ?" );
00569 
00570     $sth->bind_param( 1, $accession, SQL_VARCHAR );
00571     $sth->execute();
00572   }
00573 
00574   my $exDB;
00575 
00576   my $max_rows = 1000;
00577 
00578   while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
00579     while ( my $arrayref = shift( @{$rowcache} ) ) {
00580       my ( $dbID,                $dbprimaryId,
00581            $displayid,           $version,
00582            $priority,
00583            $dbname,              $db_display_name,
00584            $release,             $synonym,
00585            $info_type,           $info_text,
00586            $type,                $secondary_db_name,
00587            $secondary_db_table,  $description
00588       ) = @$arrayref;
00589 
00590       if ( !defined($exDB) ) {
00591         $exDB =
00592           Bio::EnsEMBL::DBEntry->new(
00593                            -adaptor             => $self,
00594                            -dbID                => $dbID,
00595                            -primary_id          => $dbprimaryId,
00596                            -display_id          => $displayid,
00597                            -version             => $version,
00598                            -release             => $release,
00599                            -dbname              => $dbname,
00600                            -priority            => $priority,
00601                            -db_display_name     => $db_display_name,
00602                            -info_type           => $info_type,
00603                            -info_text           => $info_text,
00604                            -type                => $type,
00605                            -secondary_db_name   => $secondary_db_name,
00606                            -secondary_db_table  => $secondary_db_table,
00607                -description         => $description
00608           );
00609 
00610 
00611       }
00612 
00613       if ($synonym) { $exDB->add_synonym($synonym) }
00614 
00615     } ## end while ( my $arrayref = shift...
00616   } ## end while ( my $rowcache = $sth...
00617 
00618   $sth->finish();
00619 
00620   return $exDB;
00621 } ## end sub fetch_by_db_accession
00622 
00623 
00624 =head2 store
00625 
00626   Arg [1]    : Bio::EnsEMBL::DBEntry $exObj
00627                The DBEntry (xref) to be stored
00628   Arg [2]    : Int $ensID
00629                The dbID of an EnsEMBL object to associate with this external
00630                database entry
00631   Arg [3]    : string $ensType ('Transcript', 'Translation', 'Gene')
00632                The type of EnsEMBL object that this external database entry is
00633                being associated with.
00634   Arg [4]    : boolean $ignore_release
00635                If unset or zero, will require that the release string
00636                of the DBEntry object is identical to the release of the
00637                external database.  If set and non-zero, will ignore the
00638                release information.
00639   Example    : $dbea->store($db_entry, $transcript_id, 'Transcript');
00640   Description: Stores a reference to an external database (if it is not stored
00641                already) and associates an EnsEMBL object of a specified type
00642                with the external identifier.
00643   Returntype : int - the dbID of the newly created external refernce
00644   Exceptions : thrown when invalid dbID is passed to this method
00645   Caller     : scripts which load Xrefs and ObjectXrefs, etc. into Ensembl
00646   Status     : Stable
00647 
00648 =cut
00649 
00650 sub store {
00651   my ( $self, $exObj, $ensID, $ensType, $ignore_release ) = @_;
00652 
00653   my $dbJustInserted;
00654 
00655   #
00656   # backwards compatibility check:
00657   # check if $ensID is an object; if so, use $obj->dbID
00658   #
00659   my $ensembl_id;
00660 
00661 
00662   if ( defined($ensID) ) {
00663     if ( $ensID =~ /^\d+$/ ) {
00664       $ensembl_id = $ensID;
00665     } elsif (    ref($ensID) eq 'Bio::EnsEMBL::Gene'
00666               or ref($ensID) eq 'Bio::EnsEMBL::Transcript'
00667               or ref($ensID) eq 'Bio::EnsEMBL::Translation' 
00668               or ref($ensID) eq 'Bio::EnsEMBL::OperonTranscript'
00669               or ref($ensID) eq 'Bio::EnsEMBL::Operon' 
00670               )
00671     {
00672       warning(   "You should pass DBEntryAdaptor->store() "
00673                . "a dbID rather than an ensembl object "
00674                . "to store the xref on" );
00675 
00676       if ( defined( $ensID->dbID() ) ) {
00677         $ensembl_id = $ensID->dbID();
00678       } else {
00679         throw( sprintf( "%s %s doesn't have a dbID, can't store xref",
00680                         $ensType, $ensID->display_id() ) );
00681       }
00682     } else {
00683       throw("Invalid dbID passed to DBEntryAdaptor->store()");
00684     }
00685   }
00686 
00687   #
00688   # Check for the existance of the external_db, throw if it does not exist
00689   #
00690 
00691   my ($dbRef, $release_clause);
00692 
00693   if ( !$ignore_release ) {
00694 
00695     if(defined $exObj->release()){
00696       $release_clause = " AND db_release = ?";
00697     }
00698     else{
00699       $release_clause = " AND db_release is NULL";
00700     }
00701 
00702 
00703     my $sth = $self->prepare( "
00704      SELECT external_db_id
00705        FROM external_db
00706       WHERE db_name    = ?
00707         $release_clause" );
00708 
00709     $sth->bind_param( 1, $exObj->dbname(),  SQL_VARCHAR );
00710     $sth->bind_param( 2, $exObj->release(), SQL_VARCHAR ) if defined $exObj->release();
00711     $sth->execute();
00712 
00713     ($dbRef) = $sth->fetchrow_array();
00714     $sth->finish();
00715 
00716     if ( !$dbRef ) {
00717       throw(
00718              sprintf( "external_db [%s] release [%s] does not exist",
00719                       $exObj->dbname(), $exObj->release() ) );
00720     }
00721   } else {
00722 
00723     my $sth = $self->prepare( "
00724      SELECT external_db_id
00725        FROM external_db
00726       WHERE db_name = ? " );
00727 
00728     $sth->bind_param( 1, $exObj->dbname(), SQL_VARCHAR );
00729 
00730     $sth->execute();
00731 
00732     ($dbRef) = $sth->fetchrow_array();
00733 
00734     if ( !$dbRef ) {
00735       throw(
00736           sprintf( "external_db [%s] does not exist", $exObj->dbname() )
00737       );
00738     }
00739   }
00740 
00741   #
00742   # Check for the existance of the external reference, add it if not present
00743   #
00744 
00745   my $sql =  "SELECT xref_id FROM xref
00746                 WHERE external_db_id = ?
00747                   AND dbprimary_acc  = ?
00748                   AND version        = ?";
00749 
00750   if(defined $exObj->info_type){
00751     $sql .= " AND  info_type      = ?";
00752   }
00753   else{
00754     $sql .= " AND info_type is null";
00755   }
00756 
00757   if(defined $exObj->info_text){
00758     $sql .= " AND  info_text      = ?";
00759   }
00760   else{
00761     $sql .= " AND info_text is null";
00762   }
00763 
00764   my $sth = $self->prepare($sql);
00765 
00766   $sth->bind_param(1,$dbRef,SQL_INTEGER);
00767   $sth->bind_param(2,$exObj->primary_id,SQL_VARCHAR);
00768   $sth->bind_param(3,$exObj->version,SQL_VARCHAR);
00769 
00770   my $i = 4;
00771   if(defined $exObj->info_type){
00772     $sth->bind_param($i++,$exObj->info_type,SQL_VARCHAR);
00773   }
00774   if(defined $exObj->info_text){
00775     $sth->bind_param($i++,$exObj->info_text,SQL_VARCHAR);
00776   }
00777   $sth->execute();
00778 
00779   my ($dbX) = $sth->fetchrow_array();
00780 
00781   $sth->finish();
00782   if(!$dbX) {
00783     if(!$exObj->primary_id()) {
00784       throw("DBEntry cannot be stored without a primary_id attribute.");
00785     }
00786 
00787     #
00788     # store the new xref
00789     #
00790     $sth = $self->prepare( "
00791        INSERT ignore INTO xref
00792        SET dbprimary_acc = ?,
00793            display_label = ?,
00794            version = ?,
00795            description = ?,
00796            external_db_id = ?,
00797            info_type = ?,
00798            info_text = ?");
00799     $sth->bind_param(1, $exObj->primary_id,SQL_VARCHAR);
00800     $sth->bind_param(2, $exObj->display_id,SQL_VARCHAR);
00801     $sth->bind_param(3, $exObj->version,SQL_VARCHAR);
00802     $sth->bind_param(4, $exObj->description,SQL_VARCHAR);
00803     $sth->bind_param(5, $dbRef,SQL_INTEGER);
00804     $sth->bind_param(6, $exObj->info_type, SQL_VARCHAR);
00805     $sth->bind_param(7, $exObj->info_text, SQL_VARCHAR);
00806 
00807     $sth->execute();
00808 
00809     $dbX = $sth->{'mysql_insertid'};
00810     $exObj->dbID($dbX);
00811     $sth->finish();
00812     #
00813     # store the synonyms for the new xref
00814     #
00815     my $synonym_check_sth = $self->prepare(
00816               "SELECT xref_id, synonym
00817                FROM external_synonym
00818                WHERE xref_id = ?
00819                AND synonym = ?");
00820 
00821     my $synonym_store_sth = $self->prepare(
00822         "INSERT ignore INTO external_synonym
00823          SET xref_id = ?, synonym = ?");
00824 
00825     my $synonyms = $exObj->get_all_synonyms();
00826     foreach my $syn ( @$synonyms ) {
00827     $synonym_check_sth->bind_param(1,$dbX,SQL_INTEGER);
00828     $synonym_check_sth->bind_param(2,$syn,SQL_VARCHAR);
00829     $synonym_check_sth->execute();
00830       my ($dbSyn) = $synonym_check_sth->fetchrow_array();
00831     $synonym_store_sth->bind_param(1,$dbX,SQL_INTEGER);
00832     $synonym_store_sth->bind_param(2,$syn,SQL_VARCHAR);
00833     $synonym_store_sth->execute() if(!$dbSyn);
00834     }
00835     $synonym_check_sth->finish();
00836     $synonym_store_sth->finish();
00837   } elsif (! defined( $exObj->dbID() ) ) {
00838     $exObj->dbID($dbX);
00839   }
00840 
00841   if ( defined($ensembl_id) ) {
00842     #
00843     # check if the object mapping was already stored
00844     #
00845     $sth = $self->prepare(
00846       qq(
00847 SELECT  xref_id
00848 FROM    object_xref
00849 WHERE   xref_id = ?
00850   AND   ensembl_object_type = ?
00851   AND   ensembl_id = ?
00852   AND   (   linkage_annotation = ?
00853   OR        linkage_annotation IS NULL  )) );
00854 
00855     $sth->bind_param( 1, $dbX,                         SQL_INTEGER );
00856     $sth->bind_param( 2, $ensType,                     SQL_VARCHAR );
00857     $sth->bind_param( 3, $ensembl_id,                  SQL_INTEGER );
00858     $sth->bind_param( 4, $exObj->linkage_annotation(), SQL_VARCHAR );
00859 
00860     $sth->execute();
00861 
00862     my ($tst) = $sth->fetchrow_array();
00863 
00864     $sth->finish();
00865 
00866     if ( !$tst ) {
00867       #
00868       # Store the reference to the internal ensembl object
00869       #
00870       my $analysis_id;
00871       if ( $exObj->analysis() ) {
00872         $analysis_id =
00873           $self->db()->get_AnalysisAdaptor->store( $exObj->analysis() );
00874       } else {
00875         $analysis_id = undef;
00876       }
00877 
00878       $sth = $self->prepare(
00879         qq(
00880 INSERT INTO object_xref
00881   SET   xref_id = ?,
00882         ensembl_object_type = ?,
00883         ensembl_id = ?,
00884         linkage_annotation = ?,
00885         analysis_id = ? ) );
00886 
00887       $sth->bind_param( 1, $dbX,                         SQL_INTEGER );
00888       $sth->bind_param( 2, $ensType,                     SQL_VARCHAR );
00889       $sth->bind_param( 3, $ensembl_id,                  SQL_INTEGER );
00890       $sth->bind_param( 4, $exObj->linkage_annotation(), SQL_VARCHAR );
00891       $sth->bind_param( 5, $analysis_id,                 SQL_INTEGER );
00892 
00893       $sth->execute();
00894       $exObj->adaptor($self);
00895       my $Xidt = $sth->{'mysql_insertid'};
00896 
00897       #
00898       # If this is an IdentityXref need to store in that table too
00899       # If its OntologyXref add the linkage type to ontology_xref table
00900       #
00901       if ( $exObj->isa('Bio::EnsEMBL::IdentityXref') ) {
00902         $sth = $self->prepare( "
00903              INSERT ignore INTO identity_xref
00904              SET object_xref_id = ?,
00905              xref_identity = ?,
00906              ensembl_identity = ?,
00907              xref_start = ?,
00908              xref_end   = ?,
00909              ensembl_start = ?,
00910              ensembl_end = ?,
00911              cigar_line = ?,
00912              score = ?,
00913              evalue = ?" );
00914         $sth->bind_param( 1, $Xidt,                    SQL_INTEGER );
00915         $sth->bind_param( 2, $exObj->xref_identity,    SQL_INTEGER );
00916         $sth->bind_param( 3, $exObj->ensembl_identity, SQL_INTEGER );
00917         $sth->bind_param( 4, $exObj->xref_start,       SQL_INTEGER );
00918         $sth->bind_param( 5, $exObj->xref_end,         SQL_INTEGER );
00919         $sth->bind_param( 6, $exObj->ensembl_start,    SQL_INTEGER );
00920         $sth->bind_param( 7, $exObj->ensembl_end,      SQL_INTEGER );
00921         $sth->bind_param( 8,  $exObj->cigar_line, SQL_LONGVARCHAR );
00922         $sth->bind_param( 9,  $exObj->score,      SQL_DOUBLE );
00923         $sth->bind_param( 10, $exObj->evalue,     SQL_DOUBLE );
00924         $sth->execute();
00925       } elsif ( $exObj->isa('Bio::EnsEMBL::OntologyXref') ) {
00926         $sth = $self->prepare( "
00927              INSERT ignore INTO ontology_xref
00928                 SET object_xref_id = ?,
00929                     source_xref_id = ?,
00930                     linkage_type = ? " );
00931         foreach my $info ( @{ $exObj->get_all_linkage_info() } ) {
00932           my ( $lt, $sourceXref ) = @{$info};
00933           my $sourceXid = undef;
00934           if ($sourceXref) {
00935             $sourceXref->is_stored( $self->dbc )
00936               || $self->store($sourceXref);
00937             $sourceXid = $sourceXref->dbID;
00938           }
00939           $sth->bind_param( 1, $Xidt,      SQL_INTEGER );
00940           $sth->bind_param( 2, $sourceXid, SQL_INTEGER );
00941           $sth->bind_param( 3, $lt,        SQL_VARCHAR );
00942           $sth->execute();
00943         }
00944       }
00945     } ## end if ( !$tst )
00946   } ## end if ( defined($ensembl_id...))
00947 
00948   return $dbX;
00949 }
00950 
00951 
00952 =head2 exists
00953 
00954   Arg [1]    : Bio::EnsEMBL::DBEntry $dbe
00955   Example    : if($dbID = $db_entry_adaptor->exists($dbe)) { do stuff; }
00956   Description: Returns the db id of this DBEntry if it exists in this database
00957                otherwise returns undef.  Exists is defined as an entry with
00958                the same external_db and display_id
00959   Returntype : int
00960   Exceptions : thrown on incorrect args
00961   Caller     : GeneAdaptor::store, TranscriptAdaptor::store
00962   Status     : Stable
00963 
00964 =cut
00965 
00966 sub exists {
00967   my ($self, $dbe) = @_ ;
00968 
00969   unless($dbe && ref $dbe && $dbe->isa('Bio::EnsEMBL::DBEntry')) {
00970     throw("arg must be a Bio::EnsEMBL::DBEntry not [$dbe]");
00971   }
00972 
00973   my $sth = $self->prepare('SELECT x.xref_id
00974                             FROM   xref x, external_db xdb
00975                             WHERE  x.external_db_id = xdb.external_db_id
00976                             AND    x.display_label = ?
00977                             AND    xdb.db_name = ?
00978                             AND    x.dbprimary_acc = ?');
00979 
00980   $sth->bind_param(1,$dbe->display_id,SQL_VARCHAR);
00981   $sth->bind_param(2,$dbe->dbname,SQL_VARCHAR);
00982   $sth->bind_param(3,$dbe->primary_id,SQL_VARCHAR);
00983   $sth->execute();
00984 
00985   my ($dbID) = $sth->fetchrow_array;
00986 
00987   $sth->finish;
00988 
00989   return $dbID;
00990 }
00991 
00992 
00993 =head2 fetch_all_by_Gene
00994 
00995   Arg [1]    : Bio::EnsEMBL::Gene $gene
00996                (The gene to retrieve DBEntries for)
00997   Arg [2]    : optional external database name
00998   Arg [3]    : optional external_db type
00999   Example    : @db_entries = @{$db_entry_adaptor->fetch_by_Gene($gene)};
01000   Description: This returns a list of DBEntries associated with this gene.
01001                Note that this method was changed in release 15.  Previously
01002                it set the DBLinks attribute of the gene passed in to contain
01003                all of the gene, transcript, and translation xrefs associated
01004                with this gene.
01005   Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
01006                there is mapping data, or OntologyXref if there is linkage data.
01007   Exceptions : thows if gene object not passed
01008   Caller     : Bio::EnsEMBL::Gene
01009   Status     : Stable
01010 
01011 =cut
01012 
01013 sub fetch_all_by_Gene {
01014   my ( $self, $gene, $ex_db_reg, $exdb_type ) = @_;
01015 
01016   if(!ref($gene) || !$gene->isa('Bio::EnsEMBL::Gene')) {
01017     throw("Bio::EnsEMBL::Gene argument expected.");
01018   }
01019 
01020   return $self->_fetch_by_object_type($gene->dbID(), 'Gene', $ex_db_reg, $exdb_type);
01021 }
01022 
01023 sub fetch_all_by_Operon {
01024   my ( $self, $gene, $ex_db_reg, $exdb_type ) = @_;
01025 
01026   if(!ref($gene) || !$gene->isa('Bio::EnsEMBL::Operon')) {
01027     throw("Bio::EnsEMBL::Operon argument expected.");
01028   }
01029 
01030   return $self->_fetch_by_object_type($gene->dbID(), 'Operon', $ex_db_reg, $exdb_type);
01031 }
01032 
01033 
01034 =head2 fetch_all_by_Transcript
01035 
01036   Arg [1]    : Bio::EnsEMBL::Transcript
01037   Arg [2]    : optional external database name
01038   Arg [3]    : optional external_db type
01039   Example    : @db_entries = @{$db_entry_adaptor->fetch_by_Gene($trans)};
01040   Description: This returns a list of DBEntries associated with this
01041                transcript. Note that this method was changed in release 15.
01042                Previously it set the DBLinks attribute of the gene passed in
01043                to contain all of the gene, transcript, and translation xrefs
01044                associated with this gene.
01045   Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
01046                there is mapping data, or OntologyXref if there is linkage data.
01047   Exceptions : throes if transcript argument not passed
01048   Caller     : Bio::EnsEMBL::Gene
01049   Status     : Stable
01050 
01051 =cut
01052 
01053 sub fetch_all_by_Transcript {
01054   my ( $self, $trans, $ex_db_reg, $exdb_type ) = @_;
01055 
01056   if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Transcript')) {
01057     throw("Bio::EnsEMBL::Transcript argument expected.");
01058   }
01059 
01060   return $self->_fetch_by_object_type( $trans->dbID(), 'Transcript', $ex_db_reg, $exdb_type);
01061 }
01062 
01063 
01064 =head2 fetch_all_by_Translation
01065 
01066   Arg [1]    : Bio::EnsEMBL::Translation $trans
01067                (The translation to fetch database entries for)
01068   Arg [2]    : optional external database name
01069   Arg [3]    : optional externaldb type
01070   Example    : @db_entries = @{$db_entry_adptr->fetch_all_by_Translation($trans)};
01071   Description: Retrieves external database entries for an EnsEMBL translation
01072   Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
01073                there is mapping data, or OntologyXref if there is linkage data.
01074   Exceptions : throws if translation object not passed
01075   Caller     : general
01076   Status     : Stable
01077 
01078 =cut
01079 
01080 sub fetch_all_by_Translation {
01081   my ( $self, $trans, $ex_db_reg, $exdb_type ) = @_;
01082 
01083   if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Translation')) {
01084     throw('Bio::EnsEMBL::Translation argument expected.');
01085   }
01086   if( ! $trans->dbID ){
01087     warning( "Cannot fetch_all_by_Translation without a dbID" );
01088     return [];
01089   }
01090 
01091   return $self->_fetch_by_object_type( $trans->dbID(), 'Translation', $ex_db_reg, $exdb_type );
01092 }
01093 
01094 
01095 
01096 =head2 remove_from_object
01097 
01098   Arg [1]    : Bio::EnsEMBL::DBEntry $dbe - The external reference which
01099                is to be disassociated from an ensembl object.
01100   Arg [2]    : Bio::EnsEMBL::Storable $object - The ensembl object the
01101                external reference is to be disassociated from
01102   Arg [3]    : string $object_type - The type of the ensembl object.
01103                E.g. 'Gene', 'Transcript', 'Translation'
01104   Example    :
01105                # remove all dbentries from this translation
01106                foreach my $dbe (@{$translation->get_all_DBEntries()}) {
01107                  $dbe_adaptor->remove($dbe, $translation, 'Translation');
01108                }
01109   Description: Removes an association between an ensembl object and a
01110                DBEntry (xref).  This does not remove the actual xref from
01111                the database, only its linkage to the ensembl object.
01112   Returntype : none
01113   Exceptions : Throw on incorrect arguments.
01114                Warning if object or dbentry is not stored in this database.
01115   Caller     : TranscriptAdaptor::remove, GeneAdaptor::remove,
01116                TranslationAdaptor::remove
01117   Status     : Stable
01118 
01119 =cut
01120 
01121 sub remove_from_object {
01122   my $self = shift;
01123   my $dbe  = shift;
01124   my $object = shift;
01125   my $object_type = shift;
01126 
01127   if(!ref($dbe) || !$dbe->isa('Bio::EnsEMBL::DBEntry')) {
01128     throw("Bio::EnsEMBL::DBEntry argument expected.");
01129   }
01130 
01131   if(!ref($object) || !$dbe->isa('Bio::EnsEMBL::Storable')) {
01132     throw("Bio::EnsEMBL::Storable argument expected.");
01133   }
01134 
01135   if(!$object_type) {
01136     throw("object_type string argument expected.");
01137   }
01138 
01139   # make sure both the dbentry and the object it is allegedly linked to
01140   # are stored in this database
01141 
01142   if(!$object->is_stored($self->db())) {
01143     warning("Cannot remove DBEntries for $object_type " . $object->dbID() .
01144             ". Object is not stored in this database.");
01145     return;
01146   }
01147 
01148   if(!$dbe->is_stored($self->db())) {
01149     warning("Cannot remove DBEntry ".$dbe->dbID() . ". Is not stored " .
01150             "in this database.");
01151     return;
01152   }
01153 
01154   # obtain the identifier of the link from the object_xref table
01155   #No need to compare linkage_annotation here
01156   my $sth = $self->prepare
01157     ("SELECT ox.object_xref_id " .
01158      "FROM   object_xref ox ".
01159      "WHERE  ox.xref_id = ? " .
01160      "AND    ox.ensembl_id = ? " .
01161      "AND    ox.ensembl_object_type = ?");
01162   $sth->bind_param(1,$dbe->dbID,SQL_INTEGER);
01163   $sth->bind_param(2,$object->dbID,SQL_INTEGER);
01164   $sth->bind_param(3,$object_type,SQL_VARCHAR);
01165   $sth->execute();
01166 
01167   if(!$sth->rows() == 1) {
01168     $sth->finish();
01169     return;
01170   }
01171 
01172   my ($ox_id) = $sth->fetchrow_array();
01173   $sth->finish();
01174 
01175   # delete from the tables which contain additional linkage information
01176 
01177   $sth = $self->prepare("DELETE FROM ontology_xref WHERE object_xref_id = ?");
01178   $sth->bind_param(1,$ox_id,SQL_INTEGER);
01179   $sth->execute();
01180   $sth->finish();
01181 
01182   $sth = $self->prepare("DELETE FROM identity_xref WHERE object_xref_id = ?");
01183   $sth->bind_param(1,$ox_id,SQL_INTEGER);
01184   $sth->execute();
01185   $sth->finish();
01186 
01187   # delete the actual linkage itself
01188   $sth = $self->prepare("DELETE FROM object_xref WHERE object_xref_id = ?");
01189   $sth->bind_param(1,$ox_id,SQL_INTEGER);
01190   $sth->execute();
01191   $sth->finish();
01192 
01193   return;
01194 }
01195 
01196 
01197 =head2 _fetch_by_object_type
01198 
01199   Arg [1]    : string $ensID
01200   Arg [2]    : string $ensType (object type to be returned)
01201   Arg [3]    : optional $exdbname (external database name)
01202                (may be an SQL pattern containing '%' which matches any
01203                number of characters)
01204   Arg [4]    : optional $exdb_type (external database type)
01205                (may be an SQL pattern containing '%' which matches any
01206                number of characters)
01207   Example    : $self->_fetch_by_object_type( $translation_id, 'Translation' )
01208   Description: Fetches DBEntry by Object type
01209                NOTE:  In a multi-species database, this method will
01210                return all the entries matching the search criteria, not
01211                just the ones associated with the current species.
01212 
01213 
01214   Returntype : arrayref of DBEntry objects; may be of type IdentityXref if
01215                there is mapping data, or OntologyXref if there is linkage data.
01216   Exceptions : none
01217   Caller     : fetch_all_by_Gene
01218                fetch_all_by_Translation
01219                fetch_all_by_Transcript
01220   Status     : Stable
01221 
01222 =cut
01223 
01224 sub _fetch_by_object_type {
01225   my ( $self, $ensID, $ensType, $exdbname, $exdb_type ) = @_;
01226 
01227   my @out;
01228 
01229   if ( !defined($ensID) ) {
01230     throw("Can't fetch_by_EnsObject_type without an object");
01231   }
01232 
01233   if ( !defined($ensType) ) {
01234     throw("Can't fetch_by_EnsObject_type without a type");
01235   }
01236 
01237   #  my $sth = $self->prepare("
01238   my $sql = (<<SSQL);
01239     SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label, xref.version,
01240            exDB.priority,
01241            exDB.db_name, exDB.db_release, exDB.status, exDB.db_display_name,
01242            exDB.secondary_db_name, exDB.secondary_db_table,
01243            oxr.object_xref_id,
01244            es.synonym,
01245            idt.xref_identity, idt.ensembl_identity, idt.xref_start,
01246            idt.xref_end, idt.ensembl_start, idt.ensembl_end,
01247            idt.cigar_line, idt.score, idt.evalue, oxr.analysis_id,
01248            gx.linkage_type,
01249            xref.info_type, xref.info_text, exDB.type, gx.source_xref_id,
01250            oxr.linkage_annotation, xref.description
01251     FROM   (xref xref, external_db exDB, object_xref oxr)
01252     LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
01253     LEFT JOIN identity_xref idt on idt.object_xref_id = oxr.object_xref_id
01254     LEFT JOIN ontology_xref gx on gx.object_xref_id = oxr.object_xref_id
01255     WHERE  xref.xref_id = oxr.xref_id
01256       AND  xref.external_db_id = exDB.external_db_id
01257       AND  oxr.ensembl_id = ?
01258       AND  oxr.ensembl_object_type = ?
01259 SSQL
01260 
01261   if ( defined($exdbname) ) {
01262     if ( index( $exdbname, '%' ) != -1 ) {
01263       $sql .= " AND exDB.db_name LIKE "
01264         . $self->dbc()->db_handle()->quote( $exdbname, SQL_VARCHAR );
01265     } else {
01266       $sql .= " AND exDB.db_name = "
01267         . $self->dbc()->db_handle()->quote( $exdbname, SQL_VARCHAR );
01268     }
01269   }
01270 
01271   if ( defined($exdb_type) ) {
01272     if ( index( $exdb_type, '%' ) != -1 ) {
01273       $sql .= " AND exDB.type LIKE "
01274         . $self->dbc()->db_handle()->quote( $exdb_type, SQL_VARCHAR );
01275     } else {
01276       $sql .= " AND exDB.type = "
01277         . $self->dbc()->db_handle()->quote( $exdb_type, SQL_VARCHAR );
01278     }
01279   }
01280 
01281   my $sth = $self->prepare($sql);
01282 
01283   $sth->bind_param( 1, $ensID,   SQL_INTEGER );
01284   $sth->bind_param( 2, $ensType, SQL_VARCHAR );
01285   $sth->execute();
01286 
01287   my ( %seen, %linkage_types, %synonyms );
01288 
01289   my $max_rows = 1000;
01290 
01291   while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
01292     while ( my $arrRef = shift( @{$rowcache} ) ) {
01293       my ( $refID,                  $dbprimaryId,
01294            $displayid,              $version,
01295            $priority,
01296            $dbname,                 $release,
01297            $exDB_status,            $exDB_db_display_name,
01298            $exDB_secondary_db_name, $exDB_secondary_db_table,
01299            $objid,                  $synonym,
01300            $xrefid,                 $ensemblid,
01301            $xref_start,             $xref_end,
01302            $ensembl_start,          $ensembl_end,
01303            $cigar_line,             $score,
01304            $evalue,                 $analysis_id,
01305            $linkage_type,           $info_type,
01306            $info_text,              $type,
01307            $source_xref_id,         $link_annotation,
01308        $description
01309       ) = @$arrRef;
01310 
01311       my $linkage_key =
01312         ( $linkage_type || '' ) . ( $source_xref_id || '' );
01313 
01314 
01315       my $analysis = undef;
01316       if ( defined($analysis_id) ) {
01317     $analysis =
01318       $self->db()->get_AnalysisAdaptor()->fetch_by_dbID($analysis_id);
01319       }
01320 
01321       my %obj_hash = ( 'adaptor'            => $self,
01322                        'dbID'               => $refID,
01323                        'primary_id'         => $dbprimaryId,
01324                        'display_id'         => $displayid,
01325                        'version'            => $version,
01326                        'release'            => $release,
01327                        'info_type'          => $info_type,
01328                        'info_text'          => $info_text,
01329                        'type'               => $type,
01330                        'secondary_db_name'  => $exDB_secondary_db_name,
01331                        'secondary_db_table' => $exDB_secondary_db_table,
01332                        'dbname'             => $dbname,
01333                        'description'        => $description,
01334                        'linkage_annotation' => $link_annotation,
01335                        'analysis'           => $analysis,
01336                'ensembl_object_type' => $ensType,
01337                'ensembl_id'          => $ensID );
01338 
01339       # Using an outer join on the synonyms as well as on identity_xref,
01340       # we now have to filter out the duplicates (see v.1.18 for
01341       # original). Since there is at most one identity_xref row per
01342       # xref, this is easy enough; all the 'extra' bits are synonyms.
01343       my $source_xref;
01344       if ( !$seen{$refID} ) {
01345     
01346     my $exDB;
01347         if ( ( defined($xrefid) ) ) {  # an xref with similarity scores
01348           $exDB = Bio::EnsEMBL::IdentityXref->new_fast( \%obj_hash );
01349           $exDB->xref_identity($xrefid);
01350           $exDB->ensembl_identity($ensemblid);
01351 
01352           $exDB->cigar_line($cigar_line);
01353           $exDB->xref_start($xref_start);
01354           $exDB->xref_end($xref_end); # was not here before 14th Jan 2009 ????
01355           $exDB->ensembl_start($ensembl_start);
01356           $exDB->ensembl_end($ensembl_end);
01357           $exDB->score($score);
01358           $exDB->evalue($evalue);
01359 
01360         } elsif ( defined $linkage_type && $linkage_type ne "" ) {
01361           $exDB = Bio::EnsEMBL::OntologyXref->new_fast( \%obj_hash );
01362           $source_xref = ( defined($source_xref_id)
01363                               ? $self->fetch_by_dbID($source_xref_id)
01364                               : undef );
01365           $exDB->add_linkage_type( $linkage_type, $source_xref || () );
01366           $linkage_types{$refID}->{$linkage_key} = 1;
01367 
01368         } else {
01369           $exDB = Bio::EnsEMBL::DBEntry->new_fast( \%obj_hash );
01370         }
01371 
01372         if ( defined($exDB_status) ) { $exDB->status($exDB_status) }
01373 
01374         $exDB->priority($priority);
01375         $exDB->db_display_name($exDB_db_display_name);
01376 
01377         push( @out, $exDB );
01378         $seen{$refID} = $exDB;
01379 
01380       } ## end if ( !$seen{$refID} )
01381 
01382       # $exDB still points to the same xref, so we can keep adding GO
01383       # evidence tags or synonyms.
01384 
01385       if ( defined($synonym) && !$synonyms{$refID}->{$synonym} ) {
01386         if ( defined($synonym) ) {
01387           $seen{$refID}->add_synonym($synonym);
01388         }
01389         $synonyms{$refID}->{$synonym} = 1;
01390       }
01391 
01392       if (    defined($linkage_type)
01393            && $linkage_type ne ""
01394            && !$linkage_types{$refID}->{$linkage_key} )
01395       {
01396         $source_xref = ( defined($source_xref_id)
01397                             ? $self->fetch_by_dbID($source_xref_id)
01398                             : undef );
01399         $seen{$refID}
01400           ->add_linkage_type( $linkage_type, $source_xref || () );
01401         $linkage_types{$refID}->{$linkage_key} = 1;
01402       }
01403     } ## end while ( my $arrRef = shift...
01404   } ## end while ( my $rowcache = $sth...
01405 
01406   return \@out;
01407 } ## end sub _fetch_by_object_type
01408 
01409 =head2 list_gene_ids_by_external_db_id
01410 
01411   Arg [1]    : string $external_id
01412   Example    : @gene_ids = $dbea->list_gene_ids_by_external_db_id(1020);
01413   Description: Retrieve a list of geneid by an external identifier that
01414                is linked to any of the genes transcripts, translations
01415                or the gene itself.
01416                NOTE:  If more than one external identifier has the
01417                same primary accession then genes for each of these is
01418                returned.
01419                NOTE:  In a multi-species database, this method will
01420                return all the entries matching the search criteria, not
01421                just the ones associated with the current species.
01422   Returntype : list of ints
01423   Exceptions : none
01424   Caller     : unknown
01425   Status     : Stable
01426 
01427 =cut
01428 
01429 sub list_gene_ids_by_external_db_id{
01430    my ($self,$external_db_id) = @_;
01431 
01432    my %T = map { ($_, 1) }
01433        $self->_type_by_external_db_id( $external_db_id, 'Translation', 'gene' ),
01434        $self->_type_by_external_db_id( $external_db_id, 'Transcript',  'gene' ),
01435        $self->_type_by_external_db_id( $external_db_id, 'Gene' );
01436    return keys %T;
01437 }
01438 
01439 =head2 list_gene_ids_by_extids
01440 
01441   Arg [1]    : string $external_name
01442   Arg [2]    : (optional) string $external_db_name
01443   Example    : @gene_ids = $dbea->list_gene_ids_by_extids('CDPX');
01444   Description: Retrieve a list of geneid by an external identifier that is
01445                linked to  any of the genes transcripts, translations or the
01446                gene itself
01447   Returntype : list of ints
01448   Exceptions : none
01449   Caller     : unknown
01450   Status     : Stable
01451 
01452 =cut
01453 
01454 sub list_gene_ids_by_extids {
01455   my ( $self, $external_name, $external_db_name ) = @_;
01456 
01457   my %T = map { ( $_, 1 ) }
01458     $self->_type_by_external_id( $external_name, 'Translation', 'gene',
01459                                  $external_db_name ),
01460     $self->_type_by_external_id( $external_name, 'Transcript', 'gene',
01461                                  $external_db_name ),
01462     $self->_type_by_external_id( $external_name, 'Gene', undef,
01463                                  $external_db_name );
01464 
01465   return keys %T;
01466 }
01467 
01468 
01469 =head2 list_transcript_ids_by_extids
01470 
01471   Arg [1]    : string $external_name
01472   Arg [2]    : (optional) string $external_db_name
01473   Example    : @tr_ids = $dbea->list_transcript_ids_by_extids('BCRA2');
01474   Description: Retrieve a list transcript ids by an external identifier that
01475                is linked to any of the genes transcripts, translations or the
01476                gene itself
01477   Returntype : list of ints
01478   Exceptions : none
01479   Caller     : unknown
01480   Status     : Stable
01481 
01482 =cut
01483 
01484 sub list_transcript_ids_by_extids {
01485   my ( $self, $external_name, $external_db_name ) = @_;
01486 
01487   my %T = map { ( $_, 1 ) }
01488     $self->_type_by_external_id( $external_name, 'Translation',
01489                                  'transcript',   $external_db_name
01490     ),
01491     $self->_type_by_external_id( $external_name, 'Transcript', undef,
01492                                  $external_db_name );
01493 
01494   return keys %T;
01495 }
01496 
01497 
01498 =head2 list_translation_ids_by_extids
01499 
01500   Arg [1]    : string $external_name
01501   Arg [2]    : (optional) string $external_db_name
01502   Example    : @tr_ids = $dbea->list_translation_ids_by_extids('GO:0004835');
01503   Description: Gets a list of translation IDs by external display IDs
01504   Returntype : list of Ints
01505   Exceptions : none
01506   Caller     : unknown
01507   Status     : Stable
01508 
01509 =cut
01510 
01511 sub list_translation_ids_by_extids {
01512   my ( $self, $external_name, $external_db_name ) = @_;
01513 
01514   return
01515     $self->_type_by_external_id( $external_name, 'Translation', undef,
01516                                  $external_db_name );
01517 }
01518 
01519 =head2 _type_by_external_id
01520 
01521   Arg [1]    : string $name - dbprimary_acc
01522   Arg [2]    : string $ensType - ensembl_object_type
01523   Arg [3]    : (optional) string $extraType
01524   Arg [4]    : (optional) string $external_db_name
01525            other object type to be returned
01526   Example    : $self->_type_by_external_id($name, 'Translation');
01527                NOTE:  In a multi-species database, this method will
01528                return all the entries matching the search criteria, not
01529                just the ones associated with the current species.
01530                SQL wildcards can be used in the external id, 
01531                but overly generic queries (two characters) will be prevented.
01532   Description: Gets
01533   Returntype : list of dbIDs (gene_id, transcript_id, etc.)
01534   Exceptions : none
01535   Caller     : list_translation_ids_by_extids
01536                translationids_by_extids
01537            geneids_by_extids
01538   Status     : Stable
01539 
01540 =cut
01541 
01542 sub _type_by_external_id {
01543   my ( $self, $name, $ensType, $extraType, $external_db_name ) = @_;
01544 
01545   # $name has SQL wildcard support
01546   # = or LIKE put into SQL statement, and open queries like % or A% are rejected.
01547   my $comparison_operator;
01548   if ($name =~ /[_%\[]/) {
01549     $comparison_operator = "LIKE";
01550     if ($name =~ /^.?%/) {
01551       warn "External $ensType name $name is too vague and will monopolise database resources. Please use a more specific $ensType name.\n";
01552       return;
01553     }
01554   }
01555   else {
01556     $comparison_operator = "=";
01557   }
01558 
01559 
01560   my $from_sql  = '';
01561   my $where_sql = '';
01562   my $ID_sql    = 'oxr.ensembl_id';
01563 
01564   if ( defined($extraType) ) {
01565     if ( lc($extraType) eq 'translation' ) {
01566       $ID_sql = 'tl.translation_id';
01567     } else {
01568       $ID_sql = "t.${extraType}_id";
01569     }
01570 
01571     if ( lc($ensType) eq 'translation' ) {
01572       $from_sql  = 'transcript t, translation tl, ';
01573       $where_sql = qq(
01574           t.transcript_id = tl.transcript_id AND
01575           tl.translation_id = oxr.ensembl_id AND
01576           t.is_current = 1 AND
01577       );
01578     } else {
01579       $from_sql  = 'transcript t, ';
01580       $where_sql = 't.'
01581         . lc($ensType)
01582         . '_id = oxr.ensembl_id AND '
01583         . 't.is_current = 1 AND ';
01584     }
01585   }
01586 
01587   if ( lc($ensType) eq 'gene' ) {
01588     $from_sql = 'gene g, seq_region s, coord_system cs, ';
01589     $where_sql =
01590         'g.gene_id = oxr.ensembl_id AND '
01591       . 'g.is_current = 1 AND '
01592       . 'g.seq_region_id = s.seq_region_id AND '
01593       . 's.coord_system_id = cs.coord_system_id AND '
01594       . 'cs.species_id = ? AND ';
01595   } elsif ( lc($ensType) eq 'transcript' ) {
01596     $from_sql = 'transcript t, seq_region s, coord_system cs, ';
01597     $where_sql =
01598         't.transcript_id = oxr.ensembl_id AND '
01599       . 't.is_current = 1 AND '
01600       . 't.seq_region_id = s.seq_region_id AND '
01601       . 's.coord_system_id = cs.coord_system_id AND '
01602       . 'cs.species_id = ? AND ';
01603   } elsif ( lc($ensType) eq 'translation' ) {
01604     $from_sql = 'transcript t, translation tl, '
01605       . 'seq_region s, coord_system cs, ';
01606     $where_sql =
01607         't.transcript_id = tl.transcript_id AND '
01608       . 'tl.translation_id = oxr.ensembl_id AND '
01609       . 't.is_current = 1 AND '
01610       . 't.seq_region_id = s.seq_region_id AND '
01611       . 's.coord_system_id = cs.coord_system_id AND '
01612       . 'cs.species_id = ? AND ';
01613   }
01614 
01615   if ( defined($external_db_name) ) {
01616     # Involve the 'external_db' table to limit the hits to a particular
01617     # external database.
01618 
01619     $from_sql .= 'external_db xdb, ';
01620     $where_sql .=
01621         'xdb.db_name LIKE '
01622       . $self->dbc()->db_handle()->quote( $external_db_name . '%' )
01623       . ' AND xdb.external_db_id = x.external_db_id AND';
01624   }
01625 
01626   my $query1 = qq(
01627       SELECT    $ID_sql
01628       FROM      $from_sql
01629                 xref x,
01630                 object_xref oxr
01631       WHERE     $where_sql
01632                 ( x.dbprimary_acc $comparison_operator ? OR x.display_label $comparison_operator ? )
01633       AND         x.xref_id = oxr.xref_id
01634       AND         oxr.ensembl_object_type = ?
01635   );
01636 
01637   my $query2;
01638 
01639   if ( defined($external_db_name) ) {
01640     # If we are given the name of an external database, we need to join
01641     # between the 'xref' and the 'object_xref' tables on 'xref_id'.
01642 
01643     $query2 = qq(
01644       SELECT    $ID_sql
01645       FROM      $from_sql
01646                 external_synonym syn,
01647                 object_xref oxr,
01648                 xref x
01649       WHERE     $where_sql
01650                 syn.synonym $comparison_operator ?
01651       AND       syn.xref_id = oxr.xref_id
01652       AND       oxr.ensembl_object_type = ?
01653       AND       x.xref_id = oxr.xref_id);
01654 
01655   } else {
01656     # If we weren't given an external database name, we can get away
01657     # with less joins here.
01658 
01659     $query2 = qq(
01660       SELECT    $ID_sql
01661       FROM      $from_sql
01662                 external_synonym syn,
01663                 object_xref oxr
01664       WHERE     $where_sql
01665                 syn.synonym $comparison_operator ?
01666       AND       syn.xref_id = oxr.xref_id
01667       AND       oxr.ensembl_object_type = ?);
01668 
01669   }
01670 
01671   my %result;
01672 
01673   my $sth = $self->prepare($query1);
01674 
01675   $sth->bind_param( 1, $self->species_id(), SQL_INTEGER );
01676   $sth->bind_param( 2, $name,               SQL_VARCHAR );
01677   $sth->bind_param( 3, $name,               SQL_VARCHAR );
01678   $sth->bind_param( 4, $ensType,            SQL_VARCHAR );
01679   $sth->execute();
01680   my $r;
01681   while ( $r = $sth->fetchrow_array() ) { $result{$r} = 1 }
01682 
01683   $sth = $self->prepare($query2);
01684 
01685   $sth->bind_param( 1, $self->species_id(), SQL_INTEGER );
01686   $sth->bind_param( 2, $name,               SQL_VARCHAR );
01687   $sth->bind_param( 3, $ensType,            SQL_VARCHAR );
01688   $sth->execute();
01689 
01690   while ( $r = $sth->fetchrow_array() ) { $result{$r} = 1 }
01691 
01692   return keys(%result);
01693 
01694 } ## end sub _type_by_external_id
01695 
01696 =head2 _type_by_external_db_id
01697 
01698   Arg [1]    : integer $type - external_db_id
01699   Arg [2]    : string $ensType - ensembl_object_type
01700   Arg [3]    : (optional) string $extraType
01701            other object type to be returned
01702   Example    : $self->_type_by_external_db_id(1030, 'Translation');
01703   Description: Gets
01704                NOTE:  In a multi-species database, this method will
01705                return all the entries matching the search criteria, not
01706                just the ones associated with the current species.
01707   Returntype : list of dbIDs (gene_id, transcript_id, etc.)
01708   Exceptions : none
01709   Caller     : list_translation_ids_by_extids
01710                translationids_by_extids
01711            geneids_by_extids
01712   Status     : Stable
01713 
01714 =cut
01715 
01716 sub _type_by_external_db_id{
01717   my ($self, $external_db_id, $ensType, $extraType) = @_;
01718 
01719   my $from_sql = '';
01720   my $where_sql = '';
01721   my $ID_sql = "oxr.ensembl_id";
01722 
01723   if (defined $extraType) {
01724     if (lc($extraType) eq 'translation') {
01725       $ID_sql = "tl.translation_id";
01726     } else {
01727       $ID_sql = "t.${extraType}_id";
01728     }
01729 
01730     if (lc($ensType) eq 'translation') {
01731       $from_sql = 'transcript t, translation tl, ';
01732       $where_sql = qq(
01733           t.transcript_id = tl.transcript_id AND
01734           tl.translation_id = oxr.ensembl_id AND
01735           t.is_current = 1 AND
01736       );
01737     } else {
01738       $from_sql = 'transcript t, ';
01739       $where_sql = 't.'.lc($ensType).'_id = oxr.ensembl_id AND '.
01740           't.is_current = 1 AND ';
01741     }
01742   }
01743 
01744   if (lc($ensType) eq 'gene') {
01745     $from_sql = 'gene g, ';
01746     $where_sql = 'g.gene_id = oxr.ensembl_id AND g.is_current = 1 AND ';
01747   } elsif (lc($ensType) eq 'transcript') {
01748     $from_sql = 'transcript t, ';
01749     $where_sql = 't.transcript_id = oxr.ensembl_id AND t.is_current = 1 AND ';
01750   } elsif (lc($ensType) eq 'translation') {
01751     $from_sql = 'transcript t, translation tl, ';
01752     $where_sql = qq(
01753         t.transcript_id = tl.transcript_id AND
01754         tl.translation_id = oxr.ensembl_id AND
01755         t.is_current = 1 AND
01756     );
01757   }
01758 
01759   my $query =
01760     "SELECT $ID_sql
01761        FROM $from_sql xref x, object_xref oxr
01762       WHERE $where_sql x.external_db_id = ? AND
01763          x.xref_id = oxr.xref_id AND oxr.ensembl_object_type= ?";
01764 
01765   my %result;
01766 
01767   my $sth = $self->prepare($query);
01768 
01769   $sth->bind_param( 1, "$external_db_id", SQL_VARCHAR );
01770   $sth->bind_param( 2, $ensType,          SQL_VARCHAR );
01771   $sth->execute();
01772 
01773   while ( my $r = $sth->fetchrow_array() ) { $result{$r} = 1 }
01774 
01775   return keys(%result);
01776 }
01777 
01778 
01779 =head2 fetch_all_by_description
01780 
01781   Arg [1]    : string description to search for. Include % etc in this string
01782   Arg [2]    : <optional> string $dbname. Name of the database to search
01783 
01784   Example    : @canc_refs = @{$db_entry_adaptor->fetch_all_by_description("%cancer%")};
01785                @db_entries = @{$db_entry_adaptor->fetch_all_by_description("%cancer%","MIM_MORBID")};
01786   Description: Retrieves DBEntries that match the description.
01787                Optionally you can search on external databases type.
01788                NOTE:  In a multi-species database, this method will
01789                return all the entries matching the search criteria, not
01790                just the ones associated with the current species.
01791   Returntype : ref to array of Bio::EnsEMBL::DBSQL::DBEntry
01792   Exceptions : None.
01793   Caller     : General
01794   Status     : At Risk
01795 
01796 =cut
01797 
01798 sub fetch_all_by_description {
01799   my ( $self, $description, $dbname ) = @_;
01800 
01801   my @results = ();
01802 
01803   my $sql =
01804     "SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label,
01805            xref.version,
01806            exDB.priority,
01807            exDB.db_name, exDB.db_display_name, exDB.db_release, es.synonym,
01808            xref.info_type, xref.info_text, exDB.type, exDB.secondary_db_name,
01809            exDB.secondary_db_table, xref.description
01810     FROM   (xref, external_db exDB)
01811     LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
01812     WHERE  xref.description like ?
01813     AND    xref.external_db_id = exDB.external_db_id";
01814 
01815   if ( defined($dbname) ) { $sql .= " AND exDB.db_name = ? " }
01816 
01817   my $sth = $self->prepare($sql);
01818 
01819   $sth->bind_param( 1, $description, SQL_VARCHAR );
01820 
01821   if ( defined($dbname) ) {
01822     $sth->bind_param( 2, $dbname, SQL_VARCHAR );
01823   }
01824 
01825   $sth->execute();
01826 
01827   my $max_rows = 1000;
01828 
01829   while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
01830     while ( my $arrayref = shift( @{$rowcache} ) ) {
01831       my ( $dbID,                $dbprimaryId,
01832            $displayid,           $version,
01833            $priority,
01834            $ex_dbname,           $db_display_name,
01835            $release,             $synonym,
01836            $info_type,           $info_text,
01837            $type,                $secondary_db_name,
01838            $secondary_db_table,  $description
01839       ) = @$arrayref;
01840 
01841       my $exDB =
01842         Bio::EnsEMBL::DBEntry->new(
01843                            -adaptor             => $self,
01844                            -dbID                => $dbID,
01845                            -primary_id          => $dbprimaryId,
01846                            -display_id          => $displayid,
01847                            -version             => $version,
01848                            -release             => $release,
01849                            -dbname              => $ex_dbname,
01850                            -priority            => $priority,
01851                            -db_display_name     => $db_display_name,
01852                            -info_type           => $info_type,
01853                            -info_text           => $info_text,
01854                            -type                => $type,
01855                            -secondary_db_name   => $secondary_db_name,
01856                            -secondary_db_table  => $secondary_db_table,
01857                            -description         => $description
01858         );
01859 
01860       if ($synonym) { $exDB->add_synonym($synonym) }
01861 
01862       push @results, $exDB;
01863 
01864     } ## end while ( my $arrayref = shift...
01865   } ## end while ( my $rowcache = $sth...
01866 
01867   $sth->finish();
01868 
01869   return \@results;
01870 } ## end sub fetch_all_by_description
01871 
01872 
01873 =head2 fetch_all_by_source
01874 
01875   Arg [1]    : string source to search for. Include % etc in this string
01876                if you want to use SQL patterns
01877 
01878   Example    : @unigene_refs = @{$db_entry_adaptor->fetch_all_by_source("%unigene%")};
01879   Description: Retrieves DBEntrys that match the source name.
01880   Returntype : ref to array of Bio::EnsEMBL::DBSQL::DBEntry
01881   Exceptions : None.
01882   Caller     : General
01883   Status     : At Risk
01884 
01885 =cut
01886 
01887 sub fetch_all_by_source {
01888   my ( $self, $source ) = @_;
01889 
01890   my @results = ();
01891 
01892   my $sql =
01893     "SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label,
01894            xref.version,
01895            exDB.priority,
01896            exDB.db_name, exDB.db_display_name, exDB.db_release, es.synonym,
01897            xref.info_type, xref.info_text, exDB.type, exDB.secondary_db_name,
01898            exDB.secondary_db_table, xref.description
01899     FROM   (xref, external_db exDB)
01900     LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
01901     WHERE  exDB.db_name like ?
01902     AND    xref.external_db_id = exDB.external_db_id";
01903 
01904 
01905   my $sth = $self->prepare($sql);
01906 
01907   $sth->bind_param( 1, $source, SQL_VARCHAR );
01908 
01909   $sth->execute();
01910 
01911   my $max_rows = 1000;
01912 
01913   while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
01914     while ( my $arrayref = shift( @{$rowcache} ) ) {
01915       my ( $dbID,                $dbprimaryId,
01916            $displayid,           $version,
01917            $priority,
01918            $dbname,              $db_display_name,
01919            $release,             $synonym,
01920            $info_type,           $info_text,
01921            $type,                $secondary_db_name,
01922            $secondary_db_table,  $description
01923       ) = @$arrayref;
01924 
01925       my $exDB =
01926         Bio::EnsEMBL::DBEntry->new(
01927                            -adaptor             => $self,
01928                            -dbID                => $dbID,
01929                            -primary_id          => $dbprimaryId,
01930                            -display_id          => $displayid,
01931                            -version             => $version,
01932                            -release             => $release,
01933                            -dbname              => $dbname,
01934                            -priority            => $priority,
01935                            -db_display_name     => $db_display_name,
01936                            -info_type           => $info_type,
01937                            -info_text           => $info_text,
01938                            -type                => $type,
01939                            -secondary_db_name   => $secondary_db_name,
01940                            -secondary_db_table  => $secondary_db_table,
01941                            -description         => $description
01942         );
01943 
01944       if ($synonym) { $exDB->add_synonym($synonym) }
01945 
01946       push @results, $exDB;
01947 
01948     } ## end while ( my $arrayref = shift...
01949   } ## end while ( my $rowcache = $sth...
01950 
01951   $sth->finish();
01952 
01953   return \@results;
01954 } ## end sub fetch_all_by_source
01955 
01956 
01957 =head2 fetch_all_synonyms
01958 
01959   Arg [1]    : dbID of DBEntry to fetch synonyms for. Used in lazy loading of synonyms.
01960 
01961   Example    : @canc_refs = @{$db_entry_adaptor->fetch_all_synonyms(1234)};
01962   Description: Fetches the synonyms for a particular DBEntry.
01963   Returntype : listref of synonyms. List referred to may be empty if there are no synonyms.
01964   Exceptions : None.
01965   Caller     : General
01966   Status     : At Risk
01967 
01968 =cut
01969 
01970 
01971 sub fetch_all_synonyms {
01972   my ( $self, $dbID ) = @_;
01973 
01974   my @synonyms = ();
01975 
01976   my $sth =
01977     $self->prepare( "SELECT synonym "
01978       . "FROM external_synonym "
01979       . "WHERE xref_id = ?" );
01980 
01981   $sth->bind_param( 1, $dbID, SQL_INTEGER );
01982 
01983   $sth->execute();
01984 
01985   my $synonym;
01986   $sth->bind_col(1, \$synonym);
01987 
01988   while ( $sth->fetch() ) {
01989     push( @synonyms, $synonym );
01990   }
01991 
01992   return \@synonyms;
01993 }
01994 
01995 
01996 =head2 get_db_name_from_external_db_id
01997 
01998   Arg [1]    : external_dbid of database to get the database_name
01999   Example    : my $db_name = $db_entry_adaptor->get_db_name_from_external_db_id(1100);
02000   Description: Gets the database name for a certain external_db_id
02001   Returntype : scalar
02002   Exceptions : None.
02003   Caller     : General
02004   Status     : At Risk
02005 
02006 =cut
02007 
02008 sub get_db_name_from_external_db_id{
02009     my $self = shift;
02010     my $external_db_id = shift;
02011 
02012     my $sth = $self->prepare("SELECT db_name FROM external_db WHERE external_db_id = ?");
02013 
02014     $sth->bind_param(1, $external_db_id, SQL_INTEGER);
02015     $sth->execute();
02016     my ($db_name) = $sth->fetchrow_array();
02017     $sth->finish();
02018     return $db_name;
02019 
02020 }
02021 
02022 =head2 geneids_by_extids
02023 
02024   Description: DEPRECATED use list_gene_ids_by_extids instead
02025 
02026 =cut
02027 
02028 sub geneids_by_extids{
02029    my ($self,$name) = @_;
02030    deprecate(" use 'list_gene_ids_by_extids instead");
02031    return $self->list_gene_ids_by_extids( $name );
02032 }
02033 
02034 
02035 =head2 translationids_by_extids
02036 
02037   Description:  DEPRECATED use list_translation_ids_by_extids instead
02038 
02039 =cut
02040 
02041 sub translationids_by_extids{
02042   my ($self,$name) = @_;
02043   deprecate("Use list_translation_ids_by_extids instead");
02044   return $self->list_translation_ids_by_extids( $name );
02045 }
02046 
02047 
02048 =head2 transcriptids_by_extids
02049 
02050   Description: DEPRECATED use transcriptids_by_extids instead
02051 
02052 =cut
02053 
02054 sub transcriptids_by_extids{
02055    my ($self,$name) = @_;
02056    deprecate("Use list_transcript_ids_by_extids instead.");
02057    return $self->list_transcript_ids_by_extids( $name );
02058 }
02059 
02060 
02061 1;
02062