Каждый, кто когда-либо разрабатывал приложения, использующие базу данных, наверняка сталкивался с проблемой обновления структуры БД при разворачивании и обновлении приложения.
Чаще всего используется простой подход — создание набора 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.
Эксперименты оставляю читателям. Успехов вам!
Комментарии (7)
corr256
03.06.2017 20:07+1http://www.c-sharpcorner.com/UploadFile/raj1979/how-to-create-database-project-in-visual-studio-2015/
fortunato
03.06.2017 20:43Интересно, а есть что-нибудь подобное для python/django.
Для переноса структуры из старой базы в модели djangoHellpain
05.06.2017 09:01./manage.py inspectdb
Introspects the database tables in the database and outputs a Django model module (a models.py file) to standard output. You may choose what tables to inspect by passing their names as arguments.
Use this if you have a legacy database with which you’d like to use Django. The script will inspect the database and create a model for each table within it.
QtRoS
03.06.2017 23:12+2SQL Compare стоит своих денег. Удивительно хорошо работает в разных кейсах — сравнение БД<->БД, БД<->Скрипты, Скрипты<->Скрипты. И command line тулза, и SDK есть.
P.S. Это не реклама, просто желаю Вам добра и меньше велосипедов :)
osmirnov
04.06.2017 03:19+2Только недавно читал статью по теме Alexander Tsvetkov — 5 Ways To Manage Database Schema Changes in 2017 (in .NET)
khaale
А чем не устроил стандартный data-tier application в Visual Studio? DACPAC может обновлять существующую БД при деплое.