private List <SynchronizationItem> getDefaultConstraintCreateUpdateItems(Table targetTable, bool ignoreCase) { var skips = new List <DefaultConstraint>(); var items = new List <SynchronizationItem>(); if (targetTable != null) { foreach (var toConstraint in targetTable.DefaultConstraints) { Difference diff = null; bool drop = false; DefaultConstraint fromConstraint = null; if (!TargetDatabase.DataSource.NamedConstraints || !DatabaseObject.Database.DataSource.NamedConstraints) { fromConstraint = DatabaseObject.DefaultConstraints.FirstOrDefault(c => string.Compare(c.Table.TableName, DatabaseObject.TableName, ignoreCase) == 0 && string.Compare(c.Column.ColumnName, toConstraint.Column.ColumnName, ignoreCase) == 0); } else { fromConstraint = DatabaseObject.DefaultConstraints.FirstOrDefault(c => string.Compare(c.Table.TableName, DatabaseObject.TableName, ignoreCase) == 0 && string.Compare(c.ConstraintName, toConstraint.ConstraintName, ignoreCase) == 0); } if (fromConstraint == null) { drop = true; } else if (fromConstraint.Column.ColumnName != toConstraint.Column.ColumnName || TargetDatabase.DataSource.GetColumnDefault(fromConstraint.Column, fromConstraint.ColumnDefault).Replace("(", "").Replace(")", "") != TargetDatabase.DataSource.GetColumnDefault(toConstraint.Column, toConstraint.ColumnDefault).Replace("(", "").Replace(")", "")) { diff = getDifference(DifferenceType.Alter, fromConstraint, toConstraint, "ColumnDefault", TargetDatabase.DataSource.GetColumnDefault(fromConstraint.Column, fromConstraint.ColumnDefault), TargetDatabase.DataSource.GetColumnDefault(toConstraint.Column, toConstraint.ColumnDefault) ); var creates = new DefaultConstraintSynchronization(TargetDatabase, fromConstraint).GetCreateItems(); var item = creates.First(); item.Differences.Add(diff); // handled on column add if (_createdColumns.Contains(fromConstraint.Column.ColumnName)) { item.Scripts.Clear(); } item.AddScript(0, new DefaultConstraintSynchronization(TargetDatabase, toConstraint).GetRawDropText()); items.Add(item); } if (drop) { // table was dropped items.AddRange(new DefaultConstraintSynchronization(TargetDatabase, toConstraint).GetDropItems(DatabaseObject)); } else { skips.Add(toConstraint); } } } foreach (var fromConstraint in DatabaseObject.DefaultConstraints) { var to = skips.FirstOrDefault(s => s.ConstraintName == fromConstraint.ConstraintName); if (to == null && (!TargetDatabase.DataSource.NamedConstraints || !DatabaseObject.Database.DataSource.NamedConstraints)) { to = skips.FirstOrDefault(s => s.Column.ColumnName == fromConstraint.Column.ColumnName); } if (to != null) { continue; } if (_createdColumns.Contains(fromConstraint.Column.ObjectName)) { continue; } items.AddRange(new DefaultConstraintSynchronization(TargetDatabase, fromConstraint).GetCreateItems()); } return(items); }
public List <SynchronizationItem> GetAddAlterItems(Column targetColumn, bool ignoreCase, bool condensed) { var items = new List <SynchronizationItem>(); SynchronizationItem item = null; var sb = new StringBuilder(); if (!string.IsNullOrEmpty(DatabaseObject.Formula)) { if (targetColumn == null || DatabaseObject.Formula != targetColumn.Formula) { var diff = getDifference(DifferenceType.Alter, DatabaseObject, targetColumn, "Formula", DatabaseObject.Formula, targetColumn == null ? string.Empty : targetColumn.Formula); if (diff == null) { return(new List <SynchronizationItem>()); } item = new SynchronizationItem(DatabaseObject); item.Differences.Add(diff); if (targetColumn != null) { item.AddScript(1, string.Format("ALTER TABLE {0} DROP COLUMN {1};", DatabaseObject.Parent.GetObjectNameWithSchema(TargetDatabase.DataSource), DatabaseObject.GetQueryObjectName(TargetDatabase.DataSource))); } item.AddScript(3, string.Format("ALTER TABLE {0} ADD {1} AS {2}", DatabaseObject.Parent.GetObjectNameWithSchema(TargetDatabase.DataSource), DatabaseObject.GetQueryObjectName(TargetDatabase.DataSource), DatabaseObject.Formula)); items.Add(item); return(items); } } var differences = new List <Difference>(); if (targetColumn == null) { var difference = getDifference(DifferenceType.Create, DatabaseObject); if (difference != null) { differences.Add(difference); } } else { differences = base.GetPropertyDifferences(targetColumn, ignoreCase); } if (condensed && differences.Any()) { differences = differences.Where(x => x.PropertyName != "ColumnDefault").ToList(); } if (differences.Any()) { } // case mismatch if (!ignoreCase && targetColumn != null && targetColumn.ColumnName != DatabaseObject.ColumnName) { item = new SynchronizationItem(DatabaseObject); item.AddScript(2, string.Format("EXEC sp_rename '{0}.{1}.{2}', '{3}', 'COLUMN'", targetColumn.Parent.Schema.SchemaName, targetColumn.Parent.ObjectName, targetColumn.ColumnName, DatabaseObject.ColumnName)); var diff = getDifference(DifferenceType.Alter, DatabaseObject, targetColumn, "Column", DatabaseObject.ColumnName, targetColumn.ColumnName); if (diff != null) { item.Differences.Add(diff); } if (item.Differences.Count > 0) { items.Add(item); } diff = differences.FirstOrDefault(d => d.PropertyName == "ColumnName"); if (diff != null) { differences.Remove(diff); } } if (!differences.Any()) { return(items); } string part2 = TargetDatabase.DataSource.GetColumnPostPart(DatabaseObject); string def = string.Empty; var tempConstraint = ""; bool hasTempConstraint = false; // default constraints for existing cols need to be created after the fact if (targetColumn == null) { def = GetDefaultScript(); if (!DatabaseObject.IsNullable && !DatabaseObject.IsIdentity && string.IsNullOrEmpty(def) && DatabaseObject.ColumnType.DataType != DataType.RowVersion) { var colType = TargetDatabase.DataSource.ColumnTypes.First(c => c.DataType == DatabaseObject.ColumnType.DataType); hasTempConstraint = true; if (TargetDatabase.DataSource.DefaultNamedConstraints) { tempConstraint = "constraint_" + Guid.NewGuid().ToString().Replace("-", "_"); DatabaseObject.ConstraintName = tempConstraint; } DatabaseObject.ColumnDefault = colType.DefaultValue; def = GetDefaultScript(); } } sb.AppendLine(TargetDatabase.DataSource.GetColumnAddAlterScript(DatabaseObject, targetColumn, part2, def)); if (hasTempConstraint && DatabaseObject.Parent is Table) { // sb.AppendLine(TargetDatabase.DataSource.GetColumnAddAlterScript(DatabaseObject, targetColumn, part2, string.Empty)); var defConstraint = new DefaultConstraint(TargetDatabase); defConstraint.ConstraintName = tempConstraint; defConstraint.Table = DatabaseObject.Parent as Table; defConstraint.Column = DatabaseObject; var defSync = new DefaultConstraintSynchronization(TargetDatabase, defConstraint); var currNamed = TargetDatabase.DataSource.NamedConstraints; TargetDatabase.DataSource.NamedConstraints = TargetDatabase.DataSource.DefaultNamedConstraints; sb.AppendLine(defSync.GetRawDropText()); TargetDatabase.DataSource.NamedConstraints = currNamed; } item = new SynchronizationItem(DatabaseObject); item.AddScript(2, sb.ToString()); item.Differences.AddRange(differences); items.Add(item); var kcs = (DatabaseObject.Parent as Table).KeyConstraints.Where(k => !k.IsPrimaryKey && k.Columns.Any(ic => ic.ColumnName == DatabaseObject.ColumnName)); foreach (var kc in kcs) { var syncItem = new KeyConstraintSynchronization(TargetDatabase, kc); item.AddScript(0, syncItem.GetRawDropText()); item.AddScript(10, syncItem.GetRawCreateText()); } if (targetColumn != null && !condensed) { var dcs = (DatabaseObject.Parent as Table).DefaultConstraints.Where(dc => dc.Column.ColumnName == DatabaseObject.ColumnName); foreach (var dc in dcs) { var syncItem = new DefaultConstraintSynchronization(TargetDatabase, dc); item.AddScript(0, syncItem.GetRawDropText()); item.AddScript(10, syncItem.GetRawCreateText()); } } return(items); }