SQLite FTS5 Full-Text Search Schema & Sync Triggers

Supanote Core Team @supanote · 1 min read

plain (sql) 13 hours ago · 35 lines · 6 views

-- High-Performance SQLite FTS5 External Content Virtual Table
-- Synchronizes search index with the primary 'articles' table

-- 1. Create FTS5 Virtual Table
CREATE VIRTUAL TABLE IF NOT EXISTS articles_fts USING fts5(
  title,
  body,
  content='articles',
  content_rowid='id',
  tokenize='unicode61 remove_diacritics 2'
);

-- 2. After Insert Trigger
CREATE TRIGGER IF NOT EXISTS articles_ai AFTER INSERT ON articles
BEGIN
  INSERT INTO articles_fts(rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;

-- 3. After Delete Trigger
CREATE TRIGGER IF NOT EXISTS articles_ad AFTER DELETE ON articles
BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
END;

-- 4. After Update Trigger
CREATE TRIGGER IF NOT EXISTS articles_au AFTER UPDATE ON articles
BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO articles_fts(rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;

1-- High-Performance SQLite FTS5 External Content Virtual Table

2-- Synchronizes search index with the primary 'articles' table

4-- 1. Create FTS5 Virtual Table

5CREATE VIRTUAL TABLE IF NOT EXISTS articles_fts USING fts5(

6 title,

7 body,

8 content='articles',

9 content_rowid='id',

10 tokenize='unicode61 remove_diacritics 2'

11);

13-- 2. After Insert Trigger

14CREATE TRIGGER IF NOT EXISTS articles_ai AFTER INSERT ON articles

15BEGIN

16 INSERT INTO articles_fts(rowid, title, body)

17 VALUES (new.id, new.title, new.body);

18END;

20-- 3. After Delete Trigger

21CREATE TRIGGER IF NOT EXISTS articles_ad AFTER DELETE ON articles

22BEGIN

23 INSERT INTO articles_fts(articles_fts, rowid, title, body)

24 VALUES ('delete', old.id, old.title, old.body);

25END;

27-- 4. After Update Trigger

28CREATE TRIGGER IF NOT EXISTS articles_au AFTER UPDATE ON articles

29BEGIN

30 INSERT INTO articles_fts(articles_fts, rowid, title, body)

31 VALUES ('delete', old.id, old.title, old.body);

32 INSERT INTO articles_fts(rowid, title, body)

33 VALUES (new.id, new.title, new.body);

34END;

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note