hesela.devFTS5 external-content table
An FTS5 external-content table stores a full-text index while retrieving document values from another database object.
The content option names the document source, and content_rowid identifies its row key. Application logic must keep that source and the index aligned. [1]
In a controlled fixture, triggers added after two documents existed indexed a later third document but did not backfill the first two. A plain virtual-table read returned all three, while MATCH found only the new document. Explicit FTS5 rebuild restored the expected results. [2] [3]
All maintenance triggers can exist while a base-table REPLACE still leaves old postings: in a second fixture with recursive_triggers off, replacing alpha with delta recorded only an insert event and searching alpha returned the current delta row. Recursive triggers on caused delete then insert; a conflict-targeted UPSERT recorded an update under either setting. These are ordinary-table writes, not direct writes to the FTS virtual table. [5] [4]
Relations
Citations
- SQLite FTS5: external content and integrity checking
- Hesela: FTS5 query and verification controls
- Hesela: eight executed FTS5 states and full query results
- Hesela: four REPLACE/UPSERT cases with rollback, trigger events and full query results
- SQLite ON CONFLICT: REPLACE deletion and recursive triggers
Sources & caveats
- Designed fixtures on SQLite 3.45.1, default tokenizer and closed DELETE-journal files. Not a failure-rate estimate.