public void FixtureSetup() { var connection = ConfigurationManager.ConnectionStrings[MiscConstants.GetEfDatabaseConfigName()].ConnectionString; var allSqlInfo = SqlAllInfo.SqlAllInfoFactory(connection); _sqlInfos = allSqlInfo.TableInfos; }
public void FixtureSetup() { using (var db = new TestEf6SchemaCompareDb()) { var decoder = new Ef6MetadataDecoder(Assembly.GetAssembly(typeof(DataTop))); _efInfos = decoder.GetAllEfTablesWithColInfo(db); var allSqlInfo = SqlAllInfo.SqlAllInfoFactory(db.Database.Connection.ConnectionString); _checker = new EfRelationshipChecker(_efInfos, allSqlInfo, allSqlInfo.TableInfos); //NOTE: we aren't able to filter potentialManyToManyTables } }
/// <summary> /// This compares two SQL databases looking at each table, its columns, its keys and its foreign keys. /// It assumes the first database connection is the reference and the second is the one that should match the reference /// </summary> /// <param name="refDbNameOrConnectionString">Either a full connection string or the name of a connection string in Config file</param> /// <param name="toBeCheckDbNameOrConnectionString">Either a full connection string or the name of a to connection string in Config file</param> /// <returns></returns> public ISuccessOrErrors CompareSqlToSql(string refDbNameOrConnectionString, string toBeCheckDbNameOrConnectionString) { var refDbConnection = refDbNameOrConnectionString.GetConnectionStringAndCheckValid(); var toBeCheckDbConnection = toBeCheckDbNameOrConnectionString.GetConnectionStringAndCheckValid(); var refDatabaseName = refDbConnection.GetDatabaseNameFromConnectionString(); var toBeCheckDatabaseName = toBeCheckDbConnection.GetDatabaseNameFromConnectionString(); var refSqlData = SqlAllInfo.SqlAllInfoFactory(refDbConnection); var toBeCheckSqlData = SqlAllInfo.SqlAllInfoFactory(toBeCheckDbConnection); var comparer = new SqlCompare(refDatabaseName, toBeCheckDatabaseName, _sqlTableNamesToIgnore, _showMismatchedIndexsAsErrors); return comparer.CompareSqlToSql(refSqlData, toBeCheckSqlData); }
public EfRelationshipChecker(IEnumerable <EfTableInfo> efInfos, SqlAllInfo allSqlInfo, IList <SqlTableInfo> potentialManyToManyTables) { _efInfosDict = efInfos.ToDictionary(x => x.ClrClassType); _allSqlInfo = allSqlInfo; _sqlInfoDict = allSqlInfo.TableInfos.ToDictionary(x => x.CombinedName); _potentialManyToManyTablesDict = potentialManyToManyTables.ToDictionary(x => x.CombinedName); //This dictionary allows us to backtrack the foreign keys to the correct many-to-many table _foreignKeysGroupByParentTableName = allSqlInfo.ForeignKeys .GroupBy(key => key.ParentTableNameWithScheme) .Select(x => new Tuple <string, List <string> >(x.Key, x.Select(y => FormatHelpers.CombineTableAndColumnNames(y.ReferencedTableName, y.ReferencedColName)).OrderBy(y => y).ToList())).ToList(); }
/// <summary> /// This creates a new database based on the DbContext you give it, but with a new name consisting of the orginial name with /// ".EfGenerated" on the end. It then proceeds to check your SQL database against the EF Generated database /// NOTE: This sets a null database initializer on the database. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dbContext"></param> /// <param name="refDbNameOrConnectionString"></param> /// <returns></returns> public ISuccessOrErrors CompareEfGeneratedSqlToSql<T>(T dbContext, string refDbNameOrConnectionString) where T : DbContext, new() { var refDbConnection = FormEfGeneratedConnectionString(dbContext); var toBeCheckDbConnection = refDbNameOrConnectionString.GetConnectionStringAndCheckValid(); //This creates the EF database with the new name DatabaseCreators.DeleteAndCreateEfDatabase<T>(refDbConnection); var refDatabaseName = refDbConnection.GetDatabaseNameFromConnectionString(); var toBeCheckDatabaseName = toBeCheckDbConnection.GetDatabaseNameFromConnectionString(); var refSqlData = SqlAllInfo.SqlAllInfoFactory(refDbConnection); var toBeCheckSqlData = SqlAllInfo.SqlAllInfoFactory(toBeCheckDbConnection); var comparer = new SqlCompare(refDatabaseName, toBeCheckDatabaseName, _sqlTableNamesToIgnore, _showMismatchedIndexsAsErrors); return comparer.CompareSqlToSql(refSqlData, toBeCheckSqlData); }
public void CompareStart(string sqlDbRefString, string sqlConnectionString) { if (sqlConnectionString == null) { throw new ArgumentNullException("sqlConnectionString"); } if (CompareStartCalled) { throw new InvalidOperationException("You have already called CompareStart."); } _sqlDbRefString = sqlDbRefString; _allSqlInfo = SqlAllInfo.SqlAllInfoFactory(sqlConnectionString); _sqlInfoDict = _allSqlInfo.TableInfos.ToDictionary(x => x.CombinedName); _comparer = new EfCompare(sqlDbRefString, _sqlInfoDict); }
private ISuccessOrErrors CheckForeignKey(SqlAllInfo refSqlData, SqlAllInfo toBeCheckSqlData) { var status = SuccessOrErrors.Success("All Ok"); var foreignKeyDict = toBeCheckSqlData.ForeignKeys.ToDictionary(x => x.ToString()); //now we check the foreign keys foreach (var foreignKey in refSqlData.ForeignKeys) { if (!foreignKeyDict.ContainsKey(foreignKey.ToString())) { status.AddSingleError( "Missing Foreign key: The '{0}' SQL database has a foreign key {1}, which is missing in the '{2}' database.", _refDatabaseName, foreignKey.ToString(), _toBeCheckDatabaseName); } else { var foreignKey2 = foreignKeyDict[foreignKey.ToString()]; foreignKeyDict.Remove(foreignKey.ToString()); if (foreignKey.DeleteAction != foreignKey2.DeleteAction) { status.AddSingleError( "Foreign Key Delete Action: The [{0}] database has a foreign key {1} that has delete action of {2}, while database [{3}] has delete action of {4}.", _refDatabaseName, foreignKey.ToString(), foreignKey.DeleteAction, _toBeCheckDatabaseName, foreignKey2.DeleteAction); } } } if (foreignKeyDict.Any()) { foreach (var missingFKey in foreignKeyDict.Values) { status.AddWarning("The '{0}' database has a foreign key {1}, which the '{2}' database did not have.", _toBeCheckDatabaseName, missingFKey.ToString(), _refDatabaseName); } } return(status); }
public ISuccessOrErrors CompareEfWithSql(IList <EfTableInfo> efInfos, SqlAllInfo allSqlInfo) { var status = SuccessOrErrors.Success("All Ok"); //first we compare the ef table columns with the SQL table columns foreach (var efInfo in efInfos) { if (!_sqlInfoDict.ContainsKey(efInfo.CombinedName)) { status.AddSingleError( "Missing Table: The SQL {0} does not contain a table called {1}. Needed by EF class {2}.", _sqlDbRefString, efInfo.CombinedName, efInfo.ClrClassType.Name); } else { //has table, so compare the columns/properties var sqlTableInfo = _sqlInfoDict[efInfo.CombinedName]; _sqlInfoDict.Remove(efInfo.CombinedName); //we create a dict, which we check. As we find columns we remove them var sqlColsDict = sqlTableInfo.ColumnInfos.ToDictionary(x => x.ColumnName); foreach (var clrCol in efInfo.NormalCols) { if (!sqlColsDict.ContainsKey(clrCol.SqlColumnName)) { status.AddSingleError( "Missing Column: The SQL {0} table {1} does not contain a column called {2}. Needed by EF class {3}.", _sqlDbRefString, efInfo.CombinedName, clrCol.SqlColumnName, efInfo.ClrClassType.Name); } else { //check the columns match var sqlCol = sqlColsDict[clrCol.SqlColumnName]; sqlColsDict.Remove(clrCol.SqlColumnName); //remove it as it has been used status.Combine(CheckColumn(sqlCol, clrCol, efInfo.CombinedName)); } } //At the end we check if any sql columns are left if (sqlColsDict.Any()) { foreach (var missingCol in sqlColsDict.Values) { status.AddWarning("SQL {0} table {1} has a column called {2} (.NET type {3}) that EF does not access.", _sqlDbRefString, efInfo.CombinedName, missingCol.ColumnName, missingCol.SqlTypeName.SqlToClrType(missingCol.IsNullable)); } } } } //now we compare the EF relationships with the SQL foreign keys //we do this here because we now have the tables that wren't mentioned in EF, //which are the tables that EF will automatically add to handle many-many relationships. var relChecker = new EfRelationshipChecker(efInfos, allSqlInfo, _sqlInfoDict.Values.ToList()); foreach (var efInfo in efInfos) { //now we check the relationships foreach (var relationCol in efInfo.RelationshipCols) { var relStatus = relChecker.CheckEfRelationshipToSql(efInfo, relationCol); status.Combine(relStatus); if (relStatus.IsValid && relStatus.Result != null) { //It has found a many-to-many table which we need to remove so that it does not show a warning at the end _sqlInfoDict.Remove(relStatus.Result); } } } return(status); }
/// <summary> /// This compares two sets of SQL data looking at each table, its columns, its keys and its foreign keys. /// </summary> /// <param name="refSqlData">reference database sql data</param> /// <param name="toBeCheckSqlData">sql data of the database that is to be checked as matching the reference data</param> /// <returns></returns> public ISuccessOrErrors CompareSqlToSql(SqlAllInfo refSqlData, SqlAllInfo toBeCheckSqlData) { var status = SuccessOrErrors.Success("All Ok"); var sqlTable2Dict = toBeCheckSqlData.TableInfos.ToDictionary(x => x.CombinedName); foreach (var sqlTable in refSqlData.TableInfos) { if (!sqlTable2Dict.ContainsKey(sqlTable.CombinedName)) { if (!_tablesToIgnore.Contains(sqlTable.TableName)) { //only mark as an error if the table isn't in the ignore list status.AddSingleError( "Missing Table: The '{0}' SQL database has a table called {1}, which is missing in the '{2}' database.", _refDatabaseName, sqlTable.CombinedName, _toBeCheckDatabaseName); } } else { //has table, so compare the columns/properties var sqlTable2Info = sqlTable2Dict[sqlTable.CombinedName]; sqlTable2Dict.Remove(sqlTable.CombinedName); //we create a dict for columns in SECOND db, which we check. As we find columns we remove them var sqlColsDict = sqlTable2Info.ColumnInfos.ToDictionary(x => x.ColumnName); foreach (var col in sqlTable.ColumnInfos) { if (!sqlColsDict.ContainsKey(col.ColumnName)) { status.AddSingleError( "Missing Column: The SQL table {0} in second database does not contain a column called {1}.", sqlTable.CombinedName, col.ColumnName); } else { //check the columns match var colToCheck = sqlColsDict[col.ColumnName]; sqlColsDict.Remove(col.ColumnName); //remove it as it has been used status.Combine(CheckSqlColumn(col, colToCheck, sqlTable.CombinedName)); } } //At the end we check if any sql columns are left if (sqlColsDict.Any()) { foreach (var missingCol in sqlColsDict.Values) { status.AddWarning("Extra Column: The '{0}' database SQL table {1} has a column called {2} (type {3}), which database '{4}' did not have.", _toBeCheckDatabaseName, sqlTable.CombinedName, missingCol.ColumnName, missingCol.SqlTypeName, _refDatabaseName); } } } } //now see what SQL tables haven't been mentioned if (sqlTable2Dict.Any()) { foreach (var unusedTable in sqlTable2Dict.Values.Where(x => !_tablesToIgnore.Contains(x.TableName))) { status.AddWarning("Extra Table: SQL database '{0}', table {1} table contained an extra table, {1}", _refDatabaseName, unusedTable.CombinedName); } } //Now check the foreign keys status.Combine(CheckForeignKey(refSqlData, toBeCheckSqlData)); //finally compare non primary-key indexes status.Combine(CheckAllIndexes(refSqlData, toBeCheckSqlData)); return(status); }
//------------------------------------------------------------------------------- //private helpers private ISuccessOrErrors CheckAllIndexes(SqlAllInfo refSqlData, SqlAllInfo toBeCheckSqlData) { var status = SuccessOrErrors.Success("All Ok"); //Note: There can be multiple indexes on the same table+column. We therefore group indexes by the scheme.tabel.column to make feedback useful var toBeCheckedIndexDict = toBeCheckSqlData.Indexes .GroupBy(x => x.CombinedName).ToDictionary(x => x.Key, v => v.ToList()); //we also do not check on tables that we will ignore foreach (var refIndexGroup in refSqlData.Indexes.Where(x => !_tablesToIgnore.Contains(x.TableName)).GroupBy(x => x.CombinedName)) { if (!toBeCheckedIndexDict.ContainsKey(refIndexGroup.Key)) { foreach (var eachKey in refIndexGroup) { SetAppropriateIndexError(status, "Missing Index: The '{0}' SQL database has an index {1}, which is missing in the '{2}' database.", _refDatabaseName, eachKey.ToString(), _toBeCheckDatabaseName); } } else { var listRef = refIndexGroup.ToList(); var listToCheck = toBeCheckedIndexDict[refIndexGroup.Key].ToList(); toBeCheckedIndexDict.Remove(refIndexGroup.Key); if (listRef.Count() == 1 && listToCheck.Count() == 1) { //simple case status.Combine(CheckSpecificIndex(listRef.First(), listToCheck.First())); } else if (listRef.Count() == 1 && !listToCheck.Any()) { //simple case - missing in toBeChecked SetAppropriateIndexError(status, "Missing Index: The '{0}' database has an index {1}, which the '{2}' database did not have.", _refDatabaseName, listRef.ToString(), _toBeCheckDatabaseName); } else { if (listRef.Any() && listToCheck.Any() && listRef.Last().IsPrimaryIndex&& listToCheck.Last().IsPrimaryIndex) { //normal case is that both have a primary key, so deal with this and take out of list status.Combine(CheckSpecificIndex(listRef.Last(), listToCheck.Last())); listRef.Remove(listRef.Last()); listToCheck.Remove(listToCheck.Last()); } //we cheat a bit here, in that we assume these are only two multiple indexes on a column: primary key and one other //we assume that the indexes are in the right order to check (because of the order by in the sql request) //it will work with other combinations, but might not give such good error messages. var maxIndex = Math.Max(listRef.Count(), listToCheck.Count()); for (int i = 0; i < maxIndex; i++) { if (i >= listToCheck.Count()) { SetAppropriateIndexError(status, "Missing Index: The '{0}' database has an index {1}, which the '{2}' database did not have.", _refDatabaseName, listRef[i].ToString(), _toBeCheckDatabaseName); } else if (i >= listRef.Count()) { //Note: this is warning, as an extra index in the toBeChecked database isn't an error status.AddWarning( "Missing Index: The '{0}' database has an index {1}, which the '{2}' database did not have.", _toBeCheckDatabaseName, listToCheck[i].ToString(), _refDatabaseName); } else { //they both have a index, so check it status.Combine(CheckSpecificIndex(listRef[i], listToCheck[i])); } } } } } if (toBeCheckedIndexDict.Any()) { foreach (var missingIndex in toBeCheckedIndexDict.Values.SelectMany(x => x).Where(x => !_tablesToIgnore.Contains(x.TableName))) { //Note: this a warning as an extra index isn't an error status.AddWarning( "Missing Index: The '{0}' database has an index {1}, which the '{2}' database did not have.", _toBeCheckDatabaseName, missingIndex.ToString(), _refDatabaseName); } } return(status); }