public void SelectStatement_CommonSelect()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"select t.a, t.b, (select 1) as e
into #tempt
from
[table] t
inner join [table] t2 on
t.id = t2.id
where
t.c = 5
group by
t.a,
t.b
having
count(*) > 1
order by
t.a,
t.b;" ,
includeWhitespace: true);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
Assert.AreEqual(98, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.SELECT, select.Tokens[0].AsKeyword.Keyword);
TSQLSelectClause selectClause = select.Select;
Assert.AreEqual(3, selectClause.Columns.Count);
TSQLSelectColumn column = selectClause.Columns[0];
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.AreEqual("t", column.Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("a", column.Expression.AsColumn.Column.Name);
column = selectClause.Columns[1];
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.AreEqual("t", column.Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("b", column.Expression.AsColumn.Column.Name);
column = selectClause.Columns[2];
Assert.AreEqual("e", column.ColumnAlias.AsIdentifier.Name);
Assert.AreEqual(TSQLExpressionType.Subquery, column.Expression.Type);
TSQLSubqueryExpression subquery = column.Expression.AsSubquery;
Assert.AreEqual(1, subquery.Select.Select.Columns.Count);
Assert.AreEqual(1, subquery.Select.Select.Columns[0].Expression.AsConstant.Literal.AsNumericLiteral.Value);
Assert.AreEqual(" ", select.Tokens[1].AsWhitespace.Text);
Assert.AreEqual("t", select.Tokens[2].AsIdentifier.Name);
Assert.AreEqual(TSQLCharacters.Period, select.Tokens[3].AsCharacter.Character);
Assert.AreEqual(22, select.Select.Tokens.Count);
Assert.AreEqual(4, select.Into.Tokens.Count);
Assert.AreEqual(26, select.From.Tokens.Count);
Assert.AreEqual(10, select.Where.Tokens.Count);
Assert.AreEqual(13, select.GroupBy.Tokens.Count);
Assert.AreEqual(11, select.Having.Tokens.Count);
Assert.AreEqual(12, select.OrderBy.Tokens.Count);
Assert.AreEqual(3, select.Select.Columns.Count);
Assert.AreEqual("e", select.Select.Columns[2].ColumnAlias.Text);
}
public TSQLSelectClause Parse(ITSQLTokenizer tokenizer)
{
TSQLSelectClause select = new TSQLSelectClause();
if (!tokenizer.Current.IsKeyword(TSQLKeywords.SELECT))
{
throw new InvalidOperationException("SELECT expected.");
}
select.Tokens.Add(tokenizer.Current);
// can contain ALL, DISTINCT, TOP, PERCENT, WITH TIES, AS
// ends with FROM, semicolon, or keyword other than those listed above, when used outside of parens
// recursively walk down and back up parens
int nestedLevel = 0;
while (
tokenizer.MoveNext() &&
!tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon) &&
!(
nestedLevel == 0 &&
tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses)
) &&
(
nestedLevel > 0 ||
tokenizer.Current.Type != TSQLTokenType.Keyword ||
(
tokenizer.Current.Type == TSQLTokenType.Keyword &&
!tokenizer.Current.AsKeyword.Keyword.In
(
TSQLKeywords.INTO,
TSQLKeywords.FROM,
TSQLKeywords.WHERE,
TSQLKeywords.GROUP,
TSQLKeywords.HAVING,
TSQLKeywords.ORDER,
TSQLKeywords.UNION,
TSQLKeywords.EXCEPT,
TSQLKeywords.INTERSECT,
TSQLKeywords.FOR,
TSQLKeywords.OPTION
) &&
!tokenizer.Current.AsKeyword.Keyword.IsStatementStart()
)
))
{
TSQLSubqueryHelper.RecurseParens(
tokenizer,
select,
ref nestedLevel);
}
return(select);
}
public static List <SelectItem> Fields(this TSQLSelectClause selectClause)
{
return(selectClause.Tokens
.Where(t => t.Type == TSQLTokenType.Identifier)
.Select(f => new SelectItem()
{
Column = f.Text.ToString()
}).ToList());
}
public void SelectStatement_MultiLevelParens()
{
string query = "SELECT ((A/B)-1) FROM SomeTable";
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(query);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
TSQLSelectStatement selectStatement = statements[0].AsSelect;
Assert.AreEqual(12, selectStatement.Tokens.Count);
TSQLSelectClause selectClause = selectStatement.Select;
Assert.AreEqual(1, selectClause.Columns.Count);
// outer parens
TSQLExpression lvl1Expression = selectClause.Columns[0].Expression;
Assert.AreEqual(TSQLExpressionType.Grouped, lvl1Expression.Type);
// contents of outer parens
TSQLExpression lvl2Expression = lvl1Expression.AsGrouped.InnerExpression;
Assert.AreEqual(TSQLExpressionType.Operator, lvl2Expression.Type);
Assert.AreEqual("-", lvl2Expression.AsOperator.Operator.Text);
// (A/B)
TSQLExpression lvl2aExpression = lvl2Expression.AsOperator.LeftSide;
// 1
TSQLExpression lvl2bExpression = lvl2Expression.AsOperator.RightSide;
Assert.AreEqual(TSQLExpressionType.Grouped, lvl2aExpression.Type);
Assert.AreEqual(TSQLExpressionType.Constant, lvl2bExpression.Type);
Assert.AreEqual(1, lvl2bExpression.AsConstant.Literal.AsNumericLiteral.Value);
// A/B
TSQLExpression lvl3Expression = lvl2aExpression.AsGrouped.InnerExpression;
Assert.AreEqual(TSQLExpressionType.Operator, lvl3Expression.Type);
Assert.AreEqual("/", lvl3Expression.AsOperator.Operator.Text);
// A
TSQLExpression lvl3aExpression = lvl3Expression.AsOperator.LeftSide;
// B
TSQLExpression lvl3bExpression = lvl3Expression.AsOperator.RightSide;
Assert.AreEqual(TSQLExpressionType.Column, lvl3aExpression.Type);
Assert.AreEqual("A", lvl3aExpression.AsColumn.Column.Name);
Assert.IsNull(lvl3aExpression.AsColumn.TableReference);
Assert.AreEqual(TSQLExpressionType.Column, lvl3bExpression.Type);
Assert.AreEqual("B", lvl3bExpression.AsColumn.Column.Name);
Assert.IsNull(lvl3bExpression.AsColumn.TableReference);
}
public TSQLSelectClause Parse(ITSQLTokenizer tokenizer)
{
TSQLSelectClause select = new TSQLSelectClause();
if (!tokenizer.Current.IsKeyword(TSQLKeywords.SELECT))
{
throw new InvalidOperationException("SELECT expected.");
}
select.Tokens.Add(tokenizer.Current);
// can contain ALL, DISTINCT, TOP, PERCENT, WITH TIES, AS
// ends with FROM, semicolon, or keyword other than those listed above, when used outside of parens
// recursively walk down and back up parens
TSQLSubqueryHelper.ReadUntilStop(
tokenizer,
select,
new List <TSQLFutureKeywords>()
{
},
new List <TSQLKeywords>()
{
TSQLKeywords.INTO,
TSQLKeywords.FROM,
TSQLKeywords.WHERE,
TSQLKeywords.GROUP,
TSQLKeywords.HAVING,
TSQLKeywords.ORDER,
TSQLKeywords.UNION,
TSQLKeywords.EXCEPT,
TSQLKeywords.INTERSECT,
TSQLKeywords.FOR,
TSQLKeywords.OPTION
},
lookForStatementStarts: true);
return(select);
}
public TSQLSelectClause Parse(TSQLTokenizer tokenizer)
{
TSQLSelectClause select = new TSQLSelectClause();
if (
tokenizer.Current == null ||
tokenizer.Current.Type != TSQLTokenType.Keyword ||
tokenizer.Current.AsKeyword.Keyword != TSQLKeywords.SELECT)
{
throw new ApplicationException("SELECT expected.");
}
select.Tokens.Add(tokenizer.Current);
// can contain ALL, DISTINCT, TOP, PERCENT, WITH TIES, AS
// ends with FROM, semicolon, or keyword other than those listed above, when used outside of parens
// recursively walk down and back up parens
int nestedLevel = 0;
while (
tokenizer.Read() &&
!(
tokenizer.Current.Type == TSQLTokenType.Character &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.Semicolon
) &&
!(
nestedLevel == 0 &&
tokenizer.Current.Type == TSQLTokenType.Character &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.CloseParentheses
) &&
(
nestedLevel > 0 ||
tokenizer.Current.Type != TSQLTokenType.Keyword ||
(
tokenizer.Current.Type == TSQLTokenType.Keyword &&
tokenizer.Current.AsKeyword.Keyword.In
(
TSQLKeywords.ALL,
TSQLKeywords.AS,
TSQLKeywords.DISTINCT,
TSQLKeywords.PERCENT,
TSQLKeywords.TOP,
TSQLKeywords.WITH,
TSQLKeywords.NULL,
TSQLKeywords.CASE,
TSQLKeywords.WHEN,
TSQLKeywords.THEN,
TSQLKeywords.ELSE,
TSQLKeywords.AND,
TSQLKeywords.OR,
TSQLKeywords.BETWEEN,
TSQLKeywords.EXISTS,
TSQLKeywords.END,
TSQLKeywords.IN,
TSQLKeywords.IS,
TSQLKeywords.NOT,
TSQLKeywords.OVER,
TSQLKeywords.IDENTITY,
TSQLKeywords.LIKE
)
)
))
{
select.Tokens.Add(tokenizer.Current);
if (tokenizer.Current.Type == TSQLTokenType.Character)
{
TSQLCharacters character = tokenizer.Current.AsCharacter.Character;
if (character == TSQLCharacters.OpenParentheses)
{
// should we recurse for correlated subqueries?
nestedLevel++;
if (tokenizer.Read())
{
if (
tokenizer.Current.Type == TSQLTokenType.Keyword &&
tokenizer.Current.AsKeyword.Keyword == TSQLKeywords.SELECT)
{
TSQLSelectStatement selectStatement = new TSQLSelectStatementParser().Parse(tokenizer);
select.Tokens.AddRange(selectStatement.Tokens);
if (
tokenizer.Current != null &&
tokenizer.Current.Type == TSQLTokenType.Character &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.CloseParentheses)
{
nestedLevel--;
select.Tokens.Add(tokenizer.Current);
}
}
else
{
select.Tokens.Add(tokenizer.Current);
}
}
}
else if (character == TSQLCharacters.CloseParentheses)
{
nestedLevel--;
}
}
}
return(select);
}
public TSQLSelectClause Parse(ITSQLTokenizer tokenizer)
{
TSQLSelectClause select = new TSQLSelectClause();
if (!tokenizer.Current.IsKeyword(TSQLKeywords.SELECT))
{
throw new InvalidOperationException("SELECT expected.");
}
select.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
select);
if (tokenizer.Current.IsKeyword(TSQLKeywords.ALL) ||
tokenizer.Current.IsKeyword(TSQLKeywords.DISTINCT))
{
select.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
select);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.TOP))
{
select.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
select);
if (tokenizer.Current.IsCharacter(TSQLCharacters.OpenParentheses))
{
select.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
select);
// handling for TOP(@RowsToReturn)
// can also be used in a CROSS APPLY with an outer reference, e.g. TOP(p.RowCount)
if (tokenizer.Current != null &&
tokenizer.Current.Type.In(
TSQLTokenType.NumericLiteral,
TSQLTokenType.Identifier,
TSQLTokenType.IncompleteIdentifier,
TSQLTokenType.SystemVariable,
TSQLTokenType.Variable))
{
select.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
select);
}
if (tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses))
{
select.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
select);
}
}
else if (tokenizer.Current != null &&
tokenizer.Current.Type == TSQLTokenType.NumericLiteral)
{
select.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
select);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.PERCENT))
{
select.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
select);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.WITH))
{
select.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
select);
if (tokenizer.Current != null &&
tokenizer.Current.Type == TSQLTokenType.Identifier &&
tokenizer.Current.AsIdentifier.Text.Equals(
"TIES",
StringComparison.InvariantCultureIgnoreCase))
{
select.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
select);
}
}
}
while (
tokenizer.Current != null &&
!tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon) &&
!tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses) &&
!(
tokenizer.Current.Type == TSQLTokenType.Keyword &&
(
tokenizer.Current.AsKeyword.Keyword.In(
TSQLKeywords.INTO,
TSQLKeywords.FROM,
TSQLKeywords.WHERE,
TSQLKeywords.GROUP,
TSQLKeywords.HAVING,
TSQLKeywords.ORDER,
TSQLKeywords.UNION,
TSQLKeywords.EXCEPT,
TSQLKeywords.INTERSECT,
TSQLKeywords.FOR,
TSQLKeywords.OPTION) ||
tokenizer.Current.AsKeyword.Keyword.IsStatementStart()
)
))
{
TSQLSelectColumn column = new TSQLSelectColumnParser().Parse(tokenizer);
select.Tokens.AddRange(column.Tokens);
select.Columns.Add(column);
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
select);
if (tokenizer.Current.IsCharacter(TSQLCharacters.Comma))
{
select.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
select);
}
}
return(select);
}
public TSQLSelectClause Parse(TSQLTokenizer tokenizer)
{
TSQLSelectClause select = new TSQLSelectClause();
if (
tokenizer.Current == null ||
tokenizer.Current.Type != TSQLTokenType.Keyword ||
tokenizer.Current.AsKeyword.Keyword != TSQLKeywords.SELECT)
{
throw new ApplicationException("SELECT expected.");
}
select.Tokens.Add(tokenizer.Current);
// can contain ALL, DISTINCT, TOP, PERCENT, WITH TIES, AS
// ends with FROM, semicolon, or keyword other than those listed above, when used outside of parens
// recursively walk down and back up parens
int nestedLevel = 0;
while (
tokenizer.Read() &&
!(
tokenizer.Current.Type == TSQLTokenType.Character &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.Semicolon
) &&
!(
nestedLevel == 0 &&
tokenizer.Current.Type == TSQLTokenType.Character &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.CloseParentheses
) &&
(
nestedLevel > 0 ||
tokenizer.Current.Type != TSQLTokenType.Keyword ||
(
tokenizer.Current.Type == TSQLTokenType.Keyword &&
tokenizer.Current.AsKeyword.Keyword.In
(
TSQLKeywords.ALL,
TSQLKeywords.AS,
TSQLKeywords.DISTINCT,
TSQLKeywords.PERCENT,
TSQLKeywords.TOP,
TSQLKeywords.WITH,
TSQLKeywords.NULL,
TSQLKeywords.CASE,
TSQLKeywords.WHEN,
TSQLKeywords.THEN,
TSQLKeywords.ELSE,
TSQLKeywords.AND,
TSQLKeywords.OR,
TSQLKeywords.BETWEEN,
TSQLKeywords.EXISTS,
TSQLKeywords.END,
TSQLKeywords.IN,
TSQLKeywords.IS,
TSQLKeywords.NOT,
TSQLKeywords.OVER,
TSQLKeywords.IDENTITY,
TSQLKeywords.LIKE
)
)
))
{
select.Tokens.Add(tokenizer.Current);
if (tokenizer.Current.Type == TSQLTokenType.Character)
{
TSQLCharacters character = tokenizer.Current.AsCharacter.Character;
if (character == TSQLCharacters.OpenParentheses)
{
// should we recurse for correlated subqueries?
nestedLevel++;
if (tokenizer.Read())
{
if (
tokenizer.Current.Type == TSQLTokenType.Keyword &&
tokenizer.Current.AsKeyword.Keyword == TSQLKeywords.SELECT)
{
TSQLSelectStatement selectStatement = new TSQLSelectStatementParser().Parse(tokenizer);
select.Tokens.AddRange(selectStatement.Tokens);
if (
tokenizer.Current != null &&
tokenizer.Current.Type == TSQLTokenType.Character &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.CloseParentheses)
{
nestedLevel--;
select.Tokens.Add(tokenizer.Current);
}
}
else
{
select.Tokens.Add(tokenizer.Current);
}
}
}
else if (character == TSQLCharacters.CloseParentheses)
{
nestedLevel--;
}
}
}
return select;
}
