読者です 読者をやめる 読者になる 読者になる

C# + FluentMigratorを使って、MS Accessの主キー・インデックス・外部キーの設定をする

C# FluentMigrator MS Access

前回はFluentMigratorのおおまかな流れを試してみました。

今回は

  • 新規テーブル作成時
  • 既存テーブル

の2パターンに対して、

  • 主キー(単一/複合) の作成
  • インデックス(単一/複合) の作成
  • 外部キー(単一/複合) の作成

などをしてみます。

 

環境

 

主キーの作成

単一主キーの作成
public class Mig_01_SinglePrimaryKey : Migration
{
    public override void Up()
    {
        // 新規テーブルに作成
        Create.Table("PKNew").WithColumn("PKCol").AsInt32().PrimaryKey();


        // 既存テーブルに作成
        // Schema.Exists()を使って、既存テーブルとして存在するか判断
        if (!Schema.Table("PKExist").Exists())
        {
            Create.Table("PKExist").WithColumn("PKCol").AsInt32();
        }

        // Rollbackする際にPrimaryKey名が必要なので、引数として渡す
        Create.PrimaryKey("spk").OnTable("PKExist").Column("PKCol");
    }

    public override void Down()
    {
        Delete.Table("PKNew");                          // 新規テーブル用
        Delete.PrimaryKey("spk").FromTable("PKExist");  // 既存テーブル用
    }
}

 
上記のソースの中で、テーブルが作成済かどうかはSchema.Exists()を使っています。
Fluent Interface - Schema.Exists Expressions · schambers/fluentmigrator Wiki

なお、見やすくするため、以降のサンプルではSchema.Exists()の部分を省略します。

 

複合主キーの作成

Down()については、単一の場合と同じのため省略します。

public override void Up()
{
    // 新規テーブルに作成
    Create.Table("PKsNew")
        .WithColumn("PKCol1").AsInt32().PrimaryKey()
        .WithColumn("PKCol2").AsString().PrimaryKey();


    // 既存テーブルに作成
    Create.PrimaryKey("cpk").OnTable("PKsExist").Columns(new[] { "PKCol1", "PKCol2" });
}

 

インデックスの作成

単一インデックスの作成

「Null無視」のインデックスを作成するメソッドが分からなかったので、Execute.Sql()で直接書きます。

 

public override void Up()
{
    // 新規テーブルに作成
    // *昇順のみ作成可能っぽい
    Create.Table("IndexNew").WithColumn("IndexCol").AsInt32().Indexed("New");

    // 既存テーブルに作成
    // 昇順
    Create.Index("Asc").OnTable("IndexExist").OnColumn("AscIdxCol").Ascending();            
    // 降順
    Create.Index("Desc").OnTable("IndexExist").OnColumn("DescIdxCol").Descending();
    // 重複不可
    Create.Index("Unique").OnTable("IndexExist").OnColumn("UniqueCol").Unique();
    // 降順 & 重複不可
    Create.Index("DescUnique").OnTable("IndexExist").OnColumn("DescUniqueCol")
        .Descending().WithOptions().Unique();

    // Create.Index()ではNull無視のIndexを作れないので、Execute.Sql()でDDLを直接記述する
    Execute.Sql("CREATE INDEX IgnoreNull ON IndexExist(IgnoreNullCol) WITH IGNORE NULL");
}

public override void Down()
{
    // 新規テーブル用
    Delete.Table("IndexNew");

    // 既存テーブル用
    Delete.Index("Asc").OnTable("IndexExist");
    Delete.Index("Desc").OnTable("IndexExist");
    Delete.Index("Unique").OnTable("IndexExist");
    Delete.Index("DescUnique").OnTable("IndexExist");
    Delete.Index("IgnoreNull").OnTable("IndexExist");
}

 

複合インデックスの作成

新規テーブルに作成することができないようなので、既存テーブルのみの作成としました。
CREATE TABLE ステートメント (Microsoft Access SQL) - MSDN

また、Down()については、単一の場合と同じのため省略します。

public override void Up()
{
    // 既存テーブルに作成
    if (!Schema.Table("IndexesExist").Exists())
    {
        Create.Table("IndexesExist")
            .WithColumn("AscIdxCol").AsInt32()
            .WithColumn("DescIdxCol").AsInt32();
    }

    // 列ごとに昇順・降順を指定
    Create.Index("AscDesc").OnTable("IndexesExist")
        .OnColumn("AscIdxCol").Ascending()
        .OnColumn("DescIdxCol").Descending();

    // 列ごとに昇順・降順を指定、全体では重複不可
    Create.Index("AscDescUnique").OnTable("IndexesExist")
        .OnColumn("AscIdxCol").Ascending()
        .OnColumn("DescIdxCol").Descending()
        .WithOptions().Unique();

    // 列ごとに昇順・降順を指定、全体では重複不可・Null無視
    Execute.Sql(
        "CREATE UNIQUE INDEX AscDescUniqueIgnoreNull ON IndexesExist" +
        "(AscIdxCol ASC, DescIdxCol DESC) WITH IGNORE NULL");
}

 
参考までに、複合インデックスの作成結果は、以下のようになります。

f:id:thinkAmi:20141108070300p:plain

 

外部キーの作成

単一外部キーの作成

事前準備として、外部キーの参照先テーブルには主キーを用意しておきます。

準備を忘れると、以下のようなエラーが発生します。
固定インデックスとは --Access Club 超初心者 FORUM--

public override void Up()
{
    // 参照先テーブルの準備
    if (!Schema.Table("RefFK").Exists())
    {
        Create.Table("RefFK").WithColumn("RefCol").AsInt32().PrimaryKey(); // 外部キーのためにPK用意
    }


    // 新規テーブルに作成
    Create.Table("FKNew").WithColumn("FKCol").AsInt32().ForeignKey("FKNameNew", "RefFK", "RefCol");

    // 既存テーブルに作成
    Create.ForeignKey("FKNameExists")
        .FromTable("FKExist").ForeignColumn("FKCol")
        .ToTable("RefFK").PrimaryColumn("RefCol");
}

public override void Down()
{
    Delete.Table("FKNew");                                  // 新規テーブル用
    Delete.ForeignKey("FKNameExists").OnTable("FKExist");   // 既存テーブル用
}

 

複合外部キーの作成

新規テーブルに作成することができないようなので、既存テーブルの作成のみとしました。

また、Down()については、単一の場合と同じのため省略します。

public override void Up()
{
    // 参照先テーブルの準備
    if (!Schema.Table("RefFKs").Exists())
    {
        Create.Table("RefFKs")
            .WithColumn("RefCol1").AsInt32().PrimaryKey()
            .WithColumn("RefCol2").AsInt32().PrimaryKey();
    }


    // 既存のテーブルの列に追加する場合
    Create.ForeignKey("FKsNameExist")
        .FromTable("FKsExist").ForeignColumns(new[] { "FKCol1", "FKCol2" })
        .ToTable("RefFKs").PrimaryColumns(new[] { "RefCol1", "RefCol2" });
}

 

Auto Reversing Migrationについて

今まで、Up()Down()をそれぞれ書いていましたが、AutoReversingMigrationを使えばDown()が不要になる場合もあります。詳しくは以下に記載されています。
Auto Reversing Migrations · schambers/fluentmigrator Wiki · GitHub

そこで、Auto Reversing Migrationも試してみました。

 

テーブルのAuto Reversing Migration

Auto Reversing Migrationを使うには、継承するクラスをMigrationからAutoReversingMigrationへと変更します。

なお、主キーはAuto Reversing Migrationには対応していないようなので、新規作成するときに設定しておきます。

また、外部キーの参照用に「AutoRefTable」テーブルも用意しておきます。

public class Mig_07_AutoReversingMigrationTable : AutoReversingMigration
{
    public override void Up()
    {
        // PrimaryKeyはAutoReversingMigrationに対応していないっぽいので、宣言してしまう
        // See: https://github.com/schambers/fluentmigrator/wiki/Auto-Reversing-Migrations

        Create.Table("AutoTable")
            .WithColumn("PKCol").AsInt32().PrimaryKey()
            .WithColumn("IdxCol").AsInt32()
            .WithColumn("FKCol").AsInt32()
            .WithColumn("TextCol").AsString();

        Create.Table("AutoRefTable")
            .WithColumn("RefCol").AsInt32().PrimaryKey()
            .WithColumn("TextCol").AsString();
    }
}

 
これを /task rollbackで実行すれば、テーブルが削除されることが分かります。

 

インデックス・外部キーのAuto Reversing Migration

同様に、以下のコードを書き、 /task rollbackで実行すれば、インデックスや外部キーが削除されることが分かります。

public class Mig_08_AutoReversingMigrationKeys : AutoReversingMigration
{
    public override void Up()
    {
        // インデックス
        Create.Index("AutoIndex").OnTable("AutoTable").OnColumn("IdxCol").Descending();

        // 外部キー
        Create.ForeignKey("AutoFK")
            .FromTable("AutoTable").ForeignColumn("FKCol")
            .ToTable("AutoRefTable").PrimaryColumn("RefCol");
    }
}

 

ソースコード

GitHubTablePKFKIndexMigrationsディレクトリ以下が、今回作成したマイグレーションファイルになります。
FluentMigrator-sample/FluentMigrator-sample/TablePKFKIndexMigrations at master · thinkAmi/FluentMigrator-sample · GitHub