I had meant FTS external content in any previous mention of 'contentless' save one time I had mentioned using such in place of my current external content.
I think I may understand now. The triggers do automatically run from what I can tell, as they have, but INSERT INTO an FTS external content table is somehow not the same as inserting into a normal FTS. The rows are there but the indices are not built. Maybe such is in the docs and I missed it.
Maybe it is possible to achieve what I can from just correctly defined triggers? I've tried this, and so far in a few minutes of testing, it works. The idea, if correct is, that on changes to the view, the trigger takes the insert, delete, or update from the view itself and then re-inserts into or deletes such values from the view.
CREATE TRIGGER __item_search_ai INSTEAD OF INSERT ON item_view BEGIN
INSERT INTO item_view(id, 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 OF title, series, creator, publisher, year, subject, description 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_view(id, 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;
If such is correct, it sure has been as they say a headache getting it. In the process of learning more SQL and SQLite than I had previously known – joins, subqueries, views, triggers, FTS, etc., with enough effort indeed one could piece such together but missing the key parts from the docs that one could miss in trying to learn the subjects overall, I didn't figure it out. There isn't much info nor examples of others using external content, so it has been a struggle. It seems correct, but maybe it's not. I'll continue more tests; yet so far it seems ok.