On 16 Apr 2012, at 6:58pm, Puneet Kishor <[email protected]> wrote:
> I am experimenting with a home-grown versioning system where every > "significant" modification to row would be performed on a copy of the row, > the original being preserved. So, if I have > > CREATE TABLE t ( > id INTEGER, > created_on DATETIME DEFAULT CURRENT_TIMESTAMP, > name TEXT, > is_trivial_update BOOLEAN DEFAULT 0, > PRIMARY KEY (id, created_on) > ); > > today I can have > > 1, 2012-04-16 12:51:00, John, 0 > > and in the coming days I can make it > > 1, 2012-04-16 12:51:00, John, 0 > 1, 2012-04-17 10:00:00, Johnny, 0 > 1, 2012-04-17 10:00:00, Johnnie, 1 > 1, 2012-04-17 22:12:00, John Walker, 0 Have one table which holds just the current data. Use the standard primary key mechanism with that table, allowing it to supply an autoincrementing integer primary key for that table. Have another table which lists all the changes for the first table. The primary key for the second table can also be an autoincrementing integer primary key, but that has nothing to do with one with all the current values in it. The 'id' column of the first table should be a different column of the second table. Use a TRIGGER mechanism so that every INSERT and UPDATE for the first table makes an entry in the second table. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
