Re: [sqlite] How to parameterize a loadable extension at runtime
> 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().
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 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
> > 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to parameterize a loadable extension at runtime
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().
This x_config() function is free to change global runtime preference
variables of the x() function based on the 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 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
> 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
Re: [sqlite] How to parameterize a loadable extension at runtime
Dominique Devienne wrote: > > On Tue, Feb 6, 2018 at 11:15 AM, Ulrich Telle 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to parameterize a loadable extension at runtime
On Tue, Feb 6, 2018 at 11:15 AM, Ulrich Telle 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to parameterize a loadable extension at runtime
> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to parameterize a loadable extension at runtime
On 6 Feb 2018, at 9:24am, Ulrich Telle 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to parameterize a loadable extension at runtime
On Tue, Feb 6, 2018 at 9:44 AM, 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.
>
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to parameterize a loadable extension at runtime
> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to parameterize a loadable extension at runtime
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.
Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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: ulrich.te...@gmx.de
World Wide Web: http://www.telle-online.de
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
