public async Task<ValidationResponse> IsValidSchemaAsync(Parameters parameters)
{
//Columns type, name matching
var sqlQueryColumns =
$"SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE TABLE_NAME = '{parameters.table1}' " +
$"EXCEPT SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM[INFORMATION_SCHEMA].[COLUMNS] WHERE TABLE_NAME = '{parameters.table2}'";
var colsResult = await _sqlCommandExecutor.ExecuteAsync(sqlQueryColumns);
//Primary key matching
var sqlQueryPk =
"SELECT KU.table_name, column_name " +
"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC " +
"INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU " +
"ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME " +
$"AND KU.table_name IN ('{parameters.table1}', '{parameters.table2}') ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;";
var res = await _sqlCommandExecutor.ExecuteAsync(sqlQueryPk);
var validationResponse = new ValidationResponse(){IsValid = true};
if (colsResult.Any())
{
validationResponse.IsValid = false;
validationResponse.ReasonPhrase = "Two tables do not share same schema";
return validationResponse;
}
var table1Pks = res.Where(r => ((string)r["table_name"]).Equals(parameters.table1, StringComparison.InvariantCultureIgnoreCase)).Select(i => (string)i["column_name"]);
var table2Pks = res.Where(r => ((string)r["table_name"]).Equals(parameters.table2, StringComparison.InvariantCultureIgnoreCase)).Select(i => (string)i["column_name"]);
if (!table1Pks.Contains(parameters.primarykey, StringComparer.CurrentCultureIgnoreCase) || !table2Pks.Contains(parameters.primarykey, StringComparer.CurrentCultureIgnoreCase))
{
validationResponse.IsValid = false;
validationResponse.ReasonPhrase = "Primary Key is invalid";
}
return validationResponse;
}
