САНКТ – ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
Математико-механический факультет
Кафедра системного программирования
Генерация скрипта создания базы данных с учетом зависимостей
Дипломная работа студента 545 группы
Масунова Максима Владимировича
Научный руководитель,
Доцент кафедры системного ……………… Графеева Н.Г.
программирования, / подпись /
кандидат физ.-мат. наук
Рецензент
Старший преподаватель ……………… Помыткина Т.Б.
кафедры системного / подпись /
программирования
“Допустить к защите”
Заведующий кафедрой, ……………… Терехов А.Н.
д.ф.-м.н., профессор / подпись /
Санкт-Петербург
2008 г.
Оглавление
Оглавление 2
2
Введение 3
Теоретическая часть 9
Практическая реализация 12
Практическое внедрение 18
Заключение 18
Введение
Если требуется масштабируемая, высоконадежная СУБД с поддержкой Интернета, то можно с удивлением обнаружить, что выбор таких систем весьма ограничен. К ним можно отнести сегодня разве что DB2 UDB корпорации IBM и Oracle. Оба продукта обладают развитой функциональностью, необходимой для работы в режиме 24х7х365, т.е. круглосуточно и без выходных на протяжении всего года.
Корпорация IBM добавила много новых функций в свою СУБД. Среди них - файловая система DataLink, соперничающая с iFS, которая позволяет управлять данными и внешним содержимым, включая изображения, и при этом в полной мере сохраняет безопасность, производительность и надежность базы данных. А такие элементы DB2, как визуальные средства создания сохраненных процедур Java, административный Java-инструментарий, синтаксический анализатор XML и встроенные средства поиска позволяют IBM более успешно конкурировать с Oracle на поле электронного бизнеса. Еще с седьмой версии корпорация поставляет две инструментальные программы - утилиту объединения данных Data Joiner и систему анализа информации Intelligent Miner. Кроме того, система Visual Warehouse здесь тесно интегрирована с базой данных и, поддерживая множество файловых расширений, открывает простой доступ к другим источникам информации, например, базам данных Oracle и Sybase.
DB2 выпускается для использования на мэйнфреймах, в средах Unix и Windows NT, а также для установки на карманных устройствах. Кроме того, IBM предлагает свой продукт в вариантах для AIX, OS/400, NUMA-Q и SCO UnixWare. Таким образом, новые возможности СУБД способны оказать существенную помощь в совершенствовании бизнес-процессов.
Перед автором данной дипломной работы стояли две задачи. Первая задача состояла в разработке инструмента, который создавал бы скрипты создания всех объектов в базе данных в порядке, учитывающем зависимости между ними. Второй задачей было создавать скрипты-патчи, способные приводить старую схему базы данных к новой схеме.
Целью моей работы было облегчение работы с базой данных программистам в распределенных командах: структура базы данных постоянно меняется, особенно в самом начале работы над проектом, поэтому программистам, особенно в распределенных командах, бывает трудно отслеживать изменения. Изменения в схему необходимо вносить очень аккуратно, чтобы не потерять данные или свести потери существующих данных к минимуму.
Далее будут рассмотрены встроенные механизмы генерации DDL , средства генерации от сторонних разработчиков, их сильные и слабые стороны. Также будет предложен алгоритм генерации скрипта создания всех объектов базы данных с учетом зависимостей, его модификация применимо к созданию скриптов-патчей и их реализация под названием «DDL Smart Script Generator» или «Умный генератор DDL скриптов».
Постановка задачи
В репозитории обычно есть папка, где хранят скрипты создания всех объектов базы данных. Скрипты либо разложены по типам создаваемых объектов, либо находятся в одном текстовом файле. В первом случае никакие зависимости между объектами не учитываются. Во втором случае, как правило, тоже. Конечно, можно самому искать зависимости и упорядочивать объекты так, чтобы можно было просто скопировать весь скрипт, вставить в соответствующий редактор и база данных создастся с первого раза без ошибок, но лучше использовать автоматические средства. В связи с чем, очень часто возникает задача генерации скрипта создания базы данных с учетом зависимостей.
Задача 1: построение скрипта создания базы данных, работающей под управлением СУБД DB2, с учетом зависимостей.
Предположим, что первую задачу мы решили. Тогда возникает вторая, еще более интересная задача: если в первом случае у нас всегда лежит в репозитории полный скрипт создания базы данных, то во втором случае нам хочется еще хранить патч – текстовый файл, содержащий скрипты создания объектов новых, по сравнению с предыдущей ревизией, в порядке, учитывающем зависимости между ними.
Задача 2: создание скрипта-патча, способного привести старую схему данных к новой на основании xml-файла, содержащего новую схему данных и подключении к базе данных со старой схемой.
Проблема импорта схемы базы данных не является новой. Существует множество работ, посвященных этой теме. Из недавних работ стоит отметить диплом выпускника нашей кафедры системного программирования 2007 года Антона Комиссарова, который занимался импортом данных в REAL-IT ([1]). Однако, при внедрении того или иного механизма в конкретный проект, могут возникнуть различные сложности, связанные со спецификой проекта, будь то архитектура системы или используемые технологии.
У DB2 есть встроенная утилита генерации DDL – DB2LOOK.EXE, расположенная в папке [Каталог установки]\IBM\SQLLIB\BIN\. Она может генерировать скрипты создания следующих объектов:
Tables
Views
Automatic summary tables (AST)
Aliases
Indexes
Triggers
Sequences
User-defined distinct types
Primary key, referential integrity, and check constraints
User-defined structured types
User-defined functions
User-defined methods
User-defined transforms
Wrappers
Servers
User mappings
Nicknames
Type mappings
Function templates
Function mappings
Index specifications
Stored procedures
У этой утилиты довольно много параметров, полный список которых можно посмотреть на сайте http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0002051.htm.
Наиболее часто употребимой является команда
db2look.exe -d НАЗВАНИЕ_БАЗЫ_ДАННЫХ -e -o ФАЙЛ_КУДА_БУДЕТ_СОЗДАН_СКРИПТ –i ИДЕНТИФИКАТОР_ПОЛЬЗОВАТЕЛЯ –w ПАРОЛЬ –z СХЕМА
Значения параметров:
-d НАЗВАНИЕ_БАЗЫ_ДАННЫХ
-e - создать DDL выражения для всех объектов
-o ФАЙЛ_КУДА_БУДЕТ_СОЗДАН_СКРИПТ
–i ИДЕНТИФИКАТОР_ПОЛЬЗОВАТЕЛЯ
–w ПАРОЛЬ
-z СХЕМА
Но DB2LOOK не учитывает зависимости между таблицами, встроенными процедурами и представлениями, встроенными процедурами и пользовательскими методами…
На рынке существует большое количество генераторов сторонних разработчиков:
AQT компании Cardett Associates Ltd.
Веб-сайт: www.querytool.com
TOAD for DB2
Веб-сайт: www.quest.com/toad-for-db2/
Aqua Data Studio
Веб-сайт: www.aquafold.com
EMS SQL Manager 2007
Веб-сайт: www.sqlmanager.net/en/products/db2/manager
…
Рассмотрим поподробнее их возможности по генерации DDL выражений, учету зависимостей, созданию патчей и лицензию:
|
Aqua Data Studio*
|
EMS SQL Manager 2007
|
Advanced Query Tool*
|
Toad for DB2 freeware
|
UDB Workbench
|
DDL Smart Script Generator
|
-Tables
|
+
|
+
|
+
|
-
|
+
|
+
|
-Views
|
+
|
+
|
+
|
-
|
+
|
+
|
-Aliases
|
+
|
+
|
-
|
-
|
+
|
+
|
-Sequences
|
+
|
+
|
-
|
-
|
+
|
+
|
-Indexes
|
+
|
+
|
-
|
-
|
+
|
+
|
-Triggers
|
+
|
+
|
-
|
-
|
+
|
+
|
-Procedures
|
+
|
+
|
-
|
-
|
+
|
+
|
-Functions
|
+
|
+
|
-
|
-
|
+
|
+
|
-User Datatypes
|
+
|
+
|
-
|
-
|
+
|
+
|
Все зависи-мостти
|
-
|
-
|
-
|
-
|
-
|
+
|
Патчи
|
-
|
-
|
-
|
-
|
+
|
+
|
Freeware
|
-
|
-
|
-
|
+
|
-
|
+
|
* - умеет находить зависимости между таблицами
______________________________
Как видно из приведенной таблицы, мое приложение «DDL Smart Script Generator» позволяет учесть не только зависимости между таблицами, но также зависимости между встроенными процедурами, представлениями, триггерами и пользовательскими методами. Большим плюсом является то, что приложение абсолютно бесплатно и невелико по сравнению с тяжеловесными конкурентами.
Возникает вопрос: «Почему бы не использовать встроенные механизмы систем контроля версий для сравнения скриптов?»
Здесь есть несколько причин, по которым этот подход является не очень удобным:
Сравнивают строки, а не объекты
Не создают скрипта, а лишь показывают различия
Может не понять, если поменять местами два текстовых блока без обрамляющих “\n”.
Тогда почему бы не приложить резервную копию новой базы прямо в репозиторий?
Для примера я создал SAMPLE базу данных. Это стандартная база данных DB2, которая может быть создана вызовом команды db2sampl.exe. В этой базе находится 22 таблицы, каждая из которых содержит по 20-40 записей. Так вот, резервная копия для такой базы занял у меня на диске 102 мегабайта. Даже представить сложно, сколько места потребуется для создания резервной копии для баз данных, у которых несколько тысяч записей.
Теоретическая часть
Создание объектов с учетом зависимостей
Алгоритм
В программе используется следующий порядок генерации объектов:
На выходе создается текстовый файл со скриптом создания базы данных, в котором в первую очередь идут независимые объекты, затем объекты, зависимые от первых и т.д.
Методы
Сверяя различные скрипты, генерируемые сторонними производителями и встроенными средствами, автор пришел к выводу, что наиболее полные и точные скрипты создает встроенная утилита db2look.exe. Поэтому первые семь пунктов берутся из скрипта, сгенерированного db2look. Тела пользовательских функций, встроенных процедур, представлений и триггеров хранятся как запись в системных таблицах FUNCTIONS, PROCEDURES, VIEWS, TRIGGERS схемы SYSCAT. Так как разбор файла содержащего встроенные процедуры, функции, триггеры и представления превращается в нетривиальную задачу, то решено было эти объекты брать прямо из базы данных.
Очень легко решить задачу связей между таблицами: сначала создать все таблицы, а затем добавить в них табличные ограничения целостности, ограничения целостности CHECK и ссылочные ограничения целостности.
Поиск зависимостей между Views, User-defined functions и Stored procedures осуществлялся путем поиска вызова соответствующего объекта в теле функции, процедуры или представления.
Триггеры создаются последними.
Оценка сходимости
Пусть в базе данных всего имеется N объектов, из которых функций, встроенных процедур и представлений ровно M штук. Тогда часть алгоритма без топологической сортировки будет работать за время O(8N) = O(N). Проверка на ацикличность занимает время O(2M) = O(M),топологическая сортировка занимает время O(2M) = O(M). Построение графа зависимостей занимает O(N2). Итого время работы составляет O(N2).
Создание скрипта-патча
Алгоритм
В общих чертах алгоритм выглядит следующим образом:
Для всех измененных таблиц вызвать ALTOBJ, что приведет к изменению и связанных с данными таблицами объектов.
Добавить новые объекты в порядке, учитывающем зависимости.
Изменить существующие объекты, не являющиеся таблицами.
Удалить старые.
Каждый раз перед добавлением или удалением объектов отслеживаются зависимости, и в случае их обнаружения происходит каскадное удаление и добавление объектов.
Методы
При создании скрипта-патча для таблиц используется системная процедура ALTOBJ. Синтаксис этой процедуры следующий
>>-ALTOBJ--(--exec-mode--,--sql-stmt--,--alter-id--,--msg--)---><
Где sql-stmt - это выражение «CREATE TABLE», которое будет использовано для изменения параметров существующий таблицы с соответствующим названием. Подробнее об этой процедуре и полном списке параметров можно почитать по адресу http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0011934.htm
Для всех остальных измененных объектов используется DROP старого и CREATE соответствующего нового объекта. При этом очень важно, чтобы сохранялась целостность данных. Существуют независимые и зависимые таблицы. Независимые таблицы не имеют ссылок типа «Внешний ключ», в отличие от зависимых. Зависимость может иметь каскадный характер, важен порядок обхода таблиц. Сначала должны быть обработаны независимые таблицы, а затем зависимые. Нужно также учитывать, что могут быть циклы по внешним ключам. Такие таблицы в общем случае не могут быть синхронизированы, т.к. невозможно указать правильный порядок их обхода.
Решение данной проблемы сводится к автоматическому анализу схемы данных БД. Метод ALTOBJ умеет встроенными методами бороться с проблемами поиска и изменения зависимых объектов при изменении схемы существующей таблицы.
Оценка сходимости
Алгоритм также работает за квадратичное время.
Практическая реализация
Общее
Программа написана в среде Visual Studio 2005 на языке C# и использует .NET 2.0. Для подключения к базе данных используется протокол ODBC.
Приложение состоит из двух частей:
DLL – содержит бизнес логику
DDL Smart Script Generator – содержит программу с удобным пользовательским интерфейсом и использует методы из DLL
Системный каталог DB2
Каждая СУБД сохраняет метаданные (данные о данных) - детальную информацию обо всех объектах системы. Примерами таких объектов могут служить таблицы, представления, ограничения целостности, триггеры, правила безопасности и т.д. В DB2 метаданные называются системным каталогом. Однако общим свойством всех современных реляционных СУБД является то, что каталог сам состоит из таблиц. В результате пользователь может обращаться к метаданным так же, как и к своим данным - используя оператор SQL SELECT. Изменения же в каталоге производятся автоматически при выполнении пользователем операторов SQL, изменяющих состояние объектов базы данных.
Точнее, пользователь "видит" не сами таблицы каталога/словаря, а созданные на их базе представления, которые он, конечно же, не может изменять.
В DB2 представления системного каталога определены в схеме SYSCAT и привилегия выборки из них предоставлена группе PUBLIC.
В следующей таблице представлены некоторые представления метаданных и их наиболее значимые столбцы.
Название представления
|
Назначение представления
|
Название столбца
|
Назначение столбца
|
TABLES
|
Таблицы
|
TABNAME
|
имя таблицы
|
DEFINER
|
владелец таблицы
|
CARD
|
количество строк в таблице
|
VIEWS
|
Представления
|
VIEWNAME
|
имя таблицы
|
DEFINER
|
владелец
|
TEXT
|
текст запроса из оператора CREATE VIEW
|
VIEWCHECK
|
признак WITH CHECK OPTION
|
COLUMNS
|
Столбцы всех таблиц и представлений
|
TABNAME
|
имя таблицы
|
COLNAME
|
имя столбца
|
TYPENAME
|
тип данных
|
LENGTH
|
длина данных
|
используется LENGTH
|
точность
|
SCALE
|
число знаков после точки
|
DEFAULT
|
значение по умолчанию
|
NULLS
|
признак допустимости пустого значения
|
TABCONST
|
Табличные ограничения целостности
|
CONSTNAME
|
имя ограничения
|
TABNAME
|
имя таблицы
|
DEFINER
|
владелец
|
TYPE
|
тип ограничения
|
CHECKS
|
Ограничения целостности CHECK
|
TABNAME
|
имя таблицы
|
DEFINER
|
владелец
|
TEXT
|
текст условия
|
REFERENCES
|
Ссылочные ограничения целостности
|
TABNAME
|
имя таблицы
|
DEFINER
|
владелец
|
REFTABNAME
|
имя таблицы, на которую выполнена ссылка
|
REFKEYNAME
|
имя ключа таблицы, на который выполнена ссылка
|
DELETERULE
|
правила для удаления
|
UPDATERULE
|
правила для изменения
|
TRIGGERS
|
Триггеры
|
TRIGNAME
|
имя триггера
|
DEFINER
|
владелец
|
TABNAME
|
имя таблицы
|
TRIGTIME
|
время активизации
|
GRANULARITY
|
область действия
|
TRIGEVENT
|
условие активизации
|
TEXT
|
текст оператора CREATE TRIGGER
|
PROCEDURES
|
Встроенные процедуры
|
PROCNAME
|
имя процедуры
|
DEFINER
|
владелец
|
TEXT
|
текст оператора CREATE PROCEDURE
|
FUNCTIONS
|
Пользовательские функции
|
FUNCNAME
|
имя функции
|
DEFINER
|
владелец
|
BODY
|
текст оператора CREATE FUNCTION
|
Архитектура
В программе «DDL Smart Script Generator» все объекты базы данных имеют тип, который задается в перечислении DBObjectTypes.
public enum DBObjectTypes
{
ALIAS,
INDEX,
SEQUENCE,
CONSTRAINT,
TABLE,
TYPE,
FUNCTION,
METHOD,
TRIGGER,
VIEW,
PROCEDURE
}
Каждый CREATE-statement представлен в программе как отдельный Unit, который имеет название, являющееся названием соответствующего объекта, тело - CREATE-оператор и тип.
public class Unit
{
public string Title { … }
public string Body { … }
public DBObjectTypes Type{ … }
}
Выборка CREATE-statements для последующей обработки состоит из двух частей.
Первая часть – выборка CREATE-statements из файла, сгенерированного DB2LOOK. Для этой цели создан парсер, имеющий стандартный интерфейс, который можно расширять для добавления объектов, поддерживаемых другими СУБД.
public interface IParser
{
Unit[] GetTables();
Unit[] GetTablesConstraints();
Unit[] GetAutomaticSummaryTables();
Unit[] GetViews();
Unit[] GetIndexes();
Unit[] GetUniqueIndexes();
Unit[] GetSequences();
Unit[] GetAliases();
Unit[] GetUserDefinedStructuredTypes();
Unit[] GetUserDefinedDistinctTypes();
}
Вторая часть – выборка CREATE-statements непосредственно из базы данных. На этом этапе выбираются объекты, чей CREATE-оператор хранится как отдельная запись в некотором поле системной таблицы.
public interface IDB
{
Unit[] GetStoredProcedures();
Unit[] GetUserDefinedFunctions();
Unit[] GetTriggers();
Unit[] GetViews();
}
Этот интерфейс также можно расширять для новых объектов. Тела процедур и методов хранятся в колонке с типом BLOB. Чтобы это значение трактовалось как строка, необходимо в строке соединения установить переменную LONGDATACOMPAT = 1.
Зависимости между встроенными процедурами, триггерами, пользовательскими функциями и представлениями отображаются в виде ориентированного графа.
public interface Graph
{
void BuildGraph(Unit[] units);
void AddChild(GraphNode Parent, GraphNode Child);
bool IsAcyclic();
GraphNode[] GetRoots();
string TopologicalSort();
}
Замечание: у графа может быть несколько корней.
GraphNode содержит в себе Unit, порядковый номер и индикатор, показывающий, была ли эта вершина проверена при топологической сортировке.
public class GraphNode
{
public Unit unit;
public int NodeNumber;
public string Mark = "unvisited";
}
Построение графа зависимостей происходит следующим образом:
for (int i = 0; i < nodes.Length; i++)
{
GraphNode parent = (GraphNode)nodes[i];
for (int j = 0; j < nodes.Length; j++)
{
GraphNode child = (GraphNode)nodes[j];
if (parent.unit.Type!= DBObjectTypes.TableConstraint
&& ContainsCall(child.unit.Body, parent.unit.Title)
&& parent.UnitsAreNotEqual(child))
{
AddChild(parent, child);
}
}
}
Сначала проверяется, что искомый объект не является каким-либо табличным ограничением, так как у ограничений нет названия. Далее ищется вызов нужного объекта в теле другого. И смотрится, чтобы два объекта не были одним и тем же объектом. Если все эти условия выполняются, то в граф добавляется ребро.
После построения скрипт, содержащий все объекты базы данных, можно сохранить в XML файле. Этот файл состоит из набора объектов типа Unit, которые имеют формат
…
|
