public void ShouldApplyLimitUsingTop() { 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 VistaDBQueryPager().ApplyLimit(sql, 5); var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant()); Assert.IsTrue(expected.SequenceEqual(modified)); }
public void ShouldCopeWithAliasedDefaultSortColumn() { 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 VistaDBQueryPager().ApplyPaging(sql, 30, 10); var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant()); Assert.IsTrue(expected.SequenceEqual(modified)); }
public void ShouldApplyPagingUsingOrderByFirstColumnIfNotAlreadyOrdered() { 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 VistaDBQueryPager().ApplyPaging(sql, 10, 20); var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant()); Assert.IsTrue(expected.SequenceEqual(modified)); }