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")); }