On Apr 16, 2012, at 2:45 PM, Petite Abeille wrote: > > 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. >
Thanks for your wise words. I am not at all under any illusion that this is going to be easy, but it is worthy of an honest try. Two reactions -- 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than `created_on <= :provided_date`? The latter requires storing only a single date value for every row. Although, after some studying of the Pg timetravel docs, seems like they too use a start and stop date. 2. Yes, most constraint mechanisms might be useless or difficult to implement, but I do need a PK. -- Puneet Kishor _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
