aboutsummaryrefslogtreecommitdiffstats
path: root/lib/arel/visitors/oracle.rb
blob: 875b0e5b6a6c7aa3114feb23bfb111e7ee8d4c35 (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
module Arel
  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)
          collector << ") raw_sql_
                WHERE rownum <= #{offset.expr.to_i + limit}
              )
              WHERE "
          return visit(offset, collector)
        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
          end
          i += 1 if array[i].count('(') == array[i].count(')')
        end
        array
      end

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

    end
  end
end