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