See if something like this works better: INSERT INTO logidx(keyid,value,location) SELECT id, val, location from ( SELECT k.id 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 location FROM logfile l, logkey k ) WHERE val IS NOT NULL;
Or WITH RawData AS ( SELECT k.id 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 location FROM logfile l, logkey k ) INSERT INTO logidx(keyid,value,location) SELECT id, val, location from RawData WHERE val IS NOT NULL; Igor Tandetnik On 3/26/2019 10:15 AM, Hick Gunter wrote:
This works as expected, thanks. SQLite implements this as a pair of coroutines: Routine A) does a nested scan of the logfile (outer loop = just read the logifle once) and the logkey table (inner loop) Routine B) does the actual insert(s) into the logidx table Unfortunately, the necessary check of the generated val causes the CASE expression to be evaluated twice (see bytecode). Instructions 16 thru 25 implement the CASE, leaving R12 holding the computed value Instruction 26 checks that something the evaluates to TRUE has been found Instructions 28 thru 37 duplicate the CASE, leaving R3 with the re-computed value The same effect could be achieved by copying R12 into R3 when buildung the record, just as R11 is copied to R4 9 VFilter 0 43 7 00 iplan=r[7] zplan='' 10 VColumn 0 47 11 00 r[11]=vcolumn(47); atx_txlog.period_no 11 Lt 13 42 11 (BINARY) 53 if r[11]<r[13] goto 42 12 Gt 14 42 11 (BINARY) 53 if r[11]>r[14] goto 42 13 Explain 13 0 0 SCAN TABLE keys AS k 00 14 Rewind 1 43 0 00 15 Column 1 1 15 00 r[15]=keys.name 16 Ne 17 19 15 (NOCASE) 52 if r[15]!=r[17] goto 19 17 VColumn 0 15 12 00 r[12]=vcolumn(15); atx_txlog.event_type 18 Goto 0 26 0 00 19 Ne 18 22 15 (NOCASE) 52 if r[15]!=r[18] goto 22 20 VColumn 0 28 12 00 r[12]=vcolumn(28); atx_txlog.retailer_loc_id 21 Goto 0 26 0 00 22 Ne 19 25 15 (NOCASE) 52 if r[15]!=r[19] goto 25 23 VColumn 0 64 12 00 r[12]=vcolumn(64); atx_txlog.ticket_key_string 24 Goto 0 26 0 00 25 Null 0 12 0 00 r[12]=NULL 26 IfNot 12 41 1 00 27 Rowid 1 2 0 00 r[2]=rowid 28 Ne 17 31 15 (NOCASE) 52 if r[15]!=r[17] goto 31 29 VColumn 0 15 3 00 r[3]=vcolumn(15); atx_txlog.event_type 30 Goto 0 38 0 00 31 Ne 18 34 15 (NOCASE) 52 if r[15]!=r[18] goto 34 32 VColumn 0 28 3 00 r[3]=vcolumn(28); atx_txlog.retailer_loc_id 33 Goto 0 38 0 00 34 Ne 19 37 15 (NOCASE) 52 if r[15]!=r[19] goto 37 35 VColumn 0 64 3 00 r[3]=vcolumn(64); atx_txlog.ticket_key_string 36 Goto 0 38 0 00 37 Null 0 3 0 00 r[3]=NULL 38 Copy 11 4 0 00 r[4]=r[11] 39 VColumn 0 6 5 00 r[5]=vcolumn(6); atx_txlog.sync_offset 40 Yield 6 0 0 00 41 Next 1 15 0 01 42 VNext 0 10 0 00 -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Igor Tandetnik Gesendet: Donnerstag, 21. März 2019 17:29 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] filling a key/value table 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
