Menu
▾
▴
[Nhibernate-commit] SF.net SVN: nhibernate:[5867] trunk/nhibernate/src/NHibernate
|
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] |
