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




Якщо Ви хочете організувати повнотекстовий пошук у себе на сайті, то ця стаття для Вас. Прочитавши її Ви дізнаєтеся з чого почати і які є “підводні камені” при реалізації цього сервісу, також наводиться простенький приклад на 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: 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: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 – це колонка в таблиці). Якщо переглянути виконання запиту покроково, то спочатку виконається пошук всіх Іванових (наприклад в базі їх 100000), а потім відберуть лише ті, які задовольняють умові 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>

*

*