Обновление БД с nHibernate/FluentMigrator и проблемы с индексами/дефолтными констрейнтами

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

Если релизы происходят нечасто, то для ручного решения таких проблем хорошим решением может стать SqlCompare от RedGate, которая эту задачу предельно упрощает. Но если у вас коробочный продукт, или вы хотите обновлять staging сервер по билду CI-сервера, то, конечно, рано или поздно захочется этот процесс автоматизировать.

Для автоматизации отлично подходит FluentMigrator, использование которого предполагает написание миграций, каждая из которых производит некоторые изменения БД. Будучи последовательно примененными, миграции и обновляют БД до текущего состояния. Подобное обновление можно производить при запуске приложения (для ASP.Net-сайтов я обычно делаю это из Application_Start).

Некоторые best-practices из моей практики использования FluentMigrator в компании с nHibernate:

• Миграционные скрипты я создаю с помощью nHibernate-функции SchemaUpdate (new SchemaUpdate(config).Execute(true, false);), выведенный на консоль sql-скрипт обновления в дальнейшем просматривается на правильность и добавляется в список миграций (в виде Execute.Sql() или Execute.EmbeddedScript)
• nHibernate не генерирует скрипты на удаление полей и таблиц — sql для таких обновлений необходимо добавлять вручную, либо использовать функционал FluentMigrator (Delete.Column(«a»).FromTable(«b»);, Alter.Table()…).
• nHibernate не генерирует скрипты на удаление полей и таблиц — sql для таких обновлений необходимо добавлять вручную, либо использовать функционал FluentMigrator (Delete.Column(«a»).FromTable(«b»);, Alter.Table()…).
• Изменение типа поля, если у него было задано значение по-умолчанию (default constraint) может быть проблематичным (простое alter table alter column Test double default 0 не пройдет), т.к. на поле ссылается существующий constraint. Т.к. имена констрейнтов, созданные NHibernate, различны, то сначала необходимо существующие ограничения удалить. Сделать это можно вызовом хранимой процедуры (EXEC [dbo].[DropDefaultConstraint] ‘TableName’, ‘FieldName’).
  Текст процедуры — в конце заметки
• Для изменения полей, связанных внешними ключами, также необходимо эти ключи удалять. Но эти ключи NHibernate всегда называет одинаково, поэтому достаточно в скрипт добавить вызов (ALTER TABLE [Table] DROP CONSTRAINT FKF532455D27D97C16 — название констрейнта можно узнать из текущей dev-базы)

Список планирую пополнять в процессе обнаружения и решения новых проблем (чтобы не забыть решения в будущем :)).

Текст хранимой процедуры [dbo].[DropDefaultConstraint]

CREATE PROCEDURE [dbo].[DropDefaultConstraint] 
	@table_name varchar(256)
	,@col_name varchar(256)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
declare @Command  nvarchar(1000)
select @Command = 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name
 from sys.tables t   
  join    sys.default_constraints d       
   on d.parent_object_id = t.object_id  
  join    sys.columns c      
   on c.object_id = t.object_id      
    and c.column_id = d.parent_column_id
 where t.name = @table_name
  and c.name = @col_name
execute (@Command)
END