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 <http:
26 Questions may also be sent to the Ensembl help desk at
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
85 Arg [DBNAME] : (optional) string
86 The name of the database to connect to.
87 Arg [HOST] : (optional) string
88 The domain name of the database host to connect to.
89 'localhost' by default.
91 The name of the database user to connect with
92 Arg [PASS] : (optional) string
93 The password to be used to connect to the database
94 Arg [PORT] : (optional) int
95 The port to use when connecting to the database
96 3306 by default if the driver is mysql.
97 Arg [DRIVER] : (optional) string
98 The type of database driver to use to connect to the DB
100 Arg [DBCONN] : (optional)
101 Open another handle to the same database as another connection
102 If this argument is specified, no other arguments should be
104 Arg [DISCONNECT_WHEN_INACTIVE]: (optional) boolean
105 If set to true, the database connection will be disconnected
106 everytime there are no active statement handles. This is
107 useful when running a lot of jobs on a compute farm
108 which would otherwise keep open a lot of connections to the
109 database. Database connections are automatically reopened
110 when required.Do not use this option together with RECONNECT_WHEN_CONNECTION_LOST.
111 Arg [WAIT_TIMEOUT]: (optional) integer
112 Time in seconds for the wait timeout to happen. Time after which
113 the connection is deleted if not used. By default this is 28800 (8 hours)
115 So set this to greater than this if your connection are getting deleted.
116 Only set this if you are having problems and know what you are doing.
117 Arg [RECONNECT_WHEN_CONNECTION_LOST]: (optional) boolean
118 In case you're reusing the same database connection, i.e. DISCONNECT_WHEN_INACTIVE is
119 set to false and running a job which takes a long time to process (over 8hrs),
120 which means that the db connection may be lost, set this option to true.
121 On each prepare or do statement the db handle will be pinged and the database
122 connection will be reconnected if it's lost.
125 (-user => 'anonymous',
126 -dbname => 'homo_sapiens_core_20_34c',
127 -host => 'ensembldb.ensembl.org',
130 Description: Constructor for a Database Connection. Any adaptors that require
131 database connectivity should inherit from this class.
133 Exceptions : thrown if USER or DBNAME are not specified, or if the database
134 cannot be connected to.
145 $db, $host, $driver_arg,
146 $user, $password, $port,
147 $inactive_disconnect, $dbconn, $wait_timeout, $reconnect
150 'DBNAME',
'HOST',
'DRIVER',
'USER',
'PASS',
'PORT',
151 'DISCONNECT_WHEN_INACTIVE',
'DBCONN',
'WAIT_TIMEOUT',
'RECONNECT_WHEN_CONNECTION_LOST'
159 my $driver = $dbconn ? $dbconn->driver() : $driver_arg;
161 if ($driver eq
'pgsql') {
162 deprecate(
"Using 'pgsql' as an alias for the 'Pg' driver is deprecated.");
165 $self->driver($driver);
167 my $driver_class =
'Bio::EnsEMBL::DBSQL::Driver::' . $driver;
168 eval
"require $driver_class"; ## no critic
169 throw(
"Cannot load '$driver_class': $@")
if $@;
170 my $driver_object = $driver_class->new($self);
171 $self->_driver_object($driver_object);
174 if($db || $host || $driver_arg || $password || $port || $inactive_disconnect || $reconnect) {
175 throw(
"Cannot specify other arguments when -DBCONN argument used.");
178 $self->host($dbconn->host());
179 $self->port($dbconn->port());
180 $self->username($dbconn->username());
181 $self->password($dbconn->password());
182 $self->dbname($dbconn->dbname());
184 if($dbconn->disconnect_when_inactive()) {
185 $self->disconnect_when_inactive(1);
189 if($driver eq
'mysql') {
190 $user ||
throw(
"-USER argument is required.");
194 if($host eq
"ensembldb.ensembl.org"){
195 if( $db =~ /\w+_\w+_\w+_(\d+)/){
202 } elsif($driver eq
'pgsql') {
210 $self->host( $host );
212 $self->username( $user );
213 $self->password( $password );
214 $self->dbname( $db );
215 $self->timeout($wait_timeout);
217 if($inactive_disconnect) {
218 $self->disconnect_when_inactive($inactive_disconnect);
221 $self->reconnect_when_lost($reconnect);
225 # if(defined $dnadb) {
226 # $self->dnadb($dnadb);
234 Example : $dbcon->connect()
235 Description: Connects to the database
using the connection attribute
239 Caller :
new, db_handle
247 if ( $self->connected() ) {
return }
251 if ( defined( $self->db_handle() ) and $self->db_handle()->ping() ) {
252 warning(
"unconnected db_handle is still pingable, "
253 .
"reseting connected boolean\n" );
258 my $params = $self->_driver_object->connect_params($self);
260 $dbh = DBI->connect( @{$params}{qw(dsn username password attributes)} );
264 if ( !$dbh || $error || !$dbh->ping() ) {
266 my $dsn = $params->{dsn};
267 warn(
"Could not connect to database "
271 .
" using [$dsn] as a locator:\n"
276 throw(
"Could not connect to database "
280 .
" using [$dsn] as a locator:\n"
284 $self->db_handle($dbh);
286 if ( $self->timeout() ) {
287 $self->_driver_object->set_wait_timeout( $dbh, $self->timeout() );
296 Example : $dbcon->connected()
297 Description: Boolean which tells
if DBConnection is connected or not.
298 State is set internally, and external processes should not alter state.
299 Returntype : undef or 1
301 Caller : db_handle, connect, disconnect_if_idle, user processes
309 # Use the process id ($$) as part of the key for the connected flag.
310 # This forces the opening of another connection in a forked subprocess.
311 $self->{
'connected'.$$} = shift
if(@_);
312 return $self->{
'connected'.$$};
315 sub disconnect_count {
317 return $self->{
'disconnect_count'} = shift
if(@_);
318 $self->{
'disconnect_count'}=0 unless(defined($self->{
'disconnect_count'}));
319 return $self->{
'disconnect_count'};
323 my($self, $arg ) = @_;
326 ($self->{_timeout} = $arg );
328 return $self->{_timeout};
334 return $self->{
'_query_count'} = shift
if(@_);
335 $self->{
'_query_count'}=0 unless(defined($self->{
'_query_count'}));
336 return $self->{
'_query_count'};
341 Example : warn
'Same!' if($dbc->equals($other_dbc));
342 Description: Equality checker
for DBConnection objects
352 my ( $self, $dbc ) = @_;
353 return 0
if ! defined $dbc;
355 my $undef_str = q{!-undef-!};
359 (($self->host() || $undef_str) eq ($dbc->host() || $undef_str)) &&
360 (($self->dbname() || $undef_str) eq ($dbc->dbname() || $undef_str)) &&
361 (($self->port() || $undef_num) == ($dbc->port() || $undef_num)) &&
362 (($self->username() || $undef_str) eq ($dbc->username() || $undef_str)) &&
363 ($self->driver() eq $dbc->driver())
371 Arg [1] : (optional)
string $arg
372 the name of the driver to use to connect to the database
373 Example : $driver = $db_connection->driver()
374 Description: Getter / Setter
for the driver
this connection uses.
375 Right now there is no point to setting
this value after a
376 connection has already been established in the constructor.
385 my($self, $arg ) = @_;
388 ($self->{_driver} = $arg );
389 return $self->{_driver};
395 Arg [1] : (optional)
int $arg
396 the TCP or UDP port to use to connect to the database
397 Example : $port = $db_connection->port();
398 Description: Getter / Setter
for the port
this connection uses to communicate
399 to the database daemon. There currently is no point in
400 setting
this value after the connection has already been
401 established by the constructor.
410 my ( $self, @args ) = @_;
413 $self->{
'_port'} = $args[0];
416 return $self->{
'_port'};
422 Arg [1] : (optional)
string $arg
423 The
new value of the database name used by
this connection.
424 Example : $dbname = $db_connection->dbname()
425 Description: Getter/Setter
for the name of the database used by
this
426 connection. There is currently no point in setting
this value
427 after the connection has already been established by the
437 my ($self, @args ) = @_;
439 ( $self->{_dbname} = $args[0] );
446 Arg [1] : (optional)
string $arg
447 The
new value of the username used by
this connection.
448 Example : $username = $db_connection->username()
449 Description: Getter/Setter
for the username used by
this
450 connection. There is currently no point in setting
this value
451 after the connection has already been established by the
461 my ($self, @args ) = @_;
463 ( $self->{_username} = $args[0] );
469 Arg [1] : (optional)
string $arg
470 The
new value of the username used by
this connection.
471 Example : $user = $db_connection->user()
472 Description: Convenience alias
for the username method
478 my ($self, @args) = @_;
479 return $self->username(@args);
485 Arg [1] : (optional)
string $arg
486 The
new value of the host used by
this connection.
487 Example : $host = $db_connection->host()
488 Description: Getter/Setter
for the domain name of the database host use by
489 this connection. There is currently no point in setting
490 this value after the connection has already been established
500 my ($self, @args ) = @_;
502 ( $self->{_host} = $args[0] );
508 Arg [1] : (optional)
string $arg
509 The
new value of the host used by
this connection.
510 Example : $hostname = $db_connection->hostname()
511 Description: Convenience alias
for the host method
517 my ($self, @args) = @_;
518 return $self->host(@args);
524 Arg [1] : (optional)
string $arg
525 The
new value of the password used by
this connection.
526 Example : $host = $db_connection->password()
527 Description: Getter/Setter
for the password of to use
for this
528 connection. There is currently no point in setting
529 this value after the connection has already been
530 established by the constructor.
539 my ( $self, @args ) = @_;
542 # Use an anonymous subroutine that will return the password when
543 # invoked. This will prevent the password from being accidentally
544 # displayed when using e.g. Data::Dumper on a structure containing
545 # one of these objects.
547 $self->{_password} = sub { $args[0] };
550 return ( ref( $self->{_password} ) && &{ $self->{_password} } ) ||
'';
555 Arg [1] : (optional)
string $arg
556 The
new value of the password used by
this connection.
557 Example : $pass = $db_connection->pass()
558 Description: Convenience alias
for the password method
564 my ($self, @args) = @_;
565 return $self->password(@args);
568 =head2 disconnect_when_inactive
570 Arg [1] : (optional)
boolean $newval
571 Example : $db->disconnect_when_inactive(1);
572 Description: Getter/Setter
for the disconnect_when_inactive flag. If set
573 to
true this DBConnection will continually disconnect itself
574 when there are no active statement handles and reconnect as
575 necessary. Useful
for farm environments when there can be
576 many (often inactive) open connections to a database at once.
584 sub disconnect_when_inactive {
585 my ( $self, $value ) = @_;
587 if ( defined($value) ) {
588 $self->{
'disconnect_when_inactive'} = $value;
590 $self->disconnect_if_idle();
594 return $self->{
'disconnect_when_inactive'};
598 =head2 reconnect_when_lost
600 Arg [1] : (optional)
boolean $newval
601 Example : $db->reconnect_when_lost(1);
602 Description: Getter/Setter
for the reconnect_when_lost flag. If set
603 to
true the db handle will be pinged on each prepare or
do statement
604 and the connection will be reestablished in
case it
's lost.
605 Useful for long running jobs (over 8hrs), which means that the db
606 connection may be lost.
614 sub reconnect_when_lost {
615 my ( $self, $value ) = @_;
617 if ( defined($value) ) {
618 $self->{'reconnect_when_lost
'} = $value;
621 return $self->{'reconnect_when_lost
'};
629 Example : $locator = $dbc->locator;
630 Description: Constructs a locator string for this database connection
631 that can, for example, be used by the DBLoader module
644 "%s/host=%s;port=%s;dbname=%s;user=%s;pass=%s",
645 ref($self), $self->host(), $self->port(),
646 $self->dbname(), $self->username(), $self->password() );
652 Arg [1] : DBI Database Handle $value
653 Example : $dbh = $db_connection->db_handle()
654 Description: Getter / Setter for the Database handle used by this
656 Returntype : DBI Database Handle
658 Caller : new, DESTROY
666 # Use the process id ($$) as part of the key for the database handle
667 # this makes this object fork safe. fork() does not makes copies
668 # of the open socket which creates problems when one of the forked
669 # processes disconnects,
670 return $self->{'db_handle
'.$$} = shift if(@_);
671 return $self->{'db_handle
'.$$} if($self->connected);
674 return $self->{'db_handle
'.$$};
680 Arg [1] : string $string
681 the SQL statement to prepare
682 Example : $sth = $db_connection->prepare("SELECT column FROM table");
683 Description: Prepares a SQL statement using the internal DBI database handle
684 and returns the DBI statement handle.
685 Returntype : DBI statement handle
686 Exceptions : thrown if the SQL statement is empty, or if the internal
687 database handle is not present
688 Caller : Adaptor modules
694 my ($self,@args) = @_;
697 throw("Attempting to prepare an empty SQL query.");
700 #warn "SQL(".$self->dbname."):" . join(' ', @args) . "\n";
701 if ( ($self->reconnect_when_lost()) and (!$self->db_handle()->ping()) ) {
704 my $sth = $self->db_handle->prepare(@args);
706 # return an overridden statement handle that provides us with
707 # the means to disconnect inactive statement handles automatically
708 bless $sth, "Bio::EnsEMBL::DBSQL::StatementHandle";
712 $self->query_count($self->query_count()+1);
717 =head2 prepare_cached
719 Arg [1] : string $string
720 the SQL statement to prepare
721 Example : $sth = $db_connection->prepare_cached("SELECT column FROM table");
722 Description: Prepares a SQL statement using the internal DBI database handle
723 and returns the DBI statement handle. The prepared statement is
724 cached so that it does not have to be prepared again.
726 If only a subset of rows are required, finish() should be called
727 on the object to free up the statement handle.
729 For further information please consult https://metacpan.org/pod/DBI#prepare_cached
730 Returntype : DBI statement handle
731 Exceptions : thrown if the SQL statement is empty, or if the internal
732 database handle is not present
733 Caller : Adaptor modules
734 Status : Experimental
739 my ($self, @args) = @_;
742 throw("Attempting to prepare_cached an empty SQL query.");
745 if ( ( $self->reconnect_when_lost() ) and ( !$self->db_handle()->{Active} ) ) {
748 my $sth = $self->db_handle->prepare_cached( @args );
750 # return an overridden statement handle that provides us with
751 # the means to disconnect inactive statement handles automatically
752 bless $sth, "Bio::EnsEMBL::DBSQL::StatementHandle";
754 $sth->sql( $args[0] );
756 $self->query_count( $self->query_count() + 1 );
763 Example : $dbcon->reconnect()
764 Description: Reconnects to the database using the connection attribute
765 information if db_handle no longer pingable.
768 Caller : new, db_handle
775 $self->connected(undef);
776 $self->db_handle(undef);
784 Arg [1] : string $string
785 the SQL statement to prepare
786 Example : $sth = $db_connection->do("SELECT column FROM table");
787 Description: Executes a SQL statement using the internal DBI database handle.
788 Returntype : Result of DBI dbh do() method
789 Exceptions : thrown if the SQL statement is empty, or if the internal
790 database handle is not present.
791 Caller : Adaptor modules
797 my ($self,$string, $attr, @bind_values) = @_;
800 throw("Attempting to do an empty SQL query.");
803 # warn "SQL(".$self->dbname."): $string";
806 my $do_result = $self->work_with_db_handle(sub {
808 my $result = eval { $dbh->do($string, $attr, @bind_values) };
813 throw "Detected an error whilst executing statement '$string
': $error" if $error;
818 =head2 work_with_db_handle
820 Arg [1] : CodeRef $callback
821 Example : my $q_t = $dbc->work_with_db_handle(sub { my ($dbh) = @_; return $dbh->quote_identifier('table
'); });
822 Description: Gives access to the DBI handle to execute methods not normally
823 provided by the DBConnection interface
824 Returntype : Any from callback
825 Exceptions : If the callback paramater is not a CodeRef; all other
826 errors are re-thrown after cleanup.
827 Caller : Adaptor modules
832 sub work_with_db_handle {
833 my ($self, $callback) = @_;
834 my $wantarray = wantarray;
835 assert_ref($callback, 'CODE
', 'callback
');
836 if( $self->reconnect_when_lost() && !$self->db_handle()->ping()) {
842 @results = $callback->($self->db_handle())
844 elsif(defined $wantarray) {
845 $results[0] = $callback->($self->db_handle());
848 $callback->($self->db_handle());
851 my $original_error = $@;
853 $self->query_count($self->query_count()+1);
855 if($self->disconnect_when_inactive()) {
856 $self->disconnect_if_idle();
860 warning "Detected an error whilst attempting to disconnect the DBI handle: $@";
862 if($original_error) {
863 throw "Detected an error when running DBI wrapper callback:\n$original_error";
866 if(defined $wantarray) {
867 return ($wantarray) ? @results : $results[0];
872 =head2 prevent_disconnect
874 Arg[1] : CodeRef $callback
875 Example : $dbc->prevent_disconnect(sub { $dbc->do('do something
'); $dbc->do('something
else')});
876 Description : A wrapper method which prevents database disconnection for the
877 duration of the callback. This is very useful if you need
878 to make multiple database calls avoiding excessive database
879 connection creation/destruction but still want the API
880 to disconnect after the body of work.
882 The value of C<disconnect_when_inactive()> is set to 0 no
883 matter what the original value was & after $callback has
884 been executed. If C<disconnect_when_inactive()> was
885 already set to 0 then this method will be an effective no-op.
887 Exceptions : Raised if there are issues with reverting the connection to its
889 Caller : DBConnection methods
894 sub prevent_disconnect {
895 my ($self, $callback) = @_;
896 assert_ref($callback, 'CODE
', 'callback
');
897 my $original_dwi = $self->disconnect_when_inactive();
898 $self->disconnect_when_inactive(0);
899 eval { $callback->(); };
900 my $original_error = $@;
902 $self->disconnect_when_inactive($original_dwi);
905 warning "Detected an error whilst attempting to reset disconnect_when_idle: $@";
907 if($original_error) {
908 throw "Detected an error when running DBI wrapper callback:\n$original_error";
913 =head2 quote_identifier
915 Arg [n] : scalar/ArrayRef
916 Example : $q = $dbc->quote_identifier('table
', 'other
');
917 $q = $dbc->quote_identifier([undef, 'my_db
', 'table
'], [undef, 'my_db
', 'other
']);
918 Description: Executes the DBI C<quote_identifier> method which will quote
919 any given string using the database driver's quote character.
920 Returntype : ArrayRef
927 sub quote_identifier {
928 my ($self, @identifiers) = @_;
929 return $self->work_with_db_handle(sub {
932 foreach my $identifier_array (@identifiers) {
933 $identifier_array = wrap_array($identifier_array);
934 push(@output, $dbh->quote_identifier(@{$identifier_array}));
940 =head2 disconnect_if_idle
943 Example : $dbc->disconnect_if_idle();
944 Description: Disconnects from the database
if there are no currently active
946 It is called automatically by the DESTROY method of the
947 Bio::EnsEMBL::DBSQL::SQL::StatementHandle
if the
948 disconect_when_inactive flag is set.
949 Users may call it whenever they want to disconnect. Connection will
950 reestablish on next access to db_handle()
952 1=problem trying to disconnect while a statement handle was still active
954 Caller :
Bio::
EnsEMBL::DBSQL::SQL::StatementHandle::DESTROY
960 sub disconnect_if_idle {
963 return 0
if(!$self->connected());
964 my $db_handle = $self->db_handle();
965 return 0 unless(defined($db_handle));
967 #printf("disconnect_if_idle : kids=%d activekids=%d\n",
968 # $db_handle->{Kids}, $db_handle->{ActiveKids});
970 #If InactiveDestroy is set, don't disconnect.
971 #To comply with DBI specification
972 return 0
if($db_handle->{InactiveDestroy});
974 #If any statement handles are still active, don't allow disconnection
975 #In this case it is being called before a query has been fully processed
976 #either by not reading all rows of data returned, or not calling ->finish
977 #on the statement handle. Don't disconnect, send warning
978 if($db_handle->{ActiveKids} != 0) {
979 warn(
"Problem disconnect : kids=",$db_handle->{Kids},
980 " activekids=",$db_handle->{ActiveKids},
"\n");
984 $db_handle->disconnect();
985 $self->connected(undef);
986 $self->disconnect_count($self->disconnect_count()+1);
987 #print("DISCONNECT\n");
988 $self->db_handle(undef);
993 =head2 add_limit_clause
995 Arg [1] :
string $sql
996 Arg [2] :
int $max_number
997 Example : my $new_sql = $dbc->add_limit_clause($sql,$max_number);
998 Description: Giving an SQL statement, it adds a limit clause, dependent on the database
999 (in MySQL, should add a LIMIT at the end, MSSQL uses a TOP clause)
1000 Returntype : String containing the
new valid SQL statement
1008 sub add_limit_clause{
1011 my $max_number = shift;
1014 if ($self->driver eq
'mysql'){
1015 $new_sql = $sql .
' LIMIT ' . $max_number;
1017 elsif ($self->driver eq
'odbc'){
1018 #need to get anything after the SELECT statement
1019 $sql =~ /select(.*)/i;
1020 $new_sql =
'SELECT TOP ' . $max_number . $1;
1023 warning(
"Not possible to convert $sql to an unknow database driver: ", $self->driver,
" no limit applied");
1030 =head2 from_date_to_seconds
1032 Arg [1] : date $date
1033 Example : my $string = $dbc->from_date_to_seconds($date);
1034 Description: Giving a
string representing a column of type date
1035 applies the database
function to convert to the number of seconds from 01-01-1970
1043 sub from_date_to_seconds{
1044 my ($self, @args) = @_;
1045 return $self->_driver_object->from_date_to_seconds(@args);
1048 =head2 from_seconds_to_date
1050 Arg [1] :
int $seconds
1051 Example : my $string = $dbc->from_seconds_to_date($seconds);
1052 Description: Giving an
int representing number of seconds
1053 applies the database
function to convert to a date
1061 sub from_seconds_to_date {
1063 my $seconds = shift;
1066 return $self->_driver_object->from_seconds_to_date($seconds);
1073 Example : my $h = $dbc->sql_helper();
1074 Description: Lazy generated instance of L<Bio::EnsEMBL::Utils::SqlHelper>
1075 which provides useful wrapper methods
for interacting with a
1076 DBConnection instance.
1086 if(! exists $self->{_sql_helper}) {
1088 $self->{_sql_helper} = $helper;
1090 return $self->{_sql_helper};
1095 Example : my $hash = $dbc->to_hash();
1096 my $new_dbc = $dbc->new(%{$hash});
1097 Description: Provides a hash which is compatible with the
1098 parameters
for DBConnection
's new() method. This can be
1099 useful during serialisation
1110 -HOST => $self->host(),
1111 -PORT => $self->port(),
1112 -USER => $self->username(),
1113 -DRIVER => $self->driver(),
1115 $hash->{'-DBNAME
'} = $self->dbname() if defined $self->dbname();
1116 $hash->{'-PASS
'} = $self->password() if defined $self->password();
1120 =head2 _driver_object
1123 sub _driver_object {
1124 my ($self, @args) = @_;
1125 ($self->{_driver_object}) = @args if @args;
1126 return $self->{_driver_object};