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

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

С чего начать...

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

Определять таблицы и столбцы, зарегестрированные для полнотекстового поиска.
Индексировать данные в зарегистрированных столбцах.
Создавать запросы на поиск в зарегистрированных столбцах, для которых заполненны полнотекстовые индексы.
Следить за тем, чтобы последующие изменения в зарегистрируемых столбцах передавались механизму индексирования, чтобы полнотекстовые индексы всегда были согласованны с данными.

Итак, у Вас есть таблица 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 .
   Успехов в программировании.