public void TestSqlServerWithChangingDefault()
{
//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 = "'?'";
//create an "original" version of the column (by cloning)
var originalColumn = column.Clone();
originalColumn.DefaultValue = "'UNKNOWN'";
//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.AlterColumn(table, column, originalColumn);
//assert
//ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [DF_Orders_Name];
//-- Orders from [NAME] VARCHAR (40) NOT NULL to [NAME] VARCHAR (40) NOT NULL
//ALTER TABLE [dbo].[Orders] ALTER COLUMN [NAME] VARCHAR (40) NOT NULL;
//ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_Name] DEFAULT '?' FOR [NAME];
Assert.IsTrue(sql.Contains("DROP CONSTRAINT [DF_Orders_Name]"), "drop constraint");
Assert.IsTrue(sql.Contains("ADD CONSTRAINT [DF_Orders_Name] DEFAULT '?' FOR [NAME]"), "add default constraint");
}
public bool RunTableDdl(DirectoryInfo directory, SqlType dialect)
{
var tg = new DdlGeneratorFactory(dialect).AllTablesGenerator(_databaseSchema);
tg.IncludeSchema = false;
string txt;
try
{
txt = tg.Write();
}
catch (Exception exception)
{
Message =
@"An error occurred while creating the script.\n" + exception.Message;
return false;
}
try
{
var path = Path.Combine(directory.FullName, "table.sql");
File.WriteAllText(path, txt);
Message = @"Wrote " + path;
return true;
}
catch (Exception exception)
{
Message =
@"An IO error occurred while writing the file.\n" + exception.Message;
}
return false;
}
public bool RunSprocs(DirectoryInfo directory, SqlType dialect, DatabaseTable table)
{
if (table == null)
{
Message = "No table";
return false;
}
var gen = new DdlGeneratorFactory(dialect).ProcedureGenerator(table);
if (gen == null)
{
//there is no sproc provider (SQLite)
Message = @"There is no sproc generator";
return false;
}
var path = Path.Combine(directory.FullName, table.Name + "_sprocs.sql");
try
{
gen.WriteToScript(path);
Message = @"Wrote " + path;
return true;
}
catch (Exception exception)
{
Message =
@"An error occurred while creating the script.\n" + exception.Message;
}
return false;
}
public void TestWritingNorthwindTables()
{
var schemas = LoadCategoriesFromNorthwind().DatabaseSchema;
//take a SQLServer Northwind and write all the tables and relations
var gen = new DdlGeneratorFactory(SqlType.SqlServer).AllTablesGenerator(schemas);
var txt = gen.Write();
Assert.IsFalse(string.IsNullOrEmpty(txt), "Should have written some text");
//let's translate it into Oracle.
var oracleGen = new DdlGeneratorFactory(SqlType.Oracle).AllTablesGenerator(schemas);
oracleGen.IncludeSchema = false; //we don't want "dbo." prefixes
txt = oracleGen.Write();
Assert.IsFalse(string.IsNullOrEmpty(txt), "Should have written some text");
//let's translate it into MySQL.
var mysqlGen = new DdlGeneratorFactory(SqlType.MySql).AllTablesGenerator(schemas);
mysqlGen.IncludeSchema = false; //we don't want "dbo." prefixes
var mySqlTxt = mysqlGen.Write();
Assert.IsFalse(string.IsNullOrEmpty(mySqlTxt), "Should have written some text");
//let's translate it into SQLite.
var sqliteGen = new DdlGeneratorFactory(SqlType.SQLite).AllTablesGenerator(schemas);
sqliteGen.IncludeSchema = false; //we don't want "dbo." prefixes
var sqliteTxt = sqliteGen.Write();
Assert.IsFalse(string.IsNullOrEmpty(sqliteTxt), "Should have written some text");
//manually check the script is ok
}
public void TestMigration()
{
//arrange
var tableName = MigrationCommon.FindFreeTableName(ProviderName, ConnectionString);
var migration = new DdlGeneratorFactory(SqlType.Db2).MigrationGenerator();
MigrationCommon.ExecuteScripts(ProviderName, ConnectionString, tableName, migration);
}
public void TestMigration()
{
var databaseFile = ConnectionStrings.SqLiteFilePath;
var connectionString = "Data Source=" + databaseFile;
//arrange
var tableName = MigrationCommon.FindFreeTableName(ProviderName, connectionString);
var migration = new DdlGeneratorFactory(SqlType.SQLite).MigrationGenerator();
MigrationCommon.ExecuteScripts(ProviderName, connectionString, tableName, migration);
}
public void TestMigration()
{
//arrange
var connectionString = ConnectionStrings.MySql;
var tableName = MigrationCommon.FindFreeTableName(ProviderName, connectionString);
var migration = new DdlGeneratorFactory(SqlType.MySql).MigrationGenerator();
//MySql DDL isn't transactional. Hope this works.
MigrationCommon.ExecuteScripts(ProviderName, connectionString, tableName, migration);
}
public void TestMigration()
{
//arrange
const string connectionString = ConnectionStrings.OracleHr;
var tableName = MigrationCommon.FindFreeTableName(ProviderName, connectionString);
var migration = new DdlGeneratorFactory(SqlType.Oracle).MigrationGenerator();
//Oracle DDL isn't transactional. If it fails, you'll find my test tables in your database.
MigrationCommon.ExecuteScripts(ProviderName, connectionString, tableName, migration);
}
public void TestWritingSqlTableIntoOracleTable()
{
var table = LoadCategoriesFromNorthwind();
//take a SQLServer table and create Oracle table DDL
var gen = new DdlGeneratorFactory(SqlType.Oracle).TableGenerator(table);
var txt = gen.Write();
Assert.IsFalse(string.IsNullOrEmpty(txt), "Should have written some text");
//manually check the script is ok
}
public void BuildAllTableDdl(DatabaseSchema databaseSchema)
{
var tg = new DdlGeneratorFactory(_sqlType).AllTablesGenerator(databaseSchema);
tg.IncludeSchema = false;
try
{
var txt = tg.Write();
Clipboard.SetText(txt, TextDataFormat.UnicodeText);
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
}
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";
var column = CreateNewColumn();
//act
var sql = migration.AddColumn(table, column);
//assert
Assert.IsTrue(sql.StartsWith("ALTER TABLE \"dbo\".\"Orders\" ADD \"COUNTRY\" VARCHAR (20)", 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 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 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 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 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 TestWritingCrudSprocs()
{
var table = LoadCategoriesFromNorthwind();
//let's create the SQLServer crud procedures
var gen = new DdlGeneratorFactory(SqlType.SqlServer).ProcedureGenerator(table);
gen.ManualPrefix = table.Name + "__";
var destination = TestHelper.CreateDirectory("sql").FullName;
var path = Path.Combine(destination, "sqlserver_sprocs.sql");
gen.WriteToScript(path);
var txt = File.ReadAllText(path);
Assert.IsFalse(string.IsNullOrEmpty(txt), "Should have written some text");
//manually check the script is ok
}
public void TestDb2()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.Db2).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 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 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 TestDb2()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.Db2).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");
}
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 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 TestOracleNoSchema()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.Oracle).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
table.Name = "NewOrders";
//act
migration.IncludeSchema = false;
var sql = migration.RenameTable(table, "OldName");
//assert
Assert.IsTrue(sql.Contains("ALTER TABLE \"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 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 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 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 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");
}