Clever Geek Handbook

アプリケーション間のデータベース構造の同期

データベースを使用してアプリケーションを開発した経験のあるすべての人は、アプリケーションのデプロイおよび更新時にデータベース構造を更新する問題に遭遇したに違いありません。



最も一般的に使用される単純なアプローチは、SQLスクリプトのセットを作成して、データベース構造をバージョンごとに変更することです。 もちろん、 Red gateなどの強力なツールがありますが、最初は無料ではなく、2番目は完全に自動化された更新の問題を解決しません。







Hibernate ORMに最初に登場し、Linqに実装された移行テクノロジは非常に便利で便利ですが、既存のプロジェクトでは非常に難しい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" // (.)      //     ,  //    ); }


追跡は、XMLのVersionパラメーター（タグ）を使用して行われます。 手順を使用するシナリオは次のとおりです。







  1. データベースのバージョンを割り当てます。 Microsoft SqlServer Management Studioで、必要なデータベースのノードで、[プロパティ]を右クリックします。
  2. 次に、拡張プロパティおよびプロパティテーブルで、バージョンプロパティに値1を追加します。その後の構造の変更ごとに、このプロパティを1ずつ増やす必要があります。
  3. アプリケーションの起動時に、XMLファイルがないか、そのバージョンがデータベースよりも小さい場合、作成されます。
  4. XMLファイルのバージョンがデータベースのバージョンよりも大きい場合、データベースを更新するスクリプトが生成されて実行されます。
  5. スクリプトの実行中にエラーが発生した場合、すべての変更がロールバックされます。
  6. 同期結果は、logDitPathパラメーターで指定されたフォルダーに作成されたログファイルに書き込まれます。
  7. SqlScriptPathパラメーターが指定されている場合、手順4のスクリプトを含むファイルが作成されます。







実験は読者に任せます。 あなたに幸運を！







More articles:


All Articles