ensembl-hive  2.7.0
DBEntryAdaptor.pm
Go to the documentation of this file.
1 =head1 LICENSE
2 
3 See the NOTICE file distributed with this work for additional information
4 regarding copyright ownership.
5 
6 Licensed under the Apache License, Version 2.0 (the "License");
7 you may not use this file except in compliance with the License.
8 You may obtain a copy of the License at
9 
10  http://www.apache.org/licenses/LICENSE-2.0
11 
12 Unless required by applicable law or agreed to in writing, software
13 distributed under the License is distributed on an "AS IS" BASIS,
14 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15 See the License for the specific language governing permissions and
16 limitations under the License.
17 
18 =cut
19 
20 
21 =head1 CONTACT
22 
23  Please email comments or questions to the public Ensembl
24  developers list at <http://lists.ensembl.org/mailman/listinfo/dev>.
25 
26  Questions may also be sent to the Ensembl help desk at
27  <http://www.ensembl.org/Help/Contact>.
28 
29 =cut
30 
31 =head1 NAME
32 
33 Bio::EnsEMBL::DBSQL::DBEntryAdaptor -
34 MySQL Database queries to load and store external object references.
35 
36 =head1 SYNOPSIS
37 
38  $db_entry_adaptor =
39  $registry->get_adaptor( 'Human', 'Core', 'DBEntry' );
40 
41  $db_entry = $db_entry_adaptor->fetch_by_dbID($id);
42 
43  my $gene_adaptor = $registry->get_adaptor( 'Human', 'Core', 'Gene' );
44 
45  my $gene = $gene_adaptor->fetch_by_stable_id('ENSG00000101367');
46 
47  @db_entries = @{ $db_entry_adaptor->fetch_all_by_Gene($gene) };
48  @gene_ids = $db_entry_adaptor->list_gene_ids_by_extids('BAB15482');
49 
50 =head1 METHODS
51 
52 =cut
53 
54 package Bio::EnsEMBL::DBSQL::DBEntryAdaptor;
55 
56 use Bio::EnsEMBL::DBSQL::BaseAdaptor;
57 
58 use Bio::EnsEMBL::DBEntry;
59 use Bio::EnsEMBL::IdentityXref;
60 use Bio::EnsEMBL::OntologyXref;
61 
62 use Bio::EnsEMBL::Utils::Exception qw(throw warning);
63 use Bio::EnsEMBL::Utils::Scalar qw(check_ref);
64 
65 use vars qw(@ISA);
66 use strict;
67 
68 @ISA = qw( Bio::EnsEMBL::DBSQL::BaseAdaptor );
69 
70 =head2 fetch_by_dbID
71 
72  Arg [1] : int $dbID
73  the unique database identifier for the DBEntry to retrieve
74  Example : my $db_entry = $db_entry_adaptor->fetch_by_dbID($dbID);
75  Description: Retrieves a dbEntry from the database via its unique
76  identifier.
77  Returntype : Bio::EnsEMBL::DBEntry
78  Exceptions : none
79  Caller : general
80  Status : Stable
81 
82 =cut
83 
84 sub fetch_by_dbID {
85  my ( $self, $dbID ) = @_;
86 
87  my $sth = $self->prepare(
88  "SELECT xref.xref_id,
89  xref.dbprimary_acc,
90  xref.display_label,
91  xref.version,
92  exDB.priority,
93  exDB.db_name,
94  exDB.db_display_name,
95  exDB.db_release,
96  es.synonym,
97  xref.info_type,
98  xref.info_text,
99  exDB.type,
100  exDB.secondary_db_name,
101  exDB.secondary_db_table,
102  xref.description
103  FROM (xref, external_db exDB)
104  LEFT JOIN external_synonym es ON
105  es.xref_id = xref.xref_id
106  WHERE xref.xref_id = ?
107  AND xref.external_db_id = exDB.external_db_id" );
108 
109  $sth->bind_param( 1, $dbID, SQL_INTEGER );
110  $sth->execute();
111 
112  my $exDB;
113 
114  my $max_rows = 1000;
115 
116  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
117  #$description refers to the external_db description, while $desc was referring the xref description
118  while ( my $arrayref = shift( @{$rowcache} ) ) {
119  my ( $refID, $dbprimaryId,
120  $displayid, $version,
121  $priority,
122  $dbname, $db_display_name,
123  $release, $synonym,
124  $info_type, $info_text,
125  $type, $secondary_db_name,
126  $secondary_db_table, $description
127  ) = @$arrayref;
128 
129  if ( !defined($exDB) ) {
130  $exDB =
131  Bio::EnsEMBL::DBEntry->new(
132  -adaptor => $self,
133  -dbID => $dbID,
134  -primary_id => $dbprimaryId,
135  -display_id => $displayid,
136  -version => $version,
137  -release => $release,
138  -dbname => $dbname,
139  -priority => $priority,
140  -db_display_name => $db_display_name,
141  -info_type => $info_type,
142  -info_text => $info_text,
143  -type => $type,
144  -secondary_db_name => $secondary_db_name,
145  -secondary_db_table => $secondary_db_table,
146  -description => $description
147  );
148 
149 
150  }
151 
152  if ( defined($synonym) ) { $exDB->add_synonym($synonym) }
153 
154  } ## end while ( my $arrayref = shift...
155  } ## end while ( my $rowcache = $sth...
156 
157  $sth->finish();
158 
159  return $exDB;
160 } ## end sub fetch_by_dbID
161 
162 
163 sub _get_all_dm_loc_sth {
164  my ($self, $constraint ,$ensembl_object ) = @_;
165  my $object_type;
166  if($ensembl_object->isa("Bio::EnsEMBL::Gene")){
167  $object_type = "Gene";
168  }
169  elsif($ensembl_object->isa("Bio::EnsEMBL::Transcript")){
170  $object_type = "Transcript";
171  }
172  elsif($ensembl_object->isa("Bio::EnsEMBL::Translation")){
173  $object_type = "Translation";
174  }
175  elsif($ensembl_object->isa("Bio::EnsEMBL::Operon")){
176  $object_type = "Operon";
177  }
178  elsif($ensembl_object->isa("Bio::EnsEMBL::OperonTranscript")){
179  $object_type = "OperonTranscript";
180  }
181  else{
182  warn(ref($ensembl_object)." is not a Gene Transcript or Translation object??\n");
183  return undef;
184  }
185  my $sql = "SELECT xref.xref_id,
186  xref.dbprimary_acc,
187  xref.display_label,
188  xref.version,
189  exDB.priority,
190  exDB.db_name,
191  exDB.db_display_name,
192  exDB.db_release,
193  es.synonym,
194  xref.info_type,
195  xref.info_text,
196  exDB.type,
197  exDB.secondary_db_name,
198  exDB.secondary_db_table,
199  xref.description
200  FROM (xref, external_db exDB, dependent_xref dx, object_xref ox)
201  LEFT JOIN external_synonym es ON
202  es.xref_id = xref.xref_id
203  WHERE xref.external_db_id = exDB.external_db_id AND
204  ox.xref_id = xref.xref_id AND
205  ox.ensembl_object_type = \'$object_type\' AND
206  ox.ensembl_id = ".$ensembl_object->dbID();
207 
208  if($constraint){
209  $sql .= " AND $constraint";
210  }
211  else{
212  die "NO constraint???\n";
213  }
214 
215  my $sth = $self->prepare($sql) || die "Could not prepare $sql";
216 
217  return $self->_get_all_dm($sth);
218 }
219 
220 sub _get_all_dm_sth {
221  my ( $self, $constraint) = @_;
222 
223  my $sql = "SELECT xref.xref_id,
224  xref.dbprimary_acc,
225  xref.display_label,
226  xref.version,
227  exDB.priority,
228  exDB.db_name,
229  exDB.db_display_name,
230  exDB.db_release,
231  es.synonym,
232  xref.info_type,
233  xref.info_text,
234  exDB.type,
235  exDB.secondary_db_name,
236  exDB.secondary_db_table,
237  xref.description
238  FROM (xref, external_db exDB, dependent_xref dx)
239  LEFT JOIN external_synonym es ON
240  es.xref_id = xref.xref_id
241  WHERE xref.external_db_id = exDB.external_db_id ";
242 
243  if($constraint){
244  $sql .= "AND $constraint";
245  }
246  else{
247  die "NO constraint???\n";
248  }
249 
250  my $sth = $self->prepare($sql) || die "Could not prepare $sql";
251 
252  return $self->_get_all_dm($sth);
253 }
254 
255 
256 sub _get_all_dm{
257 
258  my ($self, $sth) = @_;
259 
260 # $sth->bind_param( 1, $dm_dbid, SQL_INTEGER );
261 
262 # print $sth."\n";
263  $sth->execute() || die "Not able to execute statement handle";
264 
265  my @list =();
266  my %seen;
267 
268  my $max_rows = 1000;
269  while ( my $rowcache = $sth->fetchall_arrayref(undef, $max_rows) ) {
270  while ( my $arrayref = shift( @{$rowcache} ) ) {
271  my ( $dbID, $dbprimaryId,
272  $displayid, $version,
273  $priority,
274  $dbname, $db_display_name,
275  $release, $synonym,
276  $info_type, $info_text,
277  $type, $secondary_db_name,
278  $secondary_db_table, $description
279  ) = @$arrayref;
280 
281  if ( !defined($seen{$dbID}) ) {
282  my $exDB =
283  Bio::EnsEMBL::DBEntry->new(
284  -adaptor => $self,
285  -dbID => $dbID,
286  -primary_id => $dbprimaryId,
287  -display_id => $displayid,
288  -version => $version,
289  -release => $release,
290  -dbname => $dbname,
291  -priority => $priority,
292  -db_display_name => $db_display_name,
293  -info_type => $info_type,
294  -info_text => $info_text,
295  -type => $type,
296  -secondary_db_name => $secondary_db_name,
297  -secondary_db_table => $secondary_db_table,
298  -description => $description
299  );
300 
301  if ($synonym) { $exDB->add_synonym($synonym) };
302  $seen{$dbID} = 1;
303  push @list, $exDB;
304  }
305 
306 
307 
308  } ## end while ( my $arrayref = shift...
309  } ## end while ( my $rowcache = $sth...
310 
311  $sth->finish();
312 
313  return \@list;
314 
315 }
316 
317 
318 =head2 get_all_dependents
319 
320  Args[1] : dbID of the DBentry to get the dependents of.
321  Args[2] : (optional) Bio::EnsEMBL::Gene, Transcript or Translation object
322  Example : my @dependents = @{ $dbe_adaptor->get_all_dependents(1234) };
323  Description: Get a list of DBEntrys that are depenednet on the DBEntry.
324  if an ensembl gene transcript or translation is given then only
325  the ones on that object will be given
326  Returntype : listref of DBEntrys. May be empty.
327  Exceptions : none
328  Caller : DBEntry->get_all_dependnets
329  Status : UnStable
330 
331 =cut
332 
333 sub get_all_dependents {
334  my ( $self, $dbid, $ensembl_object) = @_;
335 
336  if(defined($ensembl_object) and !($ensembl_object->isa("Bio::EnsEMBL::Feature") or $ensembl_object->isa("Bio::EnsEMBL::Translation"))){
337  die ref($ensembl_object)." is not an Gene Transcript or Translation";
338  }
339 
340  my $constraint = " dx.master_xref_id = $dbid AND dx.dependent_xref_id = xref.xref_id";
341  if(defined($ensembl_object)){
342  return $self->_get_all_dm_loc_sth($constraint, $ensembl_object);
343  }
344  else{
345  return $self->_get_all_dm_sth($constraint, $ensembl_object);
346  }
347 
348 }
349 
350 =head2 get_all_masters
351 
352  Args[1] : dbID of the DBentry to get the masters of.
353  Args[2] : (optional) Bio::EnsEMBL::Gene, Transcript or Translation object
354  Example : my @masters = @{ $dbe_adaptor->get_all_masters(1234) };
355  Description: Get a list of DBEntrys that are the masters of the DBEntry.
356  if an ensembl gene transcript or translation is given then only
357  the ones on that object will be given.
358  Returntype : listref of DBEntrys. May be empty.
359  Exceptions : none
360  Caller : DBEntry->get_all_masters
361  Status : UnStable
362 
363 =cut
364 
365 sub get_all_masters {
366  my ( $self, $dbid, $ensembl_object ) = @_;
367 
368  if(defined($ensembl_object) and !($ensembl_object->isa("Bio::EnsEMBL::Feature") or $ensembl_object->isa("Bio::EnsEMBL::Translation"))){
369  die ref($ensembl_object)." is not an Gene Transcript or Translation";
370  }
371 
372  my $constraint = "dx.dependent_xref_id = $dbid AND dx.master_xref_id = xref.xref_id";
373 
374  if(defined($ensembl_object)){
375  return $self->_get_all_dm_loc_sth($constraint, $ensembl_object);
376  }
377  else{
378  return $self->_get_all_dm_sth($constraint, $ensembl_object);
379  }
380 # return $self->_get_all_dm($constraint, $ensembl_object);
381 }
382 
383 
384 =head fetch_all_by_name
385 
386  Arg [1] : string $name - The name of the external reference.
387  found in accession, display_label or synonym
388  Arg [2] : (optional) string $dbname - The name of the database which
389  the provided name is for.
390 
391  Example : my $xref = @{$dbea->fetch_all_by_name('BRAC2','HGNC')}[0];
392  print $xref->description(), "\n" if($xref);
393  Description: Retrieves list of DBEntrys (xrefs) via a name.
394  The accesion is looked for first then the synonym and finally
395  the display_label.
396  NOTE $dbname this is optional but adding this speeds the
397  process up if you know what you are looking for.
398 
399  NOTE: In a multi-species database, this method will
400  return all the entries matching the search criteria, not
401  just the ones associated with the current species.
402  Returntype : Bio::EnsEMBL::DBSQL::DBEntry
403  Exceptions : thrown if arguments are incorrect
404  Caller : general, domainview
405  Status : Stable
406 
407 =cut
408 
409 sub fetch_all_by_name {
410  my ( $self, $name, $dbname ) = @_;
411 
412  my $sql = (<<SQL);
413  SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label, xref.version,
414  exDB.priority, exDB.db_name, exDB.db_display_name, exDB.db_release,
415  es.synonym, xref.info_type, xref.info_text,
416  exDB.type, exDB.secondary_db_name, exDB.secondary_db_table,
417  xref.description
418  FROM (xref, external_db exDB)
419  LEFT JOIN external_synonym es ON
420  es.xref_id = xref.xref_id
421  WHERE (xref.dbprimary_acc = ? or xref.display_label = ?)
422  AND xref.external_db_id = exDB.external_db_id
423 SQL
424 
425  if(defined $dbname){
426  $sql .= " AND exDB.db_name = ?";
427  }
428  my $sth = $self->prepare($sql);
429  $sth->bind_param( 1, $name, SQL_VARCHAR );
430  $sth->bind_param( 2, $name, SQL_VARCHAR );
431  if(defined $dbname){
432  $sth->bind_param( 3 , $dbname, SQL_VARCHAR );
433  }
434  $sth->execute();
435 
436  my $max_rows = 1000;
437 
438  my $precache = $sth->fetchall_arrayref( undef, $max_rows ); # need to fetch to ensure rows() works for SQLite
439 
440  if ( !$sth->rows() && lc($dbname) eq 'interpro' ) {
441  # This is a minor hack that means that results still come back even
442  # when a mistake was made and no interpro accessions were loaded into
443  # the xref table. This has happened in the past and had the result of
444  # breaking domainview
445 
446  $precache = undef;
447  $sth->finish();
448  $sth = $self->prepare(
449  "SELECT NULL,
450  i.interpro_ac,
451  i.id,
452  NULL,
453  NULL,
454  'Interpro',
455  NULL,
456  NULL
457  FROM interpro i
458  WHERE i.interpro_ac = ?" );
459 
460  $sth->bind_param( 1, $name, SQL_VARCHAR );
461  $sth->execute();
462  }
463 
464  my %exDB;
465  my @exDBlist;
466 
467  while ( my $rowcache = $precache || $sth->fetchall_arrayref( undef, $max_rows ) ) {
468  $precache = undef;
469  while ( my $arrayref = shift( @{$rowcache} ) ) {
470  my ( $dbID, $dbprimaryId,
471  $displayid, $version,
472  $priority,
473  $dbname, $db_display_name,
474  $release, $synonym,
475  $info_type, $info_text,
476  $type, $secondary_db_name,
477  $secondary_db_table, $description
478  ) = @$arrayref;
479 
480  if ( !defined $exDB{$dbID} ) {
481  my $entrie =
482  Bio::EnsEMBL::DBEntry->new(
483  -adaptor => $self,
484  -dbID => $dbID,
485  -primary_id => $dbprimaryId,
486  -display_id => $displayid,
487  -version => $version,
488  -release => $release,
489  -dbname => $dbname,
490  -priority => $priority,
491  -db_display_name => $db_display_name,
492  -info_type => $info_type,
493  -info_text => $info_text,
494  -type => $type,
495  -secondary_db_name => $secondary_db_name,
496  -secondary_db_table => $secondary_db_table,
497  -description => $description
498  );
499  $exDB{$dbID} = $entrie;
500  push @exDBlist, $entrie;
501  }
502  if ($synonym) { $exDB{$dbID}->add_synonym($synonym) }
503 
504  } ## end while ( my $arrayref = shift...
505  } ## end while ( my $rowcache = $sth...
506 
507  $sth->finish();
508 
509  return \@exDBlist;
510 } ## end sub fetch_all_by_name
511 
512 
513 
514 =head2 fetch_by_db_accession
515 
516  Arg [1] : string $dbname - The name of the database which the provided
517  accession is for.
518  Arg [2] : string $accession - The accesion of the external reference to
519  retrieve.
520  Example : my $xref = $dbea->fetch_by_db_accession('Interpro','IPR003439');
521  print $xref->description(), "\n" if($xref);
522  Description: Retrieves a DBEntry (xref) via the name of the database
523  it is from and its primary accession in that database.
524  Undef is returned if the xref cannot be found in the
525  database.
526  NOTE: In a multi-species database, this method will
527  return all the entries matching the search criteria, not
528  just the ones associated with the current species.
529  Returntype : Bio::EnsEMBL::DBSQL::DBEntry
530  Exceptions : thrown if arguments are incorrect
531  Caller : general, domainview
532  Status : Stable
533 
534 =cut
535 
536 sub fetch_by_db_accession {
537  my ( $self, $dbname, $accession ) = @_;
538 
539  my $sth = $self->prepare(
540  "SELECT xref.xref_id,
541  xref.dbprimary_acc,
542  xref.display_label,
543  xref.version,
544  exDB.priority,
545  exDB.db_name,
546  exDB.db_display_name,
547  exDB.db_release,
548  es.synonym,
549  xref.info_type,
550  xref.info_text,
551  exDB.type,
552  exDB.secondary_db_name,
553  exDB.secondary_db_table,
554  xref.description
555  FROM (xref, external_db exDB)
556  LEFT JOIN external_synonym es ON
557  es.xref_id = xref.xref_id
558  WHERE xref.dbprimary_acc = ?
559  AND exDB.db_name like ?
560  AND xref.external_db_id = exDB.external_db_id" );
561 
562  $sth->bind_param( 1, $accession, SQL_VARCHAR );
563  $sth->bind_param( 2, $dbname, SQL_VARCHAR );
564  $sth->execute();
565 
566  my $max_rows = 1000;
567 
568  my $precache = $sth->fetchall_arrayref( undef, $max_rows ); # need to fetch to ensure rows() works for SQLite
569 
570  if ( !$sth->rows() && lc($dbname) eq 'interpro' ) {
571  # This is a minor hack that means that results still come back even
572  # when a mistake was made and no interpro accessions were loaded into
573  # the xref table. This has happened in the past and had the result of
574  # breaking domainview
575 
576  $precache = undef;
577  $sth->finish();
578  $sth = $self->prepare(
579  "SELECT NULL,
580  i.interpro_ac,
581  i.id,
582  NULL,
583  NULL,
584  'Interpro',
585  NULL,
586  NULL
587  FROM interpro i
588  WHERE i.interpro_ac = ?" );
589 
590  $sth->bind_param( 1, $accession, SQL_VARCHAR );
591  $sth->execute();
592  }
593 
594  my $exDB;
595 
596  while ( my $rowcache = $precache || $sth->fetchall_arrayref( undef, $max_rows ) ) {
597  $precache = undef;
598  while ( my $arrayref = shift( @{$rowcache} ) ) {
599  my ( $dbID, $dbprimaryId,
600  $displayid, $version,
601  $priority,
602  $dbname, $db_display_name,
603  $release, $synonym,
604  $info_type, $info_text,
605  $type, $secondary_db_name,
606  $secondary_db_table, $description
607  ) = @$arrayref;
608 
609  if ( !defined($exDB) ) {
610  $exDB =
611  Bio::EnsEMBL::DBEntry->new(
612  -adaptor => $self,
613  -dbID => $dbID,
614  -primary_id => $dbprimaryId,
615  -display_id => $displayid,
616  -version => $version,
617  -release => $release,
618  -dbname => $dbname,
619  -priority => $priority,
620  -db_display_name => $db_display_name,
621  -info_type => $info_type,
622  -info_text => $info_text,
623  -type => $type,
624  -secondary_db_name => $secondary_db_name,
625  -secondary_db_table => $secondary_db_table,
626  -description => $description
627  );
628 
629 
630  }
631 
632  if ($synonym) { $exDB->add_synonym($synonym) }
633 
634  } ## end while ( my $arrayref = shift...
635  } ## end while ( my $rowcache = $sth...
636 
637  $sth->finish();
638 
639  return $exDB;
640 } ## end sub fetch_by_db_accession
641 
642 
643 =head2 store
644 
645  Arg [1] : Bio::EnsEMBL::DBEntry $dbEntry
646  The DBEntry (xref) to be stored
647  Arg [2] : Int $ensID
648  The dbID of an EnsEMBL object to associate with this external
649  database entry
650  Arg [3] : string $ensType ('Transcript', 'Translation', 'Gene')
651  The type of EnsEMBL object that this external database entry is
652  being associated with.
653  Arg [4] : boolean $ignore_release
654  If unset or zero, will require that the release string
655  of the DBEntry object is identical to the release of the
656  external database. If set and non-zero, will ignore the
657  release information.
658  Example : $dbea->store($db_entry, $transcript_id, 'Transcript');
659  Description: Stores a reference to an external database (if it is not stored
660  already) and associates an EnsEMBL object of a specified type
661  with the external identifier.
662  Returntype : int - the dbID of the newly created external refernce
663  Exceptions : thrown when invalid dbID is passed to this method
664  Caller : scripts which load Xrefs and ObjectXrefs, etc. into Ensembl
665  Status : Stable
666 
667 =cut
668 
669 sub store {
670  my ( $self, $dbEntry, $ensID, $ensType, $ignore_release, $master_xref ) = @_;
671 
672  my $dbJustInserted;
673 
674  #
675  # backwards compatibility check:
676  # check if $ensID is an object; if so, use $obj->dbID
677  #
678  my $ensembl_id;
679 
680  if ( defined($ensID) ) {
681  if ( $ensID =~ /^\d+$/ ) {
682  $ensembl_id = $ensID;
683  } elsif ( ref($ensID) eq 'Bio::EnsEMBL::Gene'
684  or ref($ensID) eq 'Bio::EnsEMBL::Transcript'
685  or ref($ensID) eq 'Bio::EnsEMBL::Translation'
686  or ref($ensID) eq 'Bio::EnsEMBL::OperonTranscript'
687  or ref($ensID) eq 'Bio::EnsEMBL::Operon'
688  )
689  {
690  warning( "You should pass DBEntryAdaptor->store() "
691  . "a dbID rather than an ensembl object "
692  . "to store the xref on" );
693 
694  if ( defined( $ensID->dbID() ) ) {
695  $ensembl_id = $ensID->dbID();
696  } else {
697  throw( sprintf( "%s %s doesn't have a dbID, can't store xref",
698  $ensType, $ensID->display_id() ) );
699  }
700  } else {
701  throw("Invalid dbID passed to DBEntryAdaptor->store()");
702  }
703  }
704 
705 
706 
707  # Ensure external_db contains a record of the intended xref source
708  my $dbRef;
709  $dbRef = $self->_check_external_db($dbEntry,$ignore_release);
710 
711  # Attempt to insert DBEntry
712  my $xref_id = $self->_store_or_fetch_xref($dbEntry,$dbRef);
713  $dbEntry->dbID($xref_id); #keeps DBEntry in sync with database
714  ### Attempt to create an object->xref mapping
715  my $object_xref_id;
716  if ($ensembl_id) { $object_xref_id = $self->_store_object_xref_mapping($ensembl_id,$dbEntry,$ensType, $ignore_release)};
717 
718  if (defined $master_xref && defined $object_xref_id) { $self->_store_dependent_xref_mapping($object_xref_id, $dbEntry, $master_xref); }
719 
720  return $xref_id;
721 }
722 
723 
724 
725 
726 =head2 update
727 
728  Arg [1] : Bio::EnsEMBL::DBEntry $dbentry
729  The dbentry to update
730  Example : $dbentry_adaptor->update($dbentry);
731  Description: Updates the dbprimary_acc, display_label, version, description, info_type
732  and info_text of a dbentry in the database.
733  Returntype : None
734  Exceptions : thrown if the $dbentry is not a Bio::EnsEMBL::DBEntry
735  Caller : general
736  Status : Stable
737 
738 =cut
739 
740 sub update {
741  my ($self, $dbEntry) = @_;
742 
743  if (!defined $dbEntry || !ref $dbEntry || !$dbEntry->isa('Bio::EnsEMBL::DBEntry')) {
744  throw("Must update a dbentry object, not a $dbEntry");
745  }
746 
747  my $update_dbentry_sql = qq(
748  UPDATE xref
749  SET dbprimary_acc = ?,
750  display_label = ?,
751  version = ?,
752  description = ?,
753  external_db_id = ?,
754  info_type = ?,
755  info_text = ?
756  WHERE xref_id = ?
757  );
758 
759  my $dbRef = $self->_check_external_db($dbEntry);
760  my $display_id = $dbEntry->display_id;
761  $display_id = '' unless defined $display_id; # SQLite doesn't ignore NOT NULL errors
762 
763  my $sth = $self->prepare($update_dbentry_sql);
764 
765  $sth->bind_param(1, $dbEntry->primary_id,SQL_VARCHAR);
766  $sth->bind_param(2, $display_id,SQL_VARCHAR);
767  $sth->bind_param(3, ($dbEntry->version || q{0}),SQL_VARCHAR);
768  $sth->bind_param(4, $dbEntry->description,SQL_VARCHAR);
769  $sth->bind_param(5, $dbRef,SQL_INTEGER);
770  $sth->bind_param(6, ($dbEntry->info_type || 'NONE'), SQL_VARCHAR);
771  $sth->bind_param(7, ($dbEntry->info_text || ''), SQL_VARCHAR);
772 
773  $sth->bind_param(8, $dbEntry->dbID(), SQL_INTEGER);
774 
775  $sth->execute();
776 
777 } ## end sub update
778 
779 sub _store_object_xref_mapping {
780  my $self = shift;
781  my $ensembl_id = shift;
782  my $dbEntry = shift;
783  my $ensembl_type = shift;
784  my $ignore_release = shift;
785 
786  my $dbc = $self->dbc();
787  my $sql_helper = $dbc->sql_helper();
788 
789  if (not defined ($ensembl_type)) { warning("No Ensembl data type provided for new xref");}
790 
791  my $analysis_id;
792  if ( $dbEntry->analysis() ) {
793  $analysis_id = $self->db()->get_AnalysisAdaptor->store( $dbEntry->analysis() );
794  }
795 
796  my $insert_ignore = $self->insert_ignore_clause();
797  my $insert_params = [
798  [$dbEntry->dbID(), SQL_INTEGER],
799  [$ensembl_type, SQL_VARCHAR],
800  [$ensembl_id, SQL_INTEGER],
801  [$dbEntry->linkage_annotation(),SQL_VARCHAR],
802  [$analysis_id, SQL_INTEGER],
803  ];
804  my $base_object_xref_insert_sql = qq(
805  ${insert_ignore} INTO object_xref
806  ( xref_id,
807  ensembl_object_type,
808  ensembl_id,
809  linkage_annotation,
810  analysis_id )
811  VALUES ( ?, ?, ?, ?, ? )
812  );
813 
814  my $object_xref_id;
815 
816  #If MySQL we can optimise into a single insert query to deal with object_xref_id retrieval on insert ignore
817  if($dbc->driver() eq 'mysql') {
818  my $sql = $base_object_xref_insert_sql.'ON DUPLICATE KEY UPDATE object_xref_id=LAST_INSERT_ID(object_xref_id)';
819  $sql_helper->execute_update(-SQL => $sql, -PARAMS => $insert_params);
820  $object_xref_id = $self->last_insert_id('object_xref_id', undef, 'object_xref');
821  }
822  #Otherwise we attempt & retrieve on failure
823  else {
824  my $updated_rows = $sql_helper->execute_update(-SQL => $base_object_xref_insert_sql, -PARAMS => $insert_params);
825  if($updated_rows == 1) {
826  $object_xref_id = $self->last_insert_id('object_xref_id', undef, 'object_xref');
827  }
828  else {
829  my $sql = 'select object_xref_id from object_xref where xref_id =? and ensembl_object_type =? and ensembl_id =?';
830  my $params = [
831  [$dbEntry->dbID(), SQL_INTEGER],
832  [$ensembl_type, SQL_VARCHAR],
833  [$ensembl_id, SQL_INTEGER],
834  ];
835  $object_xref_id = $sql_helper->execute_single_result(-SQL => $sql, -PARAMS => $params);
836  }
837  }
838 
839  $dbEntry->adaptor($self); # hand Adaptor to dbEntry for future use with OntologyXrefs
840  # This is here because everything else assumes it can reuse the $sth scalar ... grrr
841  my $sth;
842  if ( $dbEntry->isa('Bio::EnsEMBL::IdentityXref') ) {
843  # Can attempt multiple inserts as object_xref_id is the PRIMARY KEY
844  $sth = $self->prepare( "
845  ${insert_ignore} INTO identity_xref
846  ( object_xref_id,
847  xref_identity,
848  ensembl_identity,
849  xref_start,
850  xref_end ,
851  ensembl_start,
852  ensembl_end,
853  cigar_line,
854  score,
855  evalue )
856  VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" );
857  $sth->bind_param( 1, $object_xref_id, SQL_INTEGER );
858  $sth->bind_param( 2, $dbEntry->xref_identity, SQL_INTEGER );
859  $sth->bind_param( 3, $dbEntry->ensembl_identity, SQL_INTEGER );
860  $sth->bind_param( 4, $dbEntry->xref_start, SQL_INTEGER );
861  $sth->bind_param( 5, $dbEntry->xref_end, SQL_INTEGER );
862  $sth->bind_param( 6, $dbEntry->ensembl_start, SQL_INTEGER );
863  $sth->bind_param( 7, $dbEntry->ensembl_end, SQL_INTEGER );
864  $sth->bind_param( 8, $dbEntry->cigar_line, SQL_LONGVARCHAR );
865  $sth->bind_param( 9, $dbEntry->score, SQL_DOUBLE );
866  $sth->bind_param( 10, $dbEntry->evalue, SQL_DOUBLE );
867  $sth->execute();
868  } elsif ( $dbEntry->isa('Bio::EnsEMBL::OntologyXref') ) {
869  $sth = $self->prepare( "
870  ${insert_ignore} INTO ontology_xref
871  ( object_xref_id,
872  source_xref_id,
873  linkage_type )
874  VALUES ( ?, ?, ? )" );
875  foreach my $info ( @{ $dbEntry->get_all_linkage_info() } ) {
876  my ( $linkage_type, $sourceXref ) = @{$info};
877  my $sourceXid = undef;
878  if ($sourceXref) {
879  $sourceXref->is_stored( $self->dbc ) || $self->store($sourceXref, undef, undef, $ignore_release);
880  $sourceXid = $sourceXref->dbID;
881  }
882  $sth->bind_param( 1, $object_xref_id, SQL_INTEGER );
883  $sth->bind_param( 2, $sourceXid, SQL_INTEGER );
884  $sth->bind_param( 3, $linkage_type, SQL_VARCHAR );
885  $sth->execute();
886  } #end foreach
887 
888  $sth = $self->prepare( "
889  ${insert_ignore} INTO associated_xref
890  ( object_xref_id,
891  xref_id,
892  source_xref_id,
893  condition_type,
894  associated_group_id,
895  `rank` )
896  VALUES ( ?, ?, ?, ?, ?, ? ) " );
897 
898  my $annotext = $dbEntry->get_all_associated_xrefs();
899  foreach my $ax_group (sort keys %{ $annotext }) {
900  my $group = $annotext->{$ax_group};
901  my $gsth = $self->prepare( "
902  INSERT INTO associated_group
903  ( description )
904  VALUES ( ? )" );
905  $gsth->bind_param( 1, $ax_group, SQL_VARCHAR );
906  $gsth->execute();
907  my $associatedGid = $self->last_insert_id('associated_group_id', undef, 'associated_group');
908 
909  foreach my $ax_rank (sort keys %{ $group }) {
910  my @ax = @{ $group->{$ax_rank} };
911 
912  my $associatedXid = undef;
913  my $sourceXid = undef;
914 
915  if (!$ax[0]->dbID) {
916  $self->store($ax[0]);
917  }
918  $associatedXid = $ax[0]->dbID;
919 
920  if (!$ax[1]->dbID) {
921  $self->store($ax[1]);
922  }
923  $sourceXid = $ax[1]->dbID;
924 
925  if (!defined $associatedXid || !defined $sourceXid) {
926  next;
927  }
928  $sth->bind_param( 1, $object_xref_id, SQL_INTEGER );
929  $sth->bind_param( 2, $associatedXid, SQL_INTEGER );
930  $sth->bind_param( 3, $sourceXid, SQL_INTEGER );
931  $sth->bind_param( 4, $ax[2], SQL_VARCHAR );
932  $sth->bind_param( 5, $associatedGid, SQL_VARCHAR );
933  $sth->bind_param( 6, $ax_rank, SQL_INTEGER );
934  $sth->execute();
935  }
936  } #end foreach
937  } #end elsif
938  return $object_xref_id;
939 }
940 
941 
942 sub _store_dependent_xref_mapping {
943  my $self = shift;
944  my $object_xref_id = shift;
945  my $dbEntry = shift;
946  my $master_xref = shift;
947 
948  my $insert_ignore = $self->insert_ignore_clause();
949 
950  my $sth = $self->prepare( "
951  ${insert_ignore} INTO dependent_xref
952  ( object_xref_id,
953  master_xref_id,
954  dependent_xref_id )
955  VALUES ( ?, ?, ?)" );
956  $sth->bind_param( 1, $object_xref_id, SQL_INTEGER );
957  $sth->bind_param( 2, $master_xref->dbID, SQL_INTEGER );
958  $sth->bind_param( 3, $dbEntry->dbID, SQL_INTEGER );
959 
960  $sth->execute();
961 
962 }
963 
964 =head2 get_external_db_id
965 
966  Arg [1] : String
967  The external DB name to query by. Supports LIKE statements
968  Arg [2] : String (optional)
969  External DB release to use. If not specified then we
970  will search for NULL db_release entries
971  Arg [3] : Boolean (optional)
972  If true we will never look at the db_release value
973  when querying for an external db id
974  Description: Looks for the internal identifier of an external DB. You can
975  search using direct equality or using like statements specify. We
976  only return one value from this method.
977 
978  If you want more than one entry use get_external_db_ids().
979  Exceptions : None
980  Returntype : Int
981 
982 =cut
983 
984 sub get_external_db_id {
985  my $self = shift;
986  my $db_ids = $self->get_external_db_ids(@_);
987  return shift @$db_ids;
988 }
989 
990 =head2 get_external_db_ids
991 
992  Arg [1] : String
993  The external DB name to query by. Supports LIKE statements
994  Arg [2] : String (optional)
995  External DB release to use. If not specified then we
996  will search for NULL db_release entries
997  Arg [3] : Boolean (optional)
998  If true we will never look at the db_release value
999  when querying for an external db id
1000  Description: Looks for the internal identifier of an external DB. You can
1001  search using direct equality or using like statements specify. We
1002  only return one value from this method. Returns more than one value
1003  Exceptions : None
1004  Returntype : ArrayRef of Int
1005 
1006 =cut
1007 
1008 sub get_external_db_ids {
1009  my ($self, $db_name, $db_release, $ignore_release) = @_;
1010  my $sql_helper = $self->dbc->sql_helper;
1011  my $sql = 'SELECT external_db_id FROM external_db WHERE db_name';
1012  if ($db_name =~ /%|_/) {
1013  $sql .= ' LIKE ?';
1014  } else {
1015  $sql .= ' = ?';
1016  }
1017  my @bound_params;
1018  push @bound_params,$db_name;
1019  unless ($ignore_release) {
1020  if ($db_release) {
1021  $sql .= ' AND db_release = ?';
1022  push @bound_params,$db_release;
1023  }
1024  else {
1025  $sql .= ' AND db_release is NULL';
1026  }
1027  }
1028 
1029  my @db_ids = @{ $sql_helper->execute_simple(-SQL => $sql, -PARAMS => \@bound_params) };
1030  return \@db_ids;
1031 }
1032 
1033 =head2 get_distinct_external_dbs
1034 
1035  Description: Queries the external_db table for all unique
1036  DB names
1037  Exceptions : None
1038  Returntype : ArrayRef of String
1039 
1040 =cut
1041 
1042 sub get_distinct_external_dbs {
1043  my ($self) = @_;
1044  my $sql = 'SELECT DISTINCT db_name FROM external_db';
1045  my $names = $self->dbc->sql_helper->execute_simple(-SQL => $sql);
1046  return [ sort @{$names} ];
1047 }
1048 
1049 =head2 _check_external_db
1050 
1051  Arg [1] : DBEntry object
1052  Arg [2] : Ignore version flag
1053  Description: Looks for a record of the given external database
1054  Exceptions : Throws on missing external database entry
1055  Returntype : Int
1056 
1057 =cut
1058 
1059 sub _check_external_db {
1060  my ($self,$db_entry,$ignore) = @_;
1061  my ($db_name,$db_release);
1062  $db_name = $db_entry->dbname();
1063  $db_release = $db_entry->release();
1064  my $db_id = $self->get_external_db_id($db_name, $db_release, $ignore);
1065 
1066  if ($db_id) {
1067  return $db_id;
1068  }
1069  else {
1070  throw( sprintf( "external_db [%s] release [%s] does not exist",
1071  $db_name, $db_release)
1072  );
1073  }
1074 }
1075 
1076 =head2 _store_or_fetch_xref
1077 
1078  Arg [1] : DBEntry object
1079  Arg [2] : Database accession for external database
1080  Description: Thread-safe method for adding xrefs, or otherwise returning
1081  an xref ID for the inserted or retrieved xref. Also inserts
1082  synonyms for that xref when entire new
1083  Returns : Int - the DB ID of the xref after insertion
1084 =cut
1085 
1086 sub _store_or_fetch_xref {
1087  my $self = shift;
1088  my $dbEntry = shift;
1089  my $dbRef = shift;
1090  my $xref_id;
1091 
1092  my $display_id = $dbEntry->display_id;
1093  $display_id = '' unless defined $display_id; # SQLite doesn't ignore NOT NULL errors
1094 
1095  my $insert_ignore = $self->insert_ignore_clause();
1096  my $sth = $self->prepare( "
1097  ${insert_ignore} INTO xref
1098  ( dbprimary_acc,
1099  display_label,
1100  version,
1101  description,
1102  external_db_id,
1103  info_type,
1104  info_text )
1105  VALUES ( ?, ?, ?, ?, ?, ?, ? ) ");
1106  $sth->bind_param(1, $dbEntry->primary_id,SQL_VARCHAR);
1107  $sth->bind_param(2, $display_id,SQL_VARCHAR);
1108  $sth->bind_param(3, ($dbEntry->version || q{0}),SQL_VARCHAR);
1109  $sth->bind_param(4, $dbEntry->description,SQL_VARCHAR);
1110  $sth->bind_param(5, $dbRef,SQL_INTEGER);
1111  $sth->bind_param(6, ($dbEntry->info_type || 'NONE'), SQL_VARCHAR);
1112  $sth->bind_param(7, ($dbEntry->info_text || ''), SQL_VARCHAR);
1113 
1114  my $count = $sth->execute();
1115  $xref_id = $self->last_insert_id('xref_id',undef,'xref') if $count > 0;
1116  $sth->finish();
1117 
1118  if ($xref_id) { #insert was successful, store supplementary synonyms
1119  # thread safety no longer an issue.
1120  my $synonym_check_sth = $self->prepare(
1121  "SELECT xref_id, synonym
1122  FROM external_synonym
1123  WHERE xref_id = ?
1124  AND synonym = ?");
1125 
1126  my $synonym_store_sth = $self->prepare(
1127  "${insert_ignore} INTO external_synonym
1128  ( xref_id, synonym ) VALUES ( ?, ? ) ");
1129 
1130  my $synonyms = $dbEntry->get_all_synonyms();
1131  foreach my $syn ( @$synonyms ) {
1132  $synonym_check_sth->bind_param(1,$xref_id,SQL_INTEGER);
1133  $synonym_check_sth->bind_param(2,$syn,SQL_VARCHAR);
1134  $synonym_check_sth->execute();
1135  my ($dbSyn) = $synonym_check_sth->fetchrow_array();
1136  $synonym_store_sth->bind_param(1,$xref_id,SQL_INTEGER);
1137  $synonym_store_sth->bind_param(2,$syn,SQL_VARCHAR);
1138  $synonym_store_sth->execute() if(!$dbSyn);
1139  }
1140  $synonym_check_sth->finish();
1141  $synonym_store_sth->finish();
1142 
1143  } else { # xref_id already exists, retrieve it according to fields in the unique key
1144  my $sql = 'SELECT xref_id FROM xref
1145  WHERE dbprimary_acc = ?
1146  AND version =?
1147  AND external_db_id = ?
1148  AND info_type = ?
1149  AND info_text = ?';
1150  my $info_type = $dbEntry->info_type() || 'NONE';
1151  my $info_text = $dbEntry->info_text() || q{};
1152  my $version = $dbEntry->version() || q{0};
1153  $sth = $self->prepare( $sql );
1154 
1155  $sth->bind_param(1, $dbEntry->primary_id,SQL_VARCHAR);
1156  $sth->bind_param(2, $version, SQL_VARCHAR);
1157  $sth->bind_param(3, $dbRef, SQL_INTEGER);
1158  $sth->bind_param(4, $info_type, SQL_VARCHAR);
1159  $sth->bind_param(5, $info_text, SQL_VARCHAR);
1160  $sth->execute();
1161 
1162 
1163 
1164  ($xref_id) = $sth->fetchrow_array();
1165  $sth->finish;
1166  if(!$xref_id) {
1167  my $msg = 'Cannot find an xref id for %s (version=%d) with external db id %d.';
1168  throw(sprintf($msg, $dbEntry->primary_id(), $version, $dbRef))
1169  }
1170  }
1171 
1172  return $xref_id;
1173 }
1174 
1175 =head2 exists
1176 
1177  Arg [1] : Bio::EnsEMBL::DBEntry $dbe
1178  Example : if($dbID = $db_entry_adaptor->exists($dbe)) { do stuff; }
1179  Description: Returns the db id of this DBEntry if it exists in this database
1180  otherwise returns undef. Exists is defined as an entry with
1181  the same external_db and display_id
1182  Returntype : int
1183  Exceptions : thrown on incorrect args
1184  Caller : GeneAdaptor::store, TranscriptAdaptor::store
1185  Status : Stable
1186 
1187 =cut
1188 
1189 sub exists {
1190  my ($self, $dbe) = @_ ;
1191 
1192  unless($dbe && ref $dbe && $dbe->isa('Bio::EnsEMBL::DBEntry')) {
1193  throw("arg must be a Bio::EnsEMBL::DBEntry not [$dbe]");
1194  }
1195 
1196  my $sth = $self->prepare('SELECT x.xref_id
1197  FROM xref x, external_db xdb
1198  WHERE x.external_db_id = xdb.external_db_id
1199  AND x.display_label = ?
1200  AND xdb.db_name = ?
1201  AND x.dbprimary_acc = ?');
1202 
1203  $sth->bind_param(1,$dbe->display_id,SQL_VARCHAR);
1204  $sth->bind_param(2,$dbe->dbname,SQL_VARCHAR);
1205  $sth->bind_param(3,$dbe->primary_id,SQL_VARCHAR);
1206  $sth->execute();
1207 
1208  my ($dbID) = $sth->fetchrow_array;
1209 
1210  $sth->finish;
1211 
1212  return $dbID;
1213 }
1214 
1215 
1216 =head2 fetch_all_by_Gene
1217 
1218  Arg [1] : Bio::EnsEMBL::Gene $gene
1219  (The gene to retrieve DBEntries for)
1220  Arg [2] : optional external database name. SQL wildcards are accepted
1221  Arg [3] : optional external_db type. SQL wildcards are accepted
1222  Example : @db_entries = @{$db_entry_adaptor->fetch_all_by_Gene($gene)};
1223  Description: This returns a list of DBEntries associated with this gene.
1224  Note that this method was changed in release 15. Previously
1225  it set the DBLinks attribute of the gene passed in to contain
1226  all of the gene, transcript, and translation xrefs associated
1227  with this gene.
1228  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
1229  there is mapping data, or OntologyXref if there is linkage data.
1230  Exceptions : thows if gene object not passed
1231  Caller : Bio::EnsEMBL::Gene
1232  Status : Stable
1233 
1234 =cut
1235 
1236 sub fetch_all_by_Gene {
1237  my ( $self, $gene, $ex_db_reg, $exdb_type ) = @_;
1238 
1239  if(!ref($gene) || !$gene->isa('Bio::EnsEMBL::Gene')) {
1240  throw("Bio::EnsEMBL::Gene argument expected.");
1241  }
1242 
1243  return $self->_fetch_by_object_type($gene->dbID(), 'Gene', $ex_db_reg, $exdb_type);
1244 }
1245 
1246 =head2 fetch_all_by_Operon
1247 
1248  Arg [1] : Bio::EnsEMBL::Operon $operon
1249  (The operon to retrieve DBEntries for)
1250  Arg [2] : optional external database name. SQL wildcards are accepted
1251  Arg [3] : optional external_db type. SQL wildcards are accepted
1252  Example : @db_entries = @{$db_entry_adaptor->fetch_all_by_Operon($operon)};
1253  Description: This returns a list of DBEntries associated with this operon.
1254  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
1255  there is mapping data, or OntologyXref if there is linkage data.
1256  Exceptions : thows if operon object not passed
1257  Caller : general
1258 
1259 =cut
1260 
1261 sub fetch_all_by_Operon {
1262  my ( $self, $gene, $ex_db_reg, $exdb_type ) = @_;
1263 
1264  if(!ref($gene) || !$gene->isa('Bio::EnsEMBL::Operon')) {
1265  throw("Bio::EnsEMBL::Operon argument expected.");
1266  }
1267 
1268  return $self->_fetch_by_object_type($gene->dbID(), 'Operon', $ex_db_reg, $exdb_type);
1269 }
1270 
1271 
1272 =head2 fetch_all_by_Transcript
1273 
1274  Arg [1] : Bio::EnsEMBL::Transcript
1275  Arg [2] : optional external database name. SQL wildcards are accepted
1276  Arg [3] : optional external_db type. SQL wildcards are accepted
1277  Example : @db_entries = @{$db_entry_adaptor->fetch_all_by_Transcript($trans)};
1278  Description: This returns a list of DBEntries associated with this
1279  transcript. Note that this method was changed in release 15.
1280  Previously it set the DBLinks attribute of the gene passed in
1281  to contain all of the gene, transcript, and translation xrefs
1282  associated with this gene.
1283  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
1284  there is mapping data, or OntologyXref if there is linkage data.
1285  Exceptions : throes if transcript argument not passed
1286  Caller : Bio::EnsEMBL::Transcript
1287  Status : Stable
1288 
1289 =cut
1290 
1291 sub fetch_all_by_Transcript {
1292  my ( $self, $trans, $ex_db_reg, $exdb_type ) = @_;
1293 
1294  if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Transcript')) {
1295  throw("Bio::EnsEMBL::Transcript argument expected.");
1296  }
1297 
1298  return $self->_fetch_by_object_type( $trans->dbID(), 'Transcript', $ex_db_reg, $exdb_type);
1299 }
1300 
1301 
1302 =head2 fetch_all_by_Translation
1303 
1304  Arg [1] : Bio::EnsEMBL::Translation $trans
1305  (The translation to fetch database entries for)
1306  Arg [2] : optional external database name. SQL wildcards are accepted
1307  Arg [3] : optional externaldb type. SQL wildcards are accepted
1308  Example : @db_entries = @{$db_entry_adptr->fetch_all_by_Translation($trans)};
1309  Description: Retrieves external database entries for an EnsEMBL translation
1310  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
1311  there is mapping data, or OntologyXref if there is linkage data.
1312  Exceptions : throws if translation object not passed
1313  Caller : general
1314  Status : Stable
1315 
1316 =cut
1317 
1318 sub fetch_all_by_Translation {
1319  my ( $self, $trans, $ex_db_reg, $exdb_type ) = @_;
1320 
1321  if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Translation')) {
1322  throw('Bio::EnsEMBL::Translation argument expected.');
1323  }
1324  if( ! $trans->dbID ){
1325  warning( "Cannot fetch_all_by_Translation without a dbID" );
1326  return [];
1327  }
1328 
1329  return $self->_fetch_by_object_type( $trans->dbID(), 'Translation', $ex_db_reg, $exdb_type );
1330 }
1331 
1332 
1333 =head2 fetch_all_by_RNAProduct
1334 
1335  Arg [1] : Bio::EnsEMBL::RNAProduct $rp
1336  (The rnaproduct to fetch database entries for)
1337  Arg [2] : optional external database name. SQL wildcards are accepted
1338  Arg [3] : optional externaldb type. SQL wildcards are accepted
1339  Example : @db_entries = @{$db_entry_adptr->fetch_all_by_RNAProduct($rp)};
1340  Description: Retrieves external database entries for an EnsEMBL rnaproduct
1341  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
1342  there is mapping data, or OntologyXref if there is linkage data.
1343  Exceptions : throws if rnaproduct object not passed
1344  Caller : general
1345  Status : Stable
1346 
1347 =cut
1348 
1349 sub fetch_all_by_RNAProduct {
1350  my ($self, $rp, $ex_db_reg, $ex_db_type) = @_;
1351 
1352  if (!ref($rp) || !$rp->isa('Bio::EnsEMBL::RNAProduct')) {
1353  throw('Bio::EnsEMBL::RNAProduct argument expected.');
1354  }
1355  if (!$rp->dbID()){
1356  warning("Cannot fetch_all_by_RNAProduct without a dbID");
1357  return [];
1358  }
1359 
1360  return $self->_fetch_by_object_type($rp->dbID(), 'RNAProduct', $ex_db_reg, $ex_db_type);
1361 }
1362 
1363 
1364 
1365 =head2 remove_from_object
1366 
1367  Arg [1] : Bio::EnsEMBL::DBEntry $dbe - The external reference which
1368  is to be disassociated from an ensembl object.
1369  Arg [2] : Bio::EnsEMBL::Storable $object - The ensembl object the
1370  external reference is to be disassociated from
1371  Arg [3] : string $object_type - The type of the ensembl object.
1372  E.g. 'Gene', 'Transcript', 'Translation'
1373  Example :
1374  # remove all dbentries from this translation
1375  foreach my $dbe (@{$translation->get_all_DBEntries()}) {
1376  $dbe_adaptor->remove($dbe, $translation, 'Translation');
1377  }
1378  Description: Removes an association between an ensembl object and a
1379  DBEntry (xref). This does not remove the actual xref from
1380  the database, only its linkage to the ensembl object.
1381  Returntype : none
1382  Exceptions : Throw on incorrect arguments.
1383  Warning if object or dbentry is not stored in this database.
1384  Caller : TranscriptAdaptor::remove, GeneAdaptor::remove,
1385  TranslationAdaptor::remove
1386  Status : Stable
1387 
1388 =cut
1389 
1390 sub remove_from_object {
1391  my $self = shift;
1392  my $dbe = shift;
1393  my $object = shift;
1394  my $object_type = shift;
1395 
1396  if(!ref($dbe) || !$dbe->isa('Bio::EnsEMBL::DBEntry')) {
1397  throw("Bio::EnsEMBL::DBEntry argument expected.");
1398  }
1399 
1400  if(!ref($object) || !$dbe->isa('Bio::EnsEMBL::Storable')) {
1401  throw("Bio::EnsEMBL::Storable argument expected.");
1402  }
1403 
1404  if(!$object_type) {
1405  throw("object_type string argument expected.");
1406  }
1407 
1408  # make sure both the dbentry and the object it is allegedly linked to
1409  # are stored in this database
1410 
1411  if(!$object->is_stored($self->db())) {
1412  warning("Cannot remove DBEntries for $object_type " . $object->dbID() .
1413  ". Object is not stored in this database.");
1414  return;
1415  }
1416 
1417  if(!$dbe->is_stored($self->db())) {
1418  warning("Cannot remove DBEntry ".$dbe->dbID() . ". Is not stored " .
1419  "in this database.");
1420  return;
1421  }
1422 
1423  # obtain the identifier of the link from the object_xref table
1424  #No need to compare linkage_annotation here
1425  my $sth = $self->prepare
1426  ("SELECT ox.object_xref_id " .
1427  "FROM object_xref ox ".
1428  "WHERE ox.xref_id = ? " .
1429  "AND ox.ensembl_id = ? " .
1430  "AND ox.ensembl_object_type = ?");
1431  $sth->bind_param(1,$dbe->dbID,SQL_INTEGER);
1432  $sth->bind_param(2,$object->dbID,SQL_INTEGER);
1433  $sth->bind_param(3,$object_type,SQL_VARCHAR);
1434  $sth->execute();
1435 
1436  my ($ox_id) = $sth->fetchrow_array();
1437 
1438  if(!$sth->rows() == 1) {
1439  $sth->finish();
1440  return;
1441  }
1442 
1443  $sth->finish();
1444 
1445  # delete from the tables which contain additional linkage information
1446 
1447  $sth = $self->prepare("DELETE FROM ontology_xref WHERE object_xref_id = ?");
1448  $sth->bind_param(1,$ox_id,SQL_INTEGER);
1449  $sth->execute();
1450  $sth->finish();
1451 
1452  $sth = $self->prepare("DELETE FROM associated_xref WHERE object_xref_id = ?");
1453  $sth->bind_param(1,$ox_id,SQL_INTEGER);
1454  $sth->execute();
1455  $sth->finish();
1456 
1457  $sth = $self->prepare("DELETE FROM identity_xref WHERE object_xref_id = ?");
1458  $sth->bind_param(1,$ox_id,SQL_INTEGER);
1459  $sth->execute();
1460  $sth->finish();
1461 
1462  # delete the actual linkage itself
1463  $sth = $self->prepare("DELETE FROM object_xref WHERE object_xref_id = ?");
1464  $sth->bind_param(1,$ox_id,SQL_INTEGER);
1465  $sth->execute();
1466  $sth->finish();
1467 
1468  return;
1469 }
1470 
1471 
1472 =head2 _fetch_by_object_type
1473 
1474  Arg [1] : string $ensID
1475  Arg [2] : string $ensType (object type to be returned)
1476  Arg [3] : optional $exdbname (external database name)
1477  (may be an SQL pattern containing '%' which matches any
1478  number of characters)
1479  Arg [4] : optional $exdb_type (external database type)
1480  (may be an SQL pattern containing '%' which matches any
1481  number of characters)
1482  Example : $self->_fetch_by_object_type( $translation_id, 'Translation' )
1483  Description: Fetches DBEntry by Object type
1484  NOTE: In a multi-species database, this method will
1485  return all the entries matching the search criteria, not
1486  just the ones associated with the current species.
1487 
1488 
1489  Returntype : arrayref of DBEntry objects; may be of type IdentityXref if
1490  there is mapping data, or OntologyXref if there is linkage data.
1491  Exceptions : none
1492  Caller : fetch_all_by_Gene
1493  fetch_all_by_Translation
1494  fetch_all_by_Transcript
1495  Status : Stable
1496 
1497 =cut
1498 
1499 sub _fetch_by_object_type {
1500  my ( $self, $ensID, $ensType, $exdbname, $exdb_type ) = @_;
1501 
1502  my @out;
1503 
1504  if ( !defined($ensID) ) {
1505  throw("Can't fetch_by_EnsObject_type without an object");
1506  }
1507 
1508  if ( !defined($ensType) ) {
1509  throw("Can't fetch_by_EnsObject_type without a type");
1510  }
1511 
1512  ### TODO - SCHEMA VERSION HACK. Please remove sometime in the future once we do not want backwards compatibility
1513  my $schema_version = $self->schema_version();
1514 
1515  # my $sth = $self->prepare("
1516  my $columns_sql = (<<COLUMNS_SQL);
1517  SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label, xref.version,
1518  exDB.priority,
1519  exDB.db_name, exDB.db_release, exDB.status, exDB.db_display_name,
1520  exDB.secondary_db_name, exDB.secondary_db_table,
1521  oxr.object_xref_id,
1522  es.synonym,
1523  idt.xref_identity, idt.ensembl_identity, idt.xref_start,
1524  idt.xref_end, idt.ensembl_start, idt.ensembl_end,
1525  idt.cigar_line, idt.score, idt.evalue, oxr.analysis_id,
1526  gx.linkage_type,
1527  xref.info_type, xref.info_text, exDB.type, gx.source_xref_id,
1528  oxr.linkage_annotation, xref.description
1529 COLUMNS_SQL
1530 
1531  my $tables_sql = <<'TABLES_SQL';
1532  FROM (xref xref, external_db exDB, object_xref oxr)
1533  LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
1534  LEFT JOIN identity_xref idt on idt.object_xref_id = oxr.object_xref_id
1535  LEFT JOIN ontology_xref gx on gx.object_xref_id = oxr.object_xref_id
1536 TABLES_SQL
1537 
1538  my $where_sql = <<'WHERE_SQL';
1539  WHERE xref.xref_id = oxr.xref_id
1540  AND xref.external_db_id = exDB.external_db_id
1541  AND oxr.ensembl_id = ?
1542  AND oxr.ensembl_object_type = ?
1543 WHERE_SQL
1544 
1545  if($schema_version >= 72) {
1546  $columns_sql .= ','; #need the extra comma
1547  $columns_sql .= <<'EXTRA';
1548  ax.xref_id, ax.source_xref_id, ax.condition_type, ax.associated_group_id, ax.rank
1549 EXTRA
1550  $tables_sql .= <<'EXTRA';
1551  LEFT JOIN associated_xref ax ON ax.object_xref_id = oxr.object_xref_id
1552  LEFT JOIN associated_group ag ON ax.associated_group_id = ag.associated_group_id
1553 EXTRA
1554 
1555  }
1556 
1557  #Join the above together into one statement
1558  my $sql = "$columns_sql
1559 $tables_sql
1560 $where_sql";
1561  # warn $sql; # uncomment me to see the full SQL generated
1562 
1563  if ( defined($exdbname) ) {
1564  if ( index( $exdbname, '%' ) != -1 ) {
1565  $sql .= " AND exDB.db_name LIKE "
1566  . $self->dbc()->db_handle()->quote( $exdbname, SQL_VARCHAR );
1567  } else {
1568  $sql .= " AND exDB.db_name = "
1569  . $self->dbc()->db_handle()->quote( $exdbname, SQL_VARCHAR );
1570  }
1571  }
1572 
1573  if ( defined($exdb_type) ) {
1574  if ( index( $exdb_type, '%' ) != -1 ) {
1575  $sql .= " AND exDB.type LIKE "
1576  . $self->dbc()->db_handle()->quote( $exdb_type, SQL_VARCHAR );
1577  } else {
1578  $sql .= " AND exDB.type = "
1579  . $self->dbc()->db_handle()->quote( $exdb_type, SQL_VARCHAR );
1580  }
1581  }
1582 
1583  my $sth = $self->prepare($sql);
1584 
1585  $sth->bind_param( 1, $ensID, SQL_INTEGER );
1586  $sth->bind_param( 2, $ensType, SQL_VARCHAR );
1587  $sth->execute();
1588 
1589  my ( %seen, %linkage_types, %synonyms, %associated_xrefs );
1590 
1591  my $max_rows = 1000;
1592 
1593  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
1594  while ( my $arrRef = shift( @{$rowcache} ) ) {
1595  my ( $refID, $dbprimaryId,
1596  $displayid, $version,
1597  $priority,
1598  $dbname, $release,
1599  $exDB_status, $exDB_db_display_name,
1600  $exDB_secondary_db_name, $exDB_secondary_db_table,
1601  $objid, $synonym,
1602  $xrefid, $ensemblid,
1603  $xref_start, $xref_end,
1604  $ensembl_start, $ensembl_end,
1605  $cigar_line, $score,
1606  $evalue, $analysis_id,
1607  $linkage_type, $info_type,
1608  $info_text, $type,
1609  $source_xref_id, $link_annotation,
1610  $description, $associated_xref_id,
1611  $source_associated_xref_id, $condition_type,
1612  $associate_group_id, $associate_group_rank
1613  ) = @$arrRef;
1614 
1615  my $linkage_key =
1616  ( $linkage_type || '' ) . ( $source_xref_id || '' );
1617 
1618  my $associated_key =
1619  ( $condition_type || '' )
1620  . ( $source_associated_xref_id || '' )
1621  . ( $associated_xref_id || '' );
1622 
1623 
1624  my $analysis = undef;
1625  if ( defined($analysis_id) ) {
1626  $analysis =
1627  $self->db()->get_AnalysisAdaptor()->fetch_by_dbID($analysis_id);
1628  }
1629 
1630  my %obj_hash = ( 'adaptor' => $self,
1631  'dbID' => $refID,
1632  'primary_id' => $dbprimaryId,
1633  'display_id' => $displayid,
1634  'version' => $version,
1635  'release' => $release,
1636  'info_type' => $info_type,
1637  'info_text' => $info_text,
1638  'type' => $type,
1639  'secondary_db_name' => $exDB_secondary_db_name,
1640  'secondary_db_table' => $exDB_secondary_db_table,
1641  'dbname' => $dbname,
1642  'description' => $description,
1643  'linkage_annotation' => $link_annotation,
1644  'analysis' => $analysis,
1645  'ensembl_object_type' => $ensType,
1646  'ensembl_id' => $ensID );
1647 
1648  # Using an outer join on the synonyms as well as on identity_xref,
1649  # we now have to filter out the duplicates (see v.1.18 for
1650  # original). Since there is at most one identity_xref row per
1651  # xref, this is easy enough; all the 'extra' bits are synonyms.
1652  my $source_xref;
1653  my $associated_xref;
1654  my $source_associated_xref;
1655  if ( !$seen{$refID} ) {
1656 
1657  my $exDB;
1658  if ( ( defined($xrefid) ) ) { # an xref with similarity scores
1659  $exDB = Bio::EnsEMBL::IdentityXref->new_fast( \%obj_hash );
1660  $exDB->xref_identity($xrefid);
1661  $exDB->ensembl_identity($ensemblid);
1662 
1663  $exDB->cigar_line($cigar_line);
1664  $exDB->xref_start($xref_start);
1665  $exDB->xref_end($xref_end); # was not here before 14th Jan 2009 ????
1666  $exDB->ensembl_start($ensembl_start);
1667  $exDB->ensembl_end($ensembl_end);
1668  $exDB->score($score);
1669  $exDB->evalue($evalue);
1670 
1671  } elsif ( defined $linkage_type && $linkage_type ne "" ) {
1672  $exDB = Bio::EnsEMBL::OntologyXref->new_fast( \%obj_hash );
1673  $source_xref = ( defined($source_xref_id)
1674  ? $self->fetch_by_dbID($source_xref_id)
1675  : undef );
1676  $exDB->add_linkage_type( $linkage_type, $source_xref || () );
1677  $linkage_types{$refID}->{$linkage_key} = 1;
1678 
1679  # Add associated Xref annotations to the OntologyXref entry.
1680  if ( defined $associated_xref_id && $associated_xref_id ne "" ) {
1681  $exDB = Bio::EnsEMBL::OntologyXref->new_fast( \%obj_hash );
1682  $associated_xref = ( defined($associated_xref_id)
1683  ? $self->fetch_by_dbID($associated_xref_id)
1684  : undef );
1685  $source_associated_xref = ( defined($source_associated_xref_id)
1686  ? $self->fetch_by_dbID($source_associated_xref_id)
1687  : undef );
1688  if ( defined($associated_xref) ) {
1689  my $ct = '';
1690  if ( defined $condition_type ) {
1691  $ct = $condition_type;
1692  }
1693  $exDB->add_linked_associated_xref( $associated_xref, $source_associated_xref, $ct, $associate_group_id, $associate_group_rank );
1694  }
1695  }
1696 
1697  } else {
1698  $exDB = Bio::EnsEMBL::DBEntry->new_fast( \%obj_hash );
1699  }
1700 
1701  if ( defined($exDB_status) ) { $exDB->status($exDB_status) }
1702 
1703  $exDB->priority($priority);
1704  $exDB->db_display_name($exDB_db_display_name);
1705 
1706  push( @out, $exDB );
1707  $seen{$refID} = $exDB;
1708 
1709  } ## end if ( !$seen{$refID} )
1710 
1711  # $exDB still points to the same xref, so we can keep adding GO
1712  # evidence tags or synonyms.
1713 
1714  if ( defined($synonym) && !$synonyms{$refID}->{$synonym} ) {
1715  if ( defined($synonym) ) {
1716  $seen{$refID}->add_synonym($synonym);
1717  }
1718  $synonyms{$refID}->{$synonym} = 1;
1719  }
1720 
1721  if ( defined($linkage_type)
1722  && $linkage_type ne ""
1723  && !$linkage_types{$refID}->{$linkage_key}
1724  && check_ref($seen{$refID}, 'Bio::EnsEMBL::OntologyXref') )
1725  {
1726  $source_xref = ( defined($source_xref_id)
1727  ? $self->fetch_by_dbID($source_xref_id)
1728  : undef );
1729  $seen{$refID}
1730  ->add_linkage_type( $linkage_type, $source_xref || () );
1731  $linkage_types{$refID}->{$linkage_key} = 1;
1732  }
1733 
1734  if ( defined($associated_xref_id)
1735  && $associated_xref_id ne ""
1736  && !$associated_xrefs{$refID}->{$associated_key} )
1737  {
1738  $associated_xref = ( defined($associated_xref_id)
1739  ? $self->fetch_by_dbID($associated_xref_id)
1740  : undef );
1741  $source_associated_xref = ( defined($source_associated_xref_id)
1742  ? $self->fetch_by_dbID($source_associated_xref_id)
1743  : undef );
1744  if ( defined($associated_xref) ) {
1745  my $ct = '';
1746  if ( defined $condition_type ) {
1747  $ct = $condition_type;
1748  }
1749  $seen{$refID}->add_linked_associated_xref( $associated_xref, $source_associated_xref, $ct, $associate_group_id, $associate_group_rank );
1750  }
1751 
1752  $linkage_types{$refID}->{$linkage_key} = 1;
1753  }
1754 
1755  } ## end while ( my $arrRef = shift...
1756  } ## end while ( my $rowcache = $sth...
1757 
1758  return \@out;
1759 } ## end sub _fetch_by_object_type
1760 
1761 =head2 list_gene_ids_by_external_db_id
1762 
1763  Arg [1] : string $external_id
1764  Example : @gene_ids = $dbea->list_gene_ids_by_external_db_id(1020);
1765  Description: Retrieve a list of geneid by an external identifier that
1766  is linked to any of the genes transcripts, translations
1767  or the gene itself.
1768  NOTE: If more than one external identifier has the
1769  same primary accession then genes for each of these is
1770  returned.
1771  NOTE: In a multi-species database, this method will
1772  return all the entries matching the search criteria, not
1773  just the ones associated with the current species.
1774  Returntype : list of ints
1775  Exceptions : none
1776  Caller : unknown
1777  Status : Stable
1778 
1779 =cut
1780 
1781 sub list_gene_ids_by_external_db_id {
1782  my ($self,$external_db_id, $linkage_type) = @_;
1783 
1784  my %T = map { ($_, 1) }
1785  $self->_type_by_external_db_id( $external_db_id, 'Translation', 'gene', $linkage_type ),
1786  $self->_type_by_external_db_id( $external_db_id, 'Transcript', 'gene', $linkage_type ),
1787  $self->_type_by_external_db_id( $external_db_id, 'Gene', undef, $linkage_type );
1788  return keys %T;
1789 }
1790 
1791 =head2 list_gene_ids_by_extids
1792 
1793  Arg [1] : string $external_name
1794  Arg [2] : (optional) string $external_db_name
1795  Arg [3] : Boolean override, see _type_by_external_id
1796  Example : @gene_ids = $dbea->list_gene_ids_by_extids('CDPX');
1797  Description: Retrieve a list of geneid by an external identifier that is
1798  linked to any of the genes transcripts, translations or the
1799  gene itself
1800  Returntype : list of ints
1801  Exceptions : none
1802  Caller : unknown
1803  Status : Stable
1804 
1805 =cut
1806 
1807 sub list_gene_ids_by_extids {
1808  my ( $self, $external_name, $external_db_name, $override ) = @_;
1809 
1810  my %T = map { ( $_, 1 ) }
1811  $self->_type_by_external_id( $external_name, 'Translation', 'gene',
1812  $external_db_name, $override ),
1813  $self->_type_by_external_id( $external_name, 'Transcript', 'gene',
1814  $external_db_name, $override ),
1815  $self->_type_by_external_id( $external_name, 'Gene', undef,
1816  $external_db_name, $override );
1817 
1818  return keys %T;
1819 }
1820 
1821 
1822 =head2 list_transcript_ids_by_extids
1823 
1824  Arg [1] : string $external_name
1825  Arg [2] : (optional) string $external_db_name
1826  Arg [3] : Boolean override, see _type_by_external_id
1827  Example : @tr_ids = $dbea->list_transcript_ids_by_extids('BCRA2');
1828  Description: Retrieve a list transcript ids by an external identifier that
1829  is linked to any of the genes transcripts, translations or the
1830  gene itself
1831  Returntype : list of ints
1832  Exceptions : none
1833  Caller : unknown
1834  Status : Stable
1835 
1836 =cut
1837 
1838 sub list_transcript_ids_by_extids {
1839  my ( $self, $external_name, $external_db_name, $override ) = @_;
1840 
1841  my %T = map { ( $_, 1 ) }
1842  $self->_type_by_external_id( $external_name, 'Translation',
1843  'transcript', $external_db_name, $override
1844  ),
1845  $self->_type_by_external_id( $external_name, 'Transcript', undef,
1846  $external_db_name, $override );
1847 
1848  return keys %T;
1849 }
1850 
1851 sub list_transcript_ids_by_external_db_id {
1852  my ( $self, $external_db_id, $linkage_type) = @_;
1853  my %T = map { ( $_, 1 ) }
1854  $self->_type_by_external_db_id( $external_db_id, 'Translation', 'transcript', $linkage_type ),
1855  $self->_type_by_external_db_id( $external_db_id, 'Transcript', 'transcript', $linkage_type );
1856  return keys %T;
1857 }
1858 
1859 =head2 list_translation_ids_by_extids
1860 
1861  Arg [1] : string $external_name
1862  Arg [2] : (optional) string $external_db_name
1863  Arg [3] : Boolean override, see _type_by_external_id
1864  Example : @tr_ids = $dbea->list_translation_ids_by_extids('GO:0004835');
1865  Description: Gets a list of translation IDs by external display IDs
1866  Returntype : list of Ints
1867  Exceptions : none
1868  Caller : unknown
1869  Status : Stable
1870 
1871 =cut
1872 
1873 sub list_translation_ids_by_extids {
1874  my ( $self, $external_name, $external_db_name, $override ) = @_;
1875 
1876  return
1877  $self->_type_by_external_id( $external_name, 'Translation', undef,
1878  $external_db_name, $override );
1879 }
1880 
1881 sub list_translation_ids_by_external_db_id {
1882  my ( $self, $external_db_id, $linkage_type) = @_;
1883  return $self->_type_by_external_db_id( $external_db_id, 'Translation', undef, $linkage_type ),
1884 }
1885 
1886 
1887 =head2 list_rnaproduct_ids_by_extids
1888 
1889  Arg [1] : string $external_name
1890  Arg [2] : (optional) string $external_db_name
1891  Arg [3] : Boolean override, see _type_by_external_id
1892  Example : @rp_ids = $dbea->list_rnaproduct_ids_by_extids('GO:0004835');
1893  Description: Gets a list of rnaproduct IDs by external display IDs
1894  Returntype : list of Ints
1895  Exceptions : none
1896  Caller : unknown
1897  Status : Stable
1898 
1899 =cut
1900 
1901 sub list_rnaproduct_ids_by_extids {
1902  my ($self, $external_name, $external_db_name, $override) = @_;
1903 
1904  return $self->_type_by_external_id($external_name, 'RNAProduct', undef,
1905  $external_db_name, $override);
1906 }
1907 
1908 
1909 =head2 _type_by_external_id
1910 
1911  Arg [1] : string $name - dbprimary_acc
1912  Arg [2] : string $ensType - ensembl_object_type
1913  Arg [3] : (optional) string $extraType
1914  Arg [4] : (optional) string $external_db_name
1915  other object type to be returned
1916  Arg [5] : Boolean override to force _ to be treated as an SQL 'any'
1917  This is usually optimised out for query speed due to
1918  large numbers of names like NM_00...
1919  Example : $self->_type_by_external_id($name, 'Translation');
1920  NOTE: In a multi-species database, this method will
1921  return all the entries matching the search criteria, not
1922  just the ones associated with the current species.
1923  SQL wildcards can be used in the external id,
1924  but overly generic queries (two characters) will be prevented.
1925  Description: Gets
1926  Returntype : list of dbIDs (gene_id, transcript_id, etc.)
1927  Exceptions : none
1928  Caller : list_translation_ids_by_extids
1929  translationids_by_extids
1930  geneids_by_extids
1931  Status : Stable
1932 
1933 =cut
1934 
1935 sub _type_by_external_id {
1936  my ( $self, $name, $ensType, $extraType, $external_db_name, $override ) = @_;
1937 
1938  # $name has SQL wildcard support
1939  # = or LIKE put into SQL statement, and open queries like % or A% are rejected.
1940  my $comparison_operator;
1941  if ($name =~ /[_%\[]/ ) {
1942  $comparison_operator = "LIKE";
1943  if ($name =~ /^.?%/ && !$override) {
1944  warn "External $ensType name $name is too vague and will monopolise database resources. Please use a more specific $ensType name.\n";
1945  return;
1946  }
1947  elsif ($name =~ /^\w\w_/ && !$override) {
1948  # For entries such as NM_00000065, escape the _ so that SQL LIKE does not have to scan entire table
1949  # Escape only the _ in the third character position
1950  $name =~ s/(?<=\w\w)(?=_)/\\/;
1951  }
1952  }
1953  else {
1954  $comparison_operator = "=";
1955  }
1956 
1957 
1958  my $from_sql = '';
1959  my $where_sql = '';
1960  my $ID_sql = 'oxr.ensembl_id';
1961 
1962  if ( defined($extraType) ) {
1963  if ( lc($extraType) eq 'translation' ) {
1964  $ID_sql = 'tl.translation_id';
1965  } else {
1966  $ID_sql = "t.${extraType}_id";
1967  }
1968 
1969  if ( lc($ensType) eq 'translation' ) {
1970  $from_sql = 'transcript t, translation tl, ';
1971  $where_sql = qq(
1972  t.transcript_id = tl.transcript_id AND
1973  tl.translation_id = oxr.ensembl_id AND
1974  t.is_current = 1 AND
1975  );
1976  } else {
1977  $from_sql = 'transcript t, ';
1978  $where_sql = 't.'
1979  . lc($ensType)
1980  . '_id = oxr.ensembl_id AND '
1981  . 't.is_current = 1 AND ';
1982  }
1983  }
1984 
1985  my $multispecies = $self->db()->is_multispecies();
1986 
1987  if ( lc($ensType) eq 'gene' ) {
1988  $from_sql = 'gene g, ';
1989  $from_sql .= 'seq_region s, coord_system cs, ' if $multispecies;
1990 
1991  $where_sql = 'g.gene_id = oxr.ensembl_id AND g.is_current = 1 AND ';
1992  if($multispecies) {
1993  $where_sql .= <<'SQL';
1994 g.seq_region_id = s.seq_region_id AND
1995 s.coord_system_id = cs.coord_system_id AND
1996 cs.species_id = ? AND
1997 SQL
1998  }
1999  }
2000  elsif ( lc($ensType) eq 'transcript' ) {
2001  $from_sql = 'transcript t, ';
2002  $from_sql .= 'seq_region s, coord_system cs, ' if $multispecies;
2003 
2004  $where_sql = 't.transcript_id = oxr.ensembl_id AND t.is_current = 1 AND ';
2005  if($multispecies) {
2006  $where_sql .= <<'SQL';
2007 t.seq_region_id = s.seq_region_id AND
2008 s.coord_system_id = cs.coord_system_id AND
2009 cs.species_id = ? AND
2010 SQL
2011  }
2012  }
2013  elsif ( lc($ensType) eq 'translation' ) {
2014  $from_sql = 'translation tl, transcript t, ';
2015  $from_sql .= 'seq_region s, coord_system cs, ' if $multispecies;
2016 
2017  $where_sql = 't.transcript_id = tl.transcript_id AND tl.translation_id = oxr.ensembl_id AND t.is_current = 1 AND ';
2018  if($multispecies) {
2019  $where_sql .= <<'SQL';
2020 t.seq_region_id = s.seq_region_id AND
2021 s.coord_system_id = cs.coord_system_id AND
2022 cs.species_id = ? AND
2023 SQL
2024  }
2025  }
2026 
2027  if ( defined($external_db_name) ) {
2028  # Involve the 'external_db' table to limit the hits to a particular
2029  # external database.
2030 
2031  $from_sql .= 'external_db xdb, ';
2032  $where_sql .=
2033  'xdb.db_name LIKE '
2034  . $self->dbc()->db_handle()->quote( $external_db_name . '%' )
2035  . ' AND xdb.external_db_id = x.external_db_id AND';
2036  }
2037 
2038  my @queries;
2039  push (@queries, qq(
2040  SELECT $ID_sql
2041  FROM $from_sql
2042  xref x,
2043  object_xref oxr
2044  WHERE $where_sql
2045  x.dbprimary_acc $comparison_operator ?
2046  AND x.xref_id = oxr.xref_id
2047  AND oxr.ensembl_object_type = ?
2048  ));
2049 
2050  push (@queries, qq(
2051  SELECT $ID_sql
2052  FROM $from_sql
2053  xref x,
2054  object_xref oxr
2055  WHERE $where_sql
2056  x.display_label $comparison_operator ?
2057  AND x.xref_id = oxr.xref_id
2058  AND oxr.ensembl_object_type = ?
2059  ));
2060 
2061  if ( defined($external_db_name) ) {
2062  # If we are given the name of an external database, we need to join
2063  # between the 'xref' and the 'object_xref' tables on 'xref_id'.
2064 
2065  push (@queries, qq(
2066  SELECT $ID_sql
2067  FROM $from_sql
2068  external_synonym syn,
2069  object_xref oxr,
2070  xref x
2071  WHERE $where_sql
2072  syn.synonym $comparison_operator ?
2073  AND syn.xref_id = oxr.xref_id
2074  AND oxr.ensembl_object_type = ?
2075  AND x.xref_id = oxr.xref_id));
2076 
2077  } else {
2078  # If we weren't given an external database name, we can get away
2079  # with less joins here.
2080 
2081  push (@queries, qq(
2082  SELECT $ID_sql
2083  FROM $from_sql
2084  external_synonym syn,
2085  object_xref oxr
2086  WHERE $where_sql
2087  syn.synonym $comparison_operator ?
2088  AND syn.xref_id = oxr.xref_id
2089  AND oxr.ensembl_object_type = ?));
2090 
2091  }
2092 
2093  my %result;
2094  my $h = $self->dbc()->sql_helper();
2095  my @params = ([$name, SQL_VARCHAR], [$ensType, SQL_VARCHAR]);
2096  unshift(@params, [$self->species_id(), SQL_INTEGER] ) if $multispecies;
2097  foreach my $query (@queries) {
2098  $h->execute_no_return(-SQL => $query, -PARAMS => \@params, -CALLBACK => sub {
2099  my ($row) = @_;
2100  my ($id) = @{$row};
2101  $result{$id} = 1;
2102  });
2103  }
2104 
2105  return keys(%result);
2106 
2107 } ## end sub _type_by_external_id
2108 
2109 =head2 _type_by_external_db_id
2110 
2111  Arg [1] : integer $type - external_db_id
2112  Arg [2] : string $ensType - ensembl_object_type
2113  Arg [3] : (optional) string $extraType
2114  other object type to be returned. This references the _id fields of
2115  the transcript table, and if left unset defaults to the translation_id
2116  from the translation table.
2117  Arg [4] : (optional) string $linkage_type
2118  provides a link to ontology_xref and limits by an ontology linkage type
2119  Example : $self->_type_by_external_db_id(1030, 'Translation');
2120  Description: Gets.
2121  NOTE: In a multi-species database, this method will
2122  return all the entries matching the search criteria, not
2123  just the ones associated with the current species.
2124  Returntype : list of dbIDs (gene_id, transcript_id, etc.)
2125  Exceptions : none
2126  Caller : list_translation_ids_by_extids
2127  translationids_by_extids
2128  geneids_by_extids
2129  Status : Stable
2130 
2131 =cut
2132 
2133 sub _type_by_external_db_id {
2134  my ($self, $external_db_id, $ensType, $extraType, $linkage_type) = @_;
2135  throw "No external DB identifer given" unless defined $external_db_id;
2136  throw "No Ensembl type given" unless defined $ensType;
2137 
2138  my $from_sql = '';
2139  my $where_sql = '';
2140  my $ID_sql = "oxr.ensembl_id";
2141  my $lcEnsType = lc($ensType);
2142 
2143  if (defined $extraType) {
2144  if (lc($extraType) eq 'translation') {
2145  $ID_sql = "tl.translation_id";
2146  } else {
2147  $ID_sql = "t.${extraType}_id";
2148  }
2149 
2150  if ($lcEnsType eq 'translation') {
2151  $from_sql = 'transcript t, translation tl, ';
2152  $where_sql = qq(
2153  t.transcript_id = tl.transcript_id AND
2154  tl.translation_id = oxr.ensembl_id AND
2155  t.is_current = 1 AND
2156  );
2157  } else {
2158  $from_sql = 'transcript t, ';
2159  $where_sql = 't.'.$lcEnsType.'_id = oxr.ensembl_id AND '.
2160  't.is_current = 1 AND ';
2161  }
2162  }
2163 
2164  if ($lcEnsType eq 'gene') {
2165  $from_sql = 'gene g, ';
2166  $where_sql = 'g.gene_id = oxr.ensembl_id AND g.is_current = 1 AND ';
2167  } elsif ($lcEnsType eq 'transcript') {
2168  $from_sql = 'transcript t, ';
2169  $where_sql = 't.transcript_id = oxr.ensembl_id AND t.is_current = 1 AND ';
2170  } elsif ($lcEnsType eq 'translation') {
2171  $from_sql = 'transcript t, translation tl, ';
2172  $where_sql = qq(
2173  t.transcript_id = tl.transcript_id AND
2174  tl.translation_id = oxr.ensembl_id AND
2175  t.is_current = 1 AND
2176  );
2177  }
2178 
2179  if($linkage_type) {
2180  $from_sql .= 'ontology_xref ontx, ';
2181  $where_sql .= 'oxr.object_xref_id = ontx.object_xref_id AND ';
2182  $where_sql .= 'ontx.linkage_type =? AND';
2183  }
2184 
2185  my $query =
2186  "SELECT $ID_sql
2187  FROM $from_sql xref x, object_xref oxr
2188  WHERE $where_sql x.external_db_id = ? AND
2189  x.xref_id = oxr.xref_id AND oxr.ensembl_object_type= ?";
2190 
2191  my @params;
2192  push(@params, [$linkage_type, SQL_VARCHAR]) if $linkage_type;
2193  push(@params,
2194  [$external_db_id, SQL_INTEGER],
2195  [$ensType, SQL_VARCHAR],
2196  );
2197 
2198  my %result;
2199  $self->dbc()->sql_helper()->execute_no_return(-SQL => $query, -PARAMS => \@params, -CALLBACK => sub {
2200  my ($row) = @_;
2201  my ($id) = @{$row};
2202  $result{$id} = 1;
2203  });
2204 
2205  return keys(%result);
2206 } ## end _type_by_external_db_id
2207 
2208 
2209 =head2 fetch_all_by_description
2210 
2211  Arg [1] : string description to search for. Include % etc in this string
2212  Arg [2] : <optional> string $dbname. Name of the database to search
2213 
2214  Example : @canc_refs = @{$db_entry_adaptor->fetch_all_by_description("%cancer%")};
2215  @db_entries = @{$db_entry_adaptor->fetch_all_by_description("%cancer%","MIM_MORBID")};
2216  Description: Retrieves DBEntries that match the description.
2217  Optionally you can search on external databases type.
2218  NOTE: In a multi-species database, this method will
2219  return all the entries matching the search criteria, not
2220  just the ones associated with the current species.
2221  Returntype : ref to array of Bio::EnsEMBL::DBSQL::DBEntry
2222  Exceptions : None.
2223  Caller : General
2224  Status : At Risk
2225 
2226 =cut
2227 
2228 sub fetch_all_by_description {
2229  my ( $self, $description, $dbname ) = @_;
2230 
2231  my @results = ();
2232 
2233  my $sql =
2234  "SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label,
2235  xref.version,
2236  exDB.priority,
2237  exDB.db_name, exDB.db_display_name, exDB.db_release, es.synonym,
2238  xref.info_type, xref.info_text, exDB.type, exDB.secondary_db_name,
2239  exDB.secondary_db_table, xref.description
2240  FROM (xref, external_db exDB)
2241  LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
2242  WHERE xref.description like ?
2243  AND xref.external_db_id = exDB.external_db_id";
2244 
2245  if ( defined($dbname) ) { $sql .= " AND exDB.db_name = ? " }
2246 
2247  my $sth = $self->prepare($sql);
2248 
2249  $sth->bind_param( 1, $description, SQL_VARCHAR );
2250 
2251  if ( defined($dbname) ) {
2252  $sth->bind_param( 2, $dbname, SQL_VARCHAR );
2253  }
2254 
2255  $sth->execute();
2256 
2257  my $max_rows = 1000;
2258 
2259  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
2260  while ( my $arrayref = shift( @{$rowcache} ) ) {
2261  my ( $dbID, $dbprimaryId,
2262  $displayid, $version,
2263  $priority,
2264  $ex_dbname, $db_display_name,
2265  $release, $synonym,
2266  $info_type, $info_text,
2267  $type, $secondary_db_name,
2268  $secondary_db_table, $description
2269  ) = @$arrayref;
2270 
2271  my $exDB =
2272  Bio::EnsEMBL::DBEntry->new(
2273  -adaptor => $self,
2274  -dbID => $dbID,
2275  -primary_id => $dbprimaryId,
2276  -display_id => $displayid,
2277  -version => $version,
2278  -release => $release,
2279  -dbname => $ex_dbname,
2280  -priority => $priority,
2281  -db_display_name => $db_display_name,
2282  -info_type => $info_type,
2283  -info_text => $info_text,
2284  -type => $type,
2285  -secondary_db_name => $secondary_db_name,
2286  -secondary_db_table => $secondary_db_table,
2287  -description => $description
2288  );
2289 
2290  if ($synonym) { $exDB->add_synonym($synonym) }
2291 
2292  push @results, $exDB;
2293 
2294  } ## end while ( my $arrayref = shift...
2295  } ## end while ( my $rowcache = $sth...
2296 
2297  $sth->finish();
2298 
2299  return \@results;
2300 } ## end sub fetch_all_by_description
2301 
2302 
2303 =head2 fetch_all_by_source
2304 
2305  Arg [1] : string source to search for. Include % etc in this string
2306  if you want to use SQL patterns
2307 
2308  Example : @unigene_refs = @{$db_entry_adaptor->fetch_all_by_source("%unigene%")};
2309  Description: Retrieves DBEntrys that match the source name.
2310  Returntype : ref to array of Bio::EnsEMBL::DBSQL::DBEntry
2311  Exceptions : None.
2312  Caller : General
2313  Status : At Risk
2314 
2315 =cut
2316 
2317 sub fetch_all_by_source {
2318  my ( $self, $source ) = @_;
2319 
2320  my @results = ();
2321 
2322  my $sql =
2323  "SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label,
2324  xref.version,
2325  exDB.priority,
2326  exDB.db_name, exDB.db_display_name, exDB.db_release, es.synonym,
2327  xref.info_type, xref.info_text, exDB.type, exDB.secondary_db_name,
2328  exDB.secondary_db_table, xref.description
2329  FROM (xref, external_db exDB)
2330  LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
2331  WHERE exDB.db_name like ?
2332  AND xref.external_db_id = exDB.external_db_id";
2333 
2334 
2335  my $sth = $self->prepare($sql);
2336 
2337  $sth->bind_param( 1, $source, SQL_VARCHAR );
2338 
2339  $sth->execute();
2340 
2341  my $max_rows = 1000;
2342 
2343  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
2344  while ( my $arrayref = shift( @{$rowcache} ) ) {
2345  my ( $dbID, $dbprimaryId,
2346  $displayid, $version,
2347  $priority,
2348  $dbname, $db_display_name,
2349  $release, $synonym,
2350  $info_type, $info_text,
2351  $type, $secondary_db_name,
2352  $secondary_db_table, $description
2353  ) = @$arrayref;
2354 
2355  my $exDB =
2356  Bio::EnsEMBL::DBEntry->new(
2357  -adaptor => $self,
2358  -dbID => $dbID,
2359  -primary_id => $dbprimaryId,
2360  -display_id => $displayid,
2361  -version => $version,
2362  -release => $release,
2363  -dbname => $dbname,
2364  -priority => $priority,
2365  -db_display_name => $db_display_name,
2366  -info_type => $info_type,
2367  -info_text => $info_text,
2368  -type => $type,
2369  -secondary_db_name => $secondary_db_name,
2370  -secondary_db_table => $secondary_db_table,
2371  -description => $description
2372  );
2373 
2374  if ($synonym) { $exDB->add_synonym($synonym) }
2375 
2376  push @results, $exDB;
2377 
2378  } ## end while ( my $arrayref = shift...
2379  } ## end while ( my $rowcache = $sth...
2380 
2381  $sth->finish();
2382 
2383  return \@results;
2384 } ## end sub fetch_all_by_source
2385 
2386 
2387 =head2 fetch_all_synonyms
2388 
2389  Arg [1] : dbID of DBEntry to fetch synonyms for. Used in lazy loading of synonyms.
2390 
2391  Example : @canc_refs = @{$db_entry_adaptor->fetch_all_synonyms(1234)};
2392  Description: Fetches the synonyms for a particular DBEntry.
2393  Returntype : listref of synonyms. List referred to may be empty if there are no synonyms.
2394  Exceptions : None.
2395  Caller : General
2396  Status : At Risk
2397 
2398 =cut
2399 
2400 
2401 sub fetch_all_synonyms {
2402  my ( $self, $dbID ) = @_;
2403 
2404  my @synonyms = ();
2405 
2406  my $sth =
2407  $self->prepare( "SELECT synonym "
2408  . "FROM external_synonym "
2409  . "WHERE xref_id = ?" );
2410 
2411  $sth->bind_param( 1, $dbID, SQL_INTEGER );
2412 
2413  $sth->execute();
2414 
2415  my $synonym;
2416  $sth->bind_col(1, \$synonym);
2417 
2418  while ( $sth->fetch() ) {
2419  push( @synonyms, $synonym );
2420  }
2421 
2422  return \@synonyms;
2423 }
2424 
2425 
2426 =head2 get_db_name_from_external_db_id
2427 
2428  Arg [1] : external_dbid of database to get the database_name
2429  Example : my $db_name = $db_entry_adaptor->get_db_name_from_external_db_id(1100);
2430  Description: Gets the database name for a certain external_db_id
2431  Returntype : scalar
2432  Exceptions : None.
2433  Caller : General
2434  Status : At Risk
2435 
2436 =cut
2437 
2438 sub get_db_name_from_external_db_id{
2439  my $self = shift;
2440  my $external_db_id = shift;
2441 
2442  my $sth = $self->prepare("SELECT db_name FROM external_db WHERE external_db_id = ?");
2443 
2444  $sth->bind_param(1, $external_db_id, SQL_INTEGER);
2445  $sth->execute();
2446  my ($db_name) = $sth->fetchrow_array();
2447  $sth->finish();
2448  return $db_name;
2449 
2450 }
2451 
2452 1;
2453 
Bio::EnsEMBL::RNAProduct
Definition: RNAProduct.pm:33
transcript
public transcript()
Bio::EnsEMBL::Translation
Definition: Translation.pm:32
Bio::EnsEMBL::Operon
Definition: Operon.pm:30
EnsEMBL
Definition: Filter.pm:1
Bio::EnsEMBL::DBEntry::add_synonym
public void add_synonym()
map
public map()
accession
public accession()
Bio::EnsEMBL::Storable
Definition: Storable.pm:23
Bio::EnsEMBL::Gene
Definition: Gene.pm:37
Bio::EnsEMBL::OntologyXref
Definition: OntologyXref.pm:41
Bio::EnsEMBL::DBEntry::new
public Bio::EnsEMBL::DBEntry new()
Bio::EnsEMBL::Transcript
Definition: Transcript.pm:44
Bio::EnsEMBL::DBSQL::BaseAdaptor
Definition: BaseAdaptor.pm:71
Bio::EnsEMBL::Utils::Scalar
Definition: Scalar.pm:66
Bio::EnsEMBL::DBEntry::get_all_synonyms
public Listref get_all_synonyms()
Bio::EnsEMBL::DBSQL::DBEntryAdaptor::fetch_by_dbID
public Bio::EnsEMBL::DBEntry fetch_by_dbID()
Bio::EnsEMBL::DBEntry
Definition: DBEntry.pm:12
Bio::EnsEMBL::Storable::new_fast
public Instance new_fast()
Bio::EnsEMBL::DBSQL::DBEntryAdaptor
Definition: DBEntryAdaptor.pm:26
Bio::EnsEMBL::Utils::Exception
Definition: Exception.pm:68
Bio::EnsEMBL::IdentityXref
Definition: IdentityXref.pm:23