public void ShouldResolveUpdateStatement()
{
//Arrange
string rawTsql = "update someFactTable " +
"set somecolumn = nil.col_01, someOtherColumn = [dbo].gl.col_02 " +
"from DWH_DB.[dbo].[someFactTable]";
IDataManipulationResolver resolver = new UpdateStatementResolver();
int fileIndex = 0;
CompilerContext context = new CompilerContext("xUnit", "stdserver", "stdDatabase", true);
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(rawTsql).ToArray();
//Act
DataManipulation statement = resolver.Resolve(tokens, ref fileIndex, context);
//Assert
Assert.Equal(tokens.Length, fileIndex);
Assert.Equal(2, statement.Expressions.Count);
Assert.Equal(ExpressionType.COLUMN, statement.Expressions[0].Type);
Assert.Equal("DWH_DB.dbo.someFactTable.somecolumn", statement.Expressions[0].Name);
Assert.Equal("stdDatabase.unrelated.nil.col_01", statement.Expressions[0].ChildExpressions[0].Name);
Assert.Equal(ExpressionType.COLUMN, statement.Expressions[1].Type);
Assert.Equal("DWH_DB.dbo.someFactTable.someOtherColumn", statement.Expressions[1].Name);
Assert.Equal("stdDatabase.dbo.gl.col_02", statement.Expressions[1].ChildExpressions[0].Name);
}
public void TestParseLines()
{
string file_string = @"zzz!10,NAV15BEO0Zll
before zzz!11,87NSZSSP4Jll
zzz!12,MZOTXUFT9Tll after
zzz!13,6DFOKC74ZGll
zzz!14,3KZ94GFGLZll
zzz!15,VQTMAXWCDKll";
string[] expected =
{
"10,NAV15BEO0Z",
"11,87NSZSSP4J",
"12,MZOTXUFT9T",
"13,6DFOKC74ZG",
"14,3KZ94GFGLZ",
"15,VQTMAXWCDK"
};
string query = "LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE imptest FIELDS TERMINATED BY ',' LINES STARTING BY 'zzz!' TERMINATED BY 'll'";
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens(query);
LoadDataParser parser = new LoadDataParser(tokens);
Assert.AreEqual("zzz!", parser.lines_starting_by);
Assert.AreEqual("ll", parser.lines_terminated_by);
string[] actual = ClientSession.ParseLines(file_string, parser);
foreach (var a in actual)
{
Console.WriteLine(a);
}
Assert.IsTrue(actual.SequenceEqual(expected));
}
public void ShouldResolveUpdateStatementWithAliasTarget()
{
//Arrange
string rawTsql = "update GL " +
"set somevalue = nil.col_01, someOtherValue = gl.col_02 " +
"from [SOME_table] nil " +
"inner join [dbo].[TABLE_02] GL";
IDataManipulationResolver resolver = new UpdateStatementResolver();
int fileIndex = 0;
CompilerContext context = new CompilerContext("xUnit", "stdserver", "stdDatabase", true);
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(rawTsql).ToArray();
//Act
DataManipulation statement = resolver.Resolve(tokens, ref fileIndex, context);
//Assert
Assert.Equal(tokens.Length, fileIndex);
Assert.Equal(ExpressionType.COLUMN, statement.Expressions[0].Type);
Assert.Equal("stdDatabase.dbo.TABLE_02.somevalue", statement.Expressions[0].Name);
Assert.Equal("stdDatabase.unrelated.SOME_table.col_01", statement.Expressions[0].ChildExpressions[0].Name);
Assert.Equal(ExpressionType.COLUMN, statement.Expressions[1].Type);
Assert.Equal("stdDatabase.dbo.TABLE_02.someOtherValue", statement.Expressions[1].Name);
Assert.Equal("stdDatabase.dbo.TABLE_02.col_02", statement.Expressions[1].ChildExpressions[0].Name);
}
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 TSQLIntoClause Parse(TSQLTokenizer tokenizer)
{
TSQLIntoClause into = new TSQLIntoClause();
if (
tokenizer.Current == null ||
tokenizer.Current.Type != TSQLTokenType.Keyword ||
tokenizer.Current.AsKeyword.Keyword != TSQLKeywords.INTO)
{
throw new ApplicationException("INTO expected.");
}
into.Tokens.Add(tokenizer.Current);
while (
tokenizer.Read() &&
(
tokenizer.Current.Type == TSQLTokenType.Identifier ||
(
tokenizer.Current.Type == TSQLTokenType.Character &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.Period
) ||
tokenizer.Current.Type == TSQLTokenType.Whitespace ||
tokenizer.Current.Type == TSQLTokenType.SingleLineComment ||
tokenizer.Current.Type == TSQLTokenType.MultilineComment
))
{
into.Tokens.Add(tokenizer.Current);
}
return(into);
}
public void ShouldResolveRecursiveCaseStatement()
{
//Arrange
string rawTsql = " CASE " +
" WHEN someValue > anotherValue THEN " +
" CASE " +
" WHEN NullIf(val_01,0) IS NULL THEN NULL " +
" WHEN NullIf(val_02,0) IS NULL THEN Null " +
" WHEN(100 / val_02 * val_01) <= 100 THEN 'Green' " +
" WHEN 100 + 25 <= 125 THEN 'Yellow'" +
" ELSE 'Red' " +
" END " +
" ELSE " +
" CASE " +
" WHEN NULLIF(val_03, 0) IS NULL THEN NULL " +
" WHEN NullIf(val_02,0) IS NULL THEN NULL " +
" WHEN(100 / val_02 * val_03) <= 100 THEN 'Green' " +
" WHEN(100 / val_02 * val_03) <= 125 THEN 'Yellow'" +
" ELSE 'Red' " +
" END " +
" END";
IExpressionResolver resolver = new CaseStatementResolver();
int fileIndex = 0;
CompilerContext context = new CompilerContext("xUnit", "stdserver", "stdDatabase", true);
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(rawTsql).ToArray();
//Act
Expression expression = resolver.Resolve(tokens, ref fileIndex, context);
//Assert
Assert.Equal(tokens.Length, fileIndex);
}
public TSQLIntoClause Parse(TSQLTokenizer tokenizer)
{
TSQLIntoClause into = new TSQLIntoClause();
if (
tokenizer.Current == null ||
tokenizer.Current.Type != TSQLTokenType.Keyword ||
tokenizer.Current.AsKeyword.Keyword != TSQLKeywords.INTO)
{
throw new ApplicationException("INTO expected.");
}
into.Tokens.Add(tokenizer.Current);
while (
tokenizer.Read() &&
(
tokenizer.Current.Type == TSQLTokenType.Identifier ||
(
tokenizer.Current.Type == TSQLTokenType.Character &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.Period
) ||
tokenizer.Current.Type == TSQLTokenType.Whitespace ||
tokenizer.Current.Type == TSQLTokenType.SingleLineComment ||
tokenizer.Current.Type == TSQLTokenType.MultilineComment
))
{
into.Tokens.Add(tokenizer.Current);
}
return into;
}
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 TSQLStatement Parse(TSQLTokenizer tokenizer)
{
TSQLWithStatement with = new TSQLWithStatementParser().Parse(tokenizer);
TSQLStatement statement = new TSQLStatementParserFactory().Create(tokenizer.Current).Parse(tokenizer);
statement.Tokens.InsertRange(0, with.Tokens);
return statement;
}
public TSQLStatement Parse(TSQLTokenizer tokenizer)
{
TSQLWithStatement with = new TSQLWithStatementParser().Parse(tokenizer);
TSQLStatement statement = new TSQLStatementParserFactory().Create(tokenizer.Current).Parse(tokenizer);
statement.Tokens.InsertRange(0, with.Tokens);
return(statement);
}
public static FindCreateStatementResult FindCreate(string body)
{
var tokenizer = new TSQLTokenizer(body);
tokenizer.MoveNext();
while (tokenizer.Current != null && tokenizer.Current.AsKeyword?.Keyword != TSQLKeywords.CREATE)
{
tokenizer.MoveNext();
}
if (tokenizer.Current == null)
{
// Oops, we reached the end of the file and did not find the CREATE!
return(null);
}
var createToken = tokenizer.Current.AsKeyword;
tokenizer.MoveNext();
var typeKeyword = tokenizer.Current.AsKeyword;
tokenizer.MoveNext();
// the object owner is optional
var token0 = tokenizer.Current;
tokenizer.MoveNext();
var token1 = tokenizer.Current;
tokenizer.MoveNext();
var token2 = tokenizer.Current;
tokenizer.MoveNext();
TSQLIdentifier entityNameToken;
if (token1.AsCharacter?.Text == ".")
{
entityNameToken = token2.AsIdentifier;
}
else
{
entityNameToken = token0.AsIdentifier;
}
return(new FindCreateStatementResult
{
CreateBeginPosition = createToken.BeginPosition,
TypeEndPosition = typeKeyword.EndPosition,
NameEndPosition = entityNameToken.EndPosition
});
}
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 Parse_uspSearchCandidateResumes_NoWhitespace()
{
using (StringReader reader = new StringReader(Resources.AdventureWorks2014_dbo_uspSearchCandidateResumes))
using (TSQLTokenizer lexer = new TSQLTokenizer(reader))
{
TokenComparisons.CompareStreamStartToList(
GetuspSearchCandidateResumesTokens()
.Where(t => !(t is TSQLWhitespace)).ToList(),
lexer);
}
}
public void Parse_GO()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("GO");
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLKeyword(0, "GO")
},
tokens);
}
public void KeywordToken_SimpleKeywordLowercase()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("go ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLKeyword(0, "go"),
new TSQLWhitespace(2, " ")
},
tokens);
}
public void MoneyLiteral_ForeignNegative()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("£-.1 ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLMoneyLiteral(0, "£-.1"),
new TSQLWhitespace(4, " ")
},
tokens);
}
public void StringLiteralToken_DoubleQuote()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("\"name\" ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLStringLiteral(0, "\"name\""),
new TSQLWhitespace(6, " ")
},
tokens);
}
public void Operator_NotEqualExclamation()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("!= ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLOperator(0, "!="),
new TSQLWhitespace(2, " ")
},
tokens);
}
public void IdentifierToken_EscapedBracketedIdentifier2()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("[a]]a] ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIdentifier(0, "[a]]a]"),
new TSQLWhitespace(6, " ")
},
tokens);
}
public void IncompleteToken_Comment()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("/* something", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIncompleteCommentToken(0, "/* something")
},
tokens);
Assert.IsFalse(tokens[0].IsComplete);
}
public void IdentifierToken_QuotedUnicodeIdentifier()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("N\"name\" ", useQuotedIdentifiers: true, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIdentifier(0, "N\"name\""),
new TSQLWhitespace(7, " ")
},
tokens);
}
public void IncompleteToken_StringLiteral()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("'", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIncompleteStringToken(0, "'")
},
tokens);
Assert.IsFalse(tokens[0].IsComplete);
}
public void IncompleteToken_Identifier()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("[dbo", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIncompleteIdentifierToken(0, "[dbo")
},
tokens);
Assert.IsFalse(tokens[0].IsComplete);
}
public void BinaryToken_SimpleBinaryLiteral()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("0x69048AEFDD010E ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLBinaryLiteral(0, "0x69048AEFDD010E"),
new TSQLWhitespace(16, " ")
},
tokens);
}
public void BinaryToken_BinaryLiteralEndWithLetter()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("0x69048AEFDD010EJ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLBinaryLiteral(0, "0x69048AEFDD010E"),
new TSQLIdentifier(16, "J")
},
tokens);
}
public void StringLiteralToken_SingleQuoteUnicode()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("N'name' ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLStringLiteral(0, "N'name'"),
new TSQLWhitespace(7, " ")
},
tokens);
}
public void CharacterToken_Period()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens(". ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLCharacter(0, "."),
new TSQLWhitespace(1, " ")
},
tokens);
}
public void IdentifierToken_SimpleIdentifier()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("a ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIdentifier(0, "a"),
new TSQLWhitespace(1, " ")
},
tokens);
}
public void Operator_LessThan()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("< ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLOperator(0, "<"),
new TSQLWhitespace(1, " ")
},
tokens);
}
public void BinaryToken_EmptyBinaryLiteralCaseInsensitive()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("0X ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLBinaryLiteral(0, "0X"),
new TSQLWhitespace(2, " ")
},
tokens);
}
public TSQLUnknownStatement Parse(TSQLTokenizer tokenizer)
{
TSQLUnknownStatement statement = new TSQLUnknownStatement();
while (
tokenizer.Read() &&
!(
tokenizer.Current is TSQLCharacter &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.Semicolon
))
{
statement.Tokens.Add(tokenizer.Current);
}
if (
tokenizer.Current != null &&
tokenizer.Current is TSQLCharacter &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.Semicolon)
{
statement.Tokens.Add(tokenizer.Current);
}
return statement;
}
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 TSQLFromClause Parse(TSQLTokenizer tokenizer)
{
TSQLFromClause from = new TSQLFromClause();
if (
tokenizer.Current == null ||
tokenizer.Current.Type != TSQLTokenType.Keyword ||
tokenizer.Current.AsKeyword.Keyword != TSQLKeywords.FROM)
{
throw new ApplicationException("FROM expected.");
}
from.Tokens.Add(tokenizer.Current);
// derived tables
// TVF
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.JOIN,
TSQLKeywords.ON,
TSQLKeywords.INNER,
TSQLKeywords.LEFT,
TSQLKeywords.RIGHT,
TSQLKeywords.OUTER,
TSQLKeywords.CROSS,
TSQLKeywords.FULL,
TSQLKeywords.AS,
TSQLKeywords.PIVOT,
TSQLKeywords.UNPIVOT,
TSQLKeywords.WITH,
TSQLKeywords.MERGE,
TSQLKeywords.TABLESAMPLE,
TSQLKeywords.FOR,
TSQLKeywords.FROM, // FOR SYSTEM_TIME FROM
TSQLKeywords.BETWEEN,
TSQLKeywords.AND,
TSQLKeywords.IN,
TSQLKeywords.REPEATABLE,
TSQLKeywords.ALL
)
)
))
{
from.Tokens.Add(tokenizer.Current);
if (tokenizer.Current.Type == TSQLTokenType.Character)
{
TSQLCharacters character = tokenizer.Current.AsCharacter.Character;
if (character == TSQLCharacters.OpenParentheses)
{
// should we recurse for derived tables?
nestedLevel++;
if (tokenizer.Read())
{
if (
tokenizer.Current.Type == TSQLTokenType.Keyword &&
tokenizer.Current.AsKeyword.Keyword == TSQLKeywords.SELECT)
{
TSQLSelectStatement selectStatement = new TSQLSelectStatementParser().Parse(tokenizer);
from.Tokens.AddRange(selectStatement.Tokens);
if (
tokenizer.Current != null &&
tokenizer.Current.Type == TSQLTokenType.Character &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.CloseParentheses)
{
nestedLevel--;
from.Tokens.Add(tokenizer.Current);
}
}
else
{
from.Tokens.Add(tokenizer.Current);
}
}
}
else if (character == TSQLCharacters.CloseParentheses)
{
nestedLevel--;
}
}
}
return from;
}
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;
}
TSQLStatement ITSQLStatementParser.Parse(TSQLTokenizer tokenizer)
{
return Parse(tokenizer);
}
TSQLMergeStatement Parse(TSQLTokenizer tokenizer)
{
throw new NotImplementedException();
}
public TSQLHavingClause Parse(TSQLTokenizer tokenizer)
{
TSQLHavingClause having = new TSQLHavingClause();
if (
tokenizer.Current == null ||
tokenizer.Current.Type != TSQLTokenType.Keyword ||
tokenizer.Current.AsKeyword.Keyword != TSQLKeywords.HAVING)
{
throw new ApplicationException("HAVING expected.");
}
having.Tokens.Add(tokenizer.Current);
// subqueries
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.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.LIKE
)
)
))
{
having.Tokens.Add(tokenizer.Current);
if (tokenizer.Current.Type == TSQLTokenType.Character)
{
TSQLCharacters character = tokenizer.Current.AsCharacter.Character;
if (character == TSQLCharacters.OpenParentheses)
{
// should we recurse for subqueries?
nestedLevel++;
if (tokenizer.Read())
{
if (
tokenizer.Current.Type == TSQLTokenType.Keyword &&
tokenizer.Current.AsKeyword.Keyword == TSQLKeywords.SELECT)
{
TSQLSelectStatement selectStatement = new TSQLSelectStatementParser().Parse(tokenizer);
having.Tokens.AddRange(selectStatement.Tokens);
if (
tokenizer.Current != null &&
tokenizer.Current.Type == TSQLTokenType.Character &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.CloseParentheses)
{
nestedLevel--;
having.Tokens.Add(tokenizer.Current);
}
}
else
{
having.Tokens.Add(tokenizer.Current);
}
}
}
else if (character == TSQLCharacters.CloseParentheses)
{
nestedLevel--;
}
}
}
return having;
}
TSQLClause ITSQLClauseParser.Parse(TSQLTokenizer tokenizer)
{
return Parse(tokenizer);
}
public TSQLDeleteStatement Parse(TSQLTokenizer tokenizer)
{
throw new NotImplementedException();
}
public TSQLOrderByClause Parse(TSQLTokenizer tokenizer)
{
TSQLOrderByClause orderBy = new TSQLOrderByClause();
TSQLKeyword keyword = tokenizer.Current.AsKeyword;
if (keyword == null ||
keyword.Keyword != TSQLKeywords.ORDER)
{
throw new ApplicationException("ORDER expected.");
}
orderBy.Tokens.Add(keyword);
// subqueries
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.BY,
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.LIKE,
TSQLKeywords.ASC,
TSQLKeywords.DESC,
TSQLKeywords.FETCH,
TSQLKeywords.COLLATE
)
)
))
{
orderBy.Tokens.Add(tokenizer.Current);
if (tokenizer.Current.Type == TSQLTokenType.Character)
{
TSQLCharacters character = tokenizer.Current.AsCharacter.Character;
if (character == TSQLCharacters.OpenParentheses)
{
// should we recurse for subqueries?
nestedLevel++;
if (tokenizer.Read())
{
if (
tokenizer.Current.Type == TSQLTokenType.Keyword &&
tokenizer.Current.AsKeyword.Keyword == TSQLKeywords.SELECT)
{
TSQLSelectStatement selectStatement = new TSQLSelectStatementParser().Parse(tokenizer);
orderBy.Tokens.AddRange(selectStatement.Tokens);
if (
tokenizer.Current != null &&
tokenizer.Current.Type == TSQLTokenType.Character &&
tokenizer.Current.AsCharacter.Character == TSQLCharacters.CloseParentheses)
{
nestedLevel--;
orderBy.Tokens.Add(tokenizer.Current);
}
}
else
{
orderBy.Tokens.Add(tokenizer.Current);
}
}
}
else if (character == TSQLCharacters.CloseParentheses)
{
nestedLevel--;
}
}
}
return orderBy;
}
