|
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::BaseMetaContainer - Encapsulates all generic access
|
|
|
24 to database meta information
|
|
|
25
|
|
|
26 =head1 SYNOPSIS
|
|
|
27
|
|
|
28 my $meta_container = $db_adaptor->get_MetaContainer();
|
|
|
29
|
|
|
30 my @mapping_info =
|
|
|
31 @{ $meta_container->list_value_by_key('assembly.mapping') };
|
|
|
32
|
|
|
33 =head1 DESCRIPTION
|
|
|
34
|
|
|
35 An object that encapsulates access to db meta data
|
|
|
36
|
|
|
37 =head1 METHODS
|
|
|
38
|
|
|
39 =cut
|
|
|
40
|
|
|
41 package Bio::EnsEMBL::DBSQL::BaseMetaContainer;
|
|
|
42
|
|
|
43 use vars qw(@ISA);
|
|
|
44 use strict;
|
|
|
45
|
|
|
46 use Bio::EnsEMBL::DBSQL::BaseAdaptor;
|
|
|
47 use Bio::EnsEMBL::Utils::Exception qw(throw deprecate warning);
|
|
|
48
|
|
|
49 @ISA = qw(Bio::EnsEMBL::DBSQL::BaseAdaptor);
|
|
|
50
|
|
|
51 # new() is inherited from Bio::EnsEMBL::DBSQL::BaseAdaptor
|
|
|
52
|
|
|
53 =head2 get_schema_version
|
|
|
54
|
|
|
55 Arg [1] : none
|
|
|
56 Example : $schema_ver = $meta_container->get_schema_version();
|
|
|
57 Description: Retrieves the schema version from the database meta table
|
|
|
58 Returntype : int
|
|
|
59 Exceptions : none
|
|
|
60 Caller : ?
|
|
|
61 Status : Medium risk
|
|
|
62
|
|
|
63 =cut
|
|
|
64
|
|
|
65 sub get_schema_version {
|
|
|
66 my $self = shift;
|
|
|
67
|
|
|
68 my $arrRef = $self->list_value_by_key('schema_version');
|
|
|
69
|
|
|
70 if (@$arrRef) {
|
|
|
71 my ($ver) = ( $arrRef->[0] =~ /^\s*(\d+)\s*$/ );
|
|
|
72 if ( !defined($ver) ) { # old style format
|
|
|
73 return 0;
|
|
|
74 }
|
|
|
75 return $ver;
|
|
|
76 } else {
|
|
|
77 warning(
|
|
|
78 sprintf(
|
|
|
79 "Please insert meta_key 'schema_version' "
|
|
|
80 . "in meta table on core database '%s'\n",
|
|
|
81 $self->dbc()->dbname() ) );
|
|
|
82 }
|
|
|
83
|
|
|
84 return 0;
|
|
|
85 }
|
|
|
86
|
|
|
87
|
|
|
88 =head2 list_value_by_key
|
|
|
89
|
|
|
90 Arg [1] : string $key
|
|
|
91 the key to obtain values from the meta table with
|
|
|
92 Example : my @values = @{ $meta_container->list_value_by_key($key) };
|
|
|
93 Description: gets a value for a key. Can be anything
|
|
|
94 Returntype : listref of strings
|
|
|
95 Exceptions : none
|
|
|
96 Caller : ?
|
|
|
97 Status : Stable
|
|
|
98
|
|
|
99 =cut
|
|
|
100
|
|
|
101 sub list_value_by_key {
|
|
|
102 my ( $self, $key ) = @_;
|
|
|
103
|
|
|
104 $self->{'cache'} ||= {};
|
|
|
105
|
|
|
106 if ( exists $self->{'cache'}->{$key} ) {
|
|
|
107 return $self->{'cache'}->{$key};
|
|
|
108 }
|
|
|
109
|
|
|
110 my $sth;
|
|
|
111
|
|
|
112 if ( !$self->_species_specific_key($key) ) {
|
|
|
113 $sth =
|
|
|
114 $self->prepare( "SELECT meta_value "
|
|
|
115 . "FROM meta "
|
|
|
116 . "WHERE meta_key = ? "
|
|
|
117 . "AND species_id IS NULL "
|
|
|
118 . "ORDER BY meta_id" );
|
|
|
119 } else {
|
|
|
120 $sth =
|
|
|
121 $self->prepare( "SELECT meta_value "
|
|
|
122 . "FROM meta "
|
|
|
123 . "WHERE meta_key = ? "
|
|
|
124 . "AND species_id = ? "
|
|
|
125 . "ORDER BY meta_id" );
|
|
|
126 $sth->bind_param( 2, $self->species_id(), SQL_INTEGER );
|
|
|
127 }
|
|
|
128
|
|
|
129 $sth->bind_param( 1, $key, SQL_VARCHAR );
|
|
|
130 $sth->execute();
|
|
|
131
|
|
|
132 my @result;
|
|
|
133 while ( my $arrRef = $sth->fetchrow_arrayref() ) {
|
|
|
134 push( @result, $arrRef->[0] );
|
|
|
135 }
|
|
|
136
|
|
|
137 $sth->finish();
|
|
|
138 $self->{'cache'}->{$key} = \@result;
|
|
|
139
|
|
|
140 return \@result;
|
|
|
141 } ## end sub list_value_by_key
|
|
|
142
|
|
|
143 =head2 single_value_by_key
|
|
|
144
|
|
|
145 Arg [1] : string $key
|
|
|
146 the key to obtain values from the meta table with
|
|
|
147 Arg [2] : boolean $warn
|
|
|
148 If true will cause the code to warn the non-existence of a value
|
|
|
149 Example : my $value = $mc->single_value_by_key($key);
|
|
|
150 Description: Gets a value for a key. Can be anything
|
|
|
151 Returntype : Scalar
|
|
|
152 Exceptions : Raised if more than 1 meta item is returned
|
|
|
153
|
|
|
154 =cut
|
|
|
155
|
|
|
156 sub single_value_by_key {
|
|
|
157 my ($self, $key, $warn) = @_;
|
|
|
158 my $results = $self->list_value_by_key($key);
|
|
|
159 if(defined $results) {
|
|
|
160 my $count = scalar(@{$results});
|
|
|
161 if($count == 1) {
|
|
|
162 my ($value) = @{$results};
|
|
|
163 return $value;
|
|
|
164 }
|
|
|
165 elsif($count == 0) {
|
|
|
166 if($warn) {
|
|
|
167 my $group = $self->db()->group();
|
|
|
168 my $msg = sprintf(qq{Please insert meta_key '%s' in meta table at %s db\n}, $key, $group);
|
|
|
169 warning($msg);
|
|
|
170 }
|
|
|
171 }
|
|
|
172 else {
|
|
|
173 my $values = join(q{,}, @{$results});
|
|
|
174 throw sprintf(q{Found the values [%s] for the key '%s'}, $values, $key);
|
|
|
175 }
|
|
|
176 }
|
|
|
177 return;
|
|
|
178 } ## end sub single_value_by_key
|
|
|
179
|
|
|
180 =head2 store_key_value
|
|
|
181
|
|
|
182 Arg [1] : string $key
|
|
|
183 a key under which $value should be stored
|
|
|
184 Arg [2] : string $value
|
|
|
185 the value to store in the meta table
|
|
|
186 Example : $meta_container->store_key_value($key, $value);
|
|
|
187 Description: stores a value in the meta container, accessable by a key
|
|
|
188 Returntype : none
|
|
|
189 Exceptions : Thrown if the key/value already exists.
|
|
|
190 Caller : ?
|
|
|
191 Status : Stable
|
|
|
192
|
|
|
193 =cut
|
|
|
194
|
|
|
195 sub store_key_value {
|
|
|
196 my ( $self, $key, $value ) = @_;
|
|
|
197
|
|
|
198 if ( $self->key_value_exists( $key, $value ) ) {
|
|
|
199 warn( "Key-value pair '$key'-'$value' "
|
|
|
200 . "already exists in the meta table; "
|
|
|
201 . "not storing duplicate" );
|
|
|
202 return;
|
|
|
203 }
|
|
|
204
|
|
|
205 my $sth;
|
|
|
206
|
|
|
207 if ( !$self->_species_specific_key($key) ) {
|
|
|
208 $sth = $self->prepare(
|
|
|
209 'INSERT INTO meta (meta_key, meta_value, species_id) '
|
|
|
210 . 'VALUES(?, ?, \N)' );
|
|
|
211 } else {
|
|
|
212 $sth = $self->prepare(
|
|
|
213 'INSERT INTO meta (meta_key, meta_value, species_id) '
|
|
|
214 . 'VALUES (?, ?, ?)' );
|
|
|
215 $sth->bind_param( 3, $self->species_id(), SQL_INTEGER );
|
|
|
216 }
|
|
|
217
|
|
|
218 $sth->bind_param( 1, $key, SQL_VARCHAR );
|
|
|
219 $sth->bind_param( 2, $value, SQL_VARCHAR );
|
|
|
220 $sth->execute();
|
|
|
221
|
|
|
222 $self->{'cache'} ||= {};
|
|
|
223
|
|
|
224 delete $self->{'cache'}->{$key};
|
|
|
225 } ## end sub store_key_value
|
|
|
226
|
|
|
227 =head2 update_key_value
|
|
|
228
|
|
|
229 Arg [1] : string $key
|
|
|
230 a key under which $value should be updated
|
|
|
231 Arg [2] : string $value
|
|
|
232 the value to update in the meta table
|
|
|
233 Example : $meta_container->update_key_value($key, $value);
|
|
|
234 Description: update a value in the meta container, accessable by a key
|
|
|
235 Returntype : none
|
|
|
236 Exceptions : none
|
|
|
237 Caller : ?
|
|
|
238 Status : Stable
|
|
|
239
|
|
|
240 =cut
|
|
|
241
|
|
|
242 sub update_key_value {
|
|
|
243 my ( $self, $key, $value ) = @_;
|
|
|
244
|
|
|
245 my $sth;
|
|
|
246
|
|
|
247 if ( !$self->_species_specific_key($key) ) {
|
|
|
248 $sth =
|
|
|
249 $self->prepare( 'UPDATE meta SET meta_value = ? '
|
|
|
250 . 'WHERE meta_key = ?'
|
|
|
251 . 'AND species_id IS NULL' );
|
|
|
252 } else {
|
|
|
253 $sth =
|
|
|
254 $self->prepare( 'UPDATE meta '
|
|
|
255 . 'SET meta_value = ? '
|
|
|
256 . 'WHERE meta_key = ? '
|
|
|
257 . 'AND species_id = ?' );
|
|
|
258 $sth->bind_param( 3, $self->species_id(), SQL_INTEGER );
|
|
|
259 }
|
|
|
260
|
|
|
261 $sth->bind_param( 1, $value, SQL_VARCHAR );
|
|
|
262 $sth->bind_param( 2, $key, SQL_VARCHAR );
|
|
|
263 $sth->execute();
|
|
|
264
|
|
|
265 } ## end sub update_key_value
|
|
|
266
|
|
|
267
|
|
|
268 =head2 delete_key
|
|
|
269
|
|
|
270 Arg [1] : string $key
|
|
|
271 The key which should be removed from the database.
|
|
|
272 Example : $meta_container->delete_key('sequence.compression');
|
|
|
273 Description: Removes all rows from the meta table which have a meta_key
|
|
|
274 equal to $key.
|
|
|
275 Returntype : none
|
|
|
276 Exceptions : none
|
|
|
277 Caller : dna_compress script, general
|
|
|
278 Status : Stable
|
|
|
279
|
|
|
280 =cut
|
|
|
281
|
|
|
282 sub delete_key {
|
|
|
283 my ( $self, $key ) = @_;
|
|
|
284
|
|
|
285 my $sth;
|
|
|
286
|
|
|
287 if ( !$self->_species_specific_key($key) ) {
|
|
|
288 $sth =
|
|
|
289 $self->prepare( 'DELETE FROM meta '
|
|
|
290 . 'WHERE meta_key = ?'
|
|
|
291 . 'AND species_id IS NULL' );
|
|
|
292 } else {
|
|
|
293 $sth =
|
|
|
294 $self->prepare( 'DELETE FROM meta '
|
|
|
295 . 'WHERE meta_key = ? '
|
|
|
296 . 'AND species_id = ?' );
|
|
|
297 $sth->bind_param( 2, $self->species_id(), SQL_INTEGER );
|
|
|
298 }
|
|
|
299
|
|
|
300 $sth->bind_param( 1, $key, SQL_VARCHAR );
|
|
|
301 $sth->execute();
|
|
|
302
|
|
|
303 delete $self->{'cache'}->{$key};
|
|
|
304 }
|
|
|
305
|
|
|
306 =head2 delete_key_value
|
|
|
307
|
|
|
308 Arg [1] : string $key
|
|
|
309 The key which should be removed from the database.
|
|
|
310 Arg [2] : string $value
|
|
|
311 The value to be removed.
|
|
|
312 Example : $meta_container->delete_key('patch', 'patch_39_40_b.sql|xref_unique_constraint');
|
|
|
313 Description: Removes all rows from the meta table which have a meta_key
|
|
|
314 equal to $key, AND a meta_value equal to $value.
|
|
|
315 Returntype : none
|
|
|
316 Exceptions : none
|
|
|
317 Caller : general
|
|
|
318 Status : Stable
|
|
|
319
|
|
|
320 =cut
|
|
|
321
|
|
|
322 sub delete_key_value {
|
|
|
323 my ( $self, $key, $value ) = @_;
|
|
|
324
|
|
|
325 my $sth;
|
|
|
326
|
|
|
327 if ( !$self->_species_specific_key($key) ) {
|
|
|
328 $sth =
|
|
|
329 $self->prepare( 'DELETE FROM meta '
|
|
|
330 . 'WHERE meta_key = ? '
|
|
|
331 . 'AND meta_value = ?'
|
|
|
332 . 'AND species_id IS NULL' );
|
|
|
333 } else {
|
|
|
334 $sth =
|
|
|
335 $self->prepare( 'DELETE FROM meta '
|
|
|
336 . 'WHERE meta_key = ? '
|
|
|
337 . 'AND meta_value = ? '
|
|
|
338 . 'AND species_id = ?' );
|
|
|
339 $sth->bind_param( 3, $self->species_id(), SQL_INTEGER );
|
|
|
340 }
|
|
|
341
|
|
|
342 $sth->bind_param( 1, $key, SQL_VARCHAR );
|
|
|
343 $sth->bind_param( 2, $value, SQL_VARCHAR );
|
|
|
344 $sth->execute();
|
|
|
345
|
|
|
346 delete $self->{'cache'}->{$key};
|
|
|
347 } ## end sub delete_key_value
|
|
|
348
|
|
|
349 =head2 key_value_exists
|
|
|
350
|
|
|
351 Arg [1] : string $key
|
|
|
352 the key to check
|
|
|
353 Arg [2] : string $value
|
|
|
354 the value to check
|
|
|
355 Example : if ($meta_container->key_value_exists($key, $value)) ...
|
|
|
356 Description: Return true (1) if a particular key/value pair exists,
|
|
|
357 false (0) otherwise.
|
|
|
358 Returntype : boolean
|
|
|
359 Exceptions : none
|
|
|
360 Caller : ?
|
|
|
361 Status : Stable
|
|
|
362
|
|
|
363 =cut
|
|
|
364
|
|
|
365 sub key_value_exists {
|
|
|
366 my ( $self, $key, $value ) = @_;
|
|
|
367
|
|
|
368 my $sth;
|
|
|
369
|
|
|
370 if ( !$self->_species_specific_key($key) ) {
|
|
|
371 $sth =
|
|
|
372 $self->prepare( 'SELECT meta_value '
|
|
|
373 . 'FROM meta '
|
|
|
374 . 'WHERE meta_key = ? '
|
|
|
375 . 'AND meta_value = ?'
|
|
|
376 . 'AND species_id IS NULL' );
|
|
|
377 } else {
|
|
|
378 $sth =
|
|
|
379 $self->prepare( 'SELECT meta_value '
|
|
|
380 . 'FROM meta '
|
|
|
381 . 'WHERE meta_key = ? '
|
|
|
382 . 'AND meta_value = ? '
|
|
|
383 . 'AND species_id = ?' );
|
|
|
384 $sth->bind_param( 3, $self->species_id(), SQL_INTEGER );
|
|
|
385 }
|
|
|
386
|
|
|
387 $sth->bind_param( 1, $key, SQL_VARCHAR );
|
|
|
388 $sth->bind_param( 2, $value, SQL_VARCHAR );
|
|
|
389 $sth->execute();
|
|
|
390
|
|
|
391 while ( my $arrRef = $sth->fetchrow_arrayref() ) {
|
|
|
392 if ( $arrRef->[0] eq $value ) {
|
|
|
393 $sth->finish();
|
|
|
394 return 1;
|
|
|
395 }
|
|
|
396 }
|
|
|
397
|
|
|
398 return 0;
|
|
|
399 } ## end sub key_value_exists
|
|
|
400
|
|
|
401 # This utility method determines whether the key is a species-specific
|
|
|
402 # meta key or not. If the key is either 'patch' or 'schema_version',
|
|
|
403 # then it is not species-specific.
|
|
|
404
|
|
|
405 # FIXME variation team messed up in release 65 and added the ploidy
|
|
|
406 # entry without species_id - this will be corrected for release 66,
|
|
|
407 # for now, I've added it to the list of allowed non-species specific
|
|
|
408
|
|
|
409 sub _species_specific_key {
|
|
|
410 my ( $self, $key ) = @_;
|
|
|
411
|
|
|
412 return ( $key ne 'patch'
|
|
|
413 && $key ne 'schema_version'
|
|
|
414 && $key ne 'schema_type'
|
|
|
415 && $key ne 'ploidy');
|
|
|
416 }
|
|
|
417
|
|
|
418 1;
|
