Детективная история о потерянных документах, или почему не работает полнотекстовый поиск?

(А может, превратить "детективные истории" в традицию? )

Однажды холодным зимним днем у одного администратора системы DIRECTUM (у меня ) возникла большая необходимость разобраться, почему же некоторые документы в БД DIRECTUM никак не получается найти при помощи полнотекстового поиска. Лежат тихонько в папке два документа, с одинаковыми типами карточек, одинаковыми правами, одинаковыми приложениями-редакторами, примерно одинаковым содержанием. Мистика: один из них - прекрасно находится при помощи поиска по тексту, а другой - нет.

Логика подсказала, что если документ не ищется - значит, он почему-то не попал в полнотекстовый индекс.

А если документ не попал в индекс - надо смотреть на то, как происходит наполнение индекса, и разбираться, почему оно происходит не так, как нам хочется.

Как известно, процесс построения полнотекстового индекса для  БД DIRECTUM запускается при помощи специального сценария - называется он "Индексация текстов", и может быть запущен как вручную, так и при помощи назначенного задания Windows. Текст сценария не закрыт и имеет вполне понятные комментарии - значит, разберемся...

Разобрался. В общем, сценарий обновляет слепки объектов (это нам не очень интересно, а кому чуть больше интересно, чем нам - смотрите описание метода UpdateIndexData в справке DIRECTUM - относится эта штука к полям карточек, а не к документам) и самое важное - создает полнотекстовый каталог SQL: показывает, на какие поля табличек смотреть и какого формата там лежат данные, запускает процесс индексирования средствами SQL Server, а сам тем временем спокойно "умирает", оставив на память сообщение в логе: "Обновление слепков объектов завершено. Выполняется индексирование текстов и слепков объектов. Процесс индексирования завершится автоматически."

По всей видимости, DIRECTUM в процессе индексирования участвует минимально, только инициируя процесс. Это немножко успокаивает - хорошо, что это не дефект прикладной разработки. 

Значит, виноватый - SQL. Копаем дальше.

Как выяснилось, при индексации версий документов SQL поступает так - пытается распарсить поле VersionData таблички SBEdocVer согласно формату, который берет из поля TypeVersionData. То есть, если в TypeVersionData написано "DOCX", то SQL будет пытаться разодрать поле VersionData на понятные слова, думая, что там - тело документа, сформированное в соответствии с форматом Microsoft Word 2007. Сам SQL конечно же, не может знать все форматы в мире, и берет он информацию (и сами "парсеры" форматов, которые принято называть IFilter'ы, т.е. Indexing Filter'ы) от ОС Microsoft Windows. Информацию о том, какой формат каким IFilter'ом будет обрабатываться, можно выпытать у SQL при помощи такого запроса:

SELECT * from sys.fulltext_document_types 

Если ваших любимых форматов в этом списке нет - в полнотекстовый индекс они не попадут. Ннапример, в списке может не быть PDF - тогда бежим пытать гугл на предмет "PDF IFilter". Нету офисных фильтров - скачиваем их набор например, здесь: https://www.microsoft.com/ru-ru/download/details.aspx?id=17062. 

Но наши-то форматы есть в списке! А в полнотекстовый каталог они все равно не попадают! Грусть. Но к счастью, процесс построения полнотекстового индекса протоколируется, и в этот лог очень легко заглянуть. Лежит он в том каталоге, куда был установлен экземпляр SQL, в папке \MSSQL\Log (например, в моем случае - С:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log), и называется по шаблону "SQLFT<цыфрыцыфры>.LOG". В нем пишутся очень говорящие строки (знание английского - обязательно), например: 

2015-12-21 07:59:56.76 spid39s     Warning: No appropriate filter was found during full-text index population for table or indexed view '[DIRECTUM].[dbo].[SBEDocVer]' (table or indexed view ID '273592213', database ID '5'), full-text key value '12345'. Some columns of the row were not indexed.

И это - оно самое!

Что тут полезного? '[DIRECTUM].[dbo].[SBEDocVer]' - значит, это та самая табличка с версиями документов. "...full-text key value '12345'" - это ИД в ключевом поле, в нашем случае это поле XRecID. Посмотрим, что с этой версией не так:

select    XRecID, 
         convert(varchar(max),convert(varbinary(max), VersionData)) Data,
         TypeVersionData
from SBEDocVer 
where XRecID = 12345

Получаем такой результат:

XRecID    Data    TypeVersionData
12345     PK      DOC

В поле "Data" могут быть другие символы - это непосредственно ASCII-код содержимого документа (то же самое можно увидеть, открыв документ Word например, в каком-нибудь текстовом редакторе). Это для нас сейчас очень, очень полезное поле! По нему можно определить, какой формат документа оно представляет!

Например, если начальные символы - "PK..." - то это документы нескольких типов: DOCX, XLSX, PPTX, либо ZIP (ничего странного - документы Microsoft Office 2007 представляют собой как раз собой запакованные алгоритмом ZIP XML-ки).

Если начальные символы - "РПаЎ±б" - это документы "старого" офиса - DOC, XLS, PPT.

Если начальные символы - "{\rtf1\" - это документы форматa RTF.

Так что же мы наблюдаем в нашем случае? Несоответвие фактической структуры документа  (Microsoft Word 2007) расширению (DOC), которое указывает на фильтр, с помощью которого SQL пытается его разобрать, и конечно же, у него ничего не получается!

Почему так произошло? 

Возможно, потому, что кто-то сохранил документ формата Microsoft Office 2007 c расширением DOC, после чего занес его в DIRECTUM. DIRECTUM с чистой совестью указал, что формат документа - тот, который в расширении, т.е. Microsoft Office 97-2003.

Такие документы можно найти запросами (для DOC):

select * from SBEDocVer where TypeVersionData = 'DOC' and convert(varchar(max),convert(varbinary(max), VersionData)) like 'PK%'

Для XLS:

select * from SBEDocVer where TypeVersionData = 'XLS' and convert(varchar(max),convert(varbinary(max), VersionData)) like 'PK%'

Для RTF:

select * from SBEDocVer where TypeVersionData = 'DOC' and convert(varchar(max),convert(varbinary(max), VersionData)) like '%rtf1%'

Что делать?

Надо для всех документов формата DOCX указать расширение DOCX. Для всех XLSX - указать расширение XLSX. Для всех RTF - RTF. В общем, привести все к правильности и строгости.

Как делать?

Для настоящих RTF, которых кто-то обозвал DOC'ами:

update SBEdocVer 
set TypeVersionData = 'RTF' 
where TypeVersionData = 'DOC' 
and convert(varchar(max),convert(varbinary(max), VersionData)) like '%rtf1%'

Для настоящих DOCX, которых кто-то обозвал DOC'ами:

update SBEdocVer 
set TypeVersionData = 'DOCX' 
where TypeVersionData = 'DOC' 
and convert(varchar(max),convert(varbinary(max), VersionData)) like 'PK%'

Для настоящих XLSX, которых кто-то обозвал XLS'ами:

update SBEdocVer 
set TypeVersionData = 'XLSX' 
where TypeVersionData = 'XLS' 
and convert(varchar(max),convert(varbinary(max), VersionData)) like 'PK%'

Внимание! Все действия по обновлению таблиц нужно выполнять только при следующих условиях:
1. ВЫ СДЕЛАЛИ РЕЗЕРВНУЮ КОПИЮ БД!
2. Вы поняли все слова из этой статьи! 

P.S. по многочисленным просьбам радиослушателей публикую финал: у того самого администратора системы DIRECTUM после вышепроделанных танцев все документы нашлись полнотекстовым поиском!  Хэппи энд, новый год и мандарины )