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

[Peter Edwards]

Hi James,

DBIx::Class::Schema::Loader will scan one database for its tables and
relationships and set up a single schema object for you. As far as I know it
doesn't span more than one DB so you will need to use multiple schema
objects, one for each database. Or you can define the database relationships
manually and not use Loader.

So something like

package MyApp::Config::Schema;
use base qw/DBIx::Class::Schema::Loader/;

sub load
{
  my $class = shift;
  my $dsn = shift; # DBI:mysql:config_alpha or config_beta
  __PACKAGE__->loader_options(
    relationships => 1,
  );
  __PACKAGE__->connection($dsn);
}

package MyApp::Data::Schema;
use base qw/DBIx::Class::Schema::Loader/;

sub load
{
  my $class = shift;
  my $dsn = shift; # DBI:mysql:data
  __PACKAGE__->loader_options(
    relationships => 1,
  );
  __PACKAGE__->connection($dsn); 
}

And then in your initialization

my $schema_cfg = "MyApp::Config::Schema";
$schema_config->load('DBI:mysql:config_alpha'); # read this parameter
alpha/beta from a configuration file or session setting

my $schema_data = "MyApp::Data::Schema";
$schema_config->load('DBI:mysql:config_alpha');

Followed by code like

# get a config database value or if not found a default setting
my $rs_config = $schema_cfg->resultset('SomeConfigTable')->find({ key =>
'some_key_name' });
my $key_value = ($rs_config && $rs_config->conf_value) || 'some_default';

# process a data database resultset
my $rs_person = $schema_data->resultset('Person')->search({ surname =>
'Baggins' });
for ( @$rs_person )
{
  print $_->forename . $_->surname . ': ' . $_->is_ring_bearer, "\n";
}

Regards, 
Peter
www.dragonstaff.com 

-----Original Message-----
From: James R. Leu [mailto:jleu at mindspring.com] 
Sent: 23 January 2007 21:35
To: dbix-class at lists.rawmode.org
Subject: [Dbix-class] Runtime database name and table name changes +
Catalyst

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.

-- 
James R. Leu
jleu at mindspring.com