データベースのバージョンの制御とバージョン間の移行の問題は、Habré（ 1、2、3など）とインターネット（主に英語）の両方で繰り返し発生しています。

この記事の最初のセクションでは、データベース構造に変更を加える際にプログラマーのチームで発生する主な問題について説明します。 2番目のセクションでは、開発中にデータベース構造の変更を保存および維持できるフォームへの主な一般的なアプローチを強調しました。

用語

データベースは、すべてのデータベースオブジェクト（テーブル、プロシージャ、トリガーなど）、静的データ（ルックアップテーブルに格納された不変データ）、およびユーザーデータ（アプリケーションの処理中に変更される）のコレクションです。

データベース構造 -すべてのデータベースオブジェクトと静的データのセット。 ユーザーデータは、データベース構造の概念には含まれません。

データベースのバージョン -データベース構造の特定の状態。 通常、バージョンには、アプリケーションのバージョン番号に関連付けられた番号があります。

このコンテキストでの移行とは、データベース構造をあるバージョンから別のバージョン（通常は新しいバージョン）に更新することです。

この意味で、移行という用語は多くのソースで使用されているようです（特に、Ruby on Railsの一部であるActive Record gemからの移行が貢献しています）。 ただし、この用語を使用すると、あいまいさが発生します。コンテキストを知らない人は、データベースをあるDBMSから別のDBMSに転送すること（MySQL => Oracle）、またはクラスターノード間でプロセス/データを移行することの問題であると考えるでしょう。 したがって、コンテキストが明確でない場合は、より正確な用語、 バージョン付きデータベース移行を使用することを提案します。

なぜこれが必要なのですか？

データベースとアプリケーションのバージョンの非同期の問題に既に遭遇した開発者は、このセクションをスキップできます。 ここで、アプリケーションとデータベースのバージョンのパリティを維持する必要がある理由と、一般的な問題が発生することを思い出します。

データベースのバージョンはアプリケーションのバージョンと一致する必要があります

したがって、次の状況を想像してください。複数のプログラマーのチームが、データベースを積極的に使用するアプリケーションを開発しています。 時々、アプリケーションは実稼働環境に配信されます。たとえば、これはWebサーバーにデプロイされるWebサイトです。
アプリケーションコードを記述するプロセスでは、プログラマーはデータベースの構造とそれに格納されているデータを変更する必要があります。 簡単な例を挙げましょう。テーブルの1つにnull不可の文字列フィールドがあるとします。 このフィールドには常にデータがあるとは限りません。その場合、空の文字列がそこに保存されます。 ある時点で、空の文字列を格納することは、場合によっては意味的に正しくないと判断し（ 1、2を参照）、正しくNULLを格納します。 これを実装するには、次の手順が必要です。

1.フィールドタイプをヌル可能に変更します。

ALTER myTable CHANGE COLUMN myField myField VARCHAR (255) NULL DEFAULT NULL ;

2.このテーブルの本番データベースにはすでに空の行が存在する可能性があるため、意思決定を行い、情報の欠如として扱います。 したがって、それらをNULLに置き換える必要があります。

UPDATE myTable SET myField = NULL WHERE myField = '' ;

3.アプリケーションコードを変更して、データベースからこのフィールドに格納されたデータを受信するときに、NULLに適切に応答するようにします。 また、このフィールドへの書き込みには、空の行の代わりにNULLが必要になります。

ポイント3から、アプリケーションとデータベースが1つの全体の不可分な部分であることがわかります。 これは、アプリケーションの新しいバージョンが実稼働環境に配信されると、データベースのバージョンも更新する必要があることを意味します。そうしないと、アプリケーションは単に正しく動作できなくなります。 この例では、アプリケーションのみが新しいバージョンに更新されると、ある時点でNULLがNULL不可フィールドに挿入されますが、これは明らかなエラーです。

したがって、 アプリケーションのバージョンを更新するには、正しいバージョンのデータベース移行が必要です。

簡単ですか？

データベースとアプリケーションのバージョンのパリティが必要であることに気付いたら、目的のバージョンへのデータベースの移行が常に正しく実行されることを確認する必要があります。 しかし、ここでの問題は何ですか？ 結局のところ、一見、複雑なものは何もありません！

ここで再び生きた例に戻ります。 開発プロセスのプログラマが、データベースの構造とデータの変更を、SQLクエリ（ DDLとDMLクエリの両方）の形式で別のファイルに書き込むとします 。 そして、アプリケーションの最新バージョンをデプロイするたびに、データベースを最新バージョンに同時に更新し、同じSQLファイルからクエリを実行します...しかし、データベースを最新バージョンに更新するのはどのバージョンですか？ 「過去から」？ しかし、前のバージョン（2か月前にリリースされたもの）が正確に何であったかをよく覚えていますか？ そうでない場合、どのように更新しますか？ 実際、構造とデータの状態に関する正確な情報がないと、正しく移行することはできません。すでに実行されたクエリを誤って実行すると、データの損失や整合性の侵害につながる可能性があります。
簡単な例は、パスワードをMD5合計で置き換えることです。 このようなリクエストを再実行すると、データはバックアップからのみ復元できます。 とにかく、繰り返し実行されるUPDATE 、 DELETE 、さらにはINSERTでさえ、非常に望ましくない結果を招く可能性があります。 タイミングの悪いTRUNCATEおよびDROPについては言うまでもありません（ただし、そのような場合ははるかに少ないです）。
ちなみに、この観点から見ると、不十分な実行は、過剰な実行よりもアプリケーションの操作性への危険に劣りません。

したがって、バージョン管理された移行のプロセスでは、すべての要求を一度だけ実行し、さらに正しい順序で実行する必要があると結論付けることができます 。 一部の変更は他の変更に依存する可能性があるため、一貫性が重要です（null許容フィールドの例のように）。

バージョン移行の一般原則

前のセクションでは、バージョン管理された移行プロセスに必要なものを示す重要な基準を特定しました。 これは：

• 各変更の1回限りの実行（SQLクエリ）;
• 厳密に事前定義された変更の順序。

次に、移行の作成と保存のプロセスから何を要求するかを理解するために、より実用的な基準を強調します。 私の意見では、ほとんどの開発チームにとって重要です：

• データベースの任意のバージョンを任意の（通常は最新の）バージョンに更新できるようにします。
• 任意の2つのバージョン間の移行を実装する一連のSQLクエリをできるだけ迅速かつ簡単に受信できるようにします。
• そのため、常に最新バージョンの構造を持つデータベースをゼロから作成できます。 これは、開発プロセスとテストプロセス、および新しい運用サーバーを展開するときに非常に役立ちます。
• 異なるブランチでの作業の場合、その後のマージで、データベースファイルの手動編集が最小限に抑えられます。
• データベースを以前のバージョンにロールバックするのは、新しいバージョンに更新するのと同じくらい簡単でした。

移行のベース

判明したように、ほとんどのアプローチには共通の原則があります。ベースライン、つまり開始するデータベースの特定の参照状態が必要です。 この概念は、Scott Allen のVersioning Databases-The Baselineに詳しく説明されています。

簡単に言えば、基盤とは、ベースとして受け入れられるバージョンのデータベース構造のダンプです。 基盤が手元にあれば、データベースを最初から作成することが常に可能になります。 開発プロセス中に作成されたすべての移行をこのデータベースに適用した後、最新バージョンの構造を持つデータベースを取得します。

次に、バージョン管理されたデータベースの移行を整理するための3つのアプローチを検討します。

増分変更方法

この方法は、同じScott Allenの記事「データベースのバージョン管理-スクリプトの変更」で詳しく説明されています。 同様のアプローチは、 Michael Baylon による記事「SQLスクリプトの管理と継続的統合」にも記載されています 。

ファイル構造

この場合、移行ファイルのあるフォルダーがどのように見えるかの例：

Database
|- Baseline.sql
|- 0001. 03 .01.sql
|- 0002. 03 .01.sql
|- 0003. 03 .01.sql
|- 0004. 03 .02.sql
|- 0005. 03 .02.sql
|- 0006. 03 .02.sql
'- 0007. 03 .02.sql

この例では、フォルダーにはバージョン03の開発中に作成されたすべてのファイルが格納されます。 ただし、フォルダーはアプリケーションのすべてのバージョンに共通する場合があります。

いずれにしても、そのようなフォルダーに表示される最初のファイルはベース （Baseline.sql）です。 その後、データベースの変更は、 [ ].[].[].sqlという形式の名前を持つ新しい移行ファイルの形式でリポジトリに送信されます。

実際、この例では、ファイル名にデータベースの完全なバージョン番号が含まれています。 つまり、名前が0006 .03.02.sqlの移行ファイル0006 .03.02.sql 、データベースはバージョン03.02. 0005対応する状態から更新されます03.02. 0005 03.02. 0005からバージョン03.02. 0006 03.02. 0006

バージョン履歴保存

次のステップは、データベースにすべての変更の履歴を保存する特別なテーブルをデータベースに追加することです。

CREATE TABLE MigrationHistory
(
Id INT ,
MajorVersion VARCHAR (2),
MinorVersion VARCHAR (2),
FileNumber VARCHAR (4),
Comment VARCHAR (255),
DateApplied DATETIME ,

PRIMARY KEY (Id)
)


これは、テーブルがどのように見えるかの一例にすぎません。 必要に応じて、簡素化と補足の両方を行うことができます。

Baseline.sqlファイルで、このテーブルに最初のレコードを追加する必要があります。

INSERT INTO
MigrationHistory ( MajorVersion, MinorVersion, FileNumber, Comment, DateApplied )
VALUES ( '03' , '01' , '0000' , 'Baseline' , NOW() )


各移行ファイルが完了すると、すべての移行データを含むエントリがこのテーブルに入力されます。
データベースの現在のバージョンは、最大日付のレコードから取得できます。

自動的に移行する

このアプローチの最後の仕上げは、データベースを現在のバージョンから最新のバージョンに更新するプログラム/スクリプトです。

データベースの移行を自動的に実行するのは非常に簡単です。なぜなら、 最後に実行された移行の数は、MigrationHistoryテーブルから取得できます。その後、より大きな番号を持つすべてのファイルを適用するためだけに残ります。 ファイルを番号順に並べ替えることができるため、移行の順序に問題はありません。

このようなスクリプトには、完了した移行のレコードをMigrationHistoryテーブルに追加するタスクもあります。

さらに便利な点として、このようなスクリプトは、最初にデータベースの現在のバージョンを最初から作成し、最初にデータベースにデータベースを展開してから、最新バージョンへの標準的な移行操作を実行できる場合があります。

長所、短所、結論

最新バージョンへの迅速かつ簡単な移行。
バージョン番号付けメカニズム。 現在のバージョン番号はデータベースに直接保存されます。
最大限の利便性を得るには、移行用の自動化ツールが必要です。
データベース構造にコメントを追加するのは不便です。 Baseline.sqlに追加すると、次のバージョンでは表示されなくなります。 構造の新しいバージョンのダンプとして、ベースは最初から再び生成されます。 さらに、そのようなコメントはすぐに時代遅れになります。
リポジトリのいくつかのブランチでは、並行開発プロセス中に問題があります。 移行ファイルの番号付けは連続しているため、異なるDDL / DML要求を持つファイルは、異なるブランチの同じ番号の下に表示される場合があります。 そのため、ブランチをマージするときは、ファイルとそのシーケンスを手動で編集するか、新しい「マージされた」ブランチで、両方のブランチからの変更を考慮した新しいBaseline.sqlで開始する必要があります。

さまざまな形式のこの方法は非常に広く普及しています。 さらに、プロジェクトのニーズに合わせて簡単に変更および変更できます。
インターネットでは、インクリメンタル移行用の既成のスクリプトオプションを見つけて、プロジェクトに埋め込むことができます。

べき等変化法

この方法は、Phil Hack のINFORMATION_SCHEMAビューを使用した防弾SQL変更スクリプトで説明されています。 StackOverflowのこの質問に対する回答でも、同様のアプローチが説明されています。

べき等性とは、オブジェクトが再び変更しようとしたときに、オブジェクトのプロパティが変更されないままであると理解されることです。
テーマは「Friends」の面白いシーンを思い出します:)

このアプローチの主なアイデアは、データベース上で複数回実行できるように移行ファイルを作成することです。 SQLコマンドのいずれかを最初に実行しようとすると、変更が適用されます。 その後のすべての試行では、何も起こりません。

このアイデアは、例を使用して理解するのが最も簡単です。 データベースに新しいテーブルを追加する必要があるとします。 クエリが既に存在する場合、クエリの実行時にエラーが発生しないようにする場合、MySQLには次の目的のための短い構文があります。

CREATE TABLE IF NOT EXISTS myTable
(
id INT (10) NOT NULL ,
myField VARCHAR (255) NULL ,
PRIMARY KEY (id)
);

IF NOT EXISTSキーワードのおかげで、MySQLは同じ名前のテーブルがまだ存在しない場合にのみテーブルを作成しようとします。 ただし、この構文はすべてのDBMSで使用できるわけではありません。 また、MySQLでもすべてのコマンドに使用できるわけではありません。 したがって、より普遍的な方法を検討します。

IF NOT EXISTS
(
SELECT *
FROM information_schema.tables
WHERE table_name = 'myTable'
AND table_schema = 'myDb'
)
THEN
CREATE TABLE myTable
(
id INT (10) NOT NULL ,
myField VARCHAR (255) NULL ,
PRIMARY KEY (id)
);
END IF ;

最後の例では、テーブルmyTable myDbという名前のデータベースにmyTableかどうかを確認するクエリが条件式パラメーターの役割を果たします。 そして、テーブルが欠落している場合にのみ、実際に作成されます。 したがって、上記のクエリはべき等です。

MySQLでは、何らかの理由で、条件式内でDDLクエリを実行することが禁止されていることに注意してください。 ただし、この禁止事項は簡単に回避できます。このような要求をすべてストアドプロシージャの本文に含めるだけです。

DELIMITER $$

CREATE PROCEDURE sp_tmp() BEGIN

IF NOT EXISTS
(
--
-- .
--
)
THEN
--
-- , .
--
END IF ;

END ;
$$

DELIMITER;

CALL sp_tmp();

DROP PROCEDURE sp_tmp;

information_schemaとはどのような鳥ですか？

information_schemaという名前のデータベースにある特別なシステムテーブルから、データベース構造に関する完全な情報を取得できます。 このデータベースとそのテーブルはSQL-92標準の一部であるため、このメソッドは最新のDBMSで使用できます。 前の例では、すべてのテーブルに関するデータを格納するinformation_schema.tablesテーブルを使用していinformation_schema.tables 。 同様に、テーブル、ストアドプロシージャ、トリガー、スキーマ、および実際にはデータベース構造内の他のオブジェクトのフィールドの存在とメタデータを確認できます。

それらの目的に関する詳細な情報があるテーブルの完全なリストは標準のテキストで見つけることができます。 短いリストは、前述のPhil Hackの記事で見ることができます。 しかし、最も簡単な方法は、もちろん、動作しているデータベースサーバーでこのデータベースを開いて、その動作を確認することです。

使用例

したがって、べき等のSQLクエリを作成する方法を知っています。 次に、このアプローチを実際に使用する方法を検討します。

この場合、sqlファイルのあるフォルダーがどのように見えるかの例：

Database
|- 3.01
| |- Baseline.sql
| '- Changes.sql
|
'- 3.02
|- Baseline.sql
'- Changes.sql

この例では、データベースのマイナーバージョンごとに個別のフォルダーが作成されます。 新しいフォルダーを作成するたびに、ベースラインが生成され、Baseline.sqlに記録されます。 次に、開発プロセス中に、すべての必要な変更が、べき等クエリの形式でChanges.sqlファイルに書き込まれます。

さまざまな時期の開発中に、プログラマーがデータベースに次の変更を加える必要があるとします。
a）テーブルmyTableを作成します。
b）新しいフィールドフィールドを追加します。
c）myTableテーブルにデータを追加します。

3つの変更はすべて、繰り返されないように記述されています。 その結果、データベースがどの中間状態にあるかに関係なく、Changes.sqlファイルが実行されると、常に最新バージョンに移行されます。

たとえば、開発者の1人がデータベースのローカルコピーにmyTableテーブルを作成し、一般コードリポジトリに格納されているChanges.sqlファイルに変更a）を記録し、しばらく忘れていました。 これで、彼がローカルデータベースでこのファイルを実行すると、変更a）は無視され、変更b）とc）が適用されます。

長所、短所、結論

中間バージョンから最新バージョンへの非常に便利な移行-データベース（Changes.sql）で1つのファイルを実行するだけです。
データが失われる可能性のある状況は、これを監視する必要があります。 たとえば、テーブルを削除してから、同じ名前の別のテーブルを作成します。 削除中に名前のみがチェックされる場合、一度実行されたという事実にもかかわらず、スクリプトが実行されるたびに両方の操作（削除と作成）が発生します。
変更がi等であるためには、それらを書くのにより多くの時間（およびコード）を費やす必要があります。

データベースを最新バージョンに更新するのは非常に簡単であり、手動で行うことができるため、多くの運用サーバーがあり、頻繁に更新する必要がある場合、この方法は非常に優れています。

データベースの構造をソースコードと比較する方法

残念ながら、このアプローチに関する記事は見つかりませんでした。 もしあれば、既存の記事へのリンクに感謝します。 UPD： Absentは自分の記事で 、自己作成のdiffユーティリティを使用して同様のアプローチを実装した経験について語っています。

このメソッドの主な考え方は、ヘッダーに反映されています。データベース構造は、PHP、C＃、またはHTMLコードと同じソースコードです。 したがって、移行ファイル（データベース構造を変更するクエリ）をコードリポジトリに保存する代わりに、現在のデータベース構造を宣言形式で保存するだけで済みます。

実装例

例を簡単にするために、リポジトリの各リビジョンには常にCreateDatabase.sqlという1つのSQLファイルのみがあると想定しています。 かっこ内では、ソースコードと同様に、さらに進んで、各データベースオブジェクトの構造を個別のファイルに格納できることに注意してください。 また、構造は、DBMSでサポートされているXMLまたはその他の形式で保存できます。

CreateDatabase.sqlファイルには、データベース全体を最初から作成するCREATE TABLE 、 CREATE PROCEDUREなどのコマンドが含まれます。 必要に応じて、テーブルの構造を変更します。これらの変更は既存のDDLクエリに直接行われ、テーブルが作成されます。 ストアドプロシージャ、トリガーなどの変更についても同様です。

たとえば、リポジトリの現在のバージョンにはすでにテーブルmyTableがあり、CreateDatabase.sqlファイルでは次のようになります。

CREATE TABLE myTable
(
id INT (10) NOT NULL ,
myField VARCHAR (255) NULL ,
PRIMARY KEY (id)
);

このテーブルに新しいフィールドを追加する必要がある場合は、既存のDDLクエリに追加するだけです。

CREATE TABLE myTable
(
id INT (10) NOT NULL ,
myField VARCHAR (255) NULL ,
newfield INT(4) NOT NULL ,
PRIMARY KEY (id)
);

その後、変更されたsqlファイルがコードリポジトリに送信されます。

バージョン間の移行の実行

この方法では、データベースを新しいバージョンに更新する手順は、他の方法ほど簡単ではありません。 バージョンごとに構造の宣言的な記述のみが保存されるため、移行ごとにALTER 、 DROPおよびCREATE要求の形式で違いを生成する必要があります。 多くのDBMSで利用可能なSQLyog TOADの一部であるスキーマ同期ツール、 DmitryKoterovによるPostgreSQLのDklab_pgmigrator 、 RedGateの SQL比較SDKなど、自動化されたdiffユーティリティが役立ちます。

あるデータベースバージョンから別のデータベースバージョンに移行するには、2つの一時データベースで元のバージョンと最終バージョンの構造を復元してから、移行スクリプトを生成する必要があります。 ただし、この手順は自動化できるため、それほど時間はかかりません。

データの変更はどうですか？

本番サーバーでデータベースのバージョンを更新するときは、データベース構造だけでなく、そこに保存されているデータも更新する必要がある場合があります。 例は、正規化するための、古い構造を持つテーブルから新しいテーブルへのデータの転送です。 実稼働サーバー上のデータは既に存在し、使用されているため、新しいテーブルを作成して古いテーブルを削除するだけでは不十分です。既存のデータを転送する必要もあります。

以前の方法では、移行を保存および実行するというコンテキストで、データはデータベースの構造とあまり変わりませんでした。 ただし、この方法では、宣言形式でコードリポジトリに保存することは不可能であるため、データの変更は特別です。すべてのサーバー上のデータは異なります。 また、データを変更するためにそのようなクエリを自動的に生成することも不可能です。人間の介入が必要です。

この問題には、いくつかの多かれ少なかれ受け入れられる解決策があります。

• データの変更を増分変更方法に従って（おそらく簡略化された形式で）保存し、生成後に手動で結果のdiffスクリプトに追加します。
• データ変更リクエストを保存する場所がなく、diffスクリプトが生成されたら、それを分析して必要なすべてのDMLリクエストを追加します 。 私の同僚が、それが機能していて欠点がないと主張しているからこそ、私はこの決定をここにもたらします。 危険すぎると思う diffスクリプトの生成は、変更のアプリケーション関連部分で作業してから数か月後に発生する可能性があり、正しいデータ移行に必要な詳細はすでに忘れられている可能性があります。

長所、短所、結論

バージョン管理システムのツールを使用して、バージョン間の構造の変化を観察すると便利です。
他のソースコードと同様に、データベース構造についてコメントするのが便利です。
最新バージョンのクリーンなデータベースをゼロから作成するには、1つのファイルのみを実行する必要があります。
移行スクリプトは、自動的に生成されるため、他の方法よりも信頼性が高くなります。
新しいバージョンから古いバージョンへの移行は、古いバージョンから新しいバージョンへの移行とほぼ同じくらい簡単です（問題は悪名高いデータの変更によってのみ発生します）。
リポジトリの2つのブランチをマージする場合、データベース構造のマージは他のアプローチを使用するよりも簡単です。
データの変更は個別に保存し、生成された移行スクリプトに手動で挿入する必要があります。
手動で移行を実行するのは非常に不便であり、自動化されたツールが必要です。

この方法には多くの利点があります。記載されているデータ変更の問題を恐れず、運用サーバーへの更新がまれな場合は、この方法を使用することをお勧めします。

バージョン管理されたデータベース移行のための既製のソリューション

上記の方法は、サードパーティのソリューションなしでも使用できますが、すぐに使用できる製品もあり、それぞれ独自のイデオロギーと独自のアプローチがあり、別の記事に値します。バージョン管理された移行ソリューションを選択するときは、そのような製品も考慮してください。

それらのいくつかは、 Denis Gladkikh による最近の記事「データベースのバージョン管理のアプローチ」で説明されています。

すぐに使用できるバージョン対応の移行システムのごく一部のみを以下に示します。

• Migrator.NET ;
• ECM7.Migrator — Migrator.NET dima117 . ;
• Active Record Migrations , Ruby on Rails;
• SQL Source Control RedGate;
• DotNetMigrations ;
• Fluent Migrator . tabushi , ;
• DbDeploy.NET ;
• Tarantino ;
• Mygrate ;
• DBUpdater ;
• Wizardby ostapbender .

データベースに変更を保存して適用するには、さまざまな方法があります。プロジェクトでどれを選択するかはそれほど重要ではありません。主なことは、いずれかの方法を採用して着実にそれに従うことです。残りは詳細です。私はこの記事でこの考えを伝えようとしました。最も簡単な方法の分類を私の考えに加えて、彼らがあなたに最も適した解決策を選ぶのを助けることを望んでいます。