Am 26.10.2009 um 18:49 schrieb P Kishor:
> On Mon, Oct 26, 2009 at 12:40 PM, Wanadoo Hartwig
> <hartwig.wiesm...@wanadoo.nl> wrote:
>>
>> 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).
>
> I concur. Consider
>
> SQLite version 3.6.19
> sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY, desc TEXT);
> sqlite> CREATE TRIGGER foo AFTER INSERT ON a
> ...> BEGIN
> ...> INSERT INTO a (desc) VALUES ('after insert');
> ...> END;
> sqlite> INSERT INTO a (desc) VALUES ('insert');
> sqlite> SELECT last_insert_rowid();
> last_insert_rowid()
> -------------------
> 1
> sqlite> SELECT * FROM a;
> id desc
> ---------- ----------
> 1 insert
> 2 after inse
> sqlite>
>
>
Yes, this is expected behaviour; exactly what I am saying. The last
inserted row id is the one of the insert statement inserting 'insert'.
Anything what you do INSIDE a trigger statement is not influencing the
last-insert-id (unless you use FTS3).
Hartwig
>> 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
>>
>
>
> --
> 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
