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_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 (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 TSQLExpression ParseNext( ITSQLTokenizer tokenizer) { if (tokenizer.Current == null) { return(null); } // look at the current/first token to determine what to do if (tokenizer.Current.Text == "*") { TSQLMulticolumnExpression simpleMulti = new TSQLMulticolumnExpression(); simpleMulti.Tokens.Add(tokenizer.Current); TSQLTokenParserHelper.ReadThroughAnyCommentsOrWhitespace( tokenizer, simpleMulti.Tokens); return(simpleMulti); // still need to seperately check for p.* below } // this checks for unary operators, e.g. +, -, and ~ else if (tokenizer.Current.Type.In( TSQLTokenType.Operator)) { return(null); } else if (tokenizer.Current.IsCharacter( TSQLCharacters.OpenParentheses)) { List <TSQLToken> tokens = new List <TSQLToken>(); tokens.Add(tokenizer.Current); // read through any whitespace so we can check specifically for a SELECT TSQLTokenParserHelper.ReadThroughAnyCommentsOrWhitespace( tokenizer, tokens); if (tokenizer.Current.IsKeyword(TSQLKeywords.SELECT)) { #region parse subquery TSQLSubqueryExpression subquery = new TSQLSubqueryExpression(); subquery.Tokens.AddRange(tokens); TSQLSelectStatement select = new TSQLSelectStatementParser(tokenizer).Parse(); subquery.Select = select; subquery.Tokens.AddRange(select.Tokens); if (tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses)) { subquery.Tokens.Add(tokenizer.Current); tokenizer.MoveNext(); } return(subquery); #endregion } else { #region parse expression contained/grouped inside parenthesis TSQLGroupedExpression group = new TSQLGroupedExpression(); group.Tokens.AddRange(tokens); group.InnerExpression = new TSQLValueExpressionParser().Parse( tokenizer); group.Tokens.AddRange(group.InnerExpression.Tokens); if (tokenizer.Current.IsCharacter( TSQLCharacters.CloseParentheses)) { group.Tokens.Add(tokenizer.Current); tokenizer.MoveNext(); } return(group); #endregion } } else if (tokenizer.Current.Type.In( TSQLTokenType.Variable, TSQLTokenType.SystemVariable)) { TSQLVariableExpression variable = new TSQLVariableExpression(); variable.Tokens.Add(tokenizer.Current); variable.Variable = tokenizer.Current.AsVariable; TSQLTokenParserHelper.ReadThroughAnyCommentsOrWhitespace( tokenizer, variable.Tokens); return(variable); } else if (tokenizer.Current.Type.In( TSQLTokenType.BinaryLiteral, TSQLTokenType.MoneyLiteral, TSQLTokenType.NumericLiteral, TSQLTokenType.StringLiteral, TSQLTokenType.IncompleteString)) { TSQLConstantExpression constant = new TSQLConstantExpression(); constant.Literal = tokenizer.Current.AsLiteral; constant.Tokens.Add(tokenizer.Current); TSQLTokenParserHelper.ReadThroughAnyCommentsOrWhitespace( tokenizer, constant.Tokens); return(constant); } else if (tokenizer.Current.IsKeyword(TSQLKeywords.CASE)) { return(new TSQLCaseExpressionParser().Parse(tokenizer)); } else if (tokenizer.Current.Type.In( TSQLTokenType.SystemColumnIdentifier, TSQLTokenType.IncompleteIdentifier)) { TSQLColumnExpression column = new TSQLColumnExpression(); column.Column = tokenizer.Current.AsSystemColumnIdentifier; column.Tokens.Add(tokenizer.Current); TSQLTokenParserHelper.ReadThroughAnyCommentsOrWhitespace( tokenizer, column.Tokens); return(column); } else if (tokenizer.Current.Type.In( TSQLTokenType.Identifier, TSQLTokenType.SystemIdentifier)) { // column, with or without alias, or with full explicit table name with up to 5 parts // or function, up to 4 part naming // find last token up to and including possible first paren // if *, then multi column // if paren, then function // else column // alias would be any tokens prior to last period, removing whitespace List <TSQLToken> tokens = new List <TSQLToken>(); tokens.Add(tokenizer.Current); while (tokenizer.MoveNext()) { if (tokenizer.Current.IsCharacter(TSQLCharacters.OpenParentheses)) { #region parse function TSQLFunctionExpression function = new TSQLFunctionExpression(); function.Tokens.AddRange(tokens); function.Tokens.Add(tokenizer.Current); var identityTokens = tokens .Where(t => !t.IsComment() && !t.IsWhitespace()) .ToList(); function.Function = identityTokens[identityTokens.Count - 1] .AsIdentifier; if (identityTokens.Count > 1) { function.QualifiedPath = identityTokens .GetRange( 0, identityTokens.Count - 2); } tokenizer.MoveNext(); TSQLArgumentList arguments = null; // CAST function has it's own very unique argument syntax if (function.Function.IsIdentifier(TSQLIdentifiers.CAST)) { arguments = new TSQLValueAsTypeExpressionParser().Parse( tokenizer); } else { arguments = new TSQLArgumentListParser().Parse( tokenizer); } function.Tokens.AddRange(arguments.Tokens); function.Arguments = arguments; if (tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses)) { function.Tokens.Add(tokenizer.Current); } tokenizer.MoveNext(); TSQLTokenParserHelper.ReadCommentsAndWhitespace( tokenizer, function); // look for windowed aggregate if (tokenizer.Current.IsKeyword(TSQLKeywords.OVER)) { function.Tokens.Add(tokenizer.Current); tokenizer.MoveNext(); TSQLTokenParserHelper.ReadCommentsAndWhitespace( tokenizer, function); if (tokenizer.Current.IsCharacter(TSQLCharacters.OpenParentheses)) { function.Tokens.Add(tokenizer.Current); // recursively look for final close parens TSQLTokenParserHelper.ReadUntilStop( tokenizer, function, new List <TSQLFutureKeywords> { }, new List <TSQLKeywords> { }, lookForStatementStarts: false); if (tokenizer.Current != null && tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses)) { function.Tokens.Add(tokenizer.Current); tokenizer.MoveNext(); } } } return(function); #endregion } else if (tokenizer.Current.Text == "*") { #region parse multi column reference // e.g. p.* TSQLMulticolumnExpression multi = new TSQLMulticolumnExpression(); multi.Tokens.AddRange(tokens); multi.Tokens.Add(tokenizer.Current); List <TSQLToken> columnReference = tokens .Where(t => !t.IsComment() && !t.IsWhitespace()) .ToList(); if (columnReference.Count > 0) { // p.* will have the single token p in the final list // AdventureWorks..ErrorLog.* will have 4 tokens in the final list // e.g. {AdventureWorks, ., ., ErrorLog} multi.TableReference = columnReference .GetRange(0, columnReference .FindLastIndex(t => t.IsCharacter(TSQLCharacters.Period))) .ToList(); } TSQLTokenParserHelper.ReadThroughAnyCommentsOrWhitespace( tokenizer, multi.Tokens); return(multi); #endregion } else if ( tokenizer.Current.IsCharacter(TSQLCharacters.Comma) || tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses) || tokenizer.Current.Type.In( TSQLTokenType.Keyword, TSQLTokenType.Operator) || // this will be a nasty check, but I don't want to copy the internal logic elsewhere // two identifiers in a row means that the second one is an alias ( tokenizer.Current.Type.In( TSQLTokenType.Identifier, TSQLTokenType.IncompleteIdentifier) && tokens .Where(t => !t.IsComment() && !t.IsWhitespace()) .LastOrDefault() ?.Type.In( TSQLTokenType.Identifier, TSQLTokenType.BinaryLiteral, TSQLTokenType.MoneyLiteral, TSQLTokenType.NumericLiteral, TSQLTokenType.StringLiteral, TSQLTokenType.SystemColumnIdentifier, TSQLTokenType.SystemIdentifier, TSQLTokenType.SystemVariable, TSQLTokenType.Variable ) == true // Operator '&&' cannot be applied to operands of type 'bool' and 'bool?' )) { TSQLColumnExpression column = new TSQLColumnExpression(); column.Tokens.AddRange(tokens); List <TSQLToken> columnReference = tokens .Where(t => !t.IsComment() && !t.IsWhitespace()) .ToList(); if (columnReference.Count > 1) { // p.ProductID will have the single token p in the final list // AdventureWorks..ErrorLog.ErrorLogID will have 4 tokens in the final list // e.g. {AdventureWorks, ., ., ErrorLog} column.TableReference = columnReference .GetRange(0, columnReference .FindLastIndex(t => t.IsCharacter(TSQLCharacters.Period))) .ToList(); } column.Column = columnReference .Last() .AsIdentifier; return(column); } else { tokens.Add(tokenizer.Current); } } // this is the fall through if none of the "returns" hit above // will also hit if we parse a simple single column expression, e.g. "SELECT blah" TSQLColumnExpression simpleColumn = new TSQLColumnExpression(); simpleColumn.Tokens.AddRange(tokens); List <TSQLToken> simpleColumnReference = tokens .Where(t => !t.IsComment() && !t.IsWhitespace() && !t.IsCharacter(TSQLCharacters.Semicolon)) .ToList(); if (simpleColumnReference.Count > 1) { // p.ProductID will have the single token p in the final list // AdventureWorks..ErrorLog.ErrorLogID will have 4 tokens in the final list // e.g. {AdventureWorks, ., ., ErrorLog} simpleColumn.TableReference = simpleColumnReference .GetRange(0, simpleColumnReference .FindLastIndex(t => t.IsCharacter(TSQLCharacters.Period))) .ToList(); } simpleColumn.Column = simpleColumnReference .Last() .AsIdentifier; return(simpleColumn); } else { return(null); } }