Recherche plein texte SQLite : FTS5 et MATCH

LIKE ne passe pas à l'échelle

Si vous avez déjà cherché du texte dans SQLite, vous avez sans doute dégainé un LIKE '%mot%'. Ça tient la route sur de petites tables, mais ça s'effondre dès que ça grossit. Aucun index ne peut aider : SQLite doit parcourir chaque ligne, la mettre en minuscules, puis vérifier la sous-chaîne. Et c'est à vous de gérer les frontières de mots, le classement des résultats, les requêtes multi-mots ou la recherche par préfixe.

FTS5 est la réponse intégrée à ce problème. C'est un type de table virtuelle qui maintient un index inversé sur vos colonnes textuelles, comprend un petit langage de requête et classe les résultats avec BM25. Et bonne nouvelle : la recherche plein texte SQLite est livrée par défaut, sans extension à installer.

Créer une table virtuelle FTS5

Pour créer une table FTS5, on utilise CREATE VIRTUAL TABLE ... USING fts5(...) en listant les colonnes textuelles à indexer :

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

INSERT INTO posts (title, body) VALUES
  ('Recherche en texte intégral SQLite', 'FTS5 est rapide et intégré.'),
  ('Les index dans SQLite', 'Les arbres B alimentent la plupart des recherches dans SQLite.'),
  ('Pourquoi utiliser SQLite', 'Il s''exécute dans le processus et stocke les données dans un seul fichier.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

Trois petits détails méritent qu'on s'y attarde. D'abord, les colonnes n'ont pas de type — pour FTS5, tout est du texte. Ensuite, l'opérateur MATCH s'applique au nom de la table (posts MATCH ...), pas à une colonne en particulier. Enfin, la requête est insensible à la casse et passe par un tokenizer : 'sqlite' retrouve donc SQLite dans n'importe laquelle des lignes.

Le langage de requête MATCH

MATCH ne se limite pas à un seul mot. La chaîne de recherche dispose de sa propre petite grammaire :

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Recherche en texte intégral', 'FTS5 prend en charge les requêtes par phrase et par préfixe.'),
  ('Index', 'Un index B-tree accélère les recherches par égalité.'),
  ('Déclencheurs', 'Utilisez des déclencheurs pour garder FTS synchronisé avec la table source.');

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

Ce que fait chacune :

• 'fts5 AND prefix' — les deux termes doivent apparaître (peu importe l'ordre, n'importe où dans la ligne).
• '"keep fts"' — phrase exacte, dans cet ordre précis.
• 'trig*' — recherche par préfixe, qui matche trigger, triggers, trigonometry...
• 'index NOT trigger' — contient index, mais pas trigger.

Vous pouvez aussi cibler une seule colonne avec column:term, par exemple 'title:sqlite'. La grammaire complète gère les parenthèses pour grouper les expressions et le OR pour les alternatives — bref, ce à quoi on s'attend d'un moteur de recherche.

Classement des résultats avec BM25

Par défaut, FTS5 ajoute une colonne cachée rank à chaque ligne. C'est le score de pertinence BM25 — plus la valeur est basse, meilleur est le match. Trie sur cette colonne pour faire remonter les résultats les plus pertinents :

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Tutoriel SQLite', 'Apprenez SQLite à partir de zéro.'),
  ('SQLite vs Postgres', 'Comparaison de SQLite et Postgres pour les petites applications.'),
  ('SQLite SQLite SQLite', 'Un article qui mentionne SQLite à de nombreuses reprises.');

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

Pour donner plus de poids à certaines colonnes qu'à d'autres, il suffit de passer des poids à bm25() — un par colonne, dans l'ordre de déclaration :

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Index SQLite', 'Une courte note sur les arbres B.'),
  ('Notes Postgres', 'Inclut un paragraphe sur sqlite à titre de comparaison.');

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

Le premier post l'emporte parce que sqlite apparaît dans title (pondéré 10×) au lieu de juste body (pondéré 1×). Choisissez des poids qui collent à la façon dont votre appli veut vraiment classer les résultats.

Garder l'index synchronisé

La table FTS5 la plus basique stocke sa propre copie du texte. Ça passe très bien pour des données type logs où vous ne faites que des insertions, mais la plupart des applis ont déjà une vraie table et veulent que FTS la suive automatiquement. La bonne approche, c'est une table FTS en external content accompagnée de trois triggers.

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
  ('Déclencheurs SQLite', 'Comment utiliser les déclencheurs AFTER INSERT.'),
  ('Modèles FTS5', 'Le contenu externe conserve les données à un seul endroit.');

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

content='articles' indique à FTS5 de ne pas stocker le texte lui-même — il ira le chercher dans la table articles quand ce sera nécessaire. Les triggers, eux, répercutent chaque écriture dans l'index FTS. Du coup, articles devient la source de vérité, et articles_fts n'est plus qu'une structure de recherche posée à côté.

La syntaxe un peu déroutante INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) correspond en fait à la commande propre à FTS5 pour demander à l'index de supprimer une ligne.

Extraits et mise en évidence avec snippet et highlight

Sur une page de résultats, on veut généralement afficher un aperçu avec les termes recherchés mis en valeur. FTS5 propose deux fonctions taillées pour ça :

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Pour bien démarrer', 'SQLite est un moteur de base de données embarqué, petit et rapide, qui stocke les données dans un seul fichier sur le disque.');

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) renvoie le texte complet de la colonne avec les termes correspondants encadrés.
• snippet(table, column_index, open, close, ellipsis, token_count) renvoie un court extrait centré sur la correspondance.

Les index de colonnes commencent à zéro, dans l'ordre de déclaration. Ce sont les briques de base du fameux « termes recherchés surlignés en jaune » qu'on attend de toute UI de recherche.

Quelques pièges à connaître

Voici ce qui fait trébucher la plupart des gens :

• MATCH ne fonctionne que sur les tables FTS. Impossible de faire un MATCH sur une colonne classique. Si vous voulez indexer une table existante, utilise le pattern external-content vu plus haut.
• N'oublie pas de trier par rank. Sans ça, FTS5 te renvoie les lignes dans l'ordre de stockage, qui n'a rien à voir avec la pertinence.
• Le choix du tokenizer compte. Le tokenizer par défaut (unicode61) découpe sur les frontières de mots Unicode et passe tout en minuscules. Pour le stemming (run matche running), prends le tokenizer porter : USING fts5(body, tokenize='porter').
• FTS5 ne tolère pas les fautes de frappe. Il fait de la recherche par préfixe, pas de la recherche floue. Si vous voulez du « vouliez-vous dire… », c'est une couche à construire par-dessus FTS5.
• Les tables contentless (content='') sont plus légères, mais avec perte. Vous pouvez y faire des recherches, mais impossible de récupérer le texte original — seulement le rowid. Pratique quand le texte est stocké ailleurs.

La suite : les window functions

FTS5 couvre la recherche textuelle. La page suivante aborde un tout autre style de requête avancée : les window functions, qui permettent de calculer des cumuls, des classements et des analyses par groupe sans écraser vos lignes dans des agrégats.