/// <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 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 = RegexColumns.Match(sql);
if (!m.Success)
return false;
// Save column list and replace with COUNT(*)
var g = m.Groups[1];
parts.SqlSelectRemoved = sql.Substring(g.Index);
if (RegexDistinct.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 = RegexOrderBy.Match(parts.SqlCount);
if (m.Success)
{
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)
{
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 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)
{
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;
}
/// <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 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 = RegexColumns.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 (RegexDistinct.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 = RegexOrderBy.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);
}
/// <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 bool SplitSQL(string sql, out SQLParts parts)
{
parts.Sql = sql;
parts.SqlSelectRemoved = null;
parts.SqlCount = sql;
parts.SqlOrderBy = null;
// Extract the columns from "SELECT <whatever> FROM"
var columnsMatch = RegexColumns.Match(sql);
if (!columnsMatch.Success)
{
return(false);
}
// Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
var orderByMatch = RegexOrderBy.Match(sql);
if (orderByMatch.Success)
{
parts.SqlOrderBy = orderByMatch.Value;
parts.SqlCount = sql.Replace(orderByMatch.Value, string.Empty);
}
// Save column list and replace with COUNT(*)
var columnsGroup = columnsMatch.Groups[1];
parts.SqlSelectRemoved = sql.Substring(columnsGroup.Index);
if (RegexDistinct.IsMatch(parts.SqlSelectRemoved) || SimpleRegexGroupBy.IsMatch(parts.SqlSelectRemoved))
{
parts.SqlCount = sql.Substring(0, columnsGroup.Index) + "COUNT(*) FROM (" + parts.SqlCount + ") countAlias";
}
else
{
parts.SqlCount = sql.Substring(0, columnsGroup.Index) + "COUNT(*) " + parts.SqlCount.Substring(columnsGroup.Index + columnsGroup.Length);
}
return(true);
}
/// <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;
}
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;
}
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)
{
throw new NotSupportedException("The Access provider does not support paging.");
}