Re: "Dynamic" database handles?

[prev] [thread] [next] [Date index for 2005/01/24]

On Mon, 24 Jan 2005 11:03:46 -0500
William McKee <william@xxxxxxx.xxx> wrote:

> On Sat, Jan 22, 2005 at 06:47:18PM -0600, Jason Gessner wrote:
> > The problem with C::DBI is that your connection is a module variable
> > and is never reinitialized (unless you force it to be).  What this
> > means is that Virtual host A will start accessing Virtual host B's
> > database if that particular apache child has already been used against
> > virtual host A.  Sucks.
>
> But doesn't this assume that you are using the same module to access
> different databases? If you use a different namespace for each database
> table you are accessing, I would think you'd be spared these headaches.
> I know this is not always feasible, esp. for the type of application
> that the OP was describing.

In my case I have several customers that all have the same database
schema but different databases.  They all share some backend code
which is based on CDBI.

For instance, I have a generic 'Users.pm' module that I reuse
between customers.

It would be waaaay too much work maintain multiple customer specific
copies of each module (e.g. 'CustomerA::Users' and 'CustomerB::Users'),
when each is identical except for the name, and the name is only
different to keep the database connections separate.

I use something like the following code in my CDBI base class:

    sub db_Main {
         my $class = shift;
         my ($dsn, $user, $pass) = My::Config->get_config('dbi_connection_info');
         my %attr = $class->_default_attributes();
         return DBI->connect_cached($dsn, $user, $pass, \%attr);
     }


The above code is probably not very efficient, because each time db_Main
is called, it has to fetch the config and attribute information.  And
db_Main is called *very* frequently in Class::DBI land.

It's probably better to cache the $dbh for the duration of the request.
When I get around to it I will probably switch to something like the
following, which was adaptated from code that Perrin Harkins posted on
another list:

     sub db_Main {
         my $dbh;

         if ( $ENV{'MOD_PERL'} and !$Apache::ServerStarting ) {
             $dbh = Apache->request()->pnotes('dbh');
         }

         if ( !$dbh ) {
             my ($dsn, $user, $pass) = My::Config->get_config('dbi_connection_info');
             my %attr = $class->_default_attributes();

             my $dbh = DBI->connect_cached($dsn, $user, $pass, \%attr);

             if ( $ENV{'MOD_PERL'} and !$Apache::ServerStarting ) {
                 Apache->request()->pnotes( 'dbh', $dbh );
             }
         }
         return $dbh;
     }

Under plain CGI, you could cache $dbh in a global variable instead of in
pnotes.


Michael


--
Michael Graham <magog@xxxxxxxx.xxx>