Using multiple databases
From ClassDBI
There are cases when you have the same schema in multiple databases and would like to access two or more databases from the same script without reconnecting every time.
Here is an example of what I use with 0.96. If you know of a better solution, please add it here.
This one caches the connections in a Class hash and returns the current one after the myinit routine is called.
-- gabor@pti.co.il
package My::DBI;
use base 'Class::DBI';
use DBI;
my %dbh;
my $current_dbh;
sub myinit {
my ($dsn) = @_;
if ($dbh{$dsn}) {
$current_dbh = $dbh{$dsn};
} else {
$current_dbh = $dbh{$dsn} = DBI->connect_cached(
$dsn, $username, $password,
{
My::DBI->_default_attributes, # defaults of Class::DBI
}
);
if (not $current_dbh) {
warn "Could not connect to '$dsn' $DBI::errstr";
return 0;
}
}
return 1;
}
sub db_Main {
my $self = shift;
return $current_dbh;
}
1;
# and in the nearby script: My::DBI::myinit($dsn_a); My::DBI->create(....) My::DBI::myinit($dsn_b); My::DBI->create(....)
9/22/04 -- Question: scenario: Two websites on same server set up with virtual hosts. Modperl 2 Apache 2. CDBI::Customer.pm class and Apache::!NewCstmr module to use it to create new customers. Customer.pm. !NewCstmr.pm generates dynamic db connection at run time.
So if you go to https://OneComany.net/new_customer or https://TwoCompny.net/new_customer, then same copy of Apache::NewCstmr and CDBI::Customer do work.
My question is if OneCompany/new_customer is invoked and NewCstmr initializes to OneCompany's database but before its done with its DB transactions, TwoCompany/new_customer is invoked and inits to TwoCompany's database, is OneCompany's database handle changed?
Since the dbh is class data, I think it is. If it were global to NewCstmr, it would be fine. Also, if these two requests happened to be handled by different child processes, it would work, but there is no way to ensure that happen.
Note that if you use multiple databases you should override dbi_commit and dbi_rollback to do the right thing, since the default Class::DBI implementation won't. I.e.
sub dbi_commit() {
my $proto = shift;
$current_dbh->commit(@_);
}
sub dbi_rollback() {
my $proto = shift;
$current_dbh->rollback(@_);
}
--StepanRiha
Note: The other way to fix dbi_commit and dbi_rollback when overriding db_Main is to call:
__PACKAGE__->_remember_handle( 'Main' );
Which lets Ima::DBI know to fetch the $dbh by calling $class->db_Main.
-- Bill Moseley - 23 May 2007
Also, note that in order to have identical behavior to Class::DBI defaults you need to set ChopBlanks => 1 in your connection. It took me a while to figure that one out....
--brian@glassbrian.com
2/2/2005 - This should solve the issue from the 9/22/04 post. We have a case where we're extending Class::DBI and the db_Main issue causes problems due to its class data nature. We have several Class::DBI subclasses hitting different databases/schemas from websites in the same Apache instance, but the connection for one site will show up in another sites log unless we make db_Main instance based (as recommended in the pod). I took the idea from gabor@pti.co.il's post above and tweaked it to solve this problem (forgive me for the semantic changes), but no need for an init routine as first call will do just that.
package My::Package;
use base 'Class::DBI';
# global hash for database handles
my %g_database_handles;
# Get Class::DBI's default dbh options
my $db_options = { __PACKAGE__->_default_attributes() };
{
# default db connection
# this could still be dsn,
# but passwords should be elsewhere
my $_connection_key = 'TESTDB_A';
sub get_connection_key {
return $_connection_key
}
sub set_connection_key {
my ($self, $key) = @_;
$_connection_key = $key;
return;
}
}
sub db_Main {
my ($self) = @_;
my $connection_key = $self->get_connection_key();
unless ($g_database_handles{$connection_key}) {
my ($database,$username,$password)
= $self->getDatabaseConfig($connection_key); #see below
$g_database_handles{$connection_key}
= DBI->connect_cached( $database
, $username
, $password
, $db_options
);
# see brian@glassbrian.com comments
# about attributes and ChopBlanks=>1
unless (exists $g_database_handles{$connection_key}) {
warn ref($self) . ": Could not connect to '$username\@$database' $DBI::errstr";
return undef;
}
}
return $g_database_handles{$connection_key};
}
1;
# in some other file
# use whatever you want here to get the stored connection info,
# this is just a rough example, but easy to throw into a separate module
my %db_connect_info = ('TESTDB_A' => ['dbi:Oracle:some_database','user','password'],
'TESTDB_B' => ['dbi:Oracle:some_other_database','user','password']);
sub getDatabaseConfig {
my ($self, $key) = @_;
return @{$db_connect_info{$key}};
}
--scottsweep@yahoo.com
06/06/2005:
Fixed the typo ($db_group should have been $connection_key).
Added class variable and functions to set which db connection to use.
Just call My::Package->set_connection_key('TESTDB_B'); after use My::Package; to change the db connection.
If getDatabaseConfig is in the same class and loads username/password e.g. with Config::Simple from a file, change $self to __PACKAGE__ to avoid the perl -c errors.
--alexander.hartmaier@t-systems.at
29 June 2006
We found that with the db_Main above we would get errors if the database connection was lost. We now just call DBI->connect_cached every time because DBI->connect_cached provides caching anyway and will also reconnect if the connection is lost. That is we don't maintain a %g_database_handles hash.
--Jed Soane
Update: the problem with calling connect_cached every time is that it will reset attributes to their default state. So if AutoCommit is set on by default but is turned off to start a transaction then calling connect_cached again will re-enable AutoCommit.
I use the method used by Ima::DBI, that is check Active and ping. Ping hits the database, so might be wise to limit the ping frequency.
my %database_registry; my $current_database;
# method to save dsn, etc. in registry by a key
sub register_database { }
# method to set $current_database by key
sub select_database { }
sub db_Main {
my $self = shift;
my $connection = $database_registry{$current_database};
die "db_Main failed to find the connection settings for database key [$current_database]"
unless ref $connection eq 'HASH';
my $dbh = $connection->{dbh};
# Return active dbh
return $dbh if $dbh && $dbh->FETCH( 'Active' ) && $dbh->ping;
# Create a new connection
my %attr = (
$self->_default_attributes,
%{ $connection->{attr} },
AutoCommit => 1,
pg_enable_utf8 => 1, # see DBD::Pg
private_pid => $$, # protect against forks
);
# RaiseError is on so this should die on failures
$connection->{dbh} = DBI->connect_cached( @{$connection}{qw/ dsn user pass /}, \%attr );
return $connection->{dbh};
}
I also have register_database() method that saves the connection info in the hash, and select_database() method.
-- Bill Moseley - 23 May 2007
