Module: JSE::Functors::SQL

Defined in:
lib/jse/functors/sql.rb

Constant Summary collapse

QUERY_FIELDS = 
"subject, predicate, object, meta"
LOCAL_SQL_FUNCTORS = 
{
  "$pattern" => method(:pattern_fn),
  "$and"     => method(:legacy_and),
  "$*"       => method(:wildcard_fn),
}.freeze
SQL_FUNCTORS = 
{
  "$sql"   => method(:sql_fn),
  "$query" => method(:query_fn),
}.freeze

Class Method Summary collapse

Class Method Details

.escaped_str?(s) ⇒ Boolean

Returns:

  • (Boolean) 


26
27
28
 
# File 'lib/jse/functors/sql.rb', line 26

def self.escaped_str?(s)
  s.is_a?(String) && s.start_with?("$$")
end

.legacy_and(env, *args) ⇒ Object 



424
425
426
 
# File 'lib/jse/functors/sql.rb', line 424

def self.legacy_and(env, *args)
  args.map { |a| env.eval(a).to_s }.join(" and ")
end

.parenthesized?(s) ⇒ Boolean

Returns:

  • (Boolean) 


34
35
36
37
38
39
40
41
42
43
 
# File 'lib/jse/functors/sql.rb', line 34

def self.parenthesized?(s)
  return false unless s.start_with?("(")
  depth = 0
  s.each_char.with_index do |c, i|
    depth += 1 if c == "("
    depth -= 1 if c == ")"
    return i == s.length - 1 if depth == 0
  end
  false
end

.pattern_fn(env, *args) ⇒ Object 



417
418
419
420
421
422
 
# File 'lib/jse/functors/sql.rb', line 417

def self.pattern_fn(env, *args)
  raise "$pattern requires (subject, predicate, object)" if args.length < 3
  subj, pred, obj = args[0..2].map { |a| env.eval(a) }
  triple = pattern_to_triple(subj, pred, obj)
  triple_to_sql_condition(triple)
end

.pattern_to_triple(subject, predicate, object) ⇒ Object

Legacy $query / $pattern (backward compatibility)



402
403
404
405
406
407
408
 
# File 'lib/jse/functors/sql.rb', line 402

def self.pattern_to_triple(subject, predicate, object)
  if subject == "$*" && object == "$*"
    [predicate]
  else
    [subject, predicate, object]
  end
end

.query_fn(env, *args) ⇒ Object 



438
439
440
441
442
443
444
445
446
447
 
# File 'lib/jse/functors/sql.rb', line 438

def self.query_fn(env, *args)
  raise "$query expects a condition expression" if args.empty?
  local = Env.new(parent: env)
  local.load(LOCAL_SQL_FUNCTORS)
  parser = Parser.new(local)
  condition = parser.parse(args)
  where = condition.apply(local)
  where = where.is_a?(Array) ? where.first.to_s : where.to_s
  "select #{QUERY_FIELDS} \nfrom statement \nwhere \n    #{where} \noffset 0\nlimit 100 \n"
end

.render_agg(fn, args) ⇒ Object 



158
159
160
161
 
# File 'lib/jse/functors/sql.rb', line 158

def self.render_agg(fn, args)
  return "#{fn}(*)" if args.empty?
  "#{fn}(#{render_expr(args[0])})"
end

.render_as(args) ⇒ Object 



139
140
141
142
143
144
145
146
147
148
149
150
 
# File 'lib/jse/functors/sql.rb', line 139

def self.render_as(args)
  return "" if args.length < 2
  expr = render_expr(args[0])
  als = render_expr(args[1])
  if args[0].is_a?(Array)
    "(#{expr}) as #{als}"
  elsif args[0].is_a?(String) && symbol_str?(args[0])
    "#{args[0][1..]} as #{als}"
  else
    "#{expr} as #{als}"
  end
end

.render_binary(op, args) ⇒ Object 



163
164
165
166
 
# File 'lib/jse/functors/sql.rb', line 163

def self.render_binary(op, args)
  return "" if args.length < 2
  "#{render_expr(args[0])} #{op} #{render_expr(args[1])}"
end

.render_case(args) ⇒ Object 



206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
 
# File 'lib/jse/functors/sql.rb', line 206

def self.render_case(args)
  parts = ["case"]
  else_val = nil
  args.each do |arg|
    if arg.is_a?(Array) && !arg.empty? && arg[0].is_a?(String)
      if arg[0] == "$when" && arg.length >= 3
        parts << "when #{render_expr(arg[1])} then #{render_expr(arg[2])}"
      elsif arg[0] == "$else" && arg.length >= 2
        else_val = render_expr(arg[1])
      end
    end
  end
  parts << "else #{else_val}" if else_val
  parts << "end"
  parts.join(" ")
end

.render_clause(clause) ⇒ Object

Clause renderer



243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
 
# File 'lib/jse/functors/sql.rb', line 243

def self.render_clause(clause)
  return "" if clause.empty?
  kw = clause[0]
  return "" unless kw.is_a?(String) && kw.start_with?("$")
  args = clause[1..]

  case kw
  when "$select"
    "select #{args.map { |a| render_expr(a) }.join(", ")}"
  when "$from"
    "from #{render_table(args)}"
  when "$join"
    "join #{render_join(args)}"
  when "$left-join"
    "left join #{render_join(args)}"
  when "$right-join"
    "right join #{render_join(args)}"
  when "$full-join"
    "full join #{render_join(args)}"
  when "$cross-join"
    "cross join #{render_table(args)}"
  when "$where"
    args.empty? ? "" : "where #{render_expr(args[0])}"
  when "$group-by"
    args.empty? ? "" : "group by #{render_expr(args[0])}"
  when "$having"
    args.empty? ? "" : "having #{render_expr(args[0])}"
  when "$order-by"
    render_order_by(args)
  when "$limit"
    args.empty? ? "" : "limit #{render_expr(args[0])}"
  when "$offset"
    args.empty? ? "" : "offset #{render_expr(args[0])}"
  when "$with"
    render_with(args)
  when "$insert-into"
    render_insert_into(args)
  when "$values"
    "values (#{args.map { |a| render_expr(a) }.join(", ")})"
  when "$update"
    "update #{render_table(args)}"
  when "$set"
    render_set(args)
  when "$delete-from"
    "delete from #{render_table(args)}"
  when "$on-conflict"
    args.empty? ? "" : "on conflict (#{render_expr(args[0])})"
  when "$do-update"
    render_do_update(args)
  else
    kw[1..]
  end
end

.render_count(args) ⇒ Object 



152
153
154
155
156
 
# File 'lib/jse/functors/sql.rb', line 152

def self.render_count(args)
  return "count(*)" if args.empty?
  return "count(*)" if args[0].is_a?(String) && args[0] == "$*"
  "count(#{render_expr(args[0])})"
end

.render_dict(dict) ⇒ Object 



79
80
81
82
83
84
85
86
87
88
 
# File 'lib/jse/functors/sql.rb', line 79

def self.render_dict(dict)
  return "" if dict.empty?
  op_keys = dict.keys.select { |k| symbol_str?(k) }
  if op_keys.empty? || op_keys.length > 1
    return dict.map { |k, v| "#{render_key(k)} = #{render_expr(v)}" }.join(", ")
  end
  op = op_keys.first
  return %("#{dict[op]}") if op == "$symbol"
  render_expr(dict[op])
end

.render_do_update(args) ⇒ Object 



355
356
357
358
359
 
# File 'lib/jse/functors/sql.rb', line 355

def self.render_do_update(args)
  return "" if args.empty? || !args[0].is_a?(Hash)
  parts = args[0].map { |col, val| "#{render_key(col)} = #{render_expr(val)}" }
  "do update set #{parts.join(", ")}"
end

.render_excluded(args) ⇒ Object 



223
224
225
226
 
# File 'lib/jse/functors/sql.rb', line 223

def self.render_excluded(args)
  return "excluded" if args.empty?
  "excluded.#{render_expr(args[0])}"
end

.render_expr(value) ⇒ Object

Expression renderer



49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/jse/functors/sql.rb', line 49

def self.render_expr(value)
  case value
  when nil then "null"
  when true then "true"
  when false then "false"
  when Integer, Float then value.to_s
  when String
    if escaped_str?(value)
      sql_quote("$#{value[2..]}")
    elsif symbol_str?(value)
      value[1..]
    else
      sql_quote(value)
    end
  when Array
    return "" if value.empty?
    if subquery?(value)
      render_subquery(value)
    elsif value[0].is_a?(String) && value[0].start_with?("$") && !value[0].start_with?("$$")
      render_list_expr(value)
    else
      value.map { |v| render_expr(v) }.join(", ")
    end
  when Hash
    render_dict(value)
  else
    value.to_s
  end
end

.render_func(op, args) ⇒ Object

Function dispatch



112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
 
# File 'lib/jse/functors/sql.rb', line 112

def self.render_func(op, args)
  case op
  when "$as"   then render_as(args)
  when "$count" then render_count(args)
  when "$sum"   then render_agg("sum", args)
  when "$avg"   then render_agg("avg", args)
  when "$max"   then render_agg("max", args)
  when "$min"   then render_agg("min", args)
  when "$eq"    then render_binary("=", args)
  when "$ne"    then render_binary("!=", args)
  when "$gt"    then render_binary(">", args)
  when "$gte"   then render_binary(">=", args)
  when "$lt"    then render_binary("<", args)
  when "$lte"   then render_binary("<=", args)
  when "$like"  then render_binary("like", args)
  when "$is"    then render_is(args)
  when "$is-not" then render_is_not(args)
  when "$and"   then render_logical("and", args)
  when "$or"    then render_logical("or", args)
  when "$in"    then render_in(args)
  when "$case"  then render_case(args)
  when "$excluded" then render_excluded(args)
  else
    "#{op[1..]}(#{args.map { |a| render_expr(a) }.join(", ")})"
  end
end

.render_in(args) ⇒ Object 



191
192
193
194
195
196
197
198
199
200
201
202
203
204
 
# File 'lib/jse/functors/sql.rb', line 191

def self.render_in(args)
  return "" if args.length < 2
  col = render_expr(args[0])
  val = args[1]
  if val.is_a?(Array)
    if subquery?(val)
      "#{col} in (#{render_subquery(val)})"
    else
      "#{col} in (#{val.map { |v| render_expr(v) }.join(", ")})"
    end
  else
    "#{col} in (#{render_expr(val)})"
  end
end

.render_insert_into(args) ⇒ Object 



338
339
340
341
342
343
344
345
346
347
 
# File 'lib/jse/functors/sql.rb', line 338

def self.render_insert_into(args)
  return "insert into" if args.empty?
  table = render_expr(args[0])
  if args.length > 1
    cols = args[1..].map { |a| render_expr(a) }.join(", ")
    "insert into #{table} (#{cols})"
  else
    "insert into #{table}"
  end
end

.render_is(args) ⇒ Object 



168
169
170
171
172
173
 
# File 'lib/jse/functors/sql.rb', line 168

def self.render_is(args)
  return "" if args.length < 2
  left = render_expr(args[0])
  return "#{left} is null" if args[1].nil?
  "#{left} is #{render_expr(args[1])}"
end

.render_is_not(args) ⇒ Object 



175
176
177
178
179
180
 
# File 'lib/jse/functors/sql.rb', line 175

def self.render_is_not(args)
  return "" if args.length < 2
  left = render_expr(args[0])
  return "#{left} is not null" if args[1].nil?
  "#{left} is not #{render_expr(args[1])}"
end

.render_join(args) ⇒ Object 



307
308
309
310
311
312
 
# File 'lib/jse/functors/sql.rb', line 307

def self.render_join(args)
  return "" if args.empty?
  table = render_expr(args[0])
  return "#{table} on #{render_expr(args[1])}" if args.length >= 2
  table
end

.render_key(key) ⇒ Object 



90
91
92
93
94
95
96
97
98
 
# File 'lib/jse/functors/sql.rb', line 90

def self.render_key(key)
  if escaped_str?(key)
    "$#{key[2..]}"
  elsif symbol_str?(key)
    key[1..]
  else
    key.to_s
  end
end

.render_list_expr(lst) ⇒ Object 



100
101
102
103
104
105
106
 
# File 'lib/jse/functors/sql.rb', line 100

def self.render_list_expr(lst)
  return "" if lst.empty?
  op = lst[0]
  return lst.map { |v| render_expr(v) }.join(", ") unless op.is_a?(String)
  return lst.map { |v| render_expr(v) }.join(", ") if op.start_with?("$$")
  render_func(op, lst[1..])
end

.render_logical(op, args) ⇒ Object 



182
183
184
185
186
187
188
189
 
# File 'lib/jse/functors/sql.rb', line 182

def self.render_logical(op, args)
  return op == "and" ? "true" : "false" if args.empty?
  parts = args.map do |a|
    r = render_expr(a)
    parenthesized?(r) ? r : "(#{r})"
  end
  "(#{parts.join(" #{op} ")})"
end

.render_order_by(args) ⇒ Object 



314
315
316
317
318
319
320
321
322
323
 
# File 'lib/jse/functors/sql.rb', line 314

def self.render_order_by(args)
  return "" if args.empty?
  col = render_expr(args[0])
  if args.length >= 2 && args[1].is_a?(String) && symbol_str?(args[1])
    dir = args[1][1..]
    return "order by #{col} #{dir}" if %w[desc asc].include?(dir)
  end
  return "order by #{col} #{render_expr(args[1])}" if args.length >= 2
  "order by #{col}"
end

.render_set(args) ⇒ Object 



349
350
351
352
353
 
# File 'lib/jse/functors/sql.rb', line 349

def self.render_set(args)
  return "" if args.empty? || !args[0].is_a?(Hash)
  parts = args[0].map { |col, val| "#{render_key(col)} = #{render_expr(val)}" }
  "set #{parts.join(", ")}"
end

.render_subquery(clauses) ⇒ Object

Subquery rendering



232
233
234
235
236
237
 
# File 'lib/jse/functors/sql.rb', line 232

def self.render_subquery(clauses)
  clauses.select { |c| c.is_a?(Array) }
         .map { |c| render_clause(c) }
         .reject(&:empty?)
         .join(" ")
end

.render_table(args) ⇒ Object 



297
298
299
300
301
302
303
304
305
 
# File 'lib/jse/functors/sql.rb', line 297

def self.render_table(args)
  return "" if args.empty?
  first = args[0]
  if first.is_a?(Array) && !first.empty? && first[0] == "$as"
    render_list_expr(first)
  else
    render_expr(first)
  end
end

.render_with(args) ⇒ Object 



325
326
327
328
329
330
331
332
333
334
335
336
 
# File 'lib/jse/functors/sql.rb', line 325

def self.render_with(args)
  return "" if args.empty? || !args[0].is_a?(Hash)
  cte_defs = args[0]
  cte_parts = cte_defs.map do |cte_name_key, cte_clauses|
    cte_name = symbol_str?(cte_name_key) ? cte_name_key[1..] : cte_name_key
    next unless cte_clauses.is_a?(Array) && !cte_clauses.empty?
    inner = cte_clauses.map { |c| c.is_a?(Array) ? render_clause(c) : "" }.reject(&:empty?).join(" ")
    "#{cte_name} as (#{inner})"
  end.compact
  return "" if cte_parts.empty?
  "with #{cte_parts.join(",\n")}"
end

.sql_fn(env, *args) ⇒ Object

$sql functor



365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
 
# File 'lib/jse/functors/sql.rb', line 365

def self.sql_fn(env, *args)
  return "" if args.empty?
  data = args[0]
  return "" unless data.is_a?(Array) && !data.empty?

  parts = []
  pending_values = []

  flush_values = -> {
    unless pending_values.empty?
      rows = pending_values.map { |vlist|
        "(#{vlist.map { |v| render_expr(v) }.join(", ")})"
      }
      parts << "values #{rows.join(", ")}"
      pending_values.clear
    end
  }

  data.each do |item|
    next unless item.is_a?(Array) && !item.empty?
    if item[0] == "$values"
      pending_values << item[1..]
      next
    end
    flush_values.call
    rendered = render_clause(item)
    parts << rendered unless rendered.empty?
  end
  flush_values.call

  parts.join("\n")
end

.sql_quote(s) ⇒ Object 



30
31
32
 
# File 'lib/jse/functors/sql.rb', line 30

def self.sql_quote(s)
  "'#{s.gsub("'", "''")}'"
end

.subquery?(value) ⇒ Boolean

Subquery detection

Returns:

  • (Boolean) 


12
13
14
15
 
# File 'lib/jse/functors/sql.rb', line 12

def self.subquery?(value)
  return false unless value.is_a?(Array) && !value.empty?
  value.all? { |item| item.is_a?(Array) && !item.empty? && item[0].is_a?(String) && item[0].start_with?("$") }
end

.symbol_str?(s) ⇒ Boolean

String utilities

Returns:

  • (Boolean) 


21
22
23
24
 
# File 'lib/jse/functors/sql.rb', line 21

def self.symbol_str?(s)
  return false if s == "$*"
  s.is_a?(String) && s.start_with?("$") && !s.start_with?("$$")
end

.triple_to_sql_condition(triple) ⇒ Object 



410
411
412
413
414
415
 
# File 'lib/jse/functors/sql.rb', line 410

def self.triple_to_sql_condition(triple)
  inner = triple.map { |s| %("#{s}") }.join(",")
  json = %({"triple":[#{inner}]})
  escaped = json.gsub("'", "''")
  "meta @> '#{escaped}'"
end

.wildcard_fn(_env, *_args) ⇒ Object 



428
429
430
 
# File 'lib/jse/functors/sql.rb', line 428

def self.wildcard_fn(_env, *_args)
  "*"
end