public void GetForeignKeyNameReturnsValidForeignKeyNameForSimpleForeignKey()
{
var foreignKey = new ForeignKeyDefinition
{
ForeignTable = "Users", ForeignColumns = new[] { "GroupId" },
PrimaryTable = "Groups", PrimaryColumns = new[] { "Id" }
};
DefaultMigrationConventions.GetForeignKeyName(foreignKey).ShouldBe("FK_Users_GroupId_Groups_Id");
}
public void GetForeignKeyNameReturnsValidForeignKeyNameForComplexForeignKey()
{
var foreignKey = new ForeignKeyDefinition
{
ForeignTable = "Users", ForeignColumns = new[] { "ColumnA", "ColumnB" },
PrimaryTable = "Groups", PrimaryColumns = new[] { "ColumnC", "ColumnD" }
};
DefaultMigrationConventions.GetForeignKeyName(foreignKey).ShouldBe("FK_Users_ColumnA_ColumnB_Groups_ColumnC_ColumnD");
}
public void Delete_Foreign_Key_Should_Throw_Exception_If_Table_Name_Is_Null()
{
// Setup empty FK
var deleteFKExpression = new DeleteForeignKeyExpression();
var fkDef = new ForeignKeyDefinition();
deleteFKExpression.ForeignKey = fkDef;
// Setup empty mock object
var mockGenerator = new MockGenerator(null, null, null);
Assert.Throws<ArgumentNullException>(() => mockGenerator.Generate(deleteFKExpression));
}
public static string GetForeignKeyName(ForeignKeyDefinition foreignKey)
{
var sb = new StringBuilder();
sb.Append("FK_");
sb.Append(foreignKey.ForeignTable);
foreach (string foreignColumn in foreignKey.ForeignColumns)
{
sb.Append("_");
sb.Append(foreignColumn);
}
sb.Append("_");
sb.Append(foreignKey.PrimaryTable);
foreach (string primaryColumn in foreignKey.PrimaryColumns)
{
sb.Append("_");
sb.Append(primaryColumn);
}
return sb.ToString();
}
public DeleteForeignKeyExpression()
{
ForeignKey = new ForeignKeyDefinition();
}
private void LoadConstraints()
{
Constraints = ConstraintInfo.Read(Processor, TableMeta);
foreach (ConstraintInfo constraint in Constraints)
{
List<string> columns = new List<string>();
if (Indexes.Any(x => x.Name == constraint.IndexName))
columns = Indexes.First(x => x.Name == constraint.IndexName).Columns;
foreach (ColumnDefinition column in Definition.Columns)
{
if (columns.Contains(column.Name))
{
if (constraint.IsPrimaryKey)
{
column.IsPrimaryKey = true;
column.PrimaryKeyName = constraint.Name;
RemoveIndex(constraint.Name);
}
if (constraint.IsNotNull)
column.IsNullable = false;
if (constraint.IsUnique)
column.IsUnique = true;
}
}
if (constraint.IsForeignKey)
{
ForeignKeyDefinition fkDef = new ForeignKeyDefinition()
{
Name = constraint.Name,
ForeignTable = TableMeta.Name,
ForeignColumns = columns,
PrimaryTable = constraint.ForeignIndex.TableName,
PrimaryColumns = constraint.ForeignIndex.Columns,
OnUpdate = constraint.UpdateRule,
OnDelete = constraint.DeleteRule
};
RemoveIndex(constraint.Name);
Definition.ForeignKeys.Add(fkDef);
}
}
}
private static ICollection<ForeignKeyDefinition> ParseForeignKeyDefinition(string table, string sql)
{
var definitions = new List<ForeignKeyDefinition>();
using (var reader = new StringReader(sql))
{
while ( reader.Peek() > 0)
{
var line = reader.ReadLine();
if (string.IsNullOrEmpty(line) || !line.Contains(" FOREIGN KEY ")) continue;
var references = reader.ReadLine();
var definition = new ForeignKeyDefinition
{
Name = GetForeignKeyName(line)
,ForeignTable = table
,PrimaryTable = GetPrimaryTable(references)
,PrimaryColumns = GetColumnNames(references)
,ForeignColumns = GetColumnNames(line)
};
definitions.Add(definition);
}
}
return definitions;
}
public void Truncate(ForeignKeyDefinition foreignKey)
{
foreignKey.Name = packKeyNames ? Pack(foreignKey.Name) : Truncate(foreignKey.Name);
foreignKey.PrimaryTable = Truncate(foreignKey.PrimaryTable);
foreignKey.PrimaryColumns = TruncateNames(foreignKey.PrimaryColumns);
foreignKey.ForeignTable = Truncate(foreignKey.ForeignTable);
foreignKey.ForeignColumns = TruncateNames(foreignKey.ForeignColumns);
}
public void ErrorIsNotReturnedWhenPrimaryColumnsIsNotEmpty()
{
var column = new ForeignKeyDefinition { PrimaryColumns = new[] { "Bacon" } };
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldNotContain(ErrorMessages.ForeignKeyMustHaveOneOrMorePrimaryColumns);
}
public void ErrorIsReturnedWhenNameIsNull()
{
var column = new ForeignKeyDefinition { Name = null };
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldContain(ErrorMessages.ForeignKeyNameCannotBeNullOrEmpty);
}
protected virtual IList<ForeignKeyDefinition> ReadForeignKeys(string schemaName, string tableName)
{
// Source http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/
string query =
@"SELECT
C.CONSTRAINT_NAME As Constraint_Name,
FK.CONSTRAINT_SCHEMA AS ForeignTableSchema,
FK.TABLE_NAME As FK_Table,
CU.COLUMN_NAME As FK_Column ,
PK.CONSTRAINT_SCHEMA as PrimaryTableSchema,
PK.TABLE_NAME As PK_Table,
PT.COLUMN_NAME As PK_Column
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS As C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS As FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS As PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE As CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) As PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME = '{1}'
AND FK.CONSTRAINT_SCHEMA = '{0}'
ORDER BY 1,2,3,4";
DataSet ds = Read(query, schemaName, tableName);
DataTable dt = ds.Tables[0];
IList<ForeignKeyDefinition> keys = new List<ForeignKeyDefinition>();
foreach (DataRow dr in dt.Rows) {
List<ForeignKeyDefinition> matches = (from i in keys
where i.Name == dr["Constraint_Name"].ToString()
select i).ToList();
ForeignKeyDefinition d = null;
if (matches.Count > 0) d = matches[0];
// create the table if not found
if (d == null) {
d = new ForeignKeyDefinition()
{
Name = dr["Constraint_Name"].ToString(),
ForeignTableSchema = dr["ForeignTableSchema"].ToString(),
ForeignTable = dr["FK_Table"].ToString(),
PrimaryTable = dr["PK_Table"].ToString(),
PrimaryTableSchema = dr["PrimaryTableSchema"].ToString()
};
keys.Add(d);
}
ICollection<string> ms;
// Foreign Columns
ms = (from m in d.ForeignColumns
where m == dr["FK_Table"].ToString()
select m).ToList();
if (ms.Count == 0) d.ForeignColumns.Add(dr["FK_Column"].ToString());
// Primary Columns
ms = (from m in d.PrimaryColumns
where m == dr["PK_Table"].ToString()
select m).ToList();
if (ms.Count == 0) d.PrimaryColumns.Add(dr["PK_Column"].ToString());
}
return keys;
}
public override IMigrationExpression Reverse()
{
// there are 2 types of delete FK statements
// 1) Delete.ForeignKey("FK_Name").OnTable("Table")
// 2) Delete.ForeignKey()
// .FromTable("Table1").ForeignColumn("Id")
// .ToTable("Table2").PrimaryColumn("Id");
// there isn't a way to autoreverse the type 1
// but we can turn the type 2 into Create.ForeignKey().FromTable() ...
// only type 2 has foreign column(s) and primary column(s)
if (!ForeignKey.HasForeignAndPrimaryColumnsDefined())
{
return base.Reverse();
}
var reverseForeignKey = new ForeignKeyDefinition
{
Name = ForeignKey.Name,
ForeignTableSchema = ForeignKey.PrimaryTableSchema,
ForeignTable = ForeignKey.PrimaryTable,
PrimaryTableSchema = ForeignKey.ForeignTableSchema,
PrimaryTable = ForeignKey.ForeignTable,
ForeignColumns = new List<string>(ForeignKey.PrimaryColumns),
PrimaryColumns = new List<string>(ForeignKey.ForeignColumns),
OnDelete = ForeignKey.OnDelete,
OnUpdate = ForeignKey.OnUpdate
};
return new CreateForeignKeyExpression { ForeignKey = reverseForeignKey };
}
public void WriteKeys(ForeignKeyDefinition key, StreamWriter output)
{
output.WriteLine("\t\t\tCreate.ForeignKey(\"" + key.Name + "\").FromTable(\"" + key.ForeignTable + "\").ForeignColumn(\"" + key.ForeignColumns + "\")");
output.WriteLine("\t\t\t\t.ToTable(\"" + key.PrimaryTable + "\").PrimaryColumn(\"" + key.PrimaryColumns.First() + "\");");
}
public void CallingForeignKeySetForeignKey()
{
var expressionMock = new Mock<CreateTableExpression>();
var contextMock = new Mock<IMigrationContext>();
contextMock.SetupGet(x => x.Expressions).Returns(new List<IMigrationExpression>());
var builder = new CreateTableExpressionBuilder(expressionMock.Object, contextMock.Object);
builder.CurrentColumn = new ColumnDefinition();
var fk = new ForeignKeyDefinition
{
Name = "foreignKeyName",
PrimaryTable = "primaryTableName",
PrimaryTableSchema = "primaryTableSchema",
ForeignTable = builder.Expression.TableName,
ForeignTableSchema = builder.Expression.SchemaName
};
builder.ForeignKey(fk.Name, fk.PrimaryTableSchema, fk.PrimaryTable, "primaryColumnName");
Assert.IsTrue(builder.CurrentColumn.IsForeignKey);
Assert.AreEqual(builder.CurrentColumn.ForeignKey.Name, fk.Name);
Assert.AreEqual(builder.CurrentColumn.ForeignKey.PrimaryTable, fk.PrimaryTable);
Assert.AreEqual(builder.CurrentColumn.ForeignKey.PrimaryTableSchema, fk.PrimaryTableSchema);
Assert.AreEqual(builder.CurrentColumn.ForeignKey.ForeignTable, fk.ForeignTable);
Assert.AreEqual(builder.CurrentColumn.ForeignKey.ForeignTableSchema, fk.ForeignTableSchema);
}
public override IMigrationExpression Reverse()
{
// there are 2 types of delete FK statements
// 1) Delete.ForeignKey("FK_Name").OnTable("Table")
// 2) Delete.ForeignKey()
// .FromTable("Table1").ForeignColumn("Id")
// .ToTable("Table2").PrimaryColumn("Id");
// there isn't a way to autoreverse the type 1
// but we can turn the type 2 into Create.ForeignKey().FromTable() ...
// only type 1 has the specific FK Name so if it's there then we can't auto-reverse
if (!String.IsNullOrEmpty(ForeignKey.Name))
{
return base.Reverse();
}
var reverseForeignKey = new ForeignKeyDefinition
{
Name = ForeignKey.Name,
ForeignTableSchema = ForeignKey.PrimaryTableSchema,
ForeignTable = ForeignKey.PrimaryTable,
PrimaryTableSchema = ForeignKey.ForeignTableSchema,
PrimaryTable = ForeignKey.ForeignTable,
ForeignColumns = new List<string>(ForeignKey.PrimaryColumns),
PrimaryColumns = new List<string>(ForeignKey.ForeignColumns),
OnDelete = ForeignKey.OnDelete,
OnUpdate = ForeignKey.OnUpdate
};
return new CreateForeignKeyExpression { ForeignKey = reverseForeignKey };
}
protected virtual IList<ForeignKeyDefinition> ReadForeignKeys(string schemaName, string tableName)
{
const string query = @"SELECT C.CONSTRAINT_SCHEMA AS Contraint_Schema,
C.CONSTRAINT_NAME AS Constraint_Name,
FK.CONSTRAINT_SCHEMA AS ForeignTableSchema,
FK.TABLE_NAME AS FK_Table,
CU.COLUMN_NAME AS FK_Column,
PK.CONSTRAINT_SCHEMA as PrimaryTableSchema,
PK.TABLE_NAME AS PK_Table,
PT.COLUMN_NAME AS PK_Column
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME = '{1}'
AND PK.CONSTRAINT_SCHEMA = '{0}'
ORDER BY Constraint_Name";
DataSet ds = Read(query, schemaName, tableName);
DataTable dt = ds.Tables[0];
IList<ForeignKeyDefinition> keys = new List<ForeignKeyDefinition>();
foreach (DataRow dr in dt.Rows)
{
List<ForeignKeyDefinition> matches = (from i in keys
where i.Name == dr["Constraint_Name"].ToString()
select i).ToList();
ForeignKeyDefinition d = null;
if (matches.Count > 0) d = matches[0];
// create the table if not found
if (d == null)
{
d = new ForeignKeyDefinition
{
Name = dr["Constraint_Name"].ToString(),
ForeignTableSchema = dr["ForeignTableSchema"].ToString(),
ForeignTable = dr["FK_Table"].ToString(),
PrimaryTable = dr["PK_Table"].ToString(),
PrimaryTableSchema = dr["PrimaryTableSchema"].ToString()
};
keys.Add(d);
}
// Foreign Columns
ICollection<string> ms = (from m in d.ForeignColumns
where m == dr["FK_Table"].ToString()
select m).ToList();
if (ms.Count == 0) d.ForeignColumns.Add(dr["FK_Table"].ToString());
// Primary Columns
ms = (from m in d.PrimaryColumns
where m == dr["PK_Table"].ToString()
select m).ToList();
if (ms.Count == 0) d.PrimaryColumns.Add(dr["PK_Table"].ToString());
}
return keys;
}
public void ErrorIsNotReturnedWhenPrimaryTableNameIsDifferentThanForeignTableName()
{
var column = new ForeignKeyDefinition { PrimaryTable = "Bacon", ForeignTable = "NotBacon" };
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldNotContain(ErrorMessages.ForeignKeyCannotBeSelfReferential);
}
public CreateForeignKeyExpression()
{
ForeignKey = new ForeignKeyDefinition();
}
public void ErrorIsNotReturnedWhenPrimaryTableNameIsNotNullOrEmptyString()
{
var column = new ForeignKeyDefinition { PrimaryTable = "Bacon" };
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldNotContain(ErrorMessages.PrimaryTableNameCannotBeNullOrEmpty);
}
public void ErrorIsReturnedWhenForeignColumnsIsEmpty()
{
var column = new ForeignKeyDefinition { ForeignColumns = new string[0] };
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldContain(ErrorMessages.ForeignKeyMustHaveOneOrMoreForeignColumns);
}
public void ErrorIsReturnedWhenForeignTableNameIsEmptyString()
{
var column = new ForeignKeyDefinition { ForeignTable = String.Empty };
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldContain(ErrorMessages.ForeignTableNameCannotBeNullOrEmpty);
}
public override string GenerateForeignKeyName(ForeignKeyDefinition foreignKey)
{
truncator.Truncate(foreignKey);
return truncator.Truncate(base.GenerateForeignKeyName(foreignKey));
}
private string GetTestMigrationWithForeignKey(SchemaMigrationContext context, ForeignKeyDefinition foreignKey, params ColumnDefinition[] columns)
{
context.Type = context.Type | MigrationType.ForeignKeys;
var tableDefinitions = new List<TableDefinition>
{
new TableDefinition
{
Name = "Foo",
Columns = columns
}
};
if (foreignKey != null)
tableDefinitions[0].ForeignKeys = new[] { foreignKey };
GenerateTableMigrations(context, tableDefinitions);
return File.ReadAllText(Path.Combine(_tempDirectory, @"Migrations\Test.cs"));
}
