Blame for index.html

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>OraShift</title>
<meta name="description" content="Oracle SQL to PostgreSQL with a QLoRA fine-tuned LLM, judged by execution.">
<style>

:root {

  --bg: #f7f7f5; --surface: #ffffff; --surface-2: #f0efeb; --text: #1c1c1a; --muted: #62615c;

  --border: #dddcd6; --accent: #2f5bd3; --accent-text: #ffffff;

  --ok-bg: #e5f3ea; --ok-text: #1d6b3a; --warn-bg: #fdf1dc; --warn-text: #8a5300;

  --info-bg: #e8eefc; --info-text: #23439e;

  --mono: ui-monospace, SFMono-Regular, Menlo, Consolas, "Liberation Mono", monospace;

  --sans: system-ui, -apple-system, "Segoe UI", Roboto, sans-serif;

}

@media (prefers-color-scheme: dark) {

  :root {

    --bg: #151514; --surface: #1e1e1c; --surface-2: #272724; --text: #ecebe6; --muted: #a3a29b;

    --border: #3a3a36; --accent: #7d9cf0; --accent-text: #10131c;

    --ok-bg: #173323; --ok-text: #8fdcaa; --warn-bg: #3a2a10; --warn-text: #f3c477;

    --info-bg: #1c2645; --info-text: #a9bdf5;

  }

}

* { box-sizing: border-box; }

body { margin: 0; background: var(--bg); color: var(--text); font: 16px/1.55 var(--sans); }

main { max-width: 1040px; margin: 0 auto; padding: 32px 16px 64px; }

a { color: var(--accent); }

h1 { font-size: 2rem; margin: 0 0 4px; letter-spacing: -0.02em; }

h2 { font-size: 1.25rem; margin: 40px 0 12px; }

h3 { font-size: 1rem; margin: 0 0 8px; }

p { margin: 0 0 12px; }

.lede { color: var(--muted); font-size: 1.05rem; max-width: 720px; }

.links { display: flex; flex-wrap: wrap; gap: 8px; margin: 16px 0 0; }

.links a { text-decoration: none; border: 1px solid var(--border); background: var(--surface);

  padding: 6px 12px; border-radius: 999px; font-size: 0.9rem; color: var(--text); }

.links a:hover { border-color: var(--accent); }

.stats { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 12px; margin-top: 28px; }

.stat { background: var(--surface); border: 1px solid var(--border); border-radius: 10px; padding: 14px 16px; }

.stat .v { font-size: 1.7rem; font-weight: 650; font-variant-numeric: tabular-nums; }

.stat .l { color: var(--muted); font-size: 0.88rem; }

.stat.hi { border-color: var(--accent); }

.card { background: var(--surface); border: 1px solid var(--border); border-radius: 10px; padding: 18px; }

.tabs { display: flex; gap: 4px; border-bottom: 1px solid var(--border); margin: 40px 0 16px; }

.tabs button { background: none; border: 0; border-bottom: 2px solid transparent; color: var(--muted);

  font: inherit; font-weight: 600; padding: 8px 14px; cursor: pointer; }

.tabs button[aria-selected="true"] { color: var(--text); border-bottom-color: var(--accent); }

textarea, select, input[type=search] { font: inherit; color: var(--text); background: var(--surface);

  border: 1px solid var(--border); border-radius: 8px; padding: 8px 10px; }

textarea { width: 100%; min-height: 150px; font-family: var(--mono); font-size: 0.9rem; resize: vertical; }

.btn { background: var(--accent); color: var(--accent-text); border: 0; border-radius: 8px;

  padding: 9px 18px; font: inherit; font-weight: 600; cursor: pointer; }

.btn:disabled { opacity: 0.55; cursor: wait; }

.chips { display: flex; flex-wrap: wrap; gap: 6px; margin: 10px 0 14px; }

.chip { background: var(--surface-2); border: 1px solid var(--border); color: var(--text); border-radius: 999px;

  padding: 3px 10px; font-size: 0.82rem; cursor: pointer; font-family: var(--mono); }

.row { display: flex; gap: 10px; align-items: center; flex-wrap: wrap; }

pre { margin: 0; background: var(--surface-2); border: 1px solid var(--border); border-radius: 8px;

  padding: 10px 12px; font: 0.85rem/1.5 var(--mono); white-space: pre-wrap; word-break: break-word; }

.label { font-size: 0.78rem; text-transform: uppercase; letter-spacing: 0.05em; color: var(--muted);

  margin: 14px 0 4px; font-weight: 600; }

.badge { display: inline-block; border-radius: 6px; padding: 2px 8px; font-size: 0.82rem; font-weight: 600; }

.b-ok { background: var(--ok-bg); color: var(--ok-text); }

.b-warn { background: var(--warn-bg); color: var(--warn-text); }

.b-info { background: var(--info-bg); color: var(--info-text); }

.note { color: var(--muted); font-size: 0.9rem; margin-top: 8px; }

.status { color: var(--muted); font-size: 0.88rem; }

.table-wrap { overflow-x: auto; }

table { border-collapse: collapse; width: 100%; font-size: 0.92rem; font-variant-numeric: tabular-nums; }

th, td { text-align: left; padding: 8px 10px; border-bottom: 1px solid var(--border); white-space: nowrap; }

th { color: var(--muted); font-weight: 600; font-size: 0.82rem; }

td.num, th.num { text-align: right; }

.grid2 { display: grid; grid-template-columns: 1fr 1fr; gap: 12px; }

@media (max-width: 720px) { .grid2 { grid-template-columns: 1fr; } h1 { font-size: 1.6rem; } }

.unit { margin-bottom: 14px; }

.unit .meta { display: flex; gap: 8px; flex-wrap: wrap; font-size: 0.82rem; color: var(--muted); }

.filters { display: flex; gap: 8px; flex-wrap: wrap; margin-bottom: 12px; }

.filters input[type=search] { flex: 1 1 220px; }

.hidden { display: none; }

ul.lim { padding-left: 20px; margin: 0; }

ul.lim li { margin-bottom: 6px; }

code { font-family: var(--mono); font-size: 0.88em; background: var(--surface-2); padding: 1px 5px; border-radius: 4px; }

</style>
</head>
<body>
<main>
  <header>
    <h1>OraShift</h1>
    <p class="lede">Oracle SQL to PostgreSQL with a QLoRA fine-tune of Qwen2.5-Coder-3B, and every
      translation judged by <strong>executing it</strong> on both databases. A translation counts only
      if it runs on PostgreSQL and returns what the original returns on Oracle.</p>
    <nav class="links">
      <a href="https://github.com/devansh173/orashift" target="_blank" rel="noopener">Code &amp; write-up on GitHub</a>
      <a href="https://huggingface.co/devansh173/orashift-qlora-adapter" target="_blank" rel="noopener">Model adapter</a>
      <a href="https://huggingface.co/datasets/devansh173/orashift-pairs" target="_blank" rel="noopener">Dataset</a>
    </nav>
  </header>

  <section class="stats" id="stats" aria-label="Execution accuracy"></section>
  <p class="note">Execution accuracy over 330 held-out test units. Hybrid keeps sqlglot's output when it
    verifies against Oracle and uses the fine-tuned model otherwise.</p>

  <div class="tabs" role="tablist">
    <button role="tab" aria-selected="true" data-tab="translate">Translate</button>
    <button role="tab" aria-selected="false" data-tab="explore">Explore the test set</button>
    <button role="tab" aria-selected="false" data-tab="results">Results</button>
  </div>

  <section id="tab-translate">
    <div class="card">
      <h3>Paste Oracle SQL</h3>
      <p class="note" style="margin-top:0">The rules run live in your browser. This free demo does not run the
        3B model; for test-set statements it shows the model's real saved output.</p>
      <div class="chips" id="examples"></div>
      <textarea id="input" spellcheck="false" aria-label="Oracle SQL"></textarea>
      <div class="row" style="margin-top:10px">
        <button class="btn" id="go" disabled>Translate</button>
        <span class="status" id="status">Loading Python in your browser…</span>
      </div>
      <div id="out" class="hidden"></div>
    </div>
  </section>

  <section id="tab-explore" class="hidden">
    <p class="note" style="margin-top:0">All 330 held-out units with the base and fine-tuned models' actual
      outputs. Pass or fail was decided by execution, so text that differs from the reference can still be
      correct. Per-construct accuracy is shown for the selected construct.</p>
    <div class="filters">
      <select id="f-split" aria-label="Split"></select>
      <select id="f-cat" aria-label="Construct"></select>
      <input type="search" id="f-q" placeholder="Search SQL…" aria-label="Search SQL">
    </div>
    <div id="cat-acc" class="note"></div>
    <div id="units"></div>
    <button class="btn hidden" id="more" style="background:var(--surface-2);color:var(--text)">Show more</button>
  </section>

  <section id="tab-results" class="hidden">
    <h3>By generalisation axis</h3>
    <p class="note" style="margin-top:0">Two things are held out: the <code>logistics</code> schema, and 25 of
      123 templates. The fine-tune generalises to unseen table names, not to unseen constructs.</p>
    <div class="card table-wrap"><table id="t-split"></table></div>
    <h2>By construct</h2>
    <div class="card table-wrap"><table id="t-cat"></table></div>
    <h2>Limitations</h2>
    <ul class="lim">
      <li>On an unseen construct variant the fine-tune is no better than the base model, and on
        <code>SYSDATE</code> it is far worse (2/26 against 23/26) because it echoes unfamiliar input unchanged.</li>
      <li>The data is synthetic and template-generated, so it is more uniform than real-world SQL.</li>
      <li>This page cannot execute anything. Every accuracy figure comes from the offline evaluation.</li>
    </ul>
  </section>
</main>

<script src="https://cdn.jsdelivr.net/pyodide/v0.27.7/full/pyodide.js"></script>
<script>

const $ = (id) => document.getElementById(id);

const pct = (c) => c && c.total ? `${Math.round(c.rate * 100)}%` : "n/a";

const frac = (c) => c && c.total ? `${c.verified}/${c.total} (${pct(c)})` : "n/a";

const norm = (s) => (s || "").trim().replace(/;\s*$/, "").replace(/\s+/g, " ").toLowerCase();

const el = (tag, attrs = {}, ...kids) => {

  const n = document.createElement(tag);

  for (const [k, v] of Object.entries(attrs)) k === "class" ? (n.className = v) : n.setAttribute(k, v);

  for (const k of kids) if (k != null) n.append(k);

  return n;

};

const block = (label, sql) => [el("div", { class: "label" }, label), el("pre", {}, sql || "(empty)")];



let DATA, py, byOracle = new Map();



// Tabs

document.querySelectorAll(".tabs button").forEach((b) => b.addEventListener("click", () => {

  document.querySelectorAll(".tabs button").forEach((x) => x.setAttribute("aria-selected", x === b));

  for (const t of ["translate", "explore", "results"]) $("tab-" + t).classList.toggle("hidden", t !== b.dataset.tab);

}));



function renderStats(m) {

  const a = m.execution_accuracy;

  const items = [["Hybrid", a.hybrid, true], ["Fine-tuned", a.finetuned], ["Base model", a.base], ["sqlglot rules", a.sqlglot]];

  for (const [name, c, hi] of items)

    $("stats").append(el("div", { class: "stat" + (hi ? " hi" : "") },

      el("div", { class: "v" }, pct(c)), el("div", { class: "l" }, `${name} · ${c.verified}/${c.total}`)));

}



function renderTables(m) {

  const cols = [["base", "Base"], ["finetuned", "Fine-tuned"], ["sqlglot", "sqlglot"], ["hybrid", "Hybrid"]];

  const head = (first) => el("tr", {}, el("th", {}, first), ...cols.map(([, n]) => el("th", { class: "num" }, n)));

  $("t-split").append(head("Split"));

  for (const [k, label] of Object.entries(m.split_labels))

    $("t-split").append(el("tr", {}, el("td", {}, label), ...cols.map(([c]) => el("td", { class: "num" }, frac(m.by_split[k][c])))));

  $("t-cat").append(head("Construct"));

  for (const [k, v] of Object.entries(m.by_category))

    $("t-cat").append(el("tr", {}, el("td", {}, el("code", {}, k)), ...cols.map(([c]) => el("td", { class: "num" }, frac(v[c])))));

}



// Explore

let shown = 20;

function filtered() {

  const s = $("f-split").value, c = $("f-cat").value, q = $("f-q").value.toLowerCase();

  return DATA.units.filter((u) => (!s || u.split === s) && (!c || u.category === c) &&

    (!q || (u.oracle + " " + u.reference).toLowerCase().includes(q)));

}

function renderUnits() {

  const list = filtered();

  const box = $("units"); box.replaceChildren();

  const c = $("f-cat").value;

  $("cat-acc").textContent = c

    ? `${c}: fine-tuned ${frac(DATA.metrics.by_category[c].finetuned)}, base ${frac(DATA.metrics.by_category[c].base)} by execution.`

    : `${list.length} units.`;

  for (const u of list.slice(0, shown)) {

    box.append(el("div", { class: "card unit" },

      el("div", { class: "meta" }, el("span", { class: "badge b-info" }, u.category),

        el("span", {}, DATA.metrics.split_labels[u.split]), el("span", {}, "schema: " + u.schema)),

      ...block("Oracle input", u.oracle),

      el("div", { class: "grid2" },

        el("div", {}, ...block("Fine-tuned output", u.finetuned)),

        el("div", {}, ...block("Base model output", u.base))),

      ...block("Hand-written reference (execution-verified)", u.reference)));

  }

  $("more").classList.toggle("hidden", list.length <= shown);

}

function setupExplore() {

  const splits = [["", "All splits"], ...Object.entries(DATA.metrics.split_labels)];

  for (const [v, l] of splits) $("f-split").append(el("option", { value: v }, l));

  const cats = [...new Set(DATA.units.map((u) => u.category))].sort();

  $("f-cat").append(el("option", { value: "" }, "All constructs"));

  for (const c of cats) $("f-cat").append(el("option", { value: c }, c));

  for (const id of ["f-split", "f-cat", "f-q"]) $(id).addEventListener("input", () => { shown = 20; renderUnits(); });

  $("more").addEventListener("click", () => { shown += 20; renderUnits(); });

  renderUnits();

}



// Translate

function setupExamples() {

  const want = ["rownum", "connect_by", "add_months", "trunc_date", "nvl", "decode", "outer_join_plus", "merge"];

  for (const cat of want) {

    const u = DATA.units.find((x) => x.category === cat && x.split === "test_unseen_schema") || DATA.units.find((x) => x.category === cat);

    if (!u) continue;

    const b = el("button", { class: "chip", type: "button" }, cat);

    b.addEventListener("click", () => { $("input").value = u.oracle; });

    $("examples").append(b);

  }

  $("input").value = DATA.units.find((x) => x.category === "rownum").oracle;

}



function translate() {

  const sql = $("input").value;

  const out = $("out"); out.replaceChildren(); out.classList.remove("hidden");

  if (!sql.trim()) return;

  const [rules, isms] = JSON.parse(py.globals.get("run")(sql));

  const saved = byOracle.get(norm(sql));

  const parts = [];

  if (!isms.length && rules) {

    parts.push(el("div", { class: "label" }, "Path"), el("span", { class: "badge b-ok" }, "sqlglot rules"),

      el("p", { class: "note" }, "No Oracle-specific constructs found, so the rules answer. sqlglot scores 100% on constructs like NVL, DECODE and TO_CHAR."),

      ...block("PostgreSQL", rules));

  } else if (saved) {

    parts.push(el("div", { class: "label" }, "Path"), el("span", { class: "badge b-info" }, "fine-tuned model (saved test-set output)"),

      el("p", { class: "note" }, `Contains ${isms.join(", ")}, which the rules cannot handle. This exact statement is in the held-out test set (${DATA.metrics.split_labels[saved.split]}), so this is the model's real output from the evaluation run.`),

      ...block("PostgreSQL (fine-tuned model)", saved.finetuned),

      el("div", { class: "grid2" },

        el("div", {}, ...block("Base model, for comparison", saved.base)),

        el("div", {}, ...block("sqlglot rules, for comparison", rules || "(could not parse)"))));

  } else {

    parts.push(el("div", { class: "label" }, "Path"), el("span", { class: "badge b-warn" }, "needs the model, not run here"),

      el("p", { class: "note" }, `Contains ${isms.join(", ") || "an unsupported construct"}, which sqlglot usually passes through unchanged. This free demo does not run the 3B model; the rules' attempt is shown below and is likely wrong. Pick an example above to see real model output, or run the adapter yourself.`),

      ...block("PostgreSQL (sqlglot, unreliable here)", rules || "(sqlglot could not parse this)"));

  }

  out.append(...parts);

}



async function boot() {

  DATA = await (await fetch("data.json")).json();

  for (const u of DATA.units) byOracle.set(norm(u.oracle), u);

  renderStats(DATA.metrics); renderTables(DATA.metrics); setupExplore(); setupExamples();

  try {

    py = await loadPyodide();

    $("status").textContent = "Installing sqlglot…";

    await py.loadPackage("micropip");

    await py.runPythonAsync('import micropip\nawait micropip.install("sqlglot==30.21.0")');

    py.FS.writeFile("/home/pyodide/translate.py", await (await fetch("translate.py")).text());

    py.runPython(`

import sys, json

sys.path.insert(0, "/home/pyodide")

from translate import needs_model, try_sqlglot

def run(sql):

    sql = sql.strip().rstrip(";")

    return json.dumps([try_sqlglot(sql), needs_model(sql)])

`);

    $("status").textContent = "Ready.";

    $("go").disabled = false;

  } catch (e) {

    $("status").textContent = "Could not load Python in this browser: " + e.message;

  }

}

$("go").addEventListener("click", translate);

boot();

</script>
</body>
</html>