前回はMS Accessの主キー・インデックス・外部キーの設定をしてみました。
今回は、テーブルカラムのマイグレーションと、テーブルへのデータ追加・更新・削除のマイグレーションをしてみます。
環境
- .NET Framework 4.5
- FluentMigrator 1.3.0.0
- MS Access (*.accdb)
カラムのデータ型を指定する
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 の制約を適用できるのはフィールドに対して一度のみです。再度適用しようとした場合は実行時エラーになります。
そのため、以下のコードの最終結果は両方とも値要求プロパティがはい
になります。
値要求プロパティをはい
からいいえ
に切り替える
コード
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 およびその他の小規模アプリケーションで使用) では、単一バッチで複数のステートメントを実行できません。
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"); }
ソースコード
GitHubのColumnMigrations
ディレクトリ以下が、今回作成したマイグレーションファイルになります。
FluentMigrator-sample/FluentMigrator-sample/ColumnMigrations at master · thinkAmi/FluentMigrator-sample · GitHub