public DropWorkspace(DatabaseObjectBase dbObject)
: base(dbObject)
{
var sync = DatabaseObjectSynchronizationBase.GetSynchronization(dbObject);
SynchronizationItems.AddRange(sync.GetDropItems());
}
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> 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> 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> 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 <SynchronizationItem> GetAlterItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
if (condensed)
{
return(new List <SynchronizationItem>());
}
var items = new List <SynchronizationItem>();
var propDifferences = GetPropertyDifferences(target, ignoreCase);
if (propDifferences.Any())
{
var createAlter = DatabaseObject.Definition;
if (DatabaseObject.Type == PaJaMa.Database.Library.DatabaseObjects.RoutineSynonym.RoutineSynonymType.Synonym)
{
createAlter = createAlter.Insert(0, new RoutineSynonymSynchronization(TargetDatabase, target as RoutineSynonym).GetRawDropText() + "\r\n");
}
else
{
createAlter = Regex.Replace(createAlter, "CREATE PROCEDURE", "ALTER PROCEDURE", RegexOptions.IgnoreCase);
createAlter = Regex.Replace(createAlter, "CREATE FUNCTION", "ALTER FUNCTION", RegexOptions.IgnoreCase);
createAlter = Regex.Replace(createAlter, "CREATE VIEW", "ALTER VIEW", RegexOptions.IgnoreCase);
}
var diff = getDifference(DifferenceType.Alter, DatabaseObject, target);
if (diff != null)
{
items.AddRange(getStandardItems(createAlter, difference: diff));
}
}
return(items);
}
public override List <SynchronizationItem> GetAlterItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
if (condensed)
{
return(new List <SynchronizationItem>());
}
return(new List <SynchronizationItem>());
}
public override List <SynchronizationItem> GetDropItems(DatabaseObjectBase sourceParent)
{
if (DatabaseObject.LoginType == LoginType.SQLLogin)
{
return(getStandardDropItems(string.Format("DROP LOGIN [{0}]", DatabaseObject.ObjectName), sourceParent));
}
return(getStandardDropItems(string.Format("DROP USER [{0}]", DatabaseObject.ObjectName), sourceParent));
}
public override List <SynchronizationItem> GetDropItems(DatabaseObjectBase sourceParent)
{
var script = TargetDatabase.DataSource.GetForeignKeyDropScript(DatabaseObject);
return(getStandardDropItems(string.Format(script,
DatabaseObject.ChildTable.GetObjectNameWithSchema(TargetDatabase.DataSource), DatabaseObject.GetQueryObjectName(TargetDatabase.DataSource)),
sourceParent));
}
protected override Difference getDifference(DifferenceType differenceType, DatabaseObjectBase fromObject, DatabaseObjectBase toObject = null, string propertyName = null, string sourceValue = null, string targetValue = null)
{
if (propertyName != null && propertyName == "ForeignKeyName" && differenceType == DifferenceType.Alter && !fromObject.Database.DataSource.NamedConstraints)
{
return(null);
}
return(base.getDifference(differenceType, fromObject, toObject, propertyName, sourceValue, targetValue));
}
public override List <SynchronizationItem> GetDropItems(DatabaseObjectBase sourceParent)
{
var dbName = DatabaseObject.Database.DataSource.GetConvertedObjectName(DatabaseObject.Database.DatabaseName);
return(getStandardDropItems(string.Format("DROP TABLE {1}{0}",
DatabaseObject.GetObjectNameWithSchema(TargetDatabase.DataSource),
string.IsNullOrEmpty(dbName) ? string.Empty : dbName + "."
), sourceParent));
}
public WorkspaceWithSourceBase(DatabaseObjectBase sourceObject, DatabaseObjects.Database targetDatabase, DatabaseObjectBase targetObject,
bool ignoreCase, bool forData, bool condensed) : base(targetDatabase, targetObject)
{
SourceObject = sourceObject;
if (!forData)
{
populateDifferences(ignoreCase, condensed);
}
}
public virtual List <SynchronizationItem> GetSynchronizationItems(DatabaseObjectBase target, bool ignoreCase, bool condensed)
{
if (target == null)
{
return(GetCreateItems());
}
return(GetAlterItems(target, ignoreCase, condensed));
}
protected List <SynchronizationItem> getStandardDropItems(string script, DatabaseObjectBase sourceParent, int level = 0)
{
if (sourceParent != null && DatabaseObject.Database.DataSource.GetType().FullName != sourceParent.Database.DataSource.GetType().FullName&&
sourceParent.Database.DataSource.IgnoreDrop(sourceParent, DatabaseObject))
{
return(new List <SynchronizationItem>());
}
return(getStandardItems(script, level, getDifference(DifferenceType.Drop, DatabaseObject)));
}
public virtual List <SynchronizationItem> GetSynchronizationItems(DatabaseObjectBase target)
{
if (target == null)
{
return(GetCreateItems());
}
return(GetAlterItems(target));
}
public override List <SynchronizationItem> GetDropItems(DatabaseObjectBase sourceParent)
{
StringBuilder sb = new StringBuilder();
foreach (var princ in DatabaseObject.PermissionPrincipals)
{
sb.AppendLine(princ.GetCreateRemoveScript(false));
}
return(getStandardDropItems(sb.ToString(), sourceParent));
}
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);
}
internal override bool IgnoreDrop(DatabaseObjectBase sourceParent, DatabaseObjectBase obj)
{
if (obj is DefaultConstraint && sourceParent is Table)
{
if ((sourceParent as Table).Columns.Any(c => c.IsIdentity && c.ColumnName == (obj as DefaultConstraint).Column.ColumnName))
{
return(true);
}
}
return(base.IgnoreDrop(sourceParent, obj));
}
public override List <SynchronizationItem> GetAlterItems(DatabaseObjectBase target)
{
var items = new List <SynchronizationItem>();
var diffs = GetPropertyDifferences(target);
if (diffs.Any())
{
var createAlter = databaseObject.Definition;
createAlter = Regex.Replace(createAlter, "CREATE VIEW", "ALTER VIEW", RegexOptions.IgnoreCase);
items.AddRange(getStandardItems(createAlter, propertyName: Difference.ALTER));
}
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 string GetRenameScript(DatabaseObjectBase databaseObject, string targetName)
{
if (databaseObject is Table)
{
return($"exec sp_rename '{databaseObject.Schema.SchemaName}.{databaseObject.ObjectName}', '{targetName}'");
}
else if (databaseObject is IObjectWithParent)
{
var objWithTable = databaseObject as IObjectWithParent;
return($"exec sp_rename '{objWithTable.Parent.Schema.SchemaName}.{objWithTable.Parent.ObjectName}.{databaseObject.ObjectName}', '{targetName}'");
}
return($"exec sp_rename '{databaseObject.ObjectName}', '{targetName}'");
}
private void recursivelyPopulateMissingDependencies(DatabaseObjectBase parent, List <WorkspaceBase> selectedWorkspaces, Dictionary <DatabaseObjectBase, List <DatabaseObjectBase> > missing,
List <DatabaseObjectBase> checkedObjects, bool isForDrop)
{
if (checkedObjects.Contains(parent))
{
return;
}
checkedObjects.Add(parent);
var toObjects = ToDataSource.CurrentDatabase.GetDatabaseObjects(false).ConvertAll(s => (DatabaseObjectBase)s);
toObjects.AddRange(from s in ToDataSource.CurrentDatabase.Schemas
from t in s.Tables
select t);
foreach (var ws in selectedWorkspaces)
{
var sync = DatabaseObjectSynchronizationBase.GetSynchronization(parent.Database, parent);
var missingDendencies = sync.GetMissingDependencies(toObjects, ws.SynchronizationItems.Where(si => !si.Omit).ToList(), isForDrop, IgnoreCase);
foreach (var child in missingDendencies)
{
if (checkedObjects.Contains(child))
{
continue;
}
if (child is ForeignKey fk && ws is TableWorkspace tw &&
(tw.RemoveAddKeys || selectedWorkspaces.Any(sw => sw is TableWorkspace tw2 && tw2.Select && tw2.SourceTable == fk.ParentTable)))
{
continue;
}
checkedObjects.Add(child);
if (!missing.ContainsKey(parent))
{
missing.Add(parent, new List <DatabaseObjectBase>());
}
if (!missing[parent].Contains(child))
{
missing[parent].Add(child);
}
recursivelyPopulateMissingDependencies(child, selectedWorkspaces, missing, checkedObjects, isForDrop);
}
}
}
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);
}
private Difference getColumnDifference(DatabaseObjectBase target)
{
var targetFk = target as ForeignKey;
if (targetFk.Columns.Any(f => !DatabaseObject.Columns.Any(c => c.ParentColumn.ColumnName == f.ParentColumn.ColumnName &&
c.ChildColumn.ColumnName == f.ChildColumn.ColumnName)) ||
DatabaseObject.Columns.Any(f => !targetFk.Columns.Any(c => c.ParentColumn.ColumnName == f.ParentColumn.ColumnName &&
c.ChildColumn.ColumnName == f.ChildColumn.ColumnName)))
{
return(getDifference(DifferenceType.Alter, DatabaseObject, target, "Columns",
string.Join("\r\n", DatabaseObject.Columns.Select(c => c.ParentColumn.ColumnName + " - " + c.ChildColumn.ColumnName).ToArray()),
string.Join("\r\n", targetFk.Columns.Select(c => c.ParentColumn.ColumnName + " - " + c.ChildColumn.ColumnName).ToArray())));
}
return(null);
}
private static bool objectsAreEqual(DatabaseObjectBase obj1, DatabaseObjectBase obj2)
{
if (obj1.ObjectType != obj2.ObjectType)
{
return(false);
}
if (obj1.ObjectType == typeof(Schema).Name)
{
if (obj1.ObjectName == obj1.Database.DataSource.DefaultSchemaName && obj2.ObjectName == obj2.Database.DataSource.DefaultSchemaName)
{
return(true);
}
}
return(obj1.ToString() == obj2.ToString());
}
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);
}
private void recursivelyPopulateMissingDependencies(DatabaseObjectBase parent, List <WorkspaceBase> selectedWorkspaces, Dictionary <DatabaseObjectBase, List <DatabaseObjectBase> > missing,
List <DatabaseObjectBase> checkedObjects, bool isForDrop)
{
if (checkedObjects.Contains(parent))
{
return;
}
checkedObjects.Add(parent);
var toObjects = ToDatabase.GetDatabaseObjects(false).ConvertAll(s => (DatabaseObjectBase)s);
toObjects.AddRange(from s in ToDatabase.Schemas
from t in s.Tables
select t);
var selectedItems = from ws in selectedWorkspaces
from si in ws.SynchronizationItems
where !si.Omit
select si;
var sync = DatabaseObjectSynchronizationBase.GetSynchronization(parent);
var missingDendencies = sync.GetMissingDependencies(toObjects, selectedItems.ToList(), isForDrop);
foreach (var child in missingDendencies)
{
if (checkedObjects.Contains(child))
{
continue;
}
checkedObjects.Add(child);
if (!missing.ContainsKey(parent))
{
missing.Add(parent, new List <DatabaseObjectBase>());
}
if (!missing[parent].Contains(child))
{
missing[parent].Add(child);
}
recursivelyPopulateMissingDependencies(child, selectedWorkspaces, missing, checkedObjects, isForDrop);
}
}
private void populateChildren <TDatabaseObject>(DbConnection connection, DatabaseObjectBase parent, List <TDatabaseObject> arrayToClear, string sql, string additionalPreWhere, string additionalPostWhere)
where TDatabaseObject : DatabaseObjectBase
{
var conn = connection ?? OpenConnection(parent.Database.DatabaseName);
// TODO: assumes all schemas are from same db
using (var cmd = conn.CreateCommand())
{
arrayToClear.Clear();
populateObjects <TDatabaseObject>(parent.Database, cmd, string.Format(sql, parent.Database.DatabaseName), parent.Schema.SchemaName, true, additionalPreWhere, additionalPostWhere, null);
}
if (connection == null)
{
conn.Close();
conn.Dispose();
}
}
public override List <SynchronizationItem> GetDropItems(DatabaseObjectBase sourceParent)
{
if (TargetDatabase.DataSource.BypassKeyConstraints || DatabaseObject.Database.DataSource.BypassKeyConstraints)
{
if (DatabaseObject.Table.KeyConstraints.Any(fk => fk.ObjectName == DatabaseObject.ObjectName))
{
return(new List <SynchronizationItem>());
}
}
if (!TargetDatabase.DataSource.NamedConstraints)
{
return(new List <SynchronizationItem>());
}
return(getStandardDropItems(string.Format("ALTER TABLE {0} DROP CONSTRAINT {1};", DatabaseObject.Table.GetObjectNameWithSchema(TargetDatabase.DataSource),
DatabaseObject.GetQueryObjectName(TargetDatabase.DataSource)), sourceParent));
}
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>());
}
