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 void GetSqlWithInto_ValidSql()
{
var sut = new ParsedSql("Sql string", ParseResultValue.Valid, string.Empty, new List <string>(), 4);
sut.GetSqlWithInto("#T");
}
public void GetSqlWithInto_InvalidValidSql_Throws(ParseResultValue parseResult)
{
var sut = new ParsedSql("Sql string", parseResult, string.Empty);
Assert.Throws <InvalidOperationException>(() => 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);
}
}