Полнотекстовое индексирование SQLite

Введение

В этой лабораторной работе вы узнаете, как использовать расширение FTS5 (Full-Text Search 5) в SQLite для эффективного полнотекстового поиска. Вы создадите таблицы FTS5, вставите данные, выполните поиск и оптимизируете запросы с помощью предложений MATCH. Эта лабораторная работа представляет собой практическое введение в полнотекстовое индексирование в SQLite.

Skills Graph

Создание базы данных SQLite и включение FTS5

В этом шаге вы создадите базу данных SQLite и включите расширение FTS5 (Full-Text Search 5). FTS5 — это модуль, предоставляющий возможности полнотекстового поиска.

Сначала откройте свой терминал в LabEx VM (виртуальной машине). Ваш путь по умолчанию — /home/labex/project.

1. Создайте базу данных SQLite:

   Создайте файл базы данных с именем books.db и подключитесь к нему с помощью команды sqlite3:

   sqlite3 books.db

   Эта команда запускает оболочку SQLite, где вы можете выполнять SQL-команды.

2. Включите расширение FTS5:

   Прежде чем вы сможете использовать FTS5, вам необходимо его включить. Выполните следующую SQL-команду:

   CREATE VIRTUAL TABLE book_search USING fts5();

   Если вы видите ошибку, например Error: no such module: fts5, это означает, что расширение FTS5 недоступно. В большинстве современных версий SQLite FTS5 включен по умолчанию. Если вы столкнулись с этой ошибкой, убедитесь, что ваша версия SQLite является актуальной. Если команда выполняется без ошибок, расширение FTS5 включено.

   Эта команда создает виртуальную таблицу с именем book_search с использованием модуля fts5. Виртуальные таблицы — это функция SQLite, которая позволяет расширять базу данных пользовательскими функциями.

3. Определите схему таблицы:

   Теперь давайте определим столбцы для нашей таблицы book_search. Мы включим столбцы для title (название), author (автор) и content (содержание). Выполните следующую SQL-команду:

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

   Эта команда создает таблицу book_search с указанными столбцами. Столбцы title, author и content будут проиндексированы FTS5, что позволит вам выполнять полнотекстовый поиск по ним.

4. Настройте токенизатор (необязательно):

   Вы можете настроить способ, которым FTS5 разбивает текст на токены, указав токенизатор (tokenizer). Токенизатор unicode61 обеспечивает хорошую поддержку Unicode. Чтобы использовать его и удалить диакритические знаки (ударения), воссоздайте таблицу с помощью следующей команды:

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

   Эта команда сначала удаляет существующую таблицу book_search (если она существует), а затем воссоздает ее с помощью токенизатора unicode61. Опция remove_diacritics 1 указывает FTS5 удалять диакритические знаки из текста во время индексации.

Вставка данных в таблицу FTS5

В этом шаге вы вставите данные в таблицу book_search. Эти данные будут использоваться для полнотекстового поиска на последующих шагах.

1. Вставка данных о книгах:

   Выполните следующие SQL-команды, чтобы вставить примеры данных о книгах в таблицу 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.'
   );

   Эти команды вставляют пять строк в таблицу book_search, каждая из которых представляет книгу. Для каждой книги указаны значения title (название), author (автор) и content (содержание). Обратите внимание на экранированную одинарную кавычку в 'The Hitchhiker''s Guide to the Galaxy'. Одинарные кавычки внутри строки должны быть экранированы путем их удвоения.

2. Проверка вставки данных:

   Чтобы убедиться, что данные добавлены правильно, выполните следующую команду:

   SELECT * FROM book_search;

   Эта команда извлекает все строки и столбцы из таблицы book_search. Вы должны увидеть данные, которые вы только что вставили.

   Ожидаемый вывод:

   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.

Выполнение базовых полнотекстовых поисков

В этом шаге вы выполните базовые полнотекстовые поиски с использованием оператора MATCH.

1. Поиск по одному термину:

   Чтобы найти книги, содержащие слово "fantasy" (фэнтези), выполните следующую SQL-команду:

   SELECT * FROM book_search WHERE book_search MATCH 'fantasy';

   Эта команда ищет термин "fantasy" во всех столбцах таблицы book_search. Оператор MATCH используется для выполнения полнотекстового поиска.

   Ожидаемый вывод:

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

2. Поиск по нескольким терминам:

   Вы можете искать по нескольким терминам, разделяя их пробелами. Чтобы найти книги, содержащие как "love" (любовь), так и "society" (общество), выполните следующую SQL-команду:

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

   Эта команда ищет строки, которые содержат как "love", так и "society" в любом из проиндексированных столбцов.

   Ожидаемый вывод:

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

3. Поиск в определенном столбце:

   Чтобы выполнить поиск в определенном столбце, укажите имя столбца перед оператором MATCH. Например, чтобы найти книги с "Orwell" в столбце author (автор), выполните следующую SQL-команду:

   SELECT * FROM book_search WHERE author MATCH 'Orwell';

   Эта команда ищет термин "Orwell" только в столбце author.

   Ожидаемый вывод:

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

Использование расширенных возможностей MATCH

В этом шаге вы изучите расширенные возможности оператора MATCH, включая поиск по префиксу и логические операторы.

1. Поиск по префиксу (Prefix Search):

   Используйте подстановочный знак * для выполнения поиска по префиксу. Чтобы найти книги, содержащие слова, начинающиеся с "soci", выполните следующую SQL-команду:

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

   Эта команда ищет термины, которые начинаются с "soci", такие как "society" (общество).

   Ожидаемый вывод:

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

2. Логические операторы (Boolean Operators):

   FTS5 поддерживает логические операторы, такие как AND (И), OR (ИЛИ) и NOT (НЕ), для создания более сложных поисковых запросов.

   • AND (И): Найти документы, содержащие оба термина. По умолчанию, когда термины разделены пробелами, используется AND.

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

     Это эквивалентно SELECT * FROM book_search WHERE book_search MATCH 'love society';

     Ожидаемый вывод:

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

   • OR (ИЛИ): Найти документы, содержащие любой из терминов.

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

     Эта команда находит книги, содержащие либо "love" (любовь), либо "injustice" (несправедливость) (или оба).

     Ожидаемый вывод:

     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 (НЕ): Исключить документы, содержащие определенный термин.

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

     Эта команда находит книги, которые не содержат слово "fantasy" (фэнтези).

     Ожидаемый вывод:

     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.

Итог

В этой лабораторной работе вы узнали, как использовать расширение FTS5 (Full-Text Search 5) SQLite для полнотекстового поиска. Вы создали таблицу FTS5, вставили данные, выполнили базовые поиски с использованием оператора MATCH и изучили расширенные функции, такие как поиск по префиксу (prefix searches) и логические операторы (boolean operators). Эти навыки обеспечивают основу для создания мощных поисковых возможностей в ваших приложениях SQLite.