I thought 2008 also introduced a new syntax, or was I just remembering
what I was reading about v.Next?
Patrick Earl
On Fri, Dec 30, 2011 at 11:45 PM, CSharper <[email protected]> wrote:
> 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" />
>
>
>
