use Bio::EnsEMBL::Registry;

  Bio::EnsEMBL::Registry->load_registry_from_db(
    -host => 'ensembldb.ensembl.org',
    -user => 'anonymous'
  );

  $transcript_adaptor =
    Bio::EnsEMBL::Registry->get_adaptor( "human", "core",
    "transcript" );

  $translation_adaptor =
    Bio::EnsEMBL::Registry->get_adaptor( "human", "core",
    "translation" );

  my $transcript = $transcript_adaptor->fetch_by_dbID(131243);
  my $translation =
    $translation_adaptor->fetch_by_Transcript($transcript);

  print("Translation Start Site: "
      . $translation->start_Exon()->stable_id() . " "
      . $translation->start()
      . "\n" );
  print("Translation Stop: "
      . $translation->end_Exon()->stable_id() . " "
      . $translation->end() );

  my $self = shift;
  deprecate("Use fetch_all_by_external_name instead.");
  return $self->fetch_all_by_external_name(@_);

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

  my $entryAdaptor = $self->db->get_DBEntryAdaptor();

  my %unique_dbIDs;
  foreach my $accession ( map { $_->accession() }
    ( $term, @{ $term->descendants() } ) )
  {
    my @ids =
      $entryAdaptor->list_translation_ids_by_extids( $accession, 'GO' );
    foreach my $dbID (@ids) { $unique_dbIDs{$dbID} = 1 }
  }

  my @result;
  if ( scalar( keys(%unique_dbIDs) ) > 0 ) {
    my $transcript_adaptor = $self->db()->get_TranscriptAdaptor();

    foreach my $dbID ( sort { $a <=> $b } keys(%unique_dbIDs) ) {
      my $transcript =
        $transcript_adaptor->fetch_by_translation_id($dbID);
      if ( defined($transcript) ) {
        push( @result, $self->fetch_by_Transcript($transcript) );
      }
    }
  }

  return\@ result;
} ## end sub fetch_all_by_GOTerm

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

  if(!defined($transcripts) || ref($transcripts) ne 'ARRAY') {
    throw("reference to list of Transcripts argument is required");
  }

  return [] if(!@$transcripts);

  my %trans_hash = map {$_->dbID() => $_} @$transcripts;
  my @id_list = keys %trans_hash;

  my @out;

  # mysql is faster and we ensure that we do not exceed the max query size by
  # splitting large queries into smaller queries of 200 ids
  my $max_size = 200;

  my ( $tr_id,$tl_id, $start_exon_id, $end_exon_id,
       $seq_start, $seq_end, $stable_id, $version, 
       $created_date, $modified_date );

  my %ex_hash;

  while(@id_list) {
    my @ids;
    if(@id_list > $max_size) {
      @ids = splice(@id_list, 0, $max_size);
    } else {
      @ids = splice(@id_list, 0);
    }

    my $id_str;
    if(@ids > 1)  {
      $id_str = " IN (" . join(',', @ids). ")";
    } else {
      $id_str = " = " . $ids[0];
    }

    my $created_date = $self->db->dbc->from_date_to_seconds("tlsi.created_date");
    my $modified_date = $self->db->dbc->from_date_to_seconds("tlsi.modified_date");

    my $sth = $self->prepare
      ("SELECT tl.transcript_id, tl.translation_id, tl.start_exon_id,
           tl.end_exon_id, tl.seq_start, tl.seq_end,
           tlsi.stable_id, tlsi.version, " . $created_date . "," .
       $modified_date . 
       " FROM translation tl
 LEFT JOIN translation_stable_id tlsi
        ON tlsi.translation_id = tl.translation_id
     WHERE tl.transcript_id $id_str");

    $sth->execute();

    $sth->bind_columns(\$ tr_id,\$ tl_id,\$ start_exon_id,\$ end_exon_id,\$
                        seq_start,\$ seq_end,\$ stable_id,\$ version,\$
			created_date,\$ modified_date );

    while($sth->fetch()) {
      my ($start_exon, $end_exon);

      # this will load all the exons whenever we load the translation
      # but I guess thats ok ....

      my $tr = $trans_hash{$tr_id};

      foreach my $exon (@{$tr->get_all_Exons()}) {
        if(!$start_exon && $exon->dbID() == $start_exon_id ) {
          $start_exon = $exon;
          last if($end_exon);
        }

        if(!$end_exon && $exon->dbID() == $end_exon_id ) {
          $end_exon = $exon;
          last if($start_exon);
        }
      }

      unless($start_exon && $end_exon) {
        throw("Could not find start or end exon in transcript\n");
      }

      my $tl =  Bio::EnsEMBL::Translation->new
        (-dbID => $tl_id,
         -adaptor => $self,
         -seq_start => $seq_start,
         -seq_end => $seq_end,
         -start_exon => $start_exon,
         -end_exon => $end_exon,
         -stable_id => $stable_id,
         -version => $version,
	 -created_date => $created_date || undef,
	 -modified_date => $modified_date || undef);

      $tr->translation($tl);

      push @out, $tl;
    }
  }

  return\@ out;

  my ( $self, $external_name, $external_db_name ) = @_;

  my $entry_adaptor = $self->db->get_DBEntryAdaptor();

  my @ids =
    $entry_adaptor->list_translation_ids_by_extids( $external_name,
                                                    $external_db_name );

  my $transcript_adaptor = $self->db()->get_TranscriptAdaptor();

  my @out;
  foreach my $id (@ids) {
    my $transcript = $transcript_adaptor->fetch_by_translation_id($id);

    if ( defined($transcript) ) {
      push @out, $self->fetch_by_Transcript($transcript);
    }
  }

  return\@ out;

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

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

  my $lsi_created_date = $self->db->dbc->from_date_to_seconds("tlsi.created_date");
  my $lsi_modified_date = $self->db->dbc->from_date_to_seconds("tlsi.modified_date");

  my $sql = "
    SELECT tl.translation_id, tl.start_exon_id,
           tl.end_exon_id, tl.seq_start, tl.seq_end,
           tlsi.stable_id, tlsi.version, " . $lsi_created_date . ", ". $lsi_modified_date . 
	   " FROM translation tl
 LEFT JOIN translation_stable_id tlsi
        ON tlsi.translation_id = tl.translation_id
     WHERE tl.transcript_id = ?";

  my $transcript_id = $transcript->dbID();
  my $sth = $self->prepare($sql);
  $sth->bind_param(1,$transcript_id,SQL_INTEGER);

  $sth->execute();

  my ( $translation_id, $start_exon_id, $end_exon_id,
       $seq_start, $seq_end, $stable_id, $version, $created_date, 
       $modified_date ) = 
	 $sth->fetchrow_array();
  $sth->finish;
  if( ! defined $translation_id ) {
    return undef;
  }

  my ($start_exon, $end_exon);

  # this will load all the exons whenever we load the translation
  # but I guess thats ok ....

  foreach my $exon (@{$transcript->get_all_Exons()}) {
    if($exon->dbID() == $start_exon_id ) {
      $start_exon = $exon;
    }

    if($exon->dbID() == $end_exon_id ) {
      $end_exon = $exon;
    }
  }

  unless($start_exon && $end_exon) {
     throw("Could not find start or end exon in transcript_id=".$transcript->dbID."\n");
  }

  my $translation = Bio::EnsEMBL::Translation->new_fast
      ({
     'dbID' => $translation_id,
     'adaptor' => $self,
     'start' => $seq_start,
     'end' => $seq_end,
     'start_exon' => $start_exon,
     'end_exon' => $end_exon,
     'stable_id' => $stable_id,
     'version' => $version,
     'created_date' => $created_date || undef,
     'modified_date' => $modified_date || undef
     }
   );

  return $translation;

   my ($self,$dbID, $transcript) = @_;

   if($transcript) {
     deprecate("Use of fetch_by_dbID with a Transcript argument is deprecated."
               . "Use fetch_by_Transcript instead." );
   }

   if(!$dbID) {
     throw("dbID argument is required");
   }

   my $transcript_adaptor = $self->db()->get_TranscriptAdaptor();
   $transcript = $transcript_adaptor->fetch_by_translation_id($dbID);

   return undef if(!$transcript);

   return $self->fetch_by_Transcript($transcript);

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

   if(!$stable_id) {
     throw("stable id argument is required");
   }

   my $transcript_adaptor = $self->db()->get_TranscriptAdaptor();
   my $transcript = 
     $transcript_adaptor->fetch_by_translation_stable_id($stable_id);

   return undef if(!$transcript);

   return $self->fetch_by_Transcript($transcript);

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

  deprecate( "This method shouldnt be necessary any more" );

  unless(defined $translation && ref $translation && 
	 $translation->isa('Bio::EnsEMBL::Translation') ) {
    throw("Needs a Translation object, not a [$translation]");
  }

  my $sth = $self->prepare("SELECT stable_id, version 
                            FROM   translation_stable_id 
                            WHERE  translation_id = ?");
  $sth->bind_param(1,$translation->dbID,SQL_INTEGER);
  $sth->execute();

  my @array = $sth->fetchrow_array();
  $translation->{'_stable_id'} = $array[0];
  $translation->{'_version'}   = $array[1];

  return 1;
}

1;

   my ($self) = @_;

   return $self->_list_dbIDs("translation");

   my ($self) = @_;

   return $self->_list_dbIDs("translation_stable_id", "stable_id");

  my $self = shift;
  my $translation = shift;

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

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

  # remove athe attributes associated with this translation
  my $attrib_adp = $self->db->get_AttributeAdaptor;
  $attrib_adp->remove_from_Translation($translation);

  # remove all xref associations to this translation
  my $dbe_adaptor = $self->db()->get_DBEntryAdaptor();
  foreach my $dbe (@{$translation->get_all_DBEntries()}) {
    $dbe_adaptor->remove_from_object($dbe, $translation, 'Translation');
  }

  # remove all protein_features on this translation
  my $sth = $self->prepare
    ("DELETE FROM protein_feature WHERE translation_id = ?");
  $sth->bind_param(1,$translation->dbID,SQL_INTEGER);
  $sth->execute();
  $sth->finish();

  # remove the translation stable identifier

  $sth = $self->prepare
    ("DELETE FROM translation_stable_id WHERE translation_id = ?" );
  $sth->bind_param(1,$translation->dbID,SQL_INTEGER);
  $sth->execute();
  $sth->finish();

  # remove the translation itself

  $sth = $self->prepare("DELETE FROM translation WHERE translation_id = ?" );
  $sth->bind_param(1,$translation->dbID,SQL_INTEGER);
  $sth->execute();
  $sth->finish();

  $translation->dbID( undef );
  $translation->adaptor(undef);

  return

  my ( $self, $translation, $transcript_id )  = @_;
  
  my $start_exon = $translation->start_Exon();
  my $end_exon   = $translation->end_Exon();
 
  if(!$start_exon) {
    throw("Translation must define a start_Exon to be stored.");
  }
 
  if(!$end_exon) {
    throw("Translation must define an end_Exon to be stored.");
  }
 
  if(!$start_exon->dbID) {
    throw("start_Exon must have a dbID for Translation to be stored.");
  }

  if(!$end_exon->dbID) {
    throw("end_Exon must have a dbID for Translation to be stored.");
  }

  my $sth = $self->prepare( "
         INSERT INTO translation( seq_start, start_exon_id,
                                  seq_end, end_exon_id, transcript_id) 
         VALUES ( ?,?,?,?,? )");

  $sth->bind_param(1,$translation->start,SQL_INTEGER);
  $sth->bind_param(2,$translation->start_Exon->dbID,SQL_INTEGER);
  $sth->bind_param(3,$translation->end,SQL_INTEGER);
  $sth->bind_param(4,$translation->end_Exon->dbID,SQL_INTEGER);
  $sth->bind_param(5,$transcript_id,SQL_INTEGER);

  $sth->execute();
 
  my $transl_dbID = $sth->{'mysql_insertid'};

  #
  # store object xref mappings to translations
  #
 
  my $dbEntryAdaptor = $self->db()->get_DBEntryAdaptor();
  # store each of the xrefs for this translation
  foreach my $dbl ( @{$translation->get_all_DBEntries} ) {
     $dbEntryAdaptor->store( $dbl, $transl_dbID, "Translation", 1 );
  }

  #storing the protein features associated with the translation
  my $pfadaptor = $self->db->get_ProteinFeatureAdaptor();
  foreach my $pf(@{$translation->get_all_ProteinFeatures}){
    $pfadaptor->store($pf, $transl_dbID);
  }

  if (defined($translation->stable_id)) {
    if (!defined($translation->version)) {
     throw("Trying to store incomplete stable id information for translation");
    }

     my $statement = 
       "INSERT INTO translation_stable_id ".
	 "SET translation_id = ?, ".
	   "  stable_id = ?, ".
	     "version = ?, ";
    $statement .= "created_date = " .
      $self->db->dbc->from_seconds_to_date($translation->created_date()) . ",";
    $statement .= "modified_date = " .
      $self->db->dbc->from_seconds_to_date($translation->modified_date()) ;
    my $sth = $self->prepare($statement);

    $sth->bind_param(1,$transl_dbID,SQL_INTEGER);
    $sth->bind_param(2,$translation->stable_id,SQL_VARCHAR);
    $sth->bind_param(3,$translation->version,SQL_VARCHAR);
    $sth->execute();

    $sth->finish();
  }

  $translation->get_all_Attributes();

  # store any translation attributes that are defined
  my $attr_adaptor = $self->db->get_AttributeAdaptor();
  $attr_adaptor->store_on_Translation($transl_dbID,
                                      $translation->get_all_Attributes());

  $translation->dbID($transl_dbID);
  $translation->adaptor($self);

  return $transl_dbID;

  Copyright (c) 1999-2009 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

    /info/about/code_licence.html

  Please email comments or questions to the public Ensembl
  developers list at <ensembl-dev@ebi.ac.uk>.

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

  Arg [1]    : none
  Example    : @transl_stable_ids = @{$transl_adaptor->list_stable_dbIDs()};
  Description: Gets an array of stable ids for all translations in the current 
               db
  Returntype : reference to a list of strings
  Exceptions : none
  Caller     : general
  Status     : Stable