Beispiel #1
0
        /// <summary>
        /// 分割SQL
        /// </summary>
        public static PartedSql SplitSql(string sql)
        {
            var parts = new PartedSql {
                Raw = sql
            };

            // Extract the sql from "SELECT <whatever> FROM"
            var m = _rexSelect.Match(sql);

            if (!m.Success)
            {
                throw new ArgumentException("Unable to parse SQL statement for select");
            }
            parts.Select = m.Groups[1].Value;

            sql = sql.Substring(m.Length);
            m   = _rexOrderBy.Match(sql);
            if (m.Success)
            {
                sql           = sql.Substring(0, m.Index);
                parts.OrderBy = m.Groups[1].Value;
            }
            parts.Body = sql;

            return(parts);
        }
Beispiel #2
0
        public override string PagingBuild(ref PartedSql partedSql, object args, long skip, long take)
        {
            if (string.IsNullOrEmpty(partedSql.OrderBy))
            {
                throw new InvalidOperationException("miss order by");
            }

            var hasDistinct = partedSql.Select.IndexOf("DISTINCT", StringComparison.OrdinalIgnoreCase) == 0;
            var select      = "SELECT";

            if (hasDistinct)
            {
                partedSql.Select = partedSql.Select.Substring("DISTINCT".Length);
                select           = "SELECT DISTINCT";
            }
            if (skip <= 0)
            {
                var sbSql = StringBuilderCache.Allocate().AppendFormat("{0} TOP {1} {2}", select, take, partedSql.Select)
                            .Append(" FROM ").Append(partedSql.Body).Append(" order by ").Append(partedSql.OrderBy);
                return(StringBuilderCache.ReturnAndFree(sbSql));
            }
            else
            {
                var sbSql = StringBuilderCache.Allocate()
                            .AppendFormat("SELECT * FROM (SELECT {0}, ROW_NUMBER() OVER " +
                                          "(order by {1}) As RowNum FROM {2}) AS RowConstrainedResult " +
                                          "WHERE RowNum > {3} AND RowNum <= {4}",
                                          partedSql.Select, partedSql.OrderBy, partedSql.Body, skip, skip + take);

                return(StringBuilderCache.ReturnAndFree(sbSql));
            }
        }
        public override string PagingBuild(ref PartedSql partedSql, object args, long skip, long take)
        {
            if (string.IsNullOrEmpty(partedSql.OrderBy))
            {
                throw new ArgumentException("miss order by");
            }

            return($"{partedSql.Raw} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY");
        }
        public override string PagingBuild(ref PartedSql partedSql, object[] args, long skip, long take)
        {
            // when the query does not contain an "order by", it is very slow
            if (SimpleRegexOrderBy.IsMatch(partedSql.SelectRemovedSql))
            {
                partedSql.SelectRemovedSql = PagingUtil.ReplaceOrderBy(partedSql.SelectRemovedSql, "");
            }
            if (PagingUtil.ContainsDistinct(partedSql.SelectRemovedSql))
            {
                partedSql.SelectRemovedSql = "poco_inner.* FROM (SELECT " + partedSql.SelectRemovedSql + ") poco_inner";
            }
            var sqlPage = $"SELECT * FROM (SELECT ROW_NUMBER() OVER " +
                          $"({partedSql.OrderBySql ?? "ORDER BY (SELECT NULL)"}) poco_rn, " +
                          $"{partedSql.SelectRemovedSql}) peta_paged WHERE " +
                          $"poco_rn > {skip} AND poco_rn <= {skip + take}";

            return(sqlPage);
        }
Beispiel #5
0
        /// <summary>
        ///     Splits the given <paramref name="sql" /> into <paramref name="parts" />;
        /// </summary>
        /// <param name="sql">The SQL to split.</param>
        /// <param name="parts">The SQL parts.</param>
        /// <returns><c>True</c> if the SQL could be split; else, <c>False</c>.</returns>
        public static bool SplitSql(string sql, out PartedSql parts)
        {
            parts = new PartedSql
            {
                SelectRemovedSql = null,
                CountSql         = null,
                OrderBySql       = null
            };

            // Extract the columns from "SELECT <whatever> FROM"
            var m = RegexColumns.Match(sql);

            if (!m.Success)
            {
                return(false);
            }

            // Save column list and replace with COUNT(*)
            var g = m.Groups[1];

            parts.SelectRemovedSql = sql.Substring(g.Index);

            if (RegexDistinct.IsMatch(parts.SelectRemovedSql))
            {
                parts.CountSql = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
            }
            else
            {
                parts.CountSql = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);
            }

            // Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
            m = RegexOrderBy.Match(parts.CountSql);
            if (m.Success)
            {
                g = m.Groups[0];
                parts.OrderBySql = g.ToString();
                parts.CountSql   = parts.CountSql.Substring(0, g.Index) + parts.CountSql.Substring(g.Index + g.Length);
            }

            return(true);
        }
Beispiel #6
0
 public static string GetCountSql(PartedSql sql)
 {
     return($"SELECT COUNT(*) FROM {sql.Body}");
 }
Beispiel #7
0
        public virtual string PagingBuild(ref PartedSql partedSql, object args, long skip, long take)
        {
            var pageSql = $"{partedSql.Raw} LIMIT @{take} OFFSET @{skip}";

            return(pageSql);
        }