Lazy virtual table creation

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

Lazy virtual table creation

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

Re: Lazy virtual table creation

Richard Hipp-3
On 3/17/18, Philippe Riand <[hidden email]> wrote:
> 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.

There is no such hook.  Sorry.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Lazy virtual table creation

Marco Bambini
In reply to this post by Philippe Riand-2
Philippe,
with a bit of work you can use the authorize api in order to know when an access to a non existing table is performed.
https://sqlite.org/c3ref/set_authorizer.html

Hope this helps.
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs



> On 17 Mar 2018, at 15:53, Philippe Riand <[hidden email]> wrote:
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
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: Lazy virtual table creation

Dominique Devienne
On Sat, Mar 17, 2018 at 11:42 PM, Marco Bambini <[hidden email]> wrote:

> with a bit of work you can use the authorize api in order to know when an
> access to a non existing table is performed.
> https://sqlite.org/c3ref/set_authorizer.html


Interesting work-around, if that works. I.e. whether name resolution to
table/column happens before or after the authorizer is called.

But it brings up the more general question of knowing which APIs are safe
to call inside other APIs,
notably when doing schema changes for example. In this case for example,
we'd typically be inside
a prepare statement, and is it OK to change the schema, potentially
affecting that very statement we
are currently parsing/resolving/compiling?

I.e. API re-entrance but outside the context of multi-threading I guess.
--DD
_______________________________________________
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: Lazy virtual table creation

Hick Gunter
IIRC it is NOT safe to call sqlite3_prepare() or sqlite3_step() withtin the authorizer callback, so schema changes are out of the question, as you would have to prepare/step a "CREATE VIRTUAL TABLE" statement for on-the-fly virtual table creation.

"The authorizer callback must not do anything that will modify the database connection that invoked the authorizer callback. Note that sqlite3_prepare_v2() and sqlite3_step() both modify their database connections for the meaning of "modify" in this paragraph."

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dominique Devienne
Gesendet: Montag, 19. März 2018 10:45
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Lazy virtual table creation

On Sat, Mar 17, 2018 at 11:42 PM, Marco Bambini <[hidden email]> wrote:

> with a bit of work you can use the authorize api in order to know when
> an access to a non existing table is performed.
> https://sqlite.org/c3ref/set_authorizer.html


Interesting work-around, if that works. I.e. whether name resolution to table/column happens before or after the authorizer is called.

But it brings up the more general question of knowing which APIs are safe to call inside other APIs, notably when doing schema changes for example. In this case for example, we'd typically be inside a prepare statement, and is it OK to change the schema, potentially affecting that very statement we are currently parsing/resolving/compiling?

I.e. API re-entrance but outside the context of multi-threading I guess.
--DD
_______________________________________________
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
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Lazy virtual table creation

Hick Gunter
In reply to this post by Philippe Riand-2
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:[hidden email]] Im Auftrag von Philippe Riand
Gesendet: Samstag, 17. März 2018 15:53
An: [hidden email]
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
[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
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Lazy virtual table creation

Philippe Riand
In reply to this post by Philippe Riand-2
Thanks for your responses.

Actually, it is up to my code to find out if the table exists or not. But you also raised a good point, as it can disappear, so what I’m looking for is more something like: “this statement uses this set set of tables [x,y,z…], please prepare your environment accordingly”. This is what I’m currently doing by pre-eanalyzing the SQL statement and extract the tables matching a identifiable pattern.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users