Support ANSI SQL2003 window functions.

10 files changed, 189 insertions, 1 deletions

diff --git a/lib/arel/nodes.rb b/lib/arel/nodes.rb
index e37b09a8ac..b9b5353df7 100644
--- a/lib/arel/nodes.rb
+++ b/lib/arel/nodes.rb
@@ -26,6 +26,7 @@ require 'arel/nodes/join_source'
 require 'arel/nodes/delete_statement'
 require 'arel/nodes/table_alias'
 require 'arel/nodes/infix_operation'
+require 'arel/nodes/over'
 
 # nary
 require 'arel/nodes/and'
@@ -38,6 +39,9 @@ require 'arel/nodes/count'
 require 'arel/nodes/values'
 require 'arel/nodes/named_function'
 
+# windows
+require 'arel/nodes/window'
+
 # joins
 require 'arel/nodes/inner_join'
 require 'arel/nodes/outer_join'
diff --git a/lib/arel/nodes/function.rb b/lib/arel/nodes/function.rb
index b6f6644678..5f6056a6b6 100644
--- a/lib/arel/nodes/function.rb
+++ b/lib/arel/nodes/function.rb
@@ -3,6 +3,7 @@ module Arel
     class Function < Arel::Nodes::Node
       include Arel::Expression
       include Arel::Predications
+      include Arel::WindowPredications
       attr_accessor :expressions, :alias, :distinct
 
       def initialize expr, aliaz = nil
diff --git a/lib/arel/nodes/over.rb b/lib/arel/nodes/over.rb
new file mode 100644
index 0000000000..727ccd2dc7
--- /dev/null
+++ b/lib/arel/nodes/over.rb
@@ -0,0 +1,13 @@
+module Arel
+  module Nodes
+
+    class Over < Binary
+      def initialize(left, right = nil)
+        super(left, right)
+      end
+
+      def operator; 'OVER' end
+    end
+
+  end
+end
\ No newline at end of file
diff --git a/lib/arel/nodes/select_core.rb b/lib/arel/nodes/select_core.rb
index bee0a5930c..9b8c4a2a1f 100644
--- a/lib/arel/nodes/select_core.rb
+++ b/lib/arel/nodes/select_core.rb
@@ -1,7 +1,7 @@
 module Arel
   module Nodes
     class SelectCore < Arel::Nodes::Node
-      attr_accessor :top, :projections, :wheres, :groups
+      attr_accessor :top, :projections, :wheres, :groups, :windows
       attr_accessor :having, :source, :set_quantifier
 
       def initialize
@@ -14,6 +14,7 @@ module Arel
         @wheres         = []
         @groups         = []
         @having         = nil
+        @windows        = []
       end
 
       def from
@@ -34,6 +35,7 @@ module Arel
         @wheres      = @wheres.clone
         @groups      = @groups.clone
         @having      = @having.clone if @having
+        @windows     = @windows.clone
       end
     end
   end
diff --git a/lib/arel/nodes/window.rb b/lib/arel/nodes/window.rb
new file mode 100644
index 0000000000..b54eb7fe64
--- /dev/null
+++ b/lib/arel/nodes/window.rb
@@ -0,0 +1,78 @@
+module Arel
+  module Nodes
+    class Window < Arel::Nodes::Node
+      include Arel::Expression
+      attr_accessor :orders, :framing
+
+      def initialize
+        @orders = []
+      end
+
+      def order *expr
+        # FIXME: We SHOULD NOT be converting these to SqlLiteral automatically
+        @orders.concat expr.map { |x|
+          String === x || Symbol === x ? Nodes::SqlLiteral.new(x.to_s) : x
+        }
+        self
+      end
+
+      def frame(expr)
+        raise ArgumentError, "Window frame cannot be set more than once" if @frame
+        @framing = expr
+      end
+
+      def rows(expr = nil)
+        frame(Rows.new(expr))
+      end
+
+      def range(expr = nil)
+        frame(Range.new(expr))
+      end
+
+      def initialize_copy other
+        super
+        @orders = @orders.map { |x| x.clone }
+      end
+    end
+
+    class NamedWindow < Window
+      attr_accessor :name
+
+      def initialize name
+        super()
+        @name = name
+      end
+
+      def initialize_copy other
+        super
+        @name = other.name.clone
+      end
+    end
+
+    class Rows < Unary
+      def initialize(expr = nil)
+        super(expr)
+      end
+    end
+
+    class Range < Unary
+      def initialize(expr = nil)
+        super(expr)
+      end
+    end
+
+    class CurrentRow < Arel::Nodes::Node; end
+
+    class Preceding < Unary
+      def initialize(expr = nil)
+        super(expr)
+      end
+    end
+
+    class Following < Unary
+      def initialize(expr = nil)
+        super(expr)
+      end
+    end
+  end
+end
\ No newline at end of file
diff --git a/lib/arel/select_manager.rb b/lib/arel/select_manager.rb
index 32f833f686..d20faa6eb3 100644
--- a/lib/arel/select_manager.rb
+++ b/lib/arel/select_manager.rb
@@ -126,6 +126,12 @@ module Arel
       self
     end
 
+    def window name
+      window = Nodes::NamedWindow.new(name)
+      @ctx.windows.push window
+      window
+    end
+
     def project *projections
       # FIXME: converting these to SQLLiterals is probably not good, but
       # rails tests require it.
diff --git a/lib/arel/visitors/depth_first.rb b/lib/arel/visitors/depth_first.rb
index d1ae524db4..10bc24f36b 100644
--- a/lib/arel/visitors/depth_first.rb
+++ b/lib/arel/visitors/depth_first.rb
@@ -110,6 +110,7 @@ module Arel
       alias :visit_Arel_Nodes_Node               :terminal
       alias :visit_Arel_Nodes_SqlLiteral         :terminal
       alias :visit_Arel_Nodes_BindParam          :terminal
+      alias :visit_Arel_Nodes_Window             :terminal
       alias :visit_Arel_SqlLiteral               :terminal
       alias :visit_BigDecimal                    :terminal
       alias :visit_Bignum                        :terminal
@@ -136,6 +137,7 @@ module Arel
         visit o.source
         visit o.wheres
         visit o.groups
+        visit o.windows
         visit o.having
       end
 
diff --git a/lib/arel/visitors/dot.rb b/lib/arel/visitors/dot.rb
index 001843d8ba..800b44b602 100644
--- a/lib/arel/visitors/dot.rb
+++ b/lib/arel/visitors/dot.rb
@@ -74,6 +74,23 @@ module Arel
       alias :visit_Arel_Nodes_On                :unary
       alias :visit_Arel_Nodes_Top               :unary
       alias :visit_Arel_Nodes_UnqualifiedColumn :unary
+      alias :visit_Arel_Nodes_Preceding         :unary
+      alias :visit_Arel_Nodes_Following         :unary
+      alias :visit_Arel_Nodes_Rows              :unary
+      alias :visit_Arel_Nodes_Range             :unary
+
+      def window o
+        visit_edge o, "orders"
+        visit_edge o, "framing"
+      end
+      alias :visit_Arel_Nodes_Window            :window
+
+      def named_window o
+        visit_edge o, "orders"
+        visit_edge o, "framing"
+        visit_edge o, "name"        
+      end
+      alias :visit_Arel_Nodes_NamedWindow       :named_window
 
       def function o
         visit_edge o, "expressions"
@@ -103,6 +120,7 @@ module Arel
         visit_edge o, "source"
         visit_edge o, "projections"
         visit_edge o, "wheres"
+        visit_edge o, "windows"
       end
 
       def visit_Arel_Nodes_SelectStatement o
@@ -159,6 +177,7 @@ module Arel
       alias :visit_Arel_Nodes_NotEqual           :binary
       alias :visit_Arel_Nodes_NotIn              :binary
       alias :visit_Arel_Nodes_Or                 :binary
+      alias :visit_Arel_Nodes_Over               :binary
 
       def visit_String o
         @node_stack.last.fields << o
diff --git a/lib/arel/visitors/to_sql.rb b/lib/arel/visitors/to_sql.rb
index 64f96b44dd..c22df6289d 100644
--- a/lib/arel/visitors/to_sql.rb
+++ b/lib/arel/visitors/to_sql.rb
@@ -136,6 +136,7 @@ key on UpdateManager using UpdateManager#key=
           ("WHERE #{o.wheres.map { |x| visit x }.join ' AND ' }" unless o.wheres.empty?),
           ("GROUP BY #{o.groups.map { |x| visit x }.join ', ' }" unless o.groups.empty?),
           (visit(o.having) if o.having),
+          ("WINDOW #{o.windows.map { |x| visit x }.join ', ' }" unless o.windows.empty?)
         ].compact.join ' '
       end
 
@@ -175,6 +176,59 @@ key on UpdateManager using UpdateManager#key=
         "( #{visit o.left} EXCEPT #{visit o.right} )"
       end
 
+      def visit_Arel_Nodes_NamedWindow o
+        "#{quote_column_name o.name} AS #{visit_Arel_Nodes_Window o}"
+      end
+
+      def visit_Arel_Nodes_Window o
+        s = [
+          ("ORDER BY #{o.orders.map { |x| visit(x) }.join(', ')}" unless o.orders.empty?),
+          (visit o.framing if o.framing)
+        ].compact.join ' '
+        "(#{s})"
+      end
+
+      def visit_Arel_Nodes_Rows o
+        if o.expr
+          "ROWS #{visit o.expr}"
+        else
+          "ROWS"
+        end
+      end
+
+      def visit_Arel_Nodes_Range o
+        if o.expr
+          "RANGE #{visit o.expr}"
+        else
+          "RANGE"
+        end
+      end
+
+      def visit_Arel_Nodes_Preceding o
+        "#{o.expr ? visit(o.expr) : 'UNBOUNDED'} PRECEDING"
+      end
+
+      def visit_Arel_Nodes_Following o
+        "#{o.expr ? visit(o.expr) : 'UNBOUNDED'} FOLLOWING"
+      end
+
+      def visit_Arel_Nodes_CurrentRow o
+        "CURRENT ROW"
+      end
+
+      def visit_Arel_Nodes_Over o
+        case o.right
+          when nil
+            "#{visit o.left} OVER ()"
+          when Arel::Nodes::SqlLiteral
+            "#{visit o.left} OVER #{visit o.right}"
+          when String, Symbol
+            "#{visit o.left} OVER #{quote_column_name o.right.to_s}"
+          else
+            "#{visit o.left} OVER #{visit o.right}"
+        end
+      end
+
       def visit_Arel_Nodes_Having o
         "HAVING #{visit o.expr}"
       end
diff --git a/lib/arel/window_predications.rb b/lib/arel/window_predications.rb
new file mode 100644
index 0000000000..71844eab53
--- /dev/null
+++ b/lib/arel/window_predications.rb
@@ -0,0 +1,9 @@
+module Arel
+  module WindowPredications
+
+    def over(expr = nil)
+      Nodes::Over.new(self, expr)
+    end
+
+  end
+end
\ No newline at end of file