Perl’s Class::DBI is something I’ve had a bit of a love-hate relationship with. It’s definitely been useful. And, like with Ruby’s ActiveRecord, it can sometimes rocket-propel you 10x with a development task.
However, it’s got a lot of weird gotchas built in. One of them seems to be the inability to instantiate an object with no data. Now, I know, you might well be saying, “why on earth would you instantiate an object with no data?”
Well, for one, perhaps you’ve got a legacy DB (or a non-legacy DB, for that matter) that intelligently fills in columns based upon a trigger, default, or other mechanism. (I’d venture to say that most all modern schemas use some variation of this, be it with AUTO_INCREMENT or sequence.) Another case is where you’ve got link tables that, on their own, don’t get any data added in until and unless their relationships get filled out.
In any case, it would be mighty nice to be able to insert an object that is empty, and have it just filled out with the defaults/triggers/whatever. But when you use Class::DBI, you get this nonsense:
DB<1> use My::Class;
DB<2> $mc = My::Class->create; insert needs a hashref at (eval 813)[/System/Library/Perl/5.8.8/perl5db.pl:628] line 2
DB<3> $mc = My::Class->create({}); DBD::Pg::st execute failed: ERROR: syntax error at or near ")" LINE 1: INSERT INTO my_table ()
^ at
/Library/Perl/5.8.8/DBIx/ContextualFetch.pm
line 52.
This is a Major Bummer, since Class::DBI Just Works for most other things.
Digging through the guts of Class::DBI, it seems like you could use something like the _prepopulate_id private method, but it only works if you’ve set __PACKAGE__->sequence(...). As it happens, I am using sequences under PostgreSQL, but I did not set up the sequence entry for each class (and don’t want to do so, as I’ve been using CDBI for 5+ years without ever bothering with the sequence method).
So, for all you PostgreSQL users with normal, plain-vanilla, single-PK tables that increment off a sequence, here’s some code to add to your base class (it’s always good to have your Class::DBI inheritors inherit CDBI through a base class that you define locally so you can add behavior to all your classes) that will permit a create_blank() constructor:
# this sub allows us to create a blank entry (with only it's
# sequence-increasing ID): XXX NOTE: only works on Postgres
# (pg-specific method of getting ID).
our %CLASS_ID_SEQUENCE_NEXTVAL = ();
sub create_blank {
my $class = shift;
#check cache
my $nextval_sql = $CLASS_ID_SEQUENCE_NEXTVAL{$class};
my $pk_col = $class->primary_column;
#cache miss:
unless (defined $nextval_sql) {
my $table = $class->table;
$table && $pk_col
or die
"create_blank only for single PK tables ($table $pk_col)";
($nextval_sql) =
$class->db_Main()->selectrow_array(
'select column_default from information_schema.columns ' .
'where table_name=? and column_name=?',
{}, ($table, $pk_col)
);
$CLASS_ID_SEQUENCE_NEXTVAL{$class} = $nextval_sql;
}
die "didn't get a viable nextval() for class $class"
unless $nextval_sql;
my ($id) = $class->db_Main()->selectrow_array(
'select ' . $nextval_sql
) or die "couldn't select $nextval_sql";
return $class->create( { $pk_col => $id } ); }
I release the “create_blank” method above to the public domain, feel free to use and abuse.