On Mon, Apr 16, 2012 at 8:00 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 17 Apr 2012, at 12:33am, Petite Abeille <petite.abei...@gmail.com> wrote: >> On Apr 17, 2012, at 12:51 AM, Simon Slavin wrote: >>> Now, consider what it takes if you're logging value changes instead of >>> commands issued. Your first problem is figuring out which rows exist. >> >> Why? Each row is time boxed. There is no ambiguities about what exists when. > > You're starting from the point where you know which row you're looking for. > I'm not sure how you knew the 'where foo.foo_key = 1' part of your SELECT. > So in your reconstruction scenario, which question are you answering ? Are > you > > 1) trying to reconstruct the entire database > 2) trying to reconstruct all the data about a particular entity: find the row > for a customer named "ACME INC." > 3) trying to find a number of rows: find all customers who a particular > salesman was managing
When all historical data is mixed with current (and future) data you have to be careful to filter your queries for one point in time, else the results may not be self-consistent (e.g., keys that are supposed to be unique at a point in time may not be). You also can't really rely on UNIQUE constraints/indexes. Instead you have to have application code (not just triggers!) to check -at transaction commit time- that what would have been unique constraints are not violated at *any* point in time. This requires determining all event times implied in a transaction (e.g., creating a row with a not_after value less than infinity creates a future event). You can use temp tables and views to do most of these checks in SQL, but it still requires application code. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
