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