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