/// <inheritdoc />
public ICreateColumnOptionOrForeignKeyCascadeSyntax ReferencedBy(
string foreignKeyName,
string foreignTableSchema,
string foreignTableName,
string foreignColumnName)
{
var fk = new CreateForeignKeyExpression
{
ForeignKey = new ForeignKeyDefinition
{
Name = foreignKeyName,
PrimaryTable = Expression.TableName,
PrimaryTableSchema = Expression.SchemaName,
ForeignTable = foreignTableName,
ForeignTableSchema = foreignTableSchema
}
};
fk.ForeignKey.PrimaryColumns.Add(Expression.Column.Name);
fk.ForeignKey.ForeignColumns.Add(foreignColumnName);
_context.Expressions.Add(fk);
CurrentForeignKey = fk.ForeignKey;
return(this);
}
public ICreateForeignKeyFromTableSyntax ForeignKey()
{
var expression = new CreateForeignKeyExpression();
_context.Expressions.Add(expression);
return(new CreateForeignKeyExpressionBuilder(expression));
}
public override string Generate(CreateForeignKeyExpression expression)
{
if (expression.ForeignKey.PrimaryColumns.Count != expression.ForeignKey.ForeignColumns.Count)
{
throw new ArgumentException("Number of primary columns and secondary columns must be equal");
}
List <string> primaryColumns = new List <string>();
List <string> foreignColumns = new List <string>();
foreach (var column in expression.ForeignKey.PrimaryColumns)
{
primaryColumns.Add(Quoter.QuoteColumnName(column));
}
foreach (var column in expression.ForeignKey.ForeignColumns)
{
foreignColumns.Add(Quoter.QuoteColumnName(column));
}
return(string.Format(
CreateForeignKeyConstraint,
Quoter.QuoteTableName(expression.ForeignKey.ForeignTable, expression.ForeignKey.ForeignTableSchema),
Quoter.QuoteColumnName(expression.ForeignKey.Name),
String.Join(", ", foreignColumns.ToArray()),
Quoter.QuoteTableName(expression.ForeignKey.PrimaryTable, expression.ForeignKey.PrimaryTableSchema),
String.Join(", ", primaryColumns.ToArray()),
Column.FormatCascade("DELETE", expression.ForeignKey.OnDelete),
Column.FormatCascade("UPDATE", expression.ForeignKey.OnUpdate)
));
}
internal static int[] GetErrorFilters(this CreateForeignKeyExpression expression)
{
var id = $"{expression.GetType().Name}_{expression.ForeignKey.PrimaryTable}";
var errors = Array.Empty <int>();
if (ErrorFilter.TryGetValue($"{id}", out var tableErrors) && tableErrors.Any())
{
errors = errors.Union(tableErrors).Distinct().OrderBy(i => i).ToArray();
}
foreach (var column in expression.ForeignKey.PrimaryColumns)
{
if (ErrorFilter.TryGetValue($"{id}_{column}", out var colErrors) && colErrors.Any())
{
errors = errors.Union(colErrors).Distinct().OrderBy(i => i).ToArray();
}
}
var id2 = $"{expression.GetType().Name}_{expression.ForeignKey.ForeignTable}";
if (ErrorFilter.TryGetValue($"{id2}", out var tableErrors2) && tableErrors2.Any())
{
errors = errors.Union(tableErrors2).Distinct().OrderBy(i => i).ToArray();
}
foreach (var column in expression.ForeignKey.ForeignColumns)
{
if (ErrorFilter.TryGetValue($"{id2}_{column}", out var colErrors) && colErrors.Any())
{
errors = errors.Union(colErrors).Distinct().OrderBy(i => i).ToArray();
}
}
return(errors);
}
public ICreateForeignKeyFromTableSyntax ForeignKey()
{
var expression = new CreateForeignKeyExpression(_context.CurrentDatabaseProvider, _databaseProviders, _sqlSyntax);
_context.Expressions.Add(expression);
return(new CreateForeignKeyBuilder(expression));
}
/// <inheritdoc />
public ICreateTableColumnOptionOrForeignKeyCascadeOrWithColumnSyntax ForeignKey(
string foreignKeyName,
string primaryTableSchema,
string primaryTableName,
string primaryColumnName)
{
CurrentColumn.IsForeignKey = true;
var fk = new CreateForeignKeyExpression
{
ForeignKey = new ForeignKeyDefinition
{
Name = foreignKeyName,
PrimaryTable = primaryTableName,
PrimaryTableSchema = primaryTableSchema,
ForeignTable = Expression.TableName,
ForeignTableSchema = Expression.SchemaName
}
};
fk.ForeignKey.PrimaryColumns.Add(primaryColumnName);
fk.ForeignKey.ForeignColumns.Add(CurrentColumn.Name);
_context.Expressions.Add(fk);
CurrentForeignKey = fk.ForeignKey;
CurrentColumn.ForeignKey = fk.ForeignKey;
return(this);
}
public override string Generate(CreateForeignKeyExpression expression)
{
return(string.Format(
CreateForeignKeyConstraint,
Quoter.QuoteTableName(expression.ForeignKey.ForeignTable, expression.ForeignKey.ForeignTableSchema),
Column.FormatForeignKey(expression.ForeignKey, GenerateForeignKeyName)));
}
public ICreateForeignKeyFromTableSyntax ForeignKey(string foreignKeyName)
{
var expression = new CreateForeignKeyExpression {
ForeignKey = { Name = foreignKeyName }
};
_context.Expressions.Add(expression);
return(new CreateForeignKeyExpressionBuilder(expression));
}
/// <inheritdoc />
public ICreateForeignKeyFromTableBuilder ForeignKey(string foreignKeyName)
{
var expression = new CreateForeignKeyExpression(_context)
{
ForeignKey = { Name = foreignKeyName }
};
return(new CreateForeignKeyBuilder(expression));
}
public void WhenDefaultSchemaConventionIsChangedAndSchemaIsNotSetThenSetSchema()
{
var expression = new CreateForeignKeyExpression();
var processed = expression.Apply(ConventionSets.WithSchemaName);
Assert.That(processed.ForeignKey.ForeignTableSchema, Is.EqualTo("testdefault"));
Assert.That(processed.ForeignKey.PrimaryTableSchema, Is.EqualTo("testdefault"));
}
public void WhenDefaultSchemaConventionIsAppliedAndSchemaIsNotSetThenSchemaShouldBeNull()
{
var expression = new CreateForeignKeyExpression();
var processed = expression.Apply(ConventionSets.NoSchemaName);
Assert.That(processed.ForeignKey.ForeignTableSchema, Is.Null);
Assert.That(processed.ForeignKey.PrimaryTableSchema, Is.Null);
}
public void CallingOnDeleteOrUpdateSetsBothOnDeleteAndOnUpdateToSpecifiedRule(Rule rule)
{
var expression = new CreateForeignKeyExpression();
var builder = new CreateForeignKeyExpressionBuilder(expression);
builder.OnDeleteOrUpdate(rule);
Assert.That(expression.ForeignKey.OnUpdate, Is.EqualTo(rule));
Assert.That(expression.ForeignKey.OnDelete, Is.EqualTo(rule));
}
public override string Generate(CreateForeignKeyExpression expression)
{
// If a FK name starts with $$IGNORE$$_ then it means it was handled by the CREATE TABLE
// routine and we know it's been handled so we should just not bother erroring.
if (expression.ForeignKey.Name.StartsWith("$$IGNORE$$_"))
{
return(string.Empty);
}
return(CompatibilityMode.HandleCompatibilty("Foreign keys are not supported in SQLite"));
}
public static CreateForeignKeyExpression GetCreateNamedMultiColumnForeignKeyExpression()
{
var expression = new CreateForeignKeyExpression();
expression.ForeignKey.Name = "FK_Test";
expression.ForeignKey.PrimaryTable = TestTableName2;
expression.ForeignKey.ForeignTable = TestTableName1;
expression.ForeignKey.PrimaryColumns = new[] { TestColumnName2, "TestColumn4" };
expression.ForeignKey.ForeignColumns = new[] { TestColumnName1, "TestColumn3" };
return(expression);
}
public static CreateForeignKeyExpression GetCreateMultiColumnForeignKeyExpression()
{
var expression = new CreateForeignKeyExpression();
expression.ForeignKey.PrimaryTable = TestTableName2;
expression.ForeignKey.ForeignTable = TestTableName1;
expression.ForeignKey.PrimaryColumns = new[] { TestColumnName2, "TestColumn4" };
expression.ForeignKey.ForeignColumns = new[] { TestColumnName1, "TestColumn3" };
expression.ApplyConventions(new MigrationConventions());
return(expression);
}
public void CanCreateForeignKey()
{
var expression = new CreateForeignKeyExpression();
expression.ForeignKey.Name = "FK_Test";
expression.ForeignKey.PrimaryTable = "TestPrimaryTable";
expression.ForeignKey.ForeignTable = "TestForeignTable";
expression.ForeignKey.PrimaryColumns = new[] { "Column1", "Column2" };
expression.ForeignKey.ForeignColumns = new[] { "Column3", "Column4" };
string sql = generator.Generate(expression);
sql.ShouldBe("ALTER TABLE \"public\".\"TestForeignTable\" ADD CONSTRAINT \"FK_Test\" FOREIGN KEY (\"Column3\",\"Column4\") REFERENCES \"public\".\"TestPrimaryTable\" (\"Column1\",\"Column2\")");
}
public void WhenDefaultSchemaConventionIsAppliedAndSchemaIsSetThenSchemaShouldNotBeChanged()
{
var expression = new CreateForeignKeyExpression()
{
ForeignKey =
{
ForeignTableSchema = "testschema",
PrimaryTableSchema = "testschema"
},
};
var processed = expression.Apply(ConventionSets.WithSchemaName);
Assert.That(processed.ForeignKey.ForeignTableSchema, Is.EqualTo("testschema"));
Assert.That(processed.ForeignKey.PrimaryTableSchema, Is.EqualTo("testschema"));
}
public override string Generate(CreateForeignKeyExpression expression)
{
var primaryColumns = GetColumnList(expression.ForeignKey.PrimaryColumns);
var foreignColumns = GetColumnList(expression.ForeignKey.ForeignColumns);
const string sql = "ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3} ({4}){5}{6};";
return(string.Format(sql,
Quoter.QuoteTableName(expression.ForeignKey.ForeignTable, expression.ForeignKey.ForeignTableSchema),
Quoter.Quote(expression.ForeignKey.Name),
foreignColumns,
Quoter.QuoteTableName(expression.ForeignKey.PrimaryTable, expression.ForeignKey.PrimaryTableSchema),
primaryColumns,
Column.FormatCascade("DELETE", expression.ForeignKey.OnDelete),
Column.FormatCascade("UPDATE", expression.ForeignKey.OnUpdate)
));
}
public void CanCreateForeignKeyWithUpdateAndDelete()
{
var expression = new CreateForeignKeyExpression();
expression.ForeignKey.Name = "FK_Test";
expression.ForeignKey.PrimaryTable = "TestPrimaryTable";
expression.ForeignKey.ForeignTable = "TestForeignTable";
expression.ForeignKey.PrimaryColumns = new[] { "Column1", "Column2" };
expression.ForeignKey.ForeignColumns = new[] { "Column3", "Column4" };
expression.ForeignKey.OnUpdate = Rule.SetNull;
expression.ForeignKey.OnDelete = Rule.SetDefault;
string sql = generator.Generate(expression);
sql.ShouldBe("ALTER TABLE \"public\".\"TestForeignTable\" ADD CONSTRAINT \"FK_Test\" FOREIGN KEY (\"Column3\",\"Column4\") REFERENCES \"public\".\"TestPrimaryTable\" (\"Column1\",\"Column2\") ON DELETE SET DEFAULT ON UPDATE SET NULL");
}
public static CreateForeignKeyExpression GetCreateMultiColumnForeignKeyExpression()
{
var expression = new CreateForeignKeyExpression
{
ForeignKey =
{
PrimaryTable = TestTableName2,
ForeignTable = TestTableName1,
PrimaryColumns = new[] { TestColumnName2,"TestColumn4" },
ForeignColumns = new[] { TestColumnName1,"TestColumn3" }
}
};
var processed = expression.Apply(ConventionSets.NoSchemaName);
return(processed);
}
public void GetForeignKeyNameReturnsValidForeignKeyNameForComplexForeignKey()
{
var expr = new CreateForeignKeyExpression()
{
ForeignKey =
{
ForeignTable = "Users",
ForeignColumns = new[] { "ColumnA","ColumnB" },
PrimaryTable = "Groups",
PrimaryColumns = new[] { "ColumnC","ColumnD" }
}
};
var processed = expr.Apply(ConventionSets.NoSchemaName);
processed.ForeignKey.Name.ShouldBe("FK_Users_ColumnA_ColumnB_Groups_ColumnC_ColumnD");
}
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);
}
public override string Generate(CreateForeignKeyExpression expression)
{
var primaryColumns = GetColumnList(expression.ForeignKey.PrimaryColumns);
var foreignColumns = GetColumnList(expression.ForeignKey.ForeignColumns);
const string sql = "ALTER TABLE {0}.{1} ADD CONSTRAINT {2} FOREIGN KEY ({3}) REFERENCES {4}.{5} ({6}){7}{8}";
return(string.Format(sql,
FormatSchema(expression.ForeignKey.ForeignTableSchema),
FormatIdentifier(expression.ForeignKey.ForeignTable),
FormatIdentifier(expression.ForeignKey.Name),
foreignColumns,
FormatSchema(expression.ForeignKey.PrimaryTableSchema),
FormatIdentifier(expression.ForeignKey.PrimaryTable),
primaryColumns,
FormatCascade("DELETE", expression.ForeignKey.OnDelete),
FormatCascade("UPDATE", expression.ForeignKey.OnUpdate)
));
}
public IAlterTableColumnOptionForeignKeyCascadeSyntax ReferencedBy(string foreignKeyName, string foreignTableSchema,
string foreignTableName, string foreignColumnName)
{
var fk = new CreateForeignKeyExpression(_context.CurrentDatabaseProvider, _databaseProviders, Expression.SqlSyntax, new ForeignKeyDefinition
{
Name = foreignKeyName,
PrimaryTable = Expression.TableName,
PrimaryTableSchema = Expression.SchemaName,
ForeignTable = foreignTableName,
ForeignTableSchema = foreignTableSchema
});
fk.ForeignKey.PrimaryColumns.Add(CurrentColumn.Name);
fk.ForeignKey.ForeignColumns.Add(foreignColumnName);
_context.Expressions.Add(fk);
CurrentForeignKey = fk.ForeignKey;
return(this);
}
public ICreateColumnOptionForeignKeyCascadeBuilder ForeignKey(string?foreignKeyName, string?primaryTableSchema,
string primaryTableName, string primaryColumnName)
{
Expression.Column.IsForeignKey = true;
var fk = new CreateForeignKeyExpression(_context, new ForeignKeyDefinition
{
Name = foreignKeyName,
PrimaryTable = primaryTableName,
PrimaryTableSchema = primaryTableSchema,
ForeignTable = Expression.TableName
});
fk.ForeignKey.PrimaryColumns.Add(primaryColumnName);
fk.ForeignKey.ForeignColumns.Add(Expression.Column.Name);
Expression.Expressions.Add(fk);
CurrentForeignKey = fk.ForeignKey;
return(this);
}
public void ToStringIsDescriptive()
{
var sql = new CreateForeignKeyExpression
{
ForeignKey = new ForeignKeyDefinition
{
ForeignColumns = new Collection <string> {
"User_id"
},
ForeignTable = "UserRoles",
PrimaryColumns = new Collection <string> {
"Id"
},
PrimaryTable = "User",
Name = "FK"
}
}.ToString();
sql.ShouldBe("CreateForeignKey FK UserRoles(User_id) User(Id)");
}
public ICreateColumnOptionForeignKeyCascadeSyntax ForeignKey(string foreignKeyName, string primaryTableSchema,
string primaryTableName, string primaryColumnName)
{
Expression.Column.IsForeignKey = true;
var fk = new CreateForeignKeyExpression(_context.CurrentDatabaseProvider, _databaseProviders, Expression.SqlSyntax, new ForeignKeyDefinition
{
Name = foreignKeyName,
PrimaryTable = primaryTableName,
PrimaryTableSchema = primaryTableSchema,
ForeignTable = Expression.TableName,
ForeignTableSchema = Expression.SchemaName
});
fk.ForeignKey.PrimaryColumns.Add(primaryColumnName);
fk.ForeignKey.ForeignColumns.Add(Expression.Column.Name);
_context.Expressions.Add(fk);
CurrentForeignKey = fk.ForeignKey;
return(this);
}
public IAlterColumnOptionSyntax ForeignKey(string foreignKeyName, string primaryTableSchema, string primaryTableName, string primaryColumnName)
{
Expression.Column.IsForeignKey = true;
var fk = new CreateForeignKeyExpression
{
ForeignKey = new ForeignKeyDefinition
{
Name = foreignKeyName,
PrimaryTable = primaryTableName,
PrimaryTableSchema = primaryTableSchema,
ForeignTable = Expression.TableName,
ForeignTableSchema = Expression.SchemaName
}
};
fk.ForeignKey.PrimaryColumns.Add(primaryColumnName);
fk.ForeignKey.ForeignColumns.Add(Expression.Column.Name);
_context.Expressions.Add(fk);
return(this);
}
public ICreateTableColumnOptionOrWithColumnSyntax References(string foreignKeyName, string foreignTableSchema, string foreignTableName, IEnumerable <string> foreignColumnNames)
{
var fk = new CreateForeignKeyExpression
{
ForeignKey = new ForeignKeyDefinition
{
Name = foreignKeyName,
PrimaryTable = Expression.TableName,
PrimaryTableSchema = Expression.SchemaName,
ForeignTable = foreignTableName,
ForeignTableSchema = foreignTableSchema
}
};
fk.ForeignKey.PrimaryColumns.Add(CurrentColumn.Name);
foreach (var foreignColumnName in foreignColumnNames)
{
fk.ForeignKey.ForeignColumns.Add(foreignColumnName);
}
_context.Expressions.Add(fk);
return(this);
}
public override string Generate(CreateForeignKeyExpression expression)
{
Truncator.Truncate(expression);
return(base.Generate(expression));
}
