marcopanandClaude Fable 5
e24b41b156
Lever 1: Join-graph via FK logics in annotations + suggest-fks command
- TableAnnotation.foreign_keys field stores curated logical FKs (DWH has no FK constraints)
- tht schema suggest-fks: mine from approved SQL, heuristics (time_key → dim_time),
same-name discovery + explicit --assume flag for multi-owner PKs
- mschema renders 【Foreign keys】 section populated; validation in merge.py
- SKILL.md F4 now reads FKs from mschema-text, no custom data_time_key logic
Lever 2: Context-pack consolidation at kickoff (tht search pack)
- Single embedding of question, reused for schema + evidence + solved searches
- One command: tht search pack <question> --session <id> → retrieval_pack.md
- Graceful degradation when Ollama/vector store unreachable (exit 0, empty sections)
- SKILL.md F1 prescribes as first call; reduces model thinking turns via pre-retrieval
Lever 3: Phase-summary recap v2 auto-construction from session ledger
- tht session show --json includes full decisions ledger
- tht phase meta --json exports 'emits' (substantive decision types per phase)
- Gate appends deterministic 【Decisioni registrate in questa fase】 section (appendLedgerSection)
- Model authors only summary + checks; recap table comes from persisted state (exact by construction)
- SKILL.md Disciplina 6: brief model output, gate fills the rest
Tests: 358 Python (including 10 FK + 3 pack + 1 session-ledger tests) + 111 JS gate tests, all pass.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
59 lines
2.3 KiB
Python
59 lines
2.3 KiB
Python
"""Mining dei join reali dall'SQL approvato: coppie equi-join -> FK logiche candidate.
|
|
|
|
La fonte di verita' sono le query gia' validate da un umano (sql_final.sql, ctes/*.sql
|
|
delle sessioni approvate): un equi-join ricorrente tra due tabelle del catalogo, con
|
|
una delle due colonne PK della propria tabella, e' una FK logica ad alta confidenza.
|
|
"""
|
|
|
|
from collections import Counter
|
|
|
|
import sqlglot
|
|
from sqlglot import exp
|
|
|
|
from tht.mschema.models import PhysicalSchema
|
|
|
|
JoinPair = tuple[str, str, str, str] # (src_table, src_col, ref_table, ref_col)
|
|
|
|
|
|
def mine_join_pairs(sql_text: str, physical: PhysicalSchema) -> Counter:
|
|
"""Estrae le coppie equi-join tra tabelle del catalogo da un testo SQL.
|
|
|
|
Ritorna un Counter {(src_table, src_col, ref_table, ref_col): occorrenze}.
|
|
Il lato ref e' quello la cui colonna e' PK della propria tabella; coppie in cui
|
|
nessuno o entrambi i lati sono PK vengono scartate (non FK-like). Alias e CTE
|
|
vengono risolti; i riferimenti a CTE (non nel catalogo) sono ignorati.
|
|
"""
|
|
pairs: Counter = Counter()
|
|
try:
|
|
statements = sqlglot.parse(sql_text, read="postgres")
|
|
except sqlglot.errors.ParseError:
|
|
return pairs
|
|
for stmt in statements:
|
|
if stmt is None:
|
|
continue
|
|
alias_map: dict[str, str] = {}
|
|
for t in stmt.find_all(exp.Table):
|
|
alias_map[t.alias_or_name] = t.name
|
|
for eq in stmt.find_all(exp.EQ):
|
|
left, right = eq.left, eq.right
|
|
if not (isinstance(left, exp.Column) and isinstance(right, exp.Column)):
|
|
continue
|
|
if not (left.table and right.table):
|
|
continue
|
|
lt = alias_map.get(left.table, left.table)
|
|
rt = alias_map.get(right.table, right.table)
|
|
if lt == rt or lt not in physical.tables or rt not in physical.tables:
|
|
continue
|
|
lc, rc = left.name, right.name
|
|
if lc not in physical.tables[lt].columns or rc not in physical.tables[rt].columns:
|
|
continue
|
|
l_pk = physical.tables[lt].columns[lc].pk
|
|
r_pk = physical.tables[rt].columns[rc].pk
|
|
if l_pk == r_pk: # nessuna o entrambe PK: non FK-like
|
|
continue
|
|
if r_pk:
|
|
pairs[(lt, lc, rt, rc)] += 1
|
|
else:
|
|
pairs[(rt, rc, lt, lc)] += 1
|
|
return pairs
|