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