Revision: 5135
http://nhibernate.svn.sourceforge.net/nhibernate/?rev=5135&view=rev
Author: julian-maughan
Date: 2010-08-10 17:19:39 +0000 (Tue, 10 Aug 2010)
Log Message:
-----------
Add support for variable limit-only queries (NH-1653)
Modified Paths:
--------------
trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs
Modified: trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs
===================================================================
--- trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs 2010-08-10 13:29:39 UTC (rev 5134)
+++ trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs 2010-08-10 17:19:39 UTC (rev 5135)
@@ -23,78 +23,74 @@
/// </summary>
/// <param name="querySqlString">The <see cref="SqlString"/> to base the limit query off of.</param>
/// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param>
- /// <param name="last">Maximum number of rows to be returned by the query</param>
+ /// <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"/> with the <c>LIMIT</c> clause applied.</returns>
/// <remarks>
- /// The <c>LIMIT</c> SQL will look like
- /// <code>
- ///
- /// SELECT
- /// TOP last (columns)
- /// FROM
- /// (SELECT (columns), ROW_NUMBER() OVER(ORDER BY {original order by, with un-aliased column names) as __hibernate_sort_row
- /// {original from}) as query
- /// WHERE query.__hibernate_sort_row > offset
- /// ORDER BY query.__hibernate_sort_row
- ///
- /// </code>
- ///
- /// Note that we need to add explicitly specify the columns, because we need to be able to use them
- /// in a paged subselect. NH-1155
+ /// 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 last, int? offsetParameterIndex, int? limitParameterIndex)
+ public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit, int? offsetParameterIndex, int? limitParameterIndex)
{
- //dont do this paging code if there is no offset, use the
- //sql 2000 dialect since it just uses a top statement
+ SqlStringBuilder result = new SqlStringBuilder();
+
if (offset == 0)
{
- return base.GetLimitString(querySqlString, offset, last);
+ int insertPoint = this.GetAfterSelectInsertPoint(querySqlString);
+
+ return result
+ .Add(querySqlString.Substring(0, insertPoint))
+ .Add(" TOP (")
+ .Add(limitParameterIndex == null ? Parameter.Placeholder : Parameter.WithIndex(limitParameterIndex.Value))
+ .Add(")")
+ .Add(querySqlString.Substring(insertPoint))
+ .ToSqlString();
}
int fromIndex = GetFromIndex(querySqlString);
SqlString select = querySqlString.Substring(0, fromIndex);
+
List<SqlString> columnsOrAliases;
Dictionary<SqlString, SqlString> aliasToColumn;
ExtractColumnOrAliasNames(select, out columnsOrAliases, out aliasToColumn);
-
+
int orderIndex = querySqlString.LastIndexOfCaseInsensitive(" order by ");
- SqlString from;
+ 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()))
{
- from = querySqlString.Substring(fromIndex, orderIndex - fromIndex).Trim();
+ fromAndWhere = querySqlString.Substring(fromIndex, orderIndex - fromIndex).Trim();
SqlString orderBy = querySqlString.Substring(orderIndex).Trim();
sortExpressions = orderBy.Substring(9).Split(",");
}
else
{
- from = querySqlString.Substring(fromIndex).Trim();
+ fromAndWhere = querySqlString.Substring(fromIndex).Trim();
// Use dummy sort to avoid errors
sortExpressions = new[] {new SqlString("CURRENT_TIMESTAMP"),};
- }
-
- SqlStringBuilder result =
- new SqlStringBuilder()
- .Add("SELECT TOP (")
- .Add(Parameter.WithIndex(limitParameterIndex.Value))
- .Add(") ")
- .Add(StringHelper.Join(", ", columnsOrAliases))
- .Add(" FROM (")
- .Add(select)
- .Add(", ROW_NUMBER() OVER(ORDER BY ");
-
+ }
+
+ result
+ .Add("SELECT TOP (")
+ .Add(limitParameterIndex == null ? Parameter.Placeholder : Parameter.WithIndex(limitParameterIndex.Value))
+ .Add(") ")
+ .Add(StringHelper.Join(", ", columnsOrAliases))
+ .Add(" FROM (")
+ .Add(select)
+ .Add(", ROW_NUMBER() OVER(ORDER BY ");
+
AppendSortExpressions(aliasToColumn, sortExpressions, result);
result
.Add(") as __hibernate_sort_row ")
- .Add(from)
+ .Add(fromAndWhere)
.Add(") as query WHERE query.__hibernate_sort_row > ")
- .Add(Parameter.WithIndex(offsetParameterIndex.Value))
+ .Add(offsetParameterIndex == null ? Parameter.Placeholder : Parameter.WithIndex(offsetParameterIndex.Value))
.Add(" ORDER BY query.__hibernate_sort_row");
-
+
return result.ToSqlString();
}
@@ -143,6 +139,19 @@
}
return fromIndex;
}
+
+ private int GetAfterSelectInsertPoint(SqlString sql)
+ {
+ if (sql.StartsWithCaseInsensitive("select distinct"))
+ {
+ return 15;
+ }
+ else if (sql.StartsWithCaseInsensitive("select"))
+ {
+ return 6;
+ }
+ throw new NotSupportedException("The query should start with 'SELECT' or 'SELECT DISTINCT'");
+ }
private static void ExtractColumnOrAliasNames(SqlString select, out List<SqlString> columnsOrAliases,
out Dictionary<SqlString, SqlString> aliasToColumn)
@@ -451,4 +460,4 @@
}
}
}
-}
+}
\ No newline at end of file
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
