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 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 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"); }
/// <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); }
/// <summary> /// Updates the references of child objects to this database /// </summary> /// <remarks> /// Should be safe to call twice. /// </remarks> public static void UpdateReferences(DatabaseSchema databaseSchema) { databaseSchema.Tables.ForEach(delegate(DatabaseTable table) { table.DatabaseSchema = databaseSchema; table.Columns.ForEach(delegate(DatabaseColumn c) { //fix the bidirectional references c.DatabaseSchema = databaseSchema; c.Table = table; //foreign keys //if (string.IsNullOrEmpty(c.ForeignKeyTableName)) return; if (c.ForeignKeyTableNames.Count == 0) { return; } foreach (var fkTableName in c.ForeignKeyTableNames) { DatabaseTable fkTable = databaseSchema.FindTableByName(fkTableName); if (fkTable == null) { continue; } c.ForeignKeyTable = fkTable; if (!fkTable.ForeignKeyChildren.Contains(table)) { fkTable.ForeignKeyChildren.Add(table); } } }); }); //update schema databaseSchema.Views.ForEach(delegate(DatabaseView view) { view.DatabaseSchema = databaseSchema; view.Columns.ForEach(delegate(DatabaseColumn c) { //fix the bidirectional references c.DatabaseSchema = databaseSchema; c.Table = view; }); }); databaseSchema.StoredProcedures.ForEach(delegate(DatabaseStoredProcedure sproc) { sproc.DatabaseSchema = databaseSchema; }); if (databaseSchema.Packages.Count > 0) { UpdatePackages(databaseSchema); } UpdateDataTypes(databaseSchema); }
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); }
/// <summary> /// Removes the table from the schema and also all foreign key references. /// </summary> /// <param name="databaseSchema">The database schema.</param> /// <param name="tableName">Name of the table.</param> public static void RemoveTable(this DatabaseSchema databaseSchema, string tableName) { if (databaseSchema == null) { throw new ArgumentNullException("databaseSchema", "databaseSchema must not be null"); } if (string.IsNullOrEmpty(tableName)) { throw new ArgumentNullException("tableName", "tableName must not be null"); } var table = databaseSchema.FindTableByName(tableName); RemoveTable(databaseSchema, table); }
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 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 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 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); }
public void TestGeneratedSqlForSelectAll() { //arrange var schema = new DatabaseSchema(null, SqlType.SqlServer); schema.AddTable("Category").AddColumn<int>("Id").AddPrimaryKey() .AddColumn<string>("FirstName").AddLength(10) .Table.SchemaOwner = "first"; schema.AddTable("Category").AddColumn<int>("Id").AddPrimaryKey() .AddColumn<string>("SecondName").AddLength(20) .Table.SchemaOwner = "second"; var table = schema.FindTableByName("Category"); //this will find one of them var writer = new SqlWriter(table, SqlType.SqlServer); //act var sql = writer.SelectAllSql(); //assert }
/// <summary> /// Updates the references of child objects to this database /// </summary> /// <remarks> /// Should be safe to call twice. /// </remarks> public static void UpdateReferences(DatabaseSchema databaseSchema) { databaseSchema.Tables.ForEach(delegate(DatabaseTable table) { table.DatabaseSchema = databaseSchema; table.Columns.ForEach(delegate(DatabaseColumn c) { //fix the bidirectional references c.DatabaseSchema = databaseSchema; c.Table = table; //foreign keys //if (string.IsNullOrEmpty(c.ForeignKeyTableName)) return; if (c.ForeignKeyTableNames.Count == 0) return; foreach (var fkTableName in c.ForeignKeyTableNames) { DatabaseTable fkTable = databaseSchema.FindTableByName(fkTableName); c.ForeignKeyTable = fkTable; if (fkTable != null && !fkTable.ForeignKeyChildren.Contains(table)) fkTable.ForeignKeyChildren.Add(table); } }); }); //update schema databaseSchema.Views.ForEach(delegate(DatabaseView view) { view.DatabaseSchema = databaseSchema; view.Columns.ForEach(delegate(DatabaseColumn c) { //fix the bidirectional references c.DatabaseSchema = databaseSchema; c.Table = view; }); }); databaseSchema.StoredProcedures.ForEach(delegate(DatabaseStoredProcedure sproc) { sproc.DatabaseSchema = databaseSchema; sproc.Arguments.ForEach(delegate(DatabaseArgument arg) { arg.DatabaseSchema = databaseSchema; }); }); if (databaseSchema.Packages.Count > 0) { UpdatePackages(databaseSchema); } UpdateDataTypes(databaseSchema); }
public void UniqueKeysTest() { var schema = new DatabaseSchema(null, null); schema .AddTable("Categories") .AddColumn("CategoryId", "INT").AddPrimaryKey().AddIdentity() .AddColumn("CategoryName", "VARCHAR", c => c.Length = 30).AddUniqueKey("UK_NAME"); //assert var cats = schema.FindTableByName("Categories"); Assert.AreEqual(1, cats.UniqueKeys.Count); var uk = cats.UniqueKeys[0]; Assert.AreEqual("UK_NAME", uk.Name); Assert.AreEqual("CategoryName", uk.Columns.Single()); var catName = cats.Columns.Find(c => c.Name == "CategoryName"); Assert.IsTrue(catName.IsUniqueKey); }
public void PropertiesTest() { var schema = new DatabaseSchema(null, null); schema .AddTable("Categories") .AddColumn("CategoryId", DbType.Int32).AddPrimaryKey() .AddColumn("CategoryName", DbType.String).AddLength(50).AddNullable() .AddColumn("Cost", DbType.Decimal).AddPrecisionScale(15,4).AddNullable(); //assert var cats = schema.FindTableByName("Categories"); var catName = cats.FindColumn("CategoryName"); Assert.AreEqual("NVARCHAR", catName.DbDataType); Assert.AreEqual(50, catName.Length); Assert.AreEqual(true, catName.Nullable); Assert.IsNotNull(catName.DataType); Assert.AreEqual(true, catName.DataType.IsString); var cost = cats.FindColumn("Cost"); Assert.AreEqual("DECIMAL", cost.DbDataType); Assert.AreEqual(15, cost.Precision); Assert.AreEqual(4, cost.Scale); Assert.AreEqual(true, cost.Nullable); Assert.IsNotNull(cost.DataType); Assert.AreEqual(true, cost.DataType.IsNumeric); }
public void ParsingDataTypeTest() { var schema = new DatabaseSchema(null, null); schema .AddTable("Categories") .AddColumn("CategoryId", "INT").AddPrimaryKey() .AddColumn("CategoryName", "VARCHAR(50)"); //assert var cats = schema.FindTableByName("Categories"); var catName = cats.FindColumn("CategoryName"); Assert.AreEqual("VARCHAR", catName.DbDataType); Assert.AreEqual(50, catName.Length); Assert.IsNotNull(catName.DataType); Assert.AreEqual(true, catName.DataType.IsString); }
private void AddForeignKeys(XElement storageSchema, DatabaseSchema databaseSchema) { foreach (var association in storageSchema.Elements(_schema + "Association")) { var name = (string)association.Attribute("Name"); var referentialConstraint = association.Element(_schema + "ReferentialConstraint"); //referentialConstraint is optional if (referentialConstraint == null) continue; var principal = referentialConstraint.Element(_schema + "Principal"); var fkRole = (string)principal.Attribute("Role"); var fkTable = AssociationTypeToTableName(storageSchema, association, fkRole); var dependent = referentialConstraint.Element(_schema + "Dependent"); var role = (string)dependent.Attribute("Role"); var tableName = AssociationTypeToTableName(storageSchema, association, role); //the EF DDL generator (GenerateTSQL.Utility.ttinclude WriteFKConstraintName) adds the prefix if (!name.StartsWith("FK_", StringComparison.OrdinalIgnoreCase)) name = "FK_" + name; var fk = new DatabaseConstraint { ConstraintType = ConstraintType.ForeignKey, Name = name, TableName = tableName, RefersToTable = fkTable }; fk.Columns.AddRange( dependent.Elements(_schema + "PropertyRef") .Select(e => (string)e.Attribute("Name"))); var deleteRule = association.Elements(_schema + "End") .First(e => (string)e.Attribute("Role") == fkRole) .Element(_schema + "OnDelete"); if (deleteRule != null && (string)deleteRule.Attribute("Action") == "Cascade") { fk.DeleteRule = "Cascade"; } var databaseTable = databaseSchema.FindTableByName(tableName); databaseTable.AddConstraint(fk); var cols = fk.Columns.Select(col => databaseTable.FindColumn(col)); //SSDLToSQL10.tt creates a nonclustered index (IX_name) for the foreign key databaseTable.AddIndex("IX_" + name, cols); } }
public void MappingSharedPrimaryKeyTest() { //arrange var schema = new DatabaseSchema(null, null); schema .AddTable("vehicle") .AddColumn<string>("regnum").AddPrimaryKey().AddLength(25) .AddColumn<string>("model").AddLength(32) .AddTable("car") .AddColumn<string>("regnum").AddLength(25).AddPrimaryKey().AddForeignKey("fk", "vehicle") .AddColumn<int>("doors"); //make sure it's all tied up DatabaseSchemaFixer.UpdateReferences(schema); //make sure .Net names are assigned PrepareSchemaNames.Prepare(schema, new Namer()); var table = schema.FindTableByName("car"); var mappingNamer = new MappingNamer(); var codeWriterSettings = new CodeWriterSettings{ CodeTarget = CodeTarget.PocoEntityCodeFirst }; var target = new CodeFirstMappingWriter(table, codeWriterSettings, mappingNamer); //act var txt = target.Write(); //assert var hasScalarKey = txt.Contains("HasKey(x => x.Regnum);"); var hasForeignKey = txt.Contains("HasRequired(x => x.Vehicle);"); Assert.IsTrue(hasScalarKey); Assert.IsTrue(hasForeignKey); }
public void WriteSharedPrimaryKeyTest() { //arrange var schema = new DatabaseSchema(null, null); schema .AddTable("vehicle") .AddColumn<string>("regnum").AddPrimaryKey().AddLength(25) .AddColumn<string>("model").AddLength(32) .AddTable("car") .AddColumn<string>("regnum").AddLength(25).AddPrimaryKey().AddForeignKey("fk", "vehicle") .AddColumn<int>("doors"); //make sure it's all tied up DatabaseSchemaFixer.UpdateReferences(schema); //make sure .Net names are assigned PrepareSchemaNames.Prepare(schema, new Namer()); var table = schema.FindTableByName("car"); var cw = new ClassWriter(table, new CodeWriterSettings { CodeTarget = CodeTarget.PocoEntityCodeFirst }); //act var txt = cw.Write(); //assert var hasScalarKey = txt.Contains("public string Regnum { get; set; }"); var hasForeignKey = txt.Contains("public virtual Vehicle Vehicle { get; set; }"); Assert.IsTrue(hasScalarKey); Assert.IsTrue(hasForeignKey); }
public void DbTypeTest() { var schema = new DatabaseSchema(null, null); schema .AddTable("Categories") .AddColumn("CategoryId", DbType.Int32).AddPrimaryKey() .AddColumn("CategoryName", DbType.String).AddLength(50).AddNullable(); //assert var cats = schema.FindTableByName("Categories"); var id = cats.PrimaryKeyColumn; Assert.AreEqual("INT", id.DbDataType); var catName = cats.FindColumn("CategoryName"); Assert.AreEqual("NVARCHAR", catName.DbDataType); }
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); }