|
0
|
1 =head1 LICENSE
|
|
|
2
|
|
|
3 Copyright (c) 1999-2012 The European Bioinformatics Institute and
|
|
|
4 Genome Research Limited. All rights reserved.
|
|
|
5
|
|
|
6 This software is distributed under a modified Apache license.
|
|
|
7 For license details, please see
|
|
|
8
|
|
|
9 http://www.ensembl.org/info/about/code_licence.html
|
|
|
10
|
|
|
11 =head1 CONTACT
|
|
|
12
|
|
|
13 Please email comments or questions to the public Ensembl
|
|
|
14 developers list at <dev@ensembl.org>.
|
|
|
15
|
|
|
16 Questions may also be sent to the Ensembl help desk at
|
|
|
17 <helpdesk@ensembl.org>.
|
|
|
18
|
|
|
19 =cut
|
|
|
20
|
|
|
21 =head1 NAME
|
|
|
22
|
|
|
23 Bio::EnsEMBL::DBSQL::BaseAdaptor - Base Adaptor for DBSQL adaptors
|
|
|
24
|
|
|
25 =head1 SYNOPSIS
|
|
|
26
|
|
|
27 # base adaptor provides
|
|
|
28
|
|
|
29 # SQL prepare function
|
|
|
30 $adaptor->prepare("sql statement");
|
|
|
31
|
|
|
32 # get of root DBAdaptor object
|
|
|
33 $adaptor->db();
|
|
|
34
|
|
|
35 # constructor, ok for inheritence
|
|
|
36 $adaptor = Bio::EnsEMBL::DBSQL::SubClassOfBaseAdaptor->new($dbobj)
|
|
|
37
|
|
|
38 =head1 DESCRIPTION
|
|
|
39
|
|
|
40 This is a true base class for Adaptors in the Ensembl DBSQL
|
|
|
41 system. Original idea from Arne
|
|
|
42
|
|
|
43 Adaptors are expected to have the following functions
|
|
|
44
|
|
|
45 $obj = $adaptor->fetch_by_dbID($internal_id);
|
|
|
46
|
|
|
47 which builds the object from the primary key of the object. This
|
|
|
48 function is crucial because it allows adaptors to collaborate relatively
|
|
|
49 independently of each other - in other words, we can change the schema
|
|
|
50 under one adaptor without too many knock on changes through the other
|
|
|
51 adaptors.
|
|
|
52
|
|
|
53 Most adaptors will also have
|
|
|
54
|
|
|
55 $dbid = $adaptor->store($obj);
|
|
|
56
|
|
|
57 which stores the object. Currently the storing of an object also causes
|
|
|
58 the objects to set
|
|
|
59
|
|
|
60 $obj->dbID();
|
|
|
61
|
|
|
62 correctly and attach the adaptor.
|
|
|
63
|
|
|
64 Other fetch functions go by the convention of
|
|
|
65
|
|
|
66 @object_array = @{ $adaptor->fetch_all_by_XXXX($arguments_for_XXXX) };
|
|
|
67
|
|
|
68 sometimes it returns an array ref denoted by the 'all' in the name of
|
|
|
69 the method, sometimes an individual object. For example
|
|
|
70
|
|
|
71 $gene = $gene_adaptor->fetch_by_stable_id($stable_id);
|
|
|
72
|
|
|
73 or
|
|
|
74
|
|
|
75 @fp = @{ $simple_feature_adaptor->fetch_all_by_Slice($slice) };
|
|
|
76
|
|
|
77 Occassionally adaptors need to provide access to lists of ids. In this
|
|
|
78 case the convention is to go list_XXXX, such as
|
|
|
79
|
|
|
80 @gene_ids = @{ $gene_adaptor->list_geneIds() };
|
|
|
81
|
|
|
82 (note: this method is poorly named)
|
|
|
83
|
|
|
84 =head1 METHODS
|
|
|
85
|
|
|
86 =cut
|
|
|
87
|
|
|
88 package Bio::EnsEMBL::DBSQL::BaseAdaptor;
|
|
|
89 require Exporter;
|
|
|
90 use vars qw(@ISA @EXPORT);
|
|
|
91 use strict;
|
|
|
92
|
|
|
93 use Bio::EnsEMBL::Utils::Exception qw(throw);
|
|
|
94 use Bio::EnsEMBL::Utils::Scalar qw(assert_ref);
|
|
|
95 use DBI qw(:sql_types);
|
|
|
96 use Data::Dumper;
|
|
|
97
|
|
|
98 @ISA = qw(Exporter);
|
|
|
99 @EXPORT = (@{$DBI::EXPORT_TAGS{'sql_types'}});
|
|
|
100
|
|
|
101 =head2 new
|
|
|
102
|
|
|
103 Arg [1] : Bio::EnsEMBL::DBSQL::DBConnection $dbobj
|
|
|
104 Example : $adaptor = new AdaptorInheritedFromBaseAdaptor($dbobj);
|
|
|
105 Description: Creates a new BaseAdaptor object. The intent is that this
|
|
|
106 constructor would be called by an inherited superclass either
|
|
|
107 automatically or through $self->SUPER::new in an overridden
|
|
|
108 new method.
|
|
|
109 Returntype : Bio::EnsEMBL::DBSQL::BaseAdaptor
|
|
|
110 Exceptions : none
|
|
|
111 Caller : Bio::EnsEMBL::DBSQL::DBConnection
|
|
|
112 Status : Stable
|
|
|
113
|
|
|
114 =cut
|
|
|
115
|
|
|
116 sub new {
|
|
|
117 my ( $class, $dbobj ) = @_;
|
|
|
118
|
|
|
119 my $self = bless {}, $class;
|
|
|
120
|
|
|
121 if ( !defined $dbobj || !ref $dbobj ) {
|
|
|
122 throw("Don't have a db [$dbobj] for new adaptor");
|
|
|
123 }
|
|
|
124
|
|
|
125 if ( $dbobj->isa('Bio::EnsEMBL::DBSQL::DBAdaptor') ) {
|
|
|
126 $self->db($dbobj);
|
|
|
127 $self->dbc( $dbobj->dbc );
|
|
|
128 $self->species_id( $dbobj->species_id() );
|
|
|
129 $self->is_multispecies( $dbobj->is_multispecies() );
|
|
|
130 } elsif ( ref($dbobj) =~ /DBAdaptor$/ ) {
|
|
|
131 $self->db($dbobj);
|
|
|
132 $self->dbc( $dbobj->dbc );
|
|
|
133 } elsif ( ref($dbobj) =~ /DBConnection$/ ) {
|
|
|
134 $self->dbc($dbobj);
|
|
|
135 } else {
|
|
|
136 throw("Don't have a DBAdaptor [$dbobj] for new adaptor");
|
|
|
137 }
|
|
|
138
|
|
|
139 return $self;
|
|
|
140 }
|
|
|
141
|
|
|
142
|
|
|
143 =head2 prepare
|
|
|
144
|
|
|
145 Arg [1] : string $string
|
|
|
146 a SQL query to be prepared by this adaptors database
|
|
|
147 Example : $sth = $adaptor->prepare("select yadda from blabla")
|
|
|
148 Description: provides a DBI statement handle from the adaptor. A convenience
|
|
|
149 function so you dont have to write $adaptor->db->prepare all the
|
|
|
150 time
|
|
|
151 Returntype : DBI::StatementHandle
|
|
|
152 Exceptions : none
|
|
|
153 Caller : Adaptors inherited from BaseAdaptor
|
|
|
154 Status : Stable
|
|
|
155
|
|
|
156 =cut
|
|
|
157
|
|
|
158 sub prepare {
|
|
|
159 my ( $self, $string ) = @_;
|
|
|
160
|
|
|
161 # Uncomment next line to cancel caching on the SQL side.
|
|
|
162 # Needed for timing comparisons etc.
|
|
|
163 #$string =~ s/SELECT/SELECT SQL_NO_CACHE/i;
|
|
|
164
|
|
|
165 return $self->dbc->prepare($string);
|
|
|
166 }
|
|
|
167
|
|
|
168
|
|
|
169 =head2 db
|
|
|
170
|
|
|
171 Arg [1] : (optional) Bio::EnsEMBL::DBSQL::DBAdaptor $obj
|
|
|
172 the database this adaptor is using.
|
|
|
173 Example : $db = $adaptor->db();
|
|
|
174 Description: Getter/Setter for the DatabaseConnection that this adaptor is
|
|
|
175 using.
|
|
|
176 Returntype : Bio::EnsEMBL::DBSQL::DBAdaptor
|
|
|
177 Exceptions : none
|
|
|
178 Caller : Adaptors inherited from BaseAdaptor
|
|
|
179 Status : Stable
|
|
|
180
|
|
|
181 =cut
|
|
|
182
|
|
|
183 sub db {
|
|
|
184 my ( $self, $value ) = @_;
|
|
|
185
|
|
|
186 if ( defined($value) ) {
|
|
|
187 $self->{'db'} = $value;
|
|
|
188 }
|
|
|
189
|
|
|
190 return $self->{'db'};
|
|
|
191 }
|
|
|
192
|
|
|
193 =head2 dbc
|
|
|
194
|
|
|
195 Arg [1] : (optional) Bio::EnsEMBL::DBSQL::DBConnection $obj
|
|
|
196 the database this adaptor is using.
|
|
|
197 Example : $db = $adaptor->db();
|
|
|
198 Description: Getter/Setter for the DatabaseConnection that this adaptor is
|
|
|
199 using.
|
|
|
200 Returntype : Bio::EnsEMBL::DBSQL::DBConnection
|
|
|
201 Exceptions : none
|
|
|
202 Caller : Adaptors inherited from BaseAdaptor
|
|
|
203 Status : Stable
|
|
|
204
|
|
|
205 =cut
|
|
|
206
|
|
|
207 sub dbc {
|
|
|
208 my ( $self, $value ) = @_;
|
|
|
209
|
|
|
210 if ( defined($value) ) {
|
|
|
211 $self->{'dbc'} = $value;
|
|
|
212 }
|
|
|
213
|
|
|
214 return $self->{'dbc'};
|
|
|
215 }
|
|
|
216
|
|
|
217 =head2 is_multispecies
|
|
|
218
|
|
|
219 Arg [1] : (optional) boolean $arg
|
|
|
220 Example : if ($adaptor->is_multispecies()) { }
|
|
|
221 Description: Getter/Setter for the is_multispecies boolean of
|
|
|
222 to use for this adaptor.
|
|
|
223 Returntype : boolean
|
|
|
224 Exceptions : none
|
|
|
225 Caller : general
|
|
|
226 Status : Stable
|
|
|
227
|
|
|
228 =cut
|
|
|
229
|
|
|
230 sub is_multispecies {
|
|
|
231 my ( $self, $arg ) = @_;
|
|
|
232
|
|
|
233 if ( defined($arg) ) {
|
|
|
234 $self->{_is_multispecies} = $arg;
|
|
|
235 }
|
|
|
236
|
|
|
237 return $self->{_is_multispecies};
|
|
|
238 }
|
|
|
239
|
|
|
240 =head2 species_id
|
|
|
241
|
|
|
242 Arg [1] : (optional) int $species_id
|
|
|
243 The internal ID of the species in a multi-species database.
|
|
|
244 Example : $db = $adaptor->db();
|
|
|
245 Description: Getter/Setter for the internal ID of the species in a
|
|
|
246 multi-species database. The default species ID is 1.
|
|
|
247 Returntype : Integer
|
|
|
248 Exceptions : none
|
|
|
249 Caller : Adaptors inherited from BaseAdaptor
|
|
|
250 Status : Stable
|
|
|
251
|
|
|
252 =cut
|
|
|
253
|
|
|
254 sub species_id {
|
|
|
255 my ( $self, $value ) = @_;
|
|
|
256
|
|
|
257 if ( defined($value) ) {
|
|
|
258 $self->{'species_id'} = $value;
|
|
|
259 }
|
|
|
260
|
|
|
261 return $self->{'species_id'} || 1;
|
|
|
262 }
|
|
|
263
|
|
|
264
|
|
|
265 # list primary keys for a particular table
|
|
|
266 # args are table name and primary key field
|
|
|
267 # if primary key field is not supplied, tablename_id is assumed
|
|
|
268 # returns listref of IDs
|
|
|
269 sub _list_dbIDs {
|
|
|
270 my ( $self, $table, $pk, $ordered ) = @_;
|
|
|
271
|
|
|
272 if ( !defined($pk) ) { $pk = $table . "_id" }
|
|
|
273
|
|
|
274 my $sql = sprintf( "SELECT %s FROM %s", $pk, $table );
|
|
|
275
|
|
|
276 my $join_with_cs = 0;
|
|
|
277 if ( $self->is_multispecies()
|
|
|
278 && $self->isa('Bio::EnsEMBL::DBSQL::BaseFeatureAdaptor')
|
|
|
279 && !$self->isa('Bio::EnsEMBL::DBSQL::UnmappedObjectAdaptor') )
|
|
|
280 {
|
|
|
281
|
|
|
282 $sql .= q(
|
|
|
283 JOIN seq_region USING (seq_region_id)
|
|
|
284 JOIN coord_system cs USING (coord_system_id)
|
|
|
285 WHERE cs.species_id = ?
|
|
|
286 );
|
|
|
287
|
|
|
288 $join_with_cs = 1;
|
|
|
289 }
|
|
|
290
|
|
|
291 if ( defined($ordered) && $ordered ) {
|
|
|
292 $sql .= " ORDER BY seq_region_id, seq_region_start";
|
|
|
293 }
|
|
|
294
|
|
|
295 my $sth = $self->prepare($sql);
|
|
|
296
|
|
|
297 if ($join_with_cs) {
|
|
|
298 $sth->bind_param( 1, $self->species_id(), SQL_INTEGER );
|
|
|
299 }
|
|
|
300
|
|
|
301 eval { $sth->execute() };
|
|
|
302 if ($@) {
|
|
|
303 throw("Detected an error whilst executing SQL '${sql}': $@");
|
|
|
304 }
|
|
|
305
|
|
|
306 my $id;
|
|
|
307 $sth->bind_col( 1, \$id );
|
|
|
308
|
|
|
309 my @out;
|
|
|
310 while ( $sth->fetch() ) {
|
|
|
311 push( @out, $id );
|
|
|
312 }
|
|
|
313
|
|
|
314 return \@out;
|
|
|
315 } ## end sub _list_dbIDs
|
|
|
316
|
|
|
317
|
|
|
318 # _straight_join
|
|
|
319
|
|
|
320 # Arg [1] : (optional) boolean $new_val
|
|
|
321 # Example : $self->_straight_join(1);
|
|
|
322 # $self->generic_fetch($constraint);
|
|
|
323 # $self->_straight_join(0);
|
|
|
324 # Description: PROTECTED Getter/Setter that turns on/off the use of
|
|
|
325 # a straight join in queries.
|
|
|
326 # Returntype : boolean
|
|
|
327 # Exceptions : none
|
|
|
328 # Caller : general
|
|
|
329
|
|
|
330 sub _straight_join {
|
|
|
331 my $self = shift;
|
|
|
332 if(@_) {
|
|
|
333 $self->{'_straight_join'} = shift;
|
|
|
334 }
|
|
|
335
|
|
|
336 return $self->{'_straight_join'};
|
|
|
337 }
|
|
|
338
|
|
|
339
|
|
|
340 =head2 bind_param_generic_fetch
|
|
|
341
|
|
|
342 Arg [1] : (optional) scalar $param
|
|
|
343 This is the parameter to bind
|
|
|
344 Arg [2] : (optional) int $sql_type
|
|
|
345 Type of the parameter (from DBI (:sql_types))
|
|
|
346 Example : $adaptor->bind_param_generic_fetch($stable_id,SQL_VARCHAR);
|
|
|
347 $adaptor->generic_fetch();
|
|
|
348 Description: When using parameters for the query, will call the bind_param to avoid
|
|
|
349 some security issues. If there are no arguments, will return the bind_parameters
|
|
|
350 ReturnType : listref
|
|
|
351 Exceptions: if called with one argument
|
|
|
352
|
|
|
353 =cut
|
|
|
354
|
|
|
355 sub bind_param_generic_fetch{
|
|
|
356 my $self = shift;
|
|
|
357 my $param = shift;
|
|
|
358 my $sql_type = shift;
|
|
|
359
|
|
|
360 if (defined $param && !defined $sql_type){
|
|
|
361 throw("Need to specify sql_type for parameter $param\n");
|
|
|
362 }
|
|
|
363 elsif (defined $param && defined $sql_type){
|
|
|
364 #check when there is a SQL_INTEGER type that the parameter is really a number
|
|
|
365 if ($sql_type eq SQL_INTEGER){
|
|
|
366 throw "Trying to assign a non numerical parameter to an integer value in the database" if ($param !~ /^\d+$/);
|
|
|
367 }
|
|
|
368 #both paramters have been entered, push it to the bind_param array
|
|
|
369 push @{$self->{'_bind_param_generic_fetch'}},[$param,$sql_type];
|
|
|
370 }
|
|
|
371 elsif (!defined $param && !defined $sql_type){
|
|
|
372 #when there are no arguments, return the array
|
|
|
373 return $self->{'_bind_param_generic_fetch'};
|
|
|
374 }
|
|
|
375
|
|
|
376 }
|
|
|
377
|
|
|
378
|
|
|
379
|
|
|
380 =head2 generic_fetch
|
|
|
381
|
|
|
382 Arg [1] : (optional) string $constraint
|
|
|
383 An SQL query constraint (i.e. part of the WHERE clause)
|
|
|
384 Arg [2] : (optional) Bio::EnsEMBL::AssemblyMapper $mapper
|
|
|
385 A mapper object used to remap features
|
|
|
386 as they are retrieved from the database
|
|
|
387 Arg [3] : (optional) Bio::EnsEMBL::Slice $slice
|
|
|
388 A slice that features should be remapped to
|
|
|
389 Example : $fts = $a->generic_fetch('contig_id in (1234, 1235)');
|
|
|
390 Description: Performs a database fetch and returns feature objects in
|
|
|
391 contig coordinates.
|
|
|
392 Returntype : listref of Bio::EnsEMBL::SeqFeature in contig coordinates
|
|
|
393 Exceptions : Thrown if there is an issue with querying the data
|
|
|
394 Caller : BaseFeatureAdaptor, ProxyDnaAlignFeatureAdaptor::generic_fetch
|
|
|
395 Status : Stable
|
|
|
396
|
|
|
397 =cut
|
|
|
398
|
|
|
399 sub generic_fetch {
|
|
|
400 my ($self, $constraint, $mapper, $slice) = @_;
|
|
|
401 my $sql = $self->_generate_sql($constraint);
|
|
|
402 my $params = $self->bind_param_generic_fetch();
|
|
|
403 $params ||= [];
|
|
|
404 $self->{_bind_param_generic_fetch} = undef;
|
|
|
405 my $sth = $self->db()->dbc()->prepare($sql);
|
|
|
406 my $i = 1;
|
|
|
407 foreach my $param (@{$params}){
|
|
|
408 $sth->bind_param($i,$param->[0],$param->[1]);
|
|
|
409 $i++;
|
|
|
410 }
|
|
|
411 eval { $sth->execute() };
|
|
|
412 if ($@) {
|
|
|
413 throw("Detected an error whilst executing SQL '${sql}': $@");
|
|
|
414 }
|
|
|
415
|
|
|
416 my $res = $self->_objs_from_sth($sth, $mapper, $slice);
|
|
|
417 $sth->finish();
|
|
|
418 return $res;
|
|
|
419 }
|
|
|
420
|
|
|
421 =head2 generic_count
|
|
|
422
|
|
|
423 Arg [1] : (optional) string $constraint
|
|
|
424 An SQL query constraint (i.e. part of the WHERE clause)
|
|
|
425 Example : $number_feats = $a->generic_count('contig_id in (1234, 1235)');
|
|
|
426 Description: Performs a database fetch and returns a count of those features
|
|
|
427 found. This is analagous to C<generic_fetch()>
|
|
|
428 Returntype : Integer count of the elements.
|
|
|
429 Exceptions : Thrown if there is an issue with querying the data
|
|
|
430
|
|
|
431 =cut
|
|
|
432
|
|
|
433 sub generic_count {
|
|
|
434 my ($self, $constraint) = @_;
|
|
|
435 my $sql = $self->_generate_sql($constraint, 'count(*)');
|
|
|
436 my $params = $self->bind_param_generic_fetch();
|
|
|
437 $params ||= [];
|
|
|
438 $self->{_bind_param_generic_fetch} = undef;
|
|
|
439 my $h = $self->db()->dbc()->sql_helper();
|
|
|
440 my $count = $h->execute_single_result(-SQL => $sql, -PARAMS => $params);
|
|
|
441 return $count;
|
|
|
442 }
|
|
|
443
|
|
|
444 sub _generate_sql {
|
|
|
445 my ($self, $constraint, @input_columns) = @_;
|
|
|
446
|
|
|
447 my @tabs = $self->_tables();
|
|
|
448
|
|
|
449 my $extra_default_where;
|
|
|
450
|
|
|
451 # Hack for feature types that needs to be restricted to species_id (in
|
|
|
452 # coord_system).
|
|
|
453 if ( $self->is_multispecies()
|
|
|
454 && $self->isa('Bio::EnsEMBL::DBSQL::BaseFeatureAdaptor')
|
|
|
455 && !$self->isa('Bio::EnsEMBL::DBSQL::UnmappedObjectAdaptor') )
|
|
|
456 {
|
|
|
457 # We do a check to see if there is already seq_region
|
|
|
458 # and coord_system defined to ensure we get the right
|
|
|
459 # alias. We then do the extra query irrespectively of
|
|
|
460 # what has already been specified by the user.
|
|
|
461 my %thash = map { $_->[0] => $_->[1] } @tabs;
|
|
|
462
|
|
|
463 my $sr_alias =
|
|
|
464 ( exists( $thash{seq_region} ) ? $thash{seq_region} : 'sr' );
|
|
|
465 my $cs_alias =
|
|
|
466 ( exists( $thash{coord_system} ) ? $thash{coord_system} : 'cs' );
|
|
|
467
|
|
|
468 if ( !exists( $thash{seq_region} ) ) {
|
|
|
469 push( @tabs, [ 'seq_region', $sr_alias ] );
|
|
|
470 }
|
|
|
471 if ( !exists( $thash{coord_system} ) ) {
|
|
|
472 push( @tabs, [ 'coord_system', $cs_alias ] );
|
|
|
473 }
|
|
|
474
|
|
|
475 $extra_default_where = sprintf(
|
|
|
476 '%s.seq_region_id = %s.seq_region_id '
|
|
|
477 . 'AND %s.coord_system_id = %s.coord_system_id '
|
|
|
478 . 'AND %s.species_id = ?',
|
|
|
479 $tabs[0]->[1], $sr_alias, $sr_alias,
|
|
|
480 $cs_alias, $cs_alias );
|
|
|
481
|
|
|
482 $self->bind_param_generic_fetch( $self->species_id(), SQL_INTEGER );
|
|
|
483 } ## end if ( $self->is_multispecies...)
|
|
|
484
|
|
|
485 @input_columns = $self->_columns() if ! @input_columns;
|
|
|
486 my $columns = join(', ', @input_columns);
|
|
|
487
|
|
|
488 #
|
|
|
489 # Construct a left join statement if one was defined, and remove the
|
|
|
490 # left-joined table from the table list
|
|
|
491 #
|
|
|
492 my @left_join_list = $self->_left_join();
|
|
|
493 my $left_join_prefix = '';
|
|
|
494 my $left_join = '';
|
|
|
495 my @tables;
|
|
|
496 if(@left_join_list) {
|
|
|
497 my %left_join_hash = map { $_->[0] => $_->[1] } @left_join_list;
|
|
|
498 while(my $t = shift @tabs) {
|
|
|
499 my $t_alias = $t->[0] . " " . $t->[1];
|
|
|
500 if( exists $left_join_hash{ $t->[0] } || exists $left_join_hash{$t_alias}) {
|
|
|
501 my $condition = $left_join_hash{ $t->[0] };
|
|
|
502 $condition ||= $left_join_hash{$t_alias};
|
|
|
503 my $syn = $t->[1];
|
|
|
504 $left_join .=
|
|
|
505 "\n LEFT JOIN " . $t->[0] . " $syn ON $condition ) ";
|
|
|
506 $left_join_prefix .= '(';
|
|
|
507 } else {
|
|
|
508 push @tables, $t;
|
|
|
509 }
|
|
|
510 }
|
|
|
511 } else {
|
|
|
512 @tables = @tabs;
|
|
|
513 }
|
|
|
514
|
|
|
515 my $straight_join = '';
|
|
|
516
|
|
|
517 if($self->_straight_join()) {
|
|
|
518 $straight_join = "STRAIGHT_JOIN";
|
|
|
519 }
|
|
|
520
|
|
|
521 #construct a nice table string like 'table1 t1, table2 t2'
|
|
|
522 my $tablenames = join(', ', map({ join(' ', @$_) } @tables));
|
|
|
523
|
|
|
524 my $sql =
|
|
|
525 "SELECT $straight_join $columns\n"
|
|
|
526 . "FROM $left_join_prefix ($tablenames) $left_join";
|
|
|
527
|
|
|
528 my $default_where = $self->_default_where_clause();
|
|
|
529 my $final_clause = $self->_final_clause;
|
|
|
530
|
|
|
531 if ($extra_default_where) {
|
|
|
532 if ($default_where) {
|
|
|
533 $default_where .= "\n AND $extra_default_where";
|
|
|
534 } else {
|
|
|
535 $default_where = $extra_default_where;
|
|
|
536 }
|
|
|
537 }
|
|
|
538
|
|
|
539 #append a where clause if it was defined
|
|
|
540 if ($constraint) {
|
|
|
541 $sql .= "\n WHERE $constraint ";
|
|
|
542 if ($default_where) {
|
|
|
543 $sql .= " AND\n $default_where ";
|
|
|
544 }
|
|
|
545 } elsif ($default_where) {
|
|
|
546 $sql .= "\n WHERE $default_where ";
|
|
|
547 }
|
|
|
548
|
|
|
549 #append additional clauses which may have been defined
|
|
|
550 $sql .= "\n$final_clause";
|
|
|
551
|
|
|
552 # FOR DEBUG:
|
|
|
553 #printf(STDERR "SQL:\n%s\n", $sql);
|
|
|
554
|
|
|
555 return $sql;
|
|
|
556 }
|
|
|
557
|
|
|
558
|
|
|
559 =head2 fetch_by_dbID
|
|
|
560
|
|
|
561 Arg [1] : int $id
|
|
|
562 The unique database identifier for the feature to be obtained
|
|
|
563 Example : $feat = $adaptor->fetch_by_dbID(1234));
|
|
|
564 $feat = $feat->transform('contig');
|
|
|
565 Description: Returns the feature created from the database defined by the
|
|
|
566 the id $id. The feature will be returned in its native
|
|
|
567 coordinate system. That is, the coordinate system in which it
|
|
|
568 is stored in the database. In order to convert it to a
|
|
|
569 particular coordinate system use the transfer() or transform()
|
|
|
570 method. If the feature is not found in the database then
|
|
|
571 undef is returned instead
|
|
|
572 Returntype : Bio::EnsEMBL::Feature or undef
|
|
|
573 Exceptions : thrown if $id arg is not provided
|
|
|
574 does not exist
|
|
|
575 Caller : general
|
|
|
576 Status : Stable
|
|
|
577
|
|
|
578 =cut
|
|
|
579
|
|
|
580 sub fetch_by_dbID{
|
|
|
581 my ($self,$id) = @_;
|
|
|
582
|
|
|
583 throw("id argument is required") if(!defined $id);
|
|
|
584
|
|
|
585 #construct a constraint like 't1.table1_id = 123'
|
|
|
586 my @tabs = $self->_tables;
|
|
|
587 my ($name, $syn) = @{$tabs[0]};
|
|
|
588 $self->bind_param_generic_fetch($id,SQL_INTEGER);
|
|
|
589 my $constraint = "${syn}.${name}_id = ?";
|
|
|
590
|
|
|
591 #Should only be one
|
|
|
592 my ($feat) = @{$self->generic_fetch($constraint)};
|
|
|
593
|
|
|
594 return undef if(!$feat);
|
|
|
595
|
|
|
596 return $feat;
|
|
|
597 }
|
|
|
598
|
|
|
599
|
|
|
600 =head2 fetch_all_by_dbID_list
|
|
|
601
|
|
|
602 Arg [1] : listref of integers $id_list
|
|
|
603 The unique database identifiers for the features to
|
|
|
604 be obtained.
|
|
|
605 Arg [2] : optional - Bio::EnsEMBL::Slice to map features onto.
|
|
|
606 Example : @feats = @{$adaptor->fetch_all_by_dbID_list([1234, 2131, 982]))};
|
|
|
607 Description: Returns the features created from the database
|
|
|
608 defined by the the IDs in contained in the provided
|
|
|
609 ID list $id_list. The features will be returned
|
|
|
610 in their native coordinate system. That is, the
|
|
|
611 coordinate system in which they are stored in the
|
|
|
612 database. In order to convert the features to a
|
|
|
613 particular coordinate system use the transfer() or
|
|
|
614 transform() method. If none of the features are
|
|
|
615 found in the database a reference to an empty list is
|
|
|
616 returned.
|
|
|
617 Returntype : listref of Bio::EnsEMBL::Features
|
|
|
618 Exceptions : thrown if $id arg is not provided
|
|
|
619 does not exist
|
|
|
620 Caller : general
|
|
|
621 Status : Stable
|
|
|
622
|
|
|
623 =cut
|
|
|
624
|
|
|
625 sub fetch_all_by_dbID_list {
|
|
|
626 my ( $self, $id_list_ref, $slice ) = @_;
|
|
|
627
|
|
|
628 if ( !defined($id_list_ref) || ref($id_list_ref) ne 'ARRAY' ) {
|
|
|
629 throw("id_list list reference argument is required");
|
|
|
630 }
|
|
|
631
|
|
|
632 if ( !@{$id_list_ref} ) { return [] }
|
|
|
633
|
|
|
634 # Construct a constraint like 't1.table1_id = 123'
|
|
|
635 my @tabs = $self->_tables();
|
|
|
636 my ( $name, $syn ) = @{ $tabs[0] };
|
|
|
637
|
|
|
638 # Ensure that we do not exceed MySQL's max_allowed_packet (defaults to
|
|
|
639 # 1 MB) splitting large queries into smaller queries of at most 256 KB
|
|
|
640 # (32768 8-bit characters). Assuming a (generous) average dbID string
|
|
|
641 # length of 16, this means 2048 dbIDs in each query.
|
|
|
642 my $max_size = 2048;
|
|
|
643
|
|
|
644
|
|
|
645 my %id_list;
|
|
|
646 $id_list{$_}++ for @{$id_list_ref};
|
|
|
647 my @id_list = keys %id_list;
|
|
|
648
|
|
|
649 my @out;
|
|
|
650
|
|
|
651 while (@id_list) {
|
|
|
652 my @ids;
|
|
|
653 my $id_str;
|
|
|
654
|
|
|
655 if ( scalar(@id_list) > $max_size ) {
|
|
|
656 @ids = splice( @id_list, 0, $max_size );
|
|
|
657 } else {
|
|
|
658 @ids = @id_list;
|
|
|
659 @id_list = ();
|
|
|
660 }
|
|
|
661
|
|
|
662 if ( scalar(@ids) > 1 ) {
|
|
|
663 $id_str = " IN (" . join( ',', @ids ) . ")";
|
|
|
664 } else {
|
|
|
665 $id_str = " = " . $ids[0];
|
|
|
666 }
|
|
|
667
|
|
|
668 my $constraint = "${syn}.${name}_id $id_str";
|
|
|
669
|
|
|
670 push @out, @{ $self->generic_fetch($constraint, undef, $slice) };
|
|
|
671 }
|
|
|
672
|
|
|
673 return \@out;
|
|
|
674 } ## end sub fetch_all_by_dbID_list
|
|
|
675
|
|
|
676 # might not be a good idea, but for convenience
|
|
|
677 # shouldnt be called on the BIG tables though
|
|
|
678
|
|
|
679 sub fetch_all {
|
|
|
680 my $self = shift;
|
|
|
681 return $self->generic_fetch();
|
|
|
682 }
|
|
|
683
|
|
|
684 =head2 last_insert_id
|
|
|
685
|
|
|
686 Arg [1] : (optional) $field the name of the field the inserted ID was pushed
|
|
|
687 into
|
|
|
688 Arg [2] : (optional) HashRef used to pass extra attributes through to the
|
|
|
689 DBD driver
|
|
|
690 Arg [3] : (optional) $table the name of the table to use if the adaptor
|
|
|
691 does not implement C<_tables()>
|
|
|
692 Description : Delegating method which uses DBI to extract the last inserted
|
|
|
693 identifier. If using MySQL we just call the DBI method
|
|
|
694 L<DBI::last_insert_id()> since MySQL ignores any extra
|
|
|
695 arguments. See L<DBI> for more information about this
|
|
|
696 delegated method.
|
|
|
697 Example : my $id = $self->last_insert_id('my_id'); my $other_id = $self->last_insert_id();
|
|
|
698 Returntype : Scalar or undef
|
|
|
699
|
|
|
700 =cut
|
|
|
701
|
|
|
702 sub last_insert_id {
|
|
|
703 my ($self, $field, $attributes, $table) = @_;
|
|
|
704 my $dbc = $self->dbc();
|
|
|
705 my $dbh = $dbc->db_handle();
|
|
|
706 my @args;
|
|
|
707 if($dbc->driver() eq 'mysql') {
|
|
|
708 @args = (undef,undef,undef,undef);
|
|
|
709 }
|
|
|
710 else {
|
|
|
711 if(!$table) {
|
|
|
712 ($table) = $self->_tables();
|
|
|
713 }
|
|
|
714 @args = (undef, $dbc->dbname(), $table->[0], $field);
|
|
|
715 }
|
|
|
716 $attributes ||= {};
|
|
|
717 return $dbh->last_insert_id(@args, $attributes);
|
|
|
718 }
|
|
|
719
|
|
|
720
|
|
|
721 #_tables
|
|
|
722 #
|
|
|
723 # Args : none
|
|
|
724 # Example : $tablename = $self->_table_name()
|
|
|
725 # Description: ABSTRACT PROTECTED
|
|
|
726 # Subclasses are responsible for implementing this
|
|
|
727 # method. It should list of [tablename, alias] pairs.
|
|
|
728 # Additionally the primary table (with the dbID,
|
|
|
729 # analysis_id, and score) should be the first table in
|
|
|
730 # the list. e.g:
|
|
|
731 # ( ['repeat_feature', 'rf'],
|
|
|
732 # ['repeat_consensus', 'rc']);
|
|
|
733 # used to obtain features.
|
|
|
734 # Returntype : list of [tablename, alias] pairs
|
|
|
735 # Exceptions : thrown if not implemented by subclass
|
|
|
736 # Caller : BaseFeatureAdaptor::generic_fetch
|
|
|
737 #
|
|
|
738
|
|
|
739 sub _tables {
|
|
|
740 throw( "abstract method _tables not defined "
|
|
|
741 . "by implementing subclass of BaseAdaptor" );
|
|
|
742 }
|
|
|
743
|
|
|
744
|
|
|
745 #_columns
|
|
|
746 #
|
|
|
747 # Args : none
|
|
|
748 # Example : $tablename = $self->_columns()
|
|
|
749 # Description: ABSTRACT PROTECTED
|
|
|
750 # Subclasses are responsible for implementing this
|
|
|
751 # method. It should return a list of columns to be
|
|
|
752 # used for feature creation.
|
|
|
753 # Returntype : list of strings
|
|
|
754 # Exceptions : thrown if not implemented by subclass
|
|
|
755 # Caller : BaseFeatureAdaptor::generic_fetch
|
|
|
756 #
|
|
|
757
|
|
|
758 sub _columns {
|
|
|
759 throw( "abstract method _columns not defined "
|
|
|
760 . "by implementing subclass of BaseAdaptor" );
|
|
|
761 }
|
|
|
762
|
|
|
763
|
|
|
764 # _default_where_clause
|
|
|
765 #
|
|
|
766 # Arg [1] : none
|
|
|
767 # Example : none
|
|
|
768 # Description: May be overridden to provide an additional where
|
|
|
769 # constraint to the SQL query which is generated to
|
|
|
770 # fetch feature records. This constraint is always
|
|
|
771 # appended to the end of the generated where clause
|
|
|
772 # Returntype : string
|
|
|
773 # Exceptions : none
|
|
|
774 # Caller : generic_fetch
|
|
|
775 #
|
|
|
776
|
|
|
777 sub _default_where_clause { return '' }
|
|
|
778
|
|
|
779
|
|
|
780 # _left_join
|
|
|
781
|
|
|
782 # Arg [1] : none
|
|
|
783 # Example : none
|
|
|
784 # Description: Can be overridden by a subclass to specify any left
|
|
|
785 # joins which should occur. The table name specigfied
|
|
|
786 # in the join must still be present in the return
|
|
|
787 # values of.
|
|
|
788 # Returntype : a {'tablename' => 'join condition'} pair
|
|
|
789 # Exceptions : none
|
|
|
790 # Caller : general
|
|
|
791 #
|
|
|
792
|
|
|
793 sub _left_join { return () }
|
|
|
794
|
|
|
795
|
|
|
796 #_final_clause
|
|
|
797
|
|
|
798 # Arg [1] : none
|
|
|
799 # Example : none
|
|
|
800 # Description: May be overriden to provide an additional clause
|
|
|
801 # to the end of the SQL query used to fetch feature
|
|
|
802 # records. This is useful to add a required ORDER BY
|
|
|
803 # clause to the query for example.
|
|
|
804 # Returntype : string
|
|
|
805 # Exceptions : none
|
|
|
806 # Caller : generic_fetch
|
|
|
807
|
|
|
808 sub _final_clause { return '' }
|
|
|
809
|
|
|
810
|
|
|
811 #_objs_from_sth
|
|
|
812
|
|
|
813 # Arg [1] : DBI::row_hashref $hashref containing key-value pairs
|
|
|
814 # for each of the columns specified by the _columns method
|
|
|
815 # Example : my @feats = $self->_obj_from_hashref
|
|
|
816 # Description: ABSTRACT PROTECTED
|
|
|
817 # The subclass is responsible for implementing this
|
|
|
818 # method. It should take in a DBI row hash reference
|
|
|
819 # and return a list of created features in contig
|
|
|
820 # coordinates.
|
|
|
821 # Returntype : list of Bio::EnsEMBL::*Features in contig coordinates
|
|
|
822 # Exceptions : thrown if not implemented by subclass
|
|
|
823 # Caller : BaseFeatureAdaptor::generic_fetch
|
|
|
824
|
|
|
825 sub _objs_from_sth {
|
|
|
826 throw( "abstract method _objs_from_sth not defined "
|
|
|
827 . "by implementing subclass of BaseAdaptor" );
|
|
|
828 }
|
|
|
829
|
|
|
830 sub dump_data {
|
|
|
831 my $self = shift;
|
|
|
832 my $data = shift;
|
|
|
833
|
|
|
834 my $dumper = Data::Dumper->new([$data]);
|
|
|
835 $dumper->Indent(0);
|
|
|
836 $dumper->Terse(1);
|
|
|
837 my $dump = $dumper->Dump();
|
|
|
838 # $dump =~ s/'/\\'/g;
|
|
|
839 # $dump =~ s/^\$VAR1 = //;
|
|
|
840 return $dump;
|
|
|
841 }
|
|
|
842
|
|
|
843 sub get_dumped_data {
|
|
|
844 my $self = shift;
|
|
|
845 my $data = shift;
|
|
|
846
|
|
|
847 $data =~ s/\n|\r|\f|\\//g;
|
|
|
848 return eval ($data);
|
|
|
849 }
|
|
|
850
|
|
|
851
|
|
|
852 1;
|
