private IList <Tuple <DependencyEdge, string> > GetOrderedEdges(DependencyNode table, string levelPrefix = "") { int level = 1; List <Tuple <DependencyEdge, string> > result = new List <Tuple <DependencyEdge, string> >(); foreach (DependencyEdge edge in table.ChildEdges) { result.Add(new Tuple <DependencyEdge, string>(edge, $"{levelPrefix}{level}")); if (edge.ForeignKey.DeleteAction == ForeignKey.DeleteActions.Cascade) { result.AddRange(GetOrderedEdges(edge.Child, $"{levelPrefix}{level}.")); } level++; } return(result); }
public IList <Tuple <DependencyEdge, string> > CheckDeleteCascade(DependencyNode table, out bool OK, out string explanation) { IList <Tuple <DependencyEdge, string> > result = GetOrderedEdges(table); OK = true; explanation = ""; foreach (Tuple <DependencyEdge, string> edge in result) { if (edge.Item1.ForeignKey.DeleteAction == ForeignKey.DeleteActions.NoAction) { OK = false; explanation = $"foreign key {edge.Item1.ForeignKey} has on delete No action and blocks delete procedure"; break; } else if (explanation.Length == 0 && edge.Item1.ForeignKey.DeleteAction != ForeignKey.DeleteActions.Cascade) { explanation = $"foreign key {edge.Item1.ForeignKey} has on delete {ForeignKey.DeleteActionToString(edge.Item1.ForeignKey.DeleteAction)}"; } } return(result); }
private void ProccessDeleteSubhierarchy(DependencyNode node, DependencyEdge edge, IList <DependencyNode> orderedTables, IDictionary <DependencyNode, IList <DependencyEdge> > childEdges, IDictionary <DB.ForeignKey, DB.ForeignKey.DeleteActions> foreignKeyActions, ISet <DependencyNode> processing, ISet <DependencyNode> processed) { if (processing.Contains(node) && foreignKeyActions[edge.ForeignKey] == DB.ForeignKey.DeleteActions.Cascade) { throw new DeleteDependencyException($"In delete tree is a cycle of cascade delete (for example between {edge.Parent} and {edge.Child} - foreign key {edge.ForeignKey}), so it is not possible to execute deletion procedure."); } processing.Add(node); foreach (DependencyEdge childEdge in node.ChildEdges) { if (foreignKeyActions[childEdge.ForeignKey] == DB.ForeignKey.DeleteActions.NoAction) { throw new DeleteDependencyException($"Foreign key {childEdge.ForeignKey} has No action on delete and it blocks deletion procedure."); } else if (foreignKeyActions[childEdge.ForeignKey] == DB.ForeignKey.DeleteActions.Cascade) { ProccessDeleteSubhierarchy(childEdge.Child, childEdge, orderedTables, childEdges, foreignKeyActions, processing, processed); } childEdges[node].Add(childEdge); } processing.Remove(node); processed.Add(node); orderedTables.Add(node); }
public bool ArchiveTable(DatabaseObject tableToArchive, IDictionary <string, string> convertorTablenames, bool createTable, string whereConditions, bool useInsertIntoSelect, bool archiveAllEntries, StreamWriter streamToSave) { bool exportIsNotEmpty = false; ISet <DependencyEdge> cycleEdges; IList <DependencyNode> orderedTables = DependencyGraph.GetTopologicallySortedNodes(tableToArchive, true, out cycleEdges); if (createTable) { Server server = new Server(new ServerConnection(Database.Instance.Connection)); Microsoft.SqlServer.Management.Smo.Database database = server.Databases[Database.Instance.DatabaseName]; Transfer transfer = new Transfer(database) { CopyAllObjects = false, CopyAllUserDefinedDataTypes = true, CopyAllSchemas = true, CopyAllXmlSchemaCollections = true, Options = new ScriptingOptions { FullTextCatalogs = true, FullTextStopLists = true, FullTextIndexes = true, DriAll = true, ExtendedProperties = true, Indexes = true, NonClusteredIndexes = true, Triggers = true, IncludeIfNotExists = true } }; foreach (DependencyNode table in orderedTables) { transfer.ObjectList.Add(database.Tables[table.DbObject.Name, table.DbObject.Schema]); } StringBuilder archiveCreateTable = new StringBuilder(); foreach (string line in transfer.ScriptTransfer()) { archiveCreateTable.AppendLine(line); archiveCreateTable.AppendLine("GO"); } foreach (DependencyNode table in orderedTables) { archiveCreateTable.Replace(table.DbObject.NameWithSchemaBrackets, table.DbObject.NameWithSchema.Replace(table.DbObject.NameWithSchemaBrackets, $"[{convertorTablenames[table.DbObject.NameWithSchema.ToLower()].Replace(".", "].[")}]")); archiveCreateTable.Replace($"TABLE {table.DbObject.NameWithSchema}", $"TABLE {convertorTablenames[table.DbObject.NameWithSchema.ToLower()]}"); archiveCreateTable.Replace($"ON {table.DbObject.NameWithSchema}", $"ON {convertorTablenames[table.DbObject.NameWithSchema.ToLower()]}"); string oldTablename = table.DbObject.Name; string newTablename = convertorTablenames[table.DbObject.NameWithSchema.ToLower()]; newTablename = newTablename.Substring(Math.Min(newTablename.LastIndexOf(".") + 1, newTablename.Length - 1)).Replace("[", "").Replace("]", ""); archiveCreateTable.Replace($"_{oldTablename}_", $"_{newTablename}_"); archiveCreateTable.Replace($"name=N'{oldTablename}'", $"name=N'{newTablename}'"); } streamToSave.Write(archiveCreateTable.ToString()); exportIsNotEmpty = true; archiveCreateTable.Clear(); } IDictionary <DependencyNode, string> tableSubSelects = new Dictionary <DependencyNode, string>(orderedTables.Count); if (!archiveAllEntries) { foreach (DependencyNode table in orderedTables.Reverse()) { if (table.DbObject.Equals(tableToArchive)) { tableSubSelects.Add(table, $"FROM {table.DbObject.NameWithSchemaBrackets}{(whereConditions.Trim().Length > 0 ? $" WHERE {whereConditions}" : "")}"); } foreach (DependencyEdge edge in table.ParentEdges) { DependencyNode parent = edge.Parent; if (parent.DbObject.Equals(tableToArchive)) { continue; } if (orderedTables.Contains(parent)) { IList <string> parentColumnsConditions = new List <string>(); foreach (DependencyColumn column in edge.Columns) { parentColumnsConditions.Add($"{parent.DbObject.NameWithSchemaBrackets}.[{column.ParentColumn}] IN (SELECT DISTINCT {table.DbObject.NameWithSchemaBrackets}.[{column.ChildColumn}] {tableSubSelects[edge.Child]})"); } if (!tableSubSelects.ContainsKey(parent)) { tableSubSelects[parent] = $"FROM {parent.DbObject.NameWithSchemaBrackets} WHERE ({string.Join(" AND ", parentColumnsConditions)})"; } else { tableSubSelects[parent] = tableSubSelects[parent] + $" OR ({string.Join(" AND ", parentColumnsConditions)})"; } } } } } else { foreach (DependencyNode table in orderedTables) { if (table.DbObject.Equals(tableToArchive) && whereConditions.Trim().Length > 0) { tableSubSelects.Add(table, $"FROM {table.DbObject.NameWithSchemaBrackets} WHERE {whereConditions}"); } else { tableSubSelects.Add(table, $"FROM {table.DbObject.NameWithSchemaBrackets}"); } } } IDictionary <DependencyNode, bool> valuesInTables = new Dictionary <DependencyNode, bool>(orderedTables.Count); bool valuesGenerated = false; foreach (DependencyNode table in orderedTables) { valuesInTables[table] = (Database.Instance.ExecuteScalarSelect($"SELECT COUNT(*) {tableSubSelects[table]}") ?? 0) > 0; if (valuesInTables[table]) { valuesGenerated = true; } } StringBuilder noCheckConstraints = new StringBuilder(); StringBuilder witchCheckConstraints = new StringBuilder(); foreach (DependencyEdge edge in cycleEdges) { noCheckConstraints.AppendLine($"ALTER TABLE {edge.Child.DbObject.NameWithSchemaBrackets} NOCHECK CONSTRAINT {edge.Name};"); witchCheckConstraints.AppendLine($"ALTER TABLE {edge.Child.DbObject.NameWithSchemaBrackets} WITH CHECK CHECK CONSTRAINT {edge.Name};"); } if (valuesGenerated || useInsertIntoSelect) { streamToSave.Write(noCheckConstraints.ToString()); } foreach (DependencyNode table in orderedTables) { string tableName = convertorTablenames[table.DbObject.NameWithSchema.ToLower()]; if (!tableName.Contains("[") && !tableName.Contains("]")) { tableName = "[" + tableName.Replace(".", "].[") + "]"; } IList <string> columnsList = _databaseOperations.GetColumnNames(table.DbObject, false); string columns = "[" + string.Join("], [", columnsList) + "]"; if (!useInsertIntoSelect || table.DbObject.NameWithSchema == convertorTablenames[table.DbObject.NameWithSchema.ToLower()]) { if (!valuesInTables[table]) { continue; } if (_databaseOperations.HasTableIdentityColumn(table.DbObject)) { streamToSave.WriteLine($"SET IDENTITY_INSERT {tableName} ON;"); } int values = 0; foreach (string insert in new DatabaseOperations.ValueInsertGetter($"SELECT {columns} {tableSubSelects[table]}")) { if (values % MAX_VALUES_IN_INSERT == 0) { if (values > 0) { streamToSave.WriteLine(";"); streamToSave.WriteLine("GO"); } streamToSave.Write($"INSERT INTO {tableName} ({columns}) VALUES "); } else { streamToSave.Write(", "); } streamToSave.Write(insert); values++; } if (values > 0) { streamToSave.WriteLine(";"); streamToSave.WriteLine("GO"); } if (_databaseOperations.HasTableIdentityColumn(table.DbObject)) { streamToSave.WriteLine($"SET IDENTITY_INSERT {tableName} OFF;"); } } else { exportIsNotEmpty = true; if (_databaseOperations.HasTableIdentityColumn(table.DbObject)) { streamToSave.WriteLine($"SET IDENTITY_INSERT {tableName} ON;"); } streamToSave.WriteLine($"INSERT INTO {tableName} ({columns}) SELECT * {tableSubSelects[table]};"); } } if (valuesGenerated || useInsertIntoSelect) { exportIsNotEmpty = true; streamToSave.Write(witchCheckConstraints.ToString()); } streamToSave.Flush(); return(exportIsNotEmpty); }
public string DeleteEntriesFromTable(DatabaseObject tableWhereDelete, string whereConditions, IDictionary <DB.ForeignKey, DB.ForeignKey.DeleteActions> foreignKeyActions) { StringBuilder delete = new StringBuilder(); DependencyNode startTable = DependencyGraph[tableWhereDelete]; IList <DependencyNode> orderedTables = new List <DependencyNode>(); IDictionary <DependencyNode, IList <DependencyEdge> > childEdges = new Dictionary <DependencyNode, IList <DependencyEdge> >(); childEdges[startTable] = new List <DependencyEdge>(); foreach (DependencyNode table in DependencyGraph.GetDescendants(tableWhereDelete)) { childEdges[table] = new List <DependencyEdge>(); } ISet <DependencyNode> processing = new HashSet <DependencyNode>(); ISet <DependencyNode> processed = new HashSet <DependencyNode>(); ProccessDeleteSubhierarchy(startTable, null, orderedTables, childEdges, foreignKeyActions, processing, processed); IList <string> queries = new List <string>(); IDictionary <DependencyNode, string> tableSubSelects = new Dictionary <DependencyNode, string>(orderedTables.Count); foreach (DependencyNode table in orderedTables.Reverse()) { if (table.DbObject.Equals(tableWhereDelete)) { tableSubSelects.Add(table, $"{(whereConditions.Trim().Length > 0 ? $" WHERE {whereConditions}" : "")}"); queries.Add($"DELETE FROM {tableWhereDelete.NameWithSchemaBrackets} {tableSubSelects[table]}"); } foreach (DependencyEdge edge in childEdges[table]) { DependencyNode child = edge.Child; IList <string> childColumnsConditions = new List <string>(); foreach (DependencyColumn column in edge.Columns) { childColumnsConditions.Add($"{child.DbObject.NameWithSchemaBrackets}.[{column.ChildColumn}] IN (SELECT DISTINCT {table.DbObject.NameWithSchemaBrackets}.[{column.ParentColumn}] FROM {edge.Parent.DbObject.NameWithSchemaBrackets} {tableSubSelects[edge.Parent]})"); } if (!tableSubSelects.ContainsKey(child)) { tableSubSelects[child] = $"WHERE ({string.Join(" OR ", childColumnsConditions)})"; } else if (!child.DbObject.Equals(tableWhereDelete)) { tableSubSelects[child] = tableSubSelects[child] + $" OR ({string.Join(" OR ", childColumnsConditions)})"; } if (foreignKeyActions[edge.ForeignKey] == DB.ForeignKey.DeleteActions.Cascade) { queries.Add($"DELETE FROM {child.DbObject.NameWithSchemaBrackets} {tableSubSelects[child]}"); } else if (foreignKeyActions[edge.ForeignKey] == DB.ForeignKey.DeleteActions.SetNull) { IList <string> setColumns = new List <string>(); foreach (DependencyColumn column in edge.Columns) { setColumns.Add($"{child.DbObject.NameWithSchemaBrackets}.[{column.ChildColumn}] = NULL"); } queries.Add($"UPDATE {child.DbObject.NameWithSchemaBrackets} SET {string.Join(", ", setColumns)} {tableSubSelects[child]}"); } else if (foreignKeyActions[edge.ForeignKey] == DB.ForeignKey.DeleteActions.SetDefault) { IList <string> setColumns = new List <string>(); foreach (DependencyColumn column in edge.Columns) { string defaultValue = column.ChildColumn.DefaultValue; if (defaultValue == null) { defaultValue = "NULL"; } setColumns.Add($"{child.DbObject.NameWithSchemaBrackets}.[{column.ChildColumn}] = {defaultValue}"); } queries.Add($"UPDATE {child.DbObject.NameWithSchemaBrackets} SET {string.Join(", ", setColumns)} {tableSubSelects[child]}"); } } } delete.AppendLine("BEGIN TRY").AppendLine("BEGIN TRANSACTION"); foreach (string query in queries.Reverse()) { delete.Append(query).AppendLine(";"); } delete.AppendLine("COMMIT").AppendLine("END TRY").AppendLine("BEGIN CATCH"); delete.AppendLine("IF @@TRANCOUNT > 0").AppendLine("ROLLBACK TRAN"); delete.AppendLine("DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()"); delete.AppendLine("DECLARE @ErrorSeverity INT = ERROR_SEVERITY()"); delete.AppendLine("DECLARE @ErrorState INT = ERROR_STATE()"); delete.AppendLine("RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); "); delete.AppendLine("END CATCH"); return(delete.ToString()); }
private void GenerateTableDependencyGraph(IList <DatabaseObject> tablesLimit, bool onlyTablesInLimit) { if (tablesLimit != null) { tablesLimit = new List <DatabaseObject>(tablesLimit); } _tables = new Dictionary <int, DependencyNode>(); DatabaseOperations databaseOperations = new DatabaseOperations(Database.Instance); try { IList <DatabaseObject> tables = onlyTablesInLimit && tablesLimit != null ? new List <DatabaseObject>(tablesLimit) : databaseOperations.GetTables(); foreach (DatabaseObject table in tables) { _tables.Add(table.ID, new DependencyNode(table)); } DependencyEdges = onlyTablesInLimit ? databaseOperations.GetDependencyEdges(tablesLimit) : databaseOperations.GetDependencyEdges(); foreach (DependencyEdge edge in DependencyEdges) { _tables[edge.Child.DbObject.ID].AddDependencyEdge(edge); _tables[edge.Parent.DbObject.ID].AddDependencyEdge(edge); } if (tablesLimit != null && !onlyTablesInLimit) { IList <DependencyNode> tablesNotInLimit = new List <DependencyNode>(_tables.Values.Where(v => !tablesLimit.Contains(v.DbObject))); ISet <DependencyNode> notProcessedTables = new HashSet <DependencyNode>(_tables.Values); while (notProcessedTables.Count > 0) { DependencyNode start = notProcessedTables.First(); bool neighbourWithLimit = false; ISet <DependencyNode> processing = new HashSet <DependencyNode>(start.Children); IList <DependencyNode> processed = new List <DependencyNode>(); processing.UnionWith(start.Parents); processing.Add(start); while (processing.Count > 0) { DependencyNode node = processing.First(); processing.Remove(node); if (!notProcessedTables.Contains(node)) { continue; } notProcessedTables.Remove(node); if (!tablesNotInLimit.Contains(node)) { neighbourWithLimit = true; } else { processed.Add(node); } processing.UnionWith(node.Children); processing.UnionWith(node.Parents); } if (neighbourWithLimit) { foreach (DependencyNode node in processed) { tablesLimit.Add(node.DbObject); } } } GenerateTableDependencyGraph(tablesLimit, true); } } catch (DatabaseException exc) { Debug.WriteLine(exc); throw new TableDependencyException("Cannot generate table dependencies", exc); } }
public DependencyEdge(ForeignKey foreignKey, DependencyNode parent, TableColumn parentColumn, DependencyNode child, TableColumn childColumn) { ForeignKey = foreignKey; Parent = parent; Child = child; Columns = new List <DependencyColumn> { new DependencyColumn(parentColumn, childColumn) }; }