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
});
}
/// <inheritdoc />
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 static TableDefinition GetTableDefinition(Type modelType, ISqlSyntaxProvider sqlSyntax)
{
// Looks for NPoco's TableNameAtribute for the name of the table
// If no attribute is set we use the name of the Type as the default convention
TableNameAttribute?tableNameAttribute = modelType.FirstAttribute <TableNameAttribute>();
var tableName = tableNameAttribute == null ? modelType.Name : tableNameAttribute.Value;
var tableDefinition = new TableDefinition {
Name = tableName
};
var objProperties = modelType.GetProperties().ToList();
foreach (PropertyInfo propertyInfo in objProperties)
{
// If current property has an IgnoreAttribute then skip it
IgnoreAttribute?ignoreAttribute = propertyInfo.FirstAttribute <IgnoreAttribute>();
if (ignoreAttribute != null)
{
continue;
}
// If current property has a ResultColumnAttribute then skip it
ResultColumnAttribute?resultColumnAttribute = propertyInfo.FirstAttribute <ResultColumnAttribute>();
if (resultColumnAttribute != null)
{
continue;
}
// Looks for ColumnAttribute with the name of the column, which would exist with ExplicitColumns
// Otherwise use the name of the property itself as the default convention
ColumnAttribute? columnAttribute = propertyInfo.FirstAttribute <ColumnAttribute>();
var columnName = columnAttribute != null ? columnAttribute.Name : propertyInfo.Name;
ColumnDefinition columnDefinition =
GetColumnDefinition(modelType, propertyInfo, columnName, tableName, sqlSyntax);
tableDefinition.Columns.Add(columnDefinition);
// Creates a foreignkey definition and adds it to the collection on the table definition
IEnumerable <ForeignKeyAttribute>?foreignKeyAttributes =
propertyInfo.MultipleAttribute <ForeignKeyAttribute>();
if (foreignKeyAttributes != null)
{
foreach (ForeignKeyAttribute foreignKeyAttribute in foreignKeyAttributes)
{
ForeignKeyDefinition foreignKeyDefinition = GetForeignKeyDefinition(modelType, propertyInfo, foreignKeyAttribute, columnName, tableName);
tableDefinition.ForeignKeys.Add(foreignKeyDefinition);
}
}
// Creates an index definition and adds it to the collection on the table definition
IndexAttribute?indexAttribute = propertyInfo.FirstAttribute <IndexAttribute>();
if (indexAttribute != null)
{
IndexDefinition indexDefinition =
GetIndexDefinition(modelType, propertyInfo, indexAttribute, columnName, tableName);
tableDefinition.Indexes.Add(indexDefinition);
}
}
return(tableDefinition);
}
public static ForeignKeyDefinition GetForeignKeyDefinition(Type modelType, PropertyInfo propertyInfo,
ForeignKeyAttribute attribute, string columnName, string tableName)
{
var referencedTable = attribute.Type.FirstAttribute <TableNameAttribute>();
var referencedPrimaryKey = attribute.Type.FirstAttribute <PrimaryKeyAttribute>();
string referencedColumn = string.IsNullOrEmpty(attribute.Column)
? referencedPrimaryKey.Value
: attribute.Column;
string foreignKeyName = string.IsNullOrEmpty(attribute.Name)
? string.Format("FK_{0}_{1}_{2}", tableName, referencedTable.Value, referencedColumn)
: attribute.Name;
var definition = new ForeignKeyDefinition
{
Name = foreignKeyName,
ForeignTable = tableName,
PrimaryTable = referencedTable.Value
};
definition.ForeignColumns.Add(columnName);
definition.PrimaryColumns.Add(referencedColumn);
return(definition);
}
private static CreateForeignKeyExpression GetCreateForeignKeyExpression(Table table, ForeignKey fk)
{
if (fk.ReferencedTable == null)
{
return(null);
}
var foreignKeyDefinition = new ForeignKeyDefinition()
{
Name = fk.Name,
PrimaryTableSchema = fk.ReferencedTable.Schema,
PrimaryTable = fk.ReferencedTable.Name,
ForeignTableSchema = table.Schema,
ForeignTable = table.Name,
ForeignColumns = fk.Columns.Select(c => c.Name).ToList(),
PrimaryColumns = fk.ReferencedColumns.Count > 0 ? fk.ReferencedColumns.Select(c => c.Name).ToList() : fk.ReferencedTable.PrimaryKey.ColumnIterator.Select(c => c.Name).ToList(),
OnDelete = fk.CascadeDeleteEnabled ? Rule.Cascade : Rule.None,
OnUpdate = Rule.None
};
return(new CreateForeignKeyExpression
{
ForeignKey = foreignKeyDefinition
});
}
/// <inheritdoc />
public virtual string FormatForeignKey(ForeignKeyDefinition foreignKey,
Func <ForeignKeyDefinition, string> fkNameGeneration)
{
if (foreignKey.PrimaryColumns.Count != foreignKey.ForeignColumns.Count)
{
throw new ArgumentException("Number of primary columns and secondary columns must be equal");
}
var constraintName = string.IsNullOrEmpty(foreignKey.Name)
? fkNameGeneration(foreignKey)
: foreignKey.Name;
var primaryColumns = new List <string>();
var foreignColumns = new List <string>();
foreach (var column in foreignKey.PrimaryColumns)
{
primaryColumns.Add(Quoter.QuoteColumnName(column));
}
foreach (var column in foreignKey.ForeignColumns)
{
foreignColumns.Add(Quoter.QuoteColumnName(column));
}
var constraintClause = string.IsNullOrEmpty(constraintName)
? string.Empty
: $"CONSTRAINT {Quoter.QuoteConstraintName(constraintName)} ";
return(string.Format(
ForeignKeyConstraint,
constraintClause,
string.Join(", ", foreignColumns.ToArray()),
Quoter.QuoteTableName(foreignKey.PrimaryTable, foreignKey.PrimaryTableSchema),
string.Join(", ", primaryColumns.ToArray()),
FormatCascade("DELETE", foreignKey.OnDelete),
FormatCascade("UPDATE", foreignKey.OnUpdate)
));
}
public static void ToLower(this ForeignKeyDefinition definition)
{
definition.Name = definition.Name.ToLower();
definition.ForeignTable = definition.ForeignTable.ToLower();
definition.PrimaryTable = definition.PrimaryTable.ToLower();
definition.ForeignColumns = definition.ForeignColumns.ToLower();
definition.PrimaryColumns = definition.PrimaryColumns.ToLower();
}
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 ErrorIsNotReturnedWhenPrimaryTableNameIsDifferentThanForeignTableName()
{
var column = new ForeignKeyDefinition {
PrimaryTable = "Bacon", ForeignTable = "NotBacon"
};
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldNotContain(ErrorMessages.ForeignKeyCannotBeSelfReferential);
}
/// <summary>
/// Gets the default name of a foreign key
/// </summary>
/// <param name="foreignKey">The foreign key definition</param>
/// <returns>Name of a foreign key</returns>
private string GetForeignKeyName(ForeignKeyDefinition foreignKey)
{
var foreignColumns = string.Join('_', foreignKey.ForeignColumns);
var primaryColumns = string.Join('_', foreignKey.PrimaryColumns);
var keyName = _dataProvider.CreateForeignKeyName(foreignKey.ForeignTable, foreignColumns, foreignKey.PrimaryTable, primaryColumns);
return(keyName);
}
public void ErrorIsReturnedWhenPrimaryTableNameIsNull()
{
var column = new ForeignKeyDefinition {
PrimaryTable = null
};
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldContain(ErrorMessages.PrimaryTableNameCannotBeNullOrEmpty);
}
public void ErrorIsReturnedWhenForeignTableNameIsEmptyString()
{
var column = new ForeignKeyDefinition {
ForeignTable = string.Empty
};
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldContain(ErrorMessages.ForeignTableNameCannotBeNullOrEmpty);
}
public void ErrorIsNotReturnedWhenNameIsNotNullOrEmptyString()
{
var column = new ForeignKeyDefinition {
Name = "Bacon"
};
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldNotContain(ErrorMessages.ForeignKeyNameCannotBeNullOrEmpty);
}
public void ErrorIsReturnedWhenPrimaryColumnsIsEmpty()
{
var column = new ForeignKeyDefinition {
Name = "FkName", ForeignTable = "FkTable", PrimaryTable = "PkTable", PrimaryColumns = new string[0]
};
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldContain(ErrorMessages.ForeignKeyMustHaveOneOrMorePrimaryColumns);
}
public void ErrorIsReturnedWhenForeignColumnsIsEmpty()
{
var column = new ForeignKeyDefinition {
ForeignColumns = new string[0]
};
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldContain(ErrorMessages.ForeignKeyMustHaveOneOrMoreForeignColumns);
}
public void ErrorIsNotReturnedWhenPrimaryColumnsIsNotEmpty()
{
var column = new ForeignKeyDefinition {
PrimaryColumns = new[] { "Bacon" }
};
var errors = ValidationHelper.CollectErrors(column);
errors.ShouldNotContain(ErrorMessages.ForeignKeyMustHaveOneOrMorePrimaryColumns);
}
public override string Format(ForeignKeyDefinition foreignKey)
{
return(string.Format(CreateForeignKeyConstraint,
GetQuotedTableName(foreignKey.ForeignTable),
GetQuotedColumnName(foreignKey.ForeignColumns.First()),
GetQuotedTableName(foreignKey.PrimaryTable),
GetQuotedColumnName(foreignKey.PrimaryColumns.First()),
FormatCascade("DELETE", foreignKey.OnDelete),
FormatCascade("UPDATE", foreignKey.OnUpdate)));
}
/// <inheritdoc />
public override string FormatForeignKey(ForeignKeyDefinition foreignKey, Func <ForeignKeyDefinition, string> fkNameGeneration)
{
var fk2 = (ForeignKeyDefinition)foreignKey.Clone();
// SQLite FK's must be within the same schema as the FK itself
// so we'll remove the schema from the FK definition
fk2.PrimaryTableSchema = string.Empty;
return(base.FormatForeignKey(fk2, fkNameGeneration));
}
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 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 WhenDefaultSchemaConventionIsAppliedAndSchemaIsSetThenSchemaShouldNotBeChanged()
{
var foreignKeyDefinition = new ForeignKeyDefinition {
Name = "Test", ForeignTableSchema = "testschema", PrimaryTableSchema = "testschema"
};
foreignKeyDefinition.ApplyConventions(new MigrationConventions());
Assert.That(foreignKeyDefinition.ForeignTableSchema, Is.EqualTo("testschema"));
Assert.That(foreignKeyDefinition.PrimaryTableSchema, Is.EqualTo("testschema"));
}
public void WhenDefaultSchemaConventionIsAppliedAndSchemaIsNotSetThenSchemaShouldBeNull()
{
var foreignKeyDefinition = new ForeignKeyDefinition {
Name = "Test"
};
foreignKeyDefinition.ApplyConventions(new MigrationConventions());
Assert.That(foreignKeyDefinition.ForeignTableSchema, Is.Null);
Assert.That(foreignKeyDefinition.PrimaryTableSchema, Is.Null);
}
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 bool AreSameFk(ForeignKeyDefinition fromFk, ForeignKeyDefinition toFk)
{
return(fromFk.Name == toFk.Name &&
fromFk.ForeignTableSchema == toFk.ForeignTableSchema &&
fromFk.ForeignTable == toFk.ForeignTable &&
fromFk.PrimaryTable == toFk.PrimaryTable &&
fromFk.PrimaryTableSchema == toFk.PrimaryTableSchema &&
fromFk.OnDelete == toFk.OnDelete &&
fromFk.OnUpdate == toFk.OnUpdate &&
MatchStrings(fromFk.ForeignColumns, toFk.ForeignColumns) &&
MatchStrings(fromFk.PrimaryColumns, toFk.PrimaryColumns));
}
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);
Assert.Throws <ArgumentNullException>(() => mockGenerator.Generate(deleteFKExpression));
}
public void WhenDefaultSchemaConventionIsChangedAndSchemaIsNotSetThenSetSchema()
{
var foreignKeyDefinition = new ForeignKeyDefinition {
Name = "Test"
};
var migrationConventions = new MigrationConventions {
GetDefaultSchema = () => "testdefault"
};
foreignKeyDefinition.ApplyConventions(migrationConventions);
Assert.That(foreignKeyDefinition.ForeignTableSchema, Is.EqualTo("testdefault"));
Assert.That(foreignKeyDefinition.PrimaryTableSchema, Is.EqualTo("testdefault"));
}
public virtual string Format(ForeignKeyDefinition foreignKey)
{
var constraintName = string.IsNullOrEmpty(foreignKey.Name)
? $"FK_{foreignKey.ForeignTable}_{foreignKey.PrimaryTable}_{foreignKey.PrimaryColumns.First()}"
: foreignKey.Name;
return(string.Format(CreateForeignKeyConstraint,
GetQuotedTableName(foreignKey.ForeignTable),
GetQuotedName(constraintName),
GetQuotedColumnName(foreignKey.ForeignColumns.First()),
GetQuotedTableName(foreignKey.PrimaryTable),
GetQuotedColumnName(foreignKey.PrimaryColumns.First()),
FormatCascade("DELETE", foreignKey.OnDelete),
FormatCascade("UPDATE", foreignKey.OnUpdate)));
}
public virtual string Format(ForeignKeyDefinition foreignKey)
{
var constraintName = string.IsNullOrEmpty(foreignKey.Name)
? $"FK_{foreignKey.ForeignTable}_{foreignKey.PrimaryTable}_{foreignKey.PrimaryColumns.First()}"
: foreignKey.Name;
var localColumn = GetQuotedColumnName(foreignKey.ForeignColumns.First());
var remoteColumn = GetQuotedColumnName(foreignKey.PrimaryColumns.First());
var remoteTable = GetQuotedTableName(foreignKey.PrimaryTable);
var onDelete = FormatCascade("DELETE", foreignKey.OnDelete);
var onUpdate = FormatCascade("UPDATE", foreignKey.OnUpdate);
return
$"CONSTRAINT {constraintName} FOREIGN KEY ({localColumn}) REFERENCES {remoteTable} ({remoteColumn}) {onDelete} {onUpdate}";
}
public IAlterTableColumnOptionForeignKeyCascadeBuilder ReferencedBy(string?foreignKeyName, string?foreignTableSchema,
string foreignTableName, string foreignColumnName)
{
var fk = new CreateForeignKeyExpression(_context, new ForeignKeyDefinition
{
Name = foreignKeyName,
PrimaryTable = Expression.TableName,
ForeignTable = foreignTableName,
ForeignTableSchema = foreignTableSchema
});
fk.ForeignKey.PrimaryColumns.Add(CurrentColumn.Name);
fk.ForeignKey.ForeignColumns.Add(foreignColumnName);
Expression.Expressions.Add(fk);
CurrentForeignKey = fk.ForeignKey;
return(this);
}
