Mercurial > repos > mahtabm > ensembl

comparison variant_effect_predictor/Bio/EnsEMBL/External/BlastAdaptor.pm @ 0:1f6dce3d34e0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Uploaded
author mahtabm
date Thu, 11 Apr 2013 02:01:53 -0400
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:1f6dce3d34e0
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;