Newer
Older
=head1 LICENSE
Copyright (c) 1999-2012 The European Bioinformatics Institute and
Genome Research Limited. All rights reserved.
This software is distributed under a modified Apache license.
For license details, please see
http://www.ensembl.org/info/about/code_licence.html
=head1 CONTACT
Please email comments or questions to the public Ensembl
Andreas Kusalananda Kähäri
committed
developers list at <dev@ensembl.org>.
Questions may also be sent to the Ensembl help desk at
<helpdesk@ensembl.org>.
=cut
Bio::EnsEMBL::DBSQL::DBEntryAdaptor -
MySQL Database queries to load and store external object references.
=head1 SYNOPSIS
Andreas Kusalananda Kähäri
committed
$db_entry_adaptor =
$registry->get_adaptor( 'Human', 'Core', 'DBEntry' );
Andreas Kusalananda Kähäri
committed
$db_entry = $db_entry_adaptor->fetch_by_dbID($id);
Andreas Kusalananda Kähäri
committed
my $gene_adaptor = $registry->get_adaptor( 'Human', 'Core', 'Gene' );
my $gene = $gene_adaptor->fetch_by_stable_id('ENSG00000101367');
@db_entries = @{ $db_entry_adaptor->fetch_all_by_Gene($gene) };
@gene_ids = $db_entry_adaptor->list_gene_ids_by_extids('BAB15482');
=cut
package Bio::EnsEMBL::DBSQL::DBEntryAdaptor;
use Bio::EnsEMBL::DBSQL::BaseAdaptor;
Emmanuel Mongin
committed
use Bio::EnsEMBL::IdentityXref;
Andreas Kusalananda Kähäri
committed
use Bio::EnsEMBL::OntologyXref;
use Bio::EnsEMBL::Utils::Exception qw(deprecate throw warning);
use vars qw(@ISA);
use strict;
@ISA = qw( Bio::EnsEMBL::DBSQL::BaseAdaptor );
=head2 fetch_by_dbID
Arg [1] : int $dbID
the unique database identifier for the DBEntry to retrieve
Example : my $db_entry = $db_entry_adaptor->fetch_by_dbID($dbID);
Description: Retrieves a dbEntry from the database via its unique
identifier.
my ( $self, $dbID ) = @_;
"SELECT xref.xref_id,
xref.dbprimary_acc,
xref.display_label,
xref.version,
exDB.priority,
exDB.db_name,
exDB.db_display_name,
exDB.db_release,
es.synonym,
xref.info_type,
xref.info_text,
exDB.type,
exDB.secondary_db_name,
Daniel Rios
committed
exDB.secondary_db_table,
FROM (xref, external_db exDB)
LEFT JOIN external_synonym es ON
es.xref_id = xref.xref_id
WHERE xref.xref_id = ?
AND xref.external_db_id = exDB.external_db_id" );
$sth->bind_param( 1, $dbID, SQL_INTEGER );
Daniel Rios
committed
$sth->execute();
my $max_rows = 1000;
while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
Daniel Rios
committed
#$description refers to the external_db description, while $desc was referring the xref description
while ( my $arrayref = shift( @{$rowcache} ) ) {
my ( $refID, $dbprimaryId,
$displayid, $version,
$dbname, $db_display_name,
$release, $synonym,
$info_type, $info_text,
$type, $secondary_db_name,
Daniel Rios
committed
$secondary_db_table, $description
) = @$arrayref;
if ( !defined($exDB) ) {
$exDB =
Bio::EnsEMBL::DBEntry->new(
-adaptor => $self,
-dbID => $dbID,
-primary_id => $dbprimaryId,
-display_id => $displayid,
-version => $version,
-release => $release,
-dbname => $dbname,
-priority => $priority,
-db_display_name => $db_display_name,
-info_type => $info_type,
-info_text => $info_text,
-type => $type,
-secondary_db_name => $secondary_db_name,
Daniel Rios
committed
-secondary_db_table => $secondary_db_table,
-description => $description
);
}
if ( defined($synonym) ) { $exDB->add_synonym($synonym) }
} ## end while ( my $arrayref = shift...
} ## end while ( my $rowcache = $sth...
$sth->finish();
return $exDB;
} ## end sub fetch_by_dbID
sub _get_all_dm_loc_sth {
my ($self, $constraint ,$ensembl_object ) = @_;
my $object_type;
if($ensembl_object->isa("Bio::EnsEMBL::Gene")){
$object_type = "Gene";
}
elsif($ensembl_object->isa("Bio::EnsEMBL::Transcript")){
$object_type = "Transcript";
}
elsif($ensembl_object->isa("Bio::EnsEMBL::Translation")){
$object_type = "Translation";
}
Dan Staines
committed
elsif($ensembl_object->isa("Bio::EnsEMBL::Operon")){
$object_type = "Operon";
}
elsif($ensembl_object->isa("Bio::EnsEMBL::OperonTranscript")){
$object_type = "OperonTranscript";
}
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
else{
warn(ref($ensembl_object)." is not a Gene Transcript or Translation object??\n");
return undef;
}
my $sql = "SELECT xref.xref_id,
xref.dbprimary_acc,
xref.display_label,
xref.version,
exDB.priority,
exDB.db_name,
exDB.db_display_name,
exDB.db_release,
es.synonym,
xref.info_type,
xref.info_text,
exDB.type,
exDB.secondary_db_name,
exDB.secondary_db_table,
xref.description
FROM (xref, external_db exDB, dependent_xref dx, object_xref ox)
LEFT JOIN external_synonym es ON
es.xref_id = xref.xref_id
WHERE xref.external_db_id = exDB.external_db_id AND
ox.xref_id = xref.xref_id AND
ox.ensembl_object_type = \'$object_type\' AND
ox.ensembl_id = ".$ensembl_object->dbID();
if($constraint){
$sql .= " AND $constraint";
}
else{
die "NO constraint???\n";
}
my $sth = $self->prepare($sql) || die "Could not prepare $sql";
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
return $self->_get_all_dm($sth);
}
sub _get_all_dm_sth {
my ( $self, $constraint) = @_;
my $sql = "SELECT xref.xref_id,
xref.dbprimary_acc,
xref.display_label,
xref.version,
exDB.priority,
exDB.db_name,
exDB.db_display_name,
exDB.db_release,
es.synonym,
xref.info_type,
xref.info_text,
exDB.type,
exDB.secondary_db_name,
exDB.secondary_db_table,
xref.description
FROM (xref, external_db exDB, dependent_xref dx)
LEFT JOIN external_synonym es ON
es.xref_id = xref.xref_id
WHERE xref.external_db_id = exDB.external_db_id ";
if($constraint){
$sql .= "AND $constraint";
}
else{
die "NO constraint???\n";
}
my $sth = $self->prepare($sql) || die "Could not prepare $sql";
return $self->_get_all_dm($sth);
}
sub _get_all_dm{
my ($self, $sth) = @_;
# $sth->bind_param( 1, $dm_dbid, SQL_INTEGER );
# print $sth."\n";
$sth->execute() || die "Not able to execute statement handle";
my @list =();
my %seen;
my $max_rows = 1000;
while ( my $rowcache = $sth->fetchall_arrayref(undef, $max_rows) ) {
while ( my $arrayref = shift( @{$rowcache} ) ) {
my ( $dbID, $dbprimaryId,
$displayid, $version,
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
$dbname, $db_display_name,
$release, $synonym,
$info_type, $info_text,
$type, $secondary_db_name,
$secondary_db_table, $description
) = @$arrayref;
if ( !defined($seen{$dbID}) ) {
my $exDB =
Bio::EnsEMBL::DBEntry->new(
-adaptor => $self,
-dbID => $dbID,
-primary_id => $dbprimaryId,
-display_id => $displayid,
-version => $version,
-release => $release,
-dbname => $dbname,
-priority => $priority,
-db_display_name => $db_display_name,
-info_type => $info_type,
-info_text => $info_text,
-type => $type,
-secondary_db_name => $secondary_db_name,
-secondary_db_table => $secondary_db_table,
-description => $description
);
if ($synonym) { $exDB->add_synonym($synonym) };
$seen{$dbID} = 1;
push @list, $exDB;
}
} ## end while ( my $arrayref = shift...
} ## end while ( my $rowcache = $sth...
$sth->finish();
return \@list;
}
=head2 get_all_dependents
Args[1] : dbID of the DBentry to get the dependents of.
Args[2] : (optional) Bio::EnsEMBL::Gene, Transcript or Translation object
Example : my @dependents = @{ $dbe_adaptor->get_all_dependents(1234) };
Description: Get a list of DBEntrys that are depenednet on the DBEntry.
if an ensembl gene transcript or translation is given then only
the ones on that object will be given
Returntype : listref of DBEntrys. May be empty.
Exceptions : none
Caller : DBEntry->get_all_dependnets
Status : UnStable
=cut
sub get_all_dependents {
my ( $self, $dbid, $ensembl_object) = @_;
if(defined($ensembl_object) and !($ensembl_object->isa("Bio::EnsEMBL::Feature") or $ensembl_object->isa("Bio::EnsEMBL::Translation"))){
die ref($ensembl_object)." is not an Gene Transcript or Translation";
}
my $constraint = " dx.master_xref_id = $dbid AND dx.dependent_xref_id = xref.xref_id";
if(defined($ensembl_object)){
return $self->_get_all_dm_loc_sth($constraint, $ensembl_object);
}
else{
return $self->_get_all_dm_sth($constraint, $ensembl_object);
}
}
=head2 get_all_masters
Args[1] : dbID of the DBentry to get the masters of.
Args[2] : (optional) Bio::EnsEMBL::Gene, Transcript or Translation object
Example : my @masters = @{ $dbe_adaptor->get_all_masters(1234) };
Description: Get a list of DBEntrys that are the masters of the DBEntry.
if an ensembl gene transcript or translation is given then only
the ones on that object will be given.
Returntype : listref of DBEntrys. May be empty.
Exceptions : none
Caller : DBEntry->get_all_masters
Status : UnStable
=cut
sub get_all_masters {
my ( $self, $dbid, $ensembl_object ) = @_;
if(defined($ensembl_object) and !($ensembl_object->isa("Bio::EnsEMBL::Feature") or $ensembl_object->isa("Bio::EnsEMBL::Translation"))){
die ref($ensembl_object)." is not an Gene Transcript or Translation";
}
my $constraint = "dx.dependent_xref_id = $dbid AND dx.master_xref_id = xref.xref_id";
if(defined($ensembl_object)){
return $self->_get_all_dm_loc_sth($constraint, $ensembl_object);
}
else{
return $self->_get_all_dm_sth($constraint, $ensembl_object);
}
# return $self->_get_all_dm($constraint, $ensembl_object);
}
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
=head fetch_all_by_name
Arg [1] : string $name - The name of the external reference.
found in accession, display_label or synonym
Arg [2] : (optional) string $dbname - The name of the database which
the provided name is for.
Example : my $xref = @{$dbea->fetch_all_by_name('BRAC2','HGNC')}[0];
print $xref->description(), "\n" if($xref);
Description: Retrieves list of DBEntrys (xrefs) via a name.
The accesion is looked for first then the synonym and finally
the display_label.
NOTE $dbname this is optional but adding this speeds the
process up if you know what you are looking for.
NOTE: In a multi-species database, this method will
return all the entries matching the search criteria, not
just the ones associated with the current species.
Returntype : Bio::EnsEMBL::DBSQL::DBEntry
Exceptions : thrown if arguments are incorrect
Caller : general, domainview
Status : Stable
=cut
sub fetch_all_by_name {
my ( $self, $name, $dbname ) = @_;
my $sql = (<<SQL);
SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label, xref.version,
exDB.priority, exDB.db_name, exDB.db_display_name, exDB.db_release,
es.synonym, xref.info_type, xref.info_text,
exDB.type, exDB.secondary_db_name, exDB.secondary_db_table,
xref.description
FROM (xref, external_db exDB)
LEFT JOIN external_synonym es ON
es.xref_id = xref.xref_id
WHERE (xref.dbprimary_acc = ? or xref.display_label = ?)
AND xref.external_db_id = exDB.external_db_id
SQL
if(defined $dbname){
$sql .= " AND exDB.db_name = ?";
}
my $sth = $self->prepare($sql);
$sth->bind_param( 1, $name, SQL_VARCHAR );
$sth->bind_param( 2, $name, SQL_VARCHAR );
if(defined $dbname){
$sth->bind_param( 3 , $dbname, SQL_VARCHAR );
}
$sth->execute();
if ( !$sth->rows() && lc($dbname) eq 'interpro' ) {
# This is a minor hack that means that results still come back even
# when a mistake was made and no interpro accessions were loaded into
# the xref table. This has happened in the past and had the result of
# breaking domainview
$sth->finish();
$sth = $self->prepare(
"SELECT NULL,
i.interpro_ac,
i.id,
NULL,
NULL,
'Interpro',
NULL,
NULL
FROM interpro i
WHERE i.interpro_ac = ?" );
$sth->bind_param( 1, $name, SQL_VARCHAR );
$sth->execute();
}
my %exDB;
my @exDBlist;
my $max_rows = 1000;
while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
while ( my $arrayref = shift( @{$rowcache} ) ) {
my ( $dbID, $dbprimaryId,
$displayid, $version,
$priority,
$dbname, $db_display_name,
$release, $synonym,
$info_type, $info_text,
$type, $secondary_db_name,
$secondary_db_table, $description
) = @$arrayref;
if ( !defined $exDB{$dbID} ) {
my $entrie =
Bio::EnsEMBL::DBEntry->new(
-adaptor => $self,
-dbID => $dbID,
-primary_id => $dbprimaryId,
-display_id => $displayid,
-version => $version,
-release => $release,
-dbname => $dbname,
-priority => $priority,
-db_display_name => $db_display_name,
-info_type => $info_type,
-info_text => $info_text,
-type => $type,
-secondary_db_name => $secondary_db_name,
-secondary_db_table => $secondary_db_table,
-description => $description
);
$exDB{$dbID} = $entrie;
push @exDBlist, $entrie;
}
if ($synonym) { $exDB{$dbID}->add_synonym($synonym) }
} ## end while ( my $arrayref = shift...
} ## end while ( my $rowcache = $sth...
$sth->finish();
return \@exDBlist;
} ## end sub fetch_all_by_name
=head2 fetch_by_db_accession
Arg [1] : string $dbname - The name of the database which the provided
accession is for.
Arg [2] : string $accession - The accesion of the external reference to
retrieve.
Example : my $xref = $dbea->fetch_by_db_accession('Interpro','IPR003439');
print $xref->description(), "\n" if($xref);
Description: Retrieves a DBEntry (xref) via the name of the database
it is from and its primary accession in that database.
Undef is returned if the xref cannot be found in the
database.
NOTE: In a multi-species database, this method will
return all the entries matching the search criteria, not
just the ones associated with the current species.
Returntype : Bio::EnsEMBL::DBSQL::DBEntry
Exceptions : thrown if arguments are incorrect
Caller : general, domainview
=cut
sub fetch_by_db_accession {
my ( $self, $dbname, $accession ) = @_;
"SELECT xref.xref_id,
xref.dbprimary_acc,
xref.display_label,
xref.version,
exDB.priority,
exDB.db_name,
exDB.db_display_name,
exDB.db_release,
es.synonym,
xref.info_type,
xref.info_text,
exDB.type,
exDB.secondary_db_name,
Daniel Rios
committed
exDB.secondary_db_table,
FROM (xref, external_db exDB)
LEFT JOIN external_synonym es ON
es.xref_id = xref.xref_id
WHERE xref.dbprimary_acc = ?
AND exDB.db_name = ?
AND xref.external_db_id = exDB.external_db_id" );
$sth->bind_param( 1, $accession, SQL_VARCHAR );
$sth->bind_param( 2, $dbname, SQL_VARCHAR );
Daniel Rios
committed
$sth->execute();
if ( !$sth->rows() && lc($dbname) eq 'interpro' ) {
# This is a minor hack that means that results still come back even
# when a mistake was made and no interpro accessions were loaded into
# the xref table. This has happened in the past and had the result of
# breaking domainview
$sth = $self->prepare(
"SELECT NULL,
i.interpro_ac,
i.id,
NULL,
NULL,
'Interpro',
NULL,
NULL
FROM interpro i
WHERE i.interpro_ac = ?" );
$sth->bind_param( 1, $accession, SQL_VARCHAR );
Daniel Rios
committed
$sth->execute();
my $max_rows = 1000;
while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
while ( my $arrayref = shift( @{$rowcache} ) ) {
my ( $dbID, $dbprimaryId,
$displayid, $version,
$dbname, $db_display_name,
$release, $synonym,
$info_type, $info_text,
$type, $secondary_db_name,
Daniel Rios
committed
$secondary_db_table, $description
) = @$arrayref;
if ( !defined($exDB) ) {
$exDB =
Bio::EnsEMBL::DBEntry->new(
-adaptor => $self,
-dbID => $dbID,
-primary_id => $dbprimaryId,
-display_id => $displayid,
-version => $version,
-release => $release,
-dbname => $dbname,
-priority => $priority,
-db_display_name => $db_display_name,
-info_type => $info_type,
-info_text => $info_text,
-type => $type,
-secondary_db_name => $secondary_db_name,
Daniel Rios
committed
-secondary_db_table => $secondary_db_table,
-description => $description
);
}
if ($synonym) { $exDB->add_synonym($synonym) }
} ## end while ( my $arrayref = shift...
} ## end while ( my $rowcache = $sth...
} ## end sub fetch_by_db_accession
Arg [1] : Bio::EnsEMBL::DBEntry $dbEntry
The DBEntry (xref) to be stored
Arg [2] : Int $ensID
The dbID of an EnsEMBL object to associate with this external
database entry
Arg [3] : string $ensType ('Transcript', 'Translation', 'Gene')
The type of EnsEMBL object that this external database entry is
being associated with.
Arg [4] : boolean $ignore_release
If unset or zero, will require that the release string
of the DBEntry object is identical to the release of the
external database. If set and non-zero, will ignore the
release information.
Example : $dbea->store($db_entry, $transcript_id, 'Transcript');
Description: Stores a reference to an external database (if it is not stored
already) and associates an EnsEMBL object of a specified type
with the external identifier.
Returntype : int - the dbID of the newly created external refernce
Exceptions : thrown when invalid dbID is passed to this method
Caller : scripts which load Xrefs and ObjectXrefs, etc. into Ensembl
my ( $self, $dbEntry, $ensID, $ensType, $ignore_release ) = @_;
Graham McVicker
committed
my $dbJustInserted;
#
# backwards compatibility check:
# check if $ensID is an object; if so, use $obj->dbID
#
my $ensembl_id;
Andreas Kusalananda Kähäri
committed
if ( defined($ensID) ) {
if ( $ensID =~ /^\d+$/ ) {
$ensembl_id = $ensID;
} elsif ( ref($ensID) eq 'Bio::EnsEMBL::Gene'
or ref($ensID) eq 'Bio::EnsEMBL::Transcript'
Dan Staines
committed
or ref($ensID) eq 'Bio::EnsEMBL::Translation'
or ref($ensID) eq 'Bio::EnsEMBL::OperonTranscript'
or ref($ensID) eq 'Bio::EnsEMBL::Operon'
)
Andreas Kusalananda Kähäri
committed
{
warning( "You should pass DBEntryAdaptor->store() "
. "a dbID rather than an ensembl object "
. "to store the xref on" );
if ( defined( $ensID->dbID() ) ) {
Andreas Kusalananda Kähäri
committed
$ensembl_id = $ensID->dbID();
} else {
throw( sprintf( "%s %s doesn't have a dbID, can't store xref",
$ensType, $ensID->display_id() ) );
}
} else {
Andreas Kusalananda Kähäri
committed
throw("Invalid dbID passed to DBEntryAdaptor->store()");
}
}
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
# Ensure external_db contains a record of the intended xref source
my $dbRef;
$dbRef = $self->_check_external_db($dbEntry,$ignore_release);
# Attempt to insert DBEntry
my $xref_id = $self->_store_or_fetch_xref($dbEntry,$dbRef);
$dbEntry->dbID($xref_id); #keeps DBEntry in sync with database
### Attempt to create an object->xref mapping
if ($ensembl_id) {$self->_store_object_xref_mapping($ensembl_id,$dbEntry,$ensType)};
return $xref_id;
}
sub _store_object_xref_mapping {
my $self = shift;
my $ensembl_id = shift;
my $dbEntry = shift;
my $ensembl_type = shift;
if (not defined ($ensembl_type)) { warning("No Ensembl data type provided for new xref");}
my $analysis_id;
if ( $dbEntry->analysis() ) {
$analysis_id = $self->db()->get_AnalysisAdaptor->store( $dbEntry->analysis() );
} else {
$analysis_id = 0; ## This used to be undef, but uniqueness in mysql requires a value
Graham McVicker
committed
}
my $sth = $self->prepare(qq(
INSERT IGNORE INTO object_xref
SET xref_id = ?,
ensembl_object_type = ?,
ensembl_id = ?,
linkage_annotation = ?,
analysis_id = ? )
);
$sth->bind_param( 1, $dbEntry->dbID(), SQL_INTEGER );
$sth->bind_param( 2, $ensembl_type, SQL_VARCHAR );
$sth->bind_param( 3, $ensembl_id, SQL_INTEGER );
$sth->bind_param( 4, $dbEntry->linkage_annotation(),SQL_VARCHAR );
$sth->bind_param( 5, $analysis_id, SQL_INTEGER );
Andreas Kusalananda Kähäri
committed
$sth->execute();
$sth->finish();
my $object_xref_id = $self->last_insert_id();
$dbEntry->adaptor($self); # hand Adaptor to dbEntry for future use with OntologyXrefs
if ($object_xref_id) {
#no existing object_xref, therefore
if ( $dbEntry->isa('Bio::EnsEMBL::IdentityXref') ) {
Andreas Kusalananda Kähäri
committed
$sth = $self->prepare( "
INSERT ignore INTO identity_xref
SET object_xref_id = ?,
Ian Longden
committed
xref_identity = ?,
ensembl_identity = ?,
xref_start = ?,
xref_end = ?,
ensembl_start = ?,
ensembl_end = ?,
Graham McVicker
committed
cigar_line = ?,
score = ?,
Ian Longden
committed
evalue = ?" );
$sth->bind_param( 1, $object_xref_id, SQL_INTEGER );
$sth->bind_param( 2, $dbEntry->xref_identity, SQL_INTEGER );
$sth->bind_param( 3, $dbEntry->ensembl_identity, SQL_INTEGER );
$sth->bind_param( 4, $dbEntry->xref_start, SQL_INTEGER );
$sth->bind_param( 5, $dbEntry->xref_end, SQL_INTEGER );
$sth->bind_param( 6, $dbEntry->ensembl_start, SQL_INTEGER );
$sth->bind_param( 7, $dbEntry->ensembl_end, SQL_INTEGER );
$sth->bind_param( 8, $dbEntry->cigar_line, SQL_LONGVARCHAR );
$sth->bind_param( 9, $dbEntry->score, SQL_DOUBLE );
$sth->bind_param( 10, $dbEntry->evalue, SQL_DOUBLE );
Andreas Kusalananda Kähäri
committed
$sth->execute();
} elsif ( $dbEntry->isa('Bio::EnsEMBL::OntologyXref') ) {
Andreas Kusalananda Kähäri
committed
$sth = $self->prepare( "
Andreas Kusalananda Kähäri
committed
INSERT ignore INTO ontology_xref
SET object_xref_id = ?,
source_xref_id = ?,
linkage_type = ? " );
foreach my $info ( @{ $dbEntry->get_all_linkage_info() } ) {
my ( $linkage_type, $sourceXref ) = @{$info};
my $sourceXid = undef;
if ($sourceXref) {
$sourceXref->is_stored( $self->dbc ) || $self->store($sourceXref);
$sourceXid = $sourceXref->dbID;
}
$sth->bind_param( 1, $object_xref_id, SQL_INTEGER );
$sth->bind_param( 2, $sourceXid, SQL_INTEGER );
$sth->bind_param( 3, $linkage_type, SQL_VARCHAR );
$sth->execute();
} #end foreach
} #end elsif
} # end if ($object_xref_id)
return $object_xref_id;
}
=head2 _check_external_db
Andreas Kusalananda Kähäri
committed
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
Arg [1] : DBEntry object
Arg [2] : Ignore version flag
Description: Looks for a record of the given external database
Exceptions : Throws on missing external database entry
Returntype : Int
=cut
sub _check_external_db {
my ($self,$db_entry,$ignore) = @_;
my ($sql,@bound_params,$sql_helper,$db_name,$db_release);
$db_name = $db_entry->dbname();
$db_release = $db_entry->release();
$sql_helper = $self->dbc->sql_helper;
$sql = 'SELECT external_db_id FROM external_db WHERE db_name = ?';
push @bound_params,$db_name;
unless ($ignore) {
if ($db_release) {
$sql .= ' AND db_release = ?';
push @bound_params,$db_release;
} else {
$sql .= ' AND db_release is NULL';
}
}
my ($db_id) = @{ $sql_helper->execute_simple(-SQL => $sql, -PARAMS => \@bound_params) };
if ($db_id) {
return $db_id;
}
else {
throw( sprintf( "external_db [%s] release [%s] does not exist",
$db_name, $db_release)
);
}
Arg [1] : DBEntry object
Arg [2] : Database accession for external database
Description: Thread-safe method for adding xrefs, or otherwise returning
an xref ID for the inserted or retrieved xref. Also inserts
synonyms for that xref when entire new
Returns : Int - the DB ID of the xref after insertion
=cut
sub _store_or_fetch_xref {
my $self = shift;
my $dbEntry = shift;
my $dbRef = shift;
my $xref_id;
my $sth = $self->prepare( "
INSERT IGNORE INTO xref
SET dbprimary_acc = ?,
display_label = ?,
version = ?,
description = ?,
external_db_id = ?,
info_type = ?,
info_text = ?");
$sth->bind_param(1, $dbEntry->primary_id,SQL_VARCHAR);
$sth->bind_param(2, $dbEntry->display_id,SQL_VARCHAR);
$sth->bind_param(3, ($dbEntry->version || q{0}),SQL_VARCHAR);
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
$sth->bind_param(4, $dbEntry->description,SQL_VARCHAR);
$sth->bind_param(5, $dbRef,SQL_INTEGER);
$sth->bind_param(6, ($dbEntry->info_type || 'NONE'), SQL_VARCHAR);
$sth->bind_param(7, ($dbEntry->info_text || ''), SQL_VARCHAR);
$sth->execute();
$xref_id = $self->last_insert_id('xref_id',undef,'xref');
$sth->finish();
if ($xref_id) { #insert was successful, store supplementary synonyms
# thread safety no longer an issue.
my $synonym_check_sth = $self->prepare(
"SELECT xref_id, synonym
FROM external_synonym
WHERE xref_id = ?
AND synonym = ?");
my $synonym_store_sth = $self->prepare(
"INSERT ignore INTO external_synonym
SET xref_id = ?, synonym = ?");
my $synonyms = $dbEntry->get_all_synonyms();
foreach my $syn ( @$synonyms ) {
$synonym_check_sth->bind_param(1,$xref_id,SQL_INTEGER);
$synonym_check_sth->bind_param(2,$syn,SQL_VARCHAR);
$synonym_check_sth->execute();
my ($dbSyn) = $synonym_check_sth->fetchrow_array();
$synonym_store_sth->bind_param(1,$xref_id,SQL_INTEGER);
$synonym_store_sth->bind_param(2,$syn,SQL_VARCHAR);
$synonym_store_sth->execute() if(!$dbSyn);
}
$synonym_check_sth->finish();
$synonym_store_sth->finish();
Andy Yates
committed
} else { # xref_id already exists, retrieve it according to fields in the unique key
my $sql = 'SELECT xref_id FROM xref
Andy Yates
committed
WHERE dbprimary_acc = ?
Andy Yates
committed
AND version =?
AND external_db_id = ?
AND info_type = ?
Andy Yates
committed
AND info_text = ?';
Andy Yates
committed
my $info_type = $dbEntry->info_type() || 'NONE';
my $info_text = $dbEntry->info_text() || q{};
Andy Yates
committed
my $version = $dbEntry->version() || q{0};
$sth = $self->prepare( $sql );
$sth->bind_param(1, $dbEntry->primary_id,SQL_VARCHAR);
Andy Yates
committed
$sth->bind_param(2, $version, SQL_VARCHAR);
$sth->bind_param(3, $dbRef, SQL_INTEGER);
$sth->bind_param(4, $info_type, SQL_VARCHAR);
$sth->bind_param(5, $info_text, SQL_VARCHAR);
$sth->execute();
($xref_id) = $sth->fetchrow_array();
$sth->finish;
Andy Yates
committed
if(!$xref_id) {
Andy Yates
committed
my $msg = 'Cannot find an xref id for %s (version=%d) with external db id %d.';
Andy Yates
committed
throw(sprintf($msg, $dbEntry->primary_id(), $version, $dbRef))
Andy Yates
committed
}
}
return $xref_id;
}
Emmanuel Mongin
committed
Arne Stabenau
committed
=head2 exists
Arg [1] : Bio::EnsEMBL::DBEntry $dbe
Example : if($dbID = $db_entry_adaptor->exists($dbe)) { do stuff; }
Description: Returns the db id of this DBEntry if it exists in this database
Andreas Kusalananda Kähäri
committed
otherwise returns undef. Exists is defined as an entry with
Arne Stabenau
committed
the same external_db and display_id
Returntype : int
Exceptions : thrown on incorrect args
Caller : GeneAdaptor::store, TranscriptAdaptor::store
Arne Stabenau
committed
=cut
sub exists {
Arne Stabenau
committed
unless($dbe && ref $dbe && $dbe->isa('Bio::EnsEMBL::DBEntry')) {
throw("arg must be a Bio::EnsEMBL::DBEntry not [$dbe]");
Arne Stabenau
committed
}
Andreas Kusalananda Kähäri
committed
my $sth = $self->prepare('SELECT x.xref_id
Arne Stabenau
committed
FROM xref x, external_db xdb
WHERE x.external_db_id = xdb.external_db_id
AND xdb.db_name = ?
AND x.dbprimary_acc = ?');
Arne Stabenau
committed
$sth->bind_param(1,$dbe->display_id,SQL_VARCHAR);
$sth->bind_param(2,$dbe->dbname,SQL_VARCHAR);
$sth->bind_param(3,$dbe->primary_id,SQL_VARCHAR);
Daniel Rios
committed
$sth->execute();
Arne Stabenau
committed
my ($dbID) = $sth->fetchrow_array;
$sth->finish;
return $dbID;
}
=head2 fetch_all_by_Gene
Andreas Kusalananda Kähäri
committed
Arg [1] : Bio::EnsEMBL::Gene $gene
Andy Yates
committed
Arg [2] : optional external database name. SQL wildcards are accepted
Arg [3] : optional external_db type. SQL wildcards are accepted
Example : @db_entries = @{$db_entry_adaptor->fetch_all_by_Gene($gene)};
Description: This returns a list of DBEntries associated with this gene.
Note that this method was changed in release 15. Previously
it set the DBLinks attribute of the gene passed in to contain
Glenn Proctor
committed
all of the gene, transcript, and translation xrefs associated
Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
Andreas Kusalananda Kähäri
committed
there is mapping data, or OntologyXref if there is linkage data.
Exceptions : thows if gene object not passed
sub fetch_all_by_Gene {
my ( $self, $gene, $ex_db_reg, $exdb_type ) = @_;
if(!ref($gene) || !$gene->isa('Bio::EnsEMBL::Gene')) {
throw("Bio::EnsEMBL::Gene argument expected.");
}
return $self->_fetch_by_object_type($gene->dbID(), 'Gene', $ex_db_reg, $exdb_type);
Andy Yates
committed
=head2 fetch_all_by_Operon
Arg [1] : Bio::EnsEMBL::Operon $operon
(The operon to retrieve DBEntries for)
Arg [2] : optional external database name. SQL wildcards are accepted
Arg [3] : optional external_db type. SQL wildcards are accepted
Example : @db_entries = @{$db_entry_adaptor->fetch_all_by_Operon($operon)};
Description: This returns a list of DBEntries associated with this operon.
Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
there is mapping data, or OntologyXref if there is linkage data.
Exceptions : thows if operon object not passed