Структура базы данных и управление исходным кодом - лучшая практика

У нас есть система, в которой база данных номинально является мастером - внутри нашей системы контроля исходных текстов мы поддерживаем последовательность скриптов "изменения схемы" (файлы .sql), каждый из которых отвечает за идемпотентный откат изменения, а затем его применение. Каждый скрипт просто нумеруется, поэтому у нас есть 000.sql (который создает базу данных и устанавливает стандартные объекты), 001.sql и т.д.

Во время разработки разработчик пишет сценарий изменения схемы и запускает его на базе данных разработки. Каждое изменение должно добавлять строку в таблицу dba.change_info, содержащую номер изменения и краткое описание. Чтобы откатить изменение, можно просто запустить его первую секцию. Для SQL Server идемпотентность секции отката обрабатывается путем изучения sysobjects и т.д. перед выдачей команд DROP - аналогично конструкции "drop ... if exists". Изменения схемы могут потребовать миграции данных, если модель изменяется, а не просто добавляется, а также используются для поддержания справочных данных.

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

Все это довольно ручной процесс, но он удовлетворяет таким требованиям, как миграция данных из одной модели в другую: например, расширение булевого флага до набора опций или преобразование ассоциации "многие-к-одному" в "многие-ко-многим". Как правило, это не то, что может быть создано с помощью простых инструментов сравнения схем. Это также позволяет разделить роли - хотя на практике мы все имеем полный доступ к production, разделение достаточно велико, чтобы "DBA" мог читать и просматривать .sql файлы, которые будут применяться в production.

По крайней мере, в теории, полная база данных (содержащая только справочные данные) может быть построена путем простого выполнения всех изменений схемы по порядку для 000.sql и далее. На практике мы не делаем этого регулярно, а скорее копируем нашу производственную базу данных в dev и затем применяем сценарии изменений перед проведением регрессионных тестов перед релизом. Это служит для проверки самих сценариев изменений, но практично только для производственных баз данных среднего размера.

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

Справочные данные хранятся в электронной таблице Excel (также в системе управления версиями), которая может генерировать сценарий SQL из операторов INSERT для заполнения новых баз данных.

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

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

Взгляните на эту серию из пяти частей о принципах и методах управления версиями баз данных (К. Скотт Аллен):

1. Три правила для работы с базами данных
2. Базовый уровень
3. Сценарии изменений
4. Представления , Хранимые процедуры и т.п.
5. Ветвление и слияние

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

Если вы уже используете инструменты Red Gate, вы можете рассмотреть возможность использования SQL Source Control, который работает бок о бок с SQL Compare и SQL Data Compare, позволяя одной версии истины существовать в контроле исходных текстов. На данный момент он находится в раннем доступе, но большая часть функциональности уже есть, и ее можно опробовать. Вы можете загрузить его с http://www.red-gate.com/Products/SQL_Source_Control/index.htm . Однако на данный момент он поддерживает только SVN и TFS. Вы уже перешли на GIT?

Дэвид (менеджер по продуктам в Red Gate)

Именно для этого есть специальный инструмент. Он называется Wizardby :

... среда непрерывной интеграции базы данных и миграции схемы

Wizardby Workflow http: // octalforty-wizardby.googlecode.com/svn/trunk/docs/img/database_versioning_with_wizardby.png

Я не очень хорошо знаком с инструментарием RedGate, но если он похож на dbGhost , должна быть утилита, которая позволяет вам создавать сценарии объектов базы данных для файлов по одному для каждого объекта. В этом случае я бы предложил следующее:

• добавить ежедневное задание (или часть сборки) для обратного проектирования базы данных DEV в структуру каталогов
• , а затем сравнить ее с тем, что у вас есть в репозиторий (с помощью простого сравнения), и в основном ОТКАЗАТЬ задание на сборку и сообщить о diff , если таковые имеются. Это будет указывать на то, что структура базы данных DEV изменилась и не отражена в системе управления версиями
• , что укажет разработчику на добавление изменений в систему управления версиями (даже использовать сообщенный .diff ] для этого)

Если у вас много баз данных DEV (по одной на пользователя или ветку разработки) и это слишком громоздко, то, вероятно, лучшей комбинацией будет выполнение такой задачи на ЭТАПЕ (ТЕСТ просто перед выпуском) версии базы данных, после чего вы сохраните схему PROD в репозитории и обновите ее со STAGE только на этапе предварительного тестирования, где вы убедитесь, что изменения вашей схемы также присутствуют в репозитории.

Таким образом, разработчики по-прежнему могут работать обычным образом: сначала измените схему в базе данных DEV, и, надеюсь, вы получите баланс между гибкостью и одной истиной , которую вы хотели бы .

В моей команде мы добавляем изменения в VCS, как только изменяем базу данных DEV, но у нас все еще есть такая задача, чтобы сравнить схемы между различными базами данных (DEV, STAGE и PROD). По сути, мы следуем тому, что я однажды ответил в Как вы должны построить свою базу данных из системы контроля версий? .

Предполагая, что вы используете платформу .net, взгляните на Fluent Migrator , а также на Прослушивание подкаста Code , в котором рассказывается о проекте.
Основная цель, как я вижу, состоит в том, чтобы легко кодировать миграции, как вы делаете обычное кодирование с использованием свободного интерфейса и подхода, не зависящего от базы данных.

Он построен на основе .NET Framework. и работает с рядом форматов баз данных, включая SQL Server, SqlLite и MySQL.

Преимущество этого подхода в том, что он живет с остальной частью вашего кода и, следовательно, может управляться SCM

Пример:

   [Migration(1)]   
   public class CreateProjectsTable : Migration   
   {   
       public void Up()   
       {   
          Create.Table("Projects")              
            .WithIdColumn()             
            .WithColumn("Name").AsString().NotNullable()                
            .WithColumn("Position").AsInt32().NotNullable()             
            .WithColumn("Done").AsBoolean().NotNullable();
       }  
       public void Down()  
       {  
           Database.RemoveTable("Projects");  
       }  
   }

На работе мы активно используем мощный инструмент, который поставляется как часть ActiveRecord (который является стандартным ORM, поставляемым с Rails веб-фреймворком) под названием Миграции.

Базовая миграция будет выглядеть следующим образом:

class AddSystems < ActiveRecord::Migration
  def self.up
    create_table :systems do |t|
      t.string  :name
      t.string  :label
      t.text    :value
      t.string  :type
      t.integer :position, :default => 1
      t.timestamps
    end
  end

  def self.down
    drop_table :systems
  end
end

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

rake db:migrate #run all outstanding migrations
rake db:rollback #roll back the last run migration
rake db:seed #fill the database with your seed data

Миграции имеют методы для создания таблиц, удаления таблиц, обновления таблиц, добавления индексов и т.д. Полный набор. Миграции также автоматически добавляют колонку id, а секция t.timestamps автоматически генерирует поле "created_at" и поле "updated_at".

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