Class::DBI example

[autarch]

Class::DBI is a nice module that I've used previously. Its similar to Alzabo in terms of what it does but it is much smaller (and has many fewer features) but its still quite useful.

package Song;

use base qw(Class::DBI);

__PACKAGE__->set_db( 'Main', 'dbi:mysql', 'username', 'password' );

__PACKAGE__->table('Song');
__PACKAGE__->columns( All => qw( song_id title artist album year genre
+ ) );
__PACKAGE__->columns( Primary => 'song_id' );

package main;
find sub {
   return unless m/\.mp3$/;
   
   my $tag = get_mp3tag($_) or return;

   Song->create($tag);

}, @ARGV;

[salvadors]

And, if you're using MySQL then the example can be even smaller:

package Song;

use base qw(Class::DBI::mysql);

__PACKAGE__->set_db( 'Main', 'dbi:mysql', 'username', 'password' );

__PACKAGE__->set_up_table('Song');

package main;
...

No need to declare your columns, as it will go ask the database for them. I keep meaning to find out how to do this for other databases, and roll them in ...

Tony

[lachoy]

Hi Tony,

I created a module to do this for SPOPS. I've tested this on MySQL, PostgreSQL and DB2/AS400 thru ODBC. Basically, all you need to do is issue a dummy query against a table so DBI can grab the metadata. Here's an example:

 my $dbh = DBI->connect( ... )
           || die "Error connecting: $DBI::errstr";
 $dbh->{RaiseError} = 1;
 my $sql = "SELECT * FROM $table where 1 = 0";
 my ( $sth );
 eval {
     $sth = $dbh->prepare( $sql );
     $sth->execute;
 };
 if ( $@ ) { die "Cannot fetch column info: $@" }
 my @column_names = @{ $sth->{NAME} };
 my @column_types = @{ $sth->{TYPE} };

I think most DBDs support this basic metadata. Whenever I've found one that didn't support both of these (like DBD::ASAny at one time), I've harrangued the author and always gotten a quick response :-)

Chris
M-x auto-bs-mode