public TSQLMergeClause Parse(ITSQLTokenizer tokenizer) { TSQLMergeClause merge = new TSQLMergeClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.MERGE)) { throw new InvalidOperationException("MERGE expected."); } merge.Tokens.Add(tokenizer.Current); // can contain TOP() TSQLSubqueryHelper.ReadUntilStop( tokenizer, merge, new List <TSQLFutureKeywords>() { TSQLFutureKeywords.OUTPUT, TSQLFutureKeywords.USING }, new List <TSQLKeywords>() { TSQLKeywords.INTO, TSQLKeywords.AS, TSQLKeywords.ON, TSQLKeywords.WHEN }, lookForStatementStarts: true); return(merge); }
public TSQLInsertClause Parse(ITSQLTokenizer tokenizer) { TSQLInsertClause insert = new TSQLInsertClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.INSERT)) { throw new InvalidOperationException("INSERT expected."); } insert.Tokens.Add(tokenizer.Current); TSQLSubqueryHelper.ReadUntilStop( tokenizer, insert, new List <TSQLFutureKeywords>() { TSQLFutureKeywords.OUTPUT }, new List <TSQLKeywords>() { TSQLKeywords.SELECT, TSQLKeywords.EXECUTE, TSQLKeywords.VALUES, TSQLKeywords.DEFAULT }, lookForStatementStarts: false); return(insert); }
public TSQLHavingClause Parse(ITSQLTokenizer tokenizer) { TSQLHavingClause having = new TSQLHavingClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.HAVING)) { throw new InvalidOperationException("HAVING expected."); } having.Tokens.Add(tokenizer.Current); // subqueries TSQLSubqueryHelper.ReadUntilStop( tokenizer, having, new List <TSQLFutureKeywords>() { }, new List <TSQLKeywords>() { TSQLKeywords.ORDER, TSQLKeywords.UNION, TSQLKeywords.EXCEPT, TSQLKeywords.INTERSECT, TSQLKeywords.FOR, TSQLKeywords.OPTION }, lookForStatementStarts: true); return(having); }
public TSQLOnClause Parse(ITSQLTokenizer tokenizer) { TSQLOnClause on = new TSQLOnClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.ON)) { throw new InvalidOperationException("ON expected."); } on.Tokens.Add(tokenizer.Current); // TODO: tighten logic to handle tables named OUTPUT, but still handle ON usage in MERGE TSQLSubqueryHelper.ReadUntilStop( tokenizer, on, new List <TSQLFutureKeywords>() { TSQLFutureKeywords.OUTPUT, TSQLFutureKeywords.USING }, new List <TSQLKeywords>() { TSQLKeywords.INNER, TSQLKeywords.OUTER, TSQLKeywords.JOIN, TSQLKeywords.WHEN }, lookForStatementStarts: true); return(on); }
public TSQLWhenClause Parse(ITSQLTokenizer tokenizer) { TSQLWhenClause when = new TSQLWhenClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.WHEN)) { throw new InvalidOperationException("WHEN expected."); } when.Tokens.Add(tokenizer.Current); // we don't have to worry about accidentally running into the next statement. // https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql // The MERGE statement requires a semicolon (;) as a statement terminator. // Error 10713 is raised when a MERGE statement is run without the terminator. TSQLSubqueryHelper.ReadUntilStop( tokenizer, when, new List <TSQLFutureKeywords>() { TSQLFutureKeywords.OUTPUT }, new List <TSQLKeywords>() { TSQLKeywords.WHEN, TSQLKeywords.OPTION }, lookForStatementStarts: false); return(when); }
public TSQLGroupByClause Parse(ITSQLTokenizer tokenizer) { TSQLGroupByClause groupBy = new TSQLGroupByClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.GROUP)) { throw new InvalidOperationException("GROUP expected."); } groupBy.Tokens.Add(tokenizer.Current); // subqueries TSQLSubqueryHelper.ReadUntilStop( tokenizer, groupBy, new List <TSQLFutureKeywords>() { }, new List <TSQLKeywords>() { TSQLKeywords.HAVING, TSQLKeywords.UNION, TSQLKeywords.EXCEPT, TSQLKeywords.INTERSECT, TSQLKeywords.ORDER, TSQLKeywords.FOR, TSQLKeywords.OPTION }, lookForStatementStarts: true); return(groupBy); }
public TSQLDeleteClause Parse(ITSQLTokenizer tokenizer) { TSQLDeleteClause delete = new TSQLDeleteClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.DELETE)) { throw new InvalidOperationException("DELETE expected."); } delete.Tokens.Add(tokenizer.Current); TSQLSubqueryHelper.ReadUntilStop( tokenizer, delete, new List <TSQLFutureKeywords>() { TSQLFutureKeywords.OUTPUT }, new List <TSQLKeywords>() { TSQLKeywords.FROM, TSQLKeywords.WHERE, TSQLKeywords.OPTION }, lookForStatementStarts: true); return(delete); }
public TSQLOrderByClause Parse(ITSQLTokenizer tokenizer) { TSQLOrderByClause orderBy = new TSQLOrderByClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.ORDER)) { throw new InvalidOperationException("ORDER expected."); } orderBy.Tokens.Add(tokenizer.Current); // subqueries TSQLSubqueryHelper.ReadUntilStop( tokenizer, orderBy, new List <TSQLFutureKeywords>() { }, new List <TSQLKeywords>() { TSQLKeywords.FOR, TSQLKeywords.OPTION }, lookForStatementStarts: true); return(orderBy); }
public TSQLUpdateClause Parse(ITSQLTokenizer tokenizer) { TSQLUpdateClause update = new TSQLUpdateClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.UPDATE)) { throw new InvalidOperationException("UPDATE expected."); } update.Tokens.Add(tokenizer.Current); TSQLSubqueryHelper.ReadUntilStop( tokenizer, update, new List <TSQLFutureKeywords>() { }, new List <TSQLKeywords>() { TSQLKeywords.SET }, lookForStatementStarts: false); return(update); }
public TSQLUsingClause Parse(ITSQLTokenizer tokenizer) { TSQLUsingClause usingClause = new TSQLUsingClause(); if (!tokenizer.Current.IsFutureKeyword(TSQLFutureKeywords.USING)) { throw new InvalidOperationException("USING expected."); } usingClause.Tokens.Add(tokenizer.Current); /* can contain: * * <table_source> ::= * { * table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] * [ WITH ( table_hint [ [ , ]...n ] ) ] * | rowset_function [ [ AS ] table_alias ] * [ ( bulk_column_alias [ ,...n ] ) ] * | user_defined_function [ [ AS ] table_alias ] * | OPENXML <openxml_clause> * | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] * | <joined_table> * | <pivoted_table> * | <unpivoted_table> * } * https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15#syntax */ int nestedLevel = 0; while ( tokenizer.MoveNext() && !tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon) && !( nestedLevel == 0 && tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses) ) && ( nestedLevel > 0 || tokenizer.Current.Type != TSQLTokenType.Keyword || ( tokenizer.Current.Type == TSQLTokenType.Keyword && !tokenizer.Current.AsKeyword.Keyword.In ( // ON is required in MERGE statement after USING TSQLKeywords.ON ) ) )) { TSQLSubqueryHelper.RecurseParens( tokenizer, usingClause, ref nestedLevel); } return(usingClause); }
public TSQLSelectClause Parse(ITSQLTokenizer tokenizer) { TSQLSelectClause select = new TSQLSelectClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.SELECT)) { throw new InvalidOperationException("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.MoveNext() && !tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon) && !( nestedLevel == 0 && tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses) ) && ( nestedLevel > 0 || tokenizer.Current.Type != TSQLTokenType.Keyword || ( tokenizer.Current.Type == TSQLTokenType.Keyword && !tokenizer.Current.AsKeyword.Keyword.In ( TSQLKeywords.INTO, TSQLKeywords.FROM, TSQLKeywords.WHERE, TSQLKeywords.GROUP, TSQLKeywords.HAVING, TSQLKeywords.ORDER, TSQLKeywords.UNION, TSQLKeywords.EXCEPT, TSQLKeywords.INTERSECT, TSQLKeywords.FOR, TSQLKeywords.OPTION ) && !tokenizer.Current.AsKeyword.Keyword.IsStatementStart() ) )) { TSQLSubqueryHelper.RecurseParens( tokenizer, select, ref nestedLevel); } return(select); }
public TSQLFromClause Parse(ITSQLTokenizer tokenizer) { TSQLFromClause from = new TSQLFromClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.FROM)) { throw new InvalidOperationException("FROM expected."); } from.Tokens.Add(tokenizer.Current); // derived tables // TVF int nestedLevel = 0; while ( tokenizer.MoveNext() && !( 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.WHERE, TSQLKeywords.GROUP, TSQLKeywords.HAVING, TSQLKeywords.ORDER, TSQLKeywords.UNION, TSQLKeywords.EXCEPT, TSQLKeywords.INTERSECT, TSQLKeywords.FOR, TSQLKeywords.OPTION ) && !tokenizer.Current.AsKeyword.Keyword.IsStatementStart() ) )) { TSQLSubqueryHelper.RecurseParens( tokenizer, from, ref nestedLevel); } return(from); }
public TSQLSetClause Parse(ITSQLTokenizer tokenizer) { TSQLSetClause set = new TSQLSetClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.SET)) { throw new InvalidOperationException("SET expected."); } set.Tokens.Add(tokenizer.Current); // TODO: // parse this rare but valid horror scenario // update output // set output.output = 1 // output deleted.* // maybe create assignment expression parser? int nestedLevel = 0; while ( tokenizer.MoveNext() && !tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon) && !( nestedLevel == 0 && tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses) ) && ( nestedLevel > 0 || ( tokenizer.Current.Type != TSQLTokenType.Keyword && !tokenizer.Current.IsFutureKeyword(TSQLFutureKeywords.OUTPUT) ) || ( tokenizer.Current.Type == TSQLTokenType.Keyword && !tokenizer.Current.AsKeyword.Keyword.In ( TSQLKeywords.FROM, TSQLKeywords.WHERE, TSQLKeywords.OPTION ) && !tokenizer.Current.AsKeyword.Keyword.IsStatementStart() ) )) { TSQLSubqueryHelper.RecurseParens( tokenizer, set, ref nestedLevel); } return(set); }
public TSQLWhereClause Parse(ITSQLTokenizer tokenizer) { TSQLWhereClause where = new TSQLWhereClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.WHERE)) { throw new InvalidOperationException("WHERE expected."); } where.Tokens.Add(tokenizer.Current); // subqueries int nestedLevel = 0; while ( tokenizer.MoveNext() && !tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon) && !( nestedLevel == 0 && tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses) ) && ( nestedLevel > 0 || tokenizer.Current.Type != TSQLTokenType.Keyword || ( tokenizer.Current.Type == TSQLTokenType.Keyword && !tokenizer.Current.AsKeyword.Keyword.In ( TSQLKeywords.GROUP, TSQLKeywords.HAVING, TSQLKeywords.UNION, TSQLKeywords.EXCEPT, TSQLKeywords.INTERSECT, TSQLKeywords.ORDER, TSQLKeywords.FOR, TSQLKeywords.OPTION ) && !tokenizer.Current.AsKeyword.Keyword.IsStatementStart() ) )) { TSQLSubqueryHelper.RecurseParens( tokenizer, where, ref nestedLevel); } return(where); }
public TSQLWithClause Parse(ITSQLTokenizer tokenizer) { TSQLWithClause with = new TSQLWithClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.WITH)) { throw new InvalidOperationException("WITH expected."); } with.Tokens.Add(tokenizer.Current); // subqueries int nestedLevel = 0; while ( tokenizer.MoveNext() && !( nestedLevel == 0 && tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses) ) && ( nestedLevel > 0 || tokenizer.Current.Type != TSQLTokenType.Keyword || ( tokenizer.Current.Type == TSQLTokenType.Keyword && !tokenizer.Current.AsKeyword.Keyword.In ( TSQLKeywords.SELECT, TSQLKeywords.INSERT, TSQLKeywords.UPDATE, TSQLKeywords.DELETE, TSQLKeywords.MERGE ) ) )) { TSQLSubqueryHelper.RecurseParens( tokenizer, with, ref nestedLevel); } return(with); }
public TSQLOrderByClause Parse(ITSQLTokenizer tokenizer) { TSQLOrderByClause orderBy = new TSQLOrderByClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.ORDER)) { throw new InvalidOperationException("ORDER expected."); } orderBy.Tokens.Add(tokenizer.Current); // subqueries int nestedLevel = 0; while ( tokenizer.MoveNext() && !tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon) && !( nestedLevel == 0 && tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses) ) && ( nestedLevel > 0 || tokenizer.Current.Type != TSQLTokenType.Keyword || ( tokenizer.Current.Type == TSQLTokenType.Keyword && !tokenizer.Current.AsKeyword.Keyword.In ( TSQLKeywords.FOR, TSQLKeywords.OPTION ) && !tokenizer.Current.AsKeyword.Keyword.IsStatementStart() ) )) { TSQLSubqueryHelper.RecurseParens( tokenizer, orderBy, ref nestedLevel); } return(orderBy); }
public TSQLSelectClause Parse(ITSQLTokenizer tokenizer) { TSQLSelectClause select = new TSQLSelectClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.SELECT)) { throw new InvalidOperationException("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 TSQLSubqueryHelper.ReadUntilStop( tokenizer, select, new List <TSQLFutureKeywords>() { }, new List <TSQLKeywords>() { TSQLKeywords.INTO, TSQLKeywords.FROM, TSQLKeywords.WHERE, TSQLKeywords.GROUP, TSQLKeywords.HAVING, TSQLKeywords.ORDER, TSQLKeywords.UNION, TSQLKeywords.EXCEPT, TSQLKeywords.INTERSECT, TSQLKeywords.FOR, TSQLKeywords.OPTION }, lookForStatementStarts: true); return(select); }
public TSQLIntoClause Parse(ITSQLTokenizer tokenizer) { TSQLIntoClause into = new TSQLIntoClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.INTO)) { throw new InvalidOperationException("INTO expected."); } into.Tokens.Add(tokenizer.Current); int nestedLevel = 0; while ( tokenizer.MoveNext() && !tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon) && !( nestedLevel == 0 && tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses) ) && ( nestedLevel > 0 || tokenizer.Current.Type == TSQLTokenType.Identifier || tokenizer.Current.IsCharacter(TSQLCharacters.Period) || tokenizer.Current.IsCharacter(TSQLCharacters.OpenParentheses) || tokenizer.Current.Type == TSQLTokenType.Whitespace || tokenizer.Current.Type == TSQLTokenType.SingleLineComment || tokenizer.Current.Type == TSQLTokenType.MultilineComment )) { TSQLSubqueryHelper.RecurseParens( tokenizer, into, ref nestedLevel); } return(into); }
/// <summary> /// This reads recursively through parenthesis and returns when it hits /// one of the stop words outside of any nested parenthesis. /// </summary> public static void ReadUntilStop( ITSQLTokenizer tokenizer, TSQLExpression expression, List <TSQLFutureKeywords> futureKeywords, List <TSQLKeywords> keywords, bool lookForStatementStarts) { int nestedLevel = 0; while ( tokenizer.MoveNext() && !tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon) && !( nestedLevel == 0 && tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses) ) && ( nestedLevel > 0 || ( tokenizer.Current.Type != TSQLTokenType.Keyword && !futureKeywords.Any(fk => tokenizer.Current.IsFutureKeyword(fk)) ) || ( tokenizer.Current.Type == TSQLTokenType.Keyword && !keywords.Any(k => tokenizer.Current.AsKeyword.Keyword == k) && !( lookForStatementStarts && tokenizer.Current.AsKeyword.Keyword.IsStatementStart() ) ) )) { TSQLSubqueryHelper.RecurseParens( tokenizer, expression, ref nestedLevel); } }
public TSQLFromClause Parse(ITSQLTokenizer tokenizer) { TSQLFromClause from = new TSQLFromClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.FROM)) { throw new InvalidOperationException("FROM expected."); } from.Tokens.Add(tokenizer.Current); // derived tables // TVF TSQLSubqueryHelper.ReadUntilStop( tokenizer, from, new List <TSQLFutureKeywords>() { }, new List <TSQLKeywords>() { TSQLKeywords.WHERE, TSQLKeywords.GROUP, TSQLKeywords.HAVING, TSQLKeywords.ORDER, TSQLKeywords.UNION, TSQLKeywords.EXCEPT, TSQLKeywords.INTERSECT, TSQLKeywords.FOR, TSQLKeywords.OPTION }, lookForStatementStarts: true); return(from); }
public TSQLWithClause Parse(ITSQLTokenizer tokenizer) { TSQLWithClause with = new TSQLWithClause(); if (!tokenizer.Current.IsKeyword(TSQLKeywords.WITH)) { throw new InvalidOperationException("WITH expected."); } with.Tokens.Add(tokenizer.Current); // subqueries int nestedLevel = 0; int parenCount = 0; int identifierCount = 0; bool afterAs = false; while ( tokenizer.MoveNext() && !tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon) && !( nestedLevel == 0 && tokenizer.Current.IsCharacter(TSQLCharacters.CloseParentheses) ) && !( // only allow a set of parens at root level // if it's following an AS // or if it's the column list between the CTE name and the AS nestedLevel == 0 && tokenizer.Current.IsCharacter(TSQLCharacters.OpenParentheses) && afterAs && parenCount >= identifierCount ) && ( nestedLevel > 0 || tokenizer.Current.Type != TSQLTokenType.Keyword || ( tokenizer.Current.Type == TSQLTokenType.Keyword && !tokenizer.Current.AsKeyword.Keyword.In ( TSQLKeywords.SELECT, TSQLKeywords.INSERT, TSQLKeywords.UPDATE, TSQLKeywords.DELETE, TSQLKeywords.MERGE ) ) )) { if (nestedLevel == 0) { if (afterAs && tokenizer.Current.IsCharacter(TSQLCharacters.OpenParentheses)) { parenCount++; } else if (tokenizer.Current.Type == TSQLTokenType.Identifier) { identifierCount++; afterAs = false; } else if (tokenizer.Current.IsKeyword(TSQLKeywords.AS)) { afterAs = true; } } TSQLSubqueryHelper.RecurseParens( tokenizer, with, ref nestedLevel); } return(with); }