/// <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);
}
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);
}
/// <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);
}
public static string GetCountSql(PartedSql sql)
{
return($"SELECT COUNT(*) FROM {sql.Body}");
}
public virtual string PagingBuild(ref PartedSql partedSql, object args, long skip, long take)
{
var pageSql = $"{partedSql.Raw} LIMIT @{take} OFFSET @{skip}";
return(pageSql);
}
