Сделать полнотекстовый поиск управляемым и удобным

В чем недостатки полнотекстового поиска (full text search), применяемого в современных СУБД? Вы не можете указать сколько слов до и после найденных надо вывести, какими тегами обрамлять найденные слова, в т.ч. в зависимости от того, в какой части строки они найдены, какие и сколько вариантов перестановок найденных слов выводить. Вы имеете желание написать произвольный FTS-запрос, но не имеете возможности. Попытаемся устранить это ограничение.

Управляемость

Вложенные поля

Пусть у нас есть таблица 's' с полями 'pk' (в ней первичный ключ), 's1' и 's2', единственная запись которой содержит

1, 10, "In the morning, dog comes, cat comes home too. Continue in the NEXT issue."

Вообразим, что строка разбита на слова, а слова хранятся во вложенной (nested) таблице с полями

@TOKEN (само слово)
@SN (порядковый номер слова в поле)
@BEGINNING (смещение первой буквы слова)
@END (смещение последней буквы слова)

**Таблица, вложенная в текстовое поле**
@TOKEN	@SN	@BEGINNING	@END
In	1	1	2
the	2	4	6
morning	3	8	14
dog	4	17	19
comes	5	21	24
cat	6	27	29
comes	7	31	34
home	8	36	39
too	9	41	43
Continue	10	46	53
in	11	55	56
the	12	58	60
NEXT	13	62	65
issue	14	67	71

И что синтаксически имеем доступ к этим полям как к полям

s2.@TOKEN
s2.@SN
s2.@BEGINNING
s2.@END

таблицы 's' (как к вложенным полям, nested fields). Каждое текстовое поле каждой записи каждой таблицы имеет такое (синтаксическое) представление.

Вложенная колонка, равно как и результат функции, хотя бы одним из аргументов которой является вложенное колонка, ведет себя

если не стоит после пункта SELECT в запросе (т.е. стоит после пунктов UPDATA, DELETE, WHERE, либо находится в любом месте в под-запросе, в т.ч. после его пункта SELECT) - как вложенная колонка
если стоит после пункта SELECT в запросе - как текстовое невложенное поле с именем 's2', образованная агрегатом конкатенации запрошенного вложенной вложенной колонки. При этом гарантируется, что
- причем порядок слов остается неизменным [1]
- все разделители (знаки препинания), присутствовавшие между найденными словами, бывшими соседями в исходной строке, попадают в результат запроса [2]
- найденные слова, не бывшие соседями, разделяются символами, заданными командой 'SET OMITTED_MEDIATE ...' [3]

Представление текстового поля в СУБД в виде вложенной таблицы позволяет гибко формулировать условия для полнотекстового поиска, упоминая вложенные колонки после пункта WHERE, а при извлечении во внешний мир автоматически видеть результаты поиска как текстовую строку.

Операции с вложенной колонкой обладают следующими свойствами

вставка, обновление, удаление таковы что

при вставке записи с порядковым номером, уже существующим у одной из записей 'INSERT INTO s (s2.@TOKEN, s2.@SN) VALUES ("new", 15)', сначала располагается новая запись, затем старая ("вставка перед")
обновление и удаление (например, 'UPDATE s SET s2.@TOKEN = ""||s2.@TOKEN||"" ', 'DELETE FROM s WHERE s2.@BEGINNING >= 100') не обладает никакими новыми свойствами (например, чтобы заменить три слова на другие четыре, нужно удалить три слова командой DELETE и вставить четыре командой INSERT - а не воспользоваться UPDATE)
после вставки или удаления происходит автоматическое изменение @SN, @BEGINNING, @END у всех записей, расположенных за новой вставленной или за первой удаленной

любая функция вложенной колонки и не-вложенного поля любой таблицы либо вложенной колонки и константы

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

конкатенация двух вложенных колонок

есть UNION для них и дает новую вложенную таблицу (с именем колонки, совпадающим с именем левого операнда конкатенации) той же самой родительской таблицы
аналогично, новая вложенная таблица может не иметь в явном виде колонку, содержащую порядковые номера слов, но неявно такая колонка всегда присутствует
порядок следования в ней задается порядковыми номерами в обеих исходных колонках (значения из двух исходных вложенных колонок в ней могут перемежаться); если два значения в ней имеют одинаковые порядковые номера, то сначала следует значение из левого операнда конкатенации, потом из правого
после конкатетации в новой вложенной таблице автоматически происходит присваивание новых актуальных порядковых номеров

Первое свойство позволяет изменять текстовые поля с помощью SQL, не прибегая в СУБД к громаде строковых функций (эта тема остается за пределами настоящей статьи); второе и третье - обрамлять слова тегами-константами.

Разрешение коллизий

Даже в одной строке может быть найдено несколько образцов, а значит даже одна запись может породить несколько: будем называть этот процесс размножением (propagation), а записи, порожденные из одной - порожденной группой (propagated group). Поэтому всегда в результирующий набор автоматически добавляется фиктивное целочисленное поле SYS_CLUE, которое содержит разные значения для записей одной порожденной группы [4]. Например следующий запрос, запрашивающий слова из определенного множества [5] и выводящий их и по одному слову слева и справа от них

SELECT s1, s2.@TOKEN
FROM   s
WHERE  s2.@SN in (
  SELECT DISTINCT s2.@SN
  FROM   s, (
    SELECT s2.@SN as fn
    FROM   s
    WHERE  s2.@TOKEN in "comes next"
            )
  WHERE  abs(s2.@SN-fn) <= 1
                 );

находит два образца и возвращает две записи

**Поиск с обрамлением**
s1	s2	SYS_CLUE
10	dog comes, cat ... the NEXT issue	1
10	cat comes home ... the NEXT issue	2

Если

в результатах поиска еще раз произведен полнотекстовый поиск, то каждая из записей порожденной группы в свою очередь может породить новую порожеденную группу (группу второго порядка), но поле SYS_CLUE по-прежнему содержит разные значения для записей всех групп второго, третьего и последующих порядков, выведенных из одной первоначальной записи (т.е. второе фиктивное поле для различения записей группы второго порядка не будет нужным)
запрос выполняет полнотекстовый поиск в двух (трех и т.д.) полях одной таблицы, то образцы разных колонок дают декартовое произведение, но поле SYS_CLUE по-прежнему содержит разные значения для записей декартового произведения каждых двух (трех и т.д.) групп (т.е. второе, третье и т.д. фиктивные поля для различения записей декартового произведения не будут нужными) [6]
запрос делает декартовое произведение разных таблиц и выполняет полнотекстовый поиск в полях, исходно принадлежавших разным таблицам, то поле SYS_CLUE содержит те же значения, как если бы поля принадлежали одной таблице

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

Обрамление тегами

Чтобы проводить разные операции (обрамлять разными тегами) с разными словами, достаточно разрешить давать алиасы аргументам функций, в частности - функции конкатенации. Тогда, например, обрамление слов из определенного множества тегами и , по одному слову слева и справа от них тегами и , и возвращение всех остальных слов между ними без обрамления выглядит так

SELECT s1, ("<b>" ||s2.@TOKEN as f1 ||"</b>" ) ||
           ("<em>"||s2.@TOKEN as f2 ||"</em>") ||
           (        s2.@TOKEN as f3          ) 
FROM   s
WHERE  f1 IN "comes next"
  AND  f2 IN (
         SELECT DISTINCT ON(s2.@token, s2.@SN) s2.@token
         FROM   s, (
           SELECT s2.@SN as fn
           FROM   s
           WHERE  s2.@TOKEN in "comes next"
                   )
         WHERE  abs(s2.@SN-fn)=1
             )
  AND f3 between             
         SELECT MIN(s2.@SN)
         FROM   s
         WHERE  s2.@TOKEN in "comes next"
      AND
         SELECT MAX(s2.@SN)
         FROM   s
         WHERE  s2.@TOKEN in "comes next"
      AND NOT IN (
         SELECT DISTINCT ON(s2.@token, s2.@SN) s2.@token
         FROM   s, (
           SELECT s2.@SN as fn
           FROM   s
           WHERE  s2.@TOKEN in "comes next"
                   )
         WHERE  abs(s2.@SN-fn)=1
                   );

И возвращает следующий результат

**Search with surrounding**
s1	s2	SYS_CLUE
10	<em>dog</em> <b>comes</b>, <em>cat</em> comes home too. Continue in <em>the</em> <b>NEXT</b> <em>issue</em>	1
10	<em>cat</em> <b>comes</b> <em>home</em> too. Continue in <em>the</em> <b>NEXT</b> <em>issue</em>	2

Индексация

В результате индексации добавляются под-поля

@IDTOKEN
@IDFIELD

к которым синтаксически возможен доступ как к

s2.@IDTOKEN
s2.@IDFIELD

Использование лексемной индексации

Все грамматические формы одного слова могут рассматриваться как одна лексема. Тогда добавляется под-поле

@IDLEXEME

к которому синтаксически возможен доступ как к

s2.@IDLEXEME

Удобство использования

Основы индексированного поиска

Справочник грамматических форм может быть не загружен, или не содержать некоторых слов или их форм. Тогда индексированный поиск по всем словам (или их формам) невозможнен - только по проиндексированным. Поэтому как только построен индекс для текстового поля

не только увеличивается скорость поиска
но и может сужаться диапазон слов, по которым поиск производится [7]

Для индексации нужны

таблица разделителей 'delimiters', содержащая знаки пробел, табуляция, возврат каретки, переход на новую строку, все знаки пунктуации [8]
разложение строки в таблицы 'tokens' и 'items' [9], связанных внешним ключом 'ALTER TABLE items ADD FOREIGN KEY (idtoken) REFERENCES tokens (idtoken)'

**tokens**
idtoken	token	idlexeme
1	in	1
2	the	2
3	morning	3
4	dog	4
5	comes	5
12	come	5
6	cat	6
7	home	7
8	too	8
9	continue	9
10	next	10
11	issue	11

**items**
idfield	pk	idtoken	own name	abbreviation	sn	beginning	end
505	1	1	yes		1	1	2
505	1	1			11	55	56
505	1	2			2	4	6
505	1	2			12	58	60
505	1	3			3	8	14
505	1	4			4	17	19
505	1	5			5	21	24
505	1	5			7	31	34
505	1	6			6	27	29
505	1	7			8	36	39
505	1	8			9	41	43
505	1	9	yes		10	46	53
505	1	10		yes	13	62	65
505	1	11			14	67	71

Тогда индексирование представляет собой построение пяти индексов

CREATE INDEX i1 ON tokens( idtoken  );
CREATE INDEX i2 ON tokens( token    );
CREATE INDEX i3 ON tokens( idlexeme );

CREATE INDEX i4 ON items( idfield, pk, idtoken );
CREATE INDEX i5 ON items( idfield, pk, sn      );

Все эти индексы должны быть автоматически удалены при удалении любой из таблиц 'delimiters', 'tokens', 'items' (без 'delimiters' и 'tokens' невозможно построение второй таблицы, подобной 'items', по шаблону для сравнения - в нашем случае по константе "come next").

Построение и задействование индекса

Чтобы можно было индексировать, не создавая справочника лексем, введем команду (отдельную от команды заполнения таблицы 'items')

TOKENIZE s(s2) INTO tokens DELIMITING delimiters [, delimiters2];

которая оставит поле 'idlexeme' незаполненным. А для загрузки справочника лексем будем использовать команду заполнения таблицы из файла (поле 'idtoken' будет заполнено из его собственного sequence)

COPY tokens( idlexeme, token ) FROM c:/lexeme.txt

Разложение поля 's2' всех записей будем производить командой

ITEMIZE s(s2) INTO items DELIMITING delimiters [, delimiters2] TOKENIZING tokens;

Операции '=', IN и другие, работая с текстовыми полями и с 's2' в частности, используют индексы, построенные не для 's2', а для таблиц, указанных в параметре NOMENCLARURE [10], и тех, на которые таблицы из NOMENCLARURE ссылаются выше упомянутым внешним ключем

SET NOMENCLARURE items [, items2];

[1] Т.е. 'ORDER BY s2.@SN' писать не надо

[2] Вывод поля s2.@SN возвращает строку, состоящую из порядковых номеров найденных слов, а не из самих слов; поля s2.@BEGINNING - из смещений первых букв слов, s2.@END - из смещений последних букв слов

[3] К началу и/или концу найденной строки добавляется символы, указанные в OMITTED_FIRST и OMITTED_LAST, если для ее получения в исходной строке пришлось отбросить начальные/конечные слова

[4] "Всегда" - значит даже если все порожденные группы состоят из одной записи, а поле SYS_CLUE не упомянуто в запросе. Поле SYS_CLUE может содержать одинаковые значения в разных группах. Значение этого поля требуется клиентской программе, чтобы сообщить серверу, какой конкретный образец группы выбрал пользователь. Если нет первичного ключа, различить группы невозможно

[5] Можно указать перестановку слов из определенного множества (подробнее о перестановке '=~' на с.183-186 pdf-документа)

WHERE s2.@TOKEN =~ "come next"

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

WHERE s2.@TOKEN TO "come next" PERMUTATIONS <=2

[6] Поле SYS_CLUE может содержать одинаковые значения в декартовых произведениях разных пар групп

[7] Мы можем использовать квантор ALL перед названием под-поля, чтобы принудить к неиндексированному поиску по всем словам

SELECT s1, ALL s2.@TOKEN
FROM   s;

[8]

CREATE SEQUENCE delimiters_seq;
CREATE TABLE delimiters (
  iddelimiter  integer DEFAULT nextval('delimiters_seq'),
  delimiter    string
);

[9] 'idfield' - уникальный системный идентификатор самого поля 's2'. Заполняется командой ITEMIZE, чтобы командой 'SELECT ... FROM items' можно было искать сразу во многих полях многих таблиц

[10] Параметр NOMENCLARURE является сессионным

P.S.

Статья разъясняет с.191-197 pdf-документа.

Тюрин Дмитрий, dmitryturin@yandex.ru

Перечень статей Choose language

Используются технологии uCoz