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 



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

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 = ArelExtensions::column_of(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

#visit_Arel_Nodes_As(o, collector) ⇒ Object 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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



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

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 



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

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 



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

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

)



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

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 



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

def visit_ArelExtensions_Nodes_FormattedNumber o, collector
  format = Arel::Nodes::NamedFunction.new('printf',[Arel::Nodes.build_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 



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

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



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

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 



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

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 



160
161
162
163
164
 
# File 'lib/arel_extensions/visitors/sqlite.rb', line 160

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

#visit_ArelExtensions_Nodes_Locate(o, collector) ⇒ Object 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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