Полнотекстовый поиск. Наполнение таблицы.

В предыдущем посте мы разобрали общие теоретические положения устройства полнотекстового поиска внутри SQL Server, который теперь интегрирован внутрь SQL Server и по этой причине называется интегрированный полнотекстовый поиск - iFTS. В этом посте мы будем закреплять их на практике. Во-первых, прежде, чем полнотекстовый поиск использовать, надо, чтобы он был. Проверить, установлен ли полнотекстовый поиск на данном экземпляре SQL Server, можно так:

select fulltextserviceproperty('IsFulltextInstalled')

-----------

1

Скрипт 1

Полнотекстовый поиск ставится в общем сетапе SQL Server, нужно только не забыть отметить соответствующую фичу, когда ставите галки против всяких репликаций, клиентских тулзов и букс онлайн. Фича, как нетрудно догадаться, называется Full-text search. Если не отметили ее при установке, запустите снова сетап и скажите кое-что добавить к имеющейся установке.

Рис.1

Рис.2

 

Рис.3

Во-вторых, далее при работе с полнотекстом в 7.0 - 2005 надо было заенейблить БД для полнотекстового поиска при помощи процедуры sp_fulltext_database @action= 'enable'. В 2008 этот пункт делать не надо. Он, подразумевается, уже выполнен автоматически за нас. Каждая пользовательская БД в 2008 изначально заенейблена для полнотекстового поиска и специально енейблить ее не требуется. Процедура sp_fulltext_database поддерживается по соображениям совместимости, но в BOL предупреждается, что this feature will be removed in a future version of Microsoft SQL Server. Полнотекстовый поиск по системным базам master, model, tempdb поддерживался в 2000-м, отменился в 2005-м.

В-третьих, в базе нужен материал, по которому будут гоняться полнотекстовые запросы. Для демонстрационных целей я буду использовать базу TestFS, над которой мы тренировались, когда разбирали filestream. Скрипт ее создания можно посмотреть в начале поста "Введение в FILESTREAM". Единственно, я добавлю к ней еще одну файл-группу для иллюстрации помещения полнотекстовых индексов.

use tempdb

if exists(select 1 from sys.databases where name = 'TestFS') begin

 alter database TestFS set single_user with rollback immediate

 drop database TestFS

end

create database TestFS on

primary (name = TestFS_data, filename = 'c:\Temp\TestFS_data.mdf'),

filegroup FS contains filestream

 (name = TestFS_media, filename = 'c:\Temp\TestFS_media'),

filegroup FTS (name = TestFS_fts, filename = 'c:\Temp\TestFS_fts.ndf')

log on (name = TestFS_log, filename = 'c:\Temp\TestFS_log.ldf')

use TestFS

Скрипт 2

Грузить файлы в таблицу я буду со стороны сервера при помощи хранимой процедуры LoadDir, написанной в посте “Как переложить файловую папку в базу”. Чтобы iFTS знал, какой фильтр к какому файлу применять, нужно расширение файла. Расширение должно храниться в отдельной колонке, которая указывается при создании полнотекстового индекса. Получение расширения файла из его полного имени средствами T-SQL - достаточно муторная и медленная процедура, поэтому я добавил еще один метод в CLRный код. Указать явно детерминированный характер этой функции требуется для последующей персистенции поля. Атрибут SqlFacet ограничивает длину возвращаемого nvarchar - https://bytes.com/groups/net-c/444789-attribute-return-value-how. Эта функция будет использоваться для вычисляемого поля, содержащего тип файла. Если длина поля превышает 260 символов, оператор CREATE FULLTEXT INDEX ... TYPE COLUMN ... отказывается ее воспринимать. В данном случае SqlFacet – это выпендреж, т.к. длина результата будет значиться так, как мы ее зададим при деплойменте: CREATE FUNCTION ... RETURNS NVARCHAR(260) AS EXTERNAL NAME ...

/// <summary>

/// Функция возвращает расширение файла

/// </summary>

/// <param name="fullName">Полное имя файла</param>

/// <returns>Расширение</returns>

[SqlFunction(IsDeterministic = true)]

[return: SqlFacet(MaxSize = 260)]

public static SqlString GetFileExtension(SqlString fullName)

{

    return Path.GetExtension(fullName.Value);

}

Скрипт 3

Заведение сборки на стороне SQL Server и создание необходимых модулей:

alter database TestFS set trustworthy on

if object_id('Dir', 'FT') is not null drop function Dir

if object_id('GetSqlErrLogPath', 'FS') is not null drop function GetSqlErrLogPath

if object_id('GetFileExtension', 'FS') is not null drop function GetFileExtension

if object_id('LoadDir', 'PC') is not null drop proc LoadDir

if exists(select 1 from sys.assemblies where name = 'MyAssembly') drop assembly MyAssembly

go

create assembly MyAssembly from 'C:\Temp\LoadFolderToSQL\bin\Debug\ClassLibrary1.dll' with permission_set = unsafe

go

create proc LoadDir @folder nvarchar(255), @shallowTraversal bit, @tblName sysname as external name MyAssembly.FileSystem.LoadDirWithFileContent

go

create function GetFileExtension(@fileName nvarchar(500)) returns nvarchar(260) as external name MyAssembly.FileSystem.GetFileExtension

Скрипт 4

Под загрузку текстовой информации предназначена таблица TestFTS, куда я, не мудрствуя лукаво, положу контент своих постов с данного блога и других форумов. Можно было брать их напрямую с веба; для демонстрации разных IFilter я возьму их локальные копии в виде вордовых документов, txt и пр. у себя из файловой системы.

Если бы текст находился в виде текста типа (n)varchar, можно было брать и применять к нему полнотекстовые операции. Но в таблице будут лежать содержания файлов различных форматов: .docx, .pdf, ... в колонке типа varbinary(max). Чтобы получить из этой бинарщины текст, нужен модуль iFTS под названием фильтр. Фильтры бывают разные в зависимости от формата файла. Чтобы iFTS знал, какой фильтр применять к данной varbinary(max)-ячейке, рядом нужна ячейка с указанием типа файла. Отсюда колонка type.

Значение по умолчанию для файлстримовского поля предназначено на случай, если появится желание его грузить при помощи SqlFileStream – см. Пост "Частичное обновление FILESTREAM", Скрипт 1. Как мы с вами знаем из введения, вставка NULLового значения в файлстрим не приводит к образованию файла в папке, соответствующей данной колонке, следовательно, .PathName() от NULLовой ячейки будет NULL, следовательно, new SqlFileStream(filePath, txCtx, FileAccess.ReadWrite) от нее не создастся.

if object_id('TestFTS', 'U') is not null drop table TestFTS

create table TestFTS(ID HierarchyID, FullName nvarchar(1000), size bigint, DateModified datetime2, DateCreated datetime2, LastAccessed datetime2,

Properties xml, isDir bit, [guid] uniqueidentifier default newid() unique rowguidcol not null, type as dbo.GetFileExtension(FullName) persisted,

Content varbinary(max) filestream default (0x0))

Скрипт 5

Загружаем в таблицу содержимое интересующей файловой папки при помощи процедуры LoadDir (Скрипт 4). Прогресс работы процедуры LoadDir, как мы ее в свое время написали, можно наблюдать в создаваемом ею файле SqlFSLoader.log, который находится там же, где и все логи SQL Server. Разброс времени при загрузке 140 меговой папки из 680 файлов занял 9.5 - 13 мин. Длительность зависит не только от объема, но и от количества файлов. Так, загрузка 700-метрового каталога из 40 файлов занимала 5.5 - 8 мин. Вообще, загрузка контента из файловой системы в блоб с атрибутом файлстрим происходит быстрее, чем в обычный блоб.

exec LoadDir 'c:\Demo', 0, 'TestFTS'

Скрипт 6

Для создания полнотекстового индекса потребуется уникальный индекс, который будет идентифицировать строки таблицы. В принципе, один уникальный индекс уже есть благодаря ограничению unique на колонку [guid]. Но оператору создания полнотекстового индекса он не нравится.

Msg 7653, Level 16, State 1, Line 1

'UQ__TestFTS__497F6CB5182C9B23' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.

Мне не жалко, я могу еще создать. Колонка, по которой он создается, должна быть NOT NULL, иначе оператор create fulltext index не воспримет его в качестве key index, отсюда, предварительно нужно сказать ... alter column ID ... not null.

if exists (select 1 from sys.indexes where name = 'ixId' and object_id = object_id('dbo. TestFTS')) drop index TestFTS.ixId

alter table TestFTS alter column ID HierarchyID not null

create unique index ixId on TestFTS(ID)

Скрипт 7

Персистинг колонки Type также делается в угоду оператору создания полнотекстового индекса, чтобы тот не орал:

Msg 9929, Level 16, State 1, Line 1

Computed column 'Type' cannot be used as full-text type column for image or varbinary(MAX) column. This computed column must be deterministic, precise or persisted, with a size less or equal than 260 characters.

Размер файлстрима не отражается в общем размере базы, т.к. файл-стримовскую файл-группу она в своей бухгалтерии не учитывает. Можно видеть, что размер базы TestFS составляет, по мнению стандартных отчетов SSMS (см. пост «Автоматическое выполнение отчетов»), 15 мегабайт:

Рис.4

тогда как основная масса сосредоточена в файлстримовской папке:

Рис.5