/// <summary>分页算法</summary>
/// <remarks>
/// builder里面必须含有排序，否则就要通过key指定主键，否则大部分算法不能使用，会导致逻辑数据排序不正确。
/// 其实，一般数据都是按照聚集索引排序，而聚集索引刚好也就是主键。
/// 所以，只要设置的Key顺序跟主键顺序一致，就没有问题。
/// 如果，Key指定了跟主键不一致的顺序，那么查询语句一定要指定同样的排序。
/// </remarks>
/// <param name="builder"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <param name="isSql2005"></param>
/// <param name="queryCountCallback">查询总记录数的委托，近供DoubleTop使用</param>
/// <returns></returns>
public static SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows, Boolean isSql2005, Func<SelectBuilder, Int64> queryCountCallback = null)
{
// 从第一行开始，不需要分页
if (startRowIndex <= 0)
{
if (maximumRows < 1)
return builder;
else
return builder.Clone().Top(maximumRows);
}
if (builder.Keys == null || builder.Keys.Length < 1) throw new XCodeException("分页算法要求指定排序列！" + builder.ToString());
// 其实，一般数据都是按照聚集索引排序，而聚集索引刚好也就是主键
// 所以，只要设置的Key顺序跟主键顺序一致，就没有问题
// 如果，Key指定了跟主键不一致的顺序，那么查询语句一定要指定同样的排序
//// 如果不指定排序，只能使用TopNotIn，另外三种都会影响结果的顺序
//if (String.IsNullOrEmpty(builder.OrderBy))
//{
// // 取前面页（经试验Access字符串主键大概在200行以下）并且没有排序的时候，TopNotIn算法应该是最快的
// if (startRowIndex < 200) return TopNotIn(builder, startRowIndex, maximumRows);
//}
if (isSql2005) return RowNumber(builder, startRowIndex, maximumRows);
// 必须有排序，且排序字段必须就是数字主键
if (builder.IsInt && builder.KeyIsOrderBy) return MaxMin(builder, startRowIndex, maximumRows);
if (maximumRows > 0) return DoubleTop(builder, startRowIndex, maximumRows, queryCountCallback);
return TopNotIn(builder, startRowIndex, maximumRows);
}
/// <summary>最经典的NotIn分页，通用但是效率最差。只需指定一个排序列。</summary>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
static SelectBuilder TopNotIn(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
if (builder.Keys == null || builder.Keys.Length != 1) throw new ArgumentNullException("Key", "TopNotIn分页算法要求指定单一主键列！" + builder.ToString());
// 分页标准 Select (20,10,ID)
// 1，取目标页之前的20行
// 2，排除前20行之后取10行
// Select Top 10 * From Table Where ID Not In(Select Top 20 ID From Table)
// 构建Select Top 20 ID From Table
var builder1 = builder.Clone().Top(startRowIndex, builder.Key);
SelectBuilder builder2 = null;
if (maximumRows < 1)
builder2 = builder.CloneWithGroupBy("XCode_T0");
else
builder2 = builder.Clone().Top(maximumRows);
builder2.AppendWhereAnd("{0} Not In({1})", builder.Key, builder1);
// 结果列处理
builder2.Column = builder.Column;
// 如果结果列包含有“.”，即有形如tab1.id、tab2.name之类的列时设为获取子查询的全部列
if ((!string.IsNullOrEmpty(builder2.Column)) && builder2.Column.Contains("."))
{
builder2.Column = "*";
}
return builder2;
}
/// <summary>分页算法</summary>
/// <remarks>
/// builder里面必须含有排序，否则就要通过key指定主键，否则大部分算法不能使用，会导致逻辑数据排序不正确。
/// 其实，一般数据都是按照聚集索引排序，而聚集索引刚好也就是主键。
/// 所以，只要设置的Key顺序跟主键顺序一致，就没有问题。
/// 如果，Key指定了跟主键不一致的顺序，那么查询语句一定要指定同样的排序。
/// </remarks>
/// <param name="builder"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <param name="isSql2005"></param>
/// <param name="queryCountCallback">查询总记录数的委托，近供DoubleTop使用</param>
/// <returns></returns>
public static SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows, Boolean isSql2005, Func<SelectBuilder, Int64> queryCountCallback = null)
{
// 从第一行开始，不需要分页
// 2012.11.08 注释掉首页使用SELECT TOP的方式，此方式在对有重复数据出现的字段排序时，
// 与Row_Number()的规则不一致，导致出现第一、二页排序出现重复记录。
// 具体可百度一下【结合TOP N和Row_Number()分页因Order by排序规则不同引起的bug】
if (startRowIndex <= 0)
{
if (maximumRows < 1)
{
return builder;
}
else if (builder.KeyIsOrderBy)
{
return builder.Clone().Top(maximumRows);
}
}
if (builder.Keys == null || builder.Keys.Length < 1) throw new XCodeException("分页算法要求指定排序列！" + builder.ToString());
// 其实，一般数据都是按照聚集索引排序，而聚集索引刚好也就是主键
// 所以，只要设置的Key顺序跟主键顺序一致，就没有问题
// 如果，Key指定了跟主键不一致的顺序，那么查询语句一定要指定同样的排序
//// 如果不指定排序，只能使用TopNotIn，另外三种都会影响结果的顺序
//if (String.IsNullOrEmpty(builder.OrderBy))
//{
// // 取前面页（经试验Access字符串主键大概在200行以下）并且没有排序的时候，TopNotIn算法应该是最快的
// if (startRowIndex < 200) return TopNotIn(builder, startRowIndex, maximumRows);
//}
if (isSql2005) return RowNumber(builder, startRowIndex, maximumRows);
// 必须有排序，且排序字段必须就是数字主键
if (builder.IsInt && builder.KeyIsOrderBy) return MaxMin(builder, startRowIndex, maximumRows);
if (maximumRows > 0) return DoubleTop(builder, startRowIndex, maximumRows, queryCountCallback);
return TopNotIn(builder, startRowIndex, maximumRows);
}
/// <summary>最经典的NotIn分页，通用但是效率最差。只需指定一个排序列。</summary>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
static SelectBuilder TopNotIn(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
if (builder.Keys == null || builder.Keys.Length != 1) throw new ArgumentNullException("Key", "TopNotIn分页算法要求指定单一主键列！" + builder.ToString());
// 分页标准 Select (20,10,ID)
// 1，取目标页之前的20行
// 2，排除前20行之后取10行
// Select Top 10 * From Table Where ID Not In(Select Top 20 ID From Table)
// 构建Select Top 20 ID From Table
SelectBuilder builder1 = builder.Clone().Top(startRowIndex, builder.Key);
SelectBuilder builder2 = null;
if (maximumRows < 1)
builder2 = builder.CloneWithGroupBy("XCode_T0");
else
builder2 = builder.Clone().Top(maximumRows);
builder2.AppendWhereAnd("{0} Not In({1})", builder.Key, builder1.ToString());
return builder2;
}
/// <summary>克隆</summary>
/// <returns></returns>
public SelectBuilder Clone()
{
var sb = new SelectBuilder();
sb.Column = this.Column;
sb.Table = this.Table;
// 直接拷贝字段，避免属性set时触发分析代码
sb._Where = this._Where;
sb._OrderBy = this._OrderBy;
sb.GroupBy = this.GroupBy;
sb.Having = this.Having;
sb.Keys = this.Keys;
sb.IsDescs = this.IsDescs;
sb.IsInt = this.IsInt;
return sb;
}
/// <summary>构造分页SQL</summary> /// <param name="builder">查询生成器</param> /// <param name="startRowIndex">开始行，0表示第一行</param> /// <param name="maximumRows">最大返回行数，0表示所有行</param> /// <returns>分页SQL</returns> public override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows) => MySql.PageSplitByLimit(builder, startRowIndex, maximumRows);
public override SelectBuilder PageSplit(SelectBuilder builder, int startRowIndex, int maximumRows)
{
return MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, false, b => CreateSession().QueryCount(b));
}
/// <summary>按唯一数字最大最小分页，性能很好。必须指定一个数字型排序列。</summary>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
static SelectBuilder MaxMin(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
if (builder.Keys == null || builder.Keys.Length != 1) throw new ArgumentNullException("Key", "TopNotIn分页算法要求指定单一主键列！" + builder.ToString());
// 分页标准 Select (20,10,ID Desc)
// Select Top 10 * From Table Where ID>(Select max(ID) From (Select Top 20 ID From Table Order By ID) Order By ID Desc) Order By ID Desc
var builder1 = builder.Clone().Top(startRowIndex, builder.Key);
var builder2 = builder1.AsChild("XCode_T0");
builder2.Column = String.Format("{0}({1})", builder.IsDesc ? "Min" : "Max", builder.Key);
SelectBuilder builder3 = null;
if (maximumRows < 1)
builder3 = builder.CloneWithGroupBy("XCode_T1");
else
builder3 = builder.Clone().Top(maximumRows);
// 如果本来有Where字句，加上And，当然，要区分情况按是否有必要加圆括号
builder3.AppendWhereAnd("{0}{1}({2})", builder.Key, builder.IsDesc ? "<" : ">", builder2);
return builder3;
}
/// <summary>构造分页SQL</summary>
/// <param name="sql">SQL语句</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <param name="keyColumn">唯一键。用于not in分页</param>
/// <returns>分页SQL</returns>
public override String PageSplit(String sql, Int32 startRowIndex, Int32 maximumRows, String keyColumn)
{
// 从第一行开始，不需要分页
if (startRowIndex <= 0 && maximumRows < 1) return sql;
// 指定了起始行，并且是SQL2005及以上版本，使用RowNumber算法
if (startRowIndex > 0 && IsSQL2005)
{
//return PageSplitRowNumber(sql, startRowIndex, maximumRows, keyColumn);
SelectBuilder builder = new SelectBuilder();
builder.Parse(sql);
return MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, IsSQL2005).ToString();
}
// 如果没有Order By，直接调用基类方法
// 先用字符串判断，命中率高，这样可以提高处理效率
if (!sql.Contains(" Order "))
{
if (!sql.ToLower().Contains(" order ")) return base.PageSplit(sql, startRowIndex, maximumRows, keyColumn);
}
//// 使用正则进行严格判断。必须包含Order By，并且它右边没有右括号)，表明有order by，且不是子查询的，才需要特殊处理
//MatchCollection ms = Regex.Matches(sql, @"\border\s*by\b([^)]+)$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
//if (ms == null || ms.Count < 1 || ms[0].Index < 1)
String sql2 = sql;
String orderBy = CheckOrderClause(ref sql2);
if (String.IsNullOrEmpty(orderBy))
{
return base.PageSplit(sql, startRowIndex, maximumRows, keyColumn);
}
// 已确定该sql最外层含有order by，再检查最外层是否有top。因为没有top的order by是不允许作为子查询的
if (Regex.IsMatch(sql, @"^[^(]+\btop\b", RegexOptions.Compiled | RegexOptions.IgnoreCase))
{
return base.PageSplit(sql, startRowIndex, maximumRows, keyColumn);
}
//String orderBy = sql.Substring(ms[0].Index);
// 从第一行开始，不需要分页
if (startRowIndex <= 0)
{
if (maximumRows < 1)
return sql;
else
return String.Format("Select Top {0} * From {1} {2}", maximumRows, CheckSimpleSQL(sql2), orderBy);
//return String.Format("Select Top {0} * From {1} {2}", maximumRows, CheckSimpleSQL(sql.Substring(0, ms[0].Index)), orderBy);
}
#region Max/Min分页
// 如果要使用max/min分页法，首先keyColumn必须有asc或者desc
String kc = keyColumn.ToLower();
if (kc.EndsWith(" desc") || kc.EndsWith(" asc") || kc.EndsWith(" unknown"))
{
String str = PageSplitMaxMin(sql, startRowIndex, maximumRows, keyColumn);
if (!String.IsNullOrEmpty(str)) return str;
keyColumn = keyColumn.Substring(0, keyColumn.IndexOf(" "));
}
#endregion
sql = CheckSimpleSQL(sql2);
if (String.IsNullOrEmpty(keyColumn)) throw new ArgumentNullException("keyColumn", "分页要求指定主键列或者排序字段！");
if (maximumRows < 1)
sql = String.Format("Select * From {1} Where {2} Not In(Select Top {0} {2} From {1} {3}) {3}", startRowIndex, sql, keyColumn, orderBy);
else
sql = String.Format("Select Top {0} * From {1} Where {2} Not In(Select Top {3} {2} From {1} {4}) {4}", maximumRows, sql, keyColumn, startRowIndex, orderBy);
return sql;
}
public override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows) => MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, false, b => CreateSession().QueryCount(b));
/// <summary>构造分页SQL</summary> /// <param name="builder">查询生成器</param> /// <param name="startRowIndex">开始行，0表示第一行</param> /// <param name="maximumRows">最大返回行数，0表示所有行</param> /// <returns>分页SQL</returns> public override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows) => Server.PageSplit(builder, startRowIndex, maximumRows);
public override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
return(MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, IsSQL2005, b => CreateSession().QueryCount(b)));
}
/// <summary>同步单表数据</summary>
/// <remarks>
/// 把数据同一张表同步到另一个库
/// </remarks>
/// <param name="table">数据表</param>
/// <param name="connName">目标连接名</param>
/// <param name="syncSchema">同步架构</param>
/// <param name="progress">进度回调，参数为已处理行数和当前页表</param>
/// <returns></returns>
public Int32 Sync(IDataTable table, String connName, Boolean syncSchema = true, Action <Int32, DbTable> progress = null)
{
var dal = connName.IsNullOrEmpty() ? null : Create(connName);
var writeDb = new WriteDbActor
{
Table = table,
Dal = dal,
// 最多同时堆积数页
BoundedCapacity = 4,
};
// 自增
var id = table.Columns.FirstOrDefault(e => e.Identity);
// 主键
if (id == null)
{
var pks = table.PrimaryKeys;
if (pks != null && pks.Length == 1 && pks[0].DataType.IsInt())
{
id = pks[0];
}
}
var sw = Stopwatch.StartNew();
// 表结构
if (syncSchema)
{
dal.SetTables(table);
}
var sb = new SelectBuilder
{
Table = Db.FormatName(table)
};
var row = 0L;
var pageSize = (Db as DbBase).BatchSize;
var total = 0;
while (true)
{
var sql = "";
// 分割数据页，自增或分页
if (id != null)
{
sb.Where = $"{id.ColumnName}>={row}";
sql = PageSplit(sb, 0, pageSize);
}
else
{
sql = PageSplit(sb, row, pageSize);
}
// 查询数据
var dt = Session.Query(sql, null);
if (dt == null || dt.Rows.Count == 0)
{
break;
}
var count = dt.Rows.Count;
WriteLog("同步[{0}/{1}]数据 {2:n0} + {3:n0}", table.Name, ConnName, row, count);
// 进度报告
progress?.Invoke((Int32)row, dt);
// 消费数据
writeDb.Tell(dt);
// 下一页
total += count;
//if (count < pageSize) break;
// 自增分割时，取最后一行
if (id != null)
{
row = dt.Get <Int64>(count - 1, id.ColumnName) + 1;
}
else
{
row += pageSize;
}
}
// 通知写入完成
writeDb.Stop(-1);
sw.Stop();
var ms = sw.Elapsed.TotalMilliseconds;
WriteLog("同步[{0}/{1}]完成，共[{2:n0}]行，耗时{3:n0}ms，速度{4:n0}tps", table.Name, ConnName, total, ms, total * 1000L / ms);
// 返回总行数
return(total);
}
/// <summary>执行SQL查询，返回记录集</summary>
/// <param name="builder">SQL语句</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <param name="tableNames">所依赖的表的表名</param>
/// <returns></returns>
public DataSet Select(SelectBuilder builder, Int32 startRowIndex, Int32 maximumRows, params String[] tableNames)
{
builder = PageSplit(builder, startRowIndex, maximumRows);
return(Select(builder.ToString(), tableNames));
}
/// <summary>RowNumber分页算法</summary>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns></returns>
static SelectBuilder RowNumber(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
//if (maximumRows < 1)
// sql = String.Format("Select * From (Select *, row_number() over({2}) as rowNumber From {1}) XCode_Temp_b Where rowNumber>={0}", startRowIndex + 1, sql, orderBy);
//else
// sql = String.Format("Select * From (Select *, row_number() over({3}) as rowNumber From {1}) XCode_Temp_b Where rowNumber Between {0} And {2}", startRowIndex + 1, sql, startRowIndex + maximumRows, orderBy);
// 如果包含分组，则必须作为子查询
SelectBuilder builder1 = builder.CloneWithGroupBy("XCode_T0");
builder1.Column = String.Format("{0}, row_number() over(Order By {1}) as rowNumber", builder.ColumnOrDefault, builder.OrderBy ?? builder.KeyOrder);
SelectBuilder builder2 = builder1.AsChild("XCode_T1");
// 结果列保持原样
builder2.Column = builder.Column;
// row_number()直接影响了排序，这里不再需要
builder2.OrderBy = null;
if (maximumRows < 1)
builder2.Where = String.Format("rowNumber>={0}", startRowIndex + 1);
else
builder2.Where = String.Format("rowNumber Between {0} And {1}", startRowIndex + 1, startRowIndex + maximumRows);
return builder2;
}
/// <summary>备份单表数据，抽取数据和写入文件双线程</summary>
/// <remarks>
/// 最大支持21亿行
/// </remarks>
/// <param name="table">数据表</param>
/// <param name="stream">目标数据流</param>
/// <returns></returns>
public virtual Int32 Backup(IDataTable table, Stream stream)
{
using var span = Tracer?.NewSpan("db:Backup", table.Name);
// 并行写入文件，提升吞吐
var writeFile = new WriteFileActor
{
Stream = stream,
// 最多同时堆积数
BoundedCapacity = 4,
TracerParent = span,
Tracer = Tracer,
Log = Log,
};
var tableName = Dal.Db.FormatName(table);
var sb = new SelectBuilder {
Table = tableName
};
var connName = Dal.ConnName;
var extracer = GetExtracter(table);
// 总行数
writeFile.Total = Dal.SelectCount(sb);
WriteLog("备份[{0}/{1}]开始，共[{2:n0}]行，抽取器{3}", table, connName, writeFile.Total, extracer);
// 临时关闭日志
var old = Dal.Db.ShowSQL;
Dal.Db.ShowSQL = false;
Dal.Session.ShowSQL = false;
var total = 0;
var sw = Stopwatch.StartNew();
try
{
foreach (var dt in extracer.Fetch())
{
var row = extracer.Row;
var count = dt.Rows.Count;
WriteLog("备份[{0}/{1}]数据 {2:n0} + {3:n0}", table, connName, row, count);
if (count == 0)
{
break;
}
// 字段名更换为属性名
for (var i = 0; i < dt.Columns.Length; i++)
{
var dc = table.GetColumn(dt.Columns[i]);
if (dc != null)
{
dt.Columns[i] = dc.Name;
}
}
// 进度报告、消费数据
OnProcess(table, row, dt, writeFile);
total += count;
}
// 通知写入完成
writeFile.Stop(-1);
}
catch (Exception ex)
{
span?.SetError(ex, table);
throw;
}
finally
{
Dal.Db.ShowSQL = old;
Dal.Session.ShowSQL = old;
}
sw.Stop();
var ms = sw.Elapsed.TotalMilliseconds;
WriteLog("备份[{0}/{1}]完成，共[{2:n0}]行，耗时{3:n0}ms，速度{4:n0}tps", table, connName, total, ms, total * 1000L / ms);
// 返回总行数
return(total);
}
/// <summary>构造分页SQL</summary>
/// <remarks>
/// 两个构造分页SQL的方法，区别就在于查询生成器能够构造出来更好的分页语句，尽可能的避免子查询。
/// MS体系的分页精髓就在于唯一键，当唯一键带有Asc/Desc/Unkown等排序结尾时，就采用最大最小值分页，否则使用较次的TopNotIn分页。
/// TopNotIn分页和MaxMin分页的弊端就在于无法完美的支持GroupBy查询分页，只能查到第一页，往后分页就不行了，因为没有主键。
/// </remarks>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
public override SelectBuilder PageSplit(SelectBuilder builder, Int32 startRowIndex, Int32 maximumRows)
{
// 从第一行开始，不需要分页
if (startRowIndex <= 0)
{
if (maximumRows > 0) builder.OrderBy += String.Format(" limit {0}", maximumRows);
return builder;
}
if (maximumRows < 1) throw new NotSupportedException("不支持取第几条数据之后的所有数据！");
builder.OrderBy += String.Format(" limit {0} offset {1}", startRowIndex, maximumRows);
return builder;
}
/// <summary>备份单表数据</summary>
/// <remarks>
/// 最大支持21亿行
/// </remarks>
/// <param name="table">数据表</param>
/// <param name="stream">目标数据流</param>
/// <param name="progress">进度回调，参数为已处理行数和当前页表</param>
/// <returns></returns>
public Int32 Backup(IDataTable table, Stream stream, Action <Int32, DbTable> progress = null)
{
var writeFile = new WriteFileActor
{
Stream = stream,
// 最多同时堆积数
BoundedCapacity = 4,
};
//writeFile.Start();
// 自增
var id = table.Columns.FirstOrDefault(e => e.Identity);
// 主键
if (id == null)
{
var pks = table.PrimaryKeys;
if (pks != null && pks.Length == 1 && pks[0].DataType.IsInt())
{
id = pks[0];
}
}
var sb = new SelectBuilder
{
Table = Db.FormatTableName(table.TableName)
};
// 总行数
writeFile.Total = SelectCount(sb);
WriteLog("备份[{0}/{1}]开始，共[{2:n0}]行", table, ConnName, writeFile.Total);
var row = 0L;
var pageSize = 10_000;
var total = 0;
var sw = Stopwatch.StartNew();
while (total < writeFile.Total)
{
var sql = "";
// 分割数据页，自增或分页
if (id != null)
{
sb.Where = $"{id.ColumnName}>={row}";
sql = PageSplit(sb, 0, pageSize);
}
else
{
sql = PageSplit(sb, row, pageSize);
}
// 查询数据
var dt = Session.Query(sql, null);
if (dt == null)
{
break;
}
var count = dt.Rows.Count;
WriteLog("备份[{0}/{1}]数据 {2:n0} + {3:n0}", table, ConnName, row, count);
if (count == 0)
{
break;
}
// 进度报告
progress?.Invoke((Int32)row, dt);
// 消费数据
writeFile.Tell(dt);
// 下一页
total += count;
//if (count < pageSize) break;
// 自增分割时，取最后一行
if (id != null)
{
row = dt.Get <Int64>(count - 1, id.ColumnName) + 1;
}
else
{
row += pageSize;
}
}
// 通知写入完成
writeFile.Stop(-1);
sw.Stop();
var ms = sw.Elapsed.TotalMilliseconds;
WriteLog("备份[{0}/{1}]完成，共[{2:n0}]行，耗时{3:n0}ms，速度{4:n0}tps", table, ConnName, total, ms, total * 1000L / ms);
// 返回总行数
return(total);
}
/// <summary>备份单表数据</summary>
/// <remarks>
/// 最大支持21亿行
/// </remarks>
/// <param name="table">数据表</param>
/// <param name="stream">目标数据流</param>
/// <param name="progress">进度回调，参数为已处理行数和当前页表</param>
/// <returns></returns>
public Int32 Backup(IDataTable table, Stream stream, Action <Int64, DbTable> progress = null)
{
var writeFile = new WriteFileActor
{
Stream = stream,
// 最多同时堆积数
BoundedCapacity = 4,
};
// 自增
var id = table.Columns.FirstOrDefault(e => e.Identity);
if (id == null)
{
var pks = table.PrimaryKeys;
if (pks != null && pks.Length == 1 && pks[0].DataType.IsInt())
{
id = pks[0];
}
}
var tableName = Db.FormatName(table);
var sb = new SelectBuilder {
Table = tableName
};
// 总行数
writeFile.Total = SelectCount(sb);
WriteLog("备份[{0}/{1}]开始，共[{2:n0}]行", table, ConnName, writeFile.Total);
IExtracter <DbTable> extracer = new PagingExtracter(this, tableName);
if (id != null)
{
extracer = new IdExtracter(this, tableName, id.ColumnName);
}
var sw = Stopwatch.StartNew();
var total = 0;
foreach (var dt in extracer.Fetch())
{
var count = dt.Rows.Count;
WriteLog("备份[{0}/{1}]数据 {2:n0} + {3:n0}", table, ConnName, extracer.Row, count);
if (count == 0)
{
break;
}
// 进度报告
progress?.Invoke(extracer.Row, dt);
// 消费数据
writeFile.Tell(dt);
total += count;
}
// 通知写入完成
writeFile.Stop(-1);
sw.Stop();
var ms = sw.Elapsed.TotalMilliseconds;
WriteLog("备份[{0}/{1}]完成，共[{2:n0}]行，耗时{3:n0}ms，速度{4:n0}tps", table, ConnName, total, ms, total * 1000L / ms);
// 返回总行数
return(total);
}
/// <summary>执行SQL查询，返回总记录数</summary>
/// <param name="sb">查询生成器</param>
/// <returns></returns>
public Task <Int64> SelectCountAsync(SelectBuilder sb)
{
return(QueryByCacheAsync(sb, "", "", (s, k2, k3) => Session.QueryCountAsync(s), nameof(SelectCountAsync)));
}
/// <summary>双Top分页，因为没有使用not in，性能比NotIn要好。语句必须有排序，不需额外指定排序列</summary>
/// <param name="builder"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <param name="queryCountCallback">查询总记录数的委托，近供DoubleTop使用</param>
/// <returns></returns>
static SelectBuilder DoubleTop(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows, Func <SelectBuilder, Int64> queryCountCallback)
{
if (builder.Keys == null)
{
throw new ArgumentNullException("Key", "DoubleTop分页算法要求指定排序列！" + builder.ToString());
}
// 采用DoubleTop分页，最后一页可能有问题，需要特殊处理
if (queryCountCallback != null)
{
// 查询总记录数，计算是否最后一页
var count = queryCountCallback(builder);
// 刚好相等的就不必处理了
if (startRowIndex + maximumRows > count)
{
maximumRows = count - startRowIndex;
}
}
// 分页标准 Select (20,10,ID Desc)
// 1，按原始排序取20+10行，此时目标页位于底部
// 2，倒序过来取10行，得到目标页，但是顺序是反的
// 3，再倒序一次
// 显然，原始语句必须有排序，否则无法倒序。另外，也不能处理maximumRows<1的情况
// Select * From (Select Top 10 * From (Select Top 20+10 * From Table Order By ID Desc) Order By ID Asc) Order By ID Desc
// 找到排序，优先采用排序字句来做双Top排序
String orderby = builder.OrderBy ?? builder.KeyOrder;
Boolean[] isdescs = null;
String[] keys = SelectBuilder.Split(orderby, out isdescs);
// 把排序反过来
Boolean[] isdescs2 = new Boolean[keys.Length];
for (int i = 0; i < keys.Length; i++)
{
if (isdescs != null && isdescs.Length > i)
{
isdescs2[i] = !isdescs[i];
}
else
{
isdescs2[i] = true;
}
}
String reversekeyorder = SelectBuilder.Join(keys, isdescs2);
// 构建Select Top 20 * From Table Order By ID Asc
SelectBuilder builder1 = builder.Clone().AppendColumn(keys).Top(startRowIndex + maximumRows);
// 必须加一个排序，否则会被优化掉而导致出错
if (String.IsNullOrEmpty(builder1.OrderBy))
{
builder1.OrderBy = builder1.KeyOrder;
}
SelectBuilder builder2 = builder1.AsChild("XCode_T0").Top(maximumRows);
// 要反向排序
builder2.OrderBy = reversekeyorder;
SelectBuilder builder3 = builder2.AsChild("XCode_T1");
// 结果列保持原样
builder3.Column = builder.Column;
// 让结果正向排序
builder3.OrderBy = orderby;
return(builder3);
}
/// <summary>备份单表数据</summary>
/// <remarks>
/// 最大支持21亿行
/// </remarks>
/// <param name="table">数据表</param>
/// <param name="stream">目标数据流</param>
/// <param name="progress">进度回调，参数为已处理行数和当前页表</param>
/// <returns></returns>
public Int32 Backup(String table, Stream stream, Action <Int32, DbTable> progress = null)
{
var writeFile = new WriteFileActor
{
Stream = stream,
// 最多同时堆积数页
BoundedCapacity = 4,
};
//writeFile.Start();
var sb = new SelectBuilder
{
Table = Db.FormatTableName(table)
};
var row = 0;
var pageSize = 10_000;
var total = 0;
var sw = Stopwatch.StartNew();
while (true)
{
// 分页
var sb2 = PageSplit(sb, row, pageSize);
// 查询数据
var dt = Session.Query(sb2.ToString(), null);
if (dt == null)
{
break;
}
var count = dt.Rows.Count;
WriteLog("备份[{0}/{1}]数据 {2:n0} + {3:n0}", table, ConnName, row, count);
// 进度报告
progress?.Invoke(row, dt);
// 消费数据
writeFile.Tell(dt);
// 下一页
total += count;
if (count < pageSize)
{
break;
}
row += pageSize;
}
// 通知写入完成
writeFile.Stop(-1);
sw.Stop();
var ms = sw.Elapsed.TotalMilliseconds;
WriteLog("备份[{0}/{1}]完成，共[{2:n0}]行，耗时{3:n0}ms，速度{4:n0}tps", table, ConnName, total, ms, total * 1000L / ms);
// 返回总行数
return(total);
}
/// <summary>执行SQL查询，返回总记录数</summary> /// <param name="builder">查询生成器</param> /// <returns>总记录数</returns> public virtual Int64 QueryCount(SelectBuilder builder) => ExecuteScalar <Int64>(builder.SelectCount().ToString(), CommandType.Text, builder.Parameters.ToArray());
/// <summary>同步单表数据</summary>
/// <remarks>
/// 把数据同一张表同步到另一个库
/// </remarks>
/// <param name="table">数据表</param>
/// <param name="connName">目标连接名</param>
/// <param name="syncSchema">同步架构</param>
/// <param name="progress">进度回调，参数为已处理行数和当前页表</param>
/// <returns></returns>
public Int32 Sync(IDataTable table, String connName, Boolean syncSchema = true, Action <Int32, DbTable> progress = null)
{
var dal = connName.IsNullOrEmpty() ? null : Create(connName);
var writeDb = new WriteDbActor
{
Table = table,
Dal = dal,
// 最多同时堆积数页
BoundedCapacity = 4,
};
var sw = Stopwatch.StartNew();
// 表结构
if (syncSchema)
{
dal.SetTables(table);
}
var sb = new SelectBuilder
{
Table = Db.FormatTableName(table.TableName)
};
var row = 0;
var pageSize = 10_000;
var total = 0;
while (true)
{
// 分页
var sb2 = PageSplit(sb, row, pageSize);
// 查询数据
var dt = Session.Query(sb2.ToString(), null);
if (dt == null)
{
break;
}
var count = dt.Rows.Count;
WriteLog("同步[{0}/{1}]数据 {2:n0} + {3:n0}", table.Name, ConnName, row, count);
// 进度报告
progress?.Invoke(row, dt);
// 消费数据
writeDb.Tell(dt);
// 下一页
total += count;
if (count < pageSize)
{
break;
}
row += pageSize;
}
// 通知写入完成
writeDb.Stop(-1);
sw.Stop();
var ms = sw.Elapsed.TotalMilliseconds;
WriteLog("同步[{0}/{1}]完成，共[{2:n0}]行，耗时{3:n0}ms，速度{4:n0}tps", table.Name, ConnName, total, ms, total * 1000L / ms);
// 返回总行数
return(total);
}
/// <summary>双Top分页，因为没有使用not in，性能比NotIn要好。语句必须有排序，不需额外指定排序列</summary>
/// <param name="builder"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <param name="queryCountCallback">查询总记录数的委托，近供DoubleTop使用</param>
/// <returns></returns>
static SelectBuilder DoubleTop(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows, Func<SelectBuilder, Int64> queryCountCallback)
{
if (builder.Keys == null) throw new ArgumentNullException("Key", "DoubleTop分页算法要求指定排序列！" + builder.ToString());
// 采用DoubleTop分页，最后一页可能有问题，需要特殊处理
if (queryCountCallback != null)
{
// 查询总记录数，计算是否最后一页
var count = queryCountCallback(builder);
// 数据不足
if (count <= startRowIndex) return null;
// 刚好相等的就不必处理了
if (startRowIndex + maximumRows > count) maximumRows = count - startRowIndex;
}
// 分页标准 Select (20,10,ID Desc)
// 1，按原始排序取20+10行，此时目标页位于底部
// 2，倒序过来取10行，得到目标页，但是顺序是反的
// 3，再倒序一次
// 显然，原始语句必须有排序，否则无法倒序。另外，也不能处理maximumRows<1的情况
// Select * From (Select Top 10 * From (Select Top 20+10 * From Table Order By ID Desc) Order By ID Asc) Order By ID Desc
// 找到排序，优先采用排序字句来做双Top排序
var orderby = builder.OrderBy ?? builder.KeyOrder;
Boolean[] isdescs = null;
var keys = SelectBuilder.Split(orderby, out isdescs);
// 把排序反过来
var isdescs2 = new Boolean[keys.Length];
for (int i = 0; i < keys.Length; i++)
{
if (isdescs != null && isdescs.Length > i)
isdescs2[i] = !isdescs[i];
else
isdescs2[i] = true;
}
var reversekeyorder = SelectBuilder.Join(keys, isdescs2);
// 构建Select Top 20 * From Table Order By ID Asc
var builder1 = builder.Clone().AppendColumn(keys).Top(startRowIndex + maximumRows);
// 必须加一个排序，否则会被优化掉而导致出错
if (String.IsNullOrEmpty(builder1.OrderBy)) builder1.OrderBy = builder1.KeyOrder;
var builder2 = builder1.AsChild("XCode_T0").Top(maximumRows);
// 要反向排序
builder2.OrderBy = reversekeyorder;
var builder3 = builder2.AsChild("XCode_T1");
// 结果列处理
builder3.Column = builder.Column;
// 如果结果列包含有“.”，即有形如tab1.id、tab2.name之类的列时设为获取子查询的全部列
if ((!string.IsNullOrEmpty(builder3.Column)) && builder3.Column.Contains("."))
{
builder3.Column = "*";
}
// 让结果正向排序
builder3.OrderBy = orderby;
return builder3;
}
/// <summary>根据条件把普通查询SQL格式化为分页SQL。</summary>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
public SelectBuilder PageSplit(SelectBuilder builder, Int32 startRowIndex, Int32 maximumRows)
{
//2016年7月2日 HUIYUE 取消分页SQL缓存，此部分缓存提升性能不多，但有可能会造成分页数据不准确，感觉得不偿失
return(Db.PageSplit(builder, startRowIndex, maximumRows));
}
/// <summary>RowNumber分页算法</summary>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns></returns>
static SelectBuilder RowNumber(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
//if (maximumRows < 1)
// sql = String.Format("Select * From (Select *, row_number() over({2}) as rowNumber From {1}) XCode_Temp_b Where rowNumber>={0}", startRowIndex + 1, sql, orderBy);
//else
// sql = String.Format("Select * From (Select *, row_number() over({3}) as rowNumber From {1}) XCode_Temp_b Where rowNumber Between {0} And {2}", startRowIndex + 1, sql, startRowIndex + maximumRows, orderBy);
// 如果包含分组，则必须作为子查询
var builder1 = builder.CloneWithGroupBy("XCode_T0");
//builder1.Column = String.Format("{0}, row_number() over(Order By {1}) as rowNumber", builder.ColumnOrDefault, builder.OrderBy ?? builder.KeyOrder);
// 不必追求极致，把所有列放出来
builder1.Column = "*, row_number() over(Order By {0}) as rowNumber".F(builder.OrderBy ?? builder.KeyOrder);
var builder2 = builder1.AsChild("XCode_T1");
// 结果列处理
//builder2.Column = builder.Column;
//// 如果结果列包含有“.”，即有形如tab1.id、tab2.name之类的列时设为获取子查询的全部列
//if ((!string.IsNullOrEmpty(builder2.Column)) && builder2.Column.Contains("."))
//{
// builder2.Column = "*";
//}
// 不必追求极致，把所有列放出来
builder2.Column = "*";
// row_number()直接影响了排序，这里不再需要
builder2.OrderBy = null;
if (maximumRows < 1)
builder2.Where = String.Format("rowNumber>={0}", startRowIndex + 1);
else
builder2.Where = String.Format("rowNumber Between {0} And {1}", startRowIndex + 1, startRowIndex + maximumRows);
return builder2;
}
/// <summary>构造分页SQL</summary>
/// <param name="sql">SQL语句</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <param name="keyColumn">唯一键。用于not in分页</param>
/// <returns>分页SQL</returns>
public override String PageSplit(String sql, Int64 startRowIndex, Int64 maximumRows, String keyColumn)
{
// 从第一行开始，不需要分页
if (startRowIndex <= 0 && maximumRows < 1)
{
return(sql);
}
if (startRowIndex > 0)
{
// 指定了起始行，并且是SQL2005及以上版本，使用MS SQL 2012特有的分页算法
if (IsSQL2012)
{
// 从第一行开始，不需要分页
if (startRowIndex <= 0)
{
if (maximumRows < 1)
{
return(sql);
}
var sql_ = FormatSqlserver2012SQL(sql);
return($"{sql_} offset 1 rows fetch next {maximumRows} rows only ");
}
if (maximumRows < 1)
{
throw new NotSupportedException("不支持取第几条数据之后的所有数据！");
}
var sql__ = FormatSqlserver2012SQL(sql);
return($"{sql__} offset {startRowIndex} rows fetch next {maximumRows} rows only ");
}
// 指定了起始行，并且是SQL2005及以上版本，使用RowNumber算法
//if (IsSQL2005)
{
//return PageSplitRowNumber(sql, startRowIndex, maximumRows, keyColumn);
var builder = new SelectBuilder();
builder.Parse(sql);
//return MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, IsSQL2005).ToString();
return(PageSplit(builder, startRowIndex, maximumRows).ToString());
}
}
// 如果没有Order By，直接调用基类方法
// 先用字符串判断，命中率高，这样可以提高处理效率
if (!sql.Contains(" Order "))
{
if (!sql.ToLower().Contains(" order "))
{
return(base.PageSplit(sql, startRowIndex, maximumRows, keyColumn));
}
}
//// 使用正则进行严格判断。必须包含Order By，并且它右边没有右括号)，表明有order by，且不是子查询的，才需要特殊处理
//MatchCollection ms = Regex.Matches(sql, @"\border\s*by\b([^)]+)$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
//if (ms == null || ms.Count < 1 || ms[0].Index < 1)
var sql2 = sql;
var orderBy = CheckOrderClause(ref sql2);
if (String.IsNullOrEmpty(orderBy))
{
return(base.PageSplit(sql, startRowIndex, maximumRows, keyColumn));
}
// 已确定该sql最外层含有order by，再检查最外层是否有top。因为没有top的order by是不允许作为子查询的
if (Regex.IsMatch(sql, @"^[^(]+\btop\b", RegexOptions.Compiled | RegexOptions.IgnoreCase))
{
return(base.PageSplit(sql, startRowIndex, maximumRows, keyColumn));
}
//String orderBy = sql.Substring(ms[0].Index);
// 从第一行开始，不需要分页
if (startRowIndex <= 0)
{
if (maximumRows < 1)
{
return(sql);
}
else
{
return(String.Format("Select Top {0} * From {1} {2}", maximumRows, CheckSimpleSQL(sql2), orderBy));
}
//return String.Format("Select Top {0} * From {1} {2}", maximumRows, CheckSimpleSQL(sql.Substring(0, ms[0].Index)), orderBy);
}
#region Max/Min分页
// 如果要使用max/min分页法，首先keyColumn必须有asc或者desc
var kc = keyColumn.ToLower();
if (kc.EndsWith(" desc") || kc.EndsWith(" asc") || kc.EndsWith(" unknown"))
{
var str = PageSplitMaxMin(sql, startRowIndex, maximumRows, keyColumn);
if (!String.IsNullOrEmpty(str))
{
return(str);
}
keyColumn = keyColumn.Substring(0, keyColumn.IndexOf(" "));
}
#endregion
sql = CheckSimpleSQL(sql2);
if (String.IsNullOrEmpty(keyColumn))
{
throw new ArgumentNullException("keyColumn", "分页要求指定主键列或者排序字段！");
}
if (maximumRows < 1)
{
sql = String.Format("Select * From {1} Where {2} Not In(Select Top {0} {2} From {1} {3}) {3}", startRowIndex, sql, keyColumn, orderBy);
}
else
{
sql = String.Format("Select Top {0} * From {1} Where {2} Not In(Select Top {3} {2} From {1} {4}) {4}", maximumRows, sql, keyColumn, startRowIndex, orderBy);
}
return(sql);
}
public override SelectBuilder PageSplit(SelectBuilder builder, int startRowIndex, int maximumRows)
{
return(MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, false, b => CreateSession().QueryCount(b)));
}
/// <summary>执行SQL查询，返回记录集</summary>
/// <param name="builder">SQL语句</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <param name="tableNames">所依赖的表的表名</param>
/// <returns></returns>
public DataSet Select(SelectBuilder builder, Int32 startRowIndex, Int32 maximumRows, params String[] tableNames)
{
builder = PageSplit(builder, startRowIndex, maximumRows);
return Select(builder.ToString(), tableNames);
}
/// <summary>执行SQL查询，返回总记录数</summary>
/// <param name="sb">查询生成器</param>
/// <returns></returns>
public Int32 SelectCount(SelectBuilder sb)
{
return((Int32)QueryByCache(sb, "", "", (s, k2, k3) => Session.QueryCount(s), nameof(SelectCount)));
}
/// <summary>执行SQL查询，返回总记录数</summary>
/// <param name="sb">查询生成器</param>
/// <param name="tableNames">所依赖的表的表名</param>
/// <returns></returns>
public Int32 SelectCount(SelectBuilder sb, params String[] tableNames)
{
String sql = sb.ToString();
String cacheKey = sql + "_SelectCount" + "_" + ConnName;
Int32 rs = 0;
if (EnableCache && XCache.TryGetItem(cacheKey, out rs)) return rs;
Interlocked.Increment(ref _QueryTimes);
rs = (Int32)Session.QueryCount(sb);
if (EnableCache) XCache.Add(cacheKey, rs, tableNames);
return rs;
}
/// <summary>构造分页SQL</summary>
/// <remarks>
/// 两个构造分页SQL的方法，区别就在于查询生成器能够构造出来更好的分页语句，尽可能的避免子查询。
/// MS体系的分页精髓就在于唯一键，当唯一键带有Asc/Desc/Unkown等排序结尾时，就采用最大最小值分页，否则使用较次的TopNotIn分页。
/// TopNotIn分页和MaxMin分页的弊端就在于无法完美的支持GroupBy查询分页，只能查到第一页，往后分页就不行了，因为没有主键。
/// </remarks>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
public virtual SelectBuilder PageSplit(SelectBuilder builder, Int32 startRowIndex, Int32 maximumRows)
{
// 从第一行开始，不需要分页
if (startRowIndex <= 0 && maximumRows < 1) return builder;
var sql = PageSplit(builder.ToString(), startRowIndex, maximumRows, builder.Key);
var sb = new SelectBuilder();
sb.Parse(sql);
return sb;
}
/// <summary>根据条件把普通查询SQL格式化为分页SQL。</summary>
/// <remarks>
/// 因为需要继承重写的原因，在数据类中并不方便缓存分页SQL。
/// 所以在这里做缓存。
/// </remarks>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
public SelectBuilder PageSplit(SelectBuilder builder, Int32 startRowIndex, Int32 maximumRows)
{
String cacheKey = String.Format("{0}_{1}_{2}_{3}_", builder, startRowIndex, maximumRows, ConnName);
return _PageSplitCache2.GetItem<SelectBuilder, Int32, Int32>(cacheKey, builder, startRowIndex, maximumRows, (k, b, s, m) =>
{
return Db.PageSplit(b, s, m);
});
}
/// <summary>作为子查询</summary>
/// <param name="alias">别名，某些数据库可能需要使用as</param>
/// <returns></returns>
public SelectBuilder AsChild(String alias = null)
{
var t = this;
// 如果包含排序，则必须有Top，否则去掉
var hasOrderWithoutTop = !String.IsNullOrEmpty(t.OrderBy) && !ColumnOrDefault.StartsWithIgnoreCase("top ");
if (hasOrderWithoutTop)
{
t = this.Clone();
t.OrderBy = null;
}
var builder = new SelectBuilder();
if (String.IsNullOrEmpty(alias))
builder.Table = String.Format("({0})", t.ToString());
else
builder.Table = String.Format("({0}) {1}", t.ToString(), alias);
// 把排序加载外层
if (hasOrderWithoutTop) builder.OrderBy = this.OrderBy;
return builder;
}
/// <summary>双Top分页，因为没有使用not in，性能比NotIn要好。语句必须有排序，不需额外指定排序列</summary>
/// <param name="builder"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <param name="queryCountCallback">查询总记录数的委托，近供DoubleTop使用</param>
/// <returns></returns>
static SelectBuilder DoubleTop(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows, Func <SelectBuilder, Int64> queryCountCallback)
{
if (builder.Keys == null)
{
throw new ArgumentNullException("Key", "DoubleTop分页算法要求指定排序列！" + builder.ToString());
}
// 采用DoubleTop分页，最后一页可能有问题，需要特殊处理
if (queryCountCallback != null)
{
// 查询总记录数，计算是否最后一页
var count = queryCountCallback(builder);
// 数据不足
if (count <= startRowIndex)
{
return(null);
}
// 刚好相等的就不必处理了
if (startRowIndex + maximumRows > count)
{
maximumRows = count - startRowIndex;
}
}
// 分页标准 Select (20,10,ID Desc)
// 1，按原始排序取20+10行，此时目标页位于底部
// 2，倒序过来取10行，得到目标页，但是顺序是反的
// 3，再倒序一次
// 显然，原始语句必须有排序，否则无法倒序。另外，也不能处理maximumRows<1的情况
// Select * From (Select Top 10 * From (Select Top 20+10 * From Table Order By ID Desc) Order By ID Asc) Order By ID Desc
// 找到排序，优先采用排序字句来做双Top排序
var orderby = builder.OrderBy ?? builder.KeyOrder;
var keys = SelectBuilder.Split(orderby, out var isdescs);
// 把排序反过来
var isdescs2 = new Boolean[keys.Length];
for (Int32 i = 0; i < keys.Length; i++)
{
if (isdescs != null && isdescs.Length > i)
{
isdescs2[i] = !isdescs[i];
}
else
{
isdescs2[i] = true;
}
}
var reversekeyorder = SelectBuilder.Join(keys, isdescs2);
// 构建Select Top 20 * From Table Order By ID Asc
var builder1 = builder.Clone().AppendColumn(keys).Top(startRowIndex + maximumRows);
// 必须加一个排序，否则会被优化掉而导致出错
if (String.IsNullOrEmpty(builder1.OrderBy))
{
builder1.OrderBy = builder1.KeyOrder;
}
var builder2 = builder1.AsChild("XCode_T0", true).Top(maximumRows);
// 要反向排序
builder2.OrderBy = reversekeyorder;
var builder3 = builder2.AsChild("XCode_T1", true);
// 结果列处理
builder3.Column = builder.Column;
// 如果结果列包含有“.”，即有形如tab1.id、tab2.name之类的列时设为获取子查询的全部列
if ((!string.IsNullOrEmpty(builder3.Column)) && builder3.Column.Contains("."))
{
builder3.Column = "*";
}
// 让结果正向排序
builder3.OrderBy = orderby;
return(builder3);
}
