/// <summary>
/// Adds the constraint of any type (primary key, foreign key, unique key, check)
/// </summary>
/// <param name="con">The constraint.</param>
public void AddConstraint(DatabaseConstraint con)
{
if (con == null)
{
throw new ArgumentNullException("con");
}
switch (con.ConstraintType)
{
case ConstraintType.PrimaryKey:
PrimaryKey = con;
break;
case ConstraintType.ForeignKey:
_foreignKeys.Add(con);
break;
case ConstraintType.UniqueKey:
_uniqueKeys.Add(con);
break;
case ConstraintType.Check:
_checkConstraints.Add(con);
break;
case ConstraintType.Default:
_defaultConstraints.Add(con);
break;
}
AddConstraintColumns(con);
}
/// <summary>
/// Adds the constraint of any type (primary key, foreign key, unique key, check)
/// </summary>
/// <param name="con">The constraint.</param>
public void AddConstraint(DatabaseConstraint con)
{
switch (con.ConstraintType)
{
case ConstraintType.PrimaryKey:
PrimaryKey = con;
break;
case ConstraintType.ForeignKey:
_foreignKeys.Add(con);
break;
case ConstraintType.UniqueKey:
_uniqueKeys.Add(con);
break;
case ConstraintType.Check:
_checkConstraints.Add(con);
break;
case ConstraintType.Default:
_defaultConstraints.Add(con);
break;
}
AddConstraintColumns(con);
}
/// <summary>
/// Removes the foreign key and cleans the column markers.
/// </summary>
/// <param name="foreignKey">The foreign key.</param>
/// <exception cref="System.ArgumentNullException">foreignKey;foreignkey cannot be null</exception>
/// <exception cref="System.InvalidOperationException">Must be a foreign key</exception>
internal void RemoveForeignKey(DatabaseConstraint foreignKey)
{
if (foreignKey == null)
{
throw new ArgumentNullException("foreignKey", "foreignkey cannot be null");
}
if (foreignKey.ConstraintType != ConstraintType.ForeignKey)
{
throw new InvalidOperationException("Must be a foreign key");
}
if (!_foreignKeys.Contains(foreignKey))
{
throw new InvalidOperationException("Foreign key not for this table or already removed");
}
_foreignKeys.Remove(foreignKey);
foreach (var columnName in foreignKey.Columns)
{
var column = FindColumn(columnName);
if (column != null)
{
column.IsForeignKey = false;
column.ForeignKeyTableName = null;
column.ForeignKeyTable = null;
}
}
}
private void AddConstraintFindColumn(DatabaseConstraint con, string name)
{
foreach (DatabaseColumn col in Columns)
{
if (col.Name.Equals(name, StringComparison.OrdinalIgnoreCase))
{
switch (con.ConstraintType)
{
case ConstraintType.PrimaryKey:
col.IsPrimaryKey = true;
break;
case ConstraintType.ForeignKey:
if (!string.IsNullOrEmpty(con.RefersToTable))
{
//ignore fk constraint to a pk without a table.
col.IsForeignKey = true;
col.ForeignKeyTableName = con.RefersToTable;
}
break;
case ConstraintType.UniqueKey:
col.IsUniqueKey = true;
break;
}
break;
}
}
}
/// <summary>
/// Adds the column.
/// </summary>
/// <param name="databaseConstraint">The database constraint.</param>
/// <param name="databaseColumn">The database column.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">databaseConstraint</exception>
public static DatabaseConstraint AddColumn(this DatabaseConstraint databaseConstraint, DatabaseColumn databaseColumn)
{
if (databaseConstraint == null)
{
throw new ArgumentNullException("databaseConstraint");
}
if (databaseColumn == null)
{
throw new ArgumentNullException("databaseColumn");
}
if (databaseColumn.TableName != databaseConstraint.TableName)
{
throw new InvalidOperationException("Constraint and column must belong to same table");
}
databaseConstraint.Columns.Add(databaseColumn.Name);
switch (databaseConstraint.ConstraintType)
{
case ConstraintType.ForeignKey:
databaseColumn.IsForeignKey = true;
databaseColumn.ForeignKeyTableName = databaseConstraint.RefersToTable;
break;
case ConstraintType.PrimaryKey:
databaseColumn.IsPrimaryKey = true;
break;
case ConstraintType.UniqueKey:
databaseColumn.IsUniqueKey = true;
break;
}
return(databaseConstraint);
}
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>
/// Adds a foreign key with a single column
/// </summary>
/// <param name="databaseColumn">The database column.</param>
/// <param name="foreignKeyName">Name of the foreign key.</param>
/// <param name="foreignTableName">Name of the foreign table.</param>
/// <returns></returns>
public static DatabaseColumn AddForeignKey(this DatabaseColumn databaseColumn, string foreignKeyName, string foreignTableName)
{
if (databaseColumn == null)
{
throw new ArgumentNullException("databaseColumn", "databaseColumn must not be null");
}
if (string.IsNullOrEmpty(foreignTableName))
{
throw new ArgumentNullException("foreignTableName", "foreignTableName must not be null");
}
var table = databaseColumn.Table;
var foreignKey = new DatabaseConstraint
{
ConstraintType = ConstraintType.ForeignKey,
Name = foreignKeyName,
TableName = table.Name,
RefersToTable = foreignTableName
};
foreignKey.Columns.Add(databaseColumn.Name);
table.AddConstraint(foreignKey);
databaseColumn.IsForeignKey = true;
//add the inverse relationship
var fkTable = table.DatabaseSchema.FindTableByName(foreignTableName);
if (fkTable != null)
{
fkTable.ForeignKeyChildren.Add(table);
}
return(databaseColumn);
}
public override string WritePrimaryKey(DatabaseConstraint constraint)
{
if (constraint == null) return null;
var columnList = GetColumnList(constraint.Columns);
var pkName = ConstraintName(constraint);
var nonClustered = "";
if (constraint.Columns.Count == 1)
{
//UNIQUEIDENTIFIERs should have NON CLUSTERED indexes
var colName = constraint.Columns[0];
var col = Table.FindColumn(colName);
if (col != null)
{
colName = col.NetName;
if (string.Equals(col.DbDataType, "UNIQUEIDENTIFIER", StringComparison.OrdinalIgnoreCase))
{
nonClustered = "NONCLUSTERED ";
}
}
if ("guid".Equals(colName, StringComparison.OrdinalIgnoreCase))
{
nonClustered = "NONCLUSTERED ";
}
}
return string.Format(CultureInfo.InvariantCulture,
@"ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY {2}({3})",
TableName(Table),
EscapeName(pkName),
nonClustered,
columnList) + SqlFormatProvider().LineEnding();
}
public void TestSqlServerWithChangingDefault()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = table.FindColumn("NAME");
column.Length = 40;
column.DefaultValue = "'?'";
//create an "original" version of the column (by cloning)
var originalColumn = column.Clone();
originalColumn.DefaultValue = "'UNKNOWN'";
//add a default constraint
var df = new DatabaseConstraint { ConstraintType = ConstraintType.Default, Name = "DF_Orders_Name" };
df.Columns.Add("NAME");
table.AddConstraint(df);
//act
var sql = migration.AlterColumn(table, column, originalColumn);
//assert
//ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [DF_Orders_Name];
//-- Orders from [NAME] VARCHAR (40) NOT NULL to [NAME] VARCHAR (40) NOT NULL
//ALTER TABLE [dbo].[Orders] ALTER COLUMN [NAME] VARCHAR (40) NOT NULL;
//ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_Name] DEFAULT '?' FOR [NAME];
Assert.IsTrue(sql.Contains("DROP CONSTRAINT [DF_Orders_Name]"), "drop constraint");
Assert.IsTrue(sql.Contains("ADD CONSTRAINT [DF_Orders_Name] DEFAULT '?' FOR [NAME]"), "add default constraint");
}
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
}
private static bool ExcludeCheckConstraint(DatabaseConstraint check)
{
//Oracle doesn't allow SYSDATE in check constraints
if (check.Expression.IndexOf("getDate()", StringComparison.OrdinalIgnoreCase) != -1)
return true;
if (check.Expression.IndexOf("current_timestamp", StringComparison.OrdinalIgnoreCase) != -1)
return true;
return false;
}
/// <summary>
/// Returns the name of an inverse foreign key property. Uses <see cref="NameCollection"/>
/// For single fks, it's a collection using the name of the fk table.
/// For multiple fks, it's a collection using the name of the fk columns
/// </summary>
/// <param name="targetTable">The target table.</param>
/// <param name="table">The table.</param>
/// <param name="foreignKey">The foreign key.</param>
/// <returns>
/// Eg OrderLine has fk to Order. Order will have an ICollection<OrderLine> called "OrderLineCollection".
/// Multiple fk eg Order has Delivery Address and Billing Address.
/// Address will have an ICollection<Order> called "DeliveryAddressCollection",
/// and another ICollection<Order> called "BillingAddressCollection"
/// </returns>
public virtual string ForeignKeyCollectionName(string targetTable, DatabaseTable table, DatabaseConstraint foreignKey)
{
var fksToTarget = table.ForeignKeys.Where(x => x.RefersToTable == targetTable).ToList();
string name = table.NetName;
if (fksToTarget.Count > 1)
name = string.Join("", foreignKey.Columns.Select(x => table.FindColumn(x).NetName).ToArray());
return NameCollection(name);
}
protected override string WriteDefaultConstraint(DatabaseConstraint constraint)
{
var column = EscapeName(constraint.Columns.FirstOrDefault());
return string.Format(CultureInfo.InvariantCulture,
@"ALTER TABLE {0} ADD CONSTRAINT {1} DEFAULT {2} FOR {3}",
TableName(Table),
EscapeName(constraint.Name),
constraint.Expression,
column) + SqlFormatProvider().LineEnding();
}
private void AddConstraintColumns(DatabaseConstraint con)
{
if (con == null)
{
return;
}
foreach (string name in con.Columns)
{
AddConstraintFindColumn(con, name);
}
}
private string WriteUniqueKey(DatabaseConstraint uniqueKey)
{
var columnList = GetColumnList(uniqueKey.Columns);
var name = ConstraintName(uniqueKey);
return string.Format(CultureInfo.InvariantCulture,
AddUniqueConstraintFormat,
TableName(Table),
EscapeName(name),
columnList) + SqlFormatProvider().LineEnding();
}
public virtual string WritePrimaryKey(DatabaseConstraint constraint)
{
if (constraint == null) return null;
var columnList = GetColumnList(constraint.Columns);
var pkName = ConstraintName(constraint);
return string.Format(CultureInfo.InvariantCulture,
@"ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY ({2})",
TableName(Table),
EscapeName(pkName),
columnList) + SqlFormatProvider().LineEnding();
}
/// <summary>
/// Adds a foreign key with a single column
/// </summary>
/// <param name="databaseColumn">The database column.</param>
/// <param name="foreignKeyName">Name of the foreign key.</param>
/// <param name="foreignTableName">Name of the foreign table.</param>
/// <param name="foreignTableSchemaOwner">The foreign table schema owner.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">
/// databaseColumn;databaseColumn must not be null
/// or
/// foreignTableName;foreignTableName must not be null
/// </exception>
public static DatabaseColumn AddForeignKey(this DatabaseColumn databaseColumn,
string foreignKeyName,
string foreignTableName,
string foreignTableSchemaOwner)
{
if (databaseColumn == null)
{
throw new ArgumentNullException("databaseColumn", "databaseColumn must not be null");
}
if (string.IsNullOrEmpty(foreignTableName))
{
throw new ArgumentNullException("foreignTableName", "foreignTableName must not be null");
}
var table = databaseColumn.Table;
if (string.IsNullOrEmpty(foreignKeyName)) //no fk name, so we'll invent one (it may be too long e.g. for Oracle)
{
foreignKeyName = "FK_" + table.Name + "_" + foreignTableName;
}
var foreignKey = new DatabaseConstraint
{
ConstraintType = ConstraintType.ForeignKey,
Name = foreignKeyName,
TableName = table.Name,
RefersToTable = foreignTableName,
RefersToSchema = foreignTableSchemaOwner,
};
foreignKey.Columns.Add(databaseColumn.Name);
table.AddConstraint(foreignKey);
databaseColumn.IsForeignKey = true;
//add the inverse relationship
DatabaseTable fkTable;
if (string.IsNullOrEmpty(foreignTableSchemaOwner))
{
fkTable = table.DatabaseSchema.FindTableByName(foreignTableName);
}
else
{
fkTable = table.DatabaseSchema.FindTableByName(foreignTableName, foreignTableSchemaOwner);
}
if (fkTable != null && !fkTable.ForeignKeyChildren.Contains(table))
{
fkTable.ForeignKeyChildren.Add(table);
databaseColumn.ForeignKeyTable = fkTable;
}
return(databaseColumn);
}
private void WriteForeignKey(DatabaseTable table, DatabaseConstraint foreignKey, StringBuilder sb)
{
sb.AppendLine("ALTER TABLE [" + table.Name + "]");
var cols = string.Join(", ", foreignKey.Columns.ToArray());
var referencedTableName = foreignKey.RefersToTable;
var referencedSchemaName = foreignKey.RefersToSchema;
//find the referenced table's primary key
var refTable = _schema.Tables.Find(t => t.Name == referencedTableName && t.SchemaOwner == referencedSchemaName);
var refPrimaryKey = refTable.PrimaryKey;
var refcols = string.Join(", ", refPrimaryKey.Columns.ToArray());
sb.AppendLine(" ADD CONSTRAINT [" + foreignKey.Name +
"] FOREIGN KEY (" + cols + ") REFERENCES " + referencedTableName + "(" + refcols + ")");
}
private void AddConstraint(DatabaseConstraint foreignKey)
{
var command = Connection.CreateCommand();
var cols = string.Join(", ", foreignKey.Columns.ToArray());
var refPrimaryKey = _table.PrimaryKey;
var refcols = string.Join(", ", refPrimaryKey.Columns.ToArray());
command.CommandText = string.Format(CultureInfo.InvariantCulture,
"ALTER TABLE [{0}] ADD CONSTRAINT [{1}] FOREIGN KEY ({2}) REFERENCES [{0}]({3})",
_table.Name,
foreignKey.Name,
cols,
refcols);
command.ExecuteNonQuery();
}
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 static DatabaseTable CreateTestTable(string tableName)
{
//we only need a schema because MySQL foreign key references do not allow just the foreign key table name- they need the columns too
var schema = new DatabaseSchema(null, null);
var testTable = new DatabaseTable { Name = tableName, Description = "This is a test table" };
schema.Tables.Add(testTable);
testTable.DatabaseSchema = schema; //the migration will discover this and know how to link the self referencing table
var intDataType = new DataType("INT", "System.Int32");
var idColumn = new DatabaseColumn
{
Name = "Id",
DbDataType = "int",
DataType = intDataType,
Nullable = false,
Description = "Primary key",
};
testTable.Columns.Add(idColumn);
var parentColumn = new DatabaseColumn
{
Name = "Parent", //for a self-referencing foreign key
DbDataType = "int",
DataType = intDataType,
Nullable = true,
Description = "Self referencing foreign key",
};
testTable.Columns.Add(parentColumn);
var nameColumn = new DatabaseColumn
{
Name = "NAME",
DbDataType = "VARCHAR",
Length = 10,
DataType = new DataType("VARCHAR", "string"),
Description = "Simple varchar column",
};
testTable.Columns.Add(nameColumn);
var primaryKey = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey, Name = "PK_" + tableName };
primaryKey.Columns.Add("Id");
testTable.PrimaryKey = primaryKey;
return testTable;
}
/// <summary>
/// Adds a unique key.
/// </summary>
/// <param name="databaseColumn">The database column.</param>
/// <param name="uniqueKeyName">Name of the unique key.</param>
/// <returns></returns>
public static DatabaseColumn AddUniqueKey(this DatabaseColumn databaseColumn, string uniqueKeyName)
{
if (databaseColumn == null)
{
throw new ArgumentNullException("databaseColumn", "databaseColumn must not be null");
}
var table = databaseColumn.Table;
var uk = new DatabaseConstraint
{
ConstraintType = ConstraintType.UniqueKey,
TableName = table.Name,
Name = uniqueKeyName
};
uk.Columns.Add(databaseColumn.Name);
table.AddConstraint(uk);
databaseColumn.IsUniqueKey = true;
return(databaseColumn);
}
/// <summary>
/// Returns the name of a foreign key property for a given foreign key.
/// </summary>
/// <param name="table">The table.</param>
/// <param name="foreignKey">The foreign key.</param>
/// <returns></returns>
/// <remarks>
/// If it is a simple foreign key, it is the NetName of the column
/// if it is a composite foreign key, it is the NetName of the foreign table
/// if there is a collision with the class name, append "Key"
/// If there are multiple foreign keys to one table, ensure they are unique.
/// </remarks>
public virtual string ForeignKeyName(DatabaseTable table, DatabaseConstraint foreignKey)
{
var refTable = foreignKey.ReferencedTable(table.DatabaseSchema);
if (refTable == null)
{
//we can't find the foreign key table, so just write the columns
return null;
}
//This is a name for the foreign key. Only used for composite keys.
var propertyName = refTable.NetName;
//if there is only one column (not composite) use the netName of that column
if (foreignKey.Columns.Count == 1)
{
var columnName = foreignKey.Columns.Single();
var column = table.FindColumn(columnName);
//if it is a primary key, we've used the original name for a scalar property
if (!column.IsPrimaryKey)
propertyName = column.NetName;
}
else //composite keys
{
// Check whether the referenced table is used in any other key. This ensures that the property names
// are unique.
if (table.ForeignKeys.Count(x => x.RefersToTable == foreignKey.RefersToTable) > 1)
{
// Append the key name to the property name. In the event of multiple foreign keys to the same table
// This will give the consumer context.
propertyName += foreignKey.Name;
}
}
// Ensures that property name cannot be the same as class name
if (propertyName == table.NetName)
{
propertyName += "Key";
}
return propertyName;
}
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 IList<DatabaseConstraint> Execute(DbConnection connection)
{
var tables = new Tables(_tableName).Execute(connection);
foreach (var table in tables)
{
var tableName = table.Name;
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = string.Format(PragmaSql, tableName);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var refersToTable = dr.GetString("table");
var con =
Result.FirstOrDefault(x => x.TableName == tableName && x.RefersToTable == refersToTable);
if (con == null)
{
con = new DatabaseConstraint
{
TableName = tableName,
ConstraintType = ConstraintType.ForeignKey,
RefersToTable = refersToTable,
UpdateRule = dr.GetString("on_update"),
DeleteRule = dr.GetString("on_delete"),
};
Result.Add(con);
}
con.Columns.Add(dr.GetString("from"));
}
}
}
}
return Result;
}
public void TestAddPrimaryKeyWithGuid()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
var schema = new DatabaseSchema(null, SqlType.SqlServer);
var table = schema.AddTable("Test")
.AddColumn<Guid>("Id")
.AddColumn<string>("Name")
.Table;
var pk = new DatabaseConstraint
{
Name = "Test_PK",
ConstraintType = ConstraintType.PrimaryKey
};
pk.Columns.Add("Id");
//act
var sql = migration.AddConstraint(table, pk);
//assert
Assert.IsTrue(sql.IndexOf("ADD CONSTRAINT [Test_PK] PRIMARY KEY NONCLUSTERED ([Id])", StringComparison.OrdinalIgnoreCase) != -1, "adding a primary key");
}
public void TestSqlServerAddDefaultConstraint()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = table.FindColumn("NAME");
column.Length = 40;
column.DefaultValue = "'?'";
//add a default constraint
var df = new DatabaseConstraint { ConstraintType = ConstraintType.Default, Name = "DF_Orders_Name", Expression = "'?'" };
df.Columns.Add("NAME");
table.AddConstraint(df);
//act
var sql = migration.AddConstraint(table, df);
//assert
//ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_Name] DEFAULT '?' FOR [NAME];
Assert.IsTrue(sql.Contains("ADD CONSTRAINT [DF_Orders_Name] DEFAULT '?' FOR [NAME]"), "add constraint");
}
protected override string ConstraintName(DatabaseConstraint constraint)
{
var name = constraint.Name;
if (string.IsNullOrEmpty(name))
{
var columnNames = string.Join("_", constraint.Columns.ToArray());
//suffixes - http://stackoverflow.com/questions/4107915/postgresql-default-constraint-names/4108266#4108266
//pkey for a Primary Key constraint
//key for a Unique constraint
//excl for an Exclusion constraint
//idx for any other kind of index
//fkey for a Foreign key
//check for a Check constraint
string suffix;
switch (constraint.ConstraintType)
{
case ConstraintType.PrimaryKey:
suffix = "pkey";
break;
case ConstraintType.ForeignKey:
suffix = "fkey";
break;
case ConstraintType.UniqueKey:
suffix = "key";
break;
case ConstraintType.Check:
suffix = "check";
break;
default:
suffix = "idx";
break;
}
return Table.Name + "_" + columnNames + "_" + suffix;
}
return name;
}
public void TestSqlServerDropColumnWithDefault()
{
//arrange
var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
var table = MigrationCommon.CreateTestTable("Orders");
table.SchemaOwner = "dbo";
var column = table.FindColumn("NAME");
column.Length = 40;
column.DefaultValue = "'?'";
//add a default constraint
var df = new DatabaseConstraint { ConstraintType = ConstraintType.Default, Name = "DF_Orders_Name" };
df.Columns.Add("NAME");
table.AddConstraint(df);
//act
var sql = migration.DropColumn(table, column);
//assert
//ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [DF_Orders_Name];
//ALTER TABLE [dbo].[Orders] DROP COLUMN [NAME];
Assert.IsTrue(sql.Contains("DROP CONSTRAINT [DF_Orders_Name]"), "drop constraint");
}
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);
}
private static DatabaseTable CreateProductsTable()
{
var productsTable = new DatabaseTable { Name = "Products" };
var idColumn = new DatabaseColumn { Name = "Id", DbDataType = "int" };
productsTable.Columns.Add(idColumn);
var pk = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey, Name = "PK_Orders" };
pk.Columns.Add("Id");
productsTable.PrimaryKey = pk;
return productsTable;
}
private static DatabaseSchema CreateSchema()
{
var schema = new DatabaseSchema(null, "System.Data.SqlClient");
var orderTable = new DatabaseTable { Name = "Orders" };
schema.Tables.Add(orderTable);
var idColumn = new DatabaseColumn { Name = "Id", DbDataType = "int" };
orderTable.Columns.Add(idColumn);
var nameColumn = new DatabaseColumn { Name = "Name", DbDataType = "VARCHAR" };
orderTable.Columns.Add(nameColumn);
var descColumn = new DatabaseColumn { Name = "Desc", DbDataType = "VARCHAR" };
orderTable.Columns.Add(descColumn);
var pk = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey, Name = "PK_Orders" };
pk.Columns.Add("Id");
orderTable.PrimaryKey = pk;
return schema;
}
private static DatabaseConstraint GetUniqueConstraint()
{
var constraint = new DatabaseConstraint
{
Name = "UK_NAME",
ConstraintType = ConstraintType.UniqueKey,
};
constraint.Columns.Add("Name");
return constraint;
}
private void AddPrimaryKey(DatabaseTable table, XElement entityType)
{
var key = entityType.Element(_schema + "Key");
if (key == null) return;
var pkColumns =
key.Elements(_schema + "PropertyRef").Select(propertyRef => (string)propertyRef.Attribute("Name"));
var primaryKey = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey };
primaryKey.Columns.AddRange(pkColumns);
table.PrimaryKey = primaryKey;
}
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);
}
}
private void AddConstraintFindColumn(DatabaseConstraint con, string name)
{
foreach (DatabaseColumn col in Columns)
{
if (col.Name.Equals(name, StringComparison.OrdinalIgnoreCase))
{
switch (con.ConstraintType)
{
case ConstraintType.PrimaryKey:
col.IsPrimaryKey = true;
break;
case ConstraintType.ForeignKey:
if (!string.IsNullOrEmpty(con.RefersToTable))
{
//ignore fk constraint to a pk without a table.
col.IsForeignKey = true;
col.ForeignKeyTableName = con.RefersToTable;
}
break;
case ConstraintType.UniqueKey:
col.IsUniqueKey = true;
break;
}
break;
}
}
}
private void AddConstraintColumns(DatabaseConstraint con)
{
if (con == null) return;
foreach (string name in con.Columns)
{
AddConstraintFindColumn(con, name);
}
}
/// <summary>
/// Removes the foreign key and cleans the column markers.
/// </summary>
/// <param name="foreignKey">The foreign key.</param>
/// <exception cref="System.ArgumentNullException">foreignKey;foreignkey cannot be null</exception>
/// <exception cref="System.InvalidOperationException">Must be a foreign key</exception>
internal void RemoveForeignKey(DatabaseConstraint foreignKey)
{
if (foreignKey == null) throw new ArgumentNullException("foreignKey", "foreignkey cannot be null");
if (foreignKey.ConstraintType != ConstraintType.ForeignKey) throw new InvalidOperationException("Must be a foreign key");
if (!_foreignKeys.Contains(foreignKey)) throw new InvalidOperationException("Foreign key not for this table or already removed");
_foreignKeys.Remove(foreignKey);
foreach (var columnName in foreignKey.Columns)
{
var column = FindColumn(columnName);
if (column != null)
{
column.IsForeignKey = false;
column.ForeignKeyTableName = null;
column.ForeignKeyTable = null;
}
}
}
