Подходы к версионированию изменений БД

в 17:02, , рубрики: Git, Microsoft SQL Server, sql, версионирование, версионирование баз данных, версионная миграция, версионная миграция бд, Системы управления версиями, метки: ,

Намного лучше дисциплинарные ограничения убирать инструментарным расширением
Автор статьи

Введение

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

На протяжении 5 лет разработки нескольких корпоративных ИС, я ставил и пытался решать вопросы, как тот или иной аспект разработки БД сделать удобным. Искал инструменты, помогающие что-то делать с БД, методологии. На удивление в этой области мало наработок. И в каждом подходе сразу видно – вот это нельзя, вот тут будет неудобно, тут слишком много дисциплинарных правил (см эпиграф)… В этой статье я попытался собрать те походы, которые считаю наиболее эффективными, и один, в добавление к собранным, представлю как венец моих исканий, который считаю наиболее «бронебойным».

Эволюция проблемы

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

  1. Есть один инстанс БД, он же prod, на нем и пользователи работают, и программисты смотрят и меняют данные и схему. Вроде удобно, все очень просто, ничего лишнего, бритва Оккама и все такое.
  2. Хм, а программисты ведь программируют сразу на prod’е – а если сделают что-то не то? Залочат данные, или удалят что-то важное? Да или просто нагрузят сервер в поисках нужной оптимизации. Любое решение такой уже возникшей ситуации для пользователей будет плохо. Нет, надо бы отделить БД для разработки dev от prod’а. Возникает вопрос – как переносить изменения с dev’а на prod?
  3. Хм, а как бы нам следить за тем, когда и что изменилось в БД? За кодом приложения-то мы следим, а код хранимых процедур и всего остального в БД? Будем скриптовать некие скрипты с alter’ами? А как версионировать create? Он ведь повторно ненакатываемый, в отличие от alter’а.
  4. Хм, все программисты на одном dev-сервере, он же test для пользователей. То есть тестирующие пользователи могут наткнуться на то, что программист еще не доделал, чего не должно быть видно пользователю. Или просто сломано. Надо бы разделить dev с test’ом.
    (на самом деле на эту проблему во многих ИС можно не обращать большого внимания)
  5. А ведь такая же ситуация с программистами – если два программиста меняют БД, то могут быть какие-нибудь артефакты. Один что-то меняет, а приложение к этому еще не готово – и у всех проект не запускается. Надо бы каждому программисту по своему инстансу БД. Чтобы у каждого своя песочница была. А когда БД очень большая?
  6. Опа, а как же теперь все изменения переносить на prod? Если использовать сравнение схем, то БД одного программиста перетрет изменения другого программиста. И как изменения одного программиста переносить в инстанс другого? Переносить бекап с prod’а каждому программисту? А потом его изменения как на этот бекап накатывать? Нет, ерунда, как-то тут какие-то скрипты должны играть роль.
  7. Постойте, а как быть с теми данными, которые должны быть одинаковыми на всех инстансах (справочники и персистентные справочники)? Они будут переноситься с бекапами? Или все же скриптовать insert’ы или merge’ы и скрипты версионировать? А как их запускать – всегда все сразу, или все же только те, которые поменялись? А как порядок регламентировать?
  8. А похоже, бывает, что есть такие данные и процедуры, которые должны быть разными на разных инстансах! Настройки, репликации, связи с другими сервисами. При шардинге (sharding). Как быть с ними? Перенос бекапа и последующий скрипт изменения, свой для каждого инстанса? Так-то можно, но не все различия этому поддаются – например, memory optimized tables не могут быть преобразованы в обычные таблицы, не считая того, что поднимать бекап с ними может быть просто невозможно, когда не хватит памяти у test-сервера или инстанса программиста.
    К слову, с этим я столкнулся, когда мне нужны было сделать memory optimized tables на 20 ГБ, а еще когда на prod’е была целая система синхронизации с другой системой, чего не должно было быть на других инстансах.
  9. Ммм, а как бы сделать так, чтобы легко можно было поднимать сразу целый новый инстанс БД? Что, если все настройки и БД, и SQL Server’а скриптовать и версионировать?
    Это удобно, когда у каждого программиста свой SQL Server, а не когда сервер один с кучей баз. Или шардинг. Или новый тестовый контур для новой большой бизнес-фичи.

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

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

Полная универсальная постановка задачи

Задачи и проблемы, которые могут вставать при разработке БД:

  1. Нужно версионировать:
    A. Схему БД. То есть нужно иметь возможность в каком-либо виде сохранять историю изменений схемы БД.
    Это самая основная цель, на которую навешиваются все остальные возможности. Версионирование схемы БД – краеугольный камень разработки БД. Тут может быть два пути. Можно поток изменений всех объектов БД сохранять одним глобальным потоком, и можно как-то версионировать каждый конкретный объект. Я считаю (см п6), что второй путь в итоге намного практичнее.
    B. Некоторые данные: справочники динамические, справочники персистентные (persistent, статические).
    Цель важная, если от этих данных зависит код приложения (тогда они будут персистентными). То есть, если какая-то таблица соответствует одному enum в коде. Или же, иначе, цель второстепенная, но очень полезная для контроля добавления/изменения статичных и не очень данных, а также для автоматизации deploy'я изменений БД и релиза разных контуров (п2) – если их более одного.
    C. Скрипты преобразования данных (миграционные скрипты). Они могут быть связаны либо с изменением схемы БД, либо с бизнес-задачей. Для таких изменений важен порядок, и иногда требуется повторное исполнение.
    Не во всех ИС такие скрипты будут существовать. Но, если они существуют, то эта цель обязательна.
    D. Настройки БД.
    Примеры важных настроек БД, которые стоит версионировать – если должен быть включен service broker (он необходим для sql dependency), change tracking (он может использоваться для более сложных кешей), опция read_committed_snapshot, должен быть нужный user БД для приложения, настройка оповещений об ошибках, настройка поддержки memory optimized tables. Польза от сохранения и версионирования настроек БД в том, что можно легко сделать новую БД, работающую полностью так же, как основная. И что в удобном виде можно провести code review, согласовать изменения. А также просто для того, чтобы была история – что, когда и кем было сделано.
    E. Еще некоторые объекты SQL Server’а – джобы, некоторые настройки сервера, оповещения, логины, связанные серверы (для интеграции с другими системами).
    Это может быть нужно, когда тестовые контуры должны полностью повторить prod, вместе с обработкой в джобах. Что кажется совершенно обычной ситуацией.
    Кроме того, если все настройки, джобы и прочее заскриптованы, то можно легко поднять новый контур. Запустил один скрипт – и все новое SQL-окружение полностью рабочее.
  2. Нужна поддержка многих контуров: один prod (а может быть и не один! – например, при шардинге, или когда продукт коробочный и у каждого клиента своя БД), много тестовых – не только два тестовых контура (prod-test, dev-test), но и по контуру на каждого программиста, или по контуру на большую фичу.
    A. Поддержка многих контуров означает, что должна быть возможность изменения одного контура перенести в любой другой контур, не испортив его и не затерев те изменения, которые у него уже тоже, возможно, есть.
    То есть, если у каждого программиста своя БД-песочница, надо изменения одного программиста переносить не только на prod, но и на все остальные инстансы. Даже если там другой программист тоже что-то сделал.
    B. Бывает, что схема в разных контурах может немного отличаться – например, в тестовом окружении нужны дополнительные объекты (вьюшки, хранимки), а может и наоборот, из какого-то тестового контура надо исключить кусок БД, интеграцию с другой системой etc. Также в случае, когда в одном инстансе есть memory optimized tables, а в другом они не поместятся в память и эти таблицы должны быть обычными.
    C. В разных контурах версионируемые данные могут быть разные – например, настройки запуска приложения, доступа к другим системам. Также тестовые данные для тестовых контуров.
    Можно провести аналогию со случаем, когда у разных программистов, или на разных publish-хостах приложения должны быть разные файлы app.config – что достигается с помощью фичи Visual Studio app config transformation. Аналогично что-то в БД может отличаться в разных инстансах.
    D. То же для настроек БД и сервера. Например, на prod/prod-test нужны все джобы, а на локальных БД у программистов – не все. Поддержка memory optimized tables – на prod’е нужна, а на других контурах может быть и нет.
    E При восстановлении бекапа с prod’а на другой инстанс нужно иметь возможность привести восстановленную БД в соответствие с нужным контуром.
    Иногда это может быть невозможно (опять же – если есть memory optimized tables), но, если это возможно, то это очень удобная фича, когда нужно все данные одного инстанса БД перенести на другой, что наиболее просто сделать переносом бекапа.
  3. Задачи, связанные с версией БД:
    A. Когда программист делает изменения в БД, не все из которых должны быть в prod’е, нужно оставить ненужные – если программист их сделал для своих тестовых целей – и перенести на prod (и другие инстансы) только нужные.
    Программисту удобно оставить в своей песочнице все, что он хочет, хотя переносить на другие инстансы необходимо ровно то, что нужно. То есть если он сделал для себя какую-то удобную и нужную только ему вьюшку, процедуру, или даже таблицу – это не должно попасть на prod.
    Этот пункт почти эквивалентен 2bc, но тут имеется в виду, что могут быть объекты БД, которые можно не версионировать, а просто вручную создать в своей песочнице.
    B. Накат к какой-то конкретной версии БД (большей, чем текущая на выбранном контуре).
    Это бывает нужно, если разработка БД идет отдельно от разработки приложения. Если же изменения БД идут вместе с изменением приложения, то различных будущих версий БД просто не должно быть. То есть должен быть подход «не готово/портит/не поддерживается приложением – не делай коммит, по крайней мере в общую ветку репозитария». А если же изменения в БД есть, то они должны быть обратно совместимы. Например, удаление/переименование столбца должно сопровождаться соответствующим изменением приложения, а добавление – не обязательно. В статье 3 (Эволюционный дизайн баз данных) такие изменения автор называет деструктивными – я согласен с этим термином.
    C. Откат к старой версии, по аналогии с простым git checkout при обычном программировании приложения – невозможен в общем виде. Потому что, при наличии данных в таблицах, преобразовать их к старой (то есть произвольной) схеме невозможно.
  4. Иногда может быть полезно создать чистую БД с данной схемой и данными справочниками (или без справочников) – для тестов, например.
    A. При автоматизации тестирования (continuous integration) можно для прогона тестов создавать свежую чистую БД, или с наполнением случайными данными (бесплатных инструментов для этого не знаю, платные есть у redgate, devart) или поднабором рабочих данных (в этом может помочь Jailer).
    На практике реальную пользу от этого можно извлечь, только если этот процесс будет прост и быстр как щелчок пальцами – иначе это делать никто не будет. Ведь можно использовать обычную тестовую БД.
  5. Если какое-то изменение уже было накатано – в штатном режиме предусмотреть, чтобы оно не было накатано повторно, или делать изменения идемпотентными.
    На практике правило «делать все изменения идемпотентными» слишком дисциплинарно сложное. Намного лучше дисциплинарные ограничения убирать инструментарным расширением.

  6. По каждому объекту БД нужно иметь возможность увидеть историю его изменений.
    A. Для контроля нужно иметь возможность увидеть, какие изменения будут накатаны, в конкретных SQL-скриптах («накопительные скрипты»).
    B. Очень желательна возможность code review. Причем явный оператор alter предпочтительнее сравнения операторов create table (на основе которых делается diff и впоследствии накатывается), поскольку лучше контролировать действия с БД, а не декларации. А для процедур и подобных объектов надо иметь возможность видеть diff тела.

  7. На используемые инструменты нужны лицензии. При подходе shared db (что может провоцировать проблемы и конфликты изменений – см п2) достаточно одной (когда только один специально выделенный человек производит накат изменений), а при подходе, когда у каждого программиста есть своя БД – каждому программисту по лицензии.

В моей практике я руководствовался пунктами:

  • 1 и 2 – от корки до корки;
  • 3a;
  • 3b не использовал, потому что всегда в моих проектах БД с приложением были целостной одной системой и разрабатывались полностью совместно;
  • 4 – пробовал использовать, но тесты в целом у меня не очень прижились, потому что требуют дополнительного ресурса, ну или перестройки парадигмы для TDD;
  • 5 – обязательно;
  • 6 – важный, хотя довольно редко;
  • лицензии не требовались, потому что инструмент для выбранного подхода бесплатен.

Так что я считаю обязательными все пункты, кроме 3b и 4.

Подходы

Я пришел к выводу, что целесообразно выделить подходы:

  1. Сравнение схем целевой БД и БД-источника.
  2. Сравнение заскриптованной схемы (и данных) с целевой БД.
  3. На основе последовательных (инкрементальных) ручных SQL-скриптов.
  4. На основе ручных независимых SQL-скриптов, структура которых повторяет схему БД.

В этом списке подходы отсортированы по увеличению полезности.

Похожие статьи

  1. https://habrahabr.ru/post/121265/ — описывает кратко подходы 2, 3. Еще есть подход с последовательностью идемпотентных изменений, но я его откидываю ввиду слишком высокой сложности поддержки идемпотентности скриптов, когда их количество велико. Не считая того, что просто запустить 1000 скриптов, даже если они ничего не будут в итоге изменять, тоже занимает время (и размер лог-файла наката). Тут должен быть подход «если изменение уже накатано, не надо его накат повторять» (п5).
  2. https://habrahabr.ru/post/258005/ – комбинация подходов 3 и 1 — на основе redgate SQL Source Control и redgate SQL Compare. (статья плохо описывает работу с БД, в основном она о любви к Atlassian) – как я понял, сначала, при коммите, они накатывают скрипты на DB QA, потом сравнением схемы оно переносится на prod.
  3. https://habrahabr.ru/post/312970/ — хорошая длинная статья, подход очень похож на предыдущую статью. Используют CI, чтобы на каждый коммит накатывались изменения на БД QA, и выкатывался артефакт-накопительный скрипт изменений БД для наката на prod. Смысл этого артефакта не очень понятен, если сами скрипты в коммите. Квинтэссенция в картинке.

В целом к идеи автоматизации наката скриптов по коммиту я бы отнесся крайне настороженно – иногда бывает, что коммиты делаются неготовыми или неполными. Дисциплинарное правило «коммить в мастер только готовый код» на практике работает очень плохо. Лучше его избегать улучшением инструментария – для этого существует класс инструментов continuous integration (например, TeamCity от JetBrains или совсем бесплатный Jenkins). Я за то, чтобы накат скриптов на БД происходил исключительно осознанно человеком-программистом и только в нужные моменты времени – которые никак не должны быть связаны с коммитом.

1 Сравнение схем целевой БД и БД-источника

Инструмент

Redgate SQL Compare. Еще есть http://compalex.net/, но он работает только с php. Есть и другие инструменты сравнения схем БД.

Методология

Кроме БД prod – она целевая БД – делается БД dev – она БД-источник.

Каким-либо образом делаются изменения в БД-источнике. Причем эта БД-источник получается не тестовая в общепринятом смысле, потому что с ней нельзя делать все, что угодно – подразумевается, что все изменения (по крайней мере, изменения схемы БД) должны перенестись на целевую БД. Далее эти изменения могут скриптоваться, но эти скрипты впоследствии никак не используются – потому что, если их использовать и накатывать каким-либо образом, то вся суть подхода исчезает, сравнение схем становится бессмысленным. Эти скрипты могут лишь играть роль истории изменений. Но которая может отличаться от реальности, поскольку можно что-то визуально в Management Studio (или другом GUI для БД) поменять и забыть это заскриптовать. Или заскриптовать неправильно. Потом, в момент деплоя на целевую БД, делается (с помощью инструмента) diff-скрипт, который накатывается, приводя целевую БД в состояние равной схемы с источником.

Плюсы

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

Минусы

  • Невозможно все, что связано с несколькими контурами (п2).
    Если я делаю изменения на своей выделенной тестовой БД-песочнице – я должен перенести эти изменения на БД-источник для prod’а. Если это делать по такой же схеме, то это дает дыру – перенос тех случайных или личных изменений, которые я внес в свою БД (я ведь могу делать в своей песочнице все-все-все, правда?). Скриптуются ведь только нужные изменения, но переносятся все. Кроме того, то я затру изменения, которые переносит другой программист.
  • Если во втором контуре есть хоть какие-то отличия, нужно для него делать еще одну БД-источник. То есть, если в тестовом контуре есть какие-либо настройки или данные, отличающиеся от prod’а, то либо для него нужна вторая БД-источник, либо как-то еще решать эту проблему.
  • Отсутствие контроля над скриптами переноса изменений – будут накатаны скрипты, которые сгенерит инструмент. Иногда это может быть плохо, надо учитывать специфику. Например, некоторые изменения требуют пересоздания таблицы. Что делать нужно либо крайне осторожно, либо вообще не делать (если таблица часто используемая, очень большая).
    Если же брать этот diff-скрипт, его смотреть и править, то тогда снова вся суть сравнения схем исчезает – можно ведь сразу писать скрипты.
  • Заскриптованная история изменений объектов может отличаться от реальной. Потому что инструмент сравнивает именно схему баз, а не скрипты – можно (случайно, конечно) что-то поменять и не занести это в скрипты. Или занести в скрипты не совсем правильно.
  • Все равно нужны дополнительные усилия и инструменты для поддержки:
    — миграционных скриптов (преобразования данных),
    — версионирования справочников и персистентных данных.
  • Инструмент не перетягивает настройки БД и настройки SQL Server'а.
  • Если кто-то другой накатил что-то на БД-источник, при deploy’е оно тоже перетянется.
  • Зависимость целевой БД от другой БД – в том смысле, что prod должен идти в комплекте со своим источником.
  • Бесплатных хороших инструментов нет, а redgate SQL Compare дорогой. Причем нужна лицензия для инструмента на всех хостах, с которых будет осуществляться миграция на любую целевую БД.

Подход не решает задачи

  • 1b — подразумевается, что для этого надо использовать SQL Data Compare; 1c; 1d; 1e.
  • Для 2 – дополнительные и весьма весомые усилия. Проще отказаться от нескольких контуров, чем их поддерживать.
  • 3a – инструмент перетягивает изменения схемы либо всех объектов, либо выбранных. То есть нужно при deploy’е просмотреть список всех объектов и отщелкнуть ненужные. Принципиально это решает задачу, но… Так делать никто не будет. Снова правило «намного лучше дисциплинарные ограничения убирать инструментарным расширением». Не хватает что-то типа файла .gitignore.
  • 6 – нужны дополнительные усилия, история изменений может расходиться с реальной.

2 Cравнение заскриптованной схемы (и данных) с целевой БД

Инструмент

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

Методология

Роль БД-источника тут играл бы каталог с скриптами, полностью создающими БД – и схему, и версионируемые данные (справочники, персистентные справочники). То есть программист вносит изменения в эти скрипты, запускает инструмент, который сравнивает весь каталог с целевой БД и делает diff-скрипт, который либо сохраняется для code review, либо сразу накатывается.
Так как инструмента нет, то можно лишь фантазировать о том, как этот инструмент мог бы сравнивать данные и настройки БД и SQL Server’а.

Плюсы

  • Отлично решена задача истории изменений каждого объекта БД (п6).
  • Отлично решена задача песочницы (п3a), как и в оставшихся подходах, основанных на скриптах. Не нужны изменения – не вноси в скрипты.

Минусы

  • Аналогично подходу 1, невозможно все, что связано с несколькими контурами (п2).
  • Аналогично подходу 1, нет контроля над накатываемыми скриптами.
  • Та же проблема с миграционными скриптами.

Подход не решает задачи

  • 1c.
  • 1d, 1e – неизвестно, какой инструмент.
  • Для 2 – дополнительные и весьма весомые усилия. Проще отказаться от нескольких контуров, чем их поддерживать.

3 На основе последовательных (инкрементальных) ручных SQL-скриптов

Инструмент

flyway db. Возможно, есть альтернативы (https://github.com/lecaillon/Evolve – я не готов рассказать об этом инструменте, но, похоже, делает что-то похожее).

Методология

Методология подхода наиболее проста. Зачаровывающе проста. По мере необходимости пишутся sql-скрипты изменений – произвольные, как на изменение схемы, так и на изменение данных. Не имеет значения, какие скрипты. Файлики нумеруются, складываются в папочку. В нужное время запускается инструмент, который в порядке нумерации накатывает новые, то есть еще не исполненные файлы скриптов на выбранную БД. Накатанные запоминает в специальной табличке, то есть повторно скрипт не исполнится.

Так работает компания Qiwi. Или работала, когда я там участвовал в разработке платежной системы. Но там без инструментов, инструмент заменяют дисциплинарные правила. Есть несколько QA-сотрудников, которые следят за специальным репозитарием git и накатывают новые скрипты, сначала на тестовую БД – смотрят, не сломалось ли чего, потом, если все хорошо, на prod.

Плюсы

  • Крайне, необыкновенно просто. Пишешь скрипт, кладешь в папочку, и все. Думаю, благодаря этому этот подход самый распространенный.
  • Полный (абсолютно полный) контроль на накатываемыми изменениями. Никакой генерации скриптов.
  • В том числе можно в скриптах хранить и данные (п1b), и миграционные скрипты (п1c), и настройки и БД, и SQL Server’а (п1de).
  • Можно накатывать до какой-то конкретной версии (п3b).
  • Решена проблема многих контуров, но без отличий в них (п2a).

Минусы

  • Нельзя выделить историю изменения одного объекта, alter’ы на один объект разбросаны по многим скриптам, многим файликам.
  • При параллельной работе двух программистов, они могут создать скрипты с одинаковыми номерами.
  • Раз уж подход скриптоцентрированный, то не хватает фич:
    — Некоторые скрипты хотелось бы накатывать при их изменении. То есть добавил строчку в скрипт версионируемого справочника, и он исполнился, и строчка в таблице появилась. В таком виде можно хранить историю изменения данных (см подход 4).
    — Некоторые скрипты хотелось бы накатывать при каждом deploy’е – это, например, какая-нибудь очистка, занесение персистентных справочников, которые должны версионироваться (поэтому их нельзя заносить вручную в БД).
  • В последовательности произвольных скриптов крайне сложно разбираться. Создание таблиц, их alter’ы, добавление строчек в справочники, миграции – разбросаны практически хаотично в одной последовательности. Хотелось бы иметь алфавитную сортировку файликов, различные папочки. Словом, хочется в скриптах видеть структуру БД. Можно, конечно, что-то придумать – сделать кучу папочек, сделать огромный bat, запускающий инструмент на эти папочки в нужном порядке… Да, это начало следующего подхода, 4го.

Подход не решает задачи

  • 2bcde,
  • 6b.

4 На основе ручных независимых SQL-скриптов, структура которых повторяет схему БД

Инструмент

liquibase. Возможно, есть альтернативы (redgate SQL Source Control, https://www.quora.com/What-are-the-alternatives-to-LiquiBase – но я не вполне хорошо знаю, как они работают).

Методология

Идея

Для создания и изменения схемы на каждый объект БД создаем по файлику, в котором будет скрипт, отвечающий за этот объект – таким образом при версионировании файлов получаем историю изменений на каждый объект. Эти файлики кладем в папочки, повторяющие структуру БД. Так как последовательность исполнения скриптов важна, вводим управляющие файлы, содержащие последовательность наката скриптов, а инструмент делает написание этих управляющих файлов достаточно простым и решает, какое изменение накатывать нужно, а какое нет – если оно уже было накатано ранее или отфильтровано. Кроме того, если нужно различие в чем-то в различных инстансах БД, вводим значения переменных, которые инструмент использует, модифицируя нужным образом скрипты для каждого инстанса. Кроме того, можно ввести фильтры на скрипты, и, в зависимости от контекста («только изменение схемы», «только импорт справочников», «создать/обновить только такой-то кусок схемы») отфильтровать скрипты.

Для изменения таблицы нужно в файлик с ее create’ом дописать скриптик (changeset) с оператором alter или create index или каким-то другим. Или можно изменить существующий соответствующий changeset, если возможно сделать его повторнонакатываемым.

Для изменения процедуры/функции/триггера/вью надо поменять код в файлике, соответствующем этому объекту. Чтобы этот скрипт был повторнонакатываемым, нужно в первом changeset’е сделать создание этого объекта с пустым телом, а во втором – оператор alter с нужным телом (жаль, у SQL Server’а нет оператора create or alter). Тогда первый changeset будет исполняться только один раз, а второй – при изменении.

Ну а для непосредственно deploy’я делаем bat-файл(-ы), запускающие инструмент с нужным контекстом и настройками. Таким образом нужный deploy будет запускаться посредством запуска соответствующего bat’ника.

Можно настроить, чтобы логи запусков (какие changeset’ы исполнялись и сколько времени) сохранялись. Добавив их в .gitignore.

Файлы

Делаем следующую структуру папок:

— <Имя БД>

  • CLR_Objects
    Тут будет по 1 sql-файлику на каждую CLR сборку. В этом файлике – сначала drop function, потом drop assembly, потом create assembly, потом create function. Сами сборки придется хранить в скрипте в виде base64, но можно сделать простенький инструмент, автоматизирующий это преобразование:

    Clipboard.SetText("0x" + string.Join(“”, File.ReadAllBytes(path).Select(b => b.ToString("x2"))) )
  • Tables
    Тут будет на каждую таблицу по директории, в которой раздельно файлики создания таблицы и скрипта merge для версионируемого справочника.
    — <имя таблицы1>
    — <имя таблицы2>
    — …
  • Types
    Тут будет по 1 sql-файлику на каждый тип.
  • Functions
    Тут будет по 1 sql-файлику на каждую функцию.
  • Views
    Тут будет по 1 sql-файлику на каждую вьюху.
  • Stored_Procedures
    Тут будет по 1 sql-файлику на каждую хранимую процедуру.
  • Triggers
    Тут будет по 1 sql-файлику на каждый триггер.
  • Migration_Scripts
    Тут будет последовательность скриптов изменения данных.

— core
Тут будет сам инструмент и его настроечные файлы.

Как видно, идея этих папок в повторении схемы БД и соответствии каждого объекта своему одному файлу.

В головной папке <Имя БД> будут файлы:

  1. Скрипт создания БД (create database, alter database — настройки, пользователи, роли)
    create_db.sql
  2. Файл(-ы) со скриптами, изменяющими настройки SQL Server’а и саму БД (sp_configure, sp_add_job и ее собратья, sysmail_add_account_sp и ее собратья, alter database etc). Хотя скрипты джобов можно хранить для удобства в различных файликах.
    config_db.sql
  3. Файл с последовательностью наката таблиц. Команда инструмента includeAll исполняет скрипты в алфавитном порядке, что некорректно создаст таблицы ввиду наличия ссылок в них, поэтому нужен файл, регулирующий последовательность наката таблиц.
    tables.xml
  4. Файл с последовательностью наката вьюх. Вьюхи могут зависеть друг от друга, поэтому важен их порядок наката.
    views.xml
  5. Файл с последовательностью наката скриптов изменения данных. Для миграций данных тоже важен порядок.
    migrations.xml
  6. Файл с командами инструменту как создавать схему БД. В нем будут ссылки на файлы 1 и 2 с созданием и настройкой БД и сервера, а потом на папки CLR_Objects, Tables (точнее, файлик tables.xml), Types, Functions, Views (точнее, файлик views.xml), Stored_Procedures, Triggers – именно в этой последовательности. Так же в этом файле можно задать значения переменным, от которых будут зависеть скрипты – если в различных инстансах БД есть отличия.
    master_Scheme.xml
  7. Файл со всеми командами инструменту. В нем сначала идет ссылка на предыдущий файл создания схемы, потом на дополнительные действия – преобразование/миграция/очистка данных (ссылка на папку Migration_Scripts, а точнее, файлик migrations.xml)
    master.xml

Заметим, что для создания/изменения хранимых процедур, триггеров, функций и типов последовательность не важна. Поэтому достаточно команды includeAll инструмента, накатывающего их в алфавитном порядке.

Как использовать

Для каждого варианта использования нужно создать bat-файл, запускающий инструмент с соответствующим контекстом – например, deploy_local_scheme.bat, deploy_local_full.bat, deploy_prod_scheme.bat, deploy_prod_full.bat, deploy_<programmer>.bat etc. В одном проекте у меня таких файликов было аж 18 – там была целая система миграции данных, и нужно было регулировать, когда какую миграцию исполнять.

Кроме контекста, bat-файл в себе должен содержать connection string и имя команды инструмента.
Возможные команды:

  • update. Основная команда исполнения всех нужных changeset’ов.
  • status. Покажет, какие changeset’ы будут накатаны.
  • udpateSQL. Покажет полный sql-скрипт, собирающий в себе все накатываемые changeset’ы.
  • changelogSync. Заносит новые changeset’ы как исполненные, не накатывая их. Нужно использовать, когда изменение накатано вручную. Должна использоваться максимально редко.

Я еще для удобства просмотра логов исполнения сделал вывод их в текстовый файлик:
> %outputfilename% 2>&1

Changeset’ы могут быть помечены атрибутами:

  • runOnChange – при =true указывает инструменту накатить changeset при его изменении (если написать =false, то после изменения changeset'а будет ошибка вида «накатанный changeset был изменен»);
  • runAlways — указывает накатывать changeset при каждом deploy’е.

В случае, когда нужно изменить схему так, что сломаются какие-то скрипты изменения данных, то есть миграционные скрипты (например, если нужно изменить название таблицы, колонки, удаление чего-то), то нужно написать соответствующий alter или sp_rename в файлик, соответствующий данной таблице, и соответствующим образом изменить нужные скрипты. Далее, для одноразовых скриптов из них нужно сделать так, чтобы инструмент не выдавал ошибку, что накатанный changeset изменился. Это достигается двумя путями – либо команда changelogSync, либо вручную изменить соответствующую строку в таблице инструмента, обновив там md5-сумму – значение ее подскажет сам инструмент.

Плюсы

  • Полный (абсолютно полный) контроль на накатываемыми изменениями. Так же как в подходе 3.
  • Аналогично подходу 2, очень удобно проводить code review, смотреть историю изменений всех объектов.
  • Различия в контурах (п2bcde) поддерживаются с помощью переменных. То есть можно сделать по bat-файлу на контур, в котором будут задаваться значения переменных, а в скрипте это значение использовать, или changese’ы можно помечать label’ами, которые можно отфильтровать или нет в зависимости от контура.
  • Даже чистую БД можно создать (п4).

Минусы

  • Сложный в первоначальной настройке. Но это только сложность вхождения. При использовании такой сложности нет – нужно лишь добавить скрипт-changeset в нужный файлик, и все.
  • В длительной разработке, когда одну таблицу нужно в течение времени менять много раз, в некоторых случаях будут множиться changeset’ы с оператором alter table. Но в большинстве случаях этого не будет — если всегда писать многоразовые changeset’ы. Это минус относительно подхода 2 (при котором можно править операторы create), а вот для подхода 3 это усовершенствование – там вообще все изменения будут линейно друг за другом складываться.
  • Инструмент очень плохо поддерживается и развивается. Его пишет один человек как хобби. Но сегодняшней функциональности хватает. Кроме того, инструмент несложен, можно похожий сделать самостоятельно за месяц.

Подход не решает задачи

Только необязательный в моем видении п3b. Победа.

Автор: breezemaster

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js