Recherche en texte intégral avec SQLite (Full-text Search)

La recherche en texte intégral est une fonctionnalité puissante qui permet de rechercher efficacement du contenu textuel dans une base de données. Contrairement aux requêtes LIKE traditionnelles, la recherche en texte intégral est optimisée pour trouver rapidement des mots ou des phrases dans de grandes quantités de texte. Dans ce tutoriel, vous apprendrez à utiliser le module FTS5 de SQLite pour implémenter cette fonctionnalité.

Introduction à la recherche en texte intégral dans SQLite

SQLite propose la recherche en texte intégral grâce à un module de table virtuelle appelé FTS5. Une table virtuelle est une extension personnalisée de SQLite qui ressemble à une table normale, mais dont les données sont gérées par un code personnalisé plutôt que d’être stockées directement dans le fichier de base de données.

Création d’une table FTS5

Pour utiliser la recherche en texte intégral, nous devons d’abord créer une table virtuelle à l’aide du module FTS5. La syntaxe de base est la suivante :

CREATE VIRTUAL TABLE nom_table
USING FTS5(colonne1, colonne2...);

Notez que, contrairement aux tables normales, vous ne pouvez pas ajouter de types, de contraintes ou de déclaration PRIMARY KEY dans l’instruction CREATE VIRTUAL TABLE pour une table FTS5. Si vous le faites, SQLite générera une erreur.

Comme pour la création d’une table normale sans spécifier de clé primaire, SQLite ajoute une colonne implicite rowid à la table FTS5.

Exemple de création d’une table FTS5

Créons une table FTS5 nommée posts avec deux colonnes : title et body.

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

Ensuite, nous pouvons insérer des données dans cette table comme nous le ferions pour une table normale :

INSERT INTO posts(title, body)
VALUES('Apprendre SQLite FTS5', 'Ce tutoriel vous apprend à effectuer une recherche en texte intégral dans SQLite en utilisant FTS5'),
('Recherche en texte intégral avancée', 'Présente des techniques avancées de recherche en texte intégral dans SQLite'),
('Tutoriel SQLite', 'Vous aide à apprendre SQLite rapidement et efficacement');

Effectuer des requêtes avec la recherche en texte intégral

Il existe trois façons d’exécuter une requête en texte intégral sur une table FTS5 :

1. Utiliser l’opérateur MATCH

La première méthode consiste à utiliser l’opérateur MATCH dans la clause WHERE de l’instruction SELECT :

SELECT *
FROM posts
WHERE posts MATCH 'fts5';

2. Utiliser l’opérateur égal (=)

La deuxième méthode utilise l’opérateur égal (=) dans la clause WHERE :

SELECT *
FROM posts
WHERE posts = 'fts5';

3. Utiliser la syntaxe de fonction à valeur de table

La troisième méthode utilise une syntaxe de fonction à valeur de table, où le terme de recherche est utilisé comme premier argument de table :

SELECT *
FROM posts('fts5');

Par défaut, FTS5 est insensible à la casse. Il traite les termes fts5, FTS5 et Fts5 de la même manière.

Tri des résultats par pertinence

Pour trier les résultats de recherche du plus au moins pertinent, vous pouvez utiliser la clause ORDER BY avec le classement (rank) :

SELECT *
FROM posts
WHERE posts MATCH 'texte'
ORDER BY rank;

Syntaxe de requête en texte intégral

Une requête en texte intégral est composée de phrases, où chaque phrase est une liste ordonnée d’un ou plusieurs jetons (tokens). Vous pouvez utiliser l’opérateur ”+” pour concaténer deux phrases :

"apprendre SQLite"
"apprendre + SQLite"

FTS5 détermine si un document correspond à une phrase si le document contient au moins une sous-séquence de jetons qui correspond à la séquence de jetons utilisée pour construire la phrase.

Recherches par préfixe

Vous pouvez utiliser l’astérisque (*) comme jeton de préfixe. Lorsqu’une phrase contient un astérisque, elle correspondra à tout document contenant un jeton qui commence par cette phrase. Par exemple, recherch* correspondra à recherche, rechercher, recherches, etc.

SELECT *
FROM posts
WHERE posts = 'recherch*';

Opérateurs booléens

Vous pouvez utiliser les opérateurs booléens comme NOT, OR ou AND pour combiner des requêtes :

q1 AND q2 : correspond si les deux requêtes q1 et q2 correspondent.
q1 OR q2 : correspond si l’une des requêtes q1 ou q2 correspond.
q1 NOT q2 : correspond si la requête q1 correspond et q2 ne correspond pas.

Exemple avec l’opérateur NOT

Pour obtenir les documents qui correspondent au terme apprendre mais pas au terme FTS5 :

SELECT *
FROM posts
WHERE posts MATCH 'apprendre NOT texte';

Exemple avec l’opérateur OR

Pour rechercher des documents qui correspondent soit au terme apprendre, soit au terme texte :

SELECT *
FROM posts
WHERE posts MATCH 'apprendre OR texte';

Exemple avec l’opérateur AND

Pour trouver les documents qui correspondent à la fois à sqlite et à recherche :

SELECT *
FROM posts
WHERE posts MATCH 'sqlite AND recherche';

Utilisation de parenthèses pour grouper les expressions

Pour modifier la priorité des opérateurs, vous pouvez utiliser des parenthèses pour grouper les expressions :

SELECT *
FROM posts
WHERE posts MATCH 'recherche AND (sqlite OR aide)';

Cette requête trouvera les documents qui contiennent le terme recherche et soit sqlite soit aide.

Fonctions auxiliaires intégrées

SQLite fournit trois fonctions auxiliaires intégrées qui peuvent être utilisées dans les requêtes en texte intégral sur une table FTS5 :

La fonction bm25() renvoie une valeur qui représente la précision de la correspondance actuelle, une valeur plus faible signifiant une meilleure correspondance.
La fonction highlight() renvoie une copie du texte avec les termes de recherche entourés d’un balisage spécifié (par exemple, <b>terme de recherche</b>).
La fonction snippet() sélectionne un court fragment de texte afin de maximiser le nombre de termes de recherche qu’il contient.

Exemple d’utilisation de la fonction highlight()

SELECT highlight(posts, 0, '<b>', '</b>') title,
       highlight(posts, 1, '<b>', '</b>') body
FROM posts
WHERE posts MATCH 'SQLite'
ORDER BY rank;

Cette requête affichera les titres et les corps des posts avec les occurrences de “SQLite” en gras.