public void TestMySqlWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.MySql).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var fk = MigrationCommon.CreateForeignKey(table);
//act
var sql = migration.AddConstraint(table, fk);
//assert
Assert.IsTrue(sql.StartsWith("ALTER TABLE `dbo`.`Orders` ADD CONSTRAINT `FK_Orders` FOREIGN KEY (`Parent`) REFERENCES `dbo`.`Orders` (`Id`)", StringComparison.OrdinalIgnoreCase), "names should be quoted correctly");
}
public void TestDb2()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.Db2).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var fk = MigrationCommon.CreateForeignKey(table);
//act
var sql = migration.AddConstraint(table, fk);
//assert
Assert.IsTrue(sql.StartsWith("ALTER TABLE \"dbo\".\"Orders\" ADD CONSTRAINT \"FK_Orders\" FOREIGN KEY (\"Parent\") REFERENCES \"dbo\".\"Orders\" (\"Id\")", StringComparison.OrdinalIgnoreCase), "names should be quoted correctly");
}
public void TestSqlServerWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var fk = MigrationCommon.CreateForeignKey(table);
//act
var sql = migration.AddConstraint(table, fk);
//assert
Assert.IsTrue(sql.StartsWith("ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders] FOREIGN KEY ([Parent]) REFERENCES [dbo].[Orders] ([Id])", StringComparison.OrdinalIgnoreCase), "names should be quoted correctly");
}
public void TestAddPrimaryKeyWithGuid()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
var schema = new DatabaseSchema(null, SqlType.SqlServer);
var table = schema.AddTable("Test")
.AddColumn<Guid>("Id")
.AddColumn<string>("Name")
.Table;
var pk = new DatabaseConstraint
{
Name = "Test_PK",
ConstraintType = ConstraintType.PrimaryKey
};
pk.Columns.Add("Id");
//act
var sql = migration.AddConstraint(table, pk);
//assert
Assert.IsTrue(sql.IndexOf("ADD CONSTRAINT [Test_PK] PRIMARY KEY NONCLUSTERED ([Id])", StringComparison.OrdinalIgnoreCase) != -1, "adding a primary key");
}
public void TestSqlServerAddDefaultConstraint()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = table.FindColumn("NAME");
column.Length = 40;
column.DefaultValue = "'?'";
//add a default constraint
var df = new DatabaseConstraint { ConstraintType = ConstraintType.Default, Name = "DF_Orders_Name", Expression = "'?'" };
df.Columns.Add("NAME");
table.AddConstraint(df);
//act
var sql = migration.AddConstraint(table, df);
//assert
//ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_Name] DEFAULT '?' FOR [NAME];
Assert.IsTrue(sql.Contains("ADD CONSTRAINT [DF_Orders_Name] DEFAULT '?' FOR [NAME]"), "add constraint");
}
public void TestMigration()
{
//arrange
DbProviderFactory factory = null;
try
{
factory = DbProviderFactories.GetFactory(ProviderName);
}
catch (ArgumentException)
{
Assert.Inconclusive("Unable to find System.Data.SqlServerCe.4.0 Data Provider. It may not be installed.");
}
if (!File.Exists(FilePath))
{
Assert.Inconclusive("SqlServerCe4 test requires database file " + FilePath);
}
const string connectionString = "Data Source=\"" + FilePath + "\"";
ProviderChecker.Check(ProviderName, connectionString);
var tableName = MigrationCommon.FindFreeTableName(ProviderName, connectionString);
var migration = new DdlGeneratorFactory(SqlType.SqlServerCe).MigrationGenerator();
var table = MigrationCommon.CreateTestTable(tableName);
var newColumn = MigrationCommon.CreateNewColumn();
var unqiueConstraint = MigrationCommon.CreateUniqueConstraint(newColumn);
var fk = MigrationCommon.CreateForeignKey(table);
var index = MigrationCommon.CreateUniqueIndex(newColumn, tableName);
var createTable = migration.AddTable(table);
var addColumn = migration.AddColumn(table, newColumn);
var addUniqueConstraint = migration.AddConstraint(table, unqiueConstraint);
var addForeignKey = migration.AddConstraint(table, fk);
var addUniqueIndex = migration.AddIndex(table, index);
var dropUniqueIndex = migration.DropIndex(table, index);
var dropForeignKey = migration.DropConstraint(table, fk);
var dropUniqueConstraint = migration.DropConstraint(table, unqiueConstraint);
var dropColumn = migration.DropColumn(table, newColumn);
var dropTable = migration.DropTable(table);
using (new TransactionScope())
{
using (var con = factory.CreateConnection())
{
con.ConnectionString = connectionString;
using (var cmd = con.CreateCommand())
{
con.Open();
Execute(cmd, createTable);
Execute(cmd, addColumn);
Execute(cmd, addUniqueConstraint);
Execute(cmd, addForeignKey);
Execute(cmd, dropForeignKey);
Execute(cmd, dropUniqueConstraint);
Execute(cmd, addUniqueIndex);
Execute(cmd, dropUniqueIndex);
Execute(cmd, dropColumn);
Execute(cmd, dropTable);
}
}
}
}
public void TestSqLite()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SQLite).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var fk = MigrationCommon.CreateForeignKey(table);
//act
var sql = migration.AddConstraint(table, fk);
//assert
Assert.IsTrue(string.IsNullOrEmpty(sql), "SQLite does not support added constraints after table creation");
}
public void TestMigration()
{
//arrange
var tableName = MigrationCommon.FindFreeTableName(ProviderName, _connectionString);
var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
var table = MigrationCommon.CreateTestTable(tableName);
var newColumn = MigrationCommon.CreateNewColumn();
//this creates a nullable column with no default. Normally we automatically create a default.
//ensure it is nullable, as we don't want to create a default which we can't delete
newColumn.Nullable = true;
var unqiueConstraint = MigrationCommon.CreateUniqueConstraint(newColumn);
var fk = MigrationCommon.CreateForeignKey(table);
var index = MigrationCommon.CreateUniqueIndex(newColumn, tableName);
var createTable = migration.AddTable(table);
var addColumn = migration.AddColumn(table, newColumn);
var addUniqueConstraint = migration.AddConstraint(table, unqiueConstraint);
var addForeignKey = migration.AddConstraint(table, fk);
var addUniqueIndex = migration.AddIndex(table, index);
var dropUniqueIndex = migration.DropIndex(table, index);
var dropForeignKey = migration.DropConstraint(table, fk);
var dropUniqueConstraint = migration.DropConstraint(table, unqiueConstraint);
var dropColumn = migration.DropColumn(table, newColumn);
var dropTable = migration.DropTable(table);
var statements = ScriptTools.SplitScript(createTable);
//we need to strip out the "GO" parts from these scripts
using (new TransactionScope())
{
using (var con = _factory.CreateConnection())
{
con.ConnectionString = _connectionString;
using (var cmd = con.CreateCommand())
{
con.Open();
foreach (var statement in statements)
{
//ignore the drop table bit, which has no useful commands
if (statement.Contains(Environment.NewLine + "-- DROP TABLE")) continue;
Console.WriteLine("Executing " + statement);
cmd.CommandText = statement;
cmd.ExecuteNonQuery();
}
Execute(cmd, addColumn);
Execute(cmd, addUniqueConstraint);
Execute(cmd, addForeignKey);
Execute(cmd, dropForeignKey);
Execute(cmd, dropUniqueConstraint);
Execute(cmd, addUniqueIndex);
Execute(cmd, dropUniqueIndex);
Execute(cmd, dropColumn);
Execute(cmd, dropTable);
}
}
}
}