private void ResolveGroupByClause(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
if (fileIndex >= tokens.Length || !tokens[fileIndex].Text.ToLower().Equals("group"))
{
return;
}
else
{
fileIndex += 2; //skip 'group by'
}
do
{
StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
if (fileIndex >= tokens.Length)
{
break;
}
if (tokens[fileIndex].Text.Equals(","))
{
fileIndex++;
continue;
}
else
{
break;
}
} while (true);
}
private SelectStatement ResolveStatement(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
statement = new SelectStatement();
SkipSelectPrequelStatements(tokens, ref fileIndex, context);
statement.Expression = DetermineSourceColumns(tokens, ref fileIndex, context);
ResolveIntoClause(tokens, ref fileIndex, context);
var objects = StatementResolveHelper.ResolveFromStatement(tokens, ref fileIndex, context);
AddObjectsToContext(objects, context);
Beautifier.BeautifyColumns(statement.Expression, context);
AddSynonymousObjects(objects);
StatementResolveHelper.ResolveWhereStatement(tokens, ref fileIndex, context);
ResolveGroupByClause(tokens, ref fileIndex, context);
ResolveOrderByClause(tokens, ref fileIndex, context);
//Resolve FOR-CLAUSE
ResolveUnionclause(tokens, ref fileIndex, context);
PopObjectsFromContextStack(context);
statement.Expression.Name = "SELECT";
return(statement);
}
/// <summary>
/// Resolves a Set clause
/// </summary>
private void ResolveSetClause(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
do
{
var target = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context); //resolve target column
AddTargetObject(target);
fileIndex++; //skip '='
var source = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context); //resolve source column
target.ChildExpressions.Add(source);
manipulation.Expressions.Add(target);
if (fileIndex < tokens.Length && tokens[fileIndex].Text.Equals(","))
{
fileIndex++; //skip ','
continue;
}
else
{
break;
}
} while (true);
}
private void DetermineTargetColumns(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
if (tokens[fileIndex].Text.Equals("("))
{
fileIndex++; // skip '('
}
else
{
return;
}
do
{
var target = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
AddTargetObject(target);
targets.Add(target);
if (tokens[fileIndex].Text.Equals(","))
{
fileIndex++; //skip ','
continue;
}
else
{
break;
}
} while (true);
fileIndex++; //skip ')'
return;
}
private void ResolveIntoClause(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
if (fileIndex < tokens.Length && tokens[fileIndex].Text.ToLower().Equals("into"))
{
fileIndex++;
var target = StatementResolveHelper.ResolveDatabaseObject(tokens, ref fileIndex, context);
statement.TargetObject = target;
}
}
public Expression Resolve(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
List <Expression> expressions = new List <Expression>();
string functionName = tokens[fileIndex].Text.ToUpper();
fileIndex++; //skiptype
if (tokens[fileIndex].Text.Equals("("))
{
fileIndex++; //skip "("
if (tokens[fileIndex].Text.Equals("max") || tokens[fileIndex].Text.Equals("min"))
{
fileIndex++;
}
else
{
do
{
var innerExpression = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
if (innerExpression.Type.Equals(ExpressionType.COLUMN))
{
expressions.Add(innerExpression);
}
if (tokens[fileIndex].Text.Equals(","))
{
fileIndex++; //skip ','
continue;
}
else
{
break;
}
}while (true);
}
fileIndex++; //skip ")"
}
if (expressions.Count == 1)
{
return(expressions[0]);
}
else
{
return(new Expression(ExpressionType.SCALAR_FUNCTION)
{
Name = functionName,
ChildExpressions = expressions
});
}
}
public void ShouldResolveFromStatementWithFunctionAsDataObject()
{
//Arrange
string joinStatement = "FROM fn_helpcollations()";
CompilerContext context = new CompilerContext("xUnit", "irrelevant", "irrelevant", true);
int fileIndex = 0;
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(joinStatement).ToArray();
//Act
StatementResolveHelper.ResolveFromStatement(tokens, ref fileIndex, context);
//Assert
Assert.True(tokens.Length == fileIndex);
}
public void ShouldSplitObjectNotation_08()
{
//Arrange
string notation = "object";
//Act
StatementResolveHelper.SplitObjectNotationIntoSingleParts(notation, out string serverName, out string databaseName, out string databaseSchema, out string databaseObjectName);
//Assert
Assert.Null(serverName);
Assert.Null(databaseName);
Assert.Null(databaseSchema);
Assert.Equal("object", databaseObjectName);
}
public void ShouldResolveWhereStatement_03()
{
//Arrange
string whereStatement = "WHERE someTable.someColumn in ('string_01', 'string_02', 'string_03')";
CompilerContext context = new CompilerContext("xUnit", "irrelevant", "irrelevant", true);
int fileIndex = 0;
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(whereStatement).ToArray();
//Act
StatementResolveHelper.ResolveWhereStatement(tokens, ref fileIndex, context);
//Assert
Assert.True(tokens.Length == fileIndex);
}
public void ShouldResolveWhereStatement_02()
{
//Arrange
string whereStatement = "WHERE stamm6.deleted = 0 AND object14.deleted = 0 and feld10 is not null AND (ISNUMERIC(LEFT([feld34], 4)) = 1 " +
"OR([feld34] LIKE '[0-9][0-9][0-9][0-9][A-Z][ ]'))";
CompilerContext context = new CompilerContext("xUnit", "irrelevant", "irrelevant", true);
int fileIndex = 0;
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(whereStatement).ToArray();
//Act
StatementResolveHelper.ResolveWhereStatement(tokens, ref fileIndex, context);
//Assert
Assert.True(tokens.Length == fileIndex);
}
public void ShouldResolveAllColumnsSynonymous()
{
//Arrange
string expression = "*";
CompilerContext context = new CompilerContext("xUnit", "irrelevant", "irrelevant", true);
int fileIndex = 0;
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(expression).ToArray();
//Act
var result = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
//Assert
Assert.Equal("*", result.Name);
Assert.Equal(ExpressionType.COLUMN, result.Type);
}
private void SkipTopExpression(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
if (!tokens[fileIndex].Text.ToLower().Equals("top"))
{
return;
}
fileIndex++; //skip top
StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
if (tokens[fileIndex].Text.ToLower().Equals("percent"))
{
fileIndex++;
}
}
public void ShouldResolveFromStatement_01()
{
//Arrange
string fromStatement = "FROM ( SELECT HIST.someCol_01, HIST.someCol_02 FROM server_01.db_01.dbo.historyTable HIST) AS R " +
"INNER JOIN DWH.dbo.someDimension SOME_ALIAS_01 ON SOME_ALIAS_01.someCol_03 = some_FK COLLATE Latin1_General_CI_AS " +
"AND SOME_DATE BETWEEN SOME_ALIAS_01.ValidFrom AND SOME_ALIAS_01.ValidUntil";
CompilerContext context = new CompilerContext("xUnit", "irrelevant", "irrelevant", true);
int fileIndex = 0;
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(fromStatement).ToArray();
//Act
StatementResolveHelper.ResolveFromStatement(tokens, ref fileIndex, context);
//Assert
Assert.True(tokens.Length == fileIndex);
}
public void ShouldResolveComplexWhereStatement_01()
{
//Arrange
string whereStatement = "where (([Budget Name] LIKE 'B%' AND YEAR([Date])>=2010) " +
"OR ([Budget Name] LIKE 'P%' AND [Budget Name] >= 'P2011' AND YEAR([Date])>=2011)) " +
"AND YEAR([Date])<=2010";
CompilerContext context = new CompilerContext("xUnit", "irrelevant", "irrelevant", true);
int fileIndex = 0;
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(whereStatement).ToArray();
//Act
StatementResolveHelper.ResolveWhereStatement(tokens, ref fileIndex, context);
//Assert
Assert.True(tokens.Length == fileIndex);
}
public void ShouldResolveFromStatementWithPivotClause()
{
//Arrange
string joinStatement = "FROM (SELECT DaysToManufacture, StandardCost " +
" FROM Production.dbo.Product) AS SourceTable " +
"PIVOT( AVG(StandardCost)" +
"FOR DaysToManufacture IN([0], [1], [2], [3], [4])" +
" ) AS PivotTable";
CompilerContext context = new CompilerContext("xUnit", "irrelevant", "irrelevant", true);
int fileIndex = 0;
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(joinStatement).ToArray();
//Act
StatementResolveHelper.ResolveFromStatement(tokens, ref fileIndex, context);
//Assert
Assert.True(tokens.Length == fileIndex);
}
/// <summary>
/// We are not interested in the actual statement, so just skip it
/// </summary>
public void Resolve(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
fileIndex++; //skip 'create'
switch (tokens[fileIndex].Text.ToLower())
{
case "table":
{
fileIndex += 2;
MoveToFinalBracket(tokens, ref fileIndex);
break;
}
case "unique":
case "clustered":
case "nonclustered":
{
while (!tokens[fileIndex].Text.Equals("("))
{
fileIndex++;
}
MoveToFinalBracket(tokens, ref fileIndex); //skip column name
if (tokens[fileIndex].Text.ToLower().Equals("include"))
{
fileIndex++;
MoveToFinalBracket(tokens, ref fileIndex);
}
if (tokens[fileIndex].Text.ToLower().Equals("with"))
{
fileIndex++;
MoveToFinalBracket(tokens, ref fileIndex);
}
if (tokens[fileIndex].Text.ToLower().Equals("on"))
{
fileIndex++;
StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
}
break;
}
}
}
public Expression Resolve(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
List <Expression> expressions = new List <Expression>();
fileIndex += 2; //Skip 'cast ('
Expression innerExpression = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
if (innerExpression.Type.Equals(ExpressionType.COLUMN))
{
expressions.Add(innerExpression);
}
else if (innerExpression.Type.Equals(ExpressionType.COMPLEX) || innerExpression.Type.Equals(ExpressionType.SCALAR_FUNCTION))
{
expressions.AddRange(innerExpression.ChildExpressions);
}
fileIndex++; //skip 'as'
TypeCasterResolver caster = new TypeCasterResolver();
caster.Resolve(tokens, ref fileIndex, context);
if (!tokens[fileIndex].Text.Equals(")"))
{
throw new InvalidSqlException("Cast statement does not end with a closing bracket");
}
fileIndex++; //skip ')'
if (expressions.Count != 1)
{
return(new Expression(ExpressionType.SCALAR_FUNCTION)
{
Name = "CAST",
ChildExpressions = expressions
});
}
else
{
return(expressions[0]);
}
}
private void DetermineSourceObjects(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
do
{
var source = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
sources.Add(source);
if (fileIndex < tokens.Length && tokens[fileIndex].Text.Equals(","))
{
fileIndex++; //skip ','
continue;
}
else
{
break;
}
} while (true);
fileIndex++; //skip ')'
}
public void ShouldResolveFromStatementwithApplyKeyword_02()
{
//Arrange
string fromStatement = "FROM t1 " +
"CROSS APPLY " +
"( " +
"SELECT TOP 3 * " +
"FROM t2 " +
"WHERE t2.t1_id = t1.id " +
"ORDER BY " +
" t2.rank DESC " +
") t2o ";
CompilerContext context = new CompilerContext("xUnit", "irrelevant", "irrelevant", true);
int fileIndex = 0;
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(fromStatement).ToArray();
//Act
StatementResolveHelper.ResolveFromStatement(tokens, ref fileIndex, context);
//Assert
Assert.True(tokens.Length == fileIndex);
}
internal DatabaseObject(string notation, CompilerContext context, DatabaseObjectType type, bool addStdObjects = true)
{
StatementResolveHelper.SplitObjectNotationIntoSingleParts(notation, out string serverName, out string databaseName, out string databaseSchema, out string databaseObjectName);
Server = serverName;
Database = databaseName;
if (serverName == null && addStdObjects)
{
Server = context.CurrentServerContext;
}
if (databaseName == null && addStdObjects)
{
Database = context.CurrentDbContext;
}
if (databaseSchema == null && addStdObjects)
{
Schema = InternalConstants.UNRELATED_SCHEMA_NAME;
}
else
{
Schema = databaseSchema;
}
if (databaseObjectName == null)
{
throw new InvalidSqlException("Can't construct a database object without identifier");
}
else
{
Name = databaseObjectName;
}
Expressions = null;
}
/// <summary>
/// ATM this resolver just skips the statement without adding anything to the context
/// </summary>
public void Resolve(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
fileIndex += 2; //skip "bulk insert"
StatementResolveHelper.ResolveDatabaseObject(tokens, ref fileIndex, context);
if (!tokens[fileIndex].Text.ToLower().Equals("from"))
{
throw new InvalidSqlException("Missing 'from'-keyword in bulk insert statement");
}
fileIndex++; //skip "from"
string filePath = tokens[fileIndex].Text;
fileIndex++; //skip file
if (tokens[fileIndex].Text.ToLower().Equals("with"))
{
fileIndex += 2; //skip "with ("
int openBracketCounter = 1;
while (openBracketCounter > 0)
{
if (tokens[fileIndex].Text.Equals(")"))
{
openBracketCounter--;
}
if (tokens[fileIndex].Text.Equals("("))
{
openBracketCounter++;
}
fileIndex++;
}
}
}
/// <summary>
/// Gets called when the rows didnt match
/// </summary>
private void ResolveMergeNotMatched(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
if (!tokens[fileIndex].Text.Equals("insert", StringComparison.InvariantCultureIgnoreCase))
{
throw new InvalidSqlException("A 'WHEN NOT MATCHED'-clause did not contain an INSERT statement");
}
List <Expression> targets = new List <Expression>();
List <Expression> sources = new List <Expression>();
fileIndex++; //skip 'insert'
if (tokens[fileIndex].Text.Equals("("))
{
fileIndex++; //skip '('
do
{
var target = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
AddTargetObject(target);
targets.Add(target);
if (tokens[fileIndex].Text.Equals(","))
{
fileIndex++; //skip ','
continue;
}
else
{
break;
}
} while (true);
fileIndex++; //skip ')'
}
if (tokens[fileIndex].Text.Equals("values", StringComparison.InvariantCultureIgnoreCase))
{
fileIndex += 2; //skip 'values ('
do
{
var source = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
sources.Add(source);
if (fileIndex < tokens.Length && tokens[fileIndex].Text.Equals(","))
{
fileIndex++; //skip ','
continue;
}
else
{
break;
}
} while (true);
fileIndex++; //skip ')'
if (targets.Count != sources.Count)
{
throw new InvalidSqlException("Unable to resolve MERGE statement.");
}
for (int counter = 0; counter < targets.Count; counter++)
{
targets[counter].ChildExpressions.Add(sources[counter]);
manipulation.Expressions.Add(targets[counter]);
}
}
else if (tokens[fileIndex].Text.Equals("default", StringComparison.InvariantCultureIgnoreCase))
{
fileIndex += 2; //skip 'default values'
}
else
{
throw new InvalidSqlException("Unable to compile ");
}
}
public DataManipulation Resolve(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
manipulation = new DataManipulation();
fileIndex++; //skip "merge"
//skip top expression
SkipTopExpression(tokens, ref fileIndex, context);
if (tokens[fileIndex].Text.ToLower().Equals("into"))
{
fileIndex++;
}
targetObject = StatementResolveHelper.ResolveDatabaseObject(tokens, ref fileIndex, context);
context.AddDatabaseObjectToCurrentContext(targetObject);
if (!tokens[fileIndex].Text.ToLower().Equals("using"))
{
throw new InvalidSqlException("Trying to resolve a merge-statement without using keyword");
}
var source = ResolveUsingStatement(tokens, ref fileIndex, context);
context.AddDatabaseObjectToCurrentContext(source);
if (!tokens[fileIndex].Text.Equals("on", StringComparison.InvariantCultureIgnoreCase))
{
throw new InvalidSqlException("Expected 'ON' keyword when resolving a 'MERGE'-statement");
}
fileIndex++; //skip 'on'
SearchConditionResolver.Resolve(tokens, ref fileIndex, context);
while (tokens[fileIndex].Text.Equals("when", StringComparison.InvariantCultureIgnoreCase))
{
ResolveWhenExpression(tokens, ref fileIndex, context);
}
var beautified = new List <Expression>();
foreach (var exp in manipulation.Expressions)
{
beautified.Add(Beautifier.BeautifyColumns(exp, context));
}
manipulation.Expressions = beautified;
while (!tokens[fileIndex].Text.ToLower().Equals(";"))
{
fileIndex++;
if (fileIndex == tokens.Length)
{
throw new InvalidSqlException("Trying to resolve a merge-statement without proper ';' determination");
}
}
fileIndex++; //skip ';'
context.CurrentDatabaseObjectContext.Pop();
return(manipulation);
}
/// <summary>
/// Returns the query containing all data columns. The column properties (schema, table) may not have been assigned
/// correctly after this function since the from statement still needs to resolved
/// </summary>
private Expression DetermineSourceColumns(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
Expression query = new Expression(ExpressionType.SCALAR_FUNCTION)
{
Name = "SELECT"
};
if (tokens[fileIndex].Text.Equals("*"))
{
var column = new Expression(ExpressionType.COLUMN)
{
Name = tokens[fileIndex].Text
};
query.ChildExpressions.Add(column);
fileIndex++;
return(query);
}
do
{
var innerExpression = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
if (fileIndex < tokens.Length && tokens[fileIndex].Text.ToLower().Equals("="))
{
//An expression followed by a "=" is equivalent to: CASE WHEN col1 = col2 THEN 1 ELSE 0 END
fileIndex++; //skip '='
StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
}
if (fileIndex >= tokens.Length)
{
return(query);
}
if (tokens[fileIndex].Text.ToLower().Equals("as"))
{
fileIndex++; //skip as
string possibleAlias = tokens[fileIndex].Text;
fileIndex++; //skip alias
var aliasExpression = new Expression(ExpressionType.ALIAS)
{
Name = possibleAlias
};
aliasExpression.ChildExpressions.Add(innerExpression);
query.ChildExpressions.Add(aliasExpression);
}
else if (tokens[fileIndex].Type.Equals(TSQLTokenType.Identifier))
{
string possibleAlias = tokens[fileIndex].Text;
fileIndex++; //skip alias
var aliasExpression = new Expression(ExpressionType.ALIAS)
{
Name = possibleAlias
};
aliasExpression.ChildExpressions.Add(innerExpression);
query.ChildExpressions.Add(aliasExpression);
}
else
{
query.ChildExpressions.Add(innerExpression);
}
if (tokens[fileIndex].Text.Equals(","))
{
fileIndex++; //skip ','
continue;
}
else
{
break;
}
} while (true);
return(query);
}
public DataManipulation Resolve(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
manipulation = new DataManipulation();
fileIndex++; //skip 'update'
var targetObject = StatementResolveHelper.ResolveDatabaseObject(tokens, ref fileIndex, context, true);
fileIndex++; //skip 'set'
do
{
//Resolves the target object. Note that this target can be an alias that is resolved later in the FROM statement
var target = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
AddTargetObject(target, targetObject);
fileIndex++; //skip '='
var source = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context); //resolve source column
target.ChildExpressions.Add(source);
manipulation.Expressions.Add(target);
if (fileIndex < tokens.Length && tokens[fileIndex].Text.Equals(","))
{
fileIndex++; //skip ','
continue;
}
else
{
break;
}
} while (true);
var objects = StatementResolveHelper.ResolveFromStatement(tokens, ref fileIndex, context);
AddObjectsToContext(objects, context);
if (objects.Count > 1)
{
targetObject = AssignRealTarget(objects, targetObject);
var targetSynonymous = new Expression(ExpressionType.COLUMN)
{
Name = Beautifier.EnhanceNotation(targetObject, InternalConstants.WHOLE_OBJECT_SYNONYMOUS),
WholeObjectSynonymous = true
};
foreach (var dbo in objects)
{
if (!dbo.Type.Equals(DatabaseObjectType.REAL) || dbo.Equals(targetObject))
{
continue;
}
var sourceSynonymous = new Expression(ExpressionType.COLUMN)
{
Name = Beautifier.EnhanceNotation(dbo, InternalConstants.WHOLE_OBJECT_SYNONYMOUS),
WholeObjectSynonymous = true
};
targetSynonymous.ChildExpressions.Add(sourceSynonymous);
manipulation.Expressions.Add(targetSynonymous);
}
}
var beautified = new List <Expression>();
foreach (var expr in manipulation.Expressions)
{
beautified.Add(Beautifier.BeautifyColumns(expr, context));
}
manipulation.Expressions = beautified;
StatementResolveHelper.ResolveWhereStatement(tokens, ref fileIndex, context);
PopObjectsFromContextStack(context);
return(manipulation);
}
public Expression Resolve(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
List <Expression> expressions = new List <Expression>();
// The CASE expression has two formats: 'simple' and 'searched'. For more information visit:
// https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017
bool isSimpleFormat = false;
fileIndex++; //skip "case"
if (!tokens[fileIndex].Text.ToLower().Equals("when"))
{
isSimpleFormat = true;
StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
}
do
{
fileIndex++; //skip 'when'
if (isSimpleFormat)
{
StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
}
else
{
SearchConditionResolver.Resolve(tokens, ref fileIndex, context);
}
fileIndex++; //skip 'then'
Expression thenExpression = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
if (thenExpression.Type.Equals(ExpressionType.COLUMN))
{
expressions.Add(thenExpression);
}
else if (thenExpression.Type.Equals(ExpressionType.COMPLEX) || thenExpression.Type.Equals(ExpressionType.SCALAR_FUNCTION))
{
expressions.AddRange(thenExpression.ChildExpressions);
}
if (tokens.Length <= fileIndex)
{
throw new InvalidSqlException("Trying to resolve case-statement without 'end'-keyword");
}
if (tokens[fileIndex].Text.ToLower().Equals("when"))
{
continue;
}
if (tokens[fileIndex].Text.ToLower().Equals("else"))
{
fileIndex++; //skip 'else'
Expression elseExpression = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
if (elseExpression.Type.Equals(ExpressionType.COLUMN))
{
expressions.Add(elseExpression);
}
else if (elseExpression.Type.Equals(ExpressionType.COMPLEX) || elseExpression.Type.Equals(ExpressionType.SCALAR_FUNCTION))
{
expressions.AddRange(elseExpression.ChildExpressions);
}
}
if (tokens[fileIndex].Text.ToLower().Equals("end"))
{
fileIndex++; //skip 'end'
break;
}
} while (true);
if (expressions.Count == 1)
{
return(expressions[0]);
}
else
{
return(new Expression(ExpressionType.SCALAR_FUNCTION)
{
Name = "CASE",
ChildExpressions = expressions
});
}
}
/// <summary>
/// The method of death. Dont change anything. Trust me, just dont.
/// </summary>
private static void ResolveConcatenatedConditions(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context, ref bool expectingSecondPart)
{
bool expectDatabaseObject = false;
bool isSecondPartofBetweenExpression = false;
do
{
if (tokens.Length <= fileIndex)
{
break;
}
if (tokens[fileIndex].Text.ToLower().Equals("not") || Operators.IsUnaryOperator(tokens[fileIndex].Text.ToLower()))
{
fileIndex++; //skip operator
}
if (IsLogicalExpressionOperator(tokens[fileIndex].Text.ToLower()))
{
fileIndex++;
}
if (tokens[fileIndex].Text.Equals("("))
{
fileIndex++; //skip '('
ResolveConcatenatedConditions(tokens, ref fileIndex, context, ref expectingSecondPart);
fileIndex++; //skip ')'
}
else
{
do
{
if (fileIndex < tokens.Length && tokens[fileIndex].Text.ToLower().Equals("null"))
{
fileIndex++; //skip null
}
else if (fileIndex < tokens.Length && (tokens[fileIndex].Text.ToLower().Equals("contains") || tokens[fileIndex].Text.ToLower().Equals("exists")))
{
var resolver = new TsqlFunctionResolver();
resolver.Resolve(tokens, ref fileIndex, context);
break;
}
else if (expectDatabaseObject)
{
StatementResolveHelper.ResolveDatabaseObject(tokens, ref fileIndex, context);
}
else
{
StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
if (isSecondPartofBetweenExpression)
{
if (!tokens[fileIndex].Text.ToLower().Equals("and"))
{
throw new InvalidSqlException("BETWEEN keyword was not followed by an AND");
}
fileIndex++; //skip 'AND'
StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
isSecondPartofBetweenExpression = false;
}
if (fileIndex < tokens.Length && tokens[fileIndex].Text.Equals("escape", StringComparison.InvariantCultureIgnoreCase))
{
fileIndex += 2; // skip 'escape [pattern]'
}
}
if (expectingSecondPart)
{
expectingSecondPart = false;
}
if (fileIndex >= tokens.Length)
{
break;
}
if (tokens[fileIndex].Text.ToLower().Equals("not") || tokens[fileIndex].Text.ToLower().Equals("~"))
{
fileIndex++;
}
string possibleLogicalExpressionOperator = tokens[fileIndex].Text.ToLower();
if (possibleLogicalExpressionOperator.Equals("is"))
{
if (expectingSecondPart)
{
throw new InvalidSqlException("Expression contains multiple logical operators");
}
expectingSecondPart = true;
fileIndex++; // skip 'is'
if (tokens[fileIndex].Text.ToLower().Equals("not"))
{
fileIndex++;
}
continue;
}
if (possibleLogicalExpressionOperator.Equals("between"))
{
if (expectingSecondPart)
{
throw new InvalidSqlException("Expression contains multiple logical operators");
}
expectingSecondPart = true;
fileIndex++; // skip 'between'
isSecondPartofBetweenExpression = true;
continue;
}
if (possibleLogicalExpressionOperator.Equals("in"))
{
if (expectingSecondPart)
{
throw new InvalidSqlException("Expression contains multiple logical operators");
}
expectingSecondPart = true;
expectDatabaseObject = true;
fileIndex++; // skip 'in'
continue;
}
else
{
expectDatabaseObject = false;
}
if (IsLogicalExpressionOperator(tokens[fileIndex].Text))
{
if (expectingSecondPart)
{
throw new InvalidSqlException("Expression contains multiple logical operators");
}
expectingSecondPart = !expectingSecondPart;
fileIndex++;
continue;
}
if (tokens[fileIndex].Text.ToLower().Equals("and") && isSecondPartofBetweenExpression)
{
fileIndex++;
continue;
}
else
{
break;
}
} while (true);
}
if (tokens.Length > fileIndex && !expectingSecondPart && IsLogicalExpressionOperator(tokens[fileIndex].Text.ToLower()))
{
expectingSecondPart = true;
fileIndex++; // skip 'operator'
continue;
}
if (tokens.Length > fileIndex && (tokens[fileIndex].Text.ToLower().Equals("and") || tokens[fileIndex].Text.ToLower().Equals("or")))
{
fileIndex++;
continue;
}
else
{
break;
}
}while (true);
}
private DatabaseObject ResolveUsingStatement(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
fileIndex++; //skip 'using'
return(StatementResolveHelper.ResolveDatabaseObject(tokens, ref fileIndex, context));
}
internal void Resolve(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
fileIndex++; //skip 'with'
do
{
var alias = tokens[fileIndex].Text.ToLower();
if (string.IsNullOrEmpty(alias))
{
throw new InvalidSqlException("Common table expression must have an alias");
}
fileIndex++; //skip alias
List <Expression> columns = new List <Expression>();
if (!tokens[fileIndex].Text.Equals("as", StringComparison.InvariantCultureIgnoreCase))
{
if (tokens[fileIndex].Text.Equals("("))
{
fileIndex++; //skip '('
}
do
{
var column = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
if (!column.Type.Equals(ExpressionType.COLUMN))
{
throw new InvalidSqlException("Common Table Expressions only may contain conrete columns");
}
columns.Add(column);
if (tokens[fileIndex].Text.Equals(","))
{
fileIndex++; //skip ','
continue;
}
else
{
break;
}
}while (true);
if (tokens[fileIndex].Text.Equals(")"))
{
fileIndex++; //skip ')'
}
}
fileIndex++; //skip 'as'
var cte = StatementResolveHelper.ResolveDatabaseObject(tokens, ref fileIndex, context);
cte.Alias = alias;
cte.Type = DatabaseObjectType.CTE;
cte.Expressions = columns;
context.CurrentDatabaseObjectContext.Push(cte);
if (tokens.Length > fileIndex && tokens[fileIndex].Text.Equals(","))
{
fileIndex++; //skip ','
continue;
}
else
{
break;
}
} while (true);
}
public Expression Resolve(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context)
{
List <Expression> expressions = new List <Expression>();
string functionName = "";
if (tokens[fileIndex + 1].Text.Equals("::")) //Special function types eg: "geography::STPointFromText(........)"
{
fileIndex += 4;
}
else
{
functionName = tokens[fileIndex].Text.ToUpper();
fileIndex += 2; //skip function-keyword + '('
}
if (tokens[fileIndex].Text.Equals(")")) //parameterless function can be ignored
{
fileIndex++;
return(new Expression(ExpressionType.SCALAR_FUNCTION)
{
Name = functionName
});
}
do
{
var parameter = StatementResolveHelper.ResolveExpression(tokens, ref fileIndex, context);
if (parameter.Type.Equals(ExpressionType.COMPLEX) || parameter.Type.Equals(ExpressionType.SCALAR_FUNCTION))
{
foreach (var subExp in parameter.ChildExpressions)
{
expressions.Add(subExp);
}
}
else if (parameter.Type.Equals(ExpressionType.COLUMN))
{
expressions.Add(parameter);
}
if (fileIndex < tokens.Length && tokens[fileIndex].Text.Equals(","))
{
fileIndex++; //skip ","
continue;
}
else
{
break;
}
}while (true);
fileIndex++; //skip ')'
if (expressions.Count != 1)
{
return(new Expression(ExpressionType.SCALAR_FUNCTION)
{
Name = functionName,
ChildExpressions = expressions
});
}
else
{
return(expressions[0]);
}
}
