Полнотекстовый поиск в файлах.

Как известно полнотекстовый поиск позволяет быстро находить строки таблицы, текстовый столбец которых содержит определенное слово или фразу. Полнотекстовый поиск гибкий, поскольку помимо поиска по полному совпадению может искать словоформы, не учитывая падежи, склонения и т. д. Оказывается такой же поиск можно организовать и для столбцов, хранящих бинарные представления файлов.

Создадим таблицу для хранения содержимого файлов:

create table dbo.FilesData

(

    iFileId    int identity ( 1, 1 ) not null,

    varFile    varbinary ( max )     not null,

    vcExt      varchar ( 200 )       not null,

    vcFileName varchar ( 800 )       not null,

    constraint PK_FileData_iFileId primary key clustered ( iFileId asc ) on [PRIMARY],

    constraint AK_FileData_vcFileName unique nonclustered ( vcFileName asc ) on [PRIMARY]

) on [PRIMARY]

Столбец с расширением обязателен. Он нужен для того, что служба полнотекстового поиска определяла каким алгоритмом распознать слова в бинарном представлении файла. При этом расширение должно сохраняться в столбце с точкой впереди. Теперь нужна хранимая процедура, с помощью которой таблица будет наполняться:

if sessionproperty ( N'quoted_identifier' ) = 0

    set quoted_identifier on

go
if sessionproperty ( N'ansi_nulls' ) = 0

    set ansi_nulls on

go
if object_id ( N'dbo.SaveFile', N'P' ) is null

    exec ( N'create proc dbo.SaveFile as return 1' )

go
alter proc dbo.SaveFile

(

    @vcFileName varchar ( 800 ),

    @varFile    varbinary ( max )

)as
begin

    set nocount, xact_abort on

    if exists

    (

        select 1

        from dbo.FilesData

        where vcFileName = @vcFileName

    )

    begin

        raiserror ( N'Файл с именем "%s" уже существует.', 16, 1,

            @vcFileName )

        return

    end

    insert into dbo.FilesData ( vcFileName, varFile, vcExt )

    values

    (

        @vcFileName,

        @varFile,

        reverse ( left ( reverse ( @vcFileName ), charindex ( '.', reverse ( @vcFileName ), 1 ) ) )

    )

    select scope_identity () iFileId

end
go

Создадим несколько файлов разных типов: data1.xlsx, data2.txt, data3.pdf, data4.csv:

   

Теперь напишем на языке C# простое консольное приложение, которое загрузит эти файлы в таблицу:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Data.SqlTypes;
using System.IO;

namespace DbProject

{    class FilesData

    {       

        static void Main(string[] args)

        {           

            string FileName = args[0];

            byte[] bytes;

            FileStream fs = null;

            try

            {               

                fs = File.OpenRead(FileName);

                bytes = new byte[fs.Length];

                fs.Read(bytes, 0, Convert.ToInt32(fs.Length));
                SqlConnection cn = new SqlConnection();

                cn.ConnectionString = @"Data Source=SQLNCLI11;Server=EDYNAK\SHADOW;Initial Catalog=master;Trusted_Connection=Yes;";

                cn.Open();               

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = cn;               
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.CommandText = "dbo.SaveFile";

                cmd.Parameters.Add("@vcFileName", System.Data.SqlDbType.VarChar, 800).Value = FileName;
                cmd.Parameters.Add("@varFile", System.Data.SqlDbType.VarBinary, -1).Value = bytes;
                cmd.ExecuteNonQuery();
            }           
            catch(Exception ex)

            {
                fs.Close();
                fs.Dispose();               

                Console.WriteLine(ex.ToString());
                Console.ReadLine();

            }
        }
    }
}

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

Создадим полнотекстовый каталог:

create fulltext catalog FilesContent
    on filegroup [PRIMARY]
    with accent_sensitivity = off
    authorization dbo

Теперь построим полнотекстовый индекс.

create fulltext index on dbo.FilesData
    (
        varFile type column vcExt
        language russian
    )
    key index PK_FileData_iFileId
    on FilesContent
    with stoplist = system, change_tracking = auto
while fulltextcatalogproperty ( 'FilesContent', 'PopulateStatus' ) <> 0

begin

    waitfor delay '00:00:01'

end

После создания индекса идет цикл с проверкой статуса заполнения каталога. Это делается из-за того, что сама инструкция по созданию полнотекстового индекса асинхронная, она сразу же возвращает управление, а сервер в фоне начинает построение и наполнение каталога.

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

В опциях индекса мы задаем, что он не будет строиться для слов, которые не несут в себе важной информации, например союзы, междометия. Список таких системных стоп-слов можно посмотреть в каталоге sys.fulltext_system_stopwords. При запросе к нему требуется задать условие на идентификатор языка, который можно посмотреть в представлении sys.syslanguages. Если какие-то из системных стоп-слов нужны или если необходим свой список стоп-слов, то его можно создать с помощью инструкции create fulltext stoplist, который затем указывается в параметрах индекса.

Также мы задали опцию авто заполнения. В частности, это означает, что при создании индекса он сразу начинает строиться. А если в таблице происходит изменение, то полнотекстовый индекс сразу начинает обновление. Правда, это обновление не входит в транзакцию, которая обновляет таблицу. В частности, если сделать обновление, а затем сразу запустить полнотекстовый запрос, то он может не обнаружить сделанное изменение. Однако если через короткое время перезапусить запрос, то индекс будет уже обновленным и запрос вернет корректные данные.

Протестируем работу полнотекстовых запросов. Сделаем такой запрос:

select *

from dbo.FilesData

where contains ( varFile, 'Саша' )

Запрос от работал быстро, вернув одну строку для txt-файла. Проверим поиск по словоформам. Напишем такой запрос:

select *

from dbo.FilesData
where freetext ( varFile, 'Сашой' )
Запрос также вернул строку с текстовым файлом.


Проверим поиск для файлов остальных типов. Слово "Привет" встречается во всех файлах. Запустим такой запрос:

select *

from dbo.FilesData

where contains ( varFile, 'Привет' )

Запрос вернув всего одну строку для файла с расширение txt. Однако такой результат не является полным. Попробуем понять почему файл ищет слова в текстовых файлах и не ищет данных в Excel-, csv-, pdf-файлах. Сделаем такой запрос:

select *

from sys.dm_fts_index_keywords_by_document ( db_id (), object_id ( 'dbo.FilesData' ) )

Эта функция показывает содержимое полнотекстового индекса. И для проблемных файлов, в столбце display_item мы видим "END OF FILE":

Посмотрим на список типов файлов, которые могут быть проиндексированы (это файлы, поддерживающие интерфейсы IFilter, которые зарегистрированы на сервере):

select *

from sys.fulltext_document_types

Как и ожидалось xlsx и другие нужные расширения отсутствуют. Для возможности поиска в файлах Microsoft Office 2007 и выше необходимо установить фильтр: 2007 Office System Coverter: Microsoft Filter Pack. После установки данные о типах файлов новых версий офиса (Word, Excel, PowerPoint и т. д.) уже есть в каталоге sys.fulltext_document_types. После перестройки индекса полнотекстовый запрос уже возвращает запись с Excel-файлом. Строка с файлом csv не возвращается. Видимо, это происходит из-за того, что средства поиска слов для файлов типа csv не входит в список характерных для сервера. Требуется разрешить использование фильтров и средств разбиения операционной системы. Также на всякий случай отключим загрузку для полнотекстового поиска только достоверно подписанных двоичных файлов и перезапустим службу полнотекстового поиска. Это делается таким кодом:

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

exec sp_fulltext_service 'verify_signature', 0

exec sp_fulltext_service 'restart_all_fdhosts'

reconfigure with override

Теперь опять требуется перестроить индекс, и запустить полнотекстовый запрос. Файлы csv находятся. Осталось решить проблему с pdf. Загрузим Adobe PDF IFilter 9-ой версии для ОС x64 (в соответствии с разрядностью моей ОС). Есть фильтр 11-ой версии, но с ней поиск не работает. Также к переменной среды Path добавляется каталог, в котором хранится библиотека PDFFilter.dll. Перезагрузим компьютер. В списке типов файлов, для которых возможен полнотекстовый поиск уже появился pdf и все в порядке:

select *

from sys.fulltext_document_types