On Wed, Feb 9, 2011 at 2:00 PM, Nico Williams <n...@cryptonector.com> wrote: > This way an insert into t requires one lookup and update in t_idx and > one lookup and update in the table's implied rowid index. I.e., twice > the work of an insert without this additional index and trigger.
Also, you get double the DB size this way. Using Jim's suggestion you get to avoid those problems: sqlite> create table t (nm integer primary key); sqlite> create view tv as select nm >> 30 as n, (nm & (1 << 30)) >> 30 as m from t; sqlite> create trigger tv_ins instead of insert on tv begin ...> select case when typeof(NEW.n) != 'integer' then raise(fail, 'n is not an integer') end; ...> select case when typeof(NEW.m) != 'integer' then raise(fail, 'm is not an integer') end; ...> select case when NEW.n > (1 << 30) then raise(fail, 'n is too large') end; ...> select case when NEW.m > (1 << 30) then raise(fail, 'm is too large') end; ...> select case when NEW.n < 0 then raise(fail, 'n must be non-negative') end; ...> select case when NEW.m < 0 then raise(fail, 'm must be non-negative') end; ...> select case (select count(*) from t where nm = NEW.n << 30 | NEW.m) when 1 then raise(fail, 'This relation already exists!') end; ...> insert into t select NEW.m << 30 | NEW.n; ...> end; sqlite> insert into tv (n, m) values (1, 2); sqlite> insert into tv (n, m) values (1, 2); Error: PRIMARY KEY must be unique sqlite> insert into tv (n, m) values (3, 1); sqlite> insert into tv (n, m) values (3, 2); sqlite> insert into tv (n, m) values (1, 3); Error: This relation already exists! sqlite> This still requires two rowid index lookups for every insert, but only one update. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
