DBConnection.pm 33.3 KB
Newer Older
1
=head1 LICENSE
2

3
Copyright [1999-2013] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
4

5 6 7 8 9 10 11 12 13 14 15 16 17
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

     http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

=cut
18 19


20
=head1 CONTACT
21

22
  Please email comments or questions to the public Ensembl
Magali Ruffier's avatar
Magali Ruffier committed
23
  developers list at <http://lists.ensembl.org/mailman/listinfo/dev>.
24

25
  Questions may also be sent to the Ensembl help desk at
Magali Ruffier's avatar
Magali Ruffier committed
26
  <http://www.ensembl.org/Help/Contact>.
27

28
=cut
29

30
=head1 NAME
31

32
Bio::EnsEMBL::DBSQL::DBConnection
33

34
=head1 SYNOPSIS
35

36 37 38 39 40 41
  $dbc = Bio::EnsEMBL::DBSQL::DBConnection->new(
    -user   => 'anonymous',
    -dbname => 'homo_sapiens_core_20_34c',
    -host   => 'ensembldb.ensembl.org',
    -driver => 'mysql',
  );
42

43 44
  # SQL statements should be created/executed through this modules
  # prepare() and do() methods.
45

46 47 48 49 50 51 52 53 54
  $sth = $dbc->prepare("SELECT something FROM yourtable");

  $sth->execute();

  # do something with rows returned ...

  $sth->finish();

=head1 DESCRIPTION
55

56 57 58
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.
59

60 61
Generally this class will be used through one of the object adaptors or
the Bio::EnsEMBL::Registry and will not be instantiated directly.
62

63
=head1 METHODS
64 65 66 67 68 69 70 71 72 73 74 75

=cut


package Bio::EnsEMBL::DBSQL::DBConnection;

use vars qw(@ISA);
use strict;

use Bio::EnsEMBL::Root;
use DBI;

76 77
use Bio::EnsEMBL::DBSQL::StatementHandle;

78 79 80 81
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;
82

83
@ISA = qw(Bio::EnsEMBL::Root); # for backwards compatibility
84

Graham McVicker's avatar
Graham McVicker committed
85 86
=head2 new

87
  Arg [DBNAME] : (optional) string
Graham McVicker's avatar
Graham McVicker committed
88 89 90 91 92 93 94 95
                 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
96
  Arg [PORT] : (optional) int
Graham McVicker's avatar
Graham McVicker committed
97
               The port to use when connecting to the database
98
               3306 by default if the driver is mysql.
Graham McVicker's avatar
Graham McVicker committed
99 100 101
  Arg [DRIVER] : (optional) string
                 The type of database driver to use to connect to the DB
                 mysql by default.
102 103 104 105 106 107 108 109 110 111
  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
112
                 when required.Do not use this option together with RECONNECT_WHEN_CONNECTION_LOST.
113 114 115 116 117 118
  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.
119 120 121 122 123 124 125
  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.
                
126
  Example    : $dbc = Bio::EnsEMBL::DBSQL::DBConnection->new
127 128 129
                  (-user   => 'anonymous',
                   -dbname => 'homo_sapiens_core_20_34c',
                   -host   => 'ensembldb.ensembl.org',
Ian Longden's avatar
Ian Longden committed
130
                   -driver => 'mysql');
131

132
  Description: Constructor for a Database Connection. Any adaptors that require
Graham McVicker's avatar
Graham McVicker committed
133
               database connectivity should inherit from this class.
134
  Returntype : Bio::EnsEMBL::DBSQL::DBConnection
Graham McVicker's avatar
Graham McVicker committed
135 136
  Exceptions : thrown if USER or DBNAME are not specified, or if the database
               cannot be connected to.
137
  Caller     : Bio::EnsEMBL::Utils::ConfigRegistry ( for newer code using the registry)
Ian Longden's avatar
Ian Longden committed
138
               Bio::EnsEMBL::DBSQL::DBAdaptor        ( for old style code)
139
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
140 141 142

=cut

143 144 145
sub new {
  my $class = shift;

146 147 148
  my (
    $db,                  $host,     $driver,
    $user,                $password, $port,
149
    $inactive_disconnect, $dbconn,   $wait_timeout, $reconnect
150 151 152
    )
    = rearrange( [
      'DBNAME', 'HOST', 'DRIVER', 'USER', 'PASS', 'PORT',
153
      'DISCONNECT_WHEN_INACTIVE', 'DBCONN', 'WAIT_TIMEOUT', 'RECONNECT_WHEN_CONNECTION_LOST'
154 155 156
    ],
    @_
    );
157

158 159 160
  my $self = {};
  bless $self, $class;

161
  if($dbconn) {
162
    if($db || $host || $driver || $password || $port || $inactive_disconnect || $reconnect) {
163
      throw("Cannot specify other arguments when -DBCONN argument used.");
164 165
    }

166
    $self->driver($dbconn->driver());
167 168
    $self->host($dbconn->host());
    $self->port($dbconn->port());
169 170 171
    $self->username($dbconn->username());
    $self->password($dbconn->password());
    $self->dbname($dbconn->dbname());
172

173
    if($dbconn->disconnect_when_inactive()) {
Graham McVicker's avatar
Graham McVicker committed
174
      $self->disconnect_when_inactive(1);
175 176 177
    }
  } else {
    $driver ||= 'mysql';
178
    
179 180 181 182 183 184 185 186 187 188 189 190 191
    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;
                    }
                }
            }
        }
192 193 194 195
    } elsif($driver eq 'pgsql') {
        if(!defined($port)){
            $port   = 5432;
        }
196 197
    }

198
    $wait_timeout   ||= 0;
199

200
    $self->driver($driver);
201 202
    $self->host( $host );
    $self->port($port);
203 204 205
    $self->username( $user );
    $self->password( $password );
    $self->dbname( $db );
206
    $self->timeout($wait_timeout);
207

208 209 210
    if($inactive_disconnect) {
      $self->disconnect_when_inactive($inactive_disconnect);
    }
211
    if($reconnect) {
212
      $self->reconnect_when_lost($reconnect);
213
    }
214
  }
215

Ian Longden's avatar
Ian Longden committed
216 217 218
#  if(defined $dnadb) {
#    $self->dnadb($dnadb);
#  }
Ian Longden's avatar
Ian Longden committed
219
  return $self;
220 221 222
}


223 224
=head2 connect

225 226 227
  Example    : $dbcon->connect()
  Description: Connects to the database using the connection attribute 
               information.
228 229 230
  Returntype : none
  Exceptions : none
  Caller     : new, db_handle
231
  Status     : Stable
232 233 234 235

=cut

sub connect {
236 237 238
  my ($self) = @_;

  if ( $self->connected() ) { return }
239

240
  $self->connected(1);
241

242
  if ( defined( $self->db_handle() ) and $self->db_handle()->ping() ) {
243 244
    warning(   "unconnected db_handle is still pingable, "
             . "reseting connected boolean\n" );
245 246
  }

247
  my ( $dsn, $dbh );
248
  my $dbname = $self->dbname();
249

250
  if ( $self->driver() eq "Oracle" ) {
251 252 253 254

    $dsn = "DBI:Oracle:";

    eval {
255 256 257 258 259
      $dbh = DBI->connect( $dsn,
                           sprintf( "%s@%s",
                                    $self->username(), $dbname ),
                           $self->password(),
                           { 'RaiseError' => 1, 'PrintError' => 0 } );
260
    };
261

262
  } elsif ( $self->driver() eq "ODBC" ) {
263 264 265 266

    $dsn = sprintf( "DBI:ODBC:%s", $self->dbname() );

    eval {
267 268 269 270 271 272 273 274
      $dbh = DBI->connect( $dsn,
                           $self->username(),
                           $self->password(), {
                             'LongTruncOk'     => 1,
                             'LongReadLen'     => 2**16 - 8,
                             'RaiseError'      => 1,
                             'PrintError'      => 0,
                             'odbc_cursortype' => 2 } );
275
    };
276

277
  } elsif ( $self->driver() eq "Sybase" ) {
278 279 280 281
    my $dbparam = ($dbname) ? ";database=${dbname}" : q{};

    $dsn = sprintf( "DBI:Sybase:server=%s%s;tdsLevel=CS_TDS_495",
                    $self->host(), $dbparam );
282 283

    eval {
284 285 286 287 288 289
      $dbh = DBI->connect( $dsn,
                           $self->username(),
                           $self->password(), {
                             'LongTruncOk' => 1,
                             'RaiseError'  => 1,
                             'PrintError'  => 0 } );
290
    };
291

292 293 294 295 296
  } elsif ( lc( $self->driver() ) eq 'sqlite' ) {

    throw "We require a dbname to connect to a SQLite database"
      if !$dbname;

297
    $dsn = sprintf( "DBI:SQLite:%s", $dbname );
298 299

    eval {
300
      $dbh = DBI->connect( $dsn, '', '', { 'RaiseError' => 1, } );
301 302
    };

303
  } else {
304

305
    my $dbparam = ($dbname) ? "database=${dbname};" : q{};
306

307 308 309
    my $driver = $self->driver();
    $driver = 'Pg' if($driver eq 'pgsql');

310
    $dsn = sprintf( "DBI:%s:%shost=%s;port=%s",
311
                    $driver, $dbparam,
312 313 314
                    $self->host(),   $self->port() );

    if ( $self->{'disconnect_when_inactive'} ) {
315
      $self->{'count'}++;
316
      if ( $self->{'count'} > 1000 ) {
317 318
        sleep 1;
        $self->{'count'} = 0;
319 320
      }
    }
321
    eval {
322
      $dbh = DBI->connect( $dsn, $self->username(), $self->password(),
323
                           { 'RaiseError' => 1 } );
324
    };
325
  }
326
  my $error = $@;
327

328
  if ( !$dbh || $error || !$dbh->ping() ) {
329 330 331 332 333
    warn(   "Could not connect to database "
          . $self->dbname()
          . " as user "
          . $self->username()
          . " using [$dsn] as a locator:\n"
334
          . $error );
335

336
    $self->connected(0);
337

338 339 340 341 342
    throw(   "Could not connect to database "
           . $self->dbname()
           . " as user "
           . $self->username()
           . " using [$dsn] as a locator:\n"
343
           . $error );
344
  }
345

346
  $self->db_handle($dbh);
347 348 349

  if ( $self->timeout() ) {
    $dbh->do( "SET SESSION wait_timeout=" . $self->timeout() );
350
  }
351

352
  #print("CONNECT\n");
353
} ## end sub connect
354

355 356

=head2 connected
357

358 359 360 361 362 363
  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
364 365
  Status     : Stable

366 367 368 369
=cut

sub connected {
  my $self = shift;
370 371 372

  # Use the process id ($$) as part of the key for the connected flag.
  # This forces the opening of another connection in a forked subprocess.
373 374 375 376 377 378 379 380 381
  $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'};
382 383
}

384 385 386 387 388 389 390 391 392 393
sub timeout{
  my($self, $arg ) = @_;

  (defined $arg) &&
    ($self->{_timeout} = $arg );

  return $self->{_timeout};

}

394 395 396 397 398 399 400
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'};
}

401 402
=head2 equals

403 404 405 406 407 408
  Example    : warn 'Same!' if($dbc->equals($other_dbc));
  Description: Equality checker for DBConnection objects
  Returntype : boolean
  Exceptions : none
  Caller     : new
  Status     : Stable
409 410 411 412

=cut

  
413 414
sub equals {
  my ( $self, $dbc ) = @_;
415 416 417 418 419 420 421 422 423 424 425 426 427 428
  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;
429
}
430

Graham McVicker's avatar
Graham McVicker committed
431 432 433 434 435 436 437 438 439 440 441
=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
442
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
443 444 445

=cut

446 447
sub driver {
  my($self, $arg ) = @_;
448

449 450 451 452 453
  (defined $arg) &&
    ($self->{_driver} = $arg );
  return $self->{_driver};
}

Graham McVicker's avatar
Graham McVicker committed
454 455 456 457 458 459 460 461 462 463 464 465 466

=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
467
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
468 469 470

=cut

471
sub port {
472 473 474 475 476
  my ( $self, $value ) = @_;

  if ( defined($value) ) {
    $self->{'_port'} = $value;
  }
477

478
  return $self->{'_port'};
479 480
}

Graham McVicker's avatar
Graham McVicker committed
481 482 483 484 485 486 487 488 489 490 491 492 493

=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
494
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
495 496 497

=cut

498 499 500 501 502 503 504
sub dbname {
  my ($self, $arg ) = @_;
  ( defined $arg ) &&
    ( $self->{_dbname} = $arg );
  $self->{_dbname};
}

Graham McVicker's avatar
Graham McVicker committed
505 506 507 508 509 510 511 512 513 514 515 516 517

=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
518
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
519 520 521

=cut

522 523 524 525 526
sub username {
  my ($self, $arg ) = @_;
  ( defined $arg ) &&
    ( $self->{_username} = $arg );
  $self->{_username};
527 528
}

529 530 531 532 533 534 535 536 537 538 539 540 541 542 543
=head2 user

  Arg [1]    : (optional) string $arg
               The new value of the username used by this connection. 
  Example    : $user = $db_connection->user()
  Description: Convenience alias for the username method
  Returntype : String

=cut

sub user {
  my ($self, $arg) = @_;
  return $self->username($arg);
}

Graham McVicker's avatar
Graham McVicker committed
544

Graham McVicker's avatar
Graham McVicker committed
545
=head2 host
Graham McVicker's avatar
Graham McVicker committed
546 547 548 549 550 551 552 553 554 555 556

  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
557
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
558 559 560

=cut

561
sub host {
562 563 564 565
  my ($self, $arg ) = @_;
  ( defined $arg ) &&
    ( $self->{_host} = $arg );
  $self->{_host};
566 567
}

568 569 570 571 572 573 574 575 576 577 578 579 580 581 582
=head2 hostname

  Arg [1]    : (optional) string $arg
               The new value of the host used by this connection. 
  Example    : $hostname = $db_connection->hostname()
  Description: Convenience alias for the host method
  Returntype : String

=cut

sub hostname {
  my ($self, $arg) = @_;
  return $self->host($arg);
}

Graham McVicker's avatar
Graham McVicker committed
583

Graham McVicker's avatar
Graham McVicker committed
584
=head2 password
Graham McVicker's avatar
Graham McVicker committed
585 586

  Arg [1]    : (optional) string $arg
587
               The new value of the password used by this connection.
Graham McVicker's avatar
Graham McVicker committed
588
  Example    : $host = $db_connection->password()
589 590 591 592
  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.
Graham McVicker's avatar
Graham McVicker committed
593 594 595
  Returntype : string
  Exceptions : none
  Caller     : new
596
  Status     : Stable
Graham McVicker's avatar
Graham McVicker committed
597 598 599

=cut

600
sub password {
601 602 603 604 605 606 607 608 609 610 611 612
  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} } ) || '';
613 614
}

615
=head2 pass
Ian Longden's avatar
Ian Longden committed
616

617 618 619 620 621 622 623 624 625 626 627 628
  Arg [1]    : (optional) string $arg
               The new value of the password used by this connection. 
  Example    : $pass = $db_connection->pass()
  Description: Convenience alias for the password method
  Returntype : String

=cut

sub pass {
  my ($self, $arg) = @_;
  return $self->password($arg);
}
629

630
=head2 disconnect_when_inactive
631

632 633 634 635 636 637 638 639 640 641
  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
642
  Status     : Stable
643 644 645

=cut

646
sub disconnect_when_inactive {
647
  my ( $self, $value ) = @_;
648

649 650 651
  if ( defined($value) ) {
    $self->{'disconnect_when_inactive'} = $value;
    if ($value) {
652
      $self->disconnect_if_idle();
Ian Longden's avatar
Ian Longden committed
653
    }
654
  }
655

656
  return $self->{'disconnect_when_inactive'};
657 658
}

659

660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686
=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'};
}


687

688 689 690 691 692 693 694 695 696
=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
697
  Status     : Stable
698 699 700 701 702

=cut


sub locator {
703
  my ($self) = @_;
704

705 706 707 708
  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() );
709 710 711
}


712 713 714 715 716 717 718 719 720
=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
721
  Status     : Stable
722 723 724 725 726 727

=cut

sub db_handle {
   my $self = shift;

728 729 730 731
   # 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,
732 733
   return $self->{'db_handle'.$$} = shift if(@_);
   return $self->{'db_handle'.$$} if($self->connected);
734

735
   $self->connect();
736
   return $self->{'db_handle'.$$};
737 738 739 740 741 742 743 744 745 746 747 748 749 750
}


=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
751
  Status     : Stable
752 753 754 755

=cut

sub prepare {
756
   my ($self,@args) = @_;
757

758
   if( ! $args[0] ) {
759 760 761
     throw("Attempting to prepare an empty SQL query.");
   }

762
   #warn "SQL(".$self->dbname."):" . join(' ', @args) . "\n";
763 764 765
   if ( ($self->reconnect_when_lost()) and (!$self->db_handle()->ping()) ) { 
       $self->reconnect();
   }
766
   my $sth = $self->db_handle->prepare(@args);
767 768 769 770 771

   # 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);
772
   $sth->sql($args[0]);
773

774
   $self->query_count($self->query_count()+1);
775 776 777
   return $sth;
}

778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794
=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();
795
  return;
796 797
}

798 799 800 801 802 803 804 805 806 807 808

=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
809
  Status     : Stable
810 811 812 813 814 815 816 817 818 819

=cut

sub do {
   my ($self,$string) = @_;

   if( ! $string ) {
     throw("Attempting to do an empty SQL query.");
   }

820
   # warn "SQL(".$self->dbname."): $string";
821 822 823 824 825 826 827 828 829 830 831 832 833
   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;
}
834

835
=head2 work_with_db_handle
836

837 838 839 840 841 842 843 844 845
  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
846

847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868
=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 = $@;
869
  
870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886
  $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;
887 888
}

889
=head2 prevent_disconnect
890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907

  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
908

909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955
=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;
  });
}
956 957 958 959 960 961

=head2 disconnect_if_idle

  Arg [1]    : none
  Example    : $dbc->disconnect_if_idle();
  Description: Disconnects from the database if there are no currently active
962
               statement handles. 
963 964 965
               It is called automatically by the DESTROY method of the
               Bio::EnsEMBL::DBSQL::SQL::StatementHandle if the
               disconect_when_inactive flag is set.
966 967
               Users may call it whenever they want to disconnect. Connection will
               reestablish on next access to db_handle()
Kieron Taylor's avatar
Kieron Taylor committed
968
  Returntype : 1 or 0
969
               1=problem trying to disconnect while a statement handle was still active
970 971 972
  Exceptions : none
  Caller     : Bio::EnsEMBL::DBSQL::SQL::StatementHandle::DESTROY
               Bio::EnsEMBL::DBSQL::DBConnection::do
973
  Status     : Stable
974 975 976 977 978 979

=cut

sub disconnect_if_idle {
  my $self = shift;

980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998
  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;
999
  }
1000 1001 1002 1003 1004
  
  $db_handle->disconnect();
  $self->connected(undef);
  $self->disconnect_count($self->disconnect_count()+1);
  #print("DISCONNECT\n");
1005
  $self->db_handle(undef);
1006
  return 0;
1007 1008
}

1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031

=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'){
1032
        $new_sql = $sql . ' LIMIT ' . $max_number;
1033 1034
    }
    elsif ($self->driver eq 'odbc'){
1035 1036 1037
        #need to get anything after the SELECT statement
        $sql =~ /select(.*)/i;
        $new_sql = 'SELECT TOP ' . $max_number . $1;
1038 1039
    }
    else{
1040 1041
        warning("Not possible to convert $sql to an unknow database driver: ", $self->driver, " no limit applied");
        $new_sql = $sql;
1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065
    }
    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'){
1066
        $string = "UNIX_TIMESTAMP($column)";
1067 1068
    }
    elsif ($self->driver eq 'odbc'){