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 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 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 SelectStatement_CountAliasWithWhitespace()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"SELECT COUNT ( * ) as count FROM sqlite_master",
includeWhitespace: true);
TSQLSelectColumn count = statements
.Single()
.AsSelect
.Select
.Columns
.Single();
Assert.AreEqual("COUNT", count.Expression.AsFunction.Function.Name);
Assert.AreEqual("count", count.ColumnAlias.Name);
}
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 SelectStatement_CaseInSelect()
{
string query = "SELECT Value, CASE WHEN Value = 1 THEN 'One' ELSE 'Other' END AS Cased FROM SomeTable";
var statements = TSQLStatementReader.ParseStatements(query, includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(15, select.Select.Tokens.Count);
Assert.AreEqual(2, select.Select.Columns.Count);
Assert.AreEqual("Value", select.Select.Columns[0].Expression.AsColumn.Column.Name);
TSQLSelectColumn column = select.Select.Columns[1];
Assert.AreEqual("Cased", column.ColumnAlias.AsIdentifier.Name);
Assert.AreEqual(TSQLExpressionType.Case, column.Expression.Type);
Assert.AreEqual(2, select.From.Tokens.Count);
Assert.AreEqual("FROM SomeTable",
query.Substring(
select.From.BeginPosition,
select.From.Length));
Assert.AreEqual(2, select.Select.Columns.Count);
}
public TSQLSelectColumn Parse(ITSQLTokenizer tokenizer)
{
TSQLSelectColumn column = new TSQLSelectColumn();
TSQLExpression columnExpression = new TSQLSelectExpressionParser().Parse(tokenizer);
column.Expression = columnExpression;
column.Tokens.AddRange(columnExpression.Tokens);
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
column);
// check for operator =, when expression type is column, and return new column expression with alias
// e.g. IsFinishedGoods = p.FinishedGoodsFlag
if (
tokenizer.Current != null &&
tokenizer.Current.Type == TSQLTokenType.Operator &&
tokenizer.Current.Text == "=" &&
columnExpression.Type == TSQLExpressionType.Column
)
{
column.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLExpression actualColumn = new TSQLValueExpressionParser().Parse(tokenizer);
column.Expression = actualColumn;
column.ColumnAlias = columnExpression.AsColumn.Column;
column.Tokens.AddRange(actualColumn.Tokens);
}
else
{
if (tokenizer.Current.IsKeyword(TSQLKeywords.AS))
{
column.Tokens.Add(tokenizer.Current);
tokenizer.MoveNext();
TSQLTokenParserHelper.ReadCommentsAndWhitespace(
tokenizer,
column);
}
if (tokenizer.Current != null &&
tokenizer.Current.Type.In(
TSQLTokenType.Identifier,
TSQLTokenType.SystemIdentifier,
TSQLTokenType.IncompleteIdentifier))
{
column.Tokens.Add(tokenizer.Current);
if (tokenizer.Current.Type.In(
TSQLTokenType.Identifier,
TSQLTokenType.SystemIdentifier))
{
column.ColumnAlias = tokenizer.Current.AsIdentifier;
}
tokenizer.MoveNext();
}
}
return(column);
}
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);
}
}
