/// <summary>
/// The load merchello data.
/// </summary>
/// <param name="result">
/// The result.
/// </param>
private void LoadMerchelloData(MerchelloDatabaseSchemaResult result)
{
var sqlSettings = new Sql();
sqlSettings.Select("*")
.From <StoreSettingDto>(_sqlSyntax);
result.StoreSettings = _database.Fetch <StoreSettingDto>(sqlSettings);
var sqlTypeFields = new Sql();
sqlSettings.Select("*")
.From <TypeFieldDto>(_sqlSyntax);
result.TypeFields = _database.Fetch <TypeFieldDto>(sqlTypeFields);
}
/// <summary>
/// Validates the schema of the current database
/// </summary>
/// <returns>
/// The <see cref="MerchelloDatabaseSchemaResult"/>.
/// </returns>
public MerchelloDatabaseSchemaResult ValidateSchema()
{
var result = new MerchelloDatabaseSchemaResult
{
DbIndexDefinitions = SqlSyntaxContext.SqlSyntaxProvider.GetDefinedIndexes(_database)
.Select(x => new DbIndexDefinition()
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray()
};
//get the db index defs
foreach (var item in OrderedTables.OrderBy(x => x.Key))
{
var tableDefinition = DefinitionFactory.GetTableDefinition(item.Value);
result.TableDefinitions.Add(tableDefinition);
}
ValidateDbTables(result);
ValidateDbColumns(result);
ValidateDbIndexes(result);
ValidateDbConstraints(result);
if (
!result.MerchelloErrors.Any(
x => x.Item1.Equals("Table") && x.Item2.InvariantEquals("merchStoreSetting")))
{
// catch this so it doesn't kick off on an install
LoadMerchelloData(result);
}
else
{
result.StoreSettings = Enumerable.Empty <StoreSettingDto>();
result.TypeFields = Enumerable.Empty <TypeFieldDto>();
}
return(result);
}
private void ValidateDbTables(MerchelloDatabaseSchemaResult result)
{
//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, 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(MerchelloDatabaseSchemaResult result)
{
//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, 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(MerchelloDatabaseSchemaResult 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>
/// The ensure migration key.
/// </summary>
/// <param name="schemaResult">
/// The schema result.
/// </param>
/// <returns>
/// The <see cref="string"/>.
/// </returns>
private string EnsureMigrationKey(MerchelloDatabaseSchemaResult schemaResult)
{
var migrationSetting =
schemaResult.StoreSettings.FirstOrDefault(
x => x.Key == Constants.StoreSettingKeys.MigrationKey);
var nullSettingKey = Guid.NewGuid().ToString();
if (migrationSetting == null)
{
this.InsertMigrationKey(nullSettingKey);
}
var migrationKey = migrationSetting != null ?
string.IsNullOrEmpty(migrationSetting.Value) ? nullSettingKey : migrationSetting.Value :
nullSettingKey;
Guid validGuid;
if (Guid.TryParse(migrationKey, out validGuid))
if (validGuid.Equals(Guid.Empty))
{
// reset the key
nullSettingKey = Guid.NewGuid().ToString();
var dto = migrationSetting;
if (dto != null)
{
dto.Value = nullSettingKey;
_database.Update(dto);
migrationKey = nullSettingKey;
}
}
return migrationKey;
}
private void ValidateDbTables(MerchelloDatabaseSchemaResult result)
{
//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, 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 ValidateDbIndexes(MerchelloDatabaseSchemaResult 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));
}
}
private void ValidateDbConstraints(MerchelloDatabaseSchemaResult 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 (SqlSyntaxContext.SqlSyntaxProvider is MySqlSyntaxProvider)
return;
//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.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
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));
}
}
private void ValidateDbColumns(MerchelloDatabaseSchemaResult result)
{
//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, 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));
}
}
/// <summary>
/// The load merchello data.
/// </summary>
/// <param name="result">
/// The result.
/// </param>
private void LoadMerchelloData(MerchelloDatabaseSchemaResult result)
{
var sqlSettings = new Sql();
sqlSettings.Select("*")
.From<StoreSettingDto>();
result.StoreSettings = _database.Fetch<StoreSettingDto>(sqlSettings);
var sqlTypeFields = new Sql();
sqlSettings.Select("*")
.From<TypeFieldDto>();
result.TypeFields = _database.Fetch<TypeFieldDto>(sqlTypeFields);
}
/// <summary>
/// Validates the schema of the current database
/// </summary>
public MerchelloDatabaseSchemaResult ValidateSchema()
{
var result = new MerchelloDatabaseSchemaResult
{
DbIndexDefinitions = SqlSyntaxContext.SqlSyntaxProvider.GetDefinedIndexes(_database)
.Select(x => new DbIndexDefinition()
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray()
};
//get the db index defs
foreach (var item in OrderedTables.OrderBy(x => x.Key))
{
var tableDefinition = DefinitionFactory.GetTableDefinition(item.Value);
result.TableDefinitions.Add(tableDefinition);
}
ValidateDbTables(result);
ValidateDbColumns(result);
ValidateDbIndexes(result);
ValidateDbConstraints(result);
if (!result.MerchelloErrors.Any(x => x.Item1.Equals("Table") && x.Item2.InvariantEquals("merchStoreSetting")))
{
// catch this so it doesn't kick off on an install
LoadMerchelloData(result);
}
return result;
}
private void ValidateDbConstraints(MerchelloDatabaseSchemaResult 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 (_sqlSyntax is MySqlSyntaxProvider)
{
return;
}
//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 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
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>
/// The ensure migration key.
/// </summary>
/// <param name="schemaResult">
/// The schema result.
/// </param>
/// <returns>
/// The <see cref="string"/>.
/// </returns>
private string EnsureMigrationKey(MerchelloDatabaseSchemaResult schemaResult)
{
var migrationSetting =
schemaResult.StoreSettings.FirstOrDefault(
x => x.Key == Constants.StoreSettingKeys.MigrationKey);
var nullSettingKey = Guid.NewGuid().ToString();
if (migrationSetting == null)
{
this.InsertMigrationKey(nullSettingKey);
}
var migrationKey = migrationSetting != null ?
string.IsNullOrEmpty(migrationSetting.Value) ? nullSettingKey : migrationSetting.Value :
nullSettingKey;
// Saves a previously saved migration key without a value
if (migrationKey.Equals(nullSettingKey))
{
var setting =
MerchelloContext.Current.Services.StoreSettingService.GetByKey(
Constants.StoreSettingKeys.MigrationKey);
if (setting != null) setting.Value = migrationKey;
MerchelloContext.Current.Services.StoreSettingService.Save(setting);
}
Guid validGuid;
if (Guid.TryParse(migrationKey, out validGuid))
if (validGuid.Equals(Guid.Empty))
{
// reset the key
nullSettingKey = Guid.NewGuid().ToString();
var dto = migrationSetting;
if (dto != null)
{
dto.Value = nullSettingKey;
_database.Update(dto);
migrationKey = nullSettingKey;
}
}
return migrationKey;
}
