Do SQLite FTS tables need to be manually populated?

Question

The documentation for SQLite FTS implies that FTS tables should be populated and updated using INSERT, UPDATE, DELETE, etc.

That's what I was doing - adding rows, deleting them, etc., but recently I've noticed that as soon as I create the FTS table, it is automatically populated using the data from the source. I create it this way:

CREATE VIRTUAL TABLE notes_fts USING fts4(content="notes", notindexed="id", id, title, body)

If I add a row to the "notes" table, it is also automatically added to notes_fts. I guess that's what virtual tables are.

But then, why is there a chapter about populating FTS tables? What would even be the point since for example if I delete a row, it will come back if it's still in the source table.

Any idea about this? Do FTS actually need to be populated?

Answer 1

After further reading I found that the FTS table indeed need to be manually kept in sync with the content table. When running the CREATE VIRTUAL TABLE call, the FTS table is automatically populated but after that deletions, insertions and updates have to be done manually.

In my case I've done it using the following triggers:

CREATE VIRTUAL TABLE notes_fts USING fts4(content="notes", notindexed="id", id, title, body

CREATE TRIGGER notes_fts_before_update BEFORE UPDATE ON notes BEGIN
    DELETE FROM notes_fts WHERE docid=old.rowid;
END

CREATE TRIGGER notes_fts_before_delete BEFORE DELETE ON notes BEGIN
    DELETE FROM notes_fts WHERE docid=old.rowid;
END

CREATE TRIGGER notes_after_update AFTER UPDATE ON notes BEGIN
    INSERT INTO notes_fts(docid, id, title, body) SELECT rowid, id, title, body FROM notes WHERE is_conflict = 0 AND encryption_applied = 0 AND new.rowid = notes.rowid;
END

CREATE TRIGGER notes_after_insert AFTER INSERT ON notes BEGIN
    INSERT INTO notes_fts(docid, id, title, body) SELECT rowid, id, title, body FROM notes WHERE is_conflict = 0 AND encryption_applied = 0 AND new.rowid = notes.rowid;
END;