|
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 package Bio::EnsEMBL::External::BlastAdaptor;
|
|
|
22
|
|
|
23 use strict;
|
|
|
24 use DBI;
|
|
|
25 use Storable qw(freeze thaw);
|
|
|
26 use Data::Dumper qw( Dumper );
|
|
|
27 use Time::Local;
|
|
|
28
|
|
|
29 use vars qw(@ISA);
|
|
|
30
|
|
|
31 use Bio::EnsEMBL::DBSQL::BaseAdaptor;
|
|
|
32 use Bio::EnsEMBL::DBSQL::DBConnection;
|
|
|
33 use Bio::Search::HSP::EnsemblHSP; # This is a web module
|
|
|
34
|
|
|
35 @ISA = qw( Bio::EnsEMBL::DBSQL::BaseAdaptor );
|
|
|
36 #@ISA = qw( Bio::EnsEMBL::DBSQL::DBAdaptor );
|
|
|
37
|
|
|
38
|
|
|
39 #----------------------------------------------------------------------
|
|
|
40 # Define SQL
|
|
|
41
|
|
|
42 #--- CREATE TABLES ---
|
|
|
43 our $SQL_CREATE_TICKET = "
|
|
|
44 CREATE TABLE blast_ticket (
|
|
|
45 ticket_id int(10) unsigned NOT NULL auto_increment,
|
|
|
46 create_time datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
47 update_time datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
48 ticket varchar(32) NOT NULL default '',
|
|
|
49 status enum('CURRENT','DELETED') NOT NULL default 'CURRENT',
|
|
|
50 object longblob,
|
|
|
51 PRIMARY KEY (ticket_id),
|
|
|
52 UNIQUE KEY ticket (ticket),
|
|
|
53 KEY create_time (create_time),
|
|
|
54 KEY update_time (update_time)
|
|
|
55 ) ENGINE=MyISAM";
|
|
|
56
|
|
|
57 our $SQL_CREATE_TABLE_LOG = "
|
|
|
58 CREATE TABLE blast_table_log (
|
|
|
59 table_id int(10) unsigned NOT NULL auto_increment,
|
|
|
60 table_name varchar(32),
|
|
|
61 table_type enum('TICKET','RESULT','HIT','HSP') default NULL,
|
|
|
62 table_status enum('CURRENT','FILLED','DELETED') default NULL,
|
|
|
63 use_date date default NULL,
|
|
|
64 create_time datetime default NULL,
|
|
|
65 delete_time datetime default NULL,
|
|
|
66 num_objects int(10) default NULL,
|
|
|
67 PRIMARY KEY (table_id),
|
|
|
68 KEY table_name (table_name),
|
|
|
69 KEY table_type (table_type),
|
|
|
70 KEY use_date (use_date),
|
|
|
71 KEY table_status (table_status)
|
|
|
72 ) ENGINE=MyISAM";
|
|
|
73
|
|
|
74
|
|
|
75 our $SQL_CREATE_DAILY_RESULT = "
|
|
|
76 CREATE TABLE %s (
|
|
|
77 result_id int(10) unsigned NOT NULL auto_increment,
|
|
|
78 ticket varchar(32) default NULL,
|
|
|
79 object longblob,
|
|
|
80 PRIMARY KEY (result_id),
|
|
|
81 KEY ticket (ticket)
|
|
|
82 ) ENGINE=MyISAM";
|
|
|
83
|
|
|
84 our $SQL_CREATE_DAILY_HIT = "
|
|
|
85 CREATE TABLE %s (
|
|
|
86 hit_id int(10) unsigned NOT NULL auto_increment,
|
|
|
87 ticket varchar(32) default NULL,
|
|
|
88 object longblob,
|
|
|
89 PRIMARY KEY (hit_id),
|
|
|
90 KEY ticket (ticket)
|
|
|
91 ) ENGINE=MyISAM";
|
|
|
92
|
|
|
93 our $SQL_CREATE_DAILY_HSP = "
|
|
|
94 CREATE TABLE %s (
|
|
|
95 hsp_id int(10) unsigned NOT NULL auto_increment,
|
|
|
96 ticket varchar(32) default NULL,
|
|
|
97 object longblob,
|
|
|
98 chr_name varchar(32) default NULL,
|
|
|
99 chr_start int(10) unsigned default NULL,
|
|
|
100 chr_end int(10) unsigned default NULL,
|
|
|
101 PRIMARY KEY (hsp_id),
|
|
|
102 KEY ticket (ticket)
|
|
|
103 ) ENGINE=MyISAM MAX_ROWS=705032704 AVG_ROW_LENGTH=4000";
|
|
|
104
|
|
|
105 #--- TABLE LOG ---
|
|
|
106 our $SQL_SELECT_TABLE_LOG_CURRENT = "
|
|
|
107 SELECT use_date
|
|
|
108 FROM blast_table_log
|
|
|
109 WHERE table_type = ?
|
|
|
110 AND table_status = 'CURRENT'
|
|
|
111 ORDER BY use_date DESC";
|
|
|
112
|
|
|
113 our $SQL_TABLE_LOG_INSERT = "
|
|
|
114 INSERT into blast_table_log
|
|
|
115 ( table_name, table_status, table_type, use_date, create_time)
|
|
|
116 VALUES ( ?, ?, ?, ?, NOW() )";
|
|
|
117
|
|
|
118 our $SQL_TABLE_LOG_UPDATE = "
|
|
|
119 UPDATE blast_table_log
|
|
|
120 SET table_status = ?,
|
|
|
121 delete_time = ?,
|
|
|
122 num_objects = ?
|
|
|
123 WHERE table_name = ?";
|
|
|
124
|
|
|
125 #--- TICKETS ---
|
|
|
126
|
|
|
127 our $SQL_SEARCH_MULTI_STORE = "
|
|
|
128 INSERT INTO blast_ticket ( create_time, update_time, object, ticket )
|
|
|
129 VALUES ( NOW(), NOW(), ? , ? )";
|
|
|
130
|
|
|
131 our $SQL_SEARCH_MULTI_UPDATE = "
|
|
|
132 UPDATE blast_ticket
|
|
|
133 SET object = ?,
|
|
|
134 update_time = NOW()
|
|
|
135 WHERE ticket = ?";
|
|
|
136
|
|
|
137 our $SQL_SEARCH_MULTI_RETRIEVE = "
|
|
|
138 SELECT object
|
|
|
139 FROM blast_ticket
|
|
|
140 WHERE ticket = ? ";
|
|
|
141
|
|
|
142 #--- RESULTS ---
|
|
|
143
|
|
|
144 our $SQL_RESULT_STORE = "
|
|
|
145 INSERT INTO blast_result%s ( object, ticket )
|
|
|
146 VALUES ( ? , ? )";
|
|
|
147
|
|
|
148 our $SQL_RESULT_UPDATE = "
|
|
|
149 UPDATE blast_result%s
|
|
|
150 SET object = ?,
|
|
|
151 ticket = ?
|
|
|
152 WHERE result_id = ?";
|
|
|
153
|
|
|
154 our $SQL_RESULT_RETRIEVE = "
|
|
|
155 SELECT object
|
|
|
156 FROM blast_result%s
|
|
|
157 WHERE result_id = ? ";
|
|
|
158
|
|
|
159 our $SQL_RESULT_RETRIEVE_TICKET = "
|
|
|
160 SELECT object
|
|
|
161 FROM blast_result%s
|
|
|
162 WHERE ticket = ? ";
|
|
|
163
|
|
|
164 #--- HITS ---
|
|
|
165
|
|
|
166 our $SQL_HIT_STORE = "
|
|
|
167 INSERT INTO blast_hit%s ( object, ticket )
|
|
|
168 VALUES ( ? , ? )";
|
|
|
169
|
|
|
170 our $SQL_HIT_UPDATE = "
|
|
|
171 UPDATE blast_hit%s
|
|
|
172 SET object = ?,
|
|
|
173 ticket = ?
|
|
|
174 WHERE hit_id = ?";
|
|
|
175
|
|
|
176 our $SQL_HIT_RETRIEVE = "
|
|
|
177 SELECT object
|
|
|
178 FROM blast_hit%s
|
|
|
179 WHERE hit_id = ? ";
|
|
|
180
|
|
|
181 #--- HSPS ---
|
|
|
182
|
|
|
183 our $SQL_HSP_STORE = "
|
|
|
184 INSERT INTO blast_hsp%s ( object, ticket, chr_name, chr_start, chr_end )
|
|
|
185 VALUES ( ? , ? , ? , ? , ? )";
|
|
|
186
|
|
|
187 our $SQL_HSP_UPDATE = "
|
|
|
188 UPDATE blast_hsp%s
|
|
|
189 SET object = ?,
|
|
|
190 ticket = ?,
|
|
|
191 chr_name = ?,
|
|
|
192 chr_start = ?,
|
|
|
193 chr_end = ?
|
|
|
194 WHERE hsp_id = ?";
|
|
|
195
|
|
|
196 our $SQL_HSP_RETRIEVE = "
|
|
|
197 SELECT object
|
|
|
198 FROM blast_hsp%s
|
|
|
199 WHERE hsp_id = ? ";
|
|
|
200
|
|
|
201 our $SQL_HSP_REMOVE = "
|
|
|
202 UPDATE blast_hsp%s
|
|
|
203 SET chr_name = NULL,
|
|
|
204 chr_start = NULL,
|
|
|
205 chr_end = NULL
|
|
|
206 WHERE hsp_id = ?";
|
|
|
207
|
|
|
208
|
|
|
209 #=head2 new
|
|
|
210 #
|
|
|
211 # Arg [1] :
|
|
|
212 # Function :
|
|
|
213 # Returntype:
|
|
|
214 # Exceptions:
|
|
|
215 # Caller :
|
|
|
216 # Example :
|
|
|
217 #
|
|
|
218 #=cut
|
|
|
219 #
|
|
|
220 #
|
|
|
221 sub new {
|
|
|
222 my $caller = shift;
|
|
|
223 #warn "DB - @_";
|
|
|
224 my $connection = Bio::EnsEMBL::DBSQL::DBConnection->new(@_);
|
|
|
225 my $self = $caller->SUPER::new($connection);
|
|
|
226 $self->{'disconnect_flag'} = 1;
|
|
|
227 return $self;
|
|
|
228 }
|
|
|
229
|
|
|
230
|
|
|
231 sub new_fast{
|
|
|
232 my ($caller,$connection) = @_;
|
|
|
233 my $self = $caller->SUPER::new($connection);
|
|
|
234 $self->{'disconnect_flag'} = 1;
|
|
|
235 return $self;
|
|
|
236 }
|
|
|
237
|
|
|
238 #----------------------------------------------------------------------
|
|
|
239
|
|
|
240 sub species {
|
|
|
241 my ($self, $arg ) = @_;
|
|
|
242 ( defined $arg ) &&
|
|
|
243 ( $self->{_species} = $arg );
|
|
|
244 $self->{_species};
|
|
|
245 }
|
|
|
246
|
|
|
247 #----------------------------------------------------------------------
|
|
|
248
|
|
|
249 =head2 ticket
|
|
|
250
|
|
|
251 Arg [1] : string ticket (optional)
|
|
|
252 Function : Get/get the blast ticket attribute
|
|
|
253 Returntype: string ticket
|
|
|
254 Exceptions:
|
|
|
255 Caller :
|
|
|
256 Example :
|
|
|
257
|
|
|
258 =cut
|
|
|
259
|
|
|
260 sub ticket{
|
|
|
261 my $key = "_ticket";
|
|
|
262 my $self = shift;
|
|
|
263 if( @_ ){ $self->{$key} = shift }
|
|
|
264 return $self->{$key};
|
|
|
265 }
|
|
|
266
|
|
|
267 #----------------------------------------------------------------------
|
|
|
268
|
|
|
269 =head2 store
|
|
|
270
|
|
|
271 Arg [1] :
|
|
|
272 Function :
|
|
|
273 Returntype:
|
|
|
274 Exceptions:
|
|
|
275 Caller :
|
|
|
276 Example :
|
|
|
277
|
|
|
278 =cut
|
|
|
279
|
|
|
280 sub store {
|
|
|
281 my $self = shift;
|
|
|
282 my $obj = shift;
|
|
|
283 my $ret_value = undef;
|
|
|
284 if( $obj->isa("Bio::Tools::Run::SearchMulti") ) {
|
|
|
285 $ret_value = $self->store_search_multi( $obj, @_ );
|
|
|
286 # warn "Just stored as Bio::Tools::Run::SearchMulti";
|
|
|
287 } elsif( $obj->isa( "Bio::Search::Result::ResultI" ) ) {
|
|
|
288 $ret_value = $self->store_result( $obj, @_ );
|
|
|
289 # warn "Just stored as Bio::Tools::Result::ResultI";
|
|
|
290 } elsif( $obj->isa( "Bio::Search::Hit::HitI" ) ) {
|
|
|
291 $ret_value = $self->store_hit( $obj, @_ );
|
|
|
292 # warn "Just stored as Bio::Tools::Hit::HitI";
|
|
|
293 } elsif( $obj->isa( "Bio::Search::HSP::HSPI" ) ) {
|
|
|
294 $ret_value = $self->store_hsp( $obj, @_ );
|
|
|
295 # warn "Just stored as Bio::Tools::HSP::HSPI";
|
|
|
296 } else {
|
|
|
297 # warn "DID NOT STORE ".ref($obj);
|
|
|
298 $self->throw( "Do not know how to store objects of type ".ref($obj) );
|
|
|
299 return undef;
|
|
|
300 }
|
|
|
301 # if( $self->{'disconnect_flag'} ) {
|
|
|
302 # warn "HERE WE ARE DISCONNECTING....";
|
|
|
303 # $self->dbc->db_handle->disconnect();
|
|
|
304 # $self->dbc->connected(0);
|
|
|
305 # warn "AND WE ARE RECONNECTING....";
|
|
|
306 # $self->dbc->connect();
|
|
|
307 # }
|
|
|
308 return $ret_value;
|
|
|
309 }
|
|
|
310
|
|
|
311 sub prepare {
|
|
|
312 my $self = shift;
|
|
|
313 # warn( "==> ", $self->dbc->dbname, " ", $self->dbc->db_handle );
|
|
|
314 #warn @_;
|
|
|
315 my $T = $self->SUPER::prepare( @_ );
|
|
|
316 # warn( "<== ", $self->dbc->dbname, " ", $self->dbc->db_handle );
|
|
|
317 return $T;
|
|
|
318 }
|
|
|
319 #----------------------------------------------------------------------
|
|
|
320
|
|
|
321 =head2 retrieve
|
|
|
322
|
|
|
323 Arg [1] :
|
|
|
324 Function :
|
|
|
325 Returntype:
|
|
|
326 Exceptions:
|
|
|
327 Caller :
|
|
|
328 Example :
|
|
|
329
|
|
|
330 =cut
|
|
|
331
|
|
|
332 sub retrieve {
|
|
|
333 my $self = shift;
|
|
|
334 my $caller = shift;
|
|
|
335 my %METHODS = qw(
|
|
|
336 Bio::Tools::Run::EnsemblSearchMulti search_multi
|
|
|
337 Bio::Search::Result::ResultI result
|
|
|
338 Bio::Search::Hit::HitI hit
|
|
|
339 Bio::Search::HSP::HSPI hsp
|
|
|
340 );
|
|
|
341 foreach my $type (keys %METHODS) {
|
|
|
342 if( UNIVERSAL::isa($caller, $type) ) {
|
|
|
343 my $method = "retrieve_$METHODS{$type}";
|
|
|
344 return $self->$method( @_ );
|
|
|
345 }
|
|
|
346 }
|
|
|
347 return undef if UNIVERSAL::isa($caller,'Bio::Tools::Run::Search');
|
|
|
348 $self->throw( "Do not know how to retrieve objects of type ".
|
|
|
349 ( ref($caller)? ref($caller) : $caller ) );
|
|
|
350 }
|
|
|
351
|
|
|
352 #----------------------------------------------------------------------
|
|
|
353
|
|
|
354 =head2 remove
|
|
|
355
|
|
|
356 Arg [1] :
|
|
|
357 Function : TODO: implement remove functions
|
|
|
358 Returntype:
|
|
|
359 Exceptions:
|
|
|
360 Caller :
|
|
|
361 Example :
|
|
|
362
|
|
|
363 =cut
|
|
|
364
|
|
|
365 sub remove {
|
|
|
366 my $self = shift;
|
|
|
367 my $obj = shift;
|
|
|
368 return 1 if $obj->isa("Bio::Tools::Run::EnsemblSearchMulti"); # Nothing to do here { return $self->remove_search_multi( @_ ); }
|
|
|
369 return 1 if $obj->isa("Bio::Search::Result::ResultI"); # Nothing to do here { return $self->remove_result( @_ ); }
|
|
|
370 return 1 if $obj->isa("Bio::Search::Hit::HitI"); # Nothing to do here { return $self->remove_hit( @_ ); }
|
|
|
371 return $self->remove_hsp( $obj ) if $obj->isa("Bio::Search::HSP::HSPI");
|
|
|
372 return undef(); # Do not know how to remove objects of this type
|
|
|
373 }
|
|
|
374 #----------------------------------------------------------------------
|
|
|
375 =head2 store_search_multi
|
|
|
376
|
|
|
377 Arg [1] : Bio::Tools::Run::EnsemblSearchMulti obj
|
|
|
378 Function : Stores the ensembl SearchMulti container object in the database
|
|
|
379 Returntype: scalar (token)
|
|
|
380 Exceptions:
|
|
|
381 Caller :
|
|
|
382 Example : my $container_token = $blast_adpt->store_ticket( $container );
|
|
|
383
|
|
|
384 =cut
|
|
|
385
|
|
|
386 sub store_search_multi{
|
|
|
387 my $self = shift;
|
|
|
388 my $search_multi = shift ||
|
|
|
389 $self->throw( "Need a Bio::Tools::Run::EnsemblSearchMulti obj" );
|
|
|
390
|
|
|
391 my $frozen = shift || $search_multi->serialise;
|
|
|
392
|
|
|
393 my $dbh = $self->dbc->db_handle;
|
|
|
394 my $ticket = $search_multi->token || $self->throw( "Bio::Tools::Run::EnsemblSearchMulti obj has no ticket" );
|
|
|
395
|
|
|
396 my $sth = $self->prepare( $SQL_SEARCH_MULTI_RETRIEVE );
|
|
|
397 my $rv = $sth->execute( $ticket ) || $self->throw( $sth->errstr );
|
|
|
398 $sth->finish;
|
|
|
399
|
|
|
400 if( $rv < 1 ){ # Insert (do first to minimise risk of race)
|
|
|
401 my $sth = $self->prepare( $SQL_SEARCH_MULTI_STORE );
|
|
|
402 $sth->execute( $frozen, $ticket ) || $self->throw( $sth->errstr );
|
|
|
403 #$search_multi->token( $self->dbh->{mysql_insertid} );
|
|
|
404 $sth->finish;
|
|
|
405 }
|
|
|
406 else{ # Update
|
|
|
407 my $sth = $self->prepare( $SQL_SEARCH_MULTI_UPDATE );
|
|
|
408 $sth->execute( $frozen, $ticket ) || $self->throw( $sth->errstr );
|
|
|
409 $sth->finish;
|
|
|
410 }
|
|
|
411 my $sth = $self->prepare('show tables'); $sth->execute(); $sth->finish;
|
|
|
412 return $search_multi->token();
|
|
|
413 }
|
|
|
414
|
|
|
415 #----------------------------------------------------------------------
|
|
|
416
|
|
|
417 =head2 retrieve_search_multi
|
|
|
418
|
|
|
419 Arg [1] :
|
|
|
420 Function :
|
|
|
421 Returntype:
|
|
|
422 Exceptions:
|
|
|
423 Caller :
|
|
|
424 Example :
|
|
|
425
|
|
|
426 =cut
|
|
|
427
|
|
|
428 sub retrieve_search_multi {
|
|
|
429 my $self = shift;
|
|
|
430 my $ticket = shift || $self->throw( "Need an EnsemblSearchMulti ticket" );
|
|
|
431
|
|
|
432 my $dbh = $self->dbc->db_handle;
|
|
|
433 warn $dbh;
|
|
|
434 warn $SQL_SEARCH_MULTI_RETRIEVE;
|
|
|
435 my $sth = $self->prepare( $SQL_SEARCH_MULTI_RETRIEVE );
|
|
|
436 warn $sth;
|
|
|
437 my $rv = $sth->execute( $ticket ) || $self->throw( $sth->errstr );
|
|
|
438 if( $rv < 1 ){ $self->throw( "Token $ticket not found" ) }
|
|
|
439 my ( $frozen ) = $sth->fetchrow_array;
|
|
|
440 $frozen || $self->throw( "Object from ticket $ticket is empty" );
|
|
|
441 $sth->finish;
|
|
|
442 return $frozen;
|
|
|
443 }
|
|
|
444
|
|
|
445
|
|
|
446
|
|
|
447 #----------------------------------------------------------------------
|
|
|
448 =head2 store_result
|
|
|
449
|
|
|
450 Arg [1] : Bio::Search::Result::EnsemblResult obj
|
|
|
451 Function : Stores the ensembl Result in the database
|
|
|
452 Returntype: scalar (token)
|
|
|
453 Exceptions:
|
|
|
454 Caller :
|
|
|
455 Example : my $result_token = $blast_adpt->store_result( $result );
|
|
|
456
|
|
|
457 =cut
|
|
|
458
|
|
|
459 sub store_result{
|
|
|
460 my $self = shift;
|
|
|
461 my $res = shift || $self->throw( "Need a Bio::Search::Result::EnsemblResult obj" );
|
|
|
462 my $frozen = shift || $res->serialise;
|
|
|
463 my $dbh = $self->dbc->db_handle;
|
|
|
464 my $sth;
|
|
|
465
|
|
|
466 my ( $id, $use_date ) = split( '!!', $res->token || '' );
|
|
|
467 $use_date ||= $self->use_date( 'RESULT' );
|
|
|
468 #my $ticket = $res->group_ticket || warn( "Result $id has no ticket" );
|
|
|
469 my $ticket = $self->ticket || warn("Result $id BlastAdaptor has no ticket");
|
|
|
470
|
|
|
471 my $rv = 0;
|
|
|
472 if( $id ){
|
|
|
473 $sth = $self->prepare( sprintf $SQL_RESULT_RETRIEVE, $use_date );
|
|
|
474 $rv = $sth->execute( $id ) || $self->throw( $sth->errstr );
|
|
|
475 $sth->finish;
|
|
|
476 }
|
|
|
477 if( $rv < 1 ){ # We have no result with this token string Insert
|
|
|
478 my $use_date = $res->use_date() || $res->use_date($self->use_date('RESULT'));
|
|
|
479 $sth = $self->prepare( sprintf $SQL_RESULT_STORE, $use_date );
|
|
|
480 $sth->execute( $frozen, $ticket ) || $self->throw( $sth->errstr );
|
|
|
481 my $id = $dbh->{mysql_insertid};
|
|
|
482 $res->token( join( '!!', $id, $use_date ) );
|
|
|
483 $sth->finish;
|
|
|
484 } else { # Update
|
|
|
485 $sth = $self->prepare( sprintf $SQL_RESULT_UPDATE, $use_date );
|
|
|
486 $sth->execute( $frozen, $ticket, $id ) || $self->throw( $sth->errstr );
|
|
|
487 $sth->finish;
|
|
|
488 }
|
|
|
489 return $res->token();
|
|
|
490 }
|
|
|
491
|
|
|
492 sub store_result_2{
|
|
|
493 my $self = shift;
|
|
|
494 my $res = shift || $self->throw( "Need a Bio::Search::Result::EnsemblResult obj" );
|
|
|
495 my $frozen = shift || $res->serialise;
|
|
|
496 my $dbh = $self->dbc->db_handle;
|
|
|
497 my $sth;
|
|
|
498
|
|
|
499 my ( $id, $use_date ) = split( '!!', $res->token || '' );
|
|
|
500 $use_date ||= $self->use_date( 'RESULT' );
|
|
|
501 #my $ticket = $res->group_ticket || warn( "Result $id has no ticket" );
|
|
|
502 my $ticket = $self->ticket || warn("Result $id BlastAdaptor has no ticket");
|
|
|
503
|
|
|
504 my $rv = 0;
|
|
|
505 if( $ticket ){
|
|
|
506 $sth = $self->prepare( sprintf $SQL_RESULT_RETRIEVE_TICKET, $use_date );
|
|
|
507 $rv = $sth->execute( $ticket ) || $self->throw( $sth->errstr );
|
|
|
508 $sth->finish;
|
|
|
509 }
|
|
|
510 if( !$rv && $id ){
|
|
|
511 $sth = $self->prepare( sprintf $SQL_RESULT_RETRIEVE, $use_date );
|
|
|
512 $rv = $sth->execute( $id ) || $self->throw( $sth->errstr );
|
|
|
513 $sth->finish;
|
|
|
514 }
|
|
|
515 if( $rv < 1 ){ # We have no result with this token string Insert
|
|
|
516 my $use_date = $res->use_date() || $res->use_date($self->use_date('RESULT'));
|
|
|
517 $sth = $self->prepare( sprintf $SQL_RESULT_STORE, $use_date );
|
|
|
518 $sth->execute( $frozen, $ticket ) || $self->throw( $sth->errstr );
|
|
|
519 my $id = $dbh->{mysql_insertid};
|
|
|
520 $res->token( join( '!!', $id, $use_date ) );
|
|
|
521 $sth->finish;
|
|
|
522 } else { # Update
|
|
|
523 $sth = $self->prepare( sprintf $SQL_RESULT_UPDATE, $use_date );
|
|
|
524 $sth->execute( $frozen, $ticket, $id ) || $self->throw( $sth->errstr );
|
|
|
525 $sth->finish;
|
|
|
526 }
|
|
|
527 return $res->token();
|
|
|
528 }
|
|
|
529
|
|
|
530 #----------------------------------------------------------------------
|
|
|
531
|
|
|
532 =head2 retrieve_result
|
|
|
533
|
|
|
534 Arg [1] :
|
|
|
535 Function :
|
|
|
536 Returntype:
|
|
|
537 Exceptions:
|
|
|
538 Caller :
|
|
|
539 Example :
|
|
|
540
|
|
|
541 =cut
|
|
|
542
|
|
|
543 sub retrieve_result{
|
|
|
544 my $self = shift;
|
|
|
545 my $token = shift || $self->throw( "Need a Hit token" );
|
|
|
546 my ( $id, $use_date ) = split( '!!',$token);
|
|
|
547 $use_date ||= '';
|
|
|
548
|
|
|
549 my $dbh = $self->dbc->db_handle;
|
|
|
550 my $sth = $self->prepare( sprintf $SQL_RESULT_RETRIEVE, $use_date );
|
|
|
551 my $rv = $sth->execute( $id ) || $self->throw( $sth->errstr );
|
|
|
552 if( $rv < 1 ){ $self->throw( "Token $id not found" ) }
|
|
|
553 my ( $frozen ) = $sth->fetchrow_array;
|
|
|
554 $frozen || $self->throw( "Object from result $id is empty" );
|
|
|
555 $sth->finish;
|
|
|
556 return $frozen;
|
|
|
557 }
|
|
|
558
|
|
|
559 #----------------------------------------------------------------------
|
|
|
560 =head2 store_hit
|
|
|
561
|
|
|
562 Arg [1] : Bio::Search::Hit::EnsemblHit obj
|
|
|
563 Function : Stores the ensembl Hit in the database
|
|
|
564 Returntype: scalar (token)
|
|
|
565 Exceptions:
|
|
|
566 Caller :
|
|
|
567 Example : my $hit_token = $blast_adpt->store_hit( $hit );
|
|
|
568
|
|
|
569 =cut
|
|
|
570
|
|
|
571 sub store_hit{
|
|
|
572 my $self = shift;
|
|
|
573 my $hit = shift ||
|
|
|
574 $self->throw( "Need a Bio::Search::Hit::EnsemblHit obj" );
|
|
|
575 my $frozen = shift || $hit->serialise;
|
|
|
576
|
|
|
577 my $dbh = $self->dbc->db_handle;
|
|
|
578
|
|
|
579 my ( $id, $use_date ) = split( '!!', $hit->token || '' );
|
|
|
580 $use_date ||= $hit->use_date() || $hit->use_date($self->use_date('HIT'));;
|
|
|
581 #my $ticket = $hit->group_ticket || warn( "Hit $id has no ticket" );
|
|
|
582 my $ticket = $self->ticket || warn("Hit $id BlastAdaptor has no ticket");
|
|
|
583
|
|
|
584 my $rv = 0;
|
|
|
585 if( $id ){
|
|
|
586 my $sth = $self->prepare( sprintf $SQL_HIT_RETRIEVE, $use_date );
|
|
|
587 $rv = $sth->execute( $id ) || $self->throw( $sth->errstr );
|
|
|
588 $sth->finish;
|
|
|
589 }
|
|
|
590 if( $rv < 1 ){ # Insert
|
|
|
591 my $sth = $self->prepare( sprintf $SQL_HIT_STORE, $use_date );
|
|
|
592 $sth->execute( $frozen, $ticket ) || $self->throw( $sth->errstr );
|
|
|
593 my $id = $dbh->{mysql_insertid};
|
|
|
594 $hit->token( join( '!!', $id, $use_date ) );
|
|
|
595 $sth->finish;
|
|
|
596 }
|
|
|
597 else{ # Update
|
|
|
598 my $sth = $self->prepare( sprintf $SQL_HIT_UPDATE, $use_date );
|
|
|
599 $sth->execute( $frozen, $ticket, $id ) || $self->throw( $sth->errstr );
|
|
|
600 $sth->finish;
|
|
|
601 }
|
|
|
602 return $hit->token();
|
|
|
603 }
|
|
|
604
|
|
|
605 #----------------------------------------------------------------------
|
|
|
606
|
|
|
607 =head2 retrieve_hit
|
|
|
608
|
|
|
609 Arg [1] :
|
|
|
610 Function :
|
|
|
611 Returntype:
|
|
|
612 Exceptions:
|
|
|
613 Caller :
|
|
|
614 Example :
|
|
|
615
|
|
|
616 =cut
|
|
|
617
|
|
|
618 sub retrieve_hit{
|
|
|
619 my $self = shift;
|
|
|
620 my $token = shift || $self->throw( "Need a Hit token" );
|
|
|
621 my ( $id, $use_date ) = split( '!!',$token);
|
|
|
622 $use_date ||= '';
|
|
|
623 my $dbh = $self->dbc->db_handle;
|
|
|
624 my $sth = $self->prepare( sprintf $SQL_HIT_RETRIEVE, $use_date );
|
|
|
625 my $rv = $sth->execute( $id ) || $self->throw( $sth->errstr );
|
|
|
626 if( $rv < 1 ){ $self->throw( "Token $token not found" ) }
|
|
|
627 my ( $frozen ) = $sth->fetchrow_array;
|
|
|
628 $frozen || $self->throw( "Object from hit $id is empty" );
|
|
|
629 $sth->finish;
|
|
|
630 return $frozen;
|
|
|
631 }
|
|
|
632
|
|
|
633 #----------------------------------------------------------------------
|
|
|
634 =head2 store_hsp
|
|
|
635
|
|
|
636 Arg [1] : Bio::Search::HSP::EnsemblHSP obj
|
|
|
637 Function : Stores the ensembl HSP in the database
|
|
|
638 Returntype:
|
|
|
639 Exceptions:
|
|
|
640 Caller :
|
|
|
641 Example :
|
|
|
642
|
|
|
643 =cut
|
|
|
644
|
|
|
645 sub store_hsp{
|
|
|
646 my $self = shift;
|
|
|
647 my $hsp = shift ||
|
|
|
648 $self->throw( "Need a Bio::Search::HSP::EnsemblHSP obj" );
|
|
|
649 my $frozen = shift || $hsp->serialise;
|
|
|
650
|
|
|
651 my $dbh = $self->dbc->db_handle;
|
|
|
652 my ( $id, $use_date ) = split( '!!', $hsp->token || '');
|
|
|
653 $use_date ||= $hsp->use_date() || $hsp->use_date($self->use_date('HSP'));
|
|
|
654
|
|
|
655 #my $ticket = $hsp->group_ticket || warn( "HSP $id has no ticket" );
|
|
|
656 my $ticket = $self->ticket || warn( "HSP $id BlastAdaptor has no ticket" );
|
|
|
657
|
|
|
658 my $chr_name = '';
|
|
|
659 my $chr_start = 0;
|
|
|
660 my $chr_end = 0;
|
|
|
661 if( my $genomic = $hsp->genomic_hit ){
|
|
|
662 $chr_name = $genomic->seq_region_name;
|
|
|
663 $chr_start = $genomic->start;
|
|
|
664 $chr_end = $genomic->end;
|
|
|
665 }
|
|
|
666 my $rv = 0;
|
|
|
667 if( $id ){
|
|
|
668 my $sth = $self->prepare( sprintf $SQL_HSP_RETRIEVE, $use_date );
|
|
|
669 $rv = $sth->execute( $id ) || $self->throw( $sth->errstr );
|
|
|
670 $sth->finish;
|
|
|
671 }
|
|
|
672 if( $rv < 1 ){ # Insert
|
|
|
673 my $use_date = $hsp->use_date() || $hsp->use_date($self->use_date('HSP'));
|
|
|
674 my $sth = $self->prepare( 'show tables' ); $sth->execute(); $sth->finish();
|
|
|
675 $sth = $self->prepare( sprintf $SQL_HSP_STORE, $use_date );
|
|
|
676 my @bound = ( $frozen, $ticket, $chr_name, $chr_start, $chr_end );
|
|
|
677 $sth->execute( @bound ) || $self->throw( $sth->errstr );
|
|
|
678 my $id = $dbh->{mysql_insertid};
|
|
|
679 $hsp->token( join( '!!', $id, $use_date ) );
|
|
|
680 $sth->finish;
|
|
|
681 }
|
|
|
682 else{ # Update
|
|
|
683 my $sth = $self->prepare( sprintf $SQL_HSP_UPDATE, $use_date );
|
|
|
684 my @bound = ( $frozen, $ticket, $chr_name, $chr_start, $chr_end, $id );
|
|
|
685 $sth->execute( @bound ) || $self->throw( $sth->errstr );
|
|
|
686 $sth->finish;
|
|
|
687 }
|
|
|
688 return $hsp->token();
|
|
|
689 }
|
|
|
690
|
|
|
691 #----------------------------------------------------------------------
|
|
|
692
|
|
|
693 =head2 retrieve_hsp
|
|
|
694
|
|
|
695 Arg [1] :
|
|
|
696 Function :
|
|
|
697 Returntype:
|
|
|
698 Exceptions:
|
|
|
699 Caller :
|
|
|
700 Example :
|
|
|
701
|
|
|
702 =cut
|
|
|
703
|
|
|
704 sub retrieve_hsp{
|
|
|
705 my $self = shift;
|
|
|
706 my $token = shift || $self->throw( "Need an HSP token" );
|
|
|
707 my ( $id, $use_date ) = split( '!!',$token);
|
|
|
708 $use_date ||= '';
|
|
|
709 my $dbh = $self->dbc->db_handle;
|
|
|
710 my $sth = $self->prepare( sprintf $SQL_HSP_RETRIEVE, $use_date );
|
|
|
711 my $rv = $sth->execute( $id ) || $self->throw( $sth->errstr );
|
|
|
712 if( $rv < 1 ){ $self->throw( "Token $token not found" ) }
|
|
|
713 my ( $frozen ) = $sth->fetchrow_array;
|
|
|
714 $frozen || $self->throw( "Object from hsp $id is empty" );
|
|
|
715 $sth->finish;
|
|
|
716 return $frozen;
|
|
|
717 }
|
|
|
718
|
|
|
719 #----------------------------------------------------------------------
|
|
|
720
|
|
|
721 =head2 remove_hsp
|
|
|
722
|
|
|
723 Arg [1] : $hsp object to be removed
|
|
|
724 Function : 'removes' hsp from e.g. contigview by setting chr fields
|
|
|
725 to null
|
|
|
726 Returntype:
|
|
|
727 Exceptions:
|
|
|
728 Caller : $self->remove
|
|
|
729 Example :
|
|
|
730
|
|
|
731 =cut
|
|
|
732
|
|
|
733 sub remove_hsp {
|
|
|
734 my $self = shift;
|
|
|
735 my $hsp = shift ||
|
|
|
736 $self->throw( "Need a Bio::Search::HSP::EnsemblHSP obj" );
|
|
|
737
|
|
|
738 my $dbh = $self->dbc->db_handle;
|
|
|
739
|
|
|
740 my ( $id, $use_date ) = split( '!!', $hsp->token || '');
|
|
|
741 $use_date ||= $hsp->use_date() || $hsp->use_date($self->use_date('HSP'));
|
|
|
742
|
|
|
743 my $sth = $self->prepare( sprintf $SQL_HSP_REMOVE, $use_date );
|
|
|
744 my @bound = ( $id );
|
|
|
745 my $rv = $sth->execute( @bound ) || $self->throw( $sth->errstr );
|
|
|
746 $sth->finish;
|
|
|
747 return 1;
|
|
|
748 }
|
|
|
749
|
|
|
750
|
|
|
751
|
|
|
752 #----------------------------------------------------------------------
|
|
|
753
|
|
|
754 =head2 get_all_HSPs
|
|
|
755
|
|
|
756 Arg [1] :
|
|
|
757 Function :
|
|
|
758 Returntype:
|
|
|
759 Exceptions:
|
|
|
760 Caller :
|
|
|
761 Example :
|
|
|
762
|
|
|
763 =cut
|
|
|
764
|
|
|
765 sub get_all_HSPs {
|
|
|
766 my $self = shift;
|
|
|
767 my $ticket = shift || $self->throw( "Need a search ticket!");
|
|
|
768 my $chr_name = shift || undef;
|
|
|
769 my $chr_start = shift || undef;
|
|
|
770 my $chr_end = shift || undef;
|
|
|
771 my ( $id, $use_date ) = split( '!!', $ticket );
|
|
|
772 $use_date ||= '';
|
|
|
773
|
|
|
774 my $SQL = qq(
|
|
|
775 SELECT object, hsp_id
|
|
|
776 FROM blast_hsp%s
|
|
|
777 WHERE ticket = ? );
|
|
|
778
|
|
|
779 my $CHR_SQL = qq(
|
|
|
780 AND chr_name = ? );
|
|
|
781
|
|
|
782 my $RANGE_SQL = qq(
|
|
|
783 AND chr_start <= ?
|
|
|
784 AND chr_end >= ? );
|
|
|
785
|
|
|
786 my $q = sprintf( $SQL, $use_date );
|
|
|
787 my @binded = ( $id );
|
|
|
788
|
|
|
789 if( $chr_name ){
|
|
|
790 $q .= $CHR_SQL;
|
|
|
791 push @binded, $chr_name;
|
|
|
792
|
|
|
793 if( $chr_start && $chr_end ){
|
|
|
794 $q .= $RANGE_SQL;
|
|
|
795 push @binded, $chr_end, $chr_start;
|
|
|
796 }
|
|
|
797 }
|
|
|
798 my $sth = $self->dbc->db_handle->prepare($q);
|
|
|
799 my $rv = $sth->execute( @binded ) || $self->throw( $sth->errstr );
|
|
|
800
|
|
|
801 my @hsps = ();
|
|
|
802 foreach my $row( @{$sth->fetchall_arrayref()} ){
|
|
|
803 # Retrieve HSP and reset token
|
|
|
804 my $hsp = thaw( $row->[0] );
|
|
|
805 my $hsp_id = $row->[1];
|
|
|
806 $hsp->token( join( '!!', $hsp_id, $use_date ) );
|
|
|
807 push @hsps, $hsp;
|
|
|
808 }
|
|
|
809 $sth->finish;
|
|
|
810 return [@hsps];
|
|
|
811 }
|
|
|
812
|
|
|
813
|
|
|
814
|
|
|
815 #----------------------------------------------------------------------
|
|
|
816
|
|
|
817 =head2 get_all_SearchFeatures
|
|
|
818
|
|
|
819 Arg [1] :
|
|
|
820 Function :
|
|
|
821 Returntype:
|
|
|
822 Exceptions:
|
|
|
823 Caller :
|
|
|
824 Example :
|
|
|
825
|
|
|
826 =cut
|
|
|
827
|
|
|
828 sub get_all_SearchFeatures {
|
|
|
829 my $self = shift;
|
|
|
830 my $hsps = $self->get_all_HSPs(@_);
|
|
|
831 my $ticket = shift;
|
|
|
832
|
|
|
833 $self->dynamic_use( ref($hsps->[0] ) );
|
|
|
834 my @feats = ();
|
|
|
835 foreach my $hsp( @$hsps ){
|
|
|
836 my $base_align = $hsp->genomic_hit || next;
|
|
|
837
|
|
|
838 ( $ticket ) = split( "!!", $ticket );
|
|
|
839 my $hsp_id = join( "!!", $ticket, $hsp->token );
|
|
|
840
|
|
|
841 $base_align->hseqname( join( ":", $base_align->hseqname, $hsp_id ) );
|
|
|
842 push @feats, $base_align;
|
|
|
843 }
|
|
|
844 return [ @feats ];
|
|
|
845 }
|
|
|
846
|
|
|
847 sub dynamic_use {
|
|
|
848 my( $self, $classname ) = @_;
|
|
|
849 my( $parent_namespace, $module ) = $classname =~/^(.*::)(.*?)$/;
|
|
|
850 no strict 'refs';
|
|
|
851 return 1 if $parent_namespace->{$module.'::'}; # return if already used
|
|
|
852 eval "require $classname";
|
|
|
853 if($@) {
|
|
|
854 warn "DrawableContainer: failed to use $classname\nDrawableContainer: $@";
|
|
|
855 return 0;
|
|
|
856 }
|
|
|
857 $classname->import();
|
|
|
858 return 1;
|
|
|
859 }
|
|
|
860
|
|
|
861 #----------------------------------------------------------------------
|
|
|
862
|
|
|
863 =head2 use_date
|
|
|
864
|
|
|
865 Arg [1] :
|
|
|
866 Function :
|
|
|
867 Returntype:
|
|
|
868 Exceptions:
|
|
|
869 Caller :
|
|
|
870 Example :
|
|
|
871
|
|
|
872 =cut
|
|
|
873
|
|
|
874 my %valid_table_types = ( HIT=>1, HSP=>1, RESULT=>1 );
|
|
|
875 sub use_date {
|
|
|
876 my $key = '_current_table';
|
|
|
877 my $self = shift;
|
|
|
878 my $type = uc( shift );
|
|
|
879 #warn "$self --- $key --- $type $self";
|
|
|
880 $valid_table_types{$type} ||
|
|
|
881 $self->throw( "Need a table type (Result, Hit or HSP)" );
|
|
|
882
|
|
|
883 $self->{$key} ||= {};
|
|
|
884 if( ! $self->{$key}->{$type} ){
|
|
|
885 my $sth = $self->dbc->db_handle->prepare( "
|
|
|
886 SELECT table_type, use_date
|
|
|
887 FROM blast_table_log
|
|
|
888 WHERE table_status = 'CURRENT'
|
|
|
889 ORDER BY use_date ASC" );
|
|
|
890 #warn "prepare... $sth";
|
|
|
891 #warn $SQL_SELECT_TABLE_LOG_CURRENT;
|
|
|
892 #warn $type;
|
|
|
893 my $rv = $sth->execute();# $type );
|
|
|
894 #warn $rv;
|
|
|
895 unless( $rv ) {
|
|
|
896 $sth->finish;
|
|
|
897 warn( $sth->errstr );
|
|
|
898 return;
|
|
|
899 }
|
|
|
900 #warn "exec...";
|
|
|
901 foreach my $r (@{ $sth->fetchall_arrayref }) {
|
|
|
902 my $date = $r->[1];
|
|
|
903 $date =~ s/-//g;
|
|
|
904 $self->{$key}->{$r->[0]} = $date;
|
|
|
905 #warn "$r->[0] ---> $r->[1] ---> $date";
|
|
|
906 }
|
|
|
907 # $rv > 0 || ( warn( "No current $type table found" ) && return );
|
|
|
908 $sth->finish;
|
|
|
909 #warn "end of finish...";
|
|
|
910 }
|
|
|
911 return $self->{$key}->{$type};
|
|
|
912 }
|
|
|
913
|
|
|
914
|
|
|
915
|
|
|
916 #----------------------------------------------------------------------
|
|
|
917
|
|
|
918 =head2 clean_blast_database
|
|
|
919
|
|
|
920 Arg [1] : int $days
|
|
|
921 Function : Removes blast tickets older than $days days
|
|
|
922 Returntype:
|
|
|
923 Exceptions: SQL errors
|
|
|
924 Caller :
|
|
|
925 Example : $ba->clean_blast_database(14)
|
|
|
926
|
|
|
927 =cut
|
|
|
928
|
|
|
929 sub clean_blast_database{
|
|
|
930 my $self = shift;
|
|
|
931 my $days = shift || $self->throw( "Missing arg: number of days" );
|
|
|
932 $days =~ /\D/ && $self->throw( "Bad arg: number of days $days not int" );
|
|
|
933 my $dbh = $self->dbc->db_handle;
|
|
|
934
|
|
|
935 # Get list of tickets > $days days old
|
|
|
936 my $q = qq(
|
|
|
937 SELECT ticket_id
|
|
|
938 FROM blast_ticket
|
|
|
939 WHERE update_time < SUBDATE( NOW(), INTERVAL $days DAY ) );
|
|
|
940
|
|
|
941 my $sth = $self->dbc->db_handle->prepare($q);
|
|
|
942 my $rv = $sth->execute() || $self->throw( $sth->errstr );
|
|
|
943 my $res = $sth->fetchall_arrayref;
|
|
|
944 $sth->finish;
|
|
|
945
|
|
|
946 # Delete result and ticket rows associated with old tickets
|
|
|
947 my $q_del_tmpl = qq(
|
|
|
948 DELETE
|
|
|
949 FROM blast_ticket
|
|
|
950 WHERE ticket_id = %s);
|
|
|
951
|
|
|
952 my $c = 0;
|
|
|
953 foreach my $row( @$res ){
|
|
|
954 my $ticket_id = $row->[0];
|
|
|
955 $c++;
|
|
|
956 my $q_del = sprintf( $q_del_tmpl, $ticket_id );
|
|
|
957 my $sth = $self->dbc->db_handle->prepare($q_del);
|
|
|
958 my $rv = $sth->execute() || $self->throw( $sth->errstr );
|
|
|
959 }
|
|
|
960 warn "Purging $days days: Deleted $c rows\n";
|
|
|
961
|
|
|
962 # Drop daily Result, Hit and HSP tables not updated within $days days
|
|
|
963 my $q_find = 'show table status like ?';
|
|
|
964 my $sth2 = $self->prepare( $q_find );
|
|
|
965 $sth2->execute( "blast_result%" ) || $self->throw( $sth2->errstr );
|
|
|
966 my $res_res = $sth2->fetchall_arrayref();
|
|
|
967 $sth2->execute( "blast_hit%" ) || $self->throw( $sth2->errstr );
|
|
|
968 my $hit_res = $sth2->fetchall_arrayref();
|
|
|
969 $sth2->execute( "blast_hsp%" ) || $self->throw( $sth2->errstr );
|
|
|
970 my $hsp_res = $sth2->fetchall_arrayref();
|
|
|
971
|
|
|
972 my @deletable_hit_tables;
|
|
|
973 foreach my $row( @$res_res, @$hit_res, @$hsp_res ){
|
|
|
974 my $table_name = $row->[0]; ## table name
|
|
|
975 my $num_rows = $row->[4]; ## # Rows...
|
|
|
976 my $update_time = $row->[12]; ## update time --- Should be a string like 2003-08-15 10:36:56
|
|
|
977 next unless $update_time; #cope with an occasional innodb table that has no update time
|
|
|
978 my @time = split( /[-:\s]/, $update_time );
|
|
|
979
|
|
|
980 my $epoch_then = timelocal( $time[5], $time[4], $time[3],
|
|
|
981 $time[2], $time[1]-1, $time[0] - 1900 );
|
|
|
982 my $secs_old = time() - $epoch_then;
|
|
|
983 my $days_old = $secs_old / ( 60 * 60 * 24 );
|
|
|
984 if( $days_old > $days ){
|
|
|
985 warn( "Dropping table $table_name: $num_rows rows\n" );
|
|
|
986 my $sth_drop = $self->prepare( "DROP table $table_name" );
|
|
|
987 my $sth_log = $self->prepare( $SQL_TABLE_LOG_UPDATE );
|
|
|
988 $sth_drop->execute || $self->throw( $sth_drop->errstr );
|
|
|
989 my( $se,$mi,$hr,$da,$mo,$yr ) = (localtime)[0,1,2,3,4,5];
|
|
|
990 my $now = sprintf( "%4d-%2d-%2d %2d:%2d:%2d",
|
|
|
991 $yr+1900,$mo+1,$da,$hr,$mi,$se );
|
|
|
992 $sth_log->execute
|
|
|
993 ('DELETED',$now,$num_rows,$table_name) ||
|
|
|
994 $self->throw( $sth_log->errstr );
|
|
|
995 }
|
|
|
996 }
|
|
|
997
|
|
|
998 return 1;
|
|
|
999 }
|
|
|
1000
|
|
|
1001 #----------------------------------------------------------------------
|
|
|
1002
|
|
|
1003 =head2 create_tables
|
|
|
1004
|
|
|
1005 Arg [1] : none
|
|
|
1006 Function : Creates the blast_ticket and blast_table_log
|
|
|
1007 tables in the database indicated by the database handle.
|
|
|
1008 Checks first to make sure they do not exist
|
|
|
1009 Returntype: boolean
|
|
|
1010 Exceptions:
|
|
|
1011 Caller :
|
|
|
1012 Example :
|
|
|
1013
|
|
|
1014 =cut
|
|
|
1015
|
|
|
1016 sub create_tables {
|
|
|
1017 my $self = shift;
|
|
|
1018 my $dbh = $self->dbc->db_handle;
|
|
|
1019
|
|
|
1020 # Get list of existing tables in database
|
|
|
1021 my $q = 'show tables like ?';
|
|
|
1022 my $sth = $self->prepare( $q );
|
|
|
1023 my $rv_tck = $sth->execute("blast_ticket") || $self->throw($sth->errstr);
|
|
|
1024 my $rv_log = $sth->execute("blast_table_log" )|| $self->throw($sth->errstr);
|
|
|
1025 $sth->finish;
|
|
|
1026
|
|
|
1027 if( $rv_tck == 0 ){
|
|
|
1028 warn( "Creating blast_ticket table\n" );
|
|
|
1029 my $sth = $self->prepare( $SQL_CREATE_TICKET );
|
|
|
1030 my $rv = $sth->execute() || $self->throw( $sth->errstr );
|
|
|
1031 $sth->finish;
|
|
|
1032 }
|
|
|
1033 else{ warn( "blast_ticket table already exists\n" ) }
|
|
|
1034
|
|
|
1035 if( $rv_log == 0 ){
|
|
|
1036 warn( "Creating blast_result table\n" );
|
|
|
1037 my $sth = $self->prepare( $SQL_CREATE_TABLE_LOG );
|
|
|
1038 my $rv = $sth->execute() || $self->throw( $sth->errstr );
|
|
|
1039 $sth->finish;
|
|
|
1040 }
|
|
|
1041 else{ warn( "blast_table_log table already exists\n" ) }
|
|
|
1042
|
|
|
1043 return 1;
|
|
|
1044 }
|
|
|
1045
|
|
|
1046 #----------------------------------------------------------------------
|
|
|
1047
|
|
|
1048 =head2 rotate_daily_tables
|
|
|
1049
|
|
|
1050 Arg [1] : none
|
|
|
1051 Function : Creates the daily blast_result{date}, blast_hit{date}
|
|
|
1052 and blast_hsp{date} tables in the database indicated by
|
|
|
1053 the database handle.
|
|
|
1054 Checks first to make sure they do not exist.
|
|
|
1055 Sets the new table to 'CURRENT' in the blast_table_log.
|
|
|
1056 Sets the previous 'CURRENT' table to filled.
|
|
|
1057 Returntype: boolean
|
|
|
1058 Exceptions:
|
|
|
1059 Caller :
|
|
|
1060 Example :
|
|
|
1061
|
|
|
1062 =cut
|
|
|
1063
|
|
|
1064 sub rotate_daily_tables {
|
|
|
1065 my $self = shift;
|
|
|
1066 my $dbh = $self->dbc->db_handle;
|
|
|
1067
|
|
|
1068 # Get date
|
|
|
1069 my( $day, $month, $year ) = (localtime)[3,4,5];
|
|
|
1070 my $date = sprintf( "%04d%02d%02d", $year+1900, $month+1, $day );
|
|
|
1071
|
|
|
1072 my $res_table = "blast_result$date";
|
|
|
1073 my $hit_table = "blast_hit$date";
|
|
|
1074 my $hsp_table = "blast_hsp$date";
|
|
|
1075
|
|
|
1076 # Get list of existing tables in database
|
|
|
1077 my $q = 'show table status like ?';
|
|
|
1078 my $sth = $self->prepare( $q );
|
|
|
1079 my $rv_res = $sth->execute($res_table) || $self->throw($sth->errstr);
|
|
|
1080 my $rv_hit = $sth->execute($hit_table) || $self->throw($sth->errstr);
|
|
|
1081 my $rv_hsp = $sth->execute($hsp_table) || $self->throw($sth->errstr);
|
|
|
1082 $sth->finish;
|
|
|
1083
|
|
|
1084 if( $rv_res == 0 ){
|
|
|
1085 warn( "Creating today's $res_table table\n" );
|
|
|
1086
|
|
|
1087 # Create new table
|
|
|
1088 my $q = sprintf($SQL_CREATE_DAILY_RESULT, $res_table);
|
|
|
1089 my $sth1 = $self->prepare( $q );
|
|
|
1090 my $rv = $sth1->execute() || $self->throw( $sth1->errstr );
|
|
|
1091
|
|
|
1092 # Flip current table in blast_table_tog
|
|
|
1093 my $last_date = $self->use_date( "RESULT" ) || '';
|
|
|
1094 my $sth2 = $self->prepare( $SQL_TABLE_LOG_INSERT );
|
|
|
1095 my $sth3 = $self->prepare( $SQL_TABLE_LOG_UPDATE );
|
|
|
1096 $sth2->execute( "$res_table",'CURRENT','RESULT',$date )
|
|
|
1097 || die( $self->throw( $sth2->errstr ) );
|
|
|
1098 $sth3->execute( 'FILLED','0',0,"blast_result$last_date")
|
|
|
1099 || die( $self->throw( $sth3->errstr ) );
|
|
|
1100 $sth1->finish();
|
|
|
1101 $sth2->finish();
|
|
|
1102 $sth3->finish();
|
|
|
1103 }
|
|
|
1104 else{ warn( "Today's $res_table table already exists\n" ) }
|
|
|
1105
|
|
|
1106 if( $rv_hit == 0 ){
|
|
|
1107 warn( "Creating today's $hit_table table\n" );
|
|
|
1108
|
|
|
1109 # Create new table
|
|
|
1110 my $q = sprintf($SQL_CREATE_DAILY_HIT, $hit_table);
|
|
|
1111 my $sth1 = $self->prepare( $q );
|
|
|
1112 my $rv = $sth1->execute() || $self->throw( $sth1->errstr );
|
|
|
1113
|
|
|
1114 # Flip current table in blast_table_tog
|
|
|
1115 my $last_date = $self->use_date( "HIT" ) || '';
|
|
|
1116 my $sth2 = $self->prepare( $SQL_TABLE_LOG_INSERT );
|
|
|
1117 my $sth3 = $self->prepare( $SQL_TABLE_LOG_UPDATE );
|
|
|
1118 $sth2->execute( "$hit_table",'CURRENT','HIT',$date )
|
|
|
1119 || die( $self->throw( $sth2->errstr ) );
|
|
|
1120 $sth3->execute( 'FILLED','0',0,"blast_hit$last_date")
|
|
|
1121 || die( $self->throw( $sth3->errstr ) );
|
|
|
1122 $sth1->finish();
|
|
|
1123 $sth2->finish();
|
|
|
1124 $sth3->finish();
|
|
|
1125 }
|
|
|
1126 else{ warn( "Today's $hit_table table already exists\n" ) }
|
|
|
1127
|
|
|
1128 if( $rv_hsp == 0 ){
|
|
|
1129 warn( "Creating today's $hsp_table table\n" );
|
|
|
1130
|
|
|
1131 # Create new table
|
|
|
1132 my $q = sprintf($SQL_CREATE_DAILY_HSP, $hsp_table );
|
|
|
1133 my $sth1 = $self->prepare( $q );
|
|
|
1134 my $rv = $sth1->execute() || $self->throw( $sth1->errstr );
|
|
|
1135
|
|
|
1136 # Flip current table in blast_table_tog
|
|
|
1137 my $last_date = $self->use_date( "HSP" ) || '';
|
|
|
1138 my $sth2 = $self->prepare( $SQL_TABLE_LOG_INSERT );
|
|
|
1139 my $sth3 = $self->prepare( $SQL_TABLE_LOG_UPDATE );
|
|
|
1140 $sth2->execute( "$hsp_table",'CURRENT','HSP',$date )
|
|
|
1141 || die( $self->throw( $sth2->errstr ) );
|
|
|
1142 $sth3->execute( 'FILLED','0',0,"blast_hsp$last_date")
|
|
|
1143 || die( $self->throw( $sth3->errstr ) );
|
|
|
1144 $sth1->finish();
|
|
|
1145 $sth2->finish();
|
|
|
1146 $sth3->finish();
|
|
|
1147 }
|
|
|
1148 else{ warn( "Today's $hsp_table table already exists\n" ) }
|
|
|
1149 return 1;
|
|
|
1150 }
|
|
|
1151
|
|
|
1152 #----------------------------------------------------------------------
|
|
|
1153
|
|
|
1154
|
|
|
1155 =head2 cleanup_processes
|
|
|
1156
|
|
|
1157 Arg [1] : none
|
|
|
1158 Function : Kills any sleeping processes older that 1000
|
|
|
1159 Returntype: boolean
|
|
|
1160 Exceptions:
|
|
|
1161 Caller :
|
|
|
1162 Example :
|
|
|
1163
|
|
|
1164 =cut
|
|
|
1165
|
|
|
1166 sub cleanup_processes {
|
|
|
1167 my $self = shift;
|
|
|
1168 my $dbh = $self->dbc->db_handle;
|
|
|
1169 my $sth = $self->prepare( 'show processlist' );
|
|
|
1170 my $kill_sth = $self->prepare('kill ?');
|
|
|
1171 $sth->execute;
|
|
|
1172 my $res = $sth->fetchall_arrayref([0,3,4,5]);
|
|
|
1173 my $c = 0;
|
|
|
1174 foreach my $ps (@$res) {
|
|
|
1175 my ($pid,$db,$stat,$time) = @$ps;
|
|
|
1176 if ($db eq 'ensembl_blast') {
|
|
|
1177 if ( ($stat eq 'Sleep') && ($time > 1000) ) {
|
|
|
1178 $kill_sth->execute($pid);
|
|
|
1179 $c++;
|
|
|
1180 }
|
|
|
1181 }
|
|
|
1182 }
|
|
|
1183 warn "Killed $c processes";
|
|
|
1184 return 1;
|
|
|
1185 }
|
|
|
1186
|
|
|
1187
|
|
|
1188
|
|
|
1189
|
|
|
1190 1;
|
