internal/store/migrations/0036_issue_search.up.sql

 1-- Full-text search over issue and merge request prose. Finding an old
 2-- issue meant listing and scrolling, and the instance-wide `search`
 3-- matched titles only, because a LIKE with a leading wildcard cannot use
 4-- an index (#114).
 5--
 6-- External-content tables: the FTS index stores only the terms and points
 7-- at the row it came from, so the prose is not duplicated. content_rowid
 8-- ties a row to issues.id / merge_requests.id.
 9CREATE VIRTUAL TABLE issue_fts USING fts5(
10    title, body,
11    content = 'issues',
12    content_rowid = 'id',
13    tokenize = 'unicode61'
14);
15CREATE VIRTUAL TABLE mr_fts USING fts5(
16    title, body,
17    content = 'merge_requests',
18    content_rowid = 'id',
19    tokenize = 'unicode61'
20);
21
22-- An external-content table is not maintained for you: every write to the
23-- base table has to be mirrored, and a delete or update must first insert
24-- the old values under the 'delete' command or the index keeps terms for
25-- prose that no longer exists.
26CREATE TRIGGER issues_fts_insert AFTER INSERT ON issues BEGIN
27    INSERT INTO issue_fts (rowid, title, body) VALUES (new.id, new.title, new.body);
28END;
29CREATE TRIGGER issues_fts_delete AFTER DELETE ON issues BEGIN
30    INSERT INTO issue_fts (issue_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
31END;
32CREATE TRIGGER issues_fts_update AFTER UPDATE OF title, body ON issues BEGIN
33    INSERT INTO issue_fts (issue_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
34    INSERT INTO issue_fts (rowid, title, body) VALUES (new.id, new.title, new.body);
35END;
36
37CREATE TRIGGER mrs_fts_insert AFTER INSERT ON merge_requests BEGIN
38    INSERT INTO mr_fts (rowid, title, body) VALUES (new.id, new.title, new.body);
39END;
40CREATE TRIGGER mrs_fts_delete AFTER DELETE ON merge_requests BEGIN
41    INSERT INTO mr_fts (mr_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
42END;
43CREATE TRIGGER mrs_fts_update AFTER UPDATE OF title, body ON merge_requests BEGIN
44    INSERT INTO mr_fts (mr_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
45    INSERT INTO mr_fts (rowid, title, body) VALUES (new.id, new.title, new.body);
46END;
47
48-- Everything already in the database, since the triggers only see writes
49-- from here on.
50INSERT INTO issue_fts (rowid, title, body) SELECT id, title, body FROM issues;
51INSERT INTO mr_fts (rowid, title, body) SELECT id, title, body FROM merge_requests;