On 3/21/2019 12:04 PM, Hick Gunter wrote:
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?
Something like this: INSERT INTO logidx(keyid,value,location) SELECT k.id, (case k.name when 'type' then l.type when 'name' then l.name when 'size' then l.size else NULL end) AS val, l.location FROM logfile l, logkey k WHERE val IS NOT NULL; -- Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
