public IList <IList <string> > EvaluateForeignKeyDataType(ForeignKey key, int keySize)
{
IList <IList <string> > result = new List <IList <string> >();
if (keySize > RECOMMEND_MAX_COLUMN_SIZE_INDEX && key.Columns.All(c => c.Type != DataTypeConstants.HIERARCHYID)) // hierarchyid can be ignored, it can be very large, but it is his property
{
string columnSingular = "column";
string columnPlural = "columns";
string considerLowerSize = "";
string considerReplaceCharWithNumeric = "";
IList <string> columns;
if (key.Columns.Any(c => c.DataTypeIsChar() || c.DataTypeIsBinary()))
{
columns = key.Columns.Where(c => c.DataTypeIsChar() || c.DataTypeIsBinary()).Select(c => $"{c.Name} ({c.TypeWithLength})").ToList();
considerLowerSize = $" Consider lowering length of {(columns.Count() == 1 ? columnSingular : columnPlural)} {string.Join(", ", columns)}.";
if (key.Columns.Any(c => c.DataTypeIsChar()))
{
considerReplaceCharWithNumeric = $" Consider replacement of string columns ({string.Join(", ", key.Columns.Where(c => c.DataTypeIsChar()).Select(c => c.Name))}) with numeric ID.";
}
}
columns = key.Columns.Select(c => c.TypeWithLength).ToList();
result.Add(new List <string>
{
"Suggestion:",
$"Foreign key is consisted of {(columns.Count == 1 ? columnSingular : columnPlural)} {string.Join(", ", key.Columns.Select(c => c.TypeWithLength))} and {(columns.Count == 1 ? "its" : "their")} size exceeds {RECOMMEND_MAX_COLUMN_SIZE_INDEX} bytes.{considerLowerSize}{considerReplaceCharWithNumeric}"
});
}
else
{
result.Add(new List <string>
{
"No suggestion, data type is OK for indexing"
});
}
return(result);
}
public IList <ForeignKeyMissingIndexes> GetForeignKeysWithoutIndexes(IList <DatabaseObject> tablesList)
{
IList <ForeignKeyMissingIndexes> keys = new List <ForeignKeyMissingIndexes>();
string tablesWhere;
if (tablesList != null)
{
tablesWhere = "WHERE main.parent_object_id IN (" + string.Join(", ", tablesList.Select(table => table.ID)) + ")";
}
else
{
tablesWhere = "";
}
try
{
IDictionary <int, ForeignKey> allKeys = new Dictionary <int, ForeignKey>();
using (SqlDataReader resultReader = Database.Instance.ExecuteSelect($"SELECT main.constraint_object_id AS id, OBJECT_SCHEMA_NAME(main.parent_object_id) AS schema_name, main.parent_object_id AS object_id, OBJECT_NAME(main.parent_object_id) AS tablename, OBJECT_NAME(main.constraint_object_id) AS FK_name, COL_NAME(main.parent_object_id, main.parent_column_id) AS column_name, sys.types.name AS type_name, sys.types.is_user_defined AS is_user_type, sys.columns.max_length AS maxLength, sys.columns.is_nullable AS is_nullable, OBJECT_DEFINITION(sys.columns.default_object_id) AS default_value FROM sys.foreign_key_columns AS main LEFT JOIN sys.columns ON sys.columns.object_id = main.parent_object_id AND sys.columns.column_id = main.parent_column_id LEFT JOIN sys.types ON sys.types.user_type_id = sys.columns.user_type_id {tablesWhere} ORDER BY schema_name, tablename, id;"))
{
int columnFKIDOrdinal = resultReader.GetOrdinal("id");
int columnTableIDOrdinal = resultReader.GetOrdinal("object_id");
int columnTablenameOrdinal = resultReader.GetOrdinal("tablename");
int columnSchemaOrdinal = resultReader.GetOrdinal("schema_name");
int columnFKNameOrdinal = resultReader.GetOrdinal("FK_name");
int columnNameOrdinal = resultReader.GetOrdinal("column_name");
int columnTypeNameOrdinal = resultReader.GetOrdinal("type_name");
int columnMaxLengthOrdinal = resultReader.GetOrdinal("maxLength");
int columnIsUserTypeOrdinal = resultReader.GetOrdinal("is_user_type");
int columnIsNullableOrdinal = resultReader.GetOrdinal("is_nullable");
int columnDefaultValueOrdinal = resultReader.GetOrdinal("default_value");
ForeignKey fk = null;
int lastFK_id = -1;
string lastColumn = "";
while (resultReader.Read())
{
int FK_id = resultReader.GetInt32(columnFKIDOrdinal);
string columnName = resultReader.GetString(columnNameOrdinal);
IList <TableColumn> columns = null;
if (FK_id != lastFK_id || (FK_id == lastFK_id && lastColumn != columnName))
{
string defaultValue = null;
if (!resultReader.IsDBNull(columnDefaultValueOrdinal))
{
defaultValue = resultReader.GetString(columnDefaultValueOrdinal);
}
TableColumn column = new TableColumn(columnName, defaultValue, resultReader.GetBoolean(columnIsNullableOrdinal), resultReader.GetString(columnTypeNameOrdinal), resultReader.GetInt16(columnMaxLengthOrdinal), resultReader.GetBoolean(columnIsUserTypeOrdinal), true);
if (FK_id != lastFK_id)
{
columns = new List <TableColumn>(1);
}
else
{
columns = fk.Columns;
}
columns.Add(column);
}
if (lastFK_id != FK_id)
{
if (fk != null)
{
allKeys.Add(fk.ID, fk);
}
fk = new ForeignKey(FK_id, new DatabaseObject(resultReader.GetInt32(columnTableIDOrdinal), resultReader.GetString(columnSchemaOrdinal), resultReader.GetString(columnTablenameOrdinal), DatabaseObject.DatabaseObjectType.Table), resultReader.GetString(columnFKNameOrdinal), ForeignKey.DeleteActions.NoAction, columns);
lastFK_id = FK_id;
}
}
if (lastFK_id > 0)
{
allKeys.Add(fk.ID, fk);
}
}
using (SqlDataReader resultReader = Database.Instance.ExecuteSelect($"SELECT main.constraint_object_id AS id, sys.index_columns.index_column_id AS index_id, sys.index_columns.index_column_id AS index_order, sys.columns.name AS index_column_name FROM sys.foreign_key_columns AS main LEFT JOIN sys.index_columns AS index_columns_temp ON index_columns_temp.object_id=main.parent_object_id AND index_columns_temp.column_id=main.parent_column_id LEFT JOIN sys.indexes ON sys.indexes.object_id=index_columns_temp.object_id AND sys.indexes.index_id=index_columns_temp.index_id LEFT JOIN sys.index_columns ON sys.index_columns.object_id=sys.indexes.object_id AND sys.index_columns.index_id=sys.indexes.index_id LEFT JOIN sys.columns ON sys.columns.object_id=sys.index_columns.object_id AND sys.columns.column_id=sys.index_columns.column_id {tablesWhere} ORDER BY id, index_id, index_order;"))
{
int columnFKIDOrdinal = resultReader.GetOrdinal("id");
int columnIndexIDOrdinal = resultReader.GetOrdinal("index_id");
int columnIndexOrderOrdinal = resultReader.GetOrdinal("index_order");
int columnIndexColumnNameOrdinal = resultReader.GetOrdinal("index_column_name");
int lastFK_id = -1;
int lastIndexID = -1;
int lastIndexOrder = -1;
IList <string> stringColumns = new List <string>();
ForeignKey FK = null;
while (resultReader.Read())
{
int FK_id = resultReader.GetInt32(columnFKIDOrdinal);
if (lastFK_id != FK_id && lastFK_id > -1)
{
if (stringColumns.Count > 0)
{
AddMissingForeignKey(FK, keys, stringColumns);
}
}
if (lastFK_id != FK_id)
{
stringColumns = new List <string>(allKeys[FK_id].Columns.Select(c => c.Name));
FK = allKeys[FK_id];
lastIndexID = -1;
lastIndexOrder = -1;
}
lastFK_id = FK_id;
if (resultReader.IsDBNull(columnIndexIDOrdinal))
{
continue;
}
if (resultReader.GetInt32(columnIndexOrderOrdinal) == 1 && stringColumns.Contains(resultReader.GetString(columnIndexColumnNameOrdinal)))
{
stringColumns.Remove(resultReader.GetString(columnIndexColumnNameOrdinal));
lastIndexID = resultReader.GetInt32(columnIndexIDOrdinal);
lastIndexOrder = 1;
}
else if (resultReader.GetInt32(columnIndexIDOrdinal) == lastIndexID && resultReader.GetInt32(columnIndexOrderOrdinal) == lastIndexOrder + 1)
{
stringColumns.Remove(resultReader.GetString(columnIndexColumnNameOrdinal));
}
}
if (FK != null && stringColumns.Count > 0)
{
AddMissingForeignKey(FK, keys, stringColumns);
}
}
}
catch (Exception exc)
{
Debug.WriteLine(exc);
throw new DatabaseException("List of foreign keys without indexes cannot be loaded", exc);
}
return(keys);
}
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);
}
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);
}
}
