DBEntryAdaptor.pm

=head1 LICENSE

  Copyright (c) 1999-2010 The European Bioinformatics Institute and
  Genome Research Limited.  All rights reserved.

  This software is distributed under a modified Apache license.
  For license details, please see

    http://www.ensembl.org/info/about/code_licence.html

=head1 CONTACT

  Please email comments or questions to the public Ensembl
  developers list at <dev@ensembl.org>.

  Questions may also be sent to the Ensembl help desk at
  <helpdesk@ensembl.org>.

=cut

=head1 NAME

Bio::EnsEMBL::DBSQL::DBEntryAdaptor -
MySQL Database queries to load and store external object references.

=head1 SYNOPSIS

  $db_entry_adaptor =
    $registry->get_adaptor( 'Human', 'Core', 'DBEntry' );

  $db_entry = $db_entry_adaptor->fetch_by_dbID($id);

  my $gene_adaptor = $registry->get_adaptor( 'Human', 'Core', 'Gene' );

  my $gene = $gene_adaptor->fetch_by_stable_id('ENSG00000101367');

  @db_entries = @{ $db_entry_adaptor->fetch_all_by_Gene($gene) };
  @gene_ids   = $db_entry_adaptor->list_gene_ids_by_extids('BAB15482');

=head1 METHODS

=cut

package Bio::EnsEMBL::DBSQL::DBEntryAdaptor;

use Bio::EnsEMBL::DBSQL::BaseAdaptor;

use Bio::EnsEMBL::DBEntry;
use Bio::EnsEMBL::IdentityXref;
use Bio::EnsEMBL::OntologyXref;

use Bio::EnsEMBL::Utils::Exception qw(deprecate throw warning);

use vars qw(@ISA);
use strict;

@ISA = qw( Bio::EnsEMBL::DBSQL::BaseAdaptor );

=head2 fetch_by_dbID

  Arg [1]    : int $dbID
               the unique database identifier for the DBEntry to retrieve
  Example    : my $db_entry = $db_entry_adaptor->fetch_by_dbID($dbID);
  Description: Retrieves a dbEntry from the database via its unique
               identifier.
  Returntype : Bio::EnsEMBL::DBEntry
  Exceptions : none
  Caller     : general
  Status     : Stable

=cut

sub fetch_by_dbID {
  my ( $self, $dbID ) = @_;

  my $sth = $self->prepare(
    "SELECT  xref.xref_id,
            xref.dbprimary_acc,
            xref.display_label,
            xref.version,
            exDB.dbprimary_acc_linkable,
            exDB.display_label_linkable,
            exDB.priority,
            exDB.db_name,
            exDB.db_display_name,
            exDB.db_release,
            es.synonym,
            xref.info_type,
            xref.info_text,
            exDB.type,
            exDB.secondary_db_name,
            exDB.secondary_db_table,
            xref.description
    FROM    (xref, external_db exDB)
    LEFT JOIN external_synonym es ON
            es.xref_id = xref.xref_id
    WHERE   xref.xref_id = ?
    AND     xref.external_db_id = exDB.external_db_id" );

  $sth->bind_param( 1, $dbID, SQL_INTEGER );
  $sth->execute();

  my $exDB;

  my $max_rows = 1000;

  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
      #$description refers to the external_db description, while $desc was referring the xref description
    while ( my $arrayref = shift( @{$rowcache} ) ) {
      my ( $refID,               $dbprimaryId,
           $displayid,           $version,
	   $primary_id_linkable,
           $display_id_linkable, $priority,
           $dbname,              $db_display_name,
           $release,             $synonym,
           $info_type,           $info_text,
           $type,                $secondary_db_name,
           $secondary_db_table,  $description
      ) = @$arrayref;

      if ( !defined($exDB) ) {
        $exDB =
          Bio::EnsEMBL::DBEntry->new(
                           -adaptor             => $self,
                           -dbID                => $dbID,
                           -primary_id          => $dbprimaryId,
                           -display_id          => $displayid,
                           -version             => $version,
                           -release             => $release,
                           -dbname              => $dbname,
                           -primary_id_linkable => $primary_id_linkable,
                           -display_id_linkable => $display_id_linkable,
                           -priority            => $priority,
                           -db_display_name     => $db_display_name,
                           -info_type           => $info_type,
                           -info_text           => $info_text,
                           -type                => $type,
                           -secondary_db_name   => $secondary_db_name,
                           -secondary_db_table  => $secondary_db_table,
			   -description         => $description
          );


      }

      if ( defined($synonym) ) { $exDB->add_synonym($synonym) }

    } ## end while ( my $arrayref = shift...
  } ## end while ( my $rowcache = $sth...

  $sth->finish();

  return $exDB;
} ## end sub fetch_by_dbID


sub _get_all_dm_loc_sth {
  my ($self, $constraint ,$ensembl_object ) = @_;
  my $object_type;
  if($ensembl_object->isa("Bio::EnsEMBL::Gene")){
    $object_type = "Gene";
  }
  elsif($ensembl_object->isa("Bio::EnsEMBL::Transcript")){
    $object_type = "Transcript";
  }
  elsif($ensembl_object->isa("Bio::EnsEMBL::Translation")){
    $object_type = "Translation";
  }
  else{
    warn(ref($ensembl_object)." is not a Gene Transcript or Translation object??\n");
    return undef;
  }
  my $sql = "SELECT xref.xref_id,
               xref.dbprimary_acc,
            xref.display_label,
            xref.version,
            exDB.dbprimary_acc_linkable,
            exDB.display_label_linkable,
            exDB.priority,
            exDB.db_name,
            exDB.db_display_name,
            exDB.db_release,
            es.synonym,
            xref.info_type,
            xref.info_text,
            exDB.type,
            exDB.secondary_db_name,
            exDB.secondary_db_table,
            xref.description
    FROM    (xref, external_db exDB, dependent_xref dx, object_xref ox)
    LEFT JOIN external_synonym es ON
            es.xref_id = xref.xref_id
    WHERE   xref.external_db_id = exDB.external_db_id AND
            ox.xref_id = xref.xref_id AND
            ox.ensembl_object_type = \'$object_type\' AND
            ox.ensembl_id = ".$ensembl_object->dbID();

  if($constraint){
    $sql .= " AND $constraint";
  }
  else{
    die "NO constraint???\n";
  }
#  print "\n\n".$sql."\n";
  my $sth = $self->prepare($sql) || die "Could not prepare $sql";
#  print "sth is $sth\n";  
  return $self->_get_all_dm($sth);
}

sub _get_all_dm_sth {
  my ( $self, $constraint) = @_;

 my $sql = "SELECT xref.xref_id,
               xref.dbprimary_acc,
            xref.display_label,
            xref.version,
            exDB.dbprimary_acc_linkable,
            exDB.display_label_linkable,
            exDB.priority,
            exDB.db_name,
            exDB.db_display_name,
            exDB.db_release,
            es.synonym,
            xref.info_type,
            xref.info_text,
            exDB.type,
            exDB.secondary_db_name,
            exDB.secondary_db_table,
            xref.description
    FROM    (xref, external_db exDB, dependent_xref dx)
    LEFT JOIN external_synonym es ON
            es.xref_id = xref.xref_id
    WHERE   xref.external_db_id = exDB.external_db_id ";

  if($constraint){
    $sql .= "AND $constraint";
  }
  else{
    die "NO constraint???\n";
  }

  my $sth = $self->prepare($sql) || die "Could not prepare $sql";

#  print "sth is $sth\n";
  return $self->_get_all_dm($sth);
}


sub _get_all_dm{

  my ($self, $sth) = @_;

#  $sth->bind_param( 1, $dm_dbid, SQL_INTEGER );

#  print $sth."\n";
  $sth->execute() || die "Not able to execute statement handle";

  my @list =();
  my %seen;

  my $max_rows = 1000;
  while ( my $rowcache = $sth->fetchall_arrayref(undef, $max_rows) ) {
    while ( my $arrayref = shift( @{$rowcache} ) ) {
      my ( $dbID,                $dbprimaryId,
           $displayid,           $version,
           $primary_id_linkable,
           $display_id_linkable, $priority,
           $dbname,              $db_display_name,
           $release,             $synonym,
           $info_type,           $info_text,
           $type,                $secondary_db_name,
           $secondary_db_table,  $description
      ) = @$arrayref;

      if ( !defined($seen{$dbID}) ) {
       my $exDB =
          Bio::EnsEMBL::DBEntry->new(
                           -adaptor             => $self,
                           -dbID                => $dbID,
                           -primary_id          => $dbprimaryId,
                           -display_id          => $displayid,
                           -version             => $version,
                           -release             => $release,
                           -dbname              => $dbname,
                           -primary_id_linkable => $primary_id_linkable,
                           -display_id_linkable => $display_id_linkable,
                           -priority            => $priority,
                           -db_display_name     => $db_display_name,
                           -info_type           => $info_type,
                           -info_text           => $info_text,
                           -type                => $type,
                           -secondary_db_name   => $secondary_db_name,
                           -secondary_db_table  => $secondary_db_table,
			   -description         => $description
          );

	if ($synonym) { $exDB->add_synonym($synonym) };
	$seen{$dbID} = 1;
	push @list, $exDB;
      }



    } ## end while ( my $arrayref = shift...
  } ## end while ( my $rowcache = $sth...

  $sth->finish();

  return \@list;

}


=head2 get_all_dependents

  Args[1]    : dbID of the DBentry to get the dependents of.
  Args[2]    : (optional) Bio::EnsEMBL::Gene, Transcript or Translation object
  Example    : my @dependents = @{ $dbe_adaptor->get_all_dependents(1234) };
  Description: Get a list of DBEntrys that are depenednet on the DBEntry.
               if an ensembl gene transcript or translation is given then only
               the ones on that object will be given
  Returntype : listref of DBEntrys. May be empty.
  Exceptions : none
  Caller     : DBEntry->get_all_dependnets
  Status     : UnStable

=cut

sub get_all_dependents {
  my ( $self, $dbid, $ensembl_object) = @_;
  
  if(defined($ensembl_object) and !($ensembl_object->isa("Bio::EnsEMBL::Feature") or $ensembl_object->isa("Bio::EnsEMBL::Translation"))){
    die ref($ensembl_object)." is not an Gene Transcript or Translation";
  }
  
  my $constraint = " dx.master_xref_id = $dbid AND  dx.dependent_xref_id = xref.xref_id";
  if(defined($ensembl_object)){
    return $self->_get_all_dm_loc_sth($constraint, $ensembl_object);
  }
  else{
    return $self->_get_all_dm_sth($constraint, $ensembl_object);
  }

}

=head2 get_all_masters

  Args[1]    : dbID of the DBentry to get the masters of.
  Args[2]    : (optional) Bio::EnsEMBL::Gene, Transcript or Translation object
  Example    : my @masters = @{ $dbe_adaptor->get_all_masters(1234) };
  Description: Get a list of DBEntrys that are the masters of the DBEntry.
               if an ensembl gene transcript or translation is given then only
               the ones on that object will be given.
  Returntype : listref of DBEntrys. May be empty.
  Exceptions : none
  Caller     : DBEntry->get_all_masters
  Status     : UnStable

=cut

sub get_all_masters {
  my ( $self, $dbid, $ensembl_object ) = @_;
  
  if(defined($ensembl_object) and !($ensembl_object->isa("Bio::EnsEMBL::Feature") or $ensembl_object->isa("Bio::EnsEMBL::Translation"))){
    die ref($ensembl_object)." is not an Gene Transcript or Translation";
  }
  
  my $constraint = "dx.dependent_xref_id = $dbid AND  dx.master_xref_id = xref.xref_id";

  if(defined($ensembl_object)){
    return $self->_get_all_dm_loc_sth($constraint, $ensembl_object);
  }
  else{
    return $self->_get_all_dm_sth($constraint, $ensembl_object);
  }
#  return $self->_get_all_dm($constraint, $ensembl_object);
}


=head2 fetch_by_db_accession

  Arg [1]    : string $dbname - The name of the database which the provided
               accession is for.
  Arg [2]    : string $accession - The accesion of the external reference to
               retrieve.
  Example    : my $xref = $dbea->fetch_by_db_accession('Interpro','IPR003439');
               print $xref->description(), "\n" if($xref);
  Description: Retrieves a DBEntry (xref) via the name of the database
               it is from and its primary accession in that database.
               Undef is returned if the xref cannot be found in the
               database.
               NOTE:  In a multi-species database, this method will
               return all the entries matching the search criteria, not
               just the ones associated with the current species.
  Returntype : Bio::EnsEMBL::DBSQL::DBEntry
  Exceptions : thrown if arguments are incorrect
  Caller     : general, domainview
  Status     : Stable

=cut

sub fetch_by_db_accession {
  my ( $self, $dbname, $accession ) = @_;

  my $sth = $self->prepare(
    "SELECT xref.xref_id,
            xref.dbprimary_acc,
            xref.display_label,
            xref.version,
            exDB.dbprimary_acc_linkable,
            exDB.display_label_linkable,
            exDB.priority,
            exDB.db_name,
            exDB.db_display_name,
            exDB.db_release,
            es.synonym,
            xref.info_type,
            xref.info_text,
            exDB.type,
            exDB.secondary_db_name,
            exDB.secondary_db_table,
            xref.description
    FROM    (xref, external_db exDB)
    LEFT JOIN external_synonym es ON
            es.xref_id = xref.xref_id
    WHERE  xref.dbprimary_acc = ?
    AND    exDB.db_name = ?
    AND    xref.external_db_id = exDB.external_db_id" );

  $sth->bind_param( 1, $accession, SQL_VARCHAR );
  $sth->bind_param( 2, $dbname,    SQL_VARCHAR );
  $sth->execute();

  if ( !$sth->rows() && lc($dbname) eq 'interpro' ) {
  # This is a minor hack that means that results still come back even
  # when a mistake was made and no interpro accessions were loaded into
  # the xref table.  This has happened in the past and had the result of
  # breaking domainview

    $sth->finish();
    $sth = $self->prepare(
      "SELECT   NULL,
                i.interpro_ac,
                i.id,
                NULL,
                NULL,
                'Interpro',
                NULL,
                NULL
        FROM    interpro i
        WHERE   i.interpro_ac = ?" );

    $sth->bind_param( 1, $accession, SQL_VARCHAR );
    $sth->execute();
  }

  my $exDB;

  my $max_rows = 1000;

  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
    while ( my $arrayref = shift( @{$rowcache} ) ) {
      my ( $dbID,                $dbprimaryId,
           $displayid,           $version,
           $primary_id_linkable,
           $display_id_linkable, $priority,
           $dbname,              $db_display_name,
           $release,             $synonym,
           $info_type,           $info_text,
           $type,                $secondary_db_name,
           $secondary_db_table,  $description
      ) = @$arrayref;

      if ( !defined($exDB) ) {
        $exDB =
          Bio::EnsEMBL::DBEntry->new(
                           -adaptor             => $self,
                           -dbID                => $dbID,
                           -primary_id          => $dbprimaryId,
                           -display_id          => $displayid,
                           -version             => $version,
                           -release             => $release,
                           -dbname              => $dbname,
                           -primary_id_linkable => $primary_id_linkable,
                           -display_id_linkable => $display_id_linkable,
                           -priority            => $priority,
                           -db_display_name     => $db_display_name,
                           -info_type           => $info_type,
                           -info_text           => $info_text,
                           -type                => $type,
                           -secondary_db_name   => $secondary_db_name,
                           -secondary_db_table  => $secondary_db_table,
			   -description         => $description
          );


      }

      if ($synonym) { $exDB->add_synonym($synonym) }

    } ## end while ( my $arrayref = shift...
  } ## end while ( my $rowcache = $sth...

  $sth->finish();

  return $exDB;
} ## end sub fetch_by_db_accession


=head2 store

  Arg [1]    : Bio::EnsEMBL::DBEntry $exObj
               The DBEntry (xref) to be stored
  Arg [2]    : Int $ensID
               The dbID of an EnsEMBL object to associate with this external
               database entry
  Arg [3]    : string $ensType ('Transcript', 'Translation', 'Gene')
               The type of EnsEMBL object that this external database entry is
               being associated with.
  Arg [4]    : boolean $ignore_release
               If unset or zero, will require that the release string
               of the DBEntry object is identical to the release of the
               external database.  If set and non-zero, will ignore the
               release information.
  Example    : $dbea->store($db_entry, $transcript_id, 'Transcript');
  Description: Stores a reference to an external database (if it is not stored
               already) and associates an EnsEMBL object of a specified type
               with the external identifier.
  Returntype : int - the dbID of the newly created external refernce
  Exceptions : thrown when invalid dbID is passed to this method
  Caller     : scripts which load Xrefs and ObjectXrefs, etc. into Ensembl
  Status     : Stable

=cut

sub store {
  my ( $self, $exObj, $ensID, $ensType, $ignore_release ) = @_;

  my $dbJustInserted;

  #
  # backwards compatibility check:
  # check if $ensID is an object; if so, use $obj->dbID
  #
  my $ensembl_id;


  if ( defined($ensID) ) {
    if ( $ensID =~ /^\d+$/ ) {
      $ensembl_id = $ensID;
    } elsif (    ref($ensID) eq 'Bio::EnsEMBL::Gene'
              or ref($ensID) eq 'Bio::EnsEMBL::Transcript'
              or ref($ensID) eq 'Bio::EnsEMBL::Translation' )
    {
      warning(   "You should pass DBEntryAdaptor->store() "
               . "a dbID rather than an ensembl object "
               . "to store the xref on" );

      if ( !defined( $ensID->dbID() ) ) {
        $ensembl_id = $ensID->dbID();
      } else {
        throw( sprintf( "%s %s doesn't have a dbID, can't store xref",
                        $ensType, $ensID->display_id() ) );
      }
    } else {
      throw("Invalid dbID passed to DBEntryAdaptor->store()");
    }
  }

  #
  # Check for the existance of the external_db, throw if it does not exist
  #

  my ($dbRef, $release_clause);

  if ( !$ignore_release ) {

	if(defined $exObj->release()){
	  $release_clause = " AND db_release = ?";
	}
	else{
	  $release_clause = " AND db_release is NULL";
	}


    my $sth = $self->prepare( "
     SELECT external_db_id
       FROM external_db
      WHERE db_name    = ?
        $release_clause" );

    $sth->bind_param( 1, $exObj->dbname(),  SQL_VARCHAR );
    $sth->bind_param( 2, $exObj->release(), SQL_VARCHAR ) if defined $exObj->release();
    $sth->execute();

    ($dbRef) = $sth->fetchrow_array();
	$sth->finish();

    if ( !$dbRef ) {
      throw(
             sprintf( "external_db [%s] release [%s] does not exist",
                      $exObj->dbname(), $exObj->release() ) );
    }
  } else {

    my $sth = $self->prepare( "
     SELECT external_db_id
       FROM external_db
      WHERE db_name = ? " );

    $sth->bind_param( 1, $exObj->dbname(), SQL_VARCHAR );

    $sth->execute();

    ($dbRef) = $sth->fetchrow_array();

    if ( !$dbRef ) {
      throw(
          sprintf( "external_db [%s] does not exist", $exObj->dbname() )
      );
    }
  }

  #
  # Check for the existance of the external reference, add it if not present
  #

  my $sql =  "SELECT xref_id FROM xref
                WHERE external_db_id = ?
                  AND dbprimary_acc  = ?
                  AND version        = ?";

  if(defined $exObj->info_type){
    $sql .= " AND  info_type      = ?";
  }
  else{
    $sql .= " AND info_type is null";
  }

  if(defined $exObj->info_text){
    $sql .= " AND  info_text      = ?";
  }
  else{
    $sql .= " AND info_text is null";
  }

  my $sth = $self->prepare($sql);

  $sth->bind_param(1,$dbRef,SQL_INTEGER);
  $sth->bind_param(2,$exObj->primary_id,SQL_VARCHAR);
  $sth->bind_param(3,$exObj->version,SQL_VARCHAR);

  my $i = 4;
  if(defined $exObj->info_type){
    $sth->bind_param($i++,$exObj->info_type,SQL_VARCHAR);
  }
  if(defined $exObj->info_text){
    $sth->bind_param($i++,$exObj->info_text,SQL_VARCHAR);
  }
  $sth->execute();

  my ($dbX) = $sth->fetchrow_array();

  $sth->finish();
  if(!$dbX) {
    if(!$exObj->primary_id()) {
      throw("DBEntry cannot be stored without a primary_id attribute.");
    }

    #
    # store the new xref
    #
    $sth = $self->prepare( "
       INSERT ignore INTO xref
       SET dbprimary_acc = ?,
           display_label = ?,
           version = ?,
           description = ?,
           external_db_id = ?,
           info_type = ?,
           info_text = ?");
    $sth->bind_param(1, $exObj->primary_id,SQL_VARCHAR);
    $sth->bind_param(2, $exObj->display_id,SQL_VARCHAR);
    $sth->bind_param(3, $exObj->version,SQL_VARCHAR);
    $sth->bind_param(4, $exObj->description,SQL_VARCHAR);
    $sth->bind_param(5, $dbRef,SQL_INTEGER);
    $sth->bind_param(6, $exObj->info_type, SQL_VARCHAR);
    $sth->bind_param(7, $exObj->info_text, SQL_VARCHAR);

    $sth->execute();

    $dbX = $sth->{'mysql_insertid'};
    $sth->finish();
    #
    # store the synonyms for the new xref
    #
    my $synonym_check_sth = $self->prepare(
              "SELECT xref_id, synonym
               FROM external_synonym
               WHERE xref_id = ?
               AND synonym = ?");

    my $synonym_store_sth = $self->prepare(
        "INSERT ignore INTO external_synonym
         SET xref_id = ?, synonym = ?");

    my $synonyms = $exObj->get_all_synonyms();
    foreach my $syn ( @$synonyms ) {
	$synonym_check_sth->bind_param(1,$dbX,SQL_INTEGER);
	$synonym_check_sth->bind_param(2,$syn,SQL_VARCHAR);
	$synonym_check_sth->execute();
      my ($dbSyn) = $synonym_check_sth->fetchrow_array();
	$synonym_store_sth->bind_param(1,$dbX,SQL_INTEGER);
	$synonym_store_sth->bind_param(2,$syn,SQL_VARCHAR);
	$synonym_store_sth->execute() if(!$dbSyn);
    }
    $synonym_check_sth->finish();
    $synonym_store_sth->finish();
  } elsif (! defined( $exObj->dbID() ) ) {
    $exObj->dbID($dbX);
  }

  if ( defined($ensembl_id) ) {
    #
    # check if the object mapping was already stored
    #
    $sth = $self->prepare(
      qq(
SELECT  xref_id
FROM    object_xref
WHERE   xref_id = ?
  AND   ensembl_object_type = ?
  AND   ensembl_id = ?
  AND   (   linkage_annotation = ?
  OR        linkage_annotation IS NULL  )) );

    $sth->bind_param( 1, $dbX,                         SQL_INTEGER );
    $sth->bind_param( 2, $ensType,                     SQL_VARCHAR );
    $sth->bind_param( 3, $ensembl_id,                  SQL_INTEGER );
    $sth->bind_param( 4, $exObj->linkage_annotation(), SQL_VARCHAR );

    $sth->execute();

    my ($tst) = $sth->fetchrow_array();

    $sth->finish();

    if ( !$tst ) {
      #
      # Store the reference to the internal ensembl object
      #
      my $analysis_id;
      if ( $exObj->analysis() ) {
        $analysis_id =
          $self->db()->get_AnalysisAdaptor->store( $exObj->analysis() );
      } else {
        $analysis_id = undef;
      }

      $sth = $self->prepare(
        qq(
INSERT IGNORE INTO object_xref
  SET   xref_id = ?,
        ensembl_object_type = ?,
        ensembl_id = ?,
        linkage_annotation = ?,
        analysis_id = ? ) );

      $sth->bind_param( 1, $dbX,                         SQL_INTEGER );
      $sth->bind_param( 2, $ensType,                     SQL_VARCHAR );
      $sth->bind_param( 3, $ensembl_id,                  SQL_INTEGER );
      $sth->bind_param( 4, $exObj->linkage_annotation(), SQL_VARCHAR );
      $sth->bind_param( 5, $analysis_id,                 SQL_INTEGER );

      #print "stored xref id $dbX in obejct_xref\n";
      $sth->execute();
      $exObj->dbID($dbX);
      $exObj->adaptor($self);
      my $Xidt = $sth->{'mysql_insertid'};

      #
      # If this is an IdentityXref need to store in that table too
      # If its OntologyXref add the linkage type to ontology_xref table
      #
      if ( $exObj->isa('Bio::EnsEMBL::IdentityXref') ) {
        $sth = $self->prepare( "
             INSERT ignore INTO identity_xref
             SET object_xref_id = ?,
             xref_identity = ?,
             ensembl_identity = ?,
             xref_start = ?,
             xref_end   = ?,
             ensembl_start = ?,
             ensembl_end = ?,
             cigar_line = ?,
             score = ?,
             evalue = ?" );
        $sth->bind_param( 1, $Xidt,                    SQL_INTEGER );
        $sth->bind_param( 2, $exObj->xref_identity,    SQL_INTEGER );
        $sth->bind_param( 3, $exObj->ensembl_identity, SQL_INTEGER );
        $sth->bind_param( 4, $exObj->xref_start,       SQL_INTEGER );
        $sth->bind_param( 5, $exObj->xref_end,         SQL_INTEGER );
        $sth->bind_param( 6, $exObj->ensembl_start,    SQL_INTEGER );
        $sth->bind_param( 7, $exObj->ensembl_end,      SQL_INTEGER );
        $sth->bind_param( 8,  $exObj->cigar_line, SQL_LONGVARCHAR );
        $sth->bind_param( 9,  $exObj->score,      SQL_DOUBLE );
        $sth->bind_param( 10, $exObj->evalue,     SQL_DOUBLE );
        $sth->execute();
      } elsif ( $exObj->isa('Bio::EnsEMBL::OntologyXref') ) {
        $sth = $self->prepare( "
             INSERT ignore INTO ontology_xref
                SET object_xref_id = ?,
                    source_xref_id = ?,
                    linkage_type = ? " );
        foreach my $info ( @{ $exObj->get_all_linkage_info() } ) {
          my ( $lt, $sourceXref ) = @{$info};
          my $sourceXid = undef;
          if ($sourceXref) {
            $sourceXref->is_stored( $self->dbc )
              || $self->store($sourceXref);
            $sourceXid = $sourceXref->dbID;
          }
          $sth->bind_param( 1, $Xidt,      SQL_INTEGER );
          $sth->bind_param( 2, $sourceXid, SQL_INTEGER );
          $sth->bind_param( 3, $lt,        SQL_VARCHAR );
          $sth->execute();
        }
      }
    } ## end if ( !$tst )
  } ## end if ( defined($ensembl_id...))

  return $dbX;
}


=head2 exists

  Arg [1]    : Bio::EnsEMBL::DBEntry $dbe
  Example    : if($dbID = $db_entry_adaptor->exists($dbe)) { do stuff; }
  Description: Returns the db id of this DBEntry if it exists in this database
               otherwise returns undef.  Exists is defined as an entry with
               the same external_db and display_id
  Returntype : int
  Exceptions : thrown on incorrect args
  Caller     : GeneAdaptor::store, TranscriptAdaptor::store
  Status     : Stable

=cut

sub exists {
  my ($self, $dbe) = @_ ;

  unless($dbe && ref $dbe && $dbe->isa('Bio::EnsEMBL::DBEntry')) {
    throw("arg must be a Bio::EnsEMBL::DBEntry not [$dbe]");
  }

  my $sth = $self->prepare('SELECT x.xref_id
                            FROM   xref x, external_db xdb
                            WHERE  x.external_db_id = xdb.external_db_id
                            AND    x.display_label = ?
                            AND    xdb.db_name = ?
                            AND    x.dbprimary_acc = ?');

  $sth->bind_param(1,$dbe->display_id,SQL_VARCHAR);
  $sth->bind_param(2,$dbe->dbname,SQL_VARCHAR);
  $sth->bind_param(3,$dbe->primary_id,SQL_VARCHAR);
  $sth->execute();

  my ($dbID) = $sth->fetchrow_array;

  $sth->finish;

  return $dbID;
}


=head2 fetch_all_by_Gene

  Arg [1]    : Bio::EnsEMBL::Gene $gene
               (The gene to retrieve DBEntries for)
  Arg [2]    : optional external database name
  Arg [3]    : optional external_db type
  Example    : @db_entries = @{$db_entry_adaptor->fetch_by_Gene($gene)};
  Description: This returns a list of DBEntries associated with this gene.
               Note that this method was changed in release 15.  Previously
               it set the DBLinks attribute of the gene passed in to contain
               all of the gene, transcript, and translation xrefs associated
               with this gene.
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
               there is mapping data, or OntologyXref if there is linkage data.
  Exceptions : thows if gene object not passed
  Caller     : Bio::EnsEMBL::Gene
  Status     : Stable

=cut

sub fetch_all_by_Gene {
  my ( $self, $gene, $ex_db_reg, $exdb_type ) = @_;

  if(!ref($gene) || !$gene->isa('Bio::EnsEMBL::Gene')) {
    throw("Bio::EnsEMBL::Gene argument expected.");
  }

  return $self->_fetch_by_object_type($gene->dbID(), 'Gene', $ex_db_reg, $exdb_type);
}


=head2 fetch_all_by_Transcript

  Arg [1]    : Bio::EnsEMBL::Transcript
  Arg [2]    : optional external database name
  Arg [3]    : optional external_db type
  Example    : @db_entries = @{$db_entry_adaptor->fetch_by_Gene($trans)};
  Description: This returns a list of DBEntries associated with this
               transcript. Note that this method was changed in release 15.
               Previously it set the DBLinks attribute of the gene passed in
               to contain all of the gene, transcript, and translation xrefs
               associated with this gene.
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
               there is mapping data, or OntologyXref if there is linkage data.
  Exceptions : throes if transcript argument not passed
  Caller     : Bio::EnsEMBL::Gene
  Status     : Stable

=cut

sub fetch_all_by_Transcript {
  my ( $self, $trans, $ex_db_reg, $exdb_type ) = @_;

  if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Transcript')) {
    throw("Bio::EnsEMBL::Transcript argument expected.");
  }

  return $self->_fetch_by_object_type( $trans->dbID(), 'Transcript', $ex_db_reg, $exdb_type);
}


=head2 fetch_all_by_Translation

  Arg [1]    : Bio::EnsEMBL::Translation $trans
               (The translation to fetch database entries for)
  Arg [2]    : optional external database name
  Arg [3]    : optional externaldb type
  Example    : @db_entries = @{$db_entry_adptr->fetch_all_by_Translation($trans)};
  Description: Retrieves external database entries for an EnsEMBL translation
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
               there is mapping data, or OntologyXref if there is linkage data.
  Exceptions : throws if translation object not passed
  Caller     : general
  Status     : Stable

=cut

sub fetch_all_by_Translation {
  my ( $self, $trans, $ex_db_reg, $exdb_type ) = @_;

  if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Translation')) {
    throw('Bio::EnsEMBL::Translation argument expected.');
  }
  if( ! $trans->dbID ){
    warning( "Cannot fetch_all_by_Translation without a dbID" );
    return [];
  }

  return $self->_fetch_by_object_type( $trans->dbID(), 'Translation', $ex_db_reg, $exdb_type );
}



=head2 remove_from_object

  Arg [1]    : Bio::EnsEMBL::DBEntry $dbe - The external reference which
               is to be disassociated from an ensembl object.
  Arg [2]    : Bio::EnsEMBL::Storable $object - The ensembl object the
               external reference is to be disassociated from
  Arg [3]    : string $object_type - The type of the ensembl object.
               E.g. 'Gene', 'Transcript', 'Translation'
  Example    :
               # remove all dbentries from this translation
               foreach my $dbe (@{$translation->get_all_DBEntries()}) {
                 $dbe_adaptor->remove($dbe, $translation, 'Translation');
               }
  Description: Removes an association between an ensembl object and a
               DBEntry (xref).  This does not remove the actual xref from
               the database, only its linkage to the ensembl object.
  Returntype : none
  Exceptions : Throw on incorrect arguments.
               Warning if object or dbentry is not stored in this database.
  Caller     : TranscriptAdaptor::remove, GeneAdaptor::remove,
               TranslationAdaptor::remove
  Status     : Stable

=cut

sub remove_from_object {
  my $self = shift;
  my $dbe  = shift;
  my $object = shift;
  my $object_type = shift;

  if(!ref($dbe) || !$dbe->isa('Bio::EnsEMBL::DBEntry')) {
    throw("Bio::EnsEMBL::DBEntry argument expected.");
  }

  if(!ref($object) || !$dbe->isa('Bio::EnsEMBL::Storable')) {
    throw("Bio::EnsEMBL::Storable argument expected.");
  }

  if(!$object_type) {
    throw("object_type string argument expected.");
  }

  # make sure both the dbentry and the object it is allegedly linked to
  # are stored in this database

  if(!$object->is_stored($self->db())) {
    warning("Cannot remove DBEntries for $object_type " . $object->dbID() .
            ". Object is not stored in this database.");
    return;
  }

  if(!$dbe->is_stored($self->db())) {
    warning("Cannot remove DBEntry ".$dbe->dbID() . ". Is not stored " .
            "in this database.");
    return;
  }

  # obtain the identifier of the link from the object_xref table
  #No need to compare linkage_annotation here
  my $sth = $self->prepare
    ("SELECT ox.object_xref_id " .
     "FROM   object_xref ox ".
     "WHERE  ox.xref_id = ? " .
     "AND    ox.ensembl_id = ? " .
     "AND    ox.ensembl_object_type = ?");
  $sth->bind_param(1,$dbe->dbID,SQL_INTEGER);
  $sth->bind_param(2,$object->dbID,SQL_INTEGER);
  $sth->bind_param(3,$object_type,SQL_VARCHAR);
  $sth->execute();

  if(!$sth->rows() == 1) {
    $sth->finish();
    return;
  }

  my ($ox_id) = $sth->fetchrow_array();
  $sth->finish();

  # delete from the tables which contain additional linkage information

  $sth = $self->prepare("DELETE FROM ontology_xref WHERE object_xref_id = ?");
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
  $sth->finish();

  $sth = $self->prepare("DELETE FROM identity_xref WHERE object_xref_id = ?");
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
  $sth->finish();

  # delete the actual linkage itself
  $sth = $self->prepare("DELETE FROM object_xref WHERE object_xref_id = ?");
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
  $sth->finish();

  return;
}


=head2 _fetch_by_object_type

  Arg [1]    : string $ensID
  Arg [2]    : string $ensType (object type to be returned)
  Arg [3]    : optional $exdbname (external database name)
               (may be an SQL pattern containing '%' which matches any
               number of characters)
  Arg [4]    : optional $exdb_type (external database type)
               (may be an SQL pattern containing '%' which matches any
               number of characters)
  Example    : $self->_fetch_by_object_type( $translation_id, 'Translation' )
  Description: Fetches DBEntry by Object type
               NOTE:  In a multi-species database, this method will
               return all the entries matching the search criteria, not
               just the ones associated with the current species.


  Returntype : arrayref of DBEntry objects; may be of type IdentityXref if
               there is mapping data, or OntologyXref if there is linkage data.
  Exceptions : none
  Caller     : fetch_all_by_Gene
               fetch_all_by_Translation
               fetch_all_by_Transcript
  Status     : Stable

=cut

sub _fetch_by_object_type {
  my ( $self, $ensID, $ensType, $exdbname, $exdb_type ) = @_;

  my @out;

  if ( !defined($ensID) ) {
    throw("Can't fetch_by_EnsObject_type without an object");
  }

  if ( !defined($ensType) ) {
    throw("Can't fetch_by_EnsObject_type without a type");
  }

  #  my $sth = $self->prepare("
  my $sql = (<<SSQL);
    SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label, xref.version,
           exDB.dbprimary_acc_linkable, exDB.display_label_linkable,
           exDB.priority,
           exDB.db_name, exDB.db_release, exDB.status, exDB.db_display_name,
           exDB.secondary_db_name, exDB.secondary_db_table,
           oxr.object_xref_id,
           es.synonym,
           idt.xref_identity, idt.ensembl_identity, idt.xref_start,
           idt.xref_end, idt.ensembl_start, idt.ensembl_end,
           idt.cigar_line, idt.score, idt.evalue, oxr.analysis_id,
           gx.linkage_type,
           xref.info_type, xref.info_text, exDB.type, gx.source_xref_id,
           oxr.linkage_annotation, xref.description
    FROM   (xref xref, external_db exDB, object_xref oxr)
    LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
    LEFT JOIN identity_xref idt on idt.object_xref_id = oxr.object_xref_id
    LEFT JOIN ontology_xref gx on gx.object_xref_id = oxr.object_xref_id
    WHERE  xref.xref_id = oxr.xref_id
      AND  xref.external_db_id = exDB.external_db_id
      AND  oxr.ensembl_id = ?
      AND  oxr.ensembl_object_type = ?
SSQL

  if ( defined($exdbname) ) {
    if ( index( $exdbname, '%' ) != -1 ) {
      $sql .= " AND exDB.db_name = "
        . $self->dbc()->db_handle()->quote( $exdbname, SQL_VARCHAR );
    } else {
      $sql .= " AND exDB.db_name LIKE "
        . $self->dbc()->db_handle()->quote( $exdbname, SQL_VARCHAR );
    }
  }

  if ( defined($exdb_type) ) {
    if ( index( $exdb_type, '%' ) != -1 ) {
      $sql .= " AND exDB.type = "
        . $self->dbc()->db_handle()->quote( $exdb_type, SQL_VARCHAR );
    } else {
      $sql .= " AND exDB.type LIKE "
        . $self->dbc()->db_handle()->quote( $exdb_type, SQL_VARCHAR );
    }
  }

  my $sth = $self->prepare($sql);

  $sth->bind_param( 1, $ensID,   SQL_INTEGER );
  $sth->bind_param( 2, $ensType, SQL_VARCHAR );
  $sth->execute();

  my ( %seen, %linkage_types, %synonyms );

  my $max_rows = 1000;

  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
    while ( my $arrRef = shift( @{$rowcache} ) ) {
      my ( $refID,                  $dbprimaryId,
           $displayid,              $version,
           $primary_id_linkable,
           $display_id_linkable,    $priority,
           $dbname,                 $release,
           $exDB_status,            $exDB_db_display_name,
           $exDB_secondary_db_name, $exDB_secondary_db_table,
           $objid,                  $synonym,
           $xrefid,                 $ensemblid,
           $xref_start,             $xref_end,
           $ensembl_start,          $ensembl_end,
           $cigar_line,             $score,
           $evalue,                 $analysis_id,
           $linkage_type,           $info_type,
           $info_text,              $type,
           $source_xref_id,         $link_annotation,
	   $description
      ) = @$arrRef;

      my $linkage_key =
        ( $linkage_type || '' ) . ( $source_xref_id || '' );


      my $analysis = undef;
      if ( defined($analysis_id) ) {
	$analysis =
	  $self->db()->get_AnalysisAdaptor()->fetch_by_dbID($analysis_id);
      }

      my %obj_hash = ( 'adaptor'            => $self,
                       'dbID'               => $refID,
                       'primary_id'         => $dbprimaryId,
                       'display_id'         => $displayid,
                       'version'            => $version,
                       'release'            => $release,
                       'info_type'          => $info_type,
                       'info_text'          => $info_text,
                       'type'               => $type,
                       'secondary_db_name'  => $exDB_secondary_db_name,
                       'secondary_db_table' => $exDB_secondary_db_table,
                       'dbname'             => $dbname,
                       'description'        => $description,
                       'linkage_annotation' => $link_annotation,
                       'analysis'           => $analysis);

      # Using an outer join on the synonyms as well as on identity_xref,
      # we now have to filter out the duplicates (see v.1.18 for
      # original). Since there is at most one identity_xref row per
      # xref, this is easy enough; all the 'extra' bits are synonyms.
      my $source_xref;
      if ( !$seen{$refID} ) {
	
	my $exDB;
        if ( ( defined($xrefid) ) ) {  # an xref with similarity scores
          $exDB = Bio::EnsEMBL::IdentityXref->new_fast( \%obj_hash );
          $exDB->xref_identity($xrefid);
          $exDB->ensembl_identity($ensemblid);

          $exDB->cigar_line($cigar_line);
          $exDB->xref_start($xref_start);
          $exDB->xref_end($xref_end); # was not here before 14th Jan 2009 ????
          $exDB->ensembl_start($ensembl_start);
          $exDB->ensembl_end($ensembl_end);
          $exDB->score($score);
          $exDB->evalue($evalue);

        } elsif ( defined $linkage_type && $linkage_type ne "" ) {
          $exDB = Bio::EnsEMBL::OntologyXref->new_fast( \%obj_hash );