I have an external data store that is accessible to sqlite as a virtual table.
The equivalent SQL declaration is similar to:
CREATE TABLE logfile (timestamp INTEGER, type INTEGER, name TEXT, size INTEGER,
...);
I would like to create an index as a native SQLite table declared like:
CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER, primary key
(keyid, value, location) ) WITHOUT ROWID;
The fields of interest are stored in a config table:
CREATE TABLE logkey( ID INTEGER PRIMARY KEY, name TEXT );
INSERT INTO logkey(name) VALUES ('type'),('name'),('size');
The naive method of inserting values is thus:
INSERT INTO logidx(keyid,value,location) SELECT k.id,l.type,l.location from
logkey k, logfile l where k.name = 'type'AND l.type IS NOT NULL;
INSERT INTO logidx(keyid,value,location) SELECT k.id,l.name,l.location from
logkey k, logfile l where k.name = 'name'AND l.name IS NOT NULL;
INSERT INTO logidx(keyid,value,location) SELECT k.id,l.size,l.location from
logkey k, logfile l where k.name = 'size'AND l.size IS NOT NULL;
This hast he disadvantage of requiring a complete scan of the virtual logidx
table for each kind of entry.
Any ideas on how to create all the tuples with only one pass of the logidx
table?
___________________________________________
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
