Полнотекстовый поиск. Подключение фильтров.

В предыдущих сериях картины в таблицу была затащена файловая папка, где хранились посты в данный блог, доклады семинаров, статьи, вспомогательная литература и другие материалы, относящиеся к работе sqlclub. Каждый файл теперь является записью, а атрибуты и контент - полями. По файлстримовскому полю, где в бинарном виде хранится содержание файлов, построен полнотекстовый индекс. Однако большинство файлов хранятся в формате Office 2007 (.docx, .xslx, .pptx и пр.), а фильтра для него SQL Server 2008 по умолчанию не знает. Фильтр - это такая dll, которая умеет превращать бинарный контент файла в осмысленный текст, чтобы его можно было полнотекстно проиндексировать. Она выполняется не внутри SQL Server, a в fdhost.exe (см. Введение). Для каждого индексируемого типа файла имеется фильтр. В противном случае в полнотекстовом логе выдается сообщение см. пост "Создание полнотекстового индекса", после Рис.5.

Рис. 1

Зачастую один фильтр умеет управляться с несколькими типами файлов. Тип указывается в отдельной колонке при создании полнотекстового индекса - см. "Создание полнотекстового индекса"\Скрипт 2. Ор внутри полнотекстового лога означает, что для каких-то встретившихся в таблице TestFTS типов у нее не нашлось зарегистрированных фильтров.

Типы файлов, которые понимает SQL Server, что называется, "из коробки", т.е. для которых он знает, какие фильтры применять и их у него есть, перечислены в реестре в HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\Filters:

Рис.2

Запоминаем содержимое поля Data для типа и идем с ним в папку выше, которая называется CLSID. Например, {F07F3920-7B8C-11CF-9BE8-00AA004B9986} там соответствует такая картина:

Рис.3

из которой понятно, что для индексации типа .doc хост fdhost.exe использует библиотеку offfilt.dll, это IFilter для документов Office 97 - 2003, полный путь к которой (HKEY_CLASSES_ROOT\CLSID\{f07f3920-7b8c-11cf-9be8-00aa004b9986}\InprocServer32) = %systemroot%\system32\OffFilt.dll.

Информацию на Рис.2-3 можно получить, не прибегая к реджистри. Для этого в SQL Server имеется DMV sys.fulltext_document_types:

Рис.4

Осталось понять, каких расширений не достало в Рис.1. Для этого можно запомнить оттуда Full-text key value и сделать запрос

select top 10 ID.ToString(), FullName, type from TestFTS where ID.IsDescendantOf('/1/1/') = 1 and ID.GetLevel() = 3

 

из которого, например, следует, что файлы с расширением .url (/1/1/4/) и .htm (/1/1/6/) не вызвали неприятия, а файлы с расширением .csv (/1/1/2/), ps1 (/1/1/3/) и т.д. не были распарсены по словам и, соответственно, проиндексировнаы . Но можно поступить проще. Имеются замечательные DMV (см. "Создание полнотекстового индекса"\продолжение Скрипта 10), которые позволяют получить список (и количество) проиндексированных слов в разбивке по полнотекстовым колонкам (если их > 1) для каждой конкретной записи. Например,

if object_id('tempdb..#DocID_UniqueInd_Mapping', 'U') is not null drop table #DocID_UniqueInd_Mapping

create table #DocID_UniqueInd_Mapping

   (

      DocID int primary key,

      [Key] HierarchyID not null

   )

declare @table_id int = object_id('TestFTS')

insert into #DocID_UniqueInd_Mapping exec sp_fulltext_keymappings @table_id = @table_id

--select t.FullName, t.type, kbd.display_term from sys.dm_fts_index_keywords_by_document(db_id('TestFS'), @table_id ) kbd inner join #DocID_UniqueInd_Mapping m on kbd.document_id = m.DocID

--join TestFTS t on m.[Key] = t.ID order by 1

select t.FullName, t.type, count(kbd.display_term) from sys.dm_fts_index_keywords_by_document(db_id('TestFS'), @table_id ) kbd inner join #DocID_UniqueInd_Mapping m on kbd.document_id = m.DocID

join TestFTS t on m.[Key] = t.ID group by t.FullName, t.type order by 1

Скрипт 1

Рис.5

Сount = 1 означает, что разбивка по словам не произошла - там образовалась всего одна строчка c display_term = END OF FILE. Мы видим, что непосредственно из коробки SQL Server не понимает форматов Office 2007 и не может проиндексировать файлы с расширениями .docx и др. Этот пробел берется восполнить 2007 Office System Converter: Microsoft Filter Pack, который берется здесь - https://www.microsoft.com/downloads/details.aspx?FamilyId=60C92A37-719C-4077-B5C6-CAC34F4227CC&displaylang=en. Он весит 3-4 метра в зависимости от того, для х86 или для х64. Установка производится бесхитростно, в очередной раз я не успел снять красивый скриншот с прогрессбаром

Рис.6

Потом открываем BOL и читаем Как изменить список зарегистрированных средств разбиения по словам и фильтров. Выполняем

exec sp_fulltext_service @action = 'load_os_resources', @value = 1

exec sp_fulltext_service @action = 'update_languages'

exec sp_fulltext_service @action = 'restart_all_fdhosts'

Скрипт 2

Microsoft Filter Pack 1.0 привносит dllи, лежащие в %ProgramFiles%\Common Files\microsoft shared\Filters, добавляющие поддержку форматов Office 2007 .docx, .docm, .pptx, .pptm, .xlsx, .xlsm, .xlsb, .zip, .one, .vdx, .vsd, .vss, .vst, .vdx, .vsx, .vtx. В HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\Filters они не появляются. Я повторил запрос рис.4 и убедился, что эти и другие расширения добавились в список типов файлов, индексируемых SQL Server. Индекс у меня находился в режиме ручного Change Tracking, поэтому я выполнил запрос "Создание полнотекстового индекса"\Скрипта 15, подождал немного, пока процесс индексации таблицы не исчезнет из sys.dm_fts_index_population и заглянул в полнотекстовый лог. В нем iFTS по-прежнему бурно выражал недовольство по поводу отсутствия необходимых фильтров. Как выяснилось чуть позже, это были другие фильтры. Не разобравшись, я запустил полную популяцию "Создание полнотекстового индекса"\Скрипт 18, этого показалось мало, от широты души перестроил каталог "Создание полнотекстового индекса"\Скрипт 19, протер фары и прочитал КВ "How to register Microsoft Filter Pack IFilters with SQL Server 2005 and with SQL Server 2008", где еще более широкие душою люди рекомендуют в 2008-м за неимением msftesql перестартовать в целом SQL Server. Хвала создателю, до этого не дошло. Запустив для очистки совести Скрипт 1 в очередной раз, я увидел, что на Рис.5 напротив .docx-ов стали прирастать циферки, то есть 2007-й офисный фильтр заработал нормально, преобразуя бинарщину в словесный поток.

Хуже обстоит дело с plain text фильтром query.dll, применяемым по умолчанию для кучи типов файлов, включая .сs, .csv, .sql и др. В SQL Server путь к нему видится абсолютно дебильно как C:\Windows\system32\%systemroot%\system32\query.dll, потому что программисту, как и водителю, следует выходить на работу трезвым. Вы сами видите, к чему приводит нарушение этого правила, несмотря на то, что в реестре по CLSID = {C1243CA0-BF96-11CD-B579-08002B30BFEB} все обозначено абсолютно корректно:

 

 

Рис.7

Из этого, в Windows Search, например, тот же самый plain text filter работает:

Рис.8

Рис.9

А SQL Server из-за неправильно прописанного пути фильтра его не находит, хоть ты тресни. Скрипт 1 и Рис.5 показывает, что .sqlи не индексируются:

select t.ID.ToString(), t.FullName, t.type, count(kbd.display_term) from sys.dm_fts_index_keywords_by_document(db_id('TestFS'), @table_id ) kbd inner join #DocID_UniqueInd_Mapping m on kbd.document_id = m.DocID

join TestFTS t on m.[Key] = t.ID group by t.ID, t.FullName, t.type order by 1

Рис.10

и полнотекстный лог это, разумеется, подтверждает. Можно удостовериться, это те самые IDшники.

Рис.11

Такая же порнография творится для фильтров C:\Windows\system32\%systemroot%\system32\nlhtml.dll и C:\Windows\system32\%systemroot%\system32\xmlfilter.dll.

Это не единственный косяк с Filter Pack'ом. Есть еще КВ, https://support.microsoft.com/kb/960502. К счастью, мне пока не требуется искать в pptx дальше третьего слайда. Я также готов поступиться .csproj и проч. но в .cs и .sql у меня хранилось много текста, который бы хотелось видеть индексированным, елико сие возможно.

Вначале, полюбовавшись на Рис.2, я решил добавить ключ по имени .sql в HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\Filters на Рис.2. Сказал New -> Key и вбил ему в дефолтное значение class_id из строчки с %ProgramFiles%\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\msfte.dll на Рис.7. Это тоже фильтр общего назначения, используемый по умолчанию. Разница в том, что он SQL Serverный, а query.dll поставляется с Windows.

Рис.12

Перевыполнил Скрипт 2 - ничего не произошло. Добавил в Скрипт 2 второй строчкой

exec sp_fulltext_service @action = 'verify_signature', @value = 0

снова ничего. Последовательно перестартовал SQL Server, машину, протер фары - фиг. Для .sql остается фильтром query.dll, как было на Рис.7. Раз они не умеют по-человечески парсить %systemroot%, злобно подумал я, проще всего убрать его нафиг из пути. Отправился в реджистри на Рис.7 и подставил в дефолтное значение HKCR\CLSID\{c1243ca0-bf96-11cd-b579-08002b30bfeb}\InprocServer32 фактическую строчку C:\Windows\system32\query.dll. Как водится, выполнил Скрипт 2 и запрос Рис.4. SQL Serverу замена явно пришлась по душе, потому что в графе version и manufacturer нарисовались осмысленные значения:

Рис.13

Поперла фишка, подумал я и перезапустил популяцию. Не тут-то было. Снова туча ошибок в логе, как на Рис.11 и единички напротив .sql на Рис.10. Хотел расстроиться, но обратил внимание, что единички стоят только напротив .sql, а, например, .cs'ы, .csv и все прочие, находившиеся под опекой query.dll, стали индексироваться и выдавать про себя осмысленные количества слов. Елы-палы! Пошел в реджистри и убил созданный на Рис.12 ключ. Снова Скрипт 2, далее перепопуляция индекса, проверяем (sys.dm_fts_index_population), что закончилась, смотрим Скрипт 1:

Рис.14

А, блин, сказали суровые сибирские лесорубы. В смысле, наоборот. Заработала, зараза.

Еще хотелось иметь индексный поиск по pdf'овским документам. Когда-то pdf'овский фильтр нужно было отдельно скачивать с сайта Аdobe. Как написано на https://www.adobe.com/support/downloads/detail.jsp?ftpID=2611, начиная с версии 7.0.5, функциональность iFilter включена в Acrobat и Reader. В версии 8 добавлена поддержка Висты и Windows Desktop Search, а также, как полагается, производительность, устойчивость и т.д. Вместо установки отдельного плагина iFilter теперь рекомендуется обновить нашу копию Adobe Acrobat или Reader для получения наиболее актуальной функциональности iFilter. Обновлять мне было нечего, поскольку виртуалка в очередной раз благоухала свежестью и первозданностью; я просто пошел скачал Adobe Reader последней на данный момент версии 9.10 и поставил его туда. Привычно выполнил Скрипт 10 и узрел в результатах Рис.4 строчку .pdf:

Рис.15

Перезапустил популяцию и повторил запрос Скрипт 1

select t.ID.ToString(), t.FullName, t.type, count(kbd.display_term) from sys.dm_fts_index_keywords_by_document(db_id('TestFS'), @table_id ) kbd inner join #DocID_UniqueInd_Mapping m on kbd.document_id = m.DocID

join TestFTS t on m.[Key] = t.ID where t.type = '.pdf' group by t.ID, t.FullName, t.type order by 1

Рис.16

Pdf'ы стали парситься, как и все допреж. Теперь по всем этим типам файлов в таблице: .pdf, .txt, .htm, .doc, .docx, .xls, .xlsx, .csv, .cs, .sql и т.д. можно пускать полнотекстовые запросы.

Comments

• Anonymous
  August 23, 2009
  Спасибо, очень пригодится в текущем проекте )