Использование языка макрокоманд в AllFusion ERwin Data Modeler

Козодаев А.А.,
технический специалист компании Interface Ltd.

На современном этапе редко какое предприятие имеет единую информационную структуру. Как правило, информационный отдел организации имеет мозаичную структуру, где каждый элемент мозаики является решением отдельной задачи или подразделения и реализован в соответствии с параметрами этой задачи. Такая мозаика во многом определяется историческим развитием организации. К примеру, на гипотетическом предприятии первоначально был автоматизирован бухгалтерский учет при помощи собственной разработки, далее была внедрена сторонняя программа по учету заработной платы и т.д. Не секрет что такая участь была уготована и системам управления баз данных - от настольных баз MS Access до высокопроизводительных баз данных Oracle. В итоге практически перед любой организацией стоит задача унификации процесса создания баз данных с использованием различных СУБД, документирования и сопровождения уже существующих наработок. Именно таким инструментом является AllFusion ERwin Data Modeler (ранее: ERwin). Данный программный продукт выпускается американской компанией Computer Associates. ERwin DM поддерживает более 20 типов СУБД, как настольных, так и реляционных. Среди функциональных возможностей ERwin DM можно выделить следующие:

• поддержка нескольких методик создания диаграмм сущность-связь, на основе которых впоследствии создаются системные объекты выбранной СУБД, а именно: американский стандарт IDEF1X, методика IE во многом подобная стандарту IDEF1X и специальная методика, поддерживающая создание хранилищ данных.
• прямое генерирование (Forward engineer). Это функция создания системных объектов выбранного сервера базы данных на основе созданной в ERwin DM схемы данных.
• обратное генерирование (Reverse engineer). При помощи данной функции на основе существующей базы данных создается схема данных в ERwin, что позволяет эффективно документировать существующие базы данных.
• полное сравнение (Complete compare). Функция, которая позволяет сравнивать построенные в ERwin DM схемы данных между собой, либо схему данных с существующей базой данных, вследствие чего сокращается время, необходимое на сопровождение СУБД.

Этот список можно продолжить. Однако целью данной статьи является не перечисление всех функциональных возможностей ERwin DM, а рассказ о вспомогательных средствах, включенных в ERwin DM, которые позволяют существенно расширить функциональность данного продукта. К таковым можно причислить:

• встроенный API (ERwin API)
• поддержка XML
• использование правил преобразования типов данных, стандартов именования объектов схемы данных и глоссария
• использования построителя отчетов для извлечения данных по модели
• использование языка макрокоманд.

Далее мы рассмотрим именно язык макрокоманд, сферы его применения, а также приведем ряд практических примеров, демонстрирующих выгоду от использования макрокоманд.

Любая СУБД подразумевает поддержку определенной модели данных, которая определяет структуру данных, и правила, по которым эта структура создается. На данный момент наиболее используемой моделью данных является реляционная модель. Первая практическая реализация реляционной модели была предпринята компанией IBM в семидесятые годы (1973-1978) прошлого столетия в продукте System R. Эта система была основой для всех последовавших за ней коммерческих продуктов, реализующих реляционный подход. В данном продукте был предложен язык манипулирования данными, который носил название SEQUEL (structured English query language), в дальнейшем получивший название SQL. В коммерческих продуктах язык SQL дополнялся, и в итоге появилось большое количество диалектов, не совместимых между собой. В 1987 году американским комитетом стандартизации был предложен первый стандарт языка SQL, в 1992 году был создан стандарт SQL-92, дополняющий SQL-87, в 1999 году предложена его третья версия, главным дополнением которой были объектные возможности и хранимые процедуры. Несмотря на процесс стандартизации, версии языка SQL очень сильно различаются от производителя к производителю. Язык макрокоманд ERwin DM позволяет расширить стандартный синтаксис языка SQL до диалектов конкретных баз данных. Однако это не единственная возможность использования макрокоманд в ERwin DM. Макрокоманды могут быть использованы:

• в доменах
• в настройках правил именования объектов схемы
• для создания триггеров и хранимых отображений
• для создания шаблонов скриптов.

Как гласит реляционная теория, домен является областью определения значений атрибута. Однако, несмотря на то, что некоторые производители СУБД заявляют о полной поддержке реляционного подхода в своих продуктах, на самом деле все положения этого подхода не реализованы ни в одной коммерческой СУБД, да и вряд ли когда будут реализованы полностью. Одним из таких нереализованных моментов является понятие домена - в современных СУБД оно подменено понятием типа данных, что является довольно примитивным решением. В ERwin DM тоже существует понятие домена и его можно представить шаблоном атрибута, обладающим набором свойств, в числе которых тип данных, значение по умолчанию, правило проверки и некоторые другие. В процессе работы рекомендуется использовать именно домены, и только потом создавать атрибуты, принадлежащие конкретному домену. Такой подход не лишен определенной гибкости и позволяет более эффективно редактировать схему данных. Действительно, гораздо удобней создать домен ИД, присвоить ему набор свойств и только потом создавать атрибуты на основе данного домена. В случае изменения, к примеру, длины поля домена ИД, гораздо проще исправить данные этого домена - и все входящие в него атрибуты автоматически будут изменены. Использование макрокоманд в доменах является простейшим примером применения макрокоманд в ERwin DM.

Рис. 1. Использование макрокоманд в определении доменов

На приведенном выше рисунке используется ряд макрокоманд, в результате действия которых имя атрибута будет выглядеть следующим образом: название_сущности домен_атрибута.

%Lower - макрокоманда, понижающая регистр
%OwnerEntity - макрокоманда, возвращающая имя сущности, к которой принадлежит атрибут
%AttDomain - макрокоманда, возвращающая имя домена, к которому принадлежит атрибут

При разработке крупных информационных систем, когда над моделью данных работают несколько специалистов, необходимо соблюдать правила именования объектов схемы данных. Например: названия сущностей должны быть представлены в верхнем регистре, названия атрибутов - в нижнем, таблицы на физическом уровне представления должны иметь префикс tbl и т.д. Конечно, можно создать документ, описывающий правила именования объектов схемы данных и затем административными мерами добиваться выполнения положений этого документа. Однако, в случае, когда количество таблиц в схеме начинает исчисляться сотнями, эта задача становится трудновыполнимой. В ERwin DM имеются специальные механизмы, позволяющие задать правила именования как логического, так и физического уровня схемы данных. В общем случае макрокоманды используются для преобразования имен объектов на логическом уровне в соответствующие имена на физическом уровне. Используя различные комбинации макрокоманд, можно добиться нужного поведения при именовании объектов схемы данных.

Рис. 2. Пример использования макрокоманд в редакторе именования объектов модели.

В приведенном примере используется макрокоманда вида %4EntityName, которая указывает на то, что таблица на физическом уровне представления будет иметь имя соответствующей сущности, ограниченное четырьмя первыми символами.

Выше были рассмотрены наиболее простые примеры использования языка макрокоманд ERwin DM. Перед тем, как рассмотреть более сложные примеры, познакомимся чуть ближе с самим языком макрокоманд.

Язык макрокоманд состоит из 195 команд.

В ERwin DM включена специальная панель инструментов - Macro Toolbox для работы с макрокомандами. С помощью данной панели можно выбрать нужную макрокоманду из списка, просмотреть синтаксис выбранной макрокоманды и получить справку по ее использованию.

Рис. 3. Панель инструментов Marco Toolbox.

Рассмотрим синтаксис одной из макрокоманд ERwin DM.

%ForEachColumn(<table>,<separator>,<sort order>) { <macro code> }

Фраза %ForEachColumn является названием макрокоманды. Все фразы, заключенные в кавычки (<>), являются переменными макрокоманды, скобки и запятые указывают на необходимый синтаксис.

%ForEachColumn(cust,","){%ColName} - в результате действия этой макрокоманды будет выведен список столбцов таблицы cust.

Действия, выполняемые при помощи языка макрокоманд ERwin DM подобны действиям, выполняемым в других языках программирования, и включают в себя:

Тип действия	Макрокоманда
определение переменных	%ChildFKDecl %ChildNKDec l%ChildParamDecl %ChildPKDecl %Decl %NKDecl %ParamDecl %ParentNKDecl %ParentParamDecl %ParentPKDecl %PKDecl
выполнение арифметических операций	%- %* %/ %+
использование операций сравнения и логических операций	%!= %< %<= %= = %> %>= %And %Not %Or
ветвление	%If %Else %Switch
организация циклов	%ForEachAtt %ForEachAttribute %ForEachChildRel %ForEachColumn %ForEachDefault %ForEachDomain %ForEachEntity %ForEachFKAtt %ForEachFKAttribute %ForEachFKColumn %ForEachIndex %ForEachIndexMem %ForEachKey %ForEachKeyMem %ForEachLogEntity %ForEachParentRel %ForEachTable %ForEachValidation %ForEachValidValue %ForEachView %ForEachViewColumn
работа с внешними файлами	%File %Include %Lookup

Перейдем к использованию макрокоманд в триггерах. Как известно, одной из задач, стоящих перед современными СУБД, стоит задача поддержания логической целостности данных. Реляционные базы состоят из таблиц, связанных между собой при помощи механизма ключей. Для поддержания целостности между двумя таблицами используется триггер ссылочной целостности (RI триггер). В общем случае текст триггера зависит от типа связи таблицы, с которой он связан (триггер для родительской таблицы, триггер для дочерней таблицы) и от действия, при котором он срабатывает (добавление, изменение, удаление записи в таблице). В ERwin DM используется набор шаблонов для реализации триггеров ссылочной целостности. Тексты шаблонов зависят от типа сервера базы данных. Шаблоны представляют собой специальные скрипты, в которых используются макрокоманды ERwin DM. При генерации триггеров вместо макрокоманд подставляются имена таблиц, колонок, переменных и других фрагментов кода, соответствующих синтаксису выбранной макрокоманды. Для каждой комбинации ссылочной целостности (к примеру, Parent-Delete Cascade) в ERwin DM существует предопределенный шаблон, однако у пользователя имеется возможность переопределить этот шаблон, причем сделать это можно на трех уровнях: на уровне всей схемы, на уровне отдельной таблицы и на уровне отдельной связи. В дальнейшем на этапе генерации кода DDL будет использоваться шаблон, переопределенный пользователем.

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

Шаблон на данный триггер выглядит следующим образом:

/* ERwin Builtin %Datetime */
/* %Parent %VerbPhrase %Child ON PARENT DELETE RESTRICT */
select count(*) into numrows
from %Child
where
/* %%JoinFKPK(%Child,:%%Old," = "," and") */
%JoinFKPK(%Child,:%Old," = "," and");
if (numrows > 0)
then
raise_application_error(
-20001,
'Cannot DELETE %Parent because %Child exists.'
);
end if;

При генерации будет создан следующий триггер

create trigger tD_Отдел after DELETE on Отдел for each row
-- ERwin Builtin Wed Oct 15 17:06:44 2003
-- DELETE trigger on Отдел
declare numrows INTEGER;
begin
/* ERwin Builtin Wed Oct 15 17:06:44 2003 */
/* Отдел R/2 Сотрудник ON PARENT DELETE RESTRICT */
select count(*) into numrows
from Сотрудник
where
Сотрудник.ид_отд = :old.ид_отд;
if (numrows > 0)
then
raise_application_error(
-20001,
'Cannot DELETE Отдел because Сотрудник exists.'
);
end if;
end;

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

create trigger %TriggerName
%Fire %Actions(" or ")
on %TableName
%RefClause
%Scope
/* ERwin Builtin %Datetime */
/* default body for %TriggerName */
begin
Insert into Security (OldName, NewName, UserUpdate, UpdateDate)
values (:old1.CustomerName, :new1.CustomerName, User, Sysdate);
end;
/

В результате использования этой макрокоманды любое изменение в таблице Customer, будет фиксироваться в таблице Security, а именно прежнее значение имени, новое значение, дата изменения и имя пользователя, производившего изменения. На этапе генерации кода SQL эта макрокоманда будет выглядеть следующим образом.

create trigger SecurWrite
BEFORE UPDATE OF
CustomerName
on CUSTOMER
REFERENCING OLD AS old1 NEW AS new1
for each row
/* ERwin Builtin %Datetime */
/* default body for %TriggerName */
begin
Insert into Security (OldName, NewName, UserUpdate, UpdateDate)
values (:old1.CustomerName, ;new1.CustomerName, User, Sysdate);
end;
/

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

Создание хранимых процедур при помощи языка макрокоманд не отличается от создания триггеров.

Как уже было сказано выше, язык макрокоманд ERwin DM также может быть использован для создания пре- и пост-скриптов. В ERwin DM есть возможность создания шаблонов скриптов, которые могут быть использованы на этапе прямого генерирования. Могут быть скрипты уровня всей схемы и уровня таблицы. По времени генерации скрипты могут делиться на прескрипты и постскрипты. Генерация кода прескрипта происходит перед генерацией основного объекта, с которым этот скрипт связан, и соответственно генерация кода постскрипта осуществляется после генерации объекта. Проиллюстрируем использование макрокоманд на примере создания прескрипта уровня всей схемы и постскрипта уровня таблицы.

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

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

%ForEachTable() {
If Exists (select * from sysobjects where name = '%TableName') Drop table %TableName
%DMBSDelim
}

примечание - скрипт написан для СУБД MS SQL Server 2000

Этот скрипт легко можно преобразовать до скрипта уровня таблицы, удалив макрокоманду %ForEachTable(), в этом случае скрипт будет выглядеть следующим образом

If Exists (select * from sysobjects where name = '%TableName') Drop table %TableName
%DMBSDelim

Следующий скрипт используется для предоставления привилегий на выборку из таблицы определенному пользователю.

grant select on %TableName to %TableProp(GrantSelect)
%DBMSDelim

В ERwin DM существует механизм свойств, определенных пользователем (UDP - user defined property), которые позволяют существенно расширить функциональность продукта. В приведенном выше примере используется UDP под названием GrantSelect, в котором перечислены все пользователи базы данных. На этапе моделирования каждой таблице присваивается данный UDP и в нем указываются пользователи, которые будут иметь привилегию на выборку из таблицы. На этапе генерации кода SQL выбранным пользователям будет предоставлена привилегия на выбор из соответствующих таблиц.

AllFusion ERwin Data Modeler является мощным средством, позволяющим создавать, документировать и сопровождать базы данных. Язык макрокоманд, включенный в состав данного продукта, позволяет генерировать SQL-код, приближенный к коду диалектов SQL конкретных производителей СУБД.