Salam Dostlar.

Bugünkü məqalədə SQL Serverin çox funksional imkanlarından olan Full-Text Search, yəni tam mətnli axtarış sistemi ilə tanış olacağıq.

Full-Text Search nədir? Full-Text Search verilənlər bazasında mətn tipli verilənlərin arasından tam mətnli axtarış aparan SQL Serverin bir komponentidir. Tam mətnli sorğular müəyyən dilin (məsələn: İngilis və ya Türk) qaydalarına uyğun olaraq sözlərin və söz birləşmələrinin emalı yolu ilə mətn verilənləri arasında linqvistik axtarışını yerinə yetirir. Bu cür sorğularla verilənlər bazalarının cədvəllərindən bazaya əlavə olunmuş müxtəlif sənədlərə, məsələn: HTML, WORD, PDF və s. mətnlər üzrə axtarış etmək olar. Həmin sənədlərin mətnində sorğunun şərtində göstərilən mətn tapıldıqda uyğun gələn sənədlərin siyahısı sorğunun nəticəsində göstərilir.

Tam mətnli axtarışla nə etmək olar? Tam mətnli axtarış biznes proseslərin geniş diapazonunda tətbiq edilir. Məsələn: elektron biznesdə – veb saytda elementlərin axtarışı üçün; hüquq şirkətlərinin işində – hüquq məlumatlarının arxivində işlərin tarixlərinin axtarışı üçün; kadr şöbələrinin işində – saxlanan CV-lərdə işlərin təsvirlərinin tutuşdurulması üçün və s.

Full-Text Search SQL Serverin opsional, yəni vacib olmayan bir komponentidir. SQL Serverin quraşdırılması zamanı komponentlərin arasında seçilməlidir. Məsələn: SQL Serverin quraşdırılması zamanı biz belə bir pəncərə ilə rastlaşdıq:

Full-Text Search quraşdırılması üçün göstərilən pəncərədə “Full-Text and Semantic Extractions for Search” komponentini seçməliyik. Əgər quraşdırılma zamanı bu komponent seçilməyibsə SQL Serverin Setup faylını yenidən işə salıb bu komponenti əlavə olaraq quraşdıra bilərik.

Full-Text Search komponentin olub olmamasını necə yoxlayaq? Bunu iki üsulla etmək olar:

1-ci üsul – Start > All Programs > Microsoft SQL Server 2014 > SQL Server Configuration Manager keçirik. Açılan pəncərədə SQL Server Services hissəsində SQL Full-text Filter Daemon Launcher (MSSQLSERVER) görürüksə və qarşısında yaşıl ox işarəsi varsa deməli SQL Serverdə Full-Text Search komponenti quraşdırılıb və aktivdir:

2-ci üsul – aşağıda göstərilən sorğu ilə bunu yoxlamaq olar:

SELECT SERVERPROPERTY ('IsFullTextInstalled')

Sorğunun nəticəsi birdirsə –  Full-Text Search komponenti quraşdırılıb, sıfırdırsa – quraşdırılmayıb:

Gördüyünüz kimi, Full-Text Search komponenti Database Engindən ayrı olan bir komponentdir və onun işləməsi üçün ayrıca olaraq SQL Full-text Filter Daemon Launcher servisi istifadə olunur.

Tam mətnli axtarış istifadə etmək üçün ilk olaraq tam mətnli kataloqlar (Full Text Catalogs) və tam mətnli indekslər (Full Text Index) qurmaq lazımdır.

 Tam mətnli kataloq (Full Text Catalogs) quraq. Bunun üçün bir test bazasını yaradaq. Mən bazanın adını “MyTest” qoydum. Bu bazada sənədlərlə işləmək üçün sadə bir cədvəl yaradaq. Tutaq ki, HTML tipli sənədlərimiz var. Biz onları hər hansı bir qovluqda saxlayırıq, məsələn: D\SQLDATA\DOCS qovluğunda. Bu sənədləri verilənlər bazası cədvəlinə əlavə edə bilərik və onlara sorğu edə bilərik. Təbii ki, cədvəlin sütununda sənədin özü deyil onun qaynağı, yəni istinad linki yerləşdirilir:

--MyTest bazasini yaradiriq
USE master;
GO
IF EXISTS(select * from sys.databases where name='MyTest')
DROP DATABASE MyTest

CREATE DATABASE MyTest;
GO

USE MyTest;
GO

--Documents cedvelini yaradiriq
CREATE TABLE dbo.Documents (
 ID int identity (1, 1) NOT NULL,
 Title varchar (255),
 DocsContent varchar(max)
);
GO

ALTER TABLE dbo.Documents 
ADD CONSTRAINT PK_Documents PRIMARY KEY (ID);
GO

-- Cedvele bezi metn tipli verilenler elave edirik. 
--Verilenler HTML senedlerinden ibatretdir
INSERT INTO dbo.Documents (Title, DocsContent)
SELECT 'Microsoft SQL Server 2016 T-SQL yenilikleri', BULKCOLUMN
FROM OPENROWSET(BULK 'D:\SQLDATA\DOCS\Microsoft SQL Server 2016 T-SQL yenilikləri. DROP IF EXISTS _ TechNet.Az.html', SINGLE_CLOB) AS d

INSERT INTO dbo.Documents (Title, DocsContent)
SELECT 'Transact-SQL. NULL yoxsa sifir', BULKCOLUMN
FROM OPENROWSET(BULK 'D:\SQLDATA\DOCS\Transact-SQL. NULL yoxsa sıfır_ _ TechNet.Az.html', SINGLE_CLOB) AS d

INSERT INTO dbo.Documents (Title, DocsContent)
SELECT 'CONTAINS (Transact-SQL)', BULKCOLUMN
FROM OPENROWSET(BULK 'D:\SQLDATA\DOCS\CONTAINS (Transact-SQL).html', SINGLE_CLOB) AS d

Tam mətnli kataloq (Full Text Catalogs) yaradırıq. Qeyd etmək lazımdır ki, SQL Serverin Full-Text komponenti cəmi 53 dil dəstəkləyir. Buna baxmaq üçün aşağıdakı sorğudan istifadə edə bilərik:

--Full-Text Search hansi dilleri destekleyir
USE master;
GO
SELECT lcid, name 
FROM sys.fulltext_languages
ORDER BY name;
GO

Gördüyünüz kimi bu dillərin arasında Azərbaycan dili yoxdur. Buna görə də Azərbaycan dilində olan mətnlərdə Full-Text Search vasitəsilə axtarış nəticə vermir. Gördüyünü kimi, İngilis dilinin kodu 1033-dür. Sənədlərimizin mətnləri İngilis dilindədirsə bu koddan istifadə etməliyik. Bu kod Full-Text indeksin yaradılması zamanı tələb olunur:

--Full-Text Catalog yaradiriq
CREATE FULLTEXT CATALOG DocumentsLessons;
GO

--Full-Text Index yaradiriq
CREATE FULLTEXT INDEX ON dbo.Documents (
 Title LANGUAGE 1033,
 DocsContent LANGUAGE 1033
)
KEY INDEX PK_Documents ON DocumentsLessons
WITH CHANGE_TRACKING AUTO;
GO

T-SQL-də mətn tipli verilənlərlə işləyən LIKE operatoru istifadə olunur. Bu operator göstərilən simvollu sətrin, yəni mətnin, verilmiş şablona uyğunluğunu yoxlayır. Şablona adi simvollar və ya şablonlu-simvollar daxil ola bilər. Bu operatora nümunə üzərində baxaq:

Use Northwind;
GO
SELECT *
FROM dbo.Products
WHERE ProductName LIKE '%Tofu%'

LIKE operatorunun tam mətnli axtarışla nə fərqi var?

LIKE operatoru cədvəldə olan bütün sətirləri yoxlayır və bu sətirlərin arasında verilmiş şablona uyğun nəticə tapıldıqda məlumat qaytarır. LIKE operatoru yalnız simvolların kombinasiyalarıyla işləyir. Bundan başqa, böyük həcmli strukturlaşdırılmamış mətn məlumatında LIKE operatoru tam mətnli sorğulara nisbətən daha gec yerinə yetirilir. Milyonlarla sətri olan mətn tipli verilənlərə LIKE operatoru ilə sorğu bir neçə dəqiqə yerinə yetirilir, həmin verilənlərə edilən tam mətnli sorğu isə cəmi bir neçə saniyə çəkir. Bunun səbəbi isə – tam mətnli sorğularda indekslərdən istifadə olunur, LIKE operatoru olan sorğularda isə indeks istifadə edilmədiyinə görə bütün cədvəl skan edilir və buna görə də daha çox vaxt sərf olunur. İndekslər barədə növdəki dərslərimizdə ətraflı tanış olacağıq.

İndi isə tam mətnli sorğularla (Full-text Search) tanış olaq. “Documents” cədvəlində olan bütün məlumatı alaq:

SELECT * 
FROM dbo.Documents

İndi isə tərkibində, yəni mətnində “COALECSE” sözü olan bütün sənədlərimizi tapaq:

SELECT * 
FROM dbo.Documents
WHERE CONTAINS(DocsContent, 'COALECSE')

Bunu LIKE operatoru ilə də almaq olar:

SELECT * 
FROM dbo.Documents
WHERE DocsContent LIKE '%COALECSE%'

Sorğuların nəticələri eynidir:

Lakin yuxarıda qeyd etdiyimiz kimi, belə sənədlərin sayı çox olsa LIKE operatoru bu sənədlərin hamısının “skan” edəcək və bununla da daha çox vaxt sərf olunacaq. CONTAINS predikatı isə cədvəl üçün yaradılmış xüsusi indekssdən istifadə edir və beləliklə də yüksək sürətlə nəticələr alınır. Həmçinin, CONTAINS predikatı daha çox imkanlara malikdir. Bu imkanlarla yaxından tanış olaq:

Sorğunun içərisində məntiqi ifadələr qurmaq olar:

SELECT * 
FROM dbo.Documents
WHERE CONTAINS(DocsContent, 'single AND NOT words')

Cümlə və ya söz birləşməsi üzrə axtarış etmək olar:

SELECT * 
FROM dbo.Documents
WHERE CONTAINS(DocsContent, '"matches to single words and phrases"')

Sözlərin arasında olan məsafəyə görə axtarış etmək olar:

SELECT * 
FROM dbo.Documents
WHERE CONTAINS(DocsContent, 'NEAR (single, phrases)')

Sözün hər hansı bir forması üzrə axtarış etmək olar. Məsələn “correspond” sözü mətndə “correspond”, “corresponded”, “corresponding” formasında varsa sorğumuz bu sözlər üzrə nəticə verir:

SELECT * 
FROM dbo.Documents
WHERE CONTAINS(DocsContent, 'FORMSOF (INFLECTIONAL, correspond)')

İndi isə FREETEXT predikatına baxaq. Bu predikat mətnin yazılışı üzrə deyil onun mənası üzrə axtarış edir. Məsələn: yuxarıdakı sorğunu bu formada yaza bilərik:

SELECT * 
FROM dbo.Documents
WHERE FREETEXT(DocsContent, 'correspond')

Gördüyünüz kimi burada FORMSOF operatoru istifadə olunmur və INFLECTIONAL axtarış ilkin təyinetməyə görə (default) daxil edilib.

CONTAINS predikatından fərqli olaraq söz birləşmələrinin və cümlələrin dəqiqliklə uyğun gəlməsini tələb etmir. Məsələn: belə bir sorğunun nəticəsində üç sətir alırıq:

SELECT * 
FROM dbo.Documents
WHERE FREETEXT(DocsContent, 'matches to phrases')

Bu sorğuda “matches to phrases” söz birləşməsini tam olaraq deyil hər bir söz üzrə: “matches”,  “to”, “phrases’ sözləri üzrə axtarış aparılır.

Bu sorğunu CONTAINS predikatı ilə yazsaq heç bir nəticə almırıq, çünki bu predikat söz birləşməsinin mətndə olanla dəqiq uyğun olmasını tələb edir, həmçinin söz birləşməsi və cümlə cüt dırnaq arasında yazılmalıdır:

SELECT * 
FROM dbo.Documents
WHERE CONTAINS(DocsContent, '"matches to phrases"')

CONTAINS predikatı ilə belə bir soğru yazaq:

SELECT * 
FROM dbo.Documents
WHERE CONTAINS(DocsContent, 'single AND words')

Gördüyünüz kimi, burada biz iki söz arasında axtarış etdik “single” və “words” və bu sözlərin arasında AND məntiqi operatorundan istifadə etdik. FREETEXT predikatına heç bir əlavə operatora ehtiyac yoxdur. Axtarış hər iki söz üzrə aparılır və sözlərdən biri tapıldıqda nəticədə qaytarılır:

SELECT * 
FROM dbo.Documents
WHERE FREETEXT(DocsContent, 'single words')

Gördüyünüz kimi, nəticədə üç sətir aldıq. Birinci sorğuda axtarış hər iki söz:  “single” və “words” sözləri olan məqalələri göstərdi, ikinci sorğu isə “single” və “words” sözlərdən biri olan və bu sözlərin müxtəlif formaları olan  bütün məqalələri göstərdi.

FREETEXT predikatı ilə yazılan son sorğumuzun nəticəsində üç sətir aldıq. Real həyatda ola bilər ki, belə sorğuların nəticələri minlərlə sətir olsun. Lakin mən istəyirəm ki, bu qədər nəticələr müəyyən qaydada düzülsün, yəni sorğunun şərtinə ən yaxşı cavab verən nəticələr lap başda olsun. Yəni filtrləmə etmək istəyirəm. Bunun üçün SQL serverdə FREETEXTTABLE predikatı istifadə olunur. Sorğumuzu bu predikatla yazsaq, nəticə təyin etdiyimiz filtrə uyğun olaraq düzüləcək:

SELECT *
FROM FREETEXTTABLE(dbo.Documents, DocsContent, 'single words') AS b
 JOIN dbo.Documents a ON b.[key] = a.id
ORDER BY b.rank DESC

Hər iki sorğunu işə salaq və müqayisə edək. Gördüyünüz kimi, hər iki nəticədə eyni üç sətri alırıq, lakin fərqli ardıcıllıqda: sonuncu sorğunun nəticəsində şərtə ən yaxşı cavab verən nəticələri lap başda görürük və bura RANK sütunu da əlavə olunub:

Beləliklə, bugünkü məqalənin də sonuna gəlib çatdıq. Full-Text Search, yəni tam mətnli axtarış sistemi və tam mətnli axtarış üçün istifadə edilən CONTAINS və FREETEXT predikatları ilə tanış olduq.

Növbəti məqalələrdə görüşənədək.

Diqqətinizə görə təşəkkür edirəm.