Dynamic DSN w/ Class::DBI Example

[gryphon]

Greetings fellow monks,

I've been trying unsuccessfully to play with Class::DBI using a dynamic DSN passed to my class at runtime. I've been Googling for hours, and I haven't come up with much to show for it. Does anyone have some example code they could share for how to get this done?

This is basically what I'd like to accomplish, but it doesn't work. (Please pardon my lack of POOP knowledge/experience.)

package Stuff;
use base 'Class::DBI::mysql';

sub import {
  my $class = shift;
  unless (__PACKAGE__->can('db_Main')) {
    __PACKAGE__->set_db('Main', @_);
  }
}

package Stuff::Item;
use base 'Stuff';
__PACKAGE__->set_up_table('item');

package main;
use Stuff ('dbi:mysql:dbname', 'gryphon', 'password');
my $item = Stuff::Item->retrieve(1);
print $item->name, "\n";

Why do I even want to do this? Well, I have multiple databases all with the same schema. I'd like to have only one Class::DBI module to avoid replicating code. And I'd like to pass in the database name, username, and password from an INI file since that's where just about all other configuration data is stored.

Now, I've tried the following:

use Class::DBI::AutoLoader (
  dsn       => 'dbi:mysql:dbname',
  username  => 'gryphon',
  password  => 'password',
  tables    => ['item'],
  namespace => 'Stuff',
  use_base  => 'Class::DBI::mysql'
);
my $item = Stuff::Item->retrieve(1);
print $item->name, "\n";

...and of course this works well, but it will only work for very simple models. I won't be able to edit the automatically created classes, so it doesn't work for more complicated cases such as where I'm joining together several tables or want to muck around with output or whatever.

Sorry if this is a stupid question. I've been reading docs all day and haven't been able to figure this out.

[Belgarion]

Have you looked into Class::DBI::BaseDSN? It does allow the DSN definition to be changed at run-time, which should be similar to what you need.

[thpfft]

Every Class::DBI class uses the class method db_Main() to grab its database handle. That sub is normally a closure created in the dark and twisted heart of Ima::DBI, and it embodies various assumptions that are unhelpful when you're working with multiple databases.

The good news is that you can very easily override it: as long as calling My::Class->db_Main() returns an Ima::DBI handle connected to the right database, it doesn't matter how it does it.

You don't need to use any complicated loader mechanisms, in other words. Just something like this (schematic code):

package Stuff::DBI;
use base qw(Class::DBI);

sub db_Main {
    return shift->choose_dbh;
}

sub choose_dbh {
    my $self = shift;
    my $dsn = $self->get_dsn;
    my ($dbuser, $dbpass) = $self->get_db_id;
    return Ima::DBI->connect_cached($dsn, $dbuser, $dbass);
}

The get_dsn() and get_db_id() methods could use %ENV variables, configuration values, command line parameters, session variables or whatever you like, and because you're intervening in the normal handle-retrieval process, there are none of the problems with execution-order that you can get with other approaches.

My (blush) Class::DBI::Factory will do this work for you, but it also does a lot of other stuff that you might not want, and has plenty of assumptions of its own. If I were you I'd use just the basic Class::DBI to begin with, build with it until you're familiar with how it works, and only then start to try out the various helpers and binders you'll find on CPAN.

As perrin says, Class::DBI is really very easy, especially if you begin by conforming to its assumptions and work out from there. The mailing list is active and helpful, and the wiki has begun to get useful. Good luck.

ps. oops. link fixed. thanks.

[biosysadmin]

Good post, I'll actually look into using Class::DBI myself (I'm a longtime user of the "pure" DBI).

Also, your link to the wiki is just a tad broken. :)

[perrin]

It looks to me like the only reason your code didn't work is that you are calling import the first time with no parameters. In general, import() is a lousy place to put anything. Why not just call Stuff->set_db() directly?

[gryphon]

Pardon my stupidity, but do you mean like this:

package Stuff::DBI;
use base 'Class::DBI::BaseDSN';

package Stuff::Item;
use base 'Stuff::DBI';
__PACKAGE__->set_up_table('item');
__PACKAGE__->has_a(categoryId => 'Stuff::Category');

package Stuff::Category;
use base 'Stuff::DBI';
__PACKAGE__->set_up_table('category');
__PACKAGE__->has_a(departmentId => 'Stuff::Department');

package Stuff::Department;
use base 'Stuff::DBI';
__PACKAGE__->set_up_table('department');

package main;
Stuff::DBI->set_db('Main', 'dbi:mysql:dbname', 'gryphon', 'password');

my $item = Stuff::Item->retrieve(1);
print $item->name, "\n";

When I run this, I get a "Can't locate object method 'set_up_table' via package 'Stuff::Item' blah blah" when I run this. What am I doing wrong?

[perrin]

Get rid of that BaseDSN stuff. You have to subclass Class::DBI::MySQL, like you were before, to get the set_up_table method.

[jZed]

I know this isn't exactly what you're asking, but here's an example of using two different data sources on a single schema in the same script. The example uses SQLite and AnyData but any DBI source should work.

#!perl -w
use strict;
sub run {
    for my $source( qw(sqlite anydata)){
        Schema->db_connect( $source );
        Schema->db_drop_table;
        Schema->db_make_table;
        Schema->create({c1=>9,c2=>'Hi From'});
        printf "%s %s!\n", Schema->retrieve(9)->c2
                         , Schema->db_Main->{Driver}->{Name};
    }
}
package My::DBI;
use base 'Class::DBI';
my $source_name;
Ima::DBI->set_db ( 'anydata','DBI:AnyData(AutoCommit=1):','','' );
Ima::DBI->set_db ( 'sqlite' ,'DBI:SQLite:dbname=cdbi'    ,'','' );
sub db_Main      { my $db = 'db_'.$source_name; __PACKAGE__->$db  }
sub db_connect   { $source_name = lc $_[1] }
sub db_drop_table{ eval{ $_[0]->db_Main->do('DROP TABLE '.$_[0]->table
+)} }
sub db_make_table{ $_[0]->db_drop_table; $_[0]->sql_create->execute }

package Schema;
use base 'My::DBI';
__PACKAGE__->table  ( 't1' );
__PACKAGE__->columns( All=>qw(c1 c2) );
__PACKAGE__->columns( Primary  => qw(c1) );
__PACKAGE__->set_sql( 'create' => "CREATE TABLE t1 (c1 INT, c2 CHAR(9)
+)");

package main; run();
__END__