private ColumnIdentifier GetOutboundForeignKey(ColumnIdentifier columnIdentifier) { var constraintReader = SelectSqlData.SelectOutboundForeignKey(Connection, columnIdentifier.Table, columnIdentifier.Column); if (constraintReader.Read()) { string foreignTable = constraintReader["foreign_table"] as string; string foreignColumn = constraintReader["foreign_column"] as string; return(new ColumnIdentifier(columnIdentifier.Database, columnIdentifier.Schema, foreignTable, foreignColumn)); } return(null); }
// Recursive function to get topmost column, i.e., column with no outbound foreign key reference in the given database private ColumnIdentifier GetRootColumn(ColumnIdentifier rootCol) { if (rootCol != null) { ColumnIdentifier root = GetRootColumn(GetOutboundForeignKey(rootCol)); if (root != null) { return(root); } } return(rootCol); }
private AlterColumnTypeDetails AlterColumnToDataType(ColumnIdentifier columnIdentifier, string incomingForeignKey, ISet <ColumnIdentifier> visitedColumnSet) { // Alter the requested column var affectedColumnDetails = new List <ColumnDetail> { GetColumnDetails(columnIdentifier.Schema, columnIdentifier.Table, columnIdentifier.Column) }; // Get all constraints referencing tableName.columnName var affectedConstraintDetails = GetConstraintDetails(columnIdentifier.Schema, columnIdentifier.Table, columnIdentifier.Column, incomingForeignKey).ToList(); // Get all foreign keys referencing tableName.columnName var foreignKeys = affectedConstraintDetails.OfType <ForeignKeyDetail>().ToList(); // Recursively get column and constraint details on each foreign key foreach (var foreignKey in foreignKeys) { var columnMap = foreignKey.Columns.First(column => column.TargetColumn == columnIdentifier.Column); var foreignKeySpec = new ColumnIdentifier(columnIdentifier.Database, columnIdentifier.Schema, foreignKey.Table, columnMap.SourceColumn); var alterColumnTypeDetails = AlterColumnToDataType(foreignKeySpec, foreignKey.Name, visitedColumnSet); // Mark each foreign key column as visited (post-order traversal) visitedColumnSet.Add(foreignKeySpec); affectedConstraintDetails.AddRange(alterColumnTypeDetails.AffectedConstraintDetails.Where(newCon => !affectedConstraintDetails.Any(oldCon => oldCon.Name == newCon.Name && oldCon.Table == newCon.Table))); affectedColumnDetails.AddRange(alterColumnTypeDetails.AffectedColumnDetails); } // Mark current column as visited (post-order traversal) visitedColumnSet.Add(columnIdentifier); // Sort by priority affectedConstraintDetails.Sort( (detail1, detail2) => Comparer <Int32> .Default.Compare(detail1.Priority, detail2.Priority)); return(new AlterColumnTypeDetails(affectedConstraintDetails, affectedColumnDetails)); }