Delphi World - Использование пятого постулата Дейта

Delphi World - это проект, являющийся сборником статей и малодокументированных возможностей по программированию в среде Delphi. Здесь вы найдёте работы по следующим категориям: delphi, delfi, borland, bds, дельфи, делфи, дэльфи, дэлфи, programming, example, программирование, исходные коды, code, исходники, source, sources, сорцы, сорсы, soft, programs, программы, and, how, delphiworld, базы данных, графика, игры, интернет, сети, компоненты, классы, мультимедиа, ос, железо, программа, интерфейс, рабочий стол, синтаксис, технологии, файловая система...

Использование пятого постулата Дейта

Оформил: DeeCo

Словарь данных
СУБД должна обеспечивать функции словаря данных.
Сам словарь данных можно по праву считать базой данных
(но не пользовательской, а системной). Словарь содержит "данные о данных"
иногда называемые метаданными, т.е. определения других обьектов системы...

Введение в системы баз данных. К.Дж. Дейт

В этой статье я попробую показать один из возможных способов использования метаданных СУБД в одном конкретном случае.

Входные данные: Существует БД, функционирующая на 2-х серверах (Interbase 6.0, Oracle 8), естественно существуют различия в физической реализации, но состав таблиц и полей идентичны. Следует заметить, что при генерации БД для Interbase для описания полей использовались домены (пользовательские типы данных), в то время как для Oracle, не поддерживающего домены, при генерации полей тип данных указывался явно. Ниже представлены два скрипта для одного и того же обьекта БД.

InterBase

Oracle

create table KLSTYPE(
  ID_KLSTYPE D_PK_DM not null,
  IDDEP_KLSTYPE D_PK_DM,
  NAME_KLSTYPE D_NAME_FULL_DM not null,
  SHORTNAME_KLSTYPE D_NAME_SHORT_DM,
  TYPE_KLSTYPE D_SMALLINT_DM,
  constraint PK_KLSTYPE primary key(ID_KLSTYPE)
  );

create table KLSTYPE(
  ID_KLSTYPE VARCHAR(10) not null,
  IDDEP_KLSTYPE VARCHAR(10)null,
  NAME_KLSTYPE VARCHAR2(100) not null,
  SHORTNAME_KLSTYPE VARCHAR2(45)null,
  TYPE_KLSTYPE number(5, 0)null,
  constraint PK_KLSTYPE primary key(ID_KLSTYPE)
  )

Использование доменов для Interbase оказалось достаточно удобным :). Предположим у нас есть домен описывающий Статус обьекта предметной области определенный на интервале -2 до 15. Ниже приведено описание такого домена.

create domain D_STATUS_DM as SMALLINT default 0 check(value between - 2 and 15);

Предположим нам необходимо расширить интервал статусов или вообще снять ограничение на статусы. В случае Interbase команда DDL выглядит достаточно просто:

ALTER DOMAIN D_STATUS_DM DROP CONSTRAINT;

Для СУБД ORACLE не все так просто, в общем случае нам необходимо просмотреть все таблицы и найти все поля хранящие статусы и сформировать, а затем выполнить соответствующую команду ALTER TABLE ..... Существует альтернативный вариант - использование CASE средств, но к моему великому сожалению, используемый нами Sybase Power Designer 6.0 не умеет требуемого, в последующих версиях 7,8 появилась возможность сравнения физических моделей, но прослеживается нехорошая тенденция вместо генерации скрипта ALTER выполнять полное убиение таблицы и создания новой. Т.е. использование CASE средств не спасает в подобном случае, хотя дает возможность определить обьекты использующие определенные домены.

Теперь хорошие новости: У нас есть информация о том какие таблицы и какие поля используют данный домен в БД Interbase. Нам просто остается найти ее и использовать. Ниже приведен скрипт извлекающий из "словаря данных" Interbase необходимую информацию:

SELECT
RDB$FIELD_NAME,
RDB$RELATION_NAME
FROM RDB$RELATION_FIELDS
WHERE(RDB$FIELD_SOURCE = 'D_STATUS_DM')

В результате выполнения запроса мы получили список таблиц и полей использующих наш домен D_STATUS_DM. И что дальше? Опять ручная работа? К счастью, нет. (здесь надо сделать следующее замечание: поскольку генерация БД велась с использованием CASE средства, то имена констраинтов для оракла могут быть сформированы из имени таблицы и поля :). т.е. к примеру для таблицы OBJ содержащей поле STATUS_OBJ был сформирован констраинт с именем CKC_STATUS_OBJ_OBJ.). Формируем запрос для удаления старых ограничений на поля использующие наш домен:

SELECT
'ALTER TABLE ' || trim(RDB$RELATION_NAME)||
' DROP constraint ' || 'CKC_' || trim(RDB$FIELD_NAME)|| '_' ||
  trim(RDB$RELATION_NAME)|| ' ;'
FROM RDB$RELATION_FIELDS
WHERE(RDB$FIELD_SOURCE = 'D_STATUS_DM')

Итак результатом нашего запроса является скрипт практически готовый для исполнения. Что же с ним делать? Если вы используете для работы isql.exe вы можете создать файл, к примеру fordropckc.sql, со следующим текстом:

set names win1251;

CONNECT "myhost: d: \sqlbase\TERRA_new.GDB" user "sysdba" password "masterkey";

SELECT
cast('ALTER TABLE ' || trim(RDB$RELATION_NAME)||
  ' DROP constraint ' || 'CKC_' || trim(RDB$FIELD_NAME)|| '_' ||
    trim(RDB$RELATION_NAME)|| ' ;' as varchar(100))
FROM RDB$RELATION_FIELDS WHERE(RDB$FIELD_SOURCE = 'D_STATUS_DM');

после выполнения данного файла с помощью командной строки

"c:\Program Files\Borland\InterBase\bin\isql.exe" -e -s 1 -input fordropckc.sql -output Result.txt -m

Мы получим текстовый файл Result.txt из которого можно сравнительно легко (любым текстовым редактором) можно получить необходимый нам скрипт:

ALTER TABLE ADMUSERS DROP constraint CKC_STATUS_ADMUSERS_ADMUSERS;
ALTER TABLE OBJ DROP constraint CKC_STATUS_OBJ_OBJ;
ALTER TABLE MTDENT DROP constraint CKC_NEWSTAT_MTDENT_MTDENT;
........

Если Вы используете какое-нибудь средство администрирования, к примеру IBExpert, то вы можете просто сохранить результат запроса в файл или скопировать его в буфер обмена. Полученный скрипт готов к выполнению.

Аналогичным образом можно сформировать скрипт для создания новый ограничений.

Естественно, данный пример ни в коем случае не может претендовать на полное освещение темы "словарь данных в СУБД", но приведеный пример можно рассматривать как "привычность мысли надо гнать, столовый нож оружьем может стать" :)

Благодарности:

Sybase за CASE средство PowerDesigner 6
Borland за бесплатный IB 6.0 и политику, преведшую к появлению платного IB 6.5, и бесплатного FireBird.
Alexander Khvastunov за отличнейшее средство администрирования IBExpert.

Замечания и помидоры принимаются по адресу.