Hello,
I have an FTS5 table using a view as an external content table, kept up to date with triggers. Defined as follows:
<verbatim>
CREATE VIEW item_view AS SELECT item.id, item.title, item.series, (SELECT Group_Concat(creator.name, ', ') FROM creator JOIN item_creator ON item_creator.creator_id = creator.id AND item_creator.item_id = item.id) as creator, publisher.name as publisher, item.year, (SELECT Group_Concat(subject.name, ', ') FROM subject JOIN item_subject ON item_subject.subject_id = subject.id AND item_subject.item_id = item.id) as subject, item.description FROM item LEFT JOIN publisher ON publisher.id = item.publisher_id WHERE item.hidden = FALSE
/* item_view(id,title,series,creator,publisher,year,subject,description) */;
CREATE TRIGGER __item_search_ai INSTEAD OF INSERT ON item_view BEGIN
INSERT INTO item_search(rowid, title, series, creator, publisher, year, subject, description) VALUES(new.id, new.title, new.series, new.creator, new.publisher, new.year, new.subject, new.description);
END;
CREATE TRIGGER __item_search_ad INSTEAD OF DELETE ON item_view BEGIN
INSERT INTO item_search(item_search, rowid, title, series, creator, publisher, year, subject, description) VALUES('delete', old.id, old.title, old.series, old.creator, old.publisher, old.year, old.subject, old.description);
END;
CREATE TRIGGER __item_search_au INSTEAD OF UPDATE ON item_view BEGIN
INSERT INTO item_search(item_search, rowid, title, series, creator, publisher, year, subject, description) VALUES('delete', old.id, old.title, old.series, old.creator, old.publisher, old.year, old.subject, old.description);
INSERT INTO item_search(rowid, title, series, creator, publisher, year, subject, description) VALUES (new.id, new.title, new.series, new.creator, new.publisher, new.year, new.subject, new.description);
END;
CREATE VIRTUAL TABLE "item_search"
USING fts5(title, series, creator, publisher, year, subject, description,
tokenize="unicode61 remove_diacritics 2 tokenchars '-_.'",
content='item_view',
content_rowid='id')
/* item_search(title,series,creator,publisher,year,subject,description) */;
</verbatim>
I've noticed that at least sometimes, I'm not under what conditions, match is not able to find some rows yet I can query them from the FTS5 table by selecting rowid. Issuing a rebuild will fix it but I am not sure why new entries are not always automatically searchable with match.