DBEntryAdaptor.pm 54.3 KB
Newer Older
1
2
=head1 LICENSE

3
  Copyright (c) 1999-2010 The European Bioinformatics Institute and
4
5
6
7
8
9
10
11
12
13
  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
14
  developers list at <dev@ensembl.org>.
15
16
17
18
19

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

=cut
20
21
22

=head1 NAME

23
Bio::EnsEMBL::DBSQL::DBEntryAdaptor -
24
25
26
27
MySQL Database queries to load and store external object references.

=head1 SYNOPSIS

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

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

33
34
35
36
37
38
  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');
Graham McVicker's avatar
Graham McVicker committed
39

40
41
=head1 METHODS

42
43
44
45
46
=cut

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

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
55
56
57
58
use vars qw(@ISA);
use strict;

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

Graham McVicker's avatar
Graham McVicker committed
59
60
61
62
63
=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);
64
65
  Description: Retrieves a dbEntry from the database via its unique
               identifier.
Graham McVicker's avatar
Graham McVicker committed
66
67
  Returntype : Bio::EnsEMBL::DBEntry
  Exceptions : none
68
  Caller     : general
69
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
70
71
72

=cut

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

76
  my $sth = $self->prepare(
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
    "SELECT  xref.xref_id,
            xref.dbprimary_acc,
            xref.display_label,
            xref.version,
            exDB.dbprimary_acc_linkable,
            exDB.display_label_linkable,
            exDB.priority,
            exDB.db_name,
            exDB.db_display_name,
            exDB.db_release,
            es.synonym,
            xref.info_type,
            xref.info_text,
            exDB.type,
            exDB.secondary_db_name,
92
            exDB.secondary_db_table,
Nathan Johnson's avatar
Nathan Johnson committed
93
            xref.description
94
95
96
97
98
99
100
    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 );
101
  $sth->execute();
102

103
  my $exDB;
104

105
106
107
  my $max_rows = 1000;

  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
108
      #$description refers to the external_db description, while $desc was referring the xref description
109
110
111
    while ( my $arrayref = shift( @{$rowcache} ) ) {
      my ( $refID,               $dbprimaryId,
           $displayid,           $version,
112
	   $primary_id_linkable,
113
114
115
116
117
           $display_id_linkable, $priority,
           $dbname,              $db_display_name,
           $release,             $synonym,
           $info_type,           $info_text,
           $type,                $secondary_db_name,
118
           $secondary_db_table,  $description
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
      ) = @$arrayref;

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


      }
145

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

148
149
    } ## end while ( my $arrayref = shift...
  } ## end while ( my $rowcache = $sth...
150
151
152
153

  $sth->finish();

  return $exDB;
154
} ## end sub fetch_by_dbID
155
156
157
158
159
160
161
162
163
164


=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);
165
166
167
168
169
170
171
  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.
172
173
174
  Returntype : Bio::EnsEMBL::DBSQL::DBEntry
  Exceptions : thrown if arguments are incorrect
  Caller     : general, domainview
175
  Status     : Stable
176
177
178
179

=cut

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

182
  my $sth = $self->prepare(
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
    "SELECT xref.xref_id,
            xref.dbprimary_acc,
            xref.display_label,
            xref.version,
            exDB.dbprimary_acc_linkable,
            exDB.display_label_linkable,
            exDB.priority,
            exDB.db_name,
            exDB.db_display_name,
            exDB.db_release,
            es.synonym,
            xref.info_type,
            xref.info_text,
            exDB.type,
            exDB.secondary_db_name,
198
            exDB.secondary_db_table,
Nathan Johnson's avatar
Nathan Johnson committed
199
            xref.description
200
201
202
    FROM    (xref, external_db exDB)
    LEFT JOIN external_synonym es ON
            es.xref_id = xref.xref_id
203
204
    WHERE  xref.dbprimary_acc = ?
    AND    exDB.db_name = ?
205
    AND    xref.external_db_id = exDB.external_db_id" );
206

207
208
  $sth->bind_param( 1, $accession, SQL_VARCHAR );
  $sth->bind_param( 2, $dbname,    SQL_VARCHAR );
209
  $sth->execute();
210

211
212
213
214
215
216
  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

217
    $sth->finish();
218
219
220
221
222
223
224
225
226
227
228
229
230
    $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 );
231
    $sth->execute();
232
233
234
235
  }

  my $exDB;

236
237
238
239
240
241
  my $max_rows = 1000;

  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
    while ( my $arrayref = shift( @{$rowcache} ) ) {
      my ( $dbID,                $dbprimaryId,
           $displayid,           $version,
242
           $primary_id_linkable,
243
244
245
246
247
           $display_id_linkable, $priority,
           $dbname,              $db_display_name,
           $release,             $synonym,
           $info_type,           $info_text,
           $type,                $secondary_db_name,
248
           $secondary_db_table,  $description
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
      ) = @$arrayref;

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

273

274
275
276
277
278
279
      }

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

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

  $sth->finish();
282
283

  return $exDB;
284
} ## end sub fetch_by_db_accession
285
286


Graham McVicker's avatar
Graham McVicker committed
287
288
=head2 store

289
290
  Arg [1]    : Bio::EnsEMBL::DBEntry $exObj
               The DBEntry (xref) to be stored
291
292
293
294
  Arg [2]    : Int $ensID
               The dbID of an EnsEMBL object to associate with this external
               database entry
  Arg [3]    : string $ensType ('Transcript', 'Translation', 'Gene')
295
296
               The type of EnsEMBL object that this external database entry is
               being associated with.
297
298
299
300
301
  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.
302
  Example    : $dbea->store($db_entry, $transcript_id, 'Transcript');
303
304
305
  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.
306
307
308
  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
309
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
310
311
312

=cut

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

316
  my $dbJustInserted;
317

318
319
320
321
322
323
  #
  # backwards compatibility check:
  # check if $ensID is an object; if so, use $obj->dbID
  #
  my $ensembl_id;

324

325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
  if ( defined($ensID) ) {
    if ( $ensID =~ /^\d+$/ ) {
      $ensembl_id = $ensID;
    } elsif (    ref($ensID) eq 'Bio::EnsEMBL::Gene'
              or ref($ensID) eq 'Bio::EnsEMBL::Transcript'
              or ref($ensID) eq 'Bio::EnsEMBL::Translation' )
    {
      warning(   "You should pass DBEntryAdaptor->store() "
               . "a dbID rather than an ensembl object "
               . "to store the xref on" );

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

347
348
349
  #
  # Check for the existance of the external_db, throw if it does not exist
  #
350

351
  my ($dbRef, $release_clause);
352
353

  if ( !$ignore_release ) {
354
355
356
357
358
359
360
361
362

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


363
    my $sth = $self->prepare( "
364
365
     SELECT external_db_id
       FROM external_db
366
      WHERE db_name    = ?
367
        $release_clause" );
368
369

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

    ($dbRef) = $sth->fetchrow_array();
374
	$sth->finish();
375
376
377
378
379
380
381

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

383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
    my $sth = $self->prepare( "
     SELECT external_db_id
       FROM external_db
      WHERE db_name = ? " );

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

    $sth->execute();

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

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

401
402
403
  #
  # Check for the existance of the external reference, add it if not present
  #
404
  my $sth = $self->prepare( "
405
406
407
       SELECT xref_id
         FROM xref
        WHERE external_db_id = ?
408
409
410
411
          AND dbprimary_acc  = ?
          AND version        = ?
          AND info_type      = ?
          AND info_text      = ?" );
412

413
414
415
  $sth->bind_param(1,$dbRef,SQL_INTEGER);
  $sth->bind_param(2,$exObj->primary_id,SQL_VARCHAR);
  $sth->bind_param(3,$exObj->version,SQL_VARCHAR);
416
417
418
  $sth->bind_param(4,$exObj->info_type,SQL_VARCHAR);
  $sth->bind_param(5,$exObj->info_text,SQL_VARCHAR);

419
  $sth->execute();
420
  my ($dbX) = $sth->fetchrow_array();
421

422
423
  $sth->finish();
  if(!$dbX) {
424
425
426
427
428
429
430
    if(!$exObj->primary_id()) {
      throw("DBEntry cannot be stored without a primary_id attribute.");
    }

    #
    # store the new xref
    #
431
    $sth = $self->prepare( "
432
       INSERT ignore INTO xref
433
434
       SET dbprimary_acc = ?,
           display_label = ?,
Arne Stabenau's avatar
Arne Stabenau committed
435
436
           version = ?,
           description = ?,
437
438
           external_db_id = ?,
           info_type = ?,
Glenn Proctor's avatar
Glenn Proctor committed
439
           info_text = ?");
440
441
442
443
444
445
446
447
    $sth->bind_param(1, $exObj->primary_id,SQL_VARCHAR);
    $sth->bind_param(2, $exObj->display_id,SQL_VARCHAR);
    $sth->bind_param(3, $exObj->version,SQL_VARCHAR);
    $sth->bind_param(4, $exObj->description,SQL_VARCHAR);
    $sth->bind_param(5, $dbRef,SQL_INTEGER);
    $sth->bind_param(6, $exObj->info_type, SQL_VARCHAR);
    $sth->bind_param(7, $exObj->info_text, SQL_VARCHAR);

448
    $sth->execute();
449

450
451
452
453
    $dbX = $sth->{'mysql_insertid'};
    $sth->finish();
    #
    # store the synonyms for the new xref
454
    #
455
456
457
458
459
460
461
462
    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
463
         SET xref_id = ?, synonym = ?");
464
465

    my $synonyms = $exObj->get_all_synonyms();
466
    foreach my $syn ( @$synonyms ) {
467
	$synonym_check_sth->bind_param(1,$dbX,SQL_INTEGER);
Glenn Proctor's avatar
Glenn Proctor committed
468
	$synonym_check_sth->bind_param(2,$syn,SQL_VARCHAR);
469
	$synonym_check_sth->execute();
470
      my ($dbSyn) = $synonym_check_sth->fetchrow_array();
471
472
	$synonym_store_sth->bind_param(1,$dbX,SQL_INTEGER);
	$synonym_store_sth->bind_param(2,$syn,SQL_VARCHAR);
473
	$synonym_store_sth->execute() if(!$dbSyn);
474
475
476
    }
    $synonym_check_sth->finish();
    $synonym_store_sth->finish();
477
  } elsif (! defined( $exObj->dbID() ) ) {
478
    $exObj->dbID($dbX);
479
  }
480
481
482
483
484
485
486

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

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

500
    $sth->execute();
501

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

504
    $sth->finish();
505

506
507
508
509
510
511
512
513
514
515
516
    if ( !$tst ) {
      #
      # Store the reference to the internal ensembl object
      #
      my $analysis_id;
      if ( $exObj->analysis() ) {
        $analysis_id =
          $self->db()->get_AnalysisAdaptor->store( $exObj->analysis() );
      } else {
        $analysis_id = undef;
      }
517

518
519
      $sth = $self->prepare(
        qq(
520
521
522
523
INSERT IGNORE INTO object_xref
  SET   xref_id = ?,
        ensembl_object_type = ?,
        ensembl_id = ?,
524
525
        linkage_annotation = ?,
        analysis_id = ? ) );
526

527
528
529
530
531
      $sth->bind_param( 1, $dbX,                         SQL_INTEGER );
      $sth->bind_param( 2, $ensType,                     SQL_VARCHAR );
      $sth->bind_param( 3, $ensembl_id,                  SQL_INTEGER );
      $sth->bind_param( 4, $exObj->linkage_annotation(), SQL_VARCHAR );
      $sth->bind_param( 5, $analysis_id,                 SQL_INTEGER );
532

533
534
535
536
537
538
539
540
541
542
543
544
      #print "stored xref id $dbX in obejct_xref\n";
      $sth->execute();
      $exObj->dbID($dbX);
      $exObj->adaptor($self);
      my $Xidt = $sth->{'mysql_insertid'};

      #
      # If this is an IdentityXref need to store in that table too
      # If its OntologyXref add the linkage type to ontology_xref table
      #
      if ( $exObj->isa('Bio::EnsEMBL::IdentityXref') ) {
        $sth = $self->prepare( "
545
546
             INSERT ignore INTO identity_xref
             SET object_xref_id = ?,
547
548
549
550
551
552
             xref_identity = ?,
             ensembl_identity = ?,
             xref_start = ?,
             xref_end   = ?,
             ensembl_start = ?,
             ensembl_end = ?,
553
554
             cigar_line = ?,
             score = ?,
555
             evalue = ?" );
556
557
558
559
560
561
562
563
564
565
566
567
568
        $sth->bind_param( 1, $Xidt,                    SQL_INTEGER );
        $sth->bind_param( 2, $exObj->xref_identity,    SQL_INTEGER );
        $sth->bind_param( 3, $exObj->ensembl_identity, SQL_INTEGER );
        $sth->bind_param( 4, $exObj->xref_start,       SQL_INTEGER );
        $sth->bind_param( 5, $exObj->xref_end,         SQL_INTEGER );
        $sth->bind_param( 6, $exObj->ensembl_start,    SQL_INTEGER );
        $sth->bind_param( 7, $exObj->ensembl_end,      SQL_INTEGER );
        $sth->bind_param( 8,  $exObj->cigar_line, SQL_LONGVARCHAR );
        $sth->bind_param( 9,  $exObj->score,      SQL_DOUBLE );
        $sth->bind_param( 10, $exObj->evalue,     SQL_DOUBLE );
        $sth->execute();
      } elsif ( $exObj->isa('Bio::EnsEMBL::OntologyXref') ) {
        $sth = $self->prepare( "
569
             INSERT ignore INTO ontology_xref
570
                SET object_xref_id = ?,
571
                    source_xref_id = ?,
572
                    linkage_type = ? " );
573
574
575
576
577
578
579
580
581
582
583
584
585
        foreach my $info ( @{ $exObj->get_all_linkage_info() } ) {
          my ( $lt, $sourceXref ) = @{$info};
          my $sourceXid = undef;
          if ($sourceXref) {
            $sourceXref->is_stored( $self->dbc )
              || $self->store($sourceXref);
            $sourceXid = $sourceXref->dbID;
          }
          $sth->bind_param( 1, $Xidt,      SQL_INTEGER );
          $sth->bind_param( 2, $sourceXid, SQL_INTEGER );
          $sth->bind_param( 3, $lt,        SQL_VARCHAR );
          $sth->execute();
        }
586
      }
587
588
589
    } ## end if ( !$tst )
  } ## end if ( defined($ensembl_id...))

590
  return $dbX;
591
592
}

593

594
595
596
597
598
=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
599
               otherwise returns undef.  Exists is defined as an entry with
600
601
602
603
               the same external_db and display_id
  Returntype : int
  Exceptions : thrown on incorrect args
  Caller     : GeneAdaptor::store, TranscriptAdaptor::store
604
  Status     : Stable
605
606
607
608

=cut

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

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

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

622
  $sth->bind_param(1,$dbe->display_id,SQL_VARCHAR);
Glenn Proctor's avatar
Glenn Proctor committed
623
  $sth->bind_param(2,$dbe->dbname,SQL_VARCHAR);
624
  $sth->bind_param(3,$dbe->primary_id,SQL_VARCHAR);
625
  $sth->execute();
626
627
628
629
630
631
632
633
634

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

  $sth->finish;

  return $dbID;
}


635
=head2 fetch_all_by_Gene
Graham McVicker's avatar
Graham McVicker committed
636

637
  Arg [1]    : Bio::EnsEMBL::Gene $gene
638
               (The gene to retrieve DBEntries for)
639
  Arg [2]    : optional external database name
640
  Arg [3]    : optional external_db type
Graham McVicker's avatar
Graham McVicker committed
641
  Example    : @db_entries = @{$db_entry_adaptor->fetch_by_Gene($gene)};
642
643
644
  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
645
               all of the gene, transcript, and translation xrefs associated
646
               with this gene.
647
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
648
               there is mapping data, or OntologyXref if there is linkage data.
649
  Exceptions : thows if gene object not passed
Graham McVicker's avatar
Graham McVicker committed
650
  Caller     : Bio::EnsEMBL::Gene
651
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
652
653
654

=cut

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

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

662
  return $self->_fetch_by_object_type($gene->dbID(), 'Gene', $ex_db_reg, $exdb_type);
Arne Stabenau's avatar
Arne Stabenau committed
663
664
}

Graham McVicker's avatar
Graham McVicker committed
665

666
=head2 fetch_all_by_Transcript
Graham McVicker's avatar
Graham McVicker committed
667

Graham McVicker's avatar
Graham McVicker committed
668
  Arg [1]    : Bio::EnsEMBL::Transcript
669
  Arg [2]    : optional external database name
670
  Arg [3]    : optional external_db type
671
  Example    : @db_entries = @{$db_entry_adaptor->fetch_by_Gene($trans)};
672
  Description: This returns a list of DBEntries associated with this
673
674
675
               transcript. Note that this method was changed in release 15.
               Previously it set the DBLinks attribute of the gene passed in
               to contain all of the gene, transcript, and translation xrefs
676
               associated with this gene.
677
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
678
               there is mapping data, or OntologyXref if there is linkage data.
679
  Exceptions : throes if transcript argument not passed
680
  Caller     : Bio::EnsEMBL::Gene
681
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
682
683
684

=cut

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

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

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

Graham McVicker's avatar
Graham McVicker committed
695

696
=head2 fetch_all_by_Translation
Graham McVicker's avatar
Graham McVicker committed
697

Graham McVicker's avatar
Graham McVicker committed
698
  Arg [1]    : Bio::EnsEMBL::Translation $trans
699
               (The translation to fetch database entries for)
700
  Arg [2]    : optional external database name
701
  Arg [3]    : optional externaldb type
702
  Example    : @db_entries = @{$db_entry_adptr->fetch_all_by_Translation($trans)};
Graham McVicker's avatar
Graham McVicker committed
703
  Description: Retrieves external database entries for an EnsEMBL translation
704
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
705
               there is mapping data, or OntologyXref if there is linkage data.
706
  Exceptions : throws if translation object not passed
Graham McVicker's avatar
Graham McVicker committed
707
  Caller     : general
708
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
709
710
711

=cut

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

715
716
717
  if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Translation')) {
    throw('Bio::EnsEMBL::Translation argument expected.');
  }
718
  if( ! $trans->dbID ){
719
720
721
    warning( "Cannot fetch_all_by_Translation without a dbID" );
    return [];
  }
Glenn Proctor's avatar
Glenn Proctor committed
722

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


727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748

=head2 remove_from_object

  Arg [1]    : Bio::EnsEMBL::DBEntry $dbe - The external reference which
               is to be disassociated from an ensembl object.
  Arg [2]    : Bio::EnsEMBL::Storable $object - The ensembl object the
               external reference is to be disassociated from
  Arg [3]    : string $object_type - The type of the ensembl object.
               E.g. 'Gene', 'Transcript', 'Translation'
  Example    :
               # remove all dbentries from this translation
               foreach my $dbe (@{$translation->get_all_DBEntries()}) {
                 $dbe_adaptor->remove($dbe, $translation, 'Translation');
               }
  Description: Removes an association between an ensembl object and a
               DBEntry (xref).  This does not remove the actual xref from
               the database, only its linkage to the ensembl object.
  Returntype : none
  Exceptions : Throw on incorrect arguments.
               Warning if object or dbentry is not stored in this database.
  Caller     : TranscriptAdaptor::remove, GeneAdaptor::remove,
               TranslationAdaptor::remove
749
  Status     : Stable
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786

=cut

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

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

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

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

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

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

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

  # obtain the identifier of the link from the object_xref table
787
  #No need to compare linkage_annotation here
788
789
790
791
792
793
  my $sth = $self->prepare
    ("SELECT ox.object_xref_id " .
     "FROM   object_xref ox ".
     "WHERE  ox.xref_id = ? " .
     "AND    ox.ensembl_id = ? " .
     "AND    ox.ensembl_object_type = ?");
794
795
796
797
  $sth->bind_param(1,$dbe->dbID,SQL_INTEGER);
  $sth->bind_param(2,$object->dbID,SQL_INTEGER);
  $sth->bind_param(3,$object_type,SQL_VARCHAR);
  $sth->execute();
798
799
800
801
802
803
804
805
806
807
808

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

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

  # delete from the tables which contain additional linkage information

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

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

  # delete the actual linkage itself
  $sth = $self->prepare("DELETE FROM object_xref WHERE object_xref_id = ?");
821
822
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
823
824
825
826
827
828
829
  $sth->finish();

  return;
}


=head2 _fetch_by_object_type
Graham McVicker's avatar
Graham McVicker committed
830

831
  Arg [1]    : string $ensID
832
  Arg [2]    : string $ensType (object type to be returned)
833
  Arg [3]    : optional $exdbname (external database name)
834
835
836
837
838
               (may be an SQL pattern containing '%' which matches any
               number of characters)
  Arg [4]    : optional $exdb_type (external database type)
               (may be an SQL pattern containing '%' which matches any
               number of characters)
839
840
  Example    : $self->_fetch_by_object_type( $translation_id, 'Translation' )
  Description: Fetches DBEntry by Object type
841
842
843
               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.
844
845


846
  Returntype : arrayref of DBEntry objects; may be of type IdentityXref if
847
               there is mapping data, or OntologyXref if there is linkage data.
848
849
  Exceptions : none
  Caller     : fetch_all_by_Gene
850
851
               fetch_all_by_Translation
               fetch_all_by_Transcript
852
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
853
854
855

=cut

Graham McVicker's avatar
Graham McVicker committed
856
sub _fetch_by_object_type {
857
  my ( $self, $ensID, $ensType, $exdbname, $exdb_type ) = @_;
858

859
  my @out;
860

861
  if ( !defined($ensID) ) {
862
    throw("Can't fetch_by_EnsObject_type without an object");
Michele Clamp's avatar
Michele Clamp committed
863
  }
864
865

  if ( !defined($ensType) ) {
866
    throw("Can't fetch_by_EnsObject_type without a type");
Michele Clamp's avatar
Michele Clamp committed
867
  }
868
869

  #  my $sth = $self->prepare("
870
  my $sql = (<<SSQL);
Graham McVicker's avatar
Graham McVicker committed
871
    SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label, xref.version,
872
           exDB.dbprimary_acc_linkable, exDB.display_label_linkable,
873
           exDB.priority,
Glenn Proctor's avatar
Glenn Proctor committed
874
           exDB.db_name, exDB.db_release, exDB.status, exDB.db_display_name,
875
           exDB.secondary_db_name, exDB.secondary_db_table,
876
           oxr.object_xref_id,
877
           es.synonym,
878
879
           idt.xref_identity, idt.ensembl_identity, idt.xref_start,
           idt.xref_end, idt.ensembl_start, idt.ensembl_end,
880
           idt.cigar_line, idt.score, idt.evalue, oxr.analysis_id,
881
           gx.linkage_type,
882
           xref.info_type, xref.info_text, exDB.type, gx.source_xref_id,
Nathan Johnson's avatar
Nathan Johnson committed
883
           oxr.linkage_annotation, xref.description
884
    FROM   (xref xref, external_db exDB, object_xref oxr)
885
    LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
Graham McVicker's avatar
Graham McVicker committed
886
    LEFT JOIN identity_xref idt on idt.object_xref_id = oxr.object_xref_id
887
    LEFT JOIN ontology_xref gx on gx.object_xref_id = oxr.object_xref_id
Graham McVicker's avatar
Graham McVicker committed
888
    WHERE  xref.xref_id = oxr.xref_id
889
      AND  xref.external_db_id = exDB.external_db_id
890
891
      AND  oxr.ensembl_id = ?
      AND  oxr.ensembl_object_type = ?
892
SSQL
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913

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

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

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

916
917
  $sth->bind_param( 1, $ensID,   SQL_INTEGER );
  $sth->bind_param( 2, $ensType, SQL_VARCHAR );
918
  $sth->execute();
919
920
921
922
923
924
925
926
927

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

  my $max_rows = 1000;

  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
    while ( my $arrRef = shift( @{$rowcache} ) ) {
      my ( $refID,                  $dbprimaryId,
           $displayid,              $version,
928
           $primary_id_linkable,
929
930
931
932
933
           $display_id_linkable,    $priority,
           $dbname,                 $release,
           $exDB_status,            $exDB_db_display_name,
           $exDB_secondary_db_name, $exDB_secondary_db_table,
           $objid,                  $synonym,
934
935
936
           $xrefid,                 $ensemblid,
           $xref_start,             $xref_end,
           $ensembl_start,          $ensembl_end,
937
938
939
940
           $cigar_line,             $score,
           $evalue,                 $analysis_id,
           $linkage_type,           $info_type,
           $info_text,              $type,
941
           $source_xref_id,         $link_annotation,
942
	   $description
943
944
945
946
947
      ) = @$arrRef;

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

948
949
950
951
952
953
954

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

955
956
957
958
959
960
961
962
963
964
965
      my %obj_hash = ( 'adaptor'            => $self,
                       'dbID'               => $refID,
                       'primary_id'         => $dbprimaryId,
                       'display_id'         => $displayid,
                       'version'            => $version,
                       'release'            => $release,
                       'info_type'          => $info_type,
                       'info_text'          => $info_text,
                       'type'               => $type,
                       'secondary_db_name'  => $exDB_secondary_db_name,
                       'secondary_db_table' => $exDB_secondary_db_table,
966
                       'dbname'             => $dbname,
967
                       'description'        => $description,
968
969
                       'linkage_annotation' => $link_annotation,
                       'analysis'           => $analysis);
970
971
972
973
974

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

          $exDB->cigar_line($cigar_line);
985
986
987
988
          $exDB->xref_start($xref_start);
          $exDB->xref_end($xref_end); # was not here before 14th Jan 2009 ????
          $exDB->ensembl_start($ensembl_start);
          $exDB->ensembl_end($ensembl_end);
989
990
991
992
          $exDB->score($score);
          $exDB->evalue($evalue);

        } elsif ( defined $linkage_type && $linkage_type ne "" ) {
993
          $exDB = Bio::EnsEMBL::OntologyXref->new_fast( \%obj_hash );
994
          $source_xref = ( defined($source_xref_id)
995
996
997
998
999
1000
1001
                              ? $self->fetch_by_dbID($source_xref_id)
                              : undef );
          $exDB->add_linkage_type( $linkage_type, $source_xref || () );
          $linkage_types{$refID}->{$linkage_key} = 1;

        } else {
          $exDB = Bio::EnsEMBL::DBEntry->new_fast( \%obj_hash );
1002
        }
1003

1004
        if ( defined($exDB_status) ) { $exDB->status($exDB_status) }
1005

1006
1007
1008
1009
        $exDB->primary_id_linkable($primary_id_linkable);
        $exDB->display_id_linkable($display_id_linkable);
        $exDB->priority($priority);