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");
        }