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> ValidationErrors(IDbConnection connection)
{
foreach (var pkProp in CreateTable.PrimaryKeyProperties(_object.ModelType, true))
{
foreach (var err in pkProp.GetPrimaryKeyValidationErrors())
{
yield return(err);
}
}
}
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}])");
}
}
}
}
public override IEnumerable <string> SqlCommands(IDbConnection connection)
{
foreach (var cmd in base.SqlCommands(connection))
{
yield return(cmd);
}
var modelType = _object.ModelType;
var ct = new CreateTable(modelType);
ClusterAttribute clusterAttribute =
modelType.GetCustomAttribute <ClusterAttribute>() ??
new ClusterAttribute(ClusterOption.PrimaryKey);
yield return($"ALTER TABLE [{_object.Schema}].[{_object.Name}] ADD {ct.CreateTablePrimaryKey(clusterAttribute)}");
}
public override IEnumerable <string> SqlCommands(IDbConnection connection)
{
foreach (var cmd in base.SqlCommands(connection))
{
yield return(cmd);
}
DbObject newTable = DbObject.FromType(_modelType);
CreateTable ct = new CreateTable(_modelType);
if (!connection.TableExists(newTable.Schema, newTable.Name))
{
foreach (var cmd in ct.SqlCommands(connection))
{
yield return(cmd);
}
}
DbObject oldTable = DbObject.Parse(_attr.OldName);
if (connection.TableExists(oldTable.Schema, oldTable.Name))
{
if (!connection.IsTableEmpty(oldTable.Schema, oldTable.Name))
{
yield return($"SET IDENTITY_INSERT [{newTable.Schema}].[{newTable.Name}] ON");
string columnNames = string.Join(", ", ct.ColumnProperties().Select(pi => $"[{pi.SqlColumnName()}]").Concat(new string[] { $"[{SqlDb<int>.IdentityColumnName}]" }));
yield return($"INSERT INTO [{newTable.Schema}].[{newTable.Name}] ({columnNames}) SELECT {columnNames} FROM [{oldTable.Schema}].[{oldTable.Name}]");
yield return($"SET IDENTITY_INSERT [{newTable.Schema}].[{newTable.Name}] OFF");
}
DbObject.SetObjectId(connection, oldTable);
foreach (var cmd in connection.GetFKDropStatements(oldTable.ObjectId))
{
yield return(cmd);
}
yield return($"DROP TABLE [{oldTable.Schema}].[{oldTable.Name}]");
}
}
