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 +++++ 11 files changed, 190 insertions(+), 1 deletion(-) create mode 100644 lib/arel/nodes/over.rb create mode 100644 lib/arel/nodes/window.rb create mode 100644 lib/arel/window_predications.rb (limited to 'lib') 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 -- 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 ++++ 5 files changed, 38 insertions(+) create mode 100644 lib/arel/nodes/extract.rb (limited to 'lib') 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 -- 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 ++ 1 file changed, 2 insertions(+) (limited to 'lib') 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 -- cgit v1.2.3