Moose::Role to provide DBI interface

[nysus]

I'm fairly new to Moose and use DBI occasionally. I'd like to get some input on a Moose::Role I've written to make it easier for objects to change the database handler. I'm probably reinventing the wheel but at least I'm learning something. Here's a chunk of the code:

package DB;
use DBI;
use Carp;
use Moose::Role;
use Modern::Perl;

has 'db'      => ( is => 'rw', isa => 'Str',     lazy => 1, default =>
+ '', trigger => \&_switch_db );  
has 'dbh'     => ( is => 'rw', isa => 'DBI::db', lazy => 1, builder =>
+ '_db_connect' );
has 'dsn'     => ( is => 'rw', isa => 'Str',     lazy => 1, default =>
+ 'DBI:mysql:host=127.0.0.1');
has 'db_user' => ( is => 'rw', isa => 'Str',     lazy => 1, default =>
+ 'root' );
has 'db_pass' => ( is => 'rw', isa => 'Str',     lazy => 1, default =>
+ 'mypass' );
has 'db_host' => ( is => 'rw', isa => 'Str',     lazy => 1, default =>
+ '127.0.0.1' );
has 'db_type' => ( is => 'rw', isa => 'Str',     lazy => 1, default =>
+ 'mysql' );

sub _db_connect {
  my $self = shift;
  my $db   = shift;

  $self->_set_dsn($db);
  return DBI->connect($self->dsn, $self->db_user, $self->db_pass) || l
+ogf("Can't connect to database $db");
}

sub _set_dsn {
  my $self = shift;
  my $db = shift;

  logd('setting database');

  my $dsn = 'DBI:' . $self->db_type . ':host:' . $self->db_host;
  $dsn .= ";database=$db" if $db;
  $self->dsn($dsn);
}

sub _switch_db {
  my $self = shift;
  logd('switching db');
  $self->dbh($self->_db_connect($self->db));
}

Before I use this role too much, I'd like to get some input from more seasoned Monks as to whether I'm doing anything foolish (I've learned I usually do). Of particular concern is the creation of a new database handler when simply switching the database using the 'db' attributes 'trigger' method.

Thanks!

[1nickt]

Hi nysus,

It's hard for me to imagine when you'd want to change the DB connection attribute of an object instance. If you really need more than one connection, it might be better to hide them in the consuming class behind object methods that simply return the data you want.

On the other hand it *is* common to need to connect to more than one DB in a given application. In that case (or in the case of a single object needing more than one connection) I would suggest using an external configuration file with the configs for your various DSNs, and then create your single class that will return a DB connection based on the parameters passed. I wouldn't try to switch connections as you've outlined, seems too error-susceptible and for no real benefit. Other monks may be able to provide the nitty-gritty as to why it's a bad idea; all I can say is that it smells dodgy.

Hope this helps!

[nysus]

My objectives here are 1) less typing to set up a $dbh and 2) get practice with Moose. Just about 100% of the time my scripts are for my own purposes. So I have this role set up with defaults that I will probably use 99% of the time. I can always change them later if I need to. So now I can write one liner like this:

use LaborData::Data::SchemaLoader;
my $loader = LaborData::Data::SchemaLoader->new;
$loader->make_schema('lm2_data');

LaborData::Data::SchemaLoader contains DBIx make_schema method and my DB package which automatically loads a database handler. And so now I never have to look up the code again for loading a schema for DBIx when I want to do it on a different database.

But you are right. It's doubtful I would ever need to have my object switch the db connection.

This didn't smell good to me either. That's why, I suppose, I asked this question. I like your suggestion of a configuration file. That does seem like the proper, more extensible way of doing things. I'm sure there's a module for that. But I got enough on my plate already. :)

Thanks for the feedback.

[Anonymous Monk]

"And so now I never have to look up the code again for loading a schema for DBIx when I want to do it on a different database."

This makes zero sense. Why would you "load" a schema? What does that even mean?

[Anonymous Monk]

Perhaps you should learn about things like many to many relationships before you go building tools that are misguided attempts to simplify that which cannot be simplified.

How would you design a schema for storing book titles and their authors, for example?

[nysus]

Ah, thanks, AC. But you know, like an ancient computer, I can only work on one thing at a time. This is more of a simple, academic exercise with Moose. Now, if your advice were to have been learn how to program AI first and have this all done for me automatically, that might have been worth something.

[Anonymous Monk]

"This is more of a simple, academic exercise with Moose."

Then I suggest you leave the Database component out of this until you learn SQL.