Spaces:
Sleeping
Sleeping
let sessionId, | |
editor, | |
currentQuestions = [], | |
currentQuestionIndex = 0, | |
isSchemaVisible, | |
isTableVisible, | |
isHintVisible, | |
isSolutionVisible; | |
async function init() { | |
await new Promise((r) => | |
document.readyState === "complete" | |
? r() | |
: window.addEventListener("load", r) | |
); | |
const sqlEditor = document.getElementById("sqlEditor"); | |
if (!sqlEditor) throw new Error("SQL Editor element not found"); | |
await new Promise((r) => setTimeout(r, 100)); | |
if (typeof ace === "undefined") | |
throw new Error("Ace Editor library not loaded"); | |
editor = ace.edit("sqlEditor"); | |
editor.setTheme("ace/theme/monokai"); | |
editor.session.setMode("ace/mode/sql"); | |
editor.setOptions({ enableBasicAutocompletion: true, fontSize: "12px" }); | |
editor.setValue("SELECT * FROM customers;"); | |
const response = await fetch("/api/session", { method: "POST" }); | |
if (!response.ok) throw new Error("Failed to create session"); | |
sessionId = (await response.json()).session_id; | |
const domainResponse = await fetch("/api/databases"); | |
if (!domainResponse.ok) throw new Error("Failed to fetch databases"); | |
const domains = (await domainResponse.json()).databases; | |
const domainSelect = document.getElementById("domainSelect"); | |
domainSelect.innerHTML = '<option value="">Select Database</option>'; | |
if (domains.length === 0) { | |
showError("No databases available. Check server configuration."); | |
return; | |
} | |
domains.forEach((domain) => { | |
const option = document.createElement("option"); | |
option.value = domain; | |
option.textContent = domain.charAt(0).toUpperCase() + domain.slice(1); | |
domainSelect.appendChild(option); | |
}); | |
// Enable the load button after populating domains | |
document.getElementById("loadSchemaBtn").disabled = false; | |
document | |
.getElementById("loadSchemaBtn") | |
.addEventListener("click", loadDomain); | |
document | |
.getElementById("showSchemaBtn") | |
.addEventListener("click", showSchema); | |
document.getElementById("showTableBtn").addEventListener("click", showTable); | |
document | |
.getElementById("difficultySelect") | |
.addEventListener("change", loadQuestions); | |
document.getElementById("hintBtn").addEventListener("click", toggleHint); | |
document | |
.getElementById("solutionBtn") | |
.addEventListener("click", toggleSolution); | |
document.getElementById("prevBtn").addEventListener("click", prevQuestion); | |
document.getElementById("nextBtn").addEventListener("click", nextQuestion); | |
document.getElementById("runQueryBtn").addEventListener("click", runQuery); | |
} | |
async function loadDomain() { | |
const domain = document.getElementById("domainSelect").value; | |
if (!domain) { | |
showError("Please select a database."); | |
return; | |
} | |
const loadBtn = document.getElementById("loadSchemaBtn"); | |
loadBtn.disabled = true; | |
loadBtn.textContent = "Loading..."; | |
try { | |
const response = await fetch(`/api/load-schema/${domain}`, { | |
method: "POST", | |
headers: { "session-id": sessionId }, | |
}); | |
if (!response.ok) | |
throw new Error( | |
(await response.json()).detail || "Failed to load database" | |
); | |
await response.json(); | |
document.getElementById("difficultySelect").disabled = false; | |
document.getElementById("showSchemaBtn").classList.remove("hidden"); | |
document.getElementById("showTableBtn").classList.remove("hidden"); | |
document.getElementById("schemaInfo").classList.add("hidden"); | |
document.getElementById("questionDetails").innerHTML = ""; | |
document.getElementById("hintBtn").style.display = "block"; | |
document.getElementById("solutionBtn").style.display = "block"; | |
currentQuestions = []; | |
currentQuestionIndex = 0; | |
} catch (e) { | |
showError(`Failed to load database: ${e.message}.`); | |
} finally { | |
loadBtn.disabled = false; | |
loadBtn.textContent = "Load Database"; | |
} | |
} | |
async function showSchema() { | |
const domain = document.getElementById("domainSelect").value; | |
if (!domain) return; | |
const schemaInfo = document.getElementById("schemaInfo"); | |
if (isTableVisible) { | |
isTableVisible = false; | |
schemaInfo.classList.add("hidden"); | |
} | |
schemaInfo.classList.toggle("hidden"); | |
isSchemaVisible = !schemaInfo.classList.contains("hidden"); | |
if (isSchemaVisible) { | |
const schemaResponse = await fetch(`/api/schema/${domain}`, { | |
headers: { "session-id": sessionId }, | |
}); | |
if (!schemaResponse.ok) | |
throw new Error( | |
(await schemaResponse.json()).detail || "Failed to load schema" | |
); | |
const schema = (await schemaResponse.json()).schema; | |
let schemaHtml = '<div class="grid grid-cols-1 md:grid-cols-2 gap-4">'; | |
for (const [table, columns] of Object.entries(schema)) { | |
schemaHtml += `<div class="bg-gray-100 p-2 rounded"><h3 class="font-semibold">${table}</h3><ul class="list-disc ml-4">`; | |
columns.forEach( | |
(col) => (schemaHtml += `<li>${col.name} (${col.type})</li>`) | |
); | |
schemaHtml += "</ul></div>"; | |
} | |
schemaInfo.innerHTML = schemaHtml; | |
} | |
} | |
async function showTable() { | |
const domain = document.getElementById("domainSelect").value; | |
if (!domain) return; | |
const schemaInfo = document.getElementById("schemaInfo"); | |
if (isSchemaVisible) { | |
isSchemaVisible = false; | |
schemaInfo.classList.add("hidden"); | |
} | |
schemaInfo.classList.toggle("hidden"); | |
isTableVisible = !schemaInfo.classList.contains("hidden"); | |
if (isTableVisible) { | |
const sampleResponse = await fetch(`/api/sample-data/${domain}`, { | |
headers: { "session-id": sessionId }, | |
}); | |
if (!sampleResponse.ok) | |
throw new Error( | |
(await sampleResponse.json()).detail || "Failed to load sample data" | |
); | |
const sampleData = (await sampleResponse.json()).sample_data; | |
let tableHtml = '<div class="grid grid-cols-1 gap-4">'; | |
for (const [table, data] of Object.entries(sampleData)) { | |
tableHtml += `<div class="bg-gray-50 p-2 rounded"><h4 class="font-semibold">${table}</h4>`; | |
tableHtml += '<table class="w-full mt-2"><tr>'; | |
data.columns.forEach( | |
(col) => (tableHtml += `<th class="border p-1">${col}</th>`) | |
); | |
tableHtml += "</tr>"; | |
data.rows.forEach((row) => { | |
tableHtml += "<tr>"; | |
data.columns.forEach( | |
(col) => | |
(tableHtml += `<td class="border p-1">${row[col] || "NULL"}</td>`) | |
); | |
tableHtml += "</tr>"; | |
}); | |
tableHtml += "</table></div>"; | |
} | |
schemaInfo.innerHTML = tableHtml; | |
} | |
} | |
async function loadQuestions() { | |
const domain = document.getElementById("domainSelect").value; | |
const difficulty = document.getElementById("difficultySelect").value; | |
if (!domain) { | |
document.getElementById("difficultySelect").value = ""; | |
alert("Please select and load a database first"); | |
return; | |
} | |
if (!difficulty) { | |
document.getElementById("questionDetails").innerHTML = ""; | |
document.getElementById("hintBtn").style.display = "none"; | |
document.getElementById("solutionBtn").style.display = "none"; | |
document.getElementById("navButtons").style.display = "none"; | |
currentQuestions = []; | |
currentQuestionIndex = 0; | |
return; | |
} | |
const questionResponse = await fetch( | |
`/api/questions/${domain}?difficulty=${difficulty}` | |
); | |
if (!questionResponse.ok) | |
throw new Error( | |
(await questionResponse.json()).detail || "Failed to load questions" | |
); | |
currentQuestions = await questionResponse.json(); | |
if (currentQuestions.length > 0) { | |
currentQuestionIndex = 0; | |
updateQuestionDisplay(); | |
document.getElementById("hintBtn").style.display = "block"; | |
document.getElementById("solutionBtn").style.display = "block"; | |
document.getElementById("navButtons").style.display = "flex"; | |
} else { | |
document.getElementById("questionDetails").innerHTML = | |
"<p>No questions available for this difficulty.</p>"; | |
document.getElementById("hintBtn").style.display = "none"; | |
document.getElementById("solutionBtn").style.display = "none"; | |
document.getElementById("navButtons").style.display = "none"; | |
currentQuestions = []; | |
currentQuestionIndex = 0; | |
} | |
} | |
function updateQuestionDisplay() { | |
const questionDetails = document.getElementById("questionDetails"); | |
if ( | |
currentQuestions.length && | |
currentQuestionIndex >= 0 && | |
currentQuestionIndex < currentQuestions.length | |
) { | |
const question = currentQuestions[currentQuestionIndex]; | |
questionDetails.innerHTML = `<p id="questionText"><strong>Practice Question:</strong> ${ | |
question.description || "No question available." | |
}</p>`; | |
} else { | |
questionDetails.innerHTML = | |
'<p id="questionText">No questions available.</p>'; | |
} | |
} | |
function prevQuestion() { | |
if (currentQuestions.length && currentQuestionIndex > 0) { | |
currentQuestionIndex--; | |
updateQuestionDisplay(); | |
updateHintSolutionDisplay(); | |
} | |
} | |
function nextQuestion() { | |
if ( | |
currentQuestions.length && | |
currentQuestionIndex < currentQuestions.length - 1 | |
) { | |
currentQuestionIndex++; | |
updateQuestionDisplay(); | |
updateHintSolutionDisplay(); | |
} | |
} | |
function updateHintSolutionDisplay() { | |
if (isHintVisible) toggleHint(); | |
if (isSolutionVisible) toggleSolution(); | |
} | |
function toggleHint() { | |
const question = currentQuestions[currentQuestionIndex]; | |
const hintBtn = document.getElementById("hintBtn"); | |
const questionDetails = document.getElementById("questionDetails"); | |
if (isSolutionVisible) toggleSolution(); | |
if (question && question.hint) { | |
if (hintBtn.textContent === "Show Hint") { | |
questionDetails.innerHTML += `<p><strong>Hint:</strong> ${question.hint}</p>`; | |
hintBtn.textContent = "Hide Hint"; | |
isHintVisible = true; | |
} else { | |
questionDetails.innerHTML = questionDetails.innerHTML.replace( | |
`<p><strong>Hint:</strong> ${question.hint}</p>`, | |
"" | |
); | |
hintBtn.textContent = "Show Hint"; | |
isHintVisible = false; | |
} | |
} else { | |
if (hintBtn.textContent === "Show Hint") { | |
questionDetails.innerHTML += | |
'<p class="text-black">No hint available.</p>'; | |
hintBtn.textContent = "Hide Hint"; | |
isHintVisible = true; | |
} else { | |
questionDetails.innerHTML = questionDetails.innerHTML.replace( | |
'<p class="text-black">No hint available.</p>', | |
"" | |
); | |
hintBtn.textContent = "Show Hint"; | |
isHintVisible = false; | |
} | |
} | |
} | |
function toggleSolution() { | |
const question = currentQuestions[currentQuestionIndex]; | |
const solutionBtn = document.getElementById("solutionBtn"); | |
const questionDetails = document.getElementById("questionDetails"); | |
if (isHintVisible) toggleHint(); | |
if (question && question.expected_sql) { | |
if (solutionBtn.textContent === "Show Solution") { | |
questionDetails.innerHTML += `<p><strong>Solution:</strong> <code>${question.expected_sql}</code></p>`; | |
solutionBtn.textContent = "Hide Solution"; | |
isSolutionVisible = true; | |
} else { | |
questionDetails.innerHTML = questionDetails.innerHTML.replace( | |
`<p><strong>Solution:</strong> <code>${question.expected_sql}</code></p>`, | |
"" | |
); | |
solutionBtn.textContent = "Show Solution"; | |
isSolutionVisible = false; | |
} | |
} else { | |
if (solutionBtn.textContent === "Show Solution") { | |
questionDetails.innerHTML += | |
'<p class="text-black">No solution available.</p>'; | |
solutionBtn.textContent = "Hide Solution"; | |
isSolutionVisible = true; | |
} else { | |
questionDetails.innerHTML = questionDetails.innerHTML.replace( | |
'<p class="text-black">No solution available.</p>', | |
"" | |
); | |
solutionBtn.textContent = "Show Solution"; | |
isSolutionVisible = false; | |
} | |
} | |
} | |
async function runQuery() { | |
const runBtn = document.getElementById("runQueryBtn"); | |
const resultsDiv = document.getElementById("results"); | |
let resultMessage = | |
document.getElementById("resultMessage") || document.createElement("span"); | |
if (!resultMessage.id) { | |
resultMessage.id = "resultMessage"; | |
runBtn.parentNode.appendChild(resultMessage); | |
} | |
runBtn.disabled = true; | |
runBtn.textContent = "Running..."; | |
resultMessage.textContent = ""; | |
try { | |
if (!editor) throw new Error("Editor not initialized. Refresh the page."); | |
let query = editor.getValue().trim().toLowerCase(); | |
if (!query) throw new Error("Please enter a query."); | |
const domain = document.getElementById("domainSelect").value; | |
if (!domain) throw new Error("Please load a database first."); | |
const schemaResponse = await fetch(`/api/schema/${domain}`, { | |
headers: { "session-id": sessionId }, | |
}); | |
if (!schemaResponse.ok) | |
throw new Error("Failed to load schema for table validation"); | |
const schema = (await schemaResponse.json()).schema; | |
const validTables = Object.keys(schema).map((t) => t.toLowerCase()); | |
const tableNames = extractTableNames(query); | |
if (tableNames.some((table) => !validTables.includes(table))) | |
throw new Error( | |
`Invalid table name. Use only: ${validTables.join(", ")}` | |
); | |
const response = await fetch("/api/run-query", { | |
method: "POST", | |
headers: { "Content-Type": "application/json", "session-id": sessionId }, | |
body: JSON.stringify({ query }), | |
}); | |
if (!response.ok) { | |
const errorText = await response.text(); | |
throw new Error(errorText || "Server error occurred."); | |
} | |
const result = await response.json(); | |
if (result.columns) { | |
let html = `<table class="w-full border-collapse"><tr>${result.columns | |
.map((col) => `<th class="border p-2">${col}</th>`) | |
.join("")}</tr>`; | |
html += result.rows | |
.map( | |
(row) => | |
`<tr>${result.columns | |
.map((col) => `<td class="border p-2">${row[col] || "NULL"}</td>`) | |
.join("")}</tr>` | |
) | |
.join(""); | |
html += "</table>"; | |
resultsDiv.innerHTML = html; | |
} else { | |
resultsDiv.innerHTML = "<p>No results</p>"; | |
} | |
if ( | |
currentQuestions.length && | |
currentQuestionIndex >= 0 && | |
currentQuestionIndex < currentQuestions.length | |
) | |
await validateQuery(query, result, resultsDiv); | |
else { | |
resultMessage.textContent = "Select a question first"; | |
resultMessage.className = "text-red-500 ml-4"; | |
} | |
} catch (e) { | |
resultsDiv.innerHTML = ""; | |
resultMessage.textContent = e.message.includes("Internal Server Error") | |
? "Server error: Please check the query or try again later." | |
: e.message; | |
resultMessage.className = "text-red-500 ml-4"; | |
} finally { | |
runBtn.disabled = false; | |
runBtn.textContent = "Run"; | |
} | |
} | |
function extractTableNames(query) { | |
const tables = new Set(); | |
const tokens = query.replace(/(\s+)/g, " ").split(" "); | |
let inSubquery = false, | |
inOpenQuery = false, | |
inValues = false; | |
for (let i = 0; i < tokens.length; i++) { | |
const token = tokens[i].toLowerCase(); | |
if (token === "(" && !inSubquery && !inValues) { | |
if (i > 0 && tokens[i - 1].toLowerCase() === "values") inValues = true; | |
else inSubquery = true; | |
} | |
if (token === ")" && (inSubquery || inValues)) { | |
if ( | |
inValues && | |
i + 1 < tokens.length && | |
tokens[i + 1].toLowerCase() === "as" | |
) | |
inValues = false; | |
else if (inSubquery) inSubquery = false; | |
} | |
if (token === "openquery" && i + 1 < tokens.length && tokens[i + 1] === "(") | |
inOpenQuery = true; | |
if (token === ")" && inOpenQuery) inOpenQuery = false; | |
if (inOpenQuery) continue; | |
if ( | |
[ | |
"from", | |
"join", | |
"update", | |
"delete", | |
"insert", | |
"into", | |
"using", | |
"apply", | |
"pivot", | |
"table", | |
].includes(token) | |
) { | |
let nextToken = tokens[i + 1] | |
? tokens[i + 1].replace(/[,;)]/g, "").toLowerCase() | |
: ""; | |
if ( | |
nextToken && | |
![ | |
"select", | |
"where", | |
"on", | |
"order", | |
"group", | |
"having", | |
"as", | |
"(", | |
].includes(nextToken) | |
) { | |
if (i + 2 < tokens.length && tokens[i + 2].toLowerCase() === "as") | |
nextToken = nextToken; | |
else if ( | |
!["left", "right", "inner", "outer", "cross", "full"].includes( | |
nextToken | |
) | |
) | |
tables.add(nextToken); | |
} | |
i++; | |
} else if ( | |
token === "merge" && | |
i + 1 < tokens.length && | |
tokens[i + 1].toLowerCase() === "into" | |
) { | |
let nextToken = tokens[i + 2] | |
? tokens[i + 2].replace(/[,;)]/g, "").toLowerCase() | |
: ""; | |
if (nextToken && !["using", "select", "where"].includes(nextToken)) | |
tables.add(nextToken); | |
i += 2; | |
while (i + 1 < tokens.length && tokens[i + 1].toLowerCase() !== "using") | |
i++; | |
if (i + 2 < tokens.length) { | |
nextToken = tokens[i + 2].replace(/[,;)]/g, "").toLowerCase(); | |
if (nextToken && !["select", "where"].includes(nextToken)) | |
tables.add(nextToken); | |
} | |
} else if ( | |
token === "select" && | |
i + 1 < tokens.length && | |
tokens[i + 1].toLowerCase() === "into" | |
) { | |
let nextToken = tokens[i + 2] | |
? tokens[i + 2].replace(/[,;)]/g, "").toLowerCase() | |
: ""; | |
if (nextToken && !["from", "select"].includes(nextToken)) | |
tables.add(nextToken); | |
i += 2; | |
while (i + 1 < tokens.length && tokens[i + 1].toLowerCase() !== "from") | |
i++; | |
if (i + 2 < tokens.length) { | |
nextToken = tokens[i + 2].replace(/[,;)]/g, "").toLowerCase(); | |
if (nextToken && !["where", "join"].includes(nextToken)) | |
tables.add(nextToken); | |
} | |
} else if (token === "with") { | |
let cteStart = i + 1; | |
while (i + 1 < tokens.length && tokens[i + 1].toLowerCase() !== "as") i++; | |
if (i + 2 < tokens.length && tokens[i + 2] === "(") { | |
let bracketCount = 1, | |
subqueryStart = i + 2; | |
while (i + 1 < tokens.length && bracketCount > 0) { | |
i++; | |
if (tokens[i] === "(") bracketCount++; | |
if (tokens[i] === ")") bracketCount--; | |
} | |
const subquery = tokens.slice(subqueryStart, i).join(" "); | |
tables.add( | |
...extractTableNames(subquery).filter((t) => !tables.has(t)) | |
); | |
} | |
} else if ( | |
token === "values" && | |
i + 1 < tokens.length && | |
tokens[i + 1] === "(" | |
) { | |
let aliasStart = i + 1; | |
while (i + 1 < tokens.length && tokens[i + 1] !== "as") i++; | |
if (i + 2 < tokens.length) { | |
let alias = tokens[i + 2].replace(/[,;)]/g, "").toLowerCase(); | |
if (alias) tables.add(alias); | |
} | |
} else if (["exists", "in"].includes(token)) { | |
let subqueryStart = i + 1; | |
while (i + 1 < tokens.length && tokens[i + 1] !== ")") i++; | |
if (i > subqueryStart) { | |
const subquery = tokens.slice(subqueryStart, i + 1).join(" "); | |
tables.add( | |
...extractTableNames(subquery).filter((t) => !tables.has(t)) | |
); | |
} | |
} | |
} | |
return Array.from(tables); | |
} | |
async function validateQuery(query, runResult, resultsDiv) { | |
const question = currentQuestions[currentQuestionIndex]; | |
if (!question || !question.expected_sql) { | |
showError("No question or expected SQL available for validation."); | |
return; | |
} | |
const response = await fetch("/api/validate", { | |
method: "POST", | |
headers: { "Content-Type": "application/json", "session-id": sessionId }, | |
body: JSON.stringify({ | |
user_query: query, | |
expected_query: question.expected_sql, | |
}), | |
}); | |
if (!response.ok) | |
throw new Error((await response.json()).detail || "Failed to validate"); | |
const result = await response.json(); | |
const questionText = document.getElementById("questionText"); | |
const resultMessage = document.getElementById("resultMessage"); | |
if (result.valid) { | |
questionText.classList.remove("text-red-500"); | |
questionText.classList.add("text-green-500"); | |
resultMessage.textContent = "Correct answer!"; | |
resultMessage.className = "text-green-500 ml-4"; | |
if (runResult.columns) { | |
let html = `<table class="w-full border-collapse"><tr>${runResult.columns | |
.map((col) => `<th class="border p-2">${col}</th>`) | |
.join("")}</tr>`; | |
html += runResult.rows | |
.map( | |
(row) => | |
`<tr>${runResult.columns | |
.map((col) => `<td class="border p-2">${row[col] || "NULL"}</td>`) | |
.join("")}</tr>` | |
) | |
.join(""); | |
html += "</table>"; | |
resultsDiv.innerHTML = html; | |
} else { | |
resultsDiv.innerHTML = "<p>No results</p>"; | |
} | |
} else { | |
questionText.classList.remove("text-green-500"); | |
questionText.classList.add("text-red-500"); | |
resultMessage.textContent = "Incorrect answer!"; | |
resultMessage.className = "text-red-500 ml-4"; | |
resultsDiv.innerHTML = ""; | |
} | |
} | |
function showError(message) { | |
let errorMessage = | |
document.getElementById("errorMessage") || document.createElement("span"); | |
if (!errorMessage.id) { | |
errorMessage.id = "errorMessage"; | |
document.getElementById("runQueryBtn").parentNode.appendChild(errorMessage); | |
} | |
errorMessage.textContent = message; | |
errorMessage.className = "text-red-500 mt-2"; | |
} | |
window.onload = init; | |