MS SQL Serverデータベースのバージョン管理の簡単なアプローチ

MS SQL Serverデータベースのバージョン管理の簡単なアプローチ

まえがき

多くの場合、アプリケーションでは、データレベルだけでなく、ストアドプロシージャ、トリガー、関数を介してこのデータへのアクセスを提供する独自のAPIの作成に基づいて、開発者に非常に大きな機能を提供する最新のデータベースを使用する必要があります。 明らかに、この構造全体では、状況に応じて、いくつかの変更が必要になる場合があります。 そして、最も無害なケースでは、開発者が1つのクライアントと1つの変化するデータベース（たとえば、小規模企業）を扱う場合、更新プロセスはシンプルに見えます-構造に必要な変更を加え、SQL Examiner、新旧の特別なユーティリティの助けを借りて比較します生成されたsqlスクリプトをバージョン管理し、既存のデータベースにロールします。 前述のケースでわかるように、データベース構造が更新されるたびにデータ移行が発生します。 しかし、残念なことに、記載されている状況は非常にまれであり、より頻繁に-顧客および開発者向けの製品の対応するデータベースは、数百の、あるいはそれ以上ではありません。 したがって、データベースの通常のライフサイクルには、バージョン管理システムが必要です（Subversionなどのソースバージョン管理システムと混同しないでください）。

アプローチ

このシステムの目的をよりよく理解するために、次の例を考えてみましょう：特定のデスクトップアプリケーションがあります-顧客の要求に応じて、このプログラムは次のデータを保存する必要があります：「姓」、「名」、「ミドルネーム」、役職、およびこのプログラムがインストールされます会社の各オフィスには、市内に散らばっています。 後者の事実は、将来、ソフトウェアバージョンの更新が同期的に行われない状況が発生する可能性があることを示唆しています。たとえば、プログラムがバージョン10をリリースし、オフィスによっては最初のバージョンがまだインストールされる場合があります。しかし、すぐに10にアップグレードする場合、この更新を実行する管理者は10個すべての更新をインストールする必要はありませんが、10個の更新を一度に提供する必要があります。これにより、構造全体が個別に更新され、新しいアプリケーションのデータが移行されます。 したがって、データベースバージョン管理システムの主な要件を策定します。

1. 移行時にデータを維持する必要があります
2. バージョンに関係なくデータを移行する必要があります
3. 移行ツールはできるだけシンプルにする必要があります。

移行コマンドのタスクの最も簡単なバージョンは、任意の言語で移行スクリプトを開発することです。この例では、Windows用の標準のbatファイルが使用されます。
次に、エンティティを保存するためのルールを作成する必要があります。

1. データベースを作成する、または既存の構造を変更するDML定義を含むスクリプトは、名前がalter.sqlである同じファイルに存在する必要があります。
2. 各ストアドプロシージャには、作成（最初のバージョン）と変更（後続）の両方で独自のファイルが必要です。
3. データベース内の各エンティティに対して、可能な限り次のルールを使用できます。[アクションエンティティ]ルールに従って名前を付ける必要があるエンティティを選択、追加、更新、削除するための4つのプロシージャを作成します。 たとえば、本の本質を持っている場合-Book、次にそれにアクセスするために、SelectBook、InsertBook、UpdateBook、DeleteBookの4つのプロシージャを作成します。 新しいルールの導入は許可されていますが、可能な限り統一する必要があります。
4. 同じエンティティを操作する手順は、エンティティの名前が付いた1つのフォルダーにグループ化する必要があります。
5. バージョンごとに、実装するバージョンの番号を持つフォルダーを作成し、ステップ4のフォルダーと、ステップ1のデータベース構造を初期化または変更するためのスクリプトを含める必要があります。

上記のすべてのルールに従うことで、厳密なカタログ化を実現できます。これにより、必要なバージョン管理が可能になります。
例に戻りましょう。 最初のバージョンでは、FirstName、MiddleName、LastNameの3つのフィールドを持つ1つのPersonテーブルがあります。 まず、Versionフォルダーを作成します-バージョンが含まれます。バージョン1になり、その下にPersonフォルダーと初期化スクリプトが含まれます。

CREATE TABLE Person
(
FirstName varchar (50),
MiddleName varchar (50),
LastName varchar (50)
)

* This source code was highlighted with Source Code Highlighter .

次に、パラグラフ（3）のルールに従って、4つのストアドプロシージャを個人のフォルダーに作成し、それぞれにSelectPerson、InsertPerson、UpdatePerson、DeletePersonという名前を付け、Personフォルダー内に配置します。取得：

基本展開システムの作成

すべてのファイルとディレクトリが特定の構造で収集されたので、データベースを展開するメカニズムの作成を開始できます。 MS SQLサーバーには、コマンドラインからアクセスするためのユーティリティがあります。sqlcmd.exeは、sqlコマンドプロセッサとして使用するものです。 実行時には、このユーティリティへのパスがPath環境変数に登録されていると想定されます。 まず、sqlcmd.exeに関するいくつかの情報：

1. このユーティリティは、SQLコマンドを含むファイルを実行できます。これは、-i filenameスイッチを使用して行われます
2. また、SQLコードで使用できる変数を定義する方法も知っています。変数は–v param = valueキーを使用して定義され、既にコード内で$（param）構文を使用してアクセスでき、実行可能プロセッサはこれらのトークンを値に単純に置き換えます-vオプションを使用した呼び出しで定義されたもの
3. このユーティリティを使用すると、SQLスクリプト内の他のSQLスクリプトを呼び出すことができます。これは、ディレクティブを使用して行います。r filename

示された機能は、私たちの目的のために柔軟なシステムを作成するのに役立ちます。 まず、データベース展開アルゴリズムを定義します。

1. データベースが存在するかどうかを判断し、存在しない場合は、新しいデータベースを作成します。
2. データのバックアップを作成します。
3. バージョン管理用のデータベースの初期構成が作成されているかどうかを確認し、作成されていない場合は作成します。
4. 現在のバージョンを確認し、移行スクリプトを呼び出します。

データベースが現在のバージョンを保存できるように（手順3の場合）、このため、このバージョンが保存されるフィールドを持つテーブルを作成する必要があります。

CREATE TABLE Settings
(
DbVersion int
)
go
INSERT INTO Settings(DbVersion) VALUES (1)
go

* This source code was highlighted with Source Code Highlighter .

データベースのバージョンを変更すると、作成されたテーブルのDbVersionフィールドの対応する値が変更されます。
次に、展開スクリプトが受け取るパラメーターを決定します。

1. データベース名は変数workdbnameです。
2. SQLサービスのインスタンス名。
3. データベースファイルへのパスは、databasepath変数です。
4. バックアップファイルへのパスは、backuppath変数です。

準備段階

ここでは、ベースの存在をチェックして新しいベースを作成するロジックを記述する必要があります。 ゼロ除算などのスクリプトの実行中に重大なエラーが発生した場合、sqlcmd.exeユーティリティが水曜日に1のエラーコードを返すことをすぐに言及する価値があります。これは、batスクリプトの実行時に使用できます。 上記の検証のために、CheckDbExists.sqlファイルに次のSQLコマンドを作成します。

use master;
IF NOT EXISTS ( SELECT * FROM sysdatabases WHERE [Name] = $(workdbname)')
BEGIN
SELECT 1/0;
END

* This source code was highlighted with Source Code Highlighter .

次に、deploy.batファイルで最初のチェックを整理します。

"sqlcmd.exe" -S %2 -V 1 -v workdbname=%1 -i CheckDbExists.sql
IF ERRORLEVEL 1 GOTO :CREATENEW

データベースが存在しない場合、ユーティリティは完了コード1を実行環境に返し、ロジックはデータベースの物理的な作成を実行します。 これを行うには、次の内容のsqlスクリプトを作成します。

use master;
go
CREATE DATABASE $(workdbname) ON PRIMARY
(Name = N '$(workdbname)' , FILENAME = N '$(databasepath)' );
go
use $(workdbname);
go

CREATE TABLE Settings
(
DbVersion int
)
go
INSERT INTO Settings(DbVersion) VALUES (1)
Go

* This source code was highlighted with Source Code Highlighter .

ご覧のとおり、スクリプトはsqlcmdが呼び出されたときに渡す必要がある2つの変数を使用します。

"sqlcmd.exe" -S %2 -v workdbname=%1 -v databasepath=%3 -i InitDatabase.sql

データベースの存在を確認するステップでは、データベースが既に存在する可能性があるため、更新のためにデータベースを準備する必要があります。つまり、データのバックアップを強制するには、次のスクリプトを呼び出してこれを実行できます。

BACKUP DATABASE $(workdbname) TO DISK = N '$(backuppath)' WITH INIT,
SKIP, NOREWIND, NOUNLOAD

* This source code was highlighted with Source Code Highlighter .

生成されたbatファイルから：
"sqlcmd.exe" -S %2 -v workdbname=%1 -v backuppath=%4 -i BackupDatabase.sql

これで、システムで最も重要なこと、つまり現在のバージョンを確認するときが来ました。これは、特定のバージョンの簡単な確認によって行われます。 どういうわけか、バージョン番号を「外の世界」に通知する必要があるため、ここでもゼロ除算エラーを適用し、CheckVersion.sqlファイルでこのコードを発行します。

use $(workdbname);
go
SELECT [DbVersion]/($(checkedVersion) - [DbVersion]) FROM Settings
Go

* This source code was highlighted with Source Code Highlighter .

そして、最初のバージョンの検証を伴う呼び出しを作成します。
"sqlcmd.exe" -S %2 -V 1 -v workdbname=%1 -v checkedVersion=1 -i CheckVersion.sql

最初の呼び出しでは、予想どおりゼロ除算エラーが発生し、コード1がランタイムに戻ります。したがって、データベースの最初のバージョンの構造を初期化するために必要なすべての変更を行う必要があるスクリプトを呼び出すことができます。
IF ERRORLEVEL 1 "sqlcmd.exe" -S %2 -v workdbname=%1 -i Version\1\alter.sql
各バージョンの呼び出されたalter.sqlファイルは、次の原則に従って機能します。

1. 変数workdbnameで名前が渡されたデータベースのコンテキストを確立する必要があります。
2. ディレクティブを使用して呼び出します。r sql DMLコードを含むすべての必要なファイル。

この例の最初のバージョンでは、このファイルのスクリプトは次の行で構成され、最後に必須のバージョン更新があります。

use $(workdbname);
go
:r Version\1\script. sql
go
:r Version\1\Person\DeletePerson. sql
go
:r Version\1\Person\InsertPerson. sql
go
:r Version\1\Person\SelectPerson. sql
go
:r Version\1\Person\UpdatePerson. sql
go
UPDATE Settings SET DbVersion = 2
Go

* This source code was highlighted with Source Code Highlighter .

これで、最初のバージョンで作業するためのすべてのセットアップが完了し、ファイル構造は画像に示された形式をとるはずです。

新しいバージョンを作成する

次のリリースの時期が来たら、作業中に蓄積されたすべての変更を、メインのバージョンブランチのサブフォルダーに収集する必要があります。 わかりやすくするために、データベースの次のバージョンに新しい都市のテーブルの導入と、人と生まれた都市の関係の作成の変更が含まれると仮定します。script.sqlという名前の構造移行スクリプトは次のようになります。

CREATE TABLE City
(
CityId int identity NOT NULL ,
Title varchar (255) NOT NULL ,
PRIMARY KEY (CityID)
)
go
ALTER TABLE Person ADD CityId int
go
ALTER TABLE Person ADD FOREIGN KEY (CityID) REFERENCES City (CityID)
go

* This source code was highlighted with Source Code Highlighter .

同時に、Cityエンティティを操作するための4つの手順を作成し、Personエンティティのストレージを変更する必要があります。 次に、alter.sqlファイルに更新スクリプトを記述します。

use $(workdbname);
go
:r Version\2\script. sql
go
:r Version\2\Person\InsertPerson. sql
go
:r Version\2\Person\SelectPerson. sql
go
:r Version\2\Person\UpdatePerson. sql
go
:r Version\2\City\SelectCity. sql
go
:r Version\2\City\InsertCity. sql
go
:r Version\2\City\UpdateCity. sql
go
:r Version\2\City\DeleteCity. sql
go
UPDATE Settings SET DbVersion = 3
Go

* This source code was highlighted with Source Code Highlighter .

完了したら、メインのdeploy.batのバージョン検証セクションに次の行を追加します。
"sqlcmd.exe" -S %2 -V 1 -v workdbname=%1 -v checkedVersion=2 -i CheckVersion.sql
IF ERRORLEVEL 1 "sqlcmd.exe" -S %2 -v workdbname=%1 -i Version\2\alter.sql

2番目のバージョンでは、ファイルとフォルダーの構造は次の形式になります。

おわりに

説明したアプローチは、MS SQLデータベースだけでなく、他のDBMSにも適用できます。 たとえば、このメソッドは、DBMSがPostgresであるプロジェクトのデータスキームのバージョン管理システムを作成するプロセスで開発されました。 すべてのソーススクリプトはここから入手できます。