public static CreateForeignKeyExpression GetCreateForeignKeyExpression()
{
var expression = new CreateForeignKeyExpression();
expression.ForeignKey.Name = "FK_Test";
expression.ForeignKey.PrimaryTable = TestTableName2;
expression.ForeignKey.ForeignTable = TestTableName1;
expression.ForeignKey.PrimaryColumns = new[] { TestColumnName2 };
expression.ForeignKey.ForeignColumns = new[] { TestColumnName1 };
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 TestForeignTable ADD CONSTRAINT FK_Test FOREIGN KEY (Column3,Column4) REFERENCES TestPrimaryTable (Column1,Column2)");
}
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" };
var sql = generator.Generate(expression);
sql.ShouldBe(
"ALTER TABLE [TestForeignTable] ADD CONSTRAINT FK_Test FOREIGN KEY ([Column3],[Column4]) REFERENCES [TestPrimaryTable] ([Column1],[Column2])");
}
public override string Generate(CreateForeignKeyExpression expression)
{
string primaryColumns = GetColumnList(expression.ForeignKey.PrimaryColumns);
string foreignColumns = GetColumnList(expression.ForeignKey.ForeignColumns);
string sql = "ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3} ({4})";
return String.Format(sql,
expression.ForeignKey.ForeignTable,
expression.ForeignKey.Name,
foreignColumns,
expression.ForeignKey.PrimaryTable,
primaryColumns
);
}
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 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})";
return string.Format(sql,
FormatSchema(expression.ForeignKey.ForeignTableSchema),
expression.ForeignKey.ForeignTable,
expression.ForeignKey.Name,
foreignColumns,
FormatSchema(expression.ForeignKey.PrimaryTableSchema),
expression.ForeignKey.PrimaryTable,
primaryColumns
);
}
public override string Generate(CreateForeignKeyExpression expression)
{
string primaryColumns = GetColumnList(expression.ForeignKey.PrimaryColumns);
string foreignColumns = GetColumnList(expression.ForeignKey.ForeignColumns);
string sql = "ALTER TABLE `{0}` ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3} ({4}){5}{6}";
return String.Format(sql,
expression.ForeignKey.ForeignTable,
expression.ForeignKey.Name,
foreignColumns,
expression.ForeignKey.PrimaryTable,
primaryColumns,
FormatCascade("DELETE", expression.ForeignKey.OnDelete),
FormatCascade("UPDATE", expression.ForeignKey.OnUpdate)
);
}
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,
Quoter.QuoteSchemaName(expression.ForeignKey.ForeignTableSchema),
Quoter.QuoteTableName(expression.ForeignKey.ForeignTable),
Quoter.Quote(expression.ForeignKey.Name),
foreignColumns,
Quoter.QuoteSchemaName(expression.ForeignKey.PrimaryTableSchema),
Quoter.QuoteTableName(expression.ForeignKey.PrimaryTable),
primaryColumns,
FormatCascade("DELETE", expression.ForeignKey.OnDelete),
FormatCascade("UPDATE", expression.ForeignKey.OnUpdate)
);
}
public void Truncate(CreateForeignKeyExpression expression)
{
Truncate(expression.ForeignKey);
}
public override string Generate(CreateForeignKeyExpression expression)
{
return string.Format("{0};", base.Generate(expression));
}
public virtual string GenerateForeignKeyName(CreateForeignKeyExpression expression)
{
return string.Format("FK_{0}_{1}", expression.ForeignKey.PrimaryTable.Substring(0, 5), expression.ForeignKey.ForeignTable.Substring(0, 5));
}
public ICreateForeignKeyFromTableSyntax ForeignKey()
{
var expression = new CreateForeignKeyExpression();
_context.Expressions.Add(expression);
return new CreateForeignKeyExpressionBuilder(expression);
}
public override string Generate(CreateForeignKeyExpression expression)
{
return compatabilityMode.HandleCompatabilty("Forign keys are not supported in Sqlite");
}
public void CanForeignKeyToUniqueColumn()
{
// Arrange
var create = new CreateTableExpression
{
TableName = "Foo",
Columns = new[]{
new ColumnDefinition {Name = "Id", Type = DbType.Int32, IsPrimaryKey = true}
, new ColumnDefinition {Name = "Type", Type = DbType.Int32}
}
};
var index = new CreateIndexExpression
{
Index =
new IndexDefinition()
{
Name = "IDX_FooType",
TableName = "Foo",
IsUnique = true,
Columns = new List<IndexColumnDefinition> { new IndexColumnDefinition { Name = "Type" } }
}
};
var secondTable = new CreateTableExpression
{
TableName = "Bar",
Columns = new[]{
new ColumnDefinition {Name = "Id", Type = DbType.Int32, IsPrimaryKey = true}
, new ColumnDefinition {Name = "FooType", Type = DbType.Int32}
}
};
var foreignKey = new CreateForeignKeyExpression
{
ForeignKey =
new ForeignKeyDefinition()
{
Name = "FK_FooType",
ForeignTable = "Bar",
ForeignColumns = new [] { "FooType" },
PrimaryTable= "Foo",
PrimaryColumns = new[] { "Type" }
}
};
// Act
MigrateToOracleWithData(new List<IMigrationExpression> { create, index, secondTable, foreignKey }, 2);
// Assert
}
public void CallingOnUpdateSetsOnUpdateToSpecifiedRule(Rule rule)
{
var expression = new CreateForeignKeyExpression();
var builder = new CreateForeignKeyExpressionBuilder(expression);
builder.OnUpdate(rule);
Assert.That(expression.ForeignKey.OnUpdate, Is.EqualTo(rule));
Assert.That(expression.ForeignKey.OnDelete, Is.EqualTo(Rule.None));
}
public virtual void Process(CreateForeignKeyExpression expression)
{
Process(Generator.Generate(expression));
}
public static CreateForeignKeyExpression GetCreateForeignKeyExpression()
{
var expression = new CreateForeignKeyExpression();
expression.ForeignKey.PrimaryTable = TestTableName2;
expression.ForeignKey.ForeignTable = TestTableName1;
expression.ForeignKey.PrimaryColumns = new[] { TestColumnName2 };
expression.ForeignKey.ForeignColumns = new[] { TestColumnName1 };
expression.ApplyConventions(new MigrationConventions());
return expression;
}
public abstract string Generate(CreateForeignKeyExpression expression);
public override string Generate(CreateForeignKeyExpression expression)
{
// Ignore foreign keys for SQLite
return "";
}
public void CanCreateForeignKeyToDifferentSchema()
{
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.PrimaryTableSchema = "wibble";
string sql = generator.Generate(expression);
sql.ShouldBe("ALTER TABLE \"public\".\"TestForeignTable\" ADD CONSTRAINT \"FK_Test\" FOREIGN KEY (\"Column3\",\"Column4\") REFERENCES \"wibble\".\"TestPrimaryTable\" (\"Column1\",\"Column2\")");
}
public ICreateForeignKeyFromTableSyntax ForeignKey(string foreignKeyName)
{
var expression = new CreateForeignKeyExpression { ForeignKey = { Name = foreignKeyName } };
_context.Expressions.Add(expression);
return new CreateForeignKeyExpressionBuilder(expression);
}
public void CanCreateForeignKeyWithDeleteCascade()
{
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.OnDelete = Rule.Cascade;
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 CASCADE");
}
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");
}
string keyName = string.IsNullOrEmpty(expression.ForeignKey.Name)
? GenerateForeignKeyName(expression)
: expression.ForeignKey.Name;
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),
Quoter.QuoteColumnName(keyName),
String.Join(", ", foreignColumns.ToArray()),
Quoter.QuoteTableName(expression.ForeignKey.PrimaryTable),
String.Join(", ", primaryColumns.ToArray()),
FormatCascade("DELETE", expression.ForeignKey.OnDelete),
FormatCascade("UPDATE", expression.ForeignKey.OnUpdate)
);
}
public void CanCreateForeignKeyWithUpdateSetNull()
{
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;
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 UPDATE SET NULL");
}
public override string Generate(CreateForeignKeyExpression expression)
{
truncator.Truncate(expression);
return base.Generate(expression);
}
public override string Generate(CreateForeignKeyExpression expression)
{
throw new NotImplementedException();
}
