Mercurial > repos > mahtabm > ensembl
diff variant_effect_predictor/Bio/EnsEMBL/DBSQL/DBConnection.pm @ 0:1f6dce3d34e0
Uploaded
| author | mahtabm |
|---|---|
| date | Thu, 11 Apr 2013 02:01:53 -0400 |
| parents | |
| children |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/variant_effect_predictor/Bio/EnsEMBL/DBSQL/DBConnection.pm Thu Apr 11 02:01:53 2013 -0400 @@ -0,0 +1,1117 @@ +=head1 LICENSE + + Copyright (c) 1999-2012 The European Bioinformatics Institute and + Genome Research Limited. All rights reserved. + + This software is distributed under a modified Apache license. + For license details, please see + + http://www.ensembl.org/info/about/code_licence.html + +=head1 CONTACT + + Please email comments or questions to the public Ensembl + developers list at <dev@ensembl.org>. + + Questions may also be sent to the Ensembl help desk at + <helpdesk@ensembl.org>. + +=cut + +=head1 NAME + +Bio::EnsEMBL::DBSQL::DBConnection + +=head1 SYNOPSIS + + $dbc = Bio::EnsEMBL::DBSQL::DBConnection->new( + -user => 'anonymous', + -dbname => 'homo_sapiens_core_20_34c', + -host => 'ensembldb.ensembl.org', + -driver => 'mysql', + ); + + # SQL statements should be created/executed through this modules + # prepare() and do() methods. + + $sth = $dbc->prepare("SELECT something FROM yourtable"); + + $sth->execute(); + + # do something with rows returned ... + + $sth->finish(); + +=head1 DESCRIPTION + +This class is a wrapper around DBIs datbase handle. It provides some +additional functionality such as the ability to automatically disconnect +when inactive and reconnect when needed. + +Generally this class will be used through one of the object adaptors or +the Bio::EnsEMBL::Registry and will not be instantiated directly. + +=head1 METHODS + +=cut + + +package Bio::EnsEMBL::DBSQL::DBConnection; + +use vars qw(@ISA); +use strict; + +use Bio::EnsEMBL::Root; +use DBI; + +use Bio::EnsEMBL::DBSQL::StatementHandle; + +use Bio::EnsEMBL::Utils::Exception qw/deprecate throw info warning/; +use Bio::EnsEMBL::Utils::Argument qw/rearrange/; +use Bio::EnsEMBL::Utils::Scalar qw/assert_ref wrap_array/; +use Bio::EnsEMBL::Utils::SqlHelper; + +@ISA = qw(Bio::EnsEMBL::Root); # for backwards compatibility + +=head2 new + + Arg [DBNAME] : (optional) string + The name of the database to connect to. + Arg [HOST] : (optional) string + The domain name of the database host to connect to. + 'localhost' by default. + Arg [USER] : string + The name of the database user to connect with + Arg [PASS] : (optional) string + The password to be used to connect to the database + Arg [PORT] : (optional) int + The port to use when connecting to the database + 3306 by default if the driver is mysql. + Arg [DRIVER] : (optional) string + The type of database driver to use to connect to the DB + mysql by default. + Arg [DBCONN] : (optional) + Open another handle to the same database as another connection + If this argument is specified, no other arguments should be + specified. + Arg [DISCONNECT_WHEN_INACTIVE]: (optional) boolean + If set to true, the database connection will be disconnected + everytime there are no active statement handles. This is + useful when running a lot of jobs on a compute farm + which would otherwise keep open a lot of connections to the + database. Database connections are automatically reopened + when required.Do not use this option together with RECONNECT_WHEN_CONNECTION_LOST. + Arg [WAIT_TIMEOUT]: (optional) integer + Time in seconds for the wait timeout to happen. Time after which + the connection is deleted if not used. By default this is 28800 (8 hours) + on most systems. + So set this to greater than this if your connection are getting deleted. + Only set this if you are having problems and know what you are doing. + Arg [RECONNECT_WHEN_CONNECTION_LOST]: (optional) boolean + In case you're reusing the same database connection, i.e. DISCONNECT_WHEN_INACTIVE is + set to false and running a job which takes a long time to process (over 8hrs), + which means that the db connection may be lost, set this option to true. + On each prepare or do statement the db handle will be pinged and the database + connection will be reconnected if it's lost. + + Example : $dbc = Bio::EnsEMBL::DBSQL::DBConnection->new + (-user => 'anonymous', + -dbname => 'homo_sapiens_core_20_34c', + -host => 'ensembldb.ensembl.org', + -driver => 'mysql'); + + Description: Constructor for a Database Connection. Any adaptors that require + database connectivity should inherit from this class. + Returntype : Bio::EnsEMBL::DBSQL::DBConnection + Exceptions : thrown if USER or DBNAME are not specified, or if the database + cannot be connected to. + Caller : Bio::EnsEMBL::Utils::ConfigRegistry ( for newer code using the registry) + Bio::EnsEMBL::DBSQL::DBAdaptor ( for old style code) + Status : Stable + +=cut + +sub new { + my $class = shift; + + my ( + $db, $host, $driver, + $user, $password, $port, + $inactive_disconnect, $dbconn, $wait_timeout, $reconnect + ) + = rearrange( [ + 'DBNAME', 'HOST', 'DRIVER', 'USER', 'PASS', 'PORT', + 'DISCONNECT_WHEN_INACTIVE', 'DBCONN', 'WAIT_TIMEOUT', 'RECONNECT_WHEN_CONNECTION_LOST' + ], + @_ + ); + + my $self = {}; + bless $self, $class; + + if($dbconn) { + if($db || $host || $driver || $password || $port || $inactive_disconnect || $reconnect) { + throw("Cannot specify other arguments when -DBCONN argument used."); + } + + $self->driver($dbconn->driver()); + $self->host($dbconn->host()); + $self->port($dbconn->port()); + $self->username($dbconn->username()); + $self->password($dbconn->password()); + $self->dbname($dbconn->dbname()); + + if($dbconn->disconnect_when_inactive()) { + $self->disconnect_when_inactive(1); + } + } else { + $driver ||= 'mysql'; + + if($driver eq 'mysql') { + $user || throw("-USER argument is required."); + $host ||= 'mysql'; + if(!defined($port)){ + $port = 3306; + if($host eq "ensembldb.ensembl.org"){ + if( $db =~ /\w+_\w+_\w+_(\d+)/){ + if($1 >= 48){ + $port = 5306; + } + } + } + } + } + + $wait_timeout ||= 0; + + $self->driver($driver); + $self->host( $host ); + $self->port($port); + $self->username( $user ); + $self->password( $password ); + $self->dbname( $db ); + $self->timeout($wait_timeout); + + if($inactive_disconnect) { + $self->disconnect_when_inactive($inactive_disconnect); + } + if($reconnect) { + $self->reconnect_when_lost($reconnect); + } + } + +# if(defined $dnadb) { +# $self->dnadb($dnadb); +# } + return $self; +} + + +=head2 connect + + Example : $dbcon->connect() + Description: Connects to the database using the connection attribute + information. + Returntype : none + Exceptions : none + Caller : new, db_handle + Status : Stable + +=cut + +sub connect { + my ($self) = @_; + + if ( $self->connected() ) { return } + + $self->connected(1); + + if ( defined( $self->db_handle() ) and $self->db_handle()->ping() ) { + warning( "unconnected db_handle is still pingable, " + . "reseting connected boolean\n" ); + } + + my ( $dsn, $dbh ); + my $dbname = $self->dbname(); + + if ( $self->driver() eq "Oracle" ) { + + $dsn = "DBI:Oracle:"; + + eval { + $dbh = DBI->connect( $dsn, + sprintf( "%s@%s", + $self->username(), $dbname ), + $self->password(), + { 'RaiseError' => 1, 'PrintError' => 0 } ); + }; + + } elsif ( $self->driver() eq "ODBC" ) { + + $dsn = sprintf( "DBI:ODBC:%s", $self->dbname() ); + + eval { + $dbh = DBI->connect( $dsn, + $self->username(), + $self->password(), { + 'LongTruncOk' => 1, + 'LongReadLen' => 2**16 - 8, + 'RaiseError' => 1, + 'PrintError' => 0, + 'odbc_cursortype' => 2 } ); + }; + + } elsif ( $self->driver() eq "Sybase" ) { + my $dbparam = ($dbname) ? ";database=${dbname}" : q{}; + + $dsn = sprintf( "DBI:Sybase:server=%s%s;tdsLevel=CS_TDS_495", + $self->host(), $dbparam ); + + eval { + $dbh = DBI->connect( $dsn, + $self->username(), + $self->password(), { + 'LongTruncOk' => 1, + 'RaiseError' => 1, + 'PrintError' => 0 } ); + }; + + } elsif ( lc( $self->driver() ) eq 'sqlite' ) { + + throw "We require a dbname to connect to a SQLite database" + if !$dbname; + + $dsn = sprintf( "DBI:SQLite:%s", $dbname ); + + eval { + $dbh = DBI->connect( $dsn, '', '', { 'RaiseError' => 1, } ); + }; + + } else { + + my $dbparam = ($dbname) ? "database=${dbname};" : q{}; + + $dsn = sprintf( "DBI:%s:%shost=%s;port=%s", + $self->driver(), $dbparam, + $self->host(), $self->port() ); + + if ( $self->{'disconnect_when_inactive'} ) { + $self->{'count'}++; + if ( $self->{'count'} > 1000 ) { + sleep 1; + $self->{'count'} = 0; + } + } + eval { + $dbh = DBI->connect( $dsn, $self->username(), $self->password(), + { 'RaiseError' => 1 } ); + }; + } + + if ( !$dbh || $@ || !$dbh->ping() ) { + warn( "Could not connect to database " + . $self->dbname() + . " as user " + . $self->username() + . " using [$dsn] as a locator:\n" + . $DBI::errstr ); + + $self->connected(0); + + throw( "Could not connect to database " + . $self->dbname() + . " as user " + . $self->username() + . " using [$dsn] as a locator:\n" + . $DBI::errstr ); + } + + $self->db_handle($dbh); + + if ( $self->timeout() ) { + $dbh->do( "SET SESSION wait_timeout=" . $self->timeout() ); + } + + #print("CONNECT\n"); +} ## end sub connect + + +=head2 connected + + Example : $dbcon->connected() + Description: Boolean which tells if DBConnection is connected or not. + State is set internally, and external processes should not alter state. + Returntype : undef or 1 + Exceptions : none + Caller : db_handle, connect, disconnect_if_idle, user processes + Status : Stable + +=cut + +sub connected { + my $self = shift; + + # Use the process id ($$) as part of the key for the connected flag. + # This forces the opening of another connection in a forked subprocess. + $self->{'connected'.$$} = shift if(@_); + return $self->{'connected'.$$}; +} + +sub disconnect_count { + my $self = shift; + return $self->{'disconnect_count'} = shift if(@_); + $self->{'disconnect_count'}=0 unless(defined($self->{'disconnect_count'})); + return $self->{'disconnect_count'}; +} + +sub timeout{ + my($self, $arg ) = @_; + + (defined $arg) && + ($self->{_timeout} = $arg ); + + return $self->{_timeout}; + +} + +sub query_count { + my $self = shift; + return $self->{'_query_count'} = shift if(@_); + $self->{'_query_count'}=0 unless(defined($self->{'_query_count'})); + return $self->{'_query_count'}; +} + +=head2 equals + + Example : warn 'Same!' if($dbc->equals($other_dbc)); + Description: Equality checker for DBConnection objects + Returntype : boolean + Exceptions : none + Caller : new + Status : Stable + +=cut + + +sub equals { + my ( $self, $dbc ) = @_; + return 0 if ! defined $dbc; + my $return = 0; + my $undef_str = q{!-undef-!}; + my $undef_num = -1; + + $return = 1 if ( + (($self->host() || $undef_str) eq ($dbc->host() || $undef_str)) && + (($self->dbname() || $undef_str) eq ($dbc->dbname() || $undef_str)) && + (($self->port() || $undef_num) == ($dbc->port() || $undef_num)) && + (($self->username() || $undef_str) eq ($dbc->username() || $undef_str)) && + ($self->driver() eq $dbc->driver()) + ); + + return $return; +} + +=head2 driver + + Arg [1] : (optional) string $arg + the name of the driver to use to connect to the database + Example : $driver = $db_connection->driver() + Description: Getter / Setter for the driver this connection uses. + Right now there is no point to setting this value after a + connection has already been established in the constructor. + Returntype : string + Exceptions : none + Caller : new + Status : Stable + +=cut + +sub driver { + my($self, $arg ) = @_; + + (defined $arg) && + ($self->{_driver} = $arg ); + return $self->{_driver}; +} + + +=head2 port + + Arg [1] : (optional) int $arg + the TCP or UDP port to use to connect to the database + Example : $port = $db_connection->port(); + Description: Getter / Setter for the port this connection uses to communicate + to the database daemon. There currently is no point in + setting this value after the connection has already been + established by the constructor. + Returntype : string + Exceptions : none + Caller : new + Status : Stable + +=cut + +sub port { + my ( $self, $value ) = @_; + + if ( defined($value) ) { + $self->{'_port'} = $value; + } + + return $self->{'_port'}; +} + + +=head2 dbname + + Arg [1] : (optional) string $arg + The new value of the database name used by this connection. + Example : $dbname = $db_connection->dbname() + Description: Getter/Setter for the name of the database used by this + connection. There is currently no point in setting this value + after the connection has already been established by the + constructor. + Returntype : string + Exceptions : none + Caller : new + Status : Stable + +=cut + +sub dbname { + my ($self, $arg ) = @_; + ( defined $arg ) && + ( $self->{_dbname} = $arg ); + $self->{_dbname}; +} + + +=head2 username + + Arg [1] : (optional) string $arg + The new value of the username used by this connection. + Example : $username = $db_connection->username() + Description: Getter/Setter for the username used by this + connection. There is currently no point in setting this value + after the connection has already been established by the + constructor. + Returntype : string + Exceptions : none + Caller : new + Status : Stable + +=cut + +sub username { + my ($self, $arg ) = @_; + ( defined $arg ) && + ( $self->{_username} = $arg ); + $self->{_username}; +} + + +=head2 host + + Arg [1] : (optional) string $arg + The new value of the host used by this connection. + Example : $host = $db_connection->host() + Description: Getter/Setter for the domain name of the database host use by + this connection. There is currently no point in setting + this value after the connection has already been established + by the constructor. + Returntype : string + Exceptions : none + Caller : new + Status : Stable + +=cut + +sub host { + my ($self, $arg ) = @_; + ( defined $arg ) && + ( $self->{_host} = $arg ); + $self->{_host}; +} + + +=head2 password + + Arg [1] : (optional) string $arg + The new value of the password used by this connection. + Example : $host = $db_connection->password() + Description: Getter/Setter for the password of to use for this + connection. There is currently no point in setting + this value after the connection has already been + established by the constructor. + Returntype : string + Exceptions : none + Caller : new + Status : Stable + +=cut + +sub password { + my ( $self, $arg ) = @_; + + if ( defined($arg) ) { + # Use an anonymous subroutine that will return the password when + # invoked. This will prevent the password from being accidentally + # displayed when using e.g. Data::Dumper on a structure containing + # one of these objects. + + $self->{_password} = sub { $arg }; + } + + return ( ref( $self->{_password} ) && &{ $self->{_password} } ) || ''; +} + + + +=head2 disconnect_when_inactive + + Arg [1] : (optional) boolean $newval + Example : $db->disconnect_when_inactive(1); + Description: Getter/Setter for the disconnect_when_inactive flag. If set + to true this DBConnection will continually disconnect itself + when there are no active statement handles and reconnect as + necessary. Useful for farm environments when there can be + many (often inactive) open connections to a database at once. + Returntype : boolean + Exceptions : none + Caller : Pipeline + Status : Stable + +=cut + +sub disconnect_when_inactive { + my ( $self, $value ) = @_; + + if ( defined($value) ) { + $self->{'disconnect_when_inactive'} = $value; + if ($value) { + $self->disconnect_if_idle(); + } + } + + return $self->{'disconnect_when_inactive'}; +} + + +=head2 reconnect_when_lost + + Arg [1] : (optional) boolean $newval + Example : $db->reconnect_when_lost(1); + Description: Getter/Setter for the reconnect_when_lost flag. If set + to true the db handle will be pinged on each prepare or do statement + and the connection will be reestablished in case it's lost. + Useful for long running jobs (over 8hrs), which means that the db + connection may be lost. + Returntype : boolean + Exceptions : none + Caller : Pipeline + Status : Stable + +=cut + +sub reconnect_when_lost { + my ( $self, $value ) = @_; + + if ( defined($value) ) { + $self->{'reconnect_when_lost'} = $value; + } + + return $self->{'reconnect_when_lost'}; +} + + + +=head2 locator + + Arg [1] : none + Example : $locator = $dbc->locator; + Description: Constructs a locator string for this database connection + that can, for example, be used by the DBLoader module + Returntype : string + Exceptions : none + Caller : general + Status : Stable + +=cut + + +sub locator { + my ($self) = @_; + + return sprintf( + "%s/host=%s;port=%s;dbname=%s;user=%s;pass=%s", + ref($self), $self->host(), $self->port(), + $self->dbname(), $self->username(), $self->password() ); +} + + +=head2 db_handle + + Arg [1] : DBI Database Handle $value + Example : $dbh = $db_connection->db_handle() + Description: Getter / Setter for the Database handle used by this + database connection. + Returntype : DBI Database Handle + Exceptions : none + Caller : new, DESTROY + Status : Stable + +=cut + +sub db_handle { + my $self = shift; + + # Use the process id ($$) as part of the key for the database handle + # this makes this object fork safe. fork() does not makes copies + # of the open socket which creates problems when one of the forked + # processes disconnects, + return $self->{'db_handle'.$$} = shift if(@_); + return $self->{'db_handle'.$$} if($self->connected); + + $self->connect(); + return $self->{'db_handle'.$$}; +} + + +=head2 prepare + + Arg [1] : string $string + the SQL statement to prepare + Example : $sth = $db_connection->prepare("SELECT column FROM table"); + Description: Prepares a SQL statement using the internal DBI database handle + and returns the DBI statement handle. + Returntype : DBI statement handle + Exceptions : thrown if the SQL statement is empty, or if the internal + database handle is not present + Caller : Adaptor modules + Status : Stable + +=cut + +sub prepare { + my ($self,@args) = @_; + + if( ! $args[0] ) { + throw("Attempting to prepare an empty SQL query."); + } + + #warn "SQL(".$self->dbname."):" . join(' ', @args) . "\n"; + if ( ($self->reconnect_when_lost()) and (!$self->db_handle()->ping()) ) { + $self->reconnect(); + } + my $sth = $self->db_handle->prepare(@args); + + # return an overridden statement handle that provides us with + # the means to disconnect inactive statement handles automatically + bless $sth, "Bio::EnsEMBL::DBSQL::StatementHandle"; + $sth->dbc($self); + $sth->sql($args[0]); + + $self->query_count($self->query_count()+1); + return $sth; +} + +=head2 reconnect + + Example : $dbcon->reconnect() + Description: Reconnects to the database using the connection attribute + information if db_handle no longer pingable. + Returntype : none + Exceptions : none + Caller : new, db_handle + Status : Stable + +=cut + +sub reconnect { + my ($self) = @_; + $self->connected(undef); + $self->db_handle(undef); + $self->connect(); + return; +} + + +=head2 do + + Arg [1] : string $string + the SQL statement to prepare + Example : $sth = $db_connection->do("SELECT column FROM table"); + Description: Executes a SQL statement using the internal DBI database handle. + Returntype : Result of DBI dbh do() method + Exceptions : thrown if the SQL statement is empty, or if the internal + database handle is not present. + Caller : Adaptor modules + Status : Stable + +=cut + +sub do { + my ($self,$string) = @_; + + if( ! $string ) { + throw("Attempting to do an empty SQL query."); + } + + # warn "SQL(".$self->dbname."): $string"; + my $error; + + my $do_result = $self->work_with_db_handle(sub { + my ($dbh) = @_; + my $result = eval { $dbh->do($string) }; + $error = $@ if $@; + return $result; + }); + + throw "Detected an error whilst executing statement '$string': $error" if $error; + + return $do_result; +} + +=head2 work_with_db_handle + + Arg [1] : CodeRef $callback + Example : my $q_t = $dbc->work_with_db_handle(sub { my ($dbh) = @_; return $dbh->quote_identifier('table'); }); + Description: Gives access to the DBI handle to execute methods not normally + provided by the DBConnection interface + Returntype : Any from callback + Exceptions : If the callback paramater is not a CodeRef; all other + errors are re-thrown after cleanup. + Caller : Adaptor modules + Status : Stable + +=cut + +sub work_with_db_handle { + my ($self, $callback) = @_; + my $wantarray = wantarray; + assert_ref($callback, 'CODE', 'callback'); + if( $self->reconnect_when_lost() && !$self->db_handle()->ping()) { + $self->reconnect(); + } + my @results; + eval { + if($wantarray) { + @results = $callback->($self->db_handle()) + } + elsif(defined $wantarray) { + $results[0] = $callback->($self->db_handle()); + } + else { + $callback->($self->db_handle()); + } + }; + my $original_error = $@; + + $self->query_count($self->query_count()+1); + eval { + if($self->disconnect_when_inactive()) { + $self->disconnect_if_idle(); + } + }; + if($@) { + warning "Detected an error whilst attempting to disconnect the DBI handle: $@"; + } + if($original_error) { + throw "Detected an error when running DBI wrapper callback:\n$original_error"; + } + + if(defined $wantarray) { + return ($wantarray) ? @results : $results[0]; + } + return; +} + +=head2 prevent_disconnect + + Arg[1] : CodeRef $callback + Example : $dbc->prevent_disconnect(sub { $dbc->do('do something'); $dbc->do('something else')}); + Description : A wrapper method which prevents database disconnection for the + duration of the callback. This is very useful if you need + to make multiple database calls avoiding excessive database + connection creation/destruction but still want the API + to disconnect after the body of work. + + The value of C<disconnect_when_inactive()> is set to 0 no + matter what the original value was & after $callback has + been executed. If C<disconnect_when_inactive()> was + already set to 0 then this method will be an effective no-op. + Returntype : None + Exceptions : Raised if there are issues with reverting the connection to its + default state. + Caller : DBConnection methods + Status : Beta + +=cut + +sub prevent_disconnect { + my ($self, $callback) = @_; + assert_ref($callback, 'CODE', 'callback'); + my $original_dwi = $self->disconnect_when_inactive(); + $self->disconnect_when_inactive(0); + eval { $callback->(); }; + my $original_error = $@; + eval { + $self->disconnect_when_inactive($original_dwi); + }; + if($@) { + warning "Detected an error whilst attempting to reset disconnect_when_idle: $@"; + } + if($original_error) { + throw "Detected an error when running DBI wrapper callback:\n$original_error"; + } + return; +} + +=head2 quote_identifier + + Arg [n] : scalar/ArrayRef + Example : $q = $dbc->quote_identifier('table', 'other'); + $q = $dbc->quote_identifier([undef, 'my_db', 'table'], [undef, 'my_db', 'other']); + Description: Executes the DBI C<quote_identifier> method which will quote + any given string using the database driver's quote character. + Returntype : ArrayRef + Exceptions : None + Caller : General + Status : Stable + +=cut + +sub quote_identifier { + my ($self, @identifiers) = @_; + return $self->work_with_db_handle(sub { + my ($dbh) = @_; + my @output; + foreach my $identifier_array (@identifiers) { + $identifier_array = wrap_array($identifier_array); + push(@output, $dbh->quote_identifier(@{$identifier_array})); + } + return \@output; + }); +} + +=head2 disconnect_if_idle + + Arg [1] : none + Example : $dbc->disconnect_if_idle(); + Description: Disconnects from the database if there are no currently active + statement handles. + It is called automatically by the DESTROY method of the + Bio::EnsEMBL::DBSQL::SQL::StatementHandle if the + disconect_when_inactive flag is set. + Users may call it whenever they want to disconnect. Connection will + reestablish on next access to db_handle() + Returntype : 1 or 0 + 1=problem trying to disconnect while a statement handle was still active + Exceptions : none + Caller : Bio::EnsEMBL::DBSQL::SQL::StatementHandle::DESTROY + Bio::EnsEMBL::DBSQL::DBConnection::do + Status : Stable + +=cut + +sub disconnect_if_idle { + my $self = shift; + + return 0 if(!$self->connected()); + my $db_handle = $self->db_handle(); + return 0 unless(defined($db_handle)); + + #printf("disconnect_if_idle : kids=%d activekids=%d\n", + # $db_handle->{Kids}, $db_handle->{ActiveKids}); + + #If InactiveDestroy is set, don't disconnect. + #To comply with DBI specification + return 0 if($db_handle->{InactiveDestroy}); + + #If any statement handles are still active, don't allow disconnection + #In this case it is being called before a query has been fully processed + #either by not reading all rows of data returned, or not calling ->finish + #on the statement handle. Don't disconnect, send warning + if($db_handle->{ActiveKids} != 0) { + warn("Problem disconnect : kids=",$db_handle->{Kids}, + " activekids=",$db_handle->{ActiveKids},"\n"); + return 1; + } + + $db_handle->disconnect(); + $self->connected(undef); + $self->disconnect_count($self->disconnect_count()+1); + #print("DISCONNECT\n"); + $self->db_handle(undef); + return 0; +} + + +=head2 add_limit_clause + + Arg [1] : string $sql + Arg [2] : int $max_number + Example : my $new_sql = $dbc->add_limit_clause($sql,$max_number); + Description: Giving an SQL statement, it adds a limit clause, dependent on the database + (in MySQL, should add a LIMIT at the end, MSSQL uses a TOP clause) + Returntype : String containing the new valid SQL statement + Exceptions : none + Caller : general + Status : at risk + +=cut + + +sub add_limit_clause{ + my $self = shift; + my $sql = shift; + my $max_number = shift; + + my $new_sql = ''; + if ($self->driver eq 'mysql'){ + $new_sql = $sql . ' LIMIT ' . $max_number; + } + elsif ($self->driver eq 'odbc'){ + #need to get anything after the SELECT statement + $sql =~ /select(.*)/i; + $new_sql = 'SELECT TOP ' . $max_number . $1; + } + else{ + warning("Not possible to convert $sql to an unknow database driver: ", $self->driver, " no limit applied"); + $new_sql = $sql; + } + return $new_sql; +} + + +=head2 from_date_to_seconds + + Arg [1] : date $date + Example : my $string = $dbc->from_date_to_seconds($date); + Description: Giving a string representing a column of type date + applies the database function to convert to the number of seconds from 01-01-1970 + Returntype : string + Exceptions : none + Caller : general + Status : at risk + +=cut + +sub from_date_to_seconds{ + my $self= shift; + my $column = shift; + + my $string; + if ($self->driver eq 'mysql'){ + $string = "UNIX_TIMESTAMP($column)"; + } + elsif ($self->driver eq 'odbc'){ + $string = "DATEDIFF(second,'JAN 1 1970',$column)"; + } + else{ + warning("Not possible to convert $column due to an unknown database driver: ", $self->driver); + return ''; + } + return $string; +} + + +=head2 from_seconds_to_date + + Arg [1] : int $seconds + Example : my $string = $dbc->from_seconds_to_date($seconds); + Description: Giving an int representing number of seconds + applies the database function to convert to a date + Returntype : string + Exceptions : none + Caller : general + Status : at risk + +=cut + +sub from_seconds_to_date{ + my $self= shift; + my $seconds = shift; + + my $string; + if ($self->driver eq 'mysql'){ + if ($seconds){ + $string = "from_unixtime( ".$seconds.")"; + } + else{ + $string = "\"0000-00-00 00:00:00\""; + } + } + elsif ($self->driver eq 'odbc'){ + if ($seconds){ + $string = "DATEDIFF(date,'JAN 1 1970',$seconds)"; + } + else{ + $string = "\"0000-00-00 00:00:00\""; + } + } + else{ + warning("Not possible to convert $seconds due to an unknown database driver: ", $self->driver); + return ''; + + } + return $string; +} + +=head2 sql_helper + + Example : my $h = $dbc->sql_helper(); + Description: Lazy generated instance of L<Bio::EnsEMBL::Utils::SqlHelper> + which provides useful wrapper methods for interacting with a + DBConnection instance. + Returntype : Bio::EnsEMBL::Utils::SqlHelper + Exceptions : none + Caller : general + Status : Stable + +=cut + +sub sql_helper { + my ($self) = @_; + if(! exists $self->{_sql_helper}) { + my $helper = Bio::EnsEMBL::Utils::SqlHelper->new(-DB_CONNECTION => $self); + $self->{_sql_helper} = $helper; + } + return $self->{_sql_helper}; +} + +#### +#deprecated functions +#### + +=head2 group + + group is no longer available in DBConnection and should be accessed if needed + from an adaptor. + +=cut + +sub group { + my ($self, $arg ) = @_; + ( defined $arg ) && + ( $self->{_group} = $arg ); + deprecate "group should not be called from DBConnection but from an adaptor\n"; + return $self->{_group}; +} + +=head2 species + + species is no longer available in DBConnection and should be accessed if needed + from an adaptor. + +=cut + +sub species { + my ($self, $arg ) = @_; + ( defined $arg ) && + ( $self->{_species} = $arg ); + deprecate "species should not be called from DBConnection but from an adaptor\n"; + return $self->{_species}; +} + +1;