Archive Ensembl HomeArchive Ensembl Home
StatementHandle.pm
Go to the documentation of this file.
00001 =head1 LICENSE
00002 
00003   Copyright (c) 1999-2012 The European Bioinformatics Institute and
00004   Genome Research Limited.  All rights reserved.
00005 
00006   This software is distributed under a modified Apache license.
00007   For license details, please see
00008 
00009     http://www.ensembl.org/info/about/code_licence.html
00010 
00011 =head1 CONTACT
00012 
00013   Please email comments or questions to the public Ensembl
00014   developers list at <dev@ensembl.org>.
00015 
00016   Questions may also be sent to the Ensembl help desk at
00017   <helpdesk@ensembl.org>.
00018 
00019 =cut
00020 
00021 =head1 NAME
00022 
00023 Bio::EnsEMBL::DBSQL::StatementHandle
00024 
00025 =head1 SYNOPSIS
00026 
00027 Do not use this class directly.  It will automatically be used by the
00028 Bio::EnsEMBL::DBSQL::DBConnection class.
00029 
00030 =head1 DESCRIPTION
00031 
00032 This class extends DBD::mysql::st so that the DESTROY method may be
00033 overridden.  If the DBConnection::disconnect_when_inactive flag is set
00034 this statement handle will cause the database connection to be closed
00035 when it goes out of scope and there are no other open statement handles.
00036 
00037 =head1 METHODS
00038 
00039 =cut
00040 
00041 package Bio::EnsEMBL::DBSQL::StatementHandle;
00042 
00043 use vars qw(@ISA);
00044 use strict;
00045 
00046 use Bio::EnsEMBL::Utils::Exception qw(warning throw);
00047 
00048 use DBI;
00049 
00050 #use Time::HiRes qw(time);
00051 
00052 @ISA = qw(DBI::st);
00053 
00054 
00055 # As DBD::mysql::st is a tied hash can't store things in it,
00056 # so have to have parallel hash
00057 my %dbchash;
00058 my %dbc_sql_hash;
00059 
00060 
00061 sub dbc {
00062   my $self = shift;
00063 
00064   if (@_) {
00065     my $dbc = shift;
00066     if(!defined($dbc)) {
00067       # without delete key space would grow indefinitely causing mem-leak
00068       delete($dbchash{$self});
00069     } else {
00070       $dbchash{$self} = $dbc;
00071     }
00072   }
00073 
00074   return $dbchash{$self};
00075 }
00076 
00077 sub sql {
00078   my $self = shift;
00079 
00080   if (@_) {
00081     my $sql = shift;
00082     if(!defined($sql)) {
00083       # without delete key space would grow indefinitely causing mem-leak
00084       delete($dbc_sql_hash{$self});
00085     } else {
00086       $dbc_sql_hash{$self} = $sql;
00087     }
00088   }
00089 
00090   return $dbc_sql_hash{$self};
00091 }
00092 
00093 sub DESTROY {
00094   my ($self) = @_;
00095 
00096   my $dbc = $self->dbc;
00097   $self->dbc(undef);
00098   my $sql = $self->sql;
00099   $self->sql(undef);
00100 
00101   # Re-bless into DBI::st so that superclass destroy method is called if
00102   # it exists (it does not exist in all DBI versions).
00103   bless( $self, 'DBI::st' );
00104 
00105   # The count for the number of kids is decremented only after this
00106   # function is complete. Disconnect if there is 1 kid (this one)
00107   # remaining.
00108   if (    $dbc
00109        && $dbc->disconnect_when_inactive()
00110        && $dbc->connected
00111        && ( $dbc->db_handle->{Kids} == 1 ) )
00112   {
00113     if ( $dbc->disconnect_if_idle() ) {
00114       warn("Problem disconnect $self around sql = $sql\n");
00115     }
00116   }
00117 } ## end sub DESTROY
00118 
00119 1;
00120 
00121 # Comment out this "__END__" for printing out handy debug information
00122 # (every query if you want).
00123 
00124 __END__
00125 
00126 # To stop caching messing up your timings, try doing the following on
00127 # any adapter:
00128 #
00129 #   $slice_adaptor->dbc()->db_handle()
00130 #       ->do("SET SESSION query_cache_type = OFF");
00131 #
00132 # To start logging:
00133 # Bio::EnsEMBL::DBSQL::StatementHandle->sql_timing_start();
00134 #
00135 # To display the results:
00136 # Bio::EnsEMBL::DBSQL::StatementHandle->sql_timing_print(1);
00137 #
00138 # To pause logging:
00139 # Bio::EnsEMBL::DBSQL::StatementHandle->sql_timimg_pause();
00140 #
00141 # To resume logging after pause:
00142 # Bio::EnsEMBL::DBSQL::StatementHandle->sql_timimg_resume();
00143 
00144 use Time::HiRes qw(time);
00145 
00146 my @bind_args = ();
00147 my $dump      = 0;
00148 my %total_time;
00149 my %min_time;
00150 my %max_time;
00151 my %number_of_times;
00152 my %first_time;
00153 my $grand_total;
00154 
00155 sub sql_timing_start {
00156   %total_time      = ();
00157   %number_of_times = ();
00158   %min_time        = ();
00159   %max_time        = ();
00160   %first_time      = ();
00161   $dump            = 1;
00162 }
00163 
00164 sub sql_timing_pause  { $dump = 0 }
00165 sub sql_timing_resume { $dump = 1 }
00166 
00167 sub sql_timing_print {
00168   my ( $self, $level, $fh ) = @_;
00169 
00170   my $grand_total = 0;
00171 
00172   if ( !defined($fh) ) {
00173     $fh = \*STDERR;
00174   }
00175 
00176   print( ref($fh), "\n" );
00177 
00178   foreach my $key ( keys %total_time ) {
00179     $grand_total += $total_time{$key};
00180 
00181     if ( !( defined($level) and $level ) ) { next }
00182 
00183     print( $fh $key, "\n" );
00184 
00185     print( $fh
00186         "total\t \tnum\tfirst \t\tavg\t \t[min     ,max      ]\n" );
00187 
00188     printf( $fh "%6f\t%d\t%6f\t%6f\t[%6f, %6f]\n\n",
00189       $total_time{$key}, $number_of_times{$key},
00190       $first_time{$key}, ( $total_time{$key}/$number_of_times{$key} ),
00191       $min_time{$key}, $max_time{$key} );
00192   }
00193 
00194   printf( $fh "\ntotal time %6f\n\n", $grand_total );
00195 
00196 } ## end sub sql_timing_print
00197 
00198 sub bind_param {
00199   my ( $self, @args ) = @_;
00200 
00201   $bind_args[ $args[0] - 1 ] = $args[1];
00202   $self->SUPER::bind_param(@args);
00203 }
00204 
00205 sub execute {
00206   my ( $self, @args ) = @_;
00207 
00208   my $retval;
00209   # Skip dumping if !$dump
00210   if ( !$dump ) {
00211       local $self->{RaiseError};
00212       $retval = $self->SUPER::execute(@args);
00213       if ( !defined($retval) ) {
00214         throw("Failed to execute SQL statement");
00215       }
00216       return $retval;
00217   }
00218 
00219   my $sql = $self->sql();
00220   my @chrs = split( //, $sql );
00221 
00222   my $j = 0;
00223 
00224   for ( my $i = 0; $i < @chrs; $i++ ) {
00225     if ( $chrs[$i] eq '?' && defined( $bind_args[$j] ) ) {
00226       $chrs[$i] = $bind_args[ $j++ ];
00227     }
00228   }
00229 
00230   my $str = join( '', @chrs );
00231 
00232   # Uncomment this line if you want to see sql in order.
00233   # print( STDERR "\n\nSQL:\n$str\n\n" );
00234 
00235   my $time = time();
00236   {
00237     local $self->{RaiseError};
00238     $retval = $self->SUPER::execute(@args);
00239     if ( !defined($retval) ) {
00240       throw("Failed to execute SQL statement");
00241     }
00242   }
00243   #  my $res  = $self->SUPER::execute(@args);
00244   $time = time() - $time;
00245 
00246   if ( defined( $total_time{$sql} ) ) {
00247     $total_time{$sql} += $time;
00248     $number_of_times{$sql}++;
00249 
00250     if ( $min_time{$sql} > $time ) { $min_time{$sql} = $time }
00251     if ( $max_time{$sql} < $time ) { $max_time{$sql} = $time }
00252 
00253   } else {
00254     $first_time{$sql}      = $time;
00255     $max_time{$sql}        = $time;
00256     $min_time{$sql}        = $time;
00257     $total_time{$sql}      = $time;
00258     $number_of_times{$sql} = 1;
00259   }
00260 
00261   return $retval;
00262 } ## end sub execute
00263 
00264 1;