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).

Answer 1

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*);

Answer 2

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.