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

3
  Copyright (c) 1999-2011 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
    "SELECT  xref.xref_id,
            xref.dbprimary_acc,
            xref.display_label,
            xref.version,
            exDB.dbprimary_acc_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,
91
            exDB.secondary_db_table,
Nathan Johnson's avatar
Nathan Johnson committed
92
            xref.description
93
94
95
96
97
98
99
    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 );
100
  $sth->execute();
101

102
  my $exDB;
103

104
105
106
  my $max_rows = 1000;

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


      }
143

144
      if ( defined($synonym) ) { $exDB->add_synonym($synonym) }
145

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

  $sth->finish();

  return $exDB;
152
} ## end sub fetch_by_dbID
153
154


155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
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
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";
  }
  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.dbprimary_acc_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,
            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";
  }
201

202
  my $sth = $self->prepare($sql) || die "Could not prepare $sql";
203

204
205
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.dbprimary_acc_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,
            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,
           $primary_id_linkable,
262
           $priority,
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
373
           $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,
                           -primary_id_linkable => $primary_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,
                           -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);
}


374
375
376
377
378
379
380
381
=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);
382
383
384
385
386
387
388
  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.
389
390
391
  Returntype : Bio::EnsEMBL::DBSQL::DBEntry
  Exceptions : thrown if arguments are incorrect
  Caller     : general, domainview
392
  Status     : Stable
393
394
395
396

=cut

sub fetch_by_db_accession {
397
  my ( $self, $dbname, $accession ) = @_;
398

399
  my $sth = $self->prepare(
400
401
402
403
404
405
406
407
408
409
410
411
412
413
    "SELECT xref.xref_id,
            xref.dbprimary_acc,
            xref.display_label,
            xref.version,
            exDB.dbprimary_acc_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,
414
            exDB.secondary_db_table,
Nathan Johnson's avatar
Nathan Johnson committed
415
            xref.description
416
417
418
    FROM    (xref, external_db exDB)
    LEFT JOIN external_synonym es ON
            es.xref_id = xref.xref_id
419
420
    WHERE  xref.dbprimary_acc = ?
    AND    exDB.db_name = ?
421
    AND    xref.external_db_id = exDB.external_db_id" );
422

423
424
  $sth->bind_param( 1, $accession, SQL_VARCHAR );
  $sth->bind_param( 2, $dbname,    SQL_VARCHAR );
425
  $sth->execute();
426

427
428
429
430
431
432
  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

433
    $sth->finish();
434
435
436
437
438
439
440
441
442
443
444
445
446
    $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 );
447
    $sth->execute();
448
449
450
451
  }

  my $exDB;

452
453
454
455
456
457
  my $max_rows = 1000;

  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
    while ( my $arrayref = shift( @{$rowcache} ) ) {
      my ( $dbID,                $dbprimaryId,
           $displayid,           $version,
458
           $primary_id_linkable,
459
           $priority,
460
461
462
463
           $dbname,              $db_display_name,
           $release,             $synonym,
           $info_type,           $info_text,
           $type,                $secondary_db_name,
464
           $secondary_db_table,  $description
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
      ) = @$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,
                           -priority            => $priority,
                           -db_display_name     => $db_display_name,
                           -info_type           => $info_type,
                           -info_text           => $info_text,
                           -type                => $type,
                           -secondary_db_name   => $secondary_db_name,
484
485
                           -secondary_db_table  => $secondary_db_table,
			   -description         => $description
486
487
          );

488

489
490
491
492
493
494
      }

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

    } ## end while ( my $arrayref = shift...
  } ## end while ( my $rowcache = $sth...
495
496

  $sth->finish();
497
498

  return $exDB;
499
} ## end sub fetch_by_db_accession
500
501


Graham McVicker's avatar
Graham McVicker committed
502
503
=head2 store

504
505
  Arg [1]    : Bio::EnsEMBL::DBEntry $exObj
               The DBEntry (xref) to be stored
506
507
508
509
  Arg [2]    : Int $ensID
               The dbID of an EnsEMBL object to associate with this external
               database entry
  Arg [3]    : string $ensType ('Transcript', 'Translation', 'Gene')
510
511
               The type of EnsEMBL object that this external database entry is
               being associated with.
512
513
514
515
516
  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.
517
  Example    : $dbea->store($db_entry, $transcript_id, 'Transcript');
518
519
520
  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.
521
522
523
  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
524
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
525
526
527

=cut

528
sub store {
529
530
  my ( $self, $exObj, $ensID, $ensType, $ignore_release ) = @_;

531
  my $dbJustInserted;
532

533
534
535
536
537
538
  #
  # backwards compatibility check:
  # check if $ensID is an object; if so, use $obj->dbID
  #
  my $ensembl_id;

539

540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
  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() ) );
      }
557
    } else {
558
      throw("Invalid dbID passed to DBEntryAdaptor->store()");
559
560
561
    }
  }

562
563
564
  #
  # Check for the existance of the external_db, throw if it does not exist
  #
565

566
  my ($dbRef, $release_clause);
567
568

  if ( !$ignore_release ) {
569
570
571
572
573
574
575
576
577

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


578
    my $sth = $self->prepare( "
579
580
     SELECT external_db_id
       FROM external_db
581
      WHERE db_name    = ?
582
        $release_clause" );
583
584

    $sth->bind_param( 1, $exObj->dbname(),  SQL_VARCHAR );
585
    $sth->bind_param( 2, $exObj->release(), SQL_VARCHAR ) if defined $exObj->release();
586
587
588
    $sth->execute();

    ($dbRef) = $sth->fetchrow_array();
589
	$sth->finish();
590
591
592
593
594
595
596

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

598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
    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() )
      );
    }
614
  }
615

616
617
618
  #
  # Check for the existance of the external reference, add it if not present
  #
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639

  my $sql =  "SELECT xref_id FROM xref
                WHERE external_db_id = ?
                  AND dbprimary_acc  = ?
                  AND version        = ?";

  if(defined $exObj->info_type){
    $sql .= " AND  info_type      = ?";
  }
  else{
    $sql .= " AND info_type is null";
  }

  if(defined $exObj->info_text){
    $sql .= " AND  info_text      = ?";
  }
  else{
    $sql .= " AND info_text is null";
  }

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

641
642
643
  $sth->bind_param(1,$dbRef,SQL_INTEGER);
  $sth->bind_param(2,$exObj->primary_id,SQL_VARCHAR);
  $sth->bind_param(3,$exObj->version,SQL_VARCHAR);
644

645
646
647
648
649
650
651
  my $i = 4;
  if(defined $exObj->info_type){
    $sth->bind_param($i++,$exObj->info_type,SQL_VARCHAR);
  }
  if(defined $exObj->info_text){
    $sth->bind_param($i++,$exObj->info_text,SQL_VARCHAR);
  }
652
  $sth->execute();
653

654
  my ($dbX) = $sth->fetchrow_array();
655

656
657
  $sth->finish();
  if(!$dbX) {
658
659
660
661
662
663
664
    if(!$exObj->primary_id()) {
      throw("DBEntry cannot be stored without a primary_id attribute.");
    }

    #
    # store the new xref
    #
665
    $sth = $self->prepare( "
666
       INSERT ignore INTO xref
667
668
       SET dbprimary_acc = ?,
           display_label = ?,
Arne Stabenau's avatar
Arne Stabenau committed
669
670
           version = ?,
           description = ?,
671
672
           external_db_id = ?,
           info_type = ?,
Glenn Proctor's avatar
Glenn Proctor committed
673
           info_text = ?");
674
675
676
677
678
679
680
681
    $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);

682
    $sth->execute();
683

684
685
686
687
    $dbX = $sth->{'mysql_insertid'};
    $sth->finish();
    #
    # store the synonyms for the new xref
688
    #
689
690
691
692
693
694
695
696
    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
697
         SET xref_id = ?, synonym = ?");
698
699

    my $synonyms = $exObj->get_all_synonyms();
700
    foreach my $syn ( @$synonyms ) {
701
	$synonym_check_sth->bind_param(1,$dbX,SQL_INTEGER);
Glenn Proctor's avatar
Glenn Proctor committed
702
	$synonym_check_sth->bind_param(2,$syn,SQL_VARCHAR);
703
	$synonym_check_sth->execute();
704
      my ($dbSyn) = $synonym_check_sth->fetchrow_array();
705
706
	$synonym_store_sth->bind_param(1,$dbX,SQL_INTEGER);
	$synonym_store_sth->bind_param(2,$syn,SQL_VARCHAR);
707
	$synonym_store_sth->execute() if(!$dbSyn);
708
709
710
    }
    $synonym_check_sth->finish();
    $synonym_store_sth->finish();
711
  } elsif (! defined( $exObj->dbID() ) ) {
712
    $exObj->dbID($dbX);
713
  }
714
715
716
717
718
719
720

  if ( defined($ensembl_id) ) {
    #
    # check if the object mapping was already stored
    #
    $sth = $self->prepare(
      qq(
721
722
723
724
725
726
SELECT  xref_id
FROM    object_xref
WHERE   xref_id = ?
  AND   ensembl_object_type = ?
  AND   ensembl_id = ?
  AND   (   linkage_annotation = ?
727
  OR        linkage_annotation IS NULL  )) );
728

729
730
731
732
    $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 );
733

734
    $sth->execute();
735

736
    my ($tst) = $sth->fetchrow_array();
737

738
    $sth->finish();
739

740
741
742
743
744
745
746
747
748
749
750
    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;
      }
751

752
753
      $sth = $self->prepare(
        qq(
754
755
756
757
INSERT IGNORE INTO object_xref
  SET   xref_id = ?,
        ensembl_object_type = ?,
        ensembl_id = ?,
758
759
        linkage_annotation = ?,
        analysis_id = ? ) );
760

761
762
763
764
765
      $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 );
766

767
768
769
770
771
772
773
774
775
776
777
      $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( "
778
779
             INSERT ignore INTO identity_xref
             SET object_xref_id = ?,
780
781
782
783
784
785
             xref_identity = ?,
             ensembl_identity = ?,
             xref_start = ?,
             xref_end   = ?,
             ensembl_start = ?,
             ensembl_end = ?,
786
787
             cigar_line = ?,
             score = ?,
788
             evalue = ?" );
789
790
791
792
793
794
795
796
797
798
799
800
801
        $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( "
802
             INSERT ignore INTO ontology_xref
803
                SET object_xref_id = ?,
804
                    source_xref_id = ?,
805
                    linkage_type = ? " );
806
807
808
809
810
811
812
813
814
815
816
817
818
        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();
        }
819
      }
820
821
822
    } ## end if ( !$tst )
  } ## end if ( defined($ensembl_id...))

823
  return $dbX;
824
825
}

826

827
828
829
830
831
=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
832
               otherwise returns undef.  Exists is defined as an entry with
833
834
835
836
               the same external_db and display_id
  Returntype : int
  Exceptions : thrown on incorrect args
  Caller     : GeneAdaptor::store, TranscriptAdaptor::store
837
  Status     : Stable
838
839
840
841

=cut

sub exists {
842
  my ($self, $dbe) = @_ ;
843
844

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

848
  my $sth = $self->prepare('SELECT x.xref_id
849
850
                            FROM   xref x, external_db xdb
                            WHERE  x.external_db_id = xdb.external_db_id
851
                            AND    x.display_label = ?
852
853
                            AND    xdb.db_name = ?
                            AND    x.dbprimary_acc = ?');
854

855
  $sth->bind_param(1,$dbe->display_id,SQL_VARCHAR);
Glenn Proctor's avatar
Glenn Proctor committed
856
  $sth->bind_param(2,$dbe->dbname,SQL_VARCHAR);
857
  $sth->bind_param(3,$dbe->primary_id,SQL_VARCHAR);
858
  $sth->execute();
859
860
861
862
863
864
865
866
867

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

  $sth->finish;

  return $dbID;
}


868
=head2 fetch_all_by_Gene
Graham McVicker's avatar
Graham McVicker committed
869

870
  Arg [1]    : Bio::EnsEMBL::Gene $gene
871
               (The gene to retrieve DBEntries for)
872
  Arg [2]    : optional external database name
873
  Arg [3]    : optional external_db type
Graham McVicker's avatar
Graham McVicker committed
874
  Example    : @db_entries = @{$db_entry_adaptor->fetch_by_Gene($gene)};
875
876
877
  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
878
               all of the gene, transcript, and translation xrefs associated
879
               with this gene.
880
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
881
               there is mapping data, or OntologyXref if there is linkage data.
882
  Exceptions : thows if gene object not passed
Graham McVicker's avatar
Graham McVicker committed
883
  Caller     : Bio::EnsEMBL::Gene
884
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
885
886
887

=cut

888
sub fetch_all_by_Gene {
889
  my ( $self, $gene, $ex_db_reg, $exdb_type ) = @_;
890

891
892
893
  if(!ref($gene) || !$gene->isa('Bio::EnsEMBL::Gene')) {
    throw("Bio::EnsEMBL::Gene argument expected.");
  }
894

895
  return $self->_fetch_by_object_type($gene->dbID(), 'Gene', $ex_db_reg, $exdb_type);
Arne Stabenau's avatar
Arne Stabenau committed
896
897
}

Graham McVicker's avatar
Graham McVicker committed
898

899
=head2 fetch_all_by_Transcript
Graham McVicker's avatar
Graham McVicker committed
900

Graham McVicker's avatar
Graham McVicker committed
901
  Arg [1]    : Bio::EnsEMBL::Transcript
902
  Arg [2]    : optional external database name
903
  Arg [3]    : optional external_db type
904
  Example    : @db_entries = @{$db_entry_adaptor->fetch_by_Gene($trans)};
905
  Description: This returns a list of DBEntries associated with this
906
907
908
               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
909
               associated with this gene.
910
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
911
               there is mapping data, or OntologyXref if there is linkage data.
912
  Exceptions : throes if transcript argument not passed
913
  Caller     : Bio::EnsEMBL::Gene
914
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
915
916
917

=cut

918
sub fetch_all_by_Transcript {
919
  my ( $self, $trans, $ex_db_reg, $exdb_type ) = @_;
920

921
922
923
924
  if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Transcript')) {
    throw("Bio::EnsEMBL::Transcript argument expected.");
  }

925
  return $self->_fetch_by_object_type( $trans->dbID(), 'Transcript', $ex_db_reg, $exdb_type);
926
927
}

Graham McVicker's avatar
Graham McVicker committed
928

929
=head2 fetch_all_by_Translation
Graham McVicker's avatar
Graham McVicker committed
930

Graham McVicker's avatar
Graham McVicker committed
931
  Arg [1]    : Bio::EnsEMBL::Translation $trans
932
               (The translation to fetch database entries for)
933
  Arg [2]    : optional external database name
934
  Arg [3]    : optional externaldb type
935
  Example    : @db_entries = @{$db_entry_adptr->fetch_all_by_Translation($trans)};
Graham McVicker's avatar
Graham McVicker committed
936
  Description: Retrieves external database entries for an EnsEMBL translation
937
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
938
               there is mapping data, or OntologyXref if there is linkage data.
939
  Exceptions : throws if translation object not passed
Graham McVicker's avatar
Graham McVicker committed
940
  Caller     : general
941
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
942
943
944

=cut

945
sub fetch_all_by_Translation {
946
  my ( $self, $trans, $ex_db_reg, $exdb_type ) = @_;
947

948
949
950
  if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Translation')) {
    throw('Bio::EnsEMBL::Translation argument expected.');
  }
951
  if( ! $trans->dbID ){
952
953
954
    warning( "Cannot fetch_all_by_Translation without a dbID" );
    return [];
  }
Glenn Proctor's avatar
Glenn Proctor committed
955

956
  return $self->_fetch_by_object_type( $trans->dbID(), 'Translation', $ex_db_reg, $exdb_type );
957
958
959
}


960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981

=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
982
  Status     : Stable
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019

=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
1020
  #No need to compare linkage_annotation here
1021
1022
1023
1024
1025
1026
  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 = ?");
1027
1028
1029
1030
  $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();
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041

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

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

  # delete from the tables which contain additional linkage information

1042
  $sth = $self->prepare("DELETE FROM ontology_xref WHERE object_xref_id = ?");
1043
1044
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
1045
1046
1047
  $sth->finish();

  $sth = $self->prepare("DELETE FROM identity_xref WHERE object_xref_id = ?");
1048
1049
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
1050
1051
1052
1053
  $sth->finish();

  # delete the actual linkage itself
  $sth = $self->prepare("DELETE FROM object_xref WHERE object_xref_id = ?");
1054
1055
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
1056
1057
1058
1059
1060
1061
1062
  $sth->finish();

  return;
}


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

1064
  Arg [1]    : string $ensID
1065
  Arg [2]    : string $ensType (object type to be returned)
1066
  Arg [3]    : optional $exdbname (external database name)
1067
1068
1069
1070
1071
               (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)
1072
1073
  Example    : $self->_fetch_by_object_type( $translation_id, 'Translation' )
  Description: Fetches DBEntry by Object type
1074
1075
1076
               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.
1077
1078


1079
  Returntype : arrayref of DBEntry objects; may be of type IdentityXref if
1080
               there is mapping data, or OntologyXref if there is linkage data.
1081
1082
  Exceptions : none
  Caller     : fetch_all_by_Gene
1083
1084
               fetch_all_by_Translation
               fetch_all_by_Transcript
1085
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
1086
1087
1088

=cut

Graham McVicker's avatar
Graham McVicker committed
1089
sub _fetch_by_object_type {
1090
  my ( $self, $ensID, $ensType, $exdbname, $exdb_type ) = @_;
1091

1092
  my @out;
1093

1094
  if ( !defined($ensID) ) {
1095
    throw("Can't fetch_by_EnsObject_type without an object");
Michele Clamp's avatar
Michele Clamp committed
1096
  }
1097
1098

  if ( !defined($ensType) ) {
1099
    throw("Can't fetch_by_EnsObject_type without a type");
Michele Clamp's avatar
Michele Clamp committed
1100
  }
1101
1102

  #  my $sth = $self->prepare("
1103
  my $sql = (<<SSQL);
Graham McVicker's avatar
Graham McVicker committed
1104
    SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label, xref.version,
1105
           exDB.dbprimary_acc_linkable, 
1106
           exDB.priority,
Glenn Proctor's avatar
Glenn Proctor committed
1107
           exDB.db_name, exDB.db_release, exDB.status, exDB.db_display_name,
1108
           exDB.secondary_db_name, exDB.secondary_db_table,
1109
           oxr.object_xref_id,
1110
           es.synonym,
1111
1112
           idt.xref_identity, idt.ensembl_identity, idt.xref_start,
           idt.xref_end, idt.ensembl_start, idt.ensembl_end,
1113
           idt.cigar_line, idt.score, idt.evalue, oxr.analysis_id,
1114
           gx.linkage_type,
1115
           xref.info_type, xref.info_text, exDB.type, gx.source_xref_id,
Nathan Johnson's avatar
Nathan Johnson committed
1116
           oxr.linkage_annotation, xref.description
1117
    FROM   (xref xref, external_db exDB, object_xref oxr)
1118
    LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
Graham McVicker's avatar
Graham McVicker committed
1119
    LEFT JOIN identity_xref idt on idt.object_xref_id = oxr.object_xref_id
1120
    LEFT JOIN ontology_xref gx on gx.object_xref_id = oxr.object_xref_id
Graham McVicker's avatar
Graham McVicker committed
1121
    WHERE  xref.xref_id = oxr.xref_id
1122
      AND  xref.external_db_id = exDB.external_db_id
1123
1124
      AND  oxr.ensembl_id = ?
      AND  oxr.ensembl_object_type = ?
1125
SSQL
1126
1127
1128

  if ( defined($exdbname) ) {
    if ( index( $exdbname, '%' ) != -1 ) {
1129
      $sql .= " AND exDB.db_name LIKE "
1130
1131
        . $self->dbc()->db_handle()->quote( $exdbname, SQL_VARCHAR );
    } else {
1132
      $sql .= " AND exDB.db_name = "
1133
1134
1135
1136
1137
1138
        . $self->dbc()->db_handle()->quote( $exdbname, SQL_VARCHAR );
    }
  }

  if ( defined($exdb_type) ) {
    if ( index( $exdb_type, '%' ) != -1 ) {
1139
      $sql .= " AND exDB.type LIKE "