Pull to refresh

Версионная миграция структуры базы данных: еще один подход

SQL *
Sandbox
Прочитал интересную и полезную статью (1) — и захотел поделиться собственным опытом. В нашей фирме за 12 лет работы с одной (своей, Oracle-ориентированной) программой у сотен клиентов накоплен богатейший материал на тему апгрейда структуры БД.

Первоначально мы предполагали, что достаточно хранить в каждой БД номер версии последнего апгрейда и накатывать скрипты инкрементально, поднимая версию до нужной. Такая методика успешно использовалась в предыдущей версии нашей программы, работавшей с СУБД Paradox. Но с СУБД Oracle все пошло не так, у каждого клиента было собственное видение, какой должна быть его БД, и рассинхронизация версий стала неизбежным злом. Привычная методика апгрейдов по версиям стала постоянно приводить к ошибкам, на которые уже никто и не обращал внимание, и рассогласование структур продолжалось несколько лет. В итоге у каждого клиента оказалась собственная, не идентичная никакой другой, структура БД, а клиентская часть программы как-то должна была с этим бороться.

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



В основе новой технологии лежат три алгоритма:

— Получение XML-образа реальной БД
— Сравнение двух XML-образов и выявление различий
— Формирование скрипта на устранение различий

Пользовательский интерфейс

Визуально инструмент выглядит как окно, разделенное по горизонтали на три области:
— в левой панели — древообразная структура загруженных объектов (для краткости — «дерево»);
— в средней и правой панели — детальное описание выделенного в дереве объекта в сравниваемых структурах (эти панели я называю «панелями сравнения»).

В программу загружаются одна или две структуры — из реальной БД или из XML-файла. В дереве объектов отображается объединенная иерархия, то есть объекты, присутствующие хотя бы в одной из двух загруженных структур. Одинаковые, отличающиеся или отсутствующие с одной из сторон объекты изображаются различными значками — соответственно белыми, синими или красно-белыми (красный с той стороны, где объект отсутствует).

Над деревом по нажатию кнопки появляется или исчезает панель фильтров.

В верхней части каждой из панелей сравнения отображается источник данных — схема Oracle или файл.

Над панелями сравнения по нажатию кнопки появляется или исчезает панель опций.

Интерфейс пользователя мог бы быть и другим, это не принципиально — и, возможно, мы его переделаем в будущем — но пока это выглядит примерно так, как я описал.

Получение XML-образа реальной БД

Подключаясь к реальной БД, программа выполняет ряд SQL-запросов, получая информацию о метаданных Oracle, и полученная информация собирается в памяти программы в иерархическую объектную структуру, где каждому объекту метаданных ставится в соответствие свой узел иерархии.

При этом пользователь может наложить фильтр на типы и имена загружаемых объектов метаданных.

В общем случае загрузке подлежат структуры нескольких схем. Пользователь выбирает одну из трех опций:
— Абстрактная схема
— Конкретная схема
— Все схемы сервера

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

В случае выбора третьей опции загружаются структуры всех схем сервера, кроме стандартных (создаваемых при инсталляции Oracle и создании экземпляра БД)

«Абстрактная схема» означает, что имя основной схемы Oracle не имеет значения, так можно сравнивать между собой сходные по структуре схемы с разными именами. Практически это означает, что имя узла основной схемы получит не имя загруженной схемы, а абстрактное имя user — схему с таким именем создать нельзя (а если даже у кого-то и получится, то в этом мало смысла).

«Конкретная схема» означает, что имя основной схемы имеет значение, и ее структура может сравниваться только со структурой одноименной с ней схемы.

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

Полученная объектная структура может быть сохранена в файле формата XML, который можно сразу запаковать в формате ZIP.

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

Каждый новый релиз нашей программы поставляется с таким вот образом стандартной БД в запакованном файле, чтобы на сервере клиента можно было произвести анализ различий, сравнивая реальную структуру с образом из прилагаемого файла.

Сравнение двух XML-образов и выявление различий

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

На верхнем уровне иерархии находятся узлы схем — вначале идут дополнительные схемы, отсортированные по имени, затем основная схема (напоминаю, этот узел называется или именем схемы, или словом user).

Дочерние узлы схем — группы объектов одного типа (таблицы, представления, секвенсоры, хранимые процедуры и т.д.) Имена этих узлов соответствуют их назначению: tables, views и т.д.

На третьем уровне располагаются сами объекты, имена которых должны быть уникальны в рамках своей группы.

Четвертый и далее уровни определяются типом объекта, и в дереве не отображаются. Эти уровни соответствуют объектам или их группам, хранимым и обрабатываемым в памяти программы и отображаемым на панелях сравнения при выборе соответствующего объекта третьего уровня.

Например, у таблицы есть дочерний узел columns со своими дочерними узлами, описывающими каждое поле таблицы, есть узлы triggers, constraints и т.д.

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

Для объектов PL/SQL (триггеров, процедур и т.д.) есть возможность запустить внешнюю утилиту для визуального сравнения текстовых данных типа Beyond Compare, WinMerge или любой другой (предполагается, что эта утилита принимает имена сравниваемых файлов как первые два параметра командной строки).

Но решение об идентичности этих объектов принимается на основании собственного алгоритма сравнения, который игнорирует различия в комментариях, разделителях (пробелы, табуляторы, переводы строки) или регистре символов (за исключением строковых констант).

Формирование скрипта на устранение различий

Скрипт может быть сформирован как слева направо, так и справа налево — обе панели сравнения равноправны для всех алгоритмов.

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

При формировании скрипта применяются такие же фильтры, как и при загрузке структуры (по типам и по именам). Например, можно сравнить только триггеры и процедуры, т.д. — даже если загрузка происходила без фильтра, так как его можно поменять уже после загрузки структур.

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

Внутри одной схемы скрипт строится по типам объектов — вначале обрабатываются синонимы, затем заголовки типов, таблицы, внешние ключи, секвенсоры, функции, процедуры, заголовки пакетов, представления, тела типов, тела пакетов, триггера и контексты.

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

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

Подключение к серверу БД

Для анализа схемы и для применения скрипта возможны два варианта подключения — от имени основной схемы и от имени суперпользователя (sys) Причем второе предпочтительнее, но при отсутствии доступа (пароль sys неизвестен) возможна работа и от имени основной схемы, но некоторая часть метаданных будет в этом случае недоступна. Программа сама пробует оба варианта подключения, вначале пытаясь подключиться от имени sys.

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

Заключение

Описанная технология, возможно, несовершенна — и я буду рад прочитать мнение коллег, даже если буду с ними не согласен.

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

Инструмент постоянно совершенствуется и обрастает новыми возможностями — теперь он умеет делать экспорт и импорт данных с применением фильтра на данные и метаданные, делать полное резервное копирование программы (клиентской части, базы данных и файлов настроек), загружать и скачивать файлы из Oracle Directory, к которой нет прямого доступа через файловую систему, в нем также реализован старый метод хранимых скриптов по версиям — этот метод мы применяем в особых случаях, эти скрипты выполняются перед сравнением по новой технологии.
(я выделил цветом это место после того, как 3 или 4 раза повторил это в комментариях в ответ на вопросы)

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

Конечно, такие вещи, как апгрейд структуры БД, особенно при большом количестве отличий, лучше делать вручную, по очереди анализируя каждый отличающийся объект. Кроме того, перед такой операцией настоятельно рекомендуется делать полное резервное копирование БД. Но по мере совершенствования описанных алгоритмов значение квалификации пользователя уменьшается прямо на глазах.

Я новичок на хабре, а тут вроде бы не любят смайлики — поэтому я предлагаю читателям самим догадываться, в каких фразах я позволял себе шутить.

Благодарю за внимание.
Tags:
Hubs:
Total votes 33: ↑29 and ↓4 +25
Views 5.1K
Comments Comments 50