Re: [sqlite] Trigger's actions and callbacks
D. Richard Hipp wrote: > > > SQLite is not reentrant through the update hook. > > > D. Richard Hipp > [EMAIL PROTECTED] > > Thanks a lot. I would consider using user-defined functions as callbacks with updated data in its parameters. -- View this message in context: http://www.nabble.com/Trigger%27s-actions-and-callbacks-tp16418413p16467330.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger's actions and callbacks
> > Thanks. But I am experiensing some problems here. When I am using > the same > connection (I store it as third parameter to callback registering > function > > sqlite3_update_hook(db, Callback, db); > > and when trying to access to the db inside callback > > sqlite3_prepare((sqlite3*)data_arg_3, [...]) > > , an error SQLITE_MISUSE is returned to me SQLite is not reentrant through the update hook. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger's actions and callbacks
"Vladimir Volkov" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >>> sqlite_update_hook() returnes rowID, but I didn't >>> find any API to use this value... > >> You just run a statement along the lines of > >> select * from dbName.tableName where rowid=? > >> You need to run this statement on the same connection the hook is >> installed on. > > Thanks. But I am experiensing some problems here. When I am using the > same connection (I store it as third parameter to callback > registering function > > sqlite3_update_hook(db, Callback, db); > > and when trying to access to the db inside callback > > sqlite3_prepare((sqlite3*)data_arg_3, [...]) Show how Callback is declared. Did you really name its first parameter data_arg_3? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger's actions and callbacks
>If you are trying to use a different connection, you would only see >changes once the first connection committed its transaction - so don't >do that. >> sqlite_update_hook() returnes rowID, but I didn't >> find any API to use this value... >You just run a statement along the lines of >select * from dbName.tableName where rowid=? >You need to run this statement on the same connection the hook is >installed on. Thanks. But I am experiensing some problems here. When I am using the same connection (I store it as third parameter to callback registering function sqlite3_update_hook(db, Callback, db); and when trying to access to the db inside callback sqlite3_prepare((sqlite3*)data_arg_3, [...]) , an error SQLITE_MISUSE is returned to me (from sqlite3_prepare() as well as sqlite3_step() ). And finally I have got a Segmentation Fault from sqlite3_column_*(). The same with db connection as a global variable, when it is used during registering callback and inside one. So I supposed earlier that it is prohibited to use the same connection inside callback. And a really I used another connection and got nothing. So the question is - how to use the same connection correctry inside the callback? -- View this message in context: http://www.nabble.com/Trigger%27s-actions-and-callbacks-tp16418413p16446898.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger's actions and callbacks
> 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
pDbis 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
Re: [sqlite] Trigger's actions and callbacks
"Vladimir Volkov" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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. Correct. SQLite is not an interprocess communication mechanism. You will have to come up with your own scheme, outside SQLite. > On some certain reasons I am not allowed to write wrapper > that will send notifications using IPC. You mean, you need to perform IPC but you are not allowed to write code that performs IPC? That's quite a corner you are painted into. > I would like db to perform > this task. Then I guess SQLite is not suitable for your problem (though I can't think of any other DBMS that can do that, either). > 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. As long as you use the same connection to access the database as the one that caused the trigger or callback to fire in the first place, you should see the new data. It doesn't matter that the data has not been written to the physical file yet. If you are trying to use a different connection, you would only see changes once the first connection committed its transaction - so don't do that. > sqlite_update_hook() returnes rowID, but I didn't > find any API to use this value... You just run a statement along the lines of select * from dbName.tableName where rowid=? You need to run this statement on the same connection the hook is installed on. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
