C# + FluentMigratorを使って、MS Accessのカラムやデータのマイグレーションをする

前回はMS Accessの主キー・インデックス・外部キーの設定をしてみました。

今回は、テーブルカラムのマイグレーションと、テーブルへのデータ追加・更新・削除のマイグレーションをしてみます。

 

環境

 

カラムのデータ型を指定する

MS Accessのカラムのデータ型と、FluentMigratorのメソッドの対応は、FluentMigratorの以下のコードに記載がありました。
fluentmigrator/JetTypeMap.cs at master · schambers/fluentmigrator · GitHub

 
なお、MS Accessで作成できなかったデータ型は、

  • オートナンバー型で、フィールドサイズがレプリケーションIDのもの
  • ハイパーリンク
  • 添付ファイル型

でした。

オートナンバー型のレプリケーションIDについては、以下のコードでチェックされた結果、エラーとなっているようです。
fluentmigrator/JetColumn.cs at master · schambers/fluentmigrator · GitHub

また、ハイパーリンク型と添付ファイル型はDDLでは生成できないようでした。(DAOやADOXであれば、生成できそうです)
Microsoft Access tips: Field type reference - names and values for DDL, DAO, and ADOX

他に、AsXML()メソッドXML型が存在しないのでエラー、AsCustomeメソッドは使い方が分かりませんでした。

実際に使ってみたコードは以下の通りです。

public override void Up()
{
    Create.Table("ColumnTypes")
        // テキスト型
        // いずれの方法も同じ。引数が256以上になると、メモ型になる
        .WithColumn("StringCol").AsString(100)
        .WithColumn("FixedTextCol").AsFixedLengthAnsiString(30)
        .WithColumn("AsFixedTextCol").AsFixedLengthString(40)
        .WithColumn("AnsiStringCol").AsAnsiString()

        // メモ型(引数で256以上を指定)
        .WithColumn("MemoCol").AsString(256)


        // 数値型、フィールドサイズが異なる
        .WithColumn("ByteCol").AsByte()        // バイト型
        .WithColumn("Int16Col").AsInt16()      // 整数型
        .WithColumn("Int32Col").AsInt32()      // 長整数型

        .WithColumn("Int64Col").AsInt64()              // 十進型:精度20, 小数点以下0
        .WithColumn("DecimalCol").AsDecimal()          // 十進型:精度19, 小数点以下5
        .WithColumn("DecimalSizeCol").AsDecimal(10, 2) // 十進型:精度10, 小数点以下2

        .WithColumn("FloatCol").AsFloat()      // 単精度浮動小数点型
        .WithColumn("DoubleCol").AsDouble()    // 倍精度浮動小数点型
        .WithColumn("GuidCol").AsGuid()        // レプリケーション ID型


        // 日付/時刻型(いずれも同じ結果)
        .WithColumn("DateCol").AsDate()
        .WithColumn("DateTimeCol").AsDateTime()
        .WithColumn("TimeCol").AsTime()

        // 通貨型
        .WithColumn("CurrencyCol").AsCurrency()

        // オートナンバー型
        .WithColumn("AutoInt32Col").AsInt32().Identity()    // 長整数型のみ

        // Yes/No型
        .WithColumn("YesNoCol").AsBoolean()

        // OLEオブジェクト型
        // 引数がないとエラー。引数の中身は特に考慮されないっぽい
        .WithColumn("BinarySizeCol").AsBinary(20);
}

 

カラムのプロパティを設定

MS Accessの場合、DDLだけではできることが少ないようでした。
Microsoft Access tips: DDL Programming Code Examples

設定できたものは、

  • 値要求プロパティ
  • 既定値プロパティ
  • インデックスプロパティ

の3つだけでした。

なお、WithDescription()WithColumnDescription()は実行しても反映されませんでした。

また、WithDefault()については MS Accessで実装されていないためエラーとなりました。
Use inbuilt database functions when setting the default value · schambers/fluentmigrator Wiki · GitHub

public override void Up()
{
    Create.Table("ColumnProperty")
        // 値要求(=Null不可)プロパティ
        .WithColumn("NotNullCol").AsString().NotNullable()  // 値要求:はい
        .WithColumn("NullableCol").AsString().Nullable()    // 値要求:いいえ

        // 既定値プロパティ
        .WithColumn("DefalutStringCol").AsString().WithDefaultValue("デフォルト値");

        // インデックスプロパティ
        // 重複なしで`IX_ColumnProperty_UniqueCol`という名前のインデックスが自動作成
        .WithColumn("UniqueCol").AsString().Unique();
}

 

値要求プロパティについて (2014/11/15 追記)
デフォルト値:はい

MS Accessでは、値要求プロパティについて何も指定しない場合、デフォルトでは はい になるようです。

コード

public override void Up()
{
    Create.Table("DefalutNotNullable")
        .WithColumn("NotNullable").AsString()
        .WithColumn("NotNullableDatetime").AsDateTime();
}

発行されるSQL

CREATE TABLE [DefalutNotNullable] ([NotNullable] VARCHAR(255) NOT NULL, [NotNullableDatetime] DATETIME NOT NULL)

 

値要求プロパティの変更は一度のみ

MS Accessの場合、DDLによる値要求プロパティの変更は一度のみです。

NOT NULL は、単一フィールド、または名前付き CONSTRAINT 句の内部で使用できます。名前付き CONSTRAINT 句は、単一フィールドまたは複数フィールドのどちらかの名前付き CONSTRAINT 句に適用されます。ただし、NOT NULL の制約を適用できるのはフィールドに対して一度のみです。再度適用しようとした場合は実行時エラーになります。

ALTER TABLE ステートメント (Microsoft Access SQL) - MSDN

 
そのため、以下のコードの最終結果は両方とも値要求プロパティがはいになります。

 

値要求プロパティをはいからいいえに切り替える

コード

public override void Up()
{
    Create.Table("AlterNullable").WithColumn("ToNullable").AsString().NotNullable();
    Alter.Table("AlterNullable").AlterColumn("ToNullable").AsString().Nullable();
}

発行されるSQL

CREATE TABLE [AlterNullable] ([NotNullable] VARCHAR(255) NOT NULL)
ALTER TABLE [AlterNullable] ALTER COLUMN [NotNullable] VARCHAR(255)

 

値要求プロパティをいいえからはいに切り替える

コード

public override void Up()
{
    Create.Table("AlterNotNullable").WithColumn("ToNotNullable").AsString().Nullable();
    Alter.Table("AlterNotNullable").AlterColumn("ToNotNullable").AsString().NotNullable();
}

発行されるSQL

CREATE TABLE [AlterNotNullable] ([ToNotNullable] VARCHAR(255))
ALTER TABLE [AlterNotNullable] ALTER COLUMN [ToNotNullable] VARCHAR(255) NOT NULL
2014/11/15 追記ここまで

 

カラムのAlter, Delete, Rename

Alter
public override void Up()
{
    Alter.Table("Columns")
        .AddColumn("AddCol").AsString().Nullable()  // 一番最後に追加される
        .AlterColumn("AlterCol1").AsString(256).Nullable();

    Alter.Column("AlterCol2").OnTable("Columns").AsString(256).Nullable();
}

public override void Down()
{
    Delete.Column("AddCol").FromTable("Columns");

    Alter.Table("Columns").AlterColumn("AlterCol1").AsString().Nullable();

    Alter.Column("AlterCol2").OnTable("Columns").AsString().Nullable();
}

 

Delete

FluentMigratorでは、Delete.Column("hoge").Column("fuga")のように、削除するカラムを一度に指定できますが、MS Accessでは;で連結したDDLは実行できないことから、複数回に分けて実行する必要があります。

SQL Server や他の強力な DBMS システムとは異なり、JET データベース エンジン (Microsoft Access およびその他の小規模アプリケーションで使用) では、単一バッチで複数ステートメントを実行できません。

@@IDENTITY クライシスを管理する - MSDN

public override void Up()
{
    Delete.Column("DelCol1").FromTable("Columns");
    Delete.Column("DelCol2").FromTable("Columns");
}

public override void Down()
{
    Alter.Table("Columns")
        .AddColumn("DelCol1").AsInt32()
        .AddColumn("DelCol2").AsString();
}

 

Rename

MS Accessでは、DDLでフィールド名を変更することができないため、以下を実行してもコンソールには No SQL statement executed. と表示されるだけになります。

そのため、MS AccessではRename()は使えないと考えて良さそうです。

public override void Up()
{
    Rename.Column("RenameCol1").OnTable("Columns").To("ReNamedCol1");
    Rename.Table("Columns").To("RenamedColumns");
}

 

テーブルへデータのINSERT, DELETE, UPDATE

INSERT

FluentMigratorでのデータINSERTも;で連結されるため、複数回に分ける必要があります。

public override void Up()
{
    Insert.IntoTable("Columns").Row(new { ValueCol1 = "hoge", ValueCol2 = "hogehoge" });
    Insert.IntoTable("Columns").Row(new { ValueCol1 = "fuga", ValueCol2 = "fugafuga" });
    Insert.IntoTable("Columns").Row(new { ValueCol1 = "piyo" }); // ValueCol2はnull
}

public override void Down()
{
    Delete.FromTable("Columns").AllRows();
}

 

DELETE

FluentMigratorでのデータDELETEも;で連結されるため、複数回に分ける必要があります。

なお、オートナンバー型の列が含まれる場合、Down()を使ってもその列の値は元に戻りません。

public override void Up()
{
    Delete.FromTable("Columns").Row(new { ValueCOl1 = "hoge" });
    Delete.FromTable("Columns").IsNull("ValueCol2");
}

public override void Down()
{
    Insert.IntoTable("Columns").Row(new { ValueCol1 = "hoge", ValueCol2 = "hogehoge" });
    Insert.IntoTable("Columns").Row(new { ValueCol1 = "piyo" });
}

 

UPDATE
public override void Up()
{
    Update.Table("Columns").Set(new { ValueCol1 = "piyopiyo" })
        .Where(new { ValueCol1 = "fuga" });
}

public override void Down()
{
    Update.Table("Columns").Set(new { ValueCol1 = "fuga" })
        .Where(new { ValueCol1 = "piyopiyo" });
}

 

新規列を追加し、既存のデータには決められた値をセットする

FluentMigrator1.3からは、 SetExistingRowsTo()を使えば、簡単にセットできるようになっています。
Fluent Interface - AllRows Attribute · schambers/fluentmigrator Wiki · GitHub

public override void Up()
{
    Alter.Table("Columns").AddColumn("ExistingCol").AsString()
        .SetExistingRowsTo("DefalutValue").NotNullable();
}

public override void Down()
{
    Delete.Column("ExistingCol").FromTable("Columns");
}

 

ソースコード

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