Interesting. How do you discern between "names of virtual tables that are not
yet loaded" and "names of virtual tables that do not exist"?
We have two strategies here:
1) "Cloned" general tables:
These have identical structures, but contents is partitioned by one or more
fields. The backing store (typically one ctree file per table) and SQLite
virtual table are created/destroyed ("cloned" und "uncloned") together. There
is usually another virtual table (called the "partition" table) that works as a
single point of access for queries.
E.g. a table of customer cards organized per jurisdiction. There will be one
(or more, for a multi-jurisdiction application) customer card file
"/...cust_card_01.dat" with a corresponding SQLite virtual table "CREATE
VIRTUAL TABLE cust_card_01 USING ctree(...);" and one partition table for SQL
access "CREATE VIRUTAL TABLE cust_card USING partition(...);"
This strategy is mostly used for tables that typically stick around for a long
time. Application processes typically directly access the backing store,
because this is faster; reports and queries typically access the partition
table, because they are independant of the jurisdiction.
2) Speciality tables:
The backing store is composed of files that are typically created per day and
persist only for a short period of time. There is only one virtual table, and
the virtual table module handles which files are present internally.
E.g. a transaction log table, organised by daily files that are kept for a
certai number of days. "CREATE VIRTUAL TABLE txlog USING txlog(...);" Which
checks internally if the requested day's file is present or not.
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Philippe Riand
Gesendet: Samstag, 17. März 2018 15:53
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Lazy virtual table creation
We are using virtual tables to provide an SQL access to our data set and this
works very well. But we have potentially "a lot” of virtual tables, with some
even yet unknown when we start the DB. We’d like to create them lazily, on
first access.
Is there a hook we can use so when an SQL statement refers to a non existing
table it asks a callback for a VT definition? It is fine if these dynamic table
requires a specific prefix similar to the "temp” one.
Regards,
Phil.
_______________________________________________
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
