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>
> 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
