DBSqlParser.cs source code in C# .NET

Source code for the .NET framework in C#

                        

Code:

/ Dotnetfx_Vista_SP2 / Dotnetfx_Vista_SP2 / 8.0.50727.4016 / DEVDIV / depot / DevDiv / releases / whidbey / NetFxQFE / ndp / fx / src / DataOracleClient / System / Data / Common / DBSqlParser.cs / 1 / DBSqlParser.cs

                            //------------------------------------------------------------------------------ 
// 
//      Copyright (c) Microsoft Corporation.  All rights reserved.
// 
// [....] 
//-----------------------------------------------------------------------------
 
namespace System.Data.OracleClient { 

    using System; 
    using System.Collections;
    using System.Data.Common;
    using System.ComponentModel;
    using System.Diagnostics; 
    using System.Globalization;
    using System.Text; 
    using System.Text.RegularExpressions; 

    //--------------------------------------------------------------------- 
    // DbSqlParser
    //
    //  This class implements a basic SQL parser to allow providers that
    //  do not get schema information from their back end to gather it 
    //  from the statement text itself.
    // 
    abstract internal class DbSqlParser { 
        public enum TokenType {
            Null        = 0, 
            Identifier  = 1,
            QuotedIdentifier = 2,
            String      = 3,
 
            Other       = 100,
            Other_Comma, 
            Other_Period, 
            Other_LeftParen,
            Other_RightParen, 
            Other_Star,

            Keyword     = 200,
 
            Keyword_ALL,
            Keyword_AS, 
            Keyword_COMPUTE, 
            Keyword_CROSS,
            Keyword_DISTINCT, 
            Keyword_FOR,
            Keyword_FROM,
            Keyword_FULL,
            Keyword_GROUP, 
            Keyword_HAVING,
            Keyword_INNER, 
            Keyword_INTERSECT, 
            Keyword_INTO,
            Keyword_JOIN, 
            Keyword_LEFT,
            Keyword_MINUS,
            Keyword_NATURAL,
            Keyword_ON, 
            Keyword_ORDER,
            Keyword_OUTER, 
            Keyword_RIGHT, 
            Keyword_SELECT,
            Keyword_TOP, 
            Keyword_UNION,
            Keyword_USING,
            Keyword_WHERE,
        }; 

        internal struct Token { 
            private TokenType   _type; 
            private string      _value;
 
            internal static readonly Token Null = new Token(0, null);

            internal string Value {
                get { 
                    return _value;
                } 
            } 

            internal TokenType Type { 
                get {
                    return _type;
                }
            } 

            internal Token(TokenType type, string value) { 
                _type = type; 
                _value = value;
            } 
        }

        private const string SqlTokenPattern_Part1 =
            @"[\s;]*" 
            + @"("
            + @"(?all|as|compute|cross|distinct|for|from|full|group|having|intersect|inner|join|left|minus|natural|order|outer|on|right|select|top|union|using|where)\b" 
            + @"|" 
            + @"(?";
 
        // validIdentifierFirstCharacters
        // validIdentifierCharacters

        private const string SqlTokenPattern_Part2 = 
            @"*)"
            + @"|"; 
 
        // quotePrefixCharacter
 
        private const string SqlTokenPattern_Part3 =
            "(?";

        // quotedIdentifierCharacters 

        private const string SqlTokenPattern_Part4 = 
            ")"; 

        // quoteSuffixCharacter 

        private const string SqlTokenPattern_Part5 =
            @"|"
            + @"(?"; 

        // stringPattern 
 
        private const string SqlTokenPattern_Part6 =
            @")" 
            + @"|"
            + @"(?.)"
            + @")"
            + @"[\s;]*"; 

 
        static private Regex    _sqlTokenParser; 
        static private string   _sqlTokenPattern;
 
        static private int      _identifierGroup;
        static private int      _quotedidentifierGroup;
        static private int      _keywordGroup;
        static private int      _stringGroup; 
        static private int      _otherGroup;
 
 
        private string _quotePrefixCharacter;
        private string _quoteSuffixCharacter; 

        private DbSqlParserColumnCollection     _columns;
        private DbSqlParserTableCollection      _tables;
 

        public DbSqlParser(string quotePrefixCharacter, string quoteSuffixCharacter, string regexPattern) { 
            _quotePrefixCharacter = quotePrefixCharacter; 
            _quoteSuffixCharacter = quoteSuffixCharacter;
            _sqlTokenPattern = regexPattern; 
        }

        static internal string CreateRegexPattern(
                        string validIdentifierFirstCharacters, string validIdendifierCharacters, 
                        string quotePrefixCharacter, string quotedIdentifierCharacters,
                        string quoteSuffixCharacter, string stringPattern) { 
            // Combine the regex patterns from the user and the framework that we 
            // will require, and will return the actual regex pattern string to
            // be handed to the class constructor. 
            StringBuilder sb = new StringBuilder();

            sb.Append(SqlTokenPattern_Part1);
            sb.Append(validIdentifierFirstCharacters); 
            sb.Append(validIdendifierCharacters);
            sb.Append(SqlTokenPattern_Part2); 
            sb.Append(quotePrefixCharacter); 
            sb.Append(SqlTokenPattern_Part3);
            sb.Append(quotedIdentifierCharacters); 
            sb.Append(SqlTokenPattern_Part4);
            sb.Append(quoteSuffixCharacter);
            sb.Append(SqlTokenPattern_Part5);
            sb.Append(stringPattern); 
            sb.Append(SqlTokenPattern_Part6);
 
            string result = sb.ToString(); 
            return result;
        } 

        internal DbSqlParserColumnCollection Columns {
            get {
                if (null == _columns) { 
                    _columns = new DbSqlParserColumnCollection();
                } 
                return _columns; 
            }
        } 

        virtual protected string QuotePrefixCharacter {
            get { return _quotePrefixCharacter; }
         } 

        virtual protected string QuoteSuffixCharacter { 
            get { return _quoteSuffixCharacter; } 
        }
 
        static private Regex SqlTokenParser {
            get {
                Regex parser = _sqlTokenParser;
 
                if (null == parser) {
                    parser = GetSqlTokenParser(); 
                } 
                return parser;
            } 
        }

        internal DbSqlParserTableCollection Tables {
            get { 
                if (null == _tables) {
                    _tables = new DbSqlParserTableCollection(); 
                } 
                return _tables;
            } 
        }

        private void AddColumn(int maxPart, Token[] namePart, Token aliasName) {
#if TRACINGPARSER 
            Console.WriteLine(String.Format("\t\t\t\t\t\tADDING COLUMN: \"{0}\".\"{1}\".\"{2}\".\"{3}\" as \"{4}\"",
                        GetPart(0, namePart, maxPart), 
                        GetPart(1, namePart, maxPart), 
                        GetPart(2, namePart, maxPart),
                        GetPart(3, namePart, maxPart), 
                        GetTokenAsString(aliasName)));
#endif // TRACINGPARSER
            Columns.Add(GetPart(0, namePart, maxPart),
                        GetPart(1, namePart, maxPart), 
                        GetPart(2, namePart, maxPart),
                        GetPart(3, namePart, maxPart), 
                        GetTokenAsString(aliasName)); 
        }
 
        private void AddTable(int maxPart, Token[] namePart, Token correlationName) {
#if TRACINGPARSER
            Console.WriteLine(String.Format("\t\t\t\t\t\tADDING TABLE: \"{0}\".\"{1}\".\"{2}\" as \"{3}\"",
                        GetPart(1, namePart, maxPart), 
                        GetPart(2, namePart, maxPart),
                        GetPart(3, namePart, maxPart), 
                        GetTokenAsString(correlationName))); 
#endif // TRACINGPARSER
 
            Tables.Add( GetPart(1, namePart, maxPart),
                        GetPart(2, namePart, maxPart),
                        GetPart(3, namePart, maxPart),
                        GetTokenAsString(correlationName)); 
        }
 
        private void CompleteSchemaInformation() { 
            DbSqlParserColumnCollection columns = Columns;
            DbSqlParserTableCollection tables = Tables; 

            int columnCount = columns.Count;
            int tableCount = tables.Count;
 
            // First, derive all of the information we can about each table
            for (int i = 0; i < tableCount; i++) { 
                DbSqlParserTable table = tables[i]; 
                DbSqlParserColumnCollection tableColumns = GatherTableColumns(table);
                table.Columns = tableColumns; 
            }

            // Next, derive all of the column information we can.
            for (int i = 0; i < columnCount; i++) { 
                DbSqlParserColumn column = columns[i];
                DbSqlParserTable table = FindTableForColumn(column); 
 
                if (!column.IsExpression) {
                    // If this is a '*' column, then we have to expand the '*' into 
                    // its component parts.
                    if ("*" == column.ColumnName) {
                        // Remove the existing "*" column entry and replace it with the
                        // complete list of columns in the table. 
                        columns.RemoveAt(i);
 
                        // If this is a tablename.* column, then add references to the 
                        // all columns in the specified table, otherwise add references
                        // to all columns in all tables. 
                        if (0 != column.TableName.Length) {
                            DbSqlParserColumnCollection tableColumns = table.Columns;
                            int tableColumnCount = tableColumns.Count;
 
                            for (int j =0; j < tableColumnCount; ++j) {
                                columns.Insert(i + j, tableColumns[j]); 
                            } 
                            columnCount += tableColumnCount - 1;    // don't forget to adjust our loop end
                            i += tableColumnCount - 1; 
                        }
                        else {
                            for (int k = 0; k < tableCount; k++) {
                                table = tables[k]; 

                                DbSqlParserColumnCollection tableColumns = table.Columns; 
                                int tableColumnCount = tableColumns.Count; 

                                for (int j =0; j < tableColumnCount; ++j) { 
                                    columns.Insert(i + j, tableColumns[j]);
                                }
                                columnCount += tableColumnCount - 1;    // don't forget to adjust our loop end
                                i += tableColumnCount; 
                            }
                        } 
                    } 
                    else {
                        // if this isn't a '*' column, we find the table that the column belongs 
                        // to, and ask it's column collection for the completed column info (that
                        // contains information about key values, etc.)
                        DbSqlParserColumn completedColumn = FindCompletedColumn(table, column);
 
                        if (null != completedColumn) {// MDAC 87152
                            column.CopySchemaInfoFrom(completedColumn); 
                        } 
                        else {
                            column.CopySchemaInfoFrom(table); 
                        }
                    }
                }
            } 

            // Finally, derive the key column information for each table 
            for (int i = 0; i < tableCount; i++) { 
                DbSqlParserTable table = tables[i];
                GatherKeyColumns(table); 
            }
        }

        protected DbSqlParserColumn FindCompletedColumn(DbSqlParserTable table, DbSqlParserColumn searchColumn) { 
            DbSqlParserColumnCollection columns = table.Columns;
            int columnsCount = columns.Count; 
 
            for (int i = 0; i < columnsCount; ++i) {
                DbSqlParserColumn column = columns[i]; 

                // Compare each part of the name (if they exist) with the
                // table and pick the one that matches all the parts that exist.
                if (CatalogMatch(column.ColumnName, searchColumn.ColumnName)) { 
                    return column;
                } 
            } 

            // MDAC 87152: ROWID and ROWNUM shouldn't fire an assert here: 
            //Debug.Assert(false, "Why didn't we find a match for the search column?");
            return null;
        }
 
        internal DbSqlParserTable FindTableForColumn(DbSqlParserColumn column) {
            DbSqlParserTableCollection tables = Tables; 
            int tableCount = tables.Count; 

            for (int i = 0; i < tableCount; ++i) { 
                DbSqlParserTable table = tables[i];

                // if the table name matches the correlation name, then we're certain
                // of a match 
                if (ADP.IsEmpty(column.DatabaseName) && ADP.IsEmpty(column.SchemaName) && CatalogMatch(column.TableName, table.CorrelationName)) {
                    return table; 
                } 
                // otherwise, compare each part of the name (if they exist) with the
                // table and pick the one that matches all the parts that exist. 
                if ((ADP.IsEmpty(column.DatabaseName) || CatalogMatch(column.DatabaseName, table.DatabaseName))
                 && (ADP.IsEmpty(column.SchemaName)   || CatalogMatch(column.SchemaName,   table.SchemaName))
                  && (ADP.IsEmpty(column.TableName)   || CatalogMatch(column.TableName,    table.TableName))) {
                    return table; 
                }
            } 
 
            Debug.Assert(false, "Why didn't we find a match for the column?");
            return null; 
        }

        private string GetPart(int part, Token[] namePart, int maxPart) {
            int partBase = (maxPart - namePart.Length) + part + 1; 

            if (0 > partBase) { 
                return null; 
            }
            return GetTokenAsString(namePart[partBase]); 
        }

        static private Regex GetSqlTokenParser() {
            Regex parser = _sqlTokenParser; 
            if (null == parser) {
                // due to workingset size issues ~1.5MB, don't use RegexOptions.Compiled 
                parser = new Regex(_sqlTokenPattern, RegexOptions.ExplicitCapture | RegexOptions.IgnoreCase); 
                _identifierGroup = parser.GroupNumberFromName("identifier");
                _quotedidentifierGroup = parser.GroupNumberFromName("quotedidentifier"); 
                _keywordGroup = parser.GroupNumberFromName("keyword");
                _stringGroup = parser.GroupNumberFromName("string");
                _otherGroup = parser.GroupNumberFromName("other");
 
                _sqlTokenParser = parser;
            } 
            return parser; 
        }
 
        private string GetTokenAsString(Token token) {
            if (TokenType.QuotedIdentifier == token.Type) {
                return _quotePrefixCharacter + token.Value + _quoteSuffixCharacter;
            } 
            return token.Value;
        } 
 
        // transistion states used for parsing
        private enum PARSERSTATE { 
            NOTHINGYET=1,   //start point
            SELECT,
            COLUMN,
            COLUMNALIAS, 
            TABLE,
            TABLEALIAS, 
            FROM, 
            EXPRESSION,
            JOIN, 
            JOINCONDITION,
            DONE,
        };
 
        public void Parse(string    statementText) {
            Parse2(statementText); 
            CompleteSchemaInformation(); 
        }
 
        private void Parse2(string  statementText) {
            //          Debug.WriteLine(statementText);

            PARSERSTATE parserState = PARSERSTATE.NOTHINGYET; 

            Token[] namePart = new Token[4]; 
            int currentPart = 0; 

            Token alias = Token.Null; 

            TokenType lastTokenType = TokenType.Null;

            int parenLevel = 0; 
            //          bool            distinctFound;
 
            _columns = null; 
            _tables = null;
 
            Match match = SqlTokenParser.Match(statementText);
            Token token = TokenFromMatch(match);

            while (true) { 
                bool addTheTable = false;
 
#if TRACINGPARSER 
                Console.WriteLine(String.Format("{0,-15} {1,-17} {2} {3}", parserState, token.Type.ToString(), parenLevel, token.Value));
#endif //TRACINGPARSER 

                switch (parserState) {
                    case PARSERSTATE.DONE:
                        return; 

                    case PARSERSTATE.NOTHINGYET: 
                        switch (token.Type) { 
                            case TokenType.Keyword_SELECT:
                                parserState = PARSERSTATE.SELECT; 
                                break;

                            default:
                                Debug.Assert (false, "no state defined!!!!we should never be here!!!"); 
                                throw ADP.InvalidOperation(Res.GetString(Res.ADP_SQLParserInternalError));
                        } 
                        break; 

                    case PARSERSTATE.SELECT: 
                        switch (token.Type) {
                            case TokenType.Identifier:
                            case TokenType.QuotedIdentifier:
                                parserState = PARSERSTATE.COLUMN; 
                                currentPart = 0;
                                namePart[0] = token; 
                                break; 

                            case TokenType.Keyword_FROM: 
                                parserState = PARSERSTATE.FROM;
                                break;

                            case TokenType.Other_Star: 
                                parserState = PARSERSTATE.COLUMNALIAS;
                                currentPart = 0; 
                                namePart[0] = token; 
                                break;
 
                            case TokenType.Keyword_DISTINCT:
                                //                              distinctFound = true;
                                break;
 
                            case TokenType.Keyword_ALL:
                                break; 
 
                            case TokenType.Other_LeftParen:
                                parserState = PARSERSTATE.EXPRESSION; 
                                parenLevel++;
                                break;

                            case TokenType.Other_RightParen: 
                                throw ADP.SyntaxErrorMissingParenthesis();
 
                            default: 
                                parserState = PARSERSTATE.EXPRESSION;
                                break; 
                        }
                        break;

                    case PARSERSTATE.COLUMN: 
                        switch (token.Type) {
                            case TokenType.Identifier: 
                            case TokenType.QuotedIdentifier: 
                                if (TokenType.Other_Period != lastTokenType) {
                                    parserState = PARSERSTATE.COLUMNALIAS; 
                                    alias = token;
                                }
                                else {
                                    namePart[++currentPart] = token; 
                                }
                                break; 
 
                            case TokenType.Other_Period:
                                if (currentPart > 3) { 
                                    throw ADP.SyntaxErrorTooManyNameParts();
                                }
                                break;
 
                            case TokenType.Other_Star:
                                parserState = PARSERSTATE.COLUMNALIAS; 
                                namePart[++currentPart] = token; 
                                break;
 
                            case TokenType.Keyword_AS:
                                break;

                            case TokenType.Keyword_FROM: 
                            case TokenType.Other_Comma:
                                parserState = (token.Type == TokenType.Keyword_FROM) ? PARSERSTATE.FROM : PARSERSTATE.SELECT; 
                                AddColumn(currentPart, namePart, alias); 
                                currentPart = -1;
                                alias = Token.Null; 
                                break;

                            case TokenType.Other_LeftParen:
                                parserState = PARSERSTATE.EXPRESSION; 
                                parenLevel++;
                                currentPart = -1; 
                                break; 

                            case TokenType.Other_RightParen: 
                                throw ADP.SyntaxErrorMissingParenthesis();

                            default:
                                parserState = PARSERSTATE.EXPRESSION; 
                                currentPart = -1;
                                break; 
                        } 
                        break;
 
                    case PARSERSTATE.COLUMNALIAS:
                        switch (token.Type) {
                            case TokenType.Keyword_FROM:
                            case TokenType.Other_Comma: 
                                parserState = (token.Type == TokenType.Keyword_FROM) ? PARSERSTATE.FROM : PARSERSTATE.SELECT;
                                AddColumn(currentPart, namePart, alias); 
                                currentPart = -1; 
                                alias = Token.Null;
                                break; 

                            default:
                                throw ADP.SyntaxErrorExpectedCommaAfterColumn();
                        } 
                        break;
 
                    case PARSERSTATE.EXPRESSION: 
                        switch (token.Type) {
                            case TokenType.Identifier: 
                            case TokenType.QuotedIdentifier:
                                if (0 == parenLevel) {
                                    alias = token;
                                } 
                                break;
 
                            case TokenType.Keyword_FROM: 
                            case TokenType.Other_Comma:
                                if (0 == parenLevel) { 
                                    parserState = (token.Type == TokenType.Keyword_FROM) ? PARSERSTATE.FROM : PARSERSTATE.SELECT;
                                    AddColumn(currentPart, namePart, alias);
                                    currentPart = -1;
                                    alias = Token.Null; 
                                }
                                break; 
 
                            case TokenType.Other_LeftParen:
                                parenLevel++; 
                                break;

                            case TokenType.Other_RightParen:
                                parenLevel--; 
                                break;
                        } 
                        break; 

                    case PARSERSTATE.FROM: 
                        switch (token.Type) {
                            case TokenType.Identifier:
                            case TokenType.QuotedIdentifier:
                                parserState = PARSERSTATE.TABLE; 
                                currentPart = 0;
                                namePart[0] = token; 
                                break; 

                            default: 
                                throw ADP.SyntaxErrorExpectedIdentifier();
                        }
                        break;
 
                    case PARSERSTATE.JOIN:
                        switch (token.Type) { 
                            case TokenType.Keyword_INNER: 
                            case TokenType.Keyword_OUTER:
                                //parserState = PARSERSTATE.JOIN; 
                                break;

                            case TokenType.Keyword_JOIN:
                                parserState = PARSERSTATE.FROM; 
                                break;
 
                            default: 
                                throw ADP.SyntaxErrorExpectedNextPart();        //
                        } 
                        break;

                    case PARSERSTATE.JOINCONDITION:
                        switch (token.Type) { 
                             case TokenType.Other_LeftParen:
                                parenLevel++; 
                                 break; 

                            case TokenType.Other_RightParen: 
                                parenLevel--;
                                break;

                            default: 
                                if (0 == parenLevel) {
                                    switch (token.Type) { 
                                        case TokenType.Keyword_COMPUTE: 
                                        case TokenType.Keyword_FOR:
                                        case TokenType.Keyword_GROUP: 
                                        case TokenType.Keyword_HAVING:
                                        case TokenType.Keyword_INTERSECT:
                                        case TokenType.Keyword_MINUS:
                                        case TokenType.Keyword_ORDER: 
                                        case TokenType.Keyword_UNION:
                                        case TokenType.Keyword_WHERE: 
                                        case TokenType.Null: 
                                            parserState = PARSERSTATE.DONE;
                                            break; 

                                        case TokenType.Keyword_JOIN:
                                            parserState = PARSERSTATE.FROM;
                                            break; 

                                        case TokenType.Keyword_CROSS: 
                                        case TokenType.Keyword_LEFT: 
                                        case TokenType.Keyword_NATURAL:
                                        case TokenType.Keyword_RIGHT: 
                                            parserState = PARSERSTATE.JOIN;
                                            break;
                                        }
                                    } 
                                break;
                        } 
                        break; 

                    case PARSERSTATE.TABLE: 
                        switch (token.Type) {
                            case TokenType.Identifier:
                            case TokenType.QuotedIdentifier:
                                if (TokenType.Other_Period != lastTokenType) { 
                                    parserState = PARSERSTATE.TABLEALIAS;
                                    alias = token; 
                                } 
                                else {
                                    namePart[++currentPart] = token; 
                                }
                                break;

                            case TokenType.Other_Period: 
                                if (currentPart > 2) {
                                    throw ADP.SyntaxErrorTooManyNameParts(); 
                                } 
                                break;
 
                            case TokenType.Keyword_AS:
                                break;

                            case TokenType.Keyword_COMPUTE: 
                            case TokenType.Keyword_FOR:
                            case TokenType.Keyword_GROUP: 
                            case TokenType.Keyword_HAVING: 
                            case TokenType.Keyword_INTERSECT:
                            case TokenType.Keyword_MINUS: 
                            case TokenType.Keyword_ORDER:
                            case TokenType.Keyword_UNION:
                            case TokenType.Keyword_WHERE:
                            case TokenType.Null: 
                                parserState = PARSERSTATE.DONE;
                                addTheTable = true; 
                                break; 

                            case TokenType.Keyword_JOIN: 
                            case TokenType.Other_Comma:
                                parserState = PARSERSTATE.FROM;
                                addTheTable = true;
                                break; 

                            case TokenType.Keyword_CROSS: 
                            case TokenType.Keyword_LEFT: 
                            case TokenType.Keyword_NATURAL:
                            case TokenType.Keyword_RIGHT: 
                                parserState = PARSERSTATE.JOIN;
                                addTheTable = true;
                                break;
 
                            case TokenType.Keyword_ON:
                            case TokenType.Keyword_USING: 
                                parserState = PARSERSTATE.JOINCONDITION; 
                                addTheTable = true;
                                break; 

                            default:
                                throw ADP.SyntaxErrorExpectedNextPart();
                        } 
                        break;
 
                    case PARSERSTATE.TABLEALIAS: 
                        addTheTable = true;
 
                        switch (token.Type) {
                            case TokenType.Keyword_COMPUTE:
                            case TokenType.Keyword_FOR:
                            case TokenType.Keyword_GROUP: 
                            case TokenType.Keyword_HAVING:
                            case TokenType.Keyword_INTERSECT: 
                            case TokenType.Keyword_MINUS: 
                            case TokenType.Keyword_ORDER:
                            case TokenType.Keyword_UNION: 
                            case TokenType.Keyword_WHERE:
                            case TokenType.Null:
                                parserState = PARSERSTATE.DONE;
                                break; 

                            case TokenType.Keyword_JOIN: 
                            case TokenType.Other_Comma: 
                                parserState = PARSERSTATE.FROM;
                                break; 

                            case TokenType.Keyword_ON:
                            case TokenType.Keyword_USING:
                                parserState = PARSERSTATE.JOINCONDITION; 
                                break;
 
                            case TokenType.Keyword_CROSS: 
                            case TokenType.Keyword_LEFT:
                            case TokenType.Keyword_NATURAL: 
                            case TokenType.Keyword_RIGHT:
                                parserState = PARSERSTATE.JOIN;
                                break;
 
                            default:
                                throw ADP.SyntaxErrorExpectedCommaAfterTable(); 
                        } 
                        break;
 

                    default:
                        Debug.Assert (false, "no state defined!!!!we should never be here!!!");
                        throw ADP.InvalidOperation(Res.GetString(Res.ADP_SQLParserInternalError)); 
                }
 
                if (addTheTable) { 
                    AddTable(currentPart, namePart, alias);
                    currentPart = -1; 
                    alias = Token.Null;
                    addTheTable = false;
                }
                lastTokenType = token.Type; 

                match = match.NextMatch(); 
                token = TokenFromMatch(match); 
            }
        } 

        static internal Token TokenFromMatch(Match match) {
            // No matches? we must be at the end of the string!
            if ((null == match) || (Match.Empty == match) || (!match.Success)) { 
                return Token.Null;
            } 
            if (match.Groups[_identifierGroup].Success) { 
                return new Token(TokenType.Identifier,          match.Groups[_identifierGroup].Value);
            } 
            if (match.Groups[_quotedidentifierGroup].Success) {
                return new Token(TokenType.QuotedIdentifier,    match.Groups[_quotedidentifierGroup].Value);
            }
            if (match.Groups[_stringGroup].Success) { 
                return new Token(TokenType.String,              match.Groups[_stringGroup].Value);
            } 
            if (match.Groups[_otherGroup].Success) { 
                string value = match.Groups[_otherGroup].Value.ToLower(CultureInfo.InvariantCulture);
                TokenType key = TokenType.Other; 

                switch (value[0]) {
                    case ',':
                        key = TokenType.Other_Comma; 
                        break;
 
                    case '.': 
                        key = TokenType.Other_Period;
                        break; 

                    case '(':
                        key = TokenType.Other_LeftParen;
                        break; 

                    case ')': 
                        key = TokenType.Other_RightParen; 
                        break;
 
                    case '*':
                        key = TokenType.Other_Star;
                        break;
                } 
                return new Token(key, match.Groups[_otherGroup].Value);
            } 
            if (match.Groups[_keywordGroup].Success) { 
                string value = match.Groups[_keywordGroup].Value.ToLower(CultureInfo.InvariantCulture);
                int length = value.Length; 
                TokenType key = TokenType.Keyword;
                switch (length) {
                case 2:
                    if ("as" == value)      { key = TokenType.Keyword_AS; break; } 
                    if ("on" == value)      { key = TokenType.Keyword_ON; break; }
                    break; 
 
                case 3:
                    if ("for" == value)     { key = TokenType.Keyword_FOR;      break; } 
                    if ("all" == value)     { key = TokenType.Keyword_ALL;      break; }
                    if ("top" == value)     { key = TokenType.Keyword_TOP;      break; }
                    break;
 
                case 4:
                    if ("from" == value)    { key = TokenType.Keyword_FROM;     break; } 
                    if ("into" == value)    { key = TokenType.Keyword_INTO;     break; } 
                    if ("join" == value)    { key = TokenType.Keyword_JOIN;     break; }
                    if ("left" == value)    { key = TokenType.Keyword_LEFT;     break; } 
                    break;

                case 5:
                    if ("where" == value)   { key = TokenType.Keyword_WHERE;    break; } 
                    if ("group" == value)   { key = TokenType.Keyword_GROUP;    break; }
                    if ("order" == value)   { key = TokenType.Keyword_ORDER;    break; } 
                    if ("right" == value)   { key = TokenType.Keyword_RIGHT;    break; } 
                    if ("outer" == value)   { key = TokenType.Keyword_OUTER;    break; }
                    if ("using" == value)   { key = TokenType.Keyword_USING;    break; } 
                    if ("cross" == value)   { key = TokenType.Keyword_CROSS;    break; }
                    if ("union" == value)   { key = TokenType.Keyword_UNION;    break; }
                    if ("minus" == value)   { key = TokenType.Keyword_MINUS;    break; }
                    if ("inner" == value)   { key = TokenType.Keyword_INNER;    break; } 
                    break;
 
                case 6: 
                    if ("select" == value)  { key = TokenType.Keyword_SELECT;   break; }
                    if ("having" == value)  { key = TokenType.Keyword_HAVING;   break; } 
                    break;

                case 7:
                    if ("compute" == value) { key = TokenType.Keyword_COMPUTE;  break; } 
                    if ("natural" == value) { key = TokenType.Keyword_NATURAL;  break; }
                    break; 
 
                case 8:
                    if ("distinct" == value){ key = TokenType.Keyword_DISTINCT; break; } 
                    break;

                case 9:
                    if ("intersect" == value){key = TokenType.Keyword_INTERSECT;break; } 
                    break;
                } 
 
                if (TokenType.Keyword != key) {
                    return new Token(key, value); 
                }
                Debug.Assert(TokenType.Keyword != key, "unrecognized keyword: TokenFromMatch not in [....] with regex pattern");
            }
            else { 
                Debug.Assert(false, "unrecognized pattern: TokenFromMatch not in [....] with regex pattern");
            } 
            return Token.Null; 
        }
 
        //  compares the two values in catalog order, taking into account
        //  quoting rules, and case-sensitivity rules, and returns true if
        //  they match.
        abstract protected bool CatalogMatch(string valueA, string valueB); 

        //  Called after the table and column information is completed to 
        //  identify which columns in the select-list are key columns for 
        //  their table.
        abstract protected void GatherKeyColumns(DbSqlParserTable table); 

        //  Called to get a column list for the table specified.
        abstract protected DbSqlParserColumnCollection GatherTableColumns(DbSqlParserTable table);
 
    }
} 
 

/* 
        [STAThread]
        static void Main(string[] args)
        {
            DbSqlParser parser  = new DbSqlParser( 
                        "[a-zA-Z_#$]",
                        "[a-zA-Z0-9_#$]", 
                        "\"", 
                        "([^\"]|\"\")*",
                        "\"", 
                        "(" + "'" + "([^']|'')*" + "'" + ")"
                        );

            parser.Parse("select 'this is a test' a from dual"); 
            parser.Parse("select (2 + count(empno)) a from scott.emp");
            parser.Parse("select empno + (2 + abs(empno)) \"Test a\" from scott.emp"); 
            parser.Parse("select empno + (2 + abs(empno)) \"Test a\" from scott.emp \"table1\", scott.dept \"table 2\""); 
            parser.Parse("select select scott.emp.ename \"Test a\" from scott.emp");
            parser.Parse("select \"SCOTT\".emp.empno \"Test a\" from scott.emp"); 
            parser.Parse("select \"SCOTT\".\"EMP\".empno \"Test a\" from scott.emp");
            parser.Parse("select \"SCOTT\".\"EMP\".\"EMPNO\" \"Test a B\" from scott.emp");
            parser.Parse("select \"Database\".\"Schema\".\"Table\".* from scott.emp \"Test a B\", scott.dept");
            parser.Parse("select Scott.\"EMP\".\"EMPNO\" \"Test a B\" from scott.emp"); 
            parser.Parse("select Scott.Emp.\"EMPNO\" \"Test a B\" from scott.emp");
            parser.Parse("select aa.b, c.d e, f.g.h i, j.k.l.m n from o p, q.r s, t.u.v w where"); 
            parser.Parse("select 2 + 1 a from scott.emp"); 
            parser.Parse("select 2 + 1 a from dual");
            parser.Parse("select empno + 2 a from scott.emp"); 
            parser.Parse("select empno + 2 from scott.emp");
            parser.Parse("select 2 + empno a from scott.emp");
            select "Foo".*, "Foo".empNO eno from scott.emp "Foo"
        } 
*/

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
//------------------------------------------------------------------------------ 
// 
//      Copyright (c) Microsoft Corporation.  All rights reserved.
// 
// [....] 
//-----------------------------------------------------------------------------
 
namespace System.Data.OracleClient { 

    using System; 
    using System.Collections;
    using System.Data.Common;
    using System.ComponentModel;
    using System.Diagnostics; 
    using System.Globalization;
    using System.Text; 
    using System.Text.RegularExpressions; 

    //--------------------------------------------------------------------- 
    // DbSqlParser
    //
    //  This class implements a basic SQL parser to allow providers that
    //  do not get schema information from their back end to gather it 
    //  from the statement text itself.
    // 
    abstract internal class DbSqlParser { 
        public enum TokenType {
            Null        = 0, 
            Identifier  = 1,
            QuotedIdentifier = 2,
            String      = 3,
 
            Other       = 100,
            Other_Comma, 
            Other_Period, 
            Other_LeftParen,
            Other_RightParen, 
            Other_Star,

            Keyword     = 200,
 
            Keyword_ALL,
            Keyword_AS, 
            Keyword_COMPUTE, 
            Keyword_CROSS,
            Keyword_DISTINCT, 
            Keyword_FOR,
            Keyword_FROM,
            Keyword_FULL,
            Keyword_GROUP, 
            Keyword_HAVING,
            Keyword_INNER, 
            Keyword_INTERSECT, 
            Keyword_INTO,
            Keyword_JOIN, 
            Keyword_LEFT,
            Keyword_MINUS,
            Keyword_NATURAL,
            Keyword_ON, 
            Keyword_ORDER,
            Keyword_OUTER, 
            Keyword_RIGHT, 
            Keyword_SELECT,
            Keyword_TOP, 
            Keyword_UNION,
            Keyword_USING,
            Keyword_WHERE,
        }; 

        internal struct Token { 
            private TokenType   _type; 
            private string      _value;
 
            internal static readonly Token Null = new Token(0, null);

            internal string Value {
                get { 
                    return _value;
                } 
            } 

            internal TokenType Type { 
                get {
                    return _type;
                }
            } 

            internal Token(TokenType type, string value) { 
                _type = type; 
                _value = value;
            } 
        }

        private const string SqlTokenPattern_Part1 =
            @"[\s;]*" 
            + @"("
            + @"(?all|as|compute|cross|distinct|for|from|full|group|having|intersect|inner|join|left|minus|natural|order|outer|on|right|select|top|union|using|where)\b" 
            + @"|" 
            + @"(?";
 
        // validIdentifierFirstCharacters
        // validIdentifierCharacters

        private const string SqlTokenPattern_Part2 = 
            @"*)"
            + @"|"; 
 
        // quotePrefixCharacter
 
        private const string SqlTokenPattern_Part3 =
            "(?";

        // quotedIdentifierCharacters 

        private const string SqlTokenPattern_Part4 = 
            ")"; 

        // quoteSuffixCharacter 

        private const string SqlTokenPattern_Part5 =
            @"|"
            + @"(?"; 

        // stringPattern 
 
        private const string SqlTokenPattern_Part6 =
            @")" 
            + @"|"
            + @"(?.)"
            + @")"
            + @"[\s;]*"; 

 
        static private Regex    _sqlTokenParser; 
        static private string   _sqlTokenPattern;
 
        static private int      _identifierGroup;
        static private int      _quotedidentifierGroup;
        static private int      _keywordGroup;
        static private int      _stringGroup; 
        static private int      _otherGroup;
 
 
        private string _quotePrefixCharacter;
        private string _quoteSuffixCharacter; 

        private DbSqlParserColumnCollection     _columns;
        private DbSqlParserTableCollection      _tables;
 

        public DbSqlParser(string quotePrefixCharacter, string quoteSuffixCharacter, string regexPattern) { 
            _quotePrefixCharacter = quotePrefixCharacter; 
            _quoteSuffixCharacter = quoteSuffixCharacter;
            _sqlTokenPattern = regexPattern; 
        }

        static internal string CreateRegexPattern(
                        string validIdentifierFirstCharacters, string validIdendifierCharacters, 
                        string quotePrefixCharacter, string quotedIdentifierCharacters,
                        string quoteSuffixCharacter, string stringPattern) { 
            // Combine the regex patterns from the user and the framework that we 
            // will require, and will return the actual regex pattern string to
            // be handed to the class constructor. 
            StringBuilder sb = new StringBuilder();

            sb.Append(SqlTokenPattern_Part1);
            sb.Append(validIdentifierFirstCharacters); 
            sb.Append(validIdendifierCharacters);
            sb.Append(SqlTokenPattern_Part2); 
            sb.Append(quotePrefixCharacter); 
            sb.Append(SqlTokenPattern_Part3);
            sb.Append(quotedIdentifierCharacters); 
            sb.Append(SqlTokenPattern_Part4);
            sb.Append(quoteSuffixCharacter);
            sb.Append(SqlTokenPattern_Part5);
            sb.Append(stringPattern); 
            sb.Append(SqlTokenPattern_Part6);
 
            string result = sb.ToString(); 
            return result;
        } 

        internal DbSqlParserColumnCollection Columns {
            get {
                if (null == _columns) { 
                    _columns = new DbSqlParserColumnCollection();
                } 
                return _columns; 
            }
        } 

        virtual protected string QuotePrefixCharacter {
            get { return _quotePrefixCharacter; }
         } 

        virtual protected string QuoteSuffixCharacter { 
            get { return _quoteSuffixCharacter; } 
        }
 
        static private Regex SqlTokenParser {
            get {
                Regex parser = _sqlTokenParser;
 
                if (null == parser) {
                    parser = GetSqlTokenParser(); 
                } 
                return parser;
            } 
        }

        internal DbSqlParserTableCollection Tables {
            get { 
                if (null == _tables) {
                    _tables = new DbSqlParserTableCollection(); 
                } 
                return _tables;
            } 
        }

        private void AddColumn(int maxPart, Token[] namePart, Token aliasName) {
#if TRACINGPARSER 
            Console.WriteLine(String.Format("\t\t\t\t\t\tADDING COLUMN: \"{0}\".\"{1}\".\"{2}\".\"{3}\" as \"{4}\"",
                        GetPart(0, namePart, maxPart), 
                        GetPart(1, namePart, maxPart), 
                        GetPart(2, namePart, maxPart),
                        GetPart(3, namePart, maxPart), 
                        GetTokenAsString(aliasName)));
#endif // TRACINGPARSER
            Columns.Add(GetPart(0, namePart, maxPart),
                        GetPart(1, namePart, maxPart), 
                        GetPart(2, namePart, maxPart),
                        GetPart(3, namePart, maxPart), 
                        GetTokenAsString(aliasName)); 
        }
 
        private void AddTable(int maxPart, Token[] namePart, Token correlationName) {
#if TRACINGPARSER
            Console.WriteLine(String.Format("\t\t\t\t\t\tADDING TABLE: \"{0}\".\"{1}\".\"{2}\" as \"{3}\"",
                        GetPart(1, namePart, maxPart), 
                        GetPart(2, namePart, maxPart),
                        GetPart(3, namePart, maxPart), 
                        GetTokenAsString(correlationName))); 
#endif // TRACINGPARSER
 
            Tables.Add( GetPart(1, namePart, maxPart),
                        GetPart(2, namePart, maxPart),
                        GetPart(3, namePart, maxPart),
                        GetTokenAsString(correlationName)); 
        }
 
        private void CompleteSchemaInformation() { 
            DbSqlParserColumnCollection columns = Columns;
            DbSqlParserTableCollection tables = Tables; 

            int columnCount = columns.Count;
            int tableCount = tables.Count;
 
            // First, derive all of the information we can about each table
            for (int i = 0; i < tableCount; i++) { 
                DbSqlParserTable table = tables[i]; 
                DbSqlParserColumnCollection tableColumns = GatherTableColumns(table);
                table.Columns = tableColumns; 
            }

            // Next, derive all of the column information we can.
            for (int i = 0; i < columnCount; i++) { 
                DbSqlParserColumn column = columns[i];
                DbSqlParserTable table = FindTableForColumn(column); 
 
                if (!column.IsExpression) {
                    // If this is a '*' column, then we have to expand the '*' into 
                    // its component parts.
                    if ("*" == column.ColumnName) {
                        // Remove the existing "*" column entry and replace it with the
                        // complete list of columns in the table. 
                        columns.RemoveAt(i);
 
                        // If this is a tablename.* column, then add references to the 
                        // all columns in the specified table, otherwise add references
                        // to all columns in all tables. 
                        if (0 != column.TableName.Length) {
                            DbSqlParserColumnCollection tableColumns = table.Columns;
                            int tableColumnCount = tableColumns.Count;
 
                            for (int j =0; j < tableColumnCount; ++j) {
                                columns.Insert(i + j, tableColumns[j]); 
                            } 
                            columnCount += tableColumnCount - 1;    // don't forget to adjust our loop end
                            i += tableColumnCount - 1; 
                        }
                        else {
                            for (int k = 0; k < tableCount; k++) {
                                table = tables[k]; 

                                DbSqlParserColumnCollection tableColumns = table.Columns; 
                                int tableColumnCount = tableColumns.Count; 

                                for (int j =0; j < tableColumnCount; ++j) { 
                                    columns.Insert(i + j, tableColumns[j]);
                                }
                                columnCount += tableColumnCount - 1;    // don't forget to adjust our loop end
                                i += tableColumnCount; 
                            }
                        } 
                    } 
                    else {
                        // if this isn't a '*' column, we find the table that the column belongs 
                        // to, and ask it's column collection for the completed column info (that
                        // contains information about key values, etc.)
                        DbSqlParserColumn completedColumn = FindCompletedColumn(table, column);
 
                        if (null != completedColumn) {// MDAC 87152
                            column.CopySchemaInfoFrom(completedColumn); 
                        } 
                        else {
                            column.CopySchemaInfoFrom(table); 
                        }
                    }
                }
            } 

            // Finally, derive the key column information for each table 
            for (int i = 0; i < tableCount; i++) { 
                DbSqlParserTable table = tables[i];
                GatherKeyColumns(table); 
            }
        }

        protected DbSqlParserColumn FindCompletedColumn(DbSqlParserTable table, DbSqlParserColumn searchColumn) { 
            DbSqlParserColumnCollection columns = table.Columns;
            int columnsCount = columns.Count; 
 
            for (int i = 0; i < columnsCount; ++i) {
                DbSqlParserColumn column = columns[i]; 

                // Compare each part of the name (if they exist) with the
                // table and pick the one that matches all the parts that exist.
                if (CatalogMatch(column.ColumnName, searchColumn.ColumnName)) { 
                    return column;
                } 
            } 

            // MDAC 87152: ROWID and ROWNUM shouldn't fire an assert here: 
            //Debug.Assert(false, "Why didn't we find a match for the search column?");
            return null;
        }
 
        internal DbSqlParserTable FindTableForColumn(DbSqlParserColumn column) {
            DbSqlParserTableCollection tables = Tables; 
            int tableCount = tables.Count; 

            for (int i = 0; i < tableCount; ++i) { 
                DbSqlParserTable table = tables[i];

                // if the table name matches the correlation name, then we're certain
                // of a match 
                if (ADP.IsEmpty(column.DatabaseName) && ADP.IsEmpty(column.SchemaName) && CatalogMatch(column.TableName, table.CorrelationName)) {
                    return table; 
                } 
                // otherwise, compare each part of the name (if they exist) with the
                // table and pick the one that matches all the parts that exist. 
                if ((ADP.IsEmpty(column.DatabaseName) || CatalogMatch(column.DatabaseName, table.DatabaseName))
                 && (ADP.IsEmpty(column.SchemaName)   || CatalogMatch(column.SchemaName,   table.SchemaName))
                  && (ADP.IsEmpty(column.TableName)   || CatalogMatch(column.TableName,    table.TableName))) {
                    return table; 
                }
            } 
 
            Debug.Assert(false, "Why didn't we find a match for the column?");
            return null; 
        }

        private string GetPart(int part, Token[] namePart, int maxPart) {
            int partBase = (maxPart - namePart.Length) + part + 1; 

            if (0 > partBase) { 
                return null; 
            }
            return GetTokenAsString(namePart[partBase]); 
        }

        static private Regex GetSqlTokenParser() {
            Regex parser = _sqlTokenParser; 
            if (null == parser) {
                // due to workingset size issues ~1.5MB, don't use RegexOptions.Compiled 
                parser = new Regex(_sqlTokenPattern, RegexOptions.ExplicitCapture | RegexOptions.IgnoreCase); 
                _identifierGroup = parser.GroupNumberFromName("identifier");
                _quotedidentifierGroup = parser.GroupNumberFromName("quotedidentifier"); 
                _keywordGroup = parser.GroupNumberFromName("keyword");
                _stringGroup = parser.GroupNumberFromName("string");
                _otherGroup = parser.GroupNumberFromName("other");
 
                _sqlTokenParser = parser;
            } 
            return parser; 
        }
 
        private string GetTokenAsString(Token token) {
            if (TokenType.QuotedIdentifier == token.Type) {
                return _quotePrefixCharacter + token.Value + _quoteSuffixCharacter;
            } 
            return token.Value;
        } 
 
        // transistion states used for parsing
        private enum PARSERSTATE { 
            NOTHINGYET=1,   //start point
            SELECT,
            COLUMN,
            COLUMNALIAS, 
            TABLE,
            TABLEALIAS, 
            FROM, 
            EXPRESSION,
            JOIN, 
            JOINCONDITION,
            DONE,
        };
 
        public void Parse(string    statementText) {
            Parse2(statementText); 
            CompleteSchemaInformation(); 
        }
 
        private void Parse2(string  statementText) {
            //          Debug.WriteLine(statementText);

            PARSERSTATE parserState = PARSERSTATE.NOTHINGYET; 

            Token[] namePart = new Token[4]; 
            int currentPart = 0; 

            Token alias = Token.Null; 

            TokenType lastTokenType = TokenType.Null;

            int parenLevel = 0; 
            //          bool            distinctFound;
 
            _columns = null; 
            _tables = null;
 
            Match match = SqlTokenParser.Match(statementText);
            Token token = TokenFromMatch(match);

            while (true) { 
                bool addTheTable = false;
 
#if TRACINGPARSER 
                Console.WriteLine(String.Format("{0,-15} {1,-17} {2} {3}", parserState, token.Type.ToString(), parenLevel, token.Value));
#endif //TRACINGPARSER 

                switch (parserState) {
                    case PARSERSTATE.DONE:
                        return; 

                    case PARSERSTATE.NOTHINGYET: 
                        switch (token.Type) { 
                            case TokenType.Keyword_SELECT:
                                parserState = PARSERSTATE.SELECT; 
                                break;

                            default:
                                Debug.Assert (false, "no state defined!!!!we should never be here!!!"); 
                                throw ADP.InvalidOperation(Res.GetString(Res.ADP_SQLParserInternalError));
                        } 
                        break; 

                    case PARSERSTATE.SELECT: 
                        switch (token.Type) {
                            case TokenType.Identifier:
                            case TokenType.QuotedIdentifier:
                                parserState = PARSERSTATE.COLUMN; 
                                currentPart = 0;
                                namePart[0] = token; 
                                break; 

                            case TokenType.Keyword_FROM: 
                                parserState = PARSERSTATE.FROM;
                                break;

                            case TokenType.Other_Star: 
                                parserState = PARSERSTATE.COLUMNALIAS;
                                currentPart = 0; 
                                namePart[0] = token; 
                                break;
 
                            case TokenType.Keyword_DISTINCT:
                                //                              distinctFound = true;
                                break;
 
                            case TokenType.Keyword_ALL:
                                break; 
 
                            case TokenType.Other_LeftParen:
                                parserState = PARSERSTATE.EXPRESSION; 
                                parenLevel++;
                                break;

                            case TokenType.Other_RightParen: 
                                throw ADP.SyntaxErrorMissingParenthesis();
 
                            default: 
                                parserState = PARSERSTATE.EXPRESSION;
                                break; 
                        }
                        break;

                    case PARSERSTATE.COLUMN: 
                        switch (token.Type) {
                            case TokenType.Identifier: 
                            case TokenType.QuotedIdentifier: 
                                if (TokenType.Other_Period != lastTokenType) {
                                    parserState = PARSERSTATE.COLUMNALIAS; 
                                    alias = token;
                                }
                                else {
                                    namePart[++currentPart] = token; 
                                }
                                break; 
 
                            case TokenType.Other_Period:
                                if (currentPart > 3) { 
                                    throw ADP.SyntaxErrorTooManyNameParts();
                                }
                                break;
 
                            case TokenType.Other_Star:
                                parserState = PARSERSTATE.COLUMNALIAS; 
                                namePart[++currentPart] = token; 
                                break;
 
                            case TokenType.Keyword_AS:
                                break;

                            case TokenType.Keyword_FROM: 
                            case TokenType.Other_Comma:
                                parserState = (token.Type == TokenType.Keyword_FROM) ? PARSERSTATE.FROM : PARSERSTATE.SELECT; 
                                AddColumn(currentPart, namePart, alias); 
                                currentPart = -1;
                                alias = Token.Null; 
                                break;

                            case TokenType.Other_LeftParen:
                                parserState = PARSERSTATE.EXPRESSION; 
                                parenLevel++;
                                currentPart = -1; 
                                break; 

                            case TokenType.Other_RightParen: 
                                throw ADP.SyntaxErrorMissingParenthesis();

                            default:
                                parserState = PARSERSTATE.EXPRESSION; 
                                currentPart = -1;
                                break; 
                        } 
                        break;
 
                    case PARSERSTATE.COLUMNALIAS:
                        switch (token.Type) {
                            case TokenType.Keyword_FROM:
                            case TokenType.Other_Comma: 
                                parserState = (token.Type == TokenType.Keyword_FROM) ? PARSERSTATE.FROM : PARSERSTATE.SELECT;
                                AddColumn(currentPart, namePart, alias); 
                                currentPart = -1; 
                                alias = Token.Null;
                                break; 

                            default:
                                throw ADP.SyntaxErrorExpectedCommaAfterColumn();
                        } 
                        break;
 
                    case PARSERSTATE.EXPRESSION: 
                        switch (token.Type) {
                            case TokenType.Identifier: 
                            case TokenType.QuotedIdentifier:
                                if (0 == parenLevel) {
                                    alias = token;
                                } 
                                break;
 
                            case TokenType.Keyword_FROM: 
                            case TokenType.Other_Comma:
                                if (0 == parenLevel) { 
                                    parserState = (token.Type == TokenType.Keyword_FROM) ? PARSERSTATE.FROM : PARSERSTATE.SELECT;
                                    AddColumn(currentPart, namePart, alias);
                                    currentPart = -1;
                                    alias = Token.Null; 
                                }
                                break; 
 
                            case TokenType.Other_LeftParen:
                                parenLevel++; 
                                break;

                            case TokenType.Other_RightParen:
                                parenLevel--; 
                                break;
                        } 
                        break; 

                    case PARSERSTATE.FROM: 
                        switch (token.Type) {
                            case TokenType.Identifier:
                            case TokenType.QuotedIdentifier:
                                parserState = PARSERSTATE.TABLE; 
                                currentPart = 0;
                                namePart[0] = token; 
                                break; 

                            default: 
                                throw ADP.SyntaxErrorExpectedIdentifier();
                        }
                        break;
 
                    case PARSERSTATE.JOIN:
                        switch (token.Type) { 
                            case TokenType.Keyword_INNER: 
                            case TokenType.Keyword_OUTER:
                                //parserState = PARSERSTATE.JOIN; 
                                break;

                            case TokenType.Keyword_JOIN:
                                parserState = PARSERSTATE.FROM; 
                                break;
 
                            default: 
                                throw ADP.SyntaxErrorExpectedNextPart();        //
                        } 
                        break;

                    case PARSERSTATE.JOINCONDITION:
                        switch (token.Type) { 
                             case TokenType.Other_LeftParen:
                                parenLevel++; 
                                 break; 

                            case TokenType.Other_RightParen: 
                                parenLevel--;
                                break;

                            default: 
                                if (0 == parenLevel) {
                                    switch (token.Type) { 
                                        case TokenType.Keyword_COMPUTE: 
                                        case TokenType.Keyword_FOR:
                                        case TokenType.Keyword_GROUP: 
                                        case TokenType.Keyword_HAVING:
                                        case TokenType.Keyword_INTERSECT:
                                        case TokenType.Keyword_MINUS:
                                        case TokenType.Keyword_ORDER: 
                                        case TokenType.Keyword_UNION:
                                        case TokenType.Keyword_WHERE: 
                                        case TokenType.Null: 
                                            parserState = PARSERSTATE.DONE;
                                            break; 

                                        case TokenType.Keyword_JOIN:
                                            parserState = PARSERSTATE.FROM;
                                            break; 

                                        case TokenType.Keyword_CROSS: 
                                        case TokenType.Keyword_LEFT: 
                                        case TokenType.Keyword_NATURAL:
                                        case TokenType.Keyword_RIGHT: 
                                            parserState = PARSERSTATE.JOIN;
                                            break;
                                        }
                                    } 
                                break;
                        } 
                        break; 

                    case PARSERSTATE.TABLE: 
                        switch (token.Type) {
                            case TokenType.Identifier:
                            case TokenType.QuotedIdentifier:
                                if (TokenType.Other_Period != lastTokenType) { 
                                    parserState = PARSERSTATE.TABLEALIAS;
                                    alias = token; 
                                } 
                                else {
                                    namePart[++currentPart] = token; 
                                }
                                break;

                            case TokenType.Other_Period: 
                                if (currentPart > 2) {
                                    throw ADP.SyntaxErrorTooManyNameParts(); 
                                } 
                                break;
 
                            case TokenType.Keyword_AS:
                                break;

                            case TokenType.Keyword_COMPUTE: 
                            case TokenType.Keyword_FOR:
                            case TokenType.Keyword_GROUP: 
                            case TokenType.Keyword_HAVING: 
                            case TokenType.Keyword_INTERSECT:
                            case TokenType.Keyword_MINUS: 
                            case TokenType.Keyword_ORDER:
                            case TokenType.Keyword_UNION:
                            case TokenType.Keyword_WHERE:
                            case TokenType.Null: 
                                parserState = PARSERSTATE.DONE;
                                addTheTable = true; 
                                break; 

                            case TokenType.Keyword_JOIN: 
                            case TokenType.Other_Comma:
                                parserState = PARSERSTATE.FROM;
                                addTheTable = true;
                                break; 

                            case TokenType.Keyword_CROSS: 
                            case TokenType.Keyword_LEFT: 
                            case TokenType.Keyword_NATURAL:
                            case TokenType.Keyword_RIGHT: 
                                parserState = PARSERSTATE.JOIN;
                                addTheTable = true;
                                break;
 
                            case TokenType.Keyword_ON:
                            case TokenType.Keyword_USING: 
                                parserState = PARSERSTATE.JOINCONDITION; 
                                addTheTable = true;
                                break; 

                            default:
                                throw ADP.SyntaxErrorExpectedNextPart();
                        } 
                        break;
 
                    case PARSERSTATE.TABLEALIAS: 
                        addTheTable = true;
 
                        switch (token.Type) {
                            case TokenType.Keyword_COMPUTE:
                            case TokenType.Keyword_FOR:
                            case TokenType.Keyword_GROUP: 
                            case TokenType.Keyword_HAVING:
                            case TokenType.Keyword_INTERSECT: 
                            case TokenType.Keyword_MINUS: 
                            case TokenType.Keyword_ORDER:
                            case TokenType.Keyword_UNION: 
                            case TokenType.Keyword_WHERE:
                            case TokenType.Null:
                                parserState = PARSERSTATE.DONE;
                                break; 

                            case TokenType.Keyword_JOIN: 
                            case TokenType.Other_Comma: 
                                parserState = PARSERSTATE.FROM;
                                break; 

                            case TokenType.Keyword_ON:
                            case TokenType.Keyword_USING:
                                parserState = PARSERSTATE.JOINCONDITION; 
                                break;
 
                            case TokenType.Keyword_CROSS: 
                            case TokenType.Keyword_LEFT:
                            case TokenType.Keyword_NATURAL: 
                            case TokenType.Keyword_RIGHT:
                                parserState = PARSERSTATE.JOIN;
                                break;
 
                            default:
                                throw ADP.SyntaxErrorExpectedCommaAfterTable(); 
                        } 
                        break;
 

                    default:
                        Debug.Assert (false, "no state defined!!!!we should never be here!!!");
                        throw ADP.InvalidOperation(Res.GetString(Res.ADP_SQLParserInternalError)); 
                }
 
                if (addTheTable) { 
                    AddTable(currentPart, namePart, alias);
                    currentPart = -1; 
                    alias = Token.Null;
                    addTheTable = false;
                }
                lastTokenType = token.Type; 

                match = match.NextMatch(); 
                token = TokenFromMatch(match); 
            }
        } 

        static internal Token TokenFromMatch(Match match) {
            // No matches? we must be at the end of the string!
            if ((null == match) || (Match.Empty == match) || (!match.Success)) { 
                return Token.Null;
            } 
            if (match.Groups[_identifierGroup].Success) { 
                return new Token(TokenType.Identifier,          match.Groups[_identifierGroup].Value);
            } 
            if (match.Groups[_quotedidentifierGroup].Success) {
                return new Token(TokenType.QuotedIdentifier,    match.Groups[_quotedidentifierGroup].Value);
            }
            if (match.Groups[_stringGroup].Success) { 
                return new Token(TokenType.String,              match.Groups[_stringGroup].Value);
            } 
            if (match.Groups[_otherGroup].Success) { 
                string value = match.Groups[_otherGroup].Value.ToLower(CultureInfo.InvariantCulture);
                TokenType key = TokenType.Other; 

                switch (value[0]) {
                    case ',':
                        key = TokenType.Other_Comma; 
                        break;
 
                    case '.': 
                        key = TokenType.Other_Period;
                        break; 

                    case '(':
                        key = TokenType.Other_LeftParen;
                        break; 

                    case ')': 
                        key = TokenType.Other_RightParen; 
                        break;
 
                    case '*':
                        key = TokenType.Other_Star;
                        break;
                } 
                return new Token(key, match.Groups[_otherGroup].Value);
            } 
            if (match.Groups[_keywordGroup].Success) { 
                string value = match.Groups[_keywordGroup].Value.ToLower(CultureInfo.InvariantCulture);
                int length = value.Length; 
                TokenType key = TokenType.Keyword;
                switch (length) {
                case 2:
                    if ("as" == value)      { key = TokenType.Keyword_AS; break; } 
                    if ("on" == value)      { key = TokenType.Keyword_ON; break; }
                    break; 
 
                case 3:
                    if ("for" == value)     { key = TokenType.Keyword_FOR;      break; } 
                    if ("all" == value)     { key = TokenType.Keyword_ALL;      break; }
                    if ("top" == value)     { key = TokenType.Keyword_TOP;      break; }
                    break;
 
                case 4:
                    if ("from" == value)    { key = TokenType.Keyword_FROM;     break; } 
                    if ("into" == value)    { key = TokenType.Keyword_INTO;     break; } 
                    if ("join" == value)    { key = TokenType.Keyword_JOIN;     break; }
                    if ("left" == value)    { key = TokenType.Keyword_LEFT;     break; } 
                    break;

                case 5:
                    if ("where" == value)   { key = TokenType.Keyword_WHERE;    break; } 
                    if ("group" == value)   { key = TokenType.Keyword_GROUP;    break; }
                    if ("order" == value)   { key = TokenType.Keyword_ORDER;    break; } 
                    if ("right" == value)   { key = TokenType.Keyword_RIGHT;    break; } 
                    if ("outer" == value)   { key = TokenType.Keyword_OUTER;    break; }
                    if ("using" == value)   { key = TokenType.Keyword_USING;    break; } 
                    if ("cross" == value)   { key = TokenType.Keyword_CROSS;    break; }
                    if ("union" == value)   { key = TokenType.Keyword_UNION;    break; }
                    if ("minus" == value)   { key = TokenType.Keyword_MINUS;    break; }
                    if ("inner" == value)   { key = TokenType.Keyword_INNER;    break; } 
                    break;
 
                case 6: 
                    if ("select" == value)  { key = TokenType.Keyword_SELECT;   break; }
                    if ("having" == value)  { key = TokenType.Keyword_HAVING;   break; } 
                    break;

                case 7:
                    if ("compute" == value) { key = TokenType.Keyword_COMPUTE;  break; } 
                    if ("natural" == value) { key = TokenType.Keyword_NATURAL;  break; }
                    break; 
 
                case 8:
                    if ("distinct" == value){ key = TokenType.Keyword_DISTINCT; break; } 
                    break;

                case 9:
                    if ("intersect" == value){key = TokenType.Keyword_INTERSECT;break; } 
                    break;
                } 
 
                if (TokenType.Keyword != key) {
                    return new Token(key, value); 
                }
                Debug.Assert(TokenType.Keyword != key, "unrecognized keyword: TokenFromMatch not in [....] with regex pattern");
            }
            else { 
                Debug.Assert(false, "unrecognized pattern: TokenFromMatch not in [....] with regex pattern");
            } 
            return Token.Null; 
        }
 
        //  compares the two values in catalog order, taking into account
        //  quoting rules, and case-sensitivity rules, and returns true if
        //  they match.
        abstract protected bool CatalogMatch(string valueA, string valueB); 

        //  Called after the table and column information is completed to 
        //  identify which columns in the select-list are key columns for 
        //  their table.
        abstract protected void GatherKeyColumns(DbSqlParserTable table); 

        //  Called to get a column list for the table specified.
        abstract protected DbSqlParserColumnCollection GatherTableColumns(DbSqlParserTable table);
 
    }
} 
 

/* 
        [STAThread]
        static void Main(string[] args)
        {
            DbSqlParser parser  = new DbSqlParser( 
                        "[a-zA-Z_#$]",
                        "[a-zA-Z0-9_#$]", 
                        "\"", 
                        "([^\"]|\"\")*",
                        "\"", 
                        "(" + "'" + "([^']|'')*" + "'" + ")"
                        );

            parser.Parse("select 'this is a test' a from dual"); 
            parser.Parse("select (2 + count(empno)) a from scott.emp");
            parser.Parse("select empno + (2 + abs(empno)) \"Test a\" from scott.emp"); 
            parser.Parse("select empno + (2 + abs(empno)) \"Test a\" from scott.emp \"table1\", scott.dept \"table 2\""); 
            parser.Parse("select select scott.emp.ename \"Test a\" from scott.emp");
            parser.Parse("select \"SCOTT\".emp.empno \"Test a\" from scott.emp"); 
            parser.Parse("select \"SCOTT\".\"EMP\".empno \"Test a\" from scott.emp");
            parser.Parse("select \"SCOTT\".\"EMP\".\"EMPNO\" \"Test a B\" from scott.emp");
            parser.Parse("select \"Database\".\"Schema\".\"Table\".* from scott.emp \"Test a B\", scott.dept");
            parser.Parse("select Scott.\"EMP\".\"EMPNO\" \"Test a B\" from scott.emp"); 
            parser.Parse("select Scott.Emp.\"EMPNO\" \"Test a B\" from scott.emp");
            parser.Parse("select aa.b, c.d e, f.g.h i, j.k.l.m n from o p, q.r s, t.u.v w where"); 
            parser.Parse("select 2 + 1 a from scott.emp"); 
            parser.Parse("select 2 + 1 a from dual");
            parser.Parse("select empno + 2 a from scott.emp"); 
            parser.Parse("select empno + 2 from scott.emp");
            parser.Parse("select 2 + empno a from scott.emp");
            select "Foo".*, "Foo".empNO eno from scott.emp "Foo"
        } 
*/

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.

                        

Link Menu

Network programming in C#, Network Programming in VB.NET, Network Programming in .NET
This book is available now!
Buy at Amazon US or
Buy at Amazon UK