Support ANSI SQL2003 window functions.

3 files changed, 75 insertions, 0 deletions

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