max / balanced_breakfast

1.4 KB · 35 lines History Blame Raw

1	-- Feature: Full-text search
2	-- Full-text search index using FTS5 in external content mode.
3	-- No data duplication: the FTS index references feed_items via rowid.
4	CREATE VIRTUAL TABLE IF NOT EXISTS feed_items_fts USING fts5(
5	title,
6	body,
7	bite_text,
8	content='feed_items',
9	content_rowid='rowid'
10	);
11
12	-- Populate FTS from existing data
13	INSERT INTO feed_items_fts(rowid, title, body, bite_text)
14	SELECT rowid, COALESCE(title, ''), COALESCE(body, ''), bite_text FROM feed_items;
15
16	-- Keep FTS in sync: INSERT
17	CREATE TRIGGER feed_items_fts_insert AFTER INSERT ON feed_items BEGIN
18	INSERT INTO feed_items_fts(rowid, title, body, bite_text)
19	VALUES (NEW.rowid, COALESCE(NEW.title, ''), COALESCE(NEW.body, ''), NEW.bite_text);
20	END;
21
22	-- Keep FTS in sync: UPDATE
23	CREATE TRIGGER feed_items_fts_update AFTER UPDATE ON feed_items BEGIN
24	INSERT INTO feed_items_fts(feed_items_fts, rowid, title, body, bite_text)
25	VALUES ('delete', OLD.rowid, COALESCE(OLD.title, ''), COALESCE(OLD.body, ''), OLD.bite_text);
26	INSERT INTO feed_items_fts(rowid, title, body, bite_text)
27	VALUES (NEW.rowid, COALESCE(NEW.title, ''), COALESCE(NEW.body, ''), NEW.bite_text);
28	END;
29
30	-- Keep FTS in sync: DELETE
31	CREATE TRIGGER feed_items_fts_delete AFTER DELETE ON feed_items BEGIN
32	INSERT INTO feed_items_fts(feed_items_fts, rowid, title, body, bite_text)
33	VALUES ('delete', OLD.rowid, COALESCE(OLD.title, ''), COALESCE(OLD.body, ''), OLD.bite_text);
34	END;
35