aboutsummaryrefslogtreecommitdiffstats
path: root/activerecord/lib/arel/visitors/mssql.rb
blob: 92eb94f802ba72b5586d71b52d244e1889492f5d (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
# 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