keeping a log table in sqlite database?

Question

I'm looking for a way to set up (via SQL) a log table containing everything that had been done to my sqlite database (preferably in terms of the insert, create table etc. statements as issued to the database). I'm sure there are way to do it via setting trigger on each table, but that is just WAY too much work and does not bode well if I change the database's schema later. Is there a catch-all global thing that work on the database (like trigger on database itself)?

I'm also open for other suggestions for keeping records of changes done to sqlite database so that I can look back months later on the changes.

(Programatically of course there are ways but I can't be sure that my program is the only program writing to the database).

If there was a trigger on the database, wouldn't it fire itself?

Darren Griffith · Answer 1 · 2009-01-10 03:48:48Z

Though the following doesn't meet all your requirements, you may wish to see one way to do it. Mike Chirico's SQLite Tutorial has a section on Logging All Inserts, Updates, and Deletes that mimics the functionality of MySQL's binlog.

It relies on triggers that must match the schema for each table whose changes you wish to track. That is, if your table has a field called "a", then the logging table needs to keep track of "aOLD" and "aNEW". In this way, the trigger is able to record updates, inserts, and deletes made to the fields in that particular table.

I've never used SQL Lite but you shoule be able to retrive the schema and then you could make a script that updates your triggers and history tables.

thkala · Answer 2 · 2012-01-07 11:27:51Z

these function may be useful

void *sqlite3_update_hook(  sqlite3*,   void(*)(void *,int ,char const *,char const *,sqlite3_int64),  void*);void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*);void *sqlite3_profile(   sqlite3*,   void(*xProfile)(void*,const char*,sqlite3_uint64), void*);

they seem to act on individual dbs

the only global way I can see is to use

sqlite3_vfs *sqlite3_vfs_find(const char *zVfsName);int sqlite3_vfs_register(sqlite3_vfs*, int makeDflt);int sqlite3_vfs_unregister(sqlite3_vfs*);

The functions seem to work only when someone who writes to the database using their api explicitly hook up, but I'm looking for ways that also leaves a record when anyone (potentially not my program) writes to the database.

keeping a log table in sqlite database?

2 Answers