如何透過SQL Server的Full-Text快速搜尋文件內容

        SQL Server的功能不斷的增強，也整合了檔案與資料庫的部份(FileTable)等，在SharePoint的部份由於可以進行文件的控管，所以也會有許多的檔案上傳至SharePoint中，進行集中化的管理。所以本篇我們就來介紹如何透過Full-Text與Office中的 iFilter 套件，進行文件關鍵字的搜尋。

本篇我們將以繁體中文與同義字的部份進行測試，並且嘗試搜尋DOCX與XPS的檔案格式。

環境建置：
1、請先確認主機上是啟用SQL Server的主機上XPS Viewer的功能啟用。(Windows 2008)

1-1 請先至 Features中加入 [XPS Viewer] 的功能。

參考網址：
XPS IFilter for SQL 2008 (64bit) on server 2008 (64bit)
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/595bbadf-f480-4953-a9d9-f9a4733e5181/xps-ifilter-for-sql-2008-64bit-on-server-2008-64bit
Enabling XPS in Windows Server 2008
http://blogs.msdn.com/b/adrianford/archive/2008/02/12/enabling-xps-in-windows-server-2008.aspx


2、請執行下列的語法進行XPS檔案格式的啟用
exec sp_fulltext_service 'load_os_resources',1;
exec sp_fulltext_service 'verify_signature',0;

3、執行完成後，請重新啟動SQL Server Service，並請執行下列的語法進行確認。
select document_type, path from sys.fulltext_document_types


4、請安裝下列的Office Filter Packs套件

Microsoft Office 2010 Filter Packs
http://www.microsoft.com/en-us/download/details.aspx?id=17062

5、您可以透過下列的語法確認目前Full-Text支援的語系
select * from sys.fulltext_languages


測試資料建立
1、 建立測試資料表
CREATE TABLE [dbo].[testTBL](
 [doctype] [nvarchar](50) NOT NULL,
 [document] [varbinary](max) NULL,
 [docname] [varchar](50) NOT NULL,
 CONSTRAINT [PK_testTBL] PRIMARY KEY CLUSTERED
 ( [docname] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]

備註：
Filters (iFilters). Indexing a document in a varbinary(max), image, or xml data type column requires a filter to perform extra processing. The filter must be specific to the document type (.doc, .pdf, .xls, .xml, and so forth). For more information, see Configure and Manage Filters for Search.

2、建立Full-Text Index
CREATE FULLTEXT CATALOG [CatalogTEST]
WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]
GO

CREATE FULLTEXT INDEX ON [dbo].[testTBL](
[docname] LANGUAGE [Traditional Chinese],
[doctype] LANGUAGE [Traditional Chinese],
[document] TYPE COLUMN [doctype] LANGUAGE [Traditional Chinese])
KEY INDEX [PK_testTBL]ON ([CatalogTEST], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)
GO

3、新增二筆資料，此時我會將docx與xps的資料新增到資料庫中。
INSERT INTO dbo.testTBL(doctype,docname,document)
 SELECT '.docx' AS doctype, 'LogMeIn.docx' AS docname, *
 FROM OPENROWSET  (BULK N'F:\LogMeIn.docx',
 SINGLE_BLOB) AS Document

INSERT INTO dbo.testTBL(doctype,docname,document)
 SELECT '.xps' AS doctype, 'LogMeIn.xps' AS docname, * FROM OPENROWSET
 (BULK N'F:\LogMeIn.xps', SINGLE_BLOB) AS Document

4、您可以透過下列的語法確認目前在此Index上所建立的Term有那些。
SELECT * FROM sys.dm_fts_index_keywords(db_id('test_db'), object_id('dbo.testTBL'))


5、增加繁體中文同義字的定義。

你可以到下列的路徑找到檔案。 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\FTData\ tscht.xml
請先將開頭的註解移除後，再請新增下列的資訊。
<replacement>
<pat>MS</pat>
<sub>Microsoft</sub>
</replacement>  
<replacement>
<pat>連結</pat>
<sub>連線</sub>
</replacement>  

透過第四節的資訊中，我們知道系統已有建立二個Term，分別是Microsoft與連線，所以我們希望可以透過同義字的定義，MS -> Microsoft 與 連結 -> 連線，分別可以來找到所需的資訊。

6、新增完成後，由於同義字檔案中有中文字，所以如果單純的透過指令載入時，可能會因為定序的關係造成錯誤，所以必須透過下列的方式載入同義字的檔案。
USE master
GO
CREATE DATABASE temp_sbcs COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE temp_sbcs
GO

EXEC sys.sp_fulltext_load_thesaurus_file 1028
GO

USE master
GO
DROP DATABASE temp_sbcs
GO

PS:載入時由於檔案中有包含中文字，所以需請透過下列的方式進行解決。

錯誤訊息：
Msg 50000, Level 16, State 1, Procedure sp_fulltext_rethrow_error, Line 36
Error 30049, Level 16, State 1, Procedure sp_fulltext_thesaurus_update, Line 61, Message: Fulltext thesaurus internal error (HRESULT = '0x8007054e')

參考網址：
You cannot load a thesaurus file in SQL Server, and you receive the following error message: "ERROR_INTERNAL_DB_CORRUPTION"
http://support.microsoft.com/kb/2014749/en-us

7、載入完成後，我們先進行下列的查詢確認原始資料的情況。
select * from dbo.testTBL where contains(document, N'連線')


select * from dbo.testTBL
where contains(document, N'連結')


select * from dbo.testTBL
where contains(document, N'Microsoft')


select * from dbo.testTBL
where contains(document, N'MS')


8、接下來我們透過同義字的方式來進行搜尋，確認在同義字的定義上皆可正常的運作。
select * from dbo.testTBL
where contains(document, N'FORMSOF(THESAURUS,"ms")')


select * from dbo.testTBL
where contains(document, N'FORMSOF(THESAURUS,"連結")')


透過上列的方式，可以簡單的整合Full-Text與iFilter的套件，讓文件的管理可以更簡單的達到，本篇中主要使用到DOCX與XPS的檔案格式，但是其他的檔案格式其實方法都相同，大家可以多多的利用這個方式。

關於支援的檔案格式，請參考下列的網址：

Supported File Formats for #SQLServer Full Text Search (IFilter) http://www.olschimke.eu/2012/07/31/supported-file-formats-for-sqlserver-full-text-search-ifilter/


參考連結：
XPS IFilter for SQL 2008 (64bit) on server 2008 (64bit) http://social.msdn.microsoft.com/Forums/sqlserver/en-US/595bbadf-f480-4953-a9d9-f9a4733e5181/xps-ifilter-for-sql-2008-64bit-on-server-2008-64bit
Enabling XPS in Windows Server 2008
http://blogs.msdn.com/b/adrianford/archive/2008/02/12/enabling-xps-in-windows-server-2008.aspx
How to register Microsoft Filter Pack IFilters with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;945934
Full-Text Search (SQL Server)
http://technet.microsoft.com/en-us/library/ms142571.aspx
Configure and Manage Thesaurus Files for Full-Text Search
http://technet.microsoft.com/en-us/library/ms142491.aspx
You cannot load a thesaurus file in SQL Server, and you receive the following error message: "ERROR_INTERNAL_DB_CORRUPTION"
http://support.microsoft.com/kb/2014749/en-us


關鍵字：Full-Text Search、iFilter、Synonym、Thesaurus Files