Hi,
I experimented a little with upcoming SQL Server 2012 and found that
there are some existing red unit tests for SQL Server in HQL
functions. I've copied two function definitions from
SybaseASE15Dialect to SQL 2005 dialect (I don't know if it would work
already in SQL Server 2000) and did a quick test implementation for
the new recommended syntax for using pagination in SQL Server 2012
like
SELECT xxx FROM yyy
ORDER BY zzz OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY
It works, the pagination unit tests are all green (the others too).
But why should we change that paging behaviour?
The new syntax is less intrusive for the SQL string, it still works
when executing a native SQL query containing a common table
expression. If the original select is
WITH q AS (aaa)
SELECT xxx FROM yyy
the paging in SQL Server 2005 dialect fails with an exception because
the sql string does not start with a select. The new paging syntax
should work without any problems for such cases.
How to continue with this? Are there opinions about it? Is somebody
interested in applying a patch and introducing an SQL Server 2012
dialect already?
The changes are as follows:
MsSql2005Dialect.cs
@@ -3,10 +3,11 @@ using System.Collections.Generic;
using System.Data;
using NHibernate.Driver;
using NHibernate.Mapping;
using NHibernate.SqlCommand;
using NHibernate.Util;
+using NHibernate.Dialect.Function;
namespace NHibernate.Dialect
{
public class MsSql2005Dialect : MsSql2000Dialect
{
@@ -34,10 +35,17 @@ namespace NHibernate.Dialect
{
base.RegisterKeywords();
RegisterKeyword("xml");
}
+ protected override void RegisterFunctions()
+ {
+ base.RegisterFunctions();
+ RegisterFunction("extract", new
SQLFunctionTemplate(NHibernateUtil.Int32, "datepart(?1, ?3)"));
+ RegisterFunction("bit_length", new
SQLFunctionTemplate(NHibernateUtil.Int32, "datalength(?1) * 8"));
+ }
+
public override SqlString GetLimitString(SqlString queryString,
SqlString offset, SqlString limit)
{
var result = new SqlStringBuilder();
if (offset == null)
@@ -168,11 +176,11 @@ namespace NHibernate.Dialect
/// Indicates whether the string fragment contains matching
parenthesis
/// </summary>
/// <param name="statement"> the statement to evaluate</param>
/// <returns>true if the statment contains no parenthesis or an
equal number of
/// opening and closing parenthesis;otherwise false </returns>
- private static bool HasMatchingParens(IEnumerable<char>
statement)
+ protected static bool HasMatchingParens(IEnumerable<char>
statement)
{
//unmatched paren count
int unmatchedParen = 0;
//increment the counts based in the opening and closing
parens in
the statement
MsSql2012Dialect.cs:
new file mode 100644
@@ -0,0 +1,38 @@
+using System.Data;
+using NHibernate.Cfg;
+using NHibernate.Dialect.Function;
+using NHibernate.Driver;
+using NHibernate.SqlCommand;
+
+namespace NHibernate.Dialect
+{
+ public class MsSql2012Dialect : MsSql2008Dialect
+ {
+ public override SqlString GetLimitString(SqlString
queryString, SqlString offset, SqlString limit)
+ {
+ var result = new SqlStringBuilder(queryString);
+
+ int orderIndex = queryString.LastIndexOfCaseInsensitive("
order by ");
+
+ //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(queryString.Substring(orderIndex).ToString()))
+ {
+ // Use order by first column if no explicit ordering
is provided
+ result.Add( " ORDER BY ")
+ .Add("1");
+ }
+
+ result.Add(" OFFSET ")
+ .Add(offset ?? new SqlString("0"))
+ .Add(" ROWS");
+
+ if (limit != null)
+ {
+ result.Add(" FETCH FIRST ").Add(limit).Add(" ROWS
ONLY");
+ }
+
+ return result.ToSqlString();
+ }
+ }
+}
\ No newline at end of file
NHibernate.csproj:
@@ -132,10 +132,11 @@
<Compile Include="Connection\ConnectionProviderFactory.cs" />
<Compile Include="Connection\DriverConnectionProvider.cs" />
<Compile Include="Connection\IConnectionProvider.cs" />
<Compile Include="Connection\UserSuppliedConnectionProvider.cs" /
>
<Compile Include="Criterion\IEnhancedProjection.cs" />
+ <Compile Include="Dialect\MsSql2012Dialect.cs" />
<Compile Include="Dialect\DB2Dialect.cs" />
<Compile Include="Dialect\Dialect.cs" />
<Compile Include="Dialect\FirebirdDialect.cs" />
<Compile Include="Dialect\Function\AvgQueryFunctionInfo.cs" />
<Compile Include="Dialect\Function\CountQueryFunctionInfo.cs" />
