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


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

В чем недостатки полнотекстового поиска (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
In112
the246
morning3814
dog41719
comes52124
cat62729
comes73134
home83639
too94143
Continue104653
in115556
the125860
NEXT136265
issue146771

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

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

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

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

  1. вставка, обновление, удаление таковы что
  2. любая функция вложенной колонки и не-вложенного поля любой таблицы либо вложенной колонки и константы
  3. конкатенация двух вложенных колонок
Первое свойство позволяет изменять текстовые поля с помощью 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
                 );
находит два образца и возвращает две записи

Поиск с обрамлением
s1s2SYS_CLUE
10dog comes, cat ... the NEXT issue1
10cat comes home ... the NEXT issue2

Если

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

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

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

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
s1s2SYS_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

Индексация

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

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

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

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

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

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

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

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

Для индексации нужны
tokens
idtokentokenidlexeme
1in1
2the2
3morning3
4dog4
5comes5
12come5
6cat6
7home7
8too8
9continue9
10next10
11issue11
items
idfieldpkidtokenown nameabbreviationsnbeginningend
50511yes 112
50511  115556
50512  246
50512  125860
50513  3814
50514  41719
50515  52124
50515  73134
50516  62729
50517  83639
50518  94143
50519yes 104653
505110 yes136265
505111  146771

Тогда индексирование представляет собой построение пяти индексов
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