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()); }