On Tue, 7 May 2013 18:54:48 +0200
Hick Gunter <h...@scigames.at> wrote:
> 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.
Ah. When you said "leak", I thought you meant memory leak.
I have to tell you I don't have much sympathy for the "problem" you
propose to solve. I don't understand the purpose of an application
that
1. issues a query not knowing whether it's an INSERT, and
2. needs to know the row id of the inserted row
I can tell you for sure that
> "INSERT INTO b (a) VALUES (c)" => the ID of the inserted row
won't work, because more than one row might have been inserted.
More important, though: the problem you're trying to solve is created
by the way you're going about it. Let's look at the SQL.
> CREATE TABLE t1 ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, d
> TEXT );
That table has no natural key. It has an id column declared as a key,
but the user-provided data -- column d -- is indistinguishably
duplicated. If the user provides "a" three times, and the DBMS generates
meaningless numbers to distinguish them. At most, the user knows he has
three a's, and that the computer -- for reasons of its own -- has
assigned them numbers.
The table also presents the application programmer with problems beyond
those you're seeking to address, e.g.
1. How to delete one of the 'a' rows? Just pick the first one?
2. How to change one of the 'a' rows to 'b'? Pick one?
3. How to limit the number of 'a' rows? A trigger?
Now consider a better design:
CREATE TABLE t1 (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
q INTEGER,
d TEXT,
unique(d)
);
where column q is the count of d, and id remains an auto-generated
number (for your sake, perhaps for efficiency).
To retrieve the id (please observe the use of single quotes):
insert into t1 (q,d) values (1,'a'), (1,'b');
select id, d from t1 where d in ('a', 'b');
You will note that the three questions I posed are also easily
answered.
That is how the problem you propose to solve is conventionally
solved. Of course, it presupposes a natural key and that the
application retains the values it inserted for which it wants the
DBMS-generated ids. In other words, it uses the system according to
its strengths, as it was designed to be used.
In short, the whole problem of discovering the generated row id
disappears with judicious (one might say "correct") use of the DBMS. It
definitely does not warrant an API change.
Orthogonal to the row-id issue is the "what kind of query is it?"
question. It's easy to imagine uses for information from the parser.
A general solution would be an API function to return the tokenized
query; the caller could search the tree for the operators/keywords of
interest.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
