3 Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
4 Copyright [2016-2022] EMBL-European Bioinformatics Institute
6 Licensed under the Apache License, Version 2.0 (the
"License");
7 you may not use
this file except in compliance with the License.
8 You may obtain a copy of the License at
12 Unless required by applicable law or agreed to in writing, software
13 distributed under the License is distributed on an
"AS IS" BASIS,
14 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15 See the License
for the specific language governing permissions and
16 limitations under the License.
23 Please email comments or questions to the
public Ensembl
24 developers list at <dev@ensembl.org>.
26 Questions may also be sent to the Ensembl help desk at
27 <helpdesk@ensembl.org>.
37 $dbc = Bio::EnsEMBL::DBSQL::DBConnection->
new(
39 -dbname =>
'homo_sapiens_core_20_34c',
40 -host =>
'ensembldb.ensembl.org',
44 # SQL statements should be created/executed through this modules 45 # prepare() and do() methods. 47 $sth = $dbc->prepare(
"SELECT something FROM yourtable");
51 # do something with rows returned ... 57 This
class is a wrapper around DBIs datbase handle. It provides some
58 additional functionality such as the ability to automatically disconnect
59 when inactive and reconnect when needed.
61 Generally this class will be used through one of the object adaptors or
62 the
Bio::EnsEMBL::Registry and will not be instantiated directly.
81 use vars qw(@ISA); # If Ensembl Core code is available, inherit from its'
DBConnection for compatibility.
83 if (eval { require Bio::EnsEMBL::DBSQL::DBConnection; 1 }) {
84 @ISA = (
'Bio::EnsEMBL::DBSQL::DBConnection');
93 Arg [DBNAME] : (optional)
string 94 The name of the database to connect to.
95 Arg [HOST] : (optional)
string 96 The domain name of the database host to connect to.
97 'localhost' by
default.
99 The name of the database user to connect with
100 Arg [PASS] : (optional)
string 101 The password to be used to connect to the database
102 Arg [PORT] : (optional)
int 103 The port to use when connecting to the database
104 3306 by
default if the driver is mysql.
105 Arg [DRIVER] : (optional)
string 106 The type of database driver to use to connect to the DB
108 Arg [DBCONN] : (optional)
109 Open another handle to the same database as another connection
110 If
this argument is specified, no other arguments should be
112 Arg [DISCONNECT_WHEN_INACTIVE]: (optional)
boolean 113 If
set to
true, the database connection will be disconnected
114 everytime there are no active statement handles. This is
115 useful when running a lot of jobs on a compute farm
116 which would otherwise keep open a lot of connections to the
117 database. Database connections are automatically reopened
118 when required.Do not use
this option together with RECONNECT_WHEN_LOST.
119 Arg [WAIT_TIMEOUT]: (optional) integer
120 Time in seconds
for the wait_timeout to happen. Time after which
121 the connection is deleted
if not used. By
default this is 28800 (8 hours)
123 So
set this to greater than
this if your connection are getting deleted.
124 Only
set this if you are having problems and know what you are doing.
125 Arg [RECONNECT_WHEN_LOST]: (optional)
boolean 126 In
case you
're reusing the same database connection, i.e. DISCONNECT_WHEN_INACTIVE is 127 set to false and running a job which takes a long time to process (over 8hrs), 128 which means that the db connection may be lost, set this option to true. 129 On each prepare or do statement the db handle will be pinged and the database 130 connection will be reconnected if it's lost.
132 Example : $dbc = Bio::EnsEMBL::DBSQL::DBConnection->
new 133 (-user =>
'anonymous',
134 -dbname =>
'homo_sapiens_core_20_34c',
135 -host =>
'ensembldb.ensembl.org',
138 Description: Constructor
for a Database Connection. Any adaptors that require
139 database connectivity should inherit from
this class.
140 Returntype : Bio::EnsEMBL::DBSQL::DBConnection
141 Exceptions : thrown
if USER or DBNAME are not specified, or
if the database
142 cannot be connected to.
143 Caller : Bio::EnsEMBL::Utils::ConfigRegistry (
for newer code
using the registry)
144 Bio::EnsEMBL::DBSQL::DBAdaptor (
for old style code)
150 my $class = shift @_;
153 my ($driver, $user, $password, $host, $port, $dbname,
154 $dbconn, $disconnect_when_inactive, $wait_timeout, $reconnect_when_lost)
155 = @flags{qw(-driver -user -pass -host -port -dbname -dbconn
156 -disconnect_when_inactive -wait_timeout -reconnect_when_lost)};
162 if($dbname || $host || $driver || $password || $port || $disconnect_when_inactive || $reconnect_when_lost) {
163 throw(
"Cannot specify other arguments when -DBCONN argument used.");
166 $self->driver($dbconn->driver());
167 $self->host($dbconn->host());
168 $self->port($dbconn->port());
169 $self->username($dbconn->username());
170 $self->password($dbconn->password());
171 $self->dbname($dbconn->dbname());
173 if($dbconn->disconnect_when_inactive()) {
174 $self->disconnect_when_inactive(1);
179 if($driver eq
'mysql') {
180 $user ||
throw(
"-USER argument is required.");
184 if($host eq
"ensembldb.ensembl.org"){
185 if( $dbname =~ /\w+_\w+_\w+_(\d+)/){
192 } elsif($driver eq
'pgsql') {
198 $self->driver($driver);
199 $self->host( $host );
201 $self->username( $user );
202 $self->password( $password );
203 $self->dbname( $dbname );
204 $self->wait_timeout($wait_timeout);
206 if($disconnect_when_inactive) {
207 $self->disconnect_when_inactive($disconnect_when_inactive);
209 if($reconnect_when_lost) {
210 $self->reconnect_when_lost($reconnect_when_lost);
214 # if(defined $dnadb) { 215 # $self->dnadb($dnadb); 223 Example : $dbcon->connect()
224 Description: Connects to the database
using the connection attribute
228 Caller :
new, db_handle
236 if ( $self->connected() ) {
return }
240 if ( defined( $self->db_handle() ) and $self->db_handle()->ping() ) {
241 warn(
"unconnected db_handle is still pingable, " 242 .
"reseting connected boolean\n" );
246 my $dbname = $self->dbname();
248 if ( $self->driver() eq
"Oracle" ) {
250 $dsn =
"DBI:Oracle:";
253 $dbh = DBI->connect( $dsn,
255 $self->username(), $dbname ),
257 {
'RaiseError' => 1,
'PrintError' => 0 } );
260 } elsif ( $self->driver() eq
"ODBC" ) {
262 $dsn = sprintf(
"DBI:ODBC:%s", $self->dbname() );
265 $dbh = DBI->connect( $dsn,
269 'LongReadLen' => 2**16 - 8,
272 'odbc_cursortype' => 2 } );
275 } elsif ( $self->driver() eq
"Sybase" ) {
276 my $dbparam = ($dbname) ?
";database=${dbname}" : q{};
278 $dsn = sprintf(
"DBI:Sybase:server=%s%s;tdsLevel=CS_TDS_495",
279 $self->host(), $dbparam );
282 $dbh = DBI->connect( $dsn,
287 'PrintError' => 0 } );
290 } elsif ( lc( $self->driver() ) eq
'sqlite' ) {
292 throw "We require a dbname to connect to a SQLite database" 295 $dsn = sprintf(
"DBI:SQLite:%s", $dbname );
298 $dbh = DBI->connect( $dsn,
'',
'', {
'RaiseError' => 1, } );
303 my $dbparam = ($dbname) ?
"database=${dbname};" : q{};
305 my $driver = $self->driver();
306 $driver =
'Pg' if($driver eq
'pgsql');
308 $dsn = sprintf(
"DBI:%s:%shost=%s;port=%s",
310 $self->host(), $self->port() );
312 my $parameters = {
'RaiseError' => 1 };
313 $parameters->{
'mysql_local_infile'} = 1
if lc($self->driver()) eq
'mysql';
316 $dbh = DBI->connect( $dsn, $self->username(), $self->password(),
322 if ( !$dbh || $error || !$dbh->ping() ) {
323 warn(
"Could not connect to database " 327 .
" using [$dsn] as a locator:\n" 332 throw(
"Could not connect to database " 336 .
" using [$dsn] as a locator:\n" 340 $self->db_handle($dbh);
342 if ( $self->wait_timeout() ) {
343 my $driver = $self->driver();
345 if( $driver eq
'mysql' ) {
346 $dbh->do(
"SET SESSION wait_timeout=" . $self->wait_timeout() );
348 warn
"Don't know how to set the wait_timeout for '$driver' driver, skipping.\n";
358 Example : $dbcon->connected()
359 Description: Boolean which tells
if DBConnection is connected or not.
360 State is
set internally, and external processes should not alter state.
361 Returntype : undef or 1
371 # Use the process id ($$) as part of the key for the connected flag. 372 # This forces the opening of another connection in a forked subprocess. 373 $self->{
'connected'.$$} = shift
if(@_);
374 return $self->{
'connected'.$$};
377 sub disconnect_count {
379 return $self->{
'disconnect_count'} = shift
if(@_);
380 $self->{
'disconnect_count'}=0 unless(defined($self->{
'disconnect_count'}));
381 return $self->{
'disconnect_count'};
385 my($self, $arg ) = @_;
388 ($self->{_wait_timeout} = $arg );
390 return $self->{_wait_timeout};
396 return $self->{
'_query_count'} = shift
if(@_);
397 $self->{
'_query_count'}=0 unless(defined($self->{
'_query_count'}));
398 return $self->{
'_query_count'};
403 Example : warn
'Same!' if($dbc->equals($other_dbc));
414 my ( $self, $dbc ) = @_;
415 return 0
if ! defined $dbc;
417 my $undef_str = q{!-undef-!};
421 (($self->host() || $undef_str) eq ($dbc->host() || $undef_str)) &&
422 (($self->dbname() || $undef_str) eq ($dbc->dbname() || $undef_str)) &&
423 (($self->port() || $undef_num) == ($dbc->port() || $undef_num)) &&
424 (($self->username() || $undef_str) eq ($dbc->username() || $undef_str)) &&
425 ($self->driver() eq $dbc->driver())
433 Arg [1] : (optional)
string $arg
434 the name of the driver to use to connect to the database
435 Example : $driver = $db_connection->
driver()
436 Description: Getter / Setter
for the driver
this connection uses.
437 Right now there is no point to setting
this value after a
438 connection has already been established in the constructor.
447 my($self, $arg ) = @_;
450 ($self->{_driver} = $arg );
451 return $self->{_driver};
457 Arg [1] : (optional)
int $arg
458 the TCP or UDP port to use to connect to the database
459 Example : $port = $db_connection->port();
460 Description: Getter / Setter
for the port
this connection uses to communicate
461 to the database daemon. There currently is no point in
462 setting
this value after the connection has already been
463 established by the constructor.
472 my ( $self, $value ) = @_;
474 if ( defined($value) ) {
475 $self->{
'_port'} = $value;
478 return $self->{
'_port'};
484 Arg [1] : (optional)
string $arg
485 The
new value of the database name used by
this connection.
486 Example : $dbname = $db_connection->dbname()
487 Description: Getter/Setter
for the name of the database used by
this 488 connection. There is currently no point in setting
this value
489 after the connection has already been established by the
499 my ($self, $arg ) = @_;
501 ( $self->{_dbname} = $arg );
508 Arg [1] : (optional)
string $arg
509 The
new value of the username used by
this connection.
510 Example : $username = $db_connection->username()
511 Description: Getter/Setter
for the username used by
this 512 connection. There is currently no point in setting
this value
513 after the connection has already been established by the
523 my ($self, $arg ) = @_;
525 ( $self->{_username} = $arg );
531 Arg [1] : (optional)
string $arg
532 The
new value of the username used by
this connection.
533 Example : $user = $db_connection->user()
534 Description: Convenience alias
for the username method
540 my ($self, $arg) = @_;
541 return $self->username($arg);
547 Arg [1] : (optional)
string $arg
548 The
new value of the host used by
this connection.
549 Example : $host = $db_connection->host()
550 Description: Getter/Setter
for the domain name of the database host use by
551 this connection. There is currently no point in setting
552 this value after the connection has already been established
562 my ($self, $arg ) = @_;
564 ( $self->{_host} = $arg );
570 Arg [1] : (optional)
string $arg
571 The
new value of the host used by
this connection.
572 Example : $hostname = $db_connection->hostname()
573 Description: Convenience alias
for the host method
579 my ($self, $arg) = @_;
580 return $self->host($arg);
586 Arg [1] : (optional)
string $arg
587 The
new value of the password used by
this connection.
588 Example : $host = $db_connection->password()
589 Description: Getter/Setter
for the password of to use
for this 590 connection. There is currently no point in setting
591 this value after the connection has already been
592 established by the constructor.
601 my ( $self, $arg ) = @_;
603 if ( defined($arg) ) {
604 # Use an anonymous subroutine that will return the password when 605 # invoked. This will prevent the password from being accidentally 606 # displayed when using e.g. Data::Dumper on a structure containing 607 # one of these objects. 609 $self->{_password} = sub { $arg };
612 return ( ref( $self->{_password} ) && &{ $self->{_password} } ) ||
'';
617 Arg [1] : (optional)
string $arg
618 The
new value of the password used by
this connection.
619 Example : $pass = $db_connection->pass()
620 Description: Convenience alias
for the password method
626 my ($self, $arg) = @_;
627 return $self->password($arg);
630 =head2 disconnect_when_inactive
632 Arg [1] : (optional)
boolean $newval
633 Example : $dbc->disconnect_when_inactive(1);
634 Description: Getter/Setter
for the disconnect_when_inactive flag. If
set 635 to
true this DBConnection will continually disconnect itself
636 when there are no active statement handles and reconnect as
637 necessary. Useful
for farm environments when there can be
638 many (often inactive) open connections to a database at once.
646 sub disconnect_when_inactive {
647 my ( $self, $value ) = @_;
649 if ( defined($value) ) {
650 $self->{
'disconnect_when_inactive'} = $value;
656 return $self->{
'disconnect_when_inactive'};
660 =head2 reconnect_when_lost
662 Arg [1] : (optional)
boolean $newval
663 Example : $dbc->reconnect_when_lost(1);
664 Description: Getter/Setter
for the reconnect_when_lost flag. If
set 665 to
true the db handle will be pinged on each prepare or
do statement
666 and the connection will be reestablished in
case it
's lost. 667 Useful for long running jobs (over 8hrs), which means that the db 668 connection may be lost. 676 sub reconnect_when_lost { 677 my ( $self, $value ) = @_; 679 if ( defined($value) ) { 680 $self->{'reconnect_when_lost
'} = $value; 683 return $self->{'reconnect_when_lost
'}; 691 Example : $locator = $dbc->locator; 692 Description: Constructs a locator string for this database connection 693 that can, for example, be used by the DBLoader module 706 "%s/host=%s;port=%s;dbname=%s;user=%s;pass=%s", 707 ref($self), $self->host(), $self->port(), 708 $self->dbname(), $self->username(), $self->password() ); 714 Arg [1] : DBI Database Handle $value 715 Example : $dbh = $db_connection->db_handle() 716 Description: Getter / Setter for the Database handle used by this 718 Returntype : DBI Database Handle 720 Caller : new, DESTROY 728 # Use the process id ($$) as part of the key for the database handle 729 # this makes this object fork safe. fork() does not makes copies 730 # of the open socket which creates problems when one of the forked 731 # processes disconnects, 732 return $self->{'db_handle
'.$$} = shift if(@_); 733 return $self->{'db_handle
'.$$} if($self->connected); 736 return $self->{'db_handle
'.$$}; 742 Arg [1] : string $string 743 the SQL statement to prepare 744 Example : $sth = $db_connection->prepare("SELECT column FROM table"); 745 Description: Prepares a SQL statement using the internal DBI database handle 746 and returns the DBI statement handle. 747 Returntype : DBI statement handle 748 Exceptions : thrown if the SQL statement is empty, or if the internal 749 database handle is not present 750 Caller : Adaptor modules 760 throw("Attempting to prepare an empty SQL query."); 763 #warn "SQL(".$self->dbname."): " . join(' ', $sql, @_) . "\n"; 764 if ( ($self->reconnect_when_lost()) and (!$self->db_handle()->ping()) ) { 768 my $sth = Bio::EnsEMBL::Hive::DBSQL::StatementHandle->new( $self, $sql, @_ ); 770 $self->query_count($self->query_count()+1); 776 Example : $dbcon->reconnect() 777 Description: Reconnects to the database using the connection attribute 778 information if db_handle no longer pingable. 781 Caller : new, db_handle 788 $self->connected(undef); 789 $self->db_handle(undef); 795 =head2 work_with_db_handle 797 Arg [1] : CodeRef $callback 798 Example : my $q_t = $dbc->work_with_db_handle(sub { my ($dbh) = @_; return $dbh->quote_identifier('table
'); }); 799 Description: Gives access to the DBI handle to execute methods not normally 800 provided by the DBConnection interface 801 Returntype : Any from callback 802 Exceptions : If the callback paramater is not a CodeRef; all other 803 errors are re-thrown after cleanup. 804 Caller : Adaptor modules 809 sub work_with_db_handle { 810 my ($self, $callback) = @_; 811 my $wantarray = wantarray; 812 if( $self->reconnect_when_lost() && !$self->db_handle()->ping()) { 818 @results = $callback->($self->db_handle()) 820 elsif(defined $wantarray) { 821 $results[0] = $callback->($self->db_handle()); 824 $callback->($self->db_handle()); 827 my $original_error = $@; 829 $self->query_count($self->query_count()+1); 831 if($self->disconnect_when_inactive()) { 832 $self->disconnect_if_idle(); 836 warn "Detected an error whilst attempting to disconnect the DBI handle: $@"; 838 if($original_error) { 839 throw "Detected an error when running DBI wrapper callback:\n$original_error"; 842 if(defined $wantarray) { 843 return ($wantarray) ? @results : $results[0]; 848 =head2 prevent_disconnect 850 Arg[1] : CodeRef $callback 851 Example : $dbc->prevent_disconnect(sub { $dbc->do('do something
'); $dbc->do('something
else')}); 852 Description : A wrapper method which prevents database disconnection for the 853 duration of the callback. This is very useful if you need 854 to make multiple database calls avoiding excessive database 855 connection creation/destruction but still want the API 856 to disconnect after the body of work. 858 The value of C<disconnect_when_inactive()> is set to 0 no 859 matter what the original value was & after $callback has 860 been executed. If C<disconnect_when_inactive()> was 861 already set to 0 then this method will be an effective no-op. 863 Exceptions : Raised if there are issues with reverting the connection to its 865 Caller : DBConnection methods 870 sub prevent_disconnect { 871 my ($self, $callback) = @_; 872 my $original_dwi = $self->disconnect_when_inactive(); 873 $self->disconnect_when_inactive(0); 874 eval { $callback->(); }; 875 my $original_error = $@; 877 $self->disconnect_when_inactive($original_dwi); 880 warn "Detected an error whilst attempting to reset disconnect_when_idle: $@"; 882 if($original_error) { 883 throw "Detected an error when running DBI wrapper callback:\n$original_error"; 889 =head2 disconnect_if_idle 892 Example : $dbc->disconnect_if_idle(); 893 Description: Disconnects from the database if there are no currently active 895 It is called automatically by the DESTROY method of the 896 Bio::EnsEMBL::Hive::DBSQL::StatementHandle if the 897 disconect_when_inactive flag is set. 898 Users may call it whenever they want to disconnect. Connection will 899 reestablish on next access to db_handle() 901 1=problem trying to disconnect while a statement handle was still active 903 Caller : Bio::EnsEMBL::Hive::DBSQL::StatementHandle::DESTROY 904 Bio::EnsEMBL::Hive::DBSQL::CoreDBConnection::do 909 sub disconnect_if_idle { 912 return 0 if(!$self->connected()); 913 my $db_handle = $self->db_handle(); 914 return 0 unless(defined($db_handle)); 916 #printf("disconnect_if_idle : kids=%d activekids=%d\n", 917 # $db_handle->{Kids}, $db_handle->{ActiveKids}); 919 #If InactiveDestroy is set, don't disconnect.
920 #To comply with DBI specification
921 return 0
if($db_handle->{InactiveDestroy});
923 #If any statement handles are still active, don't allow disconnection 924 #In this case it is being called before a query has been fully processed 925 #either by not reading all rows of data returned, or not calling ->finish 926 #on the statement handle. Don't disconnect, send warning 927 if($db_handle->{ActiveKids} != 0) {
928 warn(
"Problem disconnect : kids=",$db_handle->{Kids},
929 " activekids=",$db_handle->{ActiveKids},
"\n");
933 $db_handle->disconnect();
934 $self->connected(undef);
935 $self->disconnect_count($self->disconnect_count()+1);
936 #print("DISCONNECT\n"); 937 $self->db_handle(undef);
944 # We have to redefine do() to avoid inheriting Core's do(). 945 # However our do() is no different from any other DBI-enhanced methods in that they are all AUTOLOADed. 946 # So we switch off warnings and enforce AUTOLOADing. 949 no warnings
'redefine';
951 my $autoload = __PACKAGE__.
'::AUTOLOAD';
952 $$autoload = __PACKAGE__.
'::do';
960 $AUTOLOAD=~/^.+::(\w+)$/;
961 my $method_name = $1;
963 # Mechanism to call on the dbc a db_handle method 964 # Used for "prepare" because the latter also exists on dbc 965 if ($method_name =~ /^protected_(.*)/) {
969 # warn "[AUTOLOAD instantiating '$method_name'] ($AUTOLOAD)\n"; 972 # warn "[AUTOLOADed method '$method_name' running] ($AUTOLOAD)\n"; 975 my $db_handle = $self->db_handle() or
throw(
"db_handle returns false" );
976 my $wantarray = wantarray;
981 @retval = $db_handle->$method_name( @_ );
983 $retval[0] = $db_handle->$method_name( @_ );
988 if( $error =~ /MySQL server has gone away/ # mysql version ( test by setting
"SET SESSION wait_timeout=5;" and waiting
for 10sec)
989 or $error =~ /Lost connection to MySQL server during query/ # mysql version ( test by setting
"SET SESSION wait_timeout=5;" and waiting
for 10sec)
990 or $error =~ /server closed the connection unexpectedly/ ) { # pgsql version
992 warn
"trying to reconnect...";
993 # NOTE: parameters set via the hash interface of $dbh will be lost 995 my $db_handle = $self->db_handle() or
throw(
"db_handle returns false" );
997 warn
"trying to re-$method_name...";
999 @retval = $db_handle->$method_name( @_ );
1001 $retval[0] = $db_handle->$method_name( @_ );
1008 if($self->disconnect_when_inactive() && ($method_name !~ /^prepare/)) { # we shouldn
't disconnect right after prepare() otherwise the statement handle would be linked to a closed connection 1009 $self->disconnect_if_idle(); 1012 return $wantarray ? @retval : $retval[0]; 1018 sub DESTROY { } # needed because of AUTOLOAD
public Boolean disconnect_if_idle()
public Bio::EnsEMBL::DBSQL::DBConnection new()