Metruk
Root Admin
Mesaj Sayısı : 351
Kayıt tarihi : 02/04/10
Yaş : 32
 |  Konu: SQL Server ve Indexing Service ile Gelişmiş Arama Teknikleri  Salı Nis. 06, 2010 3:05 pm | |
| İ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.
[Resimleri görebilmek için üye olun veya giriş yapın.]
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.
[Resimleri görebilmek için üye olun veya giriş yapı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.
[Resimleri görebilmek için üye olun veya giriş yapın.]
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.
[Resimleri görebilmek için üye olun veya giriş yapın.]
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
[Resimleri görebilmek için üye olun veya giriş yapın.]
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.
[Resimleri görebilmek için üye olun veya giriş yapın.]
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
%>
[Resimleri görebilmek için üye olun veya giriş yapın.]
Örnek arama sayfamızın kodu [Linkleri görebilmek için üye olun veya giriş yapın.] 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 %> | |
|
