Support ANSI SQL2003 window functions.

3 files changed, 200 insertions, 2 deletions

diff --git a/test/nodes/test_over.rb b/test/nodes/test_over.rb
new file mode 100644
index 0000000000..fcc5078e7b
--- /dev/null
+++ b/test/nodes/test_over.rb
@@ -0,0 +1,40 @@
+require 'helper'
+
+describe Arel::Nodes::Over do
+  describe 'with literal' do
+    it 'should reference the window definition by name' do
+      table = Arel::Table.new :users
+      table[:id].count.over('foo').to_sql.must_be_like %{
+        COUNT("users"."id") OVER "foo"
+      }
+    end
+  end
+
+  describe 'with SQL literal' do
+    it 'should reference the window definition by name' do
+      table = Arel::Table.new :users
+      table[:id].count.over(Arel.sql('foo')).to_sql.must_be_like %{
+        COUNT("users"."id") OVER foo
+      }
+    end
+  end
+
+  describe 'with no expression' do
+    it 'should use empty definition' do
+      table = Arel::Table.new :users
+      table[:id].count.over.to_sql.must_be_like %{
+        COUNT("users"."id") OVER ()
+      }
+    end
+  end
+
+  describe 'with expression' do
+    it 'should use definition in sub-expression' do
+      table = Arel::Table.new :users
+      window = Arel::Nodes::Window.new.order(table['foo'])
+      table[:id].count.over(window).to_sql.must_be_like %{
+        COUNT("users"."id") OVER (ORDER BY \"users\".\"foo\")
+      }
+    end
+  end
+end
diff --git a/test/test_select_manager.rb b/test/test_select_manager.rb
index bd5a4be68b..d68deb3061 100644
--- a/test/test_select_manager.rb
+++ b/test/test_select_manager.rb
@@ -731,6 +731,162 @@ module Arel
       end
     end
 
+    describe 'window definition' do
+      it 'can be empty' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        manager.window('a_window')
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS ()
+        }
+      end
+
+      it 'takes an order' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        manager.window('a_window').order(table['foo'].asc)
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (ORDER BY "users"."foo" ASC)
+        }
+      end
+
+      it 'takes a rows frame, unbounded preceding' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        manager.window('a_window').rows(Arel::Nodes::Preceding.new)
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (ROWS UNBOUNDED PRECEDING)
+        }
+      end
+
+      it 'takes a rows frame, bounded preceding' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        manager.window('a_window').rows(Arel::Nodes::Preceding.new(5))
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (ROWS 5 PRECEDING)
+        }
+      end
+
+      it 'takes a rows frame, unbounded following' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        manager.window('a_window').rows(Arel::Nodes::Following.new)
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (ROWS UNBOUNDED FOLLOWING)
+        }
+      end
+
+      it 'takes a rows frame, bounded following' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        manager.window('a_window').rows(Arel::Nodes::Following.new(5))
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (ROWS 5 FOLLOWING)
+        }
+      end
+
+      it 'takes a rows frame, current row' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        manager.window('a_window').rows(Arel::Nodes::CurrentRow.new)
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (ROWS CURRENT ROW)
+        }
+      end
+
+      it 'takes a rows frame, between two delimiters' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        window = manager.window('a_window')
+        window.frame(
+          Arel::Nodes::Between.new(
+            window.rows,
+            Nodes::And.new([
+              Arel::Nodes::Preceding.new,
+              Arel::Nodes::CurrentRow.new
+            ])))
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+        }
+      end
+
+      it 'takes a range frame, unbounded preceding' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        manager.window('a_window').range(Arel::Nodes::Preceding.new)
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (RANGE UNBOUNDED PRECEDING)
+        }
+      end
+
+      it 'takes a range frame, bounded preceding' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        manager.window('a_window').range(Arel::Nodes::Preceding.new(5))
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (RANGE 5 PRECEDING)
+        }
+      end
+
+      it 'takes a range frame, unbounded following' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        manager.window('a_window').range(Arel::Nodes::Following.new)
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (RANGE UNBOUNDED FOLLOWING)
+        }
+      end
+
+      it 'takes a range frame, bounded following' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        manager.window('a_window').range(Arel::Nodes::Following.new(5))
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (RANGE 5 FOLLOWING)
+        }
+      end
+
+      it 'takes a range frame, current row' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        manager.window('a_window').range(Arel::Nodes::CurrentRow.new)
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (RANGE CURRENT ROW)
+        }
+      end
+
+      it 'takes a range frame, between two delimiters' do
+        table   = Table.new :users
+        manager = Arel::SelectManager.new Table.engine
+        manager.from table
+        window = manager.window('a_window')
+        window.frame(
+          Arel::Nodes::Between.new(
+            window.range,
+            Nodes::And.new([
+              Arel::Nodes::Preceding.new,
+              Arel::Nodes::CurrentRow.new
+            ])))
+        manager.to_sql.must_be_like %{
+          SELECT FROM "users" WINDOW "a_window" AS (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+        }
+      end
+    end
+
     describe 'delete' do
       it "copies from" do
         engine  = EngineProxy.new Table.engine
diff --git a/test/visitors/test_depth_first.rb b/test/visitors/test_depth_first.rb
index e62ce5266f..9c01fb8fcc 100644
--- a/test/visitors/test_depth_first.rb
+++ b/test/visitors/test_depth_first.rb
@@ -179,7 +179,8 @@ module Arel
         core.froms = :b
         core.wheres << :c
         core.groups << :d
-        core.having = :e
+        core.windows << :e
+        core.having = :f
 
         @visitor.accept core
         assert_equal [
@@ -188,7 +189,8 @@ module Arel
           core.source,
           :c, core.wheres,
           :d, core.groups,
-          :e,
+          :e, core.windows,
+          :f,
           core], @collector.calls
       end