Re: [sqlite] 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(). 

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

[petern]

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

[Ulrich Telle]

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

[Dominique Devienne]

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

[Ulrich Telle]

> 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

[Simon Slavin]

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

[Dominique Devienne]

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

[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
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]

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

[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:  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