private string GetSqlQueryValuesNotUsedInForeignKeys(DependencyEdge edge)
{
IList <string> columns = new List <string>(edge.Columns.Count);
foreach (DependencyColumn column in edge.Columns)
{
columns.Add($"{edge.Parent.DbObject.NameWithSchemaBrackets}.[{column.ParentColumn.Name}] NOT IN (SELECT DISTINCT {edge.Child.DbObject.NameWithSchemaBrackets}.[{column.ChildColumn.Name}] FROM {edge.Child.DbObject.NameWithSchemaBrackets} WHERE {edge.Child.DbObject.NameWithSchemaBrackets}.[{column.ChildColumn.Name}] IS NOT NULL)");
}
return($"FROM {edge.Parent.DbObject.NameWithSchemaBrackets} WHERE {string.Join(" AND ", columns)}");
}
public int CountValuesNotUsedInForeignKeys(DependencyEdge edge)
{
try
{
return((int)Database.Instance.ExecuteScalarSelect($"SELECT COUNT(*) {GetSqlQueryValuesNotUsedInForeignKeys(edge)}"));
}
catch (Exception exc)
{
Debug.WriteLine(exc);
if (exc is SqlException || exc is InvalidOperationException)
{
throw new DatabaseException("Not used values of foreign keys is not possible to obtain", exc);
}
throw;
}
}
public ForeignKeyNotUsedTabPage(DependencyEdge edge, DatabaseOperations databaseOperations)
{
InitializeComponent();
_edge = edge;
_databaseOperations = databaseOperations;
int valuesCount = 0;
try
{
valuesCount = _databaseOperations.CountValuesNotUsedInForeignKeys(edge);
if (valuesCount == 0)
{
buttonLoad.Enabled = false;
numericUpDownLoadValues.Value = 0;
numericUpDownLoadValues.Maximum = 0;
numericUpDownLoadValues.Enabled = false;
labelEmpty.Text = NO_VALUES_ALL;
}
else
{
buttonLoad.Enabled = true;
numericUpDownLoadValues.Maximum = valuesCount;
numericUpDownLoadValues.Value = valuesCount;
numericUpDownLoadValues.Enabled = true;
labelEmpty.Text = LOAD_VALUE_INFO;
}
}
catch (DatabaseException exc)
{
Debug.WriteLine(exc);
buttonLoad.Enabled = false;
numericUpDownLoadValues.Value = 0;
numericUpDownLoadValues.Maximum = 0;
numericUpDownLoadValues.Enabled = false;
labelEmpty.Text = VALUE_LOAD_ERROR;
}
labelValues.Text = string.Format(TOTAL_VALUE, valuesCount);
Dock = DockStyle.Fill;
}
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 DataTable ValuesNotUsedInForeignKeys(DependencyEdge edge, int limit = -1)
{
string top = "";
if (limit > 0)
{
top = $"TOP {limit}";
}
DataTable result = new DataTable(edge.Name);
try
{
IList <string> columns = GetColumnNames(edge.Parent.DbObject);
foreach (string column in columns)
{
result.Columns.Add(column);
}
using (SqlDataReader resultReader = _database.ExecuteSelect($"SELECT {top} * {GetSqlQueryValuesNotUsedInForeignKeys(edge)}"))
{
while (resultReader.Read())
{
DataRow row = result.NewRow();
for (int i = 0; i < resultReader.FieldCount; i++)
{
row[columns[i]] = GetStringRepresentationOfSqlValue(resultReader, i, true);
}
result.Rows.Add(row);
}
return(result);
}
}
catch (Exception exc)
{
Debug.WriteLine(exc);
throw new DatabaseException("Values cannot be loaded", exc);
}
}
public IList <DependencyEdge> GetDependencyEdges(IList <DatabaseObject> tablesLimit)
{
string tablesCondition = "";
IList <DatabaseObject> tableList = null;
if (tablesLimit != null)
{
tableList = tablesLimit;
string tableIDs = string.Join(",", tablesLimit.Select(t => t.ID));
tablesCondition = $"WHERE sys.foreign_key_columns.parent_object_id IN({tableIDs}) AND sys.foreign_key_columns.referenced_object_id IN({tableIDs})";
}
IDictionary <int, DependencyEdge> edges = new Dictionary <int, DependencyEdge>();
IDictionary <int, DependencyNode> tables = new Dictionary <int, DependencyNode>();
try
{
if (tableList == null)
{
tableList = GetTables();
}
foreach (DatabaseObject dbObject in tableList)
{
tables.Add(dbObject.ID, new DependencyNode(dbObject));
}
using (SqlDataReader resultReader = Database.Instance.ExecuteSelect($"SELECT sys.foreign_key_columns.constraint_object_id AS id, OBJECT_NAME(sys.foreign_key_columns.constraint_object_id) AS name, sys.foreign_key_columns.parent_object_id AS child, COL_NAME(sys.foreign_key_columns.parent_object_id, sys.foreign_key_columns.parent_column_id) AS child_column, sys.foreign_key_columns.referenced_object_id AS parent, COL_NAME(sys.foreign_key_columns.referenced_object_id, sys.foreign_key_columns.referenced_column_id) AS parent_column, sys.foreign_keys.delete_referential_action AS delete_action, child_columns_table.is_nullable AS child_can_be_null, OBJECT_DEFINITION(child_columns_table.default_object_id) AS child_default_value, parent_columns_table.is_nullable AS parent_can_be_null, OBJECT_DEFINITION(parent_columns_table.default_object_id) AS parent_default_value, sys.types.name AS typeName, parent_columns_table.max_length AS maxLength, sys.types.is_user_defined AS isUserType FROM sys.foreign_key_columns LEFT JOIN sys.foreign_keys ON sys.foreign_keys.object_id = sys.foreign_key_columns.constraint_object_id LEFT JOIN sys.columns AS child_columns_table ON child_columns_table.object_id = sys.foreign_key_columns.parent_object_id AND child_columns_table.column_id = sys.foreign_key_columns.parent_column_id LEFT JOIN sys.columns AS parent_columns_table ON parent_columns_table.object_id = sys.foreign_key_columns.referenced_object_id AND parent_columns_table.column_id = sys.foreign_key_columns.referenced_column_id LEFT JOIN sys.types ON sys.types.user_type_id = parent_columns_table.user_type_id {tablesCondition};"))
{
int columnIdOrdinal = resultReader.GetOrdinal("id");
int columnNameOrdinal = resultReader.GetOrdinal("name");
int columnChildOrdinal = resultReader.GetOrdinal("child");
int columnParentOrdinal = resultReader.GetOrdinal("parent");
int columnChildColumnOrdinal = resultReader.GetOrdinal("child_column");
int columnParentColumnOrdinal = resultReader.GetOrdinal("parent_column");
int columnDeleteActionColumnOrdinal = resultReader.GetOrdinal("delete_action");
int columnChildCanBeNullColumnOrdinal = resultReader.GetOrdinal("child_can_be_null");
int columnChildDefaultValueColumnOrdinal = resultReader.GetOrdinal("child_default_value");
int columnParentCanBeNullColumnOrdinal = resultReader.GetOrdinal("parent_can_be_null");
int columnParentDefaultValueColumnOrdinal = resultReader.GetOrdinal("parent_default_value");
int columnDatatypeNameOrdinal = resultReader.GetOrdinal("typeName");
int columnMaxLengthOrdinal = resultReader.GetOrdinal("maxLength");
int columnIsUserTypeOrdinal = resultReader.GetOrdinal("isUserType");
while (resultReader.Read())
{
string childDefaultValue = null;
if (!resultReader.IsDBNull(columnChildDefaultValueColumnOrdinal))
{
childDefaultValue = resultReader.GetString(columnChildDefaultValueColumnOrdinal);
}
string parentDefaultValue = null;
if (!resultReader.IsDBNull(columnParentDefaultValueColumnOrdinal))
{
parentDefaultValue = resultReader.GetString(columnParentDefaultValueColumnOrdinal);
}
TableColumn childColumn = new TableColumn(resultReader.GetString(columnChildColumnOrdinal), childDefaultValue, resultReader.GetBoolean(columnChildCanBeNullColumnOrdinal), resultReader.GetString(columnDatatypeNameOrdinal), resultReader.GetInt16(columnMaxLengthOrdinal), resultReader.GetBoolean(columnIsUserTypeOrdinal), true);
TableColumn parentColumn = new TableColumn(resultReader.GetString(columnParentColumnOrdinal), parentDefaultValue, resultReader.GetBoolean(columnParentCanBeNullColumnOrdinal), resultReader.GetString(columnDatatypeNameOrdinal), resultReader.GetInt16(columnMaxLengthOrdinal), resultReader.GetBoolean(columnIsUserTypeOrdinal), false);
DependencyEdge edge;
if (!edges.TryGetValue(resultReader.GetInt32(columnIdOrdinal), out edge))
{
ForeignKey.DeleteActions deleteAction;
if (!Enum.TryParse(resultReader.GetByte(columnDeleteActionColumnOrdinal).ToString(), out deleteAction))
{
deleteAction = ForeignKey.DeleteActions.NoAction;
}
ForeignKey fk = new ForeignKey(resultReader.GetInt32(columnIdOrdinal), tables[resultReader.GetInt32(columnChildOrdinal)].DbObject, resultReader.GetString(columnNameOrdinal), deleteAction, new List <TableColumn> {
childColumn
});
edge = new DependencyEdge(fk, tables[resultReader.GetInt32(columnParentOrdinal)], parentColumn, tables[resultReader.GetInt32(columnChildOrdinal)], childColumn);
edges.Add(resultReader.GetInt32(columnIdOrdinal), edge);
edge.Parent.AddDependencyEdge(edge);
edge.Child.AddDependencyEdge(edge);
}
else
{
edge.AddDependencyColumn(parentColumn, childColumn);
}
}
return(edges.Values.ToList());
}
}
catch (Exception exc)
{
Debug.WriteLine(exc);
throw new DatabaseException("List of edges cannot be loaded", exc);
}
}
