PDF DOC XLS vs. Dokümanları SQL İçinde İndekslemek ve İçerik Araması Yapmak
429 views
Skip to first unread message
Abdulkadir Bener
Jun 19, 2013, 2:35:22 PM6/19/13
to bnr...@googlegroups.com
İşinize yarar mı bilmem; ama bu yazıyı hazırlamak geldi içimden.
Yakın zamanda uyguladığım için işlemle ilgili deneyimimi paylaşmak istedim.
Dokümanları SQL içine kaydetmek, ardından doküman içinde geçen kelimelere göre arama yaparak bir tür kütüphane oluşturmak nasıl bir işlem gerektirir tam olarak bununla ilgili biraz bilgi paylaşmak istiyorum.
Önce ihtiyacı biraz daha netleştirelim.
SharePoint'i biliriz hepimiz. Çok başarılı bir doküman arşivleme altyapısı da vardır. Dosyayı upload edersiniz. Sonra bir "ARAMA" yaparsınız bir de bakıverirsiniz ki dokümanlarınızın içinde geçen kelimeleri bir çırpıda bulup getirmiş karşınıza ve ilgili dokümanlar anında ekranınızda.
Peki bu nasıl oluyor da DOC, XLS, PDF, TXT vs. dosyalar SQL içinde kaydedilirken içeriğine göre indeksleniyor ve SELECT sorguları kullanılarak hemen aradığımızı bulabiliyoruz.
İşlem biraz uğraştırıcı ama sonuçlarına değer.
Ön gereksinimler:
Öncelikle bu iş için SQL 2012 öneririm, en azından SQL 2008 R2 olmasında yarar var. SQL 2012 öneririm zira kelimeleri ararken Türkçe wordbreaker kullanılabiliyor, ve semantic arama fonksiyonu sayesinde birbirine benzeyen dokümanlar tespit edilebiliyor. Bu başka bir yazının konusu olsun.
SQL Server kurulum yapılırken Full-Text servislerini kurmuş olmalıyız, veya sonradan bu servisler eklenmiş olmalıdır.
İlgili sunucuda Windows Indexing servisleri mutlaka çalışıyor olmalı. SQL Server dokümanları indekslemek için işletim sisteminin indexing servislerinden yararlanmaktadır.
Normal şartlarda 150'den fazla doküman türü Wİndows Indexing servisleri tarafından (ve dolayısıyla SQL tarafından) indekslenebilmektedir. Ancak PDF dokümanlarla ilgili bir sıkıntı da mevcuttur. PDF Adobe ürünü olduğu için Adobe firmasının yazdığı filtre kullanılmalıdır. Tam bu noktada çok dikkatli olunması gereken bir ayrıntıdan bahsetmem lazım:
Adobe'un Acrobat'ını kurduğunuzda normalde indexing servisi pdf dokümanları indeksler ama eğer SQL'iniz 64 bit ise bu filtre maalesef işe yaramamaktadır. Aşağıda bunu nasıl aşacağınızdan başlayarak işlemleri adım adım açıklamaya çalıştım.
Umarım faydalı olur.,.
(1)
SQL'inizin 64 bit olduğunu varsayarak 64 bit PDF filtresi için
http://www.adobe.com/support/downloads/thankyou.jsp?ftpID=4025&fileID=3941
Bu dosyayı indirip kurun derim. Sunucuda bir şekilde Acrobat Reader varsa ilk önce bunu kaldırmanız gerekecek.
http://www.adobe.com/support/downloads/thankyou.jsp?ftpID=4025&fileID=3941
Bu dosyayı indirip kurun derim. Sunucuda bir şekilde Acrobat Reader varsa ilk önce bunu kaldırmanız gerekecek.
(2)
My Computer (Bilgisayarım) sağ tıklayın Properties (Özellikler) i açın.
System Properties (Sistem Özellikleri)'ni açıp Advanced (Gelişmiş) sekmesine geçin.
Alttaki listeden "Path" değişkenini bulup Edit (Düzenle) ile açın.
Kutudaki yazının sonuna noktalı virgül koyup (;) yeni bir path olarak
C:\Program Files\Adobe\Adobe PDF iFilter 9 for 64-bit platforms\bin
ekleyin ve kaydederek kapatın.
My Computer (Bilgisayarım) sağ tıklayın Properties (Özellikler) i açın.
System Properties (Sistem Özellikleri)'ni açıp Advanced (Gelişmiş) sekmesine geçin.
Alttaki listeden "Path" değişkenini bulup Edit (Düzenle) ile açın.
Kutudaki yazının sonuna noktalı virgül koyup (;) yeni bir path olarak
C:\Program Files\Adobe\Adobe PDF iFilter 9 for 64-bit platforms\bin
ekleyin ve kaydederek kapatın.
(3)
Bu adımda önce SQL servisini restart edip işletim sisteminde tanımlı indeks filtrelerinin SQL'e yüklenmesini sağlamalıyız:
exec sp_fulltext_service 'load_os_resources', 1;
exec sp_fulltext_service 'verify_signature', 0;
(4)
SELECT * FROM sys.fulltext_document_types WHERE document_type='.pdf'
Bu sorguyu çalıştırdığınızda sonuç geliyorsa kurulum tamamlanmıştır.
Görünmüyorsa önce SQL Server'ı yeniden başlatıp yeniden sorgulayın.
Hala göremiyorsanız sunucuyu yeniden başlatıp tekrar sorgulayın.
Buna rağmen göremiyorsanız "Adobe PDF iFilter" programını uninstall edip
yeniden kurun ve 2.adımdan tekrar deneyin. Unutmayın SQL ve iFilter her ikisi de 64 bit olmalı.
SELECT * FROM sys.fulltext_document_types WHERE document_type='.pdf'
Bu sorguyu çalıştırdığınızda sonuç geliyorsa kurulum tamamlanmıştır.
Görünmüyorsa önce SQL Server'ı yeniden başlatıp yeniden sorgulayın.
Hala göremiyorsanız sunucuyu yeniden başlatıp tekrar sorgulayın.
Buna rağmen göremiyorsanız "Adobe PDF iFilter" programını uninstall edip
yeniden kurun ve 2.adımdan tekrar deneyin. Unutmayın SQL ve iFilter her ikisi de 64 bit olmalı.
(5)
Aşağıdaki komutları çalıştırıp bir veritabanı ve tablo hazırlayın:
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE [dbo].[testdosyalar](
[id] [int] IDENTITY(1,1) NOT NULL,
[aciklama] [nvarchar](200) NULL,
[uzanti] [varchar](50) NULL,
[dosya] [varbinary](max) NULL,
CONSTRAINT [PK_testdosyalar] PRIMARY KEY CLUSTERED ([id] ASC) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Aşağıdaki komutları çalıştırıp bir veritabanı ve tablo hazırlayın:
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE [dbo].[testdosyalar](
[id] [int] IDENTITY(1,1) NOT NULL,
[aciklama] [nvarchar](200) NULL,
[uzanti] [varchar](50) NULL,
[dosya] [varbinary](max) NULL,
CONSTRAINT [PK_testdosyalar] PRIMARY KEY CLUSTERED ([id] ASC) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
(6)
SQL Server Management Studio ile TEST veritabanına bağlanın.
testdosyalar tablosunu sağ tıklayın "Full Text Index" altındaki
"Define Full Text Index" seçeneğini tıklayın.
Next tuşu ile "Select Table Columns" ekranına kadar gelin.
"dosya" yazan satırı işaretleyip "Language for word breaker" kolonunu "Turkish" yapın.
(Not: SQL 2012'de "Turkish" var ancak 2008'de yoktur, 2008'de "Neutral" seçin veya boş bırakın)
"Type" kolonunu "uzanti" yapın. Type kolonuna dosyaların uzantısını kaydetmek zorundayız.
Böylece SQL Server kelimeleri ayıklarken dosyanın tipini bilmiş olacak ve ona göre kelimeleri ayıklayacak.
SQL 2012'de bu ekranda bir de "Statistical Semantics" kolonu gelir. İşaretlerseniz
birbirine içerik olarak çok benzeyen veya aynı olan dokümanların tespiti mümkün olur.
Next ile devam edip "Select Change Tracking" seçeneğini "Automatically" olarak bırakın.
Next ile devam edip "Create a new catalog" seçili hale getirin.
New catalog kutusuna name alanına "testdosyalarcatalog" yazın.
"Accent sensitivity" seçeneğini "Sensitive" bırakın.
Türkçe aksanlı bir dil olmadığı için (yazıldığı gibi okunan bir dil olduğundan) buradaki
Accent Sensitive ayarının önemi de yok aslında.
Index filegroup seçeneğini dilerseniz ayarlayabilirsiniz.
Full-text stoplist default olarak <system> seçilidir. Bunu <off> yaparsanız tüm kelimeler indekslenir.
Ancak <system> bırakırsanız çok genel olan ve birkaç harften oluşan kelimeler indekslenmez.
Mesela one two three gibi kelimeler indekslenmez.
Next ile devam edip "schedule" ekranına gelin.
Katalog veya bu tablo için belirli aralıklarla indeksleme için zamanlayıcı ayarı
yapabilirsiniz.
Next ile devam edip "Finish" ile ayarlamayı bitirin.
SQL Server Management Studio ile TEST veritabanına bağlanın.
testdosyalar tablosunu sağ tıklayın "Full Text Index" altındaki
"Define Full Text Index" seçeneğini tıklayın.
Next tuşu ile "Select Table Columns" ekranına kadar gelin.
"dosya" yazan satırı işaretleyip "Language for word breaker" kolonunu "Turkish" yapın.
(Not: SQL 2012'de "Turkish" var ancak 2008'de yoktur, 2008'de "Neutral" seçin veya boş bırakın)
"Type" kolonunu "uzanti" yapın. Type kolonuna dosyaların uzantısını kaydetmek zorundayız.
Böylece SQL Server kelimeleri ayıklarken dosyanın tipini bilmiş olacak ve ona göre kelimeleri ayıklayacak.
SQL 2012'de bu ekranda bir de "Statistical Semantics" kolonu gelir. İşaretlerseniz
birbirine içerik olarak çok benzeyen veya aynı olan dokümanların tespiti mümkün olur.
Next ile devam edip "Select Change Tracking" seçeneğini "Automatically" olarak bırakın.
Next ile devam edip "Create a new catalog" seçili hale getirin.
New catalog kutusuna name alanına "testdosyalarcatalog" yazın.
"Accent sensitivity" seçeneğini "Sensitive" bırakın.
Türkçe aksanlı bir dil olmadığı için (yazıldığı gibi okunan bir dil olduğundan) buradaki
Accent Sensitive ayarının önemi de yok aslında.
Index filegroup seçeneğini dilerseniz ayarlayabilirsiniz.
Full-text stoplist default olarak <system> seçilidir. Bunu <off> yaparsanız tüm kelimeler indekslenir.
Ancak <system> bırakırsanız çok genel olan ve birkaç harften oluşan kelimeler indekslenmez.
Mesela one two three gibi kelimeler indekslenmez.
Next ile devam edip "schedule" ekranına gelin.
Katalog veya bu tablo için belirli aralıklarla indeksleme için zamanlayıcı ayarı
yapabilirsiniz.
Next ile devam edip "Finish" ile ayarlamayı bitirin.
(6)
Artık deneme için bir kayıt üretebiliriz. C sürücüsünde TEMP klasörü altında MySQL 5.6 kullanım
kılavuzu pdf dökümanının olduğunu varsayarak komutu aşağıdaki gibi yazıp çalıştırabiliriz.
Artık deneme için bir kayıt üretebiliriz. C sürücüsünde TEMP klasörü altında MySQL 5.6 kullanım
kılavuzu pdf dökümanının olduğunu varsayarak komutu aşağıdaki gibi yazıp çalıştırabiliriz.
DECLARE @pdf AS VARBINARY(MAX)
SELECT @pdf = CAST(BULKCOLUMN AS VARBINARY(MAX))
FROM OPENROWSET(BULK 'C:\TEMP\Refman-5.6-en.a4.pdf',SINGLE_BLOB) AS X
SELECT @pdf = CAST(BULKCOLUMN AS VARBINARY(MAX))
FROM OPENROWSET(BULK 'C:\TEMP\Refman-5.6-en.a4.pdf',SINGLE_BLOB) AS X
INSERT INTO testdosyalar (aciklama,uzanti,dosya)
SELECT 'My SQL 5.6 Kullanım Kılavuzu','.pdf', @pdf
SELECT 'My SQL 5.6 Kullanım Kılavuzu','.pdf', @pdf
(7)
Kontrol edip bir satır pdf dokümanın tabloda olduğundan emin olun:
SELECT * FROM testdosyalar
Kontrol edip bir satır pdf dokümanın tabloda olduğundan emin olun:
SELECT * FROM testdosyalar
(8)
Bir iki dakika bekleyin ve katalogun güncellendiğinden emin olun ve
aşağıdaki komutla PDF dosya içinde arama yapabildiğinizi görmek için test yapın:
SELECT * FROM testdosyalar
WHERE CONTAINS(dosya,'MySQL')
Bir iki dakika bekleyin ve katalogun güncellendiğinden emin olun ve
aşağıdaki komutla PDF dosya içinde arama yapabildiğinizi görmek için test yapın:
SELECT * FROM testdosyalar
WHERE CONTAINS(dosya,'MySQL')
(9)
Sonuç gelmiyorsa fulltext katalog güncellenmemiş demektir.
Manuel güncelleme için şunu çalıştırın:
USE TEST
GO
ALTER FULLTEXT INDEX ON testdosyalar START FULL POPULATION
GO
Sonuç gelmiyorsa fulltext katalog güncellenmemiş demektir.
Manuel güncelleme için şunu çalıştırın:
USE TEST
GO
ALTER FULLTEXT INDEX ON testdosyalar START FULL POPULATION
GO
Bu komut sonunda aşağıdaki hata geliyorsa servisi restart edip yeniden 8.maddeyi deneyin.
Mesaj:
Warning: Request to start a full-text index population on table or
indexed view 'testdosyalar' is ignored because a population is currently
active for this table or indexed view.
Mesaj:
Warning: Request to start a full-text index population on table or
indexed view 'testdosyalar' is ignored because a population is currently
active for this table or indexed view.
8.Maddedeki sorgu sonuç veriyorsa işlem tamam demektir.
Full Text sorgulama ile ilgili komutlar için BooksOnline'dan yardım alabilirsiniz.
(10)
Hayırlı olsun.
Hayırlı olsun.
Not: Dokümanları varbinary(MAX) alana SQL'in içine değil de FILESTREAM ile disk sisteminde tutmak arasında burada anlatılan işlemler açısından fark oluşmaz. SQL her ikisini de FullText ile indeksleyebilir.
Saygılar...
Reply all
Reply to author
Forward
0 new messages