Базы данных (стр. 5 )

i, k,j:Integer;

f:TField;

begin

Grid. DataSource. DataSet. DisableControls; // Отключение набора данных от визуальных // компонентов, связанных с ним

k:=Grid. Columns. Count-1; // номер последнего столбца в соответствующем // компоненте TDBGrid

try

Grid. DataSource. DataSet. First; // Перейти к первой записи справочника

// Получить доступ к приложению Excel с помощью OLE-automation

ExcelApp := CreateOleObject('Excel. Application');

// Добавить рабочую книгу

ExcelApp. Workbooks. Add(1);

// Получить ссылку на первый лист первой рабочей книги

Sheet:=ExcelApp. Workbooks[1].Worksheets[1];

Try // Попытаться задать имя листа совпадающим с заголовка окна формы

Sheet. Name:=Caption;

except

end;

// В верхнюю левую ячейку листа записать содержимое заголовка окна формы

c:=Sheet. Cells[1,1]; c. Font. Bold:=True; c. Font. Size:=15; c. Value:=Caption;

// В следующую ячейку листа записать дату формирования твердой копии

c:=Sheet. Cells[2,1]; c. Font. Italic:=True;

c. Value:='Сформировано '+DateTimeToStr(Now);

Sheet. Cells[3, 1].Value:='¹№п/п';

for i:=0 to k do // Перечислить имена полей справочника в том порядке,

begin // как они заданы в соответсвующем компоненте TDBGrid c:=Sheet. Cells[3, 2+i];

c. Value := Grid. Columns[i].Title. Caption;

end;

j:=1;

while not Grid. DataSource. DataSet. Eof do

begin // Организовать цикл по всем записям справочника Sheet. Cells[j+2, 1].Value:=j;

for i:=0 to k do

begin // Организовать цикл по всем полям записи справочника c:=Sheet. Cells[j+2, 2+i];

try

// Получить ссылку на очередное поле

f:=Grid. DataSource. DataSet. FieldByName(Grid. Columns[i].FieldName );

// Занести значение поля в очередную клетку листа

c. Value:=f. Value;

except

end;

end;

// Перейти к следующей записи справочника

Grid. DataSource. DataSet. Next;

// Перейти к следующей строке листа электорнной таблицы Excel

j:=j+1;

end;

except

on e:Exception do ShowMessage('Ошибка:'+e. Message);

end;

Grid. DataSource. DataSet. EnableControls; // Подключение набора данных к визуальным // компонентам

ExcelApp. Visible := True; // Сделать Excel видимой для пользователя

end;

@ Задание:

Реализуйте подпрограмму выдачи результата произвольного SQL-запроса в таблицу Excel.

@

Работа №9. Просмотр справочников иерархической структуры

Иерархический справочник – это таблица в базе данных, между записями которой устанавливается (рекурсивное) отношение 1:N. Пример иерархического справочника – справочник диагнозов болезней или справочник клиентов торговой фирмы, в частности:

Физические лица 1 уровень иерархии

Местные 2 уровень иерархии

Иванов 3 уровень иерархии

Петров

Иногородние 2 уровень иерархии

Сидоров 3 уровень иерархии

…

Организации 1 уровень иерархии

ООО 2 уровень иерархии

Гарант-Сервис 3 уровень иерархии

… …

ОАО

КМК

ЗСМК

…

Прочие

…

Рисунок 9.1. Справочник иерархической структуры.

Для отражения такой иерархии в структуру таблицы в дополнение к идентификатору записи вводятся еще два поля: Родитель (PARENT), Потомок (CHILD).

Таблица 9.1. Отображение иерархической структуры в реляционной таблице.

ID	PARENT	CHILD	NAME
1	0	1	Физические лица
2	1	2	Местные
3	2	3	Иванов
4	2	4	Петров
5	1	5	Иногородние
6	5	0	Сидоров
7	5	0	…
9	6	7	ООО
10	7	0	Гарант-Сервис
11	8	0	…
12	6	9	ОАО
13	9	0	КМК
14	9	0	ЗСМК
15	9	0	…
16	9	10	Прочие
17	10	11	…

@ Задание:

Разработайте универсальный класс (THCatalog) для просмотра иерархических справочников, и на его основе реализуйте формы просмотра (а в последствии и редактирования) справочников.

@

Этот класс должен быть основан на модальной форме. Основной интерфейсный элемент в этой форме – компонент класса TDBGrid (по имени Grid). Форма должна создаваться динамически по мере запроса на вывод окна просмотра-редактирования справочника. В элементе Grid должны присутствовать необходимые для просмотра поля справочника (искусственные коды, уникально идентифицирующие запись в справочнике, не должны быть видны по умолчанию, однако следует создать элемент управления, позволяющий сделать их видимыми).

Для просмотра такого справочника следует использовать не TADOTable-компонент, а TADOQuery. Для него следует задать выражение SQL-запроса, которое позволит извлечь записи только одного уровня иерархии. Для этого придется ввести в SQL-запрос параметр, задающий значение поля PARENT, по которому будет осуществляться выборка.

SELECT PARENT, CHILD, NAME FROM CLIENTS WHERE PARENT = :p

В этом выражении :p – обозначение параметра запроса по имени ‘p’.

После вставки этого кода в свойство SQL компонента TADOQuery (договоримся, что его имя – Q), в свойстве Parameters образуется один элемент, соответствующий параметру ‘p’. Необходимо в редакторе параметров TADOQuery определить его тип – в нашем случае Integer.

Для выборки записей самого верхнего уровня иерархии следует задать значение параметра p равным 0. Значение параметра SQL-запроса можно задать следующим образом (компонент TADOQuery по имени Q):

Q. Close;

Q. Parameters. ParamByName(‘p’).Value := 0;

Q. Open;

Для перехода на один уровень иерархии вниз необходимо задать соответствующее значение того же параметра:

Var i:Integerl

…

i := Q. FieldByName(‘CHILD’).AsInteger; //Определить уровень потомков текущей записи

Q. Close;

Q. Parameters. ParamByName(‘p’).Value:= i; // Перейти на уровень потомков, сделать его текущим

Q. Open;

Для возврата на предыдущий уровень иерархии придется использовать еще один компонент TADOQuery (по имени Q1) (см. таблицу 2.), свойство SQL которого должно содержать код

SELECT PARENT FROM CLIENTS WHERE CHILD = :p1

а параметр ‘p1’ должен будет задаваться в момент, когда нам захочется вернуться на уровень назад (см. Таблица №2):

Q1.Parameters. ParamByName(‘p1’).Value := Q. FieldByName(‘PARENT’).Value;

Q1.Open;

Q. Close;

Q. Parameters. ParamByName(‘p’).Value := Q1.FieldByName(‘PARENT’).AsInteger;

Q1.Close;

Q. Open;

Работа №10. Редактирование справочников иерархической структуры

Имеются 4 случая редактирования справочника, задевающие его иерархическую структуру.

1. Для осуществления ввода новой записи на уже существующем уровне необходимо в поле PARENT новой записи записать общее для всех записей данного уровня значение, а в поле CHILD занести значение 0.

Level := Q. FieldByName(‘PARENT’).AsInteger;

Q2.SQL. Clear;

Q2.SQL. Add(‘INSERT INTO CLIENTS (PARENT, CHILD) VALUES (:p, 0)’);

// Можно задать тип параметра динамически!

Q2.Parameters. ParamByName(‘p’).DataType := ftInteger;

// Задать значение параметра

Q2.Parameters. ParamByName(‘p’).Value := level;

Q2.ExecSQL;

После выполнения этого SQL-запроса, в таблице появиться соответствующая запись, но ее нельзя будет увидеть на форме просмотра-редактирования справочника до тех пор, пока SQL-запрос компонента Q не будет выполнен повторно!

Q. Close;

Q. Open;

2. Для создания записи-потомка для записи, которая еще не имеет детей, необходимо выполнить более сложную работу. Во первых, надо определить значение идентификатора для записей нового уровня (поле PARENT), во вторых это значение занести в поле CHILD родительской записи, в третьих в поле PARENT новой записи-потомка записать это значение, а в поле CHILD занести значение 0:

Q2.SQL. Clear;

Q2.SQL. Add(‘SELECT MAX(PARENT) AS MP FROM CLIENTS’);

Q2.Open;

Level := Q2.FieldByName(‘MP’).AsInteger + 1;

Q2.Close;

Q. Edit;

Q. FieldByName(‘CHILD’).AsInteger := level;

Q. Post;

Q2.SQL. Clear;

Q2.SQL. Add(‘INSERT INTO CLIENTS (PARENT, CHILD) VALUES (:p, 0)’);

Q2.Parameters. ParamByName(‘p’).DataType := ftInteger;

Q2.Parameters. ParamByName(‘p’).Value := level;

Q2.ExecSQL;

Так как описанные действия достаточно сложны, следует выполнить их в рамках явной транзакции для того, чтобы обеспечить целостность данных.

Try

DM. AdoConnection1.BeginTrans;

Q2.SQL. Clear;

Q2.SQL. Add(‘SELECT MAX(PARENT) AS MP FROM CLIENTS’);

Q2.Open;

Level := Q2.FieldByName(‘MP’).AsInteger + 1;

Q2.Close;

Q. Edit;

Q. FieldByName(‘CHILD’).AsInteger := level;

Q. Post;

Q2.SQL. Clear;

Q2.SQL. Add(‘INSERT INTO CLIENTS (PARENT, CHILD) VALUES (:p, 0)’);

Q2.Parameters. ParamByName(‘p’).DataType := ftInteger;

Q2.Parameters. ParamByName(‘p’).Value := level;

Q2.ExecSQL;

DM. AdoConnection1.CommitTrans;

Except

On e:Exception do

begin

DM. AdoConnection1.RollBack;

ShowMessage(e. Message);

End;

End;

3. Для удаления записи справочника, не последней на текущем уровне, следует просто удалить ее из таблицы

Q2.SQL. Clear;

Q2.SQL. Add(‘DELETE FROM CLIENTS WHERE ID = :t’);

Q2.Parameters. ParamByName(‘t’).DataType := ftInteger;

Q2.Parameters. ParamByName(‘t’).Value := Q. FieldByName(‘ID’).AsInteger;

Q2.ExecSQL;

Q. Close;

Q. Open;

4. Для удаления последней на текущем уровне справочника записи следует выполнить более сложную последовательность действий: во первых удалить запись из таблицы, во вторых записать 0 в поле CHILD родительской записи, в третьих перейти к записям родительского уровня

// Запомнить уровень удаляемой записи

level := Q. FieldByName(‘PARENT’).AsInteger;

// Удалить ее

Q2.SQL. Clear;

Q2.SQL. Add(‘DELETE FROM CLIENTS WHERE ID = :t’);

Q2.Parameters. ParamByName(‘t’).DataType := ftInteger;

Q2.Parameters. ParamByName(‘t’).Value := Q. FieldByName(‘ID’).AsInteger;

Q2.ExecSQL;

// Определить уровень родительской записи

Q2.SQL. Clear;

Q2.SQL. Add(‘SELECT PARENT FROM CLIENTS WHERE CHILD = :t’);

Q2.Parameters. ParamByName(‘t’).DataType := ftInteger;

Q2.Parameters. ParamByName(‘t’).Value := Q. FieldByName(‘ID’).AsInteger;

Q2.Open;

// Запомнить его

newLevel := Q2.FieldByName(‘PARENT’).AsInteger;

// Обнулить ссылку на детей – их больше нет у родительской записи

Q2.SQL. Clear;

Q2.SQL. Add(‘UPDATE CLIENTS SET CHILD = 0 WHERE CHILD = :t’);

Q2.Parameters. ParamByName(‘t’).DataType := ftInteger;

Q2.Parameters. ParamByName(‘t’).Value := level;

Q2.ExecSQL;

// Открыть уровень, который содержит родительскую запись (теперь она уже не родительская!)

Q. Close;

Q. Parameters. ParamByName(‘p’).Value := newLevel;

Q. Open;

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

Возникает вопрос: а как определить, является ли удаляемая запись последней на текущем уровне? Для этого можно, например, узнать количество записей, возвращенных SQL-запросом, вызвав свойство RecordCount соответсвующего объекта класса TADOQuery:

i := Q. RecordCount;

либо выполнить соответсвующий SQL-запрос с помощью еще одного объекта класса TADOQuery (назовем его qKOL). Такой компонент можно создать динамически, воспользоваться им, а затем уничтожить:

qKol := TADOQuery. Create;

qKol. Connection := DM. DBC;

qKol. SQL. Add(‘SELECT COUNT(*) AS KOL_VO FROM CLIENTS WHERE PARENT=:p ‘);

with qKol. Parameters(‘p’) do begin

DataType := ftInteger;

Value := …

End;

qKol. Open;

i := qKOL. FieldByName(‘KOL_VO).AsInteger;

q. Kol. Close;

FreeAndNil(qKol);

@ Задание:

Реализуйте возможность редактирования справочника иерархической структуры в своей программе.

@

Работа №11. Разработка программы для ввода основных фактов о предметной области

Под фактами предметной области будем понимать то, ради чего собственно и создается приложение. Например, для спорткомплекса таким фактом является то, что клиент Иванов, проживающий по и работающий в купил абонемент на 8 посещений тренажерного зала по пятницам, в 17.00, заплатив за него 1000 руб.

@ Задание:

Реализуйте программу для ввода в базу данных фактов, наблюдаемых в Вашей предметной области. Для этого следует использовать возможность задания свойств с помощью таблиц-справочников. Программа должна поддерживать ввод новых фактов, исправление ошибочно введенных и удаление неверных фактов. Интерфейс программы – на Ваш вкус.

@

Работа №12. Разработка системы регламентированной отчетности

Система регламентированной отчетности является основой для анализа и принятия решений в стандартных ситуациях. Поэтому набор отчетов, входящих в регламентированную отчетность, должен быть достаточно широк и полон. Такие отчеты строятся регулярно, часть из отчетов должна включать только сильно агрегированные данные, дающие обобщенное представление о ситуации в предметной области а часть более детальны. Т. к. регламентированная отчетность формируется персоналом, работающим с приложением как пользователи (операторы, администрация, аналитики), то отчеты должны быть хорошо продуманы как с точки зрения внешнего вида, так и с точки зрения содержащейся в них информации.

Средства для построения отчетов в Delphi:

Компоненты QuickReport. Комбинация SQL-запросов с экспортом их результатов в Excel (по аналогии с формированием твердой копии справочников). В Delphi 7 включены новый генератор отчетов RAVE. Имеются несколько внешних генераторов отчетов, наиболее известные (хотя и являющиеся представителями продуктов двух совершенно разных классов) FastReport и Crystal Reports.

@ Задание:

Сформируйте несколько отчетов (2-3), дающих представление о состоянии предметной области, поддерживаемой Вашим приложением. При этом можно воспользоваться любым из доступных средств формирования отчетов. Включить отчеты в главное меню приложения.

@

Работа №13. Перенос базы данных на Microsoft SQL Server 2000

На настоящий момент база данных и соответствующая прикладная информационная система в основном реализованы. Для обеспечения работы в многопользовательской среде и для повышения масштабируемости прикладной системы может потребоваться преобразование формата базы данных из файл-серверной СУБД Access в клиент-серверную СУБД. Целевой СУБД естественно выбрать СУБД Microsoft SQL Server 2000. Такой выбор обусловлен наличием удобных средств миграции в нее и минимальными потребностями в переделке прикладной системы.

@ Задание:

Произведите преобразование базы данных в формат SQL Server. Для этого воспользуйтесь мастером Access. Узнайте у администратора имя сервера, на котором развернут SQL Server, идентификатор и пароль пользователя. Проанализируйте разные режимы преобразования, изучите отчет о процессе преобразования, формируемый мастером.

@

@ Задание:

С помощью утилиты Enterprise Manager, установленной на Вашем компьютере, соединитесь с только что полученной базой данных и проанализируйте ее отличия от базы данных на Access. Обратите внимание на отличия в типах данных (например, во что преобразовался тип данных “счетчик” и др.)

@

@ Задание:

Обеспечьте наличие диаграммы Вашей базы данных в SQL Server (создайте ее). Проанализируйте средства отображения диаграммы и сравните их с аналогичными для схемы данных в Access.

@

@ Задание:

Получите скрипт на языке T-SQL для создания (развертывания) Вашей базы данных “с нуля”.

@

@ Задание:

Выполните настройку UDL-файла, используемого в Вашем приложении для доступа к базе данных Access посредством интерфейса ADO, для доступа к новой базе данных SQL Server.

Подсказка: основные параметры, которые следует изменить – провайдер данных, имя сервера и имя базы данных.

Проверьте функционирование Вашего приложения с новой базой данных. В случае неудовлетворительного функционирования следует разобраться в причинах этого (сначала самостоятельно, если не удастся – с преподавателем).

@

Работа №14. Хранимые процедуры и триггеры

Хранимые процедуры – это объект SQL Server, содержащий набор откомпилированных операторов для выполнения некоторого алгоритма.

& Более подробно хранимые процедуры и триггеры описаны в главе 11 Курса лекций.

?Пример.

Пусть имеется 2 таблицы:

Person(PersonID INTEGER IDENTITY(1,1),

FirstName VARCHAR(50),

SurName VARCHAR(50),

BIRTHDATE DATETIME)

и

Adress(PersonID INTEGER, Adress VARCHER(50))

Тогда для ввода данных за раз в обе таблицы можно воспользоваться загружаемой процедурой:

CREATE PROCEDURE PersonInsert

@FirstName VarChar(50),

@SurName VarChar(50),

@BIRTHDATE DateTime = NULL,

@PersonAdress VARVHAR(50)

AS

DECLARE @PersonID INT

INSERT INTO Person (FirstName, SurName, BIRTHDATE)

VALUES (@FirstName, @SurName, @ BIRTHDATE)

SET @PersonID = IDENT_CURRENT(‘Person’)

INSERT INTO Adress(PersonID, PersonAdress)

VALUES (@PersonID, PersonAdress)

v

Для запуска процедуры на выполнение из интерактивной утилиты (например, Query Analyser) можно написать:

EXEC PersonInsert ‘Коля’, ‘Иванов’, ’15.05.1988’, ‘Новокузнецк пр. Металлургов, 33, 41’

В программе на Delphi для этого следует воспользоваться компонентом TADOStoredProc.

Триггер – это особый вид хранимой процедуры, способный реагировать на события. Создадим триггер для отслеживания и запрета ввода дублей имен людей в таблицу PERSON. Кроме того, этот же триггер будет использоваться для отметки о вводе сведений о новом человеке в таблицу протокола. Данный триггер относится к категории AFTER, т. е. выполняется после осуществления модификации данных. Но до завершения транзакции! Поэтому, единственный способ отменить ошибочные действия (например, ввод дубликатной информации) заключается в откате транзакции. (Вспомним, что существуют и триггеры BEFORE (перед, до выполнения операции). (Имеются также и триггеры INSTEAD OF, выполняющиеся вместо …).

?Пример.

В дополнение к имеющимся таблицам введем еще одну:

LOG(PERSONID INTEGER, DAT DATETIME, UN VARCHAR(50))

Она будет использоваться для протоколирования операций добавления/изменения данных в таблице PERSON. Поле PERSONID содержит идентификатор человека, DAT – время операции ввода/изменеия, UN – имя оператора, выполнившего изменение.

CREATE TRIGGER LOGNEWS

ON PERSON

FOR INSERT, UPDATE

AS

BEGIN TRANSACTION

DECLARE @PERSONID INTEGER

DECLARE @C INTEGER

DECLARE @SURNAME VARCHAR(50)

DECLARE @FIRSTNAME VARCHAR(50)

SELECT @C = COUNT(*)

FROM PERSON, inserted i

WHERE PERSON. SURNAME = i. SURNAME AND PERSON. FIRSTNAME = i. FIRSTNAME

IF @C >1

BEGIN

RAISERROR(‘Вы пытаетесь ввести данные о человеке, уже зафиксированном в базе данных!’, 16, 1)

ROLLBACK TRANSACTION

END

ELSE

BEGIN

SELECT @PERSONID = i. PERSONID FROM inserted i

INSERT INTO LOG(PERSON, DAT, UN)

VALUES (@PERSONID, GETDATE(), USER)

COMMIT TRANSACTION

END

v

@ Задание:

Реализуйте в своей базе данных хранимую процедуру и триггер, имеющие смысл в Вашей конкретной предметной области. Обеспечьте вызов хранимой процедуры в программе на DELPHI. Проверьте результаты работы хранимой процедуры и факт срабатывания триггера.

@

Работа №15. Разработка системы нерегламентированной отчетности (аналитическая система) на основе OLAP. Первый подход

Система нерегламентированной отчетности – это система, позволяющая формировать заранее не предусмотренные, разнообразные отчеты, которые могут помочь при ответе на заранее не предвиденные вопросы. Что особенно важно, такая система должна позволить аналитикам, т. е. специалистам в конкретной прикладной области а не ИТ-персоналу, оперативно получать доступ к данным с целью удовлетворения собственных информационных запросов. Основой системы нерегламентированной отчетности обычно является технология OLAP (online analytical processing), в основе которой, в свою очередь, лежит многомерная модель данных.

& Более подробно принципы OLAP описаны в главе 13 Курса лекций.

В данной работе воспользуемся Microsoft Excel в качестве программы-сервера для организации анализа.

В работе необходимо

1. Сформировать SQL-запрос или представление для анализируемой базы данных.

2. Подключиться к SQL-запросу или представлению через Excel и сформировать сводную таблицу на основе

2.1. результата SQL-запроса или представления

2.2. локального (его еще называют клиентским) куба OLAP для чего предварительно его создать.

3. Произвести анализ путем отображения данных из куба в сводной таблице Excel в разных ракурсах и с разными степенями агрегации.

4. Построить графическую диаграмму по данным сводной таблицы.

Пример:

Пусть на сервере Microsoft SQL Server 2000 имеется база данных SportSQL, структура которой представлена на рис.15.1. База данных содержит сведения о клиентах спортивного комплекса. При этом таблица USERS описывает клиентов, таблица ABONEMENT – абонементы клиентов, таблица TIPS – виды и атрибуты занятий, таблица TIPSP – классы занятий, таблица ABOTIME – посещения занятий, таблица PAYMENTS – платежи клиентов, таблица PURPOSE – цели платежей.

1. Создадим представление для использования в целях анализа платежей клиентов по следующим измерениям: по временным интервалам, по целям платежей, по видам занятий, по классам занятий, по клиентам. Это означает, что в представление должны войти все перечисленные измерения, а также поле SUMMA из таблицы PAYMENTS, которое мы будем рассматривать как меру:

CREATE VIEW PAYS AS

SELECT dbo. Users. Name AS Клиент, dbo. TIPS. NAME AS ЗАнятие,

TIPS1.NAME AS КлассЗанятий, dbo. PAYMENTS. SUMMA AS Сумма,

dbo. PAYMENTS. DAT AS Дата, dbo. PURPOSE. NAME AS Цель

FROM dbo. PAYMENTS

LEFT OUTER JOIN dbo. Users ON dbo. Users. ID = dbo. PAYMENTS. USERID

LEFT OUTER JOIN dbo. PURPOSE ON dbo. PURPOSE. ID = dbo. PAYMENTS. PURPOSEID

LEFT OUTER JOIN dbo. Abonement ON dbo. Abonement. ID = dbo. PAYMENTS. ABONEMID

LEFT OUTER JOIN dbo. TIPS ON dbo. TIPS. ID = dbo. Abonement. TipID

LEFT OUTER JOIN dbo. TIPS TIPS1 ON TIPS1.ID = dbo. TIPS. IDPARENT

& По поводу терминов измерение и мера а также о схеме “звезда” см. Курс лекции, глава 13.

Рисунок 15.1. Схема базы данных.

2. Создать сводную таблицу в Excel.

2.1. Для создания сводной таблицы в Excel, запустить мастер сводных таблиц (Данные/Сводная таблица). В мастере выбрать “Создать таблицу на основе данных, находящихся во внешнем источнике данных”:

Рисунок 15.2. Выбор источника данных

2.2. На втором шаге мастера нажать кнопку “Получить данные” и выбрать “Новый источник данных”. Задать имя нового источника, выбрать подходящий драйвер (SQL Server, например), и, нажав кнопку “Связь”, задать имя компьютера, на котором работает сервер СУБД и имя Вашей базы данных на этом сервере.

Рисунок 15.3. Параметры соединения с базой данных

a.   

2.3.  В открывшемся окне Microsoft Query выбрать представление PAYS, которое в нашем примере служит основой для анализа.

b.   

2.4.  Перенести все поля представления в область таблицы данных. В результате на экране появится результат открытия запроса: