How about calling sqlite3_update_hook()? You will be notified about all UPDATE, INSERT and DELETE operations as they occur, with the database, table and rowid affected and in the thread executing the statement.
If you register a well-behaved function (i.e. that calls the previously registered function) these hooks may even be layered... Von: Mikael [mailto:mikael.tr...@gmail.com] Gesendet: Dienstag, 07. Mai 2013 14:15 An: Richard Hipp; Simon Slavin; Hick Gunter; James K. Lowden; General Discussion of SQLite Database Betreff: Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or functionalequivalent? Hi, Thanks for your feedback, James. A generalized way of getting if an ID was inserted on the last performed query and if so which, is of value for instance when inserting a row into a table with an AUTOINCREMENT key e.g. CREATE TABLE t ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, d TEXT ); and then inserting into it, e.g. "INSERT INTO t (d) VALUES (\"a\");" => inserted id: 1 "INSERT INTO t (d) VALUES (\"a\");" => inserted id: 2 "INSERT INTO t (d) VALUES (\"b\");" => inserted id: 3 "INSERT INTO t (d) VALUES (\"b\");" => inserted id: 4 "INSERT INTO t (d) VALUES (\"c\");" => inserted id: 5 This functionality makes a lot of sense in at least certain SQLite bindings. In the places where it's not strictly required, it will still provide clarity which is valuable in debug, and convenience from being dynamic, please see the example use ~15 rows below for this - So what I'm looking for is a general mechanism for deriving the ID inserted for a query. E.g.: "SELECT a FROM b" => none "UPDATE b SET a = c" => none "INSERT IGNORE INTO b (a) VALUES (c)" and the ID was already in there => none "INSERT IGNORE INTO b (a) VALUES (c)" and the ID was not already in there => the ID of the inserted row "INSERT INTO b (a) VALUES (c)" => the ID of the inserted row Thanks to this, a SQLite binding can be achieved which dynamically/*automatically* detects not only if it was a readonly or non-readonly query, but also within the non-readonly query if it was an update or insert, and thus a generalized query mechanism can be devised that behaves like: SELECT query => The query results UPDATE query => The number of rows updated INSERT query => The ID of the row inserted, or none (by null/false/0/etc.) This is to create a general abstraction for this purpose. Indeed SQLite performs queries, well, at the level of query and, the meaningful work you do with SQLite is at the granularity of a query, so it appears reasonable to me that there would be a mechanism using which you could retrieve the ID of the row inserted by a particular query. A generalized "rows changed by this query" abstraction can be implemented as of today, using sqlite3_changes which returns the number of changed rows in the *last performed query*, the error reporting (as to check sqlite3_changes was set on the last query) and possibly secondarily by stmt_readonly . The problem with checking the ID of the last inserted row is that the abstraction for checking it cannot reasonably know that a query is an insert; Essentially doing a string match for "INSERT " in the query executed would be a leaky abstraction. Getting a copy of SQLite's SQL parser's data could be something, but then - this is a bit beyond my current SQLite skills - aren't there mechanisms to make INSERT:s indirectly in SQLite, like through a database-stored procedure or alike? That would make any abstraction like this leak, because it wouldn't know if an INSERT was made during a query, and that knowledge is required as to know if sqlite3_last_insert_rowid regards the last inserted ID or not. And to illustrate how sqlite3_changes and sqlite3_last_insert_rowid does not deliver for automatic query ID retrieval: Using these two, let's define an attempt at a general mechanism for this, in pseudocode: var last_rowId_on_last_query = 0; function query(query) { lock_mutex(); sqlite_perform_query(query); var last_rowId = sqlite_last_rowid(); var id_of_this_row = (last_rowId != last_rowId_on_last_query) ? last_rowId : null; last_rowId_on_last_query = last_rowId; unlock_mutex(); return id_of_this_row; } Now, let's set up these two tables CREATE TABLE t1 ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, d TEXT ); CREATE TABLE t2 ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, d TEXT ); and now make some inserts with it! The use of query() above is implied here. "INSERT INTO t1 (d) VALUES (\"a\");" => 1 - Correct! "INSERT INTO t1 (d) VALUES (\"a\");" => 2 - Correct! "INSERT INTO t2 (d) VALUES (\"a\");" => 1 - Correct! "INSERT INTO t2 (d) VALUES (\"a\");" => 2 - Correct! "INSERT INTO t2 (d) VALUES (\"a\");" => 3 - Correct! "INSERT INTO t1 (d) VALUES (\"a\");" => null - Error, abstraction leak, ouch! All of this leakiness would be undone if before a query, the SQLite user could reset the sqlite3_last_insert_rowid . By that, the user can just check the sqlite3_last_insert_rowid right after the query, and by that know that that was . It may be a bit too simplistic if a query inserted several ID:s as you pointed out, though at least it's general unlike the present solution, and can be made to fit any usecase at least I have in reach, as in SQLite any INSERT operation can be performed one row per query with OK-to-great performance. Introduction of a sqlite3_reset_last_insert_rowid would be complimenting the current sqlite_int64 sqlite3_last_insert_rowid at sqlite3.c row 114023-114028: /* ** Return the ROWID of the most recent insert */ SQLITE_API sqlite_int64 sqlite3_last_insert_rowid(sqlite3 *db){ return db->lastRowid; } with the similar SQLITE_API void sqlite3_reset_last_insert_rowid(sqlite3 *db){ db->lastRowid = 0; } and then making it export properly. The reason I address you on this list directly with this is because it appears to be of use for SQLite bindings as far as I can see. I agree that keeping the API minimal is a top priority. Though, for instance exporting this as a SQL procedure that could be invoked by preparing "CALL reset_last_insert_rowid();" and then invoking it, wouldn't that be a bit too costly to do on *every* query however small in a particular binding? If you see any other way to solve this with good performance, please feel free to share, I'm just looking for a general way of getting if the last query inserted an ID and if so which, it can be implemented any way as long as it performs so good it can be included in a general query mechanism in a binding. Thanks, Mikael 2013/5/7 James K. Lowden <jklow...@schemamania.org<mailto:jklow...@schemamania.org>> On Mon, 6 May 2013 20:55:01 +0300 Mikael <mikael.tr...@gmail.com<mailto:mikael.tr...@gmail.com>> wrote: > Currently in the absence of a sqlite3_reset_last_insert_rowid(), the > closest you can do to figure out what ID was actually inserted by the > most recent SQLite query is: > > * Lock mutex > * Get sqlite*'s last insert row ID > * Perform the query > * Get number of changed rows > * Get sqlite*'s last insert row ID > * Unlock mutex I don't understand. The process doing the insert presumably knows which data it inserted. Why not turn around and SELECT the ids for the inserted values? No mutex and no guesswork. Your process seems to imply that the the inserted values don't constitute a key, or that you want to compute the row IDs for performance. Those don't strike me as sufficient reason to perturb the API. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ________________________________ Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 - 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
