Class SqliteFts5

Generate DDL to create the full-text search index.

Run it with exec(), which runs a script of several statements. On PostgreSQL the script holds DO blocks with semicolons inside them, so a caller that splits it on ; itself would cut them.

Generate a WHERE clause fragment for full-text matching. SQLite: memory_traces_fts MATCH ? Postgres: _tsv @@ websearch_to_tsquery('english', $1), in the index's own language

A rank value, in each engine's own direction. SQLite FTS5: memory_traces_fts.rank (lower is better) SQLite FTS4: length(offsets(memory_traces_fts)) (higher is better) Postgres: ts_rank(_tsv, websearch_to_tsquery('english', $1)) (higher is better) Use relevanceOrder to order the same way on every engine.

An ORDER BY fragment that puts the best match first on every engine. SQLite FTS5: memory_traces_fts.rank SQLite FTS4: length(offsets(memory_traces_fts)) DESC Postgres: ts_rank(_tsv, websearch_to_tsquery('english', $1)) DESC

Generate the rebuild/reindex command. SQLite: INSERT INTO fts_table(fts_table) VALUES('rebuild') Postgres: UPDATE content_table SET _tsv = to_tsvector('english', col1 || ' ' || col2)

Generate an INSERT to sync external-content FTS after a row insert. SQLite: INSERT INTO fts_table (rowid, col1, col2) VALUES (expr, ?, ?) Postgres: UPDATE content_table SET _tsv = to_tsvector(...) WHERE ...

Sanitize natural-language input into a safe search query. Every engine: the words, quoted, joined with OR, with the operators of the query syntax stripped, so it matches the same rows everywhere.

Generate a SELECT joining the FTS index to the content table. This handles the structural difference between FTS5 (separate virtual table joined via rowid) and Postgres (tsvector column on the content table itself).