3 Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
4 Copyright [2016-2024] 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>.
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
82 use vars qw(@ISA); # If Ensembl Core code is available, inherit from its'
DBConnection for compatibility.
85 @ISA = (
'Bio::EnsEMBL::DBSQL::DBConnection');
94 Arg [DBNAME] : (optional)
string
95 The name of the database to connect to.
96 Arg [HOST] : (optional)
string
97 The domain name of the database host to connect to.
98 'localhost' by
default.
100 The name of the database user to connect with
101 Arg [PASS] : (optional)
string
102 The password to be used to connect to the database
103 Arg [PORT] : (optional)
int
104 The port to use when connecting to the database
105 3306 by
default if the driver is mysql.
106 Arg [DRIVER] : (optional)
string
107 The type of database driver to use to connect to the DB
109 Arg [DBCONN] : (optional)
110 Open another handle to the same database as another connection
111 If
this argument is specified, no other arguments should be
113 Arg [DISCONNECT_WHEN_INACTIVE]: (optional)
boolean
114 If set to
true, the database connection will be disconnected
115 everytime there are no active statement handles. This is
116 useful when running a lot of jobs on a compute farm
117 which would otherwise keep open a lot of connections to the
118 database. Database connections are automatically reopened
119 when required.Do not use
this option together with RECONNECT_WHEN_LOST.
120 Arg [WAIT_TIMEOUT]: (optional) integer
121 Time in seconds
for the wait_timeout to happen. Time after which
122 the connection is deleted
if not used. By
default this is 28800 (8 hours)
124 So set
this to greater than
this if your connection are getting deleted.
125 Only set
this if you are having problems and know what you are doing.
126 Arg [RECONNECT_WHEN_LOST]: (optional)
boolean
127 In
case you
're reusing the same database connection, i.e. DISCONNECT_WHEN_INACTIVE is
128 set to false and running a job which takes a long time to process (over 8hrs),
129 which means that the db connection may be lost, set this option to true.
130 On each prepare or do statement the db handle will be pinged and the database
131 connection will be reconnected if it's lost.
134 (-user =>
'anonymous',
135 -dbname =>
'homo_sapiens_core_20_34c',
136 -host =>
'ensembldb.ensembl.org',
139 Description: Constructor
for a Database Connection. Any adaptors that require
140 database connectivity should inherit from
this class.
142 Exceptions : thrown
if USER or DBNAME are not specified, or
if the database
143 cannot be connected to.
151 my $class = shift @_;
154 my ($driver, $user, $password, $host, $port, $dbname,
155 $dbconn, $disconnect_when_inactive, $wait_timeout, $reconnect_when_lost)
156 = @flags{qw(-driver -user -pass -host -port -dbname -dbconn
157 -disconnect_when_inactive -wait_timeout -reconnect_when_lost)};
163 if($dbname || $host || $driver || $password || $port || $disconnect_when_inactive || $reconnect_when_lost) {
164 throw(
"Cannot specify other arguments when -DBCONN argument used.");
167 $self->driver($dbconn->driver());
168 $self->host($dbconn->host());
169 $self->port($dbconn->port());
170 $self->username($dbconn->username());
171 $self->password($dbconn->password());
172 $self->dbname($dbconn->dbname());
174 if($dbconn->disconnect_when_inactive()) {
175 $self->disconnect_when_inactive(1);
180 if($driver eq
'mysql') {
181 $user ||
throw(
"-USER argument is required.");
185 if($host eq
"ensembldb.ensembl.org"){
186 if( $dbname =~ /\w+_\w+_\w+_(\d+)/){
193 } elsif($driver eq
'pgsql') {
199 $self->driver($driver);
200 $self->host( $host );
202 $self->username( $user );
203 $self->password( $password );
204 $self->dbname( $dbname );
205 $self->wait_timeout($wait_timeout);
207 if($disconnect_when_inactive) {
208 $self->disconnect_when_inactive($disconnect_when_inactive);
210 if($reconnect_when_lost) {
211 $self->reconnect_when_lost($reconnect_when_lost);
215 # if(defined $dnadb) {
216 # $self->dnadb($dnadb);
224 Example : $dbcon->connect()
225 Description: Connects to the database
using the connection attribute
229 Caller :
new, db_handle
237 if ( $self->connected() ) {
return }
241 if ( defined( $self->db_handle() ) and $self->db_handle()->ping() ) {
242 warn(
"unconnected db_handle is still pingable, "
243 .
"reseting connected boolean\n" );
247 my $dbname = $self->dbname();
249 if ( $self->driver() eq
"Oracle" ) {
251 $dsn =
"DBI:Oracle:";
254 $dbh = DBI->connect( $dsn,
256 $self->username(), $dbname ),
258 {
'RaiseError' => 1,
'PrintError' => 0 } );
261 } elsif ( $self->driver() eq
"ODBC" ) {
263 $dsn = sprintf(
"DBI:ODBC:%s", $self->dbname() );
266 $dbh = DBI->connect( $dsn,
270 'LongReadLen' => 2**16 - 8,
273 'odbc_cursortype' => 2 } );
276 } elsif ( $self->driver() eq
"Sybase" ) {
277 my $dbparam = ($dbname) ?
";database=${dbname}" : q{};
279 $dsn = sprintf(
"DBI:Sybase:server=%s%s;tdsLevel=CS_TDS_495",
280 $self->host(), $dbparam );
283 $dbh = DBI->connect( $dsn,
288 'PrintError' => 0 } );
291 } elsif ( lc( $self->driver() ) eq
'sqlite' ) {
293 throw "We require a dbname to connect to a SQLite database"
296 $dsn = sprintf(
"DBI:SQLite:%s", $dbname );
299 $dbh = DBI->connect( $dsn,
'',
'', {
'RaiseError' => 1, } );
304 my $dbparam = ($dbname) ?
"database=${dbname};" : q{};
306 my $driver = $self->driver();
307 $driver =
'Pg' if($driver eq
'pgsql');
309 $dsn = sprintf(
"DBI:%s:%shost=%s;port=%s",
311 $self->host(), $self->port() );
313 my $parameters = {
'RaiseError' => 1 };
314 $parameters->{
'mysql_local_infile'} = 1
if lc($self->driver()) eq 'mysql';
317 $dbh = DBI->connect( $dsn, $self->username(), $self->password(),
323 if ( !$dbh || $error || !$dbh->ping() ) {
324 warn(
"Could not connect to database "
328 .
" using [$dsn] as a locator:\n"
333 throw(
"Could not connect to database "
337 .
" using [$dsn] as a locator:\n"
341 $self->db_handle($dbh);
343 if ( $self->wait_timeout() ) {
344 my $driver = $self->driver();
346 if( $driver eq
'mysql' ) {
347 $dbh->do(
"SET SESSION wait_timeout=" . $self->wait_timeout() );
349 warn
"Don't know how to set the wait_timeout for '$driver' driver, skipping.\n";
359 Example : $dbcon->connected()
360 Description: Boolean which tells
if DBConnection is connected or not.
361 State is set internally, and external processes should not alter state.
362 Returntype : undef or 1
364 Caller : db_handle, connect, disconnect_if_idle, user processes
372 # Use the process id ($$) as part of the key for the connected flag.
373 # This forces the opening of another connection in a forked subprocess.
374 $self->{
'connected'.$$} = shift
if(@_);
375 return $self->{
'connected'.$$};
378 sub disconnect_count {
380 return $self->{
'disconnect_count'} = shift
if(@_);
381 $self->{
'disconnect_count'}=0 unless(defined($self->{
'disconnect_count'}));
382 return $self->{
'disconnect_count'};
386 my($self, $arg ) = @_;
389 ($self->{_wait_timeout} = $arg );
391 return $self->{_wait_timeout};
397 return $self->{
'_query_count'} = shift
if(@_);
398 $self->{
'_query_count'}=0 unless(defined($self->{
'_query_count'}));
399 return $self->{
'_query_count'};
404 Example : warn
'Same!' if($dbc->equals($other_dbc));
405 Description: Equality checker
for DBConnection objects
415 my ( $self, $dbc ) = @_;
416 return 0
if ! defined $dbc;
418 my $undef_str = q{!-undef-!};
422 (($self->host() || $undef_str) eq ($dbc->host() || $undef_str)) &&
423 (($self->dbname() || $undef_str) eq ($dbc->dbname() || $undef_str)) &&
424 (($self->port() || $undef_num) == ($dbc->port() || $undef_num)) &&
425 (($self->username() || $undef_str) eq ($dbc->username() || $undef_str)) &&
426 ($self->driver() eq $dbc->driver())
434 Arg [1] : (optional)
string $arg
435 the name of the driver to use to connect to the database
436 Example : $driver = $db_connection->driver()
437 Description: Getter / Setter
for the driver
this connection uses.
438 Right now there is no point to setting
this value after a
439 connection has already been established in the constructor.
448 my($self, $arg ) = @_;
451 ($self->{_driver} = $arg );
452 return $self->{_driver};
458 Arg [1] : (optional)
int $arg
459 the TCP or UDP port to use to connect to the database
460 Example : $port = $db_connection->port();
461 Description: Getter / Setter
for the port
this connection uses to communicate
462 to the database daemon. There currently is no point in
463 setting
this value after the connection has already been
464 established by the constructor.
473 my ( $self, $value ) = @_;
475 if ( defined($value) ) {
476 $self->{
'_port'} = $value;
479 return $self->{
'_port'};
485 Arg [1] : (optional)
string $arg
486 The
new value of the database name used by
this connection.
487 Example : $dbname = $db_connection->dbname()
488 Description: Getter/Setter
for the name of the database used by
this
489 connection. There is currently no point in setting
this value
490 after the connection has already been established by the
500 my ($self, $arg ) = @_;
502 ( $self->{_dbname} = $arg );
509 Arg [1] : (optional)
string $arg
510 The
new value of the username used by
this connection.
511 Example : $username = $db_connection->username()
512 Description: Getter/Setter
for the username used by
this
513 connection. There is currently no point in setting
this value
514 after the connection has already been established by the
524 my ($self, $arg ) = @_;
526 ( $self->{_username} = $arg );
532 Arg [1] : (optional)
string $arg
533 The
new value of the username used by
this connection.
534 Example : $user = $db_connection->user()
535 Description: Convenience alias
for the username method
541 my ($self, $arg) = @_;
542 return $self->username($arg);
548 Arg [1] : (optional)
string $arg
549 The
new value of the host used by
this connection.
550 Example : $host = $db_connection->host()
551 Description: Getter/Setter
for the domain name of the database host use by
552 this connection. There is currently no point in setting
553 this value after the connection has already been established
563 my ($self, $arg ) = @_;
565 ( $self->{_host} = $arg );
571 Arg [1] : (optional)
string $arg
572 The
new value of the host used by
this connection.
573 Example : $hostname = $db_connection->hostname()
574 Description: Convenience alias
for the host method
580 my ($self, $arg) = @_;
581 return $self->host($arg);
587 Arg [1] : (optional)
string $arg
588 The
new value of the password used by
this connection.
589 Example : $host = $db_connection->password()
590 Description: Getter/Setter
for the password of to use
for this
591 connection. There is currently no point in setting
592 this value after the connection has already been
593 established by the constructor.
602 my ( $self, $arg ) = @_;
604 if ( defined($arg) ) {
605 # Use an anonymous subroutine that will return the password when
606 # invoked. This will prevent the password from being accidentally
607 # displayed when using e.g. Data::Dumper on a structure containing
608 # one of these objects.
610 $self->{_password} = sub { $arg };
613 return ( ref( $self->{_password} ) && &{ $self->{_password} } ) ||
'';
618 Arg [1] : (optional)
string $arg
619 The
new value of the password used by
this connection.
620 Example : $pass = $db_connection->pass()
621 Description: Convenience alias
for the password method
627 my ($self, $arg) = @_;
628 return $self->password($arg);
631 =head2 disconnect_when_inactive
633 Arg [1] : (optional)
boolean $newval
634 Example : $dbc->disconnect_when_inactive(1);
635 Description: Getter/Setter
for the disconnect_when_inactive flag. If set
636 to
true this DBConnection will continually disconnect itself
637 when there are no active statement handles and reconnect as
638 necessary. Useful
for farm environments when there can be
639 many (often inactive) open connections to a database at once.
647 sub disconnect_when_inactive {
648 my ( $self, $value ) = @_;
650 if ( defined($value) ) {
651 $self->{
'disconnect_when_inactive'} = $value;
653 $self->disconnect_if_idle();
657 return $self->{
'disconnect_when_inactive'};
661 =head2 reconnect_when_lost
663 Arg [1] : (optional)
boolean $newval
664 Example : $dbc->reconnect_when_lost(1);
665 Description: Getter/Setter
for the reconnect_when_lost flag. If set
666 to
true the db handle will be pinged on each prepare or
do statement
667 and the connection will be reestablished in
case it
's lost.
668 Useful for long running jobs (over 8hrs), which means that the db
669 connection may be lost.
677 sub reconnect_when_lost {
678 my ( $self, $value ) = @_;
680 if ( defined($value) ) {
681 $self->{'reconnect_when_lost
'} = $value;
684 return $self->{'reconnect_when_lost
'};
692 Example : $locator = $dbc->locator;
693 Description: Constructs a locator string for this database connection
694 that can, for example, be used by the DBLoader module
707 "%s/host=%s;port=%s;dbname=%s;user=%s;pass=%s",
708 ref($self), $self->host(), $self->port(),
709 $self->dbname(), $self->username(), $self->password() );
715 Arg [1] : DBI Database Handle $value
716 Example : $dbh = $db_connection->db_handle()
717 Description: Getter / Setter for the Database handle used by this
719 Returntype : DBI Database Handle
721 Caller : new, DESTROY
729 # Use the process id ($$) as part of the key for the database handle
730 # this makes this object fork safe. fork() does not makes copies
731 # of the open socket which creates problems when one of the forked
732 # processes disconnects,
733 return $self->{'db_handle
'.$$} = shift if(@_);
734 return $self->{'db_handle
'.$$} if($self->connected);
737 return $self->{'db_handle
'.$$};
743 Arg [1] : string $string
744 the SQL statement to prepare
745 Example : $sth = $db_connection->prepare("SELECT column FROM table");
746 Description: Prepares a SQL statement using the internal DBI database handle
747 and returns the DBI statement handle.
748 Returntype : DBI statement handle
749 Exceptions : thrown if the SQL statement is empty, or if the internal
750 database handle is not present
751 Caller : Adaptor modules
761 throw("Attempting to prepare an empty SQL query.");
764 #warn "SQL(".$self->dbname."): " . join(' ', $sql, @_) . "\n";
765 if ( ($self->reconnect_when_lost()) and (!$self->db_handle()->ping()) ) {
769 my $sth = Bio::EnsEMBL::Hive::DBSQL::StatementHandle->new( $self, $sql, @_ );
771 $self->query_count($self->query_count()+1);
777 Example : $dbcon->reconnect()
778 Description: Reconnects to the database using the connection attribute
779 information if db_handle no longer pingable.
782 Caller : new, db_handle
789 $self->connected(undef);
790 $self->db_handle(undef);
796 =head2 work_with_db_handle
798 Arg [1] : CodeRef $callback
799 Example : my $q_t = $dbc->work_with_db_handle(sub { my ($dbh) = @_; return $dbh->quote_identifier('table
'); });
800 Description: Gives access to the DBI handle to execute methods not normally
801 provided by the DBConnection interface
802 Returntype : Any from callback
803 Exceptions : If the callback paramater is not a CodeRef; all other
804 errors are re-thrown after cleanup.
805 Caller : Adaptor modules
810 sub work_with_db_handle {
811 my ($self, $callback) = @_;
812 my $wantarray = wantarray;
813 if( $self->reconnect_when_lost() && !$self->db_handle()->ping()) {
819 @results = $callback->($self->db_handle())
821 elsif(defined $wantarray) {
822 $results[0] = $callback->($self->db_handle());
825 $callback->($self->db_handle());
828 my $original_error = $@;
830 $self->query_count($self->query_count()+1);
832 if($self->disconnect_when_inactive()) {
833 $self->disconnect_if_idle();
837 warn "Detected an error whilst attempting to disconnect the DBI handle: $@";
839 if($original_error) {
840 throw "Detected an error when running DBI wrapper callback:\n$original_error";
843 if(defined $wantarray) {
844 return ($wantarray) ? @results : $results[0];
849 =head2 prevent_disconnect
851 Arg[1] : CodeRef $callback
852 Example : $dbc->prevent_disconnect(sub { $dbc->do('do something
'); $dbc->do('something
else')});
853 Description : A wrapper method which prevents database disconnection for the
854 duration of the callback. This is very useful if you need
855 to make multiple database calls avoiding excessive database
856 connection creation/destruction but still want the API
857 to disconnect after the body of work.
859 The value of C<disconnect_when_inactive()> is set to 0 no
860 matter what the original value was & after $callback has
861 been executed. If C<disconnect_when_inactive()> was
862 already set to 0 then this method will be an effective no-op.
864 Exceptions : Raised if there are issues with reverting the connection to its
866 Caller : DBConnection methods
871 sub prevent_disconnect {
872 my ($self, $callback) = @_;
873 my $original_dwi = $self->disconnect_when_inactive();
874 $self->disconnect_when_inactive(0);
875 eval { $callback->(); };
876 my $original_error = $@;
878 $self->disconnect_when_inactive($original_dwi);
881 warn "Detected an error whilst attempting to reset disconnect_when_idle: $@";
883 if($original_error) {
884 throw "Detected an error when running DBI wrapper callback:\n$original_error";
890 =head2 disconnect_if_idle
893 Example : $dbc->disconnect_if_idle();
894 Description: Disconnects from the database if there are no currently active
896 It is called automatically by the DESTROY method of the
897 Bio::EnsEMBL::Hive::DBSQL::StatementHandle if the
898 disconect_when_inactive flag is set.
899 Users may call it whenever they want to disconnect. Connection will
900 reestablish on next access to db_handle()
902 1=problem trying to disconnect while a statement handle was still active
904 Caller : Bio::EnsEMBL::Hive::DBSQL::StatementHandle::DESTROY
905 Bio::EnsEMBL::Hive::DBSQL::CoreDBConnection::do
910 sub disconnect_if_idle {
913 return 0 if(!$self->connected());
914 my $db_handle = $self->db_handle();
915 return 0 unless(defined($db_handle));
917 #printf("disconnect_if_idle : kids=%d activekids=%d\n",
918 # $db_handle->{Kids}, $db_handle->{ActiveKids});
920 #If InactiveDestroy is set, don't disconnect.
921 #To comply with DBI specification
922 return 0
if($db_handle->{InactiveDestroy});
924 #If any statement handles are still active, don't allow disconnection
925 #In this case it is being called before a query has been fully processed
926 #either by not reading all rows of data returned, or not calling ->finish
927 #on the statement handle. Don't disconnect, send warning
928 if($db_handle->{ActiveKids} != 0) {
929 warn(
"Problem disconnect : kids=",$db_handle->{Kids},
930 " activekids=",$db_handle->{ActiveKids},
"\n");
934 $db_handle->disconnect();
935 $self->connected(undef);
936 $self->disconnect_count($self->disconnect_count()+1);
937 #print("DISCONNECT\n");
938 $self->db_handle(undef);
945 # We have to redefine do() to avoid inheriting Core's do().
946 # However our do() is no different from any other DBI-enhanced methods in that they are all AUTOLOADed.
947 # So we switch off warnings and enforce AUTOLOADing.
950 no warnings
'redefine';
952 my $autoload = __PACKAGE__.
'::AUTOLOAD';
953 $$autoload = __PACKAGE__.
'::do';
961 $AUTOLOAD=~/^.+::(\w+)$/;
962 my $method_name = $1;
964 # Mechanism to call on the dbc a db_handle method
965 # Used for "prepare" because the latter also exists on dbc
966 if ($method_name =~ /^protected_(.*)/) {
970 # warn "[AUTOLOAD instantiating '$method_name'] ($AUTOLOAD)\n";
973 # warn "[AUTOLOADed method '$method_name' running] ($AUTOLOAD)\n";
976 my $db_handle = $self->db_handle() or
throw(
"db_handle returns false" );
977 my $wantarray = wantarray;
981 #warn "SQL(".$self->dbname."): " . $_[0] . "\n" if ($method_name eq 'do') || ($method_name =~ /^select/);
983 @retval = $db_handle->$method_name( @_ );
985 $retval[0] = $db_handle->$method_name( @_ );
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