[Dbix-class] Runtime database name and table name changes + Catalyst

[Matt S Trout]

On 23 Jan 2007, at 21:34, James R. Leu wrote:

> Disclaimer:
> I'm a noob to DBIx::Class. I checked the archives, but was unable to
> find an answer to the following question.  If I've overlooked an  
> obvious
> source to my answers, please scold me and point me the the relevant  
> URL.
>
> Hello,
>
> I'm trying to use DBIx::Class to access a database for which the name
> changes based on who is authenticated.  In addition the authorization
> of the use affects what dynamically name table they 'link' to in  
> another
> database.
>
> I read about DBIx::Class::Schema::Loader, but was unable to grok
> how I would define relationships to a dynamically named table.
>
> To better illustrate here is a simplified example:
>
>     The following database reside within a common mysql instance.
>
>     Database: config_alpha
> 	    Table: common
>
>     Database: config_beta
> 	    Table: common
>
>     Database: data
> 	    Table: foo_01
> 	    Table: foo_88
> 	    Table: foo_34
> 	    Table: foo_39
>
> Depending which user logged in 'alpha' or 'beta' they would
> connect to the appropriate 'config' database.  In addition they are
> connected the the 'data' database.  Depending on additional
> authorization data they would have permission to see data in one of  
> the
> 'foo' tables.  I would like to define a has_many relationship between
> entries in the 'common' and the 'foo' table.
>
> I think I can image ways to make some of this work in a stand-alone
> script, but I haven't the foggiest idea how to make this work within
> a Catalyst environment.
>
> Any pointers would be much appreciated.

Ok, basically, define a has_many across to 'data.PLACEHOLDER', then  
wrap ->connect so on the schema object -to be returned-, which will  
have a copy of the ResultSource objects, then if your Foo_* tables  
are different you'll want -

$schema->source('Common')->relationship_info 
('data_has_many_rel_name')->{source} = 'Foo_1';

or similar, or if your foo_* tables are all the same define a single  
Foo class and

$schema->source('Foo')->name('data.foo_1');

then return the $schema.

For doing this dynamically in Catalyst on a per-user basis I'd  
provide an ACCEPT_CONTEXT method in your DBIC::Schema model -

sub ACCEPT_CONTEXT {
   my ($self, $c) = @_;
   if ($c->user_exists) {
     return $self->copy_and_modify_schema_with_user($c->user);
   } else {
     return $self;
   }
}

-- 
Matt S Trout, Technical Director, Shadowcat Systems Ltd.
Offering custom development, consultancy and support contracts for  
Catalyst,
DBIx::Class and BAST. Contact mst (at) shadowcatsystems.co.uk for  
details.
+ Help us build a better perl ORM: http://dbix- 
class.shadowcatsystems.co.uk/ +