filling a key/value table

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

filling a key/value table

Hick Gunter
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: filling a key/value table

Igor Tandetnik-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: filling a key/value table

Simon Slavin-3
In reply to this post by Hick Gunter
On 21 Mar 2019, at 4:04pm, Hick Gunter <[hidden email]> wrote:

> CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER, primary key (keyid, value, location) ) WITHOUT ROWID;

Can't answer the question you posed and I'm happy to see Igor could.  But I wanted to speak against including a BLOB field in a compound PRIMARY KEY.

The PRIMARY KEY gets used a lot when SQLite does anything with a row, and having to match a whole BLOB and then compound it is going to take some time.  I think it would be faster to let SQLite do its normal rowid thing and define the above PRIMARY KEY as a UNIQUE INDEX instead.

Of course, it may be faster or more convenient to you in some other way in which case I stand corrected.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: filling a key/value table

Clemens Ladisch
Simon Slavin wrote:
> I wanted to speak against including a BLOB field in a compound PRIMARY KEY.

That depends on the size of the blob.  If it contains 'normal'-sized values,
it's just as efficient as other types.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: filling a key/value table

Hick Gunter
In reply to this post by Simon Slavin-3
The declared type BLOB has the advantage of not messing around with the type of whatever is stored. Specifically, I have strings that look like 16 digit numbers, some with a leading zero, that would get clobbered by NUMERIC affinity; likewise, TEXT affinity would convert integers to text, wasting effort and space. I don't intend to actually store BLOBs in this field.

In my use case, the storage class of the actual values is constant for a given keyid. I could add an index on (value,keyid,location) and SQLite could quickly exclude any TEXT values from a key lookup given an INTEGER value just by looking at the manifest. INTEGER or REAL sort before TEXT.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Simon Slavin
Gesendet: Donnerstag, 21. März 2019 17:38
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] filling a key/value table

On 21 Mar 2019, at 4:04pm, Hick Gunter <[hidden email]> wrote:

> CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER, primary key (keyid, value, location) ) WITHOUT ROWID;

Can't answer the question you posed and I'm happy to see Igor could.  But I wanted to speak against including a BLOB field in a compound PRIMARY KEY.

The PRIMARY KEY gets used a lot when SQLite does anything with a row, and having to match a whole BLOB and then compound it is going to take some time.  I think it would be faster to let SQLite do its normal rowid thing and define the above PRIMARY KEY as a UNIQUE INDEX instead.

Of course, it may be faster or more convenient to you in some other way in which case I stand corrected.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users