public void ShouldApplyLimitUsingTop_CTE()
{
var sql = "select a,b,c from d where a = 1 order by c";
var expected = new[] { "select top 5 a,b,c from d where a = 1 order by c" };
var pagedSql = new SqlAnywhereQueryPager(true).ApplyLimit(sql, 5);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.IsTrue(expected.SequenceEqual(modified));
}
public void ShouldCopeWithAliasedDefaultSortColumn_CTE()
{
var sql = "select [a] as [foo],[b],[c] from [d] where [a] = 1";
var expected = new[]{
"with __data as (select [a] as [foo],[b],[c], row_number() over(order by [a]) as [_#_] from [d] where [a] = 1)"
+ " select [foo],[b],[c] from __data where [_#_] between 31 and 40"};
var pagedSql = new SqlAnywhereQueryPager(true).ApplyPaging(sql, 30, 10);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.AreEqual(String.Join("\n", expected), String.Join("\n", modified));
}
public void ShouldApplyPagingUsingOrderByFirstColumnIfNotAlreadyOrdered_CTE()
{
var sql = "select a,b,c from d where a = 1";
var expected = new[]{
"with __data as (select a,b,c, row_number() over(order by a) as [_#_] from d where a = 1)"
+ " select a,b,c from __data where [_#_] between 11 and 30"};
var pagedSql = new SqlAnywhereQueryPager(true).ApplyPaging(sql, 10, 20);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.AreEqual(String.Join("\n", expected), String.Join("\n", modified));
}
public void ShouldCopeWithNoSkip_CTE()
{
var sql = "select a,b,c from d where a = 1";
var expected = new[]{
"with __data as (select a,b,c, row_number() over(order by a) as [_#_] from d where a = 1)"
+ " select a,b,c from __data where [_#_] <= 15"};
var pagedSql = new SqlAnywhereQueryPager(true).ApplyPaging(sql, 0, 15);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.AreEqual(String.Join("\n", expected), String.Join("\n", modified));
}
public void ShouldNotOutputTopWithASkipOf10AndGreaterThan32757_NoCTE()
{
var sql = "select a,b,c from d where a = 1";
var expected = new[]{
"select a,b,c, cast(number() as int) as [_#_] into #__data from d where a = 1 order by a",
"select a,b,c from #__data where [_#_] between 11 and 32768",
"drop table #__data"};
var pagedSql = new SqlAnywhereQueryPager(false).ApplyPaging(sql, 10, 32758);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.AreEqual(String.Join("\n", expected), String.Join("\n", modified));
}
public void ShouldOutputTopWithATakeLessThan32768_NoCTE()
{
var sql = "select a,b,c from d where a = 1";
var expected = new[] { "select top 32767 a,b,c from d where a = 1 order by a" };
var pagedSql = new SqlAnywhereQueryPager(false).ApplyPaging(sql, 0, 32767);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.AreEqual(String.Join("\n", expected), String.Join("\n", modified));
}
public void ShouldCopeWithNoSkip_NoCTE()
{
var sql = "select a,b,c from d where a = 1";
var expected = new[] { "select top 15 a,b,c from d where a = 1 order by a" };
var pagedSql = new SqlAnywhereQueryPager(false).ApplyPaging(sql, 0, 15);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.AreEqual(String.Join("\n", expected), String.Join("\n", modified));
}
public void ShouldCopeWithNoTake_NoCTE()
{
var sql = "select a,b,c from d where a = 1";
var expected = new[]{
"select a,b,c, cast(number() as int) as [_#_] into #__data from d where a = 1 order by a",
"select a,b,c from #__data where [_#_] >= 31",
"drop table #__data"};
var pagedSql = new SqlAnywhereQueryPager(false).ApplyPaging(sql, 30, Int32.MaxValue);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.AreEqual(String.Join("\n", expected), String.Join("\n", modified));
}
public void ShouldCopeWithAliasedDefaultSortColumn_NoCTE()
{
var sql = "select [a] as [foo],[b],[c] from [d] where [a] = 1";
var expected = new[]{
"select top 40 [a] as [foo],[b],[c], cast(number() as int) as [_#_] into #__data from [d] where [a] = 1 order by [a]",
"select [foo],[b],[c] from #__data where [_#_] between 31 and 40",
"drop table #__data"};
var pagedSql = new SqlAnywhereQueryPager(false).ApplyPaging(sql, 30, 10);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.AreEqual(String.Join("\n", expected), String.Join("\n", modified));
}
public void ShouldApplyPagingUsingOrderByFirstColumnIfNotAlreadyOrdered_NoCTE()
{
var sql = "select a,b,c from d where a = 1";
var expected = new[]{
"select top 30 a,b,c, cast(number() as int) as [_#_] into #__data from d where a = 1 order by a",
"select a,b,c from #__data where [_#_] between 11 and 30",
"drop table #__data"};
var pagedSql = new SqlAnywhereQueryPager(false).ApplyPaging(sql, 10, 20);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.AreEqual(String.Join("\n", expected), String.Join("\n", modified));
}