[Catalyst] Switching between different databases during a session

[James R. Leu]

I've implemented something along the lines of what your doing, but to
suite a slightly different purpose.

Instead of searching different databases based on time I need to take
one query and run it against a dynamic list of databases and return the
results as one resultset.  The list of databases is determined by users
permissions.

I'll give you an overview of how I did it, and you can decide if it will
work for you.  I got the starter info about how to do this from the
people on this list.

I've created 'template' DBIx modules that inherit from DBIx::Class.
These are standard DBIx::Class definitions except the table
name is a sprintf format, and I register a 'source_name'

I've defined 'template' Catalyst Models that inherit from
Catalyst::Model::DBIC::Schema and list the source_name's from
'template' DBIx modules as the schema_class's.  I then include the
connect_info which for me is standard, but this could be 'templated' too.

I've created a module called Catalyst::Model::Multi.  This module
has logic for registering 'child' models, and then mapping standard
resultset operations to each of the registered children and aggregating
the results.  Much of he code is spent trying to do standard resultset
stuff in a way that makes sense with multiple resultset to pull from.

I have a parent model in my application that inherits from
Catalyst::Model::Multi. In the COMPONENT method I build the full list
of 'child' models that might be needed by individual sessions.
The COMPONENT method is called during application start up, so I build
the full list just one, and each request refers to that list. The
child models are build by calling MyApp::Model::FooDB->COMPONENT($c)
where FooDB is the name of one of the 'template' Catalyst Models.  I
then dig through schema and sources of this model replacing table names
(by using the sprintf formats). I believe you could change the connection
info at this time as well.  I then register these instance of the
'template' Catalyst Model as children of my parent, using a unique name.

During authentication process for a session I build he list of unique names
for child models that will be needed by the session.

As each request comes in ACCEPT_CONTEXT is called on the parent model.
For that request I pull the list of unique names from the session
data and register them with the parent model as where to look when
I make queries to that parent mode for this session.

In my application I refer only to the parent model, never the 'template'
Catalyst Models.

My guess is you could use a similar scheme but instead of using my
Catalyst::Model::Multi, you'd write your own that knows how to
query the various model based on time.

On Thu, Nov 15, 2007 at 05:16:58PM -0800, Andrew Peebles wrote:
> I've been searching and experimenting and have not found a solution ...
> 
> I have multiple instances of a database (each in a separate sqlite db 
> file), same schema, different data.  Each represents a snapshot of 
> something at a particular time.  During a session, user wants switch 
> among these databases.  I am trying to add a method to my Model 
> (Catalyst::Model::DBIC::Schema) that takes a new connection string, and 
> switches to that database ... all things otherwise remaining the same.  
> Seems relatively simple, but I just ain't getting it.  Lots of hints on 
> the list, but nothing I can find that's helping.
> 
> I've tried a lot of variants on something like this:
> 
> package SMART::Model::SMARTDB;
> 
> use strict;
> use base 'Catalyst::Model::DBIC::Schema';
> 
> my $dsn = $ENV{MY_DSN} ||= 
> 'dbi:SQLite:/auto/project/tools/metadot/html/cgi/SMART/smart.db';
> 
> __PACKAGE__->config(
>    schema_class => 'SMARTDB',
>    connect_info => [
>        $dsn,
>        '',
>        '',
>        {AutoCommit => 1},
>       
>    ],
> );
> 
> sub switch {
>    my $self = shift;
>    my $dsn  = shift;
> 
>    my $connect_info = [ @{$self->{connect_info}} ];
>    $$connect_info[0] = $dsn;
> 
>    my $schema = $self->clone();
>    $schema->connection(@$connect_info);
>    $self->schema($schema);
> }
> 
> 1;
> 
> 
> _______________________________________________
> List: Catalyst at lists.scsys.co.uk
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/
> Dev site: http://dev.catalyst.perl.org/

-- 
James R. Leu
jleu at mindspring.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
Url : http://lists.scsys.co.uk/pipermail/catalyst/attachments/20071115/f593ce7a/attachment-0001.pgp