Raw content of Bio::EnsEMBL::External::BlastAdaptor =head1 LICENSE Copyright (c) 1999-2009 The European Bioinformatics Institute and Genome Research Limited. All rights reserved. This software is distributed under a modified Apache license. For license details, please see /info/about/code_licence.html =head1 CONTACT Please email comments or questions to the public Ensembl developers list at <ensembl-dev@ebi.ac.uk>. Questions may also be sent to the Ensembl help desk at <helpdesk@ensembl.org>. =cut package Bio::EnsEMBL::External::BlastAdaptor; use strict; use DBI; use Storable qw(freeze thaw); use Data::Dumper qw( Dumper ); use Time::Local; use vars qw(@ISA); use Bio::EnsEMBL::DBSQL::BaseAdaptor; use Bio::EnsEMBL::DBSQL::DBConnection; use Bio::Search::HSP::EnsemblHSP; # This is a web module @ISA = qw( Bio::EnsEMBL::DBSQL::BaseAdaptor ); #@ISA = qw( Bio::EnsEMBL::DBSQL::DBAdaptor ); #---------------------------------------------------------------------- # Define SQL #--- CREATE TABLES --- our $SQL_CREATE_TICKET = " CREATE TABLE blast_ticket ( ticket_id int(10) unsigned NOT NULL auto_increment, create_time datetime NOT NULL default '0000-00-00 00:00:00', update_time datetime NOT NULL default '0000-00-00 00:00:00', ticket varchar(32) NOT NULL default '', status enum('CURRENT','DELETED') NOT NULL default 'CURRENT', object longblob, PRIMARY KEY (ticket_id), UNIQUE KEY ticket (ticket), KEY create_time (create_time), KEY update_time (update_time) ) TYPE=MyISAM"; our $SQL_CREATE_TABLE_LOG = " CREATE TABLE blast_table_log ( table_id int(10) unsigned NOT NULL auto_increment, table_name varchar(32), table_type enum('TICKET','RESULT','HIT','HSP') default NULL, table_status enum('CURRENT','FILLED','DELETED') default NULL, use_date date default NULL, create_time datetime default NULL, delete_time datetime default NULL, num_objects int(10) default NULL, PRIMARY KEY (table_id), KEY table_name (table_name), KEY table_type (table_type), KEY use_date (use_date), KEY table_status (table_status) ) TYPE=MyISAM"; our $SQL_CREATE_DAILY_RESULT = " CREATE TABLE %s ( result_id int(10) unsigned NOT NULL auto_increment, ticket varchar(32) default NULL, object longblob, PRIMARY KEY (result_id), KEY ticket (ticket) ) TYPE=MyISAM"; our $SQL_CREATE_DAILY_HIT = " CREATE TABLE %s ( hit_id int(10) unsigned NOT NULL auto_increment, ticket varchar(32) default NULL, object longblob, PRIMARY KEY (hit_id), KEY ticket (ticket) ) TYPE=MyISAM"; our $SQL_CREATE_DAILY_HSP = " CREATE TABLE %s ( hsp_id int(10) unsigned NOT NULL auto_increment, ticket varchar(32) default NULL, object longblob, chr_name varchar(32) default NULL, chr_start int(10) unsigned default NULL, chr_end int(10) unsigned default NULL, PRIMARY KEY (hsp_id), KEY ticket (ticket) ) TYPE=MyISAM MAX_ROWS=705032704 AVG_ROW_LENGTH=4000"; #--- TABLE LOG --- our $SQL_SELECT_TABLE_LOG_CURRENT = " SELECT use_date FROM blast_table_log WHERE table_type = ? AND table_status = 'CURRENT' ORDER BY use_date DESC"; our $SQL_TABLE_LOG_INSERT = " INSERT into blast_table_log ( table_name, table_status, table_type, use_date, create_time) VALUES ( ?, ?, ?, ?, NOW() )"; our $SQL_TABLE_LOG_UPDATE = " UPDATE blast_table_log SET table_status = ?, delete_time = ?, num_objects = ? WHERE table_name = ?"; #--- TICKETS --- our $SQL_SEARCH_MULTI_STORE = " INSERT INTO blast_ticket ( create_time, update_time, object, ticket ) VALUES ( NOW(), NOW(), ? , ? )"; our $SQL_SEARCH_MULTI_UPDATE = " UPDATE blast_ticket SET object = ?, update_time = NOW() WHERE ticket = ?"; our $SQL_SEARCH_MULTI_RETRIEVE = " SELECT object FROM blast_ticket WHERE ticket = ? "; #--- RESULTS --- our $SQL_RESULT_STORE = " INSERT INTO blast_result%s ( object, ticket ) VALUES ( ? , ? )"; our $SQL_RESULT_UPDATE = " UPDATE blast_result%s SET object = ?, ticket = ? WHERE result_id = ?"; our $SQL_RESULT_RETRIEVE = " SELECT object FROM blast_result%s WHERE result_id = ? "; our $SQL_RESULT_RETRIEVE_TICKET = " SELECT object FROM blast_result%s WHERE ticket = ? "; #--- HITS --- our $SQL_HIT_STORE = " INSERT INTO blast_hit%s ( object, ticket ) VALUES ( ? , ? )"; our $SQL_HIT_UPDATE = " UPDATE blast_hit%s SET object = ?, ticket = ? WHERE hit_id = ?"; our $SQL_HIT_RETRIEVE = " SELECT object FROM blast_hit%s WHERE hit_id = ? "; #--- HSPS --- our $SQL_HSP_STORE = " INSERT INTO blast_hsp%s ( object, ticket, chr_name, chr_start, chr_end ) VALUES ( ? , ? , ? , ? , ? )"; our $SQL_HSP_UPDATE = " UPDATE blast_hsp%s SET object = ?, ticket = ?, chr_name = ?, chr_start = ?, chr_end = ? WHERE hsp_id = ?"; our $SQL_HSP_RETRIEVE = " SELECT object FROM blast_hsp%s WHERE hsp_id = ? "; our $SQL_HSP_REMOVE = " UPDATE blast_hsp%s SET chr_name = NULL, chr_start = NULL, chr_end = NULL WHERE hsp_id = ?"; #=head2 new # # Arg [1] : # Function : # Returntype: # Exceptions: # Caller : # Example : # #=cut # # sub new { my $caller = shift; #warn "DB - @_"; my $connection = Bio::EnsEMBL::DBSQL::DBConnection->new(@_); my $self = $caller->SUPER::new($connection); $self->{'disconnect_flag'} = 1; return $self; } sub new_fast{ my ($caller,$connection) = @_; my $self = $caller->SUPER::new($connection); $self->{'disconnect_flag'} = 1; return $self; } #---------------------------------------------------------------------- sub species { my ($self, $arg ) = @_; ( defined $arg ) && ( $self->{_species} = $arg ); $self->{_species}; } #---------------------------------------------------------------------- =head2 ticket Arg [1] : string ticket (optional) Function : Get/get the blast ticket attribute Returntype: string ticket Exceptions: Caller : Example : =cut sub ticket{ my $key = "_ticket"; my $self = shift; if( @_ ){ $self->{$key} = shift } return $self->{$key}; } #---------------------------------------------------------------------- =head2 store Arg [1] : Function : Returntype: Exceptions: Caller : Example : =cut sub store { my $self = shift; my $obj = shift; my $ret_value = undef; if( $obj->isa("Bio::Tools::Run::SearchMulti") ) { $ret_value = $self->store_search_multi( $obj, @_ ); # warn "Just stored as Bio::Tools::Run::SearchMulti"; } elsif( $obj->isa( "Bio::Search::Result::ResultI" ) ) { $ret_value = $self->store_result( $obj, @_ ); # warn "Just stored as Bio::Tools::Result::ResultI"; } elsif( $obj->isa( "Bio::Search::Hit::HitI" ) ) { $ret_value = $self->store_hit( $obj, @_ ); # warn "Just stored as Bio::Tools::Hit::HitI"; } elsif( $obj->isa( "Bio::Search::HSP::HSPI" ) ) { $ret_value = $self->store_hsp( $obj, @_ ); # warn "Just stored as Bio::Tools::HSP::HSPI"; } else { # warn "DID NOT STORE ".ref($obj); $self->throw( "Do not know how to store objects of type ".ref($obj) ); return undef; } # if( $self->{'disconnect_flag'} ) { # warn "HERE WE ARE DISCONNECTING...."; # $self->dbc->db_handle->disconnect(); # $self->dbc->connected(0); # warn "AND WE ARE RECONNECTING...."; # $self->dbc->connect(); # } return $ret_value; } sub prepare { my $self = shift; # warn( "==> ", $self->dbc->dbname, " ", $self->dbc->db_handle ); #warn @_; my $T = $self->SUPER::prepare( @_ ); # warn( "<== ", $self->dbc->dbname, " ", $self->dbc->db_handle ); return $T; } #---------------------------------------------------------------------- =head2 retrieve Arg [1] : Function : Returntype: Exceptions: Caller : Example : =cut sub retrieve { my $self = shift; my $caller = shift; my %METHODS = qw( Bio::Tools::Run::EnsemblSearchMulti search_multi Bio::Search::Result::ResultI result Bio::Search::Hit::HitI hit Bio::Search::HSP::HSPI hsp ); foreach my $type (keys %METHODS) { if( UNIVERSAL::isa($caller, $type) ) { my $method = "retrieve_$METHODS{$type}"; return $self->$method( @_ ); } } return undef if UNIVERSAL::isa($caller,'Bio::Tools::Run::Search'); $self->throw( "Do not know how to retrieve objects of type ". ( ref($caller)? ref($caller) : $caller ) ); } #---------------------------------------------------------------------- =head2 remove Arg [1] : Function : TODO: implement remove functions Returntype: Exceptions: Caller : Example : =cut sub remove { my $self = shift; my $obj = shift; return 1 if $obj->isa("Bio::Tools::Run::EnsemblSearchMulti"); # Nothing to do here { return $self->remove_search_multi( @_ ); } return 1 if $obj->isa("Bio::Search::Result::ResultI"); # Nothing to do here { return $self->remove_result( @_ ); } return 1 if $obj->isa("Bio::Search::Hit::HitI"); # Nothing to do here { return $self->remove_hit( @_ ); } return $self->remove_hsp( $obj ) if $obj->isa("Bio::Search::HSP::HSPI"); return undef(); # Do not know how to remove objects of this type } #---------------------------------------------------------------------- =head2 store_search_multi Arg [1] : Bio::Tools::Run::EnsemblSearchMulti obj Function : Stores the ensembl SearchMulti container object in the database Returntype: scalar (token) Exceptions: Caller : Example : my $container_token = $blast_adpt->store_ticket( $container ); =cut sub store_search_multi{ my $self = shift; my $search_multi = shift || $self->throw( "Need a Bio::Tools::Run::EnsemblSearchMulti obj" ); my $frozen = shift || $search_multi->serialise; my $dbh = $self->dbc->db_handle; my $ticket = $search_multi->token || $self->throw( "Bio::Tools::Run::EnsemblSearchMulti obj has no ticket" ); my $sth = $self->prepare( $SQL_SEARCH_MULTI_RETRIEVE ); my $rv = $sth->execute( $ticket ) || $self->throw( $sth->errstr ); $sth->finish; if( $rv < 1 ){ # Insert (do first to minimise risk of race) my $sth = $self->prepare( $SQL_SEARCH_MULTI_STORE ); $sth->execute( $frozen, $ticket ) || $self->throw( $sth->errstr ); #$search_multi->token( $self->dbh->{mysql_insertid} ); $sth->finish; } else{ # Update my $sth = $self->prepare( $SQL_SEARCH_MULTI_UPDATE ); $sth->execute( $frozen, $ticket ) || $self->throw( $sth->errstr ); $sth->finish; } my $sth = $self->prepare('show tables'); $sth->execute(); $sth->finish; return $search_multi->token(); } #---------------------------------------------------------------------- =head2 retrieve_search_multi Arg [1] : Function : Returntype: Exceptions: Caller : Example : =cut sub retrieve_search_multi { my $self = shift; my $ticket = shift || $self->throw( "Need an EnsemblSearchMulti ticket" ); my $dbh = $self->dbc->db_handle; warn $dbh; warn $SQL_SEARCH_MULTI_RETRIEVE; my $sth = $self->prepare( $SQL_SEARCH_MULTI_RETRIEVE ); warn $sth; my $rv = $sth->execute( $ticket ) || $self->throw( $sth->errstr ); if( $rv < 1 ){ $self->throw( "Token $ticket not found" ) } my ( $frozen ) = $sth->fetchrow_array; $frozen || $self->throw( "Object from ticket $ticket is empty" ); $sth->finish; return $frozen; } #---------------------------------------------------------------------- =head2 store_result Arg [1] : Bio::Search::Result::EnsemblResult obj Function : Stores the ensembl Result in the database Returntype: scalar (token) Exceptions: Caller : Example : my $result_token = $blast_adpt->store_result( $result ); =cut sub store_result{ my $self = shift; my $res = shift || $self->throw( "Need a Bio::Search::Result::EnsemblResult obj" ); my $frozen = shift || $res->serialise; my $dbh = $self->dbc->db_handle; my $sth; my ( $id, $use_date ) = split( '!!', $res->token || '' ); $use_date ||= $self->use_date( 'RESULT' ); #my $ticket = $res->group_ticket || warn( "Result $id has no ticket" ); my $ticket = $self->ticket || warn("Result $id BlastAdaptor has no ticket"); my $rv = 0; if( $id ){ $sth = $self->prepare( sprintf $SQL_RESULT_RETRIEVE, $use_date ); $rv = $sth->execute( $id ) || $self->throw( $sth->errstr ); $sth->finish; } if( $rv < 1 ){ # We have no result with this token string Insert my $use_date = $res->use_date() || $res->use_date($self->use_date('RESULT')); $sth = $self->prepare( sprintf $SQL_RESULT_STORE, $use_date ); $sth->execute( $frozen, $ticket ) || $self->throw( $sth->errstr ); my $id = $dbh->{mysql_insertid}; $res->token( join( '!!', $id, $use_date ) ); $sth->finish; } else { # Update $sth = $self->prepare( sprintf $SQL_RESULT_UPDATE, $use_date ); $sth->execute( $frozen, $ticket, $id ) || $self->throw( $sth->errstr ); $sth->finish; } return $res->token(); } sub store_result_2{ my $self = shift; my $res = shift || $self->throw( "Need a Bio::Search::Result::EnsemblResult obj" ); my $frozen = shift || $res->serialise; my $dbh = $self->dbc->db_handle; my $sth; my ( $id, $use_date ) = split( '!!', $res->token || '' ); $use_date ||= $self->use_date( 'RESULT' ); #my $ticket = $res->group_ticket || warn( "Result $id has no ticket" ); my $ticket = $self->ticket || warn("Result $id BlastAdaptor has no ticket"); my $rv = 0; if( $ticket ){ $sth = $self->prepare( sprintf $SQL_RESULT_RETRIEVE_TICKET, $use_date ); $rv = $sth->execute( $ticket ) || $self->throw( $sth->errstr ); $sth->finish; } if( !$rv && $id ){ $sth = $self->prepare( sprintf $SQL_RESULT_RETRIEVE, $use_date ); $rv = $sth->execute( $id ) || $self->throw( $sth->errstr ); $sth->finish; } if( $rv < 1 ){ # We have no result with this token string Insert my $use_date = $res->use_date() || $res->use_date($self->use_date('RESULT')); $sth = $self->prepare( sprintf $SQL_RESULT_STORE, $use_date ); $sth->execute( $frozen, $ticket ) || $self->throw( $sth->errstr ); my $id = $dbh->{mysql_insertid}; $res->token( join( '!!', $id, $use_date ) ); $sth->finish; } else { # Update $sth = $self->prepare( sprintf $SQL_RESULT_UPDATE, $use_date ); $sth->execute( $frozen, $ticket, $id ) || $self->throw( $sth->errstr ); $sth->finish; } return $res->token(); } #---------------------------------------------------------------------- =head2 retrieve_result Arg [1] : Function : Returntype: Exceptions: Caller : Example : =cut sub retrieve_result{ my $self = shift; my $token = shift || $self->throw( "Need a Hit token" ); my ( $id, $use_date ) = split( '!!',$token); $use_date ||= ''; my $dbh = $self->dbc->db_handle; my $sth = $self->prepare( sprintf $SQL_RESULT_RETRIEVE, $use_date ); my $rv = $sth->execute( $id ) || $self->throw( $sth->errstr ); if( $rv < 1 ){ $self->throw( "Token $id not found" ) } my ( $frozen ) = $sth->fetchrow_array; $frozen || $self->throw( "Object from result $id is empty" ); $sth->finish; return $frozen; } #---------------------------------------------------------------------- =head2 store_hit Arg [1] : Bio::Search::Hit::EnsemblHit obj Function : Stores the ensembl Hit in the database Returntype: scalar (token) Exceptions: Caller : Example : my $hit_token = $blast_adpt->store_hit( $hit ); =cut sub store_hit{ my $self = shift; my $hit = shift || $self->throw( "Need a Bio::Search::Hit::EnsemblHit obj" ); my $frozen = shift || $hit->serialise; my $dbh = $self->dbc->db_handle; my ( $id, $use_date ) = split( '!!', $hit->token || '' ); $use_date ||= $hit->use_date() || $hit->use_date($self->use_date('HIT'));; #my $ticket = $hit->group_ticket || warn( "Hit $id has no ticket" ); my $ticket = $self->ticket || warn("Hit $id BlastAdaptor has no ticket"); my $rv = 0; if( $id ){ my $sth = $self->prepare( sprintf $SQL_HIT_RETRIEVE, $use_date ); $rv = $sth->execute( $id ) || $self->throw( $sth->errstr ); $sth->finish; } if( $rv < 1 ){ # Insert my $sth = $self->prepare( sprintf $SQL_HIT_STORE, $use_date ); $sth->execute( $frozen, $ticket ) || $self->throw( $sth->errstr ); my $id = $dbh->{mysql_insertid}; $hit->token( join( '!!', $id, $use_date ) ); $sth->finish; } else{ # Update my $sth = $self->prepare( sprintf $SQL_HIT_UPDATE, $use_date ); $sth->execute( $frozen, $ticket, $id ) || $self->throw( $sth->errstr ); $sth->finish; } return $hit->token(); } #---------------------------------------------------------------------- =head2 retrieve_hit Arg [1] : Function : Returntype: Exceptions: Caller : Example : =cut sub retrieve_hit{ my $self = shift; my $token = shift || $self->throw( "Need a Hit token" ); my ( $id, $use_date ) = split( '!!',$token); $use_date ||= ''; my $dbh = $self->dbc->db_handle; my $sth = $self->prepare( sprintf $SQL_HIT_RETRIEVE, $use_date ); my $rv = $sth->execute( $id ) || $self->throw( $sth->errstr ); if( $rv < 1 ){ $self->throw( "Token $token not found" ) } my ( $frozen ) = $sth->fetchrow_array; $frozen || $self->throw( "Object from hit $id is empty" ); $sth->finish; return $frozen; } #---------------------------------------------------------------------- =head2 store_hsp Arg [1] : Bio::Search::HSP::EnsemblHSP obj Function : Stores the ensembl HSP in the database Returntype: Exceptions: Caller : Example : =cut sub store_hsp{ my $self = shift; my $hsp = shift || $self->throw( "Need a Bio::Search::HSP::EnsemblHSP obj" ); my $frozen = shift || $hsp->serialise; my $dbh = $self->dbc->db_handle; my ( $id, $use_date ) = split( '!!', $hsp->token || ''); $use_date ||= $hsp->use_date() || $hsp->use_date($self->use_date('HSP')); #my $ticket = $hsp->group_ticket || warn( "HSP $id has no ticket" ); my $ticket = $self->ticket || warn( "HSP $id BlastAdaptor has no ticket" ); my $chr_name = ''; my $chr_start = 0; my $chr_end = 0; if( my $genomic = $hsp->genomic_hit ){ $chr_name = $genomic->seq_region_name; $chr_start = $genomic->start; $chr_end = $genomic->end; } my $rv = 0; if( $id ){ my $sth = $self->prepare( sprintf $SQL_HSP_RETRIEVE, $use_date ); $rv = $sth->execute( $id ) || $self->throw( $sth->errstr ); $sth->finish; } if( $rv < 1 ){ # Insert my $use_date = $hsp->use_date() || $hsp->use_date($self->use_date('HSP')); my $sth = $self->prepare( 'show tables' ); $sth->execute(); $sth->finish(); $sth = $self->prepare( sprintf $SQL_HSP_STORE, $use_date ); my @bound = ( $frozen, $ticket, $chr_name, $chr_start, $chr_end ); $sth->execute( @bound ) || $self->throw( $sth->errstr ); my $id = $dbh->{mysql_insertid}; $hsp->token( join( '!!', $id, $use_date ) ); $sth->finish; } else{ # Update my $sth = $self->prepare( sprintf $SQL_HSP_UPDATE, $use_date ); my @bound = ( $frozen, $ticket, $chr_name, $chr_start, $chr_end, $id ); $sth->execute( @bound ) || $self->throw( $sth->errstr ); $sth->finish; } return $hsp->token(); } #---------------------------------------------------------------------- =head2 retrieve_hsp Arg [1] : Function : Returntype: Exceptions: Caller : Example : =cut sub retrieve_hsp{ my $self = shift; my $token = shift || $self->throw( "Need an HSP token" ); my ( $id, $use_date ) = split( '!!',$token); $use_date ||= ''; my $dbh = $self->dbc->db_handle; my $sth = $self->prepare( sprintf $SQL_HSP_RETRIEVE, $use_date ); my $rv = $sth->execute( $id ) || $self->throw( $sth->errstr ); if( $rv < 1 ){ $self->throw( "Token $token not found" ) } my ( $frozen ) = $sth->fetchrow_array; $frozen || $self->throw( "Object from hsp $id is empty" ); $sth->finish; return $frozen; } #---------------------------------------------------------------------- =head2 remove_hsp Arg [1] : $hsp object to be removed Function : 'removes' hsp from e.g. contigview by setting chr fields to null Returntype: Exceptions: Caller : $self->remove Example : =cut sub remove_hsp { my $self = shift; my $hsp = shift || $self->throw( "Need a Bio::Search::HSP::EnsemblHSP obj" ); my $dbh = $self->dbc->db_handle; my ( $id, $use_date ) = split( '!!', $hsp->token || ''); $use_date ||= $hsp->use_date() || $hsp->use_date($self->use_date('HSP')); my $sth = $self->prepare( sprintf $SQL_HSP_REMOVE, $use_date ); my @bound = ( $id ); my $rv = $sth->execute( @bound ) || $self->throw( $sth->errstr ); $sth->finish; return 1; } #---------------------------------------------------------------------- =head2 get_all_HSPs Arg [1] : Function : Returntype: Exceptions: Caller : Example : =cut sub get_all_HSPs { my $self = shift; my $ticket = shift || $self->throw( "Need a search ticket!"); my $chr_name = shift || undef; my $chr_start = shift || undef; my $chr_end = shift || undef; my ( $id, $use_date ) = split( '!!', $ticket ); $use_date ||= ''; my $SQL = qq( SELECT object, hsp_id FROM blast_hsp%s WHERE ticket = ? ); my $CHR_SQL = qq( AND chr_name = ? ); my $RANGE_SQL = qq( AND chr_start <= ? AND chr_end >= ? ); my $q = sprintf( $SQL, $use_date ); my @binded = ( $id ); if( $chr_name ){ $q .= $CHR_SQL; push @binded, $chr_name; if( $chr_start && $chr_end ){ $q .= $RANGE_SQL; push @binded, $chr_end, $chr_start; } } my $sth = $self->dbc->db_handle->prepare($q); my $rv = $sth->execute( @binded ) || $self->throw( $sth->errstr ); my @hsps = (); foreach my $row( @{$sth->fetchall_arrayref()} ){ # Retrieve HSP and reset token my $hsp = thaw( $row->[0] ); my $hsp_id = $row->[1]; $hsp->token( join( '!!', $hsp_id, $use_date ) ); push @hsps, $hsp; } $sth->finish; return [@hsps]; } #---------------------------------------------------------------------- =head2 get_all_SearchFeatures Arg [1] : Function : Returntype: Exceptions: Caller : Example : =cut sub get_all_SearchFeatures { my $self = shift; my $hsps = $self->get_all_HSPs(@_); my $ticket = shift; $self->dynamic_use( ref($hsps->[0] ) ); my @feats = (); foreach my $hsp( @$hsps ){ my $base_align = $hsp->genomic_hit || next; ( $ticket ) = split( "!!", $ticket ); my $hsp_id = join( "!!", $ticket, $hsp->token ); $base_align->hseqname( join( ":", $base_align->hseqname, $hsp_id ) ); push @feats, $base_align; } return [ @feats ]; } sub dynamic_use { my( $self, $classname ) = @_; my( $parent_namespace, $module ) = $classname =~/^(.*::)(.*?)$/; no strict 'refs'; return 1 if $parent_namespace->{$module.'::'}; # return if already used eval "require $classname"; if($@) { warn "DrawableContainer: failed to use $classname\nDrawableContainer: $@"; return 0; } $classname->import(); return 1; } #---------------------------------------------------------------------- =head2 use_date Arg [1] : Function : Returntype: Exceptions: Caller : Example : =cut my %valid_table_types = ( HIT=>1, HSP=>1, RESULT=>1 ); sub use_date { my $key = '_current_table'; my $self = shift; my $type = uc( shift ); #warn "$self --- $key --- $type $self"; $valid_table_types{$type} || $self->throw( "Need a table type (Result, Hit or HSP)" ); $self->{$key} ||= {}; if( ! $self->{$key}->{$type} ){ my $sth = $self->dbc->db_handle->prepare( " SELECT table_type, use_date FROM blast_table_log WHERE table_status = 'CURRENT' ORDER BY use_date ASC" ); #warn "prepare... $sth"; #warn $SQL_SELECT_TABLE_LOG_CURRENT; #warn $type; my $rv = $sth->execute();# $type ); #warn $rv; unless( $rv ) { $sth->finish; warn( $sth->errstr ); return; } #warn "exec..."; foreach my $r (@{ $sth->fetchall_arrayref }) { my $date = $r->[1]; $date =~ s/-//g; $self->{$key}->{$r->[0]} = $date; #warn "$r->[0] ---> $r->[1] ---> $date"; } # $rv > 0 || ( warn( "No current $type table found" ) && return ); $sth->finish; #warn "end of finish..."; } return $self->{$key}->{$type}; } #---------------------------------------------------------------------- =head2 clean_blast_database Arg [1] : int $days Function : Removes blast tickets older than $days days Returntype: Exceptions: SQL errors Caller : Example : $ba->clean_blast_database(14) =cut sub clean_blast_database{ my $self = shift; my $days = shift || $self->throw( "Missing arg: number of days" ); $days =~ /\D/ && $self->throw( "Bad arg: number of days $days not int" ); my $dbh = $self->dbc->db_handle; # Get list of tickets > $days days old my $q = qq/ SELECT ticket FROM blast_ticket WHERE update_time < SUBDATE( NOW(), INTERVAL $days DAY ) /; my $sth = $self->dbc->db_handle->prepare($q); my $rv = $sth->execute() || $self->throw( $sth->errstr ); my $res = $sth->fetchall_arrayref; $sth->finish; # Delete result and ticket rows associated with old tickets # my $q_del_tmpl = qq/ #DELETE #FROM blast_%s #WHERE ticket like "%s" /; # # my @types = ( 'result','ticket' ); # my %num_deleted = map{ $_=>0 } @types; # # foreach my $row( @$res ){ # my $ticket = $row->[0]; # # foreach my $type( @types ){ # my $q_del = sprintf( $q_del_tmpl, $type, $ticket ); # my $sth = $self->db->db_handle->prepare($q_del); # my $rv = $sth->execute() || $self->throw( $sth->errstr ); # $num_deleted{$type} += $rv; # } # } # map{ warn("Purging $days days: Deleted $num_deleted{$_} rows of type $_\n") } # keys %num_deleted; # Drop daily Result, Hit and HSP tables not updated within $days days my $q_find = 'show table status like ?'; my $sth2 = $self->prepare( $q_find ); $sth2->execute( "blast_result%" ) || $self->throw( $sth2->errstr ); my $res_res = $sth2->fetchall_arrayref(); $sth2->execute( "blast_hit%" ) || $self->throw( $sth2->errstr ); my $hit_res = $sth2->fetchall_arrayref(); $sth2->execute( "blast_hsp%" ) || $self->throw( $sth2->errstr ); my $hsp_res = $sth2->fetchall_arrayref(); my @deletable_hit_tables; foreach my $row( @$res_res, @$hit_res, @$hsp_res ){ my $table_name = $row->[0]; ## table name my $num_rows = $row->[4]; ## # Rows... my $update_time = $row->[12]; ## update time --- Should be a string like 2003-08-15 10:36:56 my @time = split( /[-:\s]/, $update_time ); my $epoch_then = timelocal( $time[5], $time[4], $time[3], $time[2], $time[1]-1, $time[0] - 1900 ); my $secs_old = time() - $epoch_then; my $days_old = $secs_old / ( 60 * 60 * 24 ); if( $days_old > $days ){ warn( "Dropping table $table_name: $num_rows rows\n" ); my $sth_drop = $self->prepare( "DROP table $table_name" ); my $sth_log = $self->prepare( $SQL_TABLE_LOG_UPDATE ); $sth_drop->execute || $self->throw( $sth_drop->errstr ); my( $se,$mi,$hr,$da,$mo,$yr ) = (localtime)[0,1,2,3,4,5]; my $now = sprintf( "%4d-%2d-%2d %2d:%2d:%2d", $yr+1900,$mo+1,$da,$hr,$mi,$se ); $sth_log->execute ('DELETED',$now,$num_rows,$table_name) || $self->throw( $sth_log->errstr ); } } return 1; } #---------------------------------------------------------------------- =head2 create_tables Arg [1] : none Function : Creates the blast_ticket and blast_table_log tables in the database indicated by the database handle. Checks first to make sure they do not exist Returntype: boolean Exceptions: Caller : Example : =cut sub create_tables { my $self = shift; my $dbh = $self->dbc->db_handle; # Get list of existing tables in database my $q = 'show tables like ?'; my $sth = $self->prepare( $q ); my $rv_tck = $sth->execute("blast_ticket") || $self->throw($sth->errstr); my $rv_log = $sth->execute("blast_table_log" )|| $self->throw($sth->errstr); $sth->finish; if( $rv_tck == 0 ){ warn( "Creating blast_ticket table\n" ); my $sth = $self->prepare( $SQL_CREATE_TICKET ); my $rv = $sth->execute() || $self->throw( $sth->errstr ); $sth->finish; } else{ warn( "blast_ticket table already exists\n" ) } if( $rv_log == 0 ){ warn( "Creating blast_result table\n" ); my $sth = $self->prepare( $SQL_CREATE_TABLE_LOG ); my $rv = $sth->execute() || $self->throw( $sth->errstr ); $sth->finish; } else{ warn( "blast_table_log table already exists\n" ) } return 1; } #---------------------------------------------------------------------- =head2 rotate_daily_tables Arg [1] : none Function : Creates the daily blast_result{date}, blast_hit{date} and blast_hsp{date} tables in the database indicated by the database handle. Checks first to make sure they do not exist. Sets the new table to 'CURRENT' in the blast_table_log. Sets the previous 'CURRENT' table to filled. Returntype: boolean Exceptions: Caller : Example : =cut sub rotate_daily_tables { my $self = shift; my $dbh = $self->dbc->db_handle; # Get date my( $day, $month, $year ) = (localtime)[3,4,5]; my $date = sprintf( "%04d%02d%02d", $year+1900, $month+1, $day ); my $res_table = "blast_result$date"; my $hit_table = "blast_hit$date"; my $hsp_table = "blast_hsp$date"; # Get list of existing tables in database my $q = 'show table status like ?'; my $sth = $self->prepare( $q ); my $rv_res = $sth->execute($res_table) || $self->throw($sth->errstr); my $rv_hit = $sth->execute($hit_table) || $self->throw($sth->errstr); my $rv_hsp = $sth->execute($hsp_table) || $self->throw($sth->errstr); $sth->finish; if( $rv_res == 0 ){ warn( "Creating today's $res_table table\n" ); # Create new table my $q = sprintf($SQL_CREATE_DAILY_RESULT, $res_table); my $sth1 = $self->prepare( $q ); my $rv = $sth1->execute() || $self->throw( $sth1->errstr ); # Flip current table in blast_table_tog my $last_date = $self->use_date( "RESULT" ) || ''; my $sth2 = $self->prepare( $SQL_TABLE_LOG_INSERT ); my $sth3 = $self->prepare( $SQL_TABLE_LOG_UPDATE ); $sth2->execute( "$res_table",'CURRENT','RESULT',$date ) || die( $self->throw( $sth2->errstr ) ); $sth3->execute( 'FILLED','0',0,"blast_result$last_date") || die( $self->throw( $sth3->errstr ) ); $sth1->finish(); $sth2->finish(); $sth3->finish(); } else{ warn( "Today's $res_table table already exists\n" ) } if( $rv_hit == 0 ){ warn( "Creating today's $hit_table table\n" ); # Create new table my $q = sprintf($SQL_CREATE_DAILY_HIT, $hit_table); my $sth1 = $self->prepare( $q ); my $rv = $sth1->execute() || $self->throw( $sth1->errstr ); # Flip current table in blast_table_tog my $last_date = $self->use_date( "HIT" ) || ''; my $sth2 = $self->prepare( $SQL_TABLE_LOG_INSERT ); my $sth3 = $self->prepare( $SQL_TABLE_LOG_UPDATE ); $sth2->execute( "$hit_table",'CURRENT','HIT',$date ) || die( $self->throw( $sth2->errstr ) ); $sth3->execute( 'FILLED','0',0,"blast_hit$last_date") || die( $self->throw( $sth3->errstr ) ); $sth1->finish(); $sth2->finish(); $sth3->finish(); } else{ warn( "Today's $hit_table table already exists\n" ) } if( $rv_hsp == 0 ){ warn( "Creating today's $hsp_table table\n" ); # Create new table my $q = sprintf($SQL_CREATE_DAILY_HSP, $hsp_table ); my $sth1 = $self->prepare( $q ); my $rv = $sth1->execute() || $self->throw( $sth1->errstr ); # Flip current table in blast_table_tog my $last_date = $self->use_date( "HSP" ) || ''; my $sth2 = $self->prepare( $SQL_TABLE_LOG_INSERT ); my $sth3 = $self->prepare( $SQL_TABLE_LOG_UPDATE ); $sth2->execute( "$hsp_table",'CURRENT','HSP',$date ) || die( $self->throw( $sth2->errstr ) ); $sth3->execute( 'FILLED','0',0,"blast_hsp$last_date") || die( $self->throw( $sth3->errstr ) ); $sth1->finish(); $sth2->finish(); $sth3->finish(); } else{ warn( "Today's $hsp_table table already exists\n" ) } return 1; } #---------------------------------------------------------------------- =head2 cleanup_processes Arg [1] : none Function : Kills any sleeping processes older that 1000 Returntype: boolean Exceptions: Caller : Example : =cut sub cleanup_processes { my $self = shift; my $dbh = $self->dbc->db_handle; my $sth = $self->prepare( 'show processlist' ); my $kill_sth = $self->prepare('kill ?'); $sth->execute; my $res = $sth->fetchall_arrayref([0,3,4,5]); my $c = 0; foreach my $ps (@$res) { my ($pid,$db,$stat,$time) = @$ps; if ($db eq 'ensembl_blast') { if ( ($stat eq 'Sleep') && ($time > 1000) ) { $kill_sth->execute($pid); $c++; } } } warn "Killed $c processes"; return 1; } 1;