Menu
▾
▴
[Nhibernate-commit] SF.net SVN: nhibernate:[3946] trunk/nhibernate/src/NHibernate
|
From: <te...@us...> - 2008-12-12 14:57:23
|
Revision: 3946
http://nhibernate.svn.sourceforge.net/nhibernate/?rev=3946&view=rev
Author: tehlike
Date: 2008-12-12 14:57:17 +0000 (Fri, 12 Dec 2008)
Log Message:
-----------
Another proposal for MsSql2005 Paging issue, by Dana Naideth
Modified Paths:
--------------
trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs
trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs
Modified: trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs
===================================================================
--- trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs 2008-12-12 14:40:38 UTC (rev 3945)
+++ trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs 2008-12-12 14:57:17 UTC (rev 3946)
@@ -29,12 +29,13 @@
/// The <c>LIMIT</c> SQL will look like
/// <code>
///
- /// SELECT TOP last (columns) FROM (
- /// SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_1__ {sort direction 1} [, __hibernate_sort_expr_2__ {sort direction 2}, ...]) as row, (query.columns) FROM (
- /// {original select query part}, {sort field 1} as __hibernate_sort_expr_1__ [, {sort field 2} as __hibernate_sort_expr_2__, ...]
- /// {remainder of original query minus the order by clause}
- /// ) query
- /// ) page WHERE page.row > offset
+ /// 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>
///
@@ -76,58 +77,26 @@
{
from = querySqlString.Substring(fromIndex).Trim();
// Use dummy sort to avoid errors
- sortExpressions = new[] { new SqlString("CURRENT_TIMESTAMP"), };
+ sortExpressions = new[] {new SqlString("CURRENT_TIMESTAMP"),};
}
SqlStringBuilder result =
- new SqlStringBuilder().Add("SELECT TOP ").Add(last.ToString()).Add(" ").Add(StringHelper.Join(", ", columnsOrAliases))
- .Add(" FROM (SELECT ROW_NUMBER() OVER(ORDER BY ");
+ new SqlStringBuilder()
+ .Add("SELECT TOP ")
+ .Add(last.ToString())
+ .Add(" ")
+ .Add(StringHelper.Join(", ", columnsOrAliases))
+ .Add(" FROM (")
+ .Add(select)
+ .Add(", ROW_NUMBER() OVER(ORDER BY ");
- AppendSortExpressions(columnsOrAliases, sortExpressions, result);
+ AppendSortExpressions(aliasToColumn, sortExpressions, result);
- result.Add(") as row, ");
-
- for (int i = 0; i < columnsOrAliases.Count; i++)
- {
- result.Add("query.").Add(columnsOrAliases[i]);
- bool notLastColumn = i != columnsOrAliases.Count - 1;
- if (notLastColumn)
- {
- result.Add(", ");
- }
- }
- for (int i = 0; i < sortExpressions.Length; i++)
- {
- SqlString sortExpression = RemoveSortOrderDirection(sortExpressions[i]);
- if (!columnsOrAliases.Contains(sortExpression))
- {
- result.Add(", query.__hibernate_sort_expr_").Add(i.ToString()).Add("__");
- }
- }
-
- result.Add(" FROM (").Add(select);
-
- for (int i = 0; i < sortExpressions.Length; i++)
- {
- SqlString sortExpression = RemoveSortOrderDirection(sortExpressions[i]);
-
- if (columnsOrAliases.Contains(sortExpression))
- {
- continue;
- }
-
- if (aliasToColumn.ContainsKey(sortExpression))
- {
- sortExpression = aliasToColumn[sortExpression];
- }
-
- result.Add(", ").Add(sortExpression).Add(" as __hibernate_sort_expr_").Add(i.ToString()).Add("__");
- }
-
- result.Add(" ").Add(from).Add(") query ) page WHERE page.row > ").Add(offset.ToString()).Add(" ORDER BY ");
-
- AppendSortExpressions(columnsOrAliases, sortExpressions, result);
-
+ result.Add(") as __hibernate_sort_row ")
+ .Add(from)
+ .Add(") as query WHERE query.__hibernate_sort_row > ")
+ .Add(offset.ToString()).Add(" ORDER BY query.__hibernate_sort_row");
+
return result.ToSqlString();
}
@@ -141,7 +110,7 @@
return trimmedExpression.Trim();
}
- private static void AppendSortExpressions(ICollection<SqlString> columnsOrAliases, SqlString[] sortExpressions,
+ private static void AppendSortExpressions(Dictionary<SqlString, SqlString> aliasToColumn, SqlString[] sortExpressions,
SqlStringBuilder result)
{
for (int i = 0; i < sortExpressions.Length; i++)
@@ -152,13 +121,13 @@
}
SqlString sortExpression = RemoveSortOrderDirection(sortExpressions[i]);
- if (columnsOrAliases.Contains(sortExpression))
+ if (aliasToColumn.ContainsKey(sortExpression))
{
- result.Add(sortExpression);
+ result.Add(aliasToColumn[sortExpression]);
}
else
{
- result.Add("__hibernate_sort_expr_").Add(i.ToString()).Add("__");
+ result.Add(sortExpression);
}
if (sortExpressions[i].Trim().EndsWithCaseInsensitive("desc"))
{
@@ -240,7 +209,7 @@
}
columnsOrAliases.Add(new SqlString(alias));
- aliasToColumn[new SqlString(alias)] = new SqlString(token);
+ aliasToColumn[SqlString.Parse(alias)] = SqlString.Parse(token);
}
}
@@ -443,3 +412,4 @@
}
}
}
+
Modified: trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs
===================================================================
--- trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs 2008-12-12 14:40:38 UTC (rev 3945)
+++ trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs 2008-12-12 14:57:17 UTC (rev 3946)
@@ -16,55 +16,65 @@
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"), 0, 10);
+ System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 Contact1_19_0_, Rating2_19_0_ FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__ DESC, __hibernate_sort_expr_1__, __hibernate_sort_expr_2__) as row, query.Contact1_19_0_, query.Rating2_19_0_, query.__hibernate_sort_expr_0__, query.__hibernate_sort_expr_1__, query.__hibernate_sort_expr_2__ FROM (select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_, c.Rating as __hibernate_sort_expr_0__, c.Last_Name as __hibernate_sort_expr_1__, c.First_Name as __hibernate_sort_expr_2__ from dbo.Contact c where COALESCE(c.Rating, 0) > 0) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__ DESC, __hibernate_sort_expr_1__, __hibernate_sort_expr_2__",
+ "SELECT TOP 10 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 > 0 ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT fish.id FROM fish"), 0, 10);
+ System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 id FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.id, query.__hibernate_sort_expr_0__ FROM (SELECT fish.id, CURRENT_TIMESTAMP as __hibernate_sort_expr_0__ FROM fish) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__",
+ "SELECT TOP 10 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 > 0 ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id FROM fish fish_"), 0, 10);
+ System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 id FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.id, query.__hibernate_sort_expr_0__ FROM (SELECT DISTINCT fish_.id, CURRENT_TIMESTAMP as __hibernate_sort_expr_0__ FROM fish fish_) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__",
+ "SELECT TOP 10 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 > 0 ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id as ixx9_ FROM fish fish_"), 0, 10);
+ System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 ixx9_ FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.ixx9_, query.__hibernate_sort_expr_0__ FROM (SELECT DISTINCT fish_.id as ixx9_, CURRENT_TIMESTAMP as __hibernate_sort_expr_0__ FROM fish fish_) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__",
+ "SELECT TOP 10 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 > 0 ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT * FROM fish ORDER BY name"), 5, 15);
+ System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 15 * FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.*, query.__hibernate_sort_expr_0__ FROM (SELECT *, name as __hibernate_sort_expr_0__ FROM fish) query ) page WHERE page.row > 5 ORDER BY __hibernate_sort_expr_0__",
+ "SELECT TOP 15 * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 5 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);
+ System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 28 id, name FROM (SELECT ROW_NUMBER() OVER(ORDER BY name DESC) as row, query.id, query.name FROM (SELECT fish.id, fish.name FROM fish) query ) page WHERE page.row > 7 ORDER BY name DESC",
+ "SELECT TOP 28 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 > 7 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);
+ System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 20 * FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__ DESC) as row, query.*, query.__hibernate_sort_expr_0__ FROM (SELECT *, name as __hibernate_sort_expr_0__ FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t) query ) page WHERE page.row > 10 ORDER BY __hibernate_sort_expr_0__ DESC",
+ "SELECT TOP 20 * 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 > 10 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"), 0, 10);
+ System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 *, some_count FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.*, query.some_count, query.__hibernate_sort_expr_0__ FROM (SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count, CURRENT_TIMESTAMP as __hibernate_sort_expr_0__ FROM fish) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__",
+ "SELECT TOP 10 *, 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 > 0 ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT * FROM fish WHERE scales = ", Parameter.Placeholder), 0, 10);
+ System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 * FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.*, query.__hibernate_sort_expr_0__ FROM (SELECT *, CURRENT_TIMESTAMP as __hibernate_sort_expr_0__ FROM fish WHERE scales = ?) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__",
+ "SELECT TOP 10 * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish WHERE scales = ?) as query WHERE query.__hibernate_sort_row > 0 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)"), 0, 10);
+ System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 Type, Name FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.Type, query.Name, query.__hibernate_sort_expr_0__ FROM (SELECT f.Type, COUNT(DISTINCT f.Name) AS Name, COUNT(DISTINCT f.Name) as __hibernate_sort_expr_0__ FROM Fish f GROUP BY f.Type) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__",
+ "SELECT TOP 10 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 > 0 ORDER BY query.__hibernate_sort_row",
str.ToString());
}
@@ -74,13 +84,15 @@
MsSql2005Dialect d = new MsSql2005Dialect();
SqlString result =
d.GetLimitString(new SqlString("select concat(a.Description,', ', a.Description) as desc from Animal a"), 0, 10);
- Assert.AreEqual("SELECT TOP 10 desc FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.desc, query.__hibernate_sort_expr_0__ FROM (select concat(a.Description,', ', a.Description) as desc, CURRENT_TIMESTAMP as __hibernate_sort_expr_0__ from Animal a) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__", result.ToString());
+ System.Console.WriteLine(result);
+ Assert.AreEqual("SELECT TOP 10 desc FROM (select concat(a.Description,', ', a.Description) as desc, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row from Animal a) as query WHERE query.__hibernate_sort_row > 0 ORDER BY query.__hibernate_sort_row", result.ToString());
// The test use the function "cast" because cast need the keyWork "as" too
SqlString str =
d.GetLimitString(new SqlString("SELECT fish.id, cast('astring, with,comma' as string) as bar FROM fish"), 0, 10);
+ System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 id, bar FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.id, query.bar, query.__hibernate_sort_expr_0__ FROM (SELECT fish.id, cast('astring, with,comma' as string) as bar, CURRENT_TIMESTAMP as __hibernate_sort_expr_0__ FROM fish) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__",
+ "SELECT TOP 10 id, bar FROM (SELECT fish.id, cast('astring, with,comma' as string) as bar, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 0 ORDER BY query.__hibernate_sort_row",
str.ToString());
}
[Test]
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
