/// <summary>
/// Builds an SQL query suitable for performing page based queries to the database
/// </summary>
/// <param name="skip">The number of rows that should be skipped by the query</param>
/// <param name="take">The number of rows that should be retruend by the query</param>
/// <param name="parts">The original SQL query after being parsed into it's component parts</param>
/// <param name="args">Arguments to any embedded parameters in the SQL query</param>
/// <returns>The final SQL query that should be executed.</returns>
public virtual string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
var sql = string.Format("{0}\nLIMIT @{1} OFFSET @{2}", parts.Sql, args.Length, args.Length + 1);
args = args.Concat(new object[] { take, skip }).ToArray();
return(sql);
}
//private static readonly Regex SelectTopRegex = new Regex(@"^SELECT +TOP(\d+)", RegexOptions.IgnoreCase);
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
if (!parts.Sql.ToLower().Contains("order by"))
{
return(base.BuildPageQuery(skip, take, parts, ref args));
}
if (SelectTopRegex.IsMatch(parts.Sql))
{
return(parts.Sql);
}
if (skip == 0)
{
if (parts.Sql.StartsWith("SELECT ", StringComparison.InvariantCultureIgnoreCase))
{
var sql = $"SELECT TOP(@{args.Length}) " + parts.Sql.Substring(7 /*"SELECT ".Length*/);
args = args.Concat(new object[] { take }).ToArray();
return(sql);
}
}
var sqlPage = $"{parts.Sql}\nOFFSET @{args.Length} ROWS FETCH NEXT @{args.Length + 1} ROWS ONLY";
args = args.Concat(new object[] { skip, take }).ToArray();
return(sqlPage);
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args, string primaryKey = "")
{
var helper = (PagingHelper)PagingUtility;
// when the query does not contain an "order by", it is very slow
if (helper.SimpleRegexOrderBy.IsMatch(parts.SqlSelectRemoved))
{
var m = helper.SimpleRegexOrderBy.Match(parts.SqlSelectRemoved);
if (m.Success)
{
var g = m.Groups[0];
parts.SqlSelectRemoved = parts.SqlSelectRemoved.Substring(0, g.Index);
}
}
if (helper.RegexDistinct.IsMatch(parts.SqlSelectRemoved))
{
parts.SqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.SqlSelectRemoved + ") peta_inner";
}
//var sqlPage =
// $"SELECT * FROM (SELECT ROW_NUMBER() OVER ({parts.SqlOrderBy ?? "ORDER BY (SELECT NULL)"}) peta_rn, {parts.SqlSelectRemoved}) peta_paged WHERE peta_rn > @{args.Length} AND peta_rn <= @{args.Length + 1}";
primaryKey = primaryKey ?? string.Empty;
if (primaryKey.Contains <char>('.') && !primaryKey.EndsWith("."))
{
primaryKey = primaryKey.Substring(primaryKey.LastIndexOf(".") + 1);//移除表名
}
var sqlPage = string.IsNullOrEmpty(primaryKey) ? string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}", parts.SqlOrderBy == null ? "ORDER BY (SELECT NULL)" : parts.SqlOrderBy, parts.SqlSelectRemoved, args.Length, args.Length + 1)
: string.Format("SELECT peta_paged.{4} FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}",
parts.SqlOrderBy == null ? "ORDER BY (SELECT NULL)" : parts.SqlOrderBy, parts.SqlSelectRemoved, args.Length, args.Length + 1, primaryKey);
args = args.Concat(new object[] { skip, skip + take }).ToArray();
return(sqlPage);
}
private void Fetch()
{
Db2SourceContext ctx = CurrentDataSet;
if (ctx == null)
{
AddLog("データベースに接続していません。", null, null, LogStatus.Error, true);
return;
}
string sql = textBoxSql.Text.TrimEnd();
if (string.IsNullOrEmpty(sql))
{
AddLog("SQLがありません。", null, null, LogStatus.Error, true);
return;
}
SQLParts parts = ctx.SplitSQL(sql);
if (parts.Count == 0)
{
AddLog("SQLがありません。", null, null, LogStatus.Error, true);
return;
}
listBoxErrors.Items.Clear();
listBoxErrors.Visibility = Visibility.Collapsed;
UpdateDataGridResult(parts);
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args, string primaryKey = "")
{
var sql = $"{parts.Sql}\nROWS @{args.Length} TO @{args.Length + 1}";
args = args.Concat(new object[] { skip + 1, skip + take }).ToArray();
return(sql);
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
var sql = string.Format("{0}\nROWS @{1} TO @{2}", parts.Sql, args.Length, args.Length + 1);
args = args.Concat(new object[] { skip + 1, skip + take }).ToArray();
return(sql);
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
var helper = PagingUtility;
// when the query does not contain an "order by", it is very slow
if (helper.SimpleRegexOrderBy.IsMatch(parts.SqlSelectRemoved))
{
var m = helper.SimpleRegexOrderBy.Match(parts.SqlSelectRemoved);
if (m.Success)
{
var g = m.Groups[0];
parts.SqlSelectRemoved = parts.SqlSelectRemoved.Substring(0, g.Index);
}
}
if (helper.RegexDistinct.IsMatch(parts.SqlSelectRemoved))
{
parts.SqlSelectRemoved = $"peta_inner.* FROM (SELECT {parts.SqlSelectRemoved}) peta_inner";
}
var sqlPage =
$"SELECT * FROM (SELECT ROW_NUMBER() OVER ({parts.SqlOrderBy ?? "ORDER BY (SELECT NULL)"}) peta_rn, " +
$"{parts.SqlSelectRemoved}) peta_paged " +
$"WHERE peta_rn > @{args.Length} AND peta_rn <= @{args.Length + 1}";
args = args.Concat(new object[] { skip, skip + take }).ToArray();
return(sqlPage);
}
public static bool SplitSQL(string sql, out SQLParts parts)
{
parts.sql = sql;
parts.sqlSelectRemoved = null;
parts.sqlCount = null;
parts.sqlOrderBy = null;
parts.sqlUnordered = sql.Trim().Trim(';');
parts.sqlColumns = "*";
// Extract the columns from "SELECT <whatever> FROM"
var m = rxColumns.Match(sql);
if (!m.Success) return false;
// Save column list [and replace with COUNT(*)]
Group g = m.Groups[1];
parts.sqlSelectRemoved = sql.Substring(g.Index);
// Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
var matches = rxOrderBy.Matches(parts.sqlUnordered);
if (matches.Count > 0)
{
m = matches[matches.Count - 1];
g = m.Groups[0];
parts.sqlOrderBy = g.ToString();
parts.sqlUnordered = rxOrderBy.Replace(parts.sqlUnordered, "", 1, m.Index);
}
parts.sqlCount = string.Format(@"SELECT COUNT(*) FROM ({0}) peta_tbl", parts.sqlUnordered);
return true;
}
/// <summary>
/// 分割SQL
/// </summary>
/// <param name="sql">主SQL</param>
/// <param name="parts">分页SQL信息</param>
/// <returns></returns>
public static bool SplitSQL(string sql, out SQLParts parts)
{
parts.sql = sql;
parts.sqlSelectRemoved = null;
parts.sqlCount = null;
parts.sqlOrderBy = null;
parts.sqlUnordered = sql.Trim().Trim(';');
parts.sqlColumns = "*";
// Extract the columns from "SELECT <whatever> FROM"
var m = rxColumns.Match(sql);
if (!m.Success)
{
return(false);
}
// Save column list [and replace with COUNT(*)]
Group g = m.Groups[1];
parts.sqlSelectRemoved = sql.Substring(g.Index);
// Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
m = rxOrderBy.Match(parts.sql);
if (m.Success)
{
g = m.Groups[0];
parts.sqlOrderBy = g.ToString();
parts.sqlUnordered = rxOrderBy.Replace(parts.sqlUnordered, "");
}
parts.sqlCount = string.Format(@"SELECT COUNT(*) FROM ({0}) peta_tbl", parts.sqlUnordered);
return(true);
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
var sqlPage = string.Format("{0}\nOFFSET @{1} ROWS FETCH NEXT @{2} ROWS ONLY", parts.Sql, args.Length, args.Length + 1);
args = args.Concat(new object[] { skip, take }).ToArray();
return(sqlPage);
}
/// <inheritdoc />
public virtual string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
var sql = $"{parts.Sql}\nLIMIT @{args.Length} OFFSET @{args.Length + 1}";
args = args.Concat(new object[] { take, skip }).ToArray();
return(sql);
}
public virtual string BuildPageQuery(long skip, long take, SQLParts parts, ref object param)
{
var sql = string.Format("{0}\nLIMIT @take OFFSET @skip", parts.Sql);
DynamicParameters newParam = new DynamicParameters(param);
newParam.Add("skip", skip);
newParam.Add("take", take);
param = newParam;
return(sql);
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
if (parts.SqlSelectRemoved.StartsWith("*"))
{
throw new Exception("Query must alias '*' when performing a paged query.\neg. select t.* from table t order by t.id");
}
// Same deal as SQL Server
return(Singleton <SqlServerDatabaseProvider> .Instance.BuildPageQuery(skip, take, parts, ref args));
}
public static string BuildPaging(long skip, long take, SQLParts parts, ref object[] args)
{
parts.sqlOrderBy = string.IsNullOrEmpty(parts.sqlOrderBy) ? null : OrderByAlias.Replace(parts.sqlOrderBy, "$1");
var sqlPage = string.Format("SELECT {4} FROM (SELECT poco_base.*, ROW_NUMBER() OVER ({0}) poco_rn \nFROM ( \n{1}) poco_base ) poco_paged \nWHERE poco_rn > @{2} AND poco_rn <= @{3} \nORDER BY poco_rn",
parts.sqlOrderBy ?? "ORDER BY (SELECT NULL /*poco_dual*/)", parts.sqlUnordered, args.Length, args.Length + 1, parts.sqlColumns);
args = args.Concat(new object[] { skip, skip + take }).ToArray();
return(sqlPage);
}
public string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
if (string.IsNullOrEmpty(parts.SqlOrderBy))
{
parts.Sql += " ORDER BY ABS(1)";
}
var sqlPage = string.Format("{0}\nOFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", parts.Sql, skip, take);
return(sqlPage);
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args, string primaryKey = "")
{
if (string.IsNullOrEmpty(parts.SqlOrderBy))
{
parts.Sql += " ORDER BY ABS(1)";
}
var sqlPage = $"{parts.Sql}\nOFFSET @{args.Length} ROWS FETCH NEXT @{args.Length + 1} ROWS ONLY";
args = args.Concat(new object[] { skip, take }).ToArray();
return(sqlPage);
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
if (string.IsNullOrEmpty(parts.SqlOrderBy))
{
parts.Sql += " ORDER BY ABS(1)";
}
var sqlPage = string.Format("{0}\nOFFSET @{1} ROWS FETCH NEXT @{2} ROWS ONLY", parts.Sql, args.Length, args.Length + 1);
args = args.Concat(new object[] { skip, take }).ToArray();
return(sqlPage);
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
if (!parts.Sql.ToLower().Contains("order by"))
{
throw new Exception("SQL Server 2012 Paging via OFFSET requires an ORDER BY statement.");
}
var sqlPage = $"{parts.Sql}\nOFFSET @{args.Length} ROWS FETCH NEXT @{args.Length + 1} ROWS ONLY";
args = args.Concat(new object[] { skip, take }).ToArray();
return(sqlPage);
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
var helper = (PagingHelper)PagingUtility;
parts.SqlSelectRemoved = helper.RegexOrderBy.Replace(parts.SqlSelectRemoved, "", 1);
if (helper.RegexDistinct.IsMatch(parts.SqlSelectRemoved))
{
parts.SqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.SqlSelectRemoved + ") peta_inner";
}
var sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}",
parts.SqlOrderBy == null ? "ORDER BY (SELECT NULL)" : parts.SqlOrderBy, parts.SqlSelectRemoved, args.Length, args.Length + 1);
args = args.Concat(new object[] { skip, skip + take }).ToArray();
return(sqlPage);
}
public static bool SplitSQL(string sql, out SQLParts parts)
{
parts.sql = sql;
parts.sqlSelectRemoved = null;
parts.sqlCount = null;
parts.sqlOrderBy = null;
// Extract the columns from "SELECT <whatever> FROM"
var m = rxColumns.Match(sql);
if (!m.Success)
{
return(false);
}
// Save column list and replace with COUNT(*)
Group g = m.Groups[1];
parts.sqlSelectRemoved = sql.Substring(g.Index);
if (rxDistinct.IsMatch(parts.sqlSelectRemoved))
{
parts.sqlCount = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
}
else
{
parts.sqlCount = 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 = rxOrderBy.Match(parts.sqlCount);
if (!m.Success)
{
parts.sqlOrderBy = null;
}
else
{
g = m.Groups[0];
parts.sqlOrderBy = g.ToString();
parts.sqlCount = parts.sqlCount.Substring(0, g.Index) + parts.sqlCount.Substring(g.Index + g.Length);
}
return(true);
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object param)
{
var helper = (PagingHelper)PagingUtility;
parts.SqlSelectRemoved = helper.RegexOrderBy.Replace(parts.SqlSelectRemoved, "", 1);
if (helper.RegexDistinct.IsMatch(parts.SqlSelectRemoved))
{
parts.SqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.SqlSelectRemoved + ") peta_inner";
}
var sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@min AND peta_rn<=@max",
parts.SqlOrderBy == null ? "ORDER BY (SELECT NULL)" : parts.SqlOrderBy, parts.SqlSelectRemoved);
DynamicParameters newParam = new DynamicParameters(param);
newParam.Add("min", skip);
newParam.Add("max", skip + take);
param = newParam;
return(sqlPage);
}
public string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
var helper = PagingHelper.Instance;
// when the query does not contain an "order by", it is very slow
if (helper.SimpleRegexOrderBy.IsMatch(parts.SqlSelectRemoved))
{
var m = helper.SimpleRegexOrderBy.Match(parts.SqlSelectRemoved);
if (m.Success)
{
var g = m.Groups[0];
parts.SqlSelectRemoved = parts.SqlSelectRemoved.Substring(0, g.Index);
}
}
if (helper.RegexDistinct.IsMatch(parts.SqlSelectRemoved))
{
parts.SqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.SqlSelectRemoved + ") peta_inner";
}
var sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn > @{2} AND peta_rn <= @{3}", parts.SqlOrderBy ?? "ORDER BY (SELECT NULL)", parts.SqlSelectRemoved, args.Length, args.Length + 1);
args = args.Concat(new object[] { skip, skip + take }).ToArray();
return(sqlPage);
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
if (parts.SqlSelectRemoved.StartsWith("*"))
{
throw new Exception("Query must alias '*' when performing a paged query.\neg. select t.* from table t order by t.id");
}
var helper = (PagingHelper)PagingUtility;
// when the query does not contain an "order by", it is very slow
if (simpleRegexOrderBy.IsMatch(parts.SqlSelectRemoved))
{
parts.SqlSelectRemoved = helper.RegexOrderBy.Replace(parts.SqlSelectRemoved, "", 1);
}
//if (helper.RegexDistinct.IsMatch(parts.SqlSelectRemoved))
//{
// parts.SqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.SqlSelectRemoved + ") peta_inner";
//}
var sqlPage = string.Empty;
if (helper.RegexDistinct.IsMatch(parts.SqlSelectRemoved))
{
string SqlSelectRemoved = parts.SqlSelectRemoved.Substring(parts.SqlSelectRemoved.IndexOf("DISTINCT", StringComparison.OrdinalIgnoreCase) + "DISTINCT".Length);
sqlPage = string.Format("SELECT * FROM (SELECT DISTINCT ROW_NUMBER() OVER ({0}) peta_rn,{1} ) peta_paged WHERE peta_rn > @{2} AND peta_rn <= @{3}", parts.SqlOrderBy ?? "ORDER BY (SELECT NULL)", SqlSelectRemoved, args.Length, args.Length + 1);
}
else
{
sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn > @{2} AND peta_rn <= @{3}", parts.SqlOrderBy ?? "ORDER BY (NULL)", parts.SqlSelectRemoved, args.Length, args.Length + 1);
}
args = args.Concat(new object[] { skip, skip + take }).ToArray();
return(sqlPage);
//Same deal as SQL Server
//return Singleton<SqlServerDatabaseProvider>.Instance.BuildPageQuery(skip, take, parts, ref args);
}
public static bool SplitSQL(string sql, out SQLParts parts)
{
parts.sql = sql;
parts.sqlSelectRemoved = null;
parts.sqlCount = null;
parts.sqlOrderBy = null;
// Extract the columns from "SELECT <whatever> FROM"
var m = rxColumns.Match(sql);
if (!m.Success)
return false;
// Save column list and replace with COUNT(*)
Group g = m.Groups[1];
parts.sqlSelectRemoved = sql.Substring(g.Index);
if (rxDistinct.IsMatch(parts.sqlSelectRemoved))
parts.sqlCount = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
else
parts.sqlCount = sql.Substring(0, g.Index) + "COUNT(1) " + sql.Substring(g.Index + g.Length);
// Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
m = rxOrderBy.Match(parts.sqlCount);
if (!m.Success)
{
parts.sqlOrderBy = null;
}
else
{
g = m.Groups[0];
parts.sqlOrderBy = g.ToString();
parts.sqlCount = parts.sqlCount.Substring(0, g.Index) + parts.sqlCount.Substring(g.Index + g.Length);
}
return true;
}
public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
throw new NotSupportedException("The Access provider does not support paging.");
}
public string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
{
var sql = string.Format("{0}\nLIMIT {1} OFFSET {2}", parts.Sql, take, skip);
return(sql);
}
public bool SplitSql(string sql, out SQLParts parts)
{
throw new System.NotImplementedException();
}
private void UpdateDataGridResult(SQLParts sqls)
{
Db2SourceContext ctx = CurrentDataSet;
using (IDbConnection conn = ctx.NewConnection(true))
{
bool modified;
Parameters = ParameterStore.GetParameterStores(sqls.ParameterNames, Parameters, out modified);
if (modified)
{
return;
}
foreach (ParameterStore p in Parameters)
{
if (p.IsError)
{
dataGridParameters.Focus();
dataGridParameters.SelectedItem = p;
MessageBox.Show(string.Format("パラメータ{0}の値が不正です", p.ParameterName), "エラー", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}
foreach (SQLPart sql in sqls.Items)
{
IDbCommand cmd = ctx.GetSqlCommand(sql.SQL, Command_Log, conn);
ParameterStoreCollection stores = ParameterStore.GetParameterStores(cmd, Parameters, out modified);
DateTime start = DateTime.Now;
try
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataGridControllerResult.Load(reader);
if (0 <= reader.RecordsAffected)
{
AddLog(string.Format("{0}行反映しました。", reader.RecordsAffected), null, null, LogStatus.Normal, true);
}
else if (0 < reader.FieldCount)
{
tabControlResult.SelectedItem = tabItemDataGrid;
}
}
AddToHistory(sql, stores);
}
catch (Exception t)
{
Tuple <int, int> errPos = CurrentDataSet.GetErrorPosition(t, sql.SQL, 0);
AddLog(CurrentDataSet.GetExceptionMessage(t), sql.SQL, stores, LogStatus.Error, true, errPos);
Db2SrcDataSetController.ShowErrorPosition(t, textBoxSql, CurrentDataSet, sql.Offset);
return;
}
finally
{
ParameterStore.GetParameterStores(cmd, stores, out modified);
UpdateDataGridParameters();
DateTime end = DateTime.Now;
TimeSpan time = end - start;
string s = string.Format("{0}:{1:00}:{2:00}.{3:000}", (int)time.TotalHours, time.Minutes, time.Seconds, time.Milliseconds);
AddLog(string.Format("実行しました (所要時間 {0})", s), cmd.CommandText, stores, LogStatus.Aux, false);
textBlockGridResult.Text = string.Format("{0}件見つかりました。 所要時間 {1}", DataGridControllerResult.Rows.Count, s);
}
}
}
}
