Поиск, фильтрация и индексация таблиц

Цель лекции

Ознакомление с механизмами поиска данных, фильтрации записей и использование индексов для сортировки.

Последовательный перебор

В программах, работающих с базами данных, часто используют поиск данных. Для чего еще нужны базы данных, как не для этого? Самый простой, но в то же время и самый медленный, "тяжеловесный" поиск, это, пожалуй, последовательный перебор. Вы переходите на первую запись таблицы, создаете цикл, который длится до последней записи, и внутри этого цикла проверяете необходимое условие. Также можно делать и обратный перебор, от последней записи к первой. В таблице 3.1 приведены все свойства и методы наборов данных ( TTable/ADOTable, TQuery/ADOQuery ), которые могут быть использованы при организации последовательного перебора:

Пример:

//перешли на первую запись:
fDM.TLichData.First;
//делать, пока не конец таблицы:
while not fDM.TLichData.Eof do begin
   if fDM.TLichData['Фамилия'] = 'Иванов' then
       break;  //нашли нужную запись, и вышли из цикла
  fDM.TLichData.Next; //иначе перешли на следующую запись
end; //while

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

Edit1.Text := fDM.TLichData['Фамилия']; //получили значение
fDM.TLichData['Фамилия']:= Edit1.Text; //изменили значение

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

Метод Locate

Метод Locate ищет первую запись, удовлетворяющую условию поиска. Если запись найдена, метод делает ее текущей и возвращает True. В противном случае метод возвращает False и курсор не меняет положения. Поле, по которому ведется поиск, не обязательно должно быть индексировано. Однако если поле индексировано, то метод ищет запись по индексу, что значительно ускоряет поиск. Поиск может вестись как по одному полю, так и по нескольким полям. Метод имеет три параметра:

function Locate (const KeyFields: String; const KeyValues: Variant;
                 Options: TLocateOptions) : Boolean;

Параметр KeyFields задает поле или список полей, по которым ведется поиск. Если имеется несколько полей, их разделяют точкой с запятой.

Параметр KeyValues является вариантным массивом, в котором задаются критерии поиска. При этом первое значение KeyValues ставится в соответствие с первым полем, указанным в KeyFields. Второе - со вторым, и так далее.

Третий параметр Options позволяет задать некоторые опции поиска:

Примеры использования метода Locate:

Table1.Locate('Фамилия', Edit1.Text, []);
Table1.Locate('Фамилия;Имя', 
   VarArrayOf(['Иванов', 'Иван']), [loCaseInsensitive]);

Как видно из примера, если для поиска вы используете одно поле, то значение может передаваться напрямую из компонента Edit. Если же вы используете список полей, то должны передать в метод массив вариантов, в которых содержатся искомые значения, по одному на каждое поле. При установке компонента ADOTable в раздел uses прописывается модуль ADODB, который содержит описания всех свойств, методов и событий компонента. Желательно использовать метод в том модуле, где установлен этот компонент.

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

public
    { Public declarations }
   procedure MyLocate(s: String);

Как видите, в процедуру передается параметр - строка. В ней мы будем передавать искомую фамилию. Если курсор находится на описании нашей процедуры, то нажмите <Ctrl + Shift + C>, чтобы сгенерировать процедуру автоматически. Процедура будет иметь следующий код:

procedure TfDM.MyLocate(s: String);
begin
  TLichData.Locate('Фамилия', s, [loPartialKey]);
end;

Таким образом, при нахождении подходящей записи курсор будет перемещаться к ней.

На главной форме выделите компонент Edit, предназначенный для поиска по фамилии. Создайте для него событие onChange, которое наступает при изменении текста в поле компонента. В созданной процедуре пропишите вызов поиска:

fDM.MyLocate(Edit1.Text);

Сохраните пример, скомпилируйте и опробуйте результаты поиска. Метод Locate рекомендуется использовать везде, где это возможно, поскольку он всегда пытается применить наиболее быстрый поиск. Если поле индексировано, и использование индекса ускорит процесс поиска, Locate использует индекс. Если поле не имеет индекса, Locate все равно ищет данные наиболее быстрым способом. Это делает вашу программу независимой от индексов.

Метод Lookup

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

function Lookup (const KeyFields: String;
                 const KeyValues: Variant;
                 const ResultFields: String) : Variant;

Как вы видите, первые два параметра такие же, как у Locate. А вот третий параметр и возвращаемое значение отличаются. В строке ResultFields через точку с запятой перечисляются поля таблицы, значения которых метод должен вернуть. Возвращаются эти значения в виде вариантного массива. Проблема в том, что вернуться может значение Null, то есть, ничего, или Empty (пустой) и это нужно проверять. Рассмотрим работу метода Lookup на примере нашей программы.

Прежде всего, вспомним, как работает тип данных Variant. В переменную типа Variant можно поместить любое значение, в том числе и массив. Этот тип данных обычно используют, когда не известно заранее, данные какого типа нам понадобятся на этапе выполнения программы. Когда переменной типа Variant присвоено значение, имеется возможность проверить тип данных этого значения. Для этого служит функция VarType():

function VarType(const V: Variant): TVarType;

В качестве параметра в функцию передается переменная вариантного типа. Функция возвращает значение типа TVarType. Это значение указывает, какого типа данные содержатся в переменной. Значение может быть varSmallint (короткое целое), varInteger (целое), varCurrency (денежный формат) и так далее. Чтобы увидеть полный список возвращаемых функцией значений, в редакторе кода установите курсор на название функции и нажмите <Ctrl + F1>, вызвав контекстный справочник. Нас же в данном примере интересуют всего два значения: varNull (записи нет) и varEmpty (запись пустая). Если в программе мы заранее не проведем проверку на эти значения, то вполне можем вызвать ошибку программы. Если же поиск прошел успешно, то будет возвращен массив вариантных значений, элементы которого начинаются с нуля. Каждый элемент массива будет содержать данные одного из указанных полей.

Загрузите проект программы. Для поиска воспользуемся кнопкой с надписью "Найти", расположенной в верхней части главной формы. Идея такова: пользователь вводит в поле Edit1 какую то фамилию и нажимает кнопку "Найти". Событие onClick этой кнопки собирает в строковую переменную значения четырех указанных полей найденной записи. Причем после каждого значения в строку добавляется символ "#13" (переход на новую строку), формируя многострочный отчет. Затем эту строку мы выведем на экран функцией ShowMessage().

Итак, в окне главной формы дважды щелкните по кнопке "Найти", генерируя событие onClick. Полный листинг процедуры приведен ниже:

{щелкнули по кнопке Найти}
procedure TfMain.BitBtn1Click(Sender: TObject);
var
  myLookup: Variant; //для получения результата
  s : String; //для отчета
begin
  //получаем результат:
  myLookup := fDM.TLichData.Lookup('Фамилия', Edit1.Text,
              'Фамилия;Имя;Отчество;Образование');
  //проверяем, не Null ли это:
  if VarType(myLookup) = varNull then
     ShowMessage('Сотрудник с такой фамилией не найден!')
  else if VarType(myLookup) = varEmpty then
     ShowMessage('Запись не найдена!')
  //если это массив, то из его элементов собираем
  //многострочную строку:
  else if VarIsArray(myLookup) then begin
     s := myLookup[0] + #13 +  myLookup[1] + #13 + 
            myLookup[2] + #13 + myLookup[3];
     //и выводим ее на экран:
     ShowMessage(s);
  end; //else if
end;

Комментарии достаточно подробны, чтобы вы разобрались с кодом. Сохраните проект, скомпилируйте его и запустите. Опробуйте этот способ поиска.

Фильтрация данных

Фильтрацию данных применяют не реже а, пожалуй, даже чаще, чем поиск. Разница в том, что при поиске данных пользователь видит все записи таблицы, при этом курсор либо переходит к искомой записи, либо он получает данные этой записи в виде результата работы функции. При фильтрации дело обстоит иначе. Пользователь в результате видит только те записи, которые удовлетворяют условиям фильтра, остальные записи становятся скрытыми. Конечно, таким образом искать нужные данные проще. Можно указать в условиях фильтра, что требуется вывести всех сотрудников, чья фамилия начинается на "И". Пользователь увидит только их. А можно и по-другому: вывести всех сотрудников, которые поступили на работу в период между 2000 и 2005 годом. Короче говоря, удобство работы пользователя с вашей программой зависит от вашей фантазии. Рассмотрим основные способы фильтрации записей.

Свойство Filter

Свойство Filter - наиболее часто используемый способ фильтрации записей, имеет тип String. Вначале программист задает условия фильтрации в этом свойстве, затем присваивает логическому свойству Filtered значение True, после чего таблица будет отфильтрована. Условия фильтрации должны входить в строку, например:

fDM.TLichData.Filter := 'Фамилия =''Иванов''';

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

Фамилия = 'Иванов'

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

//fDM.MyLocate(Edit1.Text); - закомментировали

  fDM.TLichData.Filter := 'Фамилия >=' + QuotedStr(Edit1.Text);
  fDM.TLichData.Filtered := True;

Откомпилируйте проект и запустите его на выполнение. При введении только первой буквы фамилии записи уже начинают фильтроваться. К примеру, если мы ввели букву "Л", то остаются записи с фамилиями, начинающимися от буквы "Л" до конца алфавита. Можно также улучшить поиск, если при этом еще отсортировать записи по индексу, но об этом чуть позже. Функция QuotedStr() возвращает переданный ей текст, заключенный в апострофы. Условие фильтра можно было бы описать и так:

fDM.TLichData.Filter := 'Фамилия >=''' + 
     Edit1.Text + '''';

Сложность заключается в том, что в этом случае приходится считать апострофы. Функция QuotedStr() помогает решить эту проблему.

Событие onFilterRecord

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

В событие передаются два параметра. Первый параметр - набор данных DataSet. С ним можно обращаться, как с именем фильтруемой таблицы. Второй параметр - логическая переменная Accept. Этой переменной нужно передавать результат проверки условия фильтра. Если переменная Accept возвращает False, то запись не принимается, и не будет отображаться. Соответственно, если возвращается True, то запись принимается. Рассмотрим этот способ на примере. Суть примера в следующем: необходимо отфильтровать записи по начальным (или всем) буквам фамилии, вводимым пользователем в поле Edit1. В предыдущем примере, если бы мы ввели букву "И", то вышли бы фамилии, первой буквой которых были бы "И" - "Я". Это не так удобно. Сделаем так, чтобы если пользователь введет букву "И", то останутся только фамилии, начинающиеся на "И". Если пользователь введет еще букву "в", то останутся только фамилии, начинающиеся на "Ив", и так далее. Поочередно вводя начальные буквы, пользователь доберется до нужных фамилий.

Для начала подготовим модуль данных. В нем нам потребуется создать глобальную переменную ed, чтобы мы могли передавать в нее текст из компонента Edit1:

var
  fDM: TfDM;
  ed: String; //текст из Edit1

Этого действия можно было бы избежать, если бы компонент ADOTable, подключенный к таблице LichData, располагался на главной форме. Но поскольку он находится в модуле данных, то и событие onFilterRecord будет сгенерировано в нем. А в этом событии нам нужно будет знать, что в данный момент находится в поле ввода Edit1. Именно для этого и нужна глобальная переменная ed.

Далее выделяем TLichData, то есть, компонент ADOTable, подключенный к таблице LichData. На вкладке Events (События) инспектора объектов найдите событие onFilterRecord и дважды щелкните по нему, сгенерировав процедуру. Полный листинг процедуры:

{onFilterRecord главной таблицы}
procedure TfDM.TLichDataFilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
var
  s : String; //для значения поля
begin
  //получаем столько начальных букв из поля Фамилия,
  //сколько букв имеется в переменной ed:
  s := Copy(DataSet['Фамилия'], 1, Length(ed));
  //делаем проверку на совпадение значений:
  Accept := s = ed;
end;

Здесь в переменную s попадает столько начальных букв из поля "Фамилия", сколько букв содержит в данный момент компонент Edit1 на главной форме (эти буквы мы передадим в переменную ed чуть позже). Если текст в переменной s совпадает с текстом из поля Edit1, то переменной Accept присваивается True, и запись принимается. Иначе запись отфильтровывается. Не забудьте сохранить проект.

Далее перейдем в главную форму. Нужно удалить весь текст из события onChange компонента Edit1, и вписать новый:

{Изменение поиска по фамилии}
procedure TfMain.Edit1Change(Sender: TObject);
begin
  //если в поле Edit1 есть хоть одна буква,
  if Edit1.Text <> '' then begin
    fDM.TLichData.Filtered := False; //отключаем фильтр
    ed := Edit1.Text; //передаем в fDM новый текст
    fDM.TLichData.Filtered := True; //включаем фильтр
  end
  //если букв нет, фильтрацию отключаем:
  else fDM.TLichData.Filtered := False;
end;

Вот и все. Что же тут у нас происходит? Как только пользователь введет хоть одну букву, срабатывает событие onChange компонента Edit1. Если в Edit1 есть хоть одна буква, то мы вначале отключаем фильтрацию, отменяя прошлый фильтр, если он был. Затем мы передаем в глобальную переменную ed, расположенную в модуле данных, текст из Edit1. Далее снова включаем фильтр. При этом срабатывает событие onFilterRecord нашей таблицы, и в этом событии сравнивается текущее значение переменной ed и записей поля "Фамилия".

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

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

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

При создании в базе данных таблицы LichData мы указали поля "Фамилия" и "Имя", как индексированные. Этим и воспользуемся. Чтобы включить сортировку записей по полю "Фамилия", достаточно указать название поля в свойстве IndexFieldNames таблицы:

fDM.TLichData.IndexFieldNames := 'Фамилия';

Если требуется отключить сортировку, этому свойству присваивается пустая строка:

fDM.TLichData.IndexFieldNames := '';

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

Сортировать по фамилии
Сортировать по имени
Не сортировать
-
Обратная сортировка

В редакторе меню выделите пункт "Сортировать по фамилии" и измените свойство Name этого пункта на NFam. Пункт "Сортировать по имени" переименуйте в NImya. Пункт "Не сортировать" - в NNet, а пункт "Обратная сортировка" - в NObrat.

Вначале создайте обработчик событий для пункта "Не сортировать" (дважды щелкните по пункту). Тут все просто:

{Не сортировать}
procedure TfMain.NNetClick(Sender: TObject);
begin
  fDM.TLichData.IndexFieldNames := '';
end;

Для обработчика событий пункта "Сортировать по фамилии" код немного сложней:

{Сортировать по фамилии}
procedure TfMain.NFamClick(Sender: TObject);
var
  stype : String;
begin
  //выбираем направление сортировки:
  if NObrat.Checked then stype := ' DESC' //обратная сортировка
  else stype := ' ASC';  //прямая сортировка
  //сортируем
  fDM.TLichData.IndexFieldNames := 'Фамилия' + stype;
end;

Здесь, в зависимости от состояния свойства Checked пункта "Обратная сортировка" мы присваиваем строковой переменной stype либо значение ' ASC ' (прямая сортировка), либо ' DESC ' (обратная сортировка). Обратите внимание, что первым символом строки является пробел, он нужен, чтобы строка не "прилепилась" к названию поля. Далее мы устанавливаем индекс, указывая имя поля и добавляя к нему значение переменной stype. Таким образом, если Checked пункта "Обратная сортировка" имеет значение True (галочка установлена), мы добавляем ' DESC ', или ' ASC ' в противном случае. В результате имя индексного поля может быть либо "Фамилия ASC ", либо "Фамилия DESC ".

Сортировку по имени кодируем аналогичным образом:

{Сортировать по имени}
procedure TfMain.NImyaClick(Sender: TObject);
var
  stype : String;
begin
  //выбираем направление сортировки:
  if NObrat.Checked then stype := ' DESC'
  else stype := ' ASC';
  //сортируем
  fDM.TLichData.IndexFieldNames := 'Имя' + stype;
end;

Нам осталось указать код пункта всплывающего меню "Обратная сортировка". Тут нам нужно не просто установить галочку, если ее не было, но также проверить - есть ли сортировка по какому либо полю? Если таблица отсортирована, требуется ее пересортировать по этому же полю, но уже в обратном порядке. Вот код:

{Команда "Обратная сортировка"}
procedure TfMain.NObratClick(Sender: TObject);
begin
  //изменяем направление сортировки
  NObrat.Checked := not NObrat.Checked;
  //если сортировка по фамилии, пересортируем
  if Pos('Фамилия',fDM.TLichData.IndexFieldNames)>0 then
    fMain.NFamClick(Sender);
  //если сортировка по имени, пересортируем
  if Pos('Имя',fDM.TLichData.IndexFieldNames)>0 then
    fMain.NImyaClick(Sender);
end;

Как видите, мы использовали функцию Pos(), которая возвратит ноль, если в строке не найдено указанной подстроки, или номер символа, с которого эта подстрока начинается, если она есть. Нам нужно определить, не входит ли в имя индексного поля "Фамилия" или "Имя". Ведь к имени поля добавлена строка ' ASC ' или ' DESC ', так что прямая проверка

if fDM.TLichData.IndexFieldNames = 'Фамилия' then

результата не даст, в любом случае результатом было бы False. Ну а для пересортировки мы вызываем соответствующий пункт меню, чтобы не писать код сортировки еще раз, например:

fMain.NFamClick(Sender);

Следует заметить, что при большом количестве записей в таблице смена индексного поля будет несколько замедлять работу приложения. Тем не менее, индексация таблицы - очень удобный и часто применяемый способ организации вывода записей.

В свойстве PopupMenu верхней сетки DBGrid1 выберите созданное только что всплывающее меню, чтобы оно открывалось только над этой сеткой, сохраните проект, скомпилируйте его и опробуйте сортировку данных.

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