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