public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
TableField t = new TableField();
if (tokens[i] == "(")
{
t.Name = ParenBlockBuilder.ConsumeParenBlock(tokens, ref i);
}
else
{
t.Name = tokens[i++];
}
var on = new On();
var join = new Join();
var where = new Where();
if (!join.Match(tokens[i], tokens, i) &&
!on.Match(tokens[i], tokens, i) &&
!where.Match(tokens[i], tokens, i))
{
var token = tokens[i++];
if (token == "as")
{
token = tokens[i++];
}
t.Alias = token;
}
ret.Select.Tables.Add(t);
}
public override void Decompose(ParsedSql ret)
{
while (ret.Select.Wheres.Any(w => w.CompositesExist()))
{
ret.Select.Wheres.Where(w => w.CompositesExist()).ToList().ForEach(w => { w.Decompose(); });
}
}
private static ParsedSql Parse(List <string> tokens)
{
var ret = new ParsedSql
{
};
var builders = new List <Builders.TokenBuilder>
{
new Builders.Select(),
};
for (int i = 0; i < tokens.Count; i++)
{
foreach (var builder in builders)
{
if (builder.Match(tokens[i], tokens, i))
{
builder.Build(ret, tokens, ref i);
}
}
}
var decomposers = new List <Decomposers.SqlDecomposer>
{
new Decomposers.WhereClauseDecomposer(),
};
foreach (var decomposer in decomposers)
{
decomposer.Decompose(ret);
}
return(ret);
}
public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
var token = tokens[i];
if (operators.Contains(token))
{
_js.Operator = token;
_onLeft = false;
i++;
}
if (Constants.BooleanOperators.Contains(token))
{
_js = new JoinStatement();
i++;
}
else if (_onLeft)
{
_js.LeftClause = token;
i++;
}
else
{
_js.RightClause = token;
ret.Select.Joins.Add(_js);
i++;
}
}
/// <summary>
/// Initializes a new instance of the <see cref="ComparisonTemplatorTests"/> class. Test the
/// <see cref="ComparisonTemplator"/> using a single sql for test and assert. It should be
/// OK that we compare the same sql to itself because that doens't really add to the
/// complexity of the templator's work.
/// </summary>
public ComparisonTemplatorTests(ParseResultValue parseResult, string comment, string sql, List <string> columns)
{
this.parseResult = parseResult;
this.comment = comment;
this.sql = sql;
this.columns = columns;
this.actualResult = ComparableSqlParser.ParseAndValidate(sql);
}
public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
i++;
var bi = new BuilderIterator();
bi.AddBuilder(new GroupBy(), true, true);
bi.AddBuilder(new WhereStatementBuilder());
bi.Build(ret, tokens, ref i);
}
public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
i++;
i++;
// Select
var bi = new BuilderIterator();
bi.AddBuilder(new Comma());
bi.AddBuilder(new GroupByField(), false);
bi.Build(ret, tokens, ref i);
}
public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
i++;
var bi = new BuilderIterator();
bi.AddBuilder(new Join());
bi.AddBuilder(new Where());
bi.AddBuilder(new On());
bi.AddBuilder(new OnJoinStatementBuilder(), false);
bi.Build(ret, tokens, ref i);
}
public override void ContinuedBuildAppend(ParsedSql ret, List <string> tokens, ref int i, Model.SelectField field)
{
if (i < tokens.Count)
{
if (tokens[i] == "as")
{
field.Alias = tokens[++i];
i++;
}
}
ret.Select.Fields.Add(field);
}
public void GetSqlWithInto_RequiresLocalTempTable_OrThrows(bool throws, string table)
{
var sut = new ParsedSql("Sql string", ParseResultValue.Valid, string.Empty, new List <string>(), 4);
if (throws)
{
Assert.Throws <InvalidOperationException>(() => sut.GetSqlWithInto(table));
}
else
{
sut.GetSqlWithInto(table);
}
}
public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
i++;
// Select
var bi = new BuilderIterator();
bi.AddBuilder(new Where(), true, true);
bi.AddBuilder(new On());
bi.AddBuilder(new Join());
bi.AddBuilder(new GroupBy());
bi.AddBuilder(new Table());
bi.Build(ret, tokens, ref i);
}
public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
SelectStatement selectStatement = new SelectStatement();
ret.Select = selectStatement;
i++;
// Select
var bi = new BuilderIterator();
bi.AddBuilder(new From());
bi.AddBuilder(new Comma());
bi.AddBuilder(new SelectField(), false);
bi.Build(ret, tokens, ref i);
}
public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
var token = tokens[i];
var sf = new T();
if (token == "(")
{
sf.Name = ParenBlockBuilder.ConsumeParenBlock(tokens, ref i);
}
else if (FunctionBlockBuilder.Match(token, tokens, i))
{
sf.Name = FunctionBlockBuilder.ConsumeFunctionBlock(tokens, ref i);
}
else
{
sf.Name = tokens[i++];
}
ContinuedBuildAppend(ret, tokens, ref i, sf);
}
public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
SelectStatement selectStatement = new SelectStatement();
ret.Select = selectStatement;
i++;
var from = new From();
while (i < tokens.Count)
{
if (from.MatchParser(tokens[i]))
{
}
else
{
var sf = new SelectField();
sf.Name = tokens[i++];
selectStatement.Fields.Add(sf);
}
}
}
public void Build(ParsedSql ret, List <string> tokens, ref int i)
{
while (i < tokens.Count)
{
var token = tokens[i];
bool breakAfterProcess = false;
foreach (var bi in _bis)
{
if (!bi.MustMatch || bi.Builder.Match(token, tokens, i))
{
bi.Builder.Build(ret, tokens, ref i);
breakAfterProcess = bi.BreakAfterProcess;
break;
}
}
if (breakAfterProcess)
{
break;
}
}
}
public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
var token = tokens[i];
if (Constants.ComparisonOperators.Contains(token))
{
_ws.Operator = token;
_onLeft = false;
i++;
}
else if (Constants.BooleanOperators.Contains(token))
{
_ws = new WhereStatement
{
PrecedingOperator = token,
};
_onLeft = true;
i++;
}
else if (token == "(")
{
_ws.LeftClause = ParenBlockBuilder.ConsumeParenBlock(tokens, ref i);
ret.Select.Wheres.Add(_ws);
}
else if (_onLeft)
{
_ws.LeftClause = token;
i++;
}
else
{
_ws.RightClause = token;
ret.Select.Wheres.Add(_ws);
i++;
}
}
public abstract void Decompose(ParsedSql ret);
public static string Create(ParsedSql assertSql, ParsedSql testSql, IEnumerable <ComparableColumn> comparableColumns)
{
var headerWidth = 100;
var indentWidth = 4;
var assertTable = "#Assert";
var testTable = "#Test";
var matchedTable = "#Matched";
var discrepantTable = "#Discrepant";
var extraTable = "#Extra";
var missingTable = "#Missing";
var keys = comparableColumns.Where(x => x.IsKey).OrderBy(x => x.ColumnOrder).Select(x => x.ColumnName);
var commaKeys = string.Join(", ", keys);
var orderedKeys = string.Join(", ", comparableColumns.Where(x => x.IsKey).OrderBy(x => x.ColumnOrder).Select(x => $"{x.ColumnName} {(x.SortDescending ? "desc" : string.Empty)}"));
var columns = comparableColumns.Where(x => !x.IsKey).OrderBy(x => x.ColumnOrder).Select(x => x.ColumnName);
var commaColuns = string.Join(", ", columns);
var header = new StringBuilder();
var body = new StringBuilder();
AddBlockComment(
header,
"Sql Data Comparison Script - https://sqldatacompare.mjconrad.com/",
WrapLine("This script compares the results of the two queries below. The first query, called the 'Assert' query is assumed to be correct. The second query, called 'Test' is the new query we are testing. There are two main steps to this comparison:"),
string.Empty,
WrapLine($" 1. First, we check that the key(s) which were defined for this comparison are unique for each row. If this comparison fails, you will recieve an error and the output will show a summary of which key values existed multiple times.", 5),
WrapLine($" 2. Once we have verified the keys are properly defined, we compare rows based on the keys. The report will define several output tables", 5),
string.Empty,
WrapLine($" {matchedTable,-25} - Rows where all columns are identical."),
WrapLine($" {missingTable,-25} - Rows which exist in 'Assert' but are missing from 'Test'"),
WrapLine($" {extraTable,-25} - Rows which do not exist in 'Assert' but exist in 'Test'"),
WrapLine($" {discrepantTable,-25} - Rows where one or more columns are not equal"),
WrapLine($" {discrepantTable + "__ColumnName",-25} - Rows where a specific column is not equal."),
string.Empty,
$"Compared Keys: {commaKeys}",
string.Empty,
"Assert:",
"-------",
assertSql.Sql,
string.Empty,
"Test:",
"-----",
testSql.Sql);
body.AppendLine("BEGIN TRAN -- Begin a transaction which is rolled back. This is done to guarentee no database changes occur.");
AddBlockComment(
body,
"User Settings",
"Any variables in this section can be edited to adjust the output of the results.",
WrapLine("If your queries need to share variables, declare and initalize them here. Additionally, if they require the same temp table, that may be created in this section as well."));
body.AppendLine("Declare @VerboseOutput bit = 0; --Set to 1 to see additional output. \r\n\r\n");
AddBlockComment(body, "Setup", "Scroll down to add 'into' to the selects");
body.AppendLine(CreateErrorsTable());
body.AppendLine(CreateStatsTable(assertTable, testTable));
body.AppendLine("Declare @ErrorCount int; --Helper for later");
body.AppendLine(GetIntoStatements(assertSql, assertTable));
body.AppendLine();
body.AppendLine(GetIntoStatements(testSql, testTable));
AddBlockComment(
body,
"Perform Base Analysis",
"In this we ensure the provided keys will allow the main analysis to work.",
"If there are duplicate keys we cannot proceed because we might get many to many joins.");
body.AppendLine("BEGIN TRY");
body.AppendLine();
body.AppendLine(CalculateStats(assertTable, keys));
body.AppendLine();
body.AppendLine(CalculateStats(testTable, keys));
body.AppendLine(HaltOnErrors());
AddBlockComment(
body,
"Perform Main Analysis",
"In this section, we compare the two sets of selected results.");
body.AppendLine(CreateSummaryTable());
var outputTables = new List <(string Table, string OrderBy)>();
string assert = "Assert";
string test = "Test";
outputTables.Add((matchedTable, orderedKeys));
body.AppendLine();
body.AppendLine($"Select {AliasColumns(assert, keys, true)}");
body.AppendLineIf(columns.Any(), $" , '' [ ] \r\n , {AliasColumns(assert, columns, true)}");
body.AppendLine($"Into {matchedTable}");
body.AppendLine($"From {testTable} {test} ");
body.AppendLine($"Join {assertTable} {assert} on {JoinON(assert, test, keys)} ");
body.AppendLineIf(columns.Any(), $"Where {WhereMatch(assert, test, columns)}");
body.AppendLine();
outputTables.Add((missingTable, orderedKeys));
body.AppendLine();
body.AppendLine($"Select {AliasColumns(assert, keys, true)}");
body.AppendLineIf(columns.Any(), $", '' [ ] \r\n , {AliasColumns(assert, columns)}");
body.AppendLine($"Into {missingTable}");
body.AppendLine($"From {assertTable} {assert} ");
body.AppendLine($"Left Join {testTable} {test} on {JoinON(assert, test, keys)} ");
body.AppendLine($"Where {test}.{keys.First()} IS NULL");
outputTables.Add((extraTable, orderedKeys));
body.AppendLine();
body.AppendLine($"Select {AliasColumns(test, keys, true)}");
body.AppendLineIf(columns.Any(), $", '' [ ] \r\n , {AliasColumns(test, columns)}");
body.AppendLine($"Into {extraTable}");
body.AppendLine($"From {testTable} {test} ");
body.AppendLine($"Left Join {assertTable} {assert} on {JoinON(assert, test, keys)} ");
body.AppendLine($"Where {assert}.{keys.First()} IS NULL");
body.AppendLine();
if (columns.Any())
{
outputTables.Add((discrepantTable, orderedKeys));
body.AppendLine();
body.AppendLine($"Select {AliasColumns(assert, keys, true)}\r\n , '' [ ] \r\n , {CompareColumns(assert, test, columns)}");
body.AppendLine($"Into {discrepantTable}");
body.AppendLine($"From {testTable} {test} ");
body.AppendLine($"Join {assertTable} {assert} on {JoinON(assert, test, keys)} ");
body.AppendLine($"Where {WhereNotMatch(assert, test, columns)}");
body.AppendLine();
foreach (var column in columns)
{
var discrepantDetailTable = $"{discrepantTable}__{column}";
var columnDisplay = columns.Where(x => x != column).ToList();
columnDisplay.Insert(0, column);
var desc = comparableColumns.Single(x => x.ColumnName == column).SortDescending ? " desc" : string.Empty;
outputTables.Add((discrepantDetailTable, $"[{assert} {column}]{desc}, [{test} {column}]{desc}"));
body.AppendLine();
body.AppendLine($"Select {AliasColumns(assert, keys, true)}\r\n , '' [ ] \r\n , {CompareColumns(assert, test, columnDisplay.ToArray())}");
body.AppendLine($"Into {discrepantDetailTable}");
body.AppendLine($"From {testTable} {test} ");
body.AppendLine($"Join {assertTable} {assert} on {JoinON(assert, test, keys)} ");
body.AppendLine($"Where {WhereNotMatch(assert, test, new string[] { column })}");
body.AppendLine();
}
}
else
{
body.AppendLine("Print('All columns selected are keys, so rows cannot be discrepant. They can only match, be extra or missing')");
}
body.AppendLine(SummarizeOutputs(outputTables));
body.AppendLine($" {SelectTable("#Stats", "Stats")}");
body.AppendLine($" {SelectTable(assertTable, "Assert Results", commaKeys)}");
body.AppendLine($" {SelectTable(testTable, "Test Results", commaKeys)}");
body.AppendLine(" ROLLBACK TRAN -- Rollback transaction which surrounds the whole comparison script. This is done to guarentee no database changes occur.");
body.AppendLine("END TRY");
body.AppendLine("BEGIN CATCH");
body.AppendLine($" Select * From #Errors Order By Fatal desc");
body.AppendLine($" {SelectTable("#Stats", "Stats")}");
body.AppendLine($" {SelectTable(assertTable, "Assert Results", commaKeys)}");
body.AppendLine($" {SelectTable(testTable, "Test Results", commaKeys)}");
body.AppendLine();
body.AppendLine(" ROLLBACK TRAN -- Rollback transaction which surrounds the whole comparison script. This is done to guarentee no database changes occur.");
body.AppendLine(" Declare @Message varchar(255) = ERROR_MESSAGE(), @Severity int = ERROR_Severity();");
body.AppendLine(" RAISERROR(@Message,@Severity,1);");
body.AppendLine("END CATCH");
// Produce final result in correct order.
string result = header.ToString() + body.ToString();
return(result);
void AddBlockComment(StringBuilder sb, string title, params string[] lines)
{
sb.AppendLine();
sb.AppendLine($"/*{new string('-', headerWidth - 2)}");
var titlePad = (headerWidth - title.Length) / 2;
sb.AppendLine($"{new string(' ', titlePad)}{title}");
sb.AppendLine();
lines.ToList()
.ForEach(x => sb.AppendLine(x));
sb.AppendLine($"{new string('-', headerWidth - 2)}*/");
sb.AppendLine();
}
string AliasColumns(string alias, IEnumerable <string> columns, bool simpleName = false)
{
return(string.Join("\r\n , ", columns.Select(x => $"{alias}.{x} {(simpleName ? string.Empty : $"[{alias} {x}]")}")));
}
public abstract void Build(ParsedSql ret, List <string> tokens, ref int i);
public virtual void ContinuedBuildAppend(ParsedSql ret, List <string> tokens, ref int i, T field)
{
}
public virtual void Build(ParsedSql ret, List <string> tokens, ref int i)
{
}
public override void ContinuedBuildAppend(ParsedSql ret, List <string> tokens, ref int i, Model.GroupByField field)
{
ret.Select.GroupBys.Add(field);
}
public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
i++;
(new Table()).Build(ret, tokens, ref i);
}
public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
i++;
}
public void GetSqlWithInto_InvalidValidSql_Throws(ParseResultValue parseResult)
{
var sut = new ParsedSql("Sql string", parseResult, string.Empty);
Assert.Throws <InvalidOperationException>(() => sut.GetSqlWithInto("#T"));
}
public void GetSqlWithInto_ValidSql()
{
var sut = new ParsedSql("Sql string", ParseResultValue.Valid, string.Empty, new List <string>(), 4);
sut.GetSqlWithInto("#T");
}
public static ParsedSql ParseAndValidate(string sql)
{
if (string.IsNullOrWhiteSpace(sql))
{
return(new ParsedSql(sql, ParseResultValue.Warning, "Sql cannot be empty."));
}
sql = sql.Trim();
var parseResult = Parser.Parse(sql);
if (parseResult.Errors.Any())
{
var sb = new StringBuilder();
foreach (var error in parseResult.Errors)
{
sb.Append(error.Message + " ");
}
return(new ParsedSql(sql, ParseResultValue.Error, "SQL Has Errors: " + sb.ToString()));
}
else if (parseResult.BatchCount > 1)
{
return(new ParsedSql(sql, ParseResultValue.Error, "Multiple sql batches are not suported by the template engine. If you need multiple batches, then please provide the last batch which selects results to this app. Then after copying/pasting the template, add the initial batches to the top of the script."));
}
else if (parseResult.BatchCount == 0 ||
parseResult.Script.Batches[0].Statements.Any() == false)
{
return(new ParsedSql(sql, ParseResultValue.Warning, "No SQL Found."));
}
var batches = parseResult.Script.Batches;
// All sql is safe (no side effects). Only the last statement is a plain sql statement
// All columns have names, which are unique and no * selects.
if (BatchesAreSafe(batches, out var batchMessage) == false)
{
return(new ParsedSql(sql, ParseResultValue.Error, batchMessage));
}
else if (BatchesProduceOneResultSet(batches, out var resultSetMessage) == false)
{
return(new ParsedSql(sql, ParseResultValue.Error, resultSetMessage));
}
else
{
var lastStatement = batches.Last().Statements.Last();
var colList = new List <string>();
var xmlDoc = new XmlDocument();
xmlDoc.LoadXml(lastStatement.Xml);
// Narrow to only the Select. There otherwise could be a CTE or something else
// containing SqlSelectClause.
try
{
xmlDoc.LoadXml(xmlDoc.GetElementsByTagName("SqlSelectSpecification")[0].OuterXml);
}
catch (NullReferenceException ex)
{
return(new ParsedSql(sql, ParseResultValue.Error, $"Unable to find a select specification. Shouldn't be possible Please report this error if it occurs. Message: {ex.Message}"));
}
var select = xmlDoc.GetElementsByTagName("SqlSelectClause");
if (select.Count == 0)
{
return(new ParsedSql(sql, ParseResultValue.Error, "Unable to find a select clause. Shouldn't be possible Please report this error if it occurs"));
}
xmlDoc.LoadXml(select[0].OuterXml.ToString());
foreach (XmlNode node in select[0].ChildNodes)
{
if (node.Name == "SqlSelectScalarExpression")
{
if (node.Attributes["Alias"]?.Value is string alias)
{
colList.Add(alias);
continue;
}
else
{
if (node.FirstChild.NextSibling.Attributes["ColumnOrPropertyName"]?.Value is string col1)
{
colList.Add(col1.Trim());
continue;
}
else if (node.FirstChild.NextSibling.Attributes["ColumnName"]?.Value is string col2)
{
colList.Add(col2.Trim());
continue;
}
return(new ParsedSql(sql, ParseResultValue.Error, "Select is not comparable because at least column is unnamed in statement. All columns must be named."));
}
}
else if (node.Name == "SqlSelectStarExpression")
{
return(new ParsedSql(sql, ParseResultValue.Error, "Select is not comparable because at least one * is used in statement. Each column must be listed individually."));
}
}
if (colList.Count != colList.Distinct(StringComparer.InvariantCultureIgnoreCase).Count())
{
return(new ParsedSql(sql, ParseResultValue.Error, "Select is not comparable because at least one column name is used multiple times"));
}
var intoIndex = FindIntoIndex(parseResult);
var result = new ParsedSql(sql, ParseResultValue.Valid, "Valid SQL", colList, intoIndex);
var intosql = result.GetSqlWithInto("#Assert");
return(result);
}
}