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

Andy Yates's avatar
Andy Yates committed
3
  Copyright (c) 1999-2012 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
    "SELECT  xref.xref_id,
            xref.dbprimary_acc,
            xref.display_label,
            xref.version,
            exDB.priority,
            exDB.db_name,
            exDB.db_display_name,
            exDB.db_release,
            es.synonym,
            xref.info_type,
            xref.info_text,
            exDB.type,
            exDB.secondary_db_name,
90
            exDB.secondary_db_table,
Nathan Johnson's avatar
Nathan Johnson committed
91
            xref.description
92
93
94
95
96
97
98
    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 );
99
  $sth->execute();
100

101
  my $exDB;
102

103
104
105
  my $max_rows = 1000;

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

      if ( !defined($exDB) ) {
        $exDB =
          Bio::EnsEMBL::DBEntry->new(
                           -adaptor             => $self,
                           -dbID                => $dbID,
                           -primary_id          => $dbprimaryId,
                           -display_id          => $displayid,
                           -version             => $version,
                           -release             => $release,
                           -dbname              => $dbname,
                           -priority            => $priority,
                           -db_display_name     => $db_display_name,
                           -info_type           => $info_type,
                           -info_text           => $info_text,
                           -type                => $type,
                           -secondary_db_name   => $secondary_db_name,
134
                           -secondary_db_table  => $secondary_db_table,
135
			               -description         => $description
136
137
138
139
          );


      }
140

141
      if ( defined($synonym) ) { $exDB->add_synonym($synonym) }
142

143
144
    } ## end while ( my $arrayref = shift...
  } ## end while ( my $rowcache = $sth...
145
146
147
148

  $sth->finish();

  return $exDB;
149
} ## end sub fetch_by_dbID
150
151


152
153
154
155
156
157
158
159
160
161
162
163
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";
  }
164
165
166
167
168
169
  elsif($ensembl_object->isa("Bio::EnsEMBL::Operon")){
    $object_type = "Operon";
  }
  elsif($ensembl_object->isa("Bio::EnsEMBL::OperonTranscript")){
    $object_type = "OperonTranscript";
  }
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
  else{
    warn(ref($ensembl_object)." is not a Gene Transcript or Translation object??\n");
    return undef;
  }
  my $sql = "SELECT xref.xref_id,
               xref.dbprimary_acc,
            xref.display_label,
            xref.version,
            exDB.priority,
            exDB.db_name,
            exDB.db_display_name,
            exDB.db_release,
            es.synonym,
            xref.info_type,
            xref.info_text,
            exDB.type,
            exDB.secondary_db_name,
            exDB.secondary_db_table,
            xref.description
    FROM    (xref, external_db exDB, dependent_xref dx, object_xref ox)
    LEFT JOIN external_synonym es ON
            es.xref_id = xref.xref_id
    WHERE   xref.external_db_id = exDB.external_db_id AND
            ox.xref_id = xref.xref_id AND
            ox.ensembl_object_type = \'$object_type\' AND
            ox.ensembl_id = ".$ensembl_object->dbID();

  if($constraint){
    $sql .= " AND $constraint";
  }
  else{
    die "NO constraint???\n";
  }
203

204
  my $sth = $self->prepare($sql) || die "Could not prepare $sql";
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.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,
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
           $dbname,              $db_display_name,
           $release,             $synonym,
           $info_type,           $info_text,
           $type,                $secondary_db_name,
           $secondary_db_table,  $description
      ) = @$arrayref;

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

	if ($synonym) { $exDB->add_synonym($synonym) };
	$seen{$dbID} = 1;
	push @list, $exDB;
      }



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

  $sth->finish();

  return \@list;

}


=head2 get_all_dependents

  Args[1]    : dbID of the DBentry to get the dependents of.
  Args[2]    : (optional) Bio::EnsEMBL::Gene, Transcript or Translation object
  Example    : my @dependents = @{ $dbe_adaptor->get_all_dependents(1234) };
  Description: Get a list of DBEntrys that are depenednet on the DBEntry.
               if an ensembl gene transcript or translation is given then only
               the ones on that object will be given
  Returntype : listref of DBEntrys. May be empty.
  Exceptions : none
  Caller     : DBEntry->get_all_dependnets
  Status     : UnStable

=cut

sub get_all_dependents {
  my ( $self, $dbid, $ensembl_object) = @_;
  
  if(defined($ensembl_object) and !($ensembl_object->isa("Bio::EnsEMBL::Feature") or $ensembl_object->isa("Bio::EnsEMBL::Translation"))){
    die ref($ensembl_object)." is not an Gene Transcript or Translation";
  }
  
  my $constraint = " dx.master_xref_id = $dbid AND  dx.dependent_xref_id = xref.xref_id";
  if(defined($ensembl_object)){
    return $self->_get_all_dm_loc_sth($constraint, $ensembl_object);
  }
  else{
    return $self->_get_all_dm_sth($constraint, $ensembl_object);
  }

}

=head2 get_all_masters

  Args[1]    : dbID of the DBentry to get the masters of.
  Args[2]    : (optional) Bio::EnsEMBL::Gene, Transcript or Translation object
  Example    : my @masters = @{ $dbe_adaptor->get_all_masters(1234) };
  Description: Get a list of DBEntrys that are the masters of the DBEntry.
               if an ensembl gene transcript or translation is given then only
               the ones on that object will be given.
  Returntype : listref of DBEntrys. May be empty.
  Exceptions : none
  Caller     : DBEntry->get_all_masters
  Status     : UnStable

=cut

sub get_all_masters {
  my ( $self, $dbid, $ensembl_object ) = @_;
  
  if(defined($ensembl_object) and !($ensembl_object->isa("Bio::EnsEMBL::Feature") or $ensembl_object->isa("Bio::EnsEMBL::Translation"))){
    die ref($ensembl_object)." is not an Gene Transcript or Translation";
  }
  
  my $constraint = "dx.dependent_xref_id = $dbid AND  dx.master_xref_id = xref.xref_id";

  if(defined($ensembl_object)){
    return $self->_get_all_dm_loc_sth($constraint, $ensembl_object);
  }
  else{
    return $self->_get_all_dm_sth($constraint, $ensembl_object);
  }
#  return $self->_get_all_dm($constraint, $ensembl_object);
}


373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
=head fetch_all_by_name

  Arg [1]    : string $name - The name of the external reference.
               found in accession, display_label or synonym
  Arg [2]    : (optional) string $dbname  - The name of the database which 
               the provided name is for.

  Example    : my $xref = @{$dbea->fetch_all_by_name('BRAC2','HGNC')}[0];
               print $xref->description(), "\n" if($xref);
  Description: Retrieves list of DBEntrys (xrefs) via a name.
               The accesion is looked for first then the synonym and finally
               the display_label.
               NOTE $dbname this is optional but adding this speeds the
               process up if you know what you are looking for.

               NOTE:  In a multi-species database, this method will
               return all the entries matching the search criteria, not
               just the ones associated with the current species.
  Returntype : Bio::EnsEMBL::DBSQL::DBEntry
  Exceptions : thrown if arguments are incorrect
  Caller     : general, domainview
  Status     : Stable

=cut

sub fetch_all_by_name {
  my ( $self, $name, $dbname ) = @_;

  my $sql = (<<SQL);
  SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label, xref.version,
         exDB.priority, exDB.db_name, exDB.db_display_name, exDB.db_release,
            es.synonym, xref.info_type, xref.info_text,
            exDB.type, exDB.secondary_db_name, exDB.secondary_db_table,
            xref.description
    FROM    (xref, external_db exDB)
    LEFT JOIN external_synonym es ON
            es.xref_id = xref.xref_id
    WHERE  (xref.dbprimary_acc = ? or xref.display_label = ?)
    AND    xref.external_db_id = exDB.external_db_id
SQL

  if(defined $dbname){
    $sql .= " AND    exDB.db_name = ?";
  }
  my $sth = $self->prepare($sql);
  $sth->bind_param( 1, $name, SQL_VARCHAR );
  $sth->bind_param( 2, $name, SQL_VARCHAR );
  if(defined $dbname){
    $sth->bind_param( 3 , $dbname,    SQL_VARCHAR );
  }
  $sth->execute();


  if ( !$sth->rows() && lc($dbname) eq 'interpro' ) {
  # This is a minor hack that means that results still come back even
  # when a mistake was made and no interpro accessions were loaded into
  # the xref table.  This has happened in the past and had the result of
  # breaking domainview

    $sth->finish();
    $sth = $self->prepare(
      "SELECT   NULL,
                i.interpro_ac,
                i.id,
                NULL,
                NULL,
                'Interpro',
                NULL,
                NULL
        FROM    interpro i
        WHERE   i.interpro_ac = ?" );

    $sth->bind_param( 1, $name, SQL_VARCHAR );
    $sth->execute();
  }

  my %exDB;
  my @exDBlist;
  my $max_rows = 1000;

  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
    while ( my $arrayref = shift( @{$rowcache} ) ) {
      my ( $dbID,                $dbprimaryId,
           $displayid,           $version,
           $priority,
           $dbname,              $db_display_name,
           $release,             $synonym,
           $info_type,           $info_text,
           $type,                $secondary_db_name,
           $secondary_db_table,  $description
      ) = @$arrayref;

      if ( !defined $exDB{$dbID} ) {
	my $entrie = 
          Bio::EnsEMBL::DBEntry->new(
                           -adaptor             => $self,
                           -dbID                => $dbID,
                           -primary_id          => $dbprimaryId,
                           -display_id          => $displayid,
                           -version             => $version,
                           -release             => $release,
                           -dbname              => $dbname,
                           -priority            => $priority,
                           -db_display_name     => $db_display_name,
                           -info_type           => $info_type,
                           -info_text           => $info_text,
                           -type                => $type,
                           -secondary_db_name   => $secondary_db_name,
                           -secondary_db_table  => $secondary_db_table,
			   -description         => $description
          );
	$exDB{$dbID} = $entrie;
	push @exDBlist, $entrie;
      }
      if ($synonym) { $exDB{$dbID}->add_synonym($synonym) }

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

  $sth->finish();

  return \@exDBlist;
} ## end sub fetch_all_by_name



499
500
501
502
503
504
505
506
=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);
507
508
509
510
511
512
513
  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.
514
515
516
  Returntype : Bio::EnsEMBL::DBSQL::DBEntry
  Exceptions : thrown if arguments are incorrect
  Caller     : general, domainview
517
  Status     : Stable
518
519
520
521

=cut

sub fetch_by_db_accession {
522
  my ( $self, $dbname, $accession ) = @_;
523

524
  my $sth = $self->prepare(
525
526
527
528
529
530
531
532
533
534
535
536
537
    "SELECT xref.xref_id,
            xref.dbprimary_acc,
            xref.display_label,
            xref.version,
            exDB.priority,
            exDB.db_name,
            exDB.db_display_name,
            exDB.db_release,
            es.synonym,
            xref.info_type,
            xref.info_text,
            exDB.type,
            exDB.secondary_db_name,
538
            exDB.secondary_db_table,
Nathan Johnson's avatar
Nathan Johnson committed
539
            xref.description
540
541
542
    FROM    (xref, external_db exDB)
    LEFT JOIN external_synonym es ON
            es.xref_id = xref.xref_id
543
544
    WHERE  xref.dbprimary_acc = ?
    AND    exDB.db_name = ?
545
    AND    xref.external_db_id = exDB.external_db_id" );
546

547
548
  $sth->bind_param( 1, $accession, SQL_VARCHAR );
  $sth->bind_param( 2, $dbname,    SQL_VARCHAR );
549
  $sth->execute();
550

551
552
553
554
555
556
  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

557
    $sth->finish();
558
559
560
561
562
563
564
565
566
567
568
569
570
    $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 );
571
    $sth->execute();
572
573
574
575
  }

  my $exDB;

576
577
578
579
580
581
  my $max_rows = 1000;

  while ( my $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) ) {
    while ( my $arrayref = shift( @{$rowcache} ) ) {
      my ( $dbID,                $dbprimaryId,
           $displayid,           $version,
582
           $priority,
583
584
585
586
           $dbname,              $db_display_name,
           $release,             $synonym,
           $info_type,           $info_text,
           $type,                $secondary_db_name,
587
           $secondary_db_table,  $description
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
      ) = @$arrayref;

      if ( !defined($exDB) ) {
        $exDB =
          Bio::EnsEMBL::DBEntry->new(
                           -adaptor             => $self,
                           -dbID                => $dbID,
                           -primary_id          => $dbprimaryId,
                           -display_id          => $displayid,
                           -version             => $version,
                           -release             => $release,
                           -dbname              => $dbname,
                           -priority            => $priority,
                           -db_display_name     => $db_display_name,
                           -info_type           => $info_type,
                           -info_text           => $info_text,
                           -type                => $type,
                           -secondary_db_name   => $secondary_db_name,
606
607
                           -secondary_db_table  => $secondary_db_table,
			   -description         => $description
608
609
          );

610

611
612
613
614
615
616
      }

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

    } ## end while ( my $arrayref = shift...
  } ## end while ( my $rowcache = $sth...
617
618

  $sth->finish();
619
620

  return $exDB;
621
} ## end sub fetch_by_db_accession
622
623


Graham McVicker's avatar
Graham McVicker committed
624
625
=head2 store

626
  Arg [1]    : Bio::EnsEMBL::DBEntry $dbEntry
627
               The DBEntry (xref) to be stored
628
629
630
631
  Arg [2]    : Int $ensID
               The dbID of an EnsEMBL object to associate with this external
               database entry
  Arg [3]    : string $ensType ('Transcript', 'Translation', 'Gene')
632
633
               The type of EnsEMBL object that this external database entry is
               being associated with.
634
635
636
637
638
  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.
639
  Example    : $dbea->store($db_entry, $transcript_id, 'Transcript');
640
641
642
  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.
643
644
645
  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
646
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
647
648
649

=cut

650
sub store {
651
  my ( $self, $dbEntry, $ensID, $ensType, $ignore_release ) = @_;
652

653
  my $dbJustInserted;
654

655
656
657
658
659
660
  #
  # backwards compatibility check:
  # check if $ensID is an object; if so, use $obj->dbID
  #
  my $ensembl_id;

661
662
663
664
665
  if ( defined($ensID) ) {
    if ( $ensID =~ /^\d+$/ ) {
      $ensembl_id = $ensID;
    } elsif (    ref($ensID) eq 'Bio::EnsEMBL::Gene'
              or ref($ensID) eq 'Bio::EnsEMBL::Transcript'
666
667
668
669
              or ref($ensID) eq 'Bio::EnsEMBL::Translation' 
              or ref($ensID) eq 'Bio::EnsEMBL::OperonTranscript'
              or ref($ensID) eq 'Bio::EnsEMBL::Operon' 
              )
670
671
672
673
674
    {
      warning(   "You should pass DBEntryAdaptor->store() "
               . "a dbID rather than an ensembl object "
               . "to store the xref on" );

675
      if ( defined( $ensID->dbID() ) ) {
676
677
678
679
680
        $ensembl_id = $ensID->dbID();
      } else {
        throw( sprintf( "%s %s doesn't have a dbID, can't store xref",
                        $ensType, $ensID->display_id() ) );
      }
681
    } else {
682
      throw("Invalid dbID passed to DBEntryAdaptor->store()");
683
684
    }
  }
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
  
  
  
    # Ensure external_db contains a record of the intended xref source
    my $dbRef;
    $dbRef = $self->_check_external_db($dbEntry,$ignore_release);

    # Attempt to insert DBEntry
    my $xref_id = $self->_store_or_fetch_xref($dbEntry,$dbRef);
    $dbEntry->dbID($xref_id); #keeps DBEntry in sync with database
    ### Attempt to create an object->xref mapping
    if ($ensembl_id) {$self->_store_object_xref_mapping($ensembl_id,$dbEntry,$ensType)};
    
    return $xref_id;
}
    
sub _store_object_xref_mapping {
    my $self = shift;
    my $ensembl_id = shift;
    my $dbEntry = shift;
    my $ensembl_type = shift;
    
    if (not defined ($ensembl_type)) { warning("No Ensembl data type provided for new xref");}
    
    my $analysis_id;
    if ( $dbEntry->analysis() ) {
        $analysis_id = $self->db()->get_AnalysisAdaptor->store( $dbEntry->analysis() );
    } else {
        $analysis_id = 0; ## This used to be undef, but uniqueness in mysql requires a value
714
    }
715
716
717
718
719
720
721
722
723
724
725
726
727
728
    
    my $sth = $self->prepare(qq(
        INSERT IGNORE INTO object_xref
          SET   xref_id = ?,
                ensembl_object_type = ?,
                ensembl_id = ?,
                linkage_annotation = ?,
                analysis_id = ? ) 
        );
    $sth->bind_param( 1, $dbEntry->dbID(),              SQL_INTEGER );
    $sth->bind_param( 2, $ensembl_type,                 SQL_VARCHAR );
    $sth->bind_param( 3, $ensembl_id,                   SQL_INTEGER );
    $sth->bind_param( 4, $dbEntry->linkage_annotation(),SQL_VARCHAR );
    $sth->bind_param( 5, $analysis_id,                  SQL_INTEGER );
729
730
    $sth->execute();
    $sth->finish();
731
732
733
734
735
736
737
    my $object_xref_id = $self->last_insert_id();
    
    $dbEntry->adaptor($self); # hand Adaptor to dbEntry for future use with OntologyXrefs
    
    if ($object_xref_id) {
        #no existing object_xref, therefore 
        if ( $dbEntry->isa('Bio::EnsEMBL::IdentityXref') ) {
738
        $sth = $self->prepare( "
739
740
             INSERT ignore INTO identity_xref
             SET object_xref_id = ?,
741
742
743
744
745
746
             xref_identity = ?,
             ensembl_identity = ?,
             xref_start = ?,
             xref_end   = ?,
             ensembl_start = ?,
             ensembl_end = ?,
747
748
             cigar_line = ?,
             score = ?,
749
             evalue = ?" );
750
751
752
753
754
755
756
757
758
759
        $sth->bind_param( 1, $object_xref_id,            SQL_INTEGER );
        $sth->bind_param( 2, $dbEntry->xref_identity,    SQL_INTEGER );
        $sth->bind_param( 3, $dbEntry->ensembl_identity, SQL_INTEGER );
        $sth->bind_param( 4, $dbEntry->xref_start,       SQL_INTEGER );
        $sth->bind_param( 5, $dbEntry->xref_end,         SQL_INTEGER );
        $sth->bind_param( 6, $dbEntry->ensembl_start,    SQL_INTEGER );
        $sth->bind_param( 7, $dbEntry->ensembl_end,      SQL_INTEGER );
        $sth->bind_param( 8,  $dbEntry->cigar_line,  SQL_LONGVARCHAR );
        $sth->bind_param( 9,  $dbEntry->score,            SQL_DOUBLE );
        $sth->bind_param( 10, $dbEntry->evalue,           SQL_DOUBLE );
760
        $sth->execute();
761
      } elsif ( $dbEntry->isa('Bio::EnsEMBL::OntologyXref') ) {
762
        $sth = $self->prepare( "
763
             INSERT ignore INTO ontology_xref
764
                SET object_xref_id = ?,
765
                    source_xref_id = ?,
766
                    linkage_type = ? " );
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
        foreach my $info ( @{ $dbEntry->get_all_linkage_info() } ) {
            my ( $linkage_type, $sourceXref ) = @{$info};
            my $sourceXid = undef;
            if ($sourceXref) {
              $sourceXref->is_stored( $self->dbc ) || $self->store($sourceXref);
              $sourceXid = $sourceXref->dbID;
            }
            $sth->bind_param( 1, $object_xref_id, SQL_INTEGER );
            $sth->bind_param( 2, $sourceXid, SQL_INTEGER );
            $sth->bind_param( 3, $linkage_type,  SQL_VARCHAR );
            $sth->execute();
        } #end foreach
      } #end elsif
    } # end if ($object_xref_id)
    return $object_xref_id;
}

=head2 _check_external_db 
785

786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
  Arg [1]    : DBEntry object
  Arg [2]    : Ignore version flag
  Description: Looks for a record of the given external database
  Exceptions : Throws on missing external database entry
  Returntype : Int 

=cut

sub _check_external_db {
    my ($self,$db_entry,$ignore) = @_;
    my ($sql,@bound_params,$sql_helper,$db_name,$db_release);
    
    $db_name = $db_entry->dbname();
    $db_release = $db_entry->release();
    $sql_helper = $self->dbc->sql_helper;
    
    $sql = 'SELECT external_db_id FROM external_db WHERE db_name = ?';
    push @bound_params,$db_name;
    unless ($ignore) {
        if ($db_release) {
            $sql .= ' AND db_release = ?';
            push @bound_params,$db_release;
        } else {
            $sql .= ' AND db_release is NULL';
        }
    }
    
    my ($db_id) = @{ $sql_helper->execute_simple(-SQL => $sql, -PARAMS => \@bound_params) };
    
    if ($db_id) {
      return $db_id;
    }
    else {
      throw( sprintf( "external_db [%s] release [%s] does not exist",
                     $db_name, $db_release)
      );
    }
823
824
}

825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
=head2 _store_or_fetch_xref 

    Arg [1]    : DBEntry object
    Arg [2]    : Database accession for external database
    Description: Thread-safe method for adding xrefs, or otherwise returning
                 an xref ID for the inserted or retrieved xref. Also inserts
                 synonyms for that xref when entire new 
    Returns    : Int - the DB ID of the xref after insertion 
=cut
sub _store_or_fetch_xref {
    my $self = shift;
    my $dbEntry = shift;
    my $dbRef = shift;
    my $xref_id;
    
    my $sth = $self->prepare( "
       INSERT IGNORE INTO xref
       SET dbprimary_acc = ?,
           display_label = ?,
           version = ?,
           description = ?,
           external_db_id = ?,
           info_type = ?,
           info_text = ?");
    $sth->bind_param(1, $dbEntry->primary_id,SQL_VARCHAR);
    $sth->bind_param(2, $dbEntry->display_id,SQL_VARCHAR);
    $sth->bind_param(3, $dbEntry->version,SQL_VARCHAR);
    $sth->bind_param(4, $dbEntry->description,SQL_VARCHAR);
    $sth->bind_param(5, $dbRef,SQL_INTEGER);
    $sth->bind_param(6, ($dbEntry->info_type || 'NONE'), SQL_VARCHAR);
    $sth->bind_param(7, ($dbEntry->info_text || ''), SQL_VARCHAR);

    $sth->execute();
    $xref_id = $self->last_insert_id('xref_id',undef,'xref');
    $sth->finish();
    
    if ($xref_id) { #insert was successful, store supplementary synonyms
        # thread safety no longer an issue.
        my $synonym_check_sth = $self->prepare(
                  "SELECT xref_id, synonym
                   FROM external_synonym
                   WHERE xref_id = ?
                   AND synonym = ?");
    
        my $synonym_store_sth = $self->prepare(
            "INSERT ignore INTO external_synonym
             SET xref_id = ?, synonym = ?");
    
        my $synonyms = $dbEntry->get_all_synonyms();
        foreach my $syn ( @$synonyms ) {
            $synonym_check_sth->bind_param(1,$xref_id,SQL_INTEGER);
            $synonym_check_sth->bind_param(2,$syn,SQL_VARCHAR);
            $synonym_check_sth->execute();
            my ($dbSyn) = $synonym_check_sth->fetchrow_array();
            $synonym_store_sth->bind_param(1,$xref_id,SQL_INTEGER);
            $synonym_store_sth->bind_param(2,$syn,SQL_VARCHAR);
            $synonym_store_sth->execute() if(!$dbSyn);
        }
        $synonym_check_sth->finish();
        $synonym_store_sth->finish();
        
    } else { # xref_id already exists, retrieve it
        my $sql = 'SELECT xref_id FROM xref 
            WHERE 
                dbprimary_acc = ?
            AND display_label = ?
            AND version = ?
            AND external_db_id = ?
            AND info_type = ?
            AND info_text = ?
            AND description';
        if ($dbEntry->description) {$sql .= ' = ?'}
        else {$sql .= ' is NULL'}
        
        $sth = $self->prepare( $sql );
        $sth->bind_param(1, $dbEntry->primary_id,SQL_VARCHAR);
        $sth->bind_param(2, $dbEntry->display_id,SQL_VARCHAR);
        $sth->bind_param(3, $dbEntry->version,SQL_VARCHAR);
        $sth->bind_param(4, $dbRef,SQL_INTEGER);
        $sth->bind_param(5, ($dbEntry->info_type || 'NONE'), SQL_VARCHAR);
        $sth->bind_param(6, ($dbEntry->info_text || ''), SQL_VARCHAR);
        if ($dbEntry->description) {$sth->bind_param(7, $dbEntry->description,SQL_VARCHAR);}
        $sth->execute();
        ($xref_id) = $sth->fetchrow_array();
        $sth->finish;
    }
    
    return $xref_id;
}
914

915
916
917
918
919
=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
920
               otherwise returns undef.  Exists is defined as an entry with
921
922
923
924
               the same external_db and display_id
  Returntype : int
  Exceptions : thrown on incorrect args
  Caller     : GeneAdaptor::store, TranscriptAdaptor::store
925
  Status     : Stable
926
927
928
929

=cut

sub exists {
930
  my ($self, $dbe) = @_ ;
931
932

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

936
  my $sth = $self->prepare('SELECT x.xref_id
937
938
                            FROM   xref x, external_db xdb
                            WHERE  x.external_db_id = xdb.external_db_id
939
                            AND    x.display_label = ?
940
941
                            AND    xdb.db_name = ?
                            AND    x.dbprimary_acc = ?');
942

943
  $sth->bind_param(1,$dbe->display_id,SQL_VARCHAR);
Glenn Proctor's avatar
Glenn Proctor committed
944
  $sth->bind_param(2,$dbe->dbname,SQL_VARCHAR);
945
  $sth->bind_param(3,$dbe->primary_id,SQL_VARCHAR);
946
  $sth->execute();
947
948
949
950
951
952
953
954
955

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

  $sth->finish;

  return $dbID;
}


956
=head2 fetch_all_by_Gene
Graham McVicker's avatar
Graham McVicker committed
957

958
  Arg [1]    : Bio::EnsEMBL::Gene $gene
959
               (The gene to retrieve DBEntries for)
960
961
962
  Arg [2]    : optional external database name. SQL wildcards are accepted
  Arg [3]    : optional external_db type. SQL wildcards are accepted
  Example    : @db_entries = @{$db_entry_adaptor->fetch_all_by_Gene($gene)};
963
964
965
  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
966
               all of the gene, transcript, and translation xrefs associated
967
               with this gene.
968
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
969
               there is mapping data, or OntologyXref if there is linkage data.
970
  Exceptions : thows if gene object not passed
Graham McVicker's avatar
Graham McVicker committed
971
  Caller     : Bio::EnsEMBL::Gene
972
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
973
974
975

=cut

976
sub fetch_all_by_Gene {
977
  my ( $self, $gene, $ex_db_reg, $exdb_type ) = @_;
978

979
980
981
  if(!ref($gene) || !$gene->isa('Bio::EnsEMBL::Gene')) {
    throw("Bio::EnsEMBL::Gene argument expected.");
  }
982

983
  return $self->_fetch_by_object_type($gene->dbID(), 'Gene', $ex_db_reg, $exdb_type);
Arne Stabenau's avatar
Arne Stabenau committed
984
985
}

986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
=head2 fetch_all_by_Operon

  Arg [1]    : Bio::EnsEMBL::Operon $operon
               (The operon to retrieve DBEntries for)
  Arg [2]    : optional external database name. SQL wildcards are accepted
  Arg [3]    : optional external_db type. SQL wildcards are accepted
  Example    : @db_entries = @{$db_entry_adaptor->fetch_all_by_Operon($operon)};
  Description: This returns a list of DBEntries associated with this operon.
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
               there is mapping data, or OntologyXref if there is linkage data.
  Exceptions : thows if operon object not passed
  Caller     : general

=cut

1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
sub fetch_all_by_Operon {
  my ( $self, $gene, $ex_db_reg, $exdb_type ) = @_;

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

  return $self->_fetch_by_object_type($gene->dbID(), 'Operon', $ex_db_reg, $exdb_type);
}

Graham McVicker's avatar
Graham McVicker committed
1011

1012
=head2 fetch_all_by_Transcript
Graham McVicker's avatar
Graham McVicker committed
1013

Graham McVicker's avatar
Graham McVicker committed
1014
  Arg [1]    : Bio::EnsEMBL::Transcript
1015
1016
1017
  Arg [2]    : optional external database name. SQL wildcards are accepted
  Arg [3]    : optional external_db type. SQL wildcards are accepted
  Example    : @db_entries = @{$db_entry_adaptor->fetch_all_by_Transcript($trans)};
1018
  Description: This returns a list of DBEntries associated with this
1019
1020
1021
               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
1022
               associated with this gene.
1023
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
1024
               there is mapping data, or OntologyXref if there is linkage data.
1025
  Exceptions : throes if transcript argument not passed
1026
  Caller     : Bio::EnsEMBL::Transcript
1027
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
1028
1029
1030

=cut

1031
sub fetch_all_by_Transcript {
1032
  my ( $self, $trans, $ex_db_reg, $exdb_type ) = @_;
1033

1034
1035
1036
1037
  if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Transcript')) {
    throw("Bio::EnsEMBL::Transcript argument expected.");
  }

1038
  return $self->_fetch_by_object_type( $trans->dbID(), 'Transcript', $ex_db_reg, $exdb_type);
1039
1040
}

Graham McVicker's avatar
Graham McVicker committed
1041

1042
=head2 fetch_all_by_Translation
Graham McVicker's avatar
Graham McVicker committed
1043

Graham McVicker's avatar
Graham McVicker committed
1044
  Arg [1]    : Bio::EnsEMBL::Translation $trans
1045
               (The translation to fetch database entries for)
1046
1047
  Arg [2]    : optional external database name. SQL wildcards are accepted
  Arg [3]    : optional externaldb type. SQL wildcards are accepted
1048
  Example    : @db_entries = @{$db_entry_adptr->fetch_all_by_Translation($trans)};
Graham McVicker's avatar
Graham McVicker committed
1049
  Description: Retrieves external database entries for an EnsEMBL translation
1050
  Returntype : listref of Bio::EnsEMBL::DBEntries; may be of type IdentityXref if
1051
               there is mapping data, or OntologyXref if there is linkage data.
1052
  Exceptions : throws if translation object not passed
Graham McVicker's avatar
Graham McVicker committed
1053
  Caller     : general
1054
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
1055
1056
1057

=cut

1058
sub fetch_all_by_Translation {
1059
  my ( $self, $trans, $ex_db_reg, $exdb_type ) = @_;
1060

1061
1062
1063
  if(!ref($trans) || !$trans->isa('Bio::EnsEMBL::Translation')) {
    throw('Bio::EnsEMBL::Translation argument expected.');
  }
1064
  if( ! $trans->dbID ){
1065
1066
1067
    warning( "Cannot fetch_all_by_Translation without a dbID" );
    return [];
  }
Glenn Proctor's avatar
Glenn Proctor committed
1068

1069
  return $self->_fetch_by_object_type( $trans->dbID(), 'Translation', $ex_db_reg, $exdb_type );
1070
1071
1072
}


1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094

=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
1095
  Status     : Stable
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132

=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
1133
  #No need to compare linkage_annotation here
1134
1135
1136
1137
1138
1139
  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 = ?");
1140
1141
1142
1143
  $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();
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154

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

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

  # delete from the tables which contain additional linkage information

1155
  $sth = $self->prepare("DELETE FROM ontology_xref WHERE object_xref_id = ?");
1156
1157
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
1158
1159
1160
  $sth->finish();

  $sth = $self->prepare("DELETE FROM identity_xref WHERE object_xref_id = ?");
1161
1162
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
1163
1164
1165
1166
  $sth->finish();

  # delete the actual linkage itself
  $sth = $self->prepare("DELETE FROM object_xref WHERE object_xref_id = ?");
1167
1168
  $sth->bind_param(1,$ox_id,SQL_INTEGER);
  $sth->execute();
1169
1170
1171
1172
1173
1174
1175
  $sth->finish();

  return;
}


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

1177
  Arg [1]    : string $ensID
1178
  Arg [2]    : string $ensType (object type to be returned)
1179
  Arg [3]    : optional $exdbname (external database name)
1180
1181
1182
1183
1184
               (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)
1185
1186
  Example    : $self->_fetch_by_object_type( $translation_id, 'Translation' )
  Description: Fetches DBEntry by Object type
1187
1188
1189
               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.
1190
1191


1192
  Returntype : arrayref of DBEntry objects; may be of type IdentityXref if
1193
               there is mapping data, or OntologyXref if there is linkage data.
1194
1195
  Exceptions : none
  Caller     : fetch_all_by_Gene
1196
1197
               fetch_all_by_Translation
               fetch_all_by_Transcript
1198
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
1199
1200
1201

=cut

Graham McVicker's avatar
Graham McVicker committed
1202
sub _fetch_by_object_type {
1203
  my ( $self, $ensID, $ensType, $exdbname, $exdb_type ) = @_;
1204

1205
  my @out;
1206

1207
  if ( !defined($ensID) ) {
1208
    throw("Can't fetch_by_EnsObject_type without an object");
Michele Clamp's avatar
Michele Clamp committed
1209
  }
1210
1211

  if ( !defined($ensType) ) {
1212
    throw("Can't fetch_by_EnsObject_type without a type");
Michele Clamp's avatar
Michele Clamp committed
1213
  }
1214
1215

  #  my $sth = $self->prepare("
1216
  my $sql = (<<SSQL);
Graham McVicker's avatar
Graham McVicker committed
1217
    SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label, xref.version,
1218
           exDB.priority,
Glenn Proctor's avatar
Glenn Proctor committed
1219
           exDB.db_name, exDB.db_release, exDB.status, exDB.db_display_name,
1220
           exDB.secondary_db_name, exDB.secondary_db_table,
1221
           oxr.object_xref_id,
1222
           es.synonym,
1223
1224
           idt.xref_identity, idt.ensembl_identity, idt.xref_start,
           idt.xref_end, idt.ensembl_start, idt.ensembl_end,
1225
           idt.cigar_line, idt.score, idt.evalue, oxr.analysis_id,
1226
           gx.linkage_type,
1227
           xref.info_type, xref.info_text, exDB.type, gx.source_xref_id,
Nathan Johnson's avatar
Nathan Johnson committed
1228
           oxr.linkage_annotation, xref.description
1229
    FROM   (xref xref, external_db exDB, object_xref oxr)
1230
    LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
Graham McVicker's avatar
Graham McVicker committed
1231
    LEFT JOIN identity_xref idt on idt.object_xref_id = oxr.object_xref_id
1232
    LEFT JOIN ontology_xref gx on gx.object_xref_id = oxr.object_xref_id
Graham McVicker's avatar
Graham McVicker committed
1233
    WHERE  xref.xref_id = oxr.xref_id
1234
      AND  xref.external_db_id = exDB.external_db_id
1235
1236
      AND  oxr.ensembl_id = ?
      AND  oxr.ensembl_object_type = ?
1237
SSQL
1238
1239
1240

  if ( defined($exdbname) ) {
    if ( index( $exdbname, '%' ) != -1 ) {
1241
      $sql .= " AND exDB.db_name LIKE "
1242
1243
        . $self->dbc()->db_handle()->quote( $exdbname, SQL_VARCHAR );
    } else {
1244
      $sql .= " AND exDB.db_name = "
1245
1246
1247
1248
1249
1250
        . $self->dbc()->db_handle()->quote( $exdbname, SQL_VARCHAR );
    }
  }

  if ( defined($exdb_type) ) {
    if ( index( $exdb_type, '%' ) != -1 ) {
1251
      $sql .= " AND exDB.type LIKE "
1252
1253
        . $self->dbc()->db_handle()->quote( $exdb_type, SQL_VARCHAR );
    } else {
1254
      $sql .= " AND exDB.type = "
1255
1256
1257
1258
        . $self->dbc()->db_handle()->quote( $exdb_type, SQL_VARCHAR );
    }
  }

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

1261
1262
  $sth->bind_param( 1, $ensID,   SQL_INTEGER );
  $sth->bind_param( 2, $ensType, SQL_VARCHAR );
1263
  $sth->execute();
1264
1265
1266
1267
1268
1269
1270
1271
1272

  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,
1273
           $priority,
1274
1275
1276
1277
           $dbname,                 $release,
           $exDB_status,            $exDB_db_display_name,
           $exDB_secondary_db_name, $exDB_secondary_db_table,
           $objid,                  $synonym,
1278
1279
1280
           $xrefid,                 $ensemblid,
           $xref_start,             $xref_end,
           $ensembl_start,          $ensembl_end,
1281
1282
1283
1284
           $cigar_line,             $score,
           $evalue,                 $analysis_id,
           $linkage_type,           $info_type,
           $info_text,              $type,
1285
           $source_xref_id,         $link_annotation,
1286
	   $description
1287
1288
1289
1290
1291
      ) = @$arrRef;

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

1292
1293
1294
1295
1296
1297
1298

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

1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
      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,
1310
                       'dbname'             => $dbname,
1311
                       'description'        => $description,
1312
                       'linkage_annotation' => $link_annotation,
1313
1314
1315
                       'analysis'           => $analysis,
		       'ensembl_object_type' => $ensType,
		       'ensembl_id'          => $ensID );
1316
1317
1318
1319
1320

      # 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.
1321
      my $source_xref;
Andreas Kusalananda Kähäri's avatar