public SQL(SqlLogic logical, SqlCompare compare, string name, object value)
{
_logical = logical;
_compare = compare;
_name = name;
_value = value;
}
public SQL(SqlLogic logical, SqlCompare compare, string name, object value)
{
_logical = logical;
_compare = compare;
_name = name;
_value = value;
}
public void Test01CompareSameMockDataOk()
{
//SETUP
var comparer = new SqlCompare("RefUnitTest", "ToBeCheckUnitTest", "", true);
var sqlData = LoadJsonHelpers.DeserializeData <SqlAllInfo>("SqlAllInfo01*.json");
//EXECUTE
var status = comparer.CompareSqlToSql(sqlData, sqlData);
//VERIFY
status.ShouldBeValid();
}
/// <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 void Test17CompareMockDataRemoveColumnInRefOk()
{
//SETUP
var comparer = new SqlCompare("RefUnitTest", "ToBeCheckUnitTest", "", true);
var sqlData1 = LoadJsonHelpers.DeserializeObjectWithSingleRemoval <SqlAllInfo>("SqlAllInfo01*.json", "TableInfos", 0, "ColumnInfos", 0);
var sqlData2 = LoadJsonHelpers.DeserializeData <SqlAllInfo>("SqlAllInfo01*.json");
//EXECUTE
var status = comparer.CompareSqlToSql(sqlData1, sqlData2);
//VERIFY
status.ShouldBeValid();
string.Join(",", status.Warnings).ShouldEqual("Warning: Extra Column: The 'ToBeCheckUnitTest' database SQL table [dbo].[DataTop] has a column called DataTopId (type int), which database 'RefUnitTest' did not have.", string.Join(",", status.Warnings));
}
public void Test12CompareMockDataChangePrimaryKeyOk()
{
//SETUP
var comparer = new SqlCompare("RefUnitTest", "ToBeCheckUnitTest", "", true);
var sqlData1 = LoadJsonHelpers.DeserializeData <SqlAllInfo>("SqlAllInfo01*.json");
var sqlData2 = LoadJsonHelpers.DeserializeObjectWithSingleAlteration <SqlAllInfo>("SqlAllInfo01*.json", false, "TableInfos", 0, "ColumnInfos", 0, "IsPrimaryKey");
//EXECUTE
var status = comparer.CompareSqlToSql(sqlData1, sqlData2);
//VERIFY
status.ShouldBeValid(false);
status.GetAllErrors().ShouldEqual("Primary Key: The SQL column [dbo].[DataTop].DataTopId primary key settings don't match. 'RefUnitTest' db says is a key, 'ToBeCheckUnitTest' db says it is NOT a key.", status.GetAllErrors());
status.HasWarnings.ShouldEqual(false, string.Join(",", status.Warnings));
}
public void Test10CompareMockDataChangeColumnNameOk()
{
//SETUP
var comparer = new SqlCompare("RefUnitTest", "ToBeCheckUnitTest", "", true);
var sqlData1 = LoadJsonHelpers.DeserializeData <SqlAllInfo>("SqlAllInfo01*.json");
var sqlData2 = LoadJsonHelpers.DeserializeObjectWithSingleAlteration <SqlAllInfo>("SqlAllInfo01*.json", "BadColName", "TableInfos", 0, "ColumnInfos", 0, "ColumnName");
//EXECUTE
var status = comparer.CompareSqlToSql(sqlData1, sqlData2);
//VERIFY
status.ShouldBeValid(false);
status.GetAllErrors().ShouldEqual("Missing Column: The SQL table [dbo].[DataTop] in second database does not contain a column called DataTopId.", status.GetAllErrors());
string.Join(",", status.Warnings).ShouldEqual("Warning: Extra Column: The 'ToBeCheckUnitTest' database SQL table [dbo].[DataTop] has a column called BadColName (type int), which database 'RefUnitTest' did not have.", string.Join(",", status.Warnings));
}
public void Test41CompareMockDataChangeIndexRemovePrimaryKeyInSetOk()
{
//SETUP
var comparer = new SqlCompare("RefUnitTest", "ToBeCheckUnitTest", "", true);
var sqlData1 = LoadJsonHelpers.DeserializeData <SqlAllInfo>("SqlAllInfo01*.json");
var sqlData2 = LoadJsonHelpers.DeserializeObjectWithSingleRemoval <SqlAllInfo>("SqlAllInfo01*.json", "Indexes", 1);
//EXECUTE
var status = comparer.CompareSqlToSql(sqlData1, sqlData2);
//VERIFY
status.ShouldBeValid(false);
status.GetAllErrors().ShouldEqual("Missing Index: The 'RefUnitTest' SQL database has an index [dbo].[DataChild].DataChildId: (primary key, clustered, unique), which is missing in the 'ToBeCheckUnitTest' database.", status.GetAllErrors());
status.HasWarnings.ShouldEqual(false, string.Join(",", status.Warnings));
}
public void Test05CompareMockDataChangeTableNameOk()
{
//SETUP
var comparer = new SqlCompare("RefUnitTest", "ToBeCheckUnitTest", "", true);
var sqlData1 = LoadJsonHelpers.DeserializeData <SqlAllInfo>("SqlAllInfo01*.json");
var sqlData2 = LoadJsonHelpers.DeserializeObjectWithSingleAlteration <SqlAllInfo>("SqlAllInfo01*.json", "NewDataName", "TableInfos", 0, "TableName");
//EXECUTE
var status = comparer.CompareSqlToSql(sqlData1, sqlData2);
//VERIFY
status.ShouldBeValid(false);
status.GetAllErrors().ShouldEqual("Missing Table: The 'RefUnitTest' SQL database has a table called [dbo].[DataTop], which is missing in the 'ToBeCheckUnitTest' database.", status.GetAllErrors());
string.Join(",", status.Warnings).ShouldEqual("Warning: Extra Table: SQL database 'RefUnitTest', table [dbo].[NewDataName] table contained an extra table, [dbo].[NewDataName]", string.Join(",", status.Warnings));
}
public void Test35CompareMockDataChangeIndexHasIdentityOk()
{
//SETUP
var comparer = new SqlCompare("RefUnitTest", "ToBeCheckUnitTest", "", true);
var sqlData1 = LoadJsonHelpers.DeserializeData <SqlAllInfo>("SqlAllInfo01*.json");
var sqlData2 = LoadJsonHelpers.DeserializeObjectWithSingleAlteration <SqlAllInfo>("SqlAllInfo01*.json", true, "Indexes", 0, "IsIdentity");
//EXECUTE
var status = comparer.CompareSqlToSql(sqlData1, sqlData2);
//VERIFY
status.ShouldBeValid(false);
status.GetAllErrors().ShouldEqual("Index Mismatch: The 'RefUnitTest' SQL database, index on [dbo].[DataChild].DataTopId is NOT an identity column, while the index on the same table.column in SQL database ToBeCheckUnitTest is an identity column.", status.GetAllErrors());
status.HasWarnings.ShouldEqual(false, string.Join(",", status.Warnings));
}
public void Test31CompareMockDataChangeIndexColumnNameOk()
{
//SETUP
var comparer = new SqlCompare("RefUnitTest", "ToBeCheckUnitTest", "", true);
var sqlData1 = LoadJsonHelpers.DeserializeData <SqlAllInfo>("SqlAllInfo01*.json");
var sqlData2 = LoadJsonHelpers.DeserializeObjectWithSingleAlteration <SqlAllInfo>("SqlAllInfo01*.json", "BadName", "Indexes", 0, "ColumnName");
//EXECUTE
var status = comparer.CompareSqlToSql(sqlData1, sqlData2);
//VERIFY
status.ShouldBeValid(false);
status.GetAllErrors().ShouldEqual("Missing Index: The 'RefUnitTest' SQL database has an index [dbo].[DataChild].DataTopId: (not primary key, not clustered, not unique), which is missing in the 'ToBeCheckUnitTest' database.", status.GetAllErrors());
string.Join(",", status.Warnings).ShouldEqual("Warning: Missing Index: The 'ToBeCheckUnitTest' database has an index [dbo].[DataChild].BadName: (not primary key, not clustered, not unique), which the 'RefUnitTest' database did not have.", string.Join(",", status.Warnings));
}
public void Test24CompareMockDataChangeForeignKeyReferencedColNameOk()
{
//SETUP
var comparer = new SqlCompare("RefUnitTest", "ToBeCheckUnitTest", "", true);
var sqlData1 = LoadJsonHelpers.DeserializeData <SqlAllInfo>("SqlAllInfo01*.json");
var sqlData2 = LoadJsonHelpers.DeserializeObjectWithSingleAlteration <SqlAllInfo>("SqlAllInfo01*.json", "BadName", "ForeignKeys", 0, "DeleteAction");
//EXECUTE
var status = comparer.CompareSqlToSql(sqlData1, sqlData2);
//VERIFY
status.ShouldBeValid(false);
status.GetAllErrors().ShouldEqual("Foreign Key Delete Action: The [RefUnitTest] database has a foreign key Parent: DataChild.DataTopId, Referenced: DataTop.DataTopId that has delete action of CASCADE, while database [ToBeCheckUnitTest] has delete action of BadName.", status.GetAllErrors());
status.HasWarnings.ShouldEqual(false, string.Join(",", status.Warnings));
}
public void Test23CompareMockDataChangeForeignKeyReferencedColNameOk()
{
//SETUP
var comparer = new SqlCompare("RefUnitTest", "ToBeCheckUnitTest", "", true);
var sqlData1 = LoadJsonHelpers.DeserializeData <SqlAllInfo>("SqlAllInfo01*.json");
var sqlData2 = LoadJsonHelpers.DeserializeObjectWithSingleAlteration <SqlAllInfo>("SqlAllInfo01*.json", "BadName", "ForeignKeys", 0, "ReferencedColName");
//EXECUTE
var status = comparer.CompareSqlToSql(sqlData1, sqlData2);
//VERIFY
status.ShouldBeValid(false);
status.GetAllErrors().ShouldEqual("Missing Foreign key: The 'RefUnitTest' SQL database has a foreign key Parent: DataChild.DataTopId, Referenced: DataTop.DataTopId, which is missing in the 'ToBeCheckUnitTest' database.", status.GetAllErrors());
string.Join(",", status.Warnings).ShouldEqual("Warning: The 'ToBeCheckUnitTest' database has a foreign key Parent: DataChild.DataTopId, Referenced: DataTop.BadName, which the 'RefUnitTest' database did not have.", string.Join(",", status.Warnings));
}
public void Test11CompareMockDataChangeColumnSqlTypeOk()
{
//SETUP
var comparer = new SqlCompare("RefUnitTest", "ToBeCheckUnitTest", "", true);
var sqlData1 = LoadJsonHelpers.DeserializeData <SqlAllInfo>("SqlAllInfo01*.json");
var sqlData2 = LoadJsonHelpers.DeserializeObjectWithSingleAlteration <SqlAllInfo>("SqlAllInfo01*.json", "bit", "TableInfos", 0, "ColumnInfos", 0, "SqlTypeName");
//EXECUTE
var status = comparer.CompareSqlToSql(sqlData1, sqlData2);
//VERIFY
status.ShouldBeValid(false);
status.GetAllErrors().ShouldEqual("Column Type: SQL column [dbo].[DataTop].DataTopId type does not match EF. 'RefUnitTest' db type = int, 'ToBeCheckUnitTest' db type = bit.", status.GetAllErrors());
status.HasWarnings.ShouldEqual(false, string.Join(",", status.Warnings));
}
public void Test16CompareMockDataRemoveColumnInToBeCheckedOk()
{
//SETUP
var comparer = new SqlCompare("RefUnitTest", "ToBeCheckUnitTest", "", true);
var sqlData1 = LoadJsonHelpers.DeserializeData <SqlAllInfo>("SqlAllInfo01*.json");
var sqlData2 = LoadJsonHelpers.DeserializeObjectWithSingleRemoval <SqlAllInfo>("SqlAllInfo01*.json", "TableInfos", 0, "ColumnInfos", 0);
//EXECUTE
var status = comparer.CompareSqlToSql(sqlData1, sqlData2);
//VERIFY
status.ShouldBeValid(false);
status.GetAllErrors().ShouldEqual("Missing Column: The SQL table [dbo].[DataTop] in second database does not contain a column called DataTopId.", status.GetAllErrors());
status.HasWarnings.ShouldEqual(false, string.Join(",", status.Warnings));
}
/// <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 static SQL Criterion(string name, object value, SqlCompare compare = SqlCompare.Equals)
{
return(new SQL(compare, name, value));
}
public static SQL Criterion(string name, object value, SqlCompare compare = SqlCompare.Equals)
{
return new SQL(compare, name, value);
}
public static SQL Or(string name, object value, SqlCompare compare = SqlCompare.Equals)
{
return new SQL(SqlLogic.OR, compare, name, value);
}
public static SQL Or(string name, object value, SqlCompare compare = SqlCompare.Equals)
{
return(new SQL(SqlLogic.OR, compare, name, value));
}
