Hello! On Wednesday 15 July 2009 09:56:28 Neville Franks wrote: > Hi Alexey, > Thank you for this extension which could be quite interesting to many > SQLite users. Is there any documentation on this available, possibly > in your new book? I couldn't find any with the source at > http://mobigroup.ru/files/sqlite-ext/
I did add README file. > Also you have used the GNU License which means we cannot use this in > any commercial applications. It also goes against the Public Domain > license used by SQLite itself - see > http://www.sqlite.org/copyright.html It would be great if this could > be changed. I did change license to Public Domain same as SQLite core and my other extensions. P.S. Added indexes in new "Versioning" version. ============ README ============= "Versioning" SQLite extension Copyright 2009 Alexey Pechnikov <pechni...@mobigroup.ru> The code is public domain. The extension may be used for table versioning and replication. Functions: versioning_table(SOURCE) - add versioning support for SOURCE table by 1. drop if exists previously created _versioning_SOURCE table 2. add _versioning_SOURCE table to store versions 4. copy current state of SOURCE table 3. create triggers on SOURCE table unversioning_table(SOURCE) - remove versioning support for SOURCE table. Doesn't drop _versioning_SOURCE table but only remove SOURCE triggers! The _versioning_SOURCE table consists all fields of SOURCE table without any checks or constraints and some additional fields _date REAL, _action TEXT, _rowid INTEGER to store date of perform action on SOURCE row, action name ('I' - insert, 'U' - update, 'D' - delete) and original record rowid. ======================== Add versioning example: CREATE TABLE key (name text not null); select versioning_table('key'); .schema CREATE TABLE _undo(sql TEXT, status TEXT); CREATE TABLE _versioning_key(name text, _date REAL, _action TEXT, _rowid INTEGER); CREATE TABLE key (name text not null); CREATE INDEX versioning_key_date_idx on _versioning_key(_date); CREATE INDEX versioning_key_rowid_date_idx on _versioning_key(_rowid,_date); CREATE TRIGGER _versioning_key_d AFTER DELETE ON key BEGIN INSERT INTO _versioning_key (_date, _action, _rowid) values (julianday('now'), 'D', old.rowid);END; CREATE TRIGGER _versioning_key_i AFTER INSERT ON key BEGIN INSERT INTO _versioning_key SELECT *, julianday('now') as _date, 'I' as _action, new.rowid as _rowid FROM key WHERE rowid=new.rowid;END; CREATE TRIGGER _versioning_key_u AFTER UPDATE ON key BEGIN INSERT INTO _versioning_key SELECT *, julianday('now') as _date, 'U' as _action, new.rowid as _rowid FROM key WHERE rowid=new.rowid;END; ======================== Versioning example: insert into key (name) values ('test key 1'); insert into key (name) values ('test key 1'); delete from key; .header on select * from _versioning_key; name|_date|_action|_rowid test key 1|2455027.87582762|I|1 test key 1|2455027.87582772|I|2 |2455027.87709961|D|1 |2455027.87709961|D|2 ======================== Now you can select versions of SOURCE row by rowid: .header on select * from _versioning_key where _rowid=1; name|_date|_action|_rowid test key 1|2455027.87582762|I|1 |2455027.87709961|D|1 ======================== For replication can be selected versions of all records by current time which were changed after previous syncronization (1 hour ago, as example): .header on select * from _versioning_key where _date>julianday('now','-1 hour'); name|_date|_action|_rowid test key 1|2455027.87582762|I|1 test key 1|2455027.87582772|I|2 |2455027.87709961|D|1 |2455027.87709961|D|2 These records may be synced by sql dump or by other ways. 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
