dkAmulet/sql-query-optimizer
0
1<!DOCTYPE html>2<html lang="en">3<head>4<meta charset="UTF-8" />5<meta name="viewport" content="width=device-width, initial-scale=1.0" />6<title>SQL Query Optimizer — OpenEnv</title>7<style>8 :root {9 --bg: #0f1117;10 --surface: #1a1d27;11 --border: #2a2d3e;12 --accent: #4f8ef7;13 --accent2: #7c5cf7;14 --green: #22c55e;15 --yellow: #eab308;16 --red: #ef4444;17 --text: #e2e8f0;18 --muted: #64748b;19 --radius: 10px;20 --mono: 'JetBrains Mono', 'Fira Code', monospace;21 }22 * { box-sizing: border-box; margin: 0; padding: 0; }23 body { background: var(--bg); color: var(--text); font-family: system-ui, sans-serif; min-height: 100vh; }24 25 /* ── Header ── */26 header {27 background: linear-gradient(135deg, #1a1d27 0%, #0f1117 100%);28 border-bottom: 1px solid var(--border);29 padding: 20px 32px;30 display: flex; align-items: center; justify-content: space-between;31 }32 .logo { display: flex; align-items: center; gap: 12px; }33 .logo-icon { font-size: 28px; }34 .logo h1 { font-size: 20px; font-weight: 700; background: linear-gradient(90deg, var(--accent), var(--accent2)); -webkit-background-clip: text; -webkit-text-fill-color: transparent; }35 .logo p { font-size: 12px; color: var(--muted); }36 .badges { display: flex; gap: 8px; }37 .badge { padding: 4px 10px; border-radius: 20px; font-size: 11px; font-weight: 600; letter-spacing: .5px; }38 .badge-blue { background: rgba(79,142,247,.15); color: var(--accent); border: 1px solid rgba(79,142,247,.3); }39 .badge-green { background: rgba(34,197,94,.15); color: var(--green); border: 1px solid rgba(34,197,94,.3); }40 41 /* ── Layout ── */42 .main { display: grid; grid-template-columns: 340px 1fr; gap: 0; height: calc(100vh - 73px); }43 44 /* ── Sidebar ── */45 .sidebar {46 background: var(--surface);47 border-right: 1px solid var(--border);48 overflow-y: auto;49 padding: 20px;50 display: flex; flex-direction: column; gap: 20px;51 }52 .section-label { font-size: 11px; font-weight: 700; letter-spacing: 1px; color: var(--muted); text-transform: uppercase; margin-bottom: 8px; }53 54 /* Task cards */55 .task-card {56 background: var(--bg);57 border: 1.5px solid var(--border);58 border-radius: var(--radius);59 padding: 14px;60 cursor: pointer;61 transition: all .15s;62 }63 .task-card:hover { border-color: var(--accent); }64 .task-card.active { border-color: var(--accent); background: rgba(79,142,247,.05); }65 .task-card-top { display: flex; justify-content: space-between; align-items: flex-start; margin-bottom: 6px; }66 .task-name { font-size: 13px; font-weight: 600; }67 .diff-badge { padding: 2px 8px; border-radius: 20px; font-size: 10px; font-weight: 700; }68 .diff-easy { background: rgba(34,197,94,.15); color: var(--green); }69 .diff-medium { background: rgba(234,179,8,.15); color: var(--yellow); }70 .diff-hard { background: rgba(239,68,68,.15); color: var(--red); }71 .task-desc { font-size: 11px; color: var(--muted); line-height: 1.5; }72 73 /* Schema */74 .schema-box {75 background: var(--bg);76 border: 1px solid var(--border);77 border-radius: var(--radius);78 padding: 12px;79 font-family: var(--mono);80 font-size: 10.5px;81 color: #94a3b8;82 white-space: pre;83 overflow-x: auto;84 max-height: 220px;85 overflow-y: auto;86 line-height: 1.5;87 }88 89 /* ── Main panel ── */90 .panel { display: flex; flex-direction: column; overflow: hidden; }91 92 .top-row {93 padding: 16px 24px;94 border-bottom: 1px solid var(--border);95 display: flex; gap: 16px; align-items: flex-start;96 }97 .query-col { flex: 1; display: flex; flex-direction: column; gap: 6px; }98 .query-label { font-size: 11px; font-weight: 700; letter-spacing: 1px; color: var(--muted); text-transform: uppercase; }99 .slow-query {100 background: var(--bg);101 border: 1px solid var(--border);102 border-radius: 8px;103 padding: 12px;104 font-family: var(--mono);105 font-size: 12px;106 color: #f87171;107 white-space: pre;108 line-height: 1.6;109 }110 111 /* Editor */112 .editor-area { flex: 1; display: flex; flex-direction: column; overflow: hidden; padding: 16px 24px; gap: 12px; }113 .editor-header { display: flex; justify-content: space-between; align-items: center; }114 .editor-label { font-size: 11px; font-weight: 700; letter-spacing: 1px; color: var(--muted); text-transform: uppercase; }115 .editor-actions { display: flex; gap: 8px; }116 117 textarea#sql-input {118 flex: 1;119 background: var(--bg);120 border: 1.5px solid var(--border);121 border-radius: var(--radius);122 color: var(--text);123 font-family: var(--mono);124 font-size: 13px;125 padding: 14px;126 resize: none;127 line-height: 1.7;128 outline: none;129 transition: border-color .15s;130 min-height: 160px;131 }132 textarea#sql-input:focus { border-color: var(--accent); }133 134 /* Buttons */135 .btn {136 padding: 8px 18px;137 border-radius: 8px;138 font-size: 13px;139 font-weight: 600;140 cursor: pointer;141 border: none;142 transition: all .15s;143 display: flex; align-items: center; gap: 6px;144 }145 .btn-primary { background: var(--accent); color: #fff; }146 .btn-primary:hover { background: #3b7fea; }147 .btn-primary:disabled { opacity: .4; cursor: not-allowed; }148 .btn-ghost { background: transparent; color: var(--muted); border: 1px solid var(--border); }149 .btn-ghost:hover { color: var(--text); border-color: var(--text); }150 .btn-sm { padding: 5px 12px; font-size: 12px; }151 152 /* Results */153 .results-area {154 border-top: 1px solid var(--border);155 padding: 16px 24px;156 display: flex; flex-direction: column; gap: 12px;157 background: rgba(0,0,0,.2);158 min-height: 180px;159 }160 161 .score-row { display: flex; align-items: center; gap: 16px; flex-wrap: wrap; }162 .total-score {163 font-size: 40px;164 font-weight: 800;165 font-family: var(--mono);166 background: linear-gradient(90deg, var(--accent), var(--accent2));167 -webkit-background-clip: text; -webkit-text-fill-color: transparent;168 }169 .score-label { font-size: 11px; color: var(--muted); margin-top: 4px; }170 171 .breakdown { display: flex; gap: 12px; flex-wrap: wrap; flex: 1; }172 .dim {173 background: var(--surface);174 border: 1px solid var(--border);175 border-radius: 8px;176 padding: 10px 14px;177 min-width: 100px;178 }179 .dim-name { font-size: 10px; color: var(--muted); text-transform: uppercase; letter-spacing: .5px; margin-bottom: 6px; }180 .dim-bar-bg { height: 4px; background: var(--border); border-radius: 2px; overflow: hidden; }181 .dim-bar { height: 100%; border-radius: 2px; transition: width .4s ease; }182 .dim-val { font-size: 15px; font-weight: 700; font-family: var(--mono); margin-top: 4px; }183 184 .feedback-box {185 background: var(--bg);186 border: 1px solid var(--border);187 border-radius: 8px;188 padding: 10px 14px;189 font-size: 12px;190 color: var(--muted);191 line-height: 1.6;192 }193 .feedback-box.has-content { color: var(--text); }194 195 /* Step progress */196 .progress-bar { display: flex; gap: 4px; }197 .step-pip { width: 20px; height: 6px; border-radius: 3px; background: var(--border); transition: background .3s; }198 .step-pip.done { background: var(--accent); }199 .step-pip.current { background: var(--accent2); }200 201 /* History */202 .history-list { max-height: 80px; overflow-y: auto; display: flex; flex-direction: column; gap: 3px; }203 .history-item { font-size: 11px; font-family: var(--mono); color: var(--muted); display: flex; gap: 8px; }204 .history-item .h-step { color: var(--accent); min-width: 44px; }205 .history-item .h-score { font-weight: 600; }206 .history-item .h-score.good { color: var(--green); }207 .history-item .h-score.mid { color: var(--yellow); }208 .history-item .h-score.bad { color: var(--red); }209 210 /* Placeholder */211 .placeholder { display: flex; flex-direction: column; align-items: center; justify-content: center; height: 100%; gap: 8px; color: var(--muted); font-size: 13px; }212 .placeholder .icon { font-size: 32px; opacity: .4; }213 214 /* Loader */215 .spinner { width: 16px; height: 16px; border: 2px solid rgba(255,255,255,.2); border-top-color: #fff; border-radius: 50%; animation: spin .6s linear infinite; }216 @keyframes spin { to { transform: rotate(360deg); } }217 218 /* Scrollbars */219 ::-webkit-scrollbar { width: 5px; height: 5px; }220 ::-webkit-scrollbar-track { background: transparent; }221 ::-webkit-scrollbar-thumb { background: var(--border); border-radius: 3px; }222</style>223</head>224<body>225 226<header>227 <div class="logo">228 <div class="logo-icon">🗄️</div>229 <div>230 <h1>SQL Query Optimizer</h1>231 <p>OpenEnv · RL Environment for SQL Rewriting</p>232 </div>233 </div>234 <div class="badges">235 <span class="badge badge-blue">OpenEnv v1.0</span>236 <span class="badge badge-green">Live</span>237 </div>238</header>239 240<div class="main">241 <!-- ── Sidebar ── -->242 <aside class="sidebar">243 <div>244 <div class="section-label">Tasks</div>245 <div id="task-list" style="display:flex;flex-direction:column;gap:8px;"></div>246 </div>247 248 <div>249 <div class="section-label">Episode Progress</div>250 <div style="display:flex;justify-content:space-between;align-items:center;margin-bottom:6px;">251 <span id="step-counter" style="font-size:12px;color:var(--muted)">Step 0 / 0</span>252 <span id="best-reward-label" style="font-size:12px;font-family:var(--mono);color:var(--accent)">Best: —</span>253 </div>254 <div class="progress-bar" id="progress-bar"></div>255 </div>256 257 <div>258 <div class="section-label">Step History</div>259 <div class="history-list" id="history-list">260 <div style="font-size:11px;color:var(--muted)">No steps yet.</div>261 </div>262 </div>263 264 <div>265 <div class="section-label">Database Schema</div>266 <div class="schema-box" id="schema-box">Loading…</div>267 </div>268 </aside>269 270 <!-- ── Main Panel ── -->271 <div class="panel">272 <!-- Slow query -->273 <div class="top-row">274 <div class="query-col">275 <div class="query-label">⚠ Slow Query (to optimise)</div>276 <div class="slow-query" id="slow-query-box">Select a task →</div>277 </div>278 </div>279 280 <!-- Editor -->281 <div class="editor-area">282 <div class="editor-header">283 <div class="editor-label">✏ Your Optimised Query</div>284 <div class="editor-actions">285 <button class="btn btn-ghost btn-sm" onclick="clearEditor()">Clear</button>286 <button class="btn btn-ghost btn-sm" onclick="pasteSlowQuery()">Paste slow query</button>287 <button class="btn btn-primary" id="submit-btn" onclick="submitQuery()" disabled>288 <span id="submit-label">Submit</span>289 </button>290 </div>291 </div>292 <textarea id="sql-input" spellcheck="false"293 placeholder="-- Write your optimised SQL here…294-- Tip: select a task on the left first, then click Submit to grade it."></textarea>295 </div>296 297 <!-- Results -->298 <div class="results-area">299 <div id="results-placeholder" class="placeholder">300 <div class="icon">📊</div>301 <div>Submit a query to see your score</div>302 </div>303 <div id="results-content" style="display:none;flex-direction:column;gap:10px;">304 <div class="score-row">305 <div>306 <div class="total-score" id="total-score">0.000</div>307 <div class="score-label">Total Score</div>308 </div>309 <div class="breakdown">310 <div class="dim">311 <div class="dim-name">Validity</div>312 <div class="dim-bar-bg"><div class="dim-bar" id="bar-validity" style="width:0%;background:#4f8ef7;"></div></div>313 <div class="dim-val" id="val-validity">0.00</div>314 </div>315 <div class="dim">316 <div class="dim-name">Correctness</div>317 <div class="dim-bar-bg"><div class="dim-bar" id="bar-correctness" style="width:0%;background:#7c5cf7;"></div></div>318 <div class="dim-val" id="val-correctness">0.00</div>319 </div>320 <div class="dim">321 <div class="dim-name">Performance</div>322 <div class="dim-bar-bg"><div class="dim-bar" id="bar-performance" style="width:0%;background:#22c55e;"></div></div>323 <div class="dim-val" id="val-performance">0.00</div>324 </div>325 <div class="dim">326 <div class="dim-name">Style</div>327 <div class="dim-bar-bg"><div class="dim-bar" id="bar-style" style="width:0%;background:#eab308;"></div></div>328 <div class="dim-val" id="val-style">0.00</div>329 </div>330 </div>331 </div>332 <div class="feedback-box" id="feedback-box">—</div>333 </div>334 </div>335 </div>336</div>337 338<script>339const API = ''; // same origin340let currentTask = null;341let currentSlowQ = '';342let stepNum = 0;343let maxSteps = 0;344let bestReward = 0;345let history = [];346 347const TASK_HINTS = {348 select_star_removal: 'SELECT user_id, username, email\nFROM users\nWHERE is_active = 1',349 subquery_to_join: 'SELECT o.order_id, o.user_id, o.total_amount\nFROM orders o\nJOIN users u ON u.user_id = o.user_id\nWHERE u.country = \'USA\'\n AND u.is_active = 1\n AND o.status = \'delivered\'',350 aggregation_optimization: 'SELECT c.name AS category_name,\n SUM(oi.quantity * oi.unit_price) AS total_revenue\nFROM categories c\nJOIN products p ON p.category_id = c.category_id\nJOIN order_items oi ON oi.product_id = p.product_id\nGROUP BY c.category_id, c.name\nHAVING SUM(oi.quantity * oi.unit_price) > 1000\nORDER BY total_revenue DESC',351};352 353// ── Boot ──────────────────────────────────────────────────────────────────────354async function init() {355 const res = await fetch(`${API}/tasks`);356 const tasks = await res.json();357 renderTaskList(tasks);358}359 360function renderTaskList(tasks) {361 const el = document.getElementById('task-list');362 el.innerHTML = '';363 tasks.forEach(t => {364 const card = document.createElement('div');365 card.className = 'task-card';366 card.id = `task-card-${t.task_id}`;367 card.innerHTML = `368 <div class="task-card-top">369 <div class="task-name">${t.name}</div>370 <span class="diff-badge diff-${t.difficulty}">${t.difficulty}</span>371 </div>372 <div class="task-desc">${t.description.substring(0,120)}…</div>`;373 card.onclick = () => selectTask(t);374 el.appendChild(card);375 });376}377 378// ── Task selection ─────────────────────────────────────────────────────────────379async function selectTask(t) {380 // Highlight card381 document.querySelectorAll('.task-card').forEach(c => c.classList.remove('active'));382 document.getElementById(`task-card-${t.task_id}`).classList.add('active');383 384 // Reset state385 history = [];386 bestReward = 0;387 renderHistory();388 hideResults();389 390 // Call /reset391 const res = await fetch(`${API}/reset?task_id=${t.task_id}`, { method: 'POST' });392 const obs = await res.json();393 394 currentTask = t.task_id;395 currentSlowQ = obs.slow_query;396 stepNum = 0;397 maxSteps = obs.max_steps;398 399 document.getElementById('slow-query-box').textContent = obs.slow_query;400 document.getElementById('schema-box').textContent = obs.schema_ddl;401 document.getElementById('sql-input').value = '';402 document.getElementById('sql-input').placeholder = `-- Write your optimised SQL for "${t.name}"…`;403 document.getElementById('submit-btn').disabled = false;404 405 updateProgress();406 document.getElementById('best-reward-label').textContent = 'Best: 0.000';407}408 409// ── Submit ─────────────────────────────────────────────────────────────────────410async function submitQuery() {411 const q = document.getElementById('sql-input').value.trim();412 if (!q || !currentTask) return;413 414 const btn = document.getElementById('submit-btn');415 const label = document.getElementById('submit-label');416 btn.disabled = true;417 label.innerHTML = '<div class="spinner"></div>';418 419 try {420 const res = await fetch(`${API}/step`, {421 method: 'POST',422 headers: { 'Content-Type': 'application/json' },423 body: JSON.stringify({ optimized_query: q }),424 });425 const result = await res.json();426 427 if (res.status === 400) {428 alert(result.detail || 'Error — reset the task and try again.');429 return;430 }431 432 stepNum++;433 const r = result.reward;434 const bd = r.breakdown;435 436 // Update best437 if (r.value > bestReward) bestReward = r.value;438 439 // History440 history.push({ step: stepNum, score: r.value, feedback: r.feedback });441 renderHistory();442 443 // Results444 showResults(r.value, bd, r.feedback);445 updateProgress();446 447 document.getElementById('best-reward-label').textContent =448 `Best: ${bestReward.toFixed(3)}`;449 450 if (result.done) {451 btn.disabled = true;452 label.textContent = 'Episode done';453 showToast(r.value >= 0.95 ? '🎉 Perfect score!' : `Episode complete — best ${bestReward.toFixed(3)}`);454 }455 } finally {456 if (!document.getElementById('submit-btn').disabled) {457 label.textContent = 'Submit';458 btn.disabled = false;459 } else if (label.textContent !== 'Episode done') {460 label.textContent = 'Submit';461 btn.disabled = false;462 }463 }464}465 466// ── UI helpers ────────────────────────────────────────────────────────────────467function showResults(total, bd, feedback) {468 document.getElementById('results-placeholder').style.display = 'none';469 const rc = document.getElementById('results-content');470 rc.style.display = 'flex';471 472 document.getElementById('total-score').textContent = total.toFixed(3);473 474 const dims = { validity: bd.validity, correctness: bd.correctness, performance: bd.performance, style: bd.style };475 // Max values per dim (approximate — for bar scaling)476 const maxes = { validity: 0.10, correctness: 0.40, performance: 0.30, style: 0.30 };477 for (const [k, v] of Object.entries(dims)) {478 document.getElementById(`val-${k}`).textContent = v.toFixed(2);479 const pct = Math.round((v / maxes[k]) * 100);480 document.getElementById(`bar-${k}`).style.width = `${Math.min(pct, 100)}%`;481 }482 483 const fb = document.getElementById('feedback-box');484 fb.textContent = feedback || '—';485 fb.className = `feedback-box ${feedback ? 'has-content' : ''}`;486}487 488function hideResults() {489 document.getElementById('results-placeholder').style.display = 'flex';490 document.getElementById('results-content').style.display = 'none';491}492 493function updateProgress() {494 document.getElementById('step-counter').textContent = `Step ${stepNum} / ${maxSteps}`;495 const bar = document.getElementById('progress-bar');496 bar.innerHTML = '';497 for (let i = 0; i < maxSteps; i++) {498 const pip = document.createElement('div');499 pip.className = 'step-pip' + (i < stepNum ? ' done' : i === stepNum ? ' current' : '');500 bar.appendChild(pip);501 }502}503 504function renderHistory() {505 const el = document.getElementById('history-list');506 if (!history.length) {507 el.innerHTML = '<div style="font-size:11px;color:var(--muted)">No steps yet.</div>';508 return;509 }510 el.innerHTML = [...history].reverse().map(h => {511 const cls = h.score >= 0.80 ? 'good' : h.score >= 0.45 ? 'mid' : 'bad';512 const short = h.feedback ? h.feedback.substring(0, 60) + (h.feedback.length > 60 ? '…' : '') : '';513 return `<div class="history-item">514 <span class="h-step">Step ${h.step}</span>515 <span class="h-score ${cls}">${h.score.toFixed(3)}</span>516 <span style="color:var(--muted);font-size:10px">${short}</span>517 </div>`;518 }).join('');519}520 521function clearEditor() {522 document.getElementById('sql-input').value = '';523 document.getElementById('sql-input').focus();524}525 526function pasteSlowQuery() {527 document.getElementById('sql-input').value = currentSlowQ || '';528 document.getElementById('sql-input').focus();529}530 531function showToast(msg) {532 const t = document.createElement('div');533 t.textContent = msg;534 Object.assign(t.style, {535 position: 'fixed', bottom: '24px', right: '24px',536 background: 'var(--surface)', border: '1px solid var(--border)',537 borderRadius: '10px', padding: '12px 20px', fontSize: '13px',538 boxShadow: '0 8px 24px rgba(0,0,0,.4)', zIndex: 999,539 animation: 'fadeIn .2s ease',540 color: 'var(--text)',541 });542 document.body.appendChild(t);543 setTimeout(() => t.remove(), 3500);544}545 546// Submit on Ctrl/Cmd+Enter547document.addEventListener('keydown', e => {548 if ((e.ctrlKey || e.metaKey) && e.key === 'Enter') submitQuery();549});550 551init();552</script>553</body>554</html>555