Базы данных -> SQL -> Как строить поисковые системы, работающие через WEB

Создание полезных WEB-страниц для доступа к данным и поиска информации

В приводимой ниже статье описан процесс построения простого, но эффективного поискового средства для работы с базой или хранилищем данных в сети WEB. В отличие от большинства клиентских WEB-приложений в рассматриваемом проекте основная логика обработки данных реализуется на сервере баз данных. Для задания критерия поиска в данном проекте используются основные объекты HTML для ввода информации. После того как WEB-сервер получит необходимые параметры, они включаются в оператор EXEC, который вызывает хранимую процедуру. По завершении исполнения хранимая процедура возвращает набор записей, который сервер переводит в формат HTML и отсылает в браузер.

Изюминкой рассматриваемого подхода является применение хранимых процедур. Процедуры, базирующиеся на сервере, обеспечивают откомпилированный заранее доступ ко всем таблицам хранилища данных. Это существенно повышает производительность системы. Поскольку одним из параметров процедуры служит тип поиска, такая организация обработки, при которой одна WEB-страница вызывает одну процедуру, практически снимает ограничения на количество типов поиска или заголовков столбцов, а также на число демонстрируемых столбцов.

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

Рассматриваемый проект базируется на трехслойной архитектуре: браузер - сервер WEB - база данных. Для реализации проекта необходимы следующие компоненты:

• SQL Server версий 6.х или 7.0;
• Windows NT с Информационным сервером Интернет, IIS (Internet Information Server) версий 3.0 или 4.0;
• Активные серверные страницы, ASP (Active Server Pages), с ADO версий 1.0, 1.5 или 2.х.

Кроме того, желательно знать основные команды HTML и уметь строить страницы ASP, а также хранимые процедуры средствами Т-SQL (Transact SQL).

Получение данных через хранимые процедуры

Прежде всего необходимо построить хранимую процедуру для работы с базой данных. Беря за основу учебную базу данных Pubs, создайте процедуру, обладающую тремя входными параметрами и возвращающую набор записей. Входными параметрами являются: тип поиска, основной поисковый аргумент (SARG) и второй поисковый аргумент SARG, используемый для фильтрации набора записей по продажам за год на текущую дату (YTD). После этого постройте страницу IIS/ASP, на которой пользователь будет вводить входные данные и просматривать полученные результаты.

Выбор типа поиска. Первый параметр процедуры представляет собой тип поиска, запрашиваемый пользователем. Применение параметра Тип поиска обеспечивает гибкость при выборе различных вариантов поиска, предоставляемых страницами WEB. Приведенная простая процедура предлагает проводить поиск только трех типов: по фамилии автора (Author), по названию книги (Title) и по идентификатору автора (Author_Id). Если для типа поиска не было выбрано какое-либо значение из перечисленных выше, то по умолчанию будет производиться поиск по фамилии автора. Поскольку браузеры манипулируют только строками, необходимо пользоваться типом данных Varchar() для передачи значение всем переменным.

Как видно из листинга 1, переменная, соответствующая типу поиска, названа @strSearchType. На основании значения этой переменной и исходя из порядка обработки команд в Т-SQL процедура выбирает какой из блоков Begin ... End ей следует выполнить. Каждый блок содержит различные поисковые запросы (операторы SELECT) для возврата на страницу IIS/ASP набора записей. Выбор элементов передачи управления в Т-SQL невелик, поэтому возьмем команду IF- ELSE. За такое решение вы не получите приз на конкурсе программистов, но зато после компиляции сервер будет быстро обрабатывать этот оператор. Обратите внимание на то, что автор не стал применять команду CASE. В Т-SQL есть выражение CASE, но его можно использовать только в операторах (то есть в SELECT, UPDATE, DELETE).

Задание аргументов поиска. Первый параметр определяет, какой блок программы будет выполняться. Следующие параметры передают аргументы поиска, которые и задают что же именно следует найти. В рассматриваемом проекте использованы два типа аргументов поиска SARG. Первый принимает символьную строку, вводимую пользователем. Он вводит либо фамилию автора, либо название книги. Второй поисковый аргумент позволяет пользователю еще более ограничить сферу поиска, проводя выбор на основании объема продаж, произведенных в течение года от текущей даты, для любого названия книги. В данном проекте использованы только два поисковых аргумента, но можно применять любое их количество, в том числе и ни одного. Если вам хочется построить по-настоящему совершенную процедуру для своего хранилища данных, то в вашем распоряжении до 255 параметров при использовании SQL Server 6.х и до 1024 в случае применения SQL Server 7.0.

Первый поисковый аргумент принимает символьную строку, такую как фамилия автора или название книги. Для того чтобы проводить поиск и тогда, когда полностью эти данные не известны, следует применять оборот LIKE с джокером в конце строки для расширения области поиска (напомним, что джокер - это знак %, обозначающий произвольный символ). Следующая команда позволяет использовать джокер:

SELECT @NameSrch = @strUSERINPUT + '%'

Применение оборота LIKE с джокером % в конце не приводит к ухудшению производительности системы. Оптимизатор запросов по-прежнему обращается к вашим индексам (кстати, столбец, используемый подобным образом, становится первым кандидатом для построения кластеризованного индекса). Другое дело - джокер % в начале оборота LIKE. Такая конструкция заставляет оптимизатор прибегнуть к сканированию таблицы, что может значительно снизить производительность. (Более подробно эти вопросы рассмотрены в статье Петковича и Унтеррайтмейера <Новые возможности обработки запросов>.)

Следующий поисковый аргумент данного проекта предусматривает анализ столбца ytd_sales в таблице Titles. Сведения о продажах за год на текущую дату, YTD, хранятся в денежном формате, поэтому вводимый параметр также необходимо преобразовать в денежный тип. Кроме того, следует проверить, являются ли все введенные пользователем символы числами. Если нет, то целесообразно сделать равным нулю значение по умолчанию этого параметра. (Альтернативным решением может служить написание на VBScript программы, которая будет проводить проверку того, все ли знаки введенной символьной строки являются числами.) Для проверки удобно применить выражение CASE, например, следующим образом:

SELECT @moneyYTDsales = CASE WHEN @strYTDsales 
                    LIKE '%[^0-9]%'  
                    THEN 0
                    ELSE CONVERT (money, @strYTDsales)
END

Использование оборота LIKE в условии проверки того, все ли введенные символы были цифрами, кажется противоречащим интуитивному представлении об эффективном коде. Но здесь в игру вступает символ отрицания, ^. В данной команде выражение LIKE '%[^0-9]%' принимает значение Истина только тогда, когда какой-либо символ не лежит в интервале от 0 до 9.

Завершающая функция данной процедуры - обеспечить связь HTML с именем автора. Для этого следует включить ссылки HTML в качестве части столбца с фамилией автора, возвращаемого с каждой записью. Данная процедура создает строку примерно такого содержания: "s;<A HREF='PUBSlookup?author_id=363-45-1234'>authorname</A>"s;. Поскольку URL ссылается на ту же самую страницу, то HREF начинается непосредственно с имени страницы ASP. В браузере пользователю желательно видеть не только фамилию, но и имя автора. В нижней строке статуса (см. экран 1) приводится полная гиперссылка, в которую входит и & идентификатор автора.

Экран 1. Просмотр гиперссылки Author_Id в строке статуса

Чтобы включить гиперссылку на идентификатор автора, можно вставить следующий код написанный на Т-SQL:

SELECT Author
   = SUBSTRING(<<A HREF='pubslookup.aspAutor_id=> +
       COALESCE(A.au_id,>) + <'>>
          + SUBSTRING(COALESCE((au_frame + '   ' + au_lname,'-
       '),1,25) + <</A>> , 1,75) ,

Последняя строка размером в 75 знаков рассчитана на суммарную длину всех возвращаемых символов, включая и встроенные ярлычки HTML. Ярлычки ссылок являются полезной информацией, так как они не видны при копировании данных из браузера.

Основы интерфейса с сетью WEB

Давайте рассмотрим требования к нашему гипотетическому интерфейсу с сетью WEB. Во-первых, он должен представлять собой единое решение в форме WEB-страницы для поиска информации в хранилище данных. Когда пользователи первый раз обращаются к странице ASP, они видят начальное приветствие и форму для ввода критерия поиска и запуска запроса. По завершении поиска браузер показывает полученные результаты и входную форму на случай, если пользователю понадобится ввести новый запрос.

Пользователи, работающие с хранилищем данных, хотят получать результаты быстро. кроме того, им часто необходимо скопировать полученные данные в сообщение электронной почты, отчет или электронную таблицу для дальнейшего анализа. В гораздо меньшей степени их занимают графическое представление, шрифты и прочие ухищрения. Но скопировать данные из таблицы HTML в обычный редактор не так-то просто. Поэтому вторая цель интерфейса - предоставить пользователям возможность копировать данные.

После того, как данные уже получены, пользователям часто требуется изменить формат страницы или добавить новую информацию. Поэтому им необходима гибкая и быстро работающая WEB-страница. Это накладывает обязательства на разработчиков, которые со своей стороны заинтересованы в том, чтобы минимизировать объем программирования ASP, необходимый для модификации страницы.

Как запрашивать: через браузер или через форму?

Браузер либо сам формирует и отсылает запрос, либо использует для этого специальную форму. В рассматриваемом проекте применяются оба подхода. Можно предположить, что большинство пользовательских запросов поступают в систему через предложенную форму. Однако гиперсвязь Author_ID посылает строку запроса непосредственно на страницу, так что ваша страница ASP тоже должна уметь реагировать на запросы. Каждый раз, когда пользователь щелкает по странице, код ASP должен определить, какой тип поиска следует произвести: отослать форму, сформировать запрос по идентификатору автора или ничего не делать (если это был начальный щелчок или же неопознанный запрос). Когда пользователь впервые щелкает по WEB-странице, строка запроса и входной объект формы пусты, поэтому проверка на начальный щелчок осуществляется просто. Когда же пользователь отсылает форму или делает запрос, код ASP должен оценить входящие объекты Строка запроса (QueryString) и Форма (Form), чтобы опознать требуемый пользователем тип поиска. (Форма, применяемая в данном проекте, использует метод Отослать (Post), который прячет отправляемые значения. При работе с методом Получить (Get) значения, найденные в процессе поиска, будут высвечиваться в строке запроса.)

Прежде чем приступать к проверке корректности запроса следует сначала объявить переменные, которые понадобятся для хранения входящих значений объектов Строка запроса (QueryString) и Форма (Form). После заполнения переменных приступайте к проверке их корректности. В данном проекте предпочтение отдано форме, поэтому начните с тестирования значения соответствующей ей переменной. Если такое значение отсутствует, переходите к проверке правильности заполнения строки запроса. Если окажется, что существует объект Строка запроса (QueryString) с правильно заполненным идентификатором автора Author_ID, это означает, что пользователь ввел корректный запрос. Если же оба теста дали отрицательный результат, то либо это был <пустой>запрос, либо неверно заполненный. В последнем случае следует вывести на экран текст приветствия и пустую форму для ввода параметров поиска.

Сборка критерия поиска

Итак, уже построена средствами Т-SQL процедура, которая принимает три параметра: тип поиска; вводимую пользователем строку с фамилией или идентификатором автора, или с названием книги; годовой объем продаж YTD. Для хранения этих параметров на WEB-странице следует использовать комбинацию окон INPUT и SELECT.

Для хранения параметра Тип поиска удобно применить ярлык HTML <SELECT> (программисты обычно называют его комбинированным списком). Комбинированный список предоставляет пользователю перечень возможных типов поиска, каждому из которых соответствует определенная часть процедуры. Для хранения введенной пользователем строки с параметром поиска хорошо подходит окно INPUT, у которого свойство Тип (TYPE) имеет значение Текст (text). Иногда в хранилищах данных находятся не вполне согласованные сведения, поэтому не следует накладывать никаких проверок на введенное значение. Возможно, как раз ведется поиск некорректных записей, так что не стоит этому мешать. И наконец, для хранения годовых продаж YTD удобно применить кнопки наподобие ручек радиоприемника. Для улучшения пользовательского интерфейса код ASP проекта предусматривает включение формы для поиска в нижнюю часть любого запроса. Не следует заставлять пользователя метаться от формы запроса на поиск к форме с полученными результатами. Просто включайте поисковую форму в нижнюю часть любой страницы ASP после вывода на экран браузера результирующего набора записей. Еще одной чертой хорошего стиля, которую иногда опускают в поисковых формах, является сохранение и показ пользователю текста запроса при выводе на экран результатов его обработки. Каждый раз, когда поисковая форма возвращается пользователю, код ASP может также вернуть и заданный пользователем критерий поиска. Конечно, это не является обязательным правилом, но пользователи ценят такую заботу. Для добавления этой информации в текстовое окно INPUT можно использовать следующий код (он вставляет значение, которое браузер отсылал последним): < INPUT TYPE = text NAME = 'USER_INPUT' VALUE = '<% =strUSER_INPUT %> ' > Чтобы показать значение, выбранное пользователем из перечня окна SELECT или с помощью (радио) кнопки INPUT, необходимо сравнить значение переменной VBScript с каждым возможным значением ярлыка HTML. Для ярлыка SELECT применим следующий код: Response.write( <SELECT NAME + 'SelectType'> Response.write(<OPTION VALUE = 'Author'> ) If strSelectType = <Author> Then Response.write(<SELECTED>) End If Установление соединения Теперь, получив параметры поиска, код ASP должен произвести подключение к базе данных и запустить процедуру поиска. Для этого можно использовать объекты ADO. (Подробно о совместном применении ASP и ADO написано в статье Майкла Оути <Азы ADO и ASP>). Поскольку обновление производственной среды занимает, как правило, длительное время, автор данной статьи применил команды совместимые со всеми версиями ADO. Сначала создайте, а затем откройте объект ADO Соединение (Connection). После этого создайте объект Набор записей (Recordset), постройте строку SQL, переведите соединение в активное состояние и откройте его. В рассматриваемом примере используется DSN ODBC, который тоже необходимо установить. Приведенный на листинге2 код соединяет ASP с базой данных и запускает процедуру. Ваша хранимая процедура посылается на сервер и выполняется после того как будет открыт набор записей. Возвращаемый набор записей попадает в объект Набор записей (Recordset), который в целях повышения эффективности по умолчанию предназначен только для пересылки вперед и только для чтения. Поскольку хранимая процедура заканчивается оператором SELECT, она в качестве результата выдает набор записей, так что отпадает необходимость использовать в процедуре параметр OUTPUT. Строка SQL (источник) состоит из трех частей: команды EXEC, имени хранимой процедуры и одного или более параметров. Команда EXEC сообщает ядру сервера данных, что следующие за ней операторы Т-SQL представляют собой процедуру. Так как процедура - это первый исполняемый оператор SQL, необходимости в ключевом слове EXEC нет, но его применение - хороший тон в программировании. Следующий компонент строки SQL - имя хранимой процедуры. В приводимом примере и ключевое слово EXEC и название хранимой процедуры включены в код ASP. Но при работе с этим образцом легко заменить имя процедуры на переменную ASP, хранящую вводимое пользователем значение. Последний компонент строки источника - три параметра процедуры. Код ASP заполняет эти параметры значениями, которые поставляет браузер. Представление результатов Мнения о том, в каком же виде представлять запрашиваемые пользователем данные, расходятся. Разработчики клиентской стороны приложения часто настаивают на красиво оформленных WEB-страницах, в то время как разработчики серверной стороны и финансовые аналитики хотят просто получить данные. По опыту автора статьи пользователям хранилищ данных нужны факты. И кроме этого, им необходимо иметь возможность скопировать полученные результаты в электронные таблицы для дальнейшего анализа. (Пользователям информационных систем для высшего руководства компаний, как правило, нужны графики, диаграммы и другие графические усовершенствования WEB- интерфейса, хотя читатели должны лучше представлять потребности своих заказчиков.) Ниже описан базовый способ представления данных. В него введено единственное усовершенствование - встроенные возвраты каретки, обеспечивающие желанную возможность копирования во внешние наборы данных. Без посторонних ярлыков HTML набор записей <несется> по сети связи. При работе с одним хранилищем данных, которое автор построил, применяя предлагаемый подход, сервер возвращал через крупномасштабную территориальную сеть в браузер в среднем 200 записей в секунду. Иногда целесообразно подкорректировать формат представления данных в том случае, когда набор записей пуст или состоит из единственной строки. Прежде всего, следует проверить, действительно ли пуст набор записей. Для этого можно применить следующий код: IF (objRst.BOF = False OR objRst.EOF = False) THEN 'Обработка записей Возвращаемый набор из одной записи. Полезно использовать для представления одиночной записи несколько отличающийся формат. В рассматриваемом проекте набор записей, содержащий только одну запись, форматируется вертикально, а не горизонтально, как в случае нескольких записей. Чтобы проверить, содержится в наборе одна запись или больше, следует перейти ко второй записи и сделать проверку на конец файла. Если тест дает положительный результат, это означает, что в наборе находится только одна запись. С той же целью можно было бы использовать свойство ADO Число записей (RecordCount), но это заставило бы сервер пересчитать все записи, а это ненужная трата времени. Ниже приведен код, реализующий предложенный алгоритм: objRst.MoveFirst objRst.MoveNext IF objRst.EOF = True THEN flagSingleRecord =

Экран 2. Вывод на экран одной записи

На экране 2 представлены результаты форматирования единичной записи в рассматриваемом проекте.

Возвращаемый набор из множества записей. Одним из важных аспектов данного проекта является тот факт, что код ASP программным путем определяет число возвращаемых столбцов, порядок их следования, заголовки, а также количество записей в наборе. Это обеспечивает гибкость при внесении изменений в хранимые на сервере процедуры. Можно добавлять или удалять столбцы, переставлять их местами, можно менять размер столбцов - и все это без каких- либо доработок кода ASP.

На первом шаге форматирования результирующего набора, состоящего из множества записей, определяется формат HTML. Чтобы обеспечить пользователям возможность копировать полученную информацию, следует применять предварительно отформатированный ярлык HTML. Он позволяет послать набор записей в браузер практически без каких-либо форматирующих ярлыков HTML. Хотя с использованием ярлыков форматирования данные в таблицах выглядят красиво, но такие ярлыки увеличивают размер файла, дополнительно загружают сеть и приводят к снижению ее пропускной способности. Автор статьи обычно настраивает свои хранимые процедуры на возврат не более 1500 записей, и при этом он наблюдал, что ярлыки форматирования таблиц снижают производительность до неприемлемых величин.

После выработки плана размещения данных решается следующая задача - определение количества столбцов и их заголовков. Для получения заголовков столбцов следует в цикле просмотреть все столбцы. В тексте с ярлыком не игнорируются пустые места в отличие от стандартного форматирования HTML. По существу, в браузер пересылается набор с фиксированной шириной записей, соответствующий стандарту ASCII. Поэтому необходимо заполнить всю ширину заголовка столбца пробелами. Выяснить значение этой величины можно с помощью свойства ADO Размер по определению (DefinedSize). Затем заполните название столбца пробелами и вычислите разность. Приводимая ниже команда VBScript поможет вычислить количество пробелов, которые необходимо добавить в заголовок столбца:

intLength = CInt(objRst(i).DefinedSize + 1)
          - Len(objRst(i).Name)

Обратите внимание на то, что к величине размера по определению прибавили единицу (+1). Это сделано для того, чтобы между каждой парой столбцов был один пробел - для облегчения чтения данных.

После заполнения заголовков столбцов аналогичным образом формируются строки таблицы. Удобно использовать стандартную конструкцию - цикл в цикле: цикл по ширине столбца внутри цикла по строке. Если для определения размера заголовка столбца применялась команда VBScript LEN, то для определения ширины данных в ячейках строки можно воспользоваться свойством ADO Реальный размер (ActualSize). Разность между Размером по определению (DefinedSize) и Реальным размером (ActualSize) как раз и равна числу пробелов, которые необходимо добавить в ячейку данного столбца. (Заметим, что при работе с ADO версии 2.х метод Получить строку (GetString) объекта Набор записей (Recordset) работает более эффективно, чем описанные циклы). В конце каждой строки не забудьте с помощью команд VBScript вставить символы возврата каретки и конца строки, чтобы пользователи могли копировать данные в любой текстовый редактор:

strPrint = strPrint + chr(10) + chr (13)

Чтобы данные легко помещались в электронную таблицу, можно было бы встраивать ярлык или любой другой символ после каждого столбца. И наконец, после записи каждой строки добавляйте ярлык HTML, означающий прерывание строки. Представление таблицы полученное в результате всех предпринятых действий, показано на экране 3.

Экран 3. Вывод множества записей

После того, как все части приложения будут отлажены и собраны, у вас появится простое, но эффективное средство работы с хранилищем данных через сеть WEB. Администрация сети оценит экономное использование полосы пропускания, а аналитикам придется по вкусу быстрота реакции на запросы. А в силу того, что основную рабочую нагрузку принимают на себя хранимые процедуры Т-SQL, размещенные на сервере, можно легко и просто расширять и модифицировать алгоритмы поиска в хранилищах данных. Быстро и эффективно.

Эндрю Питерсон (andrew_peterson@bigfoot.com) имеет сертификаты MCP, MBA и CPA. Он выступал в качестве системного архитектора, разработчика баз данных и консультанта при разработке таких программных продуктов, как ASP и VBScript.