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


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


 


select fulltextserviceproperty('IsFulltextInstalled')


-----------


1


Скрипт 1


 


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


 


image001



Рис.1


 


image003



Рис.2


 


image005



Рис.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 - http://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 мегабайт:


 


image007



Рис.4


 


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


 


image009



Рис.5


Skip to main content