SQLite Full-Text Search: טבלאות וירטואליות של FTS5 ו-MATCH

LIKE לא עומד בעומס

אם כבר חיפשתם טקסט ב-SQLite, כנראה השתמשתם ב-LIKE '%word%'. זה עובד בטבלאות קטנות ומתפרק בגדולות. אין אינדקס שיכול לעזור: SQLite צריכה לסרוק כל שורה, להמיר אותה לאותיות קטנות ולבדוק אם תת-המחרוזת מופיעה. גבולות מילים, דירוג, שאילתות מרובות מילים והתאמת קידומות, את כל אלה אתם צריכים לממש בעצמכם.

FTS5 היא התשובה המובנית. זה סוג של טבלה וירטואלית שמתחזקת אינדקס הפוך על עמודות הטקסט שלכם, מבינה שפת שאילתות קטנה ומדרגת תוצאות עם BM25. היא מגיעה עם SQLite כברירת מחדל, בלי הרחבה להתקנה.

יצירת טבלת FTS5

יוצרים טבלת FTS5 עם CREATE VIRTUAL TABLE ... USING fts5(...), ומפרטים את עמודות הטקסט שרוצים לאנדקס:

CREATE VIRTUAL TABLE posts USING fts5(title, body);

INSERT INTO posts (title, body) VALUES
  ('SQLite full-text search', 'FTS5 is fast and built in.'),
  ('Indexes in SQLite', 'B-trees power most lookups in SQLite.'),
  ('Why use SQLite', 'It runs in-process and stores data in one file.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

שלושה דברים ששווה לשים לב אליהם. לעמודות אין טיפוסים: FTS5 מתייחסת לכל דבר כטקסט. האופרטור MATCH מופעל על שם הטבלה (posts MATCH ...), לא על עמודה. והשאילתה לא רגישה לאותיות גדולות וקטנות ומפורקת לטוקנים, כך ש-'sqlite' מוצא את SQLite בכל אחת מהשורות.

שפת השאילתות של MATCH

MATCH מקבל יותר ממילה בודדת. למחרוזת השאילתה יש דקדוק קטן משלה:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Full-text search', 'FTS5 supports phrase and prefix queries.'),
  ('Indexes', 'A B-tree index speeds up equality lookups.'),
  ('Triggers', 'Use triggers to keep FTS in sync with the source table.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

מה כל אחת עושה:

• 'fts5 AND prefix': שני הטוקנים חייבים להופיע (בכל סדר, בכל מקום בשורה).
• '"keep fts"': ביטוי מדויק, בסדר הזה.
• 'trig*': חיפוש קידומת, מתאים ל-trigger, triggers, trigonometry...
• 'index NOT trigger': מכיל את index, לא מכיל את trigger.

אפשר גם לכוון לעמודה בודדת עם column:term, למשל 'title:sqlite'. הדקדוק המלא כולל סוגריים לקיבוץ ו-OR לחלופות, בדיוק הצורה שהייתם מצפים לה ממנוע חיפוש.

דירוג עם BM25

כברירת מחדל, FTS5 מצמידה עמודה נסתרת בשם rank לכל שורה. זה ציון הרלוונטיות של BM25: מספרים נמוכים יותר הם התאמות טובות יותר. מיינו לפיה כדי לקבל קודם את התוצאות הרלוונטיות ביותר:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite tutorial', 'Learn SQLite from scratch.'),
  ('SQLite vs Postgres', 'Comparing SQLite to Postgres for small apps.'),
  ('SQLite SQLite SQLite', 'A post that mentions SQLite many times.');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

רוצים לתת לעמודות מסוימות משקל גבוה יותר מאחרות? קראו ל-bm25() עם משקלות, אחד לכל עמודה לפי סדר ההצהרה:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite indexes', 'A short note about B-trees.'),
  ('Postgres notes', 'Includes a paragraph about sqlite for comparison.');

SELECT title, bm25(posts, 10.0, 1.0) AS score
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY score;

הפוסט הראשון מנצח כי sqlite מופיע ב-title (במשקל 10×) ולא רק ב-body (במשקל 1×). בחרו משקלות שמתאימים לאופן שבו האפליקציה שלכם באמת רוצה לדרג.

שמירה על סנכרון האינדקס

טבלת FTS5 הפשוטה ביותר שומרת עותק משלה של הטקסט. זה בסדר לנתונים בסגנון לוג שרק מוסיפים אליהם, אבל לרוב האפליקציות כבר יש טבלה אמיתית, והן רוצות ש-FTS יעקוב אחריה. התבנית הנקייה היא טבלת FTS עם תוכן חיצוני (external content) ועוד שלושה טריגרים.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, body,
    content='articles',
    content_rowid='id'
);

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER 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;

CREATE TRIGGER 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;

INSERT INTO articles (title, body) VALUES
  ('SQLite triggers', 'How to use AFTER INSERT triggers.'),
  ('FTS5 patterns', 'External content keeps the data in one place.');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' אומר ל-FTS5 לא לשמור את הטקסט בעצמה: היא תביא אותו מהטבלה articles כשצריך. הטריגרים משקפים את הכתיבות לאינדקס ה-FTS. עכשיו articles היא מקור האמת, ו-articles_fts היא רק מבנה החיפוש שלצידה.

ה-INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) שנראה מוזר הוא תחביר הפקודות של FTS5 שאומר לאינדקס להסיר שורה.

קטעי תצוגה והדגשה

תוצאות חיפוש צריכות בדרך כלל תצוגה מקדימה שבה המונחים שנמצאו מודגשים. ל-FTS5 יש שתי פונקציות לזה:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Getting started', 'SQLite is a small, fast, embedded database engine that stores data in a single file on disk.');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite database';

• highlight(table, column_index, open, close) מחזירה את הטקסט המלא של העמודה כשהטוקנים שנמצאו עטופים.
• snippet(table, column_index, open, close, ellipsis, token_count) מחזירה קטע קצר שממורכז סביב ההתאמה.

אינדקסי העמודות מתחילים מאפס, לפי סדר ההצהרה. אלה אבני הבניין להתנהגות של "המונחים שנמצאו בצהוב" שכל ממשק חיפוש צריך.

מלכודות שכדאי להכיר

כמה דברים שתופסים אנשים:

• MATCH עובד רק על טבלאות FTS. אי אפשר להפעיל MATCH על עמודה רגילה. אם צריך חיפוש על טבלה קיימת, השתמשו בתבנית ה-external-content שלמעלה.
• אל תשכחו למיין לפי rank. בלי זה, FTS5 מחזירה שורות לפי סדר האחסון, שאין לו שום קשר לרלוונטיות.
• ה-tokenizer חשוב. ה-tokenizer ברירת המחדל (unicode61) מפצל לפי גבולות מילים של Unicode וממיר לאותיות קטנות. בשביל stemming (ש-run יתאים ל-running), השתמשו ב-tokenizer porter: USING fts5(body, tokenize='porter').
• FTS5 הוא לא מנוע שסולח על שגיאות הקלדה. הוא מבצע התאמת קידומות, לא התאמה מקורבת. אם צריך התנהגות של "האם התכוונתם ל...", זו שכבה מעל FTS5.
• טבלאות contentless (content='') קטנות יותר אבל מאבדות מידע. אפשר לחפש בהן, אבל אי אפשר לשלוף את הטקסט המקורי, רק את ה-rowid. שימושי כשהטקסט שמור במקום אחר.

הצעד הבא: Window Functions

FTS5 מכסה חיפוש טקסט. העמוד הבא עוסק בצורה אחרת של שאילתות מתקדמות: window functions, שמאפשרות לחשב סכומים מצטברים, דירוגים וניתוחים לכל קבוצה בלי לכווץ את השורות לצבירות.