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?

I don't know about the older versions, but FTS5 with an external content table requires an update of the fts table when rows are added, deleted, or changed in the content table so that the indexes are updated. Maybe there are some triggers set up to do that that you're overlooking? — Shawn, Dec 11 '18 at 23:47
It is a good practice to leave a comment what was wrong. I cannot reproduce it **[demo](https://www.db-fiddle.com/f/sCmRdoj2VyvAmc3UXs7B6z/0)** — Lukasz Szozda, Dec 29 '18 at 19:02
@LukaszSzozda, the message was actually an error in a related trigger. Somehow Sqlite doesn't display the name of the trigger so that was making it confusing. — laurent, Dec 29 '18 at 19:30

score 6 · Accepted Answer · answered Dec 12 '18 at 21:27

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;

Making the table and keeping it all in sync by using triggers is a neat solution! Thanks for the hint! — Philipp Doerner, Jun 28 '21 at 17:07

score 0 · Answer 2 · answered May 06 '19 at 07:34

According to sqlite document

To delete entry, either

-- Insert a row with rowid=14 into the fts5 table.
INSERT INTO ft(rowid, a, b, c) VALUES(14, $a, $b, $c);

-- Remove the same row from the fts5 table.
INSERT INTO ft(ft, rowid, a, b, c) VALUES('delete', 14, $a, $b, $c);

or

CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;

To rebuild based on the modified virtual table

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

Do SQLite FTS tables need to be manually populated?

2 Answers2