public void SelectClause_Comments()
{
using (StringReader reader = new StringReader(
@"select top 1
oh.TaxAmt / oh.SubTotal /* tax percent */
from
Sales.SalesOrderHeader oh;" ))
using (ITSQLTokenizer tokenizer = new TSQLTokenizer(reader))
{
Assert.IsTrue(tokenizer.MoveNext());
TSQLSelectClause select = new TSQLSelectClauseParser().Parse(tokenizer);
Assert.AreEqual(11, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.FROM, tokenizer.Current.AsKeyword.Keyword);
Assert.AreEqual(1, select.Columns.Count);
Assert.IsNull(select.Columns[0].ColumnAlias);
Assert.AreEqual(TSQLExpressionType.Operator, select.Columns[0].Expression.Type);
TSQLOperatorExpression operatorExpression = select.Columns[0].Expression.AsOperator;
Assert.AreEqual("/", operatorExpression.Operator.Text);
Assert.AreEqual(TSQLExpressionType.Column, operatorExpression.LeftSide.Type);
TSQLColumnExpression leftSide = operatorExpression.LeftSide.AsColumn;
Assert.AreEqual("oh", leftSide.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("TaxAmt", leftSide.Column.Name);
Assert.AreEqual(TSQLExpressionType.Column, operatorExpression.RightSide.Type);
TSQLColumnExpression rightSide = operatorExpression.RightSide.AsColumn;
Assert.AreEqual("oh", rightSide.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("SubTotal", rightSide.Column.Name);
Assert.AreEqual(" tax percent ", select.Columns.Last().Tokens.Last().AsMultilineComment.Comment);
}
}
public void SelectClause_UnaryOperator()
{
using (StringReader reader = new StringReader(
@"SELECT
+1;" ))
using (ITSQLTokenizer tokenizer = new TSQLTokenizer(reader))
{
Assert.IsTrue(tokenizer.MoveNext());
TSQLSelectClause select = new TSQLSelectClauseParser().Parse(tokenizer);
Assert.AreEqual(3, select.Tokens.Count);
Assert.IsTrue(tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon));
Assert.AreEqual(1, select.Columns.Count);
TSQLSelectColumn column = select.Columns[0];
Assert.IsNull(column.ColumnAlias);
Assert.AreEqual(TSQLExpressionType.Operator, column.Expression.Type);
TSQLOperatorExpression tsqlOperator = column.Expression.AsOperator;
Assert.AreEqual("+", tsqlOperator.Operator.Text);
Assert.IsNull(tsqlOperator.LeftSide);
Assert.AreEqual(TSQLExpressionType.Constant, tsqlOperator.RightSide.Type);
Assert.AreEqual(1, tsqlOperator.RightSide.AsConstant.Literal.AsNumericLiteral.Value);
}
}
public void SelectClause_VariableAssignment()
{
using (StringReader reader = new StringReader(
@"SELECT @id = p.ProductID
FROM Production.Product p
WHERE
p.[Name] = 'Blade';" ))
using (ITSQLTokenizer tokenizer = new TSQLTokenizer(reader))
{
Assert.IsTrue(tokenizer.MoveNext());
TSQLSelectClause select = new TSQLSelectClauseParser().Parse(tokenizer);
Assert.AreEqual(6, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.FROM, tokenizer.Current.AsKeyword.Keyword);
Assert.AreEqual(1, select.Columns.Count);
TSQLSelectColumn column = select.Columns[0];
Assert.IsNull(column.ColumnAlias);
Assert.AreEqual(TSQLExpressionType.VariableAssignment, column.Expression.Type);
TSQLVariableAssignmentExpression assignmentExpression = column.Expression.AsVariableAssignment;
Assert.AreEqual("=", assignmentExpression.Operator.Text);
Assert.AreEqual("@id", assignmentExpression.Variable.Text);
TSQLColumnExpression columnExpression = assignmentExpression.ValueExpression.AsColumn;
Assert.AreEqual("p", columnExpression.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("ProductID", columnExpression.Column.Name);
}
}
public void SelectClause_FullyQualifiedFunction()
{
using (StringReader reader = new StringReader(
@"SELECT
p.ProductID,
p.[Name],
Test.dbo.Multiply(p.SafetyStockLevel, p.StandardCost) AS RestockCost
FROM
Production.Product p
ORDER BY
p.[Name];" ))
using (ITSQLTokenizer tokenizer = new TSQLTokenizer(reader))
{
Assert.IsTrue(tokenizer.MoveNext());
TSQLSelectClause select = new TSQLSelectClauseParser().Parse(tokenizer);
Assert.AreEqual(25, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.FROM, tokenizer.Current.AsKeyword.Keyword);
Assert.AreEqual(3, select.Columns.Count);
TSQLSelectColumn column = select.Columns[0];
Assert.IsNull(column.ColumnAlias);
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.AreEqual("p", column.Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("ProductID", column.Expression.AsColumn.Column.Name);
column = select.Columns[1];
Assert.IsNull(column.ColumnAlias);
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.AreEqual("p", column.Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("Name", column.Expression.AsColumn.Column.Name);
column = select.Columns[2];
Assert.AreEqual("RestockCost", column.ColumnAlias.Name);
Assert.AreEqual(TSQLExpressionType.Function, column.Expression.Type);
TSQLFunctionExpression functionExpression = column.Expression.AsFunction;
Assert.AreEqual("Multiply", functionExpression.Function.Name);
Assert.AreEqual(3, functionExpression.QualifiedPath.Count);
Assert.AreEqual("Test", functionExpression.QualifiedPath[0].AsIdentifier.Name);
Assert.AreEqual(".", functionExpression.QualifiedPath[1].AsCharacter.Text);
Assert.AreEqual("dbo", functionExpression.QualifiedPath[2].AsIdentifier.Name);
TSQLColumnExpression argumentExpression = functionExpression.Arguments[0].AsColumn;
Assert.AreEqual("p", argumentExpression.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("SafetyStockLevel", argumentExpression.Column.Name);
argumentExpression = functionExpression.Arguments[1].AsColumn;
Assert.AreEqual("p", argumentExpression.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("StandardCost", argumentExpression.Column.Name);
}
}
public void SelectClause_StopAtFrom()
{
using (StringReader reader = new StringReader(@"select a from b;"))
using (IEnumerator <TSQLToken> tokenizer = new TSQLTokenizer(reader))
{
Assert.IsTrue(tokenizer.MoveNext());
TSQLSelectClause select = new TSQLSelectClauseParser().Parse(tokenizer);
Assert.AreEqual(2, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.FROM, tokenizer.Current.AsKeyword.Keyword);
}
}
public void SelectClause_SanityCheck()
{
using (StringReader reader = new StringReader(
@"bogus"))
using (ITSQLTokenizer tokenizer = new TSQLTokenizer(reader))
{
Assert.IsTrue(tokenizer.MoveNext());
Exception ex = Assert.Throws <InvalidOperationException>(
delegate
{
TSQLSelectClause select = new TSQLSelectClauseParser().Parse(tokenizer);
});
}
}
public void SelectClause_Comments()
{
using (StringReader reader = new StringReader(
@"select top 1
oh.TaxAmt / oh.SubTotal /* tax percent */
from
Sales.SalesOrderHeader oh;" ))
using (ITSQLTokenizer tokenizer = new TSQLTokenizer(reader))
{
Assert.IsTrue(tokenizer.MoveNext());
TSQLSelectClause select = new TSQLSelectClauseParser().Parse(tokenizer);
Assert.AreEqual(11, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.FROM, tokenizer.Current.AsKeyword.Keyword);
}
}
public void SelectClause_WindowedAggregate()
{
using (StringReader reader = new StringReader(
@"SELECT
p.ProductID,
p.[Name],
ROW_NUMBER() OVER (
PARTITION BY
p.DaysToManufacture
ORDER BY
p.[Name]) AS GroupNumber
FROM
Production.Product p
ORDER BY
p.DaysToManufacture,
p.[Name];" ))
using (ITSQLTokenizer tokenizer = new TSQLTokenizer(reader))
{
Assert.IsTrue(tokenizer.MoveNext());
TSQLSelectClause select = new TSQLSelectClauseParser().Parse(tokenizer);
Assert.AreEqual(27, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.FROM, tokenizer.Current.AsKeyword.Keyword);
Assert.AreEqual(3, select.Columns.Count);
TSQLSelectColumn column = select.Columns[0];
Assert.IsNull(column.ColumnAlias);
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.AreEqual("p", column.Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("ProductID", column.Expression.AsColumn.Column.Name);
column = select.Columns[1];
Assert.IsNull(column.ColumnAlias);
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.AreEqual("p", column.Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("Name", column.Expression.AsColumn.Column.Name);
column = select.Columns[2];
Assert.AreEqual("GroupNumber", column.ColumnAlias.Name);
Assert.AreEqual(TSQLExpressionType.Function, column.Expression.Type);
Assert.AreEqual("ROW_NUMBER", column.Expression.AsFunction.Function.Name);
}
}
public void SelectClause_StopAtFrom()
{
using (StringReader reader = new StringReader(@"select a from b;"))
using (ITSQLTokenizer tokenizer = new TSQLTokenizer(reader))
{
Assert.IsTrue(tokenizer.MoveNext());
TSQLSelectClause select = new TSQLSelectClauseParser().Parse(tokenizer);
Assert.AreEqual(2, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.FROM, tokenizer.Current.AsKeyword.Keyword);
Assert.AreEqual(1, select.Columns.Count);
Assert.IsNull(select.Columns[0].ColumnAlias);
Assert.AreEqual(TSQLExpressionType.Column, select.Columns[0].Expression.Type);
TSQLColumnExpression column = select.Columns[0].Expression.AsColumn;
Assert.IsNull(column.TableReference);
Assert.AreEqual("a", column.Column.Name);
}
}
public TSQLSelectStatement Parse(ITSQLTokenizer tokenizer)
{
TSQLSelectStatement select = new TSQLSelectStatement();
TSQLSelectClause selectClause = new TSQLSelectClauseParser().Parse(tokenizer);
select.Select = selectClause;
select.Tokens.AddRange(selectClause.Tokens);
if (tokenizer.Current.IsKeyword(TSQLKeywords.INTO))
{
TSQLIntoClause intoClause = new TSQLIntoClauseParser().Parse(tokenizer);
select.Into = intoClause;
select.Tokens.AddRange(intoClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.FROM))
{
TSQLFromClause fromClause = new TSQLFromClauseParser().Parse(tokenizer);
select.From = fromClause;
select.Tokens.AddRange(fromClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.WHERE))
{
TSQLWhereClause whereClause = new TSQLWhereClauseParser().Parse(tokenizer);
select.Where = whereClause;
select.Tokens.AddRange(whereClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.GROUP))
{
TSQLGroupByClause groupByClause = new TSQLGroupByClauseParser().Parse(tokenizer);
select.GroupBy = groupByClause;
select.Tokens.AddRange(groupByClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.HAVING))
{
TSQLHavingClause havingClause = new TSQLHavingClauseParser().Parse(tokenizer);
select.Having = havingClause;
select.Tokens.AddRange(havingClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.ORDER))
{
TSQLOrderByClause orderByClause = new TSQLOrderByClauseParser().Parse(tokenizer);
select.OrderBy = orderByClause;
select.Tokens.AddRange(orderByClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.OPTION))
{
TSQLOptionClause optionClause = new TSQLOptionClauseParser().Parse(tokenizer);
select.Option = optionClause;
select.Tokens.AddRange(optionClause.Tokens);
}
if (
tokenizer.Current != null &&
tokenizer.Current.Type == TSQLTokenType.Keyword)
{
tokenizer.Putback();
}
return(select);
}
public TSQLSelectStatement Parse(TSQLTokenizer tokenizer)
{
TSQLSelectStatement select = new TSQLSelectStatement();
// should whitespace be excluded from statement parsing logic?
// should I differentiate keywords that start commands?
// correlated subqueries
// scalar function calls
// SELECT clause
TSQLSelectClause selectClause = new TSQLSelectClauseParser().Parse(tokenizer);
select.Select = selectClause;
select.Tokens.AddRange(selectClause.Tokens);
if (tokenizer.Current.IsKeyword(TSQLKeywords.INTO))
{
TSQLIntoClause intoClause = new TSQLIntoClauseParser().Parse(tokenizer);
select.Into = intoClause;
select.Tokens.AddRange(intoClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.FROM))
{
TSQLFromClause fromClause = new TSQLFromClauseParser().Parse(tokenizer);
select.From = fromClause;
select.Tokens.AddRange(fromClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.WHERE))
{
TSQLWhereClause whereClause = new TSQLWhereClauseParser().Parse(tokenizer);
select.Where = whereClause;
select.Tokens.AddRange(whereClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.GROUP))
{
TSQLGroupByClause groupByClause = new TSQLGroupByClauseParser().Parse(tokenizer);
select.GroupBy = groupByClause;
select.Tokens.AddRange(groupByClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.HAVING))
{
TSQLHavingClause havingClause = new TSQLHavingClauseParser().Parse(tokenizer);
select.Having = havingClause;
select.Tokens.AddRange(havingClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.ORDER))
{
TSQLOrderByClause orderByClause = new TSQLOrderByClauseParser().Parse(tokenizer);
select.OrderBy = orderByClause;
select.Tokens.AddRange(orderByClause.Tokens);
}
if (tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon))
{
select.Tokens.Add(tokenizer.Current);
}
return(select);
}
public TSQLSelectStatement Parse()
{
TSQLSelectClause selectClause = new TSQLSelectClauseParser().Parse(Tokenizer);
Statement.Select = selectClause;
Statement.Tokens.AddRange(selectClause.Tokens);
if (Tokenizer.Current.IsKeyword(TSQLKeywords.INTO))
{
TSQLIntoClause intoClause = new TSQLIntoClauseParser().Parse(Tokenizer);
Statement.Into = intoClause;
Statement.Tokens.AddRange(intoClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.FROM))
{
TSQLFromClause fromClause = new TSQLFromClauseParser().Parse(Tokenizer);
Statement.From = fromClause;
Statement.Tokens.AddRange(fromClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.WHERE))
{
TSQLWhereClause whereClause = new TSQLWhereClauseParser().Parse(Tokenizer);
Statement.Where = whereClause;
Statement.Tokens.AddRange(whereClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.GROUP))
{
TSQLGroupByClause groupByClause = new TSQLGroupByClauseParser().Parse(Tokenizer);
Statement.GroupBy = groupByClause;
Statement.Tokens.AddRange(groupByClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.HAVING))
{
TSQLHavingClause havingClause = new TSQLHavingClauseParser().Parse(Tokenizer);
Statement.Having = havingClause;
Statement.Tokens.AddRange(havingClause.Tokens);
}
// ORDER not allowed
// FOR not allowed
// OPTION not allowed
if (
Tokenizer.Current?.AsKeyword != null &&
Tokenizer.Current.AsKeyword.Keyword.IsStatementStart())
{
Tokenizer.Putback();
}
return Statement;
}
public TSQLSelectStatement Parse()
{
TSQLSelectClause selectClause = new TSQLSelectClauseParser().Parse(Tokenizer);
Statement.Select = selectClause;
Statement.Tokens.AddRange(selectClause.Tokens);
if (Tokenizer.Current.IsKeyword(TSQLKeywords.INTO))
{
TSQLIntoClause intoClause = new TSQLIntoClauseParser().Parse(Tokenizer);
Statement.Into = intoClause;
Statement.Tokens.AddRange(intoClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.FROM))
{
TSQLFromClause fromClause = new TSQLFromClauseParser().Parse(Tokenizer);
Statement.From = fromClause;
Statement.Tokens.AddRange(fromClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.WHERE))
{
TSQLWhereClause whereClause = new TSQLWhereClauseParser().Parse(Tokenizer);
Statement.Where = whereClause;
Statement.Tokens.AddRange(whereClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.GROUP))
{
TSQLGroupByClause groupByClause = new TSQLGroupByClauseParser().Parse(Tokenizer);
Statement.GroupBy = groupByClause;
Statement.Tokens.AddRange(groupByClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.HAVING))
{
TSQLHavingClause havingClause = new TSQLHavingClauseParser().Parse(Tokenizer);
Statement.Having = havingClause;
Statement.Tokens.AddRange(havingClause.Tokens);
}
// ORDER not allowed
// FOR not allowed
// OPTION not allowed
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
Tokenizer,
Statement);
return(Statement);
}
public TSQLSelectStatement Parse()
{
// (SELECT 1)
int level = 0;
while (Tokenizer.Current.IsCharacter(TSQLCharacters.OpenParentheses))
{
Statement.Tokens.Add(Tokenizer.Current);
level++;
Tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
Tokenizer,
Statement);
}
TSQLSelectClause selectClause = new TSQLSelectClauseParser().Parse(Tokenizer);
Statement.Select = selectClause;
Statement.Tokens.AddRange(selectClause.Tokens);
if (Tokenizer.Current.IsKeyword(TSQLKeywords.INTO))
{
TSQLIntoClause intoClause = new TSQLIntoClauseParser().Parse(Tokenizer);
Statement.Into = intoClause;
Statement.Tokens.AddRange(intoClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.FROM))
{
TSQLFromClause fromClause = new TSQLFromClauseParser().Parse(Tokenizer);
Statement.From = fromClause;
Statement.Tokens.AddRange(fromClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.WHERE))
{
TSQLWhereClause whereClause = new TSQLWhereClauseParser().Parse(Tokenizer);
Statement.Where = whereClause;
Statement.Tokens.AddRange(whereClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.GROUP))
{
TSQLGroupByClause groupByClause = new TSQLGroupByClauseParser().Parse(Tokenizer);
Statement.GroupBy = groupByClause;
Statement.Tokens.AddRange(groupByClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.HAVING))
{
TSQLHavingClause havingClause = new TSQLHavingClauseParser().Parse(Tokenizer);
Statement.Having = havingClause;
Statement.Tokens.AddRange(havingClause.Tokens);
}
while (level > 0 && Tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses))
{
Statement.Tokens.Add(Tokenizer.Current);
level--;
Tokenizer.MoveNext();
}
while (Tokenizer.Current?.AsKeyword != null &&
Tokenizer.Current.AsKeyword.Keyword.In(
TSQLKeywords.UNION,
TSQLKeywords.EXCEPT,
TSQLKeywords.INTERSECT))
{
TSQLSetOperatorClause set = new TSQLSetOperatorClauseParser().Parse(Tokenizer);
Statement.SetOperators.Add(set);
Statement.Tokens.AddRange(set.Tokens);
}
while (level > 0 && Tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses))
{
Statement.Tokens.Add(Tokenizer.Current);
level--;
Tokenizer.MoveNext();
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.ORDER))
{
TSQLOrderByClause orderByClause = new TSQLOrderByClauseParser().Parse(Tokenizer);
Statement.OrderBy = orderByClause;
Statement.Tokens.AddRange(orderByClause.Tokens);
}
// order for OPTION and FOR doesn't seem to matter
while (
Tokenizer.Current.IsKeyword(TSQLKeywords.FOR) ||
Tokenizer.Current.IsKeyword(TSQLKeywords.OPTION))
{
if (Tokenizer.Current.IsKeyword(TSQLKeywords.FOR))
{
TSQLForClause forClause = new TSQLForClauseParser().Parse(Tokenizer);
Statement.For = forClause;
Statement.Tokens.AddRange(forClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.OPTION))
{
TSQLOptionClause optionClause = new TSQLOptionClauseParser().Parse(Tokenizer);
Statement.Option = optionClause;
Statement.Tokens.AddRange(optionClause.Tokens);
}
}
return(Statement);
}
public TSQLSelectStatement Parse()
{
TSQLSelectClause selectClause = new TSQLSelectClauseParser().Parse(Tokenizer);
Statement.Select = selectClause;
Statement.Tokens.AddRange(selectClause.Tokens);
if (Tokenizer.Current.IsKeyword(TSQLKeywords.INTO))
{
TSQLIntoClause intoClause = new TSQLIntoClauseParser().Parse(Tokenizer);
Statement.Into = intoClause;
Statement.Tokens.AddRange(intoClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.FROM))
{
TSQLFromClause fromClause = new TSQLFromClauseParser().Parse(Tokenizer);
Statement.From = fromClause;
Statement.Tokens.AddRange(fromClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.WHERE))
{
TSQLWhereClause whereClause = new TSQLWhereClauseParser().Parse(Tokenizer);
Statement.Where = whereClause;
Statement.Tokens.AddRange(whereClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.GROUP))
{
TSQLGroupByClause groupByClause = new TSQLGroupByClauseParser().Parse(Tokenizer);
Statement.GroupBy = groupByClause;
Statement.Tokens.AddRange(groupByClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.HAVING))
{
TSQLHavingClause havingClause = new TSQLHavingClauseParser().Parse(Tokenizer);
Statement.Having = havingClause;
Statement.Tokens.AddRange(havingClause.Tokens);
}
if (Tokenizer.Current?.AsKeyword != null &&
Tokenizer.Current.AsKeyword.Keyword.In(
TSQLKeywords.UNION,
TSQLKeywords.EXCEPT,
TSQLKeywords.INTERSECT))
{
TSQLSetOperatorClause set = new TSQLSetOperatorClauseParser().Parse(Tokenizer);
Statement.SetOperator = set;
Statement.Tokens.AddRange(set.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.ORDER))
{
TSQLOrderByClause orderByClause = new TSQLOrderByClauseParser().Parse(Tokenizer);
Statement.OrderBy = orderByClause;
Statement.Tokens.AddRange(orderByClause.Tokens);
}
// order for OPTION and FOR doesn't seem to matter
while (
Tokenizer.Current.IsKeyword(TSQLKeywords.FOR) ||
Tokenizer.Current.IsKeyword(TSQLKeywords.OPTION))
{
if (Tokenizer.Current.IsKeyword(TSQLKeywords.FOR))
{
TSQLForClause forClause = new TSQLForClauseParser().Parse(Tokenizer);
Statement.For = forClause;
Statement.Tokens.AddRange(forClause.Tokens);
}
if (Tokenizer.Current.IsKeyword(TSQLKeywords.OPTION))
{
TSQLOptionClause optionClause = new TSQLOptionClauseParser().Parse(Tokenizer);
Statement.Option = optionClause;
Statement.Tokens.AddRange(optionClause.Tokens);
}
}
if (
Tokenizer.Current?.AsKeyword != null &&
Tokenizer.Current.AsKeyword.Keyword.IsStatementStart())
{
Tokenizer.Putback();
}
return(Statement);
}
public void SelectClause_ColumnAliasSyntaxes()
{
using (StringReader reader = new StringReader(
@"SELECT
ProductID,
p.[Name],
p.ProductNumber [Number],
p.MakeFlag AS IsMake,
IsFinishedGoods = p.FinishedGoodsFlag,
@Category AS ProductCategory,
p.*
FROM
Production.Product p;" ))
using (ITSQLTokenizer tokenizer = new TSQLTokenizer(reader))
{
Assert.IsTrue(tokenizer.MoveNext());
TSQLSelectClause select = new TSQLSelectClauseParser().Parse(tokenizer);
Assert.AreEqual(31, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.FROM, tokenizer.Current.AsKeyword.Keyword);
Assert.AreEqual(7, select.Columns.Count);
TSQLSelectColumn column = select.Columns[0];
Assert.IsNull(column.ColumnAlias);
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.IsNull(column.Expression.AsColumn.TableReference);
Assert.AreEqual("ProductID", column.Expression.AsColumn.Column.Name);
column = select.Columns[1];
Assert.IsNull(column.ColumnAlias);
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.AreEqual("p", column.Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("Name", column.Expression.AsColumn.Column.Name);
column = select.Columns[2];
Assert.AreEqual("Number", column.ColumnAlias.Name);
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.AreEqual("p", column.Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("ProductNumber", column.Expression.AsColumn.Column.Name);
column = select.Columns[3];
Assert.AreEqual("IsMake", column.ColumnAlias.Name);
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.AreEqual("p", column.Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("MakeFlag", column.Expression.AsColumn.Column.Name);
column = select.Columns[4];
Assert.AreEqual("IsFinishedGoods", column.ColumnAlias.Name);
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.AreEqual("p", column.Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("FinishedGoodsFlag", column.Expression.AsColumn.Column.Name);
column = select.Columns[5];
Assert.AreEqual("ProductCategory", column.ColumnAlias.Name);
Assert.AreEqual(TSQLExpressionType.Variable, column.Expression.Type);
Assert.AreEqual("@Category", column.Expression.AsVariable.Variable.Text);
column = select.Columns[6];
Assert.IsNull(column.ColumnAlias);
Assert.AreEqual(TSQLExpressionType.Multicolumn, column.Expression.Type);
Assert.AreEqual("p", column.Expression.AsMulticolumn.TableReference.Single().AsIdentifier.Name);
}
}