[Dbix-class] Multiple DBs, one Schema to rule them all

[Wes Cravens]

On 2/11/2011 7:20 PM, Trevor Leffler wrote:
> Hello,
>
> I am investigating whether DBIC has support for switching between
> logical databases (ala 'use db1; ...; use db2; ...;'). I've a situation
> where there exist many cloned databases (same DDL), one per customer.
> The current model is that all customers share the same front-end, and it
> switches between DBs as needed. I would like to avoid the following:
> multiple open connections per app process, multiple MyDB::Schema
> objects, server hups upon customer adds/drops, etc.

My user authentication holds information on a particular login's 
'default db'.  I then use InstancePerContext.  Not sure if this is a 
great solution so I'm posting for criticism as much as anything.

<code>
package MyApp::Model::DB;
use strict;
use base 'Catalyst::Model::DBIC::Schema';

use Moose;
with 'Catalyst::Component::InstancePerContext';


__PACKAGE__->config(
     schema_class => 'MyApp::DBICSchema::DB',
     connect_info => []
);

sub build_per_context_instance {
     my ($self, $c) = @_;
     my $new = bless({ %$self }, ref($self));
     $new->schema( $self->schema->connect( {
         dsn      => 'dbi:Pg:dbname=' . $c->user->default_db->dbname .
                     ';host=' . $c->user->default_db->dbhost,
         user     => $c->user->default_db->dbusername,
         password => $c->user->default_db->dbpassword } ) );
}
</code>

> The very reasonable suggestion to combine all of the client databases
> into one is being considered, so please let's not get side-tracked by
> that line of thought, thanks. :)
>
> So... how might I have my Schema issue a 'use $db' prior to any queries?
> Or perhaps are there other solutions to how one could have multiple DB
> clones while avoiding the code and connection scaling issues?
>
> Thanks,
> --Trevor
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk