private SqlTopLimitNode ParseSelectTopClause(ITokenizer t)
{
// "TOP" "(" <Number> | <Variable> ")" "PERCENT"? "WITH TIES"?
// "TOP" <Number> | <Variable> "PERCENT"? "WITH TIES"?
if (!t.NextIs(SqlTokenType.Keyword, "TOP"))
{
return(null);
}
var topToken = t.GetNext();
var numberOrVariable = ParseMaybeParenthesis(t, ParseNumberOrVariable);
bool percent = t.NextIs(SqlTokenType.Keyword, "PERCENT", true);
bool withTies = false;
if (t.NextIs(SqlTokenType.Keyword, "WITH", true))
{
t.Expect(SqlTokenType.Keyword, "TIES");
withTies = true;
}
return(new SqlTopLimitNode {
Location = topToken.Location,
Value = numberOrVariable,
Percent = percent,
WithTies = withTies
});
}
private ISqlNode ParseWithChildStatement(ITokenizer t)
{
while (t.NextIs(SqlTokenType.Symbol, ";", true))
{
;
}
var stmt = ParseUnterminatedWithChildStatement(t);
t.NextIs(SqlTokenType.Symbol, ";", true);
return(stmt);
}
private SqlObjectIdentifierNode ParseObjectIdentifier(ITokenizer t)
{
// (((<ServerName> ".")? <DatabaseName> ".")? <Schema> ".")? <Identifier>
var item1 = t.Expect(SqlTokenType.Identifier);
if (!t.NextIs(SqlTokenType.Symbol, ".", true))
{
return(new SqlObjectIdentifierNode
{
Location = item1.Location,
Name = new SqlIdentifierNode(item1)
});
}
var item2 = t.Expect(SqlTokenType.Identifier);
if (!t.NextIs(SqlTokenType.Symbol, ".", true))
{
return(new SqlObjectIdentifierNode
{
Location = item1.Location,
Schema = new SqlIdentifierNode(item1),
Name = new SqlIdentifierNode(item2)
});
}
var item3 = t.Expect(SqlTokenType.Identifier);
if (!t.NextIs(SqlTokenType.Symbol, ".", true))
{
return(new SqlObjectIdentifierNode
{
Location = item1.Location,
Database = new SqlIdentifierNode(item1),
Schema = new SqlIdentifierNode(item2),
Name = new SqlIdentifierNode(item3)
});
}
var item4 = t.Expect(SqlTokenType.Identifier);
return(new SqlObjectIdentifierNode
{
Location = item1.Location,
Server = new SqlIdentifierNode(item1),
Database = new SqlIdentifierNode(item2),
Schema = new SqlIdentifierNode(item3),
Name = new SqlIdentifierNode(item4)
});
}
private ISqlNode ParseSelectColumn(ITokenizer t)
{
var next = t.GetNext();
// "*"
if (next.Is(SqlTokenType.Symbol, "*"))
{
return(new SqlOperatorNode(next));
}
// TODO: Should change this to (<Variable> "=")? <SelectColumnExpression>
// <Variable> ("=" <SelectColumnExpression>)?
if (next.IsType(SqlTokenType.Variable))
{
if (t.NextIs(SqlTokenType.Symbol, "="))
{
var equalsOperator = t.GetNext();
var rvalue = ParseScalarExpression(t);
return(new SqlInfixOperationNode
{
Location = equalsOperator.Location,
Left = new SqlVariableNode(next),
Right = rvalue,
Operator = new SqlOperatorNode(equalsOperator)
});
}
}
t.PutBack(next);
// <SelectColumnExpression> (AS <Alias>)?
return(ParseMaybeAliasedScalar(t, ParseSelectColumnExpression));
}
private SqlListNode <TNode> ParseList <TNode>(ITokenizer t, Func <ITokenizer, TNode> getItem)
where TNode : class, ISqlNode
{
if (t.Peek().IsType(SqlTokenType.EndOfInput))
{
return(null);
}
// <Item> ("," <Item>)*
var list = new SqlListNode <TNode>();
while (true)
{
var item = getItem(t);
if (item == null)
{
break;
}
list.Children.Add(item);
if (t.NextIs(SqlTokenType.Symbol, ",", true))
{
continue;
}
break;
}
if (list.Children.Count == 0)
{
return(null);
}
list.Location = list.Children[0].Location;
return(list);
}
private ISqlNode ParseStatement(ITokenizer t)
{
var stmt = ParseUnterminatedStatement(t);
t.NextIs(SqlTokenType.Symbol, ";", true);
return(stmt);
}
private ISqlNode ParseOverPartitionBy(ITokenizer t)
{
// "PARTITION" "BY" <Expr> ("," <Expr>)*
if (!t.NextIs(SqlTokenType.Keyword, "PARTITION", true))
{
return(null);
}
t.Expect(SqlTokenType.Keyword, "BY");
return(ParseList(t, ParseScalarExpression));
}
private ISqlNode ParseOverOrderBy(ITokenizer t)
{
// "ORDER" "BY" <OrderByTerm> ("," <OrderByTerm>)*
if (!t.NextIs(SqlTokenType.Keyword, "ORDER", true))
{
return(null);
}
t.Expect(SqlTokenType.Keyword, "BY");
return(ParseList(t, ParseOrderTerm));
}
private ISqlNode ParseSelectFromClause(ITokenizer t)
{
// ("FROM" <join>)?
if (!t.NextIs(SqlTokenType.Keyword, "FROM"))
{
return(null);
}
t.GetNext();
return(ParseJoin(t));
}
public ISqlNode ParseMergeMatched(ITokenizer t)
{
if (t.NextIs(SqlTokenType.Keyword, "UPDATE"))
{
var updateToken = t.Expect(SqlTokenType.Keyword, "UPDATE");
var setList = ParseUpdateSetClause(t);
return(new SqlUpdateNode
{
Location = updateToken.Location,
SetClause = setList
});
}
if (t.NextIs(SqlTokenType.Keyword, "DELETE"))
{
var deleteToken = t.Expect(SqlTokenType.Keyword, "DELETE");
return(new SqlKeywordNode(deleteToken));
}
throw ParsingException.CouldNotParseRule(nameof(ParseMergeMatched), t.Peek());
}
private ISqlNode ParseSelectHavingClause(ITokenizer t)
{
// "HAVING" <BooleanExpression>
if (!t.NextIs(SqlTokenType.Keyword, "HAVING"))
{
return(null);
}
t.GetNext();
return(ParseBooleanExpression(t));
}
private ISqlNode ParseWhereClause(ITokenizer t)
{
// "WHERE" <BooleanExpression>
if (!t.NextIs(SqlTokenType.Keyword, "WHERE"))
{
return(null);
}
t.GetNext();
return(ParseBooleanExpression(t));
}
private ISqlNode ParseSelectGroupByClause(ITokenizer t)
{
// "GROUP" "BY" <IdentifierList>
if (!t.NextIs(SqlTokenType.Keyword, "GROUP"))
{
return(null);
}
t.GetNext();
t.Expect(SqlTokenType.Keyword, "BY");
return(ParseList(t, ParseQualifiedIdentifier));
}
private ISqlNode ParseSelectOffsetClause(ITokenizer t)
{
if (!t.NextIs(SqlTokenType.Keyword, "OFFSET"))
{
return(null);
}
t.GetNext();
var offset = ParseNumberOrVariable(t);
t.Expect(SqlTokenType.Keyword, "ROW", "ROWS");
return(offset);
}
private ISqlNode ParseSelectFetchClause(ITokenizer t)
{
if (!t.NextIs(SqlTokenType.Keyword, "FETCH"))
{
return(null);
}
t.GetNext();
t.Expect(SqlTokenType.Keyword, "FIRST", "NEXT");
var fetch = ParseNumberOrVariable(t);
t.Expect(SqlTokenType.Keyword, "ROW", "ROWS");
t.Expect(SqlTokenType.Keyword, "ONLY");
return(fetch);
}
private SqlIfNode ParseIf(ITokenizer t)
{
// "IF" (("(" <Condition> ")") | <Condition>) <Statement> ("ELSE" <Statement>)?
var ifToken = t.Expect(SqlTokenType.Keyword, "IF");
var ifNode = new SqlIfNode
{
Location = ifToken.Location,
Condition = ParseMaybeParenthesis(t, ParseBooleanExpression),
Then = ParseStatement(t)
};
if (t.NextIs(SqlTokenType.Keyword, "ELSE", true))
{
ifNode.Else = ParseStatement(t);
}
return(ifNode);
}
private ISqlNode ParseDeleteStatement(ITokenizer t)
{
var deleteToken = t.Expect(SqlTokenType.Keyword, "DELETE");
// TODO: TOP clause
t.NextIs(SqlTokenType.Keyword, "FROM", true);
var table = ParseObjectIdentifier(t);
var where = ParseWhereClause(t);
// TODO: RETURNING clause
return(new SqlDeleteNode
{
Location = deleteToken.Location,
Source = table,
WhereClause = where
});
}
public SqlMergeNode ParseMergeStatement(ITokenizer t)
{
var mergeToken = t.Expect(SqlTokenType.Keyword, "MERGE");
var mergeNode = new SqlMergeNode
{
Location = mergeToken.Location
};
// TODO: "TOP" <maybeParenVariableOrNumericExpression> "PERCENT"?
t.NextIs(SqlTokenType.Keyword, "INTO", true);
mergeNode.Target = ParseMaybeAliasedTable(t, ParseObjectIdentifier);
t.Expect(SqlTokenType.Keyword, "USING");
mergeNode.Source = ParseMaybeAliasedTable(t, ParseObjectIdentifier);
t.Expect(SqlTokenType.Keyword, "ON");
mergeNode.MergeCondition = ParseBooleanExpression(t);
while (true)
{
var whenClauseToken = t.MaybeGetKeywordSequence("WHEN", "NOT", "MATCHED", "BY", "SOURCE", "TARGET");
if (whenClauseToken == null)
{
break;
}
// TODO: "AND" <clauseSearchCondition>
t.Expect(SqlTokenType.Keyword, "THEN");
if (whenClauseToken.Value == "WHEN MATCHED")
{
// TODO: Allow multiple
mergeNode.Matched = ParseMergeMatched(t);
}
else if (whenClauseToken.Value == "WHEN NOT MATCHED" || whenClauseToken.Value == "WHEN NOT MATCHED BY TARGET")
{
mergeNode.NotMatchedByTarget = ParseMergeNotMatched(t);
}
else if (whenClauseToken.Value == "WHEN NOT MATCHED BY SOURCE")
{
// TODO: Allow multiple
mergeNode.NotMatchedBySource = ParseMergeMatched(t);
}
}
// TODO: Output clause
// TODO: OPTION clause
return(mergeNode);
}
private SqlOrderByNode ParseSelectOrderByClause(ITokenizer t)
{
// "ORDER" "BY" <OrderTerm>+ ("OFFSET" <NumberOrVariable> "ROWS")? ("FETCH" "NEXT" <NumberOrVariable> "ROWS" "ONLY")?
if (!t.NextIs(SqlTokenType.Keyword, "ORDER"))
{
return(null);
}
var orderByToken = t.GetNext();
t.Expect(SqlTokenType.Keyword, "BY");
var orderByItems = ParseList(t, ParseOrderTerm);
return(new SqlOrderByNode
{
Location = orderByToken.Location,
Entries = orderByItems
});
}
private SqlTopLimitNode ParseSelectLimitClause(ITokenizer t)
{
// "LIMIT" <Number>
// TODO: Are there more supported forms?
// TODO: LIMIT x OFFSET y
if (!t.NextIs(SqlTokenType.Keyword, "LIMIT"))
{
return(null);
}
var topToken = t.GetNext();
var numberOrVariable = ParseMaybeParenthesis(t, ParseNumberOrVariable);
return(new SqlTopLimitNode
{
Location = topToken.Location,
Value = numberOrVariable
});
}
private ISqlNode ParseQueryExpression(ITokenizer t)
{
// <querySpecification> ( <UnionOperator> <querySpecification> )*
var firstQuery = ParseQuerySpecificiation(t);
var unionOperator = t.Peek();
if (!unionOperator.IsKeyword("UNION") && !unionOperator.IsKeyword("EXCEPT") && !unionOperator.IsKeyword("INTERSECT"))
{
return(firstQuery);
}
unionOperator = t.GetNext();
return(new SqlInfixOperationNode
{
Location = firstQuery.Location,
Left = firstQuery,
Operator = new SqlOperatorNode(t.NextIs(SqlTokenType.Keyword, "ALL", true) ? "UNION ALL" : unionOperator.Value),
Right = ParseQueryExpression(t)
});
}
private SqlDeclareNode ParseDeclare(ITokenizer t)
{
// "DECLARE" <identifier> <DataType> (":=" <Expression>)?
// TODO: "DECLARE" <identifier> <DataType> (":=" <Expression>)? ("," <identifier> <DataType> (":=" <Expression>)?)*
var declare = t.Expect(SqlTokenType.Keyword, "DECLARE");
var v = t.Expect(SqlTokenType.Identifier);
// TODO: Improve data type parsing
var dataType = ParseDataType(t);
var declareNode = new SqlDeclareNode
{
Location = declare.Location,
DataType = dataType,
Variable = new SqlIdentifierNode(v)
};
if (t.NextIs(SqlTokenType.Symbol, ":=", true))
{
declareNode.Initializer = ParseScalarExpression(t);
}
return(declareNode);
}
private SqlWithCteNode ParseCte(ITokenizer t)
{
// "RECURSIVE"? <identifier> ("(" <columnList> ")")? "AS" "(" <QueryExpression> ")"
bool isRecursive = t.NextIs(SqlTokenType.Keyword, "RECURSIVE", true);
var name = t.Expect(SqlTokenType.Identifier);
var cteNode = new SqlWithCteNode
{
Location = name.Location,
Name = new SqlIdentifierNode(name),
Recursive = isRecursive
};
var lookahead = t.Peek();
if (lookahead.IsSymbol("("))
{
cteNode.ColumnNames = ParseParenthesis(t, x => ParseList(x, ParseIdentifier)).Expression;
}
t.Expect(SqlTokenType.Keyword, "AS");
// TODO: The CTE can contain INSERT, UPDATE and DELETE statements as well (usually with the RETURNING clause)
cteNode.Select = ParseParenthesis(t, ParseQueryExpression).Expression;
return(cteNode);
}
// TODO: "GO" which starts a new logical block and also sets scope limits for variables
private SqlStatementListNode ParseStatementList(ITokenizer t)
{
var statements = new SqlStatementListNode();
while (true)
{
while (t.NextIs(SqlTokenType.Symbol, ";", true))
{
;
}
if (t.Peek().IsType(SqlTokenType.EndOfInput))
{
break;
}
var statement = ParseStatement(t);
if (statement == null)
{
throw ParsingException.CouldNotParseRule(nameof(ParseStatement), t.Peek());
}
statements.Statements.Add(statement);
}
return(statements);
}
private SqlOperatorNode ParseJoinOperator(ITokenizer t)
{
// "CROSS" ("APPLY" | "JOIN")
// "NATURAL" "JOIN"
// "INNER" "JOIN"
// ("LEFT" | "RIGHT")? "OUTER"? "JOIN"
var k = t.GetNext();
if (!k.IsKeyword())
{
t.PutBack(k);
return(null);
}
if (k.Value == "CROSS")
{
if (t.NextIs(SqlTokenType.Keyword, "APPLY", true))
{
return(new SqlOperatorNode("CROSS APPLY", k.Location));
}
if (t.NextIs(SqlTokenType.Keyword, "JOIN", true))
{
return(new SqlOperatorNode("CROSS JOIN", k.Location));
}
throw ParsingException.CouldNotParseRule(nameof(ParseJoinOperator), k);
}
if (k.Value == "NATURAL")
{
t.Expect(SqlTokenType.Keyword, "JOIN");
return(new SqlOperatorNode("NATURAL JOIN", k.Location));
}
if (k.Value == "INNER")
{
t.Expect(SqlTokenType.Keyword, "JOIN");
return(new SqlOperatorNode("INNER JOIN", k.Location));
}
var joinOperator = new List <SqlToken>();
var location = k.Location;
if (k.Value == "FULL" || k.Value == "LEFT" || k.Value == "RIGHT")
{
joinOperator.Add(k);
k = t.GetNext();
}
if (k.Value == "OUTER")
{
joinOperator.Add(k);
k = t.GetNext();
if (k.Value == "APPLY")
{
return(new SqlOperatorNode("OUTER APPLY"));
}
}
// TODO: hints: "MERGE" | "HASH" | "REDISTRIBUTE" | "REPLICATE" | "REDUCE"
if (k.Value == "JOIN")
{
joinOperator.Add(k);
var op = string.Join(" ", joinOperator.Select(j => j.Value));
return(new SqlOperatorNode(op, location));
}
if (joinOperator.Count > 0)
{
throw ParsingException.CouldNotParseRule(nameof(ParseJoinOperator), k);
}
t.PutBack(k);
return(null);
}
public SqlExecuteArgumentNode ParseExecArgument(ITokenizer t)
{
// (<parameter> "=")? (<Expression> | "DEFAULT" | <Variable> ("OUT"|"OUTPUT"))
var next = t.GetNext();
if (next.IsSymbol(";") || next.IsType(SqlTokenType.EndOfInput))
{
return(null);
}
if (next.IsKeyword("DEFAULT"))
{
var keyword = new SqlKeywordNode(next);
return(new SqlExecuteArgumentNode
{
Location = keyword.Location,
Value = keyword
});
}
if (next.IsType(SqlTokenType.Variable))
{
var lookahead = t.Peek();
if (lookahead.IsSymbol("="))
{
t.GetNext();
return(new SqlExecuteArgumentNode
{
Location = next.Location,
AssignVariable = new SqlVariableNode(next),
Value = ParseScalarExpression(t),
IsOut = t.NextIs(SqlTokenType.Keyword, "OUT", true) || t.NextIs(SqlTokenType.Keyword, "OUTPUT", true)
});
}
}
ISqlNode expression = null;
if (next.IsType(SqlTokenType.Identifier, SqlTokenType.Number, SqlTokenType.QuotedString, SqlTokenType.Variable))
{
t.PutBack(next);
expression = ParseScalarExpression(t);
}
else if (next.IsType(SqlTokenType.Keyword))
{
if (t.Peek().IsSymbol("("))
{
t.PutBack(next);
expression = ParseScalarExpression(t);
}
}
if (expression == null)
{
t.PutBack(next);
return(null);
}
return(new SqlExecuteArgumentNode
{
Location = next.Location,
Value = expression,
IsOut = t.NextIs(SqlTokenType.Keyword, "OUT", true) || t.NextIs(SqlTokenType.Keyword, "OUTPUT", true)
});
}
private ISqlNode ParseBooleanExpression1(ITokenizer t)
{
// <ScalarExpression> <ComparisonOperator> <ScalarExpression>
// <ScalarExpression> "IS" "NOT"? "NULL"
// <ScalarExpression> "NOT"? "BETWEEN" <ScalarExpression> "AND" <ScalarExpression>
// <ScalarExpression> "NOT"? "IN" "(" <ValueList> ")"
// <ScalarExpression> "NOT"? "LIKE" <String>
var left = ParseScalarExpression(t);
var operatorToken = t.Peek();
if (operatorToken.IsSymbol(">", "<", "=", "<=", ">=", "!=", "<>"))
{
t.GetNext();
if (t.Peek().IsKeyword("ALL", "ANY", "SOME"))
{
var prefix = t.GetNext();
var query = ParseParenthesis(t, ParseQueryExpression);
return(new SqlInfixOperationNode
{
Location = operatorToken.Location,
Left = left,
Right = query,
Operator = new SqlOperatorNode(operatorToken.Value + " " + prefix.Value)
});
}
var right = ParseScalarExpression(t);
return(new SqlInfixOperationNode
{
Location = operatorToken.Location,
Left = left,
Right = right,
Operator = new SqlOperatorNode(operatorToken)
});
}
if (operatorToken.IsKeyword("IS"))
{
t.GetNext();
var not = t.NextIs(SqlTokenType.Keyword, "NOT", true);
var nullToken = t.Expect(SqlTokenType.Keyword, "NULL");
return(new SqlInfixOperationNode
{
Location = operatorToken.Location,
Left = left,
Operator = new SqlOperatorNode
{
Location = operatorToken.Location,
Operator = not ? "IS NOT" : "IS"
},
Right = new SqlNullNode(nullToken)
});
}
var isNot = false;
if (operatorToken.IsKeyword("NOT"))
{
t.GetNext();
isNot = true;
operatorToken = t.Peek();
}
if (operatorToken.IsKeyword("BETWEEN"))
{
t.GetNext();
var first = ParseScalarExpression(t);
t.Expect(SqlTokenType.Keyword, "AND");
var second = ParseScalarExpression(t);
return(new SqlBetweenOperationNode
{
Location = operatorToken.Location,
Not = isNot,
Left = left,
Low = first,
High = second
});
}
if (operatorToken.IsKeyword("IN"))
{
t.GetNext();
// TODO: "IN" "(" <QueryExpression> ")"
var list = ParseParenthesis(t, t2 => ParseList(t2, ParseVariableOrConstant)).Expression;
return(new SqlInNode
{
Not = isNot,
Search = left,
Location = operatorToken.Location,
Items = list
});
}
if (operatorToken.IsKeyword("LIKE"))
{
t.GetNext();
var right = ParseMaybeParenthesis(t, ParseString);
return(new SqlInfixOperationNode
{
Left = left,
Operator = new SqlOperatorNode
{
Location = operatorToken.Location,
Operator = (isNot ? "NOT " : "") + "LIKE"
},
Right = right
});
}
// There's no operator, it's just a scalar expression in a boolean context.
// This happens in CASE statements, for example. Return the scalar expression
// as-is and let the AST deal with it.
return(left);
}
