· В соответствии с вариантом спроектируйте базу данных любым методом проектирования на основе описания предметной области.
1. В СУБД InterBase 6.0/Firebird 1.5 реализуйте серверную часть спроектированной ранее базы данных, которая должна содержать:
1) таблицы с определением первичного ключа
2) триггеры, реализующие каскадное обновление и каскадное удаление связанных полей
3) триггеры, присваивающие уникальное значение генератора в поле первичного ключа
4) триггеры, обеспечивающие журнализацию изменений определенной таблицы
5) не менее трех различных хранимых процедур
2. В ИСР Delphi создайте клиентское приложение, используя любую технологию доступа к данным (BDE, IBX, ADO и др.) с возможностью поиска и фильтрации данных, а также вывода отчета.
1) У каждой книги в библиотеке свой идентификационный номер.
2) Каждая книга может быть издана в одном издательстве.
3) В одном и том же издательстве издается несколько книг.
4) Каждая книга имеет определенное количество страниц, тип переплета, величину тиража.
5) Каждая книга в конкретный момент может находиться или в библиотеке или на руках только у одного читателя.
6) Каждый читатель может взять из библиотеки несколько книг.
7) Каждый читатель имеет уникальный номер абонемента.
8) Имя читателя не уникально. Название издательства не уникально.
9) Читатель имеет ФИО, телефон, адрес. Издательство – название, город.
Проектирование базы данных
Приведём ER-диаграмму в соответствии с описанием предметной области:
Рис.1. ER-диаграмма
Так как каждая книга в конкретный момент времени может находиться на руках только у одного читателя, то мы можем связать сущности «Книга» и «Читатели» по полю «ID Абонента» связью типа Многие к одному так как в этом поле, являющемся внешним ключом для атрибута «Книги», могут повторяться записи, ибо один и тот же читатель может взять сразу несколько книг. Если это поле содержит значение NULL, то значит, что данная книга ни находится на руках у читателя, а находится в библиотеке. Добавим для сущности «Книга» дополнительное свойство «Дата выдачи». Значения этого поля должны равняться NULL если значение поля «ID Абонента» так же равняется NULL.
Приведём базу данных к третьей нормальной форме. Свойство «Издательство» сущности «Книги» имеет дополнительные свойства не зависящие от ключевых. Поэтому сделаем свойство «Издательство» самостоятельной сущностью и введём для него дополнительное свойство «ID Издательства» и сделаем его ключевым.
Рис.2. ER-диаграмма
Опишем структуру каждой таблицы.
Таблица «Книги» (Books)
| Наименование поля
|
Тип данных
|
Ограничения
|
| ID Книги (ID_Books) | Целое число | NOTNULL Первичный ключ Значение уникально |
| Наименование (Name) | Строка (30) | NOT NULL |
| ID Издательства (ID_Publishers) | Целое число | NOT NULL Внешний ключ |
| Количество страниц (K_pages) | Целое число | NOT NULL |
| Тип переплёта (Cover) | Строка (15) | NOT NULL |
| Тираж (Tiraz) | Целое число | NOT NULL |
| ID Абонента (ID_abonent) | Целое число | Внешний ключ |
| Дата выдачи (Date_issue) | Дата |
Таблица «Издательства» (Publishers)
| Наименование поля
|
Тип данных
|
Ограничения
|
| ID Издательства (ID_Publishers) | Целое число | NOTNULL Первичный ключ Значение уникально |
| Название (Name) | Строка (20) | NOT NULL |
| Город (City) | Строка (20) |
Таблица «Читатели» (Readers)
| Наименование поля
|
Тип данных
|
Ограничения
|
| ID Абонента (ID_ abonent) | Целое число | NOTNULL Первичный ключ Значение уникально |
| ФИО (FIO) | Строка (30) | NOTNULL |
| Телефон (Telephone) | Строка (10) | |
| Адрес (Address) | Строка (20) | NOTNULL |
Реализация серверной части базы данных
Серверную часть базы данных будем выполнять на сервере FireBird 2.1 в визуальной среде разработки IBExpert.
Запустим IBExpert. Выберем команду Database/CreateDatabase (База данных/Создать базу данных). В появившемся окне CreateDatabase (Создание базы данных) укажем:
· Server (Сервер) – Local (локальный)
· Database (Файл БД) – C:01LIBRARY.fdb
· UserName (Имя_пользователя) – SYSDBA
· Password (пароль) – masterkey – это имя и пароль администратора по умолчанию.
· Charset (кодировка) – WIN1251
· SQL Dialect (Диалект БД) – Dialect 3 (Диалект 3).
Нажмём OK. база данных будет создана. В появившемся окне DatabaseRegister (Регистрация БД) выберем в поле Версия сервера FireBird 2.1. Нажмем кнопку Register, и база данных будет зарегистрирована и отобразится в окне DatabaseExplorer. В этом окне выберем зарегистрированную базу данных и выберем Database/ConnecttoDatabase (База данных/Подключиться к Базе данных). В окне DatabaseExplorer отобразятся все элементы базы данных.
Создание таблиц
Создадим таблицу «Книги» (Books). Выберем Database/NewTable (База данных/Новая таблица). В окне Table (Таблица) зададим имя Books и определим называния атрибутов их типы данных и ограничения. Откомпилируем скрипт, нажав <Ctrl+F9>.
Зададим ключевое поле таблицы «BOOKS». Для этого сделаем активной вкладку Constraint (Ограничения). Щелкнем правой кнопкой мыши в свободном пространстве окна Table (Таблица) и в выпадающем меню выберем пункт Newprimarykey (новый первичный ключ). Далее установим первичный ключ для атрибута «ID_BOOKS». Для этого щелкнем левой кнопкой мыши в поле OnField (На поле) и установим нужный атрибут. Установим сортировку по возрастанию значения индекса – выберем Ascending для IndexSorting.
Перейдём на вкладку Fields (поля) щелчком правой кнопкой мыши на поле «ID_BOOKS» вызовем меню, из которого выберем EditField ID_BOOKS. В появившемся окне перейдём на вкладку Autoincrement (авто приращение). Ниже появятся три дополнительные вкладки Generator (генератор) Trigger (триггер) и Procedure (процедура). На вкладке Generator выберем CreateGenerator (создать генератор), присвоим ему имя «GEN_BOOKS_ID» и начальное значение укажем равным 1. Перейдём на вкладку Trigger и выберем CreateTrigger (создать триггер). Автоматически будет написан код:
IF (NEW."ID_ BOOKS" IS NULL) THEN
NEW."ID_ BOOKS" = GEN_ID(GEN_BOOKS_ID,1);
Здесь содержится оператор условия. Если при обращении к новому значению столбца «ID_ BOOKS» (оператор NEW), оно является пустым (оператор IS NULL) то ему присваивается значение, генерируемое созданным ранее генератором GEN_BOOKS_ID (GEN_ID(имя_генератора, шаг) – оператор обращения к генератору). Нажмём «ОК» и откомпилируем скрипт.
Аналогично мы создадим при создании других таблиц генераторы и триггеры, присваивающие уникальные значения ключевым столбцам этих таблиц.
Создадим две оставшиеся таблицы: «Издательства» (Publishers) и «Читатели» (Readers).
Создание триггеров
Создадим триггеры журнализации изменений таблицы «BOOKS». Для этого создадим таблицу «BOOKS_JOURNAL», в которую будут автоматически записываться любые изменения, добавления, удаления в таблице «BOOKS». При этом будет фиксироваться дата (поле «DATE»), операция (INS, UPD, DEL) над таблицей «BOOKS» (поле «OPERATION»), а также старое и новое значение столбцов этой таблицы. Для операции удаления новое значение столбцов будет пустым. Для операции добавления пустым будет старое значение столбцов.
Для журнализации была создана таблица:
CREATE TABLE BOOKS_JOURNAL (
OPERATION CHAR(6) NOT NULL,
ID_BOOKS_OLD INTEGER,
ID_BOOKS_NEW INTEGER,
ID_PUBLISHERS_OLD INTEGER,
ID_PUBLISHERS_NEW INTEGER,
K_PAGES_OLD INTEGER,
K_PAGES_NEW INTEGER,
COVER_OLD VARCHAR(15),
COVER_NEW VARCHAR(15),
TIRAZ_OLD INTEGER,
TIRAZ_NEW INTEGER,
ID_ABONENT_OLD INTEGER,
ID_ABONENT_NEW INTEGER,
DATE_ISSUE_OLD DATE,
DATE_ISSUE_NEW DATE,
NAME_OLD VARCHAR(20),
NAME_NEW VARCHAR(20),
DATE_ DATE NOT NULL,
TIME_ TIME NOT NULL,
ID_JOURNAL INTEGER NOT NULL);
ALTER TABLE BOOKS_JOURNAL ADD PRIMARY KEY (ID_JOURNAL);
Теперь создадим триггер, закреплённый за таблицей «BOOKS» для вставки, изменения и удаления записей. Для создания триггера выберем Triggers/NewTrigger...(Триггер/Новый триггер). Появится окно создания триггера в котором укажем:
· Имя – «JOURNAL»;
· Для таблицы – «BOOKS»;
· Тип – «BEFORE», т. е. выполняется прежде операции;
· Укажем INSERT, UPDATE, DELETE.
Полный текст триггера будет следующий:
SET TERM ^ ;
CREATE OR ALTER TRIGGER JOURNAL FOR BOOKS
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0
AS
begin
IF (INSERTING) THEN
begin
insert into BOOKS_JOURNAL values ('INSERT', null, new.id_books, null, new.id_publishers, null, new.k_pages, null, new.cover, null, new.tiraz, null, new.id_abonent, null, new.date_issue, null, new.name, 'now', 'now', gen_id(generator_journal,1));
end
IF (updating) THEN
begin
insert into BOOKS_JOURNAL values ('UPDATE', old.id_books, new.id_books, old.id_publishers, new.id_publishers, old.k_pages, new.k_pages, old.cover, new.cover, old.tiraz, new.tiraz, old.id_abonent, new.id_abonent, old.date_issue, new.date_issue, old.name, new.name, 'now', 'now', gen_id(generator_journal,1));
end
IF (deleting) THEN
begin
insert into BOOKS_JOURNAL values ('DELETE', old.id_books, null, old.id_publishers, null, old.k_pages, null, old.cover, null, old.tiraz, null, old.id_abonent, null, old.date_issue, null, old.name, null, 'now', 'now', gen_id(generator_journal,1));
end
end
^ SET TERM ; ^
Триггер состоит из трёх условных операторов, проверяющих выполняется ли над записями таблицы соответственно операция вставки, изменения и удаления. Для операции вставки будет выполнен первый оператор, где в таблице журнала в поле «OPERATION» будет вставлено значение «INSERT», в поле «DATE_» текущая дата (значение «now»), в поле «TIME_» текущее время (значение «now»), также будут переписаны все новые значения полей, а в полях таблицы журнала, определённых для хранения старых значений полей таблицы «BOOKS», занесутся значения NULL. Второй оператор « IF (updating) THEN» проверяет, выполняется ли изменение данных, и если да, то выполнится оператор вставки записи в таблицу журнала «BOOKS_JOURNAL». Этот оператор задан аналогично предыдущему, только в поля под старые значения вместо NULL заносятся значения соответствующих столбцов таблицы «BOOKS» до их изменения. Аналогично задан последний оператор на удаления записи из таблицы «BOOKS», где NULL заносится в столбцы под новые значения.
Создадим триггеры каскадного обновления и удаления записей.
Создадим триггер каскадного обновления и удаления записей таблицы «BOOKS» при обновлении и удалении соответствующих им записей в таблице «PUBLISHERS».
Этот триггер закреплён за таблицей «PUBLISHERS», и задан как триггер для изменения и удаления записей. Для создания триггера выберем Triggers/NewTrigger...(Триггер/Новый триггер). Появится окно создания триггера, в котором укажем:
· Имя – «PUBLISHERS_CASCADE_BOOKS»;
· Для таблицы – «PUBLISHERS»;
· Тип – «BEFORE», т. е. выполняется прежде операции;
· Укажем UPDATE, DELETE.
Полный текст триггера будет следующий:
SET TERM ^ ;
CREATE OR ALTER TRIGGER PUBLISHERS_CASCADE_BOOKS FOR PUBLISHERS
ACTIVE BEFORE UPDATE OR DELETE POSITION 0
AS
begin
IF (updating) THEN
begin
update books C
set C.id_publishers = new.id_publishers
where c.id_publishers = old.id_publishers;
end
else
begin
delete from books C
where c.id_publishers = old.id_publishers;
end
end
^
SET TERM ; ^
Так как данный триггер выполняется при изменении и удалении записей из таблицы «PUBLISHERS», то в нём задано условие, проверяющее, какая операция над записями этой таблицы производится. IF (updating) THEN= ИСТИНА, если операция изменения (UPDATE), иначе =ЛОЖЬ (для данного триггера это операция удаления). В первом случае производится каскадное обновление записей при помощи оператора изменения данных UPDATE, во втором каскадное удаление при помощи операции DELETEFROM. В обоих случаях условием отбора записей на изменение или удаление является равенство значений поля «ID_PUBLISHERS» таблицы «BOOKS» и старым значением поля «ID_PUBLISHERS» таблицы «PUBLISHERS». В случае изменения данных этим полям присваиваются новые значения записей.
Аналогично создадим триггер каскадного обновления и удаления записей таблицы «BOOKS» при обновлении и удалении соответствующих им записей в таблице «READERS» – «READERS_CASCADE_BOOKS». Разница здесь в том, что триггер будет задан для таблицы «READERS» и в записях сравниваются значения полей «ID_ABONENT».
Создание хранимых процедур
Создадим три хранимых процедуры.
Первая процедура будет выводить список книг, находящихся в библиотеке. Для этого зададим запрос на выборку списка книг, в поле «BOOKS.ID_ABONENT» которых стоит значение NULL.
Для создания новой процедуры выберем ProceduresNewProcedure(ПроцедурыНовая процедура). Выберем OutputParameters (Выходные Параметры) затем Insertparameter/variable (Добавить параметр/переменную). Добавим параметр «NAME_BOOKS VARCHAR(30)» под название книги, а также ещё два – под наименование и город издательства – соответственно «NAME_PUBLISHERS VARCHAR(20)» и «CITY_PUBLISHERS VARCHAR(20)».
Листинг первой процедуры «BOOKS_LIBRARY»:
CREATE PROCEDURE BOOKS_LIBRARY
returns (
city_publishers varchar(20) character set win1251,
name_publishers varchar(20) character set win1251,
name_books varchar(30) character set win1251)
as
begin
for
select distinct books.name, publishers.name, publishers.city
from books inner join publishers on books.id_publishers = publishers.id_publishers
where books.id_abonent iS NULL
into: name_books, : name_publishers, : city_publishers
dosuspend;
end
В данной процедуре вместо обычного запроса на выборку SELECT использована конструкция «FOR SELECT ... INTO ... DO ...», которая производит обработку возвращаемого набора записей в цикле. Иначе если SELECT возвратит более одной строки, то возникнет ошибка «multiplerowsinsingletonselect». Здесь же после каждой возвращаемой строки производится принудительная выдача параметров, после чего они принимают новые значения при следующей итерации цикла и т. д. пока не будут выданы все строки, удовлетворяющие условию запроса.
Вторая процедура будет выводить список книг, выданных за указанный период (входные параметры – начальная и конечная дата). Зададим входные параметры «DATE_1 DATE» и «DATE_2 DATE». Зададим выходные параметры: «NAME_BOOKS VARCHAR (30)», «NAME_PUBLISHERS VARCHAR(20)» и «CITY_PUBLISHERS VARCHAR(20)».
Листинг процедуры «BOOKS_LIST_PERIOD»:
CREATE PROCEDURE BOOKS_LIST_PERIOD (
date_1 date,
date_2 date)
returns (
city_publishers varchar(20) character set win1251,
name_publishers varchar(20) character set win1251,
name_books varchar(30) character set win1251)
as
declare variable var_date date;
begin
for
select distinct books.name, publishers.name, publishers.city, books.date_issue
from books inner join publishers on books.id_publishers = publishers.id_publishers
into: name_books, : name_publishers, : city_publishers, :var_date
do if (var_date between date_1 and date_2) then suspend;
end
В этой процедуре в операторе циклической выборки для каждой записи, полученной в результате выполнения оператора выборки «SELECT», проверяется, лежит ли значение одной из возвращаемых SELECT-ом переменной var_date между двумя введёнными, заданными входными параметрами date_1 и date_2. Если да, то производится выдача процедурой значений выходных параметров при помощи оператора suspend.
Создадим третью процедуру, которая будет выводить количество книг и средний тираж по издательствам. Здесь выходные данные будут представлять собой значения функций агрегирования, вычисляемые для каждой группы по значению поля «PUBLISHERS. NAME».
Листинг процедуры «KOL_BOOKS_TIRAZ»:
CREATE PROCEDURE KOL_BOOKS_TIRAZ
returns (
publishers_name varchar(20) character set win1251,
avg_tiraz integer,
count_ integer)
as
begin
for
select distinct publishers.name, count(publishers.id_publishers), AVG(books
from books inner join publishers on books.id_publishers = publishers.id_publishers
group by publishers.name
into: publishers_name, : count_, : avg_tiraz
dosuspend;
end
Выходными параметрами процедуры являются:
· publishers_namevarchar(20) charactersetwin1251 – строковой параметр для значений имени издательства;
· avg_tirazinteger – параметр для среднего значения тиража книги каждой группы.
· count_ integer – параметр для выдачи значения количества строк каждой группы.
В данной процедуре осуществляется выборка значений поля «PUBLISHERS.NAME», количества возвращаемых строк и среднего значения по полю «BOOKS.TIRAZ» для каждого значения поля «PUBLISHERS.NAME».
Теперь структура базы данных готова. Заполним таблицы некоторыми записями:
Таблица «Издательства» (Publishers)
| ID_Publishers
|
Name
|
City
|
| 1 | Питер | С.- Петербург |
| 2 | ДиаСофт | Киев |
| 3 | КОРОНА принт | С.- Петербург |
| 4 | Финансы и статистика | Москва |
Таблица «Читатели» (Readers)
| ID
_ abonent |
FIO
|
Telephone
|
Address
|
| 1 | Иванов Вадим | 12345678 | Москва ул. 1 д.1 кв. 1 |
| 2 | Петров Борис | 11111111 | Орел ул. 1 д.1 кв. 1 |
| 3 | Сидоров Иван | 22222222 | Курск ул. 1 д.1 кв. 1 |
| 4 | Кузнецов Артем | 12121212 | Воронеж ул.1 д.1 кв.1 |
Таблица «Книги» (Books)
| ID_ Books
|
Name
|
ID_ Publishers
|
K_ pages
|
Cover
|
Tiraz
|
ID_ abonent
|
Date_issue
|
| 1 | Эффективная работа с СУБД | 1 | 704 | твёрдый | 6000 | 1 | 01.01.2009 |
| 2 | Delphi. Разработка баз данных | 1 | 477 | твёрдый | 5000 | 1 | 01.01.2009 |
| 3 | Базы данных и приложения | 2 | 592 | твёрдый | 7000 | 2 | 11.01.2009 |
| 4 | Базы данных | 2 | 416 | твёрдый | 5000 | NULL | NULL |
Создание клиентского приложения баз данных
Создадим клиентское приложение в ИСР Delphi, используя технологию доступа к данным InterBase
eXpress (
IBX).
Выберем File/New/Application (Файл/Новое/Приложение), затем добавим модуль данных для компонентов доступа к данным – File/New/DataModule (Файл/Новый/Модуль данных). Компоненты доступа к данным расположены на страницеDataAccessПалитры компонентов. Компоненты отображения данных расположены на страницеDataControls Палитры компонентов. Компоненты, используемые в технологии InterBaseeXpress распологаются на странице InterBase, а компоненты для создания отчётов – QReport.
Поместим на модуль данных компонент TIBDatabase. Укажем в свойстве DatabaseName полный путь (включая имя сервера) к выбранному файлу БД – «C:01LIBRARY.FDB».
Поместим следующие компоненты на форму модуля данных:
· компонент IBTransaction
· три IBDataSet,
· три DataSource
Подключимся к базе данных. Выделим компонент TIBDatabase и выберем из контекстного меню Database Editor.… В этом окне укажем User Name = SYSDBA, Character Set = WIN1251. Затем установим свойство Connected компонента IBDatabase1 равным True и свойство DefaultTransaction компонента IBDatabase1 равным IBTransaction1.
Зададим управление транзакциями. Сделаем активным компонент IBTransaction1, для чего его свойству Active придадим значение True. Вызовем редактор TransactionEditor..., и в появившемся диалоговом окне выберем уровень изоляции транзакций – ReadCommitted.
Установим значения свойств:
· DefaultAction – TACommitRetaining
· DefaultDatabase – IBDatabase1
· Params – read_committed
rec_version
nowait
· Active – True
Перейдём к компоненту IBDataSet1. Переименуем его на BOOKS_DataSet (свойство Name). Укажем базу данных – DataBase= IBDataBase1 и компонент обработки транзакций – Transaction = IBTransaction1. Укажем в свойстве SelectSQL текст основного запроса: «select* from BOOKS». При помощи свойства GeneratorField выбираем поле, значение которого присваивается генератором и сам генератор. Активируем компонент: Active – True. Вызовем редактор компонента DatasetEditor.... Выберем из списка TableName таблицу и нажмём кнопку GetTableFields (Получить поля таблицы). В списке KeyFields (Ключевые поля) выделим поле «ID_BOOKS», которое будут формировать условие WHERE в запросах. После нажатия на кнопку GenerateSQL автоматически сгенерируются значения свойств DeleteSQL, InsertSQL, ModifySQL, RefreshSQL. Эти значения станут равны:
DeleteSQL
:
delete from BOOKS
where
ID_BOOKS = :OLD_ID_BOOKS
InsertSQL:
insert into BOOKS
(ID_BOOKS, NAME, ID_PUBLISHERS, K_PAGES, COVER, TIRAZ, ID_ABONENT, DATE_ISSUE)
values
(:ID_BOOKS, :NAME, :ID_PUBLISHERS, :K_PAGES, :COVER, :TIRAZ, :ID_ABONENT,
:DATE_ISSUE)
ModifySQL:
update BOOKS
set
ID_BOOKS = :ID_BOOKS,
NAME = :NAME,
ID_PUBLISHERS = :ID_PUBLISHERS,
K_PAGES = :K_PAGES,
COVER = :COVER,
TIRAZ = :TIRAZ,
ID_ABONENT = :ID_ABONENT,
DATE_ISSUE = :DATE_ISSUE
where
ID_BOOKS = :OLD_ID_BOOKS
RefreshSQL:
Select
ID_BOOKS,
NAME,
ID_PUBLISHERS,
K_PAGES,
COVER,
TIRAZ,
ID_ABONENT,
DATE_ISSUE
from BOOKS
where
ID_BOOKS = :ID_BOOKS
Аналогично зададим значения свойств двум остальным компонентам IBDataSet.
У каждого компонента DataSource в свойстве Dataset укажем название соответствующего ему компонента IBDataSet.
Создадим три формы для отображения таблиц. На каждую форму поместим компоненты DBGrid и DBNavigator. У компонентов DBGrid и DBNavigator в свойстве DataSource укажем соответствующий компонент DataSource.
Поиск данных
Для поиска данных используется функция
function LocateNext(const KeyFields: string; const KeyValues: Variant; Options: TLocateOptions): Boolean;
где KeyFields – список полей, по которым производится поиск (имена полей разделяются точкой с запятой), KeyValues – список значений, сравниваемых со значениями этих полей (значения разделяются запятой), TLocateOptions – параметры поиска, где loCaselnsensitive означает поиск без учета регистра (заглавные и малые символы), а loPartialKey. – значения полей для поиска даны не полностью. Функция LocateNext позволяет находить несколько записей, удовлетворяющих условиям поиска, для отображения очередной записи следует вызвать функцию ещё раз. Функция возвращает значение логического типа, равного TRUE (ИСТИНА), если найдена подходящая запись, и FALSE (ЛОЖЬ) в противном случае.
На форме отображения и редактирования данных из таблицы BOOKS для поиска разместим следующие компоненты:
· Edit1 – для ввода пользователем значений KeyValues.
· Button1 – для активации поиска.
· Несколько компонентов CheckBox для указания списка полей для поиска, т. е. значения KeyFields.
В процедуре – обработчике события нажатия на кнопку Button1 сначала производится сбор строки списка полей KeyFields в соответствии с указаниями флажков CheckBox. Затем это значение вместе со значением поля Edit1 отправляется в функцию LocateNext через пользовательскую функцию loc(Fields,Values), описанную в модуле Unit2 (в модуле Unit2 описан класс TDataModule2 (форма модуля данных) в котором определён и компонент «BOOKS_DataSet: TIBDataSet;»).
Аналогично в программе организован поиск в таблицах PUBLISHERS и READERS.
Фильтрация данных
Зададим фильтрацию значений в таблице BOOKS.
На форме таблицы BOOKS поместим компоненты CheckBox для того, чтобы пользователь имел возможность включать и выключать фильтр по отдельным полям. Фильтр производится только по тем полям, для которых выбраны соответствующие им переключатели CheckBox, для этого в программе определены логические переменные (в модуле Unit2) для указания, следует ли фильтровать записи по введённому пользователем значению соответствующего поля. Для включения фильтра служит кнопка «Включить фильтр» где производится присвоение переменным (модуля Unit2) введённых значений указанных полей, после чего включается фильтр заданием свойству BOOKS_DataSet.Filtered значения True. После того, как свойству Filtered компонента TIBDataSet будет присвоено значение True то производится переоткрытие набора данных, но при этом выполняется метод OnFilterRecord этого компонента. Процедура – обработчик этого метода определена следующим образом:
procedureTDataModule2.BOOKS_DataSetFilterRecord(DataSet: TDataSet; varAccept: Boolean);
где DataSet соответствующий набор данных. Эта процедура выполняется для каждой записи и если возвращаемый этой процедурой параметр Accept равен True, то текущая запись отображается, в противном случае – нет. Пример фильтра:
if (DataSet['NAME'] <> Name_) thenAccept := False;
где NAME – название поля, Name_ – переменная, значение которой сравнивается со значением этого поля текущей записи.
В программе определён следующий фильтр:
procedure TDataModule2.BOOKS_DataSetFilterRecord(DataSet: TDataSet;
var Accept: Boolean);
begin
Accept := True;
if P1 then if (DataSet['NAME'] <> Name_) then Accept := False;
if P2 then if (DataSet['K_PAGES'] < K_Pages_1) then Accept := False;
if P3 then if (DataSet['K_PAGES'] > K_Pages_2) then Accept := False;
if P4 then if (DataSet['DATE_ISSUE'] < Date_1) then Accept := False;
if P5 then if (DataSet['DATE_ISSUE'] > Date_2) then Accept := False;
if P6 then if (DataSet['ID_PUBLISHERS'] <> Id_pub) then Accept := False;
if P7 then if (DataSet['COVER'] <> Cov) then Accept := False;
if P8 then if (DataSet['TIRAZ'] < Tiraz_1) then Accept := False;
if P9 then if (DataSet['TIRAZ'] > Tiraz_2) then Accept := False;
if 10 then if (DataSet['ID_ABONENT'] <> Id_ab) then Accept := False;
end;
Логические переменные P1 – P10 хранят значения, соответствующие выбранным флажкам CheckBox, и если флажок выбран, то соответствующая ему переменная будет равна True, а значит, будет производиться фильтр по значению этого поля. Изначально переменная Accept принимает значение True, т. е. считается, что строка проходит, если не будет показано невыполнение хотя бы одного условия фильтрации. Поэтому здесь проверяется невыполнение каждого из заданных условий (например вместо «DataSet['NAME'] = Name_» проверяется «DataSet['NAME'] <> Name_» и ставится не «Accept:= True» в случае выполнения этого условия, а «Accept := False»). Достаточно невыполнения одного условия, чтобы строка не прошла, т. е. переменная Accept принимает значение False.
Аналогично зададим фильтр и для других таблиц.
Вывод отчёта
Организуем вывод отчётов в программе. Для начала создадим запрос на выборку записей таблицы BOOKS, где вместо кодов издательства и читателя будет выводиться вся о них информация, полученная из таблиц PUBLISHERS и READERS. Для этого поместим компонент IBQuery, назовём его BOOKS_Query и в свойстве SQL запишем:
select all BOOKS.NAME, BOOKS.COVER, BOOKS.TIRAZ, BOOKS.K_PAGES, PUBLISHERS.NAME, PUBLISHERS.CITY, READERS.FIO, READERS.ADDRESS, READERS.TELEPHONE, BOOKS.DATE_ISSUE
from (BOOKS inner join PUBLISHERS on BOOKS.ID_PUBLISHERS= PUBLISHERS.ID_PUBLISHERS) left join READERS on BOOKS.ID_ABONENT= READERS.ID_ABONENT
order by BOOKS.NAME;
Запрос выводит поля из всех трёх таблиц. Таблицы BOOKS и PUBLISHERS связаны по равенству поля ID_PUBLISHERS, а полученная в результате такой связи выборка дополнительно связана с таблицей READERS по равенству поля ID_ABONENT. Здесь благодаря связи leftjoinв запрос выводятся записи, содержащие в поле BOOKS.ID_ABONENT значение NULL. Дополнительно к записям применяется сортировка по значению поля BOOKS.NAME.
Установим свойство Active компонента BOOKS_Query в True. Если при этом не появится сообщение об ошибке, то запрос SQL введён без ошибок.
Также поместим на модуль данных компонент DataSourse, назовём его BOOKS_Query_Source и в свойстве DataSet укажем BOOKS_Query.
Создадим новую форму и поместим на неё компонент DBGrid1 в свойстве DataSourse которого укажем DataModule2.BOOKS_Query_Source. На форме расположим кнопку «Вывести отчёт», по нажатию на которую будет выводиться отчёт запроса BOOKS_Query.
Для создания непосредственно отчёта создадим для него новую форму и поместим на неё компонент QuickRep. И укажем в свойстве DataSetDataModule2.BOOKS_Query. Укажем в свойстве Page.Orientation = poLandscape (Пейзажная ориентация страницы). У свойства Bands установим в True следующие подсвойства: HasDetail, hasTitle, PageHander. Поместим на компонент QuickRep в область Detail компоненты QRDBTextдля каждого поля, в свойстве DataSet которых укажем DataModule2.BOOKS_Query, а в свойстве DataField соответствующее поле. В область Title поместим такое же количество компонентов QRLabel для названий атрибутов, и укажем эти названия в свойстве Caption этих компонентов. Расположим на форме компоненты QRTextFilter, QRHTMLFilter для сохранения отчетов в форматах HTML, TXT. В области отчёта PageHander поместим компонент QRSysData для вывода текущей даты, для чего в свойстве Data установим значение qrsDate. Те же компоненты используем и для вывода текущего времени и текущего номера страницы.
Ввод SQL запросов и вывод хранимых процедур
Работая с приложением баз данных, пользователь должен иметь возможность задавать SQL запросы, некоторые из которых сохранены на сервере в виде хранимых процедур. Для обеспечения такой возможности следует на главную форму поместить компонент для ввода текста Memo, в который пользователь и будет вводить текст запроса. Затем на модуль данных поместим компонент IBQuery1: TIBQuery1 (соответственно и DataSource1: TDataSource1), создадим форму для вывода результата и поместим на неё компонент DBGrid1: TDBGrid1. Затем на главную форму поместим кнопку и в обработчике её нажатия зададим перезапись введённого запроса из текстового поля в свойство SQL компонента IBQuery1 после чего сделаем компонент IBQuery1 активным и отобразим форму вывода результата.
Если в запросе указать вывод хранимой процедуры, просто указав
select * from KOL_BOOKS_TIRAZ;
или
select * from BOOKS_LIBRARY;
то выполнится запрос, определённый в этой процедуре.
Листинг процедуры – обработчика события нажатие на кнопку «Вывести результат запроса»:
procedure TForm1.Button10Click(Sender: TObject);
var i:integer;
begin
DataModule2.IBQuery1.Active:=False;
DataModule2.IBQuery1.SQL.Clear;
for i:=0 to Memo1.Lines.Count do
begin
DataModule2.IBQuery1.SQL.Append(Memo1.Lines[i]);
end;
DataModule2.IBQuery1.Active:=True;
Form10.Show;
end;
Для хранимой процедуры с входными параметрами предварительно нужно указать значения этих параметров, поэтому вызов хранимой процедуры BOOKS_LIST_PERIOD организован отдельно. Листинг процедуры, выполняющей вызов этой процедуры в программе:
procedure TForm1.Button9Click(Sender: TObject);
begin
DataModule2.IBQuery1.SQL.Clear;
DataModule2.IBQuery1.SQL.Add('select * from BOOKS_LIST_PERIOD(' +#39+MaskEdit1.Text+#39+','+#39+MaskEdit2.Text+#39+');');
DataModule2.IBQuery1.Active:=true;
Form9.Show;
end;
Здесь при помощи процедуры Add добавляется запись к свойству SQL компонента IBQuery1 (предварительно оно очищается при помощи процедуры Clear). MaskEdit1.Text и MaskEdit2.Text – значения полей ввода значений входных параметров, #39 – ASCII код одинарной кавычки, кавычки нужны для заключения в них значений входных параметров хранимой процедуры в SQL запросе. Затем активируется компонент IBQuery1 и отображается форма вывода результата хранимой процедуры.
Список литературы
1. Ковязин С., Востриков С. Мир InterBase. Архитектура, администрирование и разработка приложений баз данных в InterBase/ Firebird/ Yaffil. – М.: КУДИЦ – ОБРАЗ, 2005. – 496 с.
2. Хомоненко А.Д., Гофман В.Э. Работа с базами данных в Delphi. – СПб.: БХВ – Петербург, 2005. – 640с.
3. Кондзюба С.П., Громов В.Н. Delphi 6. Базы данных и приложения: Лекции и упражнения. – Киев: ДиаСофт, 2001. – 576 с.
4. http://www.ibase.ru/devinfo/ibfaq.htm
5. http://www.piter.com/lib/978527200003/sql7.phtml?fil=Ch12
6. http://www.codenet.ru/progr/delphi/stat/SQL-Delphi.php