Синхронизация структуры базы данных между приложениями

в 13:56, , рубрики: .net, database, dbsync, MS SQL, shed, shed.dbsync, sql, syncronize, Программирование, синхронизация баз данных

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

Чаще всего используется простой подход — создание набора 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, 
                @"StructDbStructure.xml",      //  путь к файлу структуры
                @"StructLogs",                 //  путь к папке логов синхронизации
                @"Structupdate_script.sql"     //  (необяз.) в случае определения этого параметра
                                                //  в него будет записан скрипт, сгенерированный 
                                                //  для обновления БД
            );
        }

Слежение производится при помощи параметра (тега) Version в XML. Сценарий использования процедуры такой:

  1. Назначить версию БД. В Microsoft SqlServer Management Studio на узле нужной базы данных правой кнопкой выбрать Properties.
  2. Далее Extended Properties и в таблице свойств добавить свойство Version со значением 1. При каждой последующей модификации структуры это свойство следует наращивать на 1.
  3. При запуске приложения, если XML-файла нет или его версия меньше, чем у БД, он создается.
  4. Если версия XML-файла больше, чем у БД, генерируется скрипт на обновление БД и исполняется.
  5. Если в процессе исполнения скрипта возникли ошибки, все изменения откатываются.
  6. Результаты синхронизации пишутся в log-файл, создаваемый в папке, указанной параметром logDitPath.
  7. Если указан параметр SqlScriptPath, создается файл со скриптом из п.4.

Эксперименты оставляю читателям. Успехов вам!

Автор: chiefivs

Источник

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

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