By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601421 fiddles created (47999 in the last week).
create table Texts
( tid integer not null primary key AUTOINCREMENT
, textval varchar(20) not null unique);
✓
hidden batch(es)
create index x1 on texts (textval);
✓
hidden batch(es)
create table T
( x int not null
, tid int not null references texts (tid)
, primary key (x, tid) );
✓
hidden batch(es)
create view v as
select t.x, texts.textval
from t
join texts
on t.tid = texts.tid
;
✓
hidden batch(es)
CREATE TRIGGER trig1
INSTEAD OF INSERT ON V
BEGIN
-- insert unless textval is already in place
INSERT INTO texts (textval)
VALUES (NEW.textval)
-- ON CONFLICT (textval) DO NOTHING
;
-- lookup tid for textval
--insert into t (x, tid)
--select NEW.x, texts.tid
--from texts where texts.textval = NEW.textval;
END;
✓
hidden batch(es)
INSERT INTO texts (textval)
VALUES ('a')
-- ON CONFLICT (textval) DO NOTHING
;
✓
hidden batch(es)
select * from Texts;
tid
textval
1
a
…
hidden batch(es)
CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
✓
hidden batch(es)
INSERT INTO vocabulary(word) VALUES('jovial')
ON CONFLICT(word) DO UPDATE SET count=count+1;