public override List <SynchronizationItem> GetDropItems()
{
if (databaseObject.PrincipalType == PrincipalType.DatabaseRole)
{
var items = new List <SynchronizationItem>();
SynchronizationItem item;
StringBuilder sb = new StringBuilder();
foreach (var child in databaseObject.ChildMembers)
{
item = new SynchronizationItem(child);
item.Differences.Add(new Difference()
{
PropertyName = "Drop - " + child.ObjectName
});
item.AddScript(1, string.Format("ALTER ROLE [{0}] DROP MEMBER [{1}]", databaseObject.ObjectName, child.ObjectName));
items.Add(item);
}
item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = "Drop - " + databaseObject.ObjectName
});
item.AddScript(2, string.Format("DROP ROLE [{0}]", databaseObject.ObjectName));
items.Add(item);
return(items);
}
return(getStandardDropItems(string.Format("DROP USER [{0}]", databaseObject.ObjectName)));
}
public override List <SynchronizationItem> GetCreateItems()
{
var items = new List <SynchronizationItem>();
var item = new SynchronizationItem(databaseObject);
items.Add(item);
item.Differences.Add(new Difference()
{
PropertyName = Difference.CREATE
});
item.AddScript(2, getCreateScript());
foreach (var fk in databaseObject.ForeignKeys)
{
items.AddRange(new ForeignKeySynchronization(fk).GetCreateItems());
}
items.AddRange(getIndexCreateUpdateItems(null));
items.AddRange(getDefaultConstraintCreateUpdateItems(null));
items.AddRange(getTriggerUpdateItems(null));
foreach (var column in databaseObject.Columns)
{
items.AddRange(ExtendedPropertySynchronization.GetExtendedProperties(column, null));
}
return(items);
}
public override List <SynchronizationItem> GetAlterItems(DatabaseObjectBase target)
{
var differences = base.GetPropertyDifferences(target);
if (databaseObject.IgnoreSchema)
{
var schemDiff = differences.FirstOrDefault(d => d.PropertyName == "ObjectSchema");
if (schemDiff != null)
{
differences.Remove(schemDiff);
}
}
if (differences.Any())
{
var syncItem = new SynchronizationItem(databaseObject);
syncItem.Differences.AddRange(differences);
syncItem.AddScript(0, GetRawDropText());
syncItem.AddScript(1, GetRawCreateText());
return(new List <SynchronizationItem>()
{
syncItem
});
}
return(new List <SynchronizationItem>());
}
public override List <SynchronizationItem> GetSynchronizationItems(DatabaseObjectBase target)
{
var items = base.GetSynchronizationItems(target);
var ext = target as ExtendedProperty;
if ((ext.PropValue != null && databaseObject.PropValue == null) || (ext.PropValue == null && databaseObject.PropValue != null) ||
(ext.PropValue != null && databaseObject.PropValue != null && ext.PropValue.ToString().Trim() != databaseObject.PropValue.ToString().Trim()))
{
var item = items.FirstOrDefault();
if (item == null)
{
item = new SynchronizationItem(ext);
items.Add(item);
}
item.Differences.Add(new Difference()
{
PropertyName = databaseObject.ObjectName,
SourceValue = ext.PropValue == null ? string.Empty : databaseObject.PropValue.ToString(),
TargetValue = databaseObject.PropValue == null ? string.Empty : ext.PropValue.ToString()
});
item.AddScript(1, new ExtendedPropertySynchronization(databaseObject).GetRawDropText());
item.AddScript(7, getAddScript());
}
return(items);
}
//public override List<Difference> GetDifferences(DatabaseObjectBase target)
//{
// return (from si in GetSynchronizationItems(target)
// from d in si.Differences
// select d).ToList();
//}
//public override List<Difference> GetDifferences(DatabaseObjectBase target)
//{
// var diffs = base.GetDifferences(target);
// foreach (var col in databaseObject.Columns)
// {
// diffs.AddRange(new ColumnSynchronization(col).GetDifferences(target == null ? null : (target as Table).Columns.FirstOrDefault(x => x.ColumnName == col.ColumnName)));
// }
// foreach (var i in databaseObject.Indexes)
// {
// diffs.AddRange(new IndexSynchronization(i).GetDifferences(target == null ? null : (target as Table).Indexes.FirstOrDefault(x => x.IndexName == i.IndexName)));
// }
// foreach (var kc in databaseObject.KeyConstraints)
// {
// diffs.AddRange(new KeyConstraintSynchronization(kc).GetDifferences(target == null ? null : (target as Table).KeyConstraints.FirstOrDefault(x => x.ConstraintName == kc.ConstraintName)));
// }
// foreach (var fk in databaseObject.ForeignKeys)
// {
// diffs.AddRange(new ForeignKeySynchronization(fk).GetDifferences(target == null ? null : (target as Table).ForeignKeys.FirstOrDefault(x => x.ForeignKeyName == fk.ForeignKeyName)));
// }
// foreach (var dc in databaseObject.DefaultConstraints)
// {
// diffs.AddRange(new DefaultConstraintSynchronization(dc).GetDifferences(target == null ? null : (target as Table).DefaultConstraints.FirstOrDefault(x => x.ConstraintName == dc.ConstraintName)));
// }
// foreach (var t in databaseObject.Triggers)
// {
// diffs.AddRange(new TriggerSynchronization(t).GetDifferences(target == null ? null : (target as Table).Triggers.FirstOrDefault(x => x.TriggerName == t.TriggerName)));
// }
// return diffs;
//}
private SynchronizationItem getRecreateTableItem(Table targetTable)
{
var item = new SynchronizationItem(databaseObject);
var tmpTable = "#tmp" + Guid.NewGuid().ToString().Replace("-", "_");
item.AddScript(0, string.Format("SELECT * INTO {0} FROM [{1}].[{2}]",
tmpTable,
databaseObject.Schema.SchemaName,
databaseObject.TableName
));
var foreignKeys = from s in targetTable.Schema.Database.Schemas
from t in s.Tables
from fk in t.ForeignKeys
where fk.ParentTable.ObjectName == databaseObject.ObjectName ||
fk.ChildTable.ObjectName == databaseObject.ObjectName
select fk;
foreach (var fk in foreignKeys)
{
item.AddScript(4, new ForeignKeySynchronization(fk).GetDropItems().ToString());
}
item.AddScript(4, string.Format("DROP TABLE [{0}].[{1}]", databaseObject.Schema.SchemaName,
databaseObject.TableName));
item.AddScript(5, getCreateScript());
if (databaseObject.Columns.Any(c => c.IsIdentity))
{
item.AddScript(6, string.Format("SET IDENTITY_INSERT [{0}].[{1}] ON", databaseObject.Schema.SchemaName, databaseObject.TableName));
}
item.AddScript(6, string.Format("INSERT INTO [{0}].[{1}] ({2}) SELECT {2} FROM [{3}]",
databaseObject.Schema.SchemaName,
databaseObject.TableName,
string.Join(",", databaseObject.Columns.Where(c => c.DataType != "timestamp").Select(c => "[" + c.ColumnName + "]").ToArray()),
tmpTable));
if (databaseObject.Columns.Any(c => c.IsIdentity))
{
item.AddScript(6, string.Format("SET IDENTITY_INSERT [{0}].[{1}] OFF", databaseObject.Schema.SchemaName, databaseObject.TableName));
}
foreach (var fk in foreignKeys)
{
foreach (var i in new ForeignKeySynchronization(fk).GetCreateItems())
{
foreach (var kvp in i.Scripts.Where(s => s.Value.Length > 0))
{
item.AddScript(7, kvp.Value.ToString());
}
}
}
item.AddScript(7, string.Format("DROP TABLE [{0}]", tmpTable));
return(item);
}
protected List <SynchronizationItem> getStandardItems(string script, int level = 4, string propertyName = Difference.CREATE)
{
var item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = propertyName
});
item.AddScript(level, script);
return(new List <SynchronizationItem>()
{
item
});
}
public override List <SynchronizationItem> GetSynchronizationItems(DatabaseObjectBase target)
{
if (target == null)
{
return(base.GetSynchronizationItems(target));
}
var items = new List <SynchronizationItem>();
var targetPermission = target as Permission;
List <PermissionPrincipal> skips = new List <PermissionPrincipal>();
foreach (var pp in databaseObject.PermissionPrincipals)
{
var tpp = targetPermission.PermissionPrincipals.FirstOrDefault(p => pp.IsEqual(p));
if (tpp == null)
{
var item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = Difference.CREATE
});
item.AddScript(2, pp.GetCreateRemoveScript(true));
items.Add(item);
skips.Add(pp);
}
}
foreach (var pp in targetPermission.PermissionPrincipals)
{
if (skips.Any(s => s.PermissionType == pp.PermissionType && s.DatbasePrincipal.PrincipalName == pp.DatbasePrincipal.PrincipalName))
{
continue;
}
var tpp = databaseObject.PermissionPrincipals.FirstOrDefault(p => pp.IsEqual(p));
if (tpp == null)
{
var item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = Difference.DROP
});
item.AddScript(2, pp.GetCreateRemoveScript(false));
items.Add(item);
}
}
return(items);
}
public override List <SynchronizationItem> GetCreateItems()
{
var items = getStandardItems(databaseObject.Definition.Trim(), level: 10 * getIndex());
if (databaseObject.Disabled)
{
var item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = "Disabled"
});
item.AddScript(11 * getIndex(), string.Format("DISABLE TRIGGER [{0}] ON [{1}]", databaseObject.TriggerName, databaseObject.Table.TableName));
items.Add(item);
}
return(items);
}
public override List <SynchronizationItem> GetCreateItems()
{
var items = getStandardItems(getLoginScript(true));
if (databaseObject.IsDisabled)
{
var item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = "Disabled"
});
item.AddScript(7, string.Format("\r\nALTER LOGIN [{0}] DISABLE", databaseObject.LoginName));
items.Add(item);
}
return(items);
}
public override List <SynchronizationItem> GetAlterItems(DatabaseObjectBase target)
{
var diff = GetPropertyDifferences(target);
if (diff.Count == 1 && diff[0].PropertyName == "IsDisabled")
{
var item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = "Disabled"
});
item.AddScript(7, string.Format("\r\nALTER LOGIN [{0}] {1}", databaseObject.LoginName, databaseObject.IsDisabled ? "DISABLE" : "ENABLE"));
return(new List <SynchronizationItem>()
{
item
});
}
return(new List <SynchronizationItem>());
}
public virtual List <SynchronizationItem> GetAlterItems(DatabaseObjectBase target)
{
var items = GetCreateItems();
var dropItem = items.FirstOrDefault();
if (dropItem == null)
{
dropItem = new SynchronizationItem(databaseObject);
items.Insert(0, dropItem);
}
var diff = dropItem.Differences.FirstOrDefault();
if (diff != null && diff.PropertyName == Difference.CREATE)
{
dropItem.Differences.Remove(diff);
}
dropItem.Differences.AddRange(GetPropertyDifferences(target));
dropItem.AddScript(0, GetRawDropText());
return(items);
}
public override List <SynchronizationItem> GetSynchronizationItems(DatabaseObjectBase target)
{
if (target == null)
{
return(base.GetSynchronizationItems(target));
}
var targetSchema = target as Schema;
if (databaseObject.SchemaOwner != targetSchema.SchemaOwner)
{
var item = new SynchronizationItem(databaseObject);
item.Differences.AddRange(GetPropertyDifferences(target));
item.AddScript(7, string.Format(@"ALTER AUTHORIZATION ON SCHEMA::[{0}] TO [{1}]", databaseObject.SchemaName, databaseObject.SchemaOwner));
return(new List <SynchronizationItem>()
{
item
});
}
return(new List <SynchronizationItem>());
}
public override List <SynchronizationItem> GetSynchronizationItems(DatabaseObjectBase target)
{
if (target == null)
{
return(base.GetSynchronizationItems(target));
}
var targetCredential = target as Credential;
if (databaseObject.CredentialIdentity != targetCredential.CredentialIdentity)
{
var item = new SynchronizationItem(databaseObject);
item.Differences.AddRange(GetPropertyDifferences(target));
item.AddScript(7, string.Format(@"ALTER CREDENTIAL [{0}] WITH IDENTITY = '{1}'", databaseObject.CredentialName, databaseObject.CredentialIdentity));
return(new List <SynchronizationItem>()
{
item
});
}
return(new List <SynchronizationItem>());
}
public override List <SynchronizationItem> GetAlterItems(DatabaseObjectBase target)
{
var items = base.GetAlterItems(target);
if (target != null)
{
var targetKey = target as KeyConstraint;
var childKeys = from t in databaseObject.Table.Schema.Tables
from fk in t.ForeignKeys
where fk.ParentTable.TableName == targetKey.Table.TableName
select fk;
foreach (var childKey in childKeys)
{
var childSync = new ForeignKeySynchronization(childKey);
var item = new SynchronizationItem(childKey);
foreach (var dropItem in childSync.GetDropItems())
{
item.Differences.AddRange(dropItem.Differences);
foreach (var script in dropItem.Scripts)
{
item.AddScript(-1, script.Value.ToString());
}
}
foreach (var createItem in childSync.GetCreateItems())
{
item.Differences.AddRange(createItem.Differences);
foreach (var script in createItem.Scripts)
{
item.AddScript(100, script.Value.ToString());
}
}
items.Add(item);
}
}
return(items);
}
public override List <SynchronizationItem> GetAlterItems(DatabaseObjectBase target)
{
var diffs = GetPropertyDifferences(target);
var diff = getColumnDifference(target);
if (diff != null)
{
diffs.Add(diff);
}
if (diffs.Any())
{
var syncItem = new SynchronizationItem(databaseObject);
syncItem.Differences.AddRange(diffs);
syncItem.AddScript(0, GetRawDropText());
syncItem.AddScript(1, GetRawCreateText());
return(new List <SynchronizationItem>()
{
syncItem
});
}
return(new List <SynchronizationItem>());
}
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);
}
public override List <SynchronizationItem> GetSynchronizationItems(DatabaseObjectBase target)
{
var items = new List <SynchronizationItem>();
if (databaseObject.PrincipalType != PrincipalType.DatabaseRole)
{
if (target == null)
{
return(base.GetSynchronizationItems(target));
}
var diffs = GetPropertyDifferences(target);
if (diffs.Any())
{
if (target != null && databaseObject.PrincipalName == "dbo")
{
var item = new SynchronizationItem(databaseObject);
item.Differences.AddRange(diffs);
item.AddScript(2, string.Format("sp_changedbowner '{0}'", databaseObject.LoginName));
return(new List <SynchronizationItem>()
{
item
});
}
else
{
var item = new SynchronizationItem(databaseObject);
item.Differences.AddRange(diffs);
item.AddScript(2, string.Format("ALTER USER [{0}] WITH DEFAULT_SCHEMA = [{1}]{2}", databaseObject.PrincipalName,
databaseObject.DefaultSchema, string.IsNullOrEmpty(databaseObject.LoginName) ?
string.Empty : string.Format(", LOGIN = [{0}]", databaseObject.LoginName)));
return(new List <SynchronizationItem>()
{
item
});
}
}
return(new List <SynchronizationItem>());
}
var dp = target as DatabasePrincipal;
if (dp == null)
{
var item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = Difference.CREATE
});
if (!item.Scripts.ContainsKey(7))
{
item.Scripts.Add(7, new StringBuilder());
}
item.AddScript(7, string.Format(@"CREATE ROLE [{0}] {1}", databaseObject.PrincipalName, databaseObject.Owner == null ? string.Empty :
string.Format("AUTHORIZATION [{0}]", databaseObject.Owner.ObjectName)));
items.Add(item);
}
else
{
if (databaseObject.Owner.PrincipalName != dp.Owner.PrincipalName)
{
var item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = "Owner",
SourceValue = databaseObject.Owner.PrincipalName,
TargetValue = dp.Owner.PrincipalName
});
if (!item.Scripts.ContainsKey(7))
{
item.Scripts.Add(7, new StringBuilder());
}
item.AddScript(7, string.Format("ALTER AUTHORIZATION ON ROLE::[{0}] TO [{1}]", databaseObject.ObjectName, databaseObject.Owner.PrincipalName));
items.Add(item);
}
var drops = dp.ChildMembers.Where(m => !databaseObject.ChildMembers.Any(x => x.ObjectName == m.ObjectName));
foreach (var drop in drops)
{
var item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = "Member",
SourceValue = "Drop",
TargetValue = drop.ObjectName
});
if (!item.Scripts.ContainsKey(7))
{
item.Scripts.Add(7, new StringBuilder());
}
item.AddScript(7, string.Format("ALTER ROLE [{0}] DROP MEMBER [{1}]", databaseObject.ObjectName, drop.ObjectName));
items.Add(item);
}
}
var creates = databaseObject.ChildMembers.Where(m => target == null || !dp.ChildMembers.Any(x => x.ObjectName == m.ObjectName));
foreach (var create in creates)
{
var item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = "Member",
SourceValue = "Create",
TargetValue = create.ObjectName
});
if (!item.Scripts.ContainsKey(7))
{
item.Scripts.Add(7, new StringBuilder());
}
item.AddScript(7, string.Format("ALTER ROLE [{0}] ADD MEMBER [{1}]", databaseObject.ObjectName, create.ObjectName));
items.Add(item);
}
return(items);
}
private List <SynchronizationItem> getIndexCreateUpdateItems(Table targetTable)
{
var items = new List <SynchronizationItem>();
var skips = new List <string>();
if (targetTable != null)
{
foreach (var toIndex in targetTable.Indexes)
{
bool drop = false;
Difference diff = null;
var fromIndex = databaseObject.Indexes.FirstOrDefault(c => c.IndexName == toIndex.IndexName);
if (fromIndex == null)
{
drop = true;
}
else if (fromIndex.IndexColumns.Any(c => !toIndex.IndexColumns.Any(t => t.ColumnName == c.ColumnName &&
t.Ordinal == c.Ordinal && t.Descending == c.Descending)) ||
toIndex.IndexColumns.Any(c => !fromIndex.IndexColumns.Any(t => t.ColumnName == c.ColumnName &&
t.Ordinal == c.Ordinal && t.Descending == c.Descending)))
{
diff = new Difference()
{
PropertyName = "Columns",
SourceValue = string.Join(", ", fromIndex.IndexColumns.OrderBy(ic => ic.Ordinal).Select(ic => ic.ColumnName).ToArray()),
TargetValue = string.Join(", ", toIndex.IndexColumns.OrderBy(ic => ic.Ordinal).Select(ic => ic.ColumnName).ToArray())
}
}
;
else if (toIndex.IndexColumns.Any(t => _alteredColumns.Contains(t.ColumnName)))
{
drop = true;
}
if (diff != null)
{
var item = new SynchronizationItem(toIndex);
items.Add(item);
item.Differences.Add(diff);
item.AddScript(1, new IndexSynchronization(toIndex).GetRawDropText());
item.AddScript(7, new IndexSynchronization(fromIndex).GetCreateScript(targetTable != null).ToString());
}
if (drop)
{
items.AddRange(new IndexSynchronization(toIndex).GetDropItems());
}
else
{
skips.Add(toIndex.IndexName);
}
}
}
var target = targetTable == null ? databaseObject : targetTable;
foreach (var fromIndex in databaseObject.Indexes)
{
if (skips.Contains(fromIndex.IndexName))
{
continue;
}
var item = new SynchronizationItem(fromIndex);
item.Differences.Add(new Difference()
{
PropertyName = Difference.CREATE
});
item.AddScript(7, new IndexSynchronization(fromIndex).GetCreateScript(targetTable != null).ToString());
items.Add(item);
}
return(items);
}
public override List <SynchronizationItem> GetSynchronizationItems(DatabaseObjectBase target)
{
if (target == null)
{
return(base.GetSynchronizationItems(target));
}
if (GetRawCreateText().ToLower() == new TriggerSynchronization(target as Trigger).GetRawCreateText().ToLower())
{
return(new List <SynchronizationItem>());
}
var targetTrigger = target as Trigger;
if (targetTrigger.Table.TableName != databaseObject.Table.TableName)
{
return(base.GetSynchronizationItems(target));
}
var items = new List <SynchronizationItem>();
var item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = target == null ? Difference.CREATE : Difference.ALTER
});
var createAlter = databaseObject.Definition.Trim();
if (target != null)
{
var targetDef = targetTrigger.Definition.Trim();
// it was only disabled
if (targetDef.ToLower() == createAlter.ToLower())
{
createAlter = string.Empty;
}
else
{
createAlter = Regex.Replace(createAlter, "CREATE TRIGGER", "ALTER TRIGGER", RegexOptions.IgnoreCase);
}
}
if (!string.IsNullOrEmpty(createAlter))
{
item.AddScript(10 * getIndex(), createAlter);
items.Add(item);
}
if (databaseObject.Disabled)
{
item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = "Disabled", SourceValue = "false", TargetValue = "true"
});
item.AddScript(11 * getIndex(), string.Format("DISABLE TRIGGER [{0}] ON [{1}]", databaseObject.TriggerName, databaseObject.Table.TableName));
items.Add(item);
}
else if (target != null && databaseObject.Disabled != (target as Trigger).Disabled)
{
item = new SynchronizationItem(databaseObject);
item.Differences.Add(new Difference()
{
PropertyName = "Disabled",
SourceValue = databaseObject.Disabled.ToString(),
TargetValue = (target as Trigger).Disabled.ToString()
});
item.AddScript(11 * getIndex(), string.Format("{2} TRIGGER [{0}] ON [{1}]", databaseObject.TriggerName, databaseObject.Table.TableName, databaseObject.Disabled ? "DISABLE" : "ENABLE"));
items.Add(item);
}
return(items);
}
private List <SynchronizationItem> getKeyConstraintUpdateItems(Table targetTable)
{
var items = new List <SynchronizationItem>();
var skips = new List <string>();
if (targetTable != null)
{
foreach (var toConstraint in targetTable.KeyConstraints)
{
bool drop = false;
Difference diff = null;
var fromConstraint = databaseObject.KeyConstraints.FirstOrDefault(c => c.ConstraintName == toConstraint.ConstraintName);
if (fromConstraint == null)
{
drop = true;
}
else if (fromConstraint.Columns.Any(c => !toConstraint.Columns.Any(t => t.ColumnName == c.ColumnName &&
t.Ordinal == c.Ordinal && t.Descending == c.Descending)) ||
toConstraint.Columns.Any(c => !fromConstraint.Columns.Any(t => t.ColumnName == c.ColumnName &&
t.Ordinal == c.Ordinal && t.Descending == c.Descending)))
{
diff = new Difference()
{
PropertyName = "Columns",
SourceValue = string.Join(", ", fromConstraint.Columns.OrderBy(ic => ic.Ordinal).Select(ic => ic.ColumnName + " (" + (ic.Descending ? "DESC" : "ASC") + ")").ToArray()),
TargetValue = string.Join(", ", toConstraint.Columns.OrderBy(ic => ic.Ordinal).Select(ic => ic.ColumnName + " (" + (ic.Descending ? "DESC" : "ASC") + ")").ToArray())
}
}
;
else if (toConstraint.Columns.Any(t => _alteredColumns.Contains(t.ColumnName)))
{
drop = true;
}
if (diff != null && toConstraint.IsPrimaryKey)
{
var item = new SynchronizationItem(toConstraint);
items.Add(item);
item.Differences.Add(diff);
foreach (var constraintItem in new KeyConstraintSynchronization(fromConstraint).GetAlterItems(toConstraint))
{
foreach (var script in constraintItem.Scripts)
{
item.AddScript(script.Key, script.Value.ToString());
}
}
//item.AddScript(1, toConstraint.GetRawDropText());
//item.AddScript(7, fromConstraint.GetRawCreateText());
}
if (drop)
{
items.AddRange(new KeyConstraintSynchronization(toConstraint).GetDropItems());
}
else
{
skips.Add(toConstraint.ConstraintName);
}
}
}
var target = targetTable == null ? databaseObject : targetTable;
foreach (var fromConstraint in databaseObject.KeyConstraints)
{
if (skips.Contains(fromConstraint.ConstraintName))
{
continue;
}
var item = new SynchronizationItem(fromConstraint);
item.Differences.Add(new Difference()
{
PropertyName = Difference.CREATE
});
item.AddScript(7, new KeyConstraintSynchronization(fromConstraint).GetRawCreateText());
items.Add(item);
}
return(items);
}
