> Hi, all.
>
> I have the following problem: I have some processes that access the same db
> file. When one of them would change some data inside the db it must notify
> all other processes about change AND send them changed data (or it's unique
> description).
>
> But I didn't find any method to perform this using db triggers or callbacks.
> On some certain reasons I am not allowed to write wrapper that will send
> notifications using IPC. I would like db to perform this task.
I have a similar problem, which I solved using db triggers, along with a
callback mechanism involving a user-defined function.
> As for triggers and callbacks (hooks): they are fired before the data is
> stored to the db (file), so that I coudn't even read changed data back
> inside hook. sqlite_update_hook() returnes rowID, but I didn't find any API
> to use this value...
Triggers can be defined to happen BEFORE or AFTER the database event.
Either way, you can access the old and new values of a field using the
OLD and NEW keywords. See www.sqlite.org->Documentation->SQL Syntax->CREATE
TRIGGER
for details.
> I would appriciate any ideas or comments.
> Thanks in advance.
Below is a memo I wrote to my boss describing how we could do change
notification
in SQLite. It is written in terms of SQLite 2; to use the same mechanism in
SQLite 3, you would use sqlite3_create_function() instead of
sqlite_create_function()
to register your user-defined function. Note that in SQLite 3, the parameters
to
sqlite(3)_create_function() have changed, as have the parameters passed to your
user-defined function.
If people are interested, I can turn this into documentation (Wiki article?)
for the SQLite website.
Regards,
- Richard Klein
==================================================================================
Hi Dave,
As you mentioned earlier, the PVR 2.0 Scheduler maintains its own data
structures
that must be kept in sync with the database. Therefore, if the app modifies the
database, the Scheduler needs to be notified of the change.
In playing around with SQLite, I have come up with a change notification
mechanism
that makes use of a user-defined function is conjunction with a SQL entity
known as
a "trigger".
A trigger is a SQL statement that is associated with a specified table, and
with a
specified action (INSERT, UPDATE, or DELETE) on that table. This SQL statement
is
automatically executed when the associated action is performed on the associated
table.
To create an INSERT trigger on the 'requests' table, you would execute the
following
SQL statement:
CREATE TRIGGER InsertedRequest AFTER INSERT ON requests
BEGIN
SELECT ChangeNotify('requests', 'INSERT', NEW.ulRequestId);
END;
UPDATE and DELETE triggers would be created in a similar fashion.
The above statement defines a trigger, named 'InsertedRequest', that will fire
*after*
an INSERT operation is performed on the 'requests' table. This trigger will
call a
user-defined function named 'ChangeNotify'. ChangeNotify takes three
parameters: The
name of the affected table, the operation (INSERT, UPDATE, or DELETE) that was
performed,
and the Request ID (ulRequestId) of the affected row.
The ChangeNotify function is defined as follows:
static void changeNotify(sqlite_func* context, int argc, const char** argv)
{
const char *table, *action, *rowid;
assert(argc == 3);
table = argv[0];
action = argv[1];
rowid = argv[2];
dprintf("Database changed: table = %s, action = %s, rowid = %s\n", table,
action, rowid);
}
Like all user-defined functions, changeNotify() is called with three
parameters: An opaque
pointer 'context', the argument count 'argc' (which will be '3' in this case),
and the three
arguments (table, action, and row in this case).
This simple version of changeNotify() simply prints out the calling parameters.
The production
version will invoke a callback that has been registered by the Scheduler.
Of course, like all user-defined functions, changeNotify() must be registered
with SQLite:
status = sqlite_create_function(pDb, "ChangeNotify", 3, changeNotify,
NULL);
if (status != SQLITE_OK) {
/* error */
}
where
pDb is an opaque pointer;
"ChangeNotify" is the name of the user-defined function;
3 is the number of parameters taken by the user-defined function;
and
changeNotify is the address of the user-defined function.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
