Thank you Kees! On 23-Nov-08, at 11:18 AM, Kees Nuyt wrote:
> 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
