SQL FULL TEXT SEARCH PROGRAMMING (SQL’DE TÜM METİNDE ARAMA PROGRAMLAMA) -

Merhaba arkadaşlar,

Bu yazımızda SQL Programlamada Tüm Metinde Arama konu başlıklı yazımızı sizlerle paylaşacağız. SQL server üzerinde tablolarda metin tabanlı arama işlemlerini Tüm Metinde Arama servisiyle nasıl programlandığını göstereceğim. Bu metot özellikle günümüzde kullandığımız herhangi bir arama motorunda (search engine, örneğin; Google, yahoo,bing,yandex… gibi) aradığımız kelime bazlı veya cümle sorgularımızda karakter bazlı aradığımız cümle veya kelimelerin geçtiği ilgili sitelerden indexlenip karşımıza sonuç olarak sunması gibi bir çok fonksiyonellikte kullanılmaktadır.

Bu Metoda Niçin İhtiyaç Duyarız?

Ticari veri tabanı topluluğu öncelikle yapılandırılmış veriler üzerinde, yapılandırılmış depolama uygulamalarının ihtiyaçlarını daha etkili bir bütün olarak ele almayı sanayi odaklı hale getirmiştir.

Buna rağmen, her yıl göz önüne getirildiğinde verilerin büyük bir çoğunluğunun sadece küçük bir kısmı tamamen yapılandırılmış ya da yarı yapılandırılmış şeklindedir. Örneğin belgelerin, web sayfalarının, elektronik tabloların, e-posta ve diğer zayıf yapılandırılmış formatlar örnek olarak gösterilebilir.

Bu anlamda bu yazımız Tüm Metinde Arama erişim metodunun yeteneklerinden ve özelliklerini siz değerli okurlarımıza sunacak alt yapıda hazırlanmıştır.

SQL SERVER DESTEĞİ

İşlemlerimizi SQL Server 2008 Studio Management R2 ile gerçekleştireceğimizden dolayı; SQL 2008 sunucusu İngilizce, İspanyolca, Çince, Japonca, Arapça ve Hindu dilleri gibi 50’den fazla farklı dil için karakter tabanlı veriye karşı Tam Metin sorgularına kullanıcı ve uygulamalar için fonksiyonellik sağlar.

1. Desteklediği her bir dil için SQL Sunucu, bir boş eş anlamlılar dosyası, sözcük ve sözcük kökü ayırıcısı dâhil olmak üzere o dile özgü dil bileşenleri sağlar.

2. Her bir tam metin dili için SQL sunucu aynı zamanda, isteğe bağlı, herhangi bir dosya üzerinde bir eş anlamlılar dosyası olarak arama sorgusunun kapsamını genişletmek için dile özgü eş tanımlamalar sağlar.

3. Tüm metin sorguları yazmak için SQL sunucu, tam metin yükleminin (CONTAINTS ve FREETEXT) ve sıra oluşumlu fonksiyon değerlerinin  (CONTAINSTABLE ve FREETEXTTABLE) set edilmesini sağlar.

SQL sunucuda tam metin filtre arka plan servisini çalıştırmak için aşağıdaki gibi çalıştırmamız gerekmektedir.

Bilgisayar --> Yönet --> Hizmetler ve Uygulamalar --> Hizmetler --> SQL Full-Text Filter Daemon Launcher
Yolu izlenilerek söz konusu servisin çalışmasına aşağıdaki gibi ihtiyaç duyar.

SQL sunucu bir sistemi durdurma listesini de sunmaktadır. Geliştiriciler, durdurma kelimeleri silme ve ekleme yoluyla sistemin durdurma listesini değiştirebilir. Bunlar etkisiz sözcükler olarak da bilinmektedir.

FULL TEXT INDEX

Her Full text index esas tablodan (base table) bir veya birden fazla sütunu indexler. Ve her bir sütun bir özgü dile sahiptir. Biz bir veri tabanında bir tablo veya indexlenmiş view üzerinde full-text index oluşturabiliriz. Sadece bir Full-text index, indexlenmiş view veya tablo başına erişim izni verir. Index 1024 sütuna kadar içerebilir.

Full-text index, tabloda bir veya daha fazla karakter içerir. Bu sütunlar herhangi bir veri tipi içerebilir. Örneğin; char, varchar, nvarchar, nchar, text, ntext, image, xml, varbinary veya varbinary(max) gibi… 

GELİŞTİRME AŞAMALARI

Full Text Search (Tüm metinde arama) programlamayı implement edebilmek için aşağıdaki aşamaları uygulamamız gerekmektedir.

• Data Setup

Klasik bir örnek vererek gerekli işlemlerimizi gösterelim. Örneğin, tblPersonel adlı bir tablomuzda aşağıdaki gibi sütunlarımız olsun. 

Amacımız, herhangi bir alanda serbest akış arama ile personele ait kayıtların aranmasıdır.

Örneğin; Kullanıcı, tblPersonel tablosundan arama için bir isim olarak (‘Muhammed’) girebilir. Sonuç, personelAdi, personelSoyadi, yoneticiAdi, yoneticiSoyadi’Dn dönebilir.

Örneğin; Eğer kullanıcı ‘Muhammed ÖNAL’ girerse, tblPersonel tablosunda herhangi bir isim sütunundan Muhammed veya ÖNAL ikisini de tam metinde arama metoduyla getirir.  Aşağıdaki gibi 3 kayıt girdisi girdiğimiz tblPersonel tablomuz bulunmaktadır.

B- Full Text Column Setup

Full text search işlemini başlatmak için, full-text’de include etmek istediğimiz her bir metin sütununun konsolide veriler olarak full-text sütununda tanımlamamız gerekmektedir.

Örneğin;
tblPersonel tablosuna yeni bir sütun ekleyelim. Bu sütunun adı da FullTextDegeri olsun.

Yukarıdaki gibi yeni eklediğimiz FullTextDegeri sütunundaki verileri doldurmak için aşağıdaki sorgumuzu çalıştırdığımızda bize izin verecektir.

UPDATE tblPersonel 
SET FullTextDegeri = [personelID] +
                             +' '+ [personelAdi] 
                             +' '+ [personelSoyadi]
                             +' '+ [yoneticiAdi]
                             +' '+ [yoneticiSoyadi]
                             +' '+ [bolumAdi]
                             +' '+ [Lokasyon]
                             +' '+ [Ulke]

Ve sonuç aşağıdaki gibi olacaktır.

C- Full Text Catalog Setup

Bir Full-Catalog, full-text indekslerini düzenlemek için bir mekanizma sağlar. Her bir katalog 0 (sıfır) veya daha fazla indeks içerir, fakat her indeks sadece bir tek katalog ile ilişkilendirilebilir. Bir Full-Text Catalog, full-text indekslerinin bir grubuna vurgu yapan mantıksal bir konsepttir. Katalog, bir dosya grubu ile ilişkilendirilebilir değildir.

Başparmak kuralı olarak, eğer verilen sütun binary veri olarak belgeleri depolamayı içeriyorsa, bu durumda yapmamız gereken her bir belgenin çeşidini sütun dizininde bir tablo sütununda tanımlamamız gerekmektedir.

Yeni full text catalog oluşturmak için SQL Sunucumuz üzerinde oluşturduğumuz Deneme adlı veri tabanımızın Storage bölümüne giriyoruz. Ve buradan da Full Text Catalogs sekmesine sağ tıklıyoruz. ( Aşağıdaki şekli inceleyiniz)

Katalog sistemi, Katalog içeriğini depolamak ve aramak için SQL Sunucu Full Text Catalog özelliğini kullanır.

FT_Personel adında yeni bir Full-Text Catalog oluşturmak için şekildeki gibi sihirbaz oluşturacağız: OK butonuna tıklayarak yeni bir Full-Text Catalog oluşturmuş oluruz.

Yeni Katalog oluşturma sihirbazımız böylelikle başarıyla oluşturulmuş olur. Böylelikle Full Text Index tanımı (Define Full Text Index) ile devam edebiliriz.

D- Full Text Index Setup

Evet değerli okurlarımız, Mevcut Deneme adlı veri tabanımızda bulunan mevcut tblPersonel tablosu ile eşleşen Full-text Index tanımını bu tablo üzerinder oluşturabiliriz.

Tüm Metinde Arama algoritması ile tblPersonel tablosuna ulaşırız. tblPersonel tablosu üzerine sağ tıklayarak, Full-Text Index menüsünden Define Full-Text Index alt menüsünü seçeriz. Aşağıdaki şekle baktığımızda izlenilen yol belirtilmiştir.

Define Full-text Index.. alt menüsüne tıklanıldığında karşımıza aşağıdaki gibi bir sihirbaz formu çıkacaktır. 

Açılan sihirbaz formunda next butonunu tıkladığımızda karşımıza başka bir form çıkacaktır.

İşlemin ilk aşaması;

Karşımıza çıkan form üzerinde Unique Index, benzersiz indeks drop down bulunmaktadır. Bu drop down kutusundan PK_tblPersonel seçilir.

tblPersonel tablosunun PK(Primary Key- Birincil Anahtar) personelID idi.

İlgili Unique Index kutubuzdan ilgili tablomuzu seçtikten sonra bir sonraki next işleminde karşımıza çıkan form üzerinde Full Text sorgularımızı gerçekleştirmek için tablomuz üzerinde sütunlarımızı seçmeye ihtiyaç duyarız.

Geliştiriciler aşağıdaki şekilde verilen sütunlardan birden fazla seçim işlemi yapabilmektedirler. Örneğimiz için gerekli sütun olarak FullTextDegeri sütununu seçeceğiz.

Bir diğer önemli aşama ise, Mevcut bir Full-Text Catalog seçmektir. Eğer mevcutta yok ise, SQL bu noktada check kutusu yardımıyla yeni bir katalog oluşturmayı sağlar.

Bizim oluşturduğumuz Full Text Catalog adı FT_Personel idi. Bu anlamda oluşturduğumuz  Full text Catalogumuz olan FT_Personel kataloğunu seçeriz.

Next butonunu takip ederek Finish butonuyla tüm işlemlerimizi bitirerek aşağıdaki şekilde verile gibi bir Başarılı sonuç elde ederiz.

Görüldüğü gibi herhangi bir hata veya uyarı verilmeden başarılı bir şekilde işlemimizi gerçekleştirmiş olduk.

E- Full Text Query

Bütün işlemlerimizi buraya kadar tamamlamış olduk. Şimdi, Son adım olarak Tüm Metinde Arama sorgumuzun sonucunu yazabiliriz:

SELECT tablo.*
FROM tblPersonel tablo
     JOIN containstable(tblPersonel, FullTextDegeri, '"1"') as key_tbl
     ON tablo.personelID = key_tbl.[Key]
     ORDER BY key_tbl.[Rank] asc

Ve sonuç:

Bu yolla, Tüm metinde Arama mekanizmasını elde etmek mümkündür.

Bugünkü makalemizde sadece ve sadece SQL sunucu tarafındaki işlemleri görmüş olduk. Bir sonraki Yazımızda bu konuya dair Mimari yapıyı ve ASP.NET ile ADO.NET ortamında C# programlama dili ile küçücük bir uygulama geliştireceğiz.

Muhammed ÖNAL
Bilgisayar Mühendisi
RFID Sistemleri Uzmanı
BT Yönetişimcisi

Blog:  http://www.muhammedonal.com
Web : http://www.yazilimdilleri.net