Ricerca full-text in SQLite: tabelle virtuali FTS5 e MATCH

LIKE non regge la crescita

Se hai già cercato del testo in SQLite, probabilmente hai usato LIKE '%word%'. Funziona sulle tabelle piccole e crolla su quelle grandi. Nessun indice può aiutare: SQLite deve scansionare ogni riga, convertirla in minuscolo e cercare la sottostringa. Confini delle parole, ranking, query con più parole e ricerca per prefisso li devi implementare tu.

FTS5 è la risposta integrata. È un tipo di tabella virtuale che mantiene un indice invertito sulle tue colonne di testo, capisce un piccolo linguaggio di query e ordina i risultati con BM25. È incluso in SQLite di default: nessuna estensione da installare.

Creare una tabella FTS5

Crei una tabella FTS5 con CREATE VIRTUAL TABLE ... USING fts5(...), elencando le colonne di testo che vuoi indicizzare:

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';

Tre cose da notare. Le colonne non hanno tipi: FTS5 tratta tutto come testo. L'operatore MATCH si applica al nome della tabella (posts MATCH ...), non a una colonna. E la query non distingue maiuscole e minuscole ed è divisa in token, quindi 'sqlite' trova SQLite in tutte le righe.

Il linguaggio di query di MATCH

MATCH accetta più di una singola parola. La stringa di query ha una sua piccola grammatica:

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';

Cosa fa ciascuna:

• 'fts5 AND prefix': devono comparire entrambi i token (in qualsiasi ordine, in qualsiasi punto della riga).
• '"keep fts"': frase esatta, in quell'ordine.
• 'trig*': ricerca per prefisso, trova trigger, triggers, trigonometry...
• 'index NOT trigger': contiene index, non contiene trigger.

Puoi anche puntare a una sola colonna con column:term, per esempio 'title:sqlite'. La grammatica completa include le parentesi per raggruppare e OR per le alternative: la stessa forma che ti aspetteresti da un motore di ricerca.

Ranking con BM25

Per impostazione predefinita, FTS5 aggiunge a ogni riga una colonna nascosta rank. È il punteggio di rilevanza BM25: numeri più bassi indicano corrispondenze migliori. Ordina in base a quella per avere prima i risultati più rilevanti:

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;

Vuoi dare ad alcune colonne più peso di altre? Chiama bm25() con dei pesi, uno per colonna nell'ordine di dichiarazione:

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;

Vince il primo post, perché sqlite compare in title (peso 10×) invece che solo in body (peso 1×). Scegli i pesi in base a come la tua app vuole davvero ordinare i risultati.

Tenere l'indice sincronizzato

La tabella FTS5 più semplice memorizza una sua copia del testo. Va bene per dati tipo log in cui fai solo inserimenti, ma la maggior parte delle app ha già una tabella reale e vuole che FTS la segua. Lo schema pulito è una tabella FTS a contenuto esterno più tre trigger.

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' dice a FTS5 di non memorizzare il testo: lo recupererà dalla tabella articles quando serve. I trigger riportano le scritture nell'indice FTS. Ora articles è la fonte di verità e articles_fts è solo la struttura di ricerca che le sta accanto.

Lo strano INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) è la sintassi di comando di FTS5 per dire all'indice di rimuovere una riga.

Snippet ed evidenziazione

Di solito i risultati di ricerca vogliono un'anteprima con i termini trovati messi in evidenza. FTS5 ha due funzioni per questo:

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) restituisce il testo completo della colonna con i token trovati racchiusi tra i marcatori.
• snippet(table, column_index, open, close, ellipsis, token_count) restituisce un breve estratto centrato sulla corrispondenza.

Gli indici delle colonne partono da zero, nell'ordine di dichiarazione. Sono i mattoni per il classico effetto "termini trovati in giallo" di cui ha bisogno ogni interfaccia di ricerca.

Insidie da conoscere

Alcune cose che mettono in difficoltà:

• MATCH funziona solo sulle tabelle FTS. Non puoi usare MATCH su una colonna normale. Se ti serve la ricerca su una tabella esistente, usa lo schema a contenuto esterno visto sopra.
• Non dimenticare di ordinare per rank. Senza, FTS5 restituisce le righe nell'ordine di memorizzazione, che non ha nulla a che fare con la rilevanza.
• I tokenizer contano. Il tokenizer predefinito (unicode61) divide sui confini delle parole Unicode e converte in minuscolo. Per lo stemming (run che trova running), usa il tokenizer porter: USING fts5(body, tokenize='porter'). Nota che porter è pensato per l'inglese.
• FTS5 non tollera gli errori di battitura. Fa ricerca per prefisso, non ricerca fuzzy. Se ti serve un comportamento tipo "forse cercavi...", va costruito in uno strato sopra FTS5.
• Le tabelle contentless (content='') sono più piccole ma perdono informazioni. Puoi cercarci dentro ma non recuperare il testo originale, solo il rowid. Utili quando il testo lo memorizzi altrove.

Prossimo passo: le window function

FTS5 copre la ricerca di testo. La prossima pagina tratta un altro tipo di query avanzata: le window function, che ti permettono di calcolare totali progressivi, classifiche e analisi per gruppo senza ridurre le righe ad aggregati.