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