pyr0ball
dd09aa21f4
Migration 015 did a one-time rebuild of recipes_fts at creation time but omitted triggers, so rows inserted after that point were invisible to MATCH queries. Adds AFTER INSERT/UPDATE/DELETE triggers to 015 (fresh DBs / tests) and migration 016 to backfill them on existing databases. Fixes 3 failing tests: test_search_recipes_by_ingredient_names, test_level1_returns_ranked_suggestions, test_level2_returns_swap_candidates.
38 lines
1.4 KiB
SQL
38 lines
1.4 KiB
SQL
-- Migration 015: FTS5 inverted index for recipe ingredient lookup.
|
|
--
|
|
-- Content table backed by `recipes` — stores only the inverted index, no text duplication.
|
|
-- MATCH queries replace O(N) LIKE scans with O(log N) token lookups.
|
|
--
|
|
-- One-time rebuild cost on 3.2M rows: ~15-30 seconds at startup.
|
|
-- Subsequent startups skip this migration entirely.
|
|
|
|
CREATE VIRTUAL TABLE IF NOT EXISTS recipes_fts USING fts5(
|
|
ingredient_names,
|
|
content=recipes,
|
|
content_rowid=id,
|
|
tokenize="unicode61"
|
|
);
|
|
|
|
INSERT INTO recipes_fts(recipes_fts) VALUES('rebuild');
|
|
|
|
-- Triggers to keep the FTS index in sync with the recipes table.
|
|
-- Without these, rows inserted after the initial rebuild are invisible to FTS queries.
|
|
CREATE TRIGGER IF NOT EXISTS recipes_fts_ai
|
|
AFTER INSERT ON recipes BEGIN
|
|
INSERT INTO recipes_fts(rowid, ingredient_names)
|
|
VALUES (new.id, new.ingredient_names);
|
|
END;
|
|
|
|
CREATE TRIGGER IF NOT EXISTS recipes_fts_ad
|
|
AFTER DELETE ON recipes BEGIN
|
|
INSERT INTO recipes_fts(recipes_fts, rowid, ingredient_names)
|
|
VALUES ('delete', old.id, old.ingredient_names);
|
|
END;
|
|
|
|
CREATE TRIGGER IF NOT EXISTS recipes_fts_au
|
|
AFTER UPDATE ON recipes BEGIN
|
|
INSERT INTO recipes_fts(recipes_fts, rowid, ingredient_names)
|
|
VALUES ('delete', old.id, old.ingredient_names);
|
|
INSERT INTO recipes_fts(rowid, ingredient_names)
|
|
VALUES (new.id, new.ingredient_names);
|
|
END;
|