sqlite> CREATE TABLE t (key TEXT PRIMARY KEY, count INTEGER);
sqlite> INSERT OR REPLACE INTO t VALUES("key1",coalesce((SELECT count FROM t
WHERE key="key1"),0)+1);
sqlite> SELECT * FROM t;
key1|1
sqlite> INSERT OR REPLACE INTO t VALUES("key1",coalesce((SELECT count FROM t
WHERE key="key1"),0)+1);
sqlite> SELECT * FROM t;
key1|2
It's not a hash table lookup though...it's a b-tree
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
________________________________
From: [email protected] on behalf of Alexei Alexandrov
Sent: Thu 11/25/2010 8:07 AM
To: [email protected]
Subject: EXTERNAL:[sqlite] Efficient hash lookup table emulation with SQLite -
how?
Hi,
I'm trying to solve efficiently the following task with SQlite:
* There is a trace file which contains a big number of some objects. Each
object has a number of fields which constitute its primary key (PK).
* The objects are loaded into a table which has a number of PK columns (mapped
from the trace object PK properties) and also has a number of non-key columns
which are used to aggregate certain information about objects - e.g. count of
objects, or min/max timestamp of the object instance in the trace.
* The loading function needs to:
** Understand whether the object is present or not in the table already. This
is done by the object PK fields.
** If it is present, update its non-key fields.
** If it is not present, insert new object filling the non-key fields with
default values.
As an example, consider table
CREATE TABLE t (key TEXT PRIMARY KEY, count INTEGER);
which would be used for counting how many times a certain word appeared in the
text. We need to walk over word list and either set the counter to 1 or
increase it if the value is already present.
Ideally, I would like to do the following:
* INSERT operation for the primary key like
INSERT INTO t (key, count) VALUES (?, 1);
and if primary key already exists, get the rowid of that row so that I can do
UPDATE t SET count = count+1 WHERE rowid = ?;
passing the rowid found during failed insertion operation.
But, it's not possible now - rowid is not returned during failed PK contraint
insertion. I cannot do "INSERT OR REPLACE" because REPLACE removes the row and
so count will be lost.
So currently I have to do:
* First, SELECT operation to try to find the row by primary key and return its
rowid.
* If select operation returned rowid, use that to do UPDATE.
* If select operation did not return anything, do INSERT.
I feel that there should be more efficient ways to do this hash table emulation.
Are there? Or am I trying to get something irrelevant from a SQL database?
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
