public override IEnumerable <string> SqlCommands(IDbConnection connection)
{
foreach (var cmd in base.SqlCommands(connection))
{
yield return(cmd);
}
CreateTable ct = new CreateTable(_modelType);
string pkName;
bool inPK = ct.InPrimaryKey(_columnRef.ColumnName, out pkName) || connection.IsColumnInPrimaryKey(_columnRef, out pkName);
if (inPK)
{
yield return($"ALTER TABLE [{_columnRef.Schema}].[{_columnRef.TableName}] DROP CONSTRAINT [{pkName}]");
}
ForeignKeyRef fk;
if (_columnRef.IsForeignKey(connection, out fk))
{
yield return($"ALTER TABLE [{_columnRef.Schema}].[{_columnRef.TableName}] DROP CONSTRAINT [{fk.ConstraintName}]");
}
yield return($"ALTER TABLE [{_columnRef.Schema}].[{_columnRef.TableName}] DROP COLUMN [{_columnRef.ColumnName}]");
if (inPK)
{
yield return($"ALTER TABLE [{_columnRef.Schema}].[{_columnRef.TableName}] ADD {ct.CreateTablePrimaryKey(ct.GetClusterAttribute())}");
}
}
public override IEnumerable <string> SqlCommands(IDbConnection connection)
{
foreach (var cmd in base.SqlCommands(connection))
{
yield return(cmd);
}
// if pk column, drop referencing foreign keys, any covering indexes, and then the pk itself
IEnumerable <ForeignKeyRef> referencingFKs = null;
bool inPK = false;
bool rebuildFKs = false;
string pkConstraint; bool isClustered;
CreateTable ct = new CreateTable(_newColumn.ModelType);
if (_newColumn.InPrimaryKey(connection, out pkConstraint, out isClustered) || ct.InPrimaryKey(_newColumn.ColumnName, out pkConstraint))
{
inPK = true;
referencingFKs = connection.GetReferencingForeignKeys(_objectId);
if (referencingFKs.Any(fk => fk.Parent.ColumnName.Equals(_newColumn.ColumnName)))
{
rebuildFKs = true;
foreach (var fk in referencingFKs)
{
yield return($"ALTER TABLE [{fk.Child.Schema}].[{fk.Child.TableName}] DROP CONSTRAINT [{fk.ConstraintName}]");
}
}
// todo: indexes
yield return($"ALTER TABLE [{_newColumn.DbObject.Schema}].[{_newColumn.DbObject.Name}] DROP CONSTRAINT [{pkConstraint}]");
}
// alter desired column
bool withCollation = ColumnRef.IsCollationChanged(_oldColumn, _newColumn);
yield return($"ALTER TABLE [{_newColumn.DbObject.Schema}].[{_newColumn.DbObject.Name}] ALTER COLUMN [{_newColumn.ColumnName}] {_newColumn.GetDataTypeSyntax(withCollation)}");
// rebuild pk and foreign keys
if (inPK)
{
string clustering = (isClustered) ? "CLUSTERED" : "NONCLUSTERED";
yield return($"ALTER TABLE [{_newColumn.DbObject.Schema}].[{_newColumn.DbObject.Name}] ADD CONSTRAINT [{pkConstraint}] PRIMARY KEY {clustering} ({ct.PrimaryKeyColumnSyntax()})");
if (rebuildFKs)
{
foreach (var fk in referencingFKs)
{
yield return($"ALTER TABLE [{fk.Child.Schema}].[{fk.Child.TableName}] ADD CONSTRAINT [{fk.ConstraintName}] FOREIGN KEY ([{fk.Child.ColumnName}]) REFERENCES [{fk.Parent.Schema}].[{fk.Parent.TableName}] ([{fk.Parent.ColumnName}])");
}
}
}
}