Re: [sqlite] Best approach for "notifications"

[Simon Slavin]

On 14 Jul 2014, at 5:00pm, João Ramos  wrote:

> The update hook says "The update hook implementation must not do anything
> that will modify the database connection that invoked the update hook. 

Ah, right.  Yes, you must store what you want your change to be in a variable 
and make it after the commit_hook or wipe it after the rollback_hook.

On that basis, I suspect a TRIGGER is going to be easier for you.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best approach for "notifications"

[João Ramos]

On Mon, Jul 14, 2014 at 3:57 PM, Simon Slavin  wrote:

>
> The update hook function is an ideal way to do what you're doing.  There
> is also a pre-update hook:
>
> 
>
> If you cannot use these, then my instinct is to try to figure out why,
> rather than to do something else that cannot possibly be as useful as they
> are.
>

The update hook says "The update hook implementation must not do anything
that will modify the database connection that invoked the update hook. Any
actions to modify the database connection must be deferred until after the
completion of the sqlite3_step() call that triggered the update hook. Note
that sqlite3_prepare_v2() and sqlite3_step() both modify their database
connections for the meaning of "modify" in this paragraph."

Because the update hook only supplies the rowId, I cannot extract the
information necessary with this callback.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best approach for "notifications"

[Simon Slavin]

On 14 Jul 2014, at 1:52pm, João Ramos  wrote:

> (for
> example I know we cannot use sqlite3_update_hook to retrieve data)

The update hook function is an ideal way to do what you're doing.  There is 
also a pre-update hook:



If you cannot use these, then my instinct is to try to figure out why, rather 
than to do something else that cannot possibly be as useful as they are.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best approach for "notifications"

[João Ramos]

Hi,

In a project I'm working on we need to have a mechanism that creates
notifications when something happens in any given table.
For example, for updates, we implemented something like:
 - create a ON UPDATE trigger in the target table
 - the trigger calls a custom function with every necessary column as an
argument (old and new data)
 - the custom function stores the old and new data
 - on commit (sqlite3_commit_hook), the data is sent as an event
 - on rollback the data is discarded

The insert and delete events are exactly the same except without the old
and new data respectively.
So my question is: is this the best approach? This method works but is also
a bit complex and I wasn't able to come up with something simpler (for
example I know we cannot use sqlite3_update_hook to retrieve data).

Thanks,
João Ramos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users