Re: [Firebird-devel] Simplify MySQL migration: LAST_INSERT_ID()

[Roman R. <ro...@ro...>]

> I'd much prefer something like LAST_GEN_ID(<name>). Oracle has
> <name>.curval, so it's like something a database-portable application
> would be capable to use.

Issue is that if you port PHP code that uses AUTO_INCREMENT feature, 
there is no name for the generator available in the context. If I knew 
which column was incremented, I would use RETURNING clause. But that's 
not the case.

Joomla has a function to insert an object, which generates an INSERT 
statement, and then if PK column is specified, generated value is filled 
in the object. Then there is another function that takes the INSERT 
statement and executes it, and then another function to get the 
generated values.

Similar approach construct is used in Java O/R mappers, however there is 
an API call that tells the JDBC driver to return the generated values. I 
parse the SQL statement and generate a correct one with RETURNING clause.

So, at the moment we have the only possibility to use the same generator 
for all tables and use gen_id(<name>, 0) as Helen suggests. And I am not 
sure that this will not require changing too much code in Joomla (which 
most likely won't be accepted by Joomla project).

Considering the big picture I was thinking about having table 
declarations like this:

CREATE SEQUENCE someTableSeq;

CREATE TABLE someTable(
   ID INTEGER NOT NULL DEFAULT NEXT VALUE FOR someTableSeq,
   ....
)

I remember that there were issues with backup/restore here (functions 
are restored after tables, so this construct is disabled, AFAIK), so one 
should use triggers at the moment. But I think that DEFAULT clause with 
gen_id function or NEXT VALUE FOR clause should be enabled anyway 
(generators can be restored first, they do not depend on any other objects).

Roman