Каждый, кто когда-либо разрабатывал приложения, использующие базу данных, наверняка сталкивался с проблемой обновления структуры БД при разворачивании и обновлении приложения.
Чаще всего используется простой подход — создание набора SQL-скриптов для модификации структуры БД от версии к версии. Конечно, есть такой мощный инструмент, как Red gate, но он во-первых небесплатный, во-вторых не решает проблему полной автоматизации обновления.
Технология migrations, впервые появившаяся в ОРМ Hibernate и реализованная в Linq, очень хороша и удобна, но подразумевает стратегию разработки структуры БД code first, что весьма трудоемко для уже существующих проектов, а использование в БД триггеров, хранимых процедур и функций делает задачу перехода на code first практически невыполнимой.
В данной статье предлагается альтернативный подход к решению этой задачи, использующий хранение эталонной структуры БД в XML-файле и автоматическую генерацию SQL-скрипта на основе сравнения эталонной и существующей структуры. Итак, начнем...
Генерация XML-файла со структурой БД
Для экспериментов будем использовать БД DbSyncSample. Скрипт для создания БД приведен ниже.
USE [DbSyncSample] GO /****** Object: Table [dbo].[Orders] Script Date: 06/01/2017 10:37:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Orders]( [Id] [int] IDENTITY(1,1) NOT NULL, [OrderNumber] [nvarchar](50) NULL, [OrderTime] [datetime] NULL, [TotalCost] [decimal](18, 2) NOT NULL, CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_Orders_OrderNumber] ON [dbo].[Orders] ( [OrderNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO /****** Object: Table [dbo].[Details] Script Date: 06/01/2017 10:37:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Details]( [Id] [int] IDENTITY(1,1) NOT NULL, [Descript] [nvarchar](150) NULL, [OrderId] [int] NULL, [Cost] [decimal](18, 2) NOT NULL, CONSTRAINT [PK_Details] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Trigger [Details_Modify] Script Date: 06/01/2017 10:37:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[Details_Modify] ON [dbo].[Details] AFTER INSERT,UPDATE AS BEGIN UPDATE Orders SET TotalCost = s.Total FROM ( SELECT i.OrderId OId, SUM(d.Cost) Total FROM Details d JOIN inserted i ON d.OrderId=i.OrderId GROUP BY i.OrderId ) s WHERE Id=s.OId END GO /****** Object: Trigger [Details_Delete] Script Date: 06/01/2017 10:37:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[Details_Delete] ON [dbo].[Details] AFTER DELETE AS BEGIN UPDATE Orders SET TotalCost = s.Total FROM ( SELECT i.OrderId OId, SUM(d.Cost) Total FROM Details d JOIN deleted i ON d.OrderId=i.OrderId GROUP BY i.OrderId ) s WHERE Id=s.OId END GO /****** Object: Default [DF_Details_Cost] Script Date: 06/01/2017 10:37:43 ******/ ALTER TABLE [dbo].[Details] ADD CONSTRAINT [DF_Details_Cost] DEFAULT ((0)) FOR [Cost] GO /****** Object: Default [DF_Orders_TotalCost] Script Date: 06/01/2017 10:37:43 ******/ ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_TotalCost] DEFAULT ((0)) FOR [TotalCost] GO /****** Object: ForeignKey [FK_Details_Orders] Script Date: 06/01/2017 10:37:43 ******/ ALTER TABLE [dbo].[Details] WITH CHECK ADD CONSTRAINT [FK_Details_Orders] FOREIGN KEY([OrderId]) REFERENCES [dbo].[Orders] ([Id]) GO ALTER TABLE [dbo].[Details] CHECK CONSTRAINT [FK_Details_Orders] GO
Для экспериментов создаем консольное приложение. Подключаем к нему nuget-пакет Shed.DbSync.
Структуру БД в виде XML получаем следующим образом:
class Program { private const string OrigConnString = "data source=.;initial catalog=FiocoKb;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"; static void Main(string[] args) { // получаем XML со структурой БД var db = new Shed.DbSync.DataBase(OrigConnString); var xml = db.GetXml(); File.WriteAllText("DbStructure.xml", xml); } }
После запуска программы в файле DbStructure.xml видим следующее:
<?xml version="1.0"?> <DataBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Version>0</Version> <Tables> <Table Name="Orders" ObjectId="2137058649" ParentObjectId="0"> <Columns> <Column Name="Id"> <ColumnId>1</ColumnId> <Type>int</Type> <MaxLength>4</MaxLength> <IsNullable>false</IsNullable> <IsIdentity>true</IsIdentity> <IsComputed>false</IsComputed> </Column> <Column Name="OrderNumber"> <ColumnId>2</ColumnId> <Type>nvarchar</Type> <MaxLength>100</MaxLength> <IsNullable>true</IsNullable> <IsIdentity>false</IsIdentity> <IsComputed>false</IsComputed> </Column> <Column Name="OrderTime"> <ColumnId>3</ColumnId> <Type>datetime</Type> <MaxLength>8</MaxLength> <IsNullable>true</IsNullable> <IsIdentity>false</IsIdentity> <IsComputed>false</IsComputed> </Column> <Column Name="TotalCost"> <ColumnId>4</ColumnId> <Type>decimal</Type> <MaxLength>9</MaxLength> <IsNullable>false</IsNullable> <IsIdentity>false</IsIdentity> <IsComputed>false</IsComputed> </Column> </Columns> <Indexes> <Index Name="PK_Orders"> <IndexId>1</IndexId> <Type>CLUSTERED</Type> <IsUnique>true</IsUnique> <IsPrimaryKey>true</IsPrimaryKey> <IsUniqueConstraint>false</IsUniqueConstraint> <Columns> <IndexColumn> <TableColumnId>1</TableColumnId> <KeyOrdinal>1</KeyOrdinal> <IsDescendingKey>false</IsDescendingKey> </IndexColumn> </Columns> </Index> <Index Name="IX_Orders_OrderNumber"> <IndexId>2</IndexId> <Type>NONCLUSTERED</Type> <IsUnique>false</IsUnique> <IsPrimaryKey>false</IsPrimaryKey> <IsUniqueConstraint>false</IsUniqueConstraint> <Columns> <IndexColumn> <TableColumnId>2</TableColumnId> <KeyOrdinal>1</KeyOrdinal> <IsDescendingKey>false</IsDescendingKey> </IndexColumn> </Columns> </Index> </Indexes> <PrimaryKey Name="PK_Orders" ObjectId="5575058" ParentObjectId="2137058649"> <UniqueIndexId>1</UniqueIndexId> </PrimaryKey> <ForeignKeys /> <Defaults> <Default Name="DF_Orders_TotalCost" ObjectId="69575286" ParentObjectId="2137058649"> <ParentColumnId>4</ParentColumnId> <Definition>((0))</Definition> </Default> </Defaults> </Table> <Table Name="Details" ObjectId="85575343" ParentObjectId="0"> <Columns> <Column Name="Id"> <ColumnId>1</ColumnId> <Type>int</Type> <MaxLength>4</MaxLength> <IsNullable>false</IsNullable> <IsIdentity>true</IsIdentity> <IsComputed>false</IsComputed> </Column> <Column Name="Descript"> <ColumnId>2</ColumnId> <Type>nvarchar</Type> <MaxLength>300</MaxLength> <IsNullable>true</IsNullable> <IsIdentity>false</IsIdentity> <IsComputed>false</IsComputed> </Column> <Column Name="OrderId"> <ColumnId>3</ColumnId> <Type>int</Type> <MaxLength>4</MaxLength> <IsNullable>true</IsNullable> <IsIdentity>false</IsIdentity> <IsComputed>false</IsComputed> </Column> <Column Name="Cost"> <ColumnId>4</ColumnId> <Type>decimal</Type> <MaxLength>9</MaxLength> <IsNullable>false</IsNullable> <IsIdentity>false</IsIdentity> <IsComputed>false</IsComputed> </Column> </Columns> <Indexes> <Index Name="PK_Details"> <IndexId>1</IndexId> <Type>CLUSTERED</Type> <IsUnique>true</IsUnique> <IsPrimaryKey>true</IsPrimaryKey> <IsUniqueConstraint>false</IsUniqueConstraint> <Columns> <IndexColumn> <TableColumnId>1</TableColumnId> <KeyOrdinal>1</KeyOrdinal> <IsDescendingKey>false</IsDescendingKey> </IndexColumn> </Columns> </Index> </Indexes> <PrimaryKey Name="PK_Details" ObjectId="117575457" ParentObjectId="85575343"> <UniqueIndexId>1</UniqueIndexId> </PrimaryKey> <ForeignKeys> <ForeignKey Name="FK_Details_Orders" ObjectId="149575571" ParentObjectId="85575343"> <ReferenceTableId>2137058649</ReferenceTableId> <References> <Reference> <ColumnId>1</ColumnId> <ParentColumnId>3</ParentColumnId> <ReferenceColumnId>1</ReferenceColumnId> </Reference> </References> <DeleteAction>NO_ACTION</DeleteAction> <UpdateAction>NO_ACTION</UpdateAction> </ForeignKey> </ForeignKeys> <Defaults> <Default Name="DF_Details_Cost" ObjectId="101575400" ParentObjectId="85575343"> <ParentColumnId>4</ParentColumnId> <Definition>((0))</Definition> </Default> </Defaults> </Table> </Tables> <Views /> <ProgrammedObjects> <ProgObject Name="Details_Modify" ObjectId="165575628" ParentObjectId="0"> <Definition>CREATE TRIGGER [dbo].[Details_Modify] ON dbo.Details AFTER INSERT,UPDATE AS BEGIN UPDATE Orders SET TotalCost = s.Total FROM ( SELECT i.OrderId OId, SUM(d.Cost) Total FROM Details d JOIN inserted i ON d.OrderId=i.OrderId GROUP BY i.OrderId ) s WHERE Id=s.OId END</Definition> <Type>SQL_TRIGGER</Type> </ProgObject> <ProgObject Name="Details_Delete" ObjectId="181575685" ParentObjectId="0"> <Definition>CREATE TRIGGER [dbo].[Details_Delete] ON dbo.Details AFTER DELETE AS BEGIN UPDATE Orders SET TotalCost = s.Total FROM ( SELECT i.OrderId OId, SUM(d.Cost) Total FROM Details d JOIN deleted i ON d.OrderId=i.OrderId GROUP BY i.OrderId ) s WHERE Id=s.OId END</Definition> <Type>SQL_TRIGGER</Type> </ProgObject> </ProgrammedObjects> </DataBase>
Разворачивание/обновление структуры БД при помощи полученного XML.
Теперь научимся использовать полученный XML. Создаем еще одну пустую БД DbSyncSampleCopy, в код нашей консольной программы добавляем следующее:
class Program { private const string OrigConnString = "data source=.;initial catalog=DbSyncSample;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"; private const string TargetConnString = "data source=.;initial catalog=DbSyncSampleCopy;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"; static void Main(string[] args) { // получаем XML со структурой эталонной БД var dborig = new Shed.DbSync.DataBase(OrigConnString); var xml = dborig.GetXml(); File.WriteAllText("DbStructure.xml", xml); // если нужно предварительно очистить структуру целевой БД, используем // Shed.DbSync.DataBase.ClearDb(TargetConnString); // обновляем структуру целевой БД var dbcopy = Shed.DbSync.DataBase.CreateFromXml(xml); dbcopy.UpdateDb(TargetConnString); // на самом деле можно обойтись одной строкой: // dborig.UpdateDb(TargetConnString); // dbcopy создаем только для демонстрации создания объекта базы из XML } }
После запуска программы можно убедиться, что в DbSyncSampleCopy появилась структура таблиц, идентичная эталонной БД. Эксперименты с изменением эталонной структуры и обновлением целевой оставляю читателю.
В сценариях тестирования может понадобиться создание тестовой БД каждый раз с нуля. В этом случае будет полезно использовать функцию Shed.DbSync.DataBase.ClearDb(string connString)
Автоматическое слежение за структурой БД.
Слежение за структурой оформим в виде отдельной функции, которую следует вызывать при старте/рестарте приложения либо в другом месте по желанию разработчика.
static void SyncDb() { // автоматическое слежение за структурой БД Shed.DbSync.DataBase.Syncronize(OrigConnString, @"Struct\DbStructure.xml", // путь к файлу структуры @"Struct\Logs", // путь к папке логов синхронизации @"Struct\update_script.sql" // (необяз.) в случае определения этого параметра // в него будет записан скрипт, сгенерированный // для обновления БД ); }
Слежение производится при помощи параметра (тега) Version в XML. Сценарий использования процедуры такой:
- Назначить версию БД. В Microsoft SqlServer Management Studio на узле нужной базы данных правой кнопкой выбрать Properties.
- Далее Extended Properties и в таблице свойств добавить свойство Version со значением 1. При каждой последующей модификации структуры это свойство следует наращивать на 1.
- При запуске приложения, если XML-файла нет или его версия меньше, чем у БД, он создается.
- Если версия XML-файла больше, чем у БД, генерируется скрипт на обновление БД и исполняется.
- Если в процессе исполнения скрипта возникли ошибки, все изменения откатываются.
- Результаты синхронизации пишутся в log-файл, создаваемый в папке, указанной параметром logDitPath.
- Если указан параметр SqlScriptPath, создается файл со скриптом из п.4.
Эксперименты оставляю читателям. Успехов вам!
