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

using System;
using System.Collections.Generic;
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;
        }
    }
}