C# DdlGeneratorFactory.AddTable方法代码示例

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