private static void RestoreTidUniqueColumn(EduHubEntity entity)
{
// check no unique column is available
if (!entity.Indexes.Any(e => e.IsUnique))
{
var tidColumn = entity.Fields.FirstOrDefault(e =>
e.Name.Equals("TID", StringComparison.Ordinal) &&
!e.IsNullable && e.SqlType.Equals("int"));
// has valid TID column
if (tidColumn != null)
{
// existing index?
var index = entity.Indexes.FirstOrDefault(i => i.Fields.Contains(tidColumn));
if (index == null)
{
// create index
var fields = new List <EduHubField>()
{
tidColumn
};
index = new EduHubIndex(entity, "Index_TID", fields, false, true, false);
entity.AddIndex(index);
}
}
}
}
public static void EnsureNavigationFieldIndexes(EduHubSchema schema)
{
foreach (var childField in schema.Entities.SelectMany(e => e.Fields).Where(f => f.ForeignParentKey.EntityName != null))
{
if (childField.GetIndex() == null)
{
var entity = childField.Entity;
var fields = new List <EduHubField>()
{
childField
};
var indexName = $"Index_{string.Join("_", fields.Select(f => f.Name))}";
var index = new EduHubIndex(entity, indexName, fields.AsReadOnly(), false, false, false);
entity.AddIndex(index);
}
}
}
private static void AugmentSchema(EduHubSchema Schema, List <SysTable> Tables, List <SysType> Types, List <SysColumn> Columns, List <SysIndex> Indexes, List <SysIndexColumn> IndexColumns)
{
var tableLookup = Tables.ToDictionary(t => t.name, t => t.object_id, StringComparer.OrdinalIgnoreCase);
var typeLookup = Types.ToDictionary(t => t.user_type_id, t => t.name);
var columnsLookup = Columns
.GroupBy(c => c.object_id)
.ToDictionary(g => g.Key, g => g.OrderBy(c => c.column_id).ToList());
var indexLookup = Indexes
.GroupBy(i => i.object_id)
.ToDictionary(g => g.Key, g => g.OrderBy(i => i.index_id).ToList());
var indexColumnLookup = IndexColumns
.GroupBy(ic => ic.object_id)
.ToDictionary(g => g.Key, g => g.GroupBy(ic => ic.index_id).ToDictionary(g2 => g2.Key, g2 => g2.OrderBy(ic => ic.key_ordinal).ToList()));
foreach (var entity in Schema.Entities)
{
// Table ID
if (!tableLookup.TryGetValue(entity.Name, out int tableObjectId))
{
throw new InvalidOperationException($"Unknown Entity: {entity.Name}");
}
else
{
// COLUMNS
// Validate schema conformance; determine nullable and identity columns
if (columnsLookup.TryGetValue(tableObjectId, out List <SysColumn> entityColumns))
{
foreach (var column in entityColumns)
{
var field = entity.Fields.First(f => f.Name.Equals(column.name, StringComparison.OrdinalIgnoreCase));
field.SqlType = typeLookup[column.user_type_id];
// Check Framework Type
if (field.Type != GetFrameworkType(field.SqlType))
{
// Overrides
if (field.Entity.Name == "SPFSTORE" && field.Name == "ASSOCIATION_TYPE" && field.Type == "short" && field.SqlType == "int")
{
field.Type = "int";
}
else
{
throw new InvalidOperationException("Entity field type didn't match database schema");
}
}
switch (field.SqlType)
{
case "varchar":
case "varbinary":
if (field.TypeMaxLength == 0)
{
field.TypeMaxLength = column.max_length;
}
// Overrides
if (field.Entity.Name == "SPFSTORE" && field.Name == "PHYSICAL_LOCATION" && field.TypeMaxLength == 255 && column.max_length == 500)
{
field.TypeMaxLength = column.max_length;
break;
}
if (field.TypeMaxLength != column.max_length)
{
throw new InvalidOperationException("Entity field max length didn't match database schema");
}
break;
}
if (!field.IsNullable && column.is_nullable)
{
throw new InvalidOperationException("Entity field nullable didn't match database schema");
}
if (field.IsNullable && !column.is_nullable)
{
field.IsNullable = false;
}
if (field.IsIdentity && !column.is_identity)
{
throw new InvalidOperationException("Entity field identity didn't match database schema");
}
if (!field.IsIdentity && column.is_identity)
{
field.IsIdentity = true;
}
}
}
// INDEXES
// Determine Indexes
if (indexLookup.TryGetValue(tableObjectId, out List <SysIndex> entityIndexes))
{
foreach (var entityIndex in entityIndexes)
{
List <SysIndexColumn> indexColumns = indexColumnLookup[entityIndex.object_id][entityIndex.index_id];
List <EduHubField> fields = indexColumns
.Select(ic => entity.Fields.First(f => f.Name.Equals(entityColumns.First(c => c.column_id == ic.column_id).name, StringComparison.OrdinalIgnoreCase)))
.ToList();
if (fields.Count == 0)
{
throw new InvalidOperationException("Unexpected index with no columns");
}
var indexName = $"Index_{string.Join("_", fields.Select(f => f.Name))}";
var index = new EduHubIndex(
Entity: entity,
Name: indexName,
Fields: fields.AsReadOnly(),
IsPrimary: entityIndex.is_primary_key,
IsUnique: entityIndex.is_unique,
IsClustered: entityIndex.type == 1); // 1 = Clustered, 2 = Non Clustered
// Check for existing Index with matching Fields; if matched, add unique or shortest name;
var matchingIndex = entity.Indexes.FirstOrDefault(ei => ei.Fields.Count == index.Fields.Count && ei.Fields.All(f => index.Fields.Contains(f)));
if (matchingIndex != null)
{
if ((!matchingIndex.IsUnique && index.IsUnique) || // New Index is unique
(!matchingIndex.IsPrimary && index.IsPrimary) || // New Index is primary
(!matchingIndex.IsClustered && index.IsClustered) || // New Index is clustered
(index.Name.Length < matchingIndex.Name.Length)) // New Index has shorter name
{
// Remove existing, add new
entity.RemoveIndex(matchingIndex);
entity.AddIndex(index);
}
}
else
{
entity.AddIndex(index);
}
}
}
// Ensure identity columns have index
foreach (var identityField in entity.Fields.Where(f => f.IsIdentity))
{
var name = $"Index_{identityField.Name}";
var index = new EduHubIndex(
Entity: entity,
Name: name,
Fields: new List <EduHubField>()
{
identityField
}.AsReadOnly(),
IsPrimary: false,
IsUnique: true,
IsClustered: false);
// Check for existing Index with matching Fields; if matched, add unique or shortest name;
var matchingIndex = entity.Indexes.FirstOrDefault(ei => ei.Fields.Count == index.Fields.Count && ei.Fields.All(f => index.Fields.Contains(f)));
if (matchingIndex != null)
{
if ((!matchingIndex.IsUnique && index.IsUnique) || // New Index is unique
(index.Name.Length < matchingIndex.Name.Length)) // New Index has shorter name
{
if (matchingIndex.IsClustered)
{
throw new InvalidOperationException("Shouldn't replace clustered indexes");
}
if (matchingIndex.IsPrimary)
{
throw new InvalidOperationException("Shouldn't replace primary indexes");
}
// Remove existing, add new
entity.RemoveIndex(matchingIndex);
entity.AddIndex(index);
}
}
else
{
entity.AddIndex(index);
}
}
}
}
}
