How to parameterize a loadable extension at runtime

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

How to parameterize a loadable extension at runtime

Ulrich Telle
I have implemented a loadable SQLite extension. The behaviour of the
extension can be configured by setting various parameters. Currently I select
the parameter settings at compile time. However this is not very flexible. I
would like to be able to modify the parameters at runtime.

The most logical way would be to add extension-specific pragmas, but it
doesn't seem to be possible to intercept the pragma handling of SQLite
without modifying the SQLite source.

Another possibility would be to add a user-defined function for the
configuration of the extension that could be called from a SELECT
statement:

SELECT myextension_config('param-name', 'param-value');

Is there a better (or even recommended) way how to accomplish such
parameterization at runtime?

Regards,

Ulrich
--
E-Mail privat:  [hidden email]
World Wide Web: http://www.telle-online.de


_______________________________________________
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: How to parameterize a loadable extension at runtime

Simon Slavin-3


On 6 Feb 2018, at 8:33am, Ulrich Telle <[hidden email]> wrote:

> Another possibility would be to add a user-defined function for the
> configuration of the extension that could be called from a SELECT
> statement:
>
> SELECT myextension_config('param-name', 'param-value');

I've seen this done before.  Of course it means that your normal function is not deterministic, so you may no longer use SQLITE_DETERMINISTIC .  This is in contrast to a function where parameters are set during compilation.

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: How to parameterize a loadable extension at runtime

Ulrich Telle
> Simon Slavin wrote:
>
> On 6 Feb 2018, at 8:33am, Ulrich Telle wrote:
>
> > Another possibility would be to add a user-defined function for the
> > configuration of the extension that could be called from a SELECT
> > statement:
> >
> > SELECT myextension_config('param-name', 'param-value');
>
> I've seen this done before.  Of course it means that your normal function is not deterministic, so you may no longer use SQLITE_DETERMINISTIC .  This is in contrast to a function where parameters are set during compilation.

Well, actually my goal is not to have an extension with non-deterministic functions. The parameters have mostly the purpose to initialize the extension (things similar to what you do to SQLite itself with pragmas like "PRAGMA cache_size", or "PRAGMA data_store_directory"). The extension would accept changes to the parameters only before the first invocation of the extension functions.

Regards,

Ulrich
_______________________________________________
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: How to parameterize a loadable extension at runtime

Dominique Devienne
In reply to this post by Simon Slavin-3
On Tue, Feb 6, 2018 at 9:44 AM, Simon Slavin <[hidden email]> wrote:

> On 6 Feb 2018, at 8:33am, Ulrich Telle <[hidden email]> wrote:
>
> > Another possibility would be to add a user-defined function for the
> > configuration of the extension that could be called from a SELECT
> > statement:
> >
> > SELECT myextension_config('param-name', 'param-value');
>
> I've seen this done before.  Of course it means that your normal function
> is not deterministic,

so you may no longer use SQLITE_DETERMINISTIC .

This is in contrast to a function where parameters are set during
> compilation.
>

An alternative is to expose a virtual table with a fixed set of rows, and
accepting
updates on the values, which can also then be "typed" too. But that's a lot
more
complicated though. (and refusing inserts/deletes too, of course).

That vtable could also expose version information for the extension, for
example,
and those would be read-only. Just thinking aloud. Avoids non-deterministic
functions. --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] How to parameterize a loadable extension at runtime

Hick Gunter
In reply to this post by Ulrich Telle
You can pass parameters to the xCreate function in the CREATE VIRTUAL TABLE statement, if the setting you desire remains unchanged during the lifetime of the table.

CREATE VIRTUAL TABLE <name> USING <module> [ ( <parameter>,...) ];

You can declare hidden fields in the call to sqlite3_declare_vtab() call within your xCreate function if the setting you desire are specific to a query. The constraint will be passed to your xBestIndex function, and (if the query plan is selected) the value will be passed to your xFilter function.

CREATE VIRTUAL TABLE with_foo USING handle_foo;

Sqlite3_declare_vtab(db_hanlde, "CREATE TABLE x ( ..., foo integer hidden, ...);");

SELECT .... FROM with_foo wf .... WHERE wf.foo = 'bar';

Or, for "none of the above", create a user defined function that will handle storing/retrieving the settings and provide a C interface for your virtual table implementation to access them directly

SELECT param('foo','bar') AS p;
p
----------
NULL

SELECT param('foo') AS foo;
foo
---------
bar



-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Ulrich Telle
Gesendet: Dienstag, 06. Februar 2018 09:34
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] How to parameterize a loadable extension at runtime

I have implemented a loadable SQLite extension. The behaviour of the extension can be configured by setting various parameters. Currently I select the parameter settings at compile time. However this is not very flexible. I would like to be able to modify the parameters at runtime.

The most logical way would be to add extension-specific pragmas, but it doesn't seem to be possible to intercept the pragma handling of SQLite without modifying the SQLite source.

Another possibility would be to add a user-defined function for the configuration of the extension that could be called from a SELECT
statement:

SELECT myextension_config('param-name', 'param-value');

Is there a better (or even recommended) way how to accomplish such parameterization at runtime?

Regards,

Ulrich
--
E-Mail privat:  [hidden email]
World Wide Web: http://www.telle-online.de


_______________________________________________
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: How to parameterize a loadable extension at runtime

Simon Slavin-3
In reply to this post by Ulrich Telle
On 6 Feb 2018, at 9:24am, Ulrich Telle <[hidden email]> wrote:

> Well, actually my goal is not to have an extension with non-deterministic functions. The parameters have mostly the purpose to initialize the extension (things similar to what you do to SQLite itself with pragmas like "PRAGMA cache_size", or "PRAGMA data_store_directory"). The extension would accept changes to the parameters only before the first invocation of the extension functions.

Well, you seem to know what you're doing.  So you could follow your proposed plan and mark the function as deterministic even though you know that you could abuse it by changing its parameters.  This seems to be simpler than any other plan I've seen.

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: How to parameterize a loadable extension at runtime

Ulrich Telle
In reply to this post by Dominique Devienne
> Dominique Devienne wrote:
>
> An alternative is to expose a virtual table with a fixed set of rows, and
> accepting updates on the values, which can also then be "typed" too.
> But that's a lot more complicated though.
> (and refusing inserts/deletes too, of course).
>
> That vtable could also expose version information for the extension, for
> example, and those would be read-only. Just thinking aloud.
> Avoids non-deterministic functions.

A vtable with a fixed number of rows, one for each config parameter - this approach sounds interesting.

I'll have to investigate how complicated it will be to implement such an approach.

Regards,

Ulrich
_______________________________________________
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: How to parameterize a loadable extension at runtime

Dominique Devienne
On Tue, Feb 6, 2018 at 11:15 AM, Ulrich Telle <[hidden email]> wrote:

> > An alternative is to expose a virtual table with a fixed set of rows, and
> > accepting updates on the values, which can also then be "typed" too.
> > But that's a lot more complicated though.
> > (and refusing inserts/deletes too, of course).
> >
> > That vtable could also expose version information for the extension, for
> > example, and those would be read-only. Just thinking aloud.
> > Avoids non-deterministic functions.
>
> A vtable with a fixed number of rows, one for each config parameter - this
> approach sounds interesting.
> I'll have to investigate how complicated it will be to implement such an
> approach.
>

This approach could IMHO be one of the contributed vtable impls in ext/misc
[1]
to be reused by other loadable extension authors, and could become the
"semi official"
way to solve that problem, lacking extension specific pragmas that is. My
$0.02c. --DD

PS: There's also always environment variables, especially for 1-time at
startup settings.
  My main beef against env.vars. though is that they are not discoverable
and often hidden.

[1] https://www.sqlite.org/src/tree?name=ext/misc
_______________________________________________
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] How to parameterize a loadable extension at runtime

Ulrich Telle
In reply to this post by Hick Gunter
> Hick Gunter wrote:
>
> You can pass parameters to the xCreate function in the CREATE VIRTUAL TABLE statement, if the setting you desire remains unchanged during the lifetime of the table.
>
> CREATE VIRTUAL TABLE <name> USING <module> [ ( <parameter>,...) ];
>
> You can declare hidden fields in the call to sqlite3_declare_vtab() call within your xCreate function if the setting you desire are specific to a query. The constraint will be passed to your xBestIndex function, and (if the query plan is selected) the value will be passed to your xFilter function.
>
> CREATE VIRTUAL TABLE with_foo USING handle_foo;
>
> Sqlite3_declare_vtab(db_hanlde, "CREATE TABLE x ( ..., foo integer hidden, ...);");
>
> SELECT .... FROM with_foo wf .... WHERE wf.foo = 'bar';

I have to admit that I don't have much experience with the vtable concept. My extensions consist of a set of functions that can be used in SQL statements. During a single database connection the behaviour of the functions will be deterministic, but the user should be able to set certain initialization parameters.

Dominique Devienne proposed in his answer to implement a vtable with a fixed set of rows for the configuration parameters. That approach seems to be a bit simpler to implement than a fully fledged vtable solution.

> Or, for "none of the above", create a user defined function that will handle storing/retrieving the settings and provide a C interface for your virtual table implementation to access them directly
>
> SELECT param('foo','bar') AS p;
> p
> ----------
> NULL
>
> SELECT param('foo') AS foo;
> foo
> ---------
> bar

Yes, that's the approach I already mentioned in my original post. Adding a C interface is probably a good idea, too. However, setting parameters should be possible without calling a C interface function, for example, if a user loads the extension from the default SQLite shell coming with the SQLite distribution.

I would have preferred a more PRAGMA like syntax, but that could probably be called "syntactic sugar" - a SELECT with a user-defined function will work and the syntax is simple enough.

Regards,

Ulrich
_______________________________________________
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: How to parameterize a loadable extension at runtime

Ulrich Telle
In reply to this post by Dominique Devienne
Dominique Devienne wrote:

>
> On Tue, Feb 6, 2018 at 11:15 AM, Ulrich Telle <[hidden email]> wrote:
>
> > > An alternative is to expose a virtual table with a fixed set of rows, and
> > > accepting updates on the values, which can also then be "typed" too.
> > > But that's a lot more complicated though.
> > > (and refusing inserts/deletes too, of course).
> > >
> > > That vtable could also expose version information for the extension, for
> > > example, and those would be read-only. Just thinking aloud.
> > > Avoids non-deterministic functions.
> >
> > A vtable with a fixed number of rows, one for each config parameter - this
> > approach sounds interesting.
> > I'll have to investigate how complicated it will be to implement such an
> > approach.
> >
>
> This approach could IMHO be one of the contributed vtable impls in ext/misc
> [1] to be reused by other loadable extension authors, and could become the
> "semi official" way to solve that problem, lacking extension specific
> pragmas that is. My $0.02c. --DD

In case I'll implement the vtable approach, I might consider to make it available.

> PS: There's also always environment variables, especially for 1-time at
> startup settings.
>   My main beef against env.vars. though is that they are not discoverable
> and often hidden.

For my purpose environment variables are not suitable. The user should be able to alter the configuration parameters for each database connection.

Regards,

Ulrich
_______________________________________________
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: How to parameterize a loadable extension at runtime

petern
In reply to this post by Ulrich Telle
Simon has the correct idea.   If you have a function x(), you are free to
define another in the same extension called function x_config(<args>).
This x_config() function is free to change global runtime preference
variables of the x() function based on the <args> passed into the last call
of x_config().   SQLITE_DETERMINISTIC is merely a performance hint for
expression evaluation within a single statement.  Deterministic functions
may be called multiple times anyway and every distinct statement where
output depends on a function, deterministic or not, will cause that
function to be evaluated at least once.

The only problem will be if a thread in your process calls the x_config()
function while the x() function has a different thread context.  If
overlapping multithreaded usage is anticipated, global configuration
variable access must be serialized by the sqlite3 mutex API or other
critical section mechanism.

Peter

On Tue, Feb 6, 2018 at 1:24 AM, Ulrich Telle <[hidden email]> wrote:

> > Simon Slavin wrote:
> >
> > On 6 Feb 2018, at 8:33am, Ulrich Telle wrote:
> >
> > > Another possibility would be to add a user-defined function for the
> > > configuration of the extension that could be called from a SELECT
> > > statement:
> > >
> > > SELECT myextension_config('param-name', 'param-value');
> >
> > I've seen this done before.  Of course it means that your normal
> function is not deterministic, so you may no longer use
> SQLITE_DETERMINISTIC .  This is in contrast to a function where parameters
> are set during compilation.
>
> Well, actually my goal is not to have an extension with non-deterministic
> functions. The parameters have mostly the purpose to initialize the
> extension (things similar to what you do to SQLite itself with pragmas like
> "PRAGMA cache_size", or "PRAGMA data_store_directory"). The extension would
> accept changes to the parameters only before the first invocation of the
> extension functions.
>
> Regards,
>
> Ulrich
> _______________________________________________
> 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: How to parameterize a loadable extension at runtime

Ulrich Telle
> Simon has the correct idea.   If you have a function x(), you are free
> to define another in the same extension called function
> x_config(<args>).

Yes, of course. In fact, I mentioned this option already in my original post.
The syntax for the user will be less intuitive than a pragma statement, but
since it seems to be impossible to intercept pragma handling, it is most
probably the easiest solution.  

> This x_config() function is free to change global runtime preference
> variables of the x() function based on the <args> passed into the last
> call of x_config().   SQLITE_DETERMINISTIC is merely a performance
> hint for expression evaluation within a single statement.
> Deterministic functions may be called multiple times anyway and every
> distinct statement where output depends on a function, deterministic or
> not, will cause that function to be evaluated at least once.

The functions in my extension will all be deterministic. The purpose of the
parameters is to initialize the environment of my extension. The alternative
would be that the user would pass the parameters to each call of one of the
extension functions, but this is cumbersome and errorprone.  

> The only problem will be if a thread in your process calls the
> x_config() function while the x() function has a different thread
> context.  If overlapping multithreaded usage is anticipated, global
> configuration variable access must be serialized by the sqlite3 mutex
> API or other critical section mechanism.

That's a valid point. I should better take care that different threads will not
use different parameter settings. Thanks.  

Regards,  

Ulrich  


> On Tue, Feb 6, 2018 at 1:24 AM, Ulrich Telle wrote:
>
> > > Simon Slavin wrote:
> > >
> > > On 6 Feb 2018, at 8:33am, Ulrich Telle wrote:
> > >
> > > > Another possibility would be to add a user-defined function for the
> > > > configuration of the extension that could be called from a SELECT
> > > > statement:
> > > >
> > > > SELECT myextension_config('param-name', 'param-value');
> > >
> > > I've seen this done before.  Of course it means that your normal
> > function is not deterministic, so you may no longer use
> > SQLITE_DETERMINISTIC .  This is in contrast to a function where parameters
> > are set during compilation.
> >
> > Well, actually my goal is not to have an extension with non-deterministic
> > functions. The parameters have mostly the purpose to initialize the
> > extension (things similar to what you do to SQLite itself with pragmas
> like
> > "PRAGMA cache_size", or "PRAGMA data_store_directory"). The extension
> would
> > accept changes to the parameters only before the first invocation of the
> > extension functions.
> >
> > Regards,
> >
> > Ulrich
> > _______________________________________________
> > 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


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users