[Gambas-user] Surrogate keys and database insertions

[Bruce Bruen]

On Mon, 2011-09-26 at 14:46 +0200, Benoît Minisini wrote:

> > On Mon, 2011-09-26 at 14:03 +0200, Benoît Minisini wrote:
> > > This feature is missing, mainly because there is no standard way to do
> > > that in SQL, and I don't know if there is a function for that in all SQL
> > > DBMS.
> > 
> > I was afraid of that. I'll do some research.  I know it is possible in
> > postgresql and I think it can be made generic across all inserts.  As much
> > as I hate delving in to the twisted entrails of mysql documentation I will
> > endeavour...  I don't have much expertise with firebird but there is a
> > learning opportunity, I suppose. With sqlite, a quick look seems there is
> > some probability.  I doubt that it is possible with ODBC.
> > 
> > I'll get back to you.
> > Bruce
> > 
> 
> Firebird being not GPL, it has been removed from Gambas. So don't worry with 
> it. But I'd like to have the information for the others!
> 

Research results so far:
1) I expanded the frame of my original quest to see whether it is
possible to implement a "with re-read" option for both INSERT and UPDATE
queries.  This would allow an automatic synchronisation of the gambas
result with the true database row when the row has been updated with
serial, default or computed values at the database level.  The theory
being that it would work regardless of whether  the primary key columns
were affected or not.  
Given the beaut way gambas works with updateable results, I think that
the optimal approach would be to have an optional boolean parameter in
Result.Update (i.e. Result.Update([WithReread=FALSE]) that would provide
the feature but not break existing code.  Alas, my C skills are not up
to a point where I can try this out on my local trunk copy!  Any
guidance here would be appreciated.
2) Assuming the above, the internal workings of Result.Update would vary
according to the dbms.  Sometimes, returning the updated result is
available directly from the dbms, other times it would have to be
simulated via a subsequent SELECT.  Further notes below.
2.1) The problem breaks down into two cases a) where the primary key
columns are all provided (by the calling program) in the Result, and b)
where some or all of the primary key fields are serial,default or
computed. In the first case the solution is trivial, the subsequent
SELECT has all it needs to query the updated row and thus refresh the
Result object.  The second case is more complex.  However I believe it
can be accomplished depending on the dbms.
2.2) For postgresql (since 8.x) the solution apears trivial as
postgresql provides INSERT ... RETURNING and UPDATE ... RETURNING i.e.
it can return some or all of the columns after the insert or update has
completed.  Further, this appears to be well protected from any
concurrency or other side effects.
2.3) For MySQL (since ?.?) there is possibly a solution as there is a
protected SELECT LAST_INSERT_ID(); that will return the last
"autoincrement" for the most recently executed INSERT on a table
containing an autoincrement column.  By protected, I mean that it works
on a per connection basis, thus avoiding concurrency side effects.  Now
since MySQL will only allow one autoincrement column per table, if the
column is in the primary key then the above approach will work using a
subsequent select on the resultant fully populated key.  HOWEVER, there
is still an unresolved issue if the db supplied key columns are not an
autoincrement, say the key contains a db generated timestamp column.  I
am still looking into this.
2.4) For Sqlite the situation is similar to MySQL but simpler in some
ways. It provides "SELECT last_insert_rowid()" to retrieve serial pkeys.
I'm still looking at this as a) I'm not sure yet how far Sqlite goes in
terms of the other computed column value types and b) it appears from
some web pages that the version of this function in Sqlite2 has some
bugs that may never have been fixed.
2.5) For ODBC, I have doubts.  I'd have fewer doubts if I could actually
get an ODBC driver to work at all on this machine.
2.6) Interestingly, Firebird (since 2.0) does support INSERT ...
RETURNING and UPDATE ... RETURNING.

Research contnues, but I'd appreciate any thoughts.

Bruce