March 2017

Volume 32 Number 3

Dino Esposito | March 2017

Dino Esposito今日の開発者の大多数は、従来型のリレーショナル データベースを使ってデータを格納しています。代わりになるスキーマレス データ ストア (総称、NoSQL ストア) がさまざまなビジネス シナリオでの高い有効性を証明しているにも関わらず、リレーショナル データベースは現在まで数 10 年に渡って用いられているアプローチです。既存のテーブル レコードを更新するたびに、以前に保持していた状態は、自動的に追跡されなくなります。これまでなら既存データが上書きされても大きな問題にはなりませんでしたが、この状況は急速に変わってきています。最近では、あらゆる企業にとってデータが最も価値のある資産となり、ビジネス インテリジェンス プロセスの原動力になっています。

2016 年 5 月号と 6 月号の本連載コラム (msdn.com/magazine/mt703431msdn.com/magazine/mt707524) では、CRUD (作成 (Create)、読み取り (Read)、更新 (Update)、削除 (Delete)) システムを、ソフト更新と削除を使う次のレベルのシステムに変える汎用アプローチを取り上げました。ソフト更新とは、標準の更新操作ですが、なんらかの方法でレコードの古い状態を保持する点が異なります。そのため、追加の API を用意して、システムの有効期間中に作成された各エンティティの履歴を取得します。

レコードを追跡したまま、そのレコードを更新および削除するという課題に対処できる方法はそれほど多くありません。削除の場合は、新しい列 (通常はブール型の列) を追加して、削除したレコードをマークします。更新の場合は、追跡対象のレコードごとに個別の履歴テーブルを作成して管理するのが最も実用的なアプローチです。データ テーブルと履歴テーブルの同期を維持する場合は、ビジネスロジックとデータ アクセス ロジックが追加で必要になり、履歴をクエリする専用の API も必要になります。

リレーショナル テーブルで履歴を管理することについては、ANSI SQL 2011 規格でその形式が定められています。最新バージョンの SQL Server では、テンポラル テーブルという機能がサポートされます。このテンポラル テーブルでは、開発者がデータ テーブルを選択して、シャドウ履歴テーブルを作成、管理できるようになります。そこで今回は、SQL Server 2016 のテンポラル テーブルと、それを Entity Framework (EF) 内で使用する方法について詳しく調べます。

テンポラル テーブルの構造

テンポラル データベースと従来型のデータベースとの主な概念的違いは、従来型のデータベースがその時点で真となるデータのみを格納するのに対し、テンポラル データベースはデータの各部分のコピーを複数管理します。テンポラル テーブルでは、レコードが特定の状態に達した時点を表す時間列がいくつか余分に追加されます。図 1 では、SQL Server 2016 Management Studio にテンポラル テーブルが表示されるようすを示しています。

SQL Server 2016 のテンポラル テーブル
図 1 SQL Server 2016 のテンポラル テーブル

この図には注目すべき点が 2 つあります。1 つは、dbo.BookingsHistory という子履歴テーブルです。このテーブルは、テンポラル テーブルを作成することになる T-SQL 命令を処理するたびに、SQL Server によって自動的に作成されます。開発者には、この履歴テーブルに対して読み取りアクセス許可しかありません。図 1 で注目すべきもう 1 つの点は、選択しているテーブルのコンテキスト メニューに [削除] コマンドが表示されないことです。テンポラル テーブルはいったん作成されると、そのテーブルに対するそれ以降の操作は、SQL Server Management Studio のインターフェイスから行う場合も、プログラムで行う場合も、厳密に管理され、場合によっては制限されることもあります。たとえば、テンポラル テーブルは削除することも複製することもできません。さらに、連鎖更新や連鎖削除にも制限があります。SQL Server 2016 でテンポラル テーブルが影響を受ける制限事項の詳細については、bit.ly/2iahP1n を参照してください。

SQL Server 2016 では、CREATE TABLE 命令の最後に特別な句を付けて、テンポラル テーブルを作成します。テンポラル テーブルの状態は、最終的には新しい SYSTEM_VERSIONING 設定の値がオンかオフかのいずれかになります。つまり、プログラムでは任意のテーブルをテンポラル テーブルに変えることができ、任意の時点で元のテンポラルではない状態に戻すことができます。前述のテンポラル テーブルが影響を受けるすべての制限事項は、SYSTEM_VERSIONING 設定がオフになっていれば効力がありません。

テンポラル テーブルと Entity Framework

最近の開発者の多くは、特に EF と EF Code First のサービスを経由して SQL Server を使用しています。ただし、現時点の EF は、テンポラル テーブルに対する特別なサポートを提供していません。近い将来、アドホック サポートが提供される予定です。とは言え、テンポラル テーブルに対するある程度基礎レベルのサポートは EF 6.x の最新バージョンでも実現でき、EF Core ではさらに強力なサポートを利用できます。ただし、残念ながら、LINQ to Entities との完全な統合は、現実的には、フレームワークに低レベルの変更を加えられ、LINQ to Entities プロバイダーがクエリ用の SQL コードを生成するようにならなければ実現できません。SQL の開発者には、テンポラル テーブルを操作するのに必要な構文ツールがすべて最新の T-SQL 言語によって用意されます。

こうした状況をまとめると、EF 開発者にとってのテンポラル テーブルの状況は、次のようになります。 まず、Code First では、テンポラル テーブルを非常に簡単に作成できます。次に、通常の方法で EF を使って更新と削除を実行できます。さらに、クエリを作成するには、アドホックな機能が必要です。

最も効率的にテンポラル テーブルにクエリするには、ADO.NET コードに基づく少数のアドホックなリポジトリ メソッドを利用します。こう聞くと最初は面倒なことのように思えますが、結局のところ、テンポラル テーブルが必要になるのは、ある特定のエンティティの履歴を取得する必要がある場合です。たとえば、注文や請求に加えられた変更をすべて取得するとします。

この場合最終的に必要になるのは、できる限り手軽な専用の FirstOrDefault 風メソッドで、集計用に直接公開されるメソッドです。これには、リポジトリ クラスが適しています。

テンポラル対応の初期化子

EF Code First では、データベースが存在しない場合は必ず新しいデータベースを作成します。その際、DbContext クラスを CreateDatabaseIfNotExists から継承します。その結果、新しいテーブルは、宣言済みの DbSet プロパティごとに作成されます。テンポラル テーブルも同じように作成できるでしょうか。 現状では、アドホックな属性や構文機能を使用しないで、2 ステップの操作でテンポラル テーブルを作成します。最初のステップでは、正規のテーブルを作成する必要があります。これは、Code First で通常行う作業です。次のステップでは、SYSTEM_VERSIONING 設定をオンにする必要があります。これには、アドホックな ALTER TABLE ステートメントが必要です。図 2 では、初期化子クラスの Seed メソッドとして考えられる実装を示しています。このメソッドでは、基になる SQL Server のバージョンをチェックしてから前に作成した Booking テーブルの状態を変更しています。 

図 2 Code First によるテンポラル テーブルの作成

protected override void Seed(DbContext context)
{
  // Grab the SQL Server version number
  var data = context
    .Database
    .SqlQuery<string>(@"select
  left(cast(serverproperty('productversion')
       as varchar), 4)")
    .FirstOrDefault();
  if (data != null)
  {
    var version = data.ToInt();+
    if (version < 13)
      throw new Exception("Invalid version of SQL Server");
  }
  // Prepare the SQL to turn system versioning on SQL Server 2016
  var cmd = String.Format(SqlSystemVersionedDbFormat, "Bookings");
  context.Database.ExecuteSqlCommand(cmd);
}

テンポラル テーブル (SQL Server 2016 の特殊用語では、システム バージョン管理されたテーブル) を作成するために実際に必要な T-SQL コマンドを、図 3 に示します。

図 3 テンポラル テーブルの作成

private const string SqlSystemVersionedDbFormat =
  @"ALTER TABLE dbo.{0}
    ADD SysStartTime datetime2(0)
    GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT
      DF_{0}_SysStart DEFAULT SYSUTCDATETIME(),
    SysEndTime datetime2(0)
    GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT
      DF_{0}_SysEnd DEFAULT CONVERT(datetime2 (0),
      '9999-12-31 23:59:59'),
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
  ALTER TABLE dbo.{0}
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.{0}History))";

図 3 にコードにあるプレースホルダー {0} は、実テーブル名を表します。今回の場合、図 1 で示すように Bookings になります。

結果として作成される履歴テーブルは、メイン テーブルのコピーに、SysStartTime と SysEndTime という datetime2 型の列を加えたものです。この 2 つの列を組み合わせると、レコードが特定の状態だった期間がわかります。SysStartTime はレコードが特定の状態になった時点を示し、SysEndTime はその状態が有効ではなくなった時点を示します。更新と削除は、SysStartTime と SysEndTime の値を変化させる原因となるデータベース操作です。

更新と削除

プライマリ テーブルとその履歴の同期を維持するロジックは、SQL Server 2016 データベース エンジンに委ねられます。更新を実行する方法とは関係なく、プライマリ テーブルのレコードが更新されるたびに、新しい履歴レコードが作成されます。つまり、Management Studio でテンポラル レコードの値を直接編集しても、ストアド プロシージャ、ADO.NET コマンド、EF を使って更新しても、新しい履歴レコードが作成されます (図 4 参照)。

テンポラル テーブルでのレコードの更新
図 4 テンポラル テーブルでのレコードの更新

図 4 の最初のクエリは、ID=2 のレコードの現在状態を返します。2 番目のクエリは、履歴テーブルから同じ ID のレコードを返します。このような観測可能な状態は、Management Studio エディターから直接 2 つの簡単な更新を行うことで決定されています。最初に、Hour 列を 9 から 13 に変更し、その数秒後、Owner 列の値を Dino から Paul に変更します。履歴テーブルの最初のレコードは、本来作成されたレコード (EF と SaveChanges への呼び出しによってテーブルに格納されたレコード) がおよそ 5 分間有効状態だったことを示しています。その後、レコードは数秒間別の状態に移行し、最終的に今の状態になっています。ご覧のように、現在状態は履歴テーブルには格納されません。図 5 には、ID=2 のレコードが削除された後のテーブルの状態を示しています。

テンポラル テーブルでのレコードの削除
図 5 テンポラル テーブルでのレコードの削除

プライマリ テーブルは、ID=2 に対するクエリが行われると、空の結果セットを返します。一方、履歴テーブルには、有効期間が最後に更新された時点から削除された時点までを示す 3 番目のレコードが含まれるようになります。

特定のエンティティのクエリ

すべての状態変化を追跡することにより、システム内で起こったことを見逃すことがなくなります。その結果、すべてのデータベース操作の完全かつ無制限のログが得られます。さらに、状態変化についての完全なリストが得られます。このリストには、SQL ステートメントのプレーンなログよりもビジネス上の役割に関連する多く御情報が含まれます。テンポラル テーブルの考え方は、イベント ソーシングとよく似ています。あえて言うなら、テンポラル テーブルとは、CRUD ベースの形式のイベント ソーシングです。では、特定の集計の古い状態をクエリする方法を見てみましょう。

入れ子になった History テーブルでも状況を把握できますが、SQL Server 2016 には特定のレコードについてのテンポラル データを直接クエリできる構文が用意されています。以下のコードは、特定の期間内の ID=2 のレコードの各バージョンを取得するサンプル コマンドのスキーマです。

var sql = @"SELECT * FROM Bookings 
  FOR SYSTEM_TIME BETWEEN '{0}' AND '{1}'
  WHERE ID=2";

テンポラル クエリでは、対象期間を設定する FOR SYSTEM_TIME 句を標準クエリに付加します。データベース エンジンが、履歴テーブル内の追加の列、プライマリ テーブルと入れ子テーブルのコンテンツを確認する、このクエリを解決します。このクエリはレコードのリストを返します。このようなクエリを EF に実行させるにはどうすればよいでしょう。 EF 6 で利用できるのは、DbSet クラスの SqlQuery メソッドだけです。

using (var db = new EF6Context())
{
  var current = db.Bookings.Single(b => b.Id == 1);
  var time = DateTime.Now.AddMinutes(-5);
  var old = db.Bookings
    .SqlQuery("SELECT * FROM dbo.Bookings
          FOR SYSTEM_TIME AS OF {0} WHERE Id = 1", time)
    .SingleOrDefault();
}

EF 6 では、クエリで返される列の名前は、クラスのプロパティの名前と一致している必要があります。このため SqlQuery はマッピングを使用しません。列とプロパティの名前が一致していない場合、SELECT * ではなく、SELECT リストの列に別名を指定する必要があります。

EF Core では、この状況が改善され、いくつかの点で簡単になっています。EF Core では、FromSql というメソッドを使用します。最初に、FromSql メソッドはマッピングを使用します。つまり、列とプロパティの名前が一致しない場合に別名を付ける必要はありません。

using (var db = new EFCoreContext())
{
  var current = db.Bookings.Single(b => b.Id == 1);
  var time = DateTime.Now.AddMinutes(-5);
  var old = db.Bookings
    .FromSql("SELECT * FROM dbo.Bookings
              FOR SYSTEM_TIME AS OF {0}", time)
    .SingleOrDefault(b => b.Id == 1);
}

次に、LINQ を使用して、最初の SELECT を土台に組み立てることができます。つまり、Where、OrderBy、GroupBy などの LINQ 演算子を何でも使用でき、通常、これらの演算子が以下の形式のクエリに変換されます。

SELECT projection
FROM (SELECT * FROM dbo.Bookings FOR SYSTEM_TIME AS OF {0}) as Bookings
WHERE condition

最終的には、必要に応じて、プレーンな ADO.NET とデータ リーダーを使用して、テンポラル テーブルに格納されたデータにいつでもアクセスできます。

まとめ

EF にほぼ基づいて、データ層でテンポラル テーブルを操作でき、クエリにプレーンな ADO.NET を使用することになった場合でも、LINQ to Objects を利用してメモリ内で複合物を構築できることは間違いありません。Entity Framework チームのロードマップには、テンポラル テーブルに対する数か月以内の取り組みが示されています。ですので、少々お待ちください。


Dino Esposito は、『Microsoft .NET: Architecting Applications for the Enterprise』(Microsoft Press、2014 年) および『Modern Web Applications』(Microsoft Press、2016 年) の著者です。JetBrains の .NET および Android プラットフォームのテクニカル エバンジェリストでもあります。世界各国で開催される業界のイベントで頻繁に講演しており、software2cents.wordpress.com (英語) や Twitter (@despos、英語) でソフトウェアに関するビジョンを紹介しています。

この記事のレビューに協力してくれたマイクロソフト技術スタッフの Rowan Miller に心より感謝いたします。