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));
}
}
