SQL Server ve Indexing Service ile Gelişmiş Arama Teknikleri

İnternet sitelerinde arama yapmak sadece sayfa aramasından ibaret değil. Veritabanında sakladığınız ürün, haber gibi bilgileri ararken, sayfalarınızı da bu arama kümelerine dahil edin ve uyumluluk oranlarını listeleyin. SQL Server, Dizin Hizmeti (Indexing Service) ile herhangi bir veritabanındaki kayıtları tek bir kayıt kümesi olarak elde edebilmemizi ve arama bilgisine uygunluk oranlarını listeleyebilmemizi sağlıyor.

• SQL Server ile Dizin Hizmetini (Indexing Service) birleştirmek
• Farklı veritabanlarını SQL Server'da birleştirmek
• Veritabanında ve sayfalarda serbest metin (free-text) araması yapmak
• Siteyi ASP ile aramak

SQL SERVER İLE DİZİN HİZMETİNİ (INDEXING SERVICE) BİRLEŞTİRİN

Birçok web sitesinde veritabanlarında ve site içindeki dosyalarda ayrı arama işlemleri yapılıyor ve bilgiler ayrı olarak listeleniyor. Örneğin ürün ve haber bilgilerinin veritabanında tutulduğu bir site için, bu bilgilerle birlikte dosyalarda arama yaparken ayrı arama sayfaları kullanılıyor çoğunlukla. Burası birçok kişi için sürpriz olacaktır, çünkü SQL Server ve Indexing Service kullananlar, hem sayfa araması, hem de veritabanı araması sonuçlarını harmanlayıp tek bir tabloda birleştirebilirler.

Indexing Service için Windows NT’nin ya da Windows 2000’in kendisi yeterliydi. Sisteminizde SQL Server 7.0
veya üstü sürümlerinden biri varsa, ek bir maliyet olmadan bu tekniği kullanabilirsiniz.

Akıllı SQL Server, sistemdeki veri dağıtıcılardan veri okuyabiliyor. Indexing Service’i dağıtan MSIDXS dağıtıcısını SQL Server içine iliştirdikten sonra SQL Server’ın içinden Indexing Service’teki her veriye ulaşmak mümkün.

SQL Server’da aşağıdaki kodu çalıştırmak gerekiyor.

EXECUTE sp_AddLinkedServer IndexService1,
'Index Services',
'MSIDXS',
'Web'

En alt satırdaki ‘Web’ adlı parametre Indexing Service içindeki katalog adını ifade ediyor. ‘IndexService1’ adı ise veritabanına burada verdiğimiz isim. Burada yapılan işlem, servis içindeki bir kataloğun SQL Server içine iliştirilip buradan sorgulanabilmesini sağlıyor.

SELECT *
FROM OPENQUERY(IndexService1, 'SELECT FileName, DocTitle, DocAuthor,
Size, Directory FROM SCOPE()')

Daha önce PC Magazine’deki yazılarımızı takip eden okuyucular, Access içine SQL Server, Paradox, Excel gibi veri kaynaklarındaki tablolarla birlikte Outlook Express’teki klasörleri ve metin dosyalarını tablo halinde ekleme ile ilgili örnekleri hatırlayacaklardır.

Bu, sadece Access’e özgü bir şey değil. SQL Server da başka veri kaynaklarını alıp sorgulama yeteneğine sahip. Access’teki tablo bağlama işleminin benzerini, SQL Server’da sp_AddLinkedServer adlı stored procedure yapıyor. Access, Excel ve metin dosyaları dahil, Oracle ve DB2 gibi sistemlerdeki veritabanları da aynı şekilde SQL Server içine bağlanabiliyor.



Aşağıdaki komut metin dosyalarının SQL Server’a bağlanıp sorgulanmasını sağlıyor.

sp_addlinkedserver
TextSorgu,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\veriler',
NULL,
'Text'

C:\Veriler klasöründeki metin dosyalarını şu şekilde sorgulayabileceksiniz böylece:

SELECT *
FROM OPENQUERY(TextSorgu, 'SELECT * FROM deneme.txt')

Deneme.txt dosyasının kutudakine benzer şekilde düzenlenmiş olması gerekiyor. İlk satırda veri alanlarının isimleri yer almalı.

Veriler.txt
ad;sehir;yas
ali;istanbul;24
osman;ankara;35
tuğba;izmir;27

Bu tür denemeler sonrasında bazı veritabanı bağlantıları oluşacaktır. Tüm bağlantıların listesini görmek için

  sp_linkedservers

gereksiz bağlantıları iptal etmek için

  sp_dropserver 'TextSorgu'

komutlarını kullanın.





Sp_AddLinkedServer ile diğer SQL Server, Oracle, DB2, Access, Excel ve Indexing Service veritabanları içeri bağlanabiliyor. Bunların dışında bir bağlantı gerektiğinde ODBC’yi kullanabiliyorsunuz.

ODBC’de tanımlı bir veritabanını SQL Server’ın içine aşağıdaki gibi ekleyebilirsiniz.

EXEC sp_addlinkedserver
'Yeni_Ad',
'',
'MSDASQL',
'ODBC_Adi'

ODBC yoluyla sağlanacak bağlantılar için ODBC’de DSN tanımlamaya her zaman gerek yok. Bağlantı dizesi belirtmek de yeterlidir.

EXEC sp_addlinkedserver
'Yeni_Ad',
'',
'MSDASQL',
NULL,
NULL,
'DRIVER={SQL Server};SERVER=Sunucu_Adi;UID=sa;PWD=;'


Dışarıdan veritabanı bağlantıları için Enterprise Manager’da Linked Servers bölümü kullanılıyor. Kayıtlı SQL Server sunucusunun Security ağacı altından buraya ulaşabilirsiniz.




Bağlı her bir veritabanı için sağ tuş menüsündeki Properties (Özellikler) komutunu kullanın. Yeni bir bağlantıyı burada tanımlamak için Linked Servers üzerine gelip New Linked Server komutu verin. Bağlı veritabanlarını yönetmek için burası daha elverişlidir. Yukarıdaki stored prosedürler ise bunu programlamak isteyenler içindi.




SORGULAMA İÇİN FARKLI VERİTABANLARINI BİRLEŞTİRİN

Buraya kadar Sql Server’ın içinde iken Indexing Service’i ve diğer dış veritabanlarını kolayca sorgulayabildik. Peki dışarıdan alınan kayıtlar ile içerideki tablolarımızda bulunan kayıtları nasıl birleştireceğiz?

Bu tür bir birleştirme işleminin bizi farklı arama sayfaları yapmaktan kurtaracağını söylemiştik. SQL dilindeki Union deyimi birleştirme işlemini yapıyor. İki ya da daha fazla tablo için aşağıdaki gibi bir SQL kodunu kayıt kümesi olarak elde edebiliyoruz.

SELECT * FROM Musteriler1
UNION
SELECT * FROM Musteriler2
UNION
SELECT * FROM Musteriler3

Buradaki temel kurallardan biri, ayrı Select cümlelerinden elde edilen alt kümelerdeki aynı sırada bulunan veri alanı adlarının, en azından türlerinin birbirleri ile uyumlu olmaları gerektiği. SQL Server’da olmasa da Access’teki Union sorgularında sonuç listesinin tüm alanlara göre yeniden sıralandığını da belirtelim.

Peki web sitesindeki dosyaların listelendiği bir tablo ile örneğin bir ürün tablosunu nasıl ve neye göre birleştireceğiz? Farklı veri alanları içeren bu tabloları birleştirmeye çalışmak ne anlama geliyor?

Bu yöntem web sitesindeki sayfaları ve ürünleri birleştirip harmanlayacağı için arama sayfasını bölmeye, farklı arama bölümleri oluşturmaya gerek kalmayacak. Sitenizde arama yapan ziyaretçinize sadece sayfaları aratmaktan daha fazlasını yaptırmış olacaksınız.

Karşılıklı gelen veri alanlarının aynı türde olması UNION sorgularının doğru çalışması için yeterlidir. Sonuç listesindeki kayıtların kaynağını ayırdetmek için her bir Select cümlesinde Kaynak adlı yeni bir alan tanımlayabilirsiniz. Buna daha sonra ihtiyacınız olacaktır, zira arama sonuçlarını listelerken linkler uygun bir şekilde tanımlanmalıdır. Kaynak değeri Urun ise urun sayfalarına, IndexServer ise ekteki adrese yönlendirmek gerekir.


SELECT TOP 100 * FROM (
SELECT TOP 50 'Urun' as Kaynak, RankTablo.Rank as Rank,
ID, UrunAdi as Baslik, Grup
FROM Urunler INNER JOIN FREETEXTTABLE(Urunler,*, 'windows') as RankTablo
ON Urunler.ID=RankTablo.[Key]
UNION
SELECT 'IndexServer' as Kaynak, *
FROM OPENQUERY( IndexService1, 'SELECT RANK, HITCOUNT, DOCTITLE, PATH FROM SCOPE()
WHERE Directory NOT LIKE ''%_vti_%'' and FREETEXT (''windows'')' )
) as Liste
ORDER BY Rank DESC





Yukarıdaki sorgunun çalışabilmesi için SQL Server’da Free-Text Query özelliğinin kurulu olması gerekir. Bunu SQL Server’ın kurulumu sırasında seçebilir ya da sonradan kurabilirsiniz. Serbest metin (Free-text) arama özelliğini kullanmak istediğiniz tabloları önce yapılandırmak gerekir.

Bu tablolar için indekslenmiş serbest metin bilgileri ayrı kataloglarda tutulurlar ve biz serbest metin araması yaparken SQL Server bu katalogları kullanır. Katalog ile tablo arasında bağ kurulurken tablodaki benzersiz indekslenmiş veri alanları kullanılır. Bu yüzden serbest metin indekslemesinden önce tabloda Primary Key ya da Uniqu Index tanımlamalısınız.

Enteprise Manager’da tablonun sağ tuş menüsündeki Full-Text Index Table altında Define Full-Text Index Table komutunu seçin. İlk aşamada bağlantı sağlayacak Primary Key ya da Unique Index’lerden birini seçeceksiniz. Sonraki kısımda hangi metin alanlarının serbest metin aramasına dahil edeceği seçilmeli. Diğer seçenekleri geçerli haliyle bırakıp kataloğu adlandırdıktan sonra, tablo serbest metin aramaları için hazır hale gelir.



Tabii ki Urunler tablosu için Free Text özelliği kullanmak zorunda değilsiniz. UNION’un üst tarafındaki bölümde sade bir Select cümlesi de yazabilirsiniz. Arama sonuçlarımızın işe yarar ve mantıklı olması için SQL Server’da Free Text özelliğini aktif ettik ve Rank alanını sonuçlara yakınlığı ölçmek için kullandık. ID alanını ise IndexServer satırlarında HitCount değeri olarak kullanıyoruz.

Normal şartlarda JOIN deyimi ile beraber FREETEXTTABLE deyimleri gerekli değildir. Koddaki karmaşıklığın sebebi ürünler tablosu için Rank değerini elde etmek istememizdi. Madem sayfalardaki rank değeri listeleniyor, sorgunun gerçek anlamını kazanması için ürünlerdeki rankın elde edilmesi ve sayfa sonuçları ile beraber harmanlama yapılıp genel bir rank sıralamasının yapılması gerekiyordu.

Veri tablolarında, Indexing Service’tekinin aksine doğrudan rank değeri elde edilemiyor. Bunun için Urunler tablosunun FreeText kayıtlarını tutan ek tabloya INNER JOIN bağlantısı açmak yeterlidir.

FreeTextTable komutu Urunler tablosundaki * alanları için ‘windows’ kelimesini aratıyor ve sadece Key ve Rank alanlarını veriyor. Key alanı Urunler tablosunda Primary Key olarak işaretlenmiş ID alanını işaret ediyor. FreeText tabloları sonuçları listelerken Primary Key ya da Unique olarak indekslenmiş veri alanlarından faydalanıyorlar.

FREETEXTTABLE komutu ile gelen kayıt kümesindeki KEY alanını Urunler’deki ID alanına bağlayıp Rank değerini elde ettik. Indexing Service ile gelen kayıtlarda zaten Rank alanı yer aldığı için ikisini birleştirip Rank değerine göre sıralayabiliriz. Tabii eğer Indexing Service’te serbest metin arama komutlarını kullanmazsanız Rank alanı, her kayıt için maksimum yani 1000 değerini alacaktır.

Sayfaları sorgulamada gizlilik ayarı tamamen size bağlıdır. Örneğin siteniz FrontPage ile yapılmış ise _VTI ile başlayan klasör adlarını sorgudan çıkarmanız gerekir. Ayrıca resim gibi dosyaları listeye koymamak tamamen size kalmış.

SELECT DOCTITLE, FILENAME, DIRECTORY FROM SCOPE() WHERE
(DIRECTORY NOT LIKE ''%_vti_%'') AND
(FILENAME LIKE “%.asp%” or
FILENAME LIKE “%.htm%” or
FILENAME LIKE “%.txt” or
FILENAME LIKE “%.doc” or
FILENAME LIKE “%.xls” )

SQL Server’ın aksine Indexing Service’te serbest metin sorgulaması yapmak için bir yapılandırmaya gerek yoktur. Bu sorgularda FREETEXT, CONTAINS, MATCHES gibi komutlarla çok ayrıntılı arama sonuçları üretebilirsiniz.

Gelişmiş arama teknikleri bu yazıdakilerle sınırlı değil. Sadece SQL Server’daki arama özellikleri bile bir makaleye sığdırılamayacak kadar fazladır. Indexing Service’te sorgulama için Jim Buyens’in Arkadaş Yayınları tarafından Türkçe’ye çevrilip basılmış Adım Adım Web Veritabanı Geliştirme kitabı size yardımcı olabilir.


ASP İLE SİTENİZİ ARAYIN

ASP ile web alanında arama yaparken Indexing Service’teki kataloglardan birini seçebiliyorsunuz. Site ile veritabanı aramasını birleştirdiyseniz doğrudan SQL Server üzerinde işlem yapmalısınız.

Indexing Service’te akla gelebilecek her türlü sorgulama yapılabiliyor. Örneğin kataloglarınıza ait belli klasörleri ayrı olarak arayabilir, alt klasörlerin de aramaya dahil edilip edilmeyeceğini belirtebilirsiniz. SCOPE deyiminden sonraki parantezlerin içinin boş olması, geçerli olarak tüm kataloğun aranması gerektiğine işaret eder.

Kataloglanmış klasör içindeki alt klasörleri doğrudan aramak için SCOPE deyiminden sonra adlarını virgül ile ayırarak yazın.

SELECT Doctitle FROM SCOPE(' "c:\web\xml", "c:\web\marjinal" ')

Tüm klasörler için alt klasörlerin aranmasını istemiyorsanız klasör adlarının öncesine SHALLOW TRAVERSAL OF deyimini bir kez yerleştirin.

SELECT Doctitle FROM SCOPE(' SHALLOW TRAVERSAL OF "c:\web\xml", "c:\web\marjinal" ')

ADO ile SQL Server’ı ve Indexing Service’i sorgulamak için Connection.Open eyleminde aşağıdaki şablonları kullanın.
 

Set Conn=CreateObject(“ADODB.Connection”)

‘SQL Server için.
Conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=username;pwd=parola;Initial Catalog=Database_Adi;Data Source=Sunucu_Adi"

‘Indexing Service için.
Conn.Open "Provider=MSIDXS; Data Source=Katalog_Adı"
 

Indexing Service’i SQL Server’a bağladıysanız SQL Server için olan bağlantıyı kullanın.

Serbest metin aramaları yaparken arama metinlerinin geçerliliğini kontrol etmek size kalmıştır. Arama bilgisini sonuç sayfasındaki arama kutusuna tekrar yerleştirmek istiyorsanız birkaç kontrol daha yapmanız lazımdır. Örneğin arama metnine javascript kodu yerleştirilebilir ya da ziyaretçinin yazdığı metindeki bazı karakterler hata oluşturabilir. Tabii bu sunucu tarafını hiçbir şekilde etkilemez ama ziyaretçi üzerinde kötü bir izlenim bırakacaktır. Bizim arama sayfamızın yapısı aşağıdakine benzerdir. Kendi sayfanızı bu sıralamaya uyarak oluşturabilir ya da aşağıda linkini verdiğimiz dosyayı alıp değiştirerek kullanabilirsiniz.

ARAMA SAYFASI

<%
Dim Ara
Ara= Request("Ara")
If Ara<>"" then
  Ara= Replace(Ara,"<","(")
  Ara= Replace(Ara,">",")")
end if
%>

<!-- Arama kutusunu yerleştir. (Değer=Ara) -->
<INPUT NAME="Ara" VALUE="<% =Ara %>">

<%

If Ara<>"" then

  Dim Conn
  Conn.Open "..."

  On Error Resume Next

  Dim Rst
  Rst.Open "...", Conn, 1, 3

  If Err.Number > 0 then
    Response.Write "Geçerli bir arama metni girmelisiniz."
  elseif Rst.Eof then
    Response.Write "Aradığınız kritere uygun sayfa bulunamadı."
  else
    On Error Goto 0
    '''Sayfaları listele.
  end if

end if

%>




Örnek arama sayfamızın kodu arama.asp.txt dosyasında.


Sayfa ve veritabanı aramasını birleştirdiğinizde link bilgisini uygun bir şekilde belirtmelisiniz. Bulunan kayıt veritabanından gelen bir ürün kaydı ise linki ürün sayfalarına yönlendirmek gerekir.

Yukarıdaki UNION sorgusunun resminde Kaynak sütunundaki değerlerin IndexServer ve Urun olduğuna dikkat edin. Listelemeyi yaparken bu değere bakıp linki belirleyeceğiz.

<% If Rst("Kaynak")="IndexServer" then %>
<A HREF="http://www.chip.com.tr/<% =rst("FILENAME") %>"
<% =Rst("Baslik") %>
</A>
<% else %>
<A HREF="http://www.chip.com.tr/urun/goster.asp?ID=<% =rst("ID") %>"
<% =Rst("Baslik") %>
</A>
<% end if %>
 

Ek: arama_asp.txt

Serkan ŞAHİNOĞLU
(Chip Dergisi, Mart 2002)