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