On Oct 26, 2010, at 2:22 PM, Alexey Pechnikov wrote: >> 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"?..
The sorting happens after the grouping. And it is while processing the GROUP BY clause that SQLite is forced to select an arbitrary record from the user_record table. See here: http://www.sqlite.org/lang_select.html#resultset Third paragraph under the third bullet point. > > 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
