Hi Alexey, Thank you for the license change and readme. Wednesday, July 15, 2009, 7:16:44 PM, you wrote:
AP> Hello! AP> 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/ AP> I did add README file. AP> >> 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. AP> I did change license to Public Domain same as SQLite core and my other extensions. AP> P.S. Added indexes in new "Versioning" version. AP> ============ README ============= AP> "Versioning" SQLite extension AP> Copyright 2009 Alexey Pechnikov <pechni...@mobigroup.ru> AP> The code is public domain. AP> The extension may be used for table versioning and replication. AP> Functions: AP> versioning_table(SOURCE) - add versioning support for SOURCE table by AP> 1. drop if exists previously created _versioning_SOURCE table AP> 2. add _versioning_SOURCE table to store versions AP> 4. copy current state of SOURCE table AP> 3. create triggers on SOURCE table AP> unversioning_table(SOURCE) - remove versioning support for AP> SOURCE table. Doesn't drop _versioning_SOURCE table but only remove SOURCE triggers! AP> The _versioning_SOURCE table consists all fields of SOURCE table AP> without any checks or constraints and some additional fields AP> _date REAL, _action TEXT, _rowid INTEGER AP> to store date of perform action on SOURCE row, action name ('I' - AP> insert, 'U' - update, 'D' - delete) and original record rowid. AP> ======================== AP> Add versioning example: AP> CREATE TABLE key (name text not null); AP> select versioning_table('key'); AP> .schema AP> CREATE TABLE _undo(sql TEXT, status TEXT); AP> CREATE TABLE _versioning_key(name text, _date REAL, _action TEXT, _rowid INTEGER); AP> CREATE TABLE key (name text not null); AP> CREATE INDEX versioning_key_date_idx on _versioning_key(_date); AP> CREATE INDEX versioning_key_rowid_date_idx on _versioning_key(_rowid,_date); AP> CREATE TRIGGER _versioning_key_d AFTER DELETE ON key BEGIN INSERT AP> INTO _versioning_key (_date, _action, _rowid) values AP> (julianday('now'), 'D', old.rowid);END; AP> CREATE TRIGGER _versioning_key_i AFTER INSERT ON key BEGIN INSERT AP> INTO _versioning_key SELECT *, julianday('now') as _date, 'I' as AP> _action, new.rowid as _rowid FROM key WHERE rowid=new.rowid;END; AP> CREATE TRIGGER _versioning_key_u AFTER UPDATE ON key BEGIN INSERT AP> INTO _versioning_key SELECT *, julianday('now') as _date, 'U' as AP> _action, new.rowid as _rowid FROM key WHERE rowid=new.rowid;END; AP> ======================== AP> Versioning example: AP> insert into key (name) values ('test key 1'); AP> insert into key (name) values ('test key 1'); AP> delete from key; AP> .header on AP> select * from _versioning_key; AP> name|_date|_action|_rowid AP> test key 1|2455027.87582762|I|1 AP> test key 1|2455027.87582772|I|2 AP> |2455027.87709961|D|1 AP> |2455027.87709961|D|2 AP> ======================== AP> Now you can select versions of SOURCE row by rowid: AP> .header on AP> select * from _versioning_key where _rowid=1; AP> name|_date|_action|_rowid AP> test key 1|2455027.87582762|I|1 AP> |2455027.87709961|D|1 AP> ======================== AP> For replication can be selected versions of all records by AP> current time which were changed after previous syncronization (1 hour ago, as example): AP> .header on AP> select * from _versioning_key where _date>julianday('now','-1 hour'); AP> name|_date|_action|_rowid AP> test key 1|2455027.87582762|I|1 AP> test key 1|2455027.87582772|I|2 AP> |2455027.87709961|D|1 AP> |2455027.87709961|D|2 AP> These records may be synced by sql dump or by other ways. AP> Best regards, Alexey Pechnikov. AP> http://pechnikov.tel/ -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
