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 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 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 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 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 TestPostgreSqlColumnDefaults()
{
//MigrationGenerator does not output default values for columns
//https://github.com/martinjw/dbschemareader/issues/11
//arrange
var gen = new DdlGeneratorFactory(SqlType.PostgreSql).MigrationGenerator();
gen.IncludeSchema = false;
var pkSeqName = "Seq_PK_Generator";
var pkSeq = new DatabaseSequence()
{
Name = pkSeqName,
MinimumValue = 1,
IncrementBy = 1,
};
//gen.AddSequence(pkSeq).Replace(";", " CACHE;");
var newTable = new DatabaseTable { Name = "TestTable" };
var idColumn = newTable.AddColumn("Id", DbType.Int64);
idColumn.AddPrimaryKey("PK_TestTable");
idColumn.DefaultValue = $"nextval('{pkSeqName}')";
var summaryColumn = newTable.AddColumn("Summary", DbType.String);
summaryColumn.Length = 100;
//act
var ddl = gen.AddTable(newTable);
//assert
//expected
/*
CREATE SEQUENCE "Seq_PK_Generator" INCREMENT BY 1 MINVALUE 1 CACHE;
CREATE TABLE "TestTable"
(
"Id" BIGINT NOT NULL, --default value missing
"Summary" VARCHAR (100) NOT NULL
);
ALTER TABLE "TestTable" ADD CONSTRAINT "PK_TestTable" PRIMARY KEY ("Id");
*/
Assert.IsTrue(ddl.IndexOf("BIGINT NOT NULL DEFAULT nextval('Seq_PK_Generator'),", StringComparison.OrdinalIgnoreCase) != -1, "default value should be included");
}
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 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 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);
}
}
}
}