public override SqlString OnPrepareStatement(SqlString sql) { Match match = Regex.Match(sql.ToString()); String tableName = match.Groups[1].Value; String tableAlias = match.Groups[2].Value; sql = sql.Substring(match.Groups[2].Index); sql = sql.Replace(tableAlias, tableName); sql = sql.Insert(0, "delete from "); Int32 orderByIndex = sql.IndexOfCaseInsensitive(" order by "); if (orderByIndex > 0) { sql = sql.Substring(0, orderByIndex); } int limitIndex = sql.IndexOfCaseInsensitive(" limit "); if (limitIndex > 0) { sql = sql.Substring(0, limitIndex); } return(sql); }
private static SqlString RemoveSortOrderDirection(SqlString sortExpression) { SqlString trimmedExpression = sortExpression.Trim(); if (trimmedExpression.EndsWithCaseInsensitive("asc")) { return(trimmedExpression.Substring(0, trimmedExpression.Length - 3).Trim()); } if (trimmedExpression.EndsWithCaseInsensitive("desc")) { return(trimmedExpression.Substring(0, trimmedExpression.Length - 4).Trim()); } return(trimmedExpression.Trim()); }
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 "))); }
/// <summary> /// Return customized limit string (for paging queries) /// For performance reason the limit string is ommitted when querying the first page. /// </summary> /// <param name="querySqlString"></param> /// <param name="offset"></param> /// <param name="last"></param> /// <returns></returns> public override NHibernate.SqlCommand.SqlString GetLimitString(NHibernate.SqlCommand.SqlString querySqlString, int offset, int last) { if (!querySqlString.StartsWithCaseInsensitive("select ")) { throw new ArgumentException("querySqlString should start with select", "querySqlString"); } SqlString sqlString = querySqlString.Substring(6); string orderSql = querySqlString.SubstringStartingWithLast("order by").ToString(); if (orderSql.Length != 0) { sqlString = sqlString.Substring(0, (sqlString.Length - orderSql.Length) - 1); } SqlStringBuilder builder = new SqlStringBuilder(); int num = offset + 1; builder.Add("SELECT TOP ").Add(last.ToString()).Add(" ").Add(sqlString); if (offset > 0) { builder.Add(" WITH FIRSTROW ").Add(num.ToString()); } if (orderSql.Length > 0) { builder.Add(" ").Add(orderSql); } return(builder.ToSqlString()); }
public void SubstringComplex() { SqlString str = new SqlString(new object[] { "select ", Parameter.Placeholder, " from table where x = ", Parameter.Placeholder }); SqlString substr7 = str.Substring(7); Assert.AreEqual( new SqlString(new object[] { Parameter.Placeholder, " from table where x = ", Parameter.Placeholder }), substr7); SqlString substr10 = str.Substring(10); Assert.AreEqual(new SqlString(new object[] { "rom table where x = ", Parameter.Placeholder }), substr10); Assert.AreEqual(SqlString.Empty, str.Substring(200)); }
/// <summary> /// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c> /// </summary> /// <param name="querySqlString">A Query in the form of a SqlString.</param> /// <param name="hasOffset">Offset of the first row is not zero</param> /// <returns>A new SqlString that contains the <c>LIMIT</c> clause.</returns> public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit, int?offsetParameterIndex, int?limitParameterIndex) { /* * "select * from (select row_number() over(orderby_clause) as rownum, " * querySqlString_without select * " ) as tempresult where rownum between ? and ?" */ string rownumClause = GetRowNumber(querySqlString); SqlStringBuilder pagingBuilder = new SqlStringBuilder(); pagingBuilder .Add("select * from (select ") .Add(rownumClause) .Add(querySqlString.Substring(7)) .Add(") as tempresult where rownum "); if (offset > 0) { pagingBuilder .Add("between ") .Add(Parameter.WithIndex(offsetParameterIndex.Value)) .Add("+1 and ") .Add(Parameter.WithIndex(limitParameterIndex.Value)); } else { pagingBuilder .Add("<= ") .Add(Parameter.WithIndex(limitParameterIndex.Value)); } return(pagingBuilder.ToSqlString()); }
/// <summary> /// SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ] /// for LIMIT/OFFSET support. /// /// Produce a parametertized SQL query using positional parameters for /// TOP and START AT (if specified). /// </summary> public override SqlString GetLimitString(SqlString sql, bool hasOffset) { int InsertionPoint = GetAfterSelectInsertPoint(sql); if (InsertionPoint > 0) { SqlStringBuilder LimitBuilder = new SqlStringBuilder(); LimitBuilder.Add("SELECT"); if (InsertionPoint > 6) { LimitBuilder.Add(" DISTINCT "); } LimitBuilder.Add(" TOP "); LimitBuilder.Add(Parameter.Placeholder); if (hasOffset) { LimitBuilder.Add(" START AT "); LimitBuilder.Add(Parameter.Placeholder); } LimitBuilder.Add(sql.Substring(InsertionPoint)); return(LimitBuilder.ToSqlString()); } else { return(sql); // unchanged } }
public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit) { // SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ] // for LIMIT/OFFSET support. Does not support a limit of zero. // FIXME - Add support for where offset is set, but limit is not. int insertionPoint = GetAfterSelectInsertPoint(sql); if (insertionPoint > 0) { SqlStringBuilder limitBuilder = new SqlStringBuilder(); limitBuilder.Add("select"); if (insertionPoint > 6) { limitBuilder.Add(" distinct "); } limitBuilder.Add(" top "); limitBuilder.Add(limit); if (offset != null) { limitBuilder.Add(" start at "); limitBuilder.Add(offset); } limitBuilder.Add(sql.Substring(insertionPoint)); return(limitBuilder.ToSqlString()); } else { return(sql); // unchanged } }
/// <summary> /// SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ] /// for LIMIT/OFFSET support. /// /// Produce a parametertized SQL query using positional parameters for /// TOP and START AT (if specified). /// </summary> public override SqlString GetLimitString(SqlString sql, bool hasOffset) { int insertionPoint = GetAfterSelectInsertPoint(sql); if (insertionPoint > 0) { SqlStringBuilder limitBuilder = new SqlStringBuilder(); limitBuilder.Add("select"); if (insertionPoint > 6) { limitBuilder.Add(" distinct "); } limitBuilder.Add(" top "); limitBuilder.Add(Parameter.Placeholder); if (hasOffset) { limitBuilder.Add(" start at "); limitBuilder.Add(Parameter.Placeholder); } limitBuilder.Add(sql.Substring(insertionPoint)); return(limitBuilder.ToSqlString()); } else { return(sql); // unchanged } }
/// <summary> /// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c> /// </summary> /// <param name="querySqlString">A Query in the form of a SqlString.</param> /// <param name="hasOffset">Offset of the first row is not zero</param> /// <returns>A new SqlString that contains the <c>LIMIT</c> clause.</returns> public override SqlString GetLimitString(SqlString querySqlString, bool hasOffset) { /* * "select * from (select row_number() over(orderby_clause) as rownum, " * querySqlString_without select * " ) as tempresult where rownum between ? and ?" */ string rownumClause = GetRowNumber(querySqlString); SqlStringBuilder pagingBuilder = new SqlStringBuilder(); pagingBuilder .Add("select * from (select ") .Add(rownumClause) .Add(querySqlString.Substring(7)) .Add(") as tempresult where rownum "); if (hasOffset) { pagingBuilder .Add("between ") .Add(Parameter.Placeholder) .Add("+1 and ") .Add(Parameter.Placeholder); } else { pagingBuilder .Add("<= ") .Add(Parameter.Placeholder); } return(pagingBuilder.ToSqlString()); }
/** * 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 "))); }
public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit) { var result = new SqlStringBuilder(); if (offset == null) { var insertPoint = GetAfterSelectInsertPoint(queryString); result .Add(queryString.Substring(0, insertPoint)) .Add(" TOP (") .Add(limit) .Add(") ") .Add(queryString.Substring(insertPoint)); return(result.ToSqlString()); } return(base.GetLimitString(queryString, offset, limit)); }
private static SqlString ProcessFromFragment(SqlString frag, JoinSequence join) { SqlString fromFragment = frag.Trim(); // The FROM fragment will probably begin with ', '. Remove this if it is present. if (fromFragment.StartsWithCaseInsensitive(", ")) { fromFragment = fromFragment.Substring(2); } return(fromFragment); }
public void HashcodeEqualForEqualStringsWithDifferentHistory() { // Verify that sql strings that are generated in different ways, but _now_ have // equal content, also have equal hashcodes. SqlString sql = new SqlString(new string[] { "select", " from table" }); sql = sql.Substring(6); SqlString sql2 = new SqlString(new string[] { " from table" }); Assert.That(sql, Is.EqualTo(sql2)); Assert.That(sql.GetHashCode(), Is.EqualTo(sql2.GetHashCode())); }
public void Split() { SqlString sql = new SqlString(new string[] { "select", " alfa, beta, gamma", " from table" }); var parts1 = sql.Split(",").Select(s => s.ToString()).ToArray(); var expectedParts1 = new[] { "select alfa", " beta", " gamma from table" }; Assert.That(parts1, Is.EqualTo(expectedParts1)); SqlString sql2 = sql.Substring(6); var parts2 = sql2.Split(",").Select(s => s.ToString()).ToArray(); var expectedParts2 = new[] { " alfa", " beta", " gamma from table" }; Assert.That(parts2, Is.EqualTo(expectedParts2)); }
public void Substring() { SqlStringBuilder builder = new SqlStringBuilder(); Parameter p = Parameter.Placeholder; builder.Add(" select from table"); builder.Add(" where p = "); builder.Add(p); SqlString sql = builder.ToSqlString(); sql = sql.Substring(1); Assert.AreEqual("select from table where p = ?", sql.ToString()); }
private SqlString PageByLimitOnly(SqlString limit) { var result = new SqlStringBuilder(); int insertPoint = GetAfterSelectInsertPoint(); return(result .Add(_sourceQuery.Substring(0, insertPoint)) .Add(" TOP (") .Add(limit) .Add(") ") .Add(_sourceQuery.Substring(insertPoint)) .ToSqlString()); }
public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit) { sql = sql.Trim(); bool isForUpdate = false; if (sql.EndsWithCaseInsensitive(" for update")) { sql = sql.Substring(0, sql.Length - 11); isForUpdate = true; } string selectColumns = ExtractColumnOrAliasNames(sql); var pagingSelect = new SqlStringBuilder(sql.Count + 10); if (offset != null) { pagingSelect.Add("select " + selectColumns + " from ( select row_.*, rownum rownum_ from ( "); } else { pagingSelect.Add("select " + selectColumns + " from ( "); } pagingSelect.Add(sql); if (offset != null && limit != null) { pagingSelect.Add(" ) row_ where rownum <=").Add(limit).Add(") where rownum_ >").Add(offset); } else if (limit != null) { pagingSelect.Add(" ) where rownum <=").Add(limit); } else { // offset is specified, but limit is not. pagingSelect.Add(" ) row_ ) where rownum_ >").Add(offset); } if (isForUpdate) { pagingSelect.Add(" for update"); } return(pagingSelect.ToSqlString()); }
private static SqlString GetWhereJoinFragment(IJoinable persister, string tableAlias) { SqlString whereJoinFragment = persister.WhereJoinFragment(tableAlias, true, false); if (whereJoinFragment == null) { whereJoinFragment = SqlString.Empty; } else { whereJoinFragment = whereJoinFragment.Trim(); if (whereJoinFragment.StartsWithCaseInsensitive("and ")) { whereJoinFragment = whereJoinFragment.Substring(4); } } return(whereJoinFragment); }
public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit) { if (offset == null) { return(new SqlString(sql, " fetch first ", limit, " rows only")); } ExtractColumnOrAliasNames(sql, out var selectColumns, out _, out _); /* * "select * from (select row_number() over(orderby_clause) as rownum, " * querySqlString_without select * " ) as tempresult where rownum between ? and ?" */ string rownumClause = GetRowNumber(sql); SqlStringBuilder pagingBuilder = new SqlStringBuilder(); pagingBuilder .Add("select ") .Add(string.Join(",", selectColumns)) .Add(" from (select ") .Add(rownumClause) .Add(sql.Substring(7)) .Add(") as tempresult where rownum "); if (limit != null) { pagingBuilder .Add("between ") .Add(offset) .Add("+1 and ") .Add(limit); } else { // We just have an offset. pagingBuilder .Add("> ") .Add(offset); } return(pagingBuilder.ToSqlString()); }
public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit) { if (offset == null) { return(new SqlString(querySqlString, " fetch first ", limit, " rows only")); } /* * "select * from (select row_number() over(orderby_clause) as rownum, " * querySqlString_without select * " ) as tempresult where rownum between ? and ?" */ string rownumClause = GetRowNumber(querySqlString); SqlStringBuilder pagingBuilder = new SqlStringBuilder(); pagingBuilder .Add("select * from (select ") .Add(rownumClause) .Add(querySqlString.Substring(7)) .Add(") as tempresult where rownum "); if (limit != null) { pagingBuilder .Add("between ") .Add(offset) .Add("+1 and ") .Add(limit); } else { // We just have an offset. pagingBuilder .Add("> ") .Add(offset); } return(pagingBuilder.ToSqlString()); }
public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit) { // SQL Anywhere uses SELECT TOP n START AT m [ select list items ] // for LIMIT/OFFSET support. Does not support a limit of zero. var insertionPoint = GetAfterSelectInsertPoint(sql); if (insertionPoint > 0) { if (limit == null && offset == null) { throw new ArgumentException("Cannot limit with neither a limit nor an offset"); } var limitBuilder = new SqlStringBuilder(); limitBuilder.Add("select"); if (insertionPoint > 6) { limitBuilder.Add(" distinct "); } limitBuilder.Add(" top "); if (limit != null) { limitBuilder.Add(limit); } else { // This seems supported since SQL Anywhere 12.0.1 only. No reference found for previous version, // included 12.0.0. limitBuilder.Add("all "); } if (offset != null) { limitBuilder.Add(" start at "); limitBuilder.Add(offset); } limitBuilder.Add(sql.Substring(insertionPoint)); return(limitBuilder.ToSqlString()); } return(sql); // unchanged }
public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit) { // SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ] // for LIMIT/OFFSET support. Does not support a limit of zero. // FIXME - Add support for where offset is set, but limit is not. int insertionPoint = GetAfterSelectInsertPoint(sql); if (insertionPoint > 0) { if (limit == null && offset == null) { throw new ArgumentException("Cannot limit with neither a limit nor an offset"); } if (limit == null) { throw new NotSupportedException($"Dialect {this} does not support setting an offset without a limit"); } SqlStringBuilder limitBuilder = new SqlStringBuilder(); limitBuilder.Add("select"); if (insertionPoint > 6) { limitBuilder.Add(" distinct "); } limitBuilder.Add(" top "); limitBuilder.Add(limit); if (offset != null) { limitBuilder.Add(" start at "); limitBuilder.Add(offset); } limitBuilder.Add(sql.Substring(insertionPoint)); return(limitBuilder.ToSqlString()); } else { return(sql); // unchanged } }
public override SqlString GetLimitString(SqlString sql, int offset, int limit, int?offsetParameterIndex, int?limitParameterIndex) { sql = sql.Trim(); bool hasOffset = offset > 0; bool isForUpdate = false; if (sql.EndsWithCaseInsensitive(" for update")) { sql = sql.Substring(0, sql.Length - 11); isForUpdate = true; } string selectColumns = ExtractColumnOrAliasNames(sql); var pagingSelect = new SqlStringBuilder(sql.Parts.Count + 10); if (hasOffset) { pagingSelect.Add("select " + selectColumns + " from ( select row_.*, rownum rownum_ from ( "); } else { pagingSelect.Add("select " + selectColumns + " from ( "); } pagingSelect.Add(sql); if (hasOffset) { pagingSelect.Add(" ) row_ where rownum <=").AddParameter(limitParameterIndex.Value).Add(") where rownum_ >").AddParameter(offsetParameterIndex.Value); } else { pagingSelect.Add(" ) where rownum <=").AddParameter(limitParameterIndex.Value); } if (isForUpdate) { pagingSelect.Add(" for update"); } return(pagingSelect.ToSqlString()); }
public override SqlString GetLimitString(SqlString sql, bool hasOffset) { sql = sql.Trim(); bool isForUpdate = false; if (sql.EndsWithCaseInsensitive(" for update")) { sql = sql.Substring(0, sql.Length - 11); isForUpdate = true; } var pagingSelect = new SqlStringBuilder(sql.Parts.Count + 10); if (hasOffset) { pagingSelect.Add("select * from ( select row_.*, rownum rownum_ from ( "); } else { pagingSelect.Add("select * from ( "); } pagingSelect.Add(sql); if (hasOffset) { pagingSelect.Add(" ) row_ where rownum <=").AddParameter().Add(") where rownum_ >").AddParameter(); } else { pagingSelect.Add(" ) where rownum <=").AddParameter(); } if (isForUpdate) { pagingSelect.Add(" for update"); } return(pagingSelect.ToSqlString()); }
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()); }
public void AddWhereFragment( JoinFragment joinFragment, SqlString whereFragment, QueryNode query, FromElement fromElement, HqlSqlWalker hqlSqlWalker) { if (whereFragment == null) { return; } if (!fromElement.UseWhereFragment && !joinFragment.HasThetaJoins) { return; } whereFragment = whereFragment.Trim(); if (StringHelper.IsEmpty(whereFragment.ToString())) { return; } // Forcefully remove leading ands from where fragments; the grammar will // handle adding them if (whereFragment.StartsWithCaseInsensitive("and")) { whereFragment = whereFragment.Substring(4); } log.Debug("Using unprocessed WHERE-fragment [" + whereFragment + "]"); SqlFragment fragment = (SqlFragment)Create(HqlSqlWalker.SQL_TOKEN, whereFragment.ToString()); fragment.SetJoinFragment(joinFragment); fragment.FromElement = fromElement; if (fromElement.IndexCollectionSelectorParamSpec != null) { fragment.AddEmbeddedParameter(fromElement.IndexCollectionSelectorParamSpec); fromElement.IndexCollectionSelectorParamSpec = null; } if (hqlSqlWalker.IsFilter()) { //if (whereFragment.IndexOfCaseInsensitive("?") >= 0) if (whereFragment.ToString().IndexOf("?") >= 0) { IType collectionFilterKeyType = hqlSqlWalker.SessionFactoryHelper .RequireQueryableCollection(hqlSqlWalker.CollectionFilterRole) .KeyType; CollectionFilterKeyParameterSpecification paramSpec = new CollectionFilterKeyParameterSpecification( hqlSqlWalker.CollectionFilterRole, collectionFilterKeyType, 0 ); fragment.AddEmbeddedParameter(paramSpec); } } JoinProcessor.ProcessDynamicFilterParameters( whereFragment, fragment, hqlSqlWalker ); log.Debug("Using processed WHERE-fragment [" + fragment.Text + "]"); // Filter conditions need to be inserted before the HQL where condition and the // theta join node. This is because org.hibernate.loader.Loader binds the filter parameters first, // then it binds all the HQL query parameters, see org.hibernate.loader.Loader.processFilterParameters(). if (fragment.FromElement.IsFilter || fragment.HasFilterCondition) { if (_filters == null) { // Find or create the WHERE clause IASTNode where = (IASTNode)query.WhereClause; // Create a new FILTERS node as a parent of all filters _filters = Create(HqlSqlWalker.FILTERS, "{filter conditions}"); // Put the FILTERS node before the HQL condition and theta joins where.InsertChild(0, _filters); } // add the current fragment to the FILTERS node _filters.AddChild(fragment); } else { if (_thetaJoins == null) { // Find or create the WHERE clause IASTNode where = (IASTNode)query.WhereClause; // Create a new THETA_JOINS node as a parent of all filters _thetaJoins = Create(HqlSqlWalker.THETA_JOINS, "{theta joins}"); // Put the THETA_JOINS node before the HQL condition, after the filters. if (_filters == null) { where.InsertChild(0, _thetaJoins); } else { _filters.AddSibling(_thetaJoins); } } // add the current fragment to the THETA_JOINS node _thetaJoins.AddChild(fragment); } }
/// <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()); }
private static void ExtractColumnOrAliasNames(SqlString select, out List <SqlString> columnsOrAliases, out Dictionary <SqlString, SqlString> aliasToColumn) { columnsOrAliases = new List <SqlString>(); aliasToColumn = new Dictionary <SqlString, SqlString>(); IList <SqlString> tokens = new QuotedAndParenthesisStringTokenizer(select).GetTokens(); int index = 0; while (index < tokens.Count) { SqlString token = tokens[index]; int nextTokenIndex = index += 1; if (token.StartsWithCaseInsensitive("select")) { continue; } if (token.StartsWithCaseInsensitive("distinct")) { continue; } if (token.StartsWithCaseInsensitive(",")) { continue; } if (token.StartsWithCaseInsensitive("from")) { break; } // handle composite expressions like "2 * 4 as foo" while ((nextTokenIndex < tokens.Count) && (tokens[nextTokenIndex].StartsWithCaseInsensitive("as") == false && tokens[nextTokenIndex].StartsWithCaseInsensitive(",") == false)) { SqlString nextToken = tokens[nextTokenIndex]; token = token.Append(nextToken); nextTokenIndex = index += 1; } // if there is no alias, the token and the alias will be the same SqlString alias = token; bool isFunctionCallOrQuotedString = token.IndexOfCaseInsensitive("'") >= 0 || token.IndexOfCaseInsensitive("(") >= 0; // this is heuristic guess, if the expression contains ' or (, it is probably // not appropriate to just slice parts off of it if (isFunctionCallOrQuotedString == false) { // its a simple column reference, so lets set the alias to the // column name minus the table qualifier if it exists int dot = token.IndexOfCaseInsensitive("."); if (dot != -1) { alias = token.Substring(dot + 1); } } // notice! we are checking here the existence of "as" "alias", two // tokens from the current one if (nextTokenIndex + 1 < tokens.Count) { SqlString nextToken = tokens[nextTokenIndex]; if (nextToken.IndexOfCaseInsensitive("as") >= 0) { SqlString tokenAfterNext = tokens[nextTokenIndex + 1]; alias = tokenAfterNext; index += 2; //skip the "as" and the alias } } columnsOrAliases.Add(alias); aliasToColumn[alias] = token; } }
/// <summary> ///Jet engine has the following from clause syntax: ///<code> /// tableexpression[, tableexpression]* ///</code> ///where tableexpression is: ///<code> /// tablename [(INNER |LEFT | RIGHT) JOIN [(] tableexpression [)] ON ...] ///</code> ///where the parenthesises are necessary if the "inner" tableexpression is not just a single tablename. ///Additionally INNER JOIN cannot be nested in LEFT | RIGHT JOIN. ///To translate the simple non-parenthesized joins to the jet syntax, the following transformation must be done: ///<code> /// A join B on ... join C on ... join D on ..., E join F on ... join G on ..., H join I on ..., J ///has to be translated as: /// (select * from ((A join B on ...) join C on ...) join D on ...) as crazyAlias1, (select * from (E join F on ...) join G on ...) as crazyAlias2, (select * from H join I on ...) as crazyAlias3, J ///</code> /// </summary> /// <param name="sqlString">the sqlstring to transform</param> /// <returns>sqlstring with parenthesized joins.</returns> private SqlString FinalizeJoins(SqlString sqlString) { if (_queryCache.Contains(sqlString)) { return((SqlString)_queryCache[sqlString]); } var beginOfFrom = sqlString.IndexOfCaseInsensitive(FromClause); var endOfFrom = sqlString.IndexOfCaseInsensitive(WhereClause); var beginOfOrderBy = sqlString.IndexOfCaseInsensitive(OrderByClause); if (beginOfFrom < 0) { return(sqlString); } if (beginOfOrderBy < 0) { if (endOfFrom < 0) { endOfFrom = sqlString.Length; } } else { endOfFrom = beginOfOrderBy; } var fromClause = sqlString.Substring(beginOfFrom, endOfFrom - beginOfFrom).ToString(); var wherePart = sqlString.Substring(endOfFrom); var fromClauseInWhere = wherePart.IndexOfCaseInsensitive(FromClause); var transformedFrom = TransformFromClause(fromClause); var processWhereJoins = string.Empty; if (fromClauseInWhere > -1) //has where clause, inspect other joins { var whereClause = wherePart.Substring(0, fromClauseInWhere); var criteria = wherePart.Substring(fromClauseInWhere).ToString(); processWhereJoins = whereClause + TransformFromClause(criteria); } //put it all together again var final = new SqlStringBuilder(sqlString.Count + 1); final.Add(sqlString.Substring(0, beginOfFrom)); final.Add(transformedFrom); if (string.IsNullOrEmpty(processWhereJoins)) { final.Add(sqlString.Substring(endOfFrom)); } else { final.Add(processWhereJoins); } SqlString ret = final.ToSqlString(); _queryCache[sqlString] = ret; return(ret); }