sql render engine

function sqlassessmentTypeInit(){ console.log("at sqlassessmentTypeInit==============") const {button, div, p, h2,pre,code, span} = van.tags const parser = new DOMParser(); ; console.log("I'm rendering!") bodyTag = document.querySelector("div.post-body") //bodyTag.innerHTML = JSON.stringify(globals.assessment) let doc = parser.parseFromString("

" +globals.assessment.data.text + "

", 'text/html') console.log("assessmentText =--->",doc) bodyTag.replaceChildren(h2({style:"margin-top:0"},globals.assessment.name),doc.querySelector("p")) let qnum=0 for(const task of globals.assessment.data.tasks){ qnum ++ console.log("task:",task) const studio= div({ class:"query-studio", "data-initial-sql": task.start || "empty", "data-studio-id": globals.assessment.id + "-" + qnum } ) if(qnum===1){ studio.dataset.schema = globals.assessment.data.schema studio.dataset.theme = "dark" } document.querySelector("div.post-body").appendChild(div({marginBottom:"2rem"},div({class:"sql-question"},span({class:"question-number"},qnum+". "),task.text),studio) ) checkForSavedStudioWork(qnum, task, studio) } addAssessmentToolbar() // now, the queries are here, let's load websql loadCss(` div.query-studio { margin-bottom:1rem; } div.sql-feedback-pane { margin: -0.75rem 0 1rem; border-radius: 0 0 6px 6px; border: 1px solid transparent; font-size: 0.95rem; overflow: hidden; } summary.sql-feedback-header { padding: 0.35rem 0.75rem; font-weight: 600; color: #fff; cursor: pointer; list-style: none; display: flex; align-items: center; justify-content: space-between; gap: 0.5rem; } summary.sql-feedback-header::marker { content: ""; } summary.sql-feedback-header::-webkit-details-marker { display: none; } summary.sql-feedback-header::after { content: ""; width: 0; height: 0; flex: none; border-left: 5px solid transparent; border-right: 5px solid transparent; border-top: 6px solid currentColor; transition: transform 0.15s ease; } details.sql-feedback-steps[open] > summary.sql-feedback-header::after { transform: rotate(180deg); } div.sql-feedback-body { padding: 0.5rem 0.75rem; } div.sql-feedback-body ol { margin: 0; padding-left: 1.25rem; } div.sql-feedback-pane.full-credit { border-color: #a5d6a7; } div.sql-feedback-pane.full-credit summary.sql-feedback-header { background: #2e7d32; } div.sql-feedback-pane.partial-credit { border-color: #e0c04a; } div.sql-feedback-pane.partial-credit summary.sql-feedback-header { background: #b8860b; } div.sql-feedback-pane.no-credit { border-color: #ef9a9a; } div.sql-feedback-pane.no-credit summary.sql-feedback-header { background: #c62828; } div.assessment-score-summary { margin-top: 2.5rem; margin-bottom: 2.5rem; padding: 0.65rem 0.75rem; border-radius: 4px; border: 1px solid #a5d6a7; background: #e8f5e9; color: #2e7d32; font-weight: 600; } div.sql-restore-banner { display: flex; align-items: center; gap: 0.6rem; margin-bottom: 0.5rem; padding: 0.4rem 0.75rem; border-radius: 4px; border: 1px solid #90caf9; background: #e3f2fd; color: #0d47a1; font-size: 0.9rem; } div.sql-restore-banner button.btn { padding: 0.25rem 0.75rem; font-size: 0.85rem; font-weight: 600; border-radius: 4px; border: 1px solid #1976d2; background: #1976d2; color: #fff; cursor: pointer; } div.sql-restore-banner button.icon-btn { margin-left: auto; border: none; background: transparent; cursor: pointer; font-size: 0.9rem; color: #0d47a1; } `) console.log("about to load cross origin----") loadCrossOrigin(`https://pglite.blogspot.com/feeds/posts/default/-/embed?alt=json-in-script&max-results=1&callback=loadCode`) loadCrossOrigin(`${globals.systemUrl}/feeds/posts/default/-/${globals.systemVersion}/sql-grader?alt=json-in-script&max-results=1&callback=loadCode`) startStudioWorkAutosave() console.log("about to restore prior attempt----") restorePriorAttempt() } // ############################################################################## // ##### In-progress work autosave & restore banner ##### // ############################################################################## // This used to live in tools/references/pglite/embed.js as its studioId // option (sessionStorage key "websql-studio-work:", wired to // editor.onDidChangeModelContent and a banner baked into that module's own // template) - moved here because it's an assessment-specific concern // (remembering a student's in-progress SQL per task), not something the // generic editor widget should own. The widget's public API is just // getQuery()/setQuery()/runQuery() with no change event, so autosaving here // has to poll (see startStudioWorkAutosave) rather than react to keystrokes // the way the old in-module version did. function studioWorkKey(qnum){ return `sql-studio-work:${globals.assessment.id}-${qnum}` } const STUDIO_WORK_SAVE_INTERVAL_MS = 1000 // Started once from sqlassessmentTypeInit. Only saves once window.pgliteStudios // exists, and simply no-ops per tick until it does. function startStudioWorkAutosave(){ setInterval(() => { if(!window.pgliteStudios){ return } let qnum = 0 for(const task of globals.assessment.data.tasks){ qnum++ const studio = window.pgliteStudios[qnum - 1] if(!studio){ continue } try{ sessionStorage.setItem(studioWorkKey(qnum), studio.getQuery()) }catch(err){ console.warn("startStudioWorkAutosave - could not save query studio work:", err) } } }, STUDIO_WORK_SAVE_INTERVAL_MS) } // Called for each task right as its studio div is built (see // sqlassessmentTypeInit). Shows a small banner offering to restore // sessionStorage's saved text for that studio, same signal the old // embed.js feature showed inline in its own template - but skipped // entirely when restoring from a specific prior *submission* (?priorKey=, // see restorePriorAttempt below): that flow already sets the definitive // prior-submission SQL from the server, and this banner would just be a // second, conflicting "we found your prior work" prompt for the same studio. function checkForSavedStudioWork(qnum, task, studioEl){ if(new URLSearchParams(window.location.search).get('priorKey')){ return } let saved try{ saved = sessionStorage.getItem(studioWorkKey(qnum)) }catch{ saved = null } if(!saved || saved === (task.start || "")){ return } const {div, span, button} = van.tags const banner = div({class: "sql-restore-banner"}, span({}, "A previous version of this query was found."), button({class: "btn", onclick: () => { const studio = window.pgliteStudios && window.pgliteStudios[qnum - 1] if(studio){ studio.setQuery(saved) } banner.remove() }}, "Restore"), button({class: "icon-btn", "aria-label": "Dismiss", title: "Dismiss", onclick: () => banner.remove()}, "✕"), ) studioEl.insertAdjacentElement("beforebegin", banner) } // ############################################################################## // ##### Restoring a prior submission (?priorKey= in the URL) into view ##### // ############################################################################## // assessment.js's "Try Again" link (see assessmentHref/renderUserSubmissions) // puts the most recent completed submission's key in ?priorKey= - fetch that // submission's detail straight from /api/submissions/item (same endpoint, // same way, that assessment.js's submissionRow uses to show it inline on the // assignment list), then replay the student's prior SQL into each studio and // show the per-task/overall score alongside it. async function restorePriorAttempt(){ const priorKey = new URLSearchParams(window.location.search).get('priorKey') console.log("restorePriorAttempt - priorKey from URL:", priorKey) if(!priorKey){ return } if(!(await waitForGlobals())){ console.warn("restorePriorAttempt - globals never became available, giving up") return } if(globals.userReady){ await globals.userReady } if(!globals.user || !globals.user.id){ console.warn("restorePriorAttempt - no signed-in user, giving up. globals.user:", globals.user) return } const options = { credentials: "include", headers: { Accept: "application/json", "X-Course-Id": sessionStorage.getItem("availabooks-course-id"), }, } let detail try{ const url = `https://app.availabooks.com/api/submissions/item?key=${encodeURIComponent(priorKey)}` console.log("restorePriorAttempt - fetching prior submission:", url) const response = await fetch(url, options) if(!response.ok){ throw new Error(`HTTP error! Status: ${response.status}`) } detail = await response.json() }catch(e){ console.error("restorePriorAttempt - could not load submission detail:", e) return } console.log("restorePriorAttempt - got prior submission:", detail) // /api/submissions/item nests the render engine's saved state under // `submission.state` (see the sample payload this was diagnosed from - // {kind, assessmentId, submittedAt, key, submission: {score, state}}), // not at the top level. const state = detail && detail.submission && detail.submission.state const answers = Array.isArray(state?.answers) ? state.answers : null if(!answers){ console.warn("restorePriorAttempt - prior submission has no state.answers to restore:", detail) return } const studiosReady = await waitForStudios(globals.assessment.data.tasks.length) console.log("restorePriorAttempt - studios ready:", studiosReady, "pgliteStudios:", window.pgliteStudios) const studioEls = document.querySelectorAll("div.query-studio") for(const answer of answers){ const studio = window.pgliteStudios && window.pgliteStudios[answer.index] console.log("restorePriorAttempt - restoring answer", answer.index, "studio found:", !!studio, answer) if(studio && typeof answer.studentSql === "string"){ studio.setQuery(answer.studentSql) } const studioEl = studioEls[answer.index] if(studioEl){ renderStoredTaskFeedback(answer) }else{ console.warn("restorePriorAttempt - could not render feedback for answer", answer.index, "studioEl found:", !!studioEl) } } if(typeof state.correct === "number" && typeof state.total === "number"){ renderAssessmentScoreSummary(null, state.correct, state.total) } } // Same poll-until-ready pattern as assessment.js's waitForGlobals - the pglite // embed module (loaded via loadCrossOrigin above) builds window.pgliteStudios // asynchronously as its own script runs, with no ready event to hook into. function waitForStudios(count, timeoutMs = 15000){ return new Promise(resolve => { const start = Date.now() ;(function check(){ if(window.pgliteStudios && window.pgliteStudios.length >= count){ resolve(true); return } if(Date.now() - start >= timeoutMs){ console.warn("waitForStudios - studios never became available, giving up") resolve(false) return } setTimeout(check, 100) })() }) } // Called by assessment.js's Submit/Download toolbar buttons. window.pgliteStudios // is built in the same order as the .query-studio divs above, so studio index // (qnum - 1) lines up with task order - each studio's getQuery() returns its // editor's current text live, straight from Monaco, no debounce/staleness. function getDocument(){ const document_ = {} let qnum = 0 for(const task of globals.assessment.data.tasks){ qnum++ const studioId = globals.assessment.id + "-" + qnum const studio = window.pgliteStudios && window.pgliteStudios[qnum - 1] document_[studioId] = studio ? studio.getQuery() : (task.start || "") } return document_ } // Called by assessment.js's Upload toolbar button with a document in the same // shape getDocument() above produces (studio id -> query text). Mirrors // getDocument()'s use of setQuery() on each live studio, so this only reaches // studios that have already been created - anything else in the uploaded // document is silently ignored. function putDocument(document_){ let qnum = 0 for(const task of globals.assessment.data.tasks){ qnum++ const studioId = globals.assessment.id + "-" + qnum if(!Object.prototype.hasOwnProperty.call(document_, studioId)){ continue } const studio = window.pgliteStudios && window.pgliteStudios[qnum - 1] if(studio){ studio.setQuery(document_[studioId]) } } } // ############################################################################## // ##### Grading. Called by assessment.js's submitAssessment() (see the ##### // ##### getDocument/putDocument contract note above - gradeDocument() is ##### // ##### the same kind of optional hook, called only if this render engine ##### // ##### module defines it). Runs each task's student query and its answer ##### // ##### key against that task's own pglite studio connection (so both see ##### // ##### the same schema/data the student was working against), compares ##### // ##### result sets via window.sql_grader.gradeQuery() (built from ##### // ##### tools/modules-builder/src/modules/sql-grader/, a port of ##### // ##### sql-book's QueryGrader), and renders per-task feedback plus an ##### // ##### aggregate score. ##### // ############################################################################## async function gradeDocument(){ if(!window.sql_grader || typeof window.sql_grader.gradeQuery !== "function"){ alert("The grading engine hasn't finished loading yet. Please wait a moment and try Submit again.") return } const report = [] let qnum = 0 for(const task of globals.assessment.data.tasks){ qnum++ // gradeQuery() already degrades a student query that fails to execute // into partial credit on its own - this try/catch is for everything // outside that (decryption failing, the studio never having loaded, // anything unforeseen), so one task's trouble can't stop the rest of // the assessment from being graded or hide the total score at the end. try{ const studio = window.pgliteStudios && window.pgliteStudios[qnum - 1] const studentSql = studio ? studio.getQuery() : (task.start || "") const key = await decryptTaskKey(task, globals.assessment.id) const solutionSqlSet = Array.isArray(key) ? key : [key] const runQuery = async (sql) => { if(!studio){ return { error: "This question's SQL studio isn't loaded." } } try{ const result = await studio.runQuery(sql) return { fields: result.fields, rows: result.rows } }catch(e){ return { error: (e && e.message) ? e.message : String(e) } } } const grade = await window.sql_grader.gradeQuery(solutionSqlSet, studentSql, task.requiredTerms, runQuery) const pointsEarned = pointsEarnedForTask(task, grade) report.push({ task, qnum, studentSql, grade, pointsEarned }) renderTaskFeedback(qnum, task, studentSql, grade, pointsEarned) }catch(e){ const message = (e && e.message) ? e.message : String(e) const grade = { zero_credit: true, complexity_index: -1, error_message: message, path: [`Grading could not be completed for this question: ${message}`] } report.push({ task, qnum, studentSql: "", grade, pointsEarned: 0 }) renderTaskFeedback(qnum, task, "", grade, 0) } } const { totalEarned, totalPossible } = computeReportTotals(report) renderAssessmentScoreSummary(report, totalEarned, totalPossible) await submitGradeReport(report, totalEarned, totalPossible) return report } // Same shape a-quiz.js's buildState() uses ({type, correct, total, answers}), // so Pro's submissions API sees one consistent state envelope regardless of // which render engine graded the assessment. `answers` carries the per-task // student SQL plus everything renderTaskFeedback's pane is built from (see // feedbackForGrade for className/label/percent/summary, and grade.path/ // error_message for the mechanical trace) - not the answer key itself - so // restorePriorAttempt can hand it to buildSqlFeedbackPane and reproduce the // exact same pane the student saw when they originally submitted. function sqlSubmissionState(report, totalEarned, totalPossible){ return { type: "sql", correct: totalEarned, total: totalPossible, answers: report.map(r => { const { className, label, percent, summary } = feedbackForGrade(r.studentSql, r.grade) return { index: r.qnum - 1, prompt: r.task.text, studentSql: r.studentSql, points: r.task.points, pointsEarned: r.pointsEarned, credit: className, label, percent, feedback: summary, steps: Array.isArray(r.grade.path) ? r.grade.path.slice() : [], errorMessage: r.grade.error_message || null, } }), } } const SQL_SUBMIT_HEADERS = {Accept: "application/json", "Content-Type": "application/json"} // Same status/error-code -> learner-facing message mapping a-quiz.js uses for // Pro submission failures (messageForSubmitError), kept in sync so students see // consistent wording no matter which render engine they submitted through. function messageForSqlSubmitError(status, errorCode){ if(status === 401 || errorCode === "unauthenticated"){ return "Sign in to submit your work." } switch(errorCode){ case "missing_organization": return "Select a course before submitting your work." case "not_enrolled": return "You are not enrolled in this course." case "course_archived": return "This course is archived; submissions are closed." case "attempt_limit_reached": return "You have used all attempts for this assessment." default: return `Could not submit assessment (${errorCode}).` } } // Posts the already-graded report to Pro the same way a-quiz.js's submitQuiz() // does - score as a 0-1 fraction plus a state blob - but via courseFetch/ // globals.appUrl (system.js) rather than a standalone fetch, since this render // engine runs inside the same page as system.js and should carry this tab's // course header like every other Pro call there. Grading has already rendered // locally by the time this runs, so a submission failure here only surfaces as // a soft status message - it never erases the score the student already sees. async function submitGradeReport(report, totalEarned, totalPossible){ const assessmentId = globals.assessment.id if(!assessmentId){ return } const score = totalPossible ? totalEarned / totalPossible : 0 const state = sqlSubmissionState(report, totalEarned, totalPossible) try{ const response = await courseFetch(globals.appUrl + "/api/submissions/complete", { method: "POST", headers: SQL_SUBMIT_HEADERS, body: JSON.stringify({assessmentId, score, state}), }) const payload = await response.json().catch(() => null) if(!response.ok){ const errorCode = payload && typeof payload === "object" && "error" in payload ? String(payload.error) : `HTTP ${response.status}` renderSubmitStatus(messageForSqlSubmitError(response.status, errorCode), true) return } renderSubmitStatus("Your work has been submitted.", false) }catch(e){ const message = (e && e.message) ? e.message : String(e) renderSubmitStatus(`Could not reach Pro (${message}).`, true) } } function renderSubmitStatus(message, isError){ const {p} = van.tags const bodyTag = document.querySelector("div.post-body") const existing = bodyTag.querySelector("p.submit-status") if(existing){ existing.remove() } const status = p({class: isError ? "submit-status is-error" : "submit-status"}, message) const summary = bodyTag.querySelector("div.assessment-score-summary") if(summary){ summary.insertAdjacentElement("afterend", status) }else{ bodyTag.appendChild(status) } } // sql-book's QueryGrader.grade_query() only ever awards full task.points when // grade.full_credit is set (exact-text match, or a matched result set with no // column/order deductions) - any other truthy percent (grade.full_percent from // a correct-but-imperfect result set, or grade.percent from the AST partial // credit fallback) gets scaled by the key query's complexity_index, same as // sql-book's per-task scoring in system-book-specific-js. Shared by // pointsEarnedForTask() and feedbackForGrade() so the percentage students see // always matches the percentage their points were actually computed from. function weightedPercent(grade){ const gradePercent = grade.full_percent || grade.percent if(!gradePercent){ return 0 } return Math.round(grade.complexity_index * gradePercent * 100) / 100 } function pointsEarnedForTask(task, grade){ if(grade.full_credit){ return task.points } return Math.round(task.points * weightedPercent(grade) * 100) / 100 } const FULL_CREDIT_MESSAGES = ["Great job!", "Outstanding!", "Nicely done!", "That's exactly right."] // Returns everything renderTaskFeedback needs to build the pane: the header's // label and (for partial credit only) percent, plus `summary` - the one // human-readable takeaway sentence for this grade, which renderTaskFeedback // appends as the closing entry in "How this was scored" rather than showing // it as its own separate block. `summary` carries the same professor/system- // authored markup grade.feedback always has (

/
), never student input. function feedbackForGrade(studentSql, grade){ if(grade.full_credit){ return { className: "full-credit", label: "Full Credit", percent: null, summary: FULL_CREDIT_MESSAGES[randomIndex(FULL_CREDIT_MESSAGES.length)] } } const gradePercent = grade.full_percent || grade.percent if(gradePercent){ const pct = Math.round(weightedPercent(grade) * 10000) / 100 return { className: "partial-credit", label: "Partial Credit", percent: pct, summary: grade.feedback || "" } } const summary = (studentSql && studentSql.trim().length > 0) ? (grade.feedback || "That result doesn't match what this question is asking for.") : "It appears that you did not enter a solution." return { className: "no-credit", label: "Incorrect", percent: null, summary } } // Math.random() reads fine here - feedback copy is cosmetic, not part of grading. function randomIndex(length){ return Math.floor(Math.random() * length) } // Builds the actual sql-feedback-pane DOM node from its raw ingredients - // shared by renderTaskFeedback (a fresh grade, just computed) and // renderStoredTaskFeedback (a past submission's saved answer, restored by // restorePriorAttempt) so a subsequent attempt shows the identical pane the // student saw the moment they originally submitted, not a lookalike. function buildSqlFeedbackPane(studioId, className, headerText, steps, errorMessage, summaryText, startOpen){ const {div, details, summary, ol, li} = van.tags // Every description of how the grade was reached lives in "How this was // scored" now, not scattered across the pane - the mechanical trace // (steps, plain professor-authored strings - never the answer key's SQL // text or anything student-supplied, so plain text children are fine) // followed by the raw database error (if the query failed to run) and // finally the one-sentence human takeaway, in that order since that's the // order a student would actually want to read them: what ran, why it // failed (if it did), then what it means for their score. const stepItems = steps.map(step => li(step)) if(errorMessage){ stepItems.push(li("Database error: " + errorMessage)) } if(summaryText){ stepItems.push(li({innerHTML: summaryText})) } // The header IS the disclosure control now (summary.sql-feedback-header, // arrow drawn by CSS via ::after) - no separate "How this was scored" // label. return div({class: "sql-feedback-pane " + className, "data-studio-id": studioId}, details({class: "sql-feedback-steps", open: startOpen}, summary({class: "sql-feedback-header"}, headerText), div({class: "sql-feedback-body"}, ol(...stepItems)) ) ) } function renderTaskFeedback(qnum, task, studentSql, grade, pointsEarned){ const studioId = globals.assessment.id + "-" + qnum // window.pgliteStudios is built in the same DOM order as these // .query-studio divs (see getDocument()/putDocument() above), so index by // qnum - 1 instead of selecting on data-studio-id, same as everywhere else // in this file that maps a task to its studio. const studioEl = document.querySelectorAll("div.query-studio")[qnum - 1] if(!studioEl){ return } const existing = studioEl.parentElement && studioEl.parentElement.querySelector('div.sql-feedback-pane[data-studio-id="' + studioId + '"]') if(existing){ existing.remove() } const { className, label, percent, summary: summaryText } = feedbackForGrade(studentSql, grade) const headerText = label + " — Earned " + pointsEarned + " of " + task.points + " points" + (percent != null ? " (" + percent + "%)" : "") const steps = Array.isArray(grade.path) ? grade.path.slice() : [] // Full credit starts collapsed since there's nothing a student needs to // dig into; anything less than full credit starts open since that detail // is the whole point. const pane = buildSqlFeedbackPane(studioId, className, headerText, steps, grade.error_message, summaryText, !grade.full_credit) studioEl.insertAdjacentElement("afterend", pane) } // Restores one answer from a past submission (see restorePriorAttempt) into // the same pane shape renderTaskFeedback builds for a fresh grade - `answer` // already carries everything needed (see sqlSubmissionState), so this is // just header text plus a straight pass-through to buildSqlFeedbackPane. function renderStoredTaskFeedback(answer){ const qnum = answer.index + 1 const studioId = globals.assessment.id + "-" + qnum const studioEl = document.querySelectorAll("div.query-studio")[answer.index] if(!studioEl){ return } const existing = studioEl.parentElement && studioEl.parentElement.querySelector('div.sql-feedback-pane[data-studio-id="' + studioId + '"]') if(existing){ existing.remove() } const headerText = "Prior attempt: " + answer.label + " — Earned " + answer.pointsEarned + " of " + answer.points + " points" + (answer.percent != null ? " (" + answer.percent + "%)" : "") const steps = Array.isArray(answer.steps) ? answer.steps : [] // Reviewing a prior attempt: start every pane collapsed regardless of // credit, so the page reads as a summary the student can drill into // rather than an already-exploded wall of past feedback. const pane = buildSqlFeedbackPane(studioId, answer.credit, headerText, steps, answer.errorMessage, answer.feedback, false) studioEl.insertAdjacentElement("afterend", pane) } // Shared by renderAssessmentScoreSummary (what the student sees on the page) // and submitGradeReport (what Pro is told the score was) so both always agree. function computeReportTotals(report){ const totalPossible = report.reduce((sum, r) => sum + (r.task.points || 0), 0) const totalEarned = Math.round(report.reduce((sum, r) => sum + r.pointsEarned, 0) * 100) / 100 return {totalEarned, totalPossible} } function renderAssessmentScoreSummary(report, totalEarned, totalPossible){ const pct = totalPossible ? Math.round((totalEarned / totalPossible) * 10000) / 100 : 0 renderScoreSummary(`Total Score: ${totalEarned} / ${totalPossible} (${pct}%)`) } // Used by both a freshly-graded attempt and a restored past attempt (see // restorePriorAttempt, which calls renderAssessmentScoreSummary directly with // the saved state's correct/total) so there's only one place that owns the // summary's placement/replace-on-rerun behavior, and a restored attempt shows // the exact same "Total Score" text the student saw originally. function renderScoreSummary(text){ const {div} = van.tags const bodyTag = document.querySelector("div.post-body") const existing = bodyTag.querySelector("div.assessment-score-summary") if(existing){ existing.remove() } const summary = div({class: "assessment-score-summary"}, text) const toolbar = bodyTag.querySelector("div.assessment-toolbar") if(toolbar){ toolbar.insertAdjacentElement("beforebegin", summary) }else{ bodyTag.appendChild(summary) } return summary } sqlassessmentTypeInit()