Визуализация и коррекция данных взаимосвязанных таблиц из клиентского приложения
Продолжим рассмотрение примера «Телефонный справочник». При рассмотрении примера к предыдущей лабораторной работе (п. 4.7.2) было отмечено, что спроектированное отношение имеет недопустимые с точки зрения теории баз данных аномалии добавления, включения и удаления.
Используя процедуру Кодда построения инфологической модели выделим основные сущности предметной области (рисунок 5.2).
Рисунок 5.2 – Диаграмма сущность-связь предметной области
«Телефонный справочник»
Для связи между отношениями и идентификации личности удобно использовать ключевое поле «Код личности». При этом в отношении «Личность» это поле является первичным ключом, а в отношении «Телефон» - внешним ключом.
Введем новые отношения в базе данных test.gdb.
CREATE TABLE PERSONA ( P_ID INTEGER NOT NULL,
FIO VARCHAR(50) CHARACTER SET WIN1251,
BDATE DATE)
CREATE TABLE PNUMBER ( P_ID INTEGER NOT NULL,
PNUM VARCHAR(10) NOT NULL, PTYPE VARCHAR(20));
Сформируем ограничения, обеспечивающие целостность базы данных. Создание нового первичного ключа в отношенииPERSONA показано на рисунке 5.3.
Рисунок 5.3 – Создание нового первичного ключа
На языке SQL аналогичное действие описывается предложением:
alter table PERSONA add constraint PK_PERSONA primary key (P_ID)
В отношении PNUMBER обозначим внешний ключ FK_PNUMBER, предназначенный для связи двух таблиц (рисунок 5.4).
Рисунок 5.4 – Создание внешнего ключа
alter table PNUMBER add constraint FK_PNUMBER foreign key (P_ID)
references PERSONA(P_ID) on delete CASCADE;
Правило удаления «CASCADE» означает, что при удалении записи из отношения PERSONA все связанные с ней записи из отношения PNUMBER удаляются автоматически.
Разработаем клиентское приложение, позволяющее автоматизировать процесс заполнения телефонного справочника. За основу возьмем приложение, разработанное в лабораторной работе №3.
Экранная форма, содержащая список абонентов будет иметь вид, показанный на рисунке 5.5.
Рисунок 5.5 – Список абонентов (экранная форма)
Так как данные теперь будут извлекаться из таблицы PERSONA модифицируем запрос в компоненте DataModule2.IBQuery:
SELECT * FROM PERSONA
Соответственно изменятся предложения языка SQL для выполнения операций удаления, вставки и обновления записей.
Фрагмент программного кода для добавление новой записи:
workquery.SQL.Add('insert into persona (p_id,fio,bdate)');
workquery.SQL.Add('VALUES (:p_id, :newfio,:newbdate)');
//Генерация ключа
workquery.ParamByName('p_id').AsInteger:=Random(High(integer));
//Перенос информации из полей ввода
workquery.ParamByName('newfio').AsString:=EdFIO.Text;
workquery.ParamByName('newbdate').AsDate:=DTP.Date;
В отличие от предыдущих лабораторных работ в таблицу PERSONA введен первичный ключ P_ID, который служит для идентификации личности и связи с таблицей PNUMBER. Чтобы избежать ввод ключа вручную используются 2 подхода:
1) создание ключа с помощью генератора случайных чисел, так же как это сделано для генерации GUID в COM-объектах. Именно этот способ используется в приведенном выше фрагменте программы;
2) использование генератора ключа средствами СУБД. Создание триггеров и генераторов будет рассмотрено в лабораторной работе №7.
За счет введения уникального ключевого поля значительно упрощаются процедуры удаления и обновления:
//Удаление записи
workquery.SQL.Add('delete from persona WHERE (p_id=:p_id)');
workquery.ParamByName('p_id').AsInteger:=
IBQuery.FieldByName('p_id').AsInteger;
//Обновление записи
workquery.SQL.Add('update persona set fio=:newfio,
bdate=:newbdate');
workquery.SQL.Add('WHERE (p_id=:p_id)');
workquery.ParamByName('p_id').AsInteger:=
IBQuery.FieldByName('p_id').AsInteger;
workquery.ParamByName('newfio').AsString:=EdFIO.Text;
workquery.ParamByName('newbdate').AsDate:=DTP.Date;
На невизуальной форме DataModule2 разместим компонент для реализации запросов телефонных номеров NumQuery. Для получения списка номеров выбранного клиента используется предложение SQL
SELECT * FROM PNUMBER WHERE P_ID=:P_ID
Параметр :P_ID должен быть сформирован перед активизацией запроса. Запрос должен активироваться каждый раз, когда пользователь перемещает указатель в таблице PERSONA. Для автоматизации этой операции можно использовать событие AfterScrollкомпонента IBQuery:
procedure TDataModule2.IBQueryAfterScroll(DataSet: TDataSet);
begin
NumQuery.Active:=false;
NumQuery.ParamByName('P_ID').AsInteger:=
IBQuery.FieldByName('P_ID').AsInteger;
NumQuery.Active:=true;
end;
Таким образом, каждый раз при смене текущей записи в таблице PERSONA будет изменяться выборка телефонов клиента.
Для связи с компонентом визуализации данных DBGrid дополним содержимое DataModule2 еще одним компонентомDataSourceNum:TDataSource.
Разработаем форму для вывода и редакции номеров телефонов (рисунок 5.6).
Рисунок 5.6 – Экранная форма для вывода номеров телефонов
Логика функционирования интерфейса данной экранной формы во многом повторяет логику работы главной формы. Приведем пример реализации операции добавления нового номера телефона.
procedure TNumEditForm.BtAddClick(Sender: TObject);
begin
with DataModule2 do
begin
NumQuery.Active:=false; //деактивизируем запрос вывода
//номеров абонента
WorkNumQuery.SQL.Clear;
//Создаем запрос для добавления новой записи
WorkNumQuery.SQL.Add('INSERT INTO PNUMBER
(p_id,pnum,ptype) VALUES (:p_id,:pnum,:ptype)');
//Идентификатор абонента получаем из таблицы PERSONA
WorkNumQuery.ParamByName('p_id').AsInteger:=
IBQuery.FieldByName('p_id').AsInteger;
//Заполняем информационные поля
WorkNumQuery.ParamByName('pnum').AsString:=NumEdit.Text;
WorkNumQuery.ParamByName('ptype').AsString:=CBPType.Text;
//Выполняем запрос
try
WorkNumQuery.ExecSQL;
WorkNumQuery.Transaction.Commit;
except
showmessage('Ошибка выполнения операции');
WorkNumQuery.Transaction.Rollback;
end;
NumQuery.Active:=true;
end;
end;
Как следует из текста программы, на форму DataModule2 добавлен компонент WorkNumQuery для выполнения рабочих запросов к таблице Pnumber. Введение дополнительного компонента неслучайно. При выполнении этой процедуры без измененийDataModule2 проявляется крайне неприятный эффект: запрос IBQuery закрывается при выполнении операцииWorkNumQuery.Transaction.Commit. Действительно, оба запроса используют один компонент управления транзакциямиMainTransaction. Завершение транзакции после добавления телефонного номера приводит к завершению транзакции запроса списка абонентов. В результате оператор NumQuery.Active:=true выполняется с ошибкой, так как значение P_ID не определено.
Для разрешения конфликта можно использовать два подхода:
1) активизировать запрос IBQuery перед выполнением запроса NumQuery. При этом необходимо принудительно установить указатель на абонента, для которого выполнялась операция добавления телефонного номера;
2) разделить транзакции для запросов IBQuery и NumQuery.
Второй подход по многим причинам является наиболее предпочтительным. Поэтому на форму DataModule2 помещается дополнительный компонент NumTransaction, который в свою очередь используют компоненты NumQuery, и WorkNumQuery (рисунок 5.7).
Рисунок 5.7 – Модифицированная форма DataModule2