private void Compare(DatabaseTable databaseTable, ICollection <DatabaseConstraint> firstConstraints, ICollection <DatabaseConstraint> secondConstraints) { foreach (var constraint in firstConstraints) { var constraintName = constraint.Name; var matchConstraint = secondConstraints.FirstOrDefault(c => c.Name == constraintName); if (matchConstraint == null) { CreateResult(ResultType.Delete, databaseTable, constraintName, _writer.DropConstraint(databaseTable, constraint)); continue; } if (!ConstraintColumnsEqual(constraint, matchConstraint)) { CreateResult(ResultType.Change, databaseTable, constraintName, _writer.DropConstraint(databaseTable, constraint) + Environment.NewLine + _writer.AddConstraint(databaseTable, matchConstraint)); continue; } if (constraint.ConstraintType == ConstraintType.Check && constraint.Expression != matchConstraint.Expression) { CreateResult(ResultType.Change, databaseTable, constraintName, _writer.DropConstraint(databaseTable, constraint) + Environment.NewLine + _writer.AddConstraint(databaseTable, matchConstraint)); } if (constraint.ConstraintType == ConstraintType.ForeignKey && constraint.RefersToTable != matchConstraint.RefersToTable) { //unlikely a foreign key will change the fk table without changing name CreateResult(ResultType.Change, databaseTable, constraintName, _writer.DropConstraint(databaseTable, constraint) + Environment.NewLine + _writer.AddConstraint(databaseTable, matchConstraint)); } } foreach (var constraint in secondConstraints) { var constraintName = constraint.Name; var firstConstraint = firstConstraints.FirstOrDefault(c => c.Name == constraintName); if (firstConstraint == null) { CreateResult(ResultType.Add, databaseTable, constraintName, _writer.AddConstraint(databaseTable, constraint)); } } }
public void Execute(IEnumerable <DatabaseTable> baseTables, IEnumerable <DatabaseTable> compareTables) { //find new tables (in compare, but not in base) var newTables = new List <DatabaseTable>(); foreach (var databaseTable in compareTables) { var name = databaseTable.Name; var schema = databaseTable.SchemaOwner; var match = baseTables.FirstOrDefault(t => t.Name == name && t.SchemaOwner == schema); if (match != null) { continue; } var script = "-- NEW TABLE " + databaseTable.Name + Environment.NewLine + _writer.AddTable(databaseTable); CreateResult(ResultType.Add, databaseTable, script); newTables.Add(databaseTable); } //find dropped and existing tables foreach (var databaseTable in baseTables) { var name = databaseTable.Name; var schema = databaseTable.SchemaOwner; var match = compareTables.FirstOrDefault(t => t.Name == name && t.SchemaOwner == schema); if (match == null) { CreateResult(ResultType.Delete, databaseTable, _writer.DropTable(databaseTable)); continue; } //table may or may not have been changed //add, alter and delete columns var compareColumns = new CompareColumns(_results, _writer); compareColumns.Execute(databaseTable, match); //add, alter and delete constraints var compareConstraints = new CompareConstraints(_results, _writer); compareConstraints.Execute(databaseTable, match); //indexes var compareIndexes = new CompareIndexes(_results, _writer); compareIndexes.Execute(databaseTable, match); //triggers var compareTriggers = new CompareTriggers(_results, _writer); compareTriggers.Execute(databaseTable, match); } //add tables doesn't add foreign key constraints (wait until all tables created) foreach (var databaseTable in newTables) { foreach (var foreignKey in databaseTable.ForeignKeys) { var result = new CompareResult { SchemaObjectType = SchemaObjectType.Constraint, ResultType = ResultType.Add, SchemaOwner = databaseTable.SchemaOwner, TableName = databaseTable.Name, Name = foreignKey.Name, Script = _writer.AddConstraint(databaseTable, foreignKey) }; _results.Add(result); } foreach (var trigger in databaseTable.Triggers) { var result = new CompareResult { SchemaObjectType = SchemaObjectType.Trigger, ResultType = ResultType.Add, Name = trigger.Name, SchemaOwner = databaseTable.SchemaOwner, TableName = databaseTable.Name, Script = _writer.AddTrigger(databaseTable, trigger) }; _results.Add(result); } } }