On Mon, Oct 26, 2009 at 9:17 AM, Scott Hess <sh...@google.com> wrote:
> The bug was closed with "cannot reproduce". So IMHO either the bug
> should be re-opened with the provided repro case, or a new bug should
> be opened with the provided repro case.
As listed in my earlier email, it can be reproduced very simply as follows --
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE Simple (ID integer primary key, Name text);
sqlite> CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);
sqlite> CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH ROW BEGIN
...> DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END;
sqlite> CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH ROW BEGIN
...> INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name); END;
sqlite> INSERT INTO Simple (Name) VALUES('one');
sqlite> INSERT INTO Simple (Name) VALUES('two');
sqlite> SELECT * FROM simple;
ID Name
---------- ----------
1 one
2 two
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
-------------------
2
sqlite> DELETE FROM Simple WHERE (ID = 1);
sqlite> INSERT INTO Simple (Name) VALUES('three');
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
-------------------
4
sqlite>
> Then, if the team decides
> that this is works-as-designed, the team can close the bug with that
> resolution.
>
> When I brought this up on the list a year and a half ago, we didn't
> really come to a conclusion on how it should work. I think there were
> arguments for both sides of the equation. My personal preference
> would be for it to work like one would expect it to (the "interior"
> last-insert-id isn't exposed to "exterior" code, because it is none of
> that code's business). But it needs to happen in a way which doesn't
> adversely impact performance.
Performance is important, but getting back what one expects
(integrity) is perhaps more important. If my subsequent actions are
dependent on the correct last_insert_rowid(), then the way it works
right now doesn't work.
>
> -scott
>
>
> On Sun, Oct 25, 2009 at 6:32 PM, Roger Binns <rog...@rogerbinns.com> wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Wanadoo Hartwig wrote:
>>>> Actually, if you replace the FTS3 table with any other type of table -
>>>> or as many tables as you like and do also in these tables insert
>>>> operations - the last row id of the original table is correctly
>>>> returned. Therefore, I assume (I do not have any details) that there
>>>> is a bug in the trigger mechanism itself.
>>
>> The point that keeps being missed is that FTS3 is not a regular table. It
>> is a virtual table. That means that the SQLite core calls it corresponding
>> to various SQL operations but behind the scenes it is up the virtual table
>> implementation what to do. Read about them at:
>>
>> http://www.sqlite.org/cvstrac/wiki?p=VirtualTables
>>
>> A virtual table implementation is free to do whatever it wants. When asked
>> to insert one row, it could store 27 after converting all text to Klingon.
>> It could instead delete whatever you asked it to add. It could do nothing.
>> The FTS3 implementation happens to have 3 other tables and so on an insert
>> those are updated with extra information and you are seeing those after
>> effects.
>>
>> Roger
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.4.9 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>>
>> iEYEARECAAYFAkrk/A4ACgkQmOOfHg372QTEWACggOjPYsHFzB00jNMcDkOmYQ5q
>> KCgAniNN8LAdKea5ZhHYO5SgrsG7qhdw
>> =R9aD
>> -----END PGP SIGNATURE-----
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> 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
