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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
|
# 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_In(o, collector)
if Array === o.right && !o.right.empty?
o.right.delete_if { |value| unboundable?(value) }
end
if Array === o.right && o.right.empty?
collector << "1=0"
else
first = true
o.right.each_slice(in_clause_length) do |sliced_o_right|
collector << " OR " unless first
first = false
collector = visit o.left, collector
collector << " IN ("
visit(sliced_o_right, collector)
collector << ")"
end
end
collector
end
def visit_Arel_Nodes_NotIn(o, collector)
if Array === o.right && !o.right.empty?
o.right.delete_if { |value| unboundable?(value) }
end
if Array === o.right && o.right.empty?
collector << "1=1"
else
first = true
o.right.each_slice(in_clause_length) do |sliced_o_right|
collector << " AND " unless first
first = false
collector = visit o.left, collector
collector << " NOT IN ("
visit(sliced_o_right, collector)
collector << ")"
end
end
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
def in_clause_length
1000
end
end
end
end
|