public void WithBidirectionalDepndencyTopologicalSort()
{
//arrange
var schema = new DatabaseSchema(null, null);
var orders = new DatabaseTable();
orders.Name = "countries";
var productsFk = new DatabaseConstraint();
productsFk.ConstraintType = ConstraintType.ForeignKey;
productsFk.RefersToTable = "capitalcities";
orders.AddConstraint(productsFk);
schema.Tables.Add(orders);
var products = new DatabaseTable();
products.Name = "capitalcities";
var categoriesFk = new DatabaseConstraint();
categoriesFk.ConstraintType = ConstraintType.ForeignKey;
categoriesFk.RefersToTable = "countries";
products.AddConstraint(categoriesFk);
schema.Tables.Add(products);
//a country has one capital city
//a capital city is in one country
//But bidirectional foreign keys is terrible database design - you really only need one direction.
//(you have to save the country with a null capital, then the capital, then update the country again).
//Topological sorts don't support cycles, so we should just get back the original list
//act
var sortedTables = SchemaTablesSorter.TopologicalSort(schema);
//assert
Assert.AreEqual(2, sortedTables.Count());
//non-deterministic order
}
public void TestSqlServerCheckConstraint()
{
//arrange
var table = new DatabaseTable {Name = "Orders"};
var check = new DatabaseConstraint
{
ConstraintType = ConstraintType.Check,
Expression = "[Quantity] > 0",
Name = "ValidationRule"
};
table.AddConstraint(check);
var writer = new ConstraintWriter(table);
//act
var txt = writer.WriteCheckConstraints();
//assert
Assert.IsTrue(txt.Contains("ALTER TABLE [Orders] ADD CONSTRAINT [ValidationRule] CHECK ([Quantity] > 0);"));
}
public void TestAccessDateCheckConstraint()
{
//arrange
var table = new DatabaseTable {Name = "Orders"};
var check = new DatabaseConstraint
{
ConstraintType = ConstraintType.Check,
Expression = ">Date()",
Name = "[Orders].[OrderDate].ValidationRule"
};
table.AddConstraint(check);
var writer = new TableGenerator(table);
//act
var txt = writer.Write();
//assert
Assert.IsTrue(txt.Contains("ALTER TABLE [Orders] ADD CONSTRAINT [Orders_OrderDate_ValidationRule] CHECK ([Orders].[OrderDate] >getdate());"));
}
public void TestTopologicalSort()
{
//arrange
var schema = new DatabaseSchema(null, null);
var orders = new DatabaseTable();
orders.Name = "orders";
var productsFk = new DatabaseConstraint
{
ConstraintType = ConstraintType.ForeignKey,
RefersToTable = "products"
};
orders.AddConstraint(productsFk);
schema.Tables.Add(orders);
var categories = new DatabaseTable();
categories.Name = "categories";
schema.Tables.Add(categories);
var products = new DatabaseTable();
products.Name = "products";
var categoriesFk = new DatabaseConstraint();
categoriesFk.ConstraintType = ConstraintType.ForeignKey;
categoriesFk.RefersToTable = "categories";
products.AddConstraint(categoriesFk);
schema.Tables.Add(products);
//act
var sortedTables = SchemaTablesSorter.TopologicalSort(schema);
//assert
var first = sortedTables.First();
var last = sortedTables.Last();
Assert.AreEqual(categories, first);
Assert.AreEqual(orders, last);
}