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

Цель лекции

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

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

В программах, работающих с базами данных, часто используют поиск данных. Для чего еще нужны базы данных, как не для этого? Самый простой, но в то же время и самый медленный, "тяжеловесный" поиск, это, пожалуй, последовательный перебор. Вы переходите на первую запись таблицы, создаете цикл, который длится до последней записи, и внутри этого цикла проверяете необходимое условие. Также можно делать и обратный перебор, от последней записи к первой. В таблице 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 позволяет задать некоторые опции поиска:

• loCaseInsensitive - поиск ведется без учета высоты букв, то есть, считаются одинаковыми строки "строка", "Строка" или "СТРОКА".
• loPartialKey - запись будет удовлетворять условию, если ее часть содержит искомый текст. То есть, если мы ищем "ст", то удовлетворять условию будут "строка", "станция", "стажер" и т.п.
• Пустой набор [] указывает, что настройки поиска игнорируются. То есть, строка ищется "как есть".

Примеры использования метода 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() помогает решить эту проблему.