/// <summary>
/// Default implementation of a Get Query
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <param name="cache">true if this query should be cached</param>
/// <returns>A sql statement that selects a single item</returns>
public virtual string GetQuery(TableInfo tableInfo, string sql, bool cache = false)
{
var q = new SqlParser(sql ?? "");
if (q.Sql.StartsWith(";"))
{
return(q.Sql.Substring(1));
}
if (!q.IsSelect)
{
return(GetQueries.Acquire(
tableInfo.ClassType.TypeHandle,
() => cache && string.IsNullOrEmpty(q.Sql),
() =>
{
var wc = string.IsNullOrWhiteSpace(q.Sql) ? $"where {EscapeWhereList(tableInfo.KeyColumns)}" : q.Sql;
if (string.IsNullOrEmpty(q.FromClause))
{
return $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} from { EscapeTableName(tableInfo)} {wc}";
}
else
{
return $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} {wc}";
}
}
));
}
return(q.Sql);
}
/// <summary>
/// Constructs a paged sql statement
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="page">the page to request</param>
/// <param name="pageSize">the size of the page to request</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <param name="parameters">the dynamic parameters for the query</param>
/// <returns>A paginated sql statement</returns>
public override string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql,
DynamicParameters parameters)
{
var q = new SqlParser(GetListQuery(tableInfo, sql));
var pageSkip = (page - 1) * pageSize;
var pageTake = pageSkip + pageSize;
var sqlOrderBy = "order by (select null)";
var sqlOrderByRemoved = q.Sql;
if (string.IsNullOrEmpty(q.OrderByClause))
{
if (tableInfo.KeyColumns.Any())
{
sqlOrderBy = $"order by {EscapeColumn(tableInfo.KeyColumns.First().PropertyName)}";
}
}
else
{
sqlOrderBy = q.OrderByClause;
sqlOrderByRemoved = sqlOrderByRemoved.Replace(q.OrderByClause, "");
}
var columnsOnly = $"page_inner.* FROM ({sqlOrderByRemoved}) page_inner";
parameters.Add(PageSizeParamName, pageTake, DbType.Int64);
parameters.Add(PageSkipParamName, pageSkip, DbType.Int64);
return
($"select * from (select row_number() over ({sqlOrderBy}) page_rn, {columnsOnly}) page_outer where page_rn > {EscapeParameter(PageSkipParamName)} and page_rn <= {EscapeParameter(PageSizeParamName)}");
}
/// <summary>
/// Default implementation of a delete query
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <returns>A sql statement that deletes</returns>
public virtual string DeleteQuery(TableInfo tableInfo, string sql)
{
var q = new SqlParser(sql ?? string.Empty);
if (q.Sql.StartsWith(";"))
{
return(q.Sql.Substring(1));
}
//Remove any order by statements
if (!string.IsNullOrEmpty(q.OrderByClause))
{
q.Sql = q.Sql.Replace(q.OrderByClause, string.Empty);
q.OrderByClause = string.Empty;
}
//Partial statement passed in
if (!q.IsDelete)
{
return(string.IsNullOrEmpty(q.FromClause)
? $"delete from {EscapeTableName(tableInfo)} {q.Sql}"
: $"delete {q.Sql}");
}
return($"delete from ({q.Sql}) calc_inner");
}
/// <summary>
/// Default implementation of a count query
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <returns>A sql statement that selects the count of matching records</returns>
public virtual string CountQuery(TableInfo tableInfo, string sql)
{
var q = new SqlParser(sql ?? "");
if (q.Sql.StartsWith(";"))
{
return(q.Sql.Substring(1));
}
if (!string.IsNullOrEmpty(q.OrderByClause))
{
q.Sql = q.Sql.Replace(q.OrderByClause, "");
}
if (!q.IsSelect)
{
if (string.IsNullOrEmpty(q.FromClause))
{
return($"select count(*) from {EscapeTableName(tableInfo)} {q.Sql}");
}
return($"select count(*) {q.Sql}");
}
return($"select count(*) from ({q.Sql}) count_inner");
}
/// <summary>
/// Default implementation of an Exists query
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <returns>A sql statement that selects true if a record matches</returns>
public override string ExistsQuery(TableInfo tableInfo, string sql)
{
var q = new SqlParser(sql ?? "");
if (q.Sql.StartsWith(";"))
{
return(q.Sql.Substring(1));
}
if (!q.IsSelect)
{
var wc = string.IsNullOrWhiteSpace(q.Sql) ? $"where {EscapeWhereList(tableInfo.KeyColumns)}" : q.Sql;
if (string.IsNullOrEmpty(q.FromClause))
{
return($"select exists (select * from { EscapeTableName(tableInfo)} {wc})");
}
else
{
return($"select exists (select * {wc})");
}
}
return($"select exists ({q.Sql})");
}
/// <summary>
/// Default implementation of a delete query
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <returns>A sql statement that deletes</returns>
public virtual string DeleteQuery(TableInfo tableInfo, string sql)
{
var q = new SqlParser(sql ?? "");
if (q.Sql.StartsWith(";"))
{
return(q.Sql.Substring(1));
}
if (!string.IsNullOrEmpty(q.OrderByClause))
{
q.Sql = q.Sql.Replace(q.OrderByClause, "");
}
if (!q.IsDelete)
{
if (string.IsNullOrEmpty(q.FromClause))
{
return($"delete from { EscapeTableName(tableInfo)} {q.Sql}");
}
else
{
return($"delete {q.Sql}");
}
}
return($"delete from ({q.Sql}) calc_inner");
}
/// <summary>
/// Default implementation of a a paged sql statement
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="page">the page to request</param>
/// <param name="pageSize">the size of the page to request</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <returns>A paginated sql statement</returns>
public virtual string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql)
{
var q = new SqlParser(GetListQuery(tableInfo, sql));
var pageSkip = (page - 1) * pageSize;
var sqlOrderBy = string.Empty;
if (string.IsNullOrEmpty(q.OrderByClause) && tableInfo.KeyColumns.Any())
{
sqlOrderBy = $"order by {EscapeColumnn(tableInfo.KeyColumns.First().ColumnName)}";
}
return($"{q.Sql} {sqlOrderBy} limit {pageSize} offset {pageSkip}");
}
/// <summary>
/// Default implementation of a a paged sql statement
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="page">the page to request</param>
/// <param name="pageSize">the size of the page to request</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <returns>A paginated sql statement</returns>
public override string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql)
{
var q = new SqlParser(GetListQuery(tableInfo, sql));
var pageSkip = (page - 1) * pageSize;
var sqlOrderBy = string.Empty;
if (string.IsNullOrEmpty(q.OrderByClause) && tableInfo.KeyColumns.Any())
{
sqlOrderBy = $"order by {EscapeColumnn(tableInfo.KeyColumns.First().ColumnName)}";
}
return($"select first {pageSize} skip {pageSkip} * from ({q.Sql}) page_inner {sqlOrderBy}");
}
/// <summary>
/// Default implementation of an Exists query
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <returns>A sql statement that selects true if a record matches</returns>
public override string ExistsQuery(TableInfo tableInfo, string sql)
{
var q = new SqlParser(sql ?? "");
if (q.Sql.StartsWith(";"))
{
return(q.Sql.Substring(1));
}
if (!q.IsSelect)
{
return(string.IsNullOrEmpty(q.FromClause)
? $"select first 1 1 from {EscapeTableName(tableInfo)} where exists (select 1 from {EscapeTableName(tableInfo)} {q.Sql});"
: $"select first 1 1 from {EscapeTableName(tableInfo)} where exists (select 1 {q.Sql});");
}
return($"select first 1 1 from {EscapeTableName(tableInfo)} where exists ({q.Sql});");
}
/// <summary>
/// Default implementation of a Get List query
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <returns>A sql statement</returns>
public virtual string GetListQuery(TableInfo tableInfo, string sql)
{
var q = new SqlParser(sql ?? "");
if (q.Sql.StartsWith(";"))
{
return(q.Sql.Substring(1));
}
if (!q.IsSelect)
{
return(string.IsNullOrEmpty(q.FromClause)
? $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} from {EscapeTableName(tableInfo)} {q.Sql}"
: $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} {q.Sql}");
}
return(q.Sql);
}
/// <summary>
/// Default implementation of an Exists query
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <returns>A sql statement that selects true if a record matches</returns>
public virtual string ExistsQuery(TableInfo tableInfo, string sql)
{
var q = new SqlParser(sql ?? "");
if (q.Sql.StartsWith(";"))
{
return(q.Sql.Substring(1));
}
if (!q.IsSelect)
{
return(string.IsNullOrEmpty(q.FromClause)
? $"select 1 where exists (select 1 from {EscapeTableName(tableInfo)} {q.Sql})"
: $"select 1 where exists (select 1 {q.Sql})");
}
return($"select 1 from ({q.Sql}) calc_inner");
}
/// <inheritdoc />
public override string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql,
DynamicParameters parameters)
{
var sqlParser = new SqlParser(GetListQuery(tableInfo, sql));
var pageSkip = (page - 1) * pageSize;
var sqlOrderBy = string.Empty;
if (string.IsNullOrEmpty(sqlParser.OrderByClause) && tableInfo.KeyColumns.Any())
{
sqlOrderBy = $"order by {EscapeColumn(tableInfo.KeyColumns.First().PropertyName)}";
}
parameters.Add(PageSizeParamName, pageSize, DbType.Int64);
parameters.Add(PageSkipParamName, pageSkip, DbType.Int64);
return
($"{sqlParser.Sql} {sqlOrderBy} offset {EscapeParameter(PageSkipParamName)} rows fetch next {EscapeParameter(PageSizeParamName)} rows only");
}
/// <summary>
/// Oracle-specific implementation of an Exists query.
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <returns>A sql statement that selects true if a record matches</returns>
public override string ExistsQuery(TableInfo tableInfo, string sql)
{
var sqlParser = new SqlParser(sql ?? "");
if (sqlParser.Sql.StartsWith(";", StringComparison.Ordinal))
{
return(sqlParser.Sql.Substring(1));
}
if (!sqlParser.IsSelect)
{
return(string.IsNullOrEmpty(sqlParser.FromClause)
? $"select case when exists (select * from {EscapeTableName(tableInfo)} {sqlParser.Sql}) then 1 else 0 end as rec_exists from dual"
: $"select case when exists (select * {sqlParser.Sql}) then 1 else 0 end as rec_exists from dual");
}
return($"select case when exists ({sqlParser.Sql}) then 1 else 0 end as rec_exists from dual");
}
/// <summary>
/// Default implementation of a a paged sql statement
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="page">the page to request</param>
/// <param name="pageSize">the size of the page to request</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <param name="parameters">the dynamic parameters for the query</param>
/// <returns>A paginated sql statement</returns>
/// <remarks>
/// Base implementation does not modify <paramref name="parameters" />.
/// </remarks>
public virtual string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql,
DynamicParameters parameters)
{
var q = new SqlParser(GetListQuery(tableInfo, sql));
var pageSkip = (page - 1) * pageSize;
var sqlOrderBy = string.Empty;
if (string.IsNullOrEmpty(q.OrderByClause) && tableInfo.KeyColumns.Any())
{
sqlOrderBy = $"order by {EscapeColumn(tableInfo.KeyColumns.First().ColumnName)}";
}
parameters.Add(PageSizeParamName, pageSize, DbType.Int64);
parameters.Add(PageSkipParamName, pageSkip, DbType.Int64);
return
($"{q.Sql} {sqlOrderBy} limit {EscapeParameter(PageSizeParamName)} offset {EscapeParameter(PageSkipParamName)}");
}
/// <summary>
/// Default implementation of a Get Query
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <param name="cache">true if this query should be cached</param>
/// <returns>A sql statement that selects a single item</returns>
public virtual string GetQuery(TableInfo tableInfo, string sql, bool cache = false)
{
var q = new SqlParser(sql ?? "");
if (q.Sql.StartsWith(";"))
{
return(q.Sql.Substring(1));
}
if (!q.IsSelect)
{
return(GetQueries.Acquire(
tableInfo.ClassType.TypeHandle,
() => cache && string.IsNullOrEmpty(q.Sql),
() => string.IsNullOrEmpty(q.FromClause)
? $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} from {EscapeTableName(tableInfo)} {q.Sql}"
: $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} {q.Sql}"));
}
return(q.Sql);
}
/// <summary>
/// Default implementation of a a paged sql statement
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="page">the page to request</param>
/// <param name="pageSize">the size of the page to request</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <param name="parameters">the dynamic parameters for the query</param>
/// <returns>A paginated sql statement</returns>
public override string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql,
DynamicParameters parameters)
{
var q = new SqlParser(GetListQuery(tableInfo, sql));
var pageSkip = (page - 1) * pageSize;
var sqlOrderBy = string.Empty;
if (string.IsNullOrEmpty(q.OrderByClause) && tableInfo.KeyColumns.Any())
{
sqlOrderBy = $"order by {EscapeColumn(tableInfo.KeyColumns.First().PropertyName)}";
}
parameters.Add(PageSizeParamName, pageSize, DbType.Int64);
parameters.Add(PageSkipParamName, pageSkip, DbType.Int64);
return
($"select first {EscapeParameter(PageSizeParamName)} skip {EscapeParameter(PageSkipParamName)} * from ({q.Sql}) page_inner {sqlOrderBy}");
}
/// <summary>
/// Default implementation of a Get List query
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <returns>A sql statement</returns>
public virtual string GetListQuery(TableInfo tableInfo, string sql)
{
var q = new SqlParser(sql ?? "");
if (q.Sql.StartsWith(";"))
{
return(q.Sql.Substring(1));
}
if (!q.IsSelect)
{
var wc = string.IsNullOrWhiteSpace(q.Sql) ? $"where {EscapeWhereList(tableInfo.KeyColumns)}" : q.Sql;
if (string.IsNullOrEmpty(q.FromClause))
{
return($"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} from { EscapeTableName(tableInfo)} {wc}");
}
else
{
return($"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} {wc}");
}
}
return(q.Sql);
//var q = sql ?? "";
//if (q.StartsWith(";"))
// return q.Substring(1);
//if (!rxSelect.IsMatch(q))
//{
// var wc = string.IsNullOrWhiteSpace(q) ? $"where {EscapeWhereList(tableInfo.KeyColumns)}" : q;
// if (!rxFrom.IsMatch(q))
// return $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} from { EscapeTableName(tableInfo)} {wc}";
// else
// return $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} {wc}";
//}
//return sql;
}
/// <summary>
/// Constructs a paged sql statement
/// </summary>
/// <param name="tableInfo">table information about the entity</param>
/// <param name="page">the page to request</param>
/// <param name="pageSize">the size of the page to request</param>
/// <param name="sql">a sql statement or partial statement</param>
/// <returns>A paginated sql statement</returns>
public override string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql)
{
var q = new SqlParser(GetListQuery(tableInfo, sql));
var pageSkip = (page - 1) * pageSize;
var sqlOrderBy = string.Empty;
if (string.IsNullOrEmpty(q.OrderByClause) && tableInfo.KeyColumns.Any())
{
sqlOrderBy = $"order by {EscapeColumnn(tableInfo.KeyColumns.First().ColumnName)}";
}
//return $"{q.Sql} {sqlOrderBy} limit {pageSize} offset {pageSkip}";
return($"{q.Sql} {sqlOrderBy} offset {pageSkip} rows fetch next {pageSize} rows only");
//var selectQuery = GetListQuery(tableInfo, sql);
//var m = rxColumns.Match(selectQuery);
//var g = m.Groups[1];
//var sqlSelectRemoved = selectQuery.Substring(g.Index);
//var sqlOrderBy = string.Empty;
//var pageSkip = (page - 1) * pageSize;
//m = rxOrderBy.Match(selectQuery);
//if (m.Success)
//{
// g = m.Groups[0];
// sqlOrderBy = g.ToString();
//}
//else if (tableInfo.KeyColumns.Any())
//{
// sqlOrderBy = $"order by {EscapeColumnn(tableInfo.KeyColumns.First().ColumnName)}";
//}
//return $"select {rxOrderBy.Replace(sqlSelectRemoved, "", 1)} {sqlOrderBy} offset {pageSkip} rows fetch next {pageSize} rows only";
}