/// <summary>
/// Validates the schema of the current database
/// </summary>
public DatabaseSchemaResult ValidateSchema()
{
var result = new DatabaseSchemaResult();
//get the db index defs
result.DbIndexDefinitions = _sqlSyntaxProvider.GetDefinedIndexes(_database)
.Select(x => new DbIndexDefinition()
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray();
foreach (var item in OrderedTables.OrderBy(x => x.Key))
{
var tableDefinition = DefinitionFactory.GetTableDefinition(_sqlSyntaxProvider, item.Value);
result.TableDefinitions.Add(tableDefinition);
}
ValidateDbTables(result);
ValidateDbColumns(result);
ValidateDbIndexes(result);
ValidateDbConstraints(result);
return(result);
}
private void ValidateDbTables(DatabaseSchemaResult result)
{
//Check tables in configured database against tables in schema
var tablesInDatabase = _sqlSyntaxProvider.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 = _sqlSyntaxProvider.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.DbIndexDefinitions.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>
/// Validates the schema of the current database
/// </summary>
public DatabaseSchemaResult ValidateSchema()
{
var result = new DatabaseSchemaResult();
foreach (var item in OrderedTables.OrderBy(x => x.Key))
{
var tableDefinition = DefinitionFactory.GetTableDefinition(item.Value);
result.TableDefinitions.Add(tableDefinition);
}
//Check tables in configured database against tables in schema
var tablesInDatabase = SqlSyntaxContext.SqlSyntaxProvider.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);
foreach (var tableName in validTableDifferences)
{
result.ValidTables.Add(tableName);
}
var invalidTableDifferences =
tablesInDatabase.Except(tablesInSchema)
.Union(tablesInSchema.Except(tablesInDatabase));
foreach (var tableName in invalidTableDifferences)
{
result.Errors.Add(new Tuple<string, string>("Table", tableName));
}
//Check columns in configured database against columns in schema
var columnsInDatabase = SqlSyntaxContext.SqlSyntaxProvider.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);
foreach (var column in validColumnDifferences)
{
result.ValidColumns.Add(column);
}
var invalidColumnDifferences = columnsPerTableInDatabase.Except(columnsPerTableInSchema);
foreach (var column in invalidColumnDifferences)
{
result.Errors.Add(new Tuple<string, string>("Column", column));
}
//MySql doesn't conform to the "normal" naming of constraints, so there is currently no point in doing these checks.
//NOTE: At a later point we do other checks for MySql, but ideally it should be necessary to do special checks for different providers.
if (SqlSyntaxContext.SqlSyntaxProvider is MySqlSyntaxProvider)
return result;
//Check constraints in configured database against constraints in schema
var constraintsInDatabase = SqlSyntaxContext.SqlSyntaxProvider.GetConstraintsPerColumn(_database).DistinctBy(x => x.Item3).ToList();
var foreignKeysInDatabase = constraintsInDatabase.Where(x => x.Item3.StartsWith("FK_")).Select(x => x.Item3).ToList();
var primaryKeysInDatabase = constraintsInDatabase.Where(x => x.Item3.StartsWith("PK_")).Select(x => x.Item3).ToList();
var indexesInDatabase = constraintsInDatabase.Where(x => x.Item3.StartsWith("IX_")).Select(x => x.Item3).ToList();
var unknownConstraintsInDatabase =
constraintsInDatabase.Where(
x =>
x.Item3.StartsWith("FK_") == false && x.Item3.StartsWith("PK_") == false &&
x.Item3.StartsWith("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)).ToList();
var indexesInSchema = result.TableDefinitions.SelectMany(x => x.Indexes.Select(y => y.Name)).ToList();
//Add valid and invalid foreign key differences to the result object
foreach (var unknown in unknownConstraintsInDatabase)
{
if (foreignKeysInSchema.Contains(unknown) || primaryKeysInSchema.Contains(unknown) || indexesInSchema.Contains(unknown))
{
result.ValidConstraints.Add(unknown);
}
else
{
result.Errors.Add(new Tuple<string, string>("Unknown", unknown));
}
}
var validForeignKeyDifferences = foreignKeysInDatabase.Intersect(foreignKeysInSchema);
foreach (var foreignKey in validForeignKeyDifferences)
{
result.ValidConstraints.Add(foreignKey);
}
var invalidForeignKeyDifferences = foreignKeysInDatabase.Except(foreignKeysInSchema);
foreach (var foreignKey in invalidForeignKeyDifferences)
{
result.Errors.Add(new Tuple<string, string>("Constraint", foreignKey));
}
//Add valid and invalid primary key differences to the result object
var validPrimaryKeyDifferences = primaryKeysInDatabase.Intersect(primaryKeysInSchema);
foreach (var primaryKey in validPrimaryKeyDifferences)
{
result.ValidConstraints.Add(primaryKey);
}
var invalidPrimaryKeyDifferences = primaryKeysInDatabase.Except(primaryKeysInSchema);
foreach (var primaryKey in invalidPrimaryKeyDifferences)
{
result.Errors.Add(new Tuple<string, string>("Constraint", primaryKey));
}
//Add valid and invalid index differences to the result object
var validIndexDifferences = indexesInDatabase.Intersect(indexesInSchema);
foreach (var index in validIndexDifferences)
{
result.ValidConstraints.Add(index);
}
var invalidIndexDifferences = indexesInDatabase.Except(indexesInSchema);
foreach (var index in invalidIndexDifferences)
{
result.Errors.Add(new Tuple<string, string>("Constraint", index));
}
return result;
}
private void ValidateDbConstraints(DatabaseSchemaResult result)
{
//MySql doesn't conform to the "normal" naming of constraints, so there is currently no point in doing these checks.
//TODO: At a later point we do other checks for MySql, but ideally it should be necessary to do special checks for different providers.
// ALso note that to get the constraints for MySql we have to open a connection which we currently have not.
if (_sqlSyntaxProvider is MySqlSyntaxProvider)
{
return;
}
//Check constraints in configured database against constraints in schema
var constraintsInDatabase = _sqlSyntaxProvider.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 indexesInDatabase = constraintsInDatabase.Where(x => x.Item3.InvariantStartsWith("IX_")).Select(x => x.Item3).ToList();
var indexesInSchema = result.TableDefinitions.SelectMany(x => x.Indexes.Select(y => y.Name)).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, and even index naming w/ MySQL 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) || indexesInSchema.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));
}
//Constaints:
//NOTE: SD: The colIndex checks above should really take care of this but I need to keep this here because it was here before
// and some schema validation checks might rely on this data remaining here!
//Add valid and invalid index differences to the result object
var validIndexDifferences = indexesInDatabase.Intersect(indexesInSchema, StringComparer.InvariantCultureIgnoreCase);
foreach (var index in validIndexDifferences)
{
result.ValidConstraints.Add(index);
}
var invalidIndexDifferences =
indexesInDatabase.Except(indexesInSchema, StringComparer.InvariantCultureIgnoreCase)
.Union(indexesInSchema.Except(indexesInDatabase, StringComparer.InvariantCultureIgnoreCase));
foreach (var index in invalidIndexDifferences)
{
result.Errors.Add(new Tuple <string, string>("Constraint", index));
}
}
/// <summary>
/// Validates the schema of the current database
/// </summary>
public DatabaseSchemaResult ValidateSchema()
{
var result = new DatabaseSchemaResult();
foreach (var item in OrderedTables.OrderBy(x => x.Key))
{
var tableDefinition = DefinitionFactory.GetTableDefinition(item.Value);
result.TableDefinitions.Add(tableDefinition);
}
//Check tables in configured database against tables in schema
var tablesInDatabase = SqlSyntaxContext.SqlSyntaxProvider.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);
foreach (var tableName in validTableDifferences)
{
result.ValidTables.Add(tableName);
}
var invalidTableDifferences = tablesInDatabase.Except(tablesInSchema);
foreach (var tableName in invalidTableDifferences)
{
result.Errors.Add(new Tuple <string, string>("Table", tableName));
}
//Check columns in configured database against columns in schema
var columnsInDatabase = SqlSyntaxContext.SqlSyntaxProvider.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);
foreach (var column in validColumnDifferences)
{
result.ValidColumns.Add(column);
}
var invalidColumnDifferences = columnsPerTableInDatabase.Except(columnsPerTableInSchema);
foreach (var column in invalidColumnDifferences)
{
result.Errors.Add(new Tuple <string, string>("Column", column));
}
//MySql doesn't conform to the "normal" naming of constraints, so there is currently no point in doing these checks.
//NOTE: At a later point we do other checks for MySql, but ideally it should be necessary to do special checks for different providers.
if (SqlSyntaxContext.SqlSyntaxProvider is MySqlSyntaxProvider)
{
return(result);
}
//Check constraints in configured database against constraints in schema
var constraintsInDatabase = SqlSyntaxContext.SqlSyntaxProvider.GetConstraintsPerColumn(_database).DistinctBy(x => x.Item3).ToList();
var foreignKeysInDatabase = constraintsInDatabase.Where(x => x.Item3.StartsWith("FK_")).Select(x => x.Item3).ToList();
var primaryKeysInDatabase = constraintsInDatabase.Where(x => x.Item3.StartsWith("PK_")).Select(x => x.Item3).ToList();
var indexesInDatabase = constraintsInDatabase.Where(x => x.Item3.StartsWith("IX_")).Select(x => x.Item3).ToList();
var unknownConstraintsInDatabase =
constraintsInDatabase.Where(
x =>
x.Item3.StartsWith("FK_") == false && x.Item3.StartsWith("PK_") == false &&
x.Item3.StartsWith("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)).ToList();
var indexesInSchema = result.TableDefinitions.SelectMany(x => x.Indexes.Select(y => y.Name)).ToList();
//Add valid and invalid foreign key differences to the result object
foreach (var unknown in unknownConstraintsInDatabase)
{
if (foreignKeysInSchema.Contains(unknown) || primaryKeysInSchema.Contains(unknown) || indexesInSchema.Contains(unknown))
{
result.ValidConstraints.Add(unknown);
}
else
{
result.Errors.Add(new Tuple <string, string>("Unknown", unknown));
}
}
var validForeignKeyDifferences = foreignKeysInDatabase.Intersect(foreignKeysInSchema);
foreach (var foreignKey in validForeignKeyDifferences)
{
result.ValidConstraints.Add(foreignKey);
}
var invalidForeignKeyDifferences = foreignKeysInDatabase.Except(foreignKeysInSchema);
foreach (var foreignKey in invalidForeignKeyDifferences)
{
result.Errors.Add(new Tuple <string, string>("Constraint", foreignKey));
}
//Add valid and invalid primary key differences to the result object
var validPrimaryKeyDifferences = primaryKeysInDatabase.Intersect(primaryKeysInSchema);
foreach (var primaryKey in validPrimaryKeyDifferences)
{
result.ValidConstraints.Add(primaryKey);
}
var invalidPrimaryKeyDifferences = primaryKeysInDatabase.Except(primaryKeysInSchema);
foreach (var primaryKey in invalidPrimaryKeyDifferences)
{
result.Errors.Add(new Tuple <string, string>("Constraint", primaryKey));
}
//Add valid and invalid index differences to the result object
var validIndexDifferences = indexesInDatabase.Intersect(indexesInSchema);
foreach (var index in validIndexDifferences)
{
result.ValidConstraints.Add(index);
}
var invalidIndexDifferences = indexesInDatabase.Except(indexesInSchema);
foreach (var index in invalidIndexDifferences)
{
result.Errors.Add(new Tuple <string, string>("Constraint", index));
}
return(result);
}
internal DatabaseSchemaResult ValidateDatabaseSchema()
{
if (_configured == false || (string.IsNullOrEmpty(_connectionString) || string.IsNullOrEmpty(ProviderName)))
return new DatabaseSchemaResult();
if (_result == null)
{
var database = new UmbracoDatabase(_connectionString, ProviderName);
var dbSchema = new DatabaseSchemaCreation(database);
_result = dbSchema.ValidateSchema();
}
return _result;
}
