2d:null 2e:["$","title",null,{"children":"Txt2SQL Generation | Kasi Viswanath | Observable"}] 33:T49e, # Txt2SQL Generation **Generates SQL queries from natural language questions** using a credit risk dataset in SQLite and a local LLM. This notebook demonstrates a practical Text-to-SQL (Text2SQL) system built for the [credit dataset](https://huggingface.co/datasets/birdsql/livesqlbench-base-lite-sqlite/tree/main). It combines rich database schema information, detailed column semantics, and domain-specific knowledge to help a local LLM produce reliable, executable SQLite queries. ### Key Features - Uses the **LiveSQLBench Credit** dataset (SQLite) - Leverages 3 context files for high-quality generation: 1. Column meanings - \${columnMeanings} 2. Domain knowledge base - \${externalKg} 3. Database schema - \${dbSchema} To Do: 1. Add ~~Exact Match, QAS~~, VES metrics. 2. Integrate full test-case suite (BIRD-Interact style) + Export 3. ~~Add multi-LLM support (cloud + local)~~ **Target Reference** ✅ when the setup is complete, the live notebook should match exactly the appearance and functionality of the attached PDF (pages 1–3 in particular represent the target final UI with dynamic metrics, evaluation cards, and context files).34:T7d8,### Database Visualization

📊 Entity Relationship Diagram (ERD)

${await visualizeSqlite(db)}

### Context Files

📋 Column Meanings

${Inputs.table(column_meanings)}

📚 External Knowledge Base

${Inputs.table(external_kg)}

📋 View Full Database Schema

```sql ${db_schema} ```35:T1da3,// ══════════════════════════════════════════════════════════ // Test Case Builder UI // ══════════════════════════════════════════════════════════ viewof testCases = (() => { const CACHE_KEY = "test_cases" const loadCache = () => { try { const r = localStorage.getItem(CACHE_KEY); return r ? JSON.parse(r) : null; } catch { return null; } } const saveCache = (c) => { try { localStorage.setItem(CACHE_KEY, JSON.stringify(c)); } catch {} } const clearCache = () => localStorage.removeItem(CACHE_KEY) let cases = loadCache() ?? [ { title: "Query 1", userQuery: "", groundTruthSql: "" } ]; const container = document.createElement("div"); container.style.cssText = "font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',sans-serif;max-width:900px"; function field(label, value, multiline, onChange, placeholder = "") { const wrap = document.createElement("div"); wrap.style.cssText = "margin-bottom:10px"; const lbl = document.createElement("div"); lbl.textContent = label; lbl.style.cssText = "font-size:11px;font-weight:700;text-transform:uppercase;letter-spacing:.5px;color:#64748b;margin-bottom:4px"; const el = document.createElement(multiline ? "textarea" : "input"); el.value = value; el.placeholder = placeholder; el.style.cssText = `width:100%;padding:8px 10px;border:1px solid #e2e8f0;border-radius:6px; font-size:12px;font-family:${multiline ? "monospace" : "inherit"}; resize:vertical;outline:none;box-sizing:border-box; ${multiline ? `min-height:${label === "Ground Truth SQL" ? "120px" : "60px"}` : "height:34px"}`; el.addEventListener("focus", () => el.style.borderColor = "#6366f1"); el.addEventListener("blur", () => el.style.borderColor = "#e2e8f0"); el.addEventListener("input", () => onChange(el.value)); wrap.append(lbl, el); return wrap; } // ── save silently (no reactive trigger) ── function save() { saveCache(cases); } function dispatch() { saveCache(cases); container.value = cases.map(c => ({ ...c })); container.dispatchEvent(new Event("input", { bubbles: true })); } function renderCards() { container.innerHTML = ""; // ── Header ────────────────────────────────────────── const header = document.createElement("div"); header.style.cssText = "display:flex;align-items:center;justify-content:space-between;margin-bottom:14px"; header.innerHTML = `

Test Cases (${cases.length})

`; const addBtn = document.createElement("button"); addBtn.textContent = "+ Add Test Case"; addBtn.style.cssText = "padding:7px 14px;background:#6366f1;color:#fff;border:none;border-radius:6px;font-size:13px;font-weight:600;cursor:pointer"; addBtn.onmouseenter = () => addBtn.style.background = "#4f46e5"; addBtn.onmouseleave = () => addBtn.style.background = "#6366f1"; addBtn.onclick = () => { cases.push({ title: `Query ${cases.length + 1}`, userQuery: "", groundTruthSql: "" }); renderCards(); save(); }; header.appendChild(addBtn); container.appendChild(header); // ── Cards ──────────────────────────────────────────── cases.forEach((tc, i) => { const card = document.createElement("div"); card.style.cssText = "background:#fff;border:1px solid #e2e8f0;border-radius:10px;margin-bottom:10px;overflow:hidden;box-shadow:0 1px 3px rgba(0,0,0,.05)"; const cardHeader = document.createElement("div"); cardHeader.style.cssText = "display:flex;align-items:center;gap:10px;padding:10px 14px;background:#f8fafc;border-bottom:1px solid #e2e8f0;cursor:pointer;user-select:none"; cardHeader.innerHTML = ` ${tc.title || `Query ${i+1}`} ▾ collapse`; const body = document.createElement("div"); body.style.cssText = "padding:14px 16px"; let collapsed = false; cardHeader.onclick = () => { collapsed = !collapsed; body.style.display = collapsed ? "none" : "block"; cardHeader.querySelector("span:last-child").textContent = collapsed ? "▸ expand" : "▾ collapse"; }; body.appendChild(field("Title", tc.title, false, v => { tc.title = v; cardHeader.querySelector("span").textContent = v || `Query ${i+1}`; save(); }, `Query ${i+1}`)); body.appendChild(field("User Query", tc.userQuery, true, v => { tc.userQuery = v; save(); }, "Describe what you want to query...")); body.appendChild(field("Ground Truth SQL", tc.groundTruthSql, true, v => { tc.groundTruthSql = v; save(); }, "SELECT ...")); if (cases.length > 1) { const del = document.createElement("button"); del.textContent = "✕ Remove"; del.style.cssText = "margin-top:4px;padding:5px 12px;background:transparent;border:1px solid #fca5a5;color:#dc2626;border-radius:6px;font-size:12px;cursor:pointer"; del.onmouseenter = () => del.style.background = "#fee2e2"; del.onmouseleave = () => del.style.background = "transparent"; del.onclick = () => { cases.splice(i, 1); renderCards(); save(); }; body.appendChild(del); } card.append(cardHeader, body); container.appendChild(card); }); // ── Actions bar ────────────────────────────────────── const bar = document.createElement("div"); bar.style.cssText = "margin-top:6px;display:flex;gap:10px;align-items:center"; const runBtn = document.createElement("button"); runBtn.textContent = "▶ Run Evaluation"; runBtn.style.cssText = "padding:9px 22px;background:#10b981;color:#fff;border:none;border-radius:7px;font-size:14px;font-weight:700;cursor:pointer"; runBtn.onmouseenter = () => runBtn.style.background = "#059669"; runBtn.onmouseleave = () => runBtn.style.background = "#10b981"; runBtn.onclick = () => dispatch(); const clearBtn = document.createElement("button"); clearBtn.textContent = "🗑 Clear Cache"; clearBtn.style.cssText = "padding:9px 16px;background:transparent;border:1px solid #e2e8f0;color:#64748b;border-radius:7px;font-size:13px;cursor:pointer"; clearBtn.onmouseenter = () => clearBtn.style.background = "#fee2e2"; clearBtn.onmouseleave = () => clearBtn.style.background = "transparent"; clearBtn.onclick = () => { if (!confirm("Clear all cached test cases?")) return; clearCache(); cases = [{ title: "Query 1", userQuery: "", groundTruthSql: "" }]; renderCards(); save(); }; const hint = document.createElement("span"); hint.textContent = `${cases.length} case${cases.length !== 1 ? "s" : ""} ready`; hint.style.cssText = "font-size:12px;color:#94a3b8"; bar.append(runBtn, clearBtn, hint); container.appendChild(bar); } renderCards(); container.value = cases.map(c => ({ ...c })); return container; })()36:T5da,## Related Work | Name | Link | Metric Details| Score | Cons/Gaps | Dialect | Additional Notes | |-|-|-|-| - | | Bird Interact | https://bird-interact.github.io | BI Queries: Soft **Exact Match**(Results Comparision) with Removing Comments, Distinct, Round, and wrappers + Optional Conditions (order is false)

DM Queries: Case-Specific Scripts that check:
• Return value of verification query (e.g., calling created function/view)
• Presence/shape/content of created artifacts (tables, indexes, constraints)
• Targeted state properties (e.g., row counts, key invariants) | Binary(0/1) |• Missing/additional Columns cause score to 0
• Weightage to user query specific columns
•Results are normalized| PostgreSQL | | Bird Bench | https://bird-bench.github.io | **Execution Accuracy (EX)** - Result comparision b/w generate and expected sql without normalization
•Valid Efficiency Score (VES) - measure the efficiency of valid SQLs generated by models | Binary(0/1) | • Missing/additional Columns cause score to 0
• Weightage to user query specific columns | PostgreSQL | | Spider 2.0 | https://spider2-sql.github.io | • Table-based assessment method - eval scripts to focus on essential components of answer (evaluation script are reviewed by at least three annotators) | Binary(0/1) | - | Snowflake | • Error Analysis of SQL Generation | | WikiSQL | https://github.com/salesforce/WikiSQL | Result Comparision | Binary(0/1) | - | | | -37:T560d,// ══════════════════════════════════════════════════════════════════════════════ // BIRD-Interact | Observable Evaluation Cell (v3) // Paste the BODY of this async function into an Observable notebook cell. // // Features: // • SQL editors auto-resize to content height (great for side-by-side diff) // • Editors start locked/greyed → ✏ Edit unlocks, 🔒 re-locks // • Copy icon overlay at top-right corner of each editor // • ▶ Run SQL button below each editor (re-runnable, stale-DOM bug fixed) // • After Run: scores RE-COMPUTE and update live in the card header // • "↺ Re-scored" badge appears when scores have been updated // • Pass/fail badge + card border colour update after re-score // • Scrollable result tables (sticky headers, zebra rows, max-height 260px) // • Summary bar + collapsible pass/fail cards // ══════════════════════════════════════════════════════════════════════════════ async function evaluate(testCases, metrics) { // ── 1. Run all test cases ───────────────────────────────────────────────── const results = await Promise.all( testCases.map(async (tc, i) => { let generatedSql = "", generatedRows = [], error = null; try { const gen = await generateSQL(tc.userQuery, db_schema, column_meanings, external_kg); generatedSql = gen.sql || ""; generatedRows = await db.query(generatedSql); } catch (e) { error = (e && e.message) ? e.message : String(e); } const groundTruthRows = await db.query(tc.groundTruthSql).catch(() => []); const scores = {}; for (const name in metrics) { try { scores[name] = await metrics[name](generatedSql, tc.groundTruthSql, generatedRows, groundTruthRows); } catch (_) { scores[name] = { value: "err", pass: false }; } } return { index: i + 1, title: tc.title || ("Case " + (i + 1)), generatedSql, groundTruthSql: tc.groundTruthSql, generatedRows, groundTruthRows, scores, passed: Object.values(scores).every(s => !!s.pass), error }; }) ); const passCount = results.filter(r => r.passed).length; // ── Helpers ─────────────────────────────────────────────────────────────── function el(tag, css, text) { const n = document.createElement(tag); if (css) n.style.cssText = css; if (text != null) n.textContent = text; return n; } function copyText(text, feedbackEl) { const done = (ok) => { feedbackEl.textContent = ok ? "Copied!" : "Failed"; feedbackEl.style.color = ok ? "#059669" : "#dc2626"; setTimeout(() => { feedbackEl.textContent = ""; }, 1500); }; if (navigator.clipboard && navigator.clipboard.writeText) { navigator.clipboard.writeText(text).then(() => done(true), () => done(false)); return; } try { const t = document.createElement("textarea"); t.value = text; document.body.appendChild(t); t.select(); document.execCommand("copy"); document.body.removeChild(t); done(true); } catch (_) { done(false); } } // Inline SVGs const SVG_COPY = ``; const SVG_EDIT = ``; const SVG_LOCK = ``; const SVG_PLAY = ``; // Pre-built button style strings (avoids fragile cssText .replace() hacks) const BTN_DEFAULT = "border:1px solid #e2e8f0;background:#f8fafc;padding:4px 10px;border-radius:5px;" + "cursor:pointer;font-size:11px;font-weight:600;color:#64748b;" + "display:inline-flex;align-items:center;gap:4px;line-height:1.4;"; const BTN_ACTIVE = "border:1px solid #fde68a;background:#fef9c3;padding:4px 10px;border-radius:5px;" + "cursor:pointer;font-size:11px;font-weight:600;color:#854d0e;" + "display:inline-flex;align-items:center;gap:4px;line-height:1.4;"; // ── Auto-resize textarea to its content ────────────────────────────────── function autoResize(ta) { ta.style.height = "auto"; // Grows freely; scrolls after 560 px ta.style.height = Math.max(160, Math.min(ta.scrollHeight + 4, 560)) + "px"; } // ── Scrollable result table ─────────────────────────────────────────────── function renderTable(rows) { // ~10 rows × 29px per row + 34px sticky header ≈ 324px const wrap = el("div", "max-height:324px;overflow:auto;border:1px solid #e2e8f0;border-radius:6px;background:#fff;" ); if (!rows || !rows.length) { wrap.appendChild(el("div", "padding:12px 14px;color:#94a3b8;font-style:italic;font-size:12px;", "No rows returned" )); return wrap; } const cols = Object.keys(rows[0]); const tbl = el("table", "border-collapse:collapse;font-size:11.5px;min-width:100%;"); const thead = document.createElement("thead"); const trh = document.createElement("tr"); cols.forEach(c => { trh.appendChild(el("th", "position:sticky;top:0;z-index:1;background:#f1f5f9;padding:6px 10px;" + "border:1px solid #e2e8f0;text-align:left;white-space:nowrap;font-weight:600;color:#374151;", c )); }); thead.appendChild(trh); tbl.appendChild(thead); const tbody = document.createElement("tbody"); rows.forEach((row, ri) => { const tr = document.createElement("tr"); tr.style.background = ri % 2 === 0 ? "#fff" : "#f9fafb"; cols.forEach(c => { tr.appendChild(el("td", "padding:5px 10px;border:1px solid #e2e8f0;vertical-align:top;white-space:nowrap;color:#374151;", row[c] == null ? "" : String(row[c]) )); }); tbody.appendChild(tr); }); tbl.appendChild(tbody); wrap.appendChild(tbl); return wrap; } // ── SQL Editor block ────────────────────────────────────────────────────── // onRunComplete(sql, rows) — async callback so the card can re-score function makeSqlEditor(label, initialSql, accent, initialRows, onRunComplete) { const box = el("div", "display:flex;flex-direction:column;min-width:0;"); // Label row const labelRow = el("div", "display:flex;align-items:center;justify-content:space-between;margin-bottom:6px;" ); labelRow.appendChild(el("div", "font-size:10px;font-weight:700;text-transform:uppercase;letter-spacing:.6px;color:#64748b;", label )); // Edit / Lock toggle const editBtn = document.createElement("button"); editBtn.innerHTML = SVG_EDIT + " Edit"; editBtn.title = "Enable editing"; editBtn.style.cssText = BTN_DEFAULT; labelRow.appendChild(editBtn); box.appendChild(labelRow); // Textarea wrapper (relative, for copy overlay) const taWrap = el("div", "position:relative;"); const ta = document.createElement("textarea"); ta.value = initialSql || ""; ta.readOnly = true; ta.spellcheck = false; ta.style.cssText = "width:100%;box-sizing:border-box;min-height:160px;" + "resize:vertical;overflow:auto;" + "font:12.5px/1.55 ui-monospace,SFMono-Regular,Menlo,Consolas,monospace;" + "padding:12px 40px 12px 14px;" + "background:#f8fafc;color:#94a3b8;" + "border:1.5px solid #e2e8f0;border-radius:7px;outline:none;" + "transition:background .18s,color .18s,border-color .18s;cursor:default;"; // Grow on input (user is editing) ta.addEventListener("input", () => autoResize(ta)); // Copy icon overlaid top-right const copyBtn = document.createElement("button"); copyBtn.innerHTML = SVG_COPY; copyBtn.title = "Copy SQL"; copyBtn.style.cssText = "position:absolute;top:8px;right:8px;" + "border:none;background:rgba(241,245,249,.85);backdrop-filter:blur(2px);" + "cursor:pointer;padding:5px;border-radius:5px;color:#94a3b8;line-height:0;" + "transition:background .15s,color .15s;"; copyBtn.addEventListener("mouseenter", () => { copyBtn.style.background = "#e2e8f0"; copyBtn.style.color = "#334155"; }); copyBtn.addEventListener("mouseleave", () => { copyBtn.style.background = "rgba(241,245,249,.85)"; copyBtn.style.color = "#94a3b8"; }); const copyFeedback = el("span", "position:absolute;top:8px;right:42px;font-size:10px;font-weight:600;pointer-events:none;" ); copyBtn.addEventListener("click", e => { e.stopPropagation(); copyText(ta.value, copyFeedback); }); taWrap.appendChild(ta); taWrap.appendChild(copyBtn); taWrap.appendChild(copyFeedback); box.appendChild(taWrap); // Edit/Lock toggle logic let editing = false; editBtn.addEventListener("click", () => { editing = !editing; if (editing) { ta.readOnly = false; ta.style.background = "#fff"; ta.style.color = "#1e293b"; ta.style.borderColor = accent; ta.style.cursor = "text"; editBtn.innerHTML = SVG_LOCK + " Lock"; editBtn.title = "Lock editing"; editBtn.style.cssText = BTN_ACTIVE; ta.focus(); autoResize(ta); // expand to full content when unlocked } else { ta.readOnly = true; ta.style.background = "#f8fafc"; ta.style.color = "#94a3b8"; ta.style.borderColor = "#e2e8f0"; ta.style.cursor = "default"; editBtn.innerHTML = SVG_EDIT + " Edit"; editBtn.title = "Enable editing"; editBtn.style.cssText = BTN_DEFAULT; } }); // Run SQL — full-width below editor const runRow = el("div", "display:flex;align-items:center;gap:8px;margin-top:8px;margin-bottom:10px;" ); const runBtn = document.createElement("button"); runBtn.innerHTML = SVG_PLAY + " Run SQL"; runBtn.style.cssText = "flex:1;border:none;background:" + accent + ";color:#fff;" + "padding:8px 0;border-radius:6px;cursor:pointer;font-size:12px;font-weight:600;" + "display:inline-flex;align-items:center;justify-content:center;gap:5px;"; runBtn.addEventListener("mouseenter", () => { if (!runBtn.disabled) runBtn.style.opacity = ".82"; }); runBtn.addEventListener("mouseleave", () => { runBtn.style.opacity = "1"; }); const runInfo = el("span", "font-size:11px;font-weight:600;white-space:nowrap;"); runRow.appendChild(runBtn); runRow.appendChild(runInfo); box.appendChild(runRow); // Results area const resultHeader = el("div", "display:flex;align-items:baseline;gap:8px;margin-bottom:6px;"); resultHeader.appendChild(el("div", "font-size:10px;font-weight:700;text-transform:uppercase;letter-spacing:.6px;color:#64748b;", "Results" )); const resultMeta = el("span", "font-size:11px;color:#94a3b8;", (initialRows || []).length + " rows"); resultHeader.appendChild(resultMeta); box.appendChild(resultHeader); // resultContainer — always clear children, never replace the node itself (fixes re-run bug) const resultContainer = el("div", ""); resultContainer.appendChild(renderTable(initialRows || [])); box.appendChild(resultContainer); const errorBox = el("div", ""); box.appendChild(errorBox); // Run handler runBtn.addEventListener("click", async () => { runBtn.disabled = true; runBtn.style.opacity = ".5"; runInfo.textContent = "Running…"; runInfo.style.color = "#64748b"; errorBox.innerHTML = ""; try { const rows = await db.query(ta.value); while (resultContainer.firstChild) resultContainer.removeChild(resultContainer.firstChild); resultContainer.appendChild(renderTable(rows)); resultMeta.textContent = rows.length + " rows"; runInfo.textContent = "✓ Done"; runInfo.style.color = "#059669"; // ── KEY FIX: notify card so it can re-score ── if (typeof onRunComplete === "function") await onRunComplete(ta.value, rows); setTimeout(() => { runInfo.textContent = ""; }, 1600); } catch (e) { errorBox.appendChild(el("div", "padding:9px 13px;margin-top:4px;background:#fff1f2;color:#991b1b;" + "border-radius:6px;font-size:11.5px;border:1px solid #fecaca;", "Error: " + ((e && e.message) ? e.message : String(e)) )); runInfo.textContent = "✗ Error"; runInfo.style.color = "#dc2626"; setTimeout(() => { runInfo.textContent = ""; }, 2200); } finally { runBtn.disabled = false; runBtn.style.opacity = "1"; } }); // Initial resize deferred — actual sync happens when card is first opened return { box, ta }; } // ── Score chip ──────────────────────────────────────────────────────────── function buildScoreChip(name, s) { const pass = !!s.pass; const val = typeof s.value === "number" ? s.value.toFixed(4) : String(s.value); const chip = el("span", "font-size:11px;padding:2px 8px;border-radius:4px;font-weight:600;" + "background:" + (pass ? "#d1fae5" : "#fee2e2") + ";" + "color:" + (pass ? "#065f46" : "#991b1b") + ";" ); chip.textContent = name + ": " + val; return chip; } // ── Card ────────────────────────────────────────────────────────────────── function card(r) { // Mutable state for live re-scoring let curGenSql = r.generatedSql, curGenRows = r.generatedRows; let curGtSql = r.groundTruthSql, curGtRows = r.groundTruthRows; async function reScore() { const s = {}; for (const name in metrics) { try { s[name] = await metrics[name](curGenSql, curGtSql, curGenRows, curGtRows); } catch (_) { s[name] = { value: "err", pass: false }; } } return s; } const passColor = (p) => p ? "#10b981" : "#ef4444"; const passBg = (p) => p ? "#f0fdf4" : "#fff1f2"; const passChipBg = (p) => p ? "#d1fae5" : "#fee2e2"; const passChipFg = (p) => p ? "#065f46" : "#991b1b"; const cardNode = el("div", "border:1.5px solid " + passColor(r.passed) + ";border-radius:10px;margin-bottom:14px;" + "overflow:hidden;background:#fff;box-shadow:0 1px 5px rgba(0,0,0,.08);" ); const header = el("div", "display:flex;align-items:center;gap:10px;padding:11px 16px;cursor:pointer;user-select:none;" + "background:" + passBg(r.passed) + ";" + "border-bottom:1.5px solid " + passColor(r.passed) + "22;" ); const badge = el("span", "font-size:11px;font-weight:700;padding:3px 10px;border-radius:4px;flex-shrink:0;" + "background:" + passChipBg(r.passed) + ";color:" + passChipFg(r.passed) + ";", r.passed ? "✓ PASS" : "✗ FAIL" ); const titleEl = el("span", "font-weight:700;font-size:13.5px;color:#1e293b;flex-shrink:0;", r.title); const scoreWrap = el("div", "display:flex;gap:5px;flex-wrap:wrap;"); Object.entries(r.scores).forEach(([n, s]) => scoreWrap.appendChild(buildScoreChip(n, s))); // "Re-scored" indicator — hidden until first re-run const rescoredTag = el("span", "font-size:10px;font-weight:600;padding:2px 7px;border-radius:4px;" + "background:#eff6ff;color:#1d4ed8;display:none;", "↺ Re-scored" ); const arrow = el("span", "margin-left:auto;font-size:13px;color:#94a3b8;flex-shrink:0;", "▾"); header.appendChild(badge); header.appendChild(titleEl); header.appendChild(scoreWrap); header.appendChild(rescoredTag); header.appendChild(arrow); // Apply fresh scores to the header after re-run function applyNewScores(newScores) { while (scoreWrap.firstChild) scoreWrap.removeChild(scoreWrap.firstChild); Object.entries(newScores).forEach(([n, s]) => scoreWrap.appendChild(buildScoreChip(n, s))); const newPassed = Object.values(newScores).every(s => !!s.pass); badge.textContent = newPassed ? "✓ PASS" : "✗ FAIL"; badge.style.background = passChipBg(newPassed); badge.style.color = passChipFg(newPassed); cardNode.style.borderColor = passColor(newPassed); header.style.background = passBg(newPassed); rescoredTag.style.display = "inline"; } const body = el("div", "padding:16px;display:none;"); let open = false; let heightsSynced = false; header.addEventListener("click", () => { open = !open; body.style.display = open ? "block" : "none"; arrow.textContent = open ? "▴" : "▾"; // Sync textarea heights on first open (DOM layout only works when visible) if (open && !heightsSynced) { heightsSynced = true; requestAnimationFrame(() => syncEditorHeights()); } }); if (r.error) { body.appendChild(el("div", "color:#991b1b;background:#fff1f2;padding:9px 13px;border-radius:6px;" + "margin-bottom:14px;font-size:12px;border:1px solid #fecaca;", "Error: " + r.error )); } // CSS grid: always two equal columns, never wraps, with a visible divider const editors = el("div", "display:grid;grid-template-columns:1fr 1fr;gap:0;" + "border:1.5px solid #e2e8f0;border-radius:8px;overflow:hidden;" ); function editorCell(isRight) { return el("div", "padding:14px;" + (isRight ? "border-left:1.5px solid #e2e8f0;" : "") ); } const leftCell = editorCell(false); const rightCell = editorCell(true); // Generated SQL editor — re-scores when run const genEditor = makeSqlEditor( "Generated SQL", r.generatedSql, "#6366f1", r.generatedRows, async (sql, rows) => { curGenSql = sql; curGenRows = rows; applyNewScores(await reScore()); requestAnimationFrame(() => syncEditorHeights()); } ); // Ground Truth editor — also re-scores when run (lets you tweak the GT too) const gtEditor = makeSqlEditor( "Ground Truth SQL", r.groundTruthSql, "#10b981", r.groundTruthRows, async (sql, rows) => { curGtSql = sql; curGtRows = rows; applyNewScores(await reScore()); requestAnimationFrame(() => syncEditorHeights()); } ); leftCell.appendChild(genEditor.box); rightCell.appendChild(gtEditor.box); editors.appendChild(leftCell); editors.appendChild(rightCell); body.appendChild(editors); cardNode.appendChild(header); cardNode.appendChild(body); // Sync heights only after body is visible (scrollHeight is 0 while display:none) function syncEditorHeights() { // Let each textarea expand to its own content first autoResize(genEditor.ta); autoResize(gtEditor.ta); // Then equalise to the taller one const h = Math.max( parseInt(genEditor.ta.style.height, 10) || 160, parseInt(gtEditor.ta.style.height, 10) || 160 ); genEditor.ta.style.height = h + "px"; gtEditor.ta.style.height = h + "px"; } return cardNode; } // ── Assemble page ───────────────────────────────────────────────────────── const container = el("div", "font-family:ui-sans-serif,system-ui,-apple-system,'Segoe UI',Roboto,sans-serif;" + "font-size:13px;line-height:1.5;" ); const summary = el("div", "display:flex;gap:12px;margin-bottom:20px;flex-wrap:wrap;"); [ ["Total", results.length, "#e2e8f0", "#1e293b"], ["Passed", passCount, "#d1fae5", "#065f46"], ["Failed", results.length - passCount, "#fee2e2", "#991b1b"] ].forEach(([lbl, val, bg, fg]) => { const box = el("div", "flex:1;min-width:140px;background:" + bg + ";border-radius:8px;padding:14px 18px;" ); box.appendChild(el("div", "font-size:10px;font-weight:700;text-transform:uppercase;color:" + fg + ";opacity:.7;", lbl )); box.appendChild(el("div", "font-size:28px;font-weight:800;color:" + fg + ";", String(val))); summary.appendChild(box); }); container.appendChild(summary); results.forEach(r => container.appendChild(card(r))); return container; } 38:[{"id":"01b156b73c73ec2b6b5ee89cb49c31f715e4b58b88b97973ef37b2b24b1df80334207a0ad6e8470c360d38804a79e89519d3024221ad361998a1159ea09e5f7a","href":"https://static.observableusercontent.com/files/01b156b73c73ec2b6b5ee89cb49c31f715e4b58b88b97973ef37b2b24b1df80334207a0ad6e8470c360d38804a79e89519d3024221ad361998a1159ea09e5f7a","name":"credit_schema.txt","type":"text/plain","lastModified":1776379290408.239,"size":11514},{"id":"fe4ae5b80f488ba6c33e0bfda92d0c7eb1c0166ed1cd9c4d920bf45a48298a87f1e903bcc87489c173716d41fdcee07fef136b58d87cc85a174da40ddfd57b4b","href":"https://static.observableusercontent.com/files/fe4ae5b80f488ba6c33e0bfda92d0c7eb1c0166ed1cd9c4d920bf45a48298a87f1e903bcc87489c173716d41fdcee07fef136b58d87cc85a174da40ddfd57b4b","name":"credit_kb.jsonl","type":"application/octet-stream","lastModified":1776379290371.394,"size":19814},{"id":"faf603ab408502e551b36e7db44a001afd1ef1b3e70d8e779c90c17b2b06194dea4f97d66d0c00bbdce6852d7bbfcf582e1c3f159925434c19f543800bd68f37","href":"https://static.observableusercontent.com/files/faf603ab408502e551b36e7db44a001afd1ef1b3e70d8e779c90c17b2b06194dea4f97d66d0c00bbdce6852d7bbfcf582e1c3f159925434c19f543800bd68f37","name":"image.png","type":"image/png","lastModified":1776397007167.813,"size":36101},{"id":"526c442d53a97cd2571e26421a5c70da59599784c9d755adbb4f3bfc63bfb777561a89ef871cd39a0ce9ede2e4ed660bcd14ef10b3e6c1320186f1036d6345d2","href":"https://static.observableusercontent.com/files/526c442d53a97cd2571e26421a5c70da59599784c9d755adbb4f3bfc63bfb777561a89ef871cd39a0ce9ede2e4ed660bcd14ef10b3e6c1320186f1036d6345d2","name":"credit_column_meaning_base.json","type":"application/json","lastModified":1776379290396.475,"size":15799},{"id":"9bdb2eca7b34e5d8bb2c7054f2017ede4a43803657d942e63a06c4e51cc65b3f55c252a2b0ce92e354eaaa014a840000e5cb3605c94051a711785de13a6c50be","href":"https://static.observableusercontent.com/files/9bdb2eca7b34e5d8bb2c7054f2017ede4a43803657d942e63a06c4e51cc65b3f55c252a2b0ce92e354eaaa014a840000e5cb3605c94051a711785de13a6c50be","name":"credit_template.sqlite","type":"application/x-sqlite3","lastModified":1776379290630.187,"size":1306624},{"id":"286ff6523d17770bd03b40a14eb4e8729a731c70a9694c1a3888fa59eddb94445659955947902e384a8ed2d863639ef4e9a170ef35af4dfdd6f1fcce8b9a7cba","href":"https://static.observableusercontent.com/files/286ff6523d17770bd03b40a14eb4e8729a731c70a9694c1a3888fa59eddb94445659955947902e384a8ed2d863639ef4e9a170ef35af4dfdd6f1fcce8b9a7cba","name":"Txt2SQL Generation _ Kasi Viswanath _ Observable.pdf","type":"application/pdf","lastModified":1776656032278.306,"size":473859}] 31:{"id":"0999abd54bd1cf11","slug":"txt2sql-generation","title":"Txt2SQL Generation","thumbnail":"82cdb088e763300ecb8a8113ff88b1a22629fbfa53405a5d09e69f90a11436cb","access_level":"public","create_time":"$D2026-04-16T22:22:22.727Z","update_time":"$D2026-05-03T05:19:08.269Z","view_time":null,"first_public_time":"$D2026-04-20T03:39:44.362Z","has_unpublished_edits":null,"fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"b760ea566a896d7d","login":"kasivisu4","name":"Kasi Viswanath","avatar_url":"https://avatars-next.observableusercontent.com/48e36723913e7e5b18b906e1d7cde12fad4146715e78bbf74d3db7969a248b56","tier":"starter_2024"},"version":1595,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"fcd94e1c1ca12ae9","login":"kasivisu4","name":"Kasi Viswanath","avatar_url":"https://avatars-next.observableusercontent.com/48e36723913e7e5b18b906e1d7cde12fad4146715e78bbf74d3db7969a248b56","tier":null}],"members":[{"type":"individual","id":"be0076fec73aa550","login":"john-guerra","name":"John Alexis Guerra Gómez","avatar_url":"https://avatars-next.observableusercontent.com/21638cee44bb0ea70798da725b57111a5d3e2acebcd14524565992f7d1594aed","tier":null,"role":"editor"},{"type":"team","id":"b760ea566a896d7d","login":"kasivisu4","name":"Kasi Viswanath","avatar_url":"https://avatars-next.observableusercontent.com/48e36723913e7e5b18b906e1d7cde12fad4146715e78bbf74d3db7969a248b56","tier":"starter_2024","role":"editor"},{"type":"individual","id":"fcd94e1c1ca12ae9","login":"kasivisu4","name":"Kasi Viswanath","avatar_url":"https://avatars-next.observableusercontent.com/48e36723913e7e5b18b906e1d7cde12fad4146715e78bbf74d3db7969a248b56","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"Txt2SQL Generation","cells":[{"id":0,"value":"$33","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1075,"value":"[Reference Output](${await txt2sqlGeneration_KasiViswanath_Observable.url()})","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1289,"value":"

\n LLM Config\n

\n\n

Language Model

\n ${viewof llm_config}\n

\n\n

Embedding Model

\n ${viewof embedConfig}\n

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":939,"value":"$34","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1495,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1501,"value":"evaluate(testCases,evalMetrics)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1131,"value":"async function exact_match(generatedSql, groundTruthSql, generatedRows, groundTruthRows, orderSensitive = false) {\n if (generatedRows.length !== groundTruthRows.length) {\n return { value: false, pass: false };\n }\n\n const normalize = (rows) => {\n const normalized = rows.map((row) =>\n JSON.stringify(Object.values(row).map(String))\n );\n return orderSensitive ? normalized : normalized.sort();\n };\n\n const normalizedGenerated = normalize(generatedRows);\n const normalizedGroundTruth = normalize(groundTruthRows);\n\n const match = normalizedGenerated.every(\n (row, i) => row === normalizedGroundTruth[i]\n );\n\n return { value: match, pass: match };\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":419,"value":"![image.png](${await FileAttachment(\"image.png\").url()})","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":414,"value":"$36","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":29,"value":"db= SQLiteDatabaseClient.open(\n FileAttachment(\"credit_template.sqlite\").url()\n)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":168,"value":"column_meanings = Object.entries(credit_column_meaning_base).map(([key, value]) => {\n return {\n column_name: key,\n description: value\n };\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":55,"value":"credit_column_meaning_base = FileAttachment(\"credit_column_meaning_base.json\").json()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":57,"value":"external_kg = (await FileAttachment(\"credit_kb.jsonl\").text())\n .split(\"\\n\")\n .filter(s => s !== \"\")\n .map(s => JSON.parse(s))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":298,"value":"// SQL extraction and wrapper\nasync function generateSQL(userQuery, dbSchema, columnMeanings, externalKg, options = {}) {\n // Build the prompt\n const prompt = `# Database Schema:\n${dbSchema}\n# Column Meanings:\n${columnMeanings}\n# External Knowledge:\n${externalKg}\n# User Task:\n${userQuery}\nGenerate the correct SQLITE to handle the user task above:\n(FORMAT: You should enclose your final SQLITE in \\`\\`\\`sql [Your Generated SQL] \\`\\`\\` in the end.)\n# Your Generated SQL: \n \\`\\`\\`sql\\`\\`\\`\n`;\n\n // Call your existing LM Studio wrapper\n const fullResponse = await llm.run({...llm_config,prompt:prompt })\n \n // Extract SQL from markdown code blocks\n const sqlMatch = fullResponse.match(/```(?:sql|sqlite)?\\s*([\\s\\S]*?)```/);\n \n return {\n sql: sqlMatch ? sqlMatch[1].trim() : fullResponse.trim(),\n fullResponse: fullResponse\n };\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1552,"value":"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1244,"value":"import { llm, LLMWidget } from \"@kasivisu4/llm-library\"\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":311,"value":"db_schema = FileAttachment(\"credit_schema.txt\").text()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":697,"value":"// CELL — normalizeSql\nnormalizeSql = (sql = \"\") =>\n sql.replace(/--.*$/gm, \"\").replace(/\\s+/g, \" \").replace(/\\s*,\\s*/g, \", \").trim().toLowerCase()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1350,"value":"Versions:\n- [v0](https://observablehq.com/@kasivisu4/txt2sql-generation@1122) - 20/4\n- [v1](https://observablehq.com/@kasivisu4/txt2sql-generation@1240) - 23/4 - added exact match","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1361,"value":"viewof embedConfig = LLMWidget({\"mode\":\"embed\", \"showPrompt\":false})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1245,"value":"viewof llm_config = LLMWidget({showPrompt:false, showSystemPrompt:false, showParams:true})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1496,"value":"evalMetrics = ({\n \"Exact Match\": (genSql, gtSql, genRows, gtRows) =>\n exact_match(genSql, gtSql, genRows, gtRows),\n\n \"qas\": make_qas({ embedConfig, llm, w: 0.8 }),\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1485,"value":"$37","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1594,"value":"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":17,"value":"import {visualizeSqlite} from \"@john-guerra/visualize-sqlite\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1071,"value":"txt2sqlGeneration_KasiViswanath_Observable = FileAttachment(\"Txt2SQL Generation _ Kasi Viswanath _ Observable.pdf\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1376,"value":"import {make_qas} from \"@kasivisu4/qas\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W38","stdlib":"1","titling":"auto"}} 32:"$31:body"