[CDBI] Re: Sequences in Class::DBI?

Warren Toomey asked:
> How can I do the same in Class::DBI?

For the most part, you shouldn't need to, I would think. The latest version
of Class::DBI utilizes DBI's last_insert_id() wherever available (including
PostgreSQL, I believe). Assuming your primary key is the sequence value,
then, when you create a new object, the primary key will be filled in for you
automatically. You just need to specify the sequence like so:

__PACKAGE__->sequence('daynum');

Make sure you RTFM the sections on sequences and the insert() method:

http://search.cpan.org/~tmtm/Class-DBI/lib/Class/DBI.pm#sequence_%2F_auto_increment

> I assume that I will need a separate class for each sequence, but given a
> sequence called 'daynum', how do I write the class?

No, you should have a separate class for each *table* you are interfacing
with. Presumably, there will be a natural sequence that you use with each
table, and it's in the class for that table that you should specify the
appropriate sequence.

Class::DBI is just a layer on top of DBI, so you can still "select
nextval(foo)" or "select currval(foo)" if you really need to, either using
standard DBI calls or using the Class::DBI way:

__PACKAGE__->sequence('daynum');
__PACKAGE__->set_sql(Currval => "select currval(%s)");
# Note: Class::DBI v3.0.14 already has a "Nextval" SQL statement defined.

sub Get_NextSequence { # This is the same as Class::DBI's semi-private
                       # _next_in_sequence() method...
  my $self = shift;
  return $self->sql_Nextval->select_val($self->sequence);
}

sub Get_CurrSequence {
  my $self = shift;
  return $self->sql_Currval->select_val($self->sequence);
}


Hope this helps,
Ed