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