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>
> On Mon, 6 May 2013 20:55:01 +0300
> Mikael <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
> 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
