internal DatabaseSchemaResult ValidateSchema(IEnumerable <Type> orderedTables)
{
var result = new DatabaseSchemaResult();
result.IndexDefinitions.AddRange(SqlSyntax.GetDefinedIndexes(_database)
.Select(x => new DbIndexDefinition(x)));
result.TableDefinitions.AddRange(orderedTables
.Select(x => DefinitionFactory.GetTableDefinition(x, SqlSyntax)));
ValidateDbTables(result);
ValidateDbColumns(result);
ValidateDbIndexes(result);
ValidateDbConstraints(result);
return(result);
}
private DatabaseSchemaResult ValidateSchema(IScope scope)
{
if (_databaseFactory.Initialized == false)
{
return(new DatabaseSchemaResult());
}
if (_databaseSchemaValidationResult != null)
{
return(_databaseSchemaValidationResult);
}
_databaseSchemaValidationResult = _scopeAccessor.AmbientScope.Database.ValidateSchema();
scope.Complete();
return(_databaseSchemaValidationResult);
}
private void ValidateDbTables(DatabaseSchemaResult result)
{
//Check tables in configured database against tables in schema
var tablesInDatabase = SqlSyntax.GetTablesInSchema(_database).ToList();
var tablesInSchema = result.TableDefinitions.Select(x => x.Name).ToList();
//Add valid and invalid table differences to the result object
var validTableDifferences = tablesInDatabase.Intersect(tablesInSchema, StringComparer.InvariantCultureIgnoreCase);
foreach (var tableName in validTableDifferences)
{
result.ValidTables.Add(tableName);
}
var invalidTableDifferences =
tablesInDatabase.Except(tablesInSchema, StringComparer.InvariantCultureIgnoreCase)
.Union(tablesInSchema.Except(tablesInDatabase, StringComparer.InvariantCultureIgnoreCase));
foreach (var tableName in invalidTableDifferences)
{
result.Errors.Add(new Tuple <string, string>("Table", tableName));
}
}
private void ValidateDbColumns(DatabaseSchemaResult result)
{
//Check columns in configured database against columns in schema
var columnsInDatabase = SqlSyntax.GetColumnsInSchema(_database);
var columnsPerTableInDatabase = columnsInDatabase.Select(x => string.Concat(x.TableName, ",", x.ColumnName)).ToList();
var columnsPerTableInSchema = result.TableDefinitions.SelectMany(x => x.Columns.Select(y => string.Concat(y.TableName, ",", y.Name))).ToList();
//Add valid and invalid column differences to the result object
var validColumnDifferences = columnsPerTableInDatabase.Intersect(columnsPerTableInSchema, StringComparer.InvariantCultureIgnoreCase);
foreach (var column in validColumnDifferences)
{
result.ValidColumns.Add(column);
}
var invalidColumnDifferences =
columnsPerTableInDatabase.Except(columnsPerTableInSchema, StringComparer.InvariantCultureIgnoreCase)
.Union(columnsPerTableInSchema.Except(columnsPerTableInDatabase, StringComparer.InvariantCultureIgnoreCase));
foreach (var column in invalidColumnDifferences)
{
result.Errors.Add(new Tuple <string, string>("Column", column));
}
}
private void ValidateDbIndexes(DatabaseSchemaResult result)
{
//These are just column indexes NOT constraints or Keys
//var colIndexesInDatabase = result.DbIndexDefinitions.Where(x => x.IndexName.InvariantStartsWith("IX_")).Select(x => x.IndexName).ToList();
var colIndexesInDatabase = result.IndexDefinitions.Select(x => x.IndexName).ToList();
var indexesInSchema = result.TableDefinitions.SelectMany(x => x.Indexes.Select(y => y.Name)).ToList();
//Add valid and invalid index differences to the result object
var validColIndexDifferences = colIndexesInDatabase.Intersect(indexesInSchema, StringComparer.InvariantCultureIgnoreCase);
foreach (var index in validColIndexDifferences)
{
result.ValidIndexes.Add(index);
}
var invalidColIndexDifferences =
colIndexesInDatabase.Except(indexesInSchema, StringComparer.InvariantCultureIgnoreCase)
.Union(indexesInSchema.Except(colIndexesInDatabase, StringComparer.InvariantCultureIgnoreCase));
foreach (var index in invalidColIndexDifferences)
{
result.Errors.Add(new Tuple <string, string>("Index", index));
}
}
/// <summary>
/// This validates the Primary/Foreign keys in the database
/// </summary>
/// <param name="result"></param>
/// <remarks>
/// This does not validate any database constraints that are not PKs or FKs because Umbraco does not create a database with non PK/FK constraints.
/// Any unique "constraints" in the database are done with unique indexes.
/// </remarks>
private void ValidateDbConstraints(DatabaseSchemaResult result)
{
//Check constraints in configured database against constraints in schema
var constraintsInDatabase = SqlSyntax.GetConstraintsPerColumn(_database).DistinctBy(x => x.Item3).ToList();
var foreignKeysInDatabase = constraintsInDatabase.Where(x => x.Item3.InvariantStartsWith("FK_")).Select(x => x.Item3).ToList();
var primaryKeysInDatabase = constraintsInDatabase.Where(x => x.Item3.InvariantStartsWith("PK_")).Select(x => x.Item3).ToList();
var unknownConstraintsInDatabase =
constraintsInDatabase.Where(
x =>
x.Item3.InvariantStartsWith("FK_") == false && x.Item3.InvariantStartsWith("PK_") == false &&
x.Item3.InvariantStartsWith("IX_") == false).Select(x => x.Item3).ToList();
var foreignKeysInSchema = result.TableDefinitions.SelectMany(x => x.ForeignKeys.Select(y => y.Name)).ToList();
var primaryKeysInSchema = result.TableDefinitions.SelectMany(x => x.Columns.Select(y => y.PrimaryKeyName))
.Where(x => x.IsNullOrWhiteSpace() == false).ToList();
// Add valid and invalid foreign key differences to the result object
// We'll need to do invariant contains with case insensitivity because foreign key, primary key is not standardized
// In theory you could have: FK_ or fk_ ...or really any standard that your development department (or developer) chooses to use.
foreach (var unknown in unknownConstraintsInDatabase)
{
if (foreignKeysInSchema.InvariantContains(unknown) || primaryKeysInSchema.InvariantContains(unknown))
{
result.ValidConstraints.Add(unknown);
}
else
{
result.Errors.Add(new Tuple <string, string>("Unknown", unknown));
}
}
//Foreign keys:
var validForeignKeyDifferences = foreignKeysInDatabase.Intersect(foreignKeysInSchema, StringComparer.InvariantCultureIgnoreCase);
foreach (var foreignKey in validForeignKeyDifferences)
{
result.ValidConstraints.Add(foreignKey);
}
var invalidForeignKeyDifferences =
foreignKeysInDatabase.Except(foreignKeysInSchema, StringComparer.InvariantCultureIgnoreCase)
.Union(foreignKeysInSchema.Except(foreignKeysInDatabase, StringComparer.InvariantCultureIgnoreCase));
foreach (var foreignKey in invalidForeignKeyDifferences)
{
result.Errors.Add(new Tuple <string, string>("Constraint", foreignKey));
}
//Primary keys:
//Add valid and invalid primary key differences to the result object
var validPrimaryKeyDifferences = primaryKeysInDatabase.Intersect(primaryKeysInSchema, StringComparer.InvariantCultureIgnoreCase);
foreach (var primaryKey in validPrimaryKeyDifferences)
{
result.ValidConstraints.Add(primaryKey);
}
var invalidPrimaryKeyDifferences =
primaryKeysInDatabase.Except(primaryKeysInSchema, StringComparer.InvariantCultureIgnoreCase)
.Union(primaryKeysInSchema.Except(primaryKeysInDatabase, StringComparer.InvariantCultureIgnoreCase));
foreach (var primaryKey in invalidPrimaryKeyDifferences)
{
result.Errors.Add(new Tuple <string, string>("Constraint", primaryKey));
}
}