to_sql.rb 8.7 KB
Newer Older
A
Aaron Patterson 已提交
1
require 'bigdecimal'
2
require 'date'
A
Aaron Patterson 已提交
3

A
Aaron Patterson 已提交
4 5
module Arel
  module Visitors
A
Aaron Patterson 已提交
6
    class ToSql < Arel::Visitors::Visitor
A
Aaron Patterson 已提交
7
      def initialize engine
A
Aaron Patterson 已提交
8 9
        @engine         = engine
        @connection     = nil
10
        @last_column    = nil
A
Aaron Patterson 已提交
11 12
        @quoted_tables  = {}
        @quoted_columns = {}
A
Aaron Patterson 已提交
13 14 15
      end

      def accept object
16
        @last_column = nil
17 18
        @engine.connection_pool.with_connection do |conn|
          @connection = conn
A
Aaron Patterson 已提交
19
          super
20
        end
A
Aaron Patterson 已提交
21 22 23
      end

      private
A
Aaron Patterson 已提交
24 25 26 27 28 29 30
      def visit_Arel_Nodes_DeleteStatement o
        [
          "DELETE FROM #{visit o.relation}",
          ("WHERE #{o.wheres.map { |x| visit x }.join ' AND '}" unless o.wheres.empty?)
        ].compact.join ' '
      end

A
Aaron Patterson 已提交
31
      def visit_Arel_Nodes_UpdateStatement o
A
Aaron Patterson 已提交
32 33 34 35 36 37 38 39 40 41 42 43 44
        if o.orders.empty? && o.limit.nil?
          wheres = o.wheres
        else
          stmt             = Nodes::SelectStatement.new
          core             = stmt.cores.first
          core.froms       = o.relation
          core.projections = [o.relation.primary_key]
          stmt.limit       = o.limit
          stmt.orders      = o.orders

          wheres = [Nodes::In.new(o.relation.primary_key, [stmt])]
        end

A
Aaron Patterson 已提交
45 46
        [
          "UPDATE #{visit o.relation}",
A
Aaron Patterson 已提交
47
          ("SET #{o.values.map { |value| visit value }.join ', '}" unless o.values.empty?),
A
Aaron Patterson 已提交
48
          ("WHERE #{wheres.map { |x| visit x }.join ' AND '}" unless wheres.empty?)
A
Aaron Patterson 已提交
49 50
        ].compact.join ' '
      end
51

52
      def visit_Arel_Nodes_InsertStatement o
53 54
        [
          "INSERT INTO #{visit o.relation}",
55 56 57 58 59

          ("(#{o.columns.map { |x|
                quote_column_name x.name
            }.join ', '})" unless o.columns.empty?),

60
          (visit o.values if o.values),
61
        ].compact.join ' '
62 63
      end

64
      def visit_Arel_Nodes_Exists o
65 66
        "EXISTS (#{visit o.select_stmt})#{
          o.alias ? " AS #{visit o.alias}" : ''}"
67 68
      end

69
      def visit_Arel_Nodes_Values o
70 71
        "VALUES (#{o.expressions.zip(o.columns).map { |value, column|
          quote(value, column && column.column)
72 73 74
        }.join ', '})"
      end

A
Aaron Patterson 已提交
75 76
      def visit_Arel_Nodes_SelectStatement o
        [
A
Aaron Patterson 已提交
77
          o.cores.map { |x| visit_Arel_Nodes_SelectCore x }.join,
A
Aaron Patterson 已提交
78
          ("ORDER BY #{o.orders.map { |x| visit x }.join(', ')}" unless o.orders.empty?),
A
Aaron Patterson 已提交
79
          ("LIMIT #{visit o.limit}" if o.limit),
A
Aaron Patterson 已提交
80
          (visit(o.offset) if o.offset),
A
Aaron Patterson 已提交
81
          (visit(o.lock) if o.lock),
A
Aaron Patterson 已提交
82 83 84 85
        ].compact.join ' '
      end

      def visit_Arel_Nodes_SelectCore o
A
Aaron Patterson 已提交
86 87
        [
          "SELECT #{o.projections.map { |x| visit x }.join ', '}",
88
          ("FROM #{visit o.froms}" if o.froms),
A
Aaron Patterson 已提交
89
          ("WHERE #{o.wheres.map { |x| visit x }.join ' AND ' }" unless o.wheres.empty?),
A
Aaron Patterson 已提交
90 91
          ("GROUP BY #{o.groups.map { |x| visit x }.join ', ' }" unless o.groups.empty?),
          (visit(o.having) if o.having),
A
Aaron Patterson 已提交
92 93 94
        ].compact.join ' '
      end

A
Aaron Patterson 已提交
95 96 97 98
      def visit_Arel_Nodes_Having o
        "HAVING #{visit o.expr}"
      end

A
Aaron Patterson 已提交
99
      def visit_Arel_Nodes_Offset o
A
Aaron Patterson 已提交
100
        "OFFSET #{visit o.expr}"
A
Aaron Patterson 已提交
101 102
      end

A
Aaron Patterson 已提交
103 104 105 106 107
      # FIXME: this does nothing on SQLLite3, but should do things on other
      # databases.
      def visit_Arel_Nodes_Lock o
      end

A
Aaron Patterson 已提交
108 109 110 111
      def visit_Arel_Nodes_Grouping o
        "(#{visit o.expr})"
      end

112 113 114 115
      def visit_Arel_Nodes_Ordering o
        "#{visit o.expr} #{o.descending? ? 'DESC' : 'ASC'}"
      end

A
Aaron Patterson 已提交
116 117 118 119
      def visit_Arel_Nodes_Group o
        visit o.expr
      end

A
Aaron Patterson 已提交
120
      def visit_Arel_Nodes_Count o
121 122 123
        "COUNT(#{o.distinct ? 'DISTINCT ' : ''}#{o.expressions.map { |x|
          visit x
        }.join(', ')})#{o.alias ? " AS #{visit o.alias}" : ''}"
A
Aaron Patterson 已提交
124 125
      end

A
Aaron Patterson 已提交
126 127 128 129 130
      def visit_Arel_Nodes_Sum o
        "SUM(#{o.expressions.map { |x|
          visit x }.join(', ')})#{o.alias ? " AS #{visit o.alias}" : ''}"
      end

A
Aaron Patterson 已提交
131 132 133 134 135
      def visit_Arel_Nodes_Max o
        "MAX(#{o.expressions.map { |x|
          visit x }.join(', ')})#{o.alias ? " AS #{visit o.alias}" : ''}"
      end

E
Emilio Tagua 已提交
136 137 138 139 140
      def visit_Arel_Nodes_Min o
        "MIN(#{o.expressions.map { |x|
          visit x }.join(', ')})#{o.alias ? " AS #{visit o.alias}" : ''}"
      end

A
Aaron Patterson 已提交
141 142 143 144 145
      def visit_Arel_Nodes_Avg o
        "AVG(#{o.expressions.map { |x|
          visit x }.join(', ')})#{o.alias ? " AS #{visit o.alias}" : ''}"
      end

A
Aaron Patterson 已提交
146 147 148
      def visit_Arel_Nodes_TableAlias o
        "#{visit o.relation} #{quote_table_name o.name}"
      end
A
Aaron Patterson 已提交
149

150 151 152 153
      def visit_Arel_Nodes_Between o
        "#{visit o.left} BETWEEN #{visit o.right}"
      end

154 155 156 157
      def visit_Arel_Nodes_GreaterThanOrEqual o
        "#{visit o.left} >= #{visit o.right}"
      end

A
Aaron Patterson 已提交
158 159 160 161
      def visit_Arel_Nodes_GreaterThan o
        "#{visit o.left} > #{visit o.right}"
      end

A
Aaron Patterson 已提交
162 163 164 165
      def visit_Arel_Nodes_LessThanOrEqual o
        "#{visit o.left} <= #{visit o.right}"
      end

166 167 168 169
      def visit_Arel_Nodes_LessThan o
        "#{visit o.left} < #{visit o.right}"
      end

170 171 172 173 174 175 176 177
      def visit_Arel_Nodes_Matches o
        "#{visit o.left} LIKE #{visit o.right}"
      end

      def visit_Arel_Nodes_DoesNotMatch o
        "#{visit o.left} NOT LIKE #{visit o.right}"
      end

178 179 180 181
      def visit_Arel_Nodes_StringJoin o
        "#{visit o.left} #{visit o.right}"
      end

A
Aaron Patterson 已提交
182
      def visit_Arel_Nodes_OuterJoin o
183
        "#{visit o.left} LEFT OUTER JOIN #{visit o.right} #{visit o.constraint}"
A
Aaron Patterson 已提交
184
      end
A
Aaron Patterson 已提交
185 186

      def visit_Arel_Nodes_InnerJoin o
187
        "#{visit o.left} INNER JOIN #{visit o.right} #{visit o.constraint if o.constraint}"
A
Aaron Patterson 已提交
188 189 190 191 192 193
      end

      def visit_Arel_Nodes_On o
        "ON #{visit o.expr}"
      end

194
      def visit_Arel_Nodes_Not o
195
        "NOT (#{visit o.expr})"
196 197
      end

A
Aaron Patterson 已提交
198
      def visit_Arel_Table o
199 200 201 202 203
        if o.table_alias
          "#{quote_table_name o.name} #{quote_table_name o.table_alias}"
        else
          quote_table_name o.name
        end
A
Aaron Patterson 已提交
204 205
      end

206
      def visit_Arel_Nodes_In o
207
        right = o.right
208 209 210
        "#{visit o.left} IN (#{
          right.empty? ? 'NULL' : right.map { |x| visit x }.join(', ')
        })"
211 212
      end

213 214
      def visit_Arel_Nodes_NotIn o
        right = o.right
215 216 217
        "#{visit o.left} NOT IN (#{
          right.empty? ? 'NULL' : right.map { |x| visit x }.join(', ')
        })"
218 219
      end

A
Aaron Patterson 已提交
220 221 222 223
      def visit_Arel_Nodes_And o
        "#{visit o.left} AND #{visit o.right}"
      end

A
Aaron Patterson 已提交
224 225 226 227
      def visit_Arel_Nodes_Or o
        "#{visit o.left} OR #{visit o.right}"
      end

228
      def visit_Arel_Nodes_Assignment o
229
        right = quote(o.right, o.left.column)
A
Aaron Patterson 已提交
230 231 232
        "#{visit o.left} = #{right}"
      end

233 234 235 236 237 238 239 240 241 242
      def visit_Arel_Nodes_Equality o
        right = o.right

        if right.nil?
          "#{visit o.left} IS NULL"
        else
          "#{visit o.left} = #{visit right}"
        end
      end

243 244 245 246 247 248 249 250 251 252
      def visit_Arel_Nodes_NotEqual o
        right = o.right

        if right.nil?
          "#{visit o.left} IS NOT NULL"
        else
          "#{visit o.left} != #{visit right}"
        end
      end

A
Aaron Patterson 已提交
253 254 255 256
      def visit_Arel_Nodes_As o
        "#{visit o.left} AS #{visit o.right}"
      end

A
Aaron Patterson 已提交
257 258
      def visit_Arel_Nodes_UnqualifiedColumn o
        "#{quote_column_name o.name}"
A
Aaron Patterson 已提交
259 260
      end

261
      def visit_Arel_Attributes_Attribute o
262
        @last_column = o.column
263 264
        join_name = o.relation.table_alias || o.relation.name
        "#{quote_table_name join_name}.#{quote_column_name o.name}"
A
Aaron Patterson 已提交
265
      end
266
      alias :visit_Arel_Attributes_Integer :visit_Arel_Attributes_Attribute
E
Ernie Miller 已提交
267
      alias :visit_Arel_Attributes_Float :visit_Arel_Attributes_Attribute
268
      alias :visit_Arel_Attributes_Decimal :visit_Arel_Attributes_Attribute
269 270
      alias :visit_Arel_Attributes_String :visit_Arel_Attributes_Attribute
      alias :visit_Arel_Attributes_Time :visit_Arel_Attributes_Attribute
A
Aaron Patterson 已提交
271
      alias :visit_Arel_Attributes_Boolean :visit_Arel_Attributes_Attribute
A
Aaron Patterson 已提交
272 273

      def visit_Fixnum o; o end
A
Aaron Patterson 已提交
274 275
      alias :visit_Arel_Nodes_SqlLiteral :visit_Fixnum
      alias :visit_Arel_SqlLiteral :visit_Fixnum # This is deprecated
R
rugek 已提交
276
      alias :visit_Bignum :visit_Fixnum
A
Aaron Patterson 已提交
277

278
      def visit_String o; quote(o, @last_column) end
279 280 281 282 283 284 285 286 287 288 289

      alias :visit_ActiveSupport_Multibyte_Chars :visit_String
      alias :visit_BigDecimal :visit_String
      alias :visit_Date :visit_String
      alias :visit_DateTime :visit_String
      alias :visit_FalseClass :visit_String
      alias :visit_Float :visit_String
      alias :visit_Hash :visit_String
      alias :visit_Symbol :visit_String
      alias :visit_Time :visit_String
      alias :visit_TrueClass :visit_String
E
Ernie Miller 已提交
290
      alias :visit_NilClass :visit_String
A
Aaron Patterson 已提交
291
      alias :visit_ActiveSupport_StringInquirer :visit_String
292
      alias :visit_Class :visit_String
A
Aaron Patterson 已提交
293

294 295 296 297
      def quote value, column = nil
        @connection.quote value, column
      end

A
Aaron Patterson 已提交
298
      def quote_table_name name
A
Aaron Patterson 已提交
299
        @quoted_tables[name] ||= @connection.quote_table_name(name)
A
Aaron Patterson 已提交
300 301 302
      end

      def quote_column_name name
A
Aaron Patterson 已提交
303
        @quoted_columns[name] ||= @connection.quote_column_name(name)
A
Aaron Patterson 已提交
304 305 306 307
      end
    end
  end
end