On Apr 16, 2012, at 3:47 PM, Petite Abeille wrote: > > On Apr 16, 2012, at 10:37 PM, Puneet Kishor wrote: > >> 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. > > Although the end date is not strictly speaking necessary, and can be derived > from a previous start date, it make the query more natural: "date between > start and end", as opposed to some other peculiar oddities… > > It also allows to express deletion in one fell swoop: delete a record by > closing its end date. > >> 2. Yes, most constraint mechanisms might be useless or difficult to >> implement, but I do need a PK. > > Well, I suspect you need the equivalent of, say, a "business key". Something > that uniquely identify a record *outside* of its versioning. But such an > identifier is most likely not going to be a primary key, in the traditional > relational constraint sense of it. >
and hence, my original question: given id INTEGER, created_on DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id, created_on) how do I get the effect of `id INTEGER AUTOINCREMENT`. Guess I can't, not in sqlite3, because AUTOINCREMENT only works with the PK invocation. So, I have to use some other manual mechanism. Fwiw, in Pg I can do id SERIAL, created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id, created_on) where `SERIAL` does the right thing by way of setting up the sequences, etc. -- Puneet Kishor _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users