[Dbix-class] Caching connections

[Dan Horne]

> From: Matt S Trout [mailto:dbix-class at trout.me.uk]
> Dan Horne wrote:
> > According to the docs
> >
> > "Note that DBIx::Class::Schema does not cache connections for you. If
> you
> > use multiple connections, you need to do this manually."
> >
> > As I'm using DBIx::Class under FastCGI, I'd like to cache my connection
> with
> > DBI's connect_cached connection method. Is there a way that I can do
> this?
> 
> "Don't do that"
> 
> More seriously, just keep the $schema object around somewhere - much
> simpler
> approach and works fine. That's basically what
> Catalyst::Model::DBIC::Schema
> does, if you want to use that as a reference (I tend to deploy most of my
> Catalyst stuff under FastCGI)
> 

The thing is, I need multiple schemas for transactional reasons - I don't
want one person to inadvertently commit or rollback another person's
transactions.  The way my (non-DBIC) CGI::Application-based app works
currently:

Front end users are non transactional - they just view a site.
Backend (i.e. admin) users require transactions. They each get their own
connection, based on their username:

$self->{_dbh} = DBI->connect_cached(
            $self->conf->param('dsn'), $self->conf->param('user'),
            $self->conf->param('password'), {username => $username,}
        );

I'd like to do something similar if I switch to DBIC, but the only way I can
see it working is to create a schema for each user, passing the username as
a DBI attribute (as above) to the Schema's connect method. But I want the
connection to persist. 

As far as I can tell from the DBIC code, allowances are made for Apache::DBI
under mod_perl, but I'd like similar consideration for FastCGI and
PersistentPerl and I think my problem is solved via DBI's connect_cached.
Matt, perhaps my logic is all wrong, and I'm missing something fundamental -
I'm still trying to assimilate all of the DBIC documentation. 

Dan