Re: Finding last identity insert for Sybase database within Class::DBI

[prev] [thread] [next] [Date index for 2004/04/29]

On Thu, Apr 29, 2004 at 10:12:58AM +0200, g@briel wrote:
> As far as I can see - it's a great piece of software and I'm
> already in love!

Great!

> Once more, let me reiterate that this is the first time I've used
> Class::DBI and I'm still a bit unfamiliar with it's innards, so,
> forgive my awkward coding within this method.

Without commenting on the Sybase stuff, as I don't really know anything
about it, I'll give a few pointers on how to work in a more idiomatic
Class::DBI style.

We'll take the 'maximum' part first:

  my $dbh  = $self->db_Main;
  my @pri_column = $self->primary_columns;
  my $max_id     = eval {
    $dbh->selectcol_arrayref("SELECT MAX($pri_column[0]) FROM " .  $self->table)
  };
  my $id            = $max_id->[0];

This can be tidied up quite a bit:

Firstly, Class::DBI provides some standard interpolation for you so that
you can, for example, avoid coding your table name, either directly,
or indirectly as you have done:

Thus your SQL could just become:
  "SELECT MAX($pri_column[0]) FROM __TABLE__"

You also shouldn't ever really need to be getting db_Main and executing
SQL on it. Class::DBI provides several layers of abstraction for
that.  In this case as you're getting a single value, you can use the
select_val() method that Ima::DBI gives us. You could either use set_sql
to name that SQL fragment above, and then call select_val on that, or use
the handy sql_single provided by Class::DBI, which basically provides:
"SELECT %s FROM __TABLE__"

So, in your example you could do:

  my $id = $self->sql_single("MAX($pri_column[0])")->select_val;

Now, you don't even really need to do that, as Class::DBI already provides
a maxiumum_value_of(column) method for you!

So your example can reduce even further to:

  my $id = $self->maximum_value_of($pri_column[0]);

We can similarly shorten your @@identity fetcher, although this time
as you're not doing it against your table, we can't use the sql_single
shortcut, so I'll demonstrate setting up some named sql:

  __PACKAGE__->set_sql(get_identity => 'SELECT @@identity');

Then you can execute this with:
  my $id = $self->sql_get_identity->select_val;

We can then combine these so your example reduces to:

  sub _auto_increment_value {
    my $self = shift;
    return $self->sql_get_identity->select_val
      || $self->maximum_value_of($self->primary_column);
  }

Hope this gives you some of a flavour as to how to use some of
Class::DBI's power to simplify your SQL further!

Tony