From: <pa...@us...> - 2011-05-24 05:23:09
|
Revision: 5867 http://nhibernate.svn.sourceforge.net/nhibernate/?rev=5867&view=rev Author: patearl Date: 2011-05-24 05:23:00 +0000 (Tue, 24 May 2011) Log Message: ----------- Removed all but one Dialect GetLimitString method. (Breaking Change) Various limit-related simplifications, bug fixes, and improvements. Modified Paths: -------------- trunk/nhibernate/src/NHibernate/Criterion/SubqueryExpression.cs trunk/nhibernate/src/NHibernate/Dialect/DB2400Dialect.cs trunk/nhibernate/src/NHibernate/Dialect/DB2Dialect.cs trunk/nhibernate/src/NHibernate/Dialect/Dialect.cs trunk/nhibernate/src/NHibernate/Dialect/FirebirdDialect.cs trunk/nhibernate/src/NHibernate/Dialect/InformixDialect.cs trunk/nhibernate/src/NHibernate/Dialect/MsSql2000Dialect.cs trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs trunk/nhibernate/src/NHibernate/Dialect/MsSqlCe40Dialect.cs trunk/nhibernate/src/NHibernate/Dialect/MySQL5Dialect.cs trunk/nhibernate/src/NHibernate/Dialect/MySQLDialect.cs trunk/nhibernate/src/NHibernate/Dialect/Oracle8iDialect.cs trunk/nhibernate/src/NHibernate/Dialect/PostgreSQLDialect.cs trunk/nhibernate/src/NHibernate/Dialect/SQLiteDialect.cs trunk/nhibernate/src/NHibernate/Dialect/SybaseASA9Dialect.cs trunk/nhibernate/src/NHibernate/Dialect/SybaseSQLAnywhere10Dialect.cs trunk/nhibernate/src/NHibernate/Hql/Ast/ANTLR/SqlGenerator.cs trunk/nhibernate/src/NHibernate/Loader/Loader.cs trunk/nhibernate/src/NHibernate/SqlCommand/SqlString.cs trunk/nhibernate/src/NHibernate.Test/DialectTest/DB2DialectFixture.cs trunk/nhibernate/src/NHibernate.Test/DialectTest/FirebirdDialectFixture.cs trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSqlCe40DialectFixture.cs trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSqlDialectFixture.cs trunk/nhibernate/src/NHibernate.Test/DialectTest/SQLiteDialectFixture.cs Modified: trunk/nhibernate/src/NHibernate/Criterion/SubqueryExpression.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Criterion/SubqueryExpression.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Criterion/SubqueryExpression.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -66,7 +66,7 @@ if (criteriaImpl.FirstResult != 0 || criteriaImpl.MaxResults != RowSelection.NoValue) { - int firstResults = Loader.Loader.GetFirstRow(parameters.RowSelection); + int firstResults = factory.Dialect.GetOffsetValue(Loader.Loader.GetFirstRow(parameters.RowSelection)); int maxResults = Loader.Loader.GetMaxOrLimit(factory.Dialect, parameters.RowSelection); int? offsetParameterIndex = criteriaQuery.CreatePagingParameter(firstResults); int? limitParameterIndex = criteriaQuery.CreatePagingParameter(maxResults); Modified: trunk/nhibernate/src/NHibernate/Dialect/DB2400Dialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/DB2400Dialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/DB2400Dialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -46,17 +46,11 @@ get { return false; } } - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit, int? offsetParameterIndex, int? limitParameterIndex) - { - // override the base-class's implementation that uses limit parameters - return GetLimitString(querySqlString, offset, limit); - } - - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) + public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit) { - return new SqlStringBuilder(querySqlString) + return new SqlStringBuilder(queryString) .Add(" fetch first ") - .Add(limit.ToString()) + .Add(limit) .Add(" rows only ") .ToSqlString(); } Modified: trunk/nhibernate/src/NHibernate/Dialect/DB2Dialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/DB2Dialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/DB2Dialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -205,16 +205,7 @@ get { return true; } } - /// <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="offset">Offset of the first row to be returned by the query (zero-based)</param> - /// <param name="limit">Maximum number of rows to be returned by the query</param> - /// <param name="offsetParameterIndex">Optionally, the Offset parameter index</param> - /// <param name="limitParameterIndex">Optionally, the Limit parameter index</param> - /// <returns>A new <see cref="SqlString"/> that contains the <c>LIMIT</c> clause.</returns> - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit, int? offsetParameterIndex, int? limitParameterIndex) + public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit) { /* * "select * from (select row_number() over(orderby_clause) as rownum, " @@ -230,20 +221,27 @@ .Add(querySqlString.Substring(7)) .Add(") as tempresult where rownum "); - if (offset > 0) + if (offset != null && limit != null) { pagingBuilder .Add("between ") - .Add(Parameter.WithIndex(offsetParameterIndex.Value)) + .Add(offset) .Add("+1 and ") - .Add(Parameter.WithIndex(limitParameterIndex.Value)); + .Add(limit); } - else + else if (limit != null) { pagingBuilder .Add("<= ") - .Add(Parameter.WithIndex(limitParameterIndex.Value)); + .Add(limit); } + else + { + // We just have an offset. + pagingBuilder + .Add("> ") + .Add(offset); + } return pagingBuilder.ToSqlString(); } Modified: trunk/nhibernate/src/NHibernate/Dialect/Dialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/Dialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/Dialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -1680,70 +1680,82 @@ get { return false; } } - public virtual SqlString GetLimitString(SqlString querySqlString, int offset, int limit, Parameter offsetParameter, Parameter limitParameter) - { - if (!SupportsVariableLimit) - return GetLimitString(querySqlString, offset, limit); + /// <summary> + /// For limit clauses, indicates whether to use 0 or 1 as the offset that returns the first row. Should be true if the first row is at offset 1. + /// </summary> + public virtual bool OffsetStartsAtOne + { + get { return false; } + } - if ((offsetParameter == null) && (limitParameter == null)) - return GetLimitString(querySqlString, offset, limit); + /// <summary> + /// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>. + /// Expects any database-specific offset and limit adjustments to have already been performed (ex. UseMaxForLimit, OffsetStartsAtOne). + /// </summary> + /// <param name="queryString">The <see cref="SqlString"/> to base the limit query off.</param> + /// <param name="offset">Offset of the first row to be returned by the query. This may be represented as a parameter, a string literal, or a null value if no limit is requested. This should have already been adjusted to account for OffsetStartsAtOne.</param> + /// <param name="limit">Maximum number of rows to be returned by the query. This may be represented as a parameter, a string literal, or a null value if no offset is requested. This should have already been adjusted to account for UseMaxForLimit.</param> + /// <returns>A new <see cref="SqlString"/> that contains the <c>LIMIT</c> clause.</returns> + public virtual SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit) + { + throw new NotSupportedException("Dialect does not have support for limit strings."); + } - throw new NotSupportedException("Override to support limits passed as parameters"); - } + /// <summary> + /// Expects any database-specific offset and limit adjustments to have already been performed (ex. UseMaxForLimit, OffsetStartsAtOne). + /// </summary> + internal SqlString GetLimitString(SqlString queryString, int? offset, int? limit, int? offsetParameterIndex, int? limitParameterIndex) + { + SqlString offsetParameter = + SupportsVariableLimit && offsetParameterIndex.HasValue ? new SqlString(Parameter.WithIndex(offsetParameterIndex.Value)) : + offset.HasValue ? new SqlString(offset.ToString()) : + null; - /// <summary> - /// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c> - /// when the dialect supports variable limits (i.e. parameters for the limit constraints) - /// </summary> - /// <param name="querySqlString">The <see cref="SqlString"/> to base the limit query off.</param> - /// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param> - /// <param name="limit">Maximum number of rows to be returned by the query</param> - /// <param name="offsetParameterIndex">Optionally, the Offset parameter index in the sql</param> - /// <param name="limitParameterIndex">Optionally, the Limit parameter index in the sql</param> - /// <returns>A new <see cref="SqlString"/> that contains the <c>LIMIT</c> clause.</returns> - public virtual SqlString GetLimitString(SqlString querySqlString, int offset, int limit, int? offsetParameterIndex, int? limitParameterIndex) - { - if (!SupportsVariableLimit) - return GetLimitString(querySqlString, offset, limit); + SqlString limitParameter = + SupportsVariableLimit && limitParameterIndex.HasValue ? new SqlString(Parameter.WithIndex(limitParameterIndex.Value)) : + limit.HasValue ? new SqlString(limit.ToString()) : + null; - if ((offsetParameterIndex == null) && (limitParameterIndex == null)) - return GetLimitString(querySqlString, offset, limit); + return GetLimitString(queryString, offsetParameter, limitParameter); + } - throw new NotSupportedException("Override to support limits passed as parameters"); - } + /// <summary> + /// Expects any database-specific offset and limit adjustments to have already been performed (ex. UseMaxForLimit, OffsetStartsAtOne). + /// </summary> + internal SqlString GetLimitString(SqlString queryString, int? offset, int? limit, Parameter offsetParameter, Parameter limitParameter) + { + SqlString o = + SupportsVariableLimit && offsetParameter != null ? new SqlString(offsetParameter) : + offset.HasValue ? new SqlString(offset.ToString()) : + null; - /// <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="offset">Offset of the first row to be returned by the query (zero-based)</param> - /// <param name="limit">Maximum number of rows to be returned by the query</param> - /// <returns>A new SqlString that contains the <c>LIMIT</c> clause.</returns> - public virtual SqlString GetLimitString(SqlString querySqlString, int offset, int limit) - { - return GetLimitString(querySqlString, offset > 0); - } + SqlString l = + SupportsVariableLimit && limitParameter != null ? new SqlString(limitParameter) : + limit.HasValue ? new SqlString(limit.ToString()) : + null; - /// <summary> Apply s limit clause to the query. </summary> - /// <param name="querySqlString">The query to which to apply the limit. </param> - /// <param name="hasOffset">Is the query requesting an offset? </param> - /// <returns> the modified SQL </returns> - /// <remarks> - /// Typically dialects utilize <see cref="SupportsVariableLimit"/> - /// limit caluses when they support limits. Thus, when building the - /// select command we do not actually need to know the limit or the offest - /// since we will just be using placeholders. - /// <p/> - /// Here we do still pass along whether or not an offset was specified - /// so that dialects not supporting offsets can generate proper exceptions. - /// In general, dialects will override one or the other of this method and - /// <see cref="GetLimitString(SqlString,int,int)"/>. - /// </remarks> - public virtual SqlString GetLimitString(SqlString querySqlString, bool hasOffset) - { - throw new NotSupportedException("Paged Queries not supported"); - } + return GetLimitString(queryString, o, l); + } + internal int GetLimitValue(int offset, int limit) + { + if (limit == int.MaxValue) + return int.MaxValue; + + if (UseMaxForLimit) + return GetOffsetValue(offset) + limit; + + return limit; + } + + internal int GetOffsetValue(int offset) + { + if (OffsetStartsAtOne) + return offset + 1; + + return offset; + } + #endregion #region identifier quoting support Modified: trunk/nhibernate/src/NHibernate/Dialect/FirebirdDialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/FirebirdDialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/FirebirdDialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -177,28 +177,32 @@ get { return true; } } - /// <summary> - /// Add a <c>FIRST x [SKIP] y</c> clause to the given SQL <c>SELECT</c> - /// </summary> - /// <param name="querySqlString">A Query in the form of a SqlString.</param> - /// <param name="limit">Maximum number of rows to be returned by the query</param> - /// <param name="offset">Offset of the first row to process in the result set</param> - /// <returns>A new SqlString that contains the <c>FIRST</c> clause.</returns> - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) + public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit) { - /* + // FIXME - This should use the ROWS syntax in Firebird to avoid problems with subqueries metioned here: + // http://www.firebirdsql.org/refdocs/langrefupd20-select.html#langrefupd20-first-skip + + /* * "SELECT FIRST x [SKIP y] rest-of-sql-statement" */ - int insertIndex = GetAfterSelectInsertPoint(querySqlString); - if (offset > 0) - { - return querySqlString.Insert(insertIndex, " first " + limit.ToString() + " skip " + offset.ToString()); + int insertIndex = GetAfterSelectInsertPoint(queryString); + + SqlStringBuilder limitFragment = new SqlStringBuilder(); + + if (limit != null) + { + limitFragment.Add(" first "); + limitFragment.Add(limit); + } + + if (offset != null) + { + limitFragment.Add(" skip "); + limitFragment.Add(offset); } - else - { - return querySqlString.Insert(insertIndex, " first " + limit.ToString()); - } + + return queryString.Insert(insertIndex, limitFragment.ToSqlString()); } public override bool SupportsVariableLimit Modified: trunk/nhibernate/src/NHibernate/Dialect/InformixDialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/InformixDialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/InformixDialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -376,36 +376,29 @@ get { return true; } } - /// <summary> Apply s limit clause to the query. </summary> - /// <param name="querySqlString">The query to which to apply the limit. </param> - /// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param> - /// <param name="limit">Maximum number of rows to be returned by the query</param> - /// <returns> the modified SQL </returns> - /// <remarks> - /// Typically dialects utilize <see cref="SupportsVariableLimit"/> - /// limit caluses when they support limits. Thus, when building the - /// select command we do not actually need to know the limit or the offest - /// since we will just be using placeholders. - /// <p/> - /// Here we do still pass along whether or not an offset was specified - /// so that dialects not supporting offsets can generate proper exceptions. - /// In general, dialects will override one or the other of this method and - /// <see cref="GetLimitString(SqlString,int,int)"/>. - /// </remarks> - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) + public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit) { /* * "SELECT [SKIP x] FIRST y rest-of-sql-statement" */ - int insertIndex = GetAfterSelectInsertPoint(querySqlString); + // TODO - Check support for cases where only the offset is specified, but the limit is not. Might need to use int.MaxValue. - if (offset > 0) + int insertIndex = GetAfterSelectInsertPoint(queryString); + + SqlStringBuilder limitFragment = new SqlStringBuilder(); + if (offset != null) + { + limitFragment.Add(" skip "); + limitFragment.Add(offset); + } + if (limit != null) { - return querySqlString.Insert(insertIndex, " skip " + offset + " first " + limit); + limitFragment.Add(" first "); + limitFragment.Add(limit); } - return querySqlString.Insert(insertIndex, " first " + limit); + return queryString.Insert(insertIndex, limitFragment.ToSqlString()); } /// <summary> Modified: trunk/nhibernate/src/NHibernate/Dialect/MsSql2000Dialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/MsSql2000Dialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/MsSql2000Dialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -329,25 +329,17 @@ get { return false; } } - /// <summary> - /// Add a <c>LIMIT (TOP)</c> clause to the given SQL <c>SELECT</c> - /// </summary> - /// <param name="querySqlString">A Query in the form of a SqlString.</param> - /// <param name="limit">Maximum number of rows to be returned by the query</param> - /// <param name="offset">Offset of the first row to process in the result set</param> - /// <returns>A new SqlString that contains the <c>LIMIT</c> clause.</returns> - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) + public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit) { - if (offset > 0) - { - throw new NotSupportedException("SQL Server does not support an offset"); - } - /* * "SELECT TOP limit rest-of-sql-statement" */ - return querySqlString.Insert(GetAfterSelectInsertPoint(querySqlString), " top " + limit); + SqlStringBuilder topFragment = new SqlStringBuilder(); + topFragment.Add(" top "); + topFragment.Add(limit); + + return querySqlString.Insert(GetAfterSelectInsertPoint(querySqlString), topFragment.ToSqlString()); } /// <summary> Modified: trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -29,89 +29,45 @@ RegisterKeyword("xml"); } - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) - { - return GetLimitString(querySqlString, - offset == 0 ? null : new SqlString(offset.ToString()), - limit == int.MaxValue ? null : new SqlString(limit.ToString())); - } - - /// <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.</param> - /// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param> - /// <param name="limit">Maximum number of rows to be returned by the query</param> - /// <param name="offsetParameterIndex">Optionally, the Offset parameter index in the sql</param> - /// <param name="limitParameterIndex">Optionally, the Limit parameter index in the sql</param> - /// <returns>A new <see cref="SqlString"/> with the <c>LIMIT</c> clause applied.</returns> - /// <remarks> - /// Note that we need to 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 limit, int? offsetParameterIndex, int? limitParameterIndex) + public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit) { - object limitObject = limitParameterIndex == null ? (object) new SqlString(limit.ToString()) : Parameter.WithIndex(limitParameterIndex.Value); - object offsetObject = null; - if (offset != 0) - { - offsetObject = offsetParameterIndex == null ? (object) new SqlString(offset.ToString()) : Parameter.WithIndex(offsetParameterIndex.Value); - } - return GetLimitString(querySqlString, offsetObject, limitObject); - } - - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit, Parameter offsetParameter, Parameter limitParameter) - { - object limitObject = limitParameter ?? (object) new SqlString(limit.ToString()); - object offsetObject = null; - if (offset != 0) - { - offsetObject = offsetParameter ?? (object) new SqlString(offset.ToString()); - } - return GetLimitString(querySqlString, offsetObject, limitObject); - } - - 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); + int insertPoint = GetAfterSelectInsertPoint(queryString); return result - .Add(querySqlString.Substring(0, insertPoint)) + .Add(queryString.Substring(0, insertPoint)) .Add(" TOP (") - .AddObject(limit) + .Add(limit) .Add(") ") - .Add(querySqlString.Substring(insertPoint)) + .Add(queryString.Substring(insertPoint)) .ToSqlString(); } - int fromIndex = GetFromIndex(querySqlString); - SqlString select = querySqlString.Substring(0, fromIndex); + int fromIndex = GetFromIndex(queryString); + SqlString select = queryString.Substring(0, fromIndex); List<SqlString> columnsOrAliases; Dictionary<SqlString, SqlString> aliasToColumn; ExtractColumnOrAliasNames(select, out columnsOrAliases, out aliasToColumn); - - int orderIndex = querySqlString.LastIndexOfCaseInsensitive(" order by "); + + int orderIndex = queryString.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())) + if (orderIndex > 0 && HasMatchingParens(queryString.Substring(orderIndex).ToString())) { - fromAndWhere = querySqlString.Substring(fromIndex, orderIndex - fromIndex).Trim(); - SqlString orderBy = querySqlString.Substring(orderIndex).Trim(); + fromAndWhere = queryString.Substring(fromIndex, orderIndex - fromIndex).Trim(); + SqlString orderBy = queryString.Substring(orderIndex).Trim(); sortExpressions = orderBy.Substring(9).Split(","); } else { - fromAndWhere = querySqlString.Substring(fromIndex).Trim(); + fromAndWhere = queryString.Substring(fromIndex).Trim(); // Use dummy sort to avoid errors sortExpressions = new[] {new SqlString("CURRENT_TIMESTAMP"),}; } @@ -119,7 +75,7 @@ result.Add("SELECT "); if (limit != null) - result.Add("TOP (").AddObject(limit).Add(") "); + result.Add("TOP (").Add(limit).Add(") "); result .Add(StringHelper.Join(", ", columnsOrAliases)) @@ -133,7 +89,7 @@ .Add(") as __hibernate_sort_row ") .Add(fromAndWhere) .Add(") as query WHERE query.__hibernate_sort_row > ") - .AddObject(offset) + .Add(offset) .Add(" ORDER BY query.__hibernate_sort_row"); return result.ToSqlString(); Modified: trunk/nhibernate/src/NHibernate/Dialect/MsSqlCe40Dialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/MsSqlCe40Dialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/MsSqlCe40Dialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -20,13 +20,27 @@ get { return true; } } - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) + public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit) { - if (querySqlString.IndexOfCaseInsensitive(" ORDER BY ") < 0) - { - querySqlString = querySqlString.Append(" ORDER BY GETDATE()"); - } - return querySqlString.Append(string.Format(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", offset, limit)); + SqlStringBuilder builder = new SqlStringBuilder(queryString); + if (queryString.IndexOfCaseInsensitive(" ORDER BY ") < 0) + builder.Add(" ORDER BY GETDATE()"); + + builder.Add(" OFFSET "); + if (offset == null) + builder.Add("0"); + else + builder.Add(offset); + builder.Add(" ROWS"); + + if (limit != null) + { + builder.Add(" FETCH NEXT "); + builder.Add(limit); + builder.Add(" ROWS ONLY"); + } + + return builder.ToSqlString(); } } } \ No newline at end of file Modified: trunk/nhibernate/src/NHibernate/Dialect/MySQL5Dialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/MySQL5Dialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/MySQL5Dialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -41,23 +41,6 @@ } } - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) - { - var pagingBuilder = new SqlStringBuilder(); - - pagingBuilder.Add(querySqlString); - pagingBuilder.Add(" limit "); - if (offset > 0) - { - pagingBuilder.Add(offset.ToString()); - pagingBuilder.Add(", "); - } - - pagingBuilder.Add(limit.ToString()); - - return pagingBuilder.ToSqlString(); - } - public override string SelectGUIDString { get Modified: trunk/nhibernate/src/NHibernate/Dialect/MySQLDialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/MySQLDialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/MySQLDialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -176,31 +176,24 @@ get { return false; } } - /// <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.</param> - /// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param> - /// <param name="limit">Maximum number of rows to be returned by the query</param> - /// <param name="offsetParameterIndex">Optionally, the Offset parameter index</param> - /// <param name="limitParameterIndex">Optionally, the Limit parameter index</param> - /// <returns>A new <see cref="SqlString"/> that contains the <c>LIMIT</c> clause.</returns> - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit, int? offsetParameterIndex, int? limitParameterIndex) - { - var pagingBuilder = new SqlStringBuilder(); - pagingBuilder.Add(querySqlString); - pagingBuilder.Add(" limit "); + public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit) + { + var pagingBuilder = new SqlStringBuilder(queryString); + pagingBuilder.Add(" limit "); - if (offset > 0) - { - pagingBuilder.Add(Parameter.WithIndex(offsetParameterIndex.Value)); - pagingBuilder.Add(", "); - } + if (offset != null) + { + pagingBuilder.Add(offset); + pagingBuilder.Add(", "); + } - pagingBuilder.Add(Parameter.WithIndex(limitParameterIndex.Value)); + if (limit != null) + pagingBuilder.Add(limit); + else + pagingBuilder.Add(int.MaxValue.ToString()); - return pagingBuilder.ToSqlString(); - } + return pagingBuilder.ToSqlString(); + } public override string GetAddForeignKeyConstraintString(string constraintName, string[] foreignKey, string referencedTable, string[] primaryKey, Modified: trunk/nhibernate/src/NHibernate/Dialect/Oracle8iDialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/Oracle8iDialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/Oracle8iDialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -250,10 +250,9 @@ return new DecodeCaseFragment(this); } - public override SqlString GetLimitString(SqlString sql, int offset, int limit, int? offsetParameterIndex, int? limitParameterIndex) + public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit) { sql = sql.Trim(); - bool hasOffset = offset > 0; bool isForUpdate = false; if (sql.EndsWithCaseInsensitive(" for update")) { @@ -264,7 +263,7 @@ string selectColumns = ExtractColumnOrAliasNames(sql); var pagingSelect = new SqlStringBuilder(sql.Parts.Count + 10); - if (hasOffset) + if (offset != null && limit != null) { pagingSelect.Add("select " + selectColumns + " from ( select row_.*, rownum rownum_ from ( "); } @@ -273,14 +272,19 @@ pagingSelect.Add("select " + selectColumns + " from ( "); } pagingSelect.Add(sql); - if (hasOffset) + if (offset != null && limit != null) { - pagingSelect.Add(" ) row_ where rownum <=").AddParameter(limitParameterIndex.Value).Add(") where rownum_ >").AddParameter(offsetParameterIndex.Value); + pagingSelect.Add(" ) row_ where rownum <=").Add(limit).Add(") where rownum_ >").Add(offset); } - else + else if (limit != null) { - pagingSelect.Add(" ) where rownum <=").AddParameter(limitParameterIndex.Value); + pagingSelect.Add(" ) where rownum <=").Add(limit); } + else + { + // offset is specified, but limit is not. + pagingSelect.Add(" ) where rownum >").Add(offset); + } if (isForUpdate) { Modified: trunk/nhibernate/src/NHibernate/Dialect/PostgreSQLDialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/PostgreSQLDialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/PostgreSQLDialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -133,42 +133,21 @@ get { return true; } } - /// <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.</param> - /// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param> - /// <param name="limit">Maximum number of rows to be returned by the query</param> - /// <param name="offsetParameterIndex">Optionally, the Offset parameter index</param> - /// <param name="limitParameterIndex">Optionally, the Limit parameter index</param> - /// <returns>A new <see cref="SqlString"/> that contains the <c>LIMIT</c> clause.</returns> - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit, int? offsetParameterIndex, int? limitParameterIndex) - { - object limitObject = Parameter.WithIndex(limitParameterIndex.Value); - object offsetObject = offset > 0 ? Parameter.WithIndex(offsetParameterIndex.Value) : null; - return GetLimitString(querySqlString, offsetObject, limitObject); - } - - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) + public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit) { - return GetLimitString(querySqlString, new SqlString(offset.ToString()), new SqlString(limit.ToString())); - } - - private SqlString GetLimitString(SqlString querySqlString, object offset, object limit) - { SqlStringBuilder pagingBuilder = new SqlStringBuilder(); - pagingBuilder.Add(querySqlString); + pagingBuilder.Add(queryString); if (limit != null) { pagingBuilder.Add(" limit "); - pagingBuilder.AddObject(limit); + pagingBuilder.Add(limit); } if (offset != null) { pagingBuilder.Add(" offset "); - pagingBuilder.AddObject(offset); + pagingBuilder.Add(offset); } return pagingBuilder.ToSqlString(); Modified: trunk/nhibernate/src/NHibernate/Dialect/SQLiteDialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/SQLiteDialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/SQLiteDialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -149,11 +149,6 @@ get { return false; } } - public override bool SupportsVariableLimit - { - get { return false; } - } - public override bool SupportsIdentityColumns { get { return true; } @@ -244,29 +239,27 @@ get { return "DEFAULT VALUES"; } } - /// <summary> - /// Add a LIMIT N clause to the given SQL <c>SELECT</c> - /// </summary> - /// <param name="querySqlString">A Query in the form of a SqlString.</param> - /// <param name="limit">Maximum number of rows to be returned by the query</param> - /// <param name="offset">Offset of the first row to process in the result set</param> - /// <returns>A new SqlString that contains the <c>LIMIT</c> clause.</returns> - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) - { - SqlStringBuilder pagingBuilder = new SqlStringBuilder(); - pagingBuilder.Add(querySqlString); - pagingBuilder.Add(" limit "); - pagingBuilder.Add(limit.ToString()); + public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit) + { + SqlStringBuilder pagingBuilder = new SqlStringBuilder(); + pagingBuilder.Add(queryString); - if (offset > 0) - { - pagingBuilder.Add(" offset "); - pagingBuilder.Add(offset.ToString()); - } + pagingBuilder.Add(" limit "); + if (limit != null) + pagingBuilder.Add(limit); + else + // We must have a limit present if we have an offset. + pagingBuilder.Add(int.MaxValue.ToString()); - return pagingBuilder.ToSqlString(); - } + if (offset != null) + { + pagingBuilder.Add(" offset "); + pagingBuilder.Add(offset); + } + return pagingBuilder.ToSqlString(); + } + public override bool SupportsTemporaryTables { get { return true; } Modified: trunk/nhibernate/src/NHibernate/Dialect/SybaseASA9Dialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/SybaseASA9Dialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/SybaseASA9Dialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -92,11 +92,29 @@ get { return false; } } - public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) + public override bool OffsetStartsAtOne + { + get { return true; } + } + + public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit) { - int intSelectInsertPoint = GetAfterSelectInsertPoint(querySqlString); - string strLimit = string.Format(" top {0} start at {1}", limit, offset + 1); - return querySqlString.Insert(intSelectInsertPoint, strLimit); + int intSelectInsertPoint = GetAfterSelectInsertPoint(queryString); + + SqlStringBuilder limitFragment = new SqlStringBuilder(); + limitFragment.Add(" top "); + if (limit != null) + limitFragment.Add(limit); + else + limitFragment.Add(int.MaxValue.ToString()); + + if (offset != null) + { + limitFragment.Add(" start at "); + limitFragment.Add(offset); + } + + return queryString.Insert(intSelectInsertPoint, limitFragment.ToSqlString()); } public override IDataBaseSchema GetDataBaseSchema(DbConnection connection) Modified: trunk/nhibernate/src/NHibernate/Dialect/SybaseSQLAnywhere10Dialect.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Dialect/SybaseSQLAnywhere10Dialect.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Dialect/SybaseSQLAnywhere10Dialect.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -446,59 +446,37 @@ return 0; } - /// <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) + public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit) { - 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 - } - } + // SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ] + // for LIMIT/OFFSET support. Does not support a limit of zero. - /// <summary> - /// SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ] - /// for LIMIT/OFFSET support. - /// - /// Generate SELECT TOP n START AT m syntax using bound parameters - /// SQL Anywhere constraints: n > 0, m >= 0 - /// </summary> - public override SqlString GetLimitString(SqlString sql, int offset, int limit) - { - if (offset < 0) - { - throw new NotSupportedException("SQL Anywhere does not support negative offsets"); - } - if (limit <= 0) - { - throw new NotSupportedException("negative or zero TOP n (SQL limit) is not supported"); - } - return GetLimitString(sql, offset > 0); + // 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 + } } #endregion Modified: trunk/nhibernate/src/NHibernate/Hql/Ast/ANTLR/SqlGenerator.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Hql/Ast/ANTLR/SqlGenerator.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Hql/Ast/ANTLR/SqlGenerator.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -341,17 +341,15 @@ var dialect = sessionFactory.Dialect; - var hqlQueryHasFixedLimits = (queryWriter.Take.HasValue || queryWriter.Skip.HasValue) && !skipIsParameter && !takeIsParameter; - if(hqlQueryHasFixedLimits) - { - return dialect.GetLimitString(sqlString, queryWriter.Skip ?? 0, queryWriter.Take ?? int.MaxValue); - } + // FIXME - We need to adjust the parameters from the user according to dialect settings like UseMaxForLimit, OffsetStartsAtOne. This will need to happen every time we query. + // Skip-Take in HQL should be supported just for Dialect supporting variable limits at least when users use parameters for skip-take. if (!dialect.SupportsVariableLimit && (skipIsParameter || takeIsParameter)) { throw new NotSupportedException("The dialect " + dialect.GetType().FullName + " does not supports variable limits"); } - // At this point at least one of the two limits is a parameter and that parameter should be of IExplicitValueParameterSpecification + + // If a limit is a parameter, it should be of type IExplicitValueParameterSpecification. Parameter skipParameter = null; Parameter takeParameter = null; if(queryWriter.SkipParameter != null) @@ -365,8 +363,12 @@ takeParameter.BackTrack = queryWriter.TakeParameter.IdForBackTrack; } - sqlString = dialect.GetLimitString(sqlString, skipIsParameter ? 1 : queryWriter.Skip ?? 0, queryWriter.Take ?? int.MaxValue, skipParameter, takeParameter); - return sqlString; + // We allow the user to specify either constants or parameters for their limits. + return dialect.GetLimitString(sqlString, + queryWriter.Skip.HasValue ? (int?)dialect.GetOffsetValue(queryWriter.Skip.Value) : null, + queryWriter.Take.HasValue ? (int?)dialect.GetLimitValue(queryWriter.Skip ?? 0, queryWriter.Take.Value) : null, + skipParameter, + takeParameter); } private void Skip(IASTNode node) Modified: trunk/nhibernate/src/NHibernate/Loader/Loader.cs =================================================================== --- trunk/nhibernate/src/NHibernate/Loader/Loader.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/Loader/Loader.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -1127,11 +1127,12 @@ if (useLimit) { + int max = GetMaxOrLimit(dialect, selection); sqlString = dialect.GetLimitString( sqlString.Trim(), - useOffset ? GetFirstRow(selection) : 0, - GetMaxOrLimit(dialect, selection), + useOffset ? (int?)dialect.GetOffsetValue(GetFirstRow(selection)) : null, + max != int.MaxValue ? (int?)max : null, queryParameters.OffsetParameterIndex, queryParameters.LimitParameterIndex); } @@ -1216,19 +1217,12 @@ internal static int GetMaxOrLimit(Dialect.Dialect dialect, RowSelection selection) { int firstRow = GetFirstRow(selection); - int lastRow = selection.MaxRows; + int rowCount = selection.MaxRows; - if (lastRow == RowSelection.NoValue) + if (rowCount == RowSelection.NoValue) return int.MaxValue; - if (dialect.UseMaxForLimit) - { - return lastRow + firstRow; - } - else - { - return lastRow; - } + return dialect.GetLimitValue(firstRow, rowCount); } private int GetFirstLimitParameterCount(Dialect.Dialect dialect, bool useLimit, bool hasFirstRow, bool useOffset) @@ -1748,11 +1742,12 @@ if (useLimit) { + int max = GetMaxOrLimit(dialect, selection); sqlString = dialect.GetLimitString( sqlString.Trim(), - useOffset ? GetFirstRow(selection) : 0, - GetMaxOrLimit(dialect, selection), + useOffset ? (int?)dialect.GetOffsetValue(GetFirstRow(selection)) : null, + max != int.MaxValue ? (int?)max : null, parameters.OffsetParameterIndex, parameters.LimitParameterIndex); } Modified: trunk/nhibernate/src/NHibernate/SqlCommand/SqlString.cs =================================================================== --- trunk/nhibernate/src/NHibernate/SqlCommand/SqlString.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate/SqlCommand/SqlString.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -614,8 +614,13 @@ return lastIndex >= 0 ? Substring(lastIndex) : Empty; } - public SqlString Insert(int index, string text) - { + public SqlString Insert(int index, string text) + { + return Insert(index, new SqlString(text)); + } + + public SqlString Insert(int index, SqlString sqlString) + { if (index < 0) { throw new ArgumentException("index should be greater than or equal to 0", "index"); @@ -642,20 +647,22 @@ else if (nextOffset == index) { result.AddObject(part); - result.Add(text); + result.Add(sqlString); inserted = true; } else if (offset == index) { - result.Add(text); + result.Add(sqlString); result.AddObject(part); inserted = true; } else if (index > offset && index < nextOffset) { string partString = (string) part; - result.Add(partString.Insert(index - offset, text)); - inserted = true; + result.Add(partString.Substring(0, index - offset)); + result.Add(sqlString); + result.Add(partString.Substring(index - offset, partString.Length - (index - offset))); + inserted = true; } else { Modified: trunk/nhibernate/src/NHibernate.Test/DialectTest/DB2DialectFixture.cs =================================================================== --- trunk/nhibernate/src/NHibernate.Test/DialectTest/DB2DialectFixture.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate.Test/DialectTest/DB2DialectFixture.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -24,11 +24,11 @@ " order by a, x" }); - SqlString limited = dialect.GetLimitString(sql, 1, 2, -1, -2); + SqlString limited = dialect.GetLimitString(sql, new SqlString("111"), new SqlString("222")); Assert.AreEqual( - "select * from (select rownumber() over(order by a, x) as rownum, a, b, c from d where X = ? and Z = ? order by a, x) as tempresult where rownum between ?+1 and ?", + "select * from (select rownumber() over(order by a, x) as rownum, a, b, c from d where X = ? and Z = ? order by a, x) as tempresult where rownum between 111+1 and 222", limited.ToString()); - Assert.AreEqual(4, limited.GetParameterCount()); + Assert.AreEqual(2, limited.GetParameterCount()); } } } \ No newline at end of file Modified: trunk/nhibernate/src/NHibernate.Test/DialectTest/FirebirdDialectFixture.cs =================================================================== --- trunk/nhibernate/src/NHibernate.Test/DialectTest/FirebirdDialectFixture.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate.Test/DialectTest/FirebirdDialectFixture.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -12,19 +12,19 @@ { FirebirdDialect d = new FirebirdDialect(); - SqlString str = d.GetLimitString(new SqlString("SELECT * FROM fish"), 0, 10); + SqlString str = d.GetLimitString(new SqlString("SELECT * FROM fish"), null, new SqlString("10")); Assert.AreEqual("SELECT first 10 * FROM fish", str.ToString()); - str = d.GetLimitString(new SqlString("SELECT * FROM fish ORDER BY name"), 5, 15); + str = d.GetLimitString(new SqlString("SELECT * FROM fish ORDER BY name"), new SqlString("5"), new SqlString("15")); Assert.AreEqual("SELECT first 15 skip 5 * FROM fish ORDER BY name", str.ToString()); - str = d.GetLimitString(new SqlString("SELECT * FROM fish ORDER BY name DESC"), 7, 28); + str = d.GetLimitString(new SqlString("SELECT * FROM fish ORDER BY name DESC"), new SqlString("7"), new SqlString("28")); Assert.AreEqual("SELECT first 28 skip 7 * FROM fish ORDER BY name DESC", str.ToString()); - str = d.GetLimitString(new SqlString("SELECT DISTINCT fish.family FROM fish ORDER BY name DESC"), 0, 28); + str = d.GetLimitString(new SqlString("SELECT DISTINCT fish.family FROM fish ORDER BY name DESC"), null, new SqlString("28")); Assert.AreEqual("SELECT first 28 DISTINCT fish.family FROM fish ORDER BY name DESC", str.ToString()); - str = d.GetLimitString(new SqlString("SELECT DISTINCT fish.family FROM fish ORDER BY name DESC"), 7, 28); + str = d.GetLimitString(new SqlString("SELECT DISTINCT fish.family FROM fish ORDER BY name DESC"), new SqlString("7"), new SqlString("28")); Assert.AreEqual("SELECT first 28 skip 7 DISTINCT fish.family FROM fish ORDER BY name DESC", str.ToString()); } } Modified: trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs =================================================================== --- trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs 2011-05-23 22:25:56 UTC (rev 5866) +++ trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs 2011-05-24 05:23:00 UTC (rev 5867) @@ -16,66 +16,54 @@ { MsSql2005Dialect d = new MsSql2005Dialect(); - SqlString str = d.GetLimitString(new SqlString("select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_ from dbo.Contact c where COALESCE(c.Rating, 0) > 0 order by c.Rating desc , c.Last_Name , c.First_Name"), 1, 10, -1, -2); - System.Console.WriteLine(str); + SqlString str = d.GetLimitString(new SqlString("select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_ from dbo.Contact c where COALESCE(c.Rating, 0) > 0 order by c.Rating desc , c.Last_Name , c.First_Name"), new SqlString("111"), new SqlString("222")); Assert.AreEqual( - "SELECT TOP (?) Contact1_19_0_, Rating2_19_0_ FROM (select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_, ROW_NUMBER() OVER(ORDER BY c.Rating DESC, c.Last_Name, c.First_Name) as __hibernate_sort_row from dbo.Contact c where COALESCE(c.Rating, 0) > 0) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row", + "SELECT TOP (222) Contact1_19_0_, Rating2_19_0_ FROM (select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_, ROW_NUMBER() OVER(ORDER BY c.Rating DESC, c.Last_Name, c.First_Name) as __hibernate_sort_row from dbo.Contact c where COALESCE(c.Rating, 0) > 0) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row", str.ToString()); - str = d.GetLimitString(new SqlString("SELECT fish.id FROM fish"), 1, 10, -1, -2); - System.Console.WriteLine(str); + str = d.GetLimitString(new SqlString("SELECT fish.id FROM fish"), new SqlString("111"), new SqlString("222")); Assert.AreEqual( - "SELECT TOP (?) id FROM (SELECT fish.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row", + "SELECT TOP (222) id FROM (SELECT fish.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row", str.ToString()); - str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id FROM fish fish_"), 1, 10, -1, -2); - System.Console.WriteLine(str); + str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id FROM fish fish_"), new SqlString("111"), new SqlString("222")); Assert.AreEqual( - "SELECT TOP (?) id FROM (SELECT DISTINCT fish_.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row", + "SELECT TOP (222) id FROM (SELECT DISTINCT fish_.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row", str.ToString()); - str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id as ixx9_ FROM fish fish_"), 1, 10, -1, -2); - System.Console.WriteLine(str); + str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id as ixx9_ FROM fish fish_"), new SqlString("111"), new SqlString("222")); Assert.AreEqual( - "SELECT TOP (?) ixx9_ FROM (SELECT DISTINCT fish_.id as ixx9_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row", + "SELECT TOP (222) ixx9_ FROM (SELECT DISTINCT fish_.id as ixx9_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row", str.ToString()); - str = d.GetLimitString(new SqlString("SELECT * FROM fish ORDER BY name"), 5, 15, -1, -2); - System.Console.WriteLine(str); + str = d.GetLimitString(new SqlString("SELECT * FROM fish ORDER BY name"), new SqlString("111"), new SqlString("222")); Assert.AreEqual( - "SELECT TOP (?) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row", + "SELECT TOP (222) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row", str.ToString()); - str = d.GetLimitString(new SqlString("SELECT fish.id, fish.name FROM fish ORDER BY name DESC"), 7, 28, -1, -2); - System.Console.WriteLine(str); + str = d.GetLimitString(new SqlString("SELECT fish.id, fish.name FROM fish ORDER BY name DESC"), new SqlString("111"), new SqlString("222")); Assert.AreEqual( - "SELECT TOP (?) id, name FROM (SELECT fish.id, fish.name, ROW_NUMBER() OVER(ORDER BY fish.name DESC) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row", + "SELECT TOP (222) id, name FROM (SELECT fish.id, fish.name, ROW_NUMBER() OVER(ORDER BY fish.name DESC) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row", str.ToString()); - str = - d.GetLimitString( - new SqlString("SELECT * FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t ORDER BY name DESC"), 10, 20, -1, -2); - System.Console.WriteLine(str); + str = d.GetLimitString(new SqlString("SELECT * FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t ORDER BY name DESC"), new SqlString("111"), new SqlString("222")); Assert.AreEqual( - "SELECT TOP (?) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name DESC) as __hibernate_sort_row FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row", + "SELECT TOP (222) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name DESC) as __hibernate_sort_row FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row", str.ToString()); - str = d.GetLimitString(new SqlString("SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count FROM fish"), 1, 10, -1, -2); - System.Console.WriteLine(str); + str = d.GetLimitString(new SqlString("SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count FROM fish"), new SqlString("111"), new SqlString("222")); Assert.AreEqual( - "SELECT TOP (?) *, some_count FROM (SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row", + "SELECT TOP (222) *, some_count FROM (SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row", str.ToString()); - str = d.GetLimitString(new SqlString("SELECT * FROM fish WHERE scales = ", Parameter.Placeholder), 1, 10, -1, -2); - System.Console.WriteLine(str); + str = d.GetLimitString(new SqlString("SELECT * FROM fish WHERE scales = ", Parameter.Placeholder), new SqlString("111"), new SqlString("222")); Assert.AreEqual( - "SELECT TOP (?) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish WHERE scales = ?) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row", + "SELECT TOP (222) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish WHERE scales = ?) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row", str.ToString()); - str = d.GetLimitString(new SqlString("SELECT f.Type, COUNT(DISTINCT f.Name) AS Name FROM Fish f GROUP BY f.Type ORDER BY COUNT(DISTINCT f.Name)"), 1, 10, -1, -2); - System.Console.WriteLine(str); + str = d.GetLimitString(new SqlString("SELECT f.Type, COUNT(DISTINCT f.Name) AS Name FROM Fish f GROUP BY f.Type ORDER BY COUNT(DISTINCT f.Name)"), new SqlString("111"), new SqlString("222")); Assert.AreEqual( - "SELECT TOP (?) Type, Name FROM (SELECT f.Type, COUNT(DISTINCT f.Name) AS Name, ROW_NUMBER() OVER(ORDER BY COUNT(DISTINCT f.Name)) as __hibernate_sort_row FROM Fish f GROUP BY f.Type) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row", + "SELECT TOP (222) Type, Name FROM (SELECT f.Type, COUNT(DISTINCT f.Name) AS Name, ROW_NUMBER() OVER(ORDER BY COUNT(DISTINCT f.Name)) as __hibernate_sort_row FROM Fish f GROUP BY f.Type) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row", str.ToString()); } @@ -85,8 +73,7 @@ { var d = new MsSql2005Dialect(); - SqlString str = d.GetLimitString(new SqlString("select distinct c.Contact_Id as Contact1_19_0_, c._Rating as ... [truncated message content] |