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 



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

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 



374
375
376
377
378
379
380
381
382
383
384
385
386
387
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 374

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 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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 



241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 241

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 



45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 45

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 



33
34
35
36
37
38
39
40
41
42
43
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 33

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 



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

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 



69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 69

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 



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

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



96
97
98
99
100
101
102
103
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 96

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 



105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 105

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 



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

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

)



210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 210

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 



389
390
391
392
393
394
395
396
397
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 389

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 



83
84
85
86
87
88
89
90
91
92
93
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 83

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



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

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 



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

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 



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

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

#visit_ArelExtensions_Nodes_Locate(o, collector) ⇒ Object 



129
130
131
132
133
134
135
136
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 129

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 



170
171
172
173
174
175
176
177
178
179
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 170

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 



57
58
59
60
61
62
63
64
65
66
67
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 57

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 



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

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 



292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 292

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 



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

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 



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

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