> But view_user statement makes no attempt to select the last version. It
picks some arbitrary random version. You might want to consider something
like this:
Why you wrote about "some arbitrary random version" when we have sorting by
"ts"?..
CREATE TABLE user
(
id INTEGER PRIMARY KEY
);
CREATE TABLE user_record
(
record_id INTEGER PRIMARY KEY,
record_version INTEGER,
ts INTEGER NOT NULL DEFAULT (strftime('%s','now')),
user_id INTEGER NOT NULL,
name TEXT,
FOREIGN KEY(user_id) REFERENCES user
);
CREATE VIEW view_user AS
SELECT user.id,user_record.*
FROM user, user_record
WHERE user.id=user_record.user_id
GROUP BY user.id
ORDER BY ts ASC;
This view returns last by "ts" row for each unique "user_id". There is used
the SQLite hint with "group by" (non-grouped values returns too).
And conflict with equal "ts" can be resolved by trigger as:
CREATE TRIGGER view_user_update instead of update on view_user
begin
...
SELECT RAISE(ABORT, 'User wait 1 second.')
WHERE EXISTS(select 1 from user_record where user_id=OLD.user_id and
ts=strftime('%s','now'));
...
end;
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
