Class: Arel::Visitors::SQLite

Inherits:
Object
  • Object
show all
Defined in:
lib/arel_extensions/visitors/sqlite.rb

Instance Method Summary collapse

Instance Method Details

#get_time_converted(element) ⇒ Object 



320
321
322
323
324
325
326
327
328
329
330
331
332
333
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 320

def get_time_converted element
  if element.is_a?(Time)
    return Arel::Nodes::NamedFunction.new('STRFTIME',[element, '%H:%M:%S'])
  elsif element.is_a?(Arel::Attributes::Attribute)
    col = Arel::Table.engine.connection.schema_cache.columns_hash(element.relation.table_name)[element.name.to_s]
    if col && (col.type == :time)
      return Arel::Nodes::NamedFunction.new('STRFTIME',[element, '%H:%M:%S'])
    else
      return element
    end
  else
    return element
  end
end

#old_visit_Arel_Nodes_As ⇒ Object 



367
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 367

alias_method :old_visit_Arel_Nodes_As, :visit_Arel_Nodes_As

#visit_Arel_Nodes_As(o, collector) ⇒ Object 



368
369
370
371
372
373
374
375
376
377
378
379
380
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 368

def visit_Arel_Nodes_As o, collector
  if o.left.is_a?(Arel::Nodes::Binary)
    collector << '('
    collector = visit o.left, collector
    collector << ')'
  else
    collector = visit o.left, collector
  end
  collector << " AS \""
  collector = visit o.right, collector
  collector << "\""
  collector
end

#visit_Arel_Nodes_GreaterThan(o, collector) ⇒ Object 



344
345
346
347
348
349
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 344

def visit_Arel_Nodes_GreaterThan o, collector
  collector = visit get_time_converted(o.left), collector
  collector << " > "
  collector = visit get_time_converted(o.right), collector
  collector
end

#visit_Arel_Nodes_GreaterThanOrEqual(o, collector) ⇒ Object 



336
337
338
339
340
341
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 336

def visit_Arel_Nodes_GreaterThanOrEqual o, collector
  collector = visit get_time_converted(o.left), collector
  collector << " >= "
  collector = visit get_time_converted(o.right), collector
  collector
end

#visit_Arel_Nodes_LessThan(o, collector) ⇒ Object 



360
361
362
363
364
365
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 360

def visit_Arel_Nodes_LessThan o, collector
  collector = visit get_time_converted(o.left), collector
  collector << " < "
  collector = visit get_time_converted(o.right), collector
  collector
end

#visit_Arel_Nodes_LessThanOrEqual(o, collector) ⇒ Object 



352
353
354
355
356
357
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 352

def visit_Arel_Nodes_LessThanOrEqual o, collector
  collector = visit get_time_converted(o.left), collector
  collector << " <= "
  collector = visit get_time_converted(o.right), collector
  collector
end

#visit_Arel_Nodes_NotRegexp(o, collector) ⇒ Object 



182
183
184
185
186
187
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 182

def visit_Arel_Nodes_NotRegexp o, collector
  collector = visit o.left, collector
  collector << " NOT REGEXP "
  collector = visit o.right, collector
  collector
end

#visit_Arel_Nodes_Regexp(o, collector) ⇒ Object 



175
176
177
178
179
180
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 175

def visit_Arel_Nodes_Regexp o, collector
  collector = visit o.left, collector
  collector << " REGEXP"
  collector = visit o.right, collector
  collector
end

#visit_ArelExtensions_InsertManager_BulkValues(o, collector) ⇒ Object 



235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 235

def visit_ArelExtensions_InsertManager_BulkValues o, collector
  o.left.each_with_index do |row, idx|
    collector << 'SELECT '
    len = row.length - 1
    row.zip(o.cols).each_with_index { |(value, attr), i|
        case value
        when Arel::Nodes::SqlLiteral, Arel::Nodes::BindParam
          collector = visit value.as(attr.name), collector
        else
          collector << quote(value, attr && column_for(attr)).to_s
          if idx == 0
            collector << " AS "
            collector << quote(attr.name)
          end
        end
        collector << Arel::Visitors::SQLite::COMMA unless i == len
    }
    collector << ' UNION ALL ' unless idx == o.left.length - 1
  end
  collector
end

#visit_ArelExtensions_Nodes_AiIMatches(o, collector) ⇒ Object 



38
39
40
41
42
43
44
45
46
47
48
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 38

def visit_ArelExtensions_Nodes_AiIMatches o, collector
    collector = visit o.left.collate(true,true), collector
    collector << ' LIKE '
    collector = visit o.right.collate(true,true), collector
    if o.escape
      collector << ' ESCAPE '
      visit o.escape, collector
    else
      collector
    end
end

#visit_ArelExtensions_Nodes_AiMatches(o, collector) ⇒ Object 



26
27
28
29
30
31
32
33
34
35
36
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 26

def visit_ArelExtensions_Nodes_AiMatches o, collector
    collector = visit o.left.ai_collate, collector
    collector << ' LIKE '
    collector = visit o.right.ai_collate, collector
    if o.escape
      collector << ' ESCAPE '
      visit o.escape, collector
    else
      collector
    end
end

#visit_ArelExtensions_Nodes_Ceil(o, collector) ⇒ Object 



221
222
223
224
225
226
227
228
229
230
231
232
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 221

def visit_ArelExtensions_Nodes_Ceil o, collector
  collector << "CASE WHEN ROUND("
  collector = visit o.left, collector
  collector << ", 1) > ROUND("
  collector = visit o.left, collector
  collector << ") THEN ROUND("
  collector = visit o.left, collector
  collector << ") + 1 ELSE ROUND("
  collector = visit o.left, collector
  collector << ") END"
  collector
end

#visit_ArelExtensions_Nodes_Collate(o, collector) ⇒ Object 



62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 62

def visit_ArelExtensions_Nodes_Collate o, collector
  if o.ai
    collector = visit o.expressions.first, collector
    collector << ' COLLATE NOACCENTS'
  elsif o.ci
    collector = visit o.expressions.first, collector
    collector << ' COLLATE NOCASE'
  else
    collector = visit o.expressions.first, collector
    collector << ' COLLATE BINARY'
  end
  collector
end

#visit_ArelExtensions_Nodes_Concat(o, collector) ⇒ Object 



132
133
134
135
136
137
138
139
140
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 132

def visit_ArelExtensions_Nodes_Concat o, collector
  collector << '('
  o.expressions.each_with_index { |arg, i|
    collector = visit arg, collector
    collector << ' || ' unless i == o.expressions.length - 1
  }
  collector << ")"
  collector
end

#visit_ArelExtensions_Nodes_DateAdd(o, collector) ⇒ Object

Date operations



90
91
92
93
94
95
96
97
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 90

def visit_ArelExtensions_Nodes_DateAdd o, collector
  collector << "date("
  collector = visit o.expressions.first, collector
  collector << Arel::Visitors::SQLite::COMMA
  collector = visit o.sqlite_value, collector
  collector << ")"
  collector
end

#visit_ArelExtensions_Nodes_DateDiff(o, collector) ⇒ Object 



99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 99

def visit_ArelExtensions_Nodes_DateDiff o, collector
  if o.left_node_type == :ruby_time || o.left_node_type == :datetime || o.left_node_type == :time
    collector << "strftime('%s', "
    collector = visit o.left, collector
    collector << ") - strftime('%s', "
    collector = visit o.right, collector
  else
    collector << "julianday("
    collector = visit o.left, collector
    collector << ") - julianday("
    collector = visit o.right, collector
  end
  collector << ")"
  collector
end

#visit_ArelExtensions_Nodes_Duration(o, collector) ⇒ Object 



115
116
117
118
119
120
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 115

def visit_ArelExtensions_Nodes_Duration o, collector
  collector << "strftime('#{Arel::Visitors::SQLite::DATE_MAPPING[o.left]}'#{Arel::Visitors::SQLite::COMMA}"
  collector = visit o.right, collector
  collector << ")"
  collector
end

#visit_ArelExtensions_Nodes_Floor(o, collector) ⇒ Object

CAST(

CASE
  WHEN 3.42 >= 0 THEN CAST(3.42 AS INT)
  WHEN CAST(3.42 AS INT) = 3.42 THEN CAST(3.42 AS INT)
  ELSE CAST((3.42 - 1.0) AS INT)
END
AS FLOAT

)



204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 204

def visit_ArelExtensions_Nodes_Floor o, collector
  collector << "CAST(CASE WHEN "
  collector = visit o.left, collector
  collector << " >= 0 THEN CAST("
  collector = visit o.left, collector
  collector << " AS INT) WHEN CAST("
  collector = visit o.left, collector
  collector << " AS INT) = "
  collector = visit o.left, collector
  collector << " THEN CAST("
  collector = visit o.left, collector
  collector << " AS INT) ELSE CAST(("
  collector = visit o.left, collector
  collector << " - 1.0) AS INT) END AS FLOAT)"
  collector
end

#visit_ArelExtensions_Nodes_FormattedNumber(o, collector) ⇒ Object 



382
383
384
385
386
387
388
389
390
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 382

def visit_ArelExtensions_Nodes_FormattedNumber o, collector
  format = Arel::Nodes::NamedFunction.new('printf',[Arel::Nodes.build_quoted(o.original_string),o.left])
  locale_map = Arel::Visitors::SQLite::NUMBER_COMMA_MAPPING[o.locale]
  if locale_map
    format = format.replace(',',locale_map[',']).replace('.',locale_map['.'])
  end
  visit format, collector
  collector
end

#visit_ArelExtensions_Nodes_IDoesNotMatch(o, collector) ⇒ Object 



77
78
79
80
81
82
83
84
85
86
87
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 77

def visit_ArelExtensions_Nodes_IDoesNotMatch o, collector
  collector = visit o.left.lower, collector
  collector << ' NOT LIKE '
  collector = visit o.right.lower(o.right), collector
  if o.escape
    collector << ' ESCAPE '
    visit o.escape, collector
  else
    collector
  end
end

#visit_ArelExtensions_Nodes_IMatches(o, collector) ⇒ Object

String functions



14
15
16
17
18
19
20
21
22
23
24
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 14

def visit_ArelExtensions_Nodes_IMatches o, collector # insensitive on ASCII
  collector = visit o.left.ci_collate, collector
  collector << ' LIKE '
  collector = visit o.right.ci_collate, collector
  if o.escape
    collector << ' ESCAPE '
    visit o.escape, collector
  else
    collector
  end
end

#visit_ArelExtensions_Nodes_IsNotNull(o, collector) ⇒ Object 



158
159
160
161
162
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 158

def visit_ArelExtensions_Nodes_IsNotNull o, collector
    collector = visit o.expr, collector
    collector << ' IS NOT NULL'
    collector
end

#visit_ArelExtensions_Nodes_IsNull(o, collector) ⇒ Object 



152
153
154
155
156
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 152

def visit_ArelExtensions_Nodes_IsNull o, collector
  collector = visit o.expr, collector
  collector << ' IS NULL'
  collector
end

#visit_ArelExtensions_Nodes_Locate(o, collector) ⇒ Object 



123
124
125
126
127
128
129
130
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 123

def visit_ArelExtensions_Nodes_Locate o, collector
  collector << "instr("
  collector = visit o.expr, collector
  collector << Arel::Visitors::SQLite::COMMA
  collector = visit o.right, collector
  collector << ")"
  collector
end

#visit_ArelExtensions_Nodes_Rand(o, collector) ⇒ Object 



164
165
166
167
168
169
170
171
172
173
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 164

def visit_ArelExtensions_Nodes_Rand o, collector
  collector << "RANDOM("
  if o.left != nil && o.right != nil
    collector = visit o.left, collector
    collector << Arel::Visitors::SQLite::COMMA
    collector = visit o.right, collector
  end
  collector << ")"
  collector
end

#visit_ArelExtensions_Nodes_SMatches(o, collector) ⇒ Object 



50
51
52
53
54
55
56
57
58
59
60
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 50

def visit_ArelExtensions_Nodes_SMatches o, collector
  collector = visit o.left.collate, collector
  collector << ' LIKE '
  collector = visit o.right.collate, collector
  if o.escape
    collector << ' ESCAPE '
    visit o.escape, collector
  else
    collector
  end
end

#visit_ArelExtensions_Nodes_Substring(o, collector) ⇒ Object 



142
143
144
145
146
147
148
149
150
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 142

def visit_ArelExtensions_Nodes_Substring o, collector
  collector << "SUBSTR("
  o.expressions.each_with_index { |arg, i|
    collector << Arel::Visitors::SQLite::COMMA unless i == 0
    collector = visit arg, collector
  }
  collector << ")"
  collector
end

#visit_ArelExtensions_Nodes_Union(o, collector) ⇒ Object 



286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 286

def visit_ArelExtensions_Nodes_Union o, collector
  collector =
    if o.left.is_a?(Arel::SelectManager)
      visit o.left.ast, collector
    else
      visit o.left, collector
    end
  collector << " UNION "
  collector =
    if o.right.is_a?(Arel::SelectManager)
      visit o.right.ast, collector
    else
      visit o.right, collector
    end
  collector
end

#visit_ArelExtensions_Nodes_UnionAll(o, collector) ⇒ Object 



303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 303

def visit_ArelExtensions_Nodes_UnionAll o, collector
  collector =
    if o.left.is_a?(Arel::SelectManager)
      visit o.left.ast, collector
    else
      visit o.left, collector
    end
  collector << " UNION ALL "
  collector =
    if o.right.is_a?(Arel::SelectManager)
      visit o.right.ast, collector
    else
      visit o.right, collector
    end
  collector
end

#visit_ArelExtensions_Nodes_Wday(o, collector) ⇒ Object 



189
190
191
192
193
194
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 189

def visit_ArelExtensions_Nodes_Wday o, collector
  collector << "STRFTIME('%w',"
  collector = visit o.date, collector
  collector << ")"
  collector
end