Hello! This may be used for table versioning and replication.
Source code is available here http://mobigroup.ru/files/sqlite-ext/ You can get from the debian repository the SQLite build with some extra extensions: deb http://mobigroup.ru/debian/ lenny main contrib non-free deb-src http://mobigroup.ru/debian/ lenny main contrib non-free =========Test script============== CREATE TABLE sessions ( key text not null, value text not null ); insert into sessions (key,value) values ('test key 0','test value 0'); select versioning_table('sessions'); .schema select * from _versioning_sessions; insert into sessions (key,value) values ('test key 1','test value 1'); insert into sessions (key,value) values ('test key 2','test value 2'); select * from _versioning_sessions; delete from sessions; select * from _versioning_sessions; select unversioning_table('sessions'); .schema ==========Test script results========= sqlite> CREATE TABLE sessions ( ...> key text not null, ...> value text not null ...> ); sqlite> insert into sessions (key,value) values ('test key 0','test value 0'); sqlite> sqlite> select versioning_table('sessions'); sqlite> .schema CREATE TABLE _undo(sql TEXT, status TEXT); CREATE TABLE _versioning_sessions(key text,value text, _date REAL, _action TEXT, _rowid INTEGER); CREATE TABLE sessions ( key text not null, value text not null ); CREATE TRIGGER _versioning_sessions_d AFTER DELETE ON sessions BEGIN INSERT INTO _versioning_sessions (_date, _action, _rowid) values (julianday('now'), 'D', old.rowid);END; CREATE TRIGGER _versioning_sessions_i AFTER INSERT ON sessions BEGIN INSERT INTO _versioning_sessions SELECT *, julianday('now') as _date, 'I' as _action, new.rowid as _rowid FROM sessions WHERE rowid=new.rowid;END; CREATE TRIGGER _versioning_sessions_u AFTER UPDATE ON sessions BEGIN INSERT INTO _versioning_sessions SELECT *, julianday('now') as _date, 'U' as _action, new.rowid as _rowid FROM sessions WHERE rowid=new.rowid;END; sqlite> select * from _versioning_sessions; test key 0|test value 0|2455027.00753221|I|1 sqlite> sqlite> insert into sessions (key,value) values ('test key 1','test value 1'); sqlite> insert into sessions (key,value) values ('test key 2','test value 2'); sqlite> select * from _versioning_sessions; test key 0|test value 0|2455027.00753221|I|1 test key 1|test value 1|2455027.00753347|I|2 test key 2|test value 2|2455027.00753368|I|3 sqlite> sqlite> delete from sessions; sqlite> select * from _versioning_sessions; test key 0|test value 0|2455027.00753221|I|1 test key 1|test value 1|2455027.00753347|I|2 test key 2|test value 2|2455027.00753368|I|3 ||2455027.00753382|D|1 ||2455027.00753382|D|2 ||2455027.00753382|D|3 sqlite> sqlite> select unversioning_table('sessions'); sqlite> .schema CREATE TABLE _undo(sql TEXT, status TEXT); CREATE TABLE _versioning_sessions(key text,value text, _date REAL, _action TEXT, _rowid INTEGER); CREATE TABLE sessions ( key text not null, value text not null ); sqlite> select * from _versioning_sessions; test key 0|test value 0|2455027.00753221|I|1 test key 1|test value 1|2455027.00753347|I|2 test key 2|test value 2|2455027.00753368|I|3 ||2455027.00753382|D|1 ||2455027.00753382|D|2 ||2455027.00753382|D|3 ============================== Best regards, Alexey Pechnikov. http://pechnikov.tel/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
