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
