aboutsummaryrefslogtreecommitdiffstats
path: root/activerecord/lib/arel/visitors/oracle.rb
blob: f96bf65ee593e570ca638a9fc22ac172f73de7d2 (plain) (blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
# frozen_string_literal: true

module Arel # :nodoc: all
  module Visitors
    class Oracle < Arel::Visitors::ToSql
      private

        def visit_Arel_Nodes_SelectStatement(o, collector)
          o = order_hacks(o)

          # if need to select first records without ORDER BY and GROUP BY and without DISTINCT
          # then can use simple ROWNUM in WHERE clause
          if o.limit && o.orders.empty? && o.cores.first.groups.empty? && !o.offset && o.cores.first.set_quantifier.class.to_s !~ /Distinct/
            o.cores.last.wheres.push Nodes::LessThanOrEqual.new(
              Nodes::SqlLiteral.new("ROWNUM"), o.limit.expr
            )
            return super
          end

          if o.limit && o.offset
            o        = o.dup
            limit    = o.limit.expr
            offset   = o.offset
            o.offset = nil
            collector << "
                SELECT * FROM (
                  SELECT raw_sql_.*, rownum raw_rnum_
                  FROM ("

            collector = super(o, collector)

            if offset.expr.is_a? Nodes::BindParam
              collector << ") raw_sql_ WHERE rownum <= ("
              collector = visit offset.expr, collector
              collector << " + "
              collector = visit limit, collector
              collector << ") ) WHERE raw_rnum_ > "
              collector = visit offset.expr, collector
              return collector
            else
              collector << ") raw_sql_
                  WHERE rownum <= #{offset.expr.to_i + limit}
                )
                WHERE "
              return visit(offset, collector)
            end
          end

          if o.limit
            o       = o.dup
            limit   = o.limit.expr
            collector << "SELECT * FROM ("
            collector = super(o, collector)
            collector << ") WHERE ROWNUM <= "
            return visit limit, collector
          end

          if o.offset
            o        = o.dup
            offset   = o.offset
            o.offset = nil
            collector << "SELECT * FROM (
                  SELECT raw_sql_.*, rownum raw_rnum_
                  FROM ("
            collector = super(o, collector)
            collector << ") raw_sql_
                )
                WHERE "
            return visit offset, collector
          end

          super
        end

        def visit_Arel_Nodes_Limit(o, collector)
          collector
        end

        def visit_Arel_Nodes_Offset(o, collector)
          collector << "raw_rnum_ > "
          visit o.expr, collector
        end

        def visit_Arel_Nodes_Except(o, collector)
          collector << "( "
          collector = infix_value o, collector, " MINUS "
          collector << " )"
        end

        def visit_Arel_Nodes_UpdateStatement(o, collector)
          # Oracle does not allow ORDER BY/LIMIT in UPDATEs.
          if o.orders.any? && o.limit.nil?
            # However, there is no harm in silently eating the ORDER BY clause if no LIMIT has been provided,
            # otherwise let the user deal with the error
            o = o.dup
            o.orders = []
          end

          super
        end

        ###
        # Hacks for the order clauses specific to Oracle
        def order_hacks(o)
          return o if o.orders.empty?
          return o unless o.cores.any? do |core|
            core.projections.any? do |projection|
              /FIRST_VALUE/ === projection
            end
          end
          # Previous version with join and split broke ORDER BY clause
          # if it contained functions with several arguments (separated by ',').
          #
          # orders   = o.orders.map { |x| visit x }.join(', ').split(',')
          orders   = o.orders.map do |x|
            string = visit(x, Arel::Collectors::SQLString.new).value
            if string.include?(",")
              split_order_string(string)
            else
              string
            end
          end.flatten
          o.orders = []
          orders.each_with_index do |order, i|
            o.orders <<
              Nodes::SqlLiteral.new("alias_#{i}__#{' DESC' if /\bdesc$/i === order}")
          end
          o
        end

        # Split string by commas but count opening and closing brackets
        # and ignore commas inside brackets.
        def split_order_string(string)
          array = []
          i = 0
          string.split(",").each do |part|
            if array[i]
              array[i] << "," << part
            else
              # to ensure that array[i] will be String and not Arel::Nodes::SqlLiteral
              array[i] = part.to_s
            end
            i += 1 if array[i].count("(") == array[i].count(")")
          end
          array
        end

        def visit_Arel_Nodes_BindParam(o, collector)
          collector.add_bind(o.value) { |i| ":a#{i}" }
        end

        def is_distinct_from(o, collector)
          collector << "DECODE("
          collector = visit [o.left, o.right, 0, 1], collector
          collector << ")"
        end
    end
  end
end