[Dbix-class] Caching connections

[Brandon Black]

On 4/30/06, Matt S Trout <dbix-class at trout.me.uk> wrote:
> Dan Horne wrote:
>  > 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.
>
> Mmmm. I think I'd create a $schema per user on-demand and persist it.
> connect_cached is imperfect IMO. Alternatively, you could pass ->connect a
> subref returning a $dbh instead of a connect string, which would let you do
> pretty much anything you wanted to get the dbh.
>
> I'm afraid this isn't a use case I've given much thought to; I tend to handle
> security at the application level and have a single db connection for the app
> (or in odd cases two, one for reading and a higher-privilege one for writing).
>

If I'm understanding the scenario correctly, another option would be
two persist two $schema objects based on the same Schema class, one
for each connection.  As in:

sub fastcgi_proc_startup_handler {
  $persistent_stuff->{ro_schema} = My::Schema->connect('dbi:Pg:dbname=ro_db');
  $persistent_stuff->{rw_schema} = My::Schema->connect('dbi:Pg:dbname=rw_db');
}

And then based on your per-user logic at each request, do:

sub handle_request {
  my $schema = $priveleged_user ? $persistent_stuff->{rw_schema} :
$persistent_stuff->{ro_schema};

  my $foo = $schema->resultset(...)->....
  ...
}

The schema objects themselves will hold open cached connections
correctly and all that, so you'll end up with up to one persistent
connection per fastcgi process to each database (they won't actually
connect until utilized, unless you're using Loader, which connects at
load time).

-- Brandon