protected void Patch()
{
var builder = new StringBuilder();
var opening = $"USE {targetDb};\n\n";
builder.Append(opening);
// drop any index not in the source or different
var indexesToDrop = targetIndexes.Values.Where(i =>
{
var sourceIndex = sourceIndexes.Values.Where(x => x.name == i.name).FirstOrDefault();
if (sourceIndex == null)
{
return(true);
}
else
{
return(sourceIndex.qualified_table_name != i.qualified_table_name ||
sourceIndex.columns != i.columns ||
sourceIndex.include != i.include);
}
});
foreach (var index in indexesToDrop)
{
builder.Append(Query.DropIndex(index));
builder.Append(Query.BatchSeperator);
}
var constraintsToDrop = targetConstraints.Values.Where(targetConstraint =>
{
// ignore all primary keys until you can drop the table and its dependencies
if (targetConstraint.type == Enumerations.GetDescription(SQLTypes.PrimaryKey))
{
return(false);
}
var sourceConstraint = sourceObjects.ContainsKey(targetConstraint.qualified_name) ? sourceConstraints[sourceObjects[targetConstraint.qualified_name]] : null;
if (sourceConstraint == null)
{
return(true);
}
else
{
return(sourceConstraint.columns != targetConstraint.columns);
}
});
foreach (var constraint in constraintsToDrop)
{
builder.Append(Query.DropConstraint(constraint));
builder.Append(Query.BatchSeperator);
}
// drop foreign keys not in source or different
var keysToDrop = targetKeys.Values.Where(k => {
if (!sourceObjects.ContainsKey(k.qualified_name))
{
return(true);
}
else
{
var fkey = sourceKeys[sourceObjects[k.qualified_name]];
return
(fkey.qualified_parent_table != k.qualified_parent_table ||
fkey.qualified_referenced_table != k.qualified_referenced_table ||
fkey.referenced_column != k.referenced_column ||
fkey.parent_column != k.parent_column);
}
});
foreach (var key in keysToDrop)
{
builder.Append(Query.DropForeignKey(key));
builder.Append(Query.BatchSeperator);
}
var excludedTables = targetTables.Values
.Select(k => k.qualified_name)
.Except(sourceTables.Values.Select(k => k.qualified_name));
var tablesToDrop = targetTables.Values.Where(k => excludedTables.Contains(k.qualified_name));
foreach (var table in tablesToDrop)
{
builder.Append(Query.DropTable(table));
builder.Append(Query.BatchSeperator);
}
var schemasToDrop = targetSchemas.Keys.Except(sourceSchemas.Keys);
foreach (var schema in schemasToDrop)
{
builder.Append(Query.DropSchema(schema));
builder.Append(Query.BatchSeperator);
}
PatchTemporalTables(builder);
PatchColumns(builder);
if (builder.ToString() == opening)
{
builder.Append("-- Nothing to patch. --");
}
File.WriteAllText($"{path}\\patch.sql", builder.ToString(), Encoding.UTF8);
}
// drop/add/alter any columns
private void PatchColumns(StringBuilder builder)
{
var source = sourceObjects.Keys.ToHashSet();
var target = targetObjects.Keys.ToHashSet();
var intersect = source.Intersect(target).Where(name => sourceTypes[sourceObjects[name]] == Enumerations.GetDescription(SQLTypes.UserTable));
foreach (var tableName in intersect)
{
var sourceId = sourceObjects[tableName];
var targetId = targetObjects[tableName];
var sourceTable = sourceTables[sourceId];
var sourceCols = sourceColumns[sourceId].Where(c => c.generated_always_type == "0");
var targetCols = targetColumns[targetId].Where(c => c.generated_always_type == "0" || string.IsNullOrEmpty(sourceTable.history_table));
var sourceColDictionary = sourceCols.ToDictionary(c => c.name);
var targetColDictionary = targetCols.ToDictionary(c => c.name);
var toDrop = targetCols.Where(c => !sourceColDictionary.ContainsKey(c.name)).ToList();
var toAdd = sourceCols.Where(c => !targetColDictionary.ContainsKey(c.name)).ToList();
var toAlter = sourceCols.Where(source =>
{
if (!targetColDictionary.ContainsKey(source.name))
{
return(false);
}
var target = targetColDictionary[source.name];
return(source.type_definition != target.type_definition);
}).ToList();
if (toDrop.Any() || toAdd.Any() || toAlter.Any())
{
builder.Append($"-- {tableName} --\n");
var targetObjConstraints = targetConstraints.Values.Where(c => c.parent_object_id == targetId);
var primaryKey = targetObjConstraints.Where(c => c.type == Enumerations.GetDescription(SQLTypes.PrimaryKey)).FirstOrDefault();
toDrop.ForEach(column =>
{
builder.Append(Query.DropColumn(column));
builder.Append(Query.BatchSeperator);
});
toAdd.ForEach(column =>
{
if (column.isNullable)
{
builder.Append(Query.AddColumn(column));
builder.Append(Query.BatchSeperator);
}
else
{
var nullable = SysColumn.DeepClone(column);
nullable.is_nullable = "true";
builder.Append(Query.AddColumn(nullable));
builder.Append(Query.BatchSeperator);
builder.Append(Query.UpdateIfNull(column));
builder.Append(Query.BatchSeperator);
toAlter.Add(column);
}
});
toAlter.ForEach(column =>
{
if (DependsOn(primaryKey, column))
{
builder.Append(Query.DropPrimaryKey(primaryKey));
builder.Append(Query.BatchSeperator);
}
builder.Append(Query.AlterColumn(column));
builder.Append(Query.BatchSeperator);
});
}
}
}
protected void Alter()
{
var builder = new StringBuilder();
builder.Append($"USE {targetDb};\n\n");
// constraints
var allConstraints = sourceConstraints.Values;
var primaryKeys = allConstraints.Where(c => c.type == Enumerations.GetDescription(SQLTypes.PrimaryKey));
var defaultConstraints = allConstraints.Where(c => c.type == Enumerations.GetDescription(SQLTypes.DefaultConstraint));
var uniqueConstraints = allConstraints.Where(c => c.type == Enumerations.GetDescription(SQLTypes.UniqueConstraint));
var checkConstraints = allConstraints.Where(c => c.type == Enumerations.GetDescription(SQLTypes.CheckConstraint));
foreach (var key in primaryKeys)
{
builder.Append(Query.AddPrimaryKey(key));
builder.Append(Query.BatchSeperator);
}
foreach (var constraint in defaultConstraints)
{
builder.Append(Query.AddDefaultConstraint(constraint));
builder.Append(Query.BatchSeperator);
}
foreach (var constraint in uniqueConstraints)
{
builder.Append(Query.AddUniqueConstraint(constraint));
builder.Append(Query.BatchSeperator);
}
foreach (var constraint in checkConstraints)
{
builder.Append(Query.AddCheckConstraint(constraint));
builder.Append(Query.BatchSeperator);
}
// foreign keys
foreach (var key in sourceKeys.Values)
{
builder.Append(Query.AddForeignKey(key));
builder.Append(Query.BatchSeperator);
}
// views
foreach (var view in sourceViews.Values)
{
//if (!ObjectChanged(view, targetViews)) continue;
builder.Append(Query.AlterView(view));
}
// funcs
foreach (var func in sourceFuncs.Values)
{
//if (!ObjectChanged(func, targetFuncs)) continue;
builder.Append(Query.AlterFunc(func));
}
// procs
foreach (var proc in sourceProcs.Values)
{
//if (!ObjectChanged(proc, targetProcs)) continue;
builder.Append(Query.AlterProc(proc));
}
File.WriteAllText($"{path}\\alter.sql", builder.ToString(), Encoding.UTF8);
}