C# DatabaseSchema.FindTableByName方法代码示例

        public void TestForeignKeysToUniqueKey()
        {
            //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<string>("Target_Name");
            var source = schema.FindTableByName("Source");
            var target = schema.FindTableByName("Target");
            source.AddConstraint(new DatabaseConstraint
                                     {
                                         ConstraintType = ConstraintType.ForeignKey,
                                         Name = "SOURCE_FK",
                                         RefersToTable = "Target",
                                         TableName = "Source",
                                         RefersToConstraint = "TARGET_UQ1",
                                     });
            var fk = source.ForeignKeys[0];
            fk.Columns.Add("Target_Name");

            //act
            var referencedColumns = fk.ReferencedColumns(schema);

            //assert
            Assert.AreEqual(target.UniqueKeys[0].Columns, referencedColumns, "Columns are matched up");
        }

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

 /// <summary>
 /// Finds the object in the specified database schema, or NULL
 /// </summary>
 /// <param name="databaseSchema">The database schema.</param>
 /// <returns></returns>
 /// <exception cref="System.ArgumentNullException">databaseSchema</exception>
 /// <exception cref="System.InvalidOperationException">
 /// Name required
 /// or
 /// TableName required
 /// </exception>
 /// <exception cref="System.ArgumentOutOfRangeException"></exception>
 public INamedObject Find(DatabaseSchema databaseSchema)
 {
     if (databaseSchema == null) throw new ArgumentNullException("databaseSchema");
     if (string.IsNullOrEmpty(Name)) throw new InvalidOperationException("Name required");
     DatabaseTable table;
     switch (SchemaObjectType)
     {
         case SchemaObjectType.Table:
             return databaseSchema.FindTableByName(Name, SchemaOwner);
         case SchemaObjectType.View:
             return databaseSchema.Views.Find(v => Name.Equals(v.Name, StringComparison.OrdinalIgnoreCase) &&
                 string.Equals(v.SchemaOwner, SchemaOwner, StringComparison.OrdinalIgnoreCase));
         case SchemaObjectType.Column:
             if (string.IsNullOrEmpty(TableName)) throw new InvalidOperationException("TableName required");
             table = databaseSchema.FindTableByName(TableName, SchemaOwner);
             return table.FindColumn(Name);
         case SchemaObjectType.Constraint:
             if (string.IsNullOrEmpty(TableName)) throw new InvalidOperationException("TableName required");
             table = databaseSchema.FindTableByName(TableName, SchemaOwner);
             if (table.PrimaryKey != null && table.PrimaryKey.Name == Name) return table.PrimaryKey;
             var constraint = table.ForeignKeys.FindByName(Name);
             if (constraint != null) return constraint;
             constraint = table.CheckConstraints.FindByName(Name);
             if (constraint != null) return constraint;
             constraint = table.UniqueKeys.FindByName(Name);
             if (constraint != null) return constraint;
             //shouldn't fall through to here
             return null;
         case SchemaObjectType.Index:
             if (string.IsNullOrEmpty(TableName)) throw new InvalidOperationException("TableName required");
             table = databaseSchema.FindTableByName(TableName, SchemaOwner);
             return table.Indexes.Find(x => Name.Equals(x.Name, StringComparison.OrdinalIgnoreCase));
         case SchemaObjectType.Trigger:
             if (string.IsNullOrEmpty(TableName)) throw new InvalidOperationException("TableName required");
             table = databaseSchema.FindTableByName(TableName, SchemaOwner);
             return table.Triggers.Find(x => Name.Equals(x.Name, StringComparison.OrdinalIgnoreCase));
         case SchemaObjectType.StoredProcedure:
             return databaseSchema.StoredProcedures.Find(x => Name.Equals(x.Name, StringComparison.OrdinalIgnoreCase) &&
                 string.Equals(x.SchemaOwner, SchemaOwner, StringComparison.OrdinalIgnoreCase));
         case SchemaObjectType.Function:
             return databaseSchema.Functions.Find(x => Name.Equals(x.Name, StringComparison.OrdinalIgnoreCase) &&
                 string.Equals(x.SchemaOwner, SchemaOwner, StringComparison.OrdinalIgnoreCase));
         case SchemaObjectType.Sequence:
             return databaseSchema.Sequences.Find(x => Name.Equals(x.Name, StringComparison.OrdinalIgnoreCase) &&
                 string.Equals(x.SchemaOwner, SchemaOwner, StringComparison.OrdinalIgnoreCase));
         case SchemaObjectType.Package:
             return databaseSchema.Packages.Find(x => Name.Equals(x.Name, StringComparison.OrdinalIgnoreCase) &&
                 string.Equals(x.SchemaOwner, SchemaOwner, StringComparison.OrdinalIgnoreCase));
         default:
             throw new ArgumentOutOfRangeException();
     }
 }

        public void TestCompositeKeys()
        {
            //arrange
            var schema = new DatabaseSchema(null, null);
            schema.AddTable("Store")
                .AddColumn<int>("Store_Id").AddPrimaryKey("Store_PK")
                .AddColumn<string>("Name").AddLength(10)

                .AddTable("StoreSale")
                .AddColumn<int>("Store_Id").AddForeignKey("Store_FK", "Store")
                .AddColumn<int>("StoreSale_Id")

                .AddTable("StoreSaleDetail")
                .AddColumn<int>("Store_Id").AddForeignKey("Store_FK", "Store")
                .AddColumn<int>("StoreSale_Id")
                .AddColumn<int>("StoreSaleDetail_Id")
                ;
            var store = schema.FindTableByName("Store");
            var storeSale = schema.FindTableByName("StoreSale");
            var storeSaleDetail = schema.FindTableByName("StoreSaleDetail");
            var pk1 = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey };
            pk1.Columns.Add("Store_Id");
            pk1.Columns.Add("StoreSale_Id");
            storeSale.AddConstraint(pk1);

            var pk2 = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey };
            pk2.Columns.Add("Store_Id");
            pk2.Columns.Add("StoreSale_Id");
            pk2.Columns.Add("StoreSaleDetail_Id");
            storeSaleDetail.AddConstraint(pk2);

            var fk = new DatabaseConstraint { ConstraintType = ConstraintType.ForeignKey, RefersToTable = "StoreSale" };
            fk.Columns.Add("Store_Id");
            fk.Columns.Add("StoreSale_Id");
            storeSaleDetail.AddConstraint(fk);

            //act
            DatabaseSchemaFixer.UpdateReferences(schema);

            //assert
            Assert.AreEqual(2, store.ForeignKeyChildren.Count, "Store is target of foreign keys from StoreSale and StoreSaleDetail");
            Assert.AreEqual(1, storeSale.ForeignKeyChildren.Count, "StoreSale is target of foreign key from StoreSaleDetail");

            var storeId = storeSaleDetail.FindColumn("Store_Id");
            Assert.AreEqual(2, storeId.ForeignKeyTableNames.Count, "StoreSaleDetail.StoreId is fk to both Store and StoreSale");
            var storeSaleId = storeSaleDetail.FindColumn("StoreSale_Id");
            Assert.AreEqual(1, storeSaleId.ForeignKeyTableNames.Count, "StoreSaleDetail.StoreSale_Id is fk to StoreSale");

        }

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

                .AddTable("Product")
                .AddColumn<int>("Id").AddPrimaryKey()
                .AddColumn<int>("CategoryId").AddForeignKey("Category")

                .AddTable("Vendor")
                .AddColumn<int>("Id").AddPrimaryKey()
                .AddColumn<int>("CategoryId").AddForeignKey("Category")
               ;

            //act
            schema.RemoveTable("Category");

            //assert
            Assert.AreEqual(2, schema.Tables.Count);
            var prod = schema.FindTableByName("Product");
            Assert.AreEqual(0, prod.ForeignKeys.Count);
            var categoryId = prod.FindColumn("CategoryId");
            Assert.IsFalse(categoryId.IsForeignKey);
        }

        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 TestExtensions()
        {
            //a simple fluent interface for creating the schema in memory
            var schema = new DatabaseSchema(null, null);
            schema
                .AddTable("Categories")
                //chaining adding pk and identity
                .AddColumn("CategoryId", "INT").AddPrimaryKey().AddIdentity()
                //chaining from one column to next, with full access to the new column
                .AddColumn("CategoryName", "VARCHAR", c => c.Length = 30)

                //chaining from a column to the next table
                .AddTable("Products")
                .AddColumn("ProductId", "INT").AddIdentity().AddPrimaryKey("PK_PRODUCTS")
                //add additional properties to column
                .AddColumn("ProductName", "VARCHAR", c =>
                                                         {
                                                             c.Length = 30;
                                                             c.Nullable = true;
                                                         })
                //adding a column directly
                .AddColumn(new DatabaseColumn { Name = "Price", DbDataType = "DECIMAL", Nullable = true })
                //adding a fk
                .AddColumn("CategoryId", "INT")
                    .AddForeignKey("FK_CATEGORY", tables => tables.Where(x => x.Name == "Categories").First());

            //assert
            Assert.AreEqual(2, schema.Tables.Count);

            var cats = schema.FindTableByName("Categories");
            Assert.AreEqual(2, cats.Columns.Count);
            Assert.IsNotNull(cats.PrimaryKey);
            Assert.IsNotNull(cats.PrimaryKeyColumn);
            var pk = cats.PrimaryKeyColumn;
            Assert.IsTrue(pk.IsAutoNumber);
            Assert.AreEqual(1, cats.ForeignKeyChildren.Count);


            var prods = schema.FindTableByName("Products");
            Assert.AreEqual(4, prods.Columns.Count);
            Assert.AreEqual(1, prods.ForeignKeys.Count);
            var fk = prods.ForeignKeys[0];
            Assert.AreEqual(cats, fk.ReferencedTable(schema));
        }

        public void TestFindByName()
        {
            var schema = new DatabaseSchema(null, null);
            schema.AddTable("Orders")
                .AddTable("Products");

            var table = schema.FindTableByName("products");
            Assert.IsNotNull(table);
            Assert.AreEqual("Products", table.Name);
        }

        public void TestManyToManyNavigator()
        {
            //arrange

            //Playlist and Track with an association table, PlaylistTrack
            var schema = new DatabaseSchema(null, null);
            schema
                .AddTable("Playlist")
                .AddColumn("PlaylistId", DbType.Int32)
                .AddPrimaryKey().AddIdentity()
                .AddColumn("Name", DbType.String).AddLength(120)

                .AddTable("Track")
                .AddColumn("TrackId", DbType.Int32)
                .AddIdentity().AddPrimaryKey()
                .AddColumn("Name", DbType.String).AddLength(200)

                .AddTable("PlaylistTrack")
                .AddColumn("TrackId", DbType.Int32).AddPrimaryKey()
                .AddForeignKey("FK_PlaylistTrack_Track", "Track")
                .AddColumn("PlaylistId", DbType.Int32).AddPrimaryKey()
                .AddForeignKey("FK_PlaylistTrack_Playlist", "Playlist");

            var associationTable = schema.FindTableByName("PlaylistTrack");
            var playlist = schema.FindTableByName("Playlist");
            var track = schema.FindTableByName("Track");

            //act
            var isManyToMany = associationTable.IsManyToManyTable();
            var isNotManyToMany = playlist.IsManyToManyTable();

            var playlistToTrack = associationTable.ManyToManyTraversal(playlist);
            var trackToPlaylist = associationTable.ManyToManyTraversal(track);

            //assert
            Assert.IsTrue(isManyToMany);
            Assert.IsFalse(isNotManyToMany);

            Assert.AreEqual(track, playlistToTrack);
            Assert.AreEqual(playlist, trackToPlaylist);
        }

        public void TestReferencedTableViaRefersToTable()
        {
            //create a schema
            var schema = new DatabaseSchema(null, SqlType.SqlServer);
            schema.AddTable("Categories")
                .AddColumn("CategoryId").AddPrimaryKey("CategoryPK")
                .AddColumn("CategoryName")

                .AddTable("Products")
                .AddColumn("ProductId").AddPrimaryKey()
                .AddColumn("ProductName")
                .AddColumn("CategoryId").AddForeignKey("Categories");

            //look at the schema
            var categories = schema.FindTableByName("Categories");
            var products = schema.FindTableByName("Products");
            var fk = products.ForeignKeys.First();

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

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

        public void GivenTimeSpan()
        {
            //arrange
            var schema = new DatabaseSchema(null, SqlType.PostgreSql);
            schema
                .AddTable("Category")
                .AddColumn<int>("CategoryId").AddPrimaryKey()
               ;

            var table = schema.FindTableByName("Category");

            //act
            var col = table.AddColumn("Timespan", typeof(TimeSpan));

            //assert
            Assert.AreEqual("INTERVAL", col.DbDataType);
        }

        public void GivenDateTimeOffset()
        {
            //arrange
            var schema = new DatabaseSchema(null, SqlType.PostgreSql);
            schema
                .AddTable("Category")
                .AddColumn<int>("CategoryId").AddPrimaryKey()
               ;

            var table = schema.FindTableByName("Category");

            //act
            var col = table.AddColumn("DateTimeOffset", typeof(DateTimeOffset));

            //assert
            Assert.AreEqual("TIMESTAMP WITH TIME ZONE", col.DbDataType);
        }

        public void PrimaryKeyTest()
        {
            var schema = new DatabaseSchema(null, null);
            schema
                .AddTable("Categories")
                .AddColumn("CategoryId", "INT").AddPrimaryKey()
                .AddColumn("CategoryName", "NVARCHAR").AddLength(50);

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

            var id = cats.PrimaryKeyColumn;
            Assert.IsNotNull(id);
            Assert.AreEqual("INT", id.DbDataType);
            Assert.AreEqual(true, id.IsPrimaryKey);
            Assert.AreEqual(false, id.Nullable);
        }

        public void GivenByte()
        {
            //arrange
            var schema = new DatabaseSchema(null, SqlType.PostgreSql);
            schema
                .AddTable("Category")
                .AddColumn<int>("CategoryId").AddPrimaryKey()
               ;

            var table = schema.FindTableByName("Category");

            //act
            var col = table.AddColumn("Byte", typeof(Byte));

            //assert
            Assert.AreEqual("NUMBER", col.DbDataType);
            Assert.AreEqual(1, col.Precision);
        }