17. Пример создания удаленной БД и приложения для работы с ней
17.1. Создание БД "Учет товаров на складе"Создадим простую БД для учета товаров на складе. Рассмотрим все шаги, необходимые для этого.
На рис. 17.1 приведена модель БД, которая будет далее использоваться в примерах второй части книги.
Структура процессов, подлежащих учету в нашей БД, проста. Каждый день на склад поступают товары. Допустим, процесс прихода товаров и проблема учета такого прихода нас не интересуют. Например, сведения о приходе и количестве товара аккумулируются в другой БД. Нам необходимо лишь вести учет отпуска товаров со склада. При этом подразумевается, что если товар отпускается, он на складе есть и как минимум в том количестве, которое нужно отпустить Пусть контроль за соответствием остатков товара запросам на его продажу ведет кто-нибудь другой - неважно, программа или человек.
Таким образом, предлагаемая в качестве примера простейшая БД предназначена для механического учета отпуска товара со склада.
Каждый товар имеет уникальное название (по нему построен первичный ключ) и обладает двумя атрибутами - единицей измерения и стоимостью за единицу измерения Если один и тот же товар присутствует на складе в разных единицах измерения (например, сахар в мешках, фасованный в пакетах, фасованный в коробках и т.д.; огурцы в банках и огурцы развесные и пр.), для каждого такого случая один и тот же товар должен иметь семантически одинаковое, но синтаксически разное название, например "Огурцы баночные", "Огурцы развесные" Так сделано нами для простоты Несомненно, многие разработчики БД определят в таблице "Товары" составной первичный ключ по столбцам НАЗВАНИЕ ТОВАРА и ЕД ИЗМЕРЕНИЯ, чтобы иметь возможность указывать синтаксически одно название товара для разных случаев единиц измерения Отметим, что практическая предпочтительность того или иного подхода зависит от особенностей конкретной предметной области.
Сразу оговоримся, что цена товара не имеет тенденции изменяться во времени Товар всегда отпускается по фиксированной цене, указанной в столбце ЦЕНА ЕД. ИЗМЕРЕНИЯ для данного товара Такое условие взято нами для простоты БД, иначе мы можем погрязнуть в вопросах организации складской деятельности, вместо того, чтобы акцентировать внимание на технических аспектах разработки БД. Для тех, кто не согласен с данной оговоркой, можно предложить мысленно вернуться во времена развитого социализма, когда покупка и отпуск товаров осуществлялся
по установленной свыше цене, или допустить, что цены установлены в условных единицах, не подверженных воздействию инфляции. Договоримся, что в течение одного и того же дня один и тот же покупатель может многократно получать со склада один и тот же товар. Поэтому в таблице "Расход товара" в качестве первичного ключа не годится последовательность столбцов ДАТА РАСХОДА, НАЗВАНИЕ ТОВАРА, НАЗВАНИЕ ПОКУПАТЕЛЯ. Такие значения могут дублироваться. Поэтому введем столбец НОМЕР РАСХОДА, который должен содержать некоторый уникальный номер, и примем его в качестве первичного ключа. Вопрос, каким образом попадают в данный столбец уникальные значения, оставим открытым (необходимо использование генератора, речь о котором пойдет в этой главе ниже).Информация собственно о
покупателе не требует никаких оговорок, за исключением того, что столбец ГОРОД выделен нами из АДРЕСА для дальнейших упражнений по составлению запросов и использованию оператора SELECT. Первичным ключом в данном случае служит столбец НАЗВАНИЕ ПОКУПАТЕЛЯ.Как видим, таблица "Расход товара" является дочерней таблицей для таблиц "Товары" и "Покупатели" и находится с ними в связи "многие-к-
одному". Поэтому между дочерней таблицей и каждой из ее родительских таблиц должна быть установлена ссылочная целостность. Для этого в таблице "Товары" определим внешний ключ по столбцу "НАЗВАНИЕ ТОВАРА", ссылающийся на первичный ключ таблицы "Товары", и внешний ключ по столбцу "НАЗВАНИЕ ПОКУПАТЕЛЯ", ссылающийся на первичный ключ таблицы "Покупатели".
17.2. Создание псевдонима удаленной БД
Пусть БД будет находиться в каталоге
D:\BOOK\IB_SKLAD Заметим, что на этапе создания БД и отладки приложения мы будем использовать локальный вариант InterBase, поставляемый с Delphi Client/Server Suite Пусть создаваемая БД будет иметь имя IB_SKLAD GDB. Назначим ей псевдоним ib_skl, используя утилиту BDE Administrator (рис. 17.2). Данный псевдоним будет нам нужен для написания клиентской части приложения по учету расхода товаров со склада.Обратим внимание на то, что для БД, которые будут содержать русскоязычную текстовую информацию в строковых столбцах с использованием набора символов
WIN 1251 и порядка сортировки PXW_CYRL, необходимо сразу установить для псевдонима драйвер Pdox ANSI Cyrillic в строке LANGDRIVER. Используя утилиту InterBase Windows ISQL (interactive SQL), далее называемый WISQL, создадим БД. Для этого запустим утилиту WISQL и в главном меню выберем режим File \ Create Database. Далее, в окне диалога укажем имя БД, имя пользователя SYSDBA, пароль masterkey (имя и пароль системного администратора для локального сервера InterBase), а также в окне DataBase Options - параметры БД. В нашем случае в качестве единственного параметра будет введена установка по умолчанию набора символов WIN1251 (рис. 17.3).Перед соединением с БД необходимо установить текущий набор символов в режиме
Session | Advanced Settings (рис. 17.4).Соединение с БД производится в режиме
File \ Connect to DataBase. Для соединения необходимо указать имя БД, имя пользователя и пароль, (рис. 17.5)В случае отсутствия сообщений об ошибках, соединение с БД установлено.
Перед созданием таблиц должны быть реализованы SQL-операторы
CREATE TABLE для каждой создаваемой таблицы. Существует два способа выполнения SQL-операторов в WISQL. Первый заключается в наборе текста оператора в окне SQL Statement. В дальнейшем набранный оператор выполняется после нажатия кнопки Run. Второй способ состоит в формировании текстового файла, который содержит от одного до нескольких SQL-операторов. Такой файл выполняется в режиме File \ Run an SQL script.Для описываемого примера воспользуемся первым способом. Поэтапно введем и выполним в окне
SQL Statement (рис. 17.6) следующие операторы:CREATE TABLE POKUPATELI(
POKUP VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
GOROD VARCHAR(12) COLLATE PXW_CYRL,
ADRES VARCHAR(20) COLLATE PXW_CYRL,
PRIMARY KEY(POKUP)
) ;
CREATE TABLE TOVARY(
TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
ED_IZM VARCHAR(lO) NOT NULL COLLATE PXW_CYRL,
ZENA INTEGER NOT NULL,
PRIMARY KEY(TOVAR)
) ;
CREATE TABLE RASHOD(
N_RASH INTEGER NOT NULL,
DAT_RASH DATE NOT NULL,
KOLVO INTEGER NOT NULL,
TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
POKUP VARCHAR(20) COLLATE PXW_CYRL,
PRIMARY KEY(N_RASH),
FOREIGN KEY(POKUP) REFERENCES POKUPATELI,
FOREIGN KEY(TOVAR) REFERENCES TOVARY
Для подтверждения выполненных действий выберите в главном меню режим
File | Commit Work или введите в окне SQL Statement и выполните оператор COMMIT Если Вы по каким-либо причинам решили не запоминать изменений, выберите режим File | Rollback Work или выполните оператор ROLLBACK. Выполните разъединение с БД, выбрав в главном меню опцию File | Disconnect from DataBase и затем выберите Еxit для выхода из W1SQL.17.6. Создание приложения для занесения данных
Создадим приложение
Inp dpr для занесения данных в БД "Расход товаров со склада". Для этого создадим приложение.17.6.1. Соединение с БД из приложения
Соединение с БД, расположенной на сервере, осуществляется при помощи компонента
TDataBaseРазместим в форме такой компонент с именем
DataBase 1 и установим в его свойство AliasName значение ранее созданного псевдонима 'ib_skl' В свойство DaiaBaseName поместим значение 'm_sklad' Это имя будет в дальнейшем служить именем нашей БД в компонентах, реализующих запросы к базе данныхДля установки параметров соединения с БД вызовем в редактор свойств БД Для этого щелкнем по компоненту
DataBasel правой кнопкой мыши и выберем в появившемся меню опцию Datable Editor Нажмем кнопку Defaults для получения параметров БД, принятых по умолчанию Введем парольPASSWORD=masterkey
затем укажем имя пользователя
USER NAME=SYSDBA
после чего снимем отметку с флажка
Login Prompt, указывающего на необходимость выдачи окна ввода имени пользователя и пароля (рис. 17.7)Нажмем кнопку
Ok. Теперь, поскольку в параметрах БД указаны имя пользователя и пароль, приложение будет соединяться с БД автоматически. Для соединения с БД установим свойство Connected в True17.6.2. Создание формы ввода в ТБД
Для каждой из таблиц создадим НД Для этого разместим в форме три компонента
TTable с именами Tovary Table, Роkир Table и RashodTable. В свойство DalabaseName каждого из них занесем 'm_sklad', выбрав его из выпадающего списка псевдонимов БД. Как можно заметить, имя 'm_sklad' установлено в свойстве DataBaseName компонента Databasel, в то время как имя зарегистрированного в BDE Configuration Utility псевдонима на самом деле есть 'ib_skl'.Для каждого
TTable выберем имя соответствующей ТБД в выпадающем списке свойства TableName Затем откроем НД, установив True в свойство Active каждого TTable .После этого разместим в форме три компонента
TDataSource и три компонента TDBGrid и соединим каждый компонент TDataSource с одним из компонентов TTable (свойство DataSet) Затем соединим каждый из компонентов TDBGrid с одним из компонентов TDataSource (свойство DataSource). Затем настроим заголовки столбцов каждого компонента TDBGrid, используя редактор столбцов TDBGrid Для этого нужно сделать компонент TDBGrid текущим элементом разрабатываемой формы, нажать правую кнопку мыши, во всплывающем меню выбрать Columns Editor, в появившемся списке перемещаться по полям и для каждого поля в инспекторе объектов установить заголовок столбца TDBGrid в свойстве TitleCaption.Занесем в таблицы информацию, подобранную таким образом, чтобы в дальнейшем рассмотреть широкий спектр возможностей, предоставляемый оператором
SELECT (рис 17 8). Самая объемная глава второй части книги, посвященная часто используемому SQL-оператору SELECT, будет построена на использовании запросов к созданной нами учебной БД.17.6.3. Создание триггеров для поддержания каскадных воздействий
В рассматриваемом варианте БД соединения дочерних и родительских таблиц (соответственно
RASHOD и TOVARY, POKUPATELI) являются "жесткими". Напомним, что при реализации ссылочной целостности на уровне PRIMARY KEY - FOREIGN KEY нельзя изменять значение поля связи как в дочерней таблице, так и в родительской таблице, если для данного значения первичного ключа существуют дочерние записи.Дпя избранной структуры данных
CREATE TABLE POKUPATELI(
POKUP VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
GOROD VARCHAR(12) COLLATE PXW_CYRL,
ADRES VARCHAR(20) COLLATE PXW_CYRL,
PRIMARY KEY(POKUP)
) ;
CREATE TABLE TOVARY(
TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
ED_IZM VARCHAR(IO) NOT NULL COLLATE PXW_CYRL,
ZENA INTEGER NOT NULL,
PRIMARY KEY(TOVAR)
) ;
CREATE TABLE RASHOD(
N_RASH INTEGER NOT NULL,
DAT_RASH DATE NOT NULL,
KOLVO INTEGER NOT NULL,
TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
POKUP VARCHAR(20) COLLATE PXW_CYRL,
PRIMARY KEY(N_RASH),
FOREIGN KEY(POKUP) REFERENCES POKUPATELI,
FOREIGN KEY(TOVAR) REFERENCES TOVARY
) ;
жирным отмечены ограничения по внешнему ключу. Таким образом, если в таблице
TOVARY для какого-либо товара существует расход в таблице RASHOD, изменение названия товара в таблице TOVARY будет блокировано. При попытке изменить в таблице TOVAR значение наименования товара с "Сахар" на "Сахар кусковой" (рис. 17 9) будет возбуждено исключение из-за нарушения жестких ограничений целостности (рис. 17.10)Можно увидеть, что жесткие ограничения целостности будут нам мешать в дальнейшем, когда возникнет необходимость изменять название товара или покупателя Если мы не будем менять названия товаров и покупателей, жесткие ограничения целостности можно оставить, однако будем считать, что потребность в подобных изменениях все-таки может возникнуть
Поэтому вместо жестких ограничений целостности нам необходимо реализовать возможность каскадных воздействий - таких, что:
• при изменении значения столбца TOVAR в таблице TOVARY эти изменения должны быть отражены в дочерних записях в таблице RASHOD (если такие есть для изменяемой записи в таблице TOVARY),• при удалении записи в таблице
TOVARY должны быть удалены дочерние записи в таблице RASHOD, если они естьДля достижения этой цели нужно удалить не устраивающие нас ограничения целостности между таблицами
TOVARY и RASHOD и затем определить в БД триггеры, реализующие каскадные воздействияДля удаления внешнего ключа
FOREIGN KEY(TOVAR) REFERENCES TOVARY необходимо удалить ограничение ссылочной целостности, определяемое этим ключом Однако в явном виде ограничение не было определено, напримерCONSTRAINTS TOV_RASH FOREIGN KEY(TOVAR) REFERENCES TOVARY
где
TOV_RASH есть имя ограничения Поэтому необходимо установить системное имя, присвоенное InterBase данному ограничению внешнего ключа Это системное имя, INTEG_32, можно увидеть в сообщении, выдаваемом при возбуждении исключения по нарушению жесткой ссылочной целостности (см выше) Следует убедиться, что INTEG_32 действительно есть ограничение целостности, которое следует удалить Для этой цели необходимо воспользоваться утилитой SQL Ex plorer, найдя для таблицы RASHOD узел Refrential Constraint's (рис 17.11) Искомое нами ограничение имеет имя INTEG.32 Удалим его Для этого запустим WISQL, соединимся с БД и выполним операторALTER TABLE RASHOD
DROP CONSTRAINT INTEG_32;
Затем создадим триггеры, реализующие каскадное изменение в таблице
RASHOD при изменении названия товара в таблице TOVARYCREATE TRIGGER BU_TOVARY FOR TOVARY
ACTIVE
BEFORE UPDATE
AS
BEGIN
IF (OLD.TOVAR 0 NEW.TOVAR) THEN
UPDATE RASHOD
SET TOVAR = NEW.TOVAR
WHERE TOVAR = OLD.TOVAR;
END
и каскадное удаление дочерних записей в таблице
RASHOD при удалении родительской записи в таблице TOVARY:CREATE TRIGGER AD_TOVARY FOR TOVARY
ACTIVE
AFTER DELETE
AS
BEGIN
DELETE FROM RASHOD
WHERE RASHOD.TOVAR =TOVARY.TOVAR;
END
Теперь, при изменении наименования товара в таблице
TOVARY произойдут каскадные изменения наименования товара в дочерних записях таблицы RASHOD (рис 17 12 и 17 13)В таблице
RASHOD первичный индекс построен по столбцу N_RASH (номер события расхода товара со склада) По этому столбцу построен первичный ключ, поскольку никакие другие столбцы или их комбинации не могут уникально идентифицировать запись, т к в принципе допускается, что один и тот же покупатель может в течение одной даты не только произвести несколько закупок одного и того же товара, но в двух или более таких закупках приобрести одинаковое количество одного и того же товара Поэтому в столбце N_RASH должны содержаться уникальные значения Известно, что у таблиц InterBase нет столбца типа "автоинкремент", чье значение автоматически увеличивается при добавлении новой записи (как, например, у таблиц Paradox). Однако в InterBase есть генераторы, всякий раз возвращающие уникальные значения при обращении к ним при помощи функции GEN_ID Для начала определим сам генератор и установим его стартовое значение в 1, выполнив в WISQL операторыCREATE GENERATOR RASHOD_N_RASH;
SET GENERATOR RASHOD_N_RASH TO 1;
Затем определим для БД хранимую процедуру
CREATE PROCEDURE GET_N_RASH
RETURNS (NR INTEGER)
AS
BEGIN
NR = GEN_ID(RASHOD_N_RASH,1) ;
END
В приложении, используемом для ввода информации в таблицы TOVAR
, POKUPATELI, RASHOD, определим компонент StoredProc1 для вызова хранимой процедуры GET_N_RASH Определим свойства этого компонента свойству DatabaseName присвоим значение 'in_skl' (значение свойства DatabaseName компонента Database 1), свойству StoredProcName присвоим значение 'GET_N_RASH' (имя хранимой процедуры)Далее, вызвав редактор параметров компонента StoredProcI (щелкнув по нему правой кнопкой мыши и выбрав из всплывающего меню опцию
Define Parameters), убедимся, что имя и тип выходного параметра процедуры установлены правильно (рис 17.14)Для компонента
RashodTable (тип TTable), ассоциированного с таблицей RASHOD, определим обработчик события AfterInsert, наступающего немедленно после перевода RashodTable в состояние dslnsert:procedure TForm!.RashodTableAfterInsert(DataSet: TDataSet) ;
begin
StoredProc!.ExecProc;
RashodTable.FieldByName('N_RASH').Value := StoredProcI.ParamByName('NR').Valued-end;
ЗАМЕЧАНИЕ.
Тот же код вместо обработчика события AfterInsert можно поместить в обработчик события OnNewRecord, наступающего также после перевода RashodTable в состояние dslnsert; обработчик этого события обычно используется для занесения умалчиваемого значения в столбцы добавляемой записи, что имеет место до того, как значения столбцов вновь добавляемой записи будут визуализированы и доступны для изменения пользователем.Теперь при переводе
RashodTable в состояние dslnsert будет открываться компонент StoredProcI, который будет вызывать процедуру GET_N_RASH. С помощью этой процедуры определяется уникальное значение, которое затем помещается в столбец N_RASH вновь добавляемой записи. Это будет происходить до того, как вновь добавляемая запись будет показана пользователю для корректировки.