Create Arel::Visitors::Oracle12 to provide better top-N query

to support `FETCH FIRST n ROWS` and `OFFSET` for Oracle 12c database

3 files changed, 101 insertions, 0 deletions

diff --git a/lib/arel/visitors.rb b/lib/arel/visitors.rb
index 4a8d254ba7..f492ca2d9d 100644
--- a/lib/arel/visitors.rb
+++ b/lib/arel/visitors.rb
@@ -6,6 +6,7 @@ require 'arel/visitors/postgresql'
 require 'arel/visitors/mysql'
 require 'arel/visitors/mssql'
 require 'arel/visitors/oracle'
+require 'arel/visitors/oracle12'
 require 'arel/visitors/where_sql'
 require 'arel/visitors/dot'
 require 'arel/visitors/ibm_db'
diff --git a/lib/arel/visitors/oracle12.rb b/lib/arel/visitors/oracle12.rb
new file mode 100644
index 0000000000..4a42343c9b
--- /dev/null
+++ b/lib/arel/visitors/oracle12.rb
@@ -0,0 +1,53 @@
+module Arel
+  module Visitors
+    class Oracle12 < Arel::Visitors::ToSql
+      private
+
+      def visit_Arel_Nodes_SelectStatement o, collector
+        # Oracle does not allow LIMIT clause with select for update
+        if o.limit && o.lock
+          o = o.dup
+          o.limit = []
+        end
+
+        super
+      end
+
+      def visit_Arel_Nodes_SelectOptions o, collector
+        collector = maybe_visit o.offset, collector
+        collector = maybe_visit o.limit, collector
+        collector = maybe_visit o.lock, collector
+      end
+
+      def visit_Arel_Nodes_Limit o, collector
+        collector << "FETCH FIRST "
+        collector = visit o.expr, collector
+        collector << " ROWS ONLY"
+      end
+
+      def visit_Arel_Nodes_Offset o, collector
+        collector << "OFFSET "
+        visit o.expr, collector
+        collector << " ROWS"
+      end
+
+      def visit_Arel_Nodes_Except o, collector
+        collector << "( "
+        collector = infix_value o, collector, " MINUS "
+        collector << " )"
+      end
+
+      def visit_Arel_Nodes_UpdateStatement o, collector
+        # Oracle does not allow ORDER BY/LIMIT in UPDATEs.
+        if o.orders.any? && o.limit.nil?
+          # However, there is no harm in silently eating the ORDER BY clause if no LIMIT has been provided,
+          # otherwise let the user deal with the error
+          o = o.dup
+          o.orders = []
+        end
+
+        super
+      end
+    end
+  end
+end
diff --git a/test/visitors/test_oracle12.rb b/test/visitors/test_oracle12.rb
new file mode 100644
index 0000000000..cfa130fcab
--- /dev/null
+++ b/test/visitors/test_oracle12.rb
@@ -0,0 +1,47 @@
+require 'helper'
+
+module Arel
+  module Visitors
+    describe 'the oracle visitor' do
+      before do
+        @visitor = Oracle12.new Table.engine.connection_pool
+      end
+
+      def compile node
+        @visitor.accept(node, Collectors::SQLString.new).value
+      end
+
+      it 'modified except to be minus' do
+        left = Nodes::SqlLiteral.new("SELECT * FROM users WHERE age > 10")
+        right = Nodes::SqlLiteral.new("SELECT * FROM users WHERE age > 20")
+        sql = compile Nodes::Except.new(left, right)
+        sql.must_be_like %{
+          ( SELECT * FROM users WHERE age > 10 MINUS SELECT * FROM users WHERE age > 20 )
+        }
+      end
+
+      it 'generates select options offset then limit' do
+        stmt = Nodes::SelectStatement.new
+        stmt.offset = Nodes::Offset.new(1)
+        stmt.limit = Nodes::Limit.new(Nodes.build_quoted(10))
+        sql = compile(stmt)
+        sql.must_be_like "SELECT OFFSET 1 ROWS FETCH FIRST 10 ROWS ONLY"
+      end
+
+      describe 'locking' do
+        it 'removes limit when locking' do
+          stmt = Nodes::SelectStatement.new
+          stmt.limit = Nodes::Limit.new(Nodes.build_quoted(10))
+          stmt.lock = Nodes::Lock.new(Arel.sql('FOR UPDATE'))
+          sql = compile(stmt)
+          sql.must_be_like "SELECT FOR UPDATE"
+        end
+
+        it 'defaults to FOR UPDATE when locking' do
+          node = Nodes::Lock.new(Arel.sql('FOR UPDATE'))
+          compile(node).must_be_like "FOR UPDATE"
+        end
+      end
+    end
+  end
+end