Archive Ensembl HomeArchive Ensembl Home
DBConnection.pm
Go to the documentation of this file.
00001 =head1 LICENSE
00002 
00003   Copyright (c) 1999-2012 The European Bioinformatics Institute and
00004   Genome Research Limited.  All rights reserved.
00005 
00006   This software is distributed under a modified Apache license.
00007   For license details, please see
00008 
00009     http://www.ensembl.org/info/about/code_licence.html
00010 
00011 =head1 CONTACT
00012 
00013   Please email comments or questions to the public Ensembl
00014   developers list at <dev@ensembl.org>.
00015 
00016   Questions may also be sent to the Ensembl help desk at
00017   <helpdesk@ensembl.org>.
00018 
00019 =cut
00020 
00021 =head1 NAME
00022 
00023 Bio::EnsEMBL::DBSQL::DBConnection
00024 
00025 =head1 SYNOPSIS
00026 
00027   $dbc = Bio::EnsEMBL::DBSQL::DBConnection->new(
00028     -user   => 'anonymous',
00029     -dbname => 'homo_sapiens_core_20_34c',
00030     -host   => 'ensembldb.ensembl.org',
00031     -driver => 'mysql',
00032   );
00033 
00034   # SQL statements should be created/executed through this modules
00035   # prepare() and do() methods.
00036 
00037   $sth = $dbc->prepare("SELECT something FROM yourtable");
00038 
00039   $sth->execute();
00040 
00041   # do something with rows returned ...
00042 
00043   $sth->finish();
00044 
00045 =head1 DESCRIPTION
00046 
00047 This class is a wrapper around DBIs datbase handle.  It provides some
00048 additional functionality such as the ability to automatically disconnect
00049 when inactive and reconnect when needed.
00050 
00051 Generally this class will be used through one of the object adaptors or
00052 the Bio::EnsEMBL::Registry and will not be instantiated directly.
00053 
00054 =head1 METHODS
00055 
00056 =cut
00057 
00058 
00059 package Bio::EnsEMBL::DBSQL::DBConnection;
00060 
00061 use vars qw(@ISA);
00062 use strict;
00063 
00064 use Bio::EnsEMBL::Root;
00065 use DBI;
00066 
00067 use Bio::EnsEMBL::DBSQL::StatementHandle;
00068 
00069 use Bio::EnsEMBL::Utils::Exception qw/deprecate throw info warning/;
00070 use Bio::EnsEMBL::Utils::Argument qw/rearrange/;
00071 use Bio::EnsEMBL::Utils::Scalar qw/assert_ref wrap_array/;
00072 use Bio::EnsEMBL::Utils::SqlHelper;
00073 
00074 @ISA = qw(Bio::EnsEMBL::Root); # for backwards compatibility
00075 
00076 =head2 new
00077 
00078   Arg [DBNAME] : (optional) string
00079                  The name of the database to connect to.
00080   Arg [HOST] : (optional) string
00081                The domain name of the database host to connect to.  
00082                'localhost' by default. 
00083   Arg [USER] : string
00084                The name of the database user to connect with 
00085   Arg [PASS] : (optional) string
00086                The password to be used to connect to the database
00087   Arg [PORT] : (optional) int
00088                The port to use when connecting to the database
00089                3306 by default if the driver is mysql.
00090   Arg [DRIVER] : (optional) string
00091                  The type of database driver to use to connect to the DB
00092                  mysql by default.
00093   Arg [DBCONN] : (optional)
00094                  Open another handle to the same database as another connection
00095                  If this argument is specified, no other arguments should be
00096                  specified.
00097   Arg [DISCONNECT_WHEN_INACTIVE]: (optional) boolean
00098                  If set to true, the database connection will be disconnected
00099                  everytime there are no active statement handles. This is
00100                  useful when running a lot of jobs on a compute farm
00101                  which would otherwise keep open a lot of connections to the
00102                  database.  Database connections are automatically reopened
00103                  when required.Do not use this option together with RECONNECT_WHEN_CONNECTION_LOST.
00104   Arg [WAIT_TIMEOUT]: (optional) integer
00105                  Time in seconds for the wait timeout to happen. Time after which
00106                  the connection is deleted if not used. By default this is 28800 (8 hours)
00107                  on most systems. 
00108                  So set this to greater than this if your connection are getting deleted.
00109                  Only set this if you are having problems and know what you are doing.
00110   Arg [RECONNECT_WHEN_CONNECTION_LOST]: (optional) boolean
00111                  In case you're reusing the same database connection, i.e. DISCONNECT_WHEN_INACTIVE is 
00112                  set to false and running a job which takes a long time to process (over 8hrs), 
00113                  which means that the db connection may be lost, set this option to true. 
00114                  On each prepare or do statement the db handle will be pinged and the database 
00115                  connection will be reconnected if it's lost.
00116                 
00117   Example    : $dbc = Bio::EnsEMBL::DBSQL::DBConnection->new
00118                   (-user   => 'anonymous',
00119                    -dbname => 'homo_sapiens_core_20_34c',
00120                    -host   => 'ensembldb.ensembl.org',
00121                    -driver => 'mysql');
00122 
00123   Description: Constructor for a Database Connection. Any adaptors that require
00124                database connectivity should inherit from this class.
00125   Returntype : Bio::EnsEMBL::DBSQL::DBConnection
00126   Exceptions : thrown if USER or DBNAME are not specified, or if the database
00127                cannot be connected to.
00128   Caller     : Bio::EnsEMBL::Utils::ConfigRegistry ( for newer code using the registry)
00129                Bio::EnsEMBL::DBSQL::DBAdaptor        ( for old style code)
00130   Status     : Stable
00131 
00132 =cut
00133 
00134 sub new {
00135   my $class = shift;
00136 
00137   my (
00138     $db,                  $host,     $driver,
00139     $user,                $password, $port,
00140     $inactive_disconnect, $dbconn,   $wait_timeout, $reconnect
00141     )
00142     = rearrange( [
00143       'DBNAME', 'HOST', 'DRIVER', 'USER', 'PASS', 'PORT',
00144       'DISCONNECT_WHEN_INACTIVE', 'DBCONN', 'WAIT_TIMEOUT', 'RECONNECT_WHEN_CONNECTION_LOST'
00145     ],
00146     @_
00147     );
00148 
00149   my $self = {};
00150   bless $self, $class;
00151 
00152   if($dbconn) {
00153     if($db || $host || $driver || $password || $port || $inactive_disconnect || $reconnect) {
00154       throw("Cannot specify other arguments when -DBCONN argument used.");
00155     }
00156 
00157     $self->driver($dbconn->driver());
00158     $self->host($dbconn->host());
00159     $self->port($dbconn->port());
00160     $self->username($dbconn->username());
00161     $self->password($dbconn->password());
00162     $self->dbname($dbconn->dbname());
00163 
00164     if($dbconn->disconnect_when_inactive()) {
00165       $self->disconnect_when_inactive(1);
00166     }
00167   } else {
00168     $driver ||= 'mysql';
00169     
00170     if($driver eq 'mysql') {
00171         $user || throw("-USER argument is required.");
00172         $host ||= 'mysql';
00173         if(!defined($port)){
00174             $port   = 3306;
00175             if($host eq "ensembldb.ensembl.org"){
00176                 if( $db =~ /\w+_\w+_\w+_(\d+)/){
00177                     if($1 >= 48){
00178                         $port = 5306;
00179                     }
00180                 }
00181             }
00182         }
00183     }
00184 
00185     $wait_timeout   ||= 0;
00186 
00187     $self->driver($driver);
00188     $self->host( $host );
00189     $self->port($port);
00190     $self->username( $user );
00191     $self->password( $password );
00192     $self->dbname( $db );
00193     $self->timeout($wait_timeout);
00194 
00195     if($inactive_disconnect) {
00196       $self->disconnect_when_inactive($inactive_disconnect);
00197     }
00198     if($reconnect) {
00199       $self->reconnect_when_lost($reconnect);
00200     }
00201   }
00202 
00203 #  if(defined $dnadb) {
00204 #    $self->dnadb($dnadb);
00205 #  }
00206   return $self;
00207 }
00208 
00209 
00210 =head2 connect
00211 
00212   Example    : $dbcon->connect()
00213   Description: Connects to the database using the connection attribute 
00214                information.
00215   Returntype : none
00216   Exceptions : none
00217   Caller     : new, db_handle
00218   Status     : Stable
00219 
00220 =cut
00221 
00222 sub connect {
00223   my ($self) = @_;
00224 
00225   if ( $self->connected() ) { return }
00226 
00227   $self->connected(1);
00228 
00229   if ( defined( $self->db_handle() ) and $self->db_handle()->ping() ) {
00230     warning(   "unconnected db_handle is still pingable, "
00231              . "reseting connected boolean\n" );
00232   }
00233 
00234   my ( $dsn, $dbh );
00235   my $dbname = $self->dbname();
00236 
00237   if ( $self->driver() eq "Oracle" ) {
00238 
00239     $dsn = "DBI:Oracle:";
00240 
00241     eval {
00242       $dbh = DBI->connect( $dsn,
00243                            sprintf( "%s@%s",
00244                                     $self->username(), $dbname ),
00245                            $self->password(),
00246                            { 'RaiseError' => 1, 'PrintError' => 0 } );
00247     };
00248 
00249   } elsif ( $self->driver() eq "ODBC" ) {
00250 
00251     $dsn = sprintf( "DBI:ODBC:%s", $self->dbname() );
00252 
00253     eval {
00254       $dbh = DBI->connect( $dsn,
00255                            $self->username(),
00256                            $self->password(), {
00257                              'LongTruncOk'     => 1,
00258                              'LongReadLen'     => 2**16 - 8,
00259                              'RaiseError'      => 1,
00260                              'PrintError'      => 0,
00261                              'odbc_cursortype' => 2 } );
00262     };
00263 
00264   } elsif ( $self->driver() eq "Sybase" ) {
00265     my $dbparam = ($dbname) ? ";database=${dbname}" : q{};
00266 
00267     $dsn = sprintf( "DBI:Sybase:server=%s%s;tdsLevel=CS_TDS_495",
00268                     $self->host(), $dbparam );
00269 
00270     eval {
00271       $dbh = DBI->connect( $dsn,
00272                            $self->username(),
00273                            $self->password(), {
00274                              'LongTruncOk' => 1,
00275                              'RaiseError'  => 1,
00276                              'PrintError'  => 0 } );
00277     };
00278 
00279   } elsif ( lc( $self->driver() ) eq 'sqlite' ) {
00280 
00281     throw "We require a dbname to connect to a SQLite database"
00282       if !$dbname;
00283 
00284     $dsn = sprintf( "DBI:SQLite:%s", $dbname );
00285 
00286     eval {
00287       $dbh = DBI->connect( $dsn, '', '', { 'RaiseError' => 1, } );
00288     };
00289 
00290   } else {
00291 
00292     my $dbparam = ($dbname) ? "database=${dbname};" : q{};
00293 
00294     $dsn = sprintf( "DBI:%s:%shost=%s;port=%s",
00295                     $self->driver(), $dbparam,
00296                     $self->host(),   $self->port() );
00297 
00298     if ( $self->{'disconnect_when_inactive'} ) {
00299       $self->{'count'}++;
00300       if ( $self->{'count'} > 1000 ) {
00301         sleep 1;
00302         $self->{'count'} = 0;
00303       }
00304     }
00305     eval {
00306       $dbh = DBI->connect( $dsn, $self->username(), $self->password(),
00307                            { 'RaiseError' => 1 } );
00308     };
00309   }
00310 
00311   if ( !$dbh || $@ || !$dbh->ping() ) {
00312     warn(   "Could not connect to database "
00313           . $self->dbname()
00314           . " as user "
00315           . $self->username()
00316           . " using [$dsn] as a locator:\n"
00317           . $DBI::errstr );
00318 
00319     $self->connected(0);
00320 
00321     throw(   "Could not connect to database "
00322            . $self->dbname()
00323            . " as user "
00324            . $self->username()
00325            . " using [$dsn] as a locator:\n"
00326            . $DBI::errstr );
00327   }
00328 
00329   $self->db_handle($dbh);
00330 
00331   if ( $self->timeout() ) {
00332     $dbh->do( "SET SESSION wait_timeout=" . $self->timeout() );
00333   }
00334 
00335   #print("CONNECT\n");
00336 } ## end sub connect
00337 
00338 
00339 =head2 connected
00340 
00341   Example    : $dbcon->connected()
00342   Description: Boolean which tells if DBConnection is connected or not.
00343                State is set internally, and external processes should not alter state.
00344   Returntype : undef or 1
00345   Exceptions : none
00346   Caller     : db_handle, connect, disconnect_if_idle, user processes
00347   Status     : Stable
00348 
00349 =cut
00350 
00351 sub connected {
00352   my $self = shift;
00353 
00354   # Use the process id ($$) as part of the key for the connected flag.
00355   # This forces the opening of another connection in a forked subprocess.
00356   $self->{'connected'.$$} = shift if(@_);
00357   return $self->{'connected'.$$};
00358 }
00359 
00360 sub disconnect_count {
00361   my $self = shift;
00362   return $self->{'disconnect_count'} = shift if(@_);
00363   $self->{'disconnect_count'}=0 unless(defined($self->{'disconnect_count'}));
00364   return $self->{'disconnect_count'};
00365 }
00366 
00367 sub timeout{
00368   my($self, $arg ) = @_;
00369 
00370   (defined $arg) &&
00371     ($self->{_timeout} = $arg );
00372 
00373   return $self->{_timeout};
00374 
00375 }
00376 
00377 sub query_count {
00378   my $self = shift;
00379   return $self->{'_query_count'} = shift if(@_);
00380   $self->{'_query_count'}=0 unless(defined($self->{'_query_count'}));
00381   return $self->{'_query_count'};
00382 }
00383 
00384 =head2 equals
00385 
00386   Example    : warn 'Same!' if($dbc->equals($other_dbc));
00387   Description: Equality checker for DBConnection objects
00388   Returntype : boolean
00389   Exceptions : none
00390   Caller     : new
00391   Status     : Stable
00392 
00393 =cut
00394 
00395   
00396 sub equals {
00397   my ( $self, $dbc ) = @_;
00398   return 0 if ! defined $dbc;
00399   my $return = 0;
00400   my $undef_str = q{!-undef-!};
00401   my $undef_num = -1;
00402 
00403   $return = 1 if  ( 
00404     (($self->host() || $undef_str)      eq ($dbc->host() || $undef_str)) &&
00405     (($self->dbname() || $undef_str)    eq ($dbc->dbname() || $undef_str)) &&
00406     (($self->port() || $undef_num)      == ($dbc->port() || $undef_num)) &&
00407     (($self->username() || $undef_str)  eq ($dbc->username() || $undef_str)) &&
00408     ($self->driver() eq $dbc->driver())
00409   );
00410   
00411   return $return;
00412 }
00413 
00414 =head2 driver
00415 
00416   Arg [1]    : (optional) string $arg
00417                the name of the driver to use to connect to the database
00418   Example    : $driver = $db_connection->driver()
00419   Description: Getter / Setter for the driver this connection uses.
00420                Right now there is no point to setting this value after a
00421                connection has already been established in the constructor.
00422   Returntype : string
00423   Exceptions : none
00424   Caller     : new
00425   Status     : Stable
00426 
00427 =cut
00428 
00429 sub driver {
00430   my($self, $arg ) = @_;
00431 
00432   (defined $arg) &&
00433     ($self->{_driver} = $arg );
00434   return $self->{_driver};
00435 }
00436 
00437 
00438 =head2 port
00439 
00440   Arg [1]    : (optional) int $arg
00441                the TCP or UDP port to use to connect to the database
00442   Example    : $port = $db_connection->port();
00443   Description: Getter / Setter for the port this connection uses to communicate
00444                to the database daemon.  There currently is no point in 
00445                setting this value after the connection has already been 
00446                established by the constructor.
00447   Returntype : string
00448   Exceptions : none
00449   Caller     : new
00450   Status     : Stable
00451 
00452 =cut
00453 
00454 sub port {
00455   my ( $self, $value ) = @_;
00456 
00457   if ( defined($value) ) {
00458     $self->{'_port'} = $value;
00459   }
00460 
00461   return $self->{'_port'};
00462 }
00463 
00464 
00465 =head2 dbname
00466 
00467   Arg [1]    : (optional) string $arg
00468                The new value of the database name used by this connection. 
00469   Example    : $dbname = $db_connection->dbname()
00470   Description: Getter/Setter for the name of the database used by this 
00471                connection.  There is currently no point in setting this value
00472                after the connection has already been established by the 
00473                constructor.
00474   Returntype : string
00475   Exceptions : none
00476   Caller     : new
00477   Status     : Stable
00478 
00479 =cut
00480 
00481 sub dbname {
00482   my ($self, $arg ) = @_;
00483   ( defined $arg ) &&
00484     ( $self->{_dbname} = $arg );
00485   $self->{_dbname};
00486 }
00487 
00488 
00489 =head2 username
00490 
00491   Arg [1]    : (optional) string $arg
00492                The new value of the username used by this connection. 
00493   Example    : $username = $db_connection->username()
00494   Description: Getter/Setter for the username used by this 
00495                connection.  There is currently no point in setting this value
00496                after the connection has already been established by the 
00497                constructor.
00498   Returntype : string
00499   Exceptions : none
00500   Caller     : new
00501   Status     : Stable
00502 
00503 =cut
00504 
00505 sub username {
00506   my ($self, $arg ) = @_;
00507   ( defined $arg ) &&
00508     ( $self->{_username} = $arg );
00509   $self->{_username};
00510 }
00511 
00512 
00513 =head2 host
00514 
00515   Arg [1]    : (optional) string $arg
00516                The new value of the host used by this connection. 
00517   Example    : $host = $db_connection->host()
00518   Description: Getter/Setter for the domain name of the database host use by 
00519                this connection.  There is currently no point in setting 
00520                this value after the connection has already been established 
00521                by the constructor.
00522   Returntype : string
00523   Exceptions : none
00524   Caller     : new
00525   Status     : Stable
00526 
00527 =cut
00528 
00529 sub host {
00530   my ($self, $arg ) = @_;
00531   ( defined $arg ) &&
00532     ( $self->{_host} = $arg );
00533   $self->{_host};
00534 }
00535 
00536 
00537 =head2 password
00538 
00539   Arg [1]    : (optional) string $arg
00540                The new value of the password used by this connection.
00541   Example    : $host = $db_connection->password()
00542   Description: Getter/Setter for the password of to use for this
00543                connection.  There is currently no point in setting
00544                this value after the connection has already been
00545                established by the constructor.
00546   Returntype : string
00547   Exceptions : none
00548   Caller     : new
00549   Status     : Stable
00550 
00551 =cut
00552 
00553 sub password {
00554   my ( $self, $arg ) = @_;
00555 
00556   if ( defined($arg) ) {
00557     # Use an anonymous subroutine that will return the password when
00558     # invoked.  This will prevent the password from being accidentally
00559     # displayed when using e.g. Data::Dumper on a structure containing
00560     # one of these objects.
00561 
00562     $self->{_password} = sub { $arg };
00563   }
00564 
00565   return ( ref( $self->{_password} ) && &{ $self->{_password} } ) || '';
00566 }
00567 
00568 
00569 
00570 =head2 disconnect_when_inactive
00571 
00572   Arg [1]    : (optional) boolean $newval
00573   Example    : $db->disconnect_when_inactive(1);
00574   Description: Getter/Setter for the disconnect_when_inactive flag.  If set
00575                to true this DBConnection will continually disconnect itself
00576                when there are no active statement handles and reconnect as
00577                necessary.  Useful for farm environments when there can be
00578                many (often inactive) open connections to a database at once.
00579   Returntype : boolean
00580   Exceptions : none
00581   Caller     : Pipeline
00582   Status     : Stable
00583 
00584 =cut
00585 
00586 sub disconnect_when_inactive {
00587   my ( $self, $value ) = @_;
00588 
00589   if ( defined($value) ) {
00590     $self->{'disconnect_when_inactive'} = $value;
00591     if ($value) {
00592       $self->disconnect_if_idle();
00593     }
00594   }
00595 
00596   return $self->{'disconnect_when_inactive'};
00597 }
00598 
00599 
00600 =head2 reconnect_when_lost
00601 
00602   Arg [1]    : (optional) boolean $newval
00603   Example    : $db->reconnect_when_lost(1);
00604   Description: Getter/Setter for the reconnect_when_lost flag.  If set
00605                to true the db handle will be pinged on each prepare or do statement 
00606                and the connection will be reestablished in case it's lost.
00607                Useful for long running jobs (over 8hrs), which means that the db 
00608                connection may be lost.
00609   Returntype : boolean
00610   Exceptions : none
00611   Caller     : Pipeline
00612   Status     : Stable
00613 
00614 =cut
00615 
00616 sub reconnect_when_lost {
00617   my ( $self, $value ) = @_;
00618 
00619   if ( defined($value) ) {
00620     $self->{'reconnect_when_lost'} = $value;
00621   }
00622 
00623   return $self->{'reconnect_when_lost'};
00624 }
00625 
00626 
00627 
00628 =head2 locator
00629 
00630   Arg [1]    : none
00631   Example    : $locator = $dbc->locator;
00632   Description: Constructs a locator string for this database connection
00633                that can, for example, be used by the DBLoader module
00634   Returntype : string
00635   Exceptions : none
00636   Caller     : general
00637   Status     : Stable
00638 
00639 =cut
00640 
00641 
00642 sub locator {
00643   my ($self) = @_;
00644 
00645   return sprintf(
00646     "%s/host=%s;port=%s;dbname=%s;user=%s;pass=%s",
00647     ref($self),      $self->host(),     $self->port(),
00648     $self->dbname(), $self->username(), $self->password() );
00649 }
00650 
00651 
00652 =head2 db_handle
00653 
00654   Arg [1]    : DBI Database Handle $value
00655   Example    : $dbh = $db_connection->db_handle() 
00656   Description: Getter / Setter for the Database handle used by this
00657                database connection.
00658   Returntype : DBI Database Handle
00659   Exceptions : none
00660   Caller     : new, DESTROY
00661   Status     : Stable
00662 
00663 =cut
00664 
00665 sub db_handle {
00666    my $self = shift;
00667 
00668    # Use the process id ($$) as part of the key for the database handle
00669    # this makes this object fork safe.  fork() does not makes copies
00670    # of the open socket which creates problems when one of the forked
00671    # processes disconnects,
00672    return $self->{'db_handle'.$$} = shift if(@_);
00673    return $self->{'db_handle'.$$} if($self->connected);
00674 
00675    $self->connect();
00676    return $self->{'db_handle'.$$};
00677 }
00678 
00679 
00680 =head2 prepare
00681 
00682   Arg [1]    : string $string
00683                the SQL statement to prepare
00684   Example    : $sth = $db_connection->prepare("SELECT column FROM table");
00685   Description: Prepares a SQL statement using the internal DBI database handle
00686                and returns the DBI statement handle.
00687   Returntype : DBI statement handle
00688   Exceptions : thrown if the SQL statement is empty, or if the internal
00689                database handle is not present
00690   Caller     : Adaptor modules
00691   Status     : Stable
00692 
00693 =cut
00694 
00695 sub prepare {
00696    my ($self,@args) = @_;
00697 
00698    if( ! $args[0] ) {
00699      throw("Attempting to prepare an empty SQL query.");
00700    }
00701 
00702    #warn "SQL(".$self->dbname."):" . join(' ', @args) . "\n";
00703    if ( ($self->reconnect_when_lost()) and (!$self->db_handle()->ping()) ) { 
00704        $self->reconnect();
00705    }
00706    my $sth = $self->db_handle->prepare(@args);
00707 
00708    # return an overridden statement handle that provides us with
00709    # the means to disconnect inactive statement handles automatically
00710    bless $sth, "Bio::EnsEMBL::DBSQL::StatementHandle";
00711    $sth->dbc($self);
00712    $sth->sql($args[0]);
00713 
00714    $self->query_count($self->query_count()+1);
00715    return $sth;
00716 }
00717 
00718 =head2 reconnect
00719 
00720   Example    : $dbcon->reconnect()
00721   Description: Reconnects to the database using the connection attribute 
00722                information if db_handle no longer pingable.
00723   Returntype : none
00724   Exceptions : none
00725   Caller     : new, db_handle
00726   Status     : Stable
00727 
00728 =cut
00729 
00730 sub reconnect {
00731   my ($self) = @_;
00732   $self->connected(undef);
00733   $self->db_handle(undef);
00734   $self->connect();
00735   return;
00736 }
00737 
00738 
00739 =head2 do
00740 
00741   Arg [1]    : string $string
00742                the SQL statement to prepare
00743   Example    : $sth = $db_connection->do("SELECT column FROM table");
00744   Description: Executes a SQL statement using the internal DBI database handle.
00745   Returntype : Result of DBI dbh do() method
00746   Exceptions : thrown if the SQL statement is empty, or if the internal
00747                database handle is not present.
00748   Caller     : Adaptor modules
00749   Status     : Stable
00750 
00751 =cut
00752 
00753 sub do {
00754    my ($self,$string) = @_;
00755 
00756    if( ! $string ) {
00757      throw("Attempting to do an empty SQL query.");
00758    }
00759 
00760    # warn "SQL(".$self->dbname."): $string";
00761    my $error;
00762    
00763    my $do_result = $self->work_with_db_handle(sub {
00764      my ($dbh) = @_;
00765      my $result = eval { $dbh->do($string) };
00766      $error = $@ if $@;
00767      return $result;
00768    });
00769    
00770    throw "Detected an error whilst executing statement '$string': $error" if $error;
00771  
00772    return $do_result;
00773 }
00774 
00775 =head2 work_with_db_handle
00776 
00777   Arg [1]    : CodeRef $callback
00778   Example    : my $q_t = $dbc->work_with_db_handle(sub { my ($dbh) = @_; return $dbh->quote_identifier('table'); });
00779   Description: Gives access to the DBI handle to execute methods not normally
00780                provided by the DBConnection interface
00781   Returntype : Any from callback
00782   Exceptions : If the callback paramater is not a CodeRef; all other 
00783                errors are re-thrown after cleanup.
00784   Caller     : Adaptor modules
00785   Status     : Stable
00786 
00787 =cut
00788 
00789 sub work_with_db_handle {
00790   my ($self, $callback) = @_;
00791   my $wantarray = wantarray;
00792   assert_ref($callback, 'CODE', 'callback');
00793   if( $self->reconnect_when_lost() && !$self->db_handle()->ping()) { 
00794     $self->reconnect();
00795   }
00796   my @results;
00797   eval {
00798     if($wantarray) { 
00799       @results = $callback->($self->db_handle())
00800     }
00801     elsif(defined $wantarray) {
00802       $results[0] = $callback->($self->db_handle());
00803     }
00804     else {
00805       $callback->($self->db_handle());
00806     }
00807   };
00808   my $original_error = $@;
00809   
00810   $self->query_count($self->query_count()+1);
00811   eval {
00812     if($self->disconnect_when_inactive()) {
00813       $self->disconnect_if_idle();
00814     }
00815   };
00816   if($@) {
00817     warning "Detected an error whilst attempting to disconnect the DBI handle: $@";
00818   }
00819   if($original_error) {
00820     throw "Detected an error when running DBI wrapper callback:\n$original_error";
00821   }
00822   
00823   if(defined $wantarray) {
00824     return ($wantarray) ? @results : $results[0];
00825   }
00826   return;
00827 }
00828 
00829 =head2
00830 
00831   Arg[1]      : CodeRef $callback
00832   Example     : $dbc->prevent_disconnect(sub { $dbc->do('do something'); $dbc->do('something else')});
00833   Description : A wrapper method which prevents database disconnection for the
00834                 duration of the callback. This is very useful if you need
00835                 to make multiple database calls avoiding excessive database
00836                 connection creation/destruction but still want the API
00837                 to disconnect after the body of work. 
00838                 
00839                 The value of C<disconnect_when_inactive()> is set to 0 no
00840                 matter what the original value was & after $callback has
00841                 been executed. If C<disconnect_when_inactive()> was 
00842                 already set to 0 then this method will be an effective no-op.
00843   Returntype  : None
00844   Exceptions  : Raised if there are issues with reverting the connection to its
00845                 default state.
00846   Caller      : DBConnection methods
00847   Status      : Beta
00848 
00849 =cut
00850 
00851 sub prevent_disconnect {
00852   my ($self, $callback) = @_;
00853   assert_ref($callback, 'CODE', 'callback');
00854   my $original_dwi = $self->disconnect_when_inactive();
00855   $self->disconnect_when_inactive(0);
00856   eval { $callback->(); };
00857   my $original_error = $@;
00858   eval {
00859     $self->disconnect_when_inactive($original_dwi);    
00860   };
00861   if($@) {
00862     warning "Detected an error whilst attempting to reset disconnect_when_idle: $@";
00863   }
00864   if($original_error) {
00865     throw "Detected an error when running DBI wrapper callback:\n$original_error";
00866   }
00867   return;
00868 }
00869 
00870 =head2 quote_identifier
00871 
00872   Arg [n]    : scalar/ArrayRef
00873   Example    : $q = $dbc->quote_identifier('table', 'other');
00874                $q = $dbc->quote_identifier([undef, 'my_db', 'table'], [undef, 'my_db', 'other']);
00875   Description: Executes the DBI C<quote_identifier> method which will quote
00876                any given string using the database driver's quote character.
00877   Returntype : ArrayRef
00878   Exceptions : None
00879   Caller     : General
00880   Status     : Stable
00881 
00882 =cut
00883 
00884 sub quote_identifier {
00885   my ($self, @identifiers) = @_;
00886   return $self->work_with_db_handle(sub {
00887     my ($dbh) = @_;
00888     my @output;
00889     foreach my $identifier_array (@identifiers) {
00890       $identifier_array = wrap_array($identifier_array);
00891       push(@output, $dbh->quote_identifier(@{$identifier_array}));
00892     }
00893     return \@output;
00894   });
00895 }
00896 
00897 =head2 disconnect_if_idle
00898 
00899   Arg [1]    : none
00900   Example    : $dbc->disconnect_if_idle();
00901   Description: Disconnects from the database if there are no currently active
00902                statement handles. 
00903                It is called automatically by the DESTROY method of the
00904                Bio::EnsEMBL::DBSQL::SQL::StatementHandle if the
00905                disconect_when_inactive flag is set.
00906                Users may call it whenever they want to disconnect. Connection will
00907                reestablish on next access to db_handle()
00908   Returntype : 0,1
00909                1=problem trying to disconnect while a statement handle was still active
00910   Exceptions : none
00911   Caller     : Bio::EnsEMBL::DBSQL::SQL::StatementHandle::DESTROY
00912                Bio::EnsEMBL::DBSQL::DBConnection::do
00913   Status     : Stable
00914 
00915 =cut
00916 
00917 sub disconnect_if_idle {
00918   my $self = shift;
00919 
00920   return 0 if(!$self->connected());
00921   my $db_handle = $self->db_handle();
00922   return 0 unless(defined($db_handle));
00923 
00924   #printf("disconnect_if_idle : kids=%d activekids=%d\n",
00925   #       $db_handle->{Kids}, $db_handle->{ActiveKids});
00926 
00927   #If InactiveDestroy is set, don't disconnect.
00928   #To comply with DBI specification
00929   return 0 if($db_handle->{InactiveDestroy});
00930 
00931   #If any statement handles are still active, don't allow disconnection
00932   #In this case it is being called before a query has been fully processed
00933   #either by not reading all rows of data returned, or not calling ->finish
00934   #on the statement handle.  Don't disconnect, send warning
00935   if($db_handle->{ActiveKids} != 0) {
00936      warn("Problem disconnect : kids=",$db_handle->{Kids},
00937             " activekids=",$db_handle->{ActiveKids},"\n");
00938      return 1;
00939   }
00940   
00941   $db_handle->disconnect();
00942   $self->connected(undef);
00943   $self->disconnect_count($self->disconnect_count()+1);
00944   #print("DISCONNECT\n");
00945   $self->db_handle(undef);
00946   return 0;
00947 }
00948 
00949 
00950 =head2 add_limit_clause
00951 
00952   Arg [1]    : string $sql
00953   Arg [2]    : int $max_number
00954   Example    : my $new_sql = $dbc->add_limit_clause($sql,$max_number);
00955   Description: Giving an SQL statement, it adds a limit clause, dependent on the database 
00956                (in MySQL, should add a LIMIT at the end, MSSQL uses a TOP clause)                                    
00957   Returntype : String containing the new valid SQL statement        
00958   Exceptions : none
00959   Caller     : general
00960   Status     : at risk
00961 
00962 =cut
00963 
00964 
00965 sub add_limit_clause{
00966     my $self = shift;
00967     my $sql = shift;
00968     my $max_number = shift;
00969 
00970     my $new_sql = '';
00971     if ($self->driver eq 'mysql'){
00972         $new_sql = $sql . ' LIMIT ' . $max_number;
00973     }
00974     elsif ($self->driver eq 'odbc'){
00975         #need to get anything after the SELECT statement
00976         $sql =~ /select(.*)/i;
00977         $new_sql = 'SELECT TOP ' . $max_number . $1;
00978     }
00979     else{
00980         warning("Not possible to convert $sql to an unknow database driver: ", $self->driver, " no limit applied");
00981         $new_sql = $sql;
00982     }
00983     return $new_sql;
00984 }
00985 
00986 
00987 =head2 from_date_to_seconds
00988 
00989   Arg [1]    : date $date
00990   Example    : my $string = $dbc->from_date_to_seconds($date);
00991   Description: Giving a string representing a column of type date 
00992                 applies the database function to convert to the number of seconds from 01-01-1970
00993   Returntype : string
00994   Exceptions : none
00995   Caller     : general
00996   Status     : at risk
00997 
00998 =cut
00999 
01000 sub from_date_to_seconds{
01001     my $self=  shift;
01002     my $column = shift;
01003 
01004     my $string;
01005     if ($self->driver eq 'mysql'){
01006         $string = "UNIX_TIMESTAMP($column)";
01007     }
01008     elsif ($self->driver eq 'odbc'){
01009         $string = "DATEDIFF(second,'JAN 1 1970',$column)";
01010     }
01011     else{
01012         warning("Not possible to convert $column due to an unknown database driver: ", $self->driver);
01013         return '';
01014     }    
01015     return $string;
01016 }
01017 
01018 
01019 =head2 from_seconds_to_date
01020 
01021   Arg [1]    : int $seconds
01022   Example    : my $string = $dbc->from_seconds_to_date($seconds);
01023   Description: Giving an int representing number of seconds
01024                 applies the database function to convert to a date 
01025   Returntype : string
01026   Exceptions : none
01027   Caller     : general
01028   Status     : at risk
01029 
01030 =cut
01031 
01032 sub from_seconds_to_date{
01033     my $self=  shift;
01034     my $seconds = shift;
01035 
01036     my $string;
01037     if ($self->driver eq 'mysql'){
01038         if ($seconds){
01039             $string = "from_unixtime( ".$seconds.")";
01040         }
01041         else{
01042             $string = "\"0000-00-00 00:00:00\"";
01043         }
01044     }
01045     elsif ($self->driver eq 'odbc'){
01046         if ($seconds){
01047             $string = "DATEDIFF(date,'JAN 1 1970',$seconds)";
01048         }
01049         else{
01050             $string = "\"0000-00-00 00:00:00\"";
01051         }
01052     }
01053     else{
01054         warning("Not possible to convert $seconds due to an unknown database driver: ", $self->driver);
01055         return '';
01056 
01057     }    
01058     return $string;
01059 }
01060 
01061 =head2 sql_helper
01062 
01063   Example    : my $h = $dbc->sql_helper();
01064   Description: Lazy generated instance of L<Bio::EnsEMBL::Utils::SqlHelper>
01065                which provides useful wrapper methods for interacting with a
01066                DBConnection instance.
01067   Returntype : Bio::EnsEMBL::Utils::SqlHelper
01068   Exceptions : none
01069   Caller     : general
01070   Status     : Stable
01071 
01072 =cut
01073 
01074 sub sql_helper {
01075   my ($self) = @_;
01076   if(! exists $self->{_sql_helper}) {
01077     my $helper = Bio::EnsEMBL::Utils::SqlHelper->new(-DB_CONNECTION => $self);
01078     $self->{_sql_helper} = $helper;
01079   }
01080   return $self->{_sql_helper};
01081 }
01082 
01083 ####
01084 #deprecated functions
01085 ####
01086 
01087 =head2 group
01088 
01089    group is no longer available in DBConnection and should be accessed if needed
01090    from an adaptor.
01091 
01092 =cut
01093 
01094 sub group {
01095   my ($self, $arg ) = @_;
01096   ( defined $arg ) &&
01097     ( $self->{_group} = $arg );
01098   deprecate "group should not be called from DBConnection but from an adaptor\n";
01099   return $self->{_group};
01100 }
01101 
01102 =head2 species
01103 
01104    species is no longer available in DBConnection and should be accessed if needed
01105    from an adaptor.
01106 
01107 =cut
01108 
01109 sub species {
01110   my ($self, $arg ) = @_;
01111   ( defined $arg ) &&
01112     ( $self->{_species} = $arg );
01113   deprecate "species should not be called from DBConnection but from an adaptor\n";
01114   return $self->{_species};
01115 }
01116 
01117 1;