I have this database schema:
CREATE TABLE observations (
src TEXT,
dest TEXT,
verb TEXT,
occurrences INTEGER
);
CREATE UNIQUE INDEX observations_index
ON observations (src, dest, verb);
My program receives a stream of events, which are (src, dest, verb)
tuples. On every event, I want to look up the row keyed by that
tuple, and then either increment the occurrences cell if a row is
found, or insert a new row (with occurrences equal to 1) if there was
no match. In pseudocode,
if (SELECT COUNT(*) FROM observations
WHERE src == :src AND dest == :dest AND verb == :verb) == 1:
UPDATE observations SET occurrences = occurrences + 1
WHERE src == :src AND dest == :dest AND verb == :verb
else:
INSERT INTO observations VALUES (:src, :dest, :verb, 1)
This does two index lookups on every event, though, and will be messy
to code as I have to do all database access asynchronously. Is it
possible to do this operation in one SQLite statement? I asked on
Stack Overflow and this was suggested ...
BEGIN;
INSERT OR IGNORE INTO observations VALUES (:src, :dest, :verb, 0);
UPDATE observations SET occurrences = occurrences + 1 WHERE
src = :src AND dest = :dest AND verb = :verb;
COMMIT;
which at least gets all the logic into SQL, but there's still two
index lookups in there where one should suffice.
I'm not subscribed to the mailing list, please cc: me on replies.
Thanks,
zw
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
