private static SqlString GetProjectionArgument(ICriteriaQuery criteriaQuery, ICriteria criteria,
IProjection projection, int loc)
{
SqlString sql = projection.ToSqlString(criteria, loc, criteriaQuery);
return(sql.Substring(0, sql.LastIndexOfCaseInsensitive(" as ")));
}
public override SqlString OnPrepareStatement(SqlString sql)
{
if (sql.LastIndexOfCaseInsensitive("Cats") != -1)
{
throw new InvalidOperationException("Can't don't like queries, curisoity killed the cat");
}
return(base.OnPrepareStatement(sql));
}
/**
* 01.06.2020: Parameter enabledFilters removed
*/
private static SqlString GetProjectionArgument(ICriteriaQuery criteriaQuery, ICriteria criteria,
IProjection projection, int loc /*,
* IDictionary<string, IFilter> enabledFilters*/)
{
/**
* 01.06.2020: Parameter enabledFilters removed
*/
SqlString sql = projection.ToSqlString(criteria, loc, criteriaQuery /*, enabledFilters*/);
return(sql.Substring(0, sql.LastIndexOfCaseInsensitive(" as ")));
}
private SqlString GetLimitString(SqlString querySqlString, object offset, object limit)
{
if (offset == null && limit == null)
{
return(querySqlString);
}
SqlStringBuilder result = new SqlStringBuilder();
if (offset == null)
{
int insertPoint = this.GetAfterSelectInsertPoint(querySqlString);
return(result
.Add(querySqlString.Substring(0, insertPoint))
.Add(" TOP (")
.AddObject(limit)
.Add(") ")
.Add(querySqlString.Substring(insertPoint))
.ToSqlString());
}
int fromIndex = GetFromIndex(querySqlString);
SqlString select = querySqlString.Substring(0, fromIndex);
List <SqlString> columnsOrAliases;
Dictionary <SqlString, SqlString> aliasToColumn;
ExtractColumnOrAliasNames(select, out columnsOrAliases, out aliasToColumn);
int orderIndex = querySqlString.LastIndexOfCaseInsensitive(" order by ");
SqlString fromAndWhere;
SqlString[] sortExpressions;
//don't use the order index if it is contained within a larger statement(assuming
//a statement with non matching parenthesis is part of a larger block)
if (orderIndex > 0 && HasMatchingParens(querySqlString.Substring(orderIndex).ToString()))
{
fromAndWhere = querySqlString.Substring(fromIndex, orderIndex - fromIndex).Trim();
SqlString orderBy = querySqlString.Substring(orderIndex).Trim();
sortExpressions = orderBy.Substring(9).Split(",");
}
else
{
fromAndWhere = querySqlString.Substring(fromIndex).Trim();
// Use dummy sort to avoid errors
sortExpressions = new[] { new SqlString("CURRENT_TIMESTAMP"), };
}
result.Add("SELECT ");
if (limit != null)
{
result.Add("TOP (").AddObject(limit).Add(") ");
}
result
.Add(StringHelper.Join(", ", columnsOrAliases))
.Add(" FROM (")
.Add(select)
.Add(", ROW_NUMBER() OVER(ORDER BY ");
AppendSortExpressions(aliasToColumn, sortExpressions, result);
result
.Add(") as __hibernate_sort_row ")
.Add(fromAndWhere)
.Add(") as query WHERE query.__hibernate_sort_row > ")
.AddObject(offset)
.Add(" ORDER BY query.__hibernate_sort_row");
return(result.ToSqlString());
}
/// <summary>
/// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>
/// </summary>
/// <param name="querySqlString">The <see cref="SqlString"/> to base the limit query off of.</param>
/// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param>
/// <param name="last">Maximum number of rows to be returned by the query</param>
/// <returns>A new <see cref="SqlString"/> with the <c>LIMIT</c> clause applied.</returns>
/// <remarks>
/// The <c>LIMIT</c> SQL will look like
/// <code>
///
/// SELECT
/// TOP last (columns)
/// FROM
/// (SELECT (columns), ROW_NUMBER() OVER(ORDER BY {original order by, with un-aliased column names) as __hibernate_sort_row
/// {original from}) as query
/// WHERE query.__hibernate_sort_row > offset
/// ORDER BY query.__hibernate_sort_row
///
/// </code>
///
/// Note that we need to add explicitly specify the columns, because we need to be able to use them
/// in a paged subselect. NH-1155
/// </remarks>
public override SqlString GetLimitString(SqlString querySqlString, int offset, int last)
{
//dont do this paging code if there is no offset, use the
//sql 2000 dialect since it wont just uses a top statement
if (offset == 0)
{
return(base.GetLimitString(querySqlString, offset, last));
}
// we have to do this in order to support parameters in order clause, the foramt
// that sql 2005 uses for paging means that we move the parameters around, which means,
// that positions are lost, so we record them before making any changes.
// NH-1528
int parameterPositon = 0;
foreach (var part in querySqlString.Parts)
{
Parameter param = part as Parameter;
if (param == null)
{
continue;
}
param.OriginalPositionInQuery = parameterPositon;
parameterPositon += 1;
}
int fromIndex = GetFromIndex(querySqlString);
SqlString select = querySqlString.Substring(0, fromIndex);
List <SqlString> columnsOrAliases;
Dictionary <SqlString, SqlString> aliasToColumn;
ExtractColumnOrAliasNames(select, out columnsOrAliases, out aliasToColumn);
int orderIndex = querySqlString.LastIndexOfCaseInsensitive(" order by ");
SqlString from;
SqlString[] sortExpressions;
//don't use the order index if it is contained within a larger statement(assuming
//a statement with non matching parenthesis is part of a larger block)
if (orderIndex > 0 && HasMatchingParens(querySqlString.Substring(orderIndex).ToString()))
{
from = querySqlString.Substring(fromIndex, orderIndex - fromIndex).Trim();
SqlString orderBy = querySqlString.Substring(orderIndex).Trim();
sortExpressions = orderBy.Substring(9).Split(",");
}
else
{
from = querySqlString.Substring(fromIndex).Trim();
// Use dummy sort to avoid errors
sortExpressions = new[] { new SqlString("CURRENT_TIMESTAMP"), };
}
SqlStringBuilder result =
new SqlStringBuilder()
.Add("SELECT TOP ")
.Add(last.ToString())
.Add(" ")
.Add(StringHelper.Join(", ", columnsOrAliases))
.Add(" FROM (")
.Add(select)
.Add(", ROW_NUMBER() OVER(ORDER BY ");
AppendSortExpressions(aliasToColumn, sortExpressions, result);
result.Add(") as __hibernate_sort_row ")
.Add(from)
.Add(") as query WHERE query.__hibernate_sort_row > ")
.Add(offset.ToString()).Add(" ORDER BY query.__hibernate_sort_row");
return(result.ToSqlString());
}
public static SqlString RemoveAsAliasesFromSql(SqlString sql)
{
return(sql.Substring(0, sql.LastIndexOfCaseInsensitive(" as ")));
}
private SqlString PageByLimitAndOffset(SqlString offset, SqlString limit)
{
int fromIndex = GetFromIndex();
SqlString select = _sourceQuery.Substring(0, fromIndex);
List <SqlString> columnsOrAliases;
Dictionary <SqlString, SqlString> aliasToColumn;
Dictionary <SqlString, SqlString> columnToAlias;
Dialect.ExtractColumnOrAliasNames(select, out columnsOrAliases, out aliasToColumn, out columnToAlias);
int orderIndex = _sourceQuery.LastIndexOfCaseInsensitive(" order by ");
SqlString fromAndWhere;
SqlString[] sortExpressions;
//don't use the order index if it is contained within a larger statement(assuming
//a statement with non matching parenthesis is part of a larger block)
if (orderIndex > 0 && HasMatchingParens(_sourceQuery.Substring(orderIndex).ToString()))
{
fromAndWhere = _sourceQuery.Substring(fromIndex, orderIndex - fromIndex).Trim();
SqlString orderBy = _sourceQuery.Substring(orderIndex).Trim().Substring(9);
sortExpressions = orderBy.SplitWithRegex(@"(?<!\([^\)]*),{1}");
}
else
{
fromAndWhere = _sourceQuery.Substring(fromIndex).Trim();
// Use dummy sort to avoid errors
sortExpressions = new[] { new SqlString("CURRENT_TIMESTAMP") };
}
var result = new SqlStringBuilder();
result.Add("SELECT ");
if (limit != null)
{
result.Add("TOP (").Add(limit).Add(") ");
}
else
{
// ORDER BY can only be used in subqueries if TOP is also specified.
result.Add("TOP (" + int.MaxValue + ") ");
}
if (IsDistinct())
{
result
.Add(StringHelper.Join(", ", columnsOrAliases))
.Add(" FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY ");
AppendSortExpressionsForDistinct(columnToAlias, sortExpressions, result);
result.Add(") as __hibernate_sort_row ")
.Add(" FROM (")
.Add(select)
.Add(" ")
.Add(fromAndWhere)
.Add(") as q_) as query WHERE query.__hibernate_sort_row > ")
.Add(offset)
.Add(" ORDER BY query.__hibernate_sort_row");
}
else
{
result
.Add(StringHelper.Join(", ", columnsOrAliases))
.Add(" FROM (")
.Add(select)
.Add(", ROW_NUMBER() OVER(ORDER BY ");
AppendSortExpressions(aliasToColumn, sortExpressions, result);
result
.Add(") as __hibernate_sort_row ")
.Add(fromAndWhere)
.Add(") as query WHERE query.__hibernate_sort_row > ")
.Add(offset)
.Add(" ORDER BY query.__hibernate_sort_row");
}
return(result.ToSqlString());
}