Class: Arel::Visitors::SQLite

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

Constant Summary collapse

DATE_MAPPING = 
{
  'd' => '%d', 'm' => '%m', 'w' => '%W', 'y' => '%Y', 'wd' => '%w', 'M' => '%M',
  'h' => '%H', 'mn' => '%M', 's' => '%S'
}.freeze
DATE_FORMAT_DIRECTIVES =

ISO C / POSIX

{ # ISO C / POSIX
  '%Y' => '%Y', '%C' =>   '', '%y' => '%y', '%m' => '%m', '%B' => '%M', '%b' => '%b', '%^b' => '%b', # year, month
  '%d' => '%d', '%e' => '%e', '%j' => '%j', '%w' => '%w', '%A' => '%W', # day, weekday
  '%H' => '%H', '%k' => '%k', '%I' => '%I', '%l' => '%l', '%P' => '%p', '%p' => '%p', # hours
  '%M' => '%M', '%S' => '%S', '%L' =>   '', '%N' => '%f', '%z' => '' # seconds, subseconds
}.freeze
NUMBER_COMMA_MAPPING = 
{
  'fr_FR' => {',' => ' ', '.' => ','}
}.freeze

Instance Method Summary collapse

Instance Method Details

#get_time_converted(element) ⇒ Object 



342
343
344
345
346
347
348
349
350
351
352
353
354
355
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 342

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

#visit_Arel_Nodes_As(o, collector) ⇒ Object 



390
391
392
393
394
395
396
397
398
399
400
401
402
403
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 390

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
  sep = o.right.size > 1 && o.right[0] == '"' && o.right[-1] == '"' ? '' : '"'
  collector << " AS #{sep}"
  collector = visit o.right, collector
  collector << "#{sep}"
  collector
end

#visit_Arel_Nodes_GreaterThan(o, collector) ⇒ Object 



366
367
368
369
370
371
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 366

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 



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

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 



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

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 



374
375
376
377
378
379
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 374

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 



204
205
206
207
208
209
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 204

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 



197
198
199
200
201
202
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 197

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 



257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 257

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 << COMMA unless i == len
    }
    collector << ' UNION ALL ' unless idx == o.left.length - 1
  end
  collector
end

#visit_ArelExtensions_Nodes_AiIMatches(o, collector) ⇒ Object 



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

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 



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

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_ByteSize(o, collector) ⇒ Object

String functions



21
22
23
24
25
26
27
28
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 21

def visit_ArelExtensions_Nodes_ByteSize o, collector
  # sqlite 3.43.0 (2023-08-24) introduced `octet_length`, but we still support older versions.
  # https://sqlite.org/changes.html
  collector << 'length(CAST('
  collector = visit o.expr.coalesce(''), collector
  collector << ' AS BLOB))'
  collector
end

#visit_ArelExtensions_Nodes_Ceil(o, collector) ⇒ Object 



243
244
245
246
247
248
249
250
251
252
253
254
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 243

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_CharLength(o, collector) ⇒ Object 



30
31
32
33
34
35
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 30

def visit_ArelExtensions_Nodes_CharLength o, collector
  collector << 'length('
  collector = visit o.expr.coalesce(''), collector
  collector << ')'
  collector
end

#visit_ArelExtensions_Nodes_Collate(o, collector) ⇒ Object 



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

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 



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

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



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

def visit_ArelExtensions_Nodes_DateAdd o, collector
  collector << 'date('
  collector = visit o.expressions.first, collector
  collector << COMMA
  collector = visit o.sqlite_value, collector
  collector << ')'
  collector
end

#visit_ArelExtensions_Nodes_DateDiff(o, collector) ⇒ Object 



121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 121

def visit_ArelExtensions_Nodes_DateDiff o, collector
  case o.left_node_type
  when :ruby_time, :datetime, :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 



138
139
140
141
142
143
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 138

def visit_ArelExtensions_Nodes_Duration o, collector
  collector << "strftime('#{DATE_MAPPING[o.left]}'#{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

)



226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 226

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 



405
406
407
408
409
410
411
412
413
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 405

def visit_ArelExtensions_Nodes_FormattedNumber o, collector
  format = Arel::Nodes::NamedFunction.new('printf', [Arel.quoted(o.original_string), o.left])
  locale_map = 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 



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

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

insensitive on ASCII



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

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 



180
181
182
183
184
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 180

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 



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

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

#visit_ArelExtensions_Nodes_Locate(o, collector) ⇒ Object 



145
146
147
148
149
150
151
152
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 145

def visit_ArelExtensions_Nodes_Locate o, collector
  collector << 'instr('
  collector = visit o.expr, collector
  collector << COMMA
  collector = visit o.right, collector
  collector << ')'
  collector
end

#visit_ArelExtensions_Nodes_Rand(o, collector) ⇒ Object 



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

def visit_ArelExtensions_Nodes_Rand o, collector
  collector << 'RANDOM('
  if o.left != nil && o.right != nil
    collector = visit o.left, collector
    collector << COMMA
    collector = visit o.right, collector
  end
  collector << ')'
  collector
end

#visit_ArelExtensions_Nodes_SMatches(o, collector) ⇒ Object 



73
74
75
76
77
78
79
80
81
82
83
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 73

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 



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

def visit_ArelExtensions_Nodes_Substring o, collector
  collector << 'SUBSTR('
  o.expressions.each_with_index { |arg, i|
    collector << COMMA if i != 0
    collector = visit arg, collector
  }
  collector << ')'
  collector
end

#visit_ArelExtensions_Nodes_Union(o, collector) ⇒ Object 



308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 308

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 



325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 325

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 



211
212
213
214
215
216
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 211

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