JLH/JLHHJSvr/DBA/DAL_SQLite/Utils/SqlStrHelper.cs

using LJLib.Method;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace LJLib.DAL.SQL
{
    internal static class SqlStrHelper
    {
        /// <summary>
        /// 从查询语句中分离出字段部分与表部分
        /// </summary>
        /// <param name="selectstr">查询语句ie:select...from...</param>
        /// <returns>两项分别是字段部分、表部分</returns>
        private static string[] SpliteSelectStr(string selectstr)
        {
            Regex regex = new Regex(@"\bselect\s+(distinct\s+)?(.+?)\s+from\s+(.*)", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            Match match = regex.Match(selectstr);
            string outCmdStr = string.Empty;
            if (match.Success)
            {
                return new[] {match.Groups[2].Value.Trim(), match.Groups[3].Value.Trim(), match.Groups[1].Value.Trim()};
            }
            else
            {
                throw new Exception("selectstr分析失败,ie:select...from...");
            }
        }

        /// <summary>
        /// 分析字段部分
        /// </summary>
        /// <param name="orgFieldStr">字段部分</param>
        /// <param name="orgFields">字段名对字段表达式</param>
        /// <param name="orgOrderFields">字段名对排序表达式</param>
        private static void AnalyseFields(string orgFieldStr, Dictionary<string, string> orgFields, Dictionary<string, string> orgOrderFields)
        {
            string[] selectFieldArr = SplitFields(orgFieldStr);
            string[] pattens = new string[] { @"^\s*(?<sort>(?<key>\w+))\s*=.+$", @"^.+\s+as\s+(?<sort>(?<key>\w+))\s*$", @"^.+\s+(?<sort>(?<key>\w+))\s*$", @"^(?<sort>.*\.\s*(?<key>\w+)\s*)$", @"^\s*(?<sort>(?<key>\w+))\s*$" };

            foreach (string orgField in selectFieldArr)
            {
                var find = false;
                foreach (string patten in pattens)
                {
                    Match fiMatch = Regex.Match(orgField, patten, RegexOptions.IgnoreCase | RegexOptions.Singleline);
                    if (fiMatch.Success)
                    {
                        find = true;
                        string key = fiMatch.Groups["key"].Value.Trim().ToLower();
                        if (orgFields.ContainsKey(key))
                        {
                            throw new Exception(string.Format("查询字段名重复:[{0}] = [{1}]", orgFields[key], orgField));
                        }
                        orgFields[key] = fiMatch.Value.Trim();
                        orgOrderFields[key] = fiMatch.Groups["sort"].Value.Trim();
                        break;
                    }
                }
                if (!find)
                {
                    throw  new Exception(string.Format("字段表达式分析失败:{0}", orgField));
                }
            }
        }

        /// <summary>
        /// 将字段部分拆分
        /// </summary>
        /// <param name="orgFieldStr">字段部分</param>
        /// <returns>各字段</returns>
        private static string[] SplitFields(string orgFieldStr)
        {
            List<string> rslt = new List<string>();

            string builder = string.Empty;
            Stack<char> states = new Stack<char>();
            for (int i = 0; i < orgFieldStr.Length; i++)
            {
                char cur = orgFieldStr[i];
                if (states.Count == 0)
                {
                    if (cur == ',')
                    {
                        rslt.Add(builder);
                        builder = string.Empty;
                    }
                    else
                    {
                        builder += cur;
                        if (cur == '(' || cur == '\'')
                        {
                            states.Push(cur);
                        }
                    }
                }
                else
                {
                    builder += cur;
                    char curstate = states.Peek();
                    if (curstate == '\'')
                    {
                        if (cur == '\'')
                        {
                            states.Pop();
                        }
                    }
                    else
                    {
                        if (cur == '(' || cur == '\'')
                        {
                            states.Push(cur);
                        }
                        else if (cur == ')')
                        {
                            states.Pop();
                        }
                    }
                }
            }
            rslt.Add(builder.Trim());

            return rslt.ToArray();
        }

        /// <summary>
        /// 生成新的字段语句
        /// </summary>
        /// <param name="orgFields">字段名-字段表达式</param>
        /// <param name="preOutFieldStr">输出字段名ie:mtrlid,mtrlname,如果与原始字段完全不匹配就输出全部字段,最终会变成与输出字段一至</param>
        /// <returns>新的字段语句</returns>
        private static string BuildSelectFields(Dictionary<string, string> orgFields, ref string preOutFieldStr, string orderFieldStr = null)
        {
            string outFieldStr = string.Empty;
            Dictionary<string, string> outFields = new Dictionary<string, string>();
            if (!string.IsNullOrEmpty(preOutFieldStr))
            {
                string[] fields = preOutFieldStr.Split(',');
                foreach (string field in fields)
                {
                    string key = field.Trim().ToLower();
                    if (orgFields.ContainsKey(key) && !outFields.ContainsKey(key))
                    {
                        outFields[key] = orgFields[key];
                    }
                }
            }
            if (!string.IsNullOrEmpty(orderFieldStr))
            {
                string[] fields = orderFieldStr.Split(',');
                foreach (string field in fields)
                {
                    string key = field.Trim().ToLower();
                    if (orgFields.ContainsKey(key) && !outFields.ContainsKey(key))
                    {
                        outFields[key] = orgFields[key];
                    }
                }
            }
            preOutFieldStr = string.Empty;
            if (outFields.Count == 0)
            {
                outFields = orgFields;
            }
            foreach (KeyValuePair<string, string> kvp in outFields)
            {
                if (string.IsNullOrEmpty(outFieldStr))
                {
                    outFieldStr = kvp.Value;
                }
                else
                {
                    outFieldStr += "," + kvp.Value;
                }
                if (string.IsNullOrEmpty(preOutFieldStr))
                {
                    preOutFieldStr = kvp.Key;
                }
                else
                {
                    preOutFieldStr += "," + kvp.Key;
                }
            }
            return outFieldStr;
        }

        /// <summary>
        /// 生成新的排序语名
        /// </summary>
        /// <param name="orgOrderFields">字段名-字段表达式</param>
        /// <param name="preOrderStr">输出字段名ie:mtrlid asc,mtrlname desc</param>
        /// <returns>可为空</returns>
        private static string BuildOrderFields(Dictionary<string, string> orgOrderFields, string preOrderStr)
        {
            string outOrderStr = string.Empty;
            Dictionary<string, string> outOrderFields = new Dictionary<string, string>();
            if (!string.IsNullOrEmpty(preOrderStr))
            {
                string[] fields = preOrderStr.Split(',');
                foreach (string field in fields)
                {
                    string key = field.Trim().ToLower();
                    string[] arr = key.Split(' ');
                    if (arr.Length > 1)
                    {
                        key = arr[0];
                        string sort = arr[arr.Length - 1];
                        if (orgOrderFields.ContainsKey(key) && !outOrderFields.ContainsKey(key) && (sort == "asc" || sort == "desc"))
                        {
                            outOrderFields[key] = orgOrderFields[key] + " " + sort;
                        }
                    }
                    else
                    {
                        if (orgOrderFields.ContainsKey(key) && !outOrderFields.ContainsKey(key))
                        {
                            outOrderFields[key] = orgOrderFields[key];
                        }
                    }
                }
            }
            foreach (KeyValuePair<string, string> kvp in outOrderFields)
            {
                if (string.IsNullOrEmpty(outOrderStr))
                {
                    outOrderStr = kvp.Value;
                }
                else
                {
                    outOrderStr += "," + kvp.Value;
                }
            }
            return outOrderStr;
        }

        /// <summary>
        /// 生成新的查询语句
        /// </summary>
        /// <param name="orgSelectStr">原始查询语名，select...from...</param>
        /// <param name="preOutFieldStr">输出字段</param>
        /// <param name="orgWhereStr">条件语句</param>
        /// <param name="preOrderStr">排序语句</param>
        /// <param name="pageindex">页数</param>
        /// <param name="pagesize">每页项数</param>
        /// <returns>新的查询语句</returns>
        public static string BuildSelectStr(string orgSelectStr, ref string preOutFieldStr, string orgWhereStr,
    string preOrderStr, int pageindex, int pagesize)
        {
            var parts = SpliteSelectStr(orgSelectStr);
            var orgFieldStr = parts[0];
            var orgFromStr = parts[1];
            var distinctStr = parts[2];

            Dictionary<string, string> orgFields = new Dictionary<string, string>();
            Dictionary<string, string> orgOrderFields = new Dictionary<string, string>();
            AnalyseFields(orgFieldStr, orgFields, orgOrderFields);

            string outFieldStr = BuildSelectFields(orgFields, ref preOutFieldStr, preOrderStr);
            string outOrderStr = BuildOrderFields(orgOrderFields, preOrderStr);

            var outCmdStr = outFieldStr + " FROM " + orgFromStr;
            if (!string.IsNullOrEmpty(orgWhereStr))
            {
                outCmdStr += " WHERE " + orgWhereStr;
            }
            if (!string.IsNullOrEmpty(outOrderStr))
            {
                outCmdStr += " ORDER BY " + outOrderStr;
            }

            if (pageindex > 0 && pagesize > 0)
            {
                outCmdStr = "SELECT " + distinctStr + " TOP " + (pageindex * pagesize).ToString() + " " + outCmdStr;
                outCmdStr = "SELECT RowNumber = IDENTITY(INT,1, 1)," + preOutFieldStr + " INTO #tmp_sorttable FROM (" +
                            outCmdStr + @") a
SELECT TOP " + pagesize.ToString() + " " + preOutFieldStr + @" FROM #tmp_sorttable WHERE RowNumber > " +
                            (pagesize * (pageindex - 1)).ToString() + @" ORDER BY RowNumber
DROP TABLE #tmp_sorttable";
            }
            else if (pageindex <= 0 && pagesize > 0)
            {
                outCmdStr = "SELECT " + distinctStr + " TOP " + pagesize + " " + outCmdStr;
            }
            else
            {
                outCmdStr = "SELECT " + distinctStr + " " + outCmdStr;
            }
            return outCmdStr;
        }

        public static string BuildSelectStrL1(ParseResult sqlInfo, string preOutFieldStr, string orgWhereStr,
    string preOrderStr, int pageindex, int pagesize)
        {

            var fromStr = sqlInfo.fromStr;

            var outCmdStr = string.Format("{0} {1}", string.Join(",", sqlInfo.selectFieldsDic.Values), fromStr);
            var whereStr = sqlInfo.whereStr;
            if (!string.IsNullOrEmpty(orgWhereStr))
            {
                if (!string.IsNullOrEmpty(whereStr))
                {
                    var rawWhereitem = whereStr.Substring(whereStr.IndexOf("WHERE", StringComparison.OrdinalIgnoreCase) + 6);
                    whereStr = "WHERE (" + rawWhereitem + ") AND ";
                }
                else
                {
                    whereStr = "WHERE ";
                }
                whereStr += orgWhereStr;
            }
            outCmdStr += whereStr;
            outCmdStr += " " + sqlInfo.groupStr;
            outCmdStr += " " + sqlInfo.orderStr;
            if (!string.IsNullOrEmpty(preOrderStr))
            {
                outCmdStr += " ORDER BY " + preOrderStr;
            }
            if (pageindex > 0 && pagesize > 0)
            {
                outCmdStr = sqlInfo.selectStr + " TOP " + (pageindex * pagesize).ToString() + " " + outCmdStr;
                outCmdStr = "SELECT RowNumber = IDENTITY(INT,1, 1)," + preOutFieldStr + " INTO #tmp_sorttable FROM (" +
                            outCmdStr + @") a
SELECT TOP " + pagesize.ToString() + " " + preOutFieldStr + @" FROM #tmp_sorttable WHERE RowNumber > " +
                            (pagesize * (pageindex - 1)).ToString() + @" ORDER BY RowNumber
DROP TABLE #tmp_sorttable";
            }
            else if (pageindex <= 0 && pagesize > 0)
            {
                outCmdStr = sqlInfo.selectStr + " TOP " + pagesize + " " + outCmdStr;
            }
            else
            {
                outCmdStr = sqlInfo.selectStr + " " + outCmdStr;
            }
            return outCmdStr;
        }
        public static string RemoveComment(string source)
        {
            var sb = new StringBuilder();
            using (StringReader reader = new StringReader(source))
            {
                string line;
                while ((line = reader.ReadLine()) != null)
                {
                    if (line.Contains("--"))
                    {
                        int commentstart = -1;
                        var instr = false;
                        for (var i = 0; i < line.Length; i++)
                        {
                            var curchar = line[i];
                            if (curchar == '\'')
                            {
                                instr = !instr;
                            }
                            else if (!instr)
                            {
                                if (curchar == '-')
                                {
                                    if (i + 1 < line.Length)
                                    {
                                        if (line[i + 1] == '-')
                                        {
                                            commentstart = i;
                                            break;
                                        }
                                    }
                                }
                            }
                        }
                        if (commentstart >= 0)
                        {
                            line = line.Substring(0, commentstart);
                        }
                    }
                    sb.AppendLine(line);
                }
            }
            return sb.ToString().TrimEnd('\n').TrimEnd('\r');
        }
        public static ParseResult ParseSelectStr(string orgSelectStr)
        {
            var rslt = new ParseResult();
            orgSelectStr = RemoveComment(orgSelectStr);
            var indexchars = GetIndexChar(orgSelectStr);

            var regex = new Regex(@"\bselect\s+(distinct\s+)?(top\b)?", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            #region 查找select关键字

            var selectMatch = regex.Match(orgSelectStr);

            if (!selectMatch.Success)
            {
                throw new Exception(string.Format("查找select关键字失败,表达式[{0}],请检查:{1}", regex, orgSelectStr));
            }

            var stateIndex = indexchars.Keys.GetFirstIndexBefore(selectMatch.Index);
            if (stateIndex >= 0)
            {
                throw new Exception(string.Format("第{0}个字符的select关键字前面不允许存在括号或字符串,表达式[{1}],请检查:{2}", selectMatch.Index, regex,
                    orgSelectStr));
            }

            if (!string.IsNullOrEmpty(selectMatch.Groups[2].Value))
            {
                throw new Exception(string.Format("第{0}位,不应该包含TOP关键字,请使用分页参数,请检查:{1}", selectMatch.Groups[2].Index,
                    orgSelectStr));
            }

            rslt.selectStr = orgSelectStr.Substring(0, selectMatch.Index + selectMatch.Length);

            #endregion

            regex = new Regex(@"\bfrom\b", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            #region 查找from关键字

            var match = regex.Match(orgSelectStr);
            int fromIndex = -1;
            while (match.Success)
            {
                stateIndex = indexchars.Keys.GetFirstIndexBefore(match.Index);
                if (stateIndex < 0 || indexchars.Values[stateIndex] == ' ')
                {
                    fromIndex = match.Index;
                    break;
                }
                match = match.NextMatch();
            }

            if (fromIndex < 0)
            {
                throw new Exception(string.Format("最外层查找from字句失败,请检查:{0}", orgSelectStr));
            }

            #endregion

            #region 分析返回字段
            Dictionary<string, string> orgFields = new Dictionary<string, string>();
            Dictionary<string, string> orgOrderFields = new Dictionary<string, string>();
            var parts = SpliteSelectStr(orgSelectStr);
            var orgFieldStr = parts[0];
            AnalyseFields(orgFieldStr, orgFields, orgOrderFields);
            rslt.selectFieldsDic = orgFields;
            #endregion

            regex = new Regex(@"\where\b", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            #region 查找where关键字

            int whereIndex = -1;
            match = regex.Match(orgSelectStr, fromIndex);
            while (match.Success)
            {
                stateIndex = indexchars.Keys.GetFirstIndexBefore(match.Index);
                if (stateIndex < 0 || indexchars.Values[stateIndex] == ' ')
                {
                    whereIndex = match.Index;
                    break;
                }
                match = match.NextMatch();
            }

            #endregion


            regex = new Regex(@"\bgroup\b", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            #region 查找group关键字

            int groupIndex = -1;
            match = regex.Match(orgSelectStr, fromIndex);
            while (match.Success)
            {
                stateIndex = indexchars.Keys.GetFirstIndexBefore(match.Index);
                if (stateIndex < 0 || indexchars.Values[stateIndex] == ' ')
                {
                    groupIndex = match.Index;
                    break;
                }
                match = match.NextMatch();
            }

            #endregion

            regex = new Regex(@"\border\b", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            #region 查找order关键字

            int orderindex = -1;
            match = regex.Match(orgSelectStr, fromIndex);
            while (match.Success)
            {
                stateIndex = indexchars.Keys.GetFirstIndexBefore(match.Index);
                if (stateIndex < 0 || indexchars.Values[stateIndex] == ' ')
                {
                    orderindex = match.Index;
                    break;
                }
                match = match.NextMatch();
            }


            #endregion

            rslt.fromStr = string.Empty;
            var fromStopIndexs = new List<int> { whereIndex, groupIndex, orderindex }.Where(x => x > 0);
            if (fromStopIndexs.Any())
            {
                rslt.fromStr = orgSelectStr.Substring(fromIndex, fromStopIndexs.Min() - fromIndex);
            }
            else
            {
                rslt.fromStr = orgSelectStr.Substring(fromIndex);
            }
            rslt.whereStr = string.Empty;
            if (whereIndex > 0)
            {
                var whereStopIndexs = new List<int> { groupIndex, orderindex }.Where(x => x > 0);
                if (whereStopIndexs.Any())
                {
                    rslt.whereStr = orgSelectStr.Substring(whereIndex, whereStopIndexs.Min() - whereIndex);
                }
                else
                {
                    rslt.whereStr = orgSelectStr.Substring(whereIndex);
                }
            }

            rslt.orderStr = string.Empty;
            if (orderindex > 0)
            {
                rslt.orderStr = orgSelectStr.Substring(orderindex);
            }
            rslt.groupStr = string.Empty;
            if (groupIndex > 0)
            {
                if (orderindex > 0)
                {
                    rslt.groupStr = orgSelectStr.Substring(groupIndex, orderindex - groupIndex);
                }
                else
                {
                    rslt.groupStr = orgSelectStr.Substring(groupIndex);
                }
            }
            return rslt;
        }

        /// <summary>
        /// 获取SQL中括号与字符串的转换位置
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public static SortedList<int, char> GetIndexChar(string sqlStr)
        {
            var rslt = new SortedList<int, char>();

            Stack<char> states = new Stack<char>();
            char curstate = ' ';
            for (int i = 0; i < sqlStr.Length; i++)
            {
                char cur = sqlStr[i];

                if (curstate == '\'')
                {
                    if (cur == '\'')
                    {
                        states.Pop();
                        curstate = states.Count > 0 ? states.Peek() : ' ';
                        rslt[i] = curstate;
                    }
                    continue;
                }

                if (curstate == '(')
                {
                    if (cur == ')')
                    {
                        states.Pop();
                        curstate = states.Count > 0 ? states.Peek() : ' ';
                        rslt[i] = curstate;
                        continue;
                    }
                }

                if (curstate == '[')
                {
                    if (cur == ']')
                    {
                        states.Pop();
                        curstate = states.Count > 0 ? states.Peek() : ' ';
                        rslt[i] = curstate;
                        continue;
                    }
                }

                if (cur == '(' || cur == '\'' || cur == '[')
                {
                    states.Push(cur);
                    curstate = cur;
                    rslt[i] = curstate;
                    continue;
                }

                if (cur == ')')
                {
                    throw new Exception(string.Format("第{0}位\")\"没有找到对应的\"(\",请检查:{1}", i, sqlStr));
                }

                if (cur == ']')
                {
                    throw new Exception(string.Format("第{0}位\"]\"没有找到对应的\"[\",请检查:{1}", i, sqlStr));
                }
            }
            if (states.Count > 0)
            {
                throw new Exception(string.Format("第{0}位\"{1}\"没有找到对应的结束符,请检查:{2}", rslt.Keys[rslt.Count - 1], rslt.Values[rslt.Count - 1], sqlStr));
            }
            return rslt;
        }

        public class ParseResult
        {
            public string selectStr { get; set; }
            //public List<string> selectFields { get; set; }
            //public List<string> selectTitles { get; set; }
            public Dictionary<string, string> selectFieldsDic { get; set; }
            public string fromStr { get; set; }
            public string whereStr { get; set; }
            public string groupStr { get; set; }
            public string orderStr { get; set; }
        }
    }
}