DBI handle management in modules

[Matthew Pressly <mp...@claborn.net>]

I have a number of perl modules that manage their own persistence
to a SQL database via DBI.  I discovered a flaw in my current
approach which was to use an Apache::Singleton::Process
object to create a connection and in modules that needed a
connection, store the connection in a lexically scoped variable
near the top of the module.  The problem I found with this approach
is that I am essentially caching the database connections at module
load time, so if the database connection goes away (which it does
from time to time because of things like the database server getting
restarted), the now broken connection continues to be used.

The code (after quite a bit of distillation) looks basically like:

package MyPersistentObject;
use strict; use warnings;
use MyProject::Dbh;
my $dbh = MyProject::Dbh->instance->dbh;  ###

sub method1 {
   my ($self, $id) = @_;
   $dbh->do(qq[delete from table1 where id=?], undef, $id);
}
#...

package MyProject::Dbh;
use strict; use warnings;
use Apache::Singleton::Process;
use base (Apache::Singleton::Process);
sub _new_instance {
   return DBI->connect($dsn, $user, $pass, {RaiseError => 1});
}

-----
httpd.conf has:
Perlrequire /pathgoeshere/startup.perl

and starutp.perl has:
use Apache::DBI;
----

What I'm basically trying to accomplish is:
1) Make database connection in one place in the code.
2) Not have to pass $dbh all over the place, but instead have modules
that need a database connection get it themselves.
3) Connection only needs to happen once per request.  If the connection
dies during a request, the next query using that request should fail, and
the next request serviced by that child process should get a new connection.
4) I don't want to call 'connect' before every query because the overhead
is too high, even when Apache::DBI returns a "pooled" connection.
5) For one request, only one connection should be used (i.e, the
connection should be a request singleton).
6) Modules that require DBI connections should not be dependent on
a mod_perl environment.  I.e. they should also be usable in command line
scripts.

What I have come up with so far is to rewrite the connection handling as 
follows,
which also seems to remove the need for Apache::DBI:

package MyProject::DbhSingleton;
use Apache::Singleton::Request;
use base (qw Apache::Singleton::Request);
sub _new_instance {
	my $dbh = DBI->connect($dsn, $user, $pass, $attr) or die DBI->errstr;
}

The thing I'm not sure about is when should I be calling this to
get a local copy of $dbh in a module.  I know that calling it only
at module "use" time is not frequent enough, and calling it before
every query is too frequent (too expensive), but I'm not sure of a
happy medium.  For some objects, calling
MyProject::DbhSingleton->instance to
store a dbh as a instance data would suffice, but not in all cases.

I've looked through the guide and done a number of web searches
but have not found anything that directly addresses this.  Any help
would be greatly appreciated

--
mpressly@claborn.net

RE: DBI handle management in modules

[Chris Hutchinson <ch...@hutchinsonsoftware.com>]

Matthew,

Bricolage (http://bricolage.sourceforge.net/) has code which addresses some
of your questions.
They have a mechanism for accessing the dbi handle globally, avoiding
passing it into everything.
May be worth a look.

Regards,
Chris Hutchinson