Extend API for last_insert_id

[pa...@cpan.org]

Hello,

I would like to propose two new changes to DBI which extends
last_insert_id API.

First one: Allow to call $dbh->last_insert_id() method without
arguments. Currently this method needs to take four arguments (plus
$dbh) and XS code validates their count:

XS_EUPXS(XS_DBD__Perl__db_last_insert_id)
{
dVAR; dXSARGS;
if (items < 5 || items > 6)
croak_xs_usage(cv, "dbh, catalog, schema, table, field, attr=Nullsv");
...
}

More databases does not process these arguments and caller needs to
supply four undefs.

So I'm proposing change that caller would be allowed to call
$dbh->last_insert_id() without any argument and DBI would fill those
missing arguments by undefs prior to calling DBI driver function. This
does not change driver API, so all existing DBI drivers would work as
before and allows application to not specify those useless four undef
arguments.

Second change: Add a new statement method $sth->last_insert_id().
Some databases (e.g. MariaDB) supports tracking last insert id when more
statement or cursor handles are open. This would allow to call e.g.

$sth1->execute();
$sth2->execute();
...
$sth1->last_insert_id();
$sth2->last_insert_id();

for drivers which would support it. $sth1->last_insert_id() would return
insert it which belongs to last ->execute of $sth1 even there were more
INSERT/execute calls (e.g. by $sth2).

Currently database handle method $dbh->last_insert_id() returns id of
the most recent INSERT statement.

What do you think about those two enhancements?

Here is my prototype implementation for DBI:
https://github.com/perl5-dbi/dbi/compare/master...pali:last-insert-id

DBI drivers can then implement own dbd_st_last_insert_id() function and
provide correct id for the selected $sth statement.

[pa...@cpan.org]

Hello, do you have any opinion or comments?

Tim, you as a DBI maintainer, what do you think about those ideas?

[Tim Bunce]

On Fri, May 04, 2018 at 02:10:18PM +0200, pa...@cpan.org wrote:
> Hello, do you have any opinion or comments?
>
> Tim, you as a DBI maintainer, what do you think about those ideas?
>
> On Friday 27 April 2018 16:03:59 pa...@cpan.org wrote:
> >
> > So I'm proposing change that caller would be allowed to call
> > $dbh->last_insert_id() without any argument

Ok.

> > Second change: Add a new statement method $sth->last_insert_id().

That seems fine. I'd write the fallback code like this:

sub last_insert_id { return shift->{Database}->last_insert_id(@_) }

Thanks Pali.

Tim.

[pa...@cpan.org]

Changed.

And now I created a pull request with these changes:
https://github.com/perl5-dbi/dbi/pull/64

> Thanks Pali.
>
> Tim.