On Sun, Oct 25, 2009 at 12:47 PM, Roger Binns <rog...@rogerbinns.com> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > >>> can you please re-open the ticket? This bug only occurs in >>> combination with FTS3. Using normal tables in triggers does not cause >>> any problems. > > The underlying cause is that FTS3 has several other tables (use .dump to see > them) that store its underlying data. So when you do an operation against > FTS3 it then does one or more operations against those backing tables. That > is why you are getting the "wrong" id. To fix this requires that the FTS3 > code remember the insert id, do its remaining operations and then set the > last insert id back to the remembered one. However there is no API for > doing that. (It may also be possible to re-order the operations done by FTS3 > so the right id comes out in the end.)
Would be cool if last_insert_id() was not a property of the connection only, but a property of the connection+table. Then, one could do a "SELECT last_insert_id() FROM table" to get the specific id. As a side-effect, FTS3 and other modules could merrily deal with their own respective INSERTs without screwing around with the more "legitimate" last_insert_id()s from the perspective of the user. I guess, as a work around, the user could maintain a separate table which would be updated via TRIGGER AFTER INSERT with the user_id "just" inserted along with the table to which it belongs. > > Anyway I am not on the SQLite team and have only been doing janitorial work > on the old bug tracker. Anyone who cares about this issue needs to convince > the SQLite team that this is an issue (ie the FTS3 specification is that > last insert row id should work) and consequently will need to be fixed (code > rearrangement, adding a new SQLite API). > > Roger > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.9 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iEYEARECAAYFAkrkjxQACgkQmOOfHg372QQ1wACguE26dPFyrFLmpYb36Cp/1mi+ > yzIAnjBKA+aTiJy4Crqg0AxiCnqBl9b5 > =V9Z5 > -----END PGP SIGNATURE----- > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= Sent from Madison, WI, United States _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
