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
|
# frozen_string_literal: true
module Arel # :nodoc: all
module Visitors
class MSSQL < Arel::Visitors::ToSql
RowNumber = Struct.new :children
def initialize(*)
@primary_keys = {}
super
end
private
def visit_Arel_Nodes_IsNotDistinctFrom(o, collector)
right = o.right
if right.nil?
collector = visit o.left, collector
collector << " IS NULL"
else
collector << "EXISTS (VALUES ("
collector = visit o.left, collector
collector << ") INTERSECT VALUES ("
collector = visit right, collector
collector << "))"
end
end
def visit_Arel_Nodes_IsDistinctFrom(o, collector)
if o.right.nil?
collector = visit o.left, collector
collector << " IS NOT NULL"
else
collector << "NOT "
visit_Arel_Nodes_IsNotDistinctFrom o, collector
end
end
def visit_Arel_Visitors_MSSQL_RowNumber(o, collector)
collector << "ROW_NUMBER() OVER (ORDER BY "
inject_join(o.children, collector, ", ") << ") as _row_num"
end
def visit_Arel_Nodes_SelectStatement(o, collector)
if !o.limit && !o.offset
return super
end
is_select_count = false
o.cores.each { |x|
core_order_by = row_num_literal determine_order_by(o.orders, x)
if select_count? x
x.projections = [core_order_by]
is_select_count = true
else
x.projections << core_order_by
end
}
if is_select_count
# fixme count distinct wouldn't work with limit or offset
collector << "SELECT COUNT(1) as count_id FROM ("
end
collector << "SELECT _t.* FROM ("
collector = o.cores.inject(collector) { |c, x|
visit_Arel_Nodes_SelectCore x, c
}
collector << ") as _t WHERE #{get_offset_limit_clause(o)}"
if is_select_count
collector << ") AS subquery"
else
collector
end
end
def visit_Arel_Nodes_SelectCore(o, collector)
collector = super
maybe_visit o.optimizer_hints, collector
end
def visit_Arel_Nodes_OptimizerHints(o, collector)
hints = o.expr.map { |v| sanitize_as_sql_comment(v) }.join(", ")
collector << "OPTION (#{hints})"
end
def get_offset_limit_clause(o)
first_row = o.offset ? o.offset.expr.to_i + 1 : 1
last_row = o.limit ? o.limit.expr.to_i - 1 + first_row : nil
if last_row
" _row_num BETWEEN #{first_row} AND #{last_row}"
else
" _row_num >= #{first_row}"
end
end
def visit_Arel_Nodes_DeleteStatement(o, collector)
collector << "DELETE "
if o.limit
collector << "TOP ("
visit o.limit.expr, collector
collector << ") "
end
collector << "FROM "
collector = visit o.relation, collector
if o.wheres.any?
collector << " WHERE "
inject_join o.wheres, collector, " AND "
else
collector
end
end
def collect_optimizer_hints(o, collector)
collector
end
def determine_order_by(orders, x)
if orders.any?
orders
elsif x.groups.any?
x.groups
else
pk = find_left_table_pk(x.froms)
pk ? [pk] : []
end
end
def row_num_literal(order_by)
RowNumber.new order_by
end
def select_count?(x)
x.projections.length == 1 && Arel::Nodes::Count === x.projections.first
end
# FIXME raise exception of there is no pk?
def find_left_table_pk(o)
if o.kind_of?(Arel::Nodes::Join)
find_left_table_pk(o.left)
elsif o.instance_of?(Arel::Table)
find_primary_key(o)
end
end
def find_primary_key(o)
@primary_keys[o.name] ||= begin
primary_key_name = @connection.primary_key(o.name)
# some tables might be without primary key
primary_key_name && o[primary_key_name]
end
end
end
end
end
|
