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

[Matt S Trout]

On 24 Jan 2007, at 20:58, James R. Leu wrote:

> Hello Matt,
>
> Thank you for your response.  Your recommendation has started me  
> down the
> right path.  I have a working implementation, but I'm not sure if I'm
> following proper DBIx::Class practices.
>
> I've implemented a template source 'Foo' which is used for
> defining the relationships.  In addition I implemented a  
> ACCEPT_CONTEXT
> in my Catalyst::Model::DBIC::Schema.
>
> Here is a simplified version of what I've done, I've framed it in
> Catalyst code, but I my question at this point is specific to my
> DBIx::Class handling:
>
> package MyApp::Model::Config
> use base 'Catalyst::Model::DBIC::Schema';
>
> ...
>
> sub ACCEPT_CONTEXT {
>     my ($self, $c) = @_;
>     if ($c->user_exists) {
> 	my $name = $c->user->username;
> 	$self->{connect_info} = [
> 	    'DBI:mysql:config_'.$name,
>             'username',
>             'password',
> 	];
> 	my $schema = $self->clone();
> 	$schema->source('Foo')->name('data.foo_01');
> 	$schema->connection(@{$self->{connect_info}});
> 	$self->schema($schema);
>     }
>     return $self;
> }
> 1;
>
> Am I conducting an layer violations by calling connection() and  
> source()?
> Do I need special handling for when the connection has already been  
> created?

Hmm ... I wonder if the better way, actually, isn't to set the  
default database to 'data' and then go across the other sources and  
set their name to 'config_'.$name.'.tblname', that way you can  
maintain a single persistent connection but still have a given  
request access the right place.

>
> I have other questions about Catalyst, but I will ask those on the
> Catalyst mailing list.
>
> Thank you for your time.
>
> On Wed, Jan 24, 2007 at 09:15:21AM +0000, Matt S Trout wrote:
>>
>> 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/ +
>>
>
> -- 
> James R. Leu
> jleu at mindspring.com

-- 
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/ +