From a1a6fbc189d0cb8c44606eafcb8bda7a010554c0 Mon Sep 17 00:00:00 2001 From: Alexander Staubo Date: Wed, 22 Feb 2012 15:25:10 +0100 Subject: Support ANSI SQL2003 window functions. --- lib/arel.rb | 1 + lib/arel/nodes.rb | 4 + lib/arel/nodes/function.rb | 1 + lib/arel/nodes/over.rb | 13 ++++ lib/arel/nodes/select_core.rb | 4 +- lib/arel/nodes/window.rb | 78 +++++++++++++++++++ lib/arel/select_manager.rb | 6 ++ lib/arel/visitors/depth_first.rb | 2 + lib/arel/visitors/dot.rb | 19 +++++ lib/arel/visitors/to_sql.rb | 54 +++++++++++++ lib/arel/window_predications.rb | 9 +++ test/nodes/test_over.rb | 40 ++++++++++ test/test_select_manager.rb | 156 ++++++++++++++++++++++++++++++++++++++ test/visitors/test_depth_first.rb | 6 +- 14 files changed, 390 insertions(+), 3 deletions(-) create mode 100644 lib/arel/nodes/over.rb create mode 100644 lib/arel/nodes/window.rb create mode 100644 lib/arel/window_predications.rb create mode 100644 test/nodes/test_over.rb diff --git a/lib/arel.rb b/lib/arel.rb index 9d08caebeb..b27da2fb05 100644 --- a/lib/arel.rb +++ b/lib/arel.rb @@ -3,6 +3,7 @@ require 'arel/factory_methods' require 'arel/expressions' require 'arel/predications' +require 'arel/window_predications' require 'arel/math' require 'arel/alias_predication' require 'arel/order_predications' 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 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 -- cgit v1.2.3 From 2db4ec6a28a59a3f74a4979ae5bc117e5c7573c4 Mon Sep 17 00:00:00 2001 From: Alexander Staubo Date: Thu, 23 Feb 2012 14:06:05 +0100 Subject: Add #extract, which produces ANSI SQL function EXTRACT( from ). --- lib/arel/expressions.rb | 4 ++++ lib/arel/nodes.rb | 1 + lib/arel/nodes/extract.rb | 23 +++++++++++++++++++++++ lib/arel/visitors/dot.rb | 6 ++++++ lib/arel/visitors/to_sql.rb | 4 ++++ test/nodes/test_extract.rb | 19 +++++++++++++++++++ 6 files changed, 57 insertions(+) create mode 100644 lib/arel/nodes/extract.rb create mode 100644 test/nodes/test_extract.rb diff --git a/lib/arel/expressions.rb b/lib/arel/expressions.rb index d1fbfd83d9..fa18f15b67 100644 --- a/lib/arel/expressions.rb +++ b/lib/arel/expressions.rb @@ -19,5 +19,9 @@ module Arel def average Nodes::Avg.new [self], Nodes::SqlLiteral.new('avg_id') end + + def extract field + Nodes::Extract.new [self], field + end end end diff --git a/lib/arel/nodes.rb b/lib/arel/nodes.rb index b9b5353df7..0477591cae 100644 --- a/lib/arel/nodes.rb +++ b/lib/arel/nodes.rb @@ -36,6 +36,7 @@ require 'arel/nodes/and' # We should make Function a Unary node and deprecate the use of "aliaz" require 'arel/nodes/function' require 'arel/nodes/count' +require 'arel/nodes/extract' require 'arel/nodes/values' require 'arel/nodes/named_function' diff --git a/lib/arel/nodes/extract.rb b/lib/arel/nodes/extract.rb new file mode 100644 index 0000000000..1c9ee78816 --- /dev/null +++ b/lib/arel/nodes/extract.rb @@ -0,0 +1,23 @@ +module Arel + module Nodes + + class Extract < Arel::Nodes::Unary + include Arel::Expression + include Arel::Predications + + attr_accessor :field + attr_accessor :alias + + def initialize expr, field, aliaz = nil + super(expr) + @field = field + @alias = aliaz && SqlLiteral.new(aliaz) + end + + def as aliaz + self.alias = SqlLiteral.new(aliaz) + self + end + end + end +end diff --git a/lib/arel/visitors/dot.rb b/lib/arel/visitors/dot.rb index 800b44b602..8a83ebf48e 100644 --- a/lib/arel/visitors/dot.rb +++ b/lib/arel/visitors/dot.rb @@ -103,6 +103,12 @@ module Arel alias :visit_Arel_Nodes_Avg :function alias :visit_Arel_Nodes_Sum :function + def extract o + visit_edge o, "expressions" + visit_edge o, "alias" + end + alias :visit_Arel_Nodes_Extract :extract + def visit_Arel_Nodes_NamedFunction o visit_edge o, "name" visit_edge o, "expressions" diff --git a/lib/arel/visitors/to_sql.rb b/lib/arel/visitors/to_sql.rb index c22df6289d..a6be451e6f 100644 --- a/lib/arel/visitors/to_sql.rb +++ b/lib/arel/visitors/to_sql.rb @@ -272,6 +272,10 @@ key on UpdateManager using UpdateManager#key= }.join(', ')})#{o.alias ? " AS #{visit o.alias}" : ''}" end + def visit_Arel_Nodes_Extract o + "EXTRACT(#{o.field.to_s.upcase} FROM #{visit o.expr})#{o.alias ? " AS #{visit o.alias}" : ''}" + end + def visit_Arel_Nodes_Count o "COUNT(#{o.distinct ? 'DISTINCT ' : ''}#{o.expressions.map { |x| visit x diff --git a/test/nodes/test_extract.rb b/test/nodes/test_extract.rb new file mode 100644 index 0000000000..bd1dfa4750 --- /dev/null +++ b/test/nodes/test_extract.rb @@ -0,0 +1,19 @@ +require 'helper' + +describe Arel::Nodes::Extract do + it "should extract field" do + table = Arel::Table.new :users + table[:timestamp].extract('date').to_sql.must_be_like %{ + EXTRACT(DATE FROM "users"."timestamp") + } + end + + describe "as" do + it 'should alias the extract' do + table = Arel::Table.new :users + table[:timestamp].extract('date').as('foo').to_sql.must_be_like %{ + EXTRACT(DATE FROM "users"."timestamp") AS foo + } + end + end +end -- cgit v1.2.3 From 74aadecc4f58e73704ac6a6fdaf25e48832374cf Mon Sep 17 00:00:00 2001 From: Alexander Staubo Date: Thu, 23 Feb 2012 14:16:44 +0100 Subject: Must support aliases for OVER operator. --- lib/arel/nodes/over.rb | 2 ++ test/nodes/test_over.rb | 9 +++++++++ 2 files changed, 11 insertions(+) diff --git a/lib/arel/nodes/over.rb b/lib/arel/nodes/over.rb index 727ccd2dc7..21d1b5029e 100644 --- a/lib/arel/nodes/over.rb +++ b/lib/arel/nodes/over.rb @@ -2,6 +2,8 @@ module Arel module Nodes class Over < Binary + include Arel::AliasPredication + def initialize(left, right = nil) super(left, right) end diff --git a/test/nodes/test_over.rb b/test/nodes/test_over.rb index fcc5078e7b..0bdd665e56 100644 --- a/test/nodes/test_over.rb +++ b/test/nodes/test_over.rb @@ -1,6 +1,15 @@ require 'helper' describe Arel::Nodes::Over do + describe 'as' do + it 'should alias the expression' do + table = Arel::Table.new :users + table[:id].count.over.as('foo').to_sql.must_be_like %{ + COUNT("users"."id") OVER () AS foo + } + end + end + describe 'with literal' do it 'should reference the window definition by name' do table = Arel::Table.new :users -- cgit v1.2.3