On Apr 16, 2012, at 12:32 PM, Igor Tandetnik wrote:
> On 4/16/2012 12:51 PM, Mr. Puneet Kishor wrote:
>>
>> On Apr 16, 2012, at 11:47 AM, Patrik Nilsson wrote:
>>
>>> You can use:
>>>
>>> create table t ( id integer primary key autoincrement, created_on
>>> DATETIME DEFAULT CURRENT_TIMESTAMP )
>>>
>>
>> No, the above will create a PK on only the 'id' column. I want a composite
>> PK with 'id' and 'created_on' columns
>
> Why? What purpose do you believe a composite key would serve, that would not
> be served equally well with a primary key on id column alone?
>
I am experimenting with a home-grown versioning system where every
"significant" modification to row would be performed on a copy of the row, the
original being preserved. So, if I have
CREATE TABLE t (
id INTEGER,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
name TEXT,
is_trivial_update BOOLEAN DEFAULT 0,
PRIMARY KEY (id, created_on)
);
today I can have
1, 2012-04-16 12:51:00, John, 0
and in the coming days I can make it
1, 2012-04-16 12:51:00, John, 0
1, 2012-04-17 10:00:00, Johnny, 0
1, 2012-04-17 10:00:00, Johnnie, 1
1, 2012-04-17 22:12:00, John Walker, 0
Then, I can get the value of id 1 on any given datetime with something like
SELECT name, created_on
FROM t
WHERE
id = 1 AND
is_trivial_update = 0 AND
created_on <= '2012-04-17 09:00:00'
ORDER DESC
LIMIT 1;
which would yield
John, 2012-04-16 12:51:00
Any other suggestions to achieve a similar functionality would be welcome.
--
Puneet Kishor
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
