Поиск и фильтрация данных в Delphi

Введение

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

Общие положения

Для понимания реализации методов поиска Delphi необходимо сделать краткое отступление относительно идеологии построения баз данных. Итак, всю систему работы с БД можно представить в виде трех слоев или модулей.
Хранилище данных отвечает за хранение информации и обеспечение ее целостности и непротиворечивости.
Бизнес логика реализует набор правил предметной области приложения, т.е правил изменения и дополнения информации.
Презентационный слой выводит данные в форме доступной пользователю и обеспечивает интерфейс для взаимодействия с бизнес логикой.

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

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

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

По количеству слоев:
- Двухуровневые
- Многослойные

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

Поиск данных

Универсальные методы класса TDataSet

Класс TDataSet является базовым классом компонента, реализующим функции доступа к БД. Многие из его методов являются абстрактными и реализуются в потомках. Он имеет два метода для поиска данных: Locate и Lookup. Данные методы ищут запись, удовлетворяющую заданным условиям.

function Locate(const KeyFields: string; const KeyValues: 
Variant; Options: TLocateOptions): Boolean; virtual; 
function Lookup(const KeyFields: string; const KeyValues: Variant; 
const ResultFields: string): Variant; virtual;

Разница между ними в том, что функция Lookup при поиске записи позиционирует курсор на найденную запись, а Lookup не делает этого. Если поля указанные для поиска индексированы, то поиск производится с использованием индекса, что значительно ускоряет поиск.
В качестве примера рассмотрим использование данных методов на примере TTable в двухуровневом приложении.
Итак, начнем с генерации нового приложения File/New Application. Мы получим проект приложения с главной формой.
На данную форму поместим компонент Table с закладки Data Access, по умолчанию он будет иметь имя Table1. Теперь настроим компонент Table1 на взаимодействие с таблицей country из базы данных DBDEMOS. Для этого необходимо установить следующие свойства Table1 в следующей последовательности:
1. DatabaseName ==> DBDEMOS
2. TableName ==> country.db

Теперь можно активизировать компонент Table1, установив его свойство Active в значение true.
Далее разместим на форме компонент DataSource, позволяющий сделать данные Table1 доступными для визуальных компонентов отображения данных. Свяжем DataSource1 c Table1, установив св-во DataSet компонента DataSource1 равным Table1.
Разместим визуальные компоненты отображения данных со страницы Data Controls: DBGrid и DBNavigator. Установим св-во DataSource обоих равным DataSource1.
В результате мы получим форму, изображенную на рис. 2. Откомпилировав и запустив данный проект, мы сможем просматривать и редактировать данные таблицы country. Остается добавить элементы для организации поиска в таблице. Разместим на форме компонент Edit1 и две кнопки. Св-во Caption кнопки Button1 установим равным "Locate", а Button2 "Lookup". Далее в обработчике события OnClick кнопки Button1 организуем вызов метода Locate. Код обработчика события приведен ниже:
TForm1.Button1Click(Sender: TObject);
begin
  if not Table1.Locate('Name',Edit1.Text,[]) then
    ShowMessage('Запись не найдена');
end;

Разберем код более подробно. Строка Table1.Locate организует поиск записи в таблице Country. Первый параметр этой функции - поля, значения которых нужно проверять. В данном случае мы ищем запись по одному полю Name. Второй параметр, что шаблон поиска и третий опции поиска. Функция возвращает значение типа boolean, указывающее на успешность поиска.
Теперь пришло время протестировать наш пример. Запустим программу на выполнение, в строке ввода пишем Cuba и нажимаем кнопку Locate. Курсор в DBGrid1 должен переместиться на запись, имеющую в поле Name введенное значение (рис.3).
Однако наш пример имеет пока один недостаток, в строку редактирования необходимо вводить полное имя c учетом регистра, т.е если мы вместо Cuba введем, например Cu или cuba, то наш поиск будет безрезультатным. Естественно это не может нас не устраивать. Поэтому пришло время рассмотреть более подробно опции поиска. Данный параметр имеет тип TlocateOptions и позволяет задавать набор из двух параметров поиска: loCaseInsensitive и loPartialKey. Установка первого из них отменяет чувствительность к регистру в текстовых полях, а второй позволяет искать запись частично соответствующие заданному условию. С учетом вышесказанного код обработчика событий будет выглядеть следующим образом:

procedure TForm1.Button1Click(Sender: TObject);
begin
  if not Table1.Locate('Name',Edit1.Text,[loCaseInsensitive, loPartialKey])then
    ShowMessage('Запись не найдена');
end;

Следующей проблемой является поиск записи по нескольким полям. Для организации поиска по имени страны и континенту добавим на форму еще один компонент Edit2. Код обработчика события нажатия на кнопку Locate изменим следующим образом:

procedure TForm1.Button1Click(Sender: TObject);
begin
  if not Table1.Locate(
    'Continent;Name',
    VarArrayOf([Edit2.Text,Edit1.Text]),
   [loCaseInsensitive, loPartialKey])
  then
    ShowMessage('Запись не найдена');
end;

Как видно, при поиске по нескольким полям, все они перечисляются в параметре функции Locate
Запустив приложение, в строке поиска континента пишем South america, а в строке "страна" - C. Нажимаем кнопку Lookup - результат поиска - установка курсора в DBGrid на запись Chile.
В ходе написания этой статьи выяснилась одна особенность. Частичный поиск при поиске по нескольким полям работает лишь для последнего поля, указанного в списке.
Модифицируем наш пример для использования функции Lookup. Ниже приведен код обработчика события нажатия на кнопку Lookup:

procedure TForm1.Button2Click(Sender: TObject);
var
  Res:Variant;
begin
  Res:=Table1.Lookup('Continent;Name',VarArrayOf([Edit2.Text,Edit1.Text]),'Area');
  if Res <> Null then
    ShowMessage('Area '+String(Res));
end;

Как видно из кода, мы проводим поиск по полям Continent, Name. При нахождении записи мы выдаем сообщение о площади страны, при это курсор на найденную запись не перемещается. К сожалению в параметрах функции отсутствует LocateOptions.
Итак, подведем некоторые итоги. Функции Locate и Lookup предназначены для поиска в базе данных одной записи, удовлетворяющей заданным условиям. Данные методы определены в классе TDataSet как виртуальные и могут быть переопределены в классах потомках. Метод Locate устанавливает курсор на обнаруженную запись, Lookup этого не делает. Метод Locate, хотя и с некоторыми оговорками, может искать записи по частично заданному ключу.
Необходимо отметить, что очень часто необходимо найти сразу несколько записей. Для решения такого рода задач нужно применять методы, предоставляемые классом Ttable, Tquery …, либо проводить фильтрацию. Как это сделать описано ниже.

Методы класса TTable

Компонент TTable предназначен для работы с таблицей база данных. Данный компонент используется в двухуровневых приложениях баз данных, либо в сервере приложений в трехуровневой БД. Как правило, использование TTable для работы с клиент-серверной БД оказывается менее эффективным, чем TQuery, поскольку TTable извлекает сразу ВСЕ записи из таблицы, а в TQuery лишь удовлетворяющие условиям запроса.
Компонент имеет несколько специфических методов для поиска данных. Данные методы используются для поиска только по индексированным полям (для dBase и Paradox как минимум). Все они делятся на две группы:
- Методы поиска одной записи
- Методы поиска диапазона записей.
Рассмотрим сначала первую группу методов. К ним относятся GotoKey, FindKey, GotoNearest, FindNearest. Первые два метода используются для поиска строго соответствия, другие ищут частичное соответствие.
В качестве примера создадим новое приложение, на главной форме разместим компоненты Table, DataSource, DBGrid, DBNavigator. Установим св-ва данных компонентов в соответствии с таблицей 1. Отметим, что таблица country.db имеет индексированное поле Name, по которому мы далее организуем поиск.

Таблица 1

Далее разместим на форме компонент Edit и четыре кнопки, установив их св-во Caption в GotoKey, GotoNearest, FindKey, FindNearest. Ниже приведен код обработчиков событий нажатия на эти кнопки.

//Использование GotoKey
procedure TForm1.Button1Click(Sender: TObject);
begin
  with Table1 do
  begin
    EditKey;
    FieldByName('Name').AsString := Edit1.Text;
    if not GotoKey then
      ShowMessage('Not found');
  end;
end;

// Использование GotoNearest
procedure TForm1.Button2Click(Sender: TObject);
begin
  with Table1 do
  begin
    EditKey;
    FieldByName('Name').AsString := Edit1.Text;
    GotoNearest;
  end;
end;

// Использование FindKey
procedure TForm1.Button3Click(Sender: TObject);
begin
  with Table1 do
  begin
    if not FindKey([Edit1.Text]) then
      ShowMessage('Not found');
  end;
end;

// Использование FindNearest
procedure TForm1.Button4Click(Sender: TObject);
begin
  with Table1 do
  begin
    FindNearest([Edit1.Text]);
  end;
end;

Прокомментируем вышеприведенный код. Методы GotoKey, FindKey проводят поиск на точное соответствие заданному ключу. Они возвращают значение типа boolean, говорящее об успехе поиска.
Методы GotoNearest, FindNearest проводят поиск первой записи хотя бы частично соответствующей ключу. Они не возвращают значений, т.к такой вид поиска всегда будет успешным.
Перед вызовом методов GotoKey, GotoNearest необходимо вызывать метод EditKey или SetKey, чтобы перевести компонент Table в режим редактирования ключа поиска.

Методы поиска диапазона записей позволяют отобразить записи таблицы, лежащие в указанном диапазоне значений поля. Для таблиц Paradox и dBase данные методы работают только для индексированных полей. К данным методам относятся SetRangeStart, SetRangeEnd, EditRangeStart, EditRangeEnd, ApplyRange, CancelRange. Техника использования данных функций очень проста.
Сначала необходимо установить начало и конец диапазона вызовом функций SetRangeStart, SetRangeEnd, EditRangeStart, EditRangeEnd, указывая при этом значения полей формирования диапазона. Затем вызовом ApplyRange применить указанный диапазон. Сброс установленного диапазона позволяет вновь отобразить все записи таблицы. Он выполняется вызовом функции CancelRange.
Для иллюстрации вышесказанного создадим новый проект, на главную форму поместим компоненты Table, DataSource, DBGrid, DBNavigator. Установим их свойства как указано в таблице 1. Далее разместим две строки ввода и две кнопки SetRange и CancelRange. Обработчики событий нажатия этих кнопок приведены ниже.

procedure TForm1.SetRangeClick(Sender: TObject);
begin
  with Table1 do
  begin
    SetRangeStart; {Входим в режим установки начала диапазона}
    FieldByName('Name').AsString := Edit1.Text; {значение начала диапазона}
    SetRangeEnd; { Входим в режим установки конца диапазона }
    FieldByName('Name').AsString := Edit2.Text; {значение конца диапазона}
    ApplyRange; { Применяем указанный диапазон}
  end;
end;

procedure TForm1.CancelRangeClick(Sender: TObject);
begin
  Table1.CancelRange; {Очистка диапазона}
end;

Использование TQuery

Компонент TQuery предназначен для извлечения данных с помощью языка SQL (Structured query language). Компонент используется в двухуровневых приложениях работы с базами данных и в сервере приложений в трехуровневых. Он, как правило, применяется для работы с клиент-серверными базами данных.
Сам компонент TQuery не имеет специальных методов поиска записей, однако, возможности поиска заложены в языке SQL.Ограничения на простые данные, получаемые предложением select, реализуются с помощью предложения where.
В качестве иллюстрации приведем следующий пример. Создадим новое приложение, разместим на его главной форме компоненты: Query, DataSource, DBGrid, DBNavigator, Memo, две кнопки Button1 и Button2.
Установим свойства компонентов, как это указано в таблице 2.

Таблица 2

В обработчике события OnCreate формы реализуем копирование текста запроса из Query1, код приведен ниже.

procedure TForm1.FormCreate(Sender: TObject);
begin
  Memo1.Text:=Query1.SQL.Text;
end;

Кнопка Button1 при нажатии будет переносить текст запроса, исправленный пользователем, из Memo1 обратно в Query1. Обработчик нажатия Button1 будет выглядеть так:

procedure TForm1.Button1Click(Sender: TObject);
begin
  Query1.Active:=false;
  Query1.SQL.Text:=Memo1.Text;
  Query1.Active:=true;
end;

Обработчик события нажатия Button2 установим, указывающим на FormCreate.
Откомпилируем и запустим нашу программу. Убедимся в том, что при изменении текста запроса (площади и населения) происходит изменение записей в DBGrid1.
Данный подход имеет ряд существенных недостатков, затрудняющих его практическое использование.
1. Пользователь приложения обязан разбираться в языке SQL;
2. Отсутствуют средства автоматического контроля синтаксиса запроса;
3. При изменении текста запроса, он отправляется на сервер, где каждый раз анализируется и компилируется, что снижает быстродействие поиска.
4. Для генерации запроса пользователю необходимо знать имена таблиц и полей базы данных.
По указанным выше причинам, данный подход применяется редко. Как правило, применение данного подхода оправдана при написании приложений для администраторов БД или разработчиков отчетов.

Более приемлемым является применение параметризированных запросов. В этом случае в тексте запроса содержатся параметры для дальнейшей подстановки конкретных значений на этапе выполнения. Параметры в тексте запроса начинаются с двоеточия.
Итак, создадим новое приложение, разместим на главной форме компоненты Query, DataSource, DBGrid, DBNavigator, кноку Button1. Установим их свойства аналогично предыдущему примеру.
Далее текст св-ва SQL компонента установим равным:

SELECT Name, Capital, Continent, Area, Population
FROM "country.db" Country
WHERE (Name = :Name_param)
В последней строке мы определили параметр запроса Name_param. Теперь открыв в инспекторе объектов редактор св-ва Params компонента Query1, мы увидим элемент, соответствующий определенному параметру. Выбрав данный элемент отредактируем его свойства.
DataType - тип значения параметра (по умолчанию ftUnknown) установим в ftString.
ParamType - тип параметра (по умолчанию ptUnknown) установим в ptInput.
Обработчик события нажатия на кнопку будет выглядеть следующим образом:

procedure TForm1.Button1Click(Sender: TObject);
begin
  Query1.Close;
  if not Query1.Prepared then
    Query1.Prepare;
  Query1.ParamByName('Name_param').Value:=Edit1.Text;
  Query1.Open;
end;

Для увеличения скорости поиска в строках 4 и 5 осуществляется проверка подготовки запроса. Если запрос не подготовлен (что случается при старте программы) вызывается метод Prepare. При этом сервер анализирует и компилирует запрос, сохраняя его на сервере. Далее серверу необходимо отсылать лишь значения параметров запроса.
Скомилируем и запустим на выполнение созданное приложение. В строке поиска введем значение Canada и нажмем кнопку "Применить". Результатом поиска будет вывод в строки в DBGrid1, как это показано на рис. 5.

Для поиска записей частично соответствующих введенному критерию изменим текст запроса компонента Query1 следующим образом:

SELECT Name, Capital, Continent, Area, Population
FROM "country.db" Country
WHERE   (Name Like :Name_param)

Как видно мы заменили оператор строго равенства (=) на LIKE. Оператор Like позволяет искать текстовые значения нестрого соответствующие заданному критерию. При этом в критерии поиска можно использовать знаки подстановки. % - любые символы, _ - один символ. Например, критерий поиска С% в нашем случае даст нам вывод четырех записей (Canada, Chile, Colombia, Cuba).
В одном запросе можно комбинировать несколько условий поиска с помощью операторов OR, AND итд. Конечно, мы осветили лишь небольшую часть возможностей SQL по поиску данных. Если Вы хотите получить более подробную информацию, рекомендую приобрести какую-либо книгу по SQL.

Фильтрация

В отличие от методов поиска, предполагающих извлечение данных из хранилища данных фильтрация предполагает отбор уже извлеченных данных в клиентском приложении. Для реализации данного подхода в Delphi в компонентах доступа к данным введены два свойства Filter и Filtered. Установка свойства Filtered типа boolean в true переводит компонент в режим фильтрации. В свойстве Filter при этом можно определить значение фильтра для отбора записей. Построение фильтра во многом похоже на построение условия where в SQL запросе. Основное отличие в том, что слово where не пишется, используются другие знаки подстановки, в тексте фильтра нельзя после знаков сравнения вставлять имена полей для локальных таблиц. Если имя поля содержит пробелы, то оно заключается в квадратные скобки, например [Home directory] Свойство FilterOptions позволяет установить дополнительные параметры фильтрации, а именно
foCaseInsensitive - нечувствительность к регистру в текстовых полях;
foNoPartialCompare - отсутствие поиска по частичному условию, при установке данной опции знак * воспринимается как литера, а не как знак подстановки любые символы.
Пример фильтрации можно найти в проекте Demos\DB\Filter из примеров, поставляемых с Delphi.
Все вышесказанное позволяет реализовывать фильтрацию данных по достаточно простым условиям. В случае, если необходимо реализовать более сложный нестандартный фильтр можно написать обработчик события OnFilterRecord. Тип события определен как
type TFilterRecordEvent = procedure(DataSet: TDataSet; var Accept: Boolean) of object;
Другими словами в обработчике события можно изменять переменную Accept, указывая будет ли отображаться каждая конкретная запись. Например обработчик может выглядеть так:
Accept := DataSet['DateOfPayment'] > DataSet['DateOfPurchase'] + 30;
Не забывайте, что обработчик события дополняет, а не замещает свойство Filter, т.е если включена фильтрация (Filtered := true) и св-во фильтр содержит значение фильтра, то в обработчике события и фильтр связаны логическим отношением "AND".

Заключение

Конечно, невозможно изложить в статье все приемы и хитрости поиска данных, а тем более в такой гибкой и мощной среде разработки как Delphi. Мы рассмотрели лишь общие положения. В частности, за рамками статьи остались такие темы, как поиск в трехуровневых приложениях баз данных.