On Apr 16, 2012, at 8:29 PM, Puneet Kishor wrote: > I am trying to create a data versioning system so that a query done at a > particular time can be reproduced identically as to the original query even > if the data have been modified in the interim time.
My 2¢ worth… (1) Proper historization/versioning is not a piece of cake (2) Most constraint mechanisms do not help with it Regarding (1), I would suggest a relatively straightforward setup where all you versioned tables include a date range specifying the point in time a record is valid. This is more conveniently expressed as two fields, along the lines of valid_from and valid_to, so you can then query it with a between clause. Each DML operations need to maintain that date range so it stays logically consistent (e.g. no overlaps, not gaps, no delete, etc). At the end of the day, you should be able to query your data for any point in time consistently: select * from foo join bar on bar.bar_key = foo.bar_key where foo.foo_key = 1 and julianday( ... ) between foo.valid_from and foo.valid_to and julianday( ... ) between bar.valid_from and bar.valid_to Regarding (2), I would suggest to forgo traditional integrity constraint mechanisms (primary, unique, referential, etc) as they simply don't play well with (1). For example, one cannot express a meaningful, and useful, primary, nor unique key on versioned data. Ditto for referential constraints. Which also means you have to re-implement all of the above by yourself. Which is a pain and rather error prone. Got luck either ways. :) _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
