C# DataSchema.DatabaseSchema类代码示例

        private void GenerateClass(IDictionary<string, string> result, IMap map, DatabaseSchema schema, string domainNamespace) {
            // set up the class and add it in
            var sourceFile = new StringBuilder();
            var constructorStatements = new StringBuilder();
            var className = this.convention.ClassNameForTable(map.Table);
            sourceFile.AppendLine("namespace " + domainNamespace);
            sourceFile.AppendLine("{");
            sourceFile.AppendLine(FourSpaces() + "using System;");
            sourceFile.AppendLine(FourSpaces() + "using System.Collections.Generic;");
            sourceFile.AppendLine();
            sourceFile.AppendLine(FourSpaces() + "public class " + className);
            sourceFile.AppendLine(FourSpaces() + "{");
            sourceFile.AppendLine(FourSpaces(2) + "public " + className + "()");
            sourceFile.AppendLine(FourSpaces(2) + "{");
            int constructorInsertionPoint = sourceFile.Length;
            sourceFile.AppendLine(FourSpaces(2) + "}");
            sourceFile.AppendLine();

            // add in the properties
            this.AddColumn(map.PrimaryKey, sourceFile, constructorStatements, schema);
            foreach (var column in map.Columns.Where(c => c.Key != map.PrimaryKey.Name)) {
                this.AddColumn(column.Value, sourceFile, constructorStatements, schema);
            }

            sourceFile.AppendLine(FourSpaces() + "}");
            sourceFile.AppendLine("}");

            // insert constructor statements
            sourceFile.Insert(constructorInsertionPoint, constructorStatements.ToString());

            result.Add(className, sourceFile.ToString());
        }

        public void DataTypeWithTypeTest()
        {
            //arrange
            var schema = new DatabaseSchema(null, null);
            schema
                .AddTable("Categories")
                .AddColumn<int>("CategoryId").AddPrimaryKey()
                .AddColumn<string>("CategoryName").AddLength(50).AddNullable()
                .AddColumn<decimal>("StockLevel").AddPrecisionScale(8, 2).AddNullable()
                .AddColumn<DateTime>("Updated");

            //assert
            var cats = schema.FindTableByName("Categories");

            var id = cats.PrimaryKeyColumn;
            Assert.AreEqual("INT", id.DbDataType);
            Assert.AreEqual(true, id.DataType.IsInt);

            var catName = cats.FindColumn("CategoryName");

            Assert.AreEqual("NVARCHAR", catName.DbDataType);
            Assert.AreEqual(true, catName.DataType.IsString);

            var stock = cats.FindColumn("StockLevel");

            Assert.AreEqual("DECIMAL", stock.DbDataType);
            Assert.AreEqual(true, stock.DataType.IsNumeric);

            var updated = cats.FindColumn("Updated");

            Assert.AreEqual("DATETIME", updated.DbDataType);
            Assert.AreEqual(true, updated.DataType.IsDateTime);
        }

        public void TestNaturalKey()
        {
            //arrange
            var schema = new DatabaseSchema(null, null);

            var diagrams = schema.AddTable("Diagrams")
                .AddColumn("diagram_id", DbType.Int32).AddPrimaryKey()
                .AddColumn<string>("name").AddLength(10).AddUniqueKey()
                .AddColumn<int>("principal_id")
                .Table;
            diagrams.UniqueKeys.Single().AddColumn(diagrams.FindColumn("principal_id"));

            DatabaseSchemaFixer.UpdateReferences(schema);

            var settings = new CodeWriterSettings { Namespace = "MyTest", CodeTarget = CodeTarget.PocoNHibernateHbm };
            PrepareSchemaNames.Prepare(schema, settings.Namer);

            //act
            var target = new MappingWriter(diagrams, settings);
            var txt = target.Write();

            //assert
            var errors = Validate(txt);
            Assert.IsFalse(errors);
        }

        public void TestSimpleMapping()
        {
            //arrange
            var schema = new DatabaseSchema(null, null);

            schema.AddTable("Categories")
                .AddColumn("CategoryId", DbType.Int32).AddPrimaryKey()
                .AddColumn("CategoryName", DbType.String);

            var products = schema.AddTable("Products")
                .AddColumn("ProductId", DbType.Int32).AddPrimaryKey()
                .AddColumn("ProductName", DbType.String)
                .AddColumn("CategoryId", DbType.Int32).AddForeignKey("fk", "Categories")
                .Table;

            DatabaseSchemaFixer.UpdateReferences(schema);

            var settings = new CodeWriterSettings { Namespace = "MyTest", CodeTarget = CodeTarget.PocoNHibernateHbm };
            PrepareSchemaNames.Prepare(schema, settings.Namer);

            //act
            var target = new MappingWriter(products, settings);
            var txt = target.Write();

            //assert
            var errors = Validate(txt);
            Assert.IsFalse(errors);
        }

        public void TestCompositeKey()
        {
            //arrange
            var schema = new DatabaseSchema(null, null);

            var orderDetail = schema.AddTable("OrderDetails")
                .AddColumn("OrderID", DbType.Int32).AddPrimaryKey()
                .AddColumn("ProductID", DbType.Int32)
                .AddColumn<int>("UnitPrice")
                .Table;
            orderDetail.PrimaryKey.AddColumn(orderDetail.FindColumn("ProductID"));

            DatabaseSchemaFixer.UpdateReferences(schema);

            var settings = new CodeWriterSettings { Namespace = "MyTest", CodeTarget = CodeTarget.PocoNHibernateHbm };
            PrepareSchemaNames.Prepare(schema, settings.Namer);

            //act
            var target = new MappingWriter(orderDetail, settings);
            var txt = target.Write();

            //assert
            var errors = Validate(txt);
            Assert.IsFalse(errors);
        }

        public void WriteTest()
        {
            //arrange
            var schema = new DatabaseSchema(null, null);
            var table = schema.AddTable("Categories")
                .AddColumn("CategoryId", "INT").AddPrimaryKey().AddIdentity()
                .AddColumn("CategoryName", "NVARCHAR").Table;
            //we need datatypes
            schema.DataTypes.Add(new DataType("INT", "System.Int32"));
            schema.DataTypes.Add(new DataType("NVARCHAR", "System.String"));
            DatabaseSchemaFixer.UpdateDataTypes(schema);
            //make sure .Net names are assigned
            PrepareSchemaNames.Prepare(schema, new Namer());

            //inject the custom code inserter
            var codeWriterSettings = new CodeWriterSettings {CodeInserter = new CustomCodeInserter()};
            var cw = new ClassWriter(table, codeWriterSettings);

            //act
            var txt = cw.Write();

            //assert
            Assert.IsTrue(txt.Contains("using System.ComponentModel.DataAnnotations.Schema"));
            Assert.IsTrue(txt.Contains("[Table(\"Categories\")]"));
            Assert.IsTrue(txt.Contains("[Column(\"CategoryId\")]"));
        }

        public void TestReferencedTableViaConstraintName()
        {
            //create a schema
            var schema = new DatabaseSchema(null, SqlType.SqlServer);
            schema.AddTable("Products")
                .AddColumn("ProductId").AddPrimaryKey()
                .AddColumn("ProductName")
                .AddColumn("CategoryId")

                .AddTable("Categories")
                .AddColumn("CategoryId").AddPrimaryKey("CategoryPK")
                .AddColumn("CategoryName")
                ;

            //look at the schema
            var categories = schema.FindTableByName("Categories");
            var products = schema.FindTableByName("Products");
            //from the database we normally get a RefersToTable defined.
            //sometimes we don't- we just get the name of the pk constraint
            //so here we simulate that
            var fk = new DatabaseConstraint
                         {
                             ConstraintType = ConstraintType.ForeignKey,
                             TableName = "Categories",
                             RefersToConstraint = "CategoryPK"
                         };
            fk.Columns.Add("CategoryId");
            products.AddConstraint(fk);

            //act
            var referencedTable = fk.ReferencedTable(schema);

            //assert
            Assert.AreEqual(categories, referencedTable);
        }

        public void GivenUnnamedConstraintsThenStandardNamesAssigned()
        {
            //arrange
            var schema = new DatabaseSchema(null, SqlType.PostgreSql);
            schema
                .AddTable("Category")
                .AddColumn<int>("CategoryId").AddPrimaryKey()
                .AddColumn<string>("CategoryName").AddLength(50).AddNullable()

                .AddTable("Product")
                .AddColumn<int>("Id").AddPrimaryKey()
                .AddColumn<string>("ProductName").AddLength(50).AddUniqueKey()
                .AddColumn<int>("CategoryId").AddForeignKey("Category")
               ;
            var table = schema.FindTableByName("Product");
            var ddlGen = new DdlGeneratorFactory(SqlType.PostgreSql);
            var tabGen = ddlGen.TableGenerator(table);

            //act
            var ddl = tabGen.Write();

            //assert
            var hasPrimaryKey =
                ddl.Contains("ALTER TABLE \"Product\" ADD CONSTRAINT \"Product_Id_pkey\" PRIMARY KEY (\"Id\");");
            var hasUniqueKey =
                ddl.Contains("ALTER TABLE \"Product\" ADD CONSTRAINT \"Product_ProductName_key\" UNIQUE (\"ProductName\");");
            Assert.IsTrue(hasPrimaryKey);
            Assert.IsTrue(hasUniqueKey);
        }

        public void TestForeignKeysCrossSchema()
        {
            //arrange
            var schema = new DatabaseSchema(null, null);
            schema.AddTable("Target")
                .AddColumn<int>("Target_Id").AddPrimaryKey("TARGET_PK")
                .AddColumn<string>("Name").AddLength(10).AddUniqueKey("TARGET_UQ1")

                .AddTable("Source")
                .AddColumn<int>("Source_Id").AddPrimaryKey("SOURCE_PK")
                .AddColumn<int>("Target_Id");
            var source = schema.FindTableByName("Source");
            var target = schema.FindTableByName("Target");
            target.SchemaOwner = "dbo";
            source.SchemaOwner = "other";
            var targetId = source.FindColumn("Target_Id");
            targetId.AddForeignKey("SOURCE_FK", tables => target);

            //act
            var fk = source.ForeignKeys[0];

            //assert
            Assert.AreEqual("Target", fk.RefersToTable, "Name of referenced table");
            Assert.AreEqual(target, fk.ReferencedTable(schema), "Actual reference to table");
            Assert.AreEqual(target.PrimaryKey.Columns, fk.ReferencedColumns(schema), "Columns are matched up");
        }

        public void WriteTest()
        {
            //arrange
            var schema = new DatabaseSchema(null, null);
            var table = schema.AddTable("Categories")
                .AddColumn("CategoryId", "INT").AddPrimaryKey().AddIdentity()
                .AddColumn("CategoryName", "NVARCHAR").Table;
            //we need datatypes
            schema.DataTypes.Add(new DataType("INT", "System.Int32"));
            schema.DataTypes.Add(new DataType("NVARCHAR", "System.String"));
            DatabaseSchemaFixer.UpdateDataTypes(schema);
            //make sure .Net names are assigned
            PrepareSchemaNames.Prepare(schema, new Namer());

            var cw = new ClassWriter(table, new CodeWriterSettings());

            //act
            var txt = cw.Write();

            //assert
            var hasName = txt.Contains("public class Category");
            var hasCategoryId = txt.Contains("public virtual int CategoryId { get; set; }");
            var hasCategoryName = txt.Contains("public virtual string CategoryName { get; set; }");

            Assert.IsTrue(hasName);
            Assert.IsTrue(hasCategoryId);
            Assert.IsTrue(hasCategoryName);
        }

        public IEnumerable<IMap> ReverseEngineer(
            DatabaseSchema schema,
            ISqlDialect sqlDialect,
            IEnumerable<string> tablesToIgnore,
            IAnswerProvider answerProvider,
            bool fixOneToOnes) {
            if (tablesToIgnore == null) {
                tablesToIgnore = new string[0];
            }

            var maps = new List<IMap>();
            this.configuration = new Configuration(sqlDialect);
            foreach (var table in schema.Tables.Where(t => !tablesToIgnore.Contains(t.Name))) {
                maps.Add(this.MapTable(table));
            }

            // go back through and add indexes and foreign keys
            foreach (var map in maps) {
                GetIndexesAndForeignKeys(schema.Tables.First(t => t.Name == map.Table), map);
            }

            // go back through and try to spot one-to-one columns
            if (fixOneToOnes) {
                foreach (var map in maps) {
                    FindOneToOnes(map, answerProvider);
                }
            }

            return maps;
        }

        public void FindOracleAutoNumberTrigger()
        {
            //arrange
            var schema = new DatabaseSchema(null, SqlType.Oracle);
            var table = schema.AddTable("Test");
            var id = table.AddColumn<int>("Id").AddPrimaryKey();
            id.IsAutoNumber = true;
            table.AddColumn<string>("Name").AddLength(200);
            var databaseTrigger = new DatabaseTrigger
                                  {
                                      Name = "Test_INS_TRG",
                                      TriggerEvent = "INSERT",
                                      TriggerBody = @"BEGIN
  SELECT ""Test_SEQ"".NEXTVAL INTO :NEW.""Id"" FROM DUAL;
END;",
                                      TriggerType = "BEFORE EACH ROW",
                                  };
            table.Triggers.Add(databaseTrigger);
            var databaseSequence = new DatabaseSequence { IncrementBy = 1, MinimumValue = 0, Name = "Test_SEQ" };
            schema.Sequences.Add(databaseSequence);

            //act
            var result = OracleSequenceTrigger.FindTrigger(table);

            //assert
            Assert.IsNotNull(result);
            Assert.IsNotNull(result.DatabaseTrigger);
            Assert.IsNotNull(result.DatabaseSequence);
            Assert.AreEqual(databaseSequence, result.DatabaseSequence);
            Assert.AreEqual(databaseTrigger, result.DatabaseTrigger);
        }

        private static DatabaseSchema Arrange()
        {
            var schema = new DatabaseSchema(null, null);
            schema.AddTable("Categories")
                  .AddColumn<int>("CategoryId").AddPrimaryKey()
                  .AddColumn<string>("CategoryName")

                  .AddTable("Products")
                  .AddColumn<int>("ProductId").AddPrimaryKey()
                  .AddColumn<string>("ProductName");

            var assoc =
                schema.AddTable("ProductCategories")
                  .AddColumn<int>("CategoryId").AddPrimaryKey().AddForeignKey("Categories")
                  .AddColumn<int>("ProductId").AddForeignKey("Products")
                  .Table;

            assoc.PrimaryKey.AddColumn(assoc.FindColumn("ProductId"));

            DatabaseSchemaFixer.UpdateDataTypes(schema);
            //make sure .Net names are assigned
            PrepareSchemaNames.Prepare(schema, new Namer());

            return schema;
        }

        public void WhenSecondSchemaIsEmptyThenEverythingIsDropped()
        {
            //arrange
            var schema1 = TestHelper.GetNorthwindReader().ReadAll();
            var schema2 = new DatabaseSchema(null, null);

            //act
            var comparison = new CompareSchemas(schema1, schema2);
            var script = comparison.Execute();

            //assert
            Assert.AreNotEqual(string.Empty, script);
            if (schema1.Tables.Any())
            {
                Assert.IsTrue(script.Contains("DROP TABLE"));
            }
            if (schema1.Views.Any())
            {
                Assert.IsTrue(script.Contains("DROP VIEW"));
            }
            if (schema1.StoredProcedures.Any())
            {
                Assert.IsTrue(script.Contains("DROP PROCEDURE"));
            }
        }

        public void TestOracleTableWithTrigger()
        {
            //arrange
            var schema = new DatabaseSchema(null, SqlType.Oracle);
            var table = schema.AddTable("Test");
            var id = table.AddColumn<int>("Id").AddPrimaryKey();
            id.IsAutoNumber = true;
            table.AddColumn<string>("Name").AddLength(200);
            table.Triggers.Add(new DatabaseTrigger
                               {
                                   Name = "Test_INS_TRG",
                                   TriggerEvent = "INSERT",
                                   TriggerBody = @"BEGIN
  SELECT ""Test_SEQ"".NEXTVAL INTO :NEW.""Id"" FROM DUAL;
END;",
                                   TriggerType = "BEFORE EACH ROW",
                               });
            var tableGen = new TableGenerator(table);

            //act
            var ddl = tableGen.Write();

            //assert
            Assert.IsTrue(ddl.Contains("\"Id\" NUMBER (9) NOT NULL,"), "Table should include Id column " + ddl);
            //line breaks may cause environmental differences
            Assert.IsTrue(ddl.Contains(@"CREATE OR REPLACE TRIGGER ""Test_INS_TRG"""), "Table should include 'create trigger' " + ddl);
            Assert.IsTrue(ddl.Contains(@"SELECT ""Test_SEQ"".NEXTVAL INTO :NEW.""Id"" FROM DUAL;"), "Table should include trigger body " + ddl);
        }