SQL Server .pdf Full-Text Search

Kad su CMS sistemi u pitanju vrlo često je potrebno realizovati pretragu po tekstu .pdf dokumenata koji su objavljeni na konkretnom sajtu. Najjednostavniji je način da se takvi dokumenti smeštavaju na MS SQL Server i omogući Full-Text Search po takvim dokumentima. SQL Server inicijalno nema podršku za indeksiranje .pdf fajlova ali ju je lako obezbediti.

Za početak potrebno je download-ovati Adobe PDF iFilter sa lokacije:

http://www.adobe.com/support/downloads/detail.jsp?ftpID=4025 (verzija 9 za SQL Server 2005)

ili sa lokacije:

http://www.adobe.com/support/downloads/detail.jsp?ftpID=5542  (verzija 11 za SQL Server 2008, 2008 R2 i 2012)

i instalirati ga na mašini na kojoj se nalazi MS SQL Server.

Iako su verzije iFilter-a namenjene konkretnim verzijama MS SQL Server-a ja sam probao verziju 9 na SQL Server-u 2012 i to radi bez problema.

Posle instalacije PDF iFiltera potrebno je uneti putanju do bin foldera IFilter-a u Path Environment varijablu (slika 1).

EnvirVar1

Slika 1. Forma za definisanje Windows Environment varijabli

Za verziju 9 ta putanja je, ako nije drugačije navedeno prilikom instalacije:

C:\Program Files\Adobe\Adobe PDF iFilter 9 for 64-bit platforms\bin

Zatim je PDF iFilter potrebno registrovati na SQL Serveru:

EXEC sp_fulltext_service @action='load_os_resources', @value=1;
EXEC sp_fulltext_service 'update_languages';
EXEC sp_fulltext_service 'restart_all_fdhosts';

Da li je registracija urađena možete da proverite sa:

SELECT document_type, path FROM sys.fulltext_document_types WHERE document_type = '.pdf'

Trebalo bi da se dobije zapis kao na slici 2:

FullTextS1

Slika 2. Potvrda da je PDF iFilter registrovan na SQL Serveru

Sledeći korak je kreiranje tabele za insert dokumenata:

CREATE TABLE dbo.DocumentFiles
(
DocumentId uniqueidentifier Primary KEY DEFAULT newsequentialid(),
AddDate datetime NOT NULL,
Name nvarchar(50) NOT NULL,
Extension nvarchar(10) NOT NULL,
FileSource varbinary(MAX) NOT NULL DEFAULT (0x)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE dbo.DocumentFiles ADD CONSTRAINT
DF_DocumentFiles_AddDate DEFAULT sysdatetime() FOR AddDate

Zatim je potrebno kreirati Full-Text Search catalog i index nad konkretnom tabelom:

EXEC sp_fulltext_database 'enable'
GO
IF NOT EXISTS (SELECT TOP 1 1 FROM sys.fulltext_catalogs WHERE name = 'Ducuments_Catalog')
BEGIN
EXEC sp_fulltext_catalog 'Ducuments_Catalog', 'create';
END

 
DECLARE @indexName nvarchar(255) = (SELECT Top 1 i.Name from sys.indexes i
Join sys.tables t on i.object_id = t.object_id
WHERE t.Name = 'DocumentFiles' AND i.type_desc = 'CLUSTERED')

EXEC sp_fulltext_table 'DocumentFiles', 'create', 'Ducuments_Catalog', @indexName
EXEC sp_fulltext_column 'DocumentFiles', 'FileSource', 'add', 0, 'Extension'
EXEC sp_fulltext_table 'DocumentFiles', 'activate'
EXEC sp_fulltext_catalog 'Ducuments_Catalog', 'start_full'

ALTER FULLTEXT INDEX ON [dbo].[DocumentFiles] ENABLE
ALTER FULLTEXT INDEX ON [dbo].[DocumentFiles] SET CHANGE_TRACKING = AUTO

Na kraju je potrebno insertovati neki .pdf fajl u tabelu:

INSERT INTO [dbo].[DocumentFiles]
([Name]
,[Extension]
,[FileSource])
SELECT 'TestPDF' as [Name]
,'.pdf' as [Extension]
,* FROM OPENROWSET(BULK 'C:\Dokumentacija\TestPDF.pdf', SINGLE_BLOB) as [FileSource]

Pretraga po tekstu insertovanog .pdf fajla se može raditi upitom:

SELECT d.* FROM dbo.DocumentFiles d
WHERE Contains(d.FileSource, '%Reč ili niz reči po kojima radite pretragu%')

Leave a Reply

%d bloggers like this: