> 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than > `created_on <= :provided_date`?
What if there are several versions created before your provided_date? Not all queries will allow to add `order by created_on desc limit 1`. Pavel On Mon, Apr 16, 2012 at 4:37 PM, Puneet Kishor <[email protected]> wrote: > > 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 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
