DBEntryAdaptor.pm 61.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
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
201
202
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
262
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
374
375
376
377
378
379
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.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,
            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";
  }
#  print "\n\n".$sql."\n";
  my $sth = $self->prepare($sql) || die "Could not prepare $sql";
#  print "sth is $sth\n";  
  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.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,
            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";

#  print "sth is $sth\n";
  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,
           $display_id_linkable, $priority,
           $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,
                           -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,
                           -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);
}


380
381
382
383
384
385
386
387
=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);
388
389
390
391
392
393
394
  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.
395
396
397
  Returntype : Bio::EnsEMBL::DBSQL::DBEntry
  Exceptions : thrown if arguments are incorrect
  Caller     : general, domainview
398
  Status     : Stable
399
400
401
402

=cut

sub fetch_by_db_accession {
403
  my ( $self, $dbname, $accession ) = @_;
404

405
  my $sth = $self->prepare(
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
    "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,
421
            exDB.secondary_db_table,
Nathan Johnson's avatar
Nathan Johnson committed
422
            xref.description
423
424
425
    FROM    (xref, external_db exDB)
    LEFT JOIN external_synonym es ON
            es.xref_id = xref.xref_id
426
427
    WHERE  xref.dbprimary_acc = ?
    AND    exDB.db_name = ?
428
    AND    xref.external_db_id = exDB.external_db_id" );
429

430
431
  $sth->bind_param( 1, $accession, SQL_VARCHAR );
  $sth->bind_param( 2, $dbname,    SQL_VARCHAR );
432
  $sth->execute();
433

434
435
436
437
438
439
  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

440
    $sth->finish();
441
442
443
444
445
446
447
448
449
450
451
452
453
    $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 );
454
    $sth->execute();
455
456
457
458
  }

  my $exDB;

459
460
461
462
463
464
  my $max_rows = 1000;

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

496

497
498
499
500
501
502
      }

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

    } ## end while ( my $arrayref = shift...
  } ## end while ( my $rowcache = $sth...
503
504

  $sth->finish();
505
506

  return $exDB;
507
} ## end sub fetch_by_db_accession
508
509


Graham McVicker's avatar
Graham McVicker committed
510
511
=head2 store

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

=cut

536
sub store {
537
538
  my ( $self, $exObj, $ensID, $ensType, $ignore_release ) = @_;

539
  my $dbJustInserted;
540

541
542
543
544
545
546
  #
  # backwards compatibility check:
  # check if $ensID is an object; if so, use $obj->dbID
  #
  my $ensembl_id;

547

548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
  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() ) );
      }
565
    } else {
566
      throw("Invalid dbID passed to DBEntryAdaptor->store()");
567
568
569
    }
  }

570
571
572
  #
  # Check for the existance of the external_db, throw if it does not exist
  #
573

574
  my ($dbRef, $release_clause);
575
576

  if ( !$ignore_release ) {
577
578
579
580
581
582
583
584
585

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


586
    my $sth = $self->prepare( "
587
588
     SELECT external_db_id
       FROM external_db
589
      WHERE db_name    = ?
590
        $release_clause" );
591
592

    $sth->bind_param( 1, $exObj->dbname(),  SQL_VARCHAR );
593
    $sth->bind_param( 2, $exObj->release(), SQL_VARCHAR ) if defined $exObj->release();
594
595
596
    $sth->execute();

    ($dbRef) = $sth->fetchrow_array();
597
	$sth->finish();
598
599
600
601
602
603
604

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

606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
    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() )
      );
    }
622
  }
623

624
625
626
  #
  # Check for the existance of the external reference, add it if not present
  #
627
  my $sth = $self->prepare( "
628
629
630
       SELECT xref_id
         FROM xref
        WHERE external_db_id = ?
631
632
633
634
          AND dbprimary_acc  = ?
          AND version        = ?
          AND info_type      = ?
          AND info_text      = ?" );
635

636
637
638
  $sth->bind_param(1,$dbRef,SQL_INTEGER);
  $sth->bind_param(2,$exObj->primary_id,SQL_VARCHAR);
  $sth->bind_param(3,$exObj->version,SQL_VARCHAR);
639
640
641
  $sth->bind_param(4,$exObj->info_type,SQL_VARCHAR);
  $sth->bind_param(5,$exObj->info_text,SQL_VARCHAR);

642
  $sth->execute();
643
  my ($dbX) = $sth->fetchrow_array();
644

645
646
  $sth->finish();
  if(!$dbX) {
647
648
649
650
651
652
653
    if(!$exObj->primary_id()) {
      throw("DBEntry cannot be stored without a primary_id attribute.");
    }

    #
    # store the new xref
    #
654
    $sth = $self->prepare( "
655
       INSERT ignore INTO xref
656
657
       SET dbprimary_acc = ?,
           display_label = ?,
Arne Stabenau's avatar
Arne Stabenau committed
658
659
           version = ?,
           description = ?,
660
661
           external_db_id = ?,
           info_type = ?,
Glenn Proctor's avatar
Glenn Proctor committed
662
           info_text = ?");
663
664
665
666
667
668
669
670
    $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);

671
    $sth->execute();
672

673
674
675
676
    $dbX = $sth->{'mysql_insertid'};
    $sth->finish();
    #
    # store the synonyms for the new xref
677
    #
678
679
680
681
682
683
684
685
    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
686
         SET xref_id = ?, synonym = ?");
687
688

    my $synonyms = $exObj->get_all_synonyms();
689
    foreach my $syn ( @$synonyms ) {
690
	$synonym_check_sth->bind_param(1,$dbX,SQL_INTEGER);
Glenn Proctor's avatar
Glenn Proctor committed
691
	$synonym_check_sth->bind_param(2,$syn,SQL_VARCHAR);
692
	$synonym_check_sth->execute();
693
      my ($dbSyn) = $synonym_check_sth->fetchrow_array();
694
695
	$synonym_store_sth->bind_param(1,$dbX,SQL_INTEGER);
	$synonym_store_sth->bind_param(2,$syn,SQL_VARCHAR);
696
	$synonym_store_sth->execute() if(!$dbSyn);
697
698
699
    }
    $synonym_check_sth->finish();
    $synonym_store_sth->finish();
700
  } elsif (! defined( $exObj->dbID() ) ) {
701
    $exObj->dbID($dbX);
702
  }
703
704
705
706
707
708
709

  if ( defined($ensembl_id) ) {
    #
    # check if the object mapping was already stored
    #
    $sth = $self->prepare(
      qq(
710
711
712
713
714
715
SELECT  xref_id
FROM    object_xref
WHERE   xref_id = ?
  AND   ensembl_object_type = ?
  AND   ensembl_id = ?
  AND   (   linkage_annotation = ?
716
  OR        linkage_annotation IS NULL  )) );
717

718
719
720
721
    $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 );
722

723
    $sth->execute();
724

725
    my ($tst) = $sth->fetchrow_array();
726

727
    $sth->finish();
728

729
730
731
732
733
734
735
736
737
738
739
    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;
      }
740

741
742
      $sth = $self->prepare(
        qq(
743
744
745
746
INSERT IGNORE INTO object_xref
  SET   xref_id = ?,
        ensembl_object_type = ?,
        ensembl_id = ?,
747
748
        linkage_annotation = ?,
        analysis_id = ? ) );
749

750
751
752
753
754
      $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 );
755

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

813
  return $dbX;
814
815
}

816

817
818
819
820
821
=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
822
               otherwise returns undef.  Exists is defined as an entry with
823
824
825
826
               the same external_db and display_id
  Returntype : int
  Exceptions : thrown on incorrect args
  Caller     : GeneAdaptor::store, TranscriptAdaptor::store
827
  Status     : Stable
828
829
830
831

=cut

sub exists {
832
  my ($self, $dbe) = @_ ;
833
834

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

838
  my $sth = $self->prepare('SELECT x.xref_id
839
840
                            FROM   xref x, external_db xdb
                            WHERE  x.external_db_id = xdb.external_db_id
841
                            AND    x.display_label = ?
842
843
                            AND    xdb.db_name = ?
                            AND    x.dbprimary_acc = ?');
844

845
  $sth->bind_param(1,$dbe->display_id,SQL_VARCHAR);
Glenn Proctor's avatar
Glenn Proctor committed
846
  $sth->bind_param(2,$dbe->dbname,SQL_VARCHAR);
847
  $sth->bind_param(3,$dbe->primary_id,SQL_VARCHAR);
848
  $sth->execute();
849
850
851
852
853
854
855
856
857

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

  $sth->finish;

  return $dbID;
}


858
=head2 fetch_all_by_Gene
Graham McVicker's avatar
Graham McVicker committed
859

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

=cut

878
sub fetch_all_by_Gene {
879
  my ( $self, $gene, $ex_db_reg, $exdb_type ) = @_;
880

881
882
883
  if(!ref($gene) || !$gene->isa('Bio::EnsEMBL::Gene')) {
    throw("Bio::EnsEMBL::Gene argument expected.");
  }
884

885
  return $self->_fetch_by_object_type($gene->dbID(), 'Gene', $ex_db_reg, $exdb_type);
Arne Stabenau's avatar
Arne Stabenau committed
886
887
}

Graham McVicker's avatar
Graham McVicker committed
888

889
=head2 fetch_all_by_Transcript
Graham McVicker's avatar
Graham McVicker committed
890

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

=cut

908
sub fetch_all_by_Transcript {
909
  my ( $self, $trans, $ex_db_reg, $exdb_type ) = @_;
910

911
912
913
914
  if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Transcript')) {
    throw("Bio::EnsEMBL::Transcript argument expected.");
  }

915
  return $self->_fetch_by_object_type( $trans->dbID(), 'Transcript', $ex_db_reg, $exdb_type);
916
917
}

Graham McVicker's avatar
Graham McVicker committed
918

919
=head2 fetch_all_by_Translation
Graham McVicker's avatar
Graham McVicker committed
920

Graham McVicker's avatar
Graham McVicker committed
921
  Arg [1]    : Bio::EnsEMBL::Translation $trans
922
               (The translation to fetch database entries for)
923
  Arg [2]    : optional external database name
924
  Arg [3]    : optional externaldb type
925
  Example    : @db_entries = @{$db_entry_adptr->fetch_all_by_Translation($trans)};
Graham McVicker's avatar
Graham McVicker committed
926
  Description: Retrieves external database entries for an EnsEMBL translation
927
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
928
               there is mapping data, or OntologyXref if there is linkage data.
929
  Exceptions : throws if translation object not passed
Graham McVicker's avatar
Graham McVicker committed
930
  Caller     : general
931
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
932
933
934

=cut

935
sub fetch_all_by_Translation {
936
  my ( $self, $trans, $ex_db_reg, $exdb_type ) = @_;
937

938
939
940
  if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Translation')) {
    throw('Bio::EnsEMBL::Translation argument expected.');
  }
941
  if( ! $trans->dbID ){
942
943
944
    warning( "Cannot fetch_all_by_Translation without a dbID" );
    return [];
  }
Glenn Proctor's avatar
Glenn Proctor committed
945

946
  return $self->_fetch_by_object_type( $trans->dbID(), 'Translation', $ex_db_reg, $exdb_type );
947
948
949
}


950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971

=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
972
  Status     : Stable
973
974
975
976
977
978
979
980
981
982
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

=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
1010
  #No need to compare linkage_annotation here
1011
1012
1013
1014
1015
1016
  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 = ?");
1017
1018
1019
1020
  $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();
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031

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

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

  # delete from the tables which contain additional linkage information

1032
  $sth = $self->prepare("DELETE FROM ontology_xref WHERE object_xref_id = ?");
1033
1034
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
1035
1036
1037
  $sth->finish();

  $sth = $self->prepare("DELETE FROM identity_xref WHERE object_xref_id = ?");
1038
1039
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
1040
1041
1042
1043
  $sth->finish();

  # delete the actual linkage itself
  $sth = $self->prepare("DELETE FROM object_xref WHERE object_xref_id = ?");
1044
1045
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
1046
1047
1048
1049
1050
1051
1052
  $sth->finish();

  return;
}


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

1054
  Arg [1]    : string $ensID
1055
  Arg [2]    : string $ensType (object type to be returned)
1056
  Arg [3]    : optional $exdbname (external database name)
1057
1058
1059
1060
1061
               (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)
1062
1063
  Example    : $self->_fetch_by_object_type( $translation_id, 'Translation' )
  Description: Fetches DBEntry by Object type
1064
1065
1066
               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.
1067
1068


1069
  Returntype : arrayref of DBEntry objects; may be of type IdentityXref if
1070
               there is mapping data, or OntologyXref if there is linkage data.
1071
1072
  Exceptions : none
  Caller     : fetch_all_by_Gene
1073
1074
               fetch_all_by_Translation
               fetch_all_by_Transcript
1075
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
1076
1077
1078

=cut

Graham McVicker's avatar
Graham McVicker committed
1079
sub _fetch_by_object_type {
1080
  my ( $self, $ensID, $ensType, $exdbname, $exdb_type ) = @_;
1081

1082
  my @out;
1083

1084
  if ( !defined($ensID) ) {
1085
    throw("Can't fetch_by_EnsObject_type without an object");
Michele Clamp's avatar
Michele Clamp committed
1086
  }
1087
1088

  if ( !defined($ensType) ) {
1089
    throw("Can't fetch_by_EnsObject_type without a type");
Michele Clamp's avatar
Michele Clamp committed
1090
  }
1091
1092

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

  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 );
    }
  }

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

1139
1140
  $sth->bind_param( 1, $ensID,   SQL_INTEGER );
  $sth->bind_param( 2, $ensType, SQL_VARCHAR );
1141
  $sth->execute();
1142
1143
1144
1145
1146
1147
1148
1149
1150

  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,
1151
           $primary_id_linkable,
1152
1153
1154
1155
1156
           $display_id_linkable,    $priority,
           $dbname,                 $release,
           $exDB_status,            $exDB_db_display_name,
           $exDB_secondary_db_name, $exDB_secondary_db_table,
           $objid,                  $synonym,
1157