public void TestSqlServerDropColumnWithDefault()
{
//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"
};
df.Columns.Add("NAME");
table.AddConstraint(df);
//act
var sql = migration.DropColumn(table, column);
//assert
// No longer true for SQL server - DROP CONSTRAINT is done by constraint deltas
//ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [DF_Orders_Name];
//ALTER TABLE [dbo].[Orders] DROP COLUMN [NAME];
//Assert.IsTrue(sql.Contains("DROP CONSTRAINT [DF_Orders_Name]"), "drop constraint");
Assert.IsTrue(sql.Contains("DROP COLUMN [NAME]"), "drop column");
}
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 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 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 TestMySqlWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.MySql).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = MigrationCommon.CreateNewColumn();
//act
var sql = migration.DropColumn(table, column);
//assert
Assert.IsTrue(sql.StartsWith("ALTER TABLE `dbo`.`Orders` DROP COLUMN `COUNTRY`", StringComparison.OrdinalIgnoreCase), "names should be quoted correctly");
}
public void TestSqLite()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SQLite).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = MigrationCommon.CreateNewColumn();
//act
var sql = migration.DropColumn(table, column);
//assert
Assert.IsTrue(sql.StartsWith("--", StringComparison.OrdinalIgnoreCase), "Not available in SQLite");
}
public void TestMySqlWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.MySql).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
table.Name = "NewOrders";
//act
var sql = migration.RenameTable(table, "OldName");
//assert
Assert.IsTrue(sql.Contains("RENAME TABLE `dbo`.`OldName` TO `NewOrders`"), "names should be quoted correctly");
}
public void TestOracleWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.Oracle).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
table.Name = "NewOrders";
//act
var sql = migration.RenameTable(table, "OldName");
//assert
Assert.IsTrue(sql.Contains("ALTER TABLE \"dbo\".\"OldName\" RENAME TO \"NewOrders\""), "names should be quoted correctly");
}
public void TestSqlServerWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
table.Name = "NewOrders";
//act
var sql = migration.RenameTable(table, "OldName");
//assert
Assert.IsTrue(sql.Contains("sp_rename '[dbo].[OldName]', '[NewOrders]'"), "names should be quoted correctly");
}
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 TestOracleWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.Oracle).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = CreateNewColumn();
//act
var sql = migration.AddColumn(table, column);
//assert
Assert.IsTrue(sql.StartsWith("ALTER TABLE \"dbo\".\"Orders\" ADD \"COUNTRY\" NVARCHAR2 (20)", StringComparison.OrdinalIgnoreCase), "names should be quoted correctly");
}
public void TestSqLite()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SQLite).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = CreateNewColumn();
//act
var sql = migration.AddColumn(table, column);
//assert
Assert.IsTrue(sql.StartsWith("ALTER TABLE [Orders] ADD [COUNTRY] TEXT;", StringComparison.OrdinalIgnoreCase), "names should be quoted correctly");
}
public void TestOracleWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.Oracle).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 TestOracleCreateTableWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.Oracle).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
//act
var sql = migration.AddTable(table);
//assert
Assert.IsTrue(sql.StartsWith("CREATE TABLE \"dbo\".\"Orders\"", StringComparison.OrdinalIgnoreCase), "table name should be quoted correctly");
Assert.IsTrue(sql.Contains("ALTER TABLE \"dbo\".\"Orders\" ADD CONSTRAINT \"PK_Orders\" PRIMARY KEY (\"Id\")"), "Primary key should be set with name");
}
public void TestMySqlCreateTableWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.MySql).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
//act
var sql = migration.AddTable(table);
//assert
Assert.IsTrue(sql.StartsWith("CREATE TABLE `dbo`.`Orders`", StringComparison.OrdinalIgnoreCase), "table name should be quoted correctly");
Assert.IsTrue(sql.Contains("`Id` INT NOT NULL PRIMARY KEY,"), "In MySQL we don't set the primary key with a name, because it seems to be rarely done");
}
public void TestSqLite()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SQLite).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
table.Name = "NewOrders";
//act
var sql = migration.RenameTable(table, "OldName");
//assert
Assert.IsTrue(sql.StartsWith("ALTER TABLE [OldName] RENAME TO [NewOrders]", StringComparison.OrdinalIgnoreCase), "Sqlite rename");
}
public void TestDb2()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.Db2).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
//act
var sql = migration.AddTable(table);
//assert
Assert.IsTrue(sql.StartsWith("CREATE TABLE \"dbo\".\"Orders\"", StringComparison.OrdinalIgnoreCase), "table name should be quoted correctly");
Assert.IsTrue(sql.Contains("\"Id\" INTEGER NOT NULL PRIMARY KEY"), "Primary key is not set with name");
}
public void TestMySqlCreateTableNoSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.MySql).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
//act
migration.IncludeSchema = false;
var sql = migration.AddTable(table);
//assert
Assert.IsTrue(sql.StartsWith("CREATE TABLE `Orders`", StringComparison.OrdinalIgnoreCase), "table name should be quoted correctly");
}
public void TestMySqlNoSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.MySql).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = CreateNewColumn();
//act
migration.IncludeSchema = false;
var sql = migration.AddColumn(table, column);
//assert
Assert.IsTrue(sql.StartsWith("ALTER TABLE `Orders` ADD `COUNTRY` VARCHAR (20)", StringComparison.OrdinalIgnoreCase), "names should be quoted correctly");
}
public void TestOracleWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.Oracle).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = MigrationCommon.CreateNewColumn();
var index = MigrationCommon.CreateUniqueIndex(column, "COUNTRY");
//act
var sql = migration.AddIndex(table, index);
//assert
Assert.IsTrue(sql.StartsWith("CREATE UNIQUE INDEX \"UI_COUNTRY\" ON \"dbo\".\"Orders\"(\"COUNTRY\")", StringComparison.OrdinalIgnoreCase), "names should be quoted correctly");
}
public void TestSqLiteCreateTable()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SQLite).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
//act
//migration.IncludeSchema = false; //By default, include schema should be false
var sql = migration.AddTable(table);
//assert
Assert.IsTrue(sql.StartsWith("CREATE TABLE [Orders]", StringComparison.OrdinalIgnoreCase), "table name should be quoted correctly");
Assert.IsTrue(sql.Contains("[Id] INTEGER PRIMARY KEY NOT NULL"), "Primary key is set without name");
}
public void TestOracleNoSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.Oracle).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = MigrationCommon.CreateNewColumn();
//act
migration.IncludeSchema = false;
var sql = migration.DropColumn(table, column);
//assert
Assert.IsTrue(sql.StartsWith("ALTER TABLE \"Orders\" DROP COLUMN \"COUNTRY\"", StringComparison.OrdinalIgnoreCase), "names should be quoted correctly");
}
public void TestSqlServerCreateTableNoSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
//act
migration.IncludeSchema = false;
var sql = migration.AddTable(table);
//assert
Assert.IsTrue(sql.StartsWith("CREATE TABLE [Orders]", StringComparison.OrdinalIgnoreCase), "table name should be quoted correctly");
Assert.IsTrue(sql.Contains("ALTER TABLE [Orders] ADD CONSTRAINT [PK_Orders] PRIMARY KEY ([Id])"), "Primary key should be set with name");
}
public void TestMySqlWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.MySql).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = MigrationCommon.CreateNewColumn();
var index = MigrationCommon.CreateUniqueIndex(column, "COUNTRY");
//act
var sql = migration.DropIndex(table, index);
//assert
Assert.IsTrue(sql.StartsWith("DROP INDEX `UI_COUNTRY` ON `dbo`.`Orders`", StringComparison.OrdinalIgnoreCase), "names should be quoted correctly");
}
public void TestSqlServerWithNoDefault()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = table.FindColumn("NAME");
column.Length = 40;
//act
var sql = migration.AlterColumn(table, column, null);
//assert
Assert.IsFalse(sql.Contains("ADD CONSTRAINT [DF_Orders_NAME] DEFAULT"), "no add default constraint");
}
public void TestMySqlWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.MySql).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = table.FindColumn("NAME");
column.Name = "NEWNAME";
//act
var sql = migration.RenameColumn(table, column, "OldName");
//assert
Assert.IsTrue(sql.Contains("ALTER TABLE `dbo`.`Orders` CHANGE `OldName` `NEWNAME` VARCHAR (10) NOT NULL"), "names should be quoted correctly");
}
public void TestMySqlWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.MySql).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = table.FindColumn("NAME");
column.Length = 40;
//act
var sql = migration.AlterColumn(table, column, null);
//assert
Assert.IsTrue(sql.Contains("ALTER TABLE `dbo`.`Orders` MODIFY `NAME` VARCHAR (40)"), "names should be quoted correctly");
}
public void TestSqLite()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SQLite).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = table.FindColumn("NAME");
column.Length = 40;
//act
var sql = migration.AlterColumn(table, column, null);
//assert
Assert.IsTrue(sql.StartsWith("--", StringComparison.OrdinalIgnoreCase), "Cannot be changed in SQLite");
}
public void TestSqlServerWithSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = table.FindColumn("NAME");
column.Name = "NEWNAME";
//act
var sql = migration.RenameColumn(table, column, "OldName");
//assert
Assert.IsTrue(sql.Contains("sp_rename '[dbo].[Orders].[OldName]', '[NEWNAME]', 'COLUMN'"), "names should be quoted correctly");
}
public void TestPostgreSql()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.PostgreSql).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = table.FindColumn("NAME");
column.Name = "NEWNAME";
//act
var sql = migration.RenameColumn(table, column, "OldName");
//assert
Assert.IsTrue(sql.Contains("ALTER TABLE \"dbo\".\"Orders\" RENAME COLUMN \"OldName\" TO \"NEWNAME\""), "names should be quoted correctly");
}
