create table Texts ( tid integer not null primary key AUTOINCREMENT , textval varchar(20) not null unique);

create index x1 on texts (textval);

create table T ( x int not null , tid int not null references texts (tid) , primary key (x, tid) );

create view v as select t.x, texts.textval from t join texts on t.tid = texts.tid ;

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;

INSERT INTO texts (textval) VALUES ('a') -- ON CONFLICT (textval) DO NOTHING ;

select * from Texts;

CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);

INSERT INTO vocabulary(word) VALUES('jovial') ON CONFLICT(word) DO UPDATE SET count=count+1;