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

Обзор

FTS3 и FTS4 — это модули виртуальных таблиц SQLite, позволяющие выполнять полнотекстовый поиск по набору документов. Наиболее распространённый (и эффективный) способ объяснить полнотекстовый поиск — это «то, что делают Google, Yahoo и Bing с документами, размещёнными во Всемирной паутине». Пользователь вводит термин или последовательность терминов, возможно, объединённых бинарным оператором или сгруппированных во фразу, а система полнотекстовых запросов находит набор документов, наиболее соответствующих этим терминам с учётом указанных пользователем операторов и группировок. В этой статье описываются развёртывание и использование FTS3 и FTS4.

FTS1 и FTS2 — устаревшие модули полнотекстового поиска для SQLite. В этих старых модулях известны проблемы, поэтому их использования следует избегать. Части исходного кода FTS3 были добавлены в проект SQLite Скоттом Хессом из Google. Теперь модуль разрабатывается и сопровождается в рамках SQLite.

1. Введение в FTS3 и FTS4

Модули расширения FTS3 и FTS4 позволяют создавать специальные таблицы со встроенным полнотекстовым индексом (далее — «таблицы FTS»). Полнотекстовый индекс позволяет эффективно искать в базе данных все строки, содержащие одно или несколько слов (далее — «токены»), даже если таблица содержит множество больших документов.

Например, если каждый из 517430 документов из «набора данных электронной почты Enron» добавить как в таблицу FTS, так и в обычную таблицу SQLite, созданную с помощью следующего SQL-скрипта:

CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT);     /* FTS3 table */
CREATE TABLE enrondata2(content TEXT);                        /* Ordinary table */

Тогда для определения количества документов в базе данных, содержащих слово «linux» (351), можно выполнить один из двух приведённых ниже запросов. На одном настольном ПК запрос к таблице FTS3 выполняется приблизительно за 0,03 секунды, тогда как запрос к обычной таблице занимает 22,5 секунды.

SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux';  /* 0.03 seconds */
SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */

Разумеется, приведённые выше запросы не полностью эквивалентны. Например, запрос LIKE выбирает строки, содержащие такие термины, как «linuxophobe» или «EnterpriseLinux» (в наборе данных электронной почты Enron, как оказалось, таких терминов нет), тогда как запрос MATCH к таблице FTS3 выбирает только строки, в которых «linux» встречается как отдельный токен. Оба поиска нечувствительны к регистру. Таблица FTS3 занимает на диске около 2006 МБ, тогда как обычная таблица — всего 1453 МБ. На той же конфигурации оборудования, которая использовалась для выполнения приведённых выше запросов SELECT, заполнение таблицы FTS3 заняло чуть менее 31 минуты, а обычной таблицы — 25 минут.

1.1. Различия между FTS3 и FTS4

FTS3 и FTS4 почти идентичны. Большая часть их кода общая, а интерфейсы совпадают. Различия следующие:

• FTS4 содержит оптимизации производительности запросов, которые могут значительно ускорить полнотекстовые запросы, содержащие часто встречающиеся термины (присутствующие в большом проценте строк таблицы).

• FTS4 поддерживает некоторые дополнительные параметры, которые можно использовать с функцией matchinfo().

• Поскольку для поддержки оптимизаций производительности и дополнительных параметров matchinfo() на диск записывается дополнительная информация в две новые теневые таблицы, таблицы FTS4 могут занимать больше места на диске, чем аналогичные таблицы, созданные с помощью FTS3. Обычно накладные расходы составляют 1–2% или меньше, но могут достигать 10%, если документы в таблице FTS очень малы. Накладные расходы можно снизить, указав директиву "matchinfo=fts3" в объявлении таблицы FTS4, но при этом придётся отказаться от некоторых дополнительных параметров matchinfo().

• FTS4 предоставляет хуки (параметры compress и uncompress), позволяющие хранить данные в сжатом виде и уменьшать объём дискового пространства и операций ввода-вывода.

FTS4 — это усовершенствованная версия FTS3. FTS3 доступен начиная с версии 3.5.0 SQLite (2007-09-04). Улучшения FTS4 были добавлены в версии 3.7.4 SQLite (2010-12-07).

Какой модуль использовать в приложении — FTS3 или FTS4? Иногда FTS4 значительно быстрее FTS3 — в зависимости от запроса ускорение может достигать нескольких порядков, хотя в обычных случаях производительность двух модулей схожа. FTS4 также предоставляет расширенный вывод matchinfo(), который может быть полезен для ранжирования результатов операции MATCH. С другой стороны, без директивы matchinfo=fts3 FTS4 требует немного больше места на диске, чем FTS3, хотя в большинстве случаев разница составляет всего пару процентов.

Для новых приложений рекомендуется FTS4; однако если важна совместимость со старыми версиями SQLite, обычно вполне подойдёт FTS3.

1.2. Создание и удаление таблиц FTS

Как и другие типы виртуальных таблиц, таблицы FTS создаются с помощью инструкции CREATE VIRTUAL TABLE. После ключевого слова USING указывается имя модуля — либо «fts3», либо «fts4». Аргументы модуля виртуальной таблицы можно не указывать; в этом случае создаётся таблица FTS с одним определяемым пользователем столбцом с именем «content». Также в качестве аргументов модуля можно передать список имён столбцов, разделённых запятыми.

Если имена столбцов таблицы FTS явно указаны в инструкции CREATE VIRTUAL TABLE, для каждого столбца можно дополнительно задать имя типа данных. Это лишь синтаксический сахар: FTS и ядро SQLite никак не используют указанные имена типов. То же относится к любым ограничениям, указанным вместе с именем столбца FTS: они разбираются, но система никак их не использует и не сохраняет.

-- Create an FTS table named "data" with one column - "content":
CREATE VIRTUAL TABLE data USING fts3();

-- Create an FTS table named "pages" with three columns:
CREATE VIRTUAL TABLE pages USING fts4(title, keywords, body);

-- Create an FTS table named "mail" with two columns. Datatypes
-- and column constraints are specified along with each column. These
-- are completely ignored by FTS and SQLite. 
CREATE VIRTUAL TABLE mail USING fts3(
  subject VARCHAR(256) NOT NULL,
  body TEXT CHECK(length(body)<10240)
);

Помимо списка столбцов, аргументы модуля, передаваемые инструкции CREATE VIRTUAL TABLE для создания таблицы FTS, можно использовать для указания токенизатора. Для этого вместо имени столбца указывается строка вида «tokenize=<имя токенизатора> <аргументы токенизатора>», где <имя токенизатора> — имя используемого токенизатора, а <аргументы токенизатора> — необязательный список разделённых пробелами параметров, передаваемых реализации токенизатора. Определение токенизатора можно разместить в любом месте списка столбцов, но в каждой инструкции CREATE VIRTUAL TABLE допускается не более одного такого определения. Ниже приведено подробное описание использования токенизатора и, при необходимости, его реализации.

-- Create an FTS table named "papers" with two columns that uses
-- the tokenizer "porter".
CREATE VIRTUAL TABLE papers USING fts3(author, document, tokenize=porter);

-- Create an FTS table with a single column - "content" - that uses
-- the "simple" tokenizer.
CREATE VIRTUAL TABLE data USING fts4(tokenize=simple);

-- Create an FTS table with two columns that uses the "icu" tokenizer.
-- The qualifier "en_AU" is passed to the tokenizer implementation
CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU);

Таблицы FTS можно удалить из базы данных с помощью обычной инструкции DROP TABLE. Например:

-- Create, then immediately drop, an FTS4 table.
CREATE VIRTUAL TABLE data USING fts4();
DROP TABLE data;

1.3. Заполнение таблиц FTS

Таблицы FTS заполняются с помощью инструкций INSERT, UPDATE и DELETE так же, как и обычные таблицы SQLite.

Помимо столбцов, заданных пользователем (или столбца «content», если в инструкции CREATE VIRTUAL TABLE не были указаны аргументы модуля), каждая таблица FTS имеет столбец «rowid». Столбец rowid таблицы FTS ведёт себя так же, как столбец rowid обычной таблицы SQLite, за исключением того, что значения в столбце rowid таблицы FTS остаются неизменными, если база данных перестраивается с помощью команды VACUUM. Для таблиц FTS наряду с обычными идентификаторами «rowid», «oid» и «_oid_» допускается псевдоним «docid». Попытка вставить или обновить строку со значением docid, уже существующим в таблице, приводит к ошибке — так же, как и в обычной таблице SQLite.

Есть ещё одно тонкое различие между «docid» и стандартными псевдонимами SQLite для столбца rowid. Обычно, если инструкция INSERT или UPDATE присваивает отдельные значения двум или более псевдонимам столбца rowid, SQLite записывает в базу данных самое правое из указанных в инструкции INSERT или UPDATE значений. Однако при вставке или обновлении таблицы FTS присваивание ненулевого значения и «docid», и одному или нескольким псевдонимам rowid SQLite считается ошибкой. Пример приведён ниже.

-- Create an FTS table
CREATE VIRTUAL TABLE pages USING fts4(title, body);

-- Insert a row with a specific docid value.
INSERT INTO pages(docid, title, body) VALUES(53, 'Home Page', 'SQLite is a software...');

-- Insert a row and allow FTS to assign a docid value using the same algorithm as
-- SQLite uses for ordinary tables. In this case the new docid will be 54,
-- one greater than the largest docid currently present in the table.
INSERT INTO pages(title, body) VALUES('Download', 'All SQLite source code...');

-- Change the title of the row just inserted.
UPDATE pages SET title = 'Download SQLite' WHERE rowid = 54;

-- Delete the entire table contents.
DELETE FROM pages;

-- The following is an error. It is not possible to assign non-NULL values to both
-- the rowid and docid columns of an FTS table.
INSERT INTO pages(rowid, docid, title, body) VALUES(1, 2, 'A title', 'A document body');

Для поддержки полнотекстовых запросов FTS ведёт инвертированный индекс, который сопоставляет каждый уникальный термин или слово из набора данных с местами его появления в содержимом таблицы. Для тех, кому интересно, ниже приведено полное описание структуры данных, используемой для хранения этого индекса в файле базы данных. Особенность этой структуры данных заключается в том, что в базе данных в любой момент времени может находиться не одно B-дерево индекса, а несколько разных B-деревьев, которые постепенно объединяются по мере вставки, обновления и удаления строк. Этот метод повышает производительность записи в таблицу FTS, но создаёт дополнительные издержки для полнотекстовых запросов, использующих индекс. Выполнение специальной команды «optimize» — инструкции SQL вида «INSERT INTO <fts-table>(<fts-table>) VALUES('optimize')» — заставляет FTS объединить все существующие B-деревья индекса в одно большое B-дерево, содержащее весь индекс. Это может быть затратной операцией, но она способна ускорить последующие запросы.

Например, чтобы оптимизировать полнотекстовый индекс таблицы FTS с именем «docs»:

-- Optimize the internal structure of FTS table "docs".
INSERT INTO docs(docs) VALUES('optimize');

Некоторым приведённая выше инструкция может показаться синтаксически неверной. Объяснение приведено в разделе о простых запросах FTS.

Существует и другой, устаревший способ запуска операции оптимизации с помощью инструкции SELECT. В новом коде для оптимизации структур FTS следует использовать инструкции, подобные приведённой выше INSERT.

1.4. Простые запросы FTS

Как и для любых других таблиц SQLite — виртуальных и обычных, — данные из таблиц FTS извлекаются с помощью инструкции SELECT.

Эффективно запрашивать данные в таблицах FTS можно с помощью инструкций SELECT двух видов:

• Запрос по rowid. Если предложение WHERE инструкции SELECT содержит подусловие вида «rowid = ?», где ? — выражение SQL, FTS может напрямую получить запрошенную строку, используя механизм, эквивалентный индексу SQLite INTEGER PRIMARY KEY.

• Полнотекстовый запрос. Если предложение WHERE инструкции SELECT содержит подусловие вида «<столбец> MATCH ?», FTS может использовать встроенный полнотекстовый индекс, чтобы ограничить поиск документами, соответствующими строке полнотекстового запроса, указанной в правом операнде предложения MATCH.

Если невозможно использовать ни одну из этих двух стратегий запроса, все запросы к таблицам FTS выполняются путём линейного сканирования всей таблицы. Если таблица содержит большой объём данных, такой подход может быть непрактичным (первый пример на этой странице показывает, что линейное сканирование 1,5 ГБ данных на современном ПК занимает около 30 секунд).

-- The examples in this block assume the following FTS table:
CREATE VIRTUAL TABLE mail USING fts3(subject, body);

SELECT * FROM mail WHERE rowid = 15;                -- Fast. Rowid lookup.
SELECT * FROM mail WHERE body MATCH 'sqlite';       -- Fast. Full-text query.
SELECT * FROM mail WHERE mail MATCH 'search';       -- Fast. Full-text query.
SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20;   -- Fast. Rowid lookup.
SELECT * FROM mail WHERE subject = 'database';      -- Slow. Linear scan.
SELECT * FROM mail WHERE subject MATCH 'database';  -- Fast. Full-text query.

Во всех приведённых выше полнотекстовых запросах правый операнд оператора MATCH — это строка, состоящая из одного термина. В этом случае выражение MATCH принимает значение true для всех документов, содержащих одно или несколько вхождений указанного слова («sqlite», «search» или «database» — в зависимости от примера). Указание одного термина в качестве правого операнда оператора MATCH — простейший и наиболее распространённый тип полнотекстового запроса. Однако возможны и более сложные запросы, включая поиск фраз, поиск по префиксу термина и поиск документов, содержащих комбинации терминов, встречающихся на заданном расстоянии друг от друга. Различные способы запросов к полнотекстовому индексу описаны ниже.

Обычно полнотекстовые запросы нечувствительны к регистру. Однако это зависит от конкретного токенизатора, используемого в запрашиваемой таблице FTS. Подробности см. в разделе о токенизаторах.

В предыдущем абзаце отмечено, что оператор MATCH с простым термином в правом операнде принимает значение true для всех документов, содержащих указанный термин. В данном контексте «документ» может обозначать либо данные в одном столбце строки таблицы FTS, либо содержимое всех столбцов одной строки — в зависимости от идентификатора, используемого в качестве левого операнда оператора MATCH. Если идентификатор, указанный в качестве левого операнда MATCH, — это имя столбца таблицы FTS, то искомый термин должен содержаться в значении указанного столбца. Однако если идентификатор — это имя самой таблицы FTS, оператор MATCH принимает значение true для каждой строки таблицы FTS, в любом столбце которой содержится искомый термин. Следующий пример это демонстрирует:

-- Example schema
CREATE VIRTUAL TABLE mail USING fts3(subject, body);

-- Example table population
INSERT INTO mail(docid, subject, body) VALUES(1, 'software feedback', 'found it too slow');
INSERT INTO mail(docid, subject, body) VALUES(2, 'software feedback', 'no feedback');
INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order',  'was a software problem');

-- Example queries
SELECT * FROM mail WHERE subject MATCH 'software';    -- Selects rows 1 and 2
SELECT * FROM mail WHERE body    MATCH 'feedback';    -- Selects row 2
SELECT * FROM mail WHERE mail    MATCH 'software';    -- Selects rows 1, 2 and 3
SELECT * FROM mail WHERE mail    MATCH 'slow';        -- Selects rows 1 and 3

На первый взгляд два последних полнотекстовых запроса в приведённом выше примере кажутся синтаксически неверными: в качестве выражения SQL используется имя таблицы («mail»). Это допустимо, потому что у каждой таблицы FTS есть столбец HIDDEN с тем же именем, что и у самой таблицы (в данном случае — «mail»). Значение, хранящееся в этом столбце, не имеет смысла для приложения, но может использоваться как левый операнд оператора MATCH. Этот специальный столбец также можно передавать в качестве аргумента вспомогательным функциям FTS.

Следующий пример иллюстрирует сказанное выше. Выражения «docs», «docs.docs» и «main.docs.docs» обозначают столбец «docs». Однако выражение «main.docs» не обозначает ни один столбец. Его можно было бы использовать для ссылки на таблицу, но в контексте, в котором оно используется ниже, имя таблицы недопустимо.

-- Example schema
CREATE VIRTUAL TABLE docs USING fts4(content);

-- Example queries
SELECT * FROM docs WHERE docs MATCH 'sqlite';              -- OK.
SELECT * FROM docs WHERE docs.docs MATCH 'sqlite';         -- OK.
SELECT * FROM docs WHERE main.docs.docs MATCH 'sqlite';    -- OK.
SELECT * FROM docs WHERE main.docs MATCH 'sqlite';         -- Error.

1.5. Итоги

С точки зрения пользователей таблицы FTS во многом похожи на обычные таблицы SQLite. Данные можно добавлять в таблицы FTS, изменять и удалять из них с помощью команд INSERT, UPDATE и DELETE, как и в обычных таблицах. Аналогично, для запросов к данным можно использовать команду SELECT. В следующем списке перечислены различия между FTS и обычными таблицами:

1. Как и для всех типов виртуальных таблиц, для таблиц FTS нельзя создавать индексы или триггеры. Также нельзя использовать команду ALTER TABLE для добавления столбцов в таблицы FTS (хотя с помощью ALTER TABLE можно переименовать таблицу FTS).

2. Типы данных, указанные в инструкции «CREATE VIRTUAL TABLE» для создания таблицы FTS, полностью игнорируются. Вместо обычных правил применения типового сродства к вставляемым значениям все значения, вставляемые в столбцы таблицы FTS (кроме специального столбца rowid), перед сохранением преобразуются к типу TEXT.

3. В таблицах FTS можно использовать специальный псевдоним «docid» для ссылки на столбец rowid, поддерживаемый всеми виртуальными таблицами.

4. Для запросов, использующих встроенный полнотекстовый индекс, поддерживается оператор FTS MATCH.

5. Для поддержки полнотекстовых запросов доступны вспомогательные функции FTS: snippet(), offsets() и matchinfo().

6. У каждой таблицы FTS есть скрытый столбец с тем же именем, что и у самой таблицы. Значение в каждой строке этого скрытого столбца — это blob, полезный только в качестве левого операнда оператора MATCH или самого левого аргумента одной из вспомогательных функций FTS.

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, либо ни один из этих модулей.

При использовании канонической системы сборки удобный способ задать эти макросы — установить переменную окружения CPPFLAGS при запуске скрипта «configure». Например, следующая команда:

CPPFLAGS="-DSQLITE_ENABLE_FTS3_PARENTHESIS" ./configure --enable-fts3 <configure options>

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

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

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

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

-DSQLITE_ENABLE_ICU

В каноническом дереве сборки версии 3.48 или выше ICU можно включить с помощью флагов скрипта configure: подробности см. в ./configure --help.

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

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

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

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

-- Virtual table declaration
CREATE VIRTUAL TABLE docs USING fts3(title, body);

-- Query for all documents containing the term "linux":
SELECT * FROM docs WHERE docs MATCH 'linux';

-- Query for all documents containing a term with the prefix "lin". This will match
-- all documents that contain "linux", but also those that contain terms "linear",
--"linker", "linguistic" and so on.
SELECT * FROM docs WHERE docs MATCH 'lin*';

• Обычно запрос по токену или его префиксу сопоставляется со столбцом таблицы FTS, указанным слева от оператора MATCH. Если же указать специальный столбец с именем самой таблицы FTS, поиск будет выполняться по всем столбцам. Это поведение можно изменить, указав перед простым запросом по термину имя столбца и символ «:». Между «:» и искомым термином может быть пробел, но между именем столбца и символом «:» пробела быть не должно. Например:

-- Query the database for documents for which the term "linux" appears in
-- the document title, and the term "problems" appears in either the title
-- or body of the document.
SELECT * FROM docs WHERE docs MATCH 'title:linux problems';

-- Query the database for documents for which the term "linux" appears in
-- the document title, and the term "driver" appears in the body of the document
-- ("driver" may also appear in the title, but this alone will not satisfy the
-- query criteria).
SELECT * FROM docs WHERE body MATCH 'title:linux driver';

• Если таблица FTS является таблицей FTS4 (а не FTS3), перед токеном также можно поставить символ «^». В этом случае для совпадения токен должен быть самым первым токеном в любом столбце соответствующей строки. Примеры:

-- All documents for which "linux" is the first token of at least one
-- column.
SELECT * FROM docs WHERE docs MATCH '^linux';

-- All documents for which the first token in column "title" begins with "lin".
SELECT * FROM docs WHERE body MATCH 'title: ^lin*';

• Запросы по фразе. Запрос по фразе возвращает все документы, содержащие заданный набор терминов или префиксов терминов в указанном порядке, без промежуточных токенов. Такие запросы задаются последовательностью терминов или префиксов, разделённых пробелами и заключённых в двойные кавычки ("). Например:

-- Query for all documents that contain the phrase "linux applications".
SELECT * FROM docs WHERE docs MATCH '"linux applications"';

-- Query for all documents that contain a phrase that matches "lin* app*". As well as
-- "linux applications", this will match common phrases such as "linoleum appliances"
-- or "link apprentice".
SELECT * FROM docs WHERE docs MATCH '"lin* app*"';

• Запросы NEAR. Запрос NEAR возвращает документы, содержащие два или более заданных терминов или фраз, расположенных на заданном расстоянии друг от друга (по умолчанию между ними допускается не более 10 промежуточных терминов). Запрос NEAR задаётся ключевым словом «NEAR» между двумя запросами по фразе, токену или префиксу токена. Чтобы задать расстояние, отличное от значения по умолчанию, можно использовать оператор вида «NEAR/<N>», где <N> — максимальное число допустимых промежуточных терминов. Например:

-- Virtual table declaration.
CREATE VIRTUAL TABLE docs USING fts4();

-- Virtual table data.
INSERT INTO docs VALUES('SQLite is an ACID compliant embedded relational database management system');

-- Search for a document that contains the terms "sqlite" and "database" with
-- not more than 10 intervening terms. This matches the only document in
-- table docs (since there are only six terms between "SQLite" and "database"
-- in the document).
SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database';

-- Search for a document that contains the terms "sqlite" and "database" with
-- not more than 6 intervening terms. This also matches the only document in
-- table docs. Note that the order in which the terms appear in the document
-- does not have to be the same as the order in which they appear in the query.
SELECT * FROM docs WHERE docs MATCH 'database NEAR/6 sqlite';

-- Search for a document that contains the terms "sqlite" and "database" with
-- not more than 5 intervening terms. This query matches no documents.
SELECT * FROM docs WHERE docs MATCH 'database NEAR/5 sqlite';

-- Search for a document that contains the phrase "ACID compliant" and the term
-- "database" with not more than 2 terms separating the two. This matches the
-- document stored in table docs.
SELECT * FROM docs WHERE docs MATCH 'database NEAR/2 "ACID compliant"';

-- Search for a document that contains the phrase "ACID compliant" and the term
-- "sqlite" with not more than 2 terms separating the two. This also matches
-- the only document stored in table docs.
SELECT * FROM docs WHERE docs MATCH '"ACID compliant" NEAR/2 sqlite';

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

-- The following query selects documents that contains an instance of the term 
-- "sqlite" separated by two or fewer terms from an instance of the term "acid",
-- which is in turn separated by two or fewer terms from an instance of the term
-- "relational".
SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational';

-- This query matches no documents. There is an instance of the term "sqlite" with
-- sufficient proximity to an instance of "acid" but it is not sufficiently close
-- to an instance of the term "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».

-- Virtual table declaration
CREATE VIRTUAL TABLE docs USING fts3();

-- Virtual table data
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');

-- Return the set of documents that contain the term "sqlite", and the
-- term "database". This query will return the document with docid 3 only.
SELECT * FROM docs WHERE docs MATCH 'sqlite AND database';

-- Again, return the set of documents that contain both "sqlite" and
-- "database". This time, use an implicit AND operator. Again, document
-- 3 is the only document matched by this query. 
SELECT * FROM docs WHERE docs MATCH 'database sqlite';

-- Query for the set of documents that contains either "sqlite" or "database".
-- All three documents in the database are matched by this query.
SELECT * FROM docs WHERE docs MATCH 'sqlite OR database';

-- Query for all documents that contain the term "database", but do not contain
-- the term "sqlite". Document 1 is the only document that matches this criteria.
SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite';

-- The following query matches no documents. Because "and" is in lowercase letters,
-- it is interpreted as a basic term query instead of an operator. Operators must
-- be specified using capital letters. In practice, this query will match any documents
-- that contain each of the three terms "database", "and" and "sqlite" at least once.
-- No documents in the example data above match this criteria.
SELECT * FROM docs WHERE docs MATCH 'database and sqlite';

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

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

-- Return the docid values associated with all documents that contain the
-- two terms "sqlite" and "database", and/or contain the term "library".
SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database OR library';

-- This query is equivalent to the above.
SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database'
  UNION
SELECT docid FROM docs WHERE docs MATCH 'library';

-- Query for the set of documents that contains the term "linux", and at least
-- one of the phrases "sqlite database" and "sqlite library".
SELECT docid FROM docs WHERE docs MATCH '("sqlite database" OR "sqlite library") AND linux';

-- This query is equivalent to the above.
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 не может состоять только из запросов по терминам или префиксам терминов с унарными операторами «-».

-- Search for the set of documents that contain the term "sqlite" but do
-- not contain the term "database".
SELECT * FROM docs WHERE docs MATCH 'sqlite -database';

4. Относительный приоритет операций над множествами различается. В частности, при использовании стандартного синтаксиса запросов оператор «OR» имеет более высокий приоритет, чем «AND». Приоритет операторов стандартного синтаксиса запросов:

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

-- Search for documents that contain at least one of the terms "database"
-- and "sqlite", and also contain the term "library". Because of the differences
-- in operator precedences, this query would have a different interpretation using
-- the enhanced query syntax.
SELECT * FROM docs WHERE docs MATCH 'sqlite OR database library';

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

Модули FTS3 и FTS4 предоставляют три специальные скалярные функции SQL, которые могут быть полезны разработчикам систем полнотекстового поиска: "snippet", "offsets" и "matchinfo". Функции "snippet" и "offsets" предназначены для того, чтобы пользователь мог определить местоположение искомых терминов в возвращённых документах. Функция "matchinfo" предоставляет пользователю метрики, которые могут быть полезны для фильтрации или сортировки результатов запроса по релевантности.

Первый аргумент всех трёх специальных скалярных функций SQL должен быть скрытым столбцом FTS таблицы FTS, к которой применяется функция. Скрытый столбец FTS — это автоматически создаваемый столбец, присутствующий во всех таблицах FTS и имеющий то же имя, что и сама таблица FTS. Например, для таблицы FTS с именем "mail":

SELECT offsets(mail) FROM mail WHERE mail MATCH <full-text query expression>;
SELECT snippet(mail) FROM mail WHERE mail MATCH <full-text query expression>;
SELECT matchinfo(mail) FROM mail WHERE mail MATCH <full-text query expression>;

Три вспомогательные функции полезны только в операторе SELECT, использующем полнотекстовый индекс таблицы FTS. Если они используются в SELECT, применяющем стратегии "запрос по rowid" или "линейное сканирование", функции snippet и offsets возвращают пустую строку, а функция matchinfo возвращает значение blob нулевого размера.

Все три вспомогательные функции извлекают из выражения запроса FTS набор «сопоставляемых фраз» для обработки. Набор сопоставляемых фраз для заданного запроса состоит из всех фраз (включая токены без кавычек и префиксы токенов) в выражении, за исключением тех, перед которыми стоит унарный оператор "-" (стандартный синтаксис), или тех, которые являются частью подвыражения, используемого в качестве правого операнда оператора NOT.

С учётом следующих условий каждая последовательность токенов в таблице FTS, соответствующая одной из сопоставляемых фраз в выражении запроса, называется «совпадением фразы»:

1. Если сопоставляемая фраза входит в последовательность фраз, соединённых операторами NEAR в выражении запроса FTS, то каждое совпадение фразы должно находиться достаточно близко к другим совпадениям фраз соответствующих типов, чтобы удовлетворять условию NEAR.
2. Если сопоставляемая фраза в запросе FTS ограничена данными в указанном столбце таблицы FTS, учитываются только совпадения фразы, находящиеся в этом столбце.

4.1. Функция Offsets

Для запроса SELECT, использующего полнотекстовый индекс, функция offsets() возвращает текстовое значение, содержащее последовательность целых чисел, разделённых пробелами. Для каждого термина в каждом совпадении фразы текущей строки в возвращаемом списке указаны четыре целых числа. Каждый набор из четырёх чисел интерпретируется следующим образом:

В следующем блоке приведены примеры использования функции offsets.

CREATE VIRTUAL TABLE mail USING fts3(subject, body);
INSERT INTO mail VALUES('hello world', 'This message is a hello world message.');
INSERT INTO mail VALUES('urgent: serious', 'This mail is seen as a more serious mail');

-- The following query returns a single row (as it matches only the first
-- entry in table "mail". The text returned by the offsets function is
-- "0 0 6 5 1 0 24 5".
--
-- The first set of four integers in the result indicate that column 0
-- contains an instance of term 0 ("world") at byte offset 6. The term instance
-- is 5 bytes in size. The second set of four integers shows that column 1
-- of the matched row contains an instance of term 0 ("world") at byte offset
-- 24. Again, the term instance is 5 bytes in size.
SELECT offsets(mail) FROM mail WHERE mail MATCH 'world';

-- The following query returns also matches only the first row in table "mail".
-- In this case the returned text is "1 0 5 7 1 0 30 7".
SELECT offsets(mail) FROM mail WHERE mail MATCH 'message';

-- The following query matches the second row in table "mail". It returns the
-- text "1 0 28 7 1 1 36 4". Only those occurrences of terms "serious" and "mail"
-- that are part of an instance of the phrase "serious mail" are identified; the
-- other occurrences of "serious" and "mail" are ignored.
SELECT offsets(mail) FROM mail WHERE mail MATCH '"serious mail"';

4.2. Функция Snippet

Функция snippet используется для создания форматированных фрагментов текста документа, отображаемых в составе отчёта о результатах полнотекстового запроса. Функции snippet можно передать от одного до шести аргументов:

Сначала функция snippet пытается найти в текущей строке фрагмент текста из |N| токенов, содержащий хотя бы одно совпадение фразы для каждой сопоставляемой фразы, совпавшей где-либо в текущей строке, где |N| — абсолютное значение шестого аргумента, переданного функции snippet. Если в тексте, хранящемся в одном столбце, меньше |N| токенов, рассматривается всё значение столбца. Фрагменты текста не могут охватывать несколько столбцов.

Если такой фрагмент текста найден, он возвращается со следующими изменениями:

• Если фрагмент текста начинается не с начала значения столбца, перед ним добавляется текст «многоточия».
• Если фрагмент текста заканчивается не в конце значения столбца, после него добавляется текст «многоточия».
• Для каждого токена во фрагменте текста, являющегося частью совпадения фразы, перед токеном вставляется текст «начала совпадения», а сразу после него — текст «конца совпадения».

Если можно найти несколько таких фрагментов, предпочтение отдаётся фрагментам, содержащим большее число «дополнительных» совпадений фраз. Начало выбранного фрагмента текста можно сдвинуть на несколько токенов вперёд или назад, чтобы попытаться сосредоточить совпадения фраз ближе к центру фрагмента.

Если N — положительное значение и не удаётся найти фрагмент, содержащий совпадение фразы для каждой сопоставляемой фразы, функция snippet пытается найти два фрагмента примерно по N/2 токенов, которые вместе содержат хотя бы одно совпадение фразы для каждой сопоставляемой фразы, совпавшей в текущей строке. Если это не удаётся, предпринимаются попытки найти три фрагмента по N/3 токенов, а затем четыре фрагмента по N/4 токенов. Если не удаётся найти набор из четырёх фрагментов, охватывающий требуемые совпадения фраз, выбираются четыре фрагмента по N/4 токенов, обеспечивающие наилучший охват.

Если N — отрицательное значение и не удаётся найти один фрагмент, содержащий требуемые совпадения фраз, функция snippet выполняет поиск двух фрагментов по |N| токенов, затем трёх, а затем четырёх. Иными словами, если указанное значение N отрицательное, размер фрагментов не уменьшается, даже если для обеспечения требуемого охвата совпадений фраз необходимо несколько фрагментов.

После нахождения M фрагментов, где M равно двум, трём или четырём, как описано выше, они объединяются в отсортированном порядке с разделением текстом «многоточия». Перед возвратом к тексту применяются три изменения, перечисленные ранее.

Note: In this block of examples, newlines and whitespace characters have
been inserted into the document inserted into the FTS table, and the expected
results described in SQL comments. This is done to enhance readability only,
they would not be present in actual SQLite commands or output.

-- Create and populate an FTS table.
CREATE VIRTUAL TABLE text USING fts4();
INSERT INTO text VALUES('
  During 30 Nov-1 Dec, 2-3oC drops. Cool in the upper portion, minimum temperature 14-16oC
  and cool elsewhere, minimum temperature 17-20oC. Cold to very cold on mountaintops,
  minimum temperature 6-12oC. Northeasterly winds 15-30 km/hr. After that, temperature
  increases. Northeasterly winds 15-30 km/hr.
');

-- The following query returns the text value:
--
--   "<b>...</b>cool elsewhere, minimum temperature 17-20oC. <b>Cold</b> to very 
--    <b>cold</b> on mountaintops, minimum temperature 6<b>...</b>".
--
SELECT snippet(text) FROM text WHERE text MATCH 'cold';

-- The following query returns the text value:
--
--   "...the upper portion, [minimum] [temperature] 14-16oC and cool elsewhere,
--    [minimum] [temperature] 17-20oC. Cold..."
--
SELECT snippet(text, '[', ']', '...') FROM text WHERE text MATCH '"min* tem*"'

4.3. Функция Matchinfo

Функция matchinfo возвращает значение blob. Если она используется в запросе, не использующем полнотекстовый индекс («запрос по rowid» или «линейное сканирование»), размер blob равен нулю байт. В противном случае blob состоит из нуля или более 32-разрядных беззнаковых целых чисел в машинном порядке байтов. Точное количество целых чисел в возвращаемом массиве зависит как от запроса, так и от значения второго аргумента (если он задан), переданного функции matchinfo.

Функция matchinfo вызывается с одним или двумя аргументами. Как и для всех вспомогательных функций, первым аргументом должен быть специальный скрытый столбец FTS. Если указан второй аргумент, он должен быть текстовым значением, состоящим только из символов 'p', 'c', 'n', 'a', 'l', 's', 'x', 'y' и 'b'. Если второй аргумент явно не указан, по умолчанию используется "pcx". Далее второй аргумент называется «строкой формата».

Символы строки формата matchinfo обрабатываются слева направо. Каждый символ строки формата приводит к добавлению в возвращаемый массив одного или нескольких 32-разрядных беззнаковых целых чисел. В столбце «значения» следующей таблицы указано количество целочисленных значений, добавляемых в выходной буфер для каждого поддерживаемого символа строки формата. В приведённых формулах cols — число столбцов таблицы FTS, а phrases — число сопоставляемых фраз в запросе.

          hits_this_row  = array[3 * (c + p*cols) + 0]
          hits_all_rows  = array[3 * (c + p*cols) + 1]
          docs_with_hits = array[3 * (c + p*cols) + 2]

          a OR (b AND c)

          "a c d"

          hits_for_phrase_p_column_c  = array[c + p*cols]

    p_is_in_c = array[p * ((nCol+31)/32)] & (1 << (c % 32))

Например:

-- Create and populate an FTS4 table with two columns:
CREATE VIRTUAL TABLE t1 USING fts4(a, b);
INSERT INTO t1 VALUES('transaction default models default', 'Non transaction reads');
INSERT INTO t1 VALUES('the default transaction', 'these semantics present');
INSERT INTO t1 VALUES('single request', 'default data');

-- In the following query, no format string is specified and so it defaults
-- to "pcx". It therefore returns a single row consisting of a single blob
-- value 80 bytes in size (20 32-bit integers - 1 for "p", 1 for "c" and
-- 3*2*3 for "x"). If each block of 4 bytes in the blob is interpreted
-- as an unsigned integer in machine byte-order, the values will be:
--
--     3 2  1 3 2  0 1 1  1 2 2  0 1 1  0 0 0  1 1 1
--
-- The row returned corresponds to the second entry inserted into table t1.
-- The first two integers in the blob show that the query contained three
-- phrases and the table being queried has two columns. The next block of
-- three integers describes column 0 (in this case column "a") and phrase
-- 0 (in this case "default"). The current row contains 1 hit for "default"
-- in column 0, of a total of 3 hits for "default" that occur in column
-- 0 of any table row. The 3 hits are spread across 2 different rows.
--
-- The next set of three integers (0 1 1) pertain to the hits for "default"
-- in column 1 of the table (0 in this row, 1 in all rows, spread across 
-- 1 rows).
--
SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'default transaction "these semantics"';

-- The format string for this query is "ns". The output array will therefore
-- contain 3 integer values - 1 for "n" and 2 for "s". The query returns
-- two rows (the first two rows in the table match). The values returned are:
--
--     3  1 1
--     3  2 0
--
-- The first value in the matchinfo array returned for both rows is 3 (the 
-- number of rows in the table). The following two values are the lengths 
-- of the longest common subsequence of phrase matches in each column.
SELECT matchinfo(t1, 'ns') FROM t1 WHERE t1 MATCH 'default transaction';

Функция matchinfo работает значительно быстрее, чем функции snippet и offsets. Это связано с тем, что реализация обеих функций — snippet и offsets — должна извлекать анализируемые документы с диска, тогда как все данные, необходимые функции matchinfo, доступны в тех же частях полнотекстового индекса, которые нужны для выполнения самого полнотекстового запроса. Это означает, что из следующих двух запросов первый может работать на порядок быстрее второго:

SELECT docid, matchinfo(tbl) FROM tbl WHERE tbl MATCH <query expression>;
SELECT docid, offsets(tbl) FROM tbl WHERE tbl MATCH <query expression>;

Функция matchinfo предоставляет всю информацию, необходимую для вычисления вероятностных оценок релевантности по модели «мешка слов», например Okapi BM25/BM25F, которые можно использовать для сортировки результатов в приложении полнотекстового поиска. В приложении A этого документа, «советы по созданию поисковых приложений», приведён пример эффективного использования функции matchinfo().

5. Fts4aux — прямой доступ к полнотекстовому индексу

Начиная с версии 3.7.6 (2011-04-12), SQLite включает новый модуль виртуальных таблиц под названием "fts4aux", который можно использовать для непосредственного просмотра полнотекстового индекса существующей таблицы FTS. Несмотря на название, fts4aux работает с таблицами FTS3 так же хорошо, как и с таблицами FTS4. Таблицы fts4aux доступны только для чтения. Изменить содержимое таблицы fts4aux можно только изменив содержимое связанной с ней таблицы FTS. Модуль fts4aux автоматически включается во все сборки, включающие FTS.

Виртуальная таблица fts4aux создаётся с одним или двумя аргументами. Если указан один аргумент, он представляет собой неквалифицированное имя таблицы FTS, к которой будет предоставлен доступ. Чтобы получить доступ к таблице в другой базе данных (например, создать временную таблицу fts4aux для доступа к таблице FTS3 в базе данных MAIN), используйте форму с двумя аргументами: в первом укажите имя целевой базы данных (например, "main"), а во втором — имя таблицы FTS3/4. (Форма fts4aux с двумя аргументами добавлена в версии SQLite 3.7.17 (2013-05-20) и вызовет ошибку в более ранних выпусках.) Например:

-- Create an FTS4 table
CREATE VIRTUAL TABLE ft USING fts4(x, y);

-- Create an fts4aux table to access the full-text index for table "ft"
CREATE VIRTUAL TABLE ft_terms USING fts4aux(ft);

-- Create a TEMP fts4aux table accessing the "ft" table in "main"
CREATE VIRTUAL TABLE temp.ft_terms_2 USING fts4aux(main,ft);

Для каждого термина, присутствующего в таблице FTS, в таблице fts4aux имеется от 2 до N+1 строк, где N — число определённых пользователем столбцов связанной таблицы FTS. Таблица fts4aux всегда содержит одни и те же четыре столбца, перечисленные слева направо:

Например, используя таблицы, созданные выше:

INSERT INTO ft(x, y) VALUES('Apple banana', 'Cherry');
INSERT INTO ft(x, y) VALUES('Banana Date Date', 'cherry');
INSERT INTO ft(x, y) VALUES('Cherry Elderberry', 'Elderberry');

-- The following query returns this data:
--
--     apple       |  *  |  1  |  1
--     apple       |  0  |  1  |  1
--     banana      |  *  |  2  |  2
--     banana      |  0  |  2  |  2
--     cherry      |  *  |  3  |  3
--     cherry      |  0  |  1  |  1
--     cherry      |  1  |  2  |  2
--     date        |  *  |  1  |  2
--     date        |  0  |  1  |  2
--     elderberry  |  *  |  1  |  2
--     elderberry  |  0  |  1  |  1
--     elderberry  |  1  |  1  |  1
--
SELECT term, col, documents, occurrences FROM ft_terms;

В примере значения столбца "term" записаны строчными буквами, хотя в таблицу "ft" они были добавлены в смешанном регистре. Это связано с тем, что таблица fts4aux содержит термины, извлечённые из текста документа токенизатором. В данном случае, поскольку в таблице "ft" используется простой токенизатор, все термины приведены к нижнему регистру. Кроме того, например, нет строки, в которой в столбце "term" указано "apple", а в столбце "col" — 1. Поскольку в столбце 1 нет экземпляров термина "apple", соответствующая строка отсутствует в таблице fts4aux.

Во время транзакции некоторые данные, записанные в таблицу FTS, могут кэшироваться в памяти и записываться в базу данных только при фиксации транзакции. Однако реализация модуля fts4aux может считывать данные только из базы данных. На практике это означает, что если выполнить запрос к таблице fts4aux внутри транзакции, в которой была изменена связанная таблица FTS, результаты запроса, скорее всего, будут отражать лишь часть изменений (возможно, пустую).

6. Параметры FTS4

Если в операторе "CREATE VIRTUAL TABLE" указан модуль FTS4 (а не FTS3), вместо имён столбцов можно также указывать специальные директивы — параметры FTS4, аналогичные параметру "tokenize=*". Параметр FTS4 состоит из имени параметра, за которым следует символ "=", а затем значение параметра. Значение параметра можно заключить в одинарные или двойные кавычки; встроенные кавычки экранируются так же, как в литералах SQL. По обе стороны от символа "=" не должно быть пробелов. Например, чтобы создать таблицу FTS4 со значением параметра "matchinfo", равным "fts3":

-- Create a reduced-footprint FTS4 table.
CREATE VIRTUAL TABLE papers USING fts4(author, document, matchinfo=fts3);

В настоящее время FTS4 поддерживает следующие параметры:

При использовании FTS4 указание имени столбца, содержащего символ "=" и не являющегося ни спецификацией "tokenize=*", ни распознанным параметром FTS4, считается ошибкой. В FTS3 первый токен нераспознанной директивы интерпретируется как имя столбца. Аналогично, при использовании FTS4 ошибка возникает, если в одном объявлении таблицы указано несколько директив "tokenize=*"; в FTS3 вторая и последующие директивы "tokenize=*" интерпретируются как имена столбцов. Например:

-- An error. FTS4 does not recognize the directive "xyz=abc".
CREATE VIRTUAL TABLE papers USING fts4(author, document, xyz=abc);

-- Create an FTS3 table with three columns - "author", "document"
-- and "xyz".
CREATE VIRTUAL TABLE papers USING fts3(author, document, xyz=abc);

-- An error. FTS4 does not allow multiple tokenize=* directives
CREATE VIRTUAL TABLE papers USING fts4(tokenize=porter, tokenize=simple);

-- Create an FTS3 table with a single column named "tokenize". The
-- table uses the "porter" tokenizer.
CREATE VIRTUAL TABLE papers USING fts3(tokenize=porter, tokenize=simple);

-- An error. Cannot create a table with two columns named "tokenize".
CREATE VIRTUAL TABLE papers USING fts3(tokenize=porter, tokenize=simple, tokenize=icu);

6.1. Параметры compress= и uncompress=

Параметры compress и uncompress позволяют хранить содержимое FTS4 в базе данных в сжатом виде. Для обоих параметров необходимо указать имя скалярной функции SQL, зарегистрированной с помощью sqlite3_create_function() и принимающей один аргумент.

Функция compress должна возвращать сжатую версию переданного ей значения. При каждой записи данных в таблицу FTS4 значение каждого столбца передаётся функции compress, а результат сохраняется в базе данных. Функция compress может возвращать значение любого типа SQLite (blob, text, real, integer или null).

Функция uncompress должна распаковывать данные, ранее сжатые функцией compress. Иными словами, для любых значений SQLite X должно выполняться равенство uncompress(compress(X)) = X. Когда FTS4 считывает из базы данных данные, сжатые функцией compress, перед использованием они передаются функции uncompress.

Если указанные функции compress или uncompress не существуют, таблицу всё равно можно создать. Ошибка возникнет только при чтении таблицы FTS4 (если не существует функция uncompress) или при записи в неё (если не существует функция compress).

-- Create an FTS4 table that stores data in compressed form. This
-- assumes that the scalar functions zip() and unzip() have been (or
-- will be) added to the database handle.
CREATE VIRTUAL TABLE papers USING fts4(author, document, compress=zip, uncompress=unzip);

При реализации функций compress и uncompress важно учитывать типы данных. В частности, когда пользователь считывает значение из сжатой таблицы FTS, возвращаемое FTS значение в точности совпадает со значением, возвращаемым функцией uncompress, включая тип данных. Если этот тип отличается от типа данных исходного значения, переданного функции compress (например, если функция uncompress возвращает BLOB, хотя функции compress изначально был передан TEXT), запрос пользователя может работать не так, как ожидалось.

6.2. Параметр content=

Параметр content позволяет FTS4 не хранить индексируемый текст. Его можно использовать двумя способами:

• Индексируемые документы вообще не хранятся в базе данных SQLite (таблица FTS4 без содержимого), или

• Индексируемые документы хранятся в таблице базы данных, создаваемой и управляемой пользователем (таблица FTS4 с внешним содержимым).

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

6.2.1. Таблицы FTS4 без содержимого

Чтобы создать таблицу FTS4, в которой не хранится копия индексируемых документов, параметру content следует присвоить пустую строку. Например, следующий оператор SQL создаёт такую таблицу FTS4 с тремя столбцами — "a", "b" и "c":

CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, c);

В такую таблицу FTS4 можно добавлять данные с помощью операторов INSERT. Однако, в отличие от обычных таблиц FTS4, пользователь должен явно указать целочисленное значение docid. Например:

-- This statement is Ok:
INSERT INTO t1(docid, a, b, c) VALUES(1, 'a b c', 'd e f', 'g h i');

-- This statement causes an error, as no docid value has been provided:
INSERT INTO t1(a, b, c) VALUES('j k l', 'm n o', 'p q r');

Обновлять или удалять строки, хранящиеся в таблице FTS4 без содержимого, невозможно. Попытка выполнить это приведёт к ошибке.

Таблицы FTS4 без содержимого также поддерживают операторы SELECT. Однако попытка получить значение любого столбца таблицы, кроме столбца docid, приведёт к ошибке. Функцию matchinfo() использовать можно, а функции snippet() и offsets() — нельзя. Например:

-- The following statements are Ok:
SELECT docid FROM t1 WHERE t1 MATCH 'xxx';
SELECT docid FROM t1 WHERE a MATCH 'xxx';
SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'xxx';

-- The following statements all cause errors, as the value of columns
-- other than docid are required to evaluate them.
SELECT * FROM t1;
SELECT a, b FROM t1 WHERE t1 MATCH 'xxx';
SELECT docid FROM t1 WHERE a LIKE 'xxx%';
SELECT snippet(t1) FROM t1 WHERE t1 MATCH 'xxx';

Ошибки, связанные с попыткой получить значения столбцов, отличных от docid, возникают во время выполнения sqlite3_step(). В некоторых случаях, например если выражение MATCH в запросе SELECT не соответствует ни одной строке, ошибки может не быть, даже если оператор обращается к значениям столбцов, отличных от docid.

6.2.2. Таблицы FTS4 с внешним содержимым

Таблица FTS4 с внешним содержимым похожа на таблицу без содержимого, за исключением того, что если для вычисления запроса требуется значение столбца, отличного от docid, FTS4 пытается получить его из таблицы (или представления, или виртуальной таблицы), указанной пользователем (далее — «таблица содержимого»). Модуль FTS4 никогда не записывает данные в таблицу содержимого, а запись данных в таблицу содержимого не влияет на полнотекстовый индекс. Пользователь отвечает за согласованность таблицы содержимого и полнотекстового индекса.

Таблица FTS4 с внешним содержимым создаётся присвоением параметру content имени таблицы (или представления, или виртуальной таблицы), из которой FTS4 при необходимости может получать значения столбцов. Если указанная таблица не существует, таблица с внешним содержимым ведёт себя как таблица без содержимого. Например:

CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
CREATE VIRTUAL TABLE t3 USING fts4(content="t2", a, c);

Если указанная таблица существует, её столбцы должны совпадать с набором столбцов таблицы FTS или включать этот набор. Внешняя таблица также должна находиться в том же файле базы данных, что и таблица FTS. Иными словами, внешняя таблица не может находиться в другом файле базы данных, подключённом с помощью ATTACH; кроме того, одна из таблиц — FTS или внешнего содержимого — не может находиться во временной базе данных TEMP, если другая находится в постоянном файле базы данных, например MAIN.

Когда запрос пользователя к таблице FTS требует значения столбца, отличного от docid, FTS пытается прочитать запрошенное значение из соответствующего столбца строки таблицы содержимого, rowid которой равен текущему docid FTS. Запрашивать можно только те столбцы таблицы содержимого, которые продублированы в объявлении таблицы FTS/34; чтобы получить значения из любых других столбцов, необходимо обращаться непосредственно к таблице содержимого. Если соответствующая строка не найдена в таблице содержимого, вместо значения используется NULL. Например:

CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c);

INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');
INSERT INTO t3(docid, b, c) SELECT id, b, c FROM t2;
-- The following query returns a single row with two columns containing
-- the text values "i j" and "k l".
--
-- The query uses the full-text index to discover that the MATCH
-- term matches the row with docid=3. It then retrieves the values
-- of columns b and c from the row with rowid=3 in the content table
-- to return.
--
SELECT * FROM t3 WHERE t3 MATCH 'k';

-- Following the UPDATE, the query still returns a single row, this
-- time containing the text values "xxx" and "yyy". This is because the
-- full-text index still indicates that the row with docid=3 matches
-- the FTS4 query 'k', even though the documents stored in the content
-- table have been modified.
--
UPDATE t2 SET b = 'xxx', c = 'yyy' WHERE rowid = 3;
SELECT * FROM t3 WHERE t3 MATCH 'k';

-- Following the DELETE below, the query returns one row containing two
-- NULL values. NULL values are returned because FTS is unable to find
-- a row with rowid=3 within the content table.
--
DELETE FROM t2;
SELECT * FROM t3 WHERE t3 MATCH 'k';

При удалении строки из таблицы FTS4 с внешним содержимым FTS4 необходимо получить значения столбцов удаляемой строки из таблицы содержимого. Это требуется для того, чтобы FTS4 мог обновить записи полнотекстового индекса для каждого токена, встречающегося в удаляемой строке, и отметить эту строку как удалённую. Если строку таблицы содержимого найти не удаётся или её значения не соответствуют содержимому индекса FTS, результат может быть трудно предсказать. В индексе FTS могут остаться записи, соответствующие удалённой строке, что может привести к внешне бессмысленным результатам последующих запросов SELECT. То же относится к обновлению строки, поскольку внутри UPDATE эквивалентен DELETE с последующим INSERT.

Это означает, что для синхронизации FTS с внешней таблицей содержимого операции UPDATE или DELETE необходимо сначала применять к таблице FTS, а затем к внешней таблице содержимого. Например:

CREATE TABLE t1_real(id INTEGER PRIMARY KEY, a, b, c, d);
CREATE VIRTUAL TABLE t1_fts USING fts4(content="t1_real", b, c);

-- This works. When the row is removed from the FTS table, FTS retrieves
-- the row with rowid=123 and tokenizes it in order to determine the entries
-- that must be removed from the full-text index.
--
DELETE FROM t1_fts WHERE rowid = 123;
DELETE FROM t1_real WHERE rowid = 123;

-- This does not work. By the time the FTS table is updated, the row
-- has already been deleted from the underlying content table. As a result
-- FTS is unable to determine the entries to remove from the FTS index and
-- so the index and content table are left out of sync.
--
DELETE FROM t1_real WHERE rowid = 123;
DELETE FROM t1_fts WHERE rowid = 123;

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

CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 BEGIN
  DELETE FROM t3 WHERE docid=old.rowid;
END;
CREATE TRIGGER t2_bd BEFORE DELETE ON t2 BEGIN
  DELETE FROM t3 WHERE docid=old.rowid;
END;

CREATE TRIGGER t2_au AFTER UPDATE ON t2 BEGIN
  INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
END;
CREATE TRIGGER t2_ai AFTER INSERT ON t2 BEGIN
  INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
END;

Триггер DELETE должен срабатывать до фактического удаления из таблицы содержимого. Это необходимо для того, чтобы FTS4 мог получить исходные значения и обновить полнотекстовый индекс. Триггер INSERT должен срабатывать после вставки новой строки, чтобы обрабатывать случаи, когда rowid назначается автоматически системой. По тем же причинам триггер UPDATE должен состоять из двух частей: одна срабатывает до обновления таблицы содержимого, а другая — после.

Команда FTS4 «rebuild» удаляет весь полнотекстовый индекс и перестраивает его на основе текущего набора документов в таблице содержимого. Если, как и ранее, «t3» — имя внешней таблицы FTS4 с содержимым, команда перестроения выглядит так:

INSERT INTO t3(t3) VALUES('rebuild');

Эту команду также можно использовать с обычными таблицами FTS4, например, если изменилась реализация токенизатора. Попытка перестроить полнотекстовый индекс, поддерживаемый таблицей FTS4 без содержимого, приводит к ошибке, поскольку для перестроения не будет доступно содержимое.

6.3. Параметр languageid=

Если указан параметр languageid, он задаёт имя другого скрытого столбца, который добавляется в таблицу FTS4 и используется для указания языка, хранящегося в каждой строке таблицы FTS4. Имя скрытого столбца languageid должно отличаться от имён всех остальных столбцов таблицы FTS4. Пример:

CREATE VIRTUAL TABLE t1 USING fts4(x, y, languageid="lid")

Значение по умолчанию для столбца languageid — 0. Любое значение, вставленное в столбец languageid, преобразуется в 32-разрядное (не 64-разрядное) целое число со знаком.

По умолчанию запросы FTS (использующие оператор MATCH) учитывают только строки, в которых столбец languageid имеет значение 0. Чтобы запросить строки с другими значениями languageid, в предложение WHERE запроса необходимо добавить ограничение вида «<language-id>=<integer>». Например:

SELECT * FROM t1 WHERE t1 MATCH 'abc' AND lid=5;

Один запрос FTS не может возвращать строки с разными значениями languageid. Результаты добавления предложений WHERE с другими операторами (например, lid!=5 или lid<=5) не определены.

Если параметр content используется вместе с параметром languageid, именованный столбец languageid должен существовать в таблице content= (с учётом обычных правил: если запросу никогда не требуется читать таблицу содержимого, это ограничение не применяется).

При использовании параметра languageid SQLite вызывает xLanguageid() для объекта sqlite3_tokenizer_module сразу после создания объекта, чтобы передать идентификатор языка, который должен использовать токенизатор. Метод xLanguageid() вызывается не более одного раза для любого отдельного объекта токенизатора. Различия в токенизации для разных языков — одна из причин, по которым один запрос FTS не может возвращать строки с разными значениями languageid.

6.4. Параметр matchinfo=

Параметру matchinfo можно присвоить только значение «fts3». Попытка задать matchinfo любое значение, отличное от «fts3», приводит к ошибке. Если этот параметр указан, часть дополнительных сведений, хранящихся в FTS4, не сохраняется. Это уменьшает объём дискового пространства, занимаемого таблицей FTS4, почти до объёма, который занимала бы соответствующая таблица FTS3, но также означает, что данные, доступные при передаче флага 'l' функции matchinfo(), недоступны.

6.5. Параметр notindexed=

Обычно модуль FTS поддерживает инвертированный индекс всех терминов во всех столбцах таблицы. Этот параметр задаёт имя столбца, записи которого не должны добавляться в индекс. Можно использовать несколько параметров «notindexed», чтобы исключить из индекса несколько столбцов. Например:

-- Create an FTS4 table for which only the contents of columns c2 and c4
-- are tokenized and added to the inverted index.
CREATE VIRTUAL TABLE t1 USING fts4(c1, c2, c3, c4, notindexed=c1, notindexed=c3);

Значения, хранящиеся в неиндексируемых столбцах, не могут соответствовать операторам MATCH. Они не влияют на результаты вспомогательных функций offsets() и matchinfo(). Кроме того, функция snippet() никогда не возвращает фрагмент, основанный на значении из неиндексируемого столбца.

6.6. Параметр prefix=

Параметр prefix FTS4 заставляет FTS индексировать префиксы терминов указанной длины так же, как обычно индексируются целые термины. Параметру prefix необходимо присвоить список положительных ненулевых целых чисел, разделённых запятыми. Для каждого значения N в списке индексируются префиксы длиной N байт (при кодировании в UTF-8). FTS4 использует индексы префиксов терминов для ускорения запросов по префиксу. Разумеется, платой за это становится увеличение размера базы данных и замедление операций записи в таблицу FTS4 из-за индексирования как префиксов терминов, так и целых терминов.

Индексы префиксов можно использовать для оптимизации запросов по префиксу в двух случаях. Если запрос выполняется по префиксу длиной N байт, индекс префиксов, созданный с помощью «prefix=N», обеспечивает наилучшую оптимизацию. Если же индекс «prefix=N» отсутствует, вместо него можно использовать индекс «prefix=N+1». Использование индекса «prefix=N+1» менее эффективно, чем индекса «prefix=N», но лучше, чем отсутствие индекса префиксов.

-- Create an FTS4 table with indexes to optimize 2 and 4 byte prefix queries.
CREATE VIRTUAL TABLE t1 USING fts4(c1, c2, prefix="2,4");

-- The following two queries are both optimized using the prefix indexes.
SELECT * FROM t1 WHERE t1 MATCH 'ab*';
SELECT * FROM t1 WHERE t1 MATCH 'abcd*';

-- The following two queries are both partially optimized using the prefix
-- indexes. The optimization is not as pronounced as it is for the queries
-- above, but still an improvement over no prefix indexes at all.
SELECT * FROM t1 WHERE t1 MATCH 'a*';
SELECT * FROM t1 WHERE t1 MATCH 'abc*';

7. Специальные команды FTS3 и FTS4

Для отправки команд таблицам FTS3 и FTS4 можно использовать специальные операции INSERT. У каждой таблицы FTS3 и FTS4 есть скрытый столбец только для чтения с тем же именем, что и у самой таблицы. Операции INSERT для этого скрытого столбца интерпретируются как команды для таблицы FTS3/4. Для таблицы с именем «xyz» поддерживаются следующие команды:

• INSERT INTO xyz(xyz) VALUES('optimize');

• INSERT INTO xyz(xyz) VALUES('rebuild');

• INSERT INTO xyz(xyz) VALUES('integrity-check');

• INSERT INTO xyz(xyz) VALUES('merge=X,Y');

• INSERT INTO xyz(xyz) VALUES('automerge=N');

7.1. Команда «optimize»

Команда «optimize» заставляет FTS3/4 объединить все свои b-деревья инвертированного индекса в одно большое и полное b-дерево. Выполнение optimize ускорит последующие запросы, поскольку для поиска потребуется меньше b-деревьев, а также может уменьшить использование диска за счёт объединения повторяющихся записей. Однако для большой таблицы FTS выполнение optimize может быть столь же затратным, как и выполнение VACUUM. По сути, команда optimize должна прочитать и записать всю таблицу FTS, что приводит к созданию большой транзакции.

При пакетной обработке, когда таблица FTS изначально заполняется большим числом операций INSERT, а затем многократно запрашивается без дальнейших изменений, часто имеет смысл выполнить «optimize» после последней операции INSERT и до первого запроса.

7.2. Команда «rebuild»

Команда «rebuild» заставляет SQLite удалить всю таблицу FTS3/4, а затем заново построить её из исходного текста. По смыслу она похожа на REINDEX, но применяется к таблице FTS3/4, а не к обычному индексу.

Команду «rebuild» следует выполнять при каждом изменении реализации пользовательского токенизатора, чтобы повторно токенизировать всё содержимое. Команда «rebuild» также полезна при использовании параметра content FTS4 после внесения изменений в исходную таблицу содержимого.

7.3. Команда «integrity-check»

Команда «integrity-check» заставляет SQLite прочитать и проверить точность всех инвертированных индексов в таблице FTS3/4, сравнив их с исходным содержимым. Команда «integrity-check» завершается без сообщений, если все инвертированные индексы в порядке, но при обнаружении проблем завершается ошибкой SQLITE_CORRUPT.

По смыслу команда «integrity-check» похожа на PRAGMA integrity_check. В работающей системе команда «integrity-command» всегда должна завершаться успешно. Возможные причины сбоев integrity-check:

• Приложение напрямую изменило теневые таблицы FTS, не используя виртуальную таблицу FTS3/4, из-за чего теневые таблицы рассинхронизировались.
• Использование параметра content FTS4 без ручной синхронизации содержимого с инвертированными индексами FTS4.
• Ошибки в виртуальной таблице FTS3/4. (Изначально команда «integrity-check» была задумана как часть набора тестов FTS3/4.)
• Повреждение базового файла базы данных SQLite. (Дополнительные сведения см. в документации о том, как повредить базу данных SQLite.)

7.4. Команда «merge=X,Y»

Команда «merge=X,Y» (где X и Y — целые числа) заставляет SQLite выполнить ограниченный объём работы по объединению различных b-деревьев инвертированного индекса таблицы FTS3/4 в одно большое b-дерево. Значение X — целевое число объединяемых «блоков», а Y — минимальное число сегментов b-дерева на уровне, необходимое для запуска объединения на этом уровне. Значение Y должно находиться в диапазоне от 2 до 16; рекомендуемое значение — 8. Значение X может быть любым положительным целым числом, однако рекомендуется выбирать значения примерно от 100 до 300.

Когда в таблице FTS накапливается 16 сегментов b-дерева на одном уровне, следующая операция INSERT в эту таблицу приводит к объединению всех 16 сегментов в один сегмент b-дерева на следующем, более высоком уровне. В результате таких объединений уровней большинство операций INSERT в таблицу FTS выполняются очень быстро и требуют мало памяти, но иногда операция INSERT оказывается медленной и создаёт большую транзакцию из-за необходимости объединения. Это приводит к «скачкообразной» производительности INSERT.

Чтобы избежать скачков производительности INSERT, приложение может периодически выполнять команду «merge=X,Y», возможно, в неактивном потоке или процессе, чтобы в таблице FTS никогда не накапливалось слишком много сегментов b-дерева на одном уровне. Скачков производительности INSERT обычно можно избежать, а производительность FTS3/4 — максимально повысить, выполняя «merge=X,Y» после каждой тысячи или нескольких тысяч вставленных документов. Каждая команда «merge=X,Y» выполняется в отдельной транзакции (если, конечно, команды не сгруппированы с помощью BEGIN...COMMIT). Транзакции можно сделать небольшими, выбрав значение X в диапазоне от 100 до 300. Неактивный поток, выполняющий команды слияния, может определить завершение работы, сравнивая разницу в sqlite3_total_changes() до и после каждой команды «merge=X,Y» и прекращая цикл, когда разница становится меньше двух.

7.5. Команда «automerge=N»

Команда «automerge=N» (где N — целое число от 0 до 15 включительно) используется для настройки параметра «automerge» таблиц FTS3/4, который управляет автоматическим инкрементальным объединением инвертированных индексов. Значение automerge по умолчанию для новых таблиц равно 0, то есть автоматическое инкрементальное объединение полностью отключено. Если значение параметра automerge изменяется с помощью команды «automerge=N», новое значение параметра сохраняется в базе данных и используется всеми последующими подключениями к базе данных.

Установка ненулевого значения параметра automerge включает автоматическое инкрементальное объединение. Это заставляет SQLite выполнять небольшое объединение инвертированного индекса после каждой операции INSERT. Объём выполняемого объединения рассчитан так, чтобы таблица FTS3/4 никогда не достигала состояния, при котором на одном уровне находится 16 сегментов и для завершения вставки требуется выполнить большое объединение. Иными словами, автоматическое инкрементальное объединение предназначено для предотвращения скачков производительности INSERT.

Недостаток автоматического инкрементального объединения заключается в том, что каждая операция INSERT, UPDATE и DELETE в таблице FTS3/4 выполняется немного медленнее, поскольку для инкрементального объединения требуется дополнительное время. Для достижения максимальной производительности рекомендуется отключить автоматическое инкрементальное объединение и вместо этого использовать команду «merge» в неактивном процессе, чтобы поддерживать хорошее состояние объединения инвертированных индексов. Однако если структура приложения не позволяет легко использовать неактивные процессы, автоматическое инкрементальное объединение — вполне разумное запасное решение.

Фактическое значение параметра automerge определяет число сегментов индекса, одновременно объединяемых при автоматическом объединении инвертированного индекса. Если значение равно N, система ждёт, пока на одном уровне не окажется как минимум N сегментов, прежде чем начать их инкрементальное объединение. Чем меньше значение N, тем быстрее объединяются сегменты; это может ускорить полнотекстовые запросы и, если рабочая нагрузка включает операции UPDATE или DELETE наряду с INSERT, уменьшить объём дискового пространства, занимаемого полнотекстовым индексом. Однако это также увеличивает объём данных, записываемых на диск.

Для общего использования в случаях, когда рабочая нагрузка включает мало операций UPDATE или DELETE, подходящим значением automerge будет 8. Если рабочая нагрузка включает много команд UPDATE или DELETE либо важна скорость запросов, может быть полезно уменьшить automerge до 2.

В целях обратной совместимости команда «automerge=1» устанавливает параметру automerge значение 8, а не 1 (значение 1 всё равно не имело бы смысла, поскольку объединение данных из одного сегмента ничего не меняет).

8. Токенизаторы

Токенизатор FTS — это набор правил извлечения терминов из документа или простого полнотекстового запроса FTS.

Если в операторе CREATE VIRTUAL TABLE, используемом для создания таблицы FTS, не указан конкретный токенизатор, применяется токенизатор по умолчанию — «simple». Токенизатор simple извлекает токены из документа или простого полнотекстового запроса FTS по следующим правилам:

• Термин — это непрерывная последовательность допустимых символов. Допустимыми считаются все буквенно-цифровые символы и все символы со значениями кодовых точек Unicode не меньше 128. При разбиении документа на термины все остальные символы отбрасываются. Их единственная функция — разделять соседние термины.

• Все символы верхнего регистра в диапазоне ASCII (кодовые точки Unicode меньше 128) в процессе токенизации преобразуются в соответствующие символы нижнего регистра. Таким образом, при использовании токенизатора simple полнотекстовые запросы нечувствительны к регистру.

Например, если документ содержит текст «Right now, they're very frustrated.», из него извлекаются и добавляются в полнотекстовый индекс следующие термины, в указанном порядке: «right now they re very frustrated». Такой документ будет соответствовать полнотекстовому запросу, например «MATCH 'Frustrated'», поскольку токенизатор simple преобразует термин в запросе в нижний регистр перед поиском в полнотекстовом индексе.

Помимо токенизатора «simple», в исходном коде FTS есть токенизатор, использующий алгоритм стемминга Портера. Этот токенизатор использует те же правила разделения входного документа на термины, включая преобразование всех терминов в нижний регистр, но также применяет алгоритм стемминга Портера, чтобы свести родственные английские слова к общей основе. Например, для того же входного документа, что и в предыдущем абзаце, токенизатор porter извлекает следующие токены: «right now thei veri frustrat». Хотя некоторые из этих терминов даже не являются английскими словами, в некоторых случаях их использование для построения полнотекстового индекса полезнее, чем более понятный результат токенизатора simple. При использовании токенизатора porter документ соответствует не только полнотекстовым запросам, например «MATCH 'Frustrated'», но и запросам, например «MATCH 'Frustration'», поскольку алгоритм стемминга Портера сводит термин «Frustration» к «frustrat» — так же, как и «Frustrated». Таким образом, при использовании токенизатора porter FTS может находить не только точные совпадения запрошенных терминов, но и совпадения с похожими английскими терминами. Дополнительные сведения об алгоритме стемминга Портера см. на странице по ссылке выше.

Пример, демонстрирующий различия между токенизаторами «simple» и «porter»:

-- Create a table using the simple tokenizer. Insert a document into it.
CREATE VIRTUAL TABLE simple USING fts3(tokenize=simple);
INSERT INTO simple VALUES('Right now they''re very frustrated');

-- The first of the following two queries matches the document stored in
-- table "simple". The second does not.
SELECT * FROM simple WHERE simple MATCH 'Frustrated';
SELECT * FROM simple WHERE simple MATCH 'Frustration';

-- Create a table using the porter tokenizer. Insert the same document into it
CREATE VIRTUAL TABLE porter USING fts3(tokenize=porter);
INSERT INTO porter VALUES('Right now they''re very frustrated');

-- Both of the following queries match the document stored in table "porter".
SELECT * FROM porter WHERE porter MATCH 'Frustrated';
SELECT * FROM porter WHERE porter MATCH 'Frustration';

Если это расширение скомпилировано с определённым символом препроцессора SQLITE_ENABLE_ICU, становится доступен встроенный токенизатор с именем «icu», реализованный с помощью библиотеки ICU. Первый аргумент, передаваемый методу xCreate() (см. fts3_tokenizer.h) этого токенизатора, может быть идентификатором локали ICU. Например, «tr_TR» для турецкого языка в Турции или «en_AU» для английского языка в Австралии. Например:

CREATE VIRTUAL TABLE thai_text USING fts3(text, tokenize=icu th_TH)

Реализация токенизатора ICU очень проста. Она разделяет входной текст согласно правилам ICU для определения границ слов и отбрасывает токены, состоящие только из пробельных символов. В некоторых приложениях и локалях этого может быть достаточно, но не во всех. Если требуется более сложная обработка, например стемминг или удаление знаков препинания, можно создать реализацию токенизатора, использующую токенизатор ICU как часть своей реализации.

Токенизатор «unicode61» доступен начиная с версии 3.7.13 SQLite (2012-06-11). Unicode61 во многом похож на «simple», однако выполняет простое преобразование регистра Unicode согласно правилам Unicode версии 6.1, распознаёт символы пробела и пунктуации Unicode и использует их для разделения токенов. Токенизатор simple преобразует регистр только для символов ASCII и распознаёт в качестве разделителей токенов только пробелы и знаки препинания ASCII.

По умолчанию «unicode61» пытается удалять диакритические знаки у символов латинского письма. Это поведение можно изменить, добавив аргумент токенизатора «remove_diacritics=0». Например:

-- Create tables that remove alldiacritics from Latin script characters
-- as part of tokenization.
CREATE VIRTUAL TABLE txt1 USING fts4(tokenize=unicode61);
CREATE VIRTUAL TABLE txt2 USING fts4(tokenize=unicode61 "remove_diacritics=2");

-- Create a table that does not remove diacritics from Latin script
-- characters as part of tokenization.
CREATE VIRTUAL TABLE txt3 USING fts4(tokenize=unicode61 "remove_diacritics=0");

Параметру remove_diacritics можно присвоить значение «0», «1» или «2». Значение по умолчанию — «1». Если задано значение «1» или «2», диакритические знаки у символов латинского письма удаляются, как описано выше. Однако при значении «1» диакритические знаки не удаляются в довольно редком случае, когда для представления символа с несколькими диакритическими знаками используется одна кодовая точка Unicode. Например, диакритические знаки не удаляются у кодовой точки 0x1ED9 («LATIN SMALL LETTER O WITH CIRCUMFLEX AND DOT BELOW»). Формально это ошибка, но её нельзя исправить без нарушения обратной совместимости. Если этому параметру присвоено значение «2», диакритические знаки корректно удаляются у всех символов латинского письма.

Также можно настроить набор кодовых точек, которые unicode61 считает символами-разделителями. Параметр «separators=» позволяет указать один или несколько дополнительных символов, которые следует считать разделителями, а параметр «tokenchars=» — один или несколько дополнительных символов, которые следует считать частью токенов, а не разделителями. Например:

-- Create a table that uses the unicode61 tokenizer, but considers "."
-- and "=" characters to be part of tokens, and capital "X" characters to
-- function as separators.
CREATE VIRTUAL TABLE txt3 USING fts4(tokenize=unicode61 "tokenchars=.=" "separators=X");

-- Create a table that considers space characters (codepoint 32) to be
-- a token character
CREATE VIRTUAL TABLE txt4 USING fts4(tokenize=unicode61 "tokenchars= ");

Если символ, указанный в аргументе «tokenchars=», по умолчанию считается символом токена, он игнорируется. Это верно даже в том случае, если более ранний параметр «separators=» обозначил его как разделитель. Аналогично, если символ, указанный в параметре «separators=», по умолчанию считается разделителем, он игнорируется. Если указано несколько параметров «tokenchars=» или «separators=», обрабатываются все они. Например:

-- Create a table that uses the unicode61 tokenizer, but considers "."
-- and "=" characters to be part of tokens, and capital "X" characters to
-- function as separators. Both of the "tokenchars=" options are processed
-- The "separators=" option ignores the "." passed to it, as "." is by
-- default a separator character, even though it has been marked as a token
-- character by an earlier "tokenchars=" option.
CREATE VIRTUAL TABLE txt5 USING fts4(
    tokenize=unicode61 "tokenchars=." "separators=X." "tokenchars=="
);

Аргументы параметров «tokenchars=» и «separators=» чувствительны к регистру. В приведённом выше примере указание «X» в качестве символа-разделителя не влияет на обработку «x».

8.1. Пользовательские токенизаторы, определяемые приложением

Помимо встроенных токенизаторов «simple», «porter» и (возможно) «icu» и «unicode61», FTS предоставляет интерфейс для реализации и регистрации приложениями пользовательских токенизаторов на языке C. Интерфейс создания нового токенизатора определён и описан в исходном файле fts3_tokenizer.h.

Регистрация нового токенизатора FTS похожа на регистрацию нового модуля виртуальной таблицы в SQLite. Пользователь передаёт указатель на структуру, содержащую указатели на различные функции обратного вызова, составляющие реализацию нового типа токенизатора. Для токенизаторов эта структура (определённая в fts3_tokenizer.h) называется «sqlite3_tokenizer_module».

В FTS нет функции C, которую пользователи могли бы вызвать для регистрации новых типов токенизаторов с дескриптором базы данных. Вместо этого указатель необходимо закодировать как значение BLOB SQL и передать FTS через SQL-движок, вызвав специальную скалярную функцию «fts3_tokenizer()». Функцию fts3_tokenizer() можно вызвать с одним или двумя аргументами следующим образом:

SELECT fts3_tokenizer(<tokenizer-name>);
SELECT fts3_tokenizer(<tokenizer-name>, <sqlite3_tokenizer_module ptr>);

Здесь <tokenizer-name> — это параметр, которому привязывается строка с помощью sqlite3_bind_text(); строка задаёт имя токенизатора. <sqlite3_tokenizer_module ptr> — это параметр, которому привязывается BLOB с помощью sqlite3_bind_blob(); значение BLOB представляет собой указатель на структуру sqlite3_tokenizer_module. Если указан второй аргумент, он регистрируется как токенизатор <tokenizer-name>, и возвращается его копия. Если передан только один аргумент, возвращается указатель на реализацию токенизатора, зарегистрированную в данный момент под именем <tokenizer-name>, в виде BLOB. Если такого токенизатора нет, возникает исключение SQL (ошибка).

До версии 3.11.0 SQLite (2016-02-15) аргументами fts3_tokenizer() могли быть строковые литералы или BLOB. Они не обязательно должны были быть связанными параметрами. Однако это могло создавать проблемы безопасности при атаке с внедрением SQL. Поэтому такое устаревшее поведение теперь по умолчанию отключено. Для обратной совместимости в приложениях, которым оно действительно необходимо, его можно включить вызовом sqlite3_db_config(db,SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER,1,0).

Ниже приведён пример вызова функции fts3_tokenizer() из кода C:

/*
** Register a tokenizer implementation with FTS3 or FTS4.
*/
int registerTokenizer(
  sqlite3 *db,
  char *zName,
  const sqlite3_tokenizer_module *p
){
  int rc;
  sqlite3_stmt *pStmt;
  const char *zSql = "SELECT fts3_tokenizer(?1, ?2)";

  rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
  if( rc!=SQLITE_OK ){
    return rc;
  }

  sqlite3_bind_text(pStmt, 1, zName, -1, SQLITE_STATIC);
  sqlite3_bind_blob(pStmt, 2, &p, sizeof(p), SQLITE_STATIC);
  sqlite3_step(pStmt);

  return sqlite3_finalize(pStmt);
}

/*
** Query FTS for the tokenizer implementation named zName.
*/
int queryTokenizer(
  sqlite3 *db,
  char *zName,
  const sqlite3_tokenizer_module **pp
){
  int rc;
  sqlite3_stmt *pStmt;
  const char *zSql = "SELECT fts3_tokenizer(?)";

  *pp = 0;
  rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
  if( rc!=SQLITE_OK ){
    return rc;
  }

  sqlite3_bind_text(pStmt, 1, zName, -1, SQLITE_STATIC);
  if( SQLITE_ROW==sqlite3_step(pStmt) ){
    if( sqlite3_column_type(pStmt, 0)==SQLITE_BLOB ){
      memcpy(pp, sqlite3_column_blob(pStmt, 0), sizeof(*pp));
    }
  }

  return sqlite3_finalize(pStmt);
}

8.2. Запрос токенизаторов

Виртуальную таблицу «fts3tokenize» можно использовать для непосредственного доступа к любому токенизатору. В следующем примере SQL показано, как создать экземпляр виртуальной таблицы fts3tokenize:

CREATE VIRTUAL TABLE tok1 USING fts3tokenize('porter');

Разумеется, в примере вместо «porter» следует указать имя нужного токенизатора. Если токенизатору требуется один или несколько аргументов, их следует разделять запятыми в объявлении fts3tokenize (хотя в объявлениях обычных таблиц fts4 они разделяются пробелами). Следующий пример создаёт таблицы fts4 и fts3tokenize, использующие один и тот же токенизатор:

CREATE VIRTUAL TABLE text1 USING fts4(tokenize=icu en_AU);
CREATE VIRTUAL TABLE tokens1 USING fts3tokenize(icu, en_AU);

CREATE VIRTUAL TABLE text2 USING fts4(tokenize=unicode61 "tokenchars=@." "separators=123");
CREATE VIRTUAL TABLE tokens2 USING fts3tokenize(unicode61, "tokenchars=@.", "separators=123");

После создания виртуальной таблицы можно выполнить следующий запрос:

SELECT token, start, end, position
  FROM tok1
 WHERE input='This is a test sentence.';

Виртуальная таблица возвращает по одной строке результата для каждого токена во входной строке. Столбец «token» содержит текст токена. Столбцы «start» и «end» содержат байтовые смещения начала и конца токена в исходной входной строке. Столбец «position» содержит порядковый номер токена в исходной входной строке. Также имеется столбец «input», который просто содержит копию входной строки, указанной в предложении WHERE. Обратите внимание: в предложении WHERE должно присутствовать ограничение вида «input=?», иначе виртуальной таблице будет нечего токенизировать и она не вернёт ни одной строки. Приведённый выше пример формирует следующий результат:

thi|0|4|0
is|5|7|1
a|8|9|2
test|10|14|3
sentenc|15|23|4

Обратите внимание, что токены в результирующем наборе виртуальной таблицы fts3tokenize преобразованы согласно правилам токенизатора. Поскольку в этом примере использовался токенизатор «porter», токен «This» был преобразован в «thi». Если нужен исходный текст токена, его можно получить с помощью столбцов «start» и «end» и функции substr(). Например:

SELECT substr(input, start+1, end-start), token, position
  FROM tok1
 WHERE input='This is a test sentence.';

Виртуальную таблицу fts3tokenize можно использовать с любым токенизатором, независимо от того, существует ли таблица FTS3 или FTS4, фактически использующая этот токенизатор.

9. Структуры данных

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

9.1. Теневые таблицы

Для каждой виртуальной таблицы FTS в базе данных создаются от трёх до пяти обычных (не виртуальных) таблиц для хранения базовых данных. Эти обычные таблицы называются «теневыми таблицами». Они имеют имена «%_content», «%_segdir», «%_segments», «%_stat» и «%_docsize», где «%» заменяется именем виртуальной таблицы FTS.

Крайний левый столбец таблицы «%_content» — это поле INTEGER PRIMARY KEY с именем «docid». За ним следует по одному столбцу для каждого столбца виртуальной таблицы FTS, объявленного пользователем. Имя каждого такого столбца формируется добавлением «cN» перед именем столбца, заданным пользователем, где N — индекс столбца в таблице, отсчитываемый слева направо начиная с 0. Типы данных, указанные в объявлении виртуальной таблицы, не используются в объявлении таблицы %_content. Например:

-- Virtual table declaration
CREATE VIRTUAL TABLE abc USING fts4(a NUMBER, b TEXT, c);

-- Corresponding %_content table declaration
CREATE TABLE abc_content(docid INTEGER PRIMARY KEY, c0a, c1b, c2c);

Таблица %_content содержит данные без изменений, вставленные пользователем в виртуальную таблицу FTS. Если при вставке записей пользователь явно не указывает значение «docid», система выбирает его автоматически.

Таблицы %_stat и %_docsize создаются только в том случае, если таблица FTS использует модуль FTS4, а не FTS3. Кроме того, таблица %_docsize не создаётся, если таблица FTS4 создана с директивой «matchinfo=fts3» в операторе CREATE VIRTUAL TABLE. Если эти таблицы создаются, их схемы выглядят следующим образом:

CREATE TABLE %_stat(
  id INTEGER PRIMARY KEY,
  value BLOB
);

CREATE TABLE %_docsize(
  docid INTEGER PRIMARY KEY,
  size BLOB
);

Для каждой строки таблицы FTS в таблице %_docsize имеется соответствующая строка с тем же значением «docid». Поле «size» содержит BLOB, состоящий из N varint FTS, где N — число определённых пользователем столбцов таблицы. Каждая varint в BLOB «size» содержит число токенов в соответствующем столбце связанной строки таблицы FTS. Таблица %_stat всегда содержит одну строку, в которой для столбца «id» задано значение 0. Столбец «value» содержит BLOB, состоящий из N+1 varint FTS, где N — также число определённых пользователем столбцов таблицы FTS. Первая varint в BLOB содержит общее число строк в таблице FTS. Вторая и последующие varint содержат общее число токенов, хранящихся в соответствующем столбце всех строк таблицы FTS.

Две оставшиеся таблицы, %_segments и %_segdir, используются для хранения полнотекстового индекса. Концептуально этот индекс представляет собой таблицу поиска, сопоставляющую каждому термину (слову) набор значений docid, соответствующих записям в таблице %_content, содержащим одно или несколько вхождений этого термина. Чтобы получить все документы, содержащие заданный термин, модуль FTS запрашивает этот индекс, определяет набор значений docid записей, содержащих термин, а затем извлекает нужные документы из таблицы %_content. Независимо от схемы виртуальной таблицы FTS, таблицы %_segments и %_segdir всегда создаются следующим образом:

CREATE TABLE %_segments(
  blockid INTEGER PRIMARY KEY,       -- B-tree node id
  block blob                         -- B-tree node data
);

CREATE TABLE %_segdir(
  level INTEGER,
  idx INTEGER,
  start_block INTEGER,               -- Blockid of first node in %_segments
  leaves_end_block INTEGER,          -- Blockid of last leaf node in %_segments
  end_block INTEGER,                 -- Blockid of last node in %_segments
  root BLOB,                         -- B-tree root node
  PRIMARY KEY(level, idx)
);

Представленная выше схема не предназначена для непосредственного хранения полнотекстового индекса. Вместо этого она используется для хранения одной или нескольких структур b-деревьев. Для каждой строки таблицы %_segdir создаётся одно b-дерево. Строка таблицы %_segdir содержит корневой узел и различные метаданные, связанные со структурой b-дерева, а таблица %_segments содержит все остальные (некорневые) узлы b-дерева. Каждое b-дерево называется «сегментом». После создания сегментное b-дерево никогда не обновляется (хотя оно может быть удалено целиком).

В качестве ключей каждого сегментного b-дерева используются термины (слова). Помимо ключа, каждой записи сегментного b-дерева соответствует «doclist» (список документов). Doclist состоит из нуля или более элементов, каждый из которых содержит:

• docid (идентификатор документа); и
• список смещений термина — по одному для каждого вхождения термина в документ. Смещение термина указывает число токенов (слов), стоящих перед этим термином, а не число символов или байтов. Например, смещение термина «war» во фразе «Ancestral voices prophesying war!» равно 3.

Элементы doclist отсортированы по docid. Позиции внутри элемента doclist хранятся в порядке возрастания.

Содержимое логического полнотекстового индекса определяется объединением содержимого всех сегментных b-деревьев. Если термин присутствует более чем в одном сегментном b-дереве, он сопоставляется объединению соответствующих списков doclist. Если для одного термина один и тот же docid встречается более чем в одном списке doclist, действительным считается только список doclist из сегментного b-дерева, созданного последним.

Для снижения затрат на вставку записей в таблицы FTS используется несколько структур B-дерева вместо одного B-дерева. Когда новая запись вставляется в таблицу FTS, уже содержащую большой объём данных, вероятно, что многие термины в новой записи уже присутствуют во множестве существующих записей. Если бы использовалось одно B-дерево, большие структуры doclist пришлось бы загружать из базы данных, изменять, добавляя новый docid и список смещений термина, а затем записывать обратно в базу данных. Использование нескольких таблиц B-деревьев позволяет избежать этого: создаётся новое B-дерево, которое можно объединить с существующим B-деревом (или деревьями) позже. Объединение структур B-деревьев можно выполнять в фоновом режиме или после накопления определённого количества отдельных структур B-деревьев. Конечно, эта схема делает запросы более затратными (поскольку коду FTS может потребоваться искать отдельные термины более чем в одном B-дереве и объединять результаты), но на практике эти накладные расходы часто оказываются незначительными.

9.2. Формат целых чисел переменной длины (varint)

Целочисленные значения, хранящиеся в узлах сегментного B-дерева, кодируются с использованием формата varint FTS. Это кодирование похоже, но не идентично формату varint SQLite.

Закодированное значение varint FTS занимает от одного до десяти байтов. Необходимое количество байтов определяется знаком и величиной кодируемого целочисленного значения. Точнее, количество байтов, используемых для хранения закодированного целого числа, зависит от положения старшего установленного бита в 64-битном представлении целочисленного значения в дополнительном коде. У отрицательных значений старший бит всегда установлен (это знаковый бит), поэтому для их хранения всегда используются все десять байтов. Положительные целочисленные значения могут занимать меньше места.

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

9.3. Формат сегментного B-дерева

Сегментные B-деревья — это B+-деревья с префиксным сжатием. Для каждой строки таблицы %_segdir существует одно сегментное B-дерево (см. выше). Корневой узел сегментного B-дерева хранится в виде blob в поле "root" соответствующей строки таблицы %_segdir. Все остальные узлы (если они есть) хранятся в столбце "blob" таблицы %_segments. Узлы в таблице %_segments идентифицируются целочисленным значением в поле blockid соответствующей строки. В следующей таблице описаны поля таблицы %_segdir:

За исключением корневого узла, узлы, составляющие одно сегментное B-дерево, всегда хранятся в непрерывной последовательности blockid. Кроме того, узлы одного уровня B-дерева сами хранятся в виде непрерывного блока в порядке B-дерева. Непрерывная последовательность blockid, используемая для хранения листьев B-дерева, выделяется начиная со значения blockid, хранящегося в столбце "start_block" соответствующей строки %_segdir, и заканчивая значением blockid, хранящимся в поле "leaves_end_block" той же строки. Поэтому можно перебрать все листья сегментного B-дерева в порядке ключей, просматривая таблицу %_segments в порядке blockid от "start_block" до "leaves_end_block".

9.3.1. Листовые узлы сегментного B-дерева

На следующей схеме показан формат листового узла сегментного B-дерева.

Формат листового узла сегментного B-дерева

Первый термин, хранящийся в каждом узле ("Term 1" на схеме выше), хранится без изменений. Каждый последующий термин кодируется с префиксным сжатием относительно предыдущего. Термины внутри страницы хранятся в отсортированном порядке (memcmp).

9.3.2. Внутренние узлы сегментного B-дерева

На следующей схеме показан формат внутреннего (нелистового) узла сегментного B-дерева.

Формат внутреннего узла сегментного B-дерева

9.4. Формат doclist

Doclist состоит из массива 64-битных знаковых целых чисел, сериализованных с использованием формата varint FTS. Каждая запись doclist состоит из двух или более целых чисел, а именно:

1. Значение docid. Первая запись doclist содержит буквальное значение docid. Первое поле каждой последующей записи doclist содержит разницу между новым docid и предыдущим (всегда положительное число).
2. Ноль или более списков смещений термина. Список смещений термина присутствует для каждого столбца виртуальной таблицы FTS, в котором содержится этот термин. Список смещений термина состоит из следующих элементов:
   1. Постоянное значение 1. Это поле опускается для любого списка смещений термина, связанного со столбцом 0.
   2. Номер столбца (1 — для второго столбца слева и т. д.). Это поле опускается для любого списка смещений термина, связанного со столбцом 0.
   3. Список смещений термина, отсортированных по возрастанию. Вместо буквального значения смещения термина сохраняется разница между текущим смещением термина и предыдущим (или нулём, если текущее смещение термина первое), увеличенная на 2.
3. Постоянное значение 0.

Формат doclist FTS3

Формат записи doclist FTS

В doclist для термина, встречающегося более чем в одном столбце виртуальной таблицы FTS, списки смещений термина хранятся в порядке номеров столбцов. Это гарантирует, что список смещений термина, связанный со столбцом 0 (если он есть), всегда будет первым, поэтому в этом случае первые два поля списка смещений термина можно опустить.

10. Ограничения

10.1. Проблема с маркером порядка байтов UTF-16

• Маркер порядка байтов UTF-16 (BOM) встроен в начало значения строкового литерала SQL, вставляемого в таблицу FTS3. Например:

  INSERT INTO fts_table(col) VALUES(char(0xfeff)||'text...');
  

• Некорректная последовательность UTF-8, которую SQLite преобразует в маркер порядка байтов UTF-16, встроена в начало значения строкового литерала SQL, вставляемого в таблицу FTS3.

• В таблицу FTS3 вставлено текстовое значение, созданное приведением типа blob, начинающегося с двух байтов 0xFF и 0xFE в любом из возможных порядков. Например:

  INSERT INTO fts_table(col) VALUES(CAST(X'FEFF' AS TEXT));
  

• Кодировка базы данных — UTF-8.
• Все текстовые строки вставляются с помощью одной из функций семейства sqlite3_bind_text().
• Строковые литералы не содержат маркеров порядка байтов.
• Используется токенизатор, распознающий маркеры порядка байтов как пробельные символы. (Токенизатор "simple" по умолчанию для FTS3/4 не считает BOM пробельным символом, а токенизатор unicode считает.)

Приложение A: Советы по созданию поисковых приложений

FTS в первую очередь предназначена для поддержки булевых полнотекстовых запросов — запросов, позволяющих найти набор документов, соответствующих заданным критериям. Однако многим (большинству?) поисковых приложений требуется каким-либо образом ранжировать результаты по «релевантности», где «релевантность» определяется как вероятность того, что пользователь, выполнивший поиск, заинтересован в конкретном элементе возвращённого набора документов. При использовании поисковой системы для поиска документов во всемирной сети пользователь ожидает, что наиболее полезные, или «релевантные», документы будут показаны на первой странице результатов, а на каждой следующей странице будут представлены результаты с постепенно снижающейся релевантностью. Точное определение того, как машина может оценить релевантность документа на основе запроса пользователя, — сложная проблема, которой посвящено множество текущих исследований.

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

-- This example (and all others in this section) assumes the following schema
CREATE VIRTUAL TABLE documents USING fts3(title, content);

-- Assuming the application has supplied an SQLite user function named "countintegers"
-- that returns the number of space-separated integers contained in its only argument,
-- the following query could be used to return the titles of the 10 documents that contain
-- the greatest number of instances of the users query terms. Hopefully, these 10
-- documents will be those that the users considers more or less the most "relevant".
SELECT title FROM documents
  WHERE documents MATCH <query>
  ORDER BY countintegers(offsets(documents)) DESC
  LIMIT 10 OFFSET 0

Приведённый выше запрос можно ускорить, используя функцию FTS matchinfo для определения количества вхождений терминов запроса в каждом результате. Функция matchinfo гораздо эффективнее функции offsets. Кроме того, функция matchinfo предоставляет дополнительную информацию об общем количестве вхождений каждого термина запроса во всём наборе документов (а не только в текущей строке) и о количестве документов, в которых встречается каждый термин запроса. Это можно использовать, например, чтобы придавать больший вес менее распространённым терминам, что может повысить общую вычисленную релевантность результатов, которые пользователь считает более интересными.

-- If the application supplies an SQLite user function called "rank" that
-- interprets the blob of data returned by matchinfo and returns a numeric
-- relevancy based on it, then the following SQL may be used to return the
-- titles of the 10 most relevant documents in the dataset for a users query.
SELECT title FROM documents
  WHERE documents MATCH <query>
  ORDER BY rank(matchinfo(documents)) DESC
  LIMIT 10 OFFSET 0

Приведённый выше SQL-запрос использует меньше ресурсов ЦП, чем первый пример в этом разделе, но всё ещё имеет неочевидную проблему с производительностью. SQLite выполняет этот запрос, получая значение столбца "title" и данные matchinfo из модуля FTS для каждой строки, соответствующей запросу пользователя, прежде чем сортировать и ограничивать результаты. Из-за особенностей работы интерфейса виртуальных таблиц SQLite получение значения столбца "title" требует загрузки всей строки с диска (включая поле "content", которое может быть довольно большим). Это означает, что если запрос пользователя соответствует нескольким тысячам документов, с диска в память может быть загружено много мегабайт данных "title" и "content", хотя они никогда не будут использоваться.

SQL-запрос в следующем блоке — один из способов решения этой проблемы. В SQLite, если подзапрос, используемый в соединении, содержит предложение LIMIT, результаты подзапроса вычисляются и сохраняются во временной таблице до выполнения основного запроса. Это означает, что SQLite загрузит в память только данные docid и matchinfo для каждой строки, соответствующей запросу пользователя, определит значения docid для десяти наиболее релевантных документов, а затем загрузит данные title и content только для этих 10 документов. Поскольку и значения matchinfo, и значения docid извлекаются исключительно из полнотекстового индекса, в результате из базы данных в память загружается значительно меньше данных.

SELECT title FROM documents JOIN (
    SELECT docid, rank(matchinfo(documents)) AS rank
    FROM documents
    WHERE documents MATCH <query>
    ORDER BY rank DESC
    LIMIT 10 OFFSET 0
) AS ranktable USING(docid)
ORDER BY ranktable.rank DESC

Следующий блок SQL дополняет запрос решениями ещё двух проблем, которые могут возникнуть при разработке поисковых приложений с использованием FTS:

1. Функцию snippet нельзя использовать с приведённым выше запросом. Поскольку внешний запрос не содержит предложения "WHERE ... MATCH", функцию snippet использовать нельзя. Один из вариантов решения — продублировать во внешнем запросе предложение WHERE, используемое подзапросом. Обычно связанные с этим накладные расходы незначительны.

2. Релевантность документа может зависеть не только от данных, доступных в возвращаемом значении matchinfo. Например, каждому документу в базе данных может быть назначен статический вес, основанный на факторах, не связанных с его содержимым (источник, автор, возраст, количество ссылок и т. д.). Приложение может хранить эти значения в отдельной таблице, которую можно объединить с таблицей документов в подзапросе, чтобы функция ранжирования могла получить к ним доступ.

Эта версия запроса очень похожа на ту, которая используется в приложении поиска по документации sqlite.org.

-- This table stores the static weight assigned to each document in FTS table
-- "documents". For each row in the documents table there is a corresponding row
-- with the same docid value in this table.
CREATE TABLE documents_data(docid INTEGER PRIMARY KEY, weight);

-- This query is similar to the one in the block above, except that:
--
--   1. It returns a "snippet" of text along with the document title for display. So
--      that the snippet function may be used, the "WHERE ... MATCH ..." clause from
--      the sub-query is duplicated in the outer query.
--
--   2. The sub-query joins the documents table with the document_data table, so that
--      implementation of the rank function has access to the static weight assigned
--      to each document.
SELECT title, snippet(documents) FROM documents JOIN (
    SELECT docid, rank(matchinfo(documents), documents_data.weight) AS rank
    FROM documents JOIN documents_data USING(docid)
    WHERE documents MATCH <query>
    ORDER BY rank DESC
    LIMIT 10 OFFSET 0
) AS ranktable USING(docid)
WHERE documents MATCH <query>
ORDER BY ranktable.rank DESC

Все приведённые выше примеры запросов возвращают десять наиболее релевантных результатов. Изменив значения в предложениях OFFSET и LIMIT, легко составить запрос, который вернёт, например, следующие десять наиболее релевантных результатов. Это можно использовать для получения данных, необходимых для второй и последующих страниц результатов поискового приложения.

Следующий блок содержит пример функции ранжирования, использующей данные matchinfo и реализованной на C. Вместо единого веса она позволяет назначить внешний вес каждому столбцу каждого документа. Её можно зарегистрировать в SQLite так же, как любую другую пользовательскую функцию, используя sqlite3_create_function.

Предупреждение о безопасности: поскольку rank() — обычная функция SQL, её можно вызвать в любом контексте в составе любого SQL-запроса. Это означает, что первый переданный аргумент может не быть допустимым blob matchinfo. Разработчики должны позаботиться об обработке такого случая, чтобы не допустить переполнения буфера и других потенциальных проблем безопасности.

/*
** SQLite user defined function to use with matchinfo() to calculate the
** relevancy of an FTS match. The value returned is the relevancy score
** (a real value greater than or equal to zero). A larger value indicates 
** a more relevant document.
**
** The overall relevancy returned is the sum of the relevancies of each 
** column value in the FTS table. The relevancy of a column value is the
** sum of the following for each reportable phrase in the FTS query:
**
**   (<hit count> / <global hit count>) * <column weight>
**
** where <hit count> is the number of instances of the phrase in the
** column value of the current row and <global hit count> is the number
** of instances of the phrase in the same column of all rows in the FTS
** table. The <column weight> is a weighting factor assigned to each
** column by the caller (see below).
**
** The first argument to this function must be the return value of the FTS 
** matchinfo() function. Following this must be one argument for each column 
** of the FTS table containing a numeric weight factor for the corresponding 
** column. Example:
**
**     CREATE VIRTUAL TABLE documents USING fts3(title, content)
**
** The following query returns the docids of documents that match the full-text
** query <query> sorted from most to least relevant. When calculating
** relevance, query term instances in the 'title' column are given twice the
** weighting of those in the 'content' column.
**
**     SELECT docid FROM documents 
**     WHERE documents MATCH <query> 
**     ORDER BY rank(matchinfo(documents), 1.0, 0.5) DESC
*/
static void rankfunc(sqlite3_context *pCtx, int nVal, sqlite3_value **apVal){
  int *aMatchinfo;                /* Return value of matchinfo() */
  int nMatchinfo;                 /* Number of elements in aMatchinfo[] */
  int nCol = 0;                   /* Number of columns in the table */
  int nPhrase = 0;                /* Number of phrases in the query */
  int iPhrase;                    /* Current phrase */
  double score = 0.0;             /* Value to return */

  assert( sizeof(int)==4 );

  /* Check that the number of arguments passed to this function is correct.
  ** If not, jump to wrong_number_args. Set aMatchinfo to point to the array
  ** of unsigned integer values returned by FTS function matchinfo. Set
  ** nPhrase to contain the number of reportable phrases in the users full-text
  ** query, and nCol to the number of columns in the table. Then check that the
  ** size of the matchinfo blob is as expected. Return an error if it is not.
  */
  if( nVal<1 ) goto wrong_number_args;
  aMatchinfo = (unsigned int *)sqlite3_value_blob(apVal[0]);
  nMatchinfo = sqlite3_value_bytes(apVal[0]) / sizeof(int);
  if( nMatchinfo>=2 ){
    nPhrase = aMatchinfo[0];
    nCol = aMatchinfo[1];
  }
  if( nMatchinfo!=(2+3*nCol*nPhrase) ){
    sqlite3_result_error(pCtx,
      "invalid matchinfo blob passed to function rank()", -1);
    return;
  }
  if( nVal!=(1+nCol) ) goto wrong_number_args;

  /* Iterate through each phrase in the users query. */
  for(iPhrase=0; iPhrase<nPhrase; iPhrase++){
    int iCol;                     /* Current column */

    /* Now iterate through each column in the users query. For each column,
    ** increment the relevancy score by:
    **
    **   (<hit count> / <global hit count>) * <column weight>
    **
    ** aPhraseinfo[] points to the start of the data for phrase iPhrase. So
    ** the hit count and global hit counts for each column are found in 
    ** aPhraseinfo[iCol*3] and aPhraseinfo[iCol*3+1], respectively.
    */
    int *aPhraseinfo = &aMatchinfo[2 + iPhrase*nCol*3];
    for(iCol=0; iCol<nCol; iCol++){
      int nHitCount = aPhraseinfo[3*iCol];
      int nGlobalHitCount = aPhraseinfo[3*iCol+1];
      double weight = sqlite3_value_double(apVal[iCol+1]);
      if( nHitCount>0 ){
        score += ((double)nHitCount / (double)nGlobalHitCount) * weight;
      }
    }
  }

  sqlite3_result_double(pCtx, score);
  return;

  /* Jump here if the wrong number of arguments are passed to this function */
wrong_number_args:
  sqlite3_result_error(pCtx, "wrong number of arguments to function rank()", -1);
}

Последнее обновление этой страницы: 2025-06-12 09:10:23Z