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 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); } }