31.2. Фильтрация и сортировка данных
Предположим, мы хотим выводить на экран не все записи БД, а лишь удовлетворяющие некоторому условию. Такое действие известно как фильтрация данных. Давайте отобразим только те записи, у которых в поле YEARF хранится значение 2003.
Первый способ – фильтрация на стороне клиента. Делается это так:
WITH DVD DO
BEGIN
Filter:='YEARF=2003';
Filtered:=True;
…
Filtered:=False
END;
В свойство Filter в виде текстовой строки записывается логическое выражение для отбора записей. Свойство Filtered логического типа включает/выключает фильтрацию.
А как отследить ситуацию, когда в БД нет записей, удовлетворяющих заданному условию? Очень просто:
WITH DVD DO
BEGIN
Filter:='YEARF=2003';
Filtered:=True;
IF BOF AND EOF THEN
MessageDlg('Ничего не найдено',
mtInformation,[mOK],0);
…
Filtered:=False
END;
Если свойства BOF (начало БД) и EOF (конец БД) одновременно истинны, это значит, что база пуста.
Второй способ – "на ходу" изменить SQL-запрос и заставить сервер прислать новую выборку данных. Делается это так:
VAR oldSQL:STRING;
BEGIN
WITH DVD DO
BEGIN
oldSQL:=SQL[0];
Close;
SQL[0]:='SELECT * FROM DVD WHERE YEARF=2003';
Prepare;
Open;
…
{ снятие фильтра }
Close;
SQL[0]:=oldSQL;
Prepare;
Open
END;
В переменную oldSQL запоминается текущий SQL-запрос, он хранится в свойстве SQL. Поскольку свойство SQL – это массив строк, мы обращается к его первому элементу с индексом 0. Запрос закрывается, меняется текст SQL-выражения, а затем он снова открывается. Для снятия фильтрации нужно восстановить старый запрос, хранящийся в переменной oldSQL.
Очень важно, что SQL-выражение можно динамически создавать как текстовую строку. Например, пусть пользователь вводит год с клавиатуры при помощи поля ввода Edit1:
SQL[0]:='SELECT * FROM DVD WHERE '+ 'YEARF='+Form1.Edit1.Text;
Сортировка данных очень часто применятся в БД. Проще всего она делается на стороне сервера средствами SQL при помощи конструкции ORDER BY. Давайте отсортируем БД по русским названиям (поле TITLE_RUSSIAN):
VAR oldSQL:STRING;
BEGIN
WITH DVD DO
BEGIN
oldSQL:=SQL[0];
Close;
SQL[0]:='SELECT * FROM DVD ORDER BY TITLE_RUSSIAN';
Prepare;
Open;
…
{ снятие сортировки }
Close;
SQL[0]:=oldSQL;
Prepare;
Open
END;
32.Работа с нормализованными бд
32.1. Связывание таблиц
Очень важно научиться устанавливать связи между таблицами, что позволяет выполнять нормализацию БД. Давайте добавим в нашу БД по DVD-дискам информацию о том, какая кинокомпания сняла тот или иной фильм. Если просто добавить в таблицу dvd.db текстовое поле, то названия компаний в нем будут повторяться, что означает нарушение принципов нормализации БД. Следовательно, список кинокомпаний нужно вынести в отдельную таблицу comp.db, а в таблице dvd.db оставить поле типа Long Integer, в котором будут храниться числа – ссылки на строки таблицы comp.db (Рис. 32 .125).
dvd.db – главная таблица |
|
comp.db – подчиненная таблица |
||
Имя поля |
Тип данных |
|
Имя поля |
Тип данных |
ID |
а |
|
ID |
автоинкремент |
NAME_RUSSIAN |
текст |
|
NAME |
текст |
NAME_ENGLISH |
текст |
|
|
|
NUMBER |
целое число |
|
|
|
SUBTITLES |
булевский |
|
|
|
YEARF |
целое число |
|
|
|
COMPANY |
длинное целое |
|
|
|
втоинкремент
Рис. 32.125 Структуры главной и подчиненной таблиц.
Под ссылкой понимается значение, хранящееся в автоинкрементном поле ID подчиненной таблицы.
Откройте таблицу
dvd.db в
программе Database Desktop
и измените ее структуру, щелкнув по
кнопке
Restructure. Добавьте поле
COMPANY
с типом данных Long Integer
и сохраните изменения. При помощи той
же программы создайте новую таблицу
comp.db с полями
ID
(автоинкремент) и NAME
(текст, 250 символов). Не забудьте изменить
язык таблицы! Сохраните таблицу в той
же директории, что и таблицу dvd.db.
В созданную таблицу comp.db
добавьте несколько записей с названиями
кинокомпаний, а поле COMPANY
таблицы dvd.db
для всех записей пока занесите значение
1.
Вернитесь в Delphi. В модуль данных добавьте компонент Query, назвав его comp. Установите его свойства Active, DatabaseName, SessionName, RequestLive в те же значения, что и у компонента DVD. В свойство SQL занесите выражение SELECT * FROM comp.
Разумеется, добавленный запрос надо открывать в начале работы программы, причем обязательно с соблюдением простого правила: все подчиненные таблицы открываются до открытия главной таблицы. Поэтому в обработчик события модуля данных OnCreate добавим следующий код (новые строки выделены жирным шрифтом):
// открываем таблицу comp.db
WITH comp DO
BEGIN
Prepare;
Open
END;
// открываем таблицу dvd.db
WITH DVD DO
BEGIN
Prepare;
Open
END
END;
Закрывать каждый запрос при завершении нет необходимости, все запросы закрываются автоматически методом CloseDatasets компонента Database.
Поскольку мы изменили структуру таблицы dvd.db, об этом надо сообщить нашей программе. Проверьте, что в компоненте Database в поле AliasName записано имя алиаса DVD, иначе Delphi не сможет открыть базу. Щелкните правой кнопкой мыши на запросе DVD и выберите пункты меню Fields Editor Add fields. В открывшемся списке появится новое поле COMPANY. Щелкните по кнопке OK для его добавления. Аналогично добавьте поля в запросе comp.
Приступаем к самому важному – связыванию главной и подчиненной таблиц. Принцип связывания таков: в главной таблице создается фиктивное поле, которого в самой таблице нет. В это фиктивное поле программа автоматически заносит значения, извлекаемые из подчиненной таблицы. Снова щелкните правой кнопкой мыши на запросе DVD и выберите пункты меню Fields Editor New field. Открывается окно добавления фиктивного поля (Рис. 32 .126) – самое важное окно во всей работе с БД
Рис. 32.126 Окно создания фиктивного поля.
В поле NAME введите придуманное имя фиктивного поля, которое не должно совпадать с именами настоящих полей в главной таблице. Можно, чтобы не запутаться, всегда начинать имена фиктивных полей с буквы V – виртуальное. В списке Type выберите тип данных, которые будут отображаться в фиктивном поле, а в поле Size введите размер этих данных. Очевидно, тип и размер фиктивного поля должны совпадать с типом и размером поля с данными в подчиненной таблице, в рассматриваемом случае это поле Name в таблице comp.db. Установите переключатель в положение Lookup – это означает, что данные в новом поле будут браться из другой таблицы.
А теперь самое важное: правильно заполнить четыре списка внизу окна:
Key Fields: имя поля в главной таблице, в котором хранятся ссылки на подчиненную таблицу. В данном случае это поле Company.
Dataset: имя подчиненной таблицы. Запрос к таблице comp.db у нас называется comp.
Lookup Keys: имя автоинкрементного поля в подчиненной таблице. В данном случае это поле ID таблицы comp.db.
Result field: имя поля в подчиненной таблице, где хранятся данные, отображаемые в фиктивном поле главной таблицы. В данном случае это поле name в базе comp.
Новое поле нужно вывести на экран. перейдите на главную форму и уже известным способом добавьте в компоненте DBGrid новую колонку, связанную с полем VCOMP. Измените обработчик события OnRezise – в таблице стало на одну колонку больше и надо поменять код на примерно следующий:
procedure TForm1.FormResize(Sender: TObject);
CONST w:ARRAY[0..5] OF REAL=(0.25,0.25,0.1,0.1,0.1,0.2);
VAR i:BYTE;
begin
FOR i:=0 TO LENGTH(w)-1 DO
DBGrid1.Columns[i].Width:=TRUNC((DBGrid1.Width- GetSystemMetrics(SM_CXVSCROLL)-LENGTH(w))*w[i])-1
end;
Все! Запускаем программу и видим очень интересный результат (Рис. 32 .127).
Рис. 32.127 Отображение фиктивного поля.
Мало того, что все работает правильно, так еще в фиктивном поле автоматически появляется раскрывающийся список для его удобного редактирования.