Module: SqlChatbot::Grammar::Modifiers

Defined in:
lib/sql_chatbot/grammar/modifiers.rb

Constant Summary collapse

WINDOWS = 
{
  "today"       => "DATE_TRUNC('day', NOW())",
  "yesterday"   => "DATE_TRUNC('day', NOW() - INTERVAL '1 day')",
  "last_7_days" => "NOW() - INTERVAL '7 days'",
  "last_30_days" => "NOW() - INTERVAL '30 days'",
  "this_week"   => "DATE_TRUNC('week', NOW())",
  "this_month"  => "DATE_TRUNC('month', NOW())",
  "this_year"   => "DATE_TRUNC('year', NOW())",
}.freeze
OPS = 
{
  "eq"  => "=",
  "neq" => "!=",
  "lt"  => "<",
  "lte" => "<=",
  "gt"  => ">",
  "gte" => ">=",
}.freeze

Class Method Summary collapse

Class Method Details

.append_clause(sql, clause) ⇒ Object 



52
53
54
55
56
57
58
 
# File 'lib/sql_chatbot/grammar/modifiers.rb', line 52

def self.append_clause(sql, clause)
  if /\bWHERE\b/i.match?(sql)
    "#{sql} AND #{clause}"
  else
    "#{sql} WHERE #{clause}"
  end
end

.apply(sql, modifier, entity) ⇒ Object 



36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
# File 'lib/sql_chatbot/grammar/modifiers.rb', line 36

def self.apply(sql, modifier, entity)
  kind = modifier[:kind].to_s
  case kind
  when "where"    then apply_where(sql, modifier, entity)
  when "time"     then apply_time(sql, modifier, entity)
  when "join"     then apply_join(sql, modifier, entity)
  when "group_by" then apply_group_by(sql, modifier, entity)
  when "having"   then apply_having(sql, modifier, entity)
  when "order_by" then apply_order_by(sql, modifier, entity)
  when "limit"    then apply_limit(sql, modifier)
  when "distinct" then sql.sub(/^SELECT /, "SELECT DISTINCT ")
  else
    raise "unknown modifier kind #{kind}"
  end
end

.apply_group_by(sql, modifier, entity) ⇒ Object 



114
115
116
117
118
 
# File 'lib/sql_chatbot/grammar/modifiers.rb', line 114

def self.apply_group_by(sql, modifier, entity)
  field_name = modifier[:field].to_s
  raise "group_by field '#{field_name}' not on entity #{entity.name}" unless entity.fields[field_name]
  "#{sql} GROUP BY #{qc(entity.table, field_name)}"
end

.apply_having(sql, modifier, entity) ⇒ Object 



120
121
122
123
124
 
# File 'lib/sql_chatbot/grammar/modifiers.rb', line 120

def self.apply_having(sql, modifier, entity)
  raise "HAVING requires GROUP BY" unless /GROUP BY/i.match?(sql)
  op = OPS[modifier[:op].to_s] || "="
  "#{sql} HAVING #{modifier[:field]} #{op} #{modifier[:value]}"
end

.apply_join(sql, modifier, entity) ⇒ Object 



92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/sql_chatbot/grammar/modifiers.rb', line 92

def self.apply_join(sql, modifier, entity)
  assoc_name = modifier[:association].to_s
  assoc = entity.associations[assoc_name]
  raise "association '#{assoc_name}' not on entity #{entity.name}" unless assoc

  join_clause = assoc.join_clause
  # Re-emit the join clause with quoted identifiers.
  # joinClause format: "src_table.src_col = tgt_table.tgt_col"
  lhs, rhs = join_clause.split("=").map(&:strip)
  lt, lc = lhs.split(".")
  rt, rc = rhs.split(".")
  target_table = rt
  quoted_clause = "#{qc(lt, lc)} = #{qc(rt, rc)}"
  join_sql = " JOIN #{q(target_table)} ON #{quoted_clause}"

  if /\bWHERE\b/i.match?(sql)
    sql.sub(/\bWHERE\b/i) { "#{join_sql} WHERE " }
  else
    "#{sql}#{join_sql}"
  end
end

.apply_limit(sql, modifier) ⇒ Object 



135
136
137
138
139
140
141
142
 
# File 'lib/sql_chatbot/grammar/modifiers.rb', line 135

def self.apply_limit(sql, modifier)
  limit_val = modifier[:value]
  if /LIMIT \d+/i.match?(sql)
    sql.sub(/LIMIT \d+/i, "LIMIT #{limit_val}")
  else
    "#{sql} LIMIT #{limit_val}"
  end
end

.apply_order_by(sql, modifier, entity) ⇒ Object 



126
127
128
129
130
131
132
133
 
# File 'lib/sql_chatbot/grammar/modifiers.rb', line 126

def self.apply_order_by(sql, modifier, entity)
  field_name = modifier[:field].to_s
  raise "order_by field '#{field_name}' not on entity #{entity.name}" unless entity.fields[field_name]
  # The intent extractor LLM emits direction as either `direction` or
  # `op` (it's prompted with the where-modifier shape and reuses `op`).
  direction = (modifier[:direction] || modifier["direction"] || modifier[:op] || modifier["op"] || "desc").to_s.upcase
  "#{sql} ORDER BY #{qc(entity.table, field_name)} #{direction}"
end

.apply_time(sql, modifier, entity) ⇒ Object 



85
86
87
88
89
90
 
# File 'lib/sql_chatbot/grammar/modifiers.rb', line 85

def self.apply_time(sql, modifier, entity)
  window_key = modifier[:window].to_s
  expr = WINDOWS[window_key]
  raise "unknown time window #{window_key}" unless expr
  append_clause(sql, "#{qc(entity.table, modifier[:field])} >= #{expr}")
end

.apply_where(sql, modifier, entity) ⇒ Object 



60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
# File 'lib/sql_chatbot/grammar/modifiers.rb', line 60

def self.apply_where(sql, modifier, entity)
  field_name = modifier[:field].to_s
  field = entity.fields[field_name]
  raise "field '#{field_name}' not on entity #{entity.name}" unless field

  value = modifier[:value]
  ftype = field.type.to_s
  if ftype == "enum"
    enum_values = field.enum_values || {}
    str_value = value.to_s
    unless enum_values.key?(str_value) || enum_values.key?(str_value.to_sym)
      raise "enum value '#{value}' not in registry for #{entity.name}.#{field_name}"
    end
    value = enum_values[str_value] || enum_values[str_value.to_sym]
  elsif (ftype == "int" || ftype == "decimal") && value.is_a?(String) && value !~ /\A-?\d+(\.\d+)?\z/
    raise "type mismatch: #{ftype} column #{entity.name}.#{field_name} cannot equal string '#{value}'"
  elsif ftype == "bool" && value.is_a?(String) && value !~ /\A(true|false|t|f|0|1)\z/i
    raise "type mismatch: bool column #{entity.name}.#{field_name} cannot equal string '#{value}'"
  end

  op = OPS[modifier[:op].to_s] || "="
  formatted = value.is_a?(String) ? "'#{value.gsub("'", "''")}'" : value
  append_clause(sql, "#{qc(entity.table, field_name)} #{op} #{formatted}")
end

.q(name) ⇒ Object 



9
10
11
 
# File 'lib/sql_chatbot/grammar/modifiers.rb', line 9

def self.q(name)
  Primitives.q(name)
end

.qc(table, col) ⇒ Object 



13
14
15
 
# File 'lib/sql_chatbot/grammar/modifiers.rb', line 13

def self.qc(table, col)
  Primitives.qc(table, col)
end