Function design question

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

Function design question

Toby Dickenson
I am using a trigger to maintain some audit information, for example
updating row modification time on any insert or update. I now want to
extend that mechanism to include other information from my
application, for example logged in user name. I can do that by
registering new functions to pass the extra information from the
application to the trigger.

But I also want to update these tables from the command line, when
these functions wont be available. Is there a way to construct the
trigger so that I get NULLs instead of errors when a custom function
doesnt exist? Or a better way to pass information into the trigger
other than functions?

Thanks,
_______________________________________________
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: Function design question

Simon Slavin-3


On 15 Mar 2018, at 11:40am, Toby Dickenson <[hidden email]> wrote:

> But I also want to update these tables from the command line, when
> these functions wont be available. Is there a way to construct the
> trigger so that I get NULLs instead of errors when a custom function
> doesnt exist? Or a better way to pass information into the trigger
> other than functions?

I can't answer your question, but if you're interested in having a function called automatically when your database is updated, you might be interested in using

<https://sqlite.org/c3ref/update_hook.html>

Because of how this works you would want to avoid this function using sqlite3_ calls (explicitly mentioned in the documentation, possibly to avoid recursion).  For this reason, if you use this hook your audit file would probably be best as a text file, opened and written using normal file operations rather than SQLite calls.

Another way to log changes is to use

<https://sqlite.org/c3ref/set_authorizer.html>

It should always return SQLITE_OK, but it can note when it is called with commands which would modify your database.

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: Function design question

petern
In reply to this post by Toby Dickenson
Simply bracket your server code with preprocessor directives like so:

static void my_universal_function(sqlite3_context *context, int argc,
sqlite3_value **argv) {
<common-mode-code>
#ifdef MY_SQLITE_EXTENSION_MODE
  <client-mode-code>
#else
  <server-mode-code>
#endif
}

Then create another makefile rule target or IDE project which defines the
'MY_SQLITE_EXTENSION_MODE' to compile the common code file(s) as an
extension module for use on the command line.  Read about extensions here:

https://sqlite.org/loadext.html

By default, SQLite will return NULL when a function makes no return value
call.  BTW, per your preference, the logically complementary directive
#ifndef might work better for you.

Obviously you are free to choose more meaningful and compact macro
directive name than 'MY_SQLITE_EXTENSION_MODE'.  I chose that macro name
only to make this reply clear to the complete novice.

In practice you may find a lot of code will be able to meaningfully run in
the command line context - or possibly communicate with a running
application server instance capable of computing answers for the command
line context.

Peter



On Thu, Mar 15, 2018 at 4:40 AM, Toby Dickenson <[hidden email]> wrote:

> I am using a trigger to maintain some audit information, for example
> updating row modification time on any insert or update. I now want to
> extend that mechanism to include other information from my
> application, for example logged in user name. I can do that by
> registering new functions to pass the extra information from the
> application to the trigger.
>
> But I also want to update these tables from the command line, when
> these functions wont be available. Is there a way to construct the
> trigger so that I get NULLs instead of errors when a custom function
> doesnt exist? Or a better way to pass information into the trigger
> other than functions?
>
> Thanks,
> _______________________________________________
> 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