private List <SynchronizationItem> getDefaultConstraintCreateUpdateItems(Table targetTable)
{
var skips = new List <string>();
var items = new List <SynchronizationItem>();
if (targetTable != null)
{
foreach (var toConstraint in targetTable.DefaultConstraints)
{
Difference diff = null;
bool drop = false;
var fromConstraint = databaseObject.DefaultConstraints.FirstOrDefault(c => c.Table.TableName == databaseObject.TableName &&
c.ConstraintName == toConstraint.ConstraintName);
if (fromConstraint == null)
{
drop = true;
}
else if (fromConstraint.Column.ColumnName != toConstraint.Column.ColumnName ||
fromConstraint.ColumnDefault.Replace("((", "(").Replace("))", ")")
!= toConstraint.ColumnDefault.Replace("((", "(").Replace("))", ")"))
{
diff = new Difference()
{
PropertyName = "ColumnDefault",
SourceValue = fromConstraint.ColumnDefault,
TargetValue = toConstraint.ColumnDefault
};
var creates = new DefaultConstraintSynchronization(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(toConstraint).GetRawDropText());
items.Add(item);
}
if (drop)
{
// table was dropped
items.AddRange(new DefaultConstraintSynchronization(toConstraint).GetDropItems());
}
else
{
skips.Add(toConstraint.ConstraintName);
}
}
}
foreach (var fromConstraint in databaseObject.DefaultConstraints)
{
if (skips.Contains(fromConstraint.ConstraintName))
{
continue;
}
if (_createdColumns.Contains(fromConstraint.Column.ObjectName))
{
continue;
}
items.AddRange(new DefaultConstraintSynchronization(fromConstraint).GetCreateItems());
}
return(items);
}
public List <SynchronizationItem> GetAddAlterItems(Column targetColumn)
{
var items = new List <SynchronizationItem>();
SynchronizationItem item = null;
var sb = new StringBuilder();
if (!string.IsNullOrEmpty(databaseObject.Formula))
{
if (targetColumn == null || databaseObject.Formula != targetColumn.Formula)
{
item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = "Formula", SourceValue = databaseObject.Formula, TargetValue = targetColumn == null ? string.Empty : targetColumn.Formula
});
if (targetColumn != null)
{
item.AddScript(1, string.Format("ALTER TABLE [{0}].[{1}] DROP COLUMN [{2}]", databaseObject.Table.Schema.SchemaName,
databaseObject.Table.TableName, databaseObject.ColumnName));
}
item.AddScript(3, string.Format("ALTER TABLE [{0}].[{1}] ADD [{2}] AS {3}",
databaseObject.Table.Schema.SchemaName,
databaseObject.Table.TableName,
databaseObject.ColumnName,
databaseObject.Formula));
items.Add(item);
return(items);
}
}
var differences = targetColumn == null ? new List <Difference>()
{
new Difference()
{
PropertyName = Difference.CREATE
}
}
: base.GetPropertyDifferences(targetColumn);
// case mismatch
if (targetColumn != null && targetColumn.ColumnName != databaseObject.ColumnName)
{
item = new SynchronizationItem(databaseObject);
item.AddScript(2, string.Format("EXEC sp_rename '{0}.{1}.{2}', '{3}', 'COLUMN'",
targetColumn.Table.Schema.SchemaName,
targetColumn.Table.TableName,
targetColumn.ColumnName,
databaseObject.ColumnName));
item.Differences.Add(new Difference()
{
PropertyName = "ColumnName",
SourceValue = databaseObject.ColumnName,
TargetValue = targetColumn.ColumnName
});
items.Add(item);
var diff = differences.First(d => d.PropertyName == "ColumnName");
differences.Remove(diff);
}
if (!differences.Any())
{
return(items);
}
string part2 = GetPostScript();
string def = string.Empty;
string tempConstraint = null;
// default constraints for existing cols need to be created after the fact
if (targetColumn == null)
{
def = GetDefaultScript();
if (!databaseObject.IsNullable && string.IsNullOrEmpty(def) && databaseObject.DataType != "timestamp")
{
// added columns to existing tables must have default so we must add a temporary one for now
var sqlDbType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType),
databaseObject.DataType == "numeric" ? "decimal" : databaseObject.DataType, true);
var clrType = Common.DataHelper.GetClrType(sqlDbType);
clrType = clrType.GetGenericArguments().FirstOrDefault() ?? clrType;
tempConstraint = "constraint_" + Guid.NewGuid().ToString().Replace("-", "_");
def = "CONSTRAINT [" + tempConstraint + "] DEFAULT({0})";
if (clrType.Equals(typeof(string)))
{
def = string.Format(def, "''");
}
else if (clrType.Equals(typeof(DateTime)) || clrType.Equals(typeof(DateTimeOffset)))
{
def = string.Format(def, "'1/1/1900'");
}
else if (clrType.IsNumericType())
{
def = string.Format(def, 0);
}
else if (clrType.Equals(typeof(byte[])))
{
def = string.Format(def, "0x");
}
else if (clrType.Equals(typeof(bool)))
{
def = string.Format(def, "0");
}
else if (clrType.Equals(typeof(Guid)))
{
def = string.Format(def, "'" + Guid.Empty.ToString() + "'");
}
else
{
throw new NotImplementedException();
}
}
}
sb.AppendLineFormat("ALTER TABLE [{0}].[{1}] {7} [{2}] [{3}]{4} {5} {6}",
databaseObject.Table.Schema.SchemaName,
databaseObject.Table.TableName,
databaseObject.ColumnName,
databaseObject.DataType,
part2,
databaseObject.IsNullable ? "NULL" : "NOT NULL",
def,
targetColumn == null ? "ADD" : "ALTER COLUMN");
if (!string.IsNullOrEmpty(tempConstraint))
{
sb.AppendLineFormat("ALTER TABLE [{0}].[{1}] {7} [{2}] [{3}]{4} {5} {6}",
databaseObject.Table.Schema.SchemaName,
databaseObject.Table.TableName,
databaseObject.ColumnName,
databaseObject.DataType,
part2,
databaseObject.IsNullable ? "NULL" : "NOT NULL",
string.Empty,
"ALTER COLUMN");
sb.AppendLineFormat("ALTER TABLE [{0}].[{1}] DROP CONSTRAINT [{2}]", databaseObject.Table.Schema.SchemaName, databaseObject.Table.TableName, tempConstraint);
}
item = new SynchronizationItem(databaseObject);
item.AddScript(2, sb.ToString());
item.Differences.AddRange(differences);
items.Add(item);
var kcs = databaseObject.Table.KeyConstraints.Where(k => !k.IsPrimaryKey && k.Columns.Any(ic => ic.ColumnName == databaseObject.ColumnName));
foreach (var kc in kcs)
{
var syncItem = new KeyConstraintSynchronization(kc);
item.AddScript(0, syncItem.GetRawDropText());
item.AddScript(10, syncItem.GetRawCreateText());
}
if (targetColumn != null)
{
var dcs = databaseObject.Table.DefaultConstraints.Where(dc => dc.Column.ColumnName == databaseObject.ColumnName);
foreach (var dc in dcs)
{
var syncItem = new DefaultConstraintSynchronization(dc);
item.AddScript(0, syncItem.GetRawDropText());
item.AddScript(10, syncItem.GetRawCreateText());
}
}
return(items);
}
