|
0
|
1 =head1 LICENSE
|
|
|
2
|
|
|
3 Copyright (c) 1999-2012 The European Bioinformatics Institute and
|
|
|
4 Genome Research Limited. All rights reserved.
|
|
|
5
|
|
|
6 This software is distributed under a modified Apache license.
|
|
|
7 For license details, please see
|
|
|
8
|
|
|
9 http://www.ensembl.org/info/about/code_licence.html
|
|
|
10
|
|
|
11 =head1 CONTACT
|
|
|
12
|
|
|
13 Please email comments or questions to the public Ensembl
|
|
|
14 developers list at <dev@ensembl.org>.
|
|
|
15
|
|
|
16 Questions may also be sent to the Ensembl help desk at
|
|
|
17 <helpdesk@ensembl.org>.
|
|
|
18
|
|
|
19 =cut
|
|
|
20
|
|
|
21 =head1 NAME
|
|
|
22
|
|
|
23 Bio::EnsEMBL::DBSQL::StatementHandle
|
|
|
24
|
|
|
25 =head1 SYNOPSIS
|
|
|
26
|
|
|
27 Do not use this class directly. It will automatically be used by the
|
|
|
28 Bio::EnsEMBL::DBSQL::DBConnection class.
|
|
|
29
|
|
|
30 =head1 DESCRIPTION
|
|
|
31
|
|
|
32 This class extends DBD::mysql::st so that the DESTROY method may be
|
|
|
33 overridden. If the DBConnection::disconnect_when_inactive flag is set
|
|
|
34 this statement handle will cause the database connection to be closed
|
|
|
35 when it goes out of scope and there are no other open statement handles.
|
|
|
36
|
|
|
37 =head1 METHODS
|
|
|
38
|
|
|
39 =cut
|
|
|
40
|
|
|
41 package Bio::EnsEMBL::DBSQL::StatementHandle;
|
|
|
42
|
|
|
43 use vars qw(@ISA);
|
|
|
44 use strict;
|
|
|
45
|
|
|
46 use Bio::EnsEMBL::Utils::Exception qw(warning throw);
|
|
|
47
|
|
|
48 use DBI;
|
|
|
49
|
|
|
50 #use Time::HiRes qw(time);
|
|
|
51
|
|
|
52 @ISA = qw(DBI::st);
|
|
|
53
|
|
|
54
|
|
|
55 # As DBD::mysql::st is a tied hash can't store things in it,
|
|
|
56 # so have to have parallel hash
|
|
|
57 my %dbchash;
|
|
|
58 my %dbc_sql_hash;
|
|
|
59
|
|
|
60
|
|
|
61 sub dbc {
|
|
|
62 my $self = shift;
|
|
|
63
|
|
|
64 if (@_) {
|
|
|
65 my $dbc = shift;
|
|
|
66 if(!defined($dbc)) {
|
|
|
67 # without delete key space would grow indefinitely causing mem-leak
|
|
|
68 delete($dbchash{$self});
|
|
|
69 } else {
|
|
|
70 $dbchash{$self} = $dbc;
|
|
|
71 }
|
|
|
72 }
|
|
|
73
|
|
|
74 return $dbchash{$self};
|
|
|
75 }
|
|
|
76
|
|
|
77 sub sql {
|
|
|
78 my $self = shift;
|
|
|
79
|
|
|
80 if (@_) {
|
|
|
81 my $sql = shift;
|
|
|
82 if(!defined($sql)) {
|
|
|
83 # without delete key space would grow indefinitely causing mem-leak
|
|
|
84 delete($dbc_sql_hash{$self});
|
|
|
85 } else {
|
|
|
86 $dbc_sql_hash{$self} = $sql;
|
|
|
87 }
|
|
|
88 }
|
|
|
89
|
|
|
90 return $dbc_sql_hash{$self};
|
|
|
91 }
|
|
|
92
|
|
|
93 sub DESTROY {
|
|
|
94 my ($self) = @_;
|
|
|
95
|
|
|
96 my $dbc = $self->dbc;
|
|
|
97 $self->dbc(undef);
|
|
|
98 my $sql = $self->sql;
|
|
|
99 $self->sql(undef);
|
|
|
100
|
|
|
101 # Re-bless into DBI::st so that superclass destroy method is called if
|
|
|
102 # it exists (it does not exist in all DBI versions).
|
|
|
103 bless( $self, 'DBI::st' );
|
|
|
104
|
|
|
105 # The count for the number of kids is decremented only after this
|
|
|
106 # function is complete. Disconnect if there is 1 kid (this one)
|
|
|
107 # remaining.
|
|
|
108 if ( $dbc
|
|
|
109 && $dbc->disconnect_when_inactive()
|
|
|
110 && $dbc->connected
|
|
|
111 && ( $dbc->db_handle->{Kids} == 1 ) )
|
|
|
112 {
|
|
|
113 if ( $dbc->disconnect_if_idle() ) {
|
|
|
114 warn("Problem disconnect $self around sql = $sql\n");
|
|
|
115 }
|
|
|
116 }
|
|
|
117 } ## end sub DESTROY
|
|
|
118
|
|
|
119 1;
|
|
|
120
|
|
|
121 # Comment out this "__END__" for printing out handy debug information
|
|
|
122 # (every query if you want).
|
|
|
123
|
|
|
124 __END__
|
|
|
125
|
|
|
126 # To stop caching messing up your timings, try doing the following on
|
|
|
127 # any adapter:
|
|
|
128 #
|
|
|
129 # $slice_adaptor->dbc()->db_handle()
|
|
|
130 # ->do("SET SESSION query_cache_type = OFF");
|
|
|
131 #
|
|
|
132 # To start logging:
|
|
|
133 # Bio::EnsEMBL::DBSQL::StatementHandle->sql_timing_start();
|
|
|
134 #
|
|
|
135 # To display the results:
|
|
|
136 # Bio::EnsEMBL::DBSQL::StatementHandle->sql_timing_print(1);
|
|
|
137 #
|
|
|
138 # To pause logging:
|
|
|
139 # Bio::EnsEMBL::DBSQL::StatementHandle->sql_timimg_pause();
|
|
|
140 #
|
|
|
141 # To resume logging after pause:
|
|
|
142 # Bio::EnsEMBL::DBSQL::StatementHandle->sql_timimg_resume();
|
|
|
143
|
|
|
144 use Time::HiRes qw(time);
|
|
|
145
|
|
|
146 my @bind_args = ();
|
|
|
147 my $dump = 0;
|
|
|
148 my %total_time;
|
|
|
149 my %min_time;
|
|
|
150 my %max_time;
|
|
|
151 my %number_of_times;
|
|
|
152 my %first_time;
|
|
|
153 my $grand_total;
|
|
|
154
|
|
|
155 sub sql_timing_start {
|
|
|
156 %total_time = ();
|
|
|
157 %number_of_times = ();
|
|
|
158 %min_time = ();
|
|
|
159 %max_time = ();
|
|
|
160 %first_time = ();
|
|
|
161 $dump = 1;
|
|
|
162 }
|
|
|
163
|
|
|
164 sub sql_timing_pause { $dump = 0 }
|
|
|
165 sub sql_timing_resume { $dump = 1 }
|
|
|
166
|
|
|
167 sub sql_timing_print {
|
|
|
168 my ( $self, $level, $fh ) = @_;
|
|
|
169
|
|
|
170 my $grand_total = 0;
|
|
|
171
|
|
|
172 if ( !defined($fh) ) {
|
|
|
173 $fh = \*STDERR;
|
|
|
174 }
|
|
|
175
|
|
|
176 print( ref($fh), "\n" );
|
|
|
177
|
|
|
178 foreach my $key ( keys %total_time ) {
|
|
|
179 $grand_total += $total_time{$key};
|
|
|
180
|
|
|
181 if ( !( defined($level) and $level ) ) { next }
|
|
|
182
|
|
|
183 print( $fh $key, "\n" );
|
|
|
184
|
|
|
185 print( $fh
|
|
|
186 "total\t \tnum\tfirst \t\tavg\t \t[min ,max ]\n" );
|
|
|
187
|
|
|
188 printf( $fh "%6f\t%d\t%6f\t%6f\t[%6f, %6f]\n\n",
|
|
|
189 $total_time{$key}, $number_of_times{$key},
|
|
|
190 $first_time{$key}, ( $total_time{$key}/$number_of_times{$key} ),
|
|
|
191 $min_time{$key}, $max_time{$key} );
|
|
|
192 }
|
|
|
193
|
|
|
194 printf( $fh "\ntotal time %6f\n\n", $grand_total );
|
|
|
195
|
|
|
196 } ## end sub sql_timing_print
|
|
|
197
|
|
|
198 sub bind_param {
|
|
|
199 my ( $self, @args ) = @_;
|
|
|
200
|
|
|
201 $bind_args[ $args[0] - 1 ] = $args[1];
|
|
|
202 $self->SUPER::bind_param(@args);
|
|
|
203 }
|
|
|
204
|
|
|
205 sub execute {
|
|
|
206 my ( $self, @args ) = @_;
|
|
|
207
|
|
|
208 my $retval;
|
|
|
209 # Skip dumping if !$dump
|
|
|
210 if ( !$dump ) {
|
|
|
211 local $self->{RaiseError};
|
|
|
212 $retval = $self->SUPER::execute(@args);
|
|
|
213 if ( !defined($retval) ) {
|
|
|
214 throw("Failed to execute SQL statement");
|
|
|
215 }
|
|
|
216 return $retval;
|
|
|
217 }
|
|
|
218
|
|
|
219 my $sql = $self->sql();
|
|
|
220 my @chrs = split( //, $sql );
|
|
|
221
|
|
|
222 my $j = 0;
|
|
|
223
|
|
|
224 for ( my $i = 0; $i < @chrs; $i++ ) {
|
|
|
225 if ( $chrs[$i] eq '?' && defined( $bind_args[$j] ) ) {
|
|
|
226 $chrs[$i] = $bind_args[ $j++ ];
|
|
|
227 }
|
|
|
228 }
|
|
|
229
|
|
|
230 my $str = join( '', @chrs );
|
|
|
231
|
|
|
232 # Uncomment this line if you want to see sql in order.
|
|
|
233 # print( STDERR "\n\nSQL:\n$str\n\n" );
|
|
|
234
|
|
|
235 my $time = time();
|
|
|
236 {
|
|
|
237 local $self->{RaiseError};
|
|
|
238 $retval = $self->SUPER::execute(@args);
|
|
|
239 if ( !defined($retval) ) {
|
|
|
240 throw("Failed to execute SQL statement");
|
|
|
241 }
|
|
|
242 }
|
|
|
243 # my $res = $self->SUPER::execute(@args);
|
|
|
244 $time = time() - $time;
|
|
|
245
|
|
|
246 if ( defined( $total_time{$sql} ) ) {
|
|
|
247 $total_time{$sql} += $time;
|
|
|
248 $number_of_times{$sql}++;
|
|
|
249
|
|
|
250 if ( $min_time{$sql} > $time ) { $min_time{$sql} = $time }
|
|
|
251 if ( $max_time{$sql} < $time ) { $max_time{$sql} = $time }
|
|
|
252
|
|
|
253 } else {
|
|
|
254 $first_time{$sql} = $time;
|
|
|
255 $max_time{$sql} = $time;
|
|
|
256 $min_time{$sql} = $time;
|
|
|
257 $total_time{$sql} = $time;
|
|
|
258 $number_of_times{$sql} = 1;
|
|
|
259 }
|
|
|
260
|
|
|
261 return $retval;
|
|
|
262 } ## end sub execute
|
|
|
263
|
|
|
264 1;
|
