SQLite External Content FTS requires rebuild every time?

Question

I set up an external content FTS4 virtual table in my app to allow full text search of an existing database. I also set up triggers similar to the documentation, so when my main content table is updated the FTS table gets new entries as well.

CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 BEGIN
  DELETE FROM t3 WHERE docid=old.rowid;
END;
CREATE TRIGGER t2_bd BEFORE DELETE ON t2 BEGIN
  DELETE FROM t3 WHERE docid=old.rowid;
END;

CREATE TRIGGER t2_au AFTER UPDATE ON t2 BEGIN
  INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
END;
CREATE TRIGGER t2_ai AFTER INSERT ON t2 BEGIN
  INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
END;

The problem is that the triggers don't seem to actually update the index. I did a simple select * from the fts_transactions table and I see the new entries with correct text and rowid, but when actually searching they don't show up. To get them to show up I have to do a full rebuild like this:

INSERT INTO fts_transactions(fts_transactions) VALUES('rebuild');

Is this how it is supposed to work? I would have figured that the insert/update/delete into the FTS table would modify the index and not require me to rebuild it every time. After rebuilding the new entries show up just fine.

If that is the case, then is it okay to just add the rebuild command to the triggers? I am just worried that adding a new item will become slow once it has to rebuild the index with a few thousand entries on and older device...it seems that there should be a better way.

Answer 1

Is this how it is supposed to work?

yes, I put this line in each Triger

INSERT INTO fts_transactions(fts_transactions) VALUES('rebuild');