Exemplo n.º 1
0
        public static void ParseSQL()
        {
            var sql = @"select AreaId = A.mcw_areaId,  SurrogateKey = A.AreaKey,  Code = S.statecode, Name = S.statename From CRM.dim_Area as A inner join CRM.dim_AreaState as S ON A.statecode = S.statecode  ; DELET  blogs SET url = 'aaa' where url='dasfds'";


            //@"select p.firstname, p.lastname, p.custid FROM persons as p ;
            //SELECT id, name FROM companies;
            //select s.test from (select 'hello' as test) as s; ";

            TSqlParser         parser = new TSql120Parser(true);
            IList <ParseError> parseErrors;
            var          tReader     = new StringReader(sql);
            TSqlFragment sqlFragment = parser.Parse(tReader, out parseErrors);
            var          queryTokens = parser.GetTokenStream(tReader, out parseErrors);

            if (parseErrors.Count > 0)
            {
                Console.WriteLine("Errors:");
            }
            parseErrors.Select(e => e.Message.Indent(2)).ToList().ForEach(Console.WriteLine);

            OwnVisitor visitor = new OwnVisitor();

            sqlFragment.Accept(visitor);
            // sqlFragment.AcceptChildren(visitor);

            Console.WriteLine("Done.");
            Console.ReadKey();
        }
        private static List <TSqlParserToken> TokenizeSql(string sql, out List <string> parserErrors)
        {
            using (TextReader textReader = new StringReader(sql))
            {
                var parser = new TSql120Parser(true);


                IList <ParseError> errors;
                var queryTokens = parser.GetTokenStream(textReader, out errors);
                if (errors.Any())
                {
                    parserErrors = errors.Select(e => $"Error: {e.Number}; Line: {e.Line}; Column: {e.Column}; Offset: {e.Offset};  Message: {e.Message};").ToList();
                }
                else
                {
                    parserErrors = null;
                }
                return(queryTokens.ToList());
            }
        }
Exemplo n.º 3
0
        private List <string> ParseFieldValues(string fieldValueString)
        {
            List <string> result = new List <string>();

            var sqlParser = new TSql120Parser(false);
            IList <ParseError> errors;

            using (var reader = new System.IO.StringReader(fieldValueString)) {
                var queryTokens = sqlParser.GetTokenStream(reader, out errors);

                var tokens = queryTokens.Where(q => q.TokenType == TSqlTokenType.AsciiStringLiteral || q.TokenType == TSqlTokenType.Null || q.TokenType == TSqlTokenType.Identifier || q.TokenType == TSqlTokenType.Integer || q.TokenType == TSqlTokenType.Numeric || q.TokenType == TSqlTokenType.UnicodeStringLiteral);

                foreach (var token in tokens)
                {
                    result.Add(GetFieldText(token.Text));
                }

                return(result);
            }
        }
Exemplo n.º 4
0
        private List <string> ParseColumnNames(string columnNameString)
        {
            List <string> result = new List <string>();

            var sqlParser = new TSql120Parser(false);
            IList <ParseError> errors;

            using (var reader = new System.IO.StringReader(columnNameString)) {
                var queryTokens = sqlParser.GetTokenStream(reader, out errors);

                var tokens = queryTokens.Where(q => q.TokenType == TSqlTokenType.QuotedIdentifier || q.TokenType == TSqlTokenType.AsciiStringOrQuotedIdentifier || q.TokenType == TSqlTokenType.Identifier);

                foreach (var token in tokens)
                {
                    result.Add(GetColumnName(token.Text));
                }

                return(result);
            }
        }
Exemplo n.º 5
0
        private string GetComments(string oldScriptBlock)
        {
            var comments = new StringBuilder();
            var parser   = new TSql120Parser(false);
            IList <ParseError> errors;
            var tokens = parser.GetTokenStream(new StringReader(oldScriptBlock), out errors);

            foreach (var token in tokens)
            {
                switch (token.TokenType)
                {
                case TSqlTokenType.MultilineComment:
                case TSqlTokenType.SingleLineComment:

                    comments.AppendLine(token.Text);
                    break;
                }
            }

            return(comments.ToString());
        }
Exemplo n.º 6
0
        private string GetComments(string oldScriptBlock)
        {

            var comments = new StringBuilder();
            var parser = new TSql120Parser(false);
            IList<ParseError> errors;
            var tokens = parser.GetTokenStream(new StringReader(oldScriptBlock), out errors);
            foreach (var token in tokens)
            {
                switch (token.TokenType)
                {
                    case TSqlTokenType.MultilineComment:
                    case TSqlTokenType.SingleLineComment:

                        comments.AppendLine(token.Text);
                        break;
                }
            }

            return comments.ToString();
        }
Exemplo n.º 7
0
        public SqlParsedInfo BradFunction(string sqlQuery)
        {
            // Remove spaces between strings/words, and remove trailing/ending spaces
            sqlQuery = sqlQuery.Trim();
            Regex regex = new Regex(@"[ ]{2,}"); // Remove spaces greater than two

            sqlQuery = regex.Replace(sqlQuery, @" ");

            var                     sqlParser = new TSql120Parser(true);
            TextReader              tReader   = new StringReader(sqlQuery.Trim());
            IList <ParseError>      sqlParseErrors;
            int                     index = 0;
            IList <TSqlParserToken> tokens;

            // Loop through all the tokens
            tokens = sqlParser.GetTokenStream(tReader, out sqlParseErrors);
            index  = FindColumns(tokens, index + 1);  // Assume we will skip the SELECT statement
            index  = FindTableName(tokens, index);
            index  = FindContraintsAfterWhere(tokens, index);

            return(SqlParsedInfoObject);
        }
Exemplo n.º 8
0
        //Method used to identify table names contained within query string passed in as a parameter
        public static List <outputstruct> GetTableNamesFromQueryString(string query)
        {
            outputstruct outputdata = new outputstruct();
            var          output     = new List <outputstruct>();
            var          sb         = new StringBuilder();
            var          parser     = new TSql120Parser(true);

            var fromTokenTypes = new[]
            {
                TSqlTokenType.From,
                TSqlTokenType.Join
            };

            var identifierTokenTypes = new[]
            {
                TSqlTokenType.Identifier,
                TSqlTokenType.QuotedIdentifier
            };

            using (System.IO.TextReader tReader = new System.IO.StringReader(query))
            {
                IList <ParseError> errors;

                //queryTokens contains separate element for each word in query
                var queryTokens = parser.GetTokenStream(tReader, out errors);


                //Identify start of FROM clause by matching to fromTokenTypes
                for (var i = 0; i < queryTokens.Count; i++)
                {
                    int newclause = 1;
                    if (fromTokenTypes.Contains(queryTokens[i].TokenType))
                    {
                        if (outputdata.DB != null)
                        {
                            output.Add(outputdata);
                            outputdata.DB    = null;
                            outputdata.Table = null;
                            outputdata.Owner = null;
                            outputdata.Alias = null;
                        }

                        //Inner loop to identify specific tables and aliases included in FROM clause
                        //If encountered another "FROM" clause or the start of the "WHERE" or "GROUP" or "ORDER" clause, then have reached end of FROM
                        for (var j = i + 1; j < queryTokens.Count; j++)
                        {
                            if (fromTokenTypes.Contains(queryTokens[j].TokenType))
                            {
                                break;
                            }
                            if (queryTokens[j].Text.ToUpper() == "WHERE" || queryTokens[j].Text.ToUpper() == "GROUP" || queryTokens[j].Text.ToUpper() == "ORDER" || queryTokens[j].Text.ToUpper() == "ON")
                            {
                                break;
                            }

                            if (queryTokens[j].TokenType == TSqlTokenType.WhiteSpace)
                            {
                                continue;
                            }

                            //User supplied value that is not a reserved word such as "JOIN" or "ON".  Indicates table, alias, or column name
                            if (identifierTokenTypes.Contains(queryTokens[j].TokenType))
                            {
                                sb.Clear();
                                GetQuotedIdentifier(queryTokens[j], sb);

                                //query can use format mdyb..tablename, and skip providing the middle parameter is owner.  This can be identified by 2 successive "."
                                if (queryTokens[j + 1].TokenType == TSqlTokenType.Dot && queryTokens[j + 2].TokenType == TSqlTokenType.Dot) //DBName with no owner
                                {
                                    outputdata.DB    = sb.ToString();
                                    outputdata.Owner = "Null";
                                    sb.Clear();
                                    //mydb..tablename, the table name in this case is 3 places out from the db name
                                    GetQuotedIdentifier(queryTokens[j + 3], sb);
                                    outputdata.Table = sb.ToString();
                                    sb.Clear();
                                    outputdata.Alias = "Null";
                                    newclause        = 0;

                                    //move to next argument
                                    j = j + 4;
                                }
                                //DBName.Owner.TableName
                                if (queryTokens[j + 1].TokenType == TSqlTokenType.Dot && queryTokens[j + 3].TokenType == TSqlTokenType.Dot) //DBName with owner
                                {
                                    outputdata.DB = sb.ToString();
                                    sb.Clear();
                                    GetQuotedIdentifier(queryTokens[j + 2], sb);
                                    outputdata.Owner = sb.ToString();
                                    sb.Clear();
                                    GetQuotedIdentifier(queryTokens[j + 4], sb);
                                    outputdata.Table = sb.ToString();
                                    newclause        = 0;
                                    sb.Clear();
                                    outputdata.Alias = "Null";
                                    j = j + 5;
                                }
                                //Owner.TableName
                                if (queryTokens[j + 1].TokenType == TSqlTokenType.Dot && queryTokens[j + 3].TokenType == TSqlTokenType.WhiteSpace) //No DBName with owner
                                {
                                    outputdata.DB    = "Null";
                                    outputdata.Owner = sb.ToString();
                                    sb.Clear();
                                    GetQuotedIdentifier(queryTokens[j + 2], sb);
                                    outputdata.Table = sb.ToString();
                                    newclause        = 0;
                                    sb.Clear();
                                    outputdata.Alias = "Null";
                                    j = j + 3;
                                }

                                //check for tablename without any prefix.  Need to verify if this is an alias from previouly identified table
                                //by looking at "newclause" variable.  This will be set to 1 if new clause or 0 if appending on as alias
                                if (queryTokens[j - 1].TokenType == TSqlTokenType.WhiteSpace && queryTokens[j + 1].TokenType == TSqlTokenType.WhiteSpace)
                                {
                                    if (newclause == 0) //must be alias name since still part of same From Clause item
                                    {
                                        outputdata.Alias = sb.ToString();
                                        sb.Clear();
                                        j         = j + 1;
                                        newclause = 1;
                                    }
                                    else //new item in From clause, must be table name
                                    {
                                        outputdata.DB    = "Null";
                                        outputdata.Owner = "Null";
                                        outputdata.Table = sb.ToString();
                                        outputdata.Alias = "Null";
                                        newclause        = 0;
                                        sb.Clear();
                                        j = j + 1;
                                    }
                                }

                                // break;
                            }
                        }
                    }
                }
                if (outputdata.DB != null)
                {
                    output.Add(outputdata);
                    outputdata.DB    = null;
                    outputdata.Table = null;
                    outputdata.Owner = null;
                    outputdata.Alias = null;
                }
            }
            return(output);
        }
Exemplo n.º 9
0
        /*GetFilterCriteriaFromQueryString method takes input as parameters, query string and the list of tables identified previously in
         * the GetTableNamesFromQueryString method */
        public static List <wherestruct> GetFilterCriteriaFromQueryString(string query, List <outputstruct> tablelist)
        {
            wherestruct outputdata = new wherestruct();
            var         output     = new List <wherestruct>();
            var         sb         = new StringBuilder();
            var         parser     = new TSql120Parser(true);

            //Identifier types used to recognize specific elements in query which are not reserved keywords such as "SELECT", "FROM", "JOIN"
            //If an Identifier type is found in query string, it is part of a user supplied search value or table name
            var identifierTokenTypes = new[]
            {
                TSqlTokenType.Identifier,
                TSqlTokenType.QuotedIdentifier,
                TSqlTokenType.Integer,
                TSqlTokenType.AsciiStringLiteral
            };
            var whereTokenTypes = new[]
            {
                TSqlTokenType.Where,
                TSqlTokenType.And
            };
            var ComparisonOperators = new[]
            {
                TSqlTokenType.In,
                TSqlTokenType.EqualsSign,
                TSqlTokenType.Not,
                TSqlTokenType.GreaterThan,
                TSqlTokenType.LessThan,
                TSqlTokenType.Like
            };

            using (System.IO.TextReader tReader = new System.IO.StringReader(query))
            {
                IList <ParseError> errors;

                /*Each query token represents a distinct word in the query.  Below FOR loop identifies start of where clause
                 * by matchint to "WhereTokenTypes" array and then from there an inner loop is used to identify specific
                 * search arguments in WHERE clause*/

                var queryTokens = parser.GetTokenStream(tReader, out errors);
                for (var i = 0; i < queryTokens.Count; i++)
                {
                    if (whereTokenTypes.Contains(queryTokens[i].TokenType))
                    {
                        if (outputdata.Table != null)
                        {
                            output.Add(outputdata);
                            outputdata.Table  = null;
                            outputdata.Column = null;
                            outputdata.comparison_operator = null;
                            outputdata.comparison_value    = null;
                            outputdata.function_name       = null;
                            outputdata.function_string     = null;
                        }
                        //Identify individual search arguments
                        for (var j = i + 1; j < queryTokens.Count; j++)
                        {
                            if (queryTokens[j].Text == null)
                            {
                                break;
                            }

                            //if function used, need to get whole function call
                            if (queryTokens[j].Text == "(")
                            {
                                int start = j - 1;
                                int end   = j + 1;

                                bool _continue = false;
                                while (queryTokens[start].Text != outputdata.comparison_operator)
                                {
                                    //looking for quoted identifier "" or [] so that if we see a space in the middle, continue on and don't break out
                                    if (queryTokens[start].Text == "\"" || queryTokens[start].Text == "[" || queryTokens[start].Text == "]")
                                    {
                                        if (_continue == false)
                                        {
                                            _continue = true;
                                        }
                                        else
                                        {
                                            _continue = false;
                                        }
                                    }
                                    if (queryTokens[start].Text == " " && _continue == false)
                                    {
                                        break;
                                    }

                                    start += -1;
                                }
                                _continue = false;
                                while (end < queryTokens.Count || !ComparisonOperators.Contains(queryTokens[end].TokenType))
                                {
                                    //looking for quoted identifier "" or [] so that if we see a space in the middle, continue on and don't break out
                                    if (queryTokens[end].Text == "\"" || queryTokens[end].Text == "[" || queryTokens[end].Text == "]")
                                    {
                                        if (_continue == false)
                                        {
                                            _continue = true;
                                        }
                                        else
                                        {
                                            _continue = false;
                                        }
                                    }
                                    if (queryTokens[end].Text == " " && _continue == false)
                                    {
                                        break;
                                    }

                                    end += 1;
                                }
                                string _result = "";
                                for (i = start + 1; i < end; i++)
                                {
                                    _result = _result + queryTokens[i].Text;
                                }
                                outputdata.function_string = _result;
                                if (_result.Contains("."))
                                {
                                    QueryContext context = new QueryContext();
                                    string[]     _temp   = _result.Split('.');
                                    string       alias   = getNestedColumn(_temp[0], "reverse");

                                    var tbl =
                                        from c in context.Tables
                                        join _query in context.Query
                                        on c.queryID equals _query.ID
                                        where _query.QueryText == query &&
                                        (c.AliasName.Replace("[", "").Replace("]", "") == alias ||
                                         c.TableName.Replace("[", "").Replace("]", "") == alias)
                                        select c.TableName;

                                    outputdata.Table  = tbl.First().ToString();
                                    outputdata.Column = getNestedColumn(_temp[1], "forward");
                                }
                                j = end;
                            }

                            //Reached end of WHERE query if reached the GROUP BY or ORDER BY clauses
                            if (queryTokens[j].Text.ToUpper() == "GROUP" || queryTokens[j].Text.ToUpper() == "ORDER" || queryTokens[j].Text.ToUpper() == "AND")
                            {
                                break;
                            }
                            // outputlist.Clear();
                            if (queryTokens[j].TokenType == TSqlTokenType.WhiteSpace)
                            {
                                continue;
                            }

                            //Checking if search argument by matching to Identifier or comparison operator
                            if (identifierTokenTypes.Contains(queryTokens[j].TokenType) || ComparisonOperators.Contains(queryTokens[j].TokenType))
                            {
                                sb.Clear();

                                //Provided Quoted Identifier "[]" for user supplied value such as table name, etc.  But not for comparison operator
                                //Maybe the [] are not needed to pass through query to document db.  If not, we can comment this part out.
                                if (identifierTokenTypes.Contains(queryTokens[j].TokenType) && queryTokens[j].TokenType != TSqlTokenType.Integer && queryTokens[j].TokenType != TSqlTokenType.AsciiStringLiteral)
                                {
                                    GetQuotedIdentifier(queryTokens[j], sb);
                                }
                                //case where alias and column provided in format "alias.column"
                                if (queryTokens[j + 1].TokenType == TSqlTokenType.Dot && identifierTokenTypes.Contains(queryTokens[j + 2].TokenType)) //Alias or Table . ColumnName
                                {
                                    foreach (outputstruct v in tablelist)
                                    {
                                        if (v.Table == sb.ToString())
                                        {
                                            outputdata.Table = v.Table;
                                            break;
                                        }
                                        //If query is using Alias in Where clause, need to sub in Table Name from outputstruct struct
                                        if (v.Alias == sb.ToString())
                                        {
                                            outputdata.Table = v.Table;
                                            break;
                                        }
                                    }
                                    //If above check failed to identify a table name, then need to break out of loop and move to next argument
                                    if (outputdata.Table == null)
                                    {
                                        break;
                                    }

                                    //If format is alias.columnname, then column is 2 positions "J+2" ahead of alias identified above
                                    else
                                    {
                                        outputdata.Column = queryTokens[j + 2].Text;

                                        //Move ahead 3 spots to look for next search argument
                                        j += 3;
                                    }
                                }

                                //Check if argument is a comparison operator (=,<,>,in,like)
                                if (ComparisonOperators.Contains(queryTokens[j].TokenType))
                                {
                                    outputdata.comparison_operator = queryTokens[j].Text;
                                    j += 1;
                                }

                                //If match identifiertokentype, this is user supplied value, so must be value being compared to
                                //logic assumes that a user supplied value with "." is alias.column.  If no "." included, must be a search value
                                if (identifierTokenTypes.Contains(queryTokens[j].TokenType) && outputdata.comparison_operator != null)
                                {
                                    /* if (outputdata.comparison_value != null)
                                     * {
                                     *   outputdata.comparison_value = String.Concat(outputdata.comparison_value, ",", queryTokens[j].Text);
                                     * }
                                     * else
                                     * {
                                     *   outputdata.comparison_value = queryTokens[j].Text;
                                     * }
                                     */
                                    while (queryTokens[j].Text != " " && queryTokens[j].Text != null)
                                    {
                                        outputdata.comparison_value += queryTokens[j].Text;
                                        j += 1;
                                    }
                                }
                            }
                        }
                    }
                }
            }

            //After exiting FOR Loop above, need to insert remaining outputdata struct into the output list
            if (outputdata.Table != null)
            {
                output.Add(outputdata);
                outputdata.Table  = null;
                outputdata.Column = null;
                outputdata.comparison_operator = null;
                outputdata.comparison_value    = null;
                outputdata.function_name       = null;
                outputdata.function_string     = null;
            }
            return(output);
        }
        public static List <string> GetTableNamesFromQueryString(string query)
        {
            var output = new List <string>();
            var sb     = new StringBuilder();
            var parser = new TSql120Parser(true);

            var fromTokenTypes = new[]
            {
                TSqlTokenType.From,
                TSqlTokenType.Join
            };

            var identifierTokenTypes = new[]
            {
                TSqlTokenType.Identifier,
                TSqlTokenType.QuotedIdentifier
            };

            using (System.IO.TextReader tReader = new System.IO.StringReader(query))
            {
                var queryTokens = parser.GetTokenStream(tReader, out IList <ParseError> errors);
                if (errors.Any())
                {
                    return(errors
                           .Select(e => string.Format("Error: {0}; Line: {1}; Column: {2}; Offset: {3};  Message: {4};", e.Number, e.Line, e.Column, e.Offset, e.Message))
                           .ToList());
                }

                for (var i = 0; i < queryTokens.Count; i++)
                {
                    if (fromTokenTypes.Contains(queryTokens[i].TokenType))
                    {
                        for (var j = i + 1; j < queryTokens.Count; j++)
                        {
                            if (queryTokens[j].TokenType == TSqlTokenType.WhiteSpace)
                            {
                                continue;
                            }

                            if (identifierTokenTypes.Contains(queryTokens[j].TokenType))
                            {
                                sb.Clear();
                                GetQuotedIdentifier(queryTokens[j], sb);

                                while (j + 2 < queryTokens.Count &&
                                       queryTokens[j + 1].TokenType == TSqlTokenType.Dot &&
                                       (queryTokens[j + 2].TokenType == TSqlTokenType.Dot || identifierTokenTypes.Contains(queryTokens[j + 2].TokenType)))
                                {
                                    sb.Append(queryTokens[j + 1].Text);

                                    if (queryTokens[j + 2].TokenType == TSqlTokenType.Dot)
                                    {
                                        if (queryTokens[j - 1].TokenType == TSqlTokenType.Dot)
                                        {
                                            GetQuotedIdentifier(queryTokens[j + 1], sb);
                                        }

                                        j++;
                                    }
                                    else
                                    {
                                        GetQuotedIdentifier(queryTokens[j + 2], sb);
                                        j += 2;
                                    }
                                }

                                output.Add(sb.ToString());
                            }
                            break;
                        }
                    }
                }

                return(output.Distinct().OrderBy(tableName => tableName).ToList());
            }
        }