Re: [sqlite] Multiple Process Notification

[Kees Nuyt]

On Sun, 23 Nov 2008 10:33:28 -0600, "Dan Saul"
<[EMAIL PROTECTED]> wrote in General Discussion of
SQLite Database <sqlite-users@sqlite.org>:

>Hi SQLite,
>
>I would like to monitor an sqlite file for changes over the boundary
>of a process. The function sqlite3_update_hook appeared promising
>however appears to only apply to changes made within the same process.

Not even that, it only applies to changes made within the
same database connection.

>Since the file for SQLite is specified I could be able to monitor it
>using g_file_monitor_file
>(http://library.gnome.org/devel/gio/unstable/GFile.html#g-file-monitor-file)
>or another file monitoring solution. This solution however seems
>crude. Would anyone on this list have a more appropriate solution?


Step 1
------
Add a log table to your schema:

CREATE TABLE IF NOT EXISTS modifications (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        tablename TEXT,
        modaction TEXT,
        modrowid  INTEGER,      
        dtupdate  REAL
);

Of course you can leave out any columns you don't need.

The minimum implementation would be:
CREATE TABLE IF NOT EXISTS lastmodified (
        id        INTEGER, -- always 1
        dtupdate  REAL
);


Step 2
------
Create insert, update and delete triggers for every table in
your schema:

CREATE TRIGGER ins_{tablename} AFTER INSERT ON {tablename}
FOR EACH ROW
BEGIN
        INSERT INTO modifications 
                (tablename, modaction, modrowid, dtupdate)
        VALUES
                ('{tablename}','i',NEW.ROWID,julianday('now')); 
END;

or, for the minimum implementation:

CREATE TRIGGER ins_{tablename} AFTER INSERT ON {tablename}
FOR EACH ROW
BEGIN
        INSERT OR REPLACE INTO lastmodified
                (id, dtupdate)
        VALUES
                (1,julianday('now')); 
END;

Notes
-----
Code is not tested.

The on update and on delete triggers are left as an exercise
to the reader.

Alternatively, you can add an update timestamp column to
every table and update that using a trigger, but that would
not reflect any deletions.

>Dan Saul
-- 
  (  Kees Nuyt
  )
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users