On Apr 16, 2012, at 1:08 PM, Simon Slavin wrote: > > On 16 Apr 2012, at 6:58pm, Puneet Kishor <punk.k...@gmail.com> 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. >
Thanks. That is one approach I have considered. I will try it out, but I am less enthusiastic about it as it would involve creating a shadow table for every table in the db. I am planning to try both approaches, evaluate, and choose among them after real experimentation. -- Puneet Kishor _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
