Реалізація повнотекстового пошуку в SQL Server.




Якщо Ви хочете організувати повнотекстовий пошук
у себе на сайті, то ця стаття для Вас. Прочитавши її Ви дізнаєтеся з чого почати і
які є "підводні камені" при реалізації цього сервісу, також наводиться
простенький приклад на C #. Вообщем, спробую викласти основну коротко і
послідовно.


З чого почати …


Повнотекстовий пошук в SQL Server можна робити по текстових полів
(char, varchar, text, ntext, nchar, або nvarchar).
Для
підтримки повнотекстового пошуку неструктурованої текстової інформації в
визначеної бази даних потрібно вміти вирішувати чотири основні завдання:


  1. Визначати таблиці і стовпчики, зареєстровані для повнотекстового пошуку.

  2. Індексувати дані в зареєстрованих стовпцях.
  3. Створювати запити на пошук у зареєстрованих стовпцях, для яких
    заповнені повнотекстові індекси.
  4. Стежити за тим, щоб наступні зміни в зарегістріруемих стовпцях
    передавалися механізму індексування, щоб повнотекстові індекси завжди були
    узгоджені з даними.

Отже, у Вас є таблиця User, В якій є три колонки:
Id(Int) (простий первинний ключ, що реєструються таблиці повинні мати
стовпець, значення якого гарантовано – унікальне для кожного рядка),
Name (text), Coment (Text). Вам потрібно організувати повнотекстовий
пошук по полях Name і Coment. Для початку потрібно зареєструвати таблицю, потім
стовпці, потім активувати таблицю і створити повнотекстовий індекс. Все це
можна досягти кількома шляхами (за допомогою GUI, що надаються SQL Server
Enterprise Manager або вручну, за допомогою збережених процедур). Я розгляну рішення
за допомогою GUI.
1. Відкрийте SQL Server Enterprise Manager.
2. Виберіть
базу даних, в якій знаходиться потрібна таблиця.
3. Виберіть Tools ->
Full-Text Indexing … З'явиться майстер налаштування для повнотекстового
пошуку.


4. Створіть повнотекстовий каталог.




























Вікно N Опис Наша реалізація
2 Вибрати таблицю, в якій буде реалізований ПП (Select a Table). Вибираємо User.
3 Вибір індексу (Select an Index). Тут задається унікальний ключ для даної
таблиці.
Для нашої таблиці хай це – UserKey
4 Вибір колонок, які будуть проіндексованих (Select Table Columns). Позначаємо колонки Name і Coment.
5 Вибір каталогу (Select a Catalog). Є два варіанти:

  • Вибрати каталог (Select Full-Text Catalog);
  • Створити новий каталог (Create a new catalog).
Вибираємо-Create a new catalog і даємо йому ім'я, наприклад
UserSearch.
6 Налаштування оновлень для каталогу (Select or Create Population
Schedules(Optional))

  • New Table Schedule;
  • New Catalog Schedule.
Залишаємо як є.

Пройшовши всі ці пункти, ви створите повнотекстовий каталог під ім'ям
UserSearch, В якому для проіндексованих значаться колонки Name
і Coment, Таблиці User. Тепер залишилося створити повнотекстові
індекси. Це можна зробити вибравши пункт Start Full Population.


Тепер можна виконувати повнотекстові запити. Ще Немешаєв б знати, як
вмикати і вимикати службу повнотекстового пошуку, і як додавати нову
таблицю в повнотекстовий каталог.
Запускати і зупиняти службу
повнотекстового пошуку можна, натиснувши правою кнопкою на об'єкт Full- Text
Search
:


Додати нову таблицю в повнотекстовий каталог можна вибравши Edit Full-
Text Indexing…
:


Таблиця User зареєстрована, активована і створений повнотекстовий
індекс. Тепер можна виконувати повнотекстові запити. Для цього існують
предикати CONTAINS і FREETEXT, А також функції ContainsTable()
і FreetextTable(), Коротко про них:


Предикат CONTAINS.
  
Використовується для того, щоб визначити, містять чи ні дані, що зберігаються в
зареєстрованому для повнотекстового використання стовпці, певні слова
і вирази.
Синтаксис:
СONTAINS ( column , ‘
contains ‘ )
column – Ідентифікують стовпець або стовпці, в
яких ведеться пошук. Можна вказати конкретний стовпець, або всі стовпці в
таблиці (*), зареєстровані для повнотекстового
використання.
сontains – Умови за якими буде проводитися
пошук. У умови можна використовувати деякі оператори:

























Оператор Опис
AND, OR і NOT стандартні оператори І, АБО, НЕ;
NEAR() шукані слова або виразу повинні бути розташовані поблизу один від
одного. (Text1 NEAR () Text2);
* теж що і% в предикаті LIKE (text * = textarea);
~ шукані слова або виразу повинні сусідити один з одним (Text1 ~ Text2
= Text1 Text2);
FORMSOF() означає лінгвістичне спорідненість (FORMSOF (INFLECTIONAL, будинок) = будинок, будиночок,
будинку …);
ISABOUT() дає можливість привласнювати ваговий коефіцієнт (ISABOUT ("Text1 Text2"
WEIGHT (1.0), Text1 WEIGHT (.5), Text2 WEIGHT (.2)) – кращими будуть вважатися ті
рядки, в яких виявитися більше слів входять до
Text1).

Приклад:
WHERE СONTAINS (*, 'Таврія and
Москвич and Жигулі and not Ауді ')


Предикат FREETEXT.
  
Використовується для того, щоб визначити чи відповідає вміст
зареєстрованого для повнотекстового пошуку стовпця глузду, а не дослівному
значенням того, що зазначено в предикаті. Синтаксис:
FREETEXT ( column ,
‘ freetext_string ‘ ),
де: freetext_string – набір слів.

Приклад:
WHERE FREETEXT (*, 'Реалізація
повнотекстового пошуку в SQL ')


Функція ContainsTable ().
  
Використовується для виконання повнотекстових запитів типу "contains", які
повертають оцінку відповідності для кожного рядка.
Синтаксис:
СONTAINSTABLE (table, column, 'contains'),
де
table – таблиця, зареєстрована для повнотекстового пошуку.
Всі
інші параметри такі-ж як і для предиката СONTAINS.
Відмінності між
СONTAINS і СONTAINSTABLE:
















СONTAINS () Повертає логічне значення (істина / неправда).
Исполь в обороті WHERE оператора SELECT.
СONTAINSTABLE () Повертає таблицю з нуля, однієї чи більше рядків. Исполь в обороті
FROM.
СONTAINS () Використовується для того, щоб задавати критерії вибірки, за якими SQL
Server визначить приналежність до результуючою безлічі.
СONTAINSTABLE () Також використовується для того, щоб задавати критерії вибірки. Повертається
їй таблиця має стовпець, званий KEY, який містить значення
"Повнотекстового ключа". Крім того, таблиця повертається функцією
ContainsTable (), має стовпець RANK, що містить число в діапазоні від 0 до 1000.
Це число показує, наскільки добре рядок задовольнила критерієм
вибірки.

Приклад:
SELECT FT_TBL .Name, FT_TBL .Coment, KEY_TBL.RANK
FROM
User AS FT_TBL   
INNER JOIN
СONTAINSTABLE ( User
, *, 'Іванов NEAR () програміст'
)
AS KEY_TBL
ON FT_TBL .Id = KEY_TBL .[KEY]
ORDER BY KEY_TBL.RANK
DESC













Синій Завжди присутній в точності як
написано;
Жирний курсив Замінити на потрібне значення;
СІРІ ЗАГЛАВНІ Може бути відсутнім, але якщо є, то вточності як
написано.

Що ж ми одержимо в результаті цього запиту?
Ми отримаємо таблицю, в
якої є три стовпці, причому рядки в цій таблиці будуть упорядковані від
Найбільш представлені значимих. Це можна побачити з шпальти RANK, він і показує наскільки
добре рядок задовольнила критерієм вибірки.


Функція FreetextTable ().
  
Використовується для повнотекстових запитів типу "freetext" і повертає оцінку
відповідності для кожної найденої рядка.
Синтаксис:
FREETEXTTABLE (
table, column , ‘ freetext ‘ )
Ця функція використовується таким же чином,
як і ContainsTable (), а умови пошуку як для предиката FREETEXT.

  
Завдяки цим предикатам і функцій можна створювати різні повнотекстові
запити, але на жаль в SQL Server спочатку не реалізована можливість
повнотекстового пошуку російською, українською мовами. Для реалізації цієї
можливості можна використовувати продукти сторонніх виробників, наприклад
ALESTA Search for BackOffice. Цей продукт являє собою набір компонентів,
які тісно інтегровані з механізмом повнотекстового пошуку Microsoft і
розширюють його стандартні можливості. Компоненти із складу ALESTA Search for
BackOffice, будучи вбудованими в стандартний механізм повнотекстового пошуку,
надають можливість коректної роботи з інформацією російською, українською
та англійською мовами і багатомовними текстами з урахуванням синтаксису і морфології
перерахованих мов. Всю інформацію щодо цього продукту можна знайти на
Web – сервері компанії ALESTA Software & Services: http://www.alesta.ru. Там до речі, можна
завантажити і бета версію програми і спробувати її на "зуб". Це звичайно не
єдина компанія надає подібного виду продукти, це всього лише
варіант :-).
Ось і все по введенню. Я намагався написати основне і коротко,
сподіваюся мені це вдалося. Тепер створимо Web-проект, в якому буде реалізований
простенький текстовий аналізатор, за допомогою якого можна генерувати
повнотекстові запити.


Приклад реалізації повнотекстового пошуку.


   Отже, у нас є таблиця User, В якій є три колонки
Id(Int) (простий первинний ключ), Name(text), Coment(text).
Вона зареєстрована для повнотекстового пошуку, активована і створений
повнотекстовий індекс. Будемо вважати, що ми реалізували можливість
використання російської та української мов для повнотекстового пошуку.
Що
ж ми повинні реалізувати? Вообщем, все просто, є Web-сторінка, на якій
є текстове поле і кнопка, вводячи текст в поле і клацаючи по кнопці, ми
генеруємо повнотекстовий запит тексту, що вводиться, до таблиці User, по
колонках Name і Coment, Сортуємо за релевантністю (спаданням) і
виводимо знайдене у вигляді таблиці на сторінку, де перше значення – це значення
релевантності, друге – Name і третє – Coment. Ще ми повинні
створити можливість використання спецсимволів для генерації повнотекстових
запитів. У нашому запиті ми можемо написати "програміст-тестувальник" і в
результаті повинні отримати всі рядки, в яких зустрічається слово "програміст"
і немає слова "тестувальник". Тепер про спецсимволів:




































Синтаксис Що означає оператор Приклад запиту
& логічне І; програміст & тестувальник
| логічне АБО; програміст | тестувальник
+ обов'язкова наявність слова; програміст + тестувальник
обов'язкове відсутність слова (І НЕ); програміст-тестувальник
“” пошук фрази; "Програміст тестувальник"
() групування слів; (Програміст тестувальник)
~ слово має йти після попереднього; програміст ~ тестувальник

Це основні спецсимволи, з їх допомогою можна створювати різні
повнотекстові запити.
Відкрийте VS і створіть новий Web-проект, назвемо
його UserSearch. Перейменуйте WebForm1.aspx в Default.aspx. Ось її
код:


<% @ Page language = "c #" Codebehind = "Default.aspx.cs"
AutoEventWireup = "false" Inherits = "FullTextSearch.Search"%>
<! DOCTYPE HTML PUBLIC "- / / W3C / / DTD HTML 4.0 Transitional / / EN">

<HTML>
<HEAD>
<title>UserSearch</title>
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">

<Asp: table id = "tblResult" style = "Z-INDEX: 104; LEFT: 14px; POSITION:
absolute; TOP: 112px "runat =" server "Font-Size =" X-Small "
Font-Names = "Tahoma" BorderWidth = "1px" Width = "99%">
</asp:table>

<Table style = "BORDER-RIGHT: 1px solid; BORDER-TOP: 1px solid;
BORDER-LEFT: 1px solid; BORDER-BOTTOM: 1px solid; FONT-FAMILY: Tahoma "
width = "99%" align = "center" border = "0">
<tr>
<td align="center">

<Asp: textbox id = "tbSearch" runat = "server"
Width = "306px"> </ asp: textbox>

<Asp: button id = "btnSearch" runat = "server"
Text = "Пошук"> </ asp: button>

<Asp: Label id = "lblInfo" runat = "server"
Font-Size = "X-Small" Font-Names = "Tahoma"
Width = "99%"> </ asp: Label>
</td>
</tr>
</table>
</form>
</body>
</HTML>


На цій сторінці є TextBox (TbSearch), в який буде вводитися
текст для пошуку, також є Button (BtnSearch), при натисканні на неї,
буде проводитися ПП введеного тексту в TextBox. Ще є Label
(LblInfo), в якому буде виводитися сам текст запиту, який генерує наша
програма, ну і є також Table (TblResult), в неї ми помістимо
результати пошуку. Ось власне і все, тепер залишилося дописати код на С #, для
цієї сторінки.
Це код обробника кліка по кнопці:


private void btnSearch_Click (object sender, System.EventArgs e)
{
string CommandText = string.Empty;

/ / Регулярний вираз для перевірки наявності спецсимволів
Regex regexp;
regexp = new Regex (@"[" + Convert.ToChar (34). ToString () +"(),+-~&|!]");
Match mcName = regexp.Match ("" + tbSearch.Text + "");

/ / 0 – спецсимволів в тексті немає …
if( mcName.Index == 0)
{
/ / Генеруємо рядок запиту для випадку коли немає спецсимволів
CommandText = DeliteStringEmpty(tbSearch.Text.Split());
CommandText = CreateStringSQL(CommandText);
}
else
{
/ / Генеруємо рядок запиту для випадку коли є спецсимволи
CommandText = tbSearch.Text;
CommandText = CreateStringSQLLogogram(CommandText);
}

/ / Показуємо текст запиту в lblInfo
lblInfo.Text = CommandText;
/ / Заповнюємо таблицю
FillTable(CommandText);
}


У цьому обробнику події є чотири функції.
























Функція Опис
1 DeliteStringEmpty() Повертає рядок з одинарними пробілами між словами
2 CreateStringSQL() Повертає рядок запиту для випадку, коли немає спецсимволів
3 CreateStringSQLLogogram() Повертає рядок запиту для випадку, коли є спецсимволи
4 FillTable() Виконує ПП і заповнює результуючу таблицю

1. DeliteStringEmpty()

private string DeliteStringEmpty(string[] controlStr)
{
string StrResult = string.Empty;
foreach(string str in controlStr)
{
if( str.Length > 2 )
{
StrResult = StrResult + " " + str;
}
}
return StrResult.Trim();
}

Тут все просто, наприклад, у нас є рядок для пошуку: "Пупкін
програміст ", так ця функція поверне:" Пупкін програміст "(з одним пропуском
між словами).


2. CreateStringSQL()

private string CreateStringSQL(string controlStr)
{
return "SELECT FT_TBL.Name, FT_TBL.Coment, KEY_TBL.RANK" +
"FROM User AS FT_TBL INNER JOIN" +
"FREETEXTTABLE (User, *," "+
controlStr.Replace ("", "NEAR ()") +
"”) AS KEY_TBL " +
"ON FT_TBL Id = KEY_TBL. [KEY]" +
"ORDER BY KEY_TBL.RANK DESC";
}

Ця функція повертає рядок запиту якщо в тексті не було спецсимволів. У
ній використовується функція FREETEXTTABLE() (Див. опис в попередній
розділі).


3. CreateStringSQLLogogram()

private string CreateStringSQLLogogram(string controlStr)
{
controlStr = controlStr.Replace ("+", "and");
controlStr = controlStr.Replace ("-", "and not");
controlStr = controlStr.Replace ("|", "or");
controlStr = controlStr.Replace ("&", "and");
if (controlStr.StartsWith ("and") | | controlStr.StartsWith ("or"))
{
controlStr = controlStr.Remove(0, 4);
}

return "SELECT FT_TBL.Name, FT_TBL.Coment, KEY_TBL.RANK" +
"FROM User AS FT_TBL INNER JOIN" +
"CONTAINSTABLE (User, *," "+ controlStr +" ")" +
AS KEY_TBL " +
"ON FT_TBL.Id = KEY_TBL. [KEY]" +
"ORDER BY KEY_TBL.RANK DESC";
}


Ця функція повертає рядок запиту якщо в тексті були спецсимволи. Всі
вони замінюються на відповідні команди, які розуміє SQL server.


4. FillTable()

private string FillTable(string CommandStr)
{
tblResult.Rows.Clear();
SqlConnection myConnection;
SqlCommand myCommand;
SqlDataAdapter MyDataAdapter;
DataSet ds = new DataSet();

/ / Відкриття з'єднання з базою
myConnection = new SqlConnection("рядок з'єднання з базою");
try
{
myConnection.Open();
myCommand = new SqlCommand (CommandStr, myConnection);
myCommand.CommandType = CommandType.Text;

MyDataAdapter = new SqlDataAdapter(myCommand);
MyDataAdapter.Fill(ds, "User");

/ / Побудова таблиці
foreach (DataRow row in ds.Tables ["User"]. Rows)
{
TableRow r = new TableRow();

TableCell c1 = new TableCell();
TableCell c2 = new TableCell();
TableCell c3 = new TableCell();

c1.Text = row[2].ToString();
c2.Text = row[0].ToString();
c3.Text = row[1].ToString();

r.Cells.Add(c1);
r.Cells.Add(c2);
r.Cells.Add(c3);

tblResult.Rows.Add(r);
}
}
catch
{
/ / Обробка винятків
}
finally
{
myConnection.Close();
}
}


Ось ми і Реалізувати простенький ПП. Його можна доповнити, змінити
сортування або зробити її вибір, можна використовувати "збережені процедури" і тд. і
тп., я лише показав найпростішу реалізацію.


Висновок



У висновку хочу додати, що ПП дуже вимогливий до ресурсів
сервера. Якщо у Вас є дуже велика таблиця, ви робите запит, наприклад,
знайти всіх Іванових і ставите додатковий критерій відбору (and Year = 1975) в
запиті (Year – це колонка в таблиці). Якщо переглянути виконання запиту
покроково, то спочатку виконається пошук всіх Іванових (наприклад у базі їх 100 000),
а потім заберуть тільки ті, які задовольняють умові Year = 1975 (наприклад в
базі їх 100). 🙁
Для кожного завдання реалізація можливості ПП буде
індивідуальної і Ви повинні знайти той оптимальний варіант, який задовольнить
всім вимогам у створенні цього сервісу.
З усіх питань, пропозиціям
і зауважень пишіть на SGrushevoy@mail.ru.
Успіхів у
програмуванні.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*