Am 26.10.2009 um 15:27 schrieb P Kishor:
> 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.
>
>
I really think that this is a bug. The last-inserted-id is under
normal circumstances always the one that caused the trigger to fire
(in case of an insert trigger). Furthermore, I checked the
documentation concerning virtual tables and there is no exception
mentioned that virtual tables should behave differently than normal
tables with respect to triggers (besides the statement that virtual
tables cannot fire triggers).
The only usable workaround I currently have is to simulate the trigger
by doing the corresponding calls explicitely.
Hartwig
>>
>> -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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
