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