Контроль изменения структуры БД

    Преамбула


    Пару лет назад я и мои сотрудники столкнулись с проблемой сопровождения большого количества аналогичных БД на удаленных филиалах. Удалены они иногда на тысячи километров, а объемы исчислялись гигабайтами – что не позволяло использовать какое-либо зеркалирование серверов. Осложнялась ситуация мелкими отличиями, т.е. 95% бизнес-логики филиалов совпадало, но было ещё 5%, которые сильно мешали и ни о какой автоматической обработке группы филиалов не было и речи.

    Для данных в итоге было написано приложение синхронизации «общей» структуры, которая собственно и интересовала центральный офис. Однако ключевым моментом являлась именно бизнес-логика: структуры таблиц, триггеры, хранимые процедуры. Т.е. когда происходило обновление логики в центре – те же изменения нужно было произвести и по всем филиалам, и не сломать при этом индивидуальные особенности каждого филиала.

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

    Постановка задачи


    Основные требования к функционалу были простые:
    • возможность сравнения двух БД;
    • автоматическая генерация скрипта на преобразования одной базы в другую;
    • создание «снимка» базы
    • поиск объектов в бизнес логике (поиск зависимостей)

    Все это в нашем случае должно было работать на SQLServer 2005+.

    Продукт



    За несколько недель неполной занятости была написана утилита, которая могла помочь нам со всеми этими напастями. Программа написана на C#.NET 3.5.

    Просмотр и анализ структуры БД или снимка.


    При просмотре, особенно удобно «вырывать» куски скриптов из процедур, функций, триггеров… Но и создание таблиц и индексов иногда нужно.


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



    Сравнение структур БД и/или снимка структуры БД


    Основное окно работы с программой. В верхней части указывается соединение с БД или заранее сохраненный снимок структуры. В нижней части окна отображается полный скрипт на преобразование структуры.


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


    Сравнение двух версий объекта. Похоже на SVN, не так ли? :) Видно новые/удаленные колонки, индексы, связи и т.д. Также видны будут изменения и в коде хранимых процедур — самое важное.


    Эпилог



    Продукт оказался очень востребован, как у нас так и у знакомых программистов. Оттуда мы узнали ещё одно важное свойство продукта: контролировать действия других разработчиков. :)
    На данный момент программа работает только с SQLServer. Но в коде реализация идет по принципу драйверов, т.о. в течении следующего года возможны и другие БД. Естественно не следует понимать данный продукт как 100% анализ изменений структуры БД. Это скорее контроль каркаса и бизнес-логики, а административные элементы остаются на совести админов.

    Продукт является полностью свободным, и использоваться должен только во благо :)
    Продавать его естественно нельзя.

    Скачать программу: архив

    UPD: Файл обновлен, баг с невозможностью копирования скрипта. Спасибо seele
    Поделиться публикацией

    Комментарии 42

      +6
      Молодцы.
      Всегда нравились люди которые не жмутся выложить собственные тулзы бесплатно.
        0
        Полностью согласен. И присоединяюсь к выше сказанному.
          0
          И я.
        +2
        Не помешали бы и исходники.
          +2
          На .net-е это не проблема ;)
          +1
          Что именно?

          Как делается сканирование структуры? С помощью «exec sp_help», «exec sp_helptext», а также иногда выборки из системных таблиц.

          Генерация скриптов? Ну тут много «if(a != b) ....» :)
          Позновательного там не много.
            +1
            Таки зажали :) "… а что там пробовать — сало оно и в африке сало..."
              0
              Если быть честным, есть кусок кода который будет использован в коммерческом продукте, отсюда и скрытность. Может быть после его выпуска, эту утилиту отдам с потрохами.
                0
                По секрету вам скажу (из своего опыта написания парсеров структур БД), там действительно либо куча if — else, либо switch, либо они же в хитрых комбинациях. Хотя в 3.5 LINQ должен помочь избавиться от этого.
            0
            Эх, такую бы штуку под PostgreSQL :)
            Но у нас не много филиалов, справляемся вручную…
              +1
              Держите — apgdiff
                0
                Опа, спасибо!
                Судя по описанию — то, что надо. Попробуем в деле.
              0
              Вы таки написали статью :) Спасибо, интересно, и утилита, судя по всему, весьма полезная. Жаль, что с MySQL ее нельзя использовать… впрочем, если сделаете её опенсорсной, я уверен, что такая возможность скоро появится :)

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

              P.S. Может быть, стоит упомянуть в статье, что эта утилита помогает реализовать «Метод уподобления структуры БД исходному коду» из статьи по ссылке выше?
              P.P.S. Возможно, статье больше подойдет название типа «Утилита для версионной миграции БД», + соответствующие теги?
                0
                Миграция данных ещё в проекте, много текущих задач. Как это будет? Пока не сделаю — ничего точно сказать не могу, вариантов то много :)

                «Метод уподобления структуры БД исходному коду» из статьи по ссылке выше — не пойму о чем речь.
                  0
                  Понял что за уподобление… Оно похоже но не совсем, ведь я не оперирую скриптами — я их создаю на основе сравнения снимков. Т.е. снимок создается при сканировании БД и потом сравнивается с аналогичным. Здесь скорее библиотека классов описывающих структуру БД.

                  То что вы описали: подходит в моем случае только для хранимых процедур, функций и триггеров.
                +1
                Круто;)

                Я лет 15 как использую PowerDesigner(S-Designer) и в кооперативных проектах использую Modify Database. Но версионный контроль триггеров/хранимых процедур в нём головная боль. Плюс он стоит прилично денег.

                Для контроля пакетов (читай хряп, триггеров) вполне подходит SVN. Но в вашей разработке удобный просмотр изменений. Красиво.
                  +2
                  В IBExpert — это инструмент администрирования для Interbase/Firebird. Уже давно есть подобная функция — Сравнение БД, сравнивает структуру, генерирует скрипты для приведения в одинаковый вид, для данных есть аналогичная вещь.
                  Давно ею успешно пользуюсь.

                  Чего не хватает там — фильтра для структуры, т.е. когда нужно синхронизировать часть объектов, а не все. Ну критерием для фильтра могла бы быть принадлежность той или иной схеме, но в Interbase/Firebird нет схем. Устроил бы фильтр по регулярке от имени объекта.
                    0
                    Там есть фильтр по видам объектов, но его бывает недостаточно, да.

                    Еще один минус — невозможно увидеть что именно изменилось в процедурах/триггерах. Процедура дропается целиком, причем вместе с зависимыми процедурами, и потом создается заново.
                      0
                      Да меня интересует фильтр не по видам, а исходя из логики проекта. Т.е. по именам.

                      Ну с просмотром изменений проще — можно из двух баз выдрать метаданные и сравнить два скрипта, как два текстовых файла. Конечно, хорошо бы, чтоб это было автоматизировано.
                    0
                    Боюсь предложить, но рискну, попробуйте использовать Rails Migrations :) Сайты создавать не нужно, достаточно иметь рельсовый проект и размещать в нем только миграции, при мне однажды так делалось на PHP-проекте.
                      +1
                      Есть замечательный продукт, SQL Toolbet
                      Он платный, есть триал. Если триала не хватает, вводите
                      i need more time
                      вместо серийника. Несколько раз сработает. Очень удобная программа :)

                      На днях знакомый рассказывал, что для версионности можно использовать что-то из старших редакций Visual Studio 2010, но я ещё не пробовал.
                        0
                        Хорошая утилита, вернее комплекс — но очень дорогой. Да и недостаток такого рода продуктов их чрезмерная мощность. Нам как и многим другим просто не нужна будет большая часть функционала.

                        А про студию, я знаю — но и она платная, да и пользуются ли ею все пользователи SQLServer (express)?
                          +1
                          Еще из этой серии: SchemaCompare. Удобно. Бесплатно.
                            +1
                            У всех подобных тулзов (да и всего процесса версионирования БД) есть одна проблема, мешающая их промышленному применению. Я покажу на примере.
                            Допустим, история базы была такой:
                            Версия  Изменение
                            ------- -----------------------------------------
                            1.0     create table t (id...
                            1.1     alter table t add column c ...
                            1.2     alter table t add constraint c_fk foreign key © references...
                            1.3     create index t_c on t ©
                            1.4     alter index t_c disable
                                    update t set c = -1 where c is null
                                    alter table t add constraint c_ck check (c is not null)
                                    alter index t_c rebuild
                            

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

                            Как ваш процесс (с участием вашей тулзы) решает эту задачу? как выглядят скрипты после тестирования и выпуска для наката в production, и как они применяются к целевой БД?
                              0
                              Я упоминал, что программа не 100% панацея. Она умеет: генерировать скрипты и выстраивать их в правильном порядке (индекс не будет создан до добавления поля в таблицу). Ну а ситуации с выключением индексов и проверкой зависимостей я вообще не обрабатывал, не было нужды. Все что накопится в этом обсуждении возможно будет добавлено в следующих версиях.
                              +1
                              Под FierBird подобное есть?
                                0
                                Отвечали выше
                                Я же могу сказать — что буду делать аналогичное для PosgreSQL, MySQL, Oracle. Другие БД о-о-очень сомнительно.
                                  0
                                  Про тот инструмент знаю, но там очень много ручной работы…
                                    0
                                    А почему сомнительно для других? Выкладывайте на github или в google.code — народ поможет. Лично мне пригодилось бы для FB.

                                    Сейчас, как я понимаю, доступ к MSSQL осуществляется нативными средствами .NET?
                                      0
                                      Напрямую ядро не работает с базами, а только со снимками. Драйвер может использовать что угодно (для SQLServer — нативный интерфейс).

                                      Честно — никогда не делал проекты в github или в google.code. Но и здесь загвоздка в коммерческой составляющей о которой я писал выше.

                                      Дойдет дело до открытия исходников — будут создано множество драйверов.
                                  0
                                  Использую для контроля изменения структуры БД Oracle самописную программку oracle-ddl2svn.
                                  Ставлю ее на расписание, чтобы она выкачивала всю схему БД в виде DDL-скриптов и коммитила их в систему контроля версий SVN.
                                  Написана на Java, Spring.

                                  Народ качает, пользуется. Даже шлют положительные отзывы.
                                    0
                                    Пользуюсь для этих целей AdeptSQL — очень доволен.
                                    Здорово, что у вас открытый код, хотя слова «полностью свободен» и «продавать нельзя» несколько противоречят друг другу.
                                      0
                                      Кода нет же.
                                        0
                                        Продавать то что отдано даром — это как? :) Кажется это обман покупателя…
                                        Полностью свободен проект скомпилированный — т.е. для использования.
                                        0
                                        Вообще, странно, что в SQL Server до сих пор не появилось никакого механизма версионирования, хотя бы кода хранимых процедур и функций. Неужели это так сложно сделать? Приходится либо забивать на версии и хранить всё в базе, либо постоянно параллельно держать набор запросов MODIFY.
                                          0
                                          rake db:migrate
                                            0
                                            В Visual Studio 2010 есть такая возможность как сравнение-синхронизация-выгрузкавскрипты-проектбазывскриптах.
                                            Очень удобно.
                                              0
                                              А вы не хотели бы выложить проект на codeplex?
                                                0
                                                Выше описал ситуацию.
                                                0
                                                Строго говоря, SQLServer — это любой сервер БД, поддерживающий язык SQL. Мы конечно понимаем, что речь идет о продукте Microsoft, но все же.
                                                  0
                                                  Ссылка устарела, можете перезалить?
                                                  С удовольствием пользовался этой программкой, да вот только удалилась она, а найти нигде не могу.

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

                                                  Самое читаемое