Indexation de texte intégral SQLite

Introduction

Dans ce laboratoire, vous apprendrez à utiliser l'extension FTS5 de SQLite pour effectuer des recherches en texte intégral (full-text searching) efficaces. Vous créerez des tables FTS5, insérerez des données, effectuerez des recherches et optimiserez les requêtes à l'aide de clauses MATCH. Ce laboratoire offre une introduction pratique à l'indexation en texte intégral dans SQLite.

Skills Graph

Créer une base de données SQLite et activer FTS5

Dans cette étape, vous allez créer une base de données SQLite et activer l'extension FTS5. FTS5 est un module qui fournit des capacités de recherche en texte intégral (full-text search).

Tout d'abord, ouvrez votre terminal dans la VM LabEx. Votre chemin d'accès par défaut est /home/labex/project.

1. Créer une base de données SQLite :

   Créez un fichier de base de données nommé books.db et connectez-vous à celui-ci à l'aide de la commande sqlite3 :

   sqlite3 books.db

   Cette commande démarre l'interpréteur (shell) SQLite, où vous pouvez exécuter des commandes SQL.

2. Activer l'extension FTS5 :

   Avant de pouvoir utiliser FTS5, vous devez l'activer. Exécutez la commande SQL suivante :

   CREATE VIRTUAL TABLE book_search USING fts5();

   Si vous voyez une erreur comme Error: no such module: fts5, cela signifie que l'extension FTS5 n'est pas disponible. Dans la plupart des versions modernes de SQLite, FTS5 est inclus par défaut. Si vous rencontrez cette erreur, assurez-vous que votre version de SQLite est à jour. Si la commande s'exécute sans erreur, l'extension FTS5 est activée.

   Cette commande crée une table virtuelle nommée book_search en utilisant le module fts5. Les tables virtuelles sont une fonctionnalité de SQLite qui vous permet d'étendre la base de données avec des fonctionnalités personnalisées.

3. Définir le schéma de la table :

   Maintenant, définissons les colonnes de notre table book_search. Nous inclurons des colonnes pour title (titre), author (auteur) et content (contenu). Exécutez la commande SQL suivante :

   CREATE VIRTUAL TABLE book_search USING fts5(title, author, content);

   Cette commande crée la table book_search avec les colonnes spécifiées. Les colonnes title, author et content seront indexées par FTS5, ce qui vous permettra d'effectuer des recherches en texte intégral sur celles-ci.

4. Configurer le tokenizer (facultatif) :

   Vous pouvez personnaliser la façon dont FTS5 tokenise le texte en spécifiant un tokenizer. Le tokenizer unicode61 offre une bonne prise en charge d'Unicode. Pour l'utiliser et supprimer les signes diacritiques (accents), recréez la table avec la commande suivante :

   DROP TABLE IF EXISTS book_search;
   CREATE VIRTUAL TABLE book_search USING fts5(title, author, content, tokenize="unicode61 remove_diacritics 1");

   Cette commande supprime d'abord la table book_search existante (si elle existe), puis la recrée avec le tokenizer unicode61. L'option remove_diacritics 1 indique à FTS5 de supprimer les signes diacritiques du texte pendant l'indexation.

Insérer des données dans la table FTS5

Dans cette étape, vous allez insérer des données dans la table book_search. Ces données seront utilisées pour la recherche en texte intégral (full-text searching) dans les étapes suivantes.

1. Insérer des données de livres :

   Exécutez les commandes SQL suivantes pour insérer des exemples de données de livres dans la table book_search :

   INSERT INTO book_search (title, author, content) VALUES (
       'The Lord of the Rings',
       'J.R.R. Tolkien',
       'A fantasy epic about hobbits, elves, and the battle against Sauron.'
   );
   
   INSERT INTO book_search (title, author, content) VALUES (
       'Pride and Prejudice',
       'Jane Austen',
       'A classic novel about love, class, and society in 19th-century England.'
   );
   
   INSERT INTO book_search (title, author, content) VALUES (
       'The Hitchhiker''s Guide to the Galaxy',
       'Douglas Adams',
       'A comedic science fiction series following the misadventures of Arthur Dent.'
   );
   
   INSERT INTO book_search (title, author, content) VALUES (
       'To Kill a Mockingbird',
       'Harper Lee',
       'A powerful story about racial injustice in the American South.'
   );
   
   INSERT INTO book_search (title, author, content) VALUES (
       '1984',
       'George Orwell',
       'A dystopian novel about totalitarianism and surveillance.'
   );

   Ces commandes insèrent cinq lignes dans la table book_search, chacune représentant un livre. Les valeurs title (titre), author (auteur) et content (contenu) sont fournies pour chaque livre. Notez l'apostrophe échappée dans 'The Hitchhiker''s Guide to the Galaxy'. Les apostrophes à l'intérieur d'une chaîne de caractères doivent être échappées en les doublant.

2. Vérifier l'insertion des données :

   Pour confirmer que les données ont été ajoutées correctement, exécutez la commande suivante :

   SELECT * FROM book_search;

   Cette commande récupère toutes les lignes et colonnes de la table book_search. Vous devriez voir les données que vous venez d'insérer.

   Résultat attendu :

   The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.
   Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
   The Hitchhiker's Guide to the Galaxy|Douglas Adams|A comedic science fiction series following the misadventures of Arthur Dent.
   To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.
   1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

Effectuer des recherches de base en texte intégral

Dans cette étape, vous allez effectuer des recherches de base en texte intégral (full-text searches) à l'aide de l'opérateur MATCH.

1. Rechercher un seul terme :

   Pour trouver les livres contenant le mot "fantasy", exécutez la commande SQL suivante :

   SELECT * FROM book_search WHERE book_search MATCH 'fantasy';

   Cette commande recherche le terme "fantasy" dans toutes les colonnes de la table book_search. L'opérateur MATCH est utilisé pour effectuer la recherche en texte intégral.

   Résultat attendu :

   The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.

2. Rechercher plusieurs termes :

   Vous pouvez rechercher plusieurs termes en les séparant par des espaces. Pour trouver les livres contenant à la fois "love" et "society", exécutez la commande SQL suivante :

   SELECT * FROM book_search WHERE book_search MATCH 'love society';

   Cette commande recherche les lignes qui contiennent à la fois "love" et "society" dans l'une des colonnes indexées.

   Résultat attendu :

   Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.

3. Rechercher dans une colonne spécifique :

   Pour rechercher dans une colonne spécifique, spécifiez le nom de la colonne avant l'opérateur MATCH. Par exemple, pour trouver les livres avec "Orwell" dans la colonne author (auteur), exécutez la commande SQL suivante :

   SELECT * FROM book_search WHERE author MATCH 'Orwell';

   Cette commande recherche uniquement le terme "Orwell" dans la colonne author.

   Résultat attendu :

   1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

Utiliser les fonctionnalités avancées de MATCH

Dans cette étape, vous explorerez les fonctionnalités avancées de l'opérateur MATCH, notamment les recherches par préfixe et les opérateurs booléens.

1. Recherche par préfixe (Prefix Search) :

   Utilisez le caractère générique * pour effectuer une recherche par préfixe. Pour trouver les livres contenant des mots commençant par "soci", exécutez la commande SQL suivante :

   SELECT * FROM book_search WHERE book_search MATCH 'soci*';

   Cette commande recherche les termes qui commencent par "soci", tels que "society".

   Résultat attendu :

   Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.

2. Opérateurs booléens (Boolean Operators) :

   FTS5 prend en charge les opérateurs booléens tels que AND, OR et NOT pour créer des requêtes de recherche plus complexes.

   • AND : Trouver les documents contenant les deux termes. Le comportement par défaut lorsque les termes sont séparés par des espaces est AND.

     SELECT * FROM book_search WHERE book_search MATCH 'love AND society';

     Ceci est équivalent à SELECT * FROM book_search WHERE book_search MATCH 'love society';

     Résultat attendu :

     Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.

   • OR : Trouver les documents contenant l'un ou l'autre terme.

     SELECT * FROM book_search WHERE book_search MATCH 'love OR injustice';

     Cette commande trouve les livres contenant soit "love", soit "injustice" (ou les deux).

     Résultat attendu :

     Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
     To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.

   • NOT : Exclure les documents contenant un terme spécifique.

     SELECT * FROM book_search WHERE book_search MATCH 'NOT fantasy';

     Cette commande trouve les livres qui ne contiennent pas le mot "fantasy".

     Résultat attendu :

     Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
     The Hitchhiker's Guide to the Galaxy|Douglas Adams|A comedic science fiction series following the misadventures of Arthur Dent.
     To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.
     1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

Résumé

Dans ce labo, vous avez appris à utiliser l'extension FTS5 de SQLite pour la recherche en texte intégral (full-text searching). Vous avez créé une table FTS5, inséré des données, effectué des recherches de base à l'aide de l'opérateur MATCH et exploré des fonctionnalités avancées telles que les recherches par préfixe (prefix searches) et les opérateurs booléens (boolean operators). Ces compétences fournissent une base pour intégrer de puissantes capacités de recherche dans vos applications SQLite.