From be48ed3071fd6524d0145c4ad3faeb4aafe3eda3 Mon Sep 17 00:00:00 2001 From: arkadiy kraportov Date: Thu, 9 Jun 2011 18:24:54 +0900 Subject: LIMIT and OFFSET support for MS SQL --- test/visitors/test_mssql.rb | 63 +++++++++++++++++++++++++++++++++++++++------ 1 file changed, 55 insertions(+), 8 deletions(-) (limited to 'test/visitors') diff --git a/test/visitors/test_mssql.rb b/test/visitors/test_mssql.rb index ccaea395fe..4e8d5c4bce 100644 --- a/test/visitors/test_mssql.rb +++ b/test/visitors/test_mssql.rb @@ -5,21 +5,68 @@ module Arel describe 'the mssql visitor' do before do @visitor = MSSQL.new Table.engine + @table = Arel::Table.new "users" end - it 'uses TOP to limit results' do + it 'should not modify query if no offset or limit' do stmt = Nodes::SelectStatement.new - stmt.cores.last.top = Nodes::Top.new(1) sql = @visitor.accept(stmt) - sql.must_be_like "SELECT TOP 1" + sql.must_be_like "SELECT" end - it 'uses TOP in updates with a limit' do - stmt = Nodes::UpdateStatement.new - stmt.limit = Nodes::Limit.new(1) - stmt.key = 'id' + it 'should go over table PK if no .order() or .group()' do + stmt = Nodes::SelectStatement.new + stmt.cores.first.from = @table + stmt.limit = Nodes::Limit.new(10) + sql = @visitor.accept(stmt) + sql.must_be_like "SELECT _t.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY \"users\".\"id\") as _row_num FROM \"users\" ) as _t WHERE _row_num BETWEEN 1 AND 10" + end + + it 'should go over query ORDER BY if .order()' do + stmt = Nodes::SelectStatement.new + stmt.limit = Nodes::Limit.new(10) + stmt.orders << Nodes::SqlLiteral.new('order_by') + sql = @visitor.accept(stmt) + sql.must_be_like "SELECT _t.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY order_by) as _row_num) as _t WHERE _row_num BETWEEN 1 AND 10" + end + + it 'should go over query GROUP BY if no .order() and there is .group()' do + stmt = Nodes::SelectStatement.new + stmt.cores.first.groups << Nodes::SqlLiteral.new('group_by') + stmt.limit = Nodes::Limit.new(10) + sql = @visitor.accept(stmt) + sql.must_be_like "SELECT _t.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY group_by) as _row_num GROUP BY group_by) as _t WHERE _row_num BETWEEN 1 AND 10" + end + + it 'should use BETWEEN if both .limit() and .offset' do + stmt = Nodes::SelectStatement.new + stmt.limit = Nodes::Limit.new(10) + stmt.offset = Nodes::Offset.new(20) + sql = @visitor.accept(stmt) + sql.must_be_like "SELECT _t.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY ) as _row_num) as _t WHERE _row_num BETWEEN 21 AND 30" + end + + it 'should use >= if only .offset' do + stmt = Nodes::SelectStatement.new + stmt.offset = Nodes::Offset.new(20) + sql = @visitor.accept(stmt) + sql.must_be_like "SELECT _t.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY ) as _row_num) as _t WHERE _row_num >= 21" + end + + it 'should guard FixNums' do + stmt = Nodes::SelectStatement.new + stmt.limit = Nodes::Limit.new(10) + stmt.cores.first.projections << 1 + sql = @visitor.accept(stmt) + sql.must_be_like "SELECT _t.* FROM (SELECT 1 as _fld_1, ROW_NUMBER() OVER (ORDER BY ) as _row_num) as _t WHERE _row_num BETWEEN 1 AND 10" + end + + it 'should generate subquery for .count' do + stmt = Nodes::SelectStatement.new + stmt.limit = Nodes::Limit.new(10) + stmt.cores.first.projections << Nodes::Count.new('*') sql = @visitor.accept(stmt) - sql.must_be_like "UPDATE NULL WHERE 'id' IN (SELECT TOP 1 'id' )" + sql.must_be_like "SELECT COUNT(1) as count_id FROM (SELECT _t.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY ) as _row_num) as _t WHERE _row_num BETWEEN 1 AND 10) AS subquery" end end -- cgit v1.2.3 From 3da816a77318d28617d0e12739d29d76e372a4f9 Mon Sep 17 00:00:00 2001 From: arkadiy kraportov Date: Fri, 10 Jun 2011 12:47:55 +0900 Subject: remove unnecessary guarding agains literal --- test/visitors/test_mssql.rb | 8 -------- 1 file changed, 8 deletions(-) (limited to 'test/visitors') diff --git a/test/visitors/test_mssql.rb b/test/visitors/test_mssql.rb index 4e8d5c4bce..8b2b756900 100644 --- a/test/visitors/test_mssql.rb +++ b/test/visitors/test_mssql.rb @@ -53,14 +53,6 @@ module Arel sql.must_be_like "SELECT _t.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY ) as _row_num) as _t WHERE _row_num >= 21" end - it 'should guard FixNums' do - stmt = Nodes::SelectStatement.new - stmt.limit = Nodes::Limit.new(10) - stmt.cores.first.projections << 1 - sql = @visitor.accept(stmt) - sql.must_be_like "SELECT _t.* FROM (SELECT 1 as _fld_1, ROW_NUMBER() OVER (ORDER BY ) as _row_num) as _t WHERE _row_num BETWEEN 1 AND 10" - end - it 'should generate subquery for .count' do stmt = Nodes::SelectStatement.new stmt.limit = Nodes::Limit.new(10) -- cgit v1.2.3