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