DacFxで差分更新スクリプトを取得する際に特定のオブジェクトを除外する

更新用T-SQLスクリプト生成時に特定のオブジェクトを除外したい

先日書いたエントリ、 kiyokura.hateblo.jp

……の補足情報です。

特定のオブジェクトを更新スクリプトに含めたくない場合もあるのではないかと思います。私はあります。 DaxFxのSchemaComparisonでこれを実現する方をあれやこれや試行錯誤してみたところ、異なる二つのアプローチにたどり着きました。

SchemaComparisonResult.Exclude で抽出結果から除外してスクリプトを生成するアプローチ

まず一つ目は、SchemaComparison.Compare()で比較結果から除外対象を登録したうえでスクリプトを生成する方法です。

比較結果として返されるSchemaComparisonResultのメンバメソッド、Exclude()を使います。 以下のように、Excludeに除外したいオブジェクトをSchemaDifference側で渡してやればOK。

// パターン1：比較結果から除外するアプローチ
var newverdacpac = @"C:\newver.dacpac";
var oldverdacpac = @"C:\oldver.dacpac";

var newverEndpoint = new SchemaCompareDacpacEndpoint(newverdacpac);
var oldverEndpoint = new SchemaCompareDacpacEndpoint(oldverdacpac);

var comparison = new SchemaComparison(newverEndpoint, oldverEndpoint);
var comparisonResult = comparison.Compare();

// 削除（DROP）になる更新を除外して追加（CREATE）と変更（ALTER）のみのスクリプトを生成する
foreach (var diff in comparisonResult.Differences.Where(x => x.UpdateAction == SchemaUpdateAction.Delete))
{
  comparisonResult.Exclude(diff);
}

// スクリプト生成
var generationResult = comparisonResult.GenerateScript("HogeDb");
var scriptfile = @"C:\update-database-exclude.sql";
using (var sw = new StreamWriter(scriptfile, false, Encoding.UTF8))
{
  sw.Write(generationResult.Script);
  sw.Flush();
}

ポイントはExcludeメソッドを呼ぶたびに比較結果内で依存関係等のチェックが行われる点です。そのためExcludeメソッドの実行にある程度時間がかかることがあります。また依存関係チェックの結果、スクリプト生成時に除外されないケースもあります（除外しようとしたオブジェクトが、更新スクリプトに含まれるオブジェクトから依存されている場合） 端的に言うと、Visual Studio上でSSDTのスキーマ比較ツールを利用して比較結果表からチェックボックスでオブジェクトを選択して除外していくのとまったく同じ操作と結果を得ることがでるようでう。

ExcludedSourceObjectsとExcludedTargetObjectsに除外オブジェクトを登録した上で比較を行うアプローチ

もう一点は、SchemaComparison.ExcludedSourceObjectsとSchemaComparison.ExcludedTargetObjectsに除外したいオブジェクトを登録した上でCompareメソッドを実行するアプローチです。 ExcludedSourceObjectsとExcludedTargetObjectsに登録されたオブジェクトは比較時に対象から除外されるため、これらに登録されているオブジェクトは（差異があっても）比較結果に差異として抽出されません。

例えば以下のようなコードになります。

// パターン2：除外してから比較するアプローチ
var newverdacpac = @"C:\newver.dacpac";
var oldverdacpac = @"C:\oldver.dacpac";

var newverEndpoint = new SchemaCompareDacpacEndpoint(newverdacpac);
var oldverEndpoint = new SchemaCompareDacpacEndpoint(oldverdacpac);

var comparison = new SchemaComparison(newverEndpoint, oldverEndpoint);
var comparisonResultPre = comparison.Compare();

// 削除（DROP）になる更新を除外して追加（CREATE）と変更（ALTER）のみのスクリプトを生成する
foreach (var diff in comparisonResultPre.Differences.Where(x => x.UpdateAction == SchemaUpdateAction.Delete))
{
  if (diff.SourceObject != null)
    comparison.ExcludedSourceObjects.Add(new SchemaComparisonExcludedObjectId(diff.SourceObject.ObjectType, diff.SourceObject.Name));
  if (diff.TargetObject != null)
    comparison.ExcludedTargetObjects.Add(new SchemaComparisonExcludedObjectId(diff.TargetObject.ObjectType, diff.TargetObject.Name));
}

// 除外オブジェクトを登録した状態で再度比較を実行
var comparisonResult = comparison.Compare();

// スクリプト生成
var generationResult = comparisonResult.GenerateScript("HogeDb");
var scriptfile = @"C:\update-database-add-excludedlist.sql";
using (var sw = new StreamWriter(scriptfile, false, Encoding.UTF8))
{
  sw.Write(generationResult.Script);
  sw.Flush();
}

この方法の場合、ExcludedXxxObjectsに含まれるオブジェクトは依存関係のチェックも行われないようで、更新用T-SQLに含まれるオブジェクトからの依存があっても除外されたままになります。 上記サンプルではExcludedXxxObjectに登録するための情報を得るために一度Compareを行い、リスト登録後に再度Compareを実行するという一見二度手間のような処理をしていますが、依存関係チェックが行われないせいかトータルの処理時間としては短めになりました*1

まとめ

具体的な記述のあるドキュメント等が見つからないためあくまでも挙動からの推測にはなりますが、このような形で一応目的を達成することができそうです。

*1:状況によるとは思います

DacFxを使って（C#で書いたコードを動かして）SQL Serverの差分更新スクリプトを取得する

Visual StudioのアドオンSQL Server Data Tools（以下SSDT）の機能に「スキーマ比較」ツールがあります。 これは大雑把に以下のような機能を持っています。

• 任意の "SQL Server データベースプロジェクト（以下DBプロジェクト）" / "（オンライン状態の）データベース" / "dacpacファイル" 間でスキーマの比較を行い差異を抽出する
• 抽出した差異を解消するための更新スクリプトを生成する

これらと同等の操作はコマンドラインツールのSqlPackage.exeでも可能であり、これらの基盤となっているDacFxを利用すれば.NETのコードで上記の操作を記述・実行することが可能です。

（多分こんなことやる／やりたい人はそうそうそういないと思うのですが自分はまた今後もやりそうな気がするので、完全に自分用のメモです）

差分の抽出

利用するのは DacFxに含まれる Microsoft.SqlServer.Dac.Compare.SchemaComparison クラスです。

docs.microsoft.com

このクラスは、dacpacを指す SchemaCompareDacpacEndpoint またはオンラインのデータベースを指す SchemaCompareDatabaseEndpoint をソースおよびターゲットとして比較する機能を持っています。つまり、比較対象はdacpacファイルとオンライン状態のデータベースのいずれかの組み合わせとなります。もちろんdacpac同士やデータベース同士での比較だけではなくdacpacとデータベース間での比較もできます。

（なお、SSDTのスキーマ比較機能のようにDBプロジェクトを比較対象にしたい場合は何らかの方法でDBプロジェクトからdacpacを生成した上で比較する必要がある、という事になります。その場合は 以前書いたように、Microsoft.Build.Evaluation 名前空間のあれこれを利用すればdacpacの生成処理自体も.NETのコードで記述することが可能です）

例えば以下のようなコード新旧のバージョンのdacpacを比較して差分を抽出するできました。

var newVersion = new SchemaCompareDacpacEndpoint(@"C:\some_database_v1.1.dacpac"));
var oldVersion = new SchemaCompareDacpacEndpoint(@"C:\some_database_v1.0.dacpac"));
var comparison = new SchemaComparison(newVersion, oldVersion);

var compareResult = comparison.Compare(); // 戻り値はSchemaComparisonResult型

結果は SchemaComparisonResult型 で戻されます。 例えば IEnumerable<SchemaDifference>型の Differences プロパティには差分のオブジェクトの情報が列挙されますので、この中身を見ることで差分の種類やオブジェクトの名前などを取得することができます（ただし若干直感と反するようなプロパティ名かもしれない）。

foreach(var d in compareResult.Differences)
{
  // UpdateAction : 更新種別（Add or Change or Delete）
  // Name : オブジェクトの種別 (Table or Procedure or .... )
  // SourceObject.Name / TargetObject.Name : オブジェクト名
  Console.WriteLine($"更新種別:{d.UpdateAction}, オブジェクトタイプ:{d.Name}, 対象オブジェクト名:{(d.SourceObject?.Name) ?? (d.TargetObject?.Name)}");
}

更新用T-SQLスクリプトの取得

上記までのコードで比較はできましたが、この状態では差分を抽出しただけでありまだ更新用T-SQLスクリプトは生成されていません。これはGenerateScriptメソッドで生成できます。 GenerateScriptメソッドを実行すると戻り値としてSchemaCompareScriptGenerationResultが得られ、SchemaCompareScriptGenerationResultのScriptプロパティにT-SQLスクリプトが格納されています。

雑にファイルに保存するならこんな感じでしょうか。

var generateResult = compareResult.GenerateScript("Hoge"); // 引数database name は任意の文字列。更新スクリプト中の USE [Hoge] などで使われる。
using (var sw = new System.IO.StreamWriter(@"C:\Update-Schema.sql", false, Encoding.UTF8))
{
  sw.Write(generateResult.Script);
  sw.Flush();
}

そのほか、Compareの第二引数側（古いバージョンのスキーマ側）がSchemaCompareDatabaseEndpointすなわちオンラインのデータベースの場合、GenerateScriptメソッドの代わりにPublishChangesToTargetメソッドを実行することで直接データベースを更新することができます。その際に実行されたT-SQLスクリプトはSchemaComparePublishResult型で返却されるPublishChangesToTargetの戻り値のScriptプロパティに格納されています。

まとめ

以上の通り、比較的簡単にSQL Serverのデータベースおよびdacpac間でスキーマ比較を行い差分更新用T-SQLスクリプトを生成できました。

……できましたが、オフィシャルのドキュメントが『ドキュメントコメントから生成されたシグネチャやメンバが書いてあるだけの極シンプルなAPIリファレンス』以上でも以下でもなく、利用方法などについての説明はほぼないためAPIリファレンスとインテリセンスであたりをつけつつ（基本的に同じことができるはずの）SqlPackage.exeのコマンドリファレンスを見たり おださん（@shinsukeoda） に教えてもらったサンプルコード を眺めてみたりと基本的に手さぐりだったので意外と時間がかかりました。

このほか、特定のオブジェクトを除外した更新スクリプトの生成にも無駄に時間を食ってしまったりしたのですが、またそのうち気が向いたら書いておこうと思います。

Hangfireを使ってみる (6)：Jobの種類について：3. Continuations

前回の続きです

今回は3つ目のタイプのジョブについて。

3.Continuations

連続という玉突きというか、指定したJobが終わった後に実行されるように登録するタイプのジョブです。

この前提の話として、前回と前々回で紹介したFire-and-forgetとDelayedを登録するBackgroundJob.EnqueueやBackgroundJob.Scheduleメソッドは、戻り値として登録したジョブのidを返します。Continuationsなジョブの登録は、BackgroundJob.ContinueWithメソッドにそのidを指定して登録することで行います。

例えばこんな感じです。

var jobid = BackgroundJob.Enqueue<MyJobsLib.ICostomJob>(x => x.Execute("Job Executed."));
BackgroundJob.ContinueWith<MyJobsLib.ICostomJob>(jobid, x => x.Execute("2nd job Executed."));

ダッシュボード

上記コードを実行後にダッシュボードをみると、このように『awaiting』にリストアップされていました。

Jobテーブル

Jobテーブルはこんな感じです。

JobQueueテーブル

JobQueueはこうです。一つ目のジョブのみが登録されています。

Setテーブル

Setテーブルを見ると、ここでawaitingで登録されていることが分かります（Valueがジョブid）。

JobParameterテーブル

Continuations Jobsの場合のポイントはこのJobParameterテーブルです。JobId=1のレコードの中のName=Continuationsのレコードがあり、後続のジョブのJobIdが記録されていることから、このパラメーターでジョブ同士の関連性を管理しているのでしょう。

実行されるときの挙動

Job StorageがSQL Serverの場合、Hangfire Serverによるキューのポーリングを起点に順次実行されます。

このとき、現在キューにあるジョブが読み込まれたタイミングで、ストレージ上に存在する連続するジョブはすべて一度に読み込まれたのち、そのまま連続して処理されるようです。

例えば、ContinueWithで関連付けられたJob1とJob2が存在する場合、Job1が実行された後、次回のポーリングを待つことなくそのまま連続して実行されました*1。 ただし、ジョブのステートの遷移を見ると一応は一瞬キューイングされてるようには記録されていますので、内部的には一応キューを経由してるはしているのかも知れません（このあたりソースを読めばはっきりするのでしょうが）。

まとめと次回

Continuations ジョブを利用すると、ちょっとしたバッチ処理のようなことも可能だと思います。ただしBackgroundJob.Requeueメソッドで再実行を行う際には二つ同時にRequeueしても順番の保証はされないようですので*2、その点は注意が必要と思いました

次は 『Recurring』、つまり定期実行タイプのジョブ実行について書きます。

*1:キューのポーリング間隔を十分に長くして検証した結果、そういう挙動が見られました

*2:ダッシュボードで連続するjobを同時に選択してRequeueすると、どちらも同時にQueueに登録された

Hangfireを使ってみる (5)：Jobの種類について：2. Delayed

前回の続きです。

今回は二つ目のタイプのジョブについて。

2.Delayed

指定した時間の経過後に遅延実行されるタイプ。 BackgroundJob.Schedule()またはBackgroundJob.Schedule<T>()で登録する際にTimeSpan型で指定した時間を経過した後に実行されます。

ドキュメントとしてはこちらを参照 : Calling methods with delay

コード例

遅延時間はTimeSpan型で指定します。例えば10分後だとこんな指定です。

BackgroundJob.Schedule<MyJobsLib.ICostomJob>(x => x.Execute("Job Executed."), TimeSpan.FromMinutes(10));

ダッシュボード表示

ダッシュボードを見るとこんな感じになってます。

Jobテーブル

jobテーブル上のステータスはScheduledになってます。

Setテーブル

そしてSetテーブルになにやらエントリが作られています。

Score列に1501998606という値が入っています。この値についてドキュメントでの言及を見つけることはできなかったのですが、ソースを見る限りは以下のようです*1。

1. SetテーブルのScore列（float型）には実行時のUNIXエポックからのUTCの秒数が格納されいる
2. Hangfire Serverがスケジュールをポーリング
3. 現在日時とScoreから算出される日時を比較し経過していたら実行される

当初は上記 3 のタイミングで、一度キューに入れられた後改めでキューがポーリングされたタイミングで実際に実行される……と思ったのですが、動きを見る分にはどうもそういうわけではなく、スケジュールのポーリングのタイミングで実行まで行われてるように見えました*2。

スケジュールのポーリングの設定

既にスケジュールはHangfire Serverからポーリングされて実行されることについては書きましたが、ついでにこのポーリング間隔の設定にも触れておきます。

Delayedの場合のスケジュールは、Hangfire ServerのオプションであるBackgroundJobServerOptionsのSchedulePollingIntervalで決められています。これも規定値は15秒で、秒以上の単位でカスタマイズできるようです。この設定についても先に紹介したドキュメントで触れられています。

次回

次は 『Continuations』タイプのジョブ実行について書きます。

*1:ざっと見ただけなので違ってるかもしれません

*2:これについてはソースまで読んでないので定かではないですが、キューに入れて次のキューのポーリングを待つよりも効率的ですし多分そういうことなんだろうと思ってます

Hangfireを使ってみる (4)：Jobの種類について：1. Fire-and-forget

Hangfire にはジョブの種類（と実行タイミング）がいくつかあるので、それぞれの動きについて調べてみたのでメモ。

ジョブの種類

Hangfireで利用できるジョブの種類は主に以下があるようです。

No	種別	説明
1	Fire-and-forget	即時実行。すぐに一回実行。
2	Delayed	遅延実行。一定時間経過後に一回実行。
3	Continuations	連続実行。指定のジョブ完了後に一回実行。
4	Recurring	定期実行。指定したスケジュールに沿って定期的に実行。

そのほかに、有償のPro版の場合バッチ実行もできるそうですが、今回はそちらは試す予定がないので割愛。

1-4のタイプについて、それぞれ少し試しながら見てみます。

1. Fire-and-forget

即時実行されるタイプ。 BackgroundJob.Enqueue()またはBackgroundJob.Enqueue<T>()で登録されるとすぐにキューイングされ、サーバーがキューを舐めたタイミング時で一回だけ実行されます。

ドキュメントとしてはこちらを参照 : Calling methods in background

登録後、Hangfire Serverにより実行されるまでの間、ダッシュボードで見るとこの状態です。

データベース上では以下のようになっています

Jobテーブル

jobテーブル上のステータスでは、現在キューに積まれていることを示す Enqueued となっています。

JobQueueテーブル

実際のキューであるJobQueueテーブルにエントリが作成され、処理待ちの状態になっています。

実行について

この後、Hangfire Serverがキューを舐めて処理が開始されます。 Job StorageがSQL Serverの場合、Hangfireサーバーは定期的にキューをポーリングして実行します。このポーリング間隔はSqlServerStorageOptionsのQueuePollInterval で指定します。デフォルトでは15秒となってます。

規定値から変更したい場合はHangfire Server側のJob Storageの設定時に、以下のドキュメントのとおりに設定します。

Using SQL Server - Configuring the Polling Interval

次回

次は『Delayed』について調べたことを書きます。