Интернет, компьютеры, софт и прочий Hi-Tech

Подписаться через RSS2Email.ru

SQLite: расширения FTS3 и FTS4

SQLite

Содержание

  1. Введение в FTS3 и FTS4
    1. Различия между FTS3 и FTS4
    2. Создание и удаление таблиц FTS
    3. Заполнение таблиц FTS
    4. Простейшие FTS-запросы
    5. Резюме
  2. Компиляция и включение FTS3 и FTS4
  3. Запросы с использованием полнотекстового индекса
    1. Набор операций в расширенном языке запросов
    2. Набор операций в стандартном языке запросов
  4. Вспомогательные функции — Snippet, Offsets и Matchinfo
    1. Функция Offsets
    2. Функция Snippet
    3. Функция Matchinfo
  5. Токенайзеры
    1. Кастомные токенайзеры (реализуемые пользователями)
  6. Структуры данных
    1. Формат целых чисел переменного размера (varint)
    2. Формат сегмента B-дерева
      1. Листовые вершины сегмента B-дерева
      2. Внутренние узлы сегмента B-дерева
    3. Формат Doclist
  7. Приложение A: Советы по разработке приложений поиска

2. Компиляция и включение FTS3 и FTS4

Несмотря на то, что FTS3 и FTS4 входят в состав исходного кода SQLite, они не включаются по умолчанию. Для сборки SQLite с включенной функциональностью FTS, нужно при компиляции определить макрос препроцессора "SQLITE_ENABLE_FTS3". Для включения в новом приложении расширенного синтаксиса запросов (смотрите ниже) нужно определить макрос "SQLITE_ENABLE_FTS3_PARENTHESIS". Обычно это делается добавлением следующих двух переключателей в командную строку при компиляции:

-DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_FTS3_PARENTHESIS

Заметим, что включение FTS3 делает доступной также и сборку FTS4. Для компиляции не существует отдельной опции SQLITE_ENABLE_FTS4. Движок SQLite собирается либо с поддержкой обоих модулей FTS3 и FTS4, либо без поддержки их обоих.

Если система сборки "амальгамы" (объединения всех сырцов SQLite в один файл) основана на autoconf, то можно задать переменную окружения CPPFLAGS при запуске скрипта 'configure'. Вот пример такой команды:

CPPFLAGS="-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS" ./configure <configure options>

где <configure options> — это те опции, которые передаются скрипту configure, если это необходимо.

Поскольку FTS3 и FTS4 являются виртуальными таблицами, опция времени компиляции SQLITE_ENABLE_FTS3 является несовместимой с опцией SQLITE_OMIT_VIRTUALTABLE.

Если в сборку SQLite не включены модули FTS, то любая попытка выполнить запрос SQL для создания таблицы FTS3 или FTS4, удаления существующей таблицы FTS или для доступа к сохраненным в ней данным будет вызывать ошибку. Возвращаемое сообщение об ошибке будет похоже на следующее: "no such module: ftsN" (где N — это 3 или 4).

Если доступна версия библиотеки ICU на C, то FTS можно скомпилировать с макросом препроцессора SQLITE_ENABLE_ICU. Компиляция с этим макросом включает токенайзер FTS, использующий библиотеку ICU для разбиения документа на термы (слова) с указанием языка и локализации.

-DSQLITE_ENABLE_ICU

3. Запросы с использованием полнотекстового индекса

На таблицах FTS чаще всего используются запросы с применением встроенного полнотекстового индекса. Полнотекстовые запросы выполняются путем задания специальной конструкции в форме "<столбец> MATCH <выражение полнотекстового запроса>" в условии WHERE запроса SELECT, читающего данные из таблицы FTS. Простые запросы FTS, возвращающие все документы, которые содержат заданный терм, уже обсуждались выше. В этом обсуждении правым операндом оператора MATCH являлась строка, состоящая из единственного терма. В данной главе будут обсуждаться более сложные типы запросов, поддерживаемые таблицами FTS, и то, как использовать более сложные выражения запросов в качестве правого операнда оператора MATCH.

Таблицы FTS поддерживают запросы трёх основных типов:

  • Запросы по токену или префиксу токена. Из таблицы FTS могут быть запрошены все документы, которые содержат указанный терм (смотрите простые случаи, описанные выше) или все документы, содержащие терм с указанным префиксом. Как мы уже видели, выражения запросов с указанием терма являются простейшими из всех. Выражение запроса, используемое для поиска по префиксу терма, составляется из самого префикса и символа '*' после него. Например:

    -- Объявление виртуальной таблицы
    CREATE VIRTUAL TABLE docs USING fts3(title, body);
    
    -- Запрос всех документов, содержащих слово "linux":
    SELECT * FROM docs WHERE docs MATCH 'linux';
    
    -- Запрос всех документов, содержащих слова, начинающиеся на "lin". Будут
    -- найдены все документы, содержащие "linux", а также все, содержащие слова
    -- "linear", "linker", "linguistic" и т.п.
    SELECT * FROM docs WHERE docs MATCH 'lin*';
    
  • Как правило, запрос по токену или префиксу токена осуществляет поиск в том столбце таблицы FTS, который указан в качестве левой части оператора MATCH. Либо по всем столбцам, если в левой части этого оператора указан специальный столбец с тем же именем, что и сама таблица FTS. Это может быть изменено указанием имени столбца и символа ":" перед каким-то термом запроса. Между именем столбца и ":" не должно быть пробелов. Между ":" и термом пробелы разрешены. Например:

    -- Из базы запрашиваются все документы, которые содержат слово "linux" в
    -- поле title и слово "problems" где угодно (хоть в title, хоть в body).
    SELECT * FROM docs WHERE docs MATCH 'title:linux problems';
    
    -- Из базы запрашиваются документы, которые содержат слово "linux" в поле
    -- title и слово "driver" в поле body (в поле title также может содержаться
    -- "driver", но это не требуется условием запроса).
    SELECT * FROM docs WHERE body MATCH 'title:linux driver';
    
  • Фразовые запросы. Фразовый запрос — это запрос, которые находит все документы, которые содержат указанный набор термов или их префиксов в указанном порядке без разделяющих их токенов. Фразовые запросы состоят из разделенных пробелами искомых терминов или их префиксов и заключаются в двойные кавычки ("). Например:

    -- Запрос всех документов, содержащих фразу "linux applications".
    SELECT * FROM docs WHERE docs MATCH '"linux applications"';
    
    -- Запрос всех документов, которые соответствуют шаблону "lin* app*". Ему
    -- соответствует как фраза "linux applications", так и "linoleum appliances" или
    -- "link apprentice".
    SELECT * FROM docs WHERE docs MATCH '"lin* app*"';
    
  • Запросы NEAR. Запрос NEAR — это запрос, который находит такие документы, которые содержат два или более указанных терма или фразы в указанной близости друг от друга (по умолчанию — 10 или меньше разделяющих термов). Запрос NEAR составляется с помощью ключевого слова NEAR между двумя фразами, термами или префиксами термов. Указание близости, отличной от умолчальной, осуществляется с помощью строки в форме "NEAR/<N>", где <N> — максимальное число разрешённых разделяющих термов. Например:

    -- Объявляем виртуальную таблицу.
    CREATE VIRTUAL TABLE docs USING fts4();
    
    -- Данные в виртуальной таблице.
    INSERT INTO docs
      VALUES('SQLite is an ACID compliant embedded relational database management system');
    
    -- Поиск документов, которые содержат слова "sqlite" и "database" так, что
    -- между ними содержится не более 10 других слов. Документ из таблицы docs
    -- соответствует этому условию (так как между словами "SQLite" и "database" в
    -- нем находится всего 6 других слов).
    SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database';
    
    -- Поиск документов, которые содержат слова "sqlite" и "database" с не более
    -- чем 6 разделяющими их словами. Документ из таблицы docs соответствует этому.
    -- Заметим, что порядок искомых слов в документе не совпадает с порядком слов в
    -- данном запросе.
    SELECT * FROM docs WHERE docs MATCH 'database NEAR/6 sqlite';
    
    -- Поиск документов, которые содержат слова "sqlite" и "database" с не более
    -- чем 5 словами между ними. Этому запросу не соответствует ни одного
    -- документа.
    SELECT * FROM docs WHERE docs MATCH 'database NEAR/5 sqlite';
    
    -- Поиск документов, которые содержат фразу "ACID compliant" и слово
    -- "database" с не более чем 2 словами между ними. Будет найден документ,
    -- сохранённый в таблице docs.
    SELECT * FROM docs WHERE docs MATCH 'database NEAR/2 "ACID compliant"';
    
    -- Поиск документов, которые содержат фразу "ACID compliant" и слово
    -- "sqlite" с не более чем 2 разделяющими их словами. Этот запрос также найдет
    -- документ из таблицы docs.
    SELECT * FROM docs WHERE docs MATCH '"ACID compliant" NEAR/2 sqlite';
    
  • В одном запросе может присутствовать несколько операторов NEAR. В этом случае каждая пара термов или фраз, разделенных оператором NEAR, должны присутствовать в документе в указанной близости друг от друга. В примерах следующего блока используется та же самая таблица и данные, что и выше:

    -- Следующий запрос выбирает документы, которые содержат слово "sqlite",
    -- находящееся на расстоянии не более чем в два слова от "acid", которое в свою
    -- очередь находится на расстоянии не больше чем в два слова от слова
    -- "relational". Вставленный выше документ из таблицы docs соответствует этим
    -- критериям.
    SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational';
    
    -- Этот запрос не найдет ни одного документа, так как в нашем примере
    -- документа слово "sqlite" находится достаточно близко от слова "acid" но
    -- недостаточно близко от слова "relational".
    SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational';
    

Фразовые и NEAR-запросы не могут покрывать сразу несколько полей в записи документа.

Три типа запросов, описанные выше, могут быть использованы для базового поиска по полнотекстовому индексу для нахождения документов, которые удовлетворяют указанным условиям. Используя язык запросов FTS можно выполнять различные операции над множествами результатов базовых запросов. В настоящее время этот язык поддерживает три операции:

  • Оператор AND определяется как пересечение двух множеств документов.
  • Оператор OR вычисляется как объединение двух множеств документов.
  • Оператор NOT (или, при использовании стандартного синтаксиса, унарный оператор "-") может быть использован для вычисления относительного дополнения одного набора документа к другому.

Модули FTS могут быть скомпилированы для использования одной из двух возможных версий синтаксиса полнотекстовых запросов. Это — "стандартный" синтаксис запросов и "расширенный". Описанные выше запросы простых термов, префиксов термов и NEAR одинаковы в обоих указанных версиях синтаксиса. По набору операторов они различаются незначительно. Два следующих подраздела описывают те операторы, которые определяются двумя версиями синтаксиса запросов. Смотрите также описание компиляции FTS, чтобы узнать, как включить поддержку того или иного синтаксиса.

3.1. Набор операций в расширенном языке запросов

Расширенный синтаксис запросов поддерживает набор операторов "AND", "OR" и "NOT". Каждый из операндов этих операторов может быть базовым запросом FTS или результатом другой операции AND, OR или NOT. Операторы должны вводиться заглавными буквами. В противном случае они будут интерпретированы как простые термы, а не операторы.

Оператор AND может задаваться неявно. Если в строке запроса FTS два простых запроса введены без разделяющего их оператора, то результат будет таким же, как если бы между ними присутствовал оператор AND. Например, выражение запроса "implicit operator" является сокращенным вариантом для "implicit AND operator".

-- Объявление виртуальной таблицы
CREATE VIRTUAL TABLE docs USING fts3();

-- Данные в виртуальной таблице
INSERT INTO docs(docid, content) VALUES(1, 'a database is a software system');
INSERT INTO docs(docid, content) VALUES(2, 'sqlite is a software system');
INSERT INTO docs(docid, content) VALUES(3, 'sqlite is a database');

-- Выдает набор документов, которые содержат слова "sqlite" и "database".
-- Этот запрос выдаст только один документ (с docid=3).
SELECT * FROM docs WHERE docs MATCH 'sqlite AND database';

-- Опять ищем документы, которые содержат оба слова "sqlite" и "database".
-- В данном случае неявно используется оператор AND. Этому запросу опять
-- соответствует только третий документ. 
SELECT * FROM docs WHERE docs MATCH 'database sqlite';

-- Запрашиваем набор документов, которые содержат хотя-бы одно из слов
-- "sqlite" или "database". Все три документа в базе данных соответствуют этому
-- запросу.
SELECT * FROM docs WHERE docs MATCH 'sqlite OR database';

-- Запрашиваем все документы, которые содержат слово "database", но не
-- содержат слово "sqlite". Только первый документ соответствует этим
-- условиям.
SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite';

-- Следующий запрос не найдет ни одного документа, так как "and" написано
-- маленькими буквами и интерпретируется как одно из искомых слов, а не как
-- оператор. Операторы должны писаться заглавными буквами. Этот запрос будет
-- искать документы, которые содержат все три слова: "database", "and" и
-- "sqlite". Но таких документов в нашем примере нет.
SELECT * FROM docs WHERE docs MATCH 'database and sqlite';

Эти примеры используют базовые полнотекстовые запросы в качестве обоих операндов для всех рассмотренных операторов. Вместо них можно использовать фразовые и NEAR-запросы, равно как и другие операции над запросами. Когда в запросе FTS присутствует более одной операции, они выполняются согласно своей приоритетности:

ОператорПриоритет согласно расширенному синтаксису запросов
NOTВысший приоритет (крепкая группировка).
AND 
ORНизший приоритет (свободная группировка).

Когда используется расширенный синтаксис запросов, для изменения умолчальных приоритетов различных операторов могут быть использованы круглые скобки. Примеры:

-- Возвращает значения docid для всех документов, которые содержат два слова
-- "sqlite" и "database" и/или содержат слово "library".
SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database OR library';

-- Этот запрос эквивалентен предыдущему.
SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database'
  UNION
SELECT docid FROM docs WHERE docs MATCH 'library';

-- Запрос множества документов, которые содержат слово "linux" и хотя бы
-- одну из фраз "sqlite database" и "sqlite library".
SELECT docid FROM docs
  WHERE docs MATCH '("sqlite database" OR "sqlite library") AND linux';

-- Этот запрос эквивалентен предыдущему.
SELECT docid FROM docs WHERE docs MATCH 'linux'
  INTERSECT
SELECT docid FROM (
  SELECT docid FROM docs WHERE docs MATCH '"sqlite library"'
    UNION
  SELECT docid FROM docs WHERE docs MATCH '"sqlite database"'
);

3.2. Набор операций в стандартном языке запросов

В стандартном синтаксисе запросов набор операций, используемых в запросах FTS, похож но не идентичен набору операций в расширенном синтаксисе запросов. Между ними существует четыре различия. А именно следующие:

  1. Поддерживается только неявная форма оператора AND. Указание в запросе строки "AND" согласно стандартному синтаксису интерпретируется как один из термов запроса для поиска документов, содержащих терм "and".

  2. Круглые скобки не поддерживаются.

  3. Оператор NOT не поддерживается. Вместо оператора NOT стандартный синтаксис запросов поддерживает унарный оператор "-", который может быть применен в запросе по простому терму или префиксу терма (но не во фразе и не в запросе NEAR). Терм или префикс терма с унарным оператором "-" не могут быть операндом для оператора OR. Запросы FTS, содержащие термы или префиксы термов с унарными операторами "-", могут содержать также что-то еще.

    -- Находим множество документов, которые содержат слово "sqlite", но не
    -- содержат слово "database".
    SELECT * FROM docs WHERE docs MATCH 'sqlite -database';
    
  4. Отличаются также правила относительной приоритетности операторов. В частности, оператор "OR" при использовании стандартного синтаксиса запросов имеет больший приоритет, чем "AND". При использовании стандартного синтаксиса приоритетность операторов соответствует следующей таблице:

    ОператорПриоритет согласно стандартному синтаксису запросов
    Унарный "-"Высший приоритет (крепкая группировка).
    OR 
    ANDНизший приоритет (свободная группировка).

    Следующий пример иллюстрирует старшинство операторов при использовании стандартного синтаксиса запросов:

    -- Ищем документы, которые содержат хотя-бы одно из слов "database" и
    -- "sqlite", а также слово "library". В силу различий в приоритетности
    -- операторов, этот запрос будет выполняться иначе при использовании
    -- расширенного синтаксиса запросов.
    SELECT * FROM docs WHERE docs MATCH 'sqlite OR database library';
    

Назад Вперед
Предисловие 4. Вспомогательные функции — Snippet, Offsets и Matchinfo

Перевод Дмитрия Скоробогатова, 01.06.2011
Оригинальный текст может быть найден по адресу http://www.sqlite.org/fts3.html


Предыдущие публикации:

Биржа долевых инвестиций SIMEX.

Последнее редактирование: 2011-06-04 04:28:21

Метки материала: компиляция, fts3, fts4, sqlite, базы данных, программирование, db, sql, разработка по

Оставьте, пожалуйста, свой комментарий к публикации

Представиться как     Антибот:
   

Просьба не постить мусор. Если вы хотите потестить xBB, воспользуйтесь кнопкой предварительного просмотра на панели инструментов xBBEditor-а.


© 2007-2017, Дмитрий Скоробогатов.
Разрешается воспроизводить, распространять и/или изменять материалы сайта
в соответствии с условиями GNU Free Documentation License,
версии 1.2 или любой более поздней версии, опубликованной FSF,
если только иное не указано в самих материалах.