Module: SqlChatbot::Grammar::TemplateCompiler

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

Class Method Summary collapse

Class Method Details

.column_already_filtered?(sql, table, col) ⇒ Boolean

Returns:

  • (Boolean) 


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

def self.column_already_filtered?(sql, table, col)
  return true if sql.include?(Primitives.qc(table, col))
  /\b#{Regexp.escape(table)}\.#{Regexp.escape(col)}\b/i.match?(sql)
end

.compile(intent, registry) ⇒ Object 



10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
 
# File 'lib/sql_chatbot/grammar/template_compiler.rb', line 10

def self.compile(intent, registry)
  return { ok: false, reason: "unmatched: #{intent[:reason]}" } if intent[:status].to_s == "unmatched"

  entity_name = registry.aliases[intent[:entity]] || intent[:entity]
  entity = registry.entities[entity_name]
  return { ok: false, reason: "entity '#{intent[:entity]}' not in registry" } unless entity

  begin
    modifiers = Array(intent[:modifiers])
    primitive_sym = intent[:primitive].to_s
    rank_field = intent[:rank_field]
    limit_n    = intent[:n]

    # V1.3-K shape-repair pass — three real-world LLM emit-fidelity issues
    # captured during the 2026-04-28 hard sweep. Mirror of TS
    # `normalizeIntentShape` in template-compiler.ts.
    modifiers = normalize_intent_shape(modifiers, entity)

    # TOP_N / MIN_MAX express the rank/field through an `order_by`
    # modifier when the LLM phrases it that way ("biggest repo by stars"
    # → primitive=TOP_N + order_by(num_stars,desc)). Lift the
    # modifier's field into the primitive slot and drop it so the
    # compiler doesn't emit ORDER BY twice (which produces a Postgres
    # syntax error and silently pushes the question to the LLM
    # fallback path). Write the lifted values back to the intent so
    # downstream consumers (programmatic renderer) can read the
    # canonical rank_field/field regardless of which surface the LLM
    # emitted.
    field    = intent[:field]
    which    = intent[:which]
    direction = intent[:direction]
    if primitive_sym == "TOP_N" || primitive_sym == "MIN_MAX"
      order_mod = modifiers.find { |m| (m[:kind] || m["kind"]).to_s == "order_by" }
      if order_mod
        lifted_field = order_mod[:field] || order_mod["field"]
        raw_dir      = order_mod[:direction] || order_mod["direction"] || order_mod[:op] || order_mod["op"] || "desc"
        if primitive_sym == "TOP_N"
          rank_field ||= lifted_field
          # V1.3-U: pass through the lifted direction so "smallest X"
          # becomes ASC, mirroring TS liftOrderByForRank.
          direction ||= raw_dir.to_s.downcase
        else
          field ||= lifted_field
          which ||= raw_dir.to_s.downcase == "asc" ? :MIN : :MAX
        end
        modifiers = modifiers.reject { |m| m.equal?(order_mod) }
      end
      limit_mod = modifiers.find { |m| (m[:kind] || m["kind"]).to_s == "limit" }
      if limit_mod && primitive_sym == "TOP_N"
        limit_n ||= limit_mod[:value] || limit_mod["value"]
        modifiers = modifiers.reject { |m| m.equal?(limit_mod) }
      end
      intent[:rank_field] = rank_field
      intent[:field]      = field
      intent[:which]      = which
      intent[:direction]  = direction
      intent[:modifiers]  = modifiers
      intent[:n]          = limit_n
    end

    sql = Primitives.build(
      primitive:  intent[:primitive],
      entity:     entity,
      field:      field,
      which:      which,
      n:          limit_n,
      rank_field: rank_field,
      direction:  direction,
      group_by:   intent[:group_by]
    )

    modifiers.each do |m|
      sql = Modifiers.apply(sql, m, entity)
    end

    sql = with_implicit_filters(sql, entity)

    unless sql =~ /LIMIT \d+/i || primitive_sym == "COUNT" || sql =~ /COUNT\(/i
      sql = "#{sql} LIMIT 100"
    end

    { ok: true, sql: sql }
  rescue => e
    { ok: false, reason: e.message }
  end
end

.default_timestamp_field(entity) ⇒ Object 



172
173
174
175
176
 
# File 'lib/sql_chatbot/grammar/template_compiler.rb', line 172

def self.default_timestamp_field(entity)
  return "created_at" if entity.fields["created_at"]
  ts_fields = entity.fields.values.select { |f| f.type.to_s == "timestamp" }
  ts_fields.length == 1 ? ts_fields.first.column : nil
end

.normalize_intent_shape(modifiers, entity) ⇒ Object

V1.3-K shape-repair pass — mirror of TS normalizeIntentShape.

1. `where` modifier whose value is a WINDOWS keyword
   → coerce to a `time` modifier with that window.
2. `time` modifier where the LLM used `value` for the keyword
   instead of `window` → alias the key.
3. `time` modifier missing field entirely → default to created_at
   if it exists, else the lone timestamp column.



135
136
137
 
# File 'lib/sql_chatbot/grammar/template_compiler.rb', line 135

def self.normalize_intent_shape(modifiers, entity)
  modifiers.map { |m| repair_one(m, entity) }
end

.repair_one(modifier, entity) ⇒ Object 



139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/sql_chatbot/grammar/template_compiler.rb', line 139

def self.repair_one(modifier, entity)
  kind = (modifier[:kind] || modifier["kind"]).to_s
  value = modifier[:value] || modifier["value"]
  field = modifier[:field] || modifier["field"]
  window = modifier[:window] || modifier["window"]

  # Rule 1: where with WINDOWS keyword → time
  if kind == "where" && value.is_a?(String) && SqlChatbot::Grammar::Modifiers::WINDOWS.key?(value.downcase)
    return {
      kind: "time",
      field: field || default_timestamp_field(entity) || "",
      window: value.downcase,
    }
  end

  # Rule 2: time with `value` instead of `window`
  if kind == "time" && (window.nil? || window.to_s.empty?) && value.is_a?(String) && SqlChatbot::Grammar::Modifiers::WINDOWS.key?(value.downcase)
    return {
      kind: "time",
      field: field || default_timestamp_field(entity) || "",
      window: value.downcase,
    }
  end

  # Rule 3: time missing field → default
  if kind == "time" && (field.nil? || field.to_s.empty?) && window
    fallback = default_timestamp_field(entity)
    return { kind: "time", field: fallback, window: window } if fallback
  end

  modifier
end

.with_implicit_filters(sql, entity) ⇒ Object

Append every implicit WHERE clause for this entity. Two sources land in the same list: schema-detected soft-delete columns and developer- declared ‘default_filters`. A clause is skipped when its column is already referenced in the generated SQL — explicit user filters always win over the implicit default.



102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/sql_chatbot/grammar/template_compiler.rb', line 102

def self.with_implicit_filters(sql, entity)
  filters = Array(entity.respond_to?(:implicit_filters) ? entity.implicit_filters : nil)
  return sql if filters.empty?

  to_apply = filters.reject { |f| column_already_filtered?(sql, entity.table, f.column) }
  return sql if to_apply.empty?

  combined = to_apply.map { |f| "#{Primitives.qc(entity.table, f.column)} #{f.expr}" }.join(" AND ")
  if /\bWHERE\b/i.match?(sql)
    return sql.sub(/\bWHERE\b/i) { "WHERE #{combined} AND " }
  end
  before_match = sql.match(/ (GROUP BY|ORDER BY|LIMIT) /i)
  if before_match
    return sql.sub(before_match[0]) { " WHERE #{combined}#{before_match[0]}" }
  end
  "#{sql} WHERE #{combined}"
end