public override List <SynchronizationItem> GetCreateItems()
{
var items = new List <SynchronizationItem>();
var diff = getDifference(DifferenceType.Create, DatabaseObject);
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
items.Add(item);
item.Differences.Add(diff);
// TODO: cache
item.AddScript(0, string.Format(@"CREATE SEQUENCE IF NOT EXISTS {0}
INCREMENT {1}
MINVALUE {2}
MAXVALUE {3}
START {4}
CACHE 1
{5} CYCLE",
DatabaseObject.GetObjectNameWithSchema(TargetDatabase.DataSource),
DatabaseObject.Increment,
DatabaseObject.MinValue,
DatabaseObject.MaxValue,
DatabaseObject.Start,
DatabaseObject.Cycle
));
}
return(items);
}
public override List <SynchronizationItem> GetDropItems(DatabaseObjectBase sourceParent)
{
if (DatabaseObject.PrincipalType == PrincipalType.DatabaseRole)
{
var items = new List <SynchronizationItem>();
SynchronizationItem item;
StringBuilder sb = new StringBuilder();
foreach (var child in DatabaseObject.ChildMembers)
{
var diff = getDifference(DifferenceType.Drop, child, null, "Drop - " + child.ObjectName);
if (diff == null)
{
continue;
}
item = new SynchronizationItem(child);
item.Differences.Add(diff);
item.AddScript(1, string.Format("ALTER ROLE [{0}] DROP MEMBER [{1}]", DatabaseObject.ObjectName, child.ObjectName));
items.Add(item);
}
var d2 = getDifference(DifferenceType.Drop, DatabaseObject, null, "Drop - " + DatabaseObject.ObjectName);
if (d2 != null)
{
item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(d2);
item.AddScript(2, string.Format("DROP ROLE [{0}]", DatabaseObject.ObjectName));
items.Add(item);
}
return(items);
}
return(getStandardDropItems(string.Format("DROP USER [{0}]", DatabaseObject.ObjectName), sourceParent));
}
public override List <SynchronizationItem> GetAlterItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
if (condensed)
{
return(new List <SynchronizationItem>());
}
var differences = base.GetPropertyDifferences(target, ignoreCase);
if (DatabaseObject.IgnoreSchema)
{
var schemDiff = differences.FirstOrDefault(d => d.PropertyName == "SchemaName");
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, bool ignoreCase, bool condensed)
{
if (condensed)
{
return(new List <SynchronizationItem>());
}
var items = base.GetSynchronizationItems(target, ignoreCase, condensed);
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 diff = getDifference(DifferenceType.Alter, DatabaseObject, ext, DatabaseObject.ObjectName,
ext.PropValue == null ? string.Empty : DatabaseObject.PropValue.ToString(),
DatabaseObject.PropValue == null ? string.Empty : ext.PropValue.ToString());
if (diff != null)
{
var item = items.FirstOrDefault();
if (item == null)
{
item = new SynchronizationItem(ext);
items.Add(item);
}
item.Differences.Add(diff);
item.AddScript(1, new ExtendedPropertySynchronization(TargetDatabase, DatabaseObject).GetRawDropText());
item.AddScript(7, getAddScript());
}
}
return(items);
}
public override List <SynchronizationItem> GetCreateItems()
{
var items = new List <SynchronizationItem>();
var diff = getDifference(DifferenceType.Create, DatabaseObject);
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
items.Add(item);
item.Differences.Add(diff);
item.AddScript(2, getCreateScript());
}
foreach (var fk in DatabaseObject.ForeignKeys)
{
items.AddRange(new ForeignKeySynchronization(TargetDatabase, fk).GetCreateItems());
}
items.AddRange(getIndexCreateUpdateItems(null, false));
items.AddRange(getDefaultConstraintCreateUpdateItems(null, false));
items.AddRange(getTriggerUpdateItems(null, false));
foreach (var column in DatabaseObject.Columns)
{
items.AddRange(ExtendedPropertySynchronization.GetExtendedProperties(TargetDatabase, column, null));
}
return(items);
}
public override List <SynchronizationItem> GetSynchronizationItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
if (condensed)
{
return(new List <SynchronizationItem>());
}
if (target == null)
{
return(base.GetSynchronizationItems(target, ignoreCase, false));
}
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 diff = getDifference(DifferenceType.Create, DatabaseObject);
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(diff);
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 diff = getDifference(DifferenceType.Drop, DatabaseObject);
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(diff);
item.AddScript(2, pp.GetCreateRemoveScript(false));
items.Add(item);
}
}
}
return(items);
}
public override List <SynchronizationItem> GetCreateItems()
{
var items = new List <SynchronizationItem>();
var diff = getDifference(DifferenceType.Create, DatabaseObject);
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
items.Add(item);
item.Differences.Add(diff);
item.AddScript(0, string.Format("CREATE EXTENSION IF NOT EXISTS \"{0}\"", DatabaseObject.Name));
}
return(items);
}
public override List <SynchronizationItem> GetDropItems(DatabaseObjectBase sourceParent)
{
var items = new List <SynchronizationItem>();
var diff = getDifference(DifferenceType.Create, DatabaseObject);
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
items.Add(item);
item.Differences.Add(diff);
item.AddScript(0, string.Format("DROP EXTENSION \"{0}\"", DatabaseObject.Name));
}
return(items);
}
public override List <SynchronizationItem> GetDropItems(DatabaseObjectBase sourceParent)
{
var items = new List <SynchronizationItem>();
var diff = getDifference(DifferenceType.Drop, DatabaseObject);
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
items.Add(item);
item.Differences.Add(diff);
item.AddScript(0, string.Format("DROP SEQUENCE {0}.\"{1}\"", DatabaseObject.Schema.SchemaName,
DatabaseObject.SequenceName));
}
return(items);
}
public override List <SynchronizationItem> GetCreateItems()
{
var items = getStandardItems(getLoginScript(true));
if (DatabaseObject.IsDisabled)
{
var diff = getDifference(DifferenceType.Alter, DatabaseObject, null, "Disabled");
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(diff);
item.AddScript(7, string.Format("\r\nALTER LOGIN [{0}] DISABLE", DatabaseObject.LoginName));
items.Add(item);
}
}
return(items);
}
public override List <SynchronizationItem> GetCreateItems()
{
var items = getStandardItems(DatabaseObject.Definition.Trim(), level: 10 * getIndex());
if (DatabaseObject.Disabled)
{
var diff = getDifference(DifferenceType.Alter, DatabaseObject, null, "Disabled");
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(diff);
item.AddScript(11 * getIndex(), string.Format("DISABLE TRIGGER [{0}] ON [{1}]", DatabaseObject.TriggerName, DatabaseObject.Table.TableName));
items.Add(item);
}
}
return(items);
}
protected List <SynchronizationItem> getStandardItems(string script, int level = 4, Difference difference = null)
{
if (difference == null)
{
difference = getDifference(DifferenceType.Create, DatabaseObject);
}
if (difference == null)
{
return(new List <SynchronizationItem>());
}
var item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(difference);
item.AddScript(level, script);
return(new List <SynchronizationItem>()
{
item
});
}
public override List <SynchronizationItem> GetAlterItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
if (target != null && condensed)
{
return(new List <SynchronizationItem>());
}
var items = base.GetAlterItems(target, ignoreCase, condensed);
if (target != null)
{
var targetKey = target as KeyConstraint;
var childKeys = from t in DatabaseObject.Parent.Schema.Tables
from fk in t.ForeignKeys
where fk.ParentTable.TableName == targetKey.Parent.ObjectName
select fk;
foreach (var childKey in childKeys)
{
var childSync = new ForeignKeySynchronization(TargetDatabase, childKey);
var item = new SynchronizationItem(childKey);
foreach (var dropItem in childSync.GetDropItems(childKey))
{
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 virtual List <SynchronizationItem> GetAlterItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
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.DifferenceType == DifferenceType.Create)
{
dropItem.Differences.Remove(diff);
}
dropItem.Differences.AddRange(GetPropertyDifferences(target, ignoreCase));
dropItem.AddScript(0, GetRawDropText());
return(items);
}
public override List <SynchronizationItem> GetSynchronizationItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
if (target == null)
{
return(base.GetSynchronizationItems(target, ignoreCase, condensed));
}
var targetCredential = target as Credential;
if (DatabaseObject.CredentialIdentity != targetCredential.CredentialIdentity)
{
var item = new SynchronizationItem(DatabaseObject);
item.Differences.AddRange(GetPropertyDifferences(target, ignoreCase));
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> GetSynchronizationItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
if (target == null)
{
return(base.GetSynchronizationItems(target, ignoreCase, false));
}
var targetSchema = target as Schema;
if (DatabaseObject.SchemaOwner != targetSchema.SchemaOwner)
{
var item = new SynchronizationItem(DatabaseObject);
item.Differences.AddRange(GetPropertyDifferences(target, ignoreCase));
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> GetAlterItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
if (condensed)
{
return(new List <SynchronizationItem>());
}
var diff = GetPropertyDifferences(target, ignoreCase);
if (diff.Count == 1 && diff[0].PropertyName == "IsDisabled")
{
var d = getDifference(DifferenceType.Alter, DatabaseObject, target, "Disabled");
if (d != null)
{
var item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(d);
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 override List <SynchronizationItem> GetAlterItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
var diffs = GetPropertyDifferences(target, ignoreCase);
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(100, GetRawCreateText());
return(new List <SynchronizationItem>()
{
syncItem
});
}
return(new List <SynchronizationItem>());
}
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);
}
public override List <SynchronizationItem> GetSynchronizationItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
if (condensed)
{
return(new List <SynchronizationItem>());
}
var items = new List <SynchronizationItem>();
if (DatabaseObject.PrincipalType != PrincipalType.DatabaseRole)
{
if (target == null)
{
return(base.GetSynchronizationItems(target, ignoreCase, false));
}
var diffs = GetPropertyDifferences(target, ignoreCase);
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 diff = getDifference(DifferenceType.Create, DatabaseObject);
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(diff);
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 diff = getDifference(DifferenceType.Alter, DatabaseObject, dp, "Owner", DatabaseObject.Owner.PrincipalName, dp.Owner.PrincipalName);
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(diff);
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 diff = getDifference(DifferenceType.Alter, drop, null, "Member", "Drop", drop.ObjectName);
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(diff);
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 diff = getDifference(DifferenceType.Alter, create, target, "Member", "Create", create.ObjectName);
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(diff);
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);
}
//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, bool ignoreCase)
{
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 string.Compare(fk.ParentTable.ObjectName, DatabaseObject.ObjectName, ignoreCase) == 0 ||
string.Compare(fk.ChildTable.ObjectName, DatabaseObject.ObjectName, ignoreCase) == 0
select fk;
foreach (var fk in foreignKeys)
{
var dropItems = new ForeignKeySynchronization(TargetDatabase, fk).GetDropItems(DatabaseObject);
foreach (var di in dropItems)
{
foreach (var s in di.Scripts)
{
item.AddScript(4, s.Value.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.ColumnType.DataType != DataType.RowVersion).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(TargetDatabase, 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);
}
public override List <SynchronizationItem> GetSynchronizationItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
if (condensed)
{
return(new List <SynchronizationItem>());
}
if (target == null)
{
return(base.GetSynchronizationItems(target, ignoreCase, false));
}
if (GetRawCreateText().ToLower() == new TriggerSynchronization(TargetDatabase, 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, ignoreCase, false));
}
var items = new List <SynchronizationItem>();
var diff = getDifference(target == null ? DifferenceType.Create : DifferenceType.Alter, DatabaseObject, target);
if (diff != null)
{
var item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(diff);
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)
{
diff = getDifference(DifferenceType.Alter, DatabaseObject, target, "Disabled", "false", "true");
if (diff != null)
{
item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(diff);
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)
{
diff = getDifference(DifferenceType.Alter, DatabaseObject, target, "Disabled",
DatabaseObject.Disabled.ToString(), (target as Trigger).Disabled.ToString());
if (diff != null)
{
item = new SynchronizationItem(DatabaseObject);
item.Differences.Add(diff);
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, bool ignoreCase, bool condensed)
{
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;
KeyConstraint fromConstraint = null;
if (!DatabaseObject.Database.DataSource.NamedConstraints || !targetTable.Database.DataSource.NamedConstraints)
{
fromConstraint = DatabaseObject.KeyConstraints.FirstOrDefault(c =>
c.Columns.All(x => toConstraint.Columns.Any(y => y.ColumnName == x.ColumnName)) &&
toConstraint.Columns.All(x => c.Columns.Any(y => y.ColumnName == x.ColumnName)));
}
else
{
fromConstraint = DatabaseObject.KeyConstraints.FirstOrDefault(c => string.Compare(c.ConstraintName, toConstraint.ConstraintName, ignoreCase) == 0);
}
if (fromConstraint == null)
{
drop = true;
}
else if (DataSourcesAreDifferent && fromConstraint.Columns.Any(c => !toConstraint.Columns.Any(t => string.Compare(t.ColumnName, c.ColumnName, ignoreCase) == 0 &&
t.Ordinal == c.Ordinal && t.Descending == c.Descending)) ||
toConstraint.Columns.Any(c => !fromConstraint.Columns.Any(t => string.Compare(t.ColumnName, c.ColumnName, ignoreCase) == 0 &&
t.Ordinal == c.Ordinal && t.Descending == c.Descending)))
{
diff = getDifference(DifferenceType.Alter, fromConstraint, toConstraint, "Columns",
string.Join(", ", fromConstraint.Columns.OrderBy(ic => ic.Ordinal).Select(ic => ic.ColumnName + " (" + (ic.Descending ? "DESC" : "ASC") + ")").ToArray()),
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(TargetDatabase, fromConstraint).GetAlterItems(toConstraint, ignoreCase, condensed))
{
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(TargetDatabase, toConstraint).GetDropItems(DatabaseObject));
}
else
{
skips.Add(toConstraint.ConstraintName);
}
}
}
var target = targetTable == null ? DatabaseObject : targetTable;
foreach (var fromConstraint in DatabaseObject.KeyConstraints)
{
if (skips.Any(s => string.Compare(s, fromConstraint.ConstraintName, ignoreCase) == 0))
{
continue;
}
var diff = getDifference(DifferenceType.Create, fromConstraint);
if (diff != null)
{
var item = new SynchronizationItem(fromConstraint);
item.Differences.Add(diff);
item.AddScript(7, new KeyConstraintSynchronization(TargetDatabase, fromConstraint).GetRawCreateText());
items.Add(item);
}
}
return(items);
}
private List <SynchronizationItem> getIndexCreateUpdateItems(Table targetTable, bool ignoreCase)
{
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 => string.Compare(c.IndexName, toIndex.IndexName, ignoreCase) == 0);
if (fromIndex == null)
{
drop = true;
}
else if (DataSourcesAreDifferent && fromIndex.IndexColumns.Any(c => !toIndex.IndexColumns.Any(t => string.Compare(t.ColumnName, c.ColumnName, ignoreCase) == 0 &&
t.Ordinal == c.Ordinal && t.Descending == c.Descending)) ||
toIndex.IndexColumns.Any(c => !fromIndex.IndexColumns.Any(t => string.Compare(t.ColumnName, c.ColumnName, ignoreCase) == 0 &&
t.Ordinal == c.Ordinal && t.Descending == c.Descending)))
{
diff = getDifference(DifferenceType.Alter, fromIndex, toIndex, "Columns",
string.Join(", ", fromIndex.IndexColumns.OrderBy(ic => ic.Ordinal).Select(ic => ic.ColumnName).ToArray()),
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(TargetDatabase, toIndex).GetRawDropText());
item.AddScript(7, TargetDatabase.DataSource.GetIndexCreateScript(fromIndex));
}
if (drop)
{
items.AddRange(new IndexSynchronization(TargetDatabase, toIndex).GetDropItems(DatabaseObject));
}
else
{
skips.Add(toIndex.IndexName);
}
}
}
var target = targetTable == null ? DatabaseObject : targetTable;
foreach (var fromIndex in DatabaseObject.Indexes)
{
if (skips.Contains(fromIndex.IndexName))
{
continue;
}
var diff = getDifference(DifferenceType.Create, fromIndex);
if (diff != null)
{
var item = new SynchronizationItem(fromIndex);
item.Differences.Add(diff);
item.AddScript(7, TargetDatabase.DataSource.GetIndexCreateScript(fromIndex));
items.Add(item);
}
}
return(items);
}
