http://qs321.pair.com?node_id=904168

kyle has asked for the wisdom of the Perl Monks concerning the following question:

Venerable monks,

I'm looking for a module that will cache a database handle for me and correctly handle when the program forks. I know DBIx::Class does this, but I don't need or want the ache of defining a schema and such, just the handle.

I have a program that uses a database. In it, I have a simple function to get a connection to the database.

sub dbh { my ($u,$p) = ('user',':)'); my $dbh = DBI->connect( "dbi:Sybase:server=sql.example.com", $u, $p, { RaiseError => 1 }, ); $dbh->do( $initial_stuff ); $L->debug( "made new database connection $dbh" ); return $dbh; }

That works fine. It would be easy enough to write something that caches that using a state variable.

sub dbh { state $dbh; if ( ! defined $dbh ) { # blah blah bah } return $dbh; }

That way I don't open up a new connection every time I need to talk to the database. Unfortunately, that will get tripped up when I fork.

I know about DBI, fork, and clone. I know how to write the code to get around this. What I want is a module that's already done it for me. Does it exist?

Replies are listed 'Best First'.
Re: Simple DBI handle caching.
by kyle (Abbot) on May 11, 2011 at 16:10 UTC

    I guess that wasn't so bad.

    use English '-no_match_vars'; sub dbh { state $dbh; state $dbh_pid; if ( defined $dbh_pid && $PROCESS_ID != $dbh_pid ) { my $child_dbh = $dbh->clone; $dbh->{InactiveDestroy} = 1; $dbh = $child_dbh; $dbh_pid = $PROCESS_ID; $L->debug( "cloned database connection in PID $dbh_pid" ); } if ( ! defined $dbh ) { my ($u,$p) = ('user',':)'); $dbh = DBI->connect( "dbi:Sybase:server=sql.example.com", $u, $p, { RaiseError => 1 }, ); $dbh->do( $initialization_stuff ); $dbh_pid = $PROCESS_ID; $L->debug( "made new database connection in PID $dbh_pid" ); } return $dbh; }
Re: Simple DBI handle caching.
by anonymized user 468275 (Curate) on May 11, 2011 at 15:23 UTC
    I couldn't find such a thing either. I think I'd settle for the child having its own db handle. It won't make any difference to inherent elapsed time( because e.g. login becomes simultaneous anyway if the child does it instead), only that due to the resource impact from increasing concurrent db sessions, which itself seems inevitable anyway.

    Update: I tend to use Parallel::Forkmanager for multiple DBI sessions and have the parent only manage its children rather than do any DB stuff itself during the parallel processing phase.

    One world, one people

Re: Simple DBI handle caching.
by derby (Abbot) on May 11, 2011 at 16:39 UTC
Re: Simple DBI handle caching.
by Your Mother (Archbishop) on May 11, 2011 at 17:28 UTC
    ...don't need or want the ache of defining a schema...

    Schema generation with DBIC is generally trivial. Some special needs can definitely be a pain for a novice with the kit, I know. I'm not saying you need to use DBIC but it's really quite excellent and in the past your code style and mine have lined-up well so I recommend it to you.

    Take a look at these goodies (assuming you haven't already)–

      I've generated a schema from an existing database before, and I agree it's pretty easy. What it came up with was a tree full of files that described every field of every table in the database. I want to avoid that for a couple of reasons.

      1. The database I'm working with is old and huge and has been through a lot of bad design. I'd be surprised if the result "just works."
      2. The program I have is just one file, and I'd like to keep it that way.
      3. The environment I'm in is mostly "not Perl." I'm writing this as a sysadmin outside the support of the developer population, so I can expect future database changes behind my back.

      All of the work of my little program is done in one table. I have one SELECT and one UPDATE (each run many times with different parameters), and that's it. I'm not doing a ton of ugly stuff that DBIC would save me.

      Anyway, it looks like DBIx::Connector will do what I want, and my quick hack works too. If DBIx::Class can do the job without tangling me up in future work, I'd love to learn how.

      As an aside, I'm touched that you remember me after all my absence.