-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdocumentation_instructor.html
More file actions
208 lines (191 loc) · 10.4 KB
/
Copy pathdocumentation_instructor.html
File metadata and controls
208 lines (191 loc) · 10.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>DBGrader Instructor Documentation</title>
<link rel="stylesheet" href="css/documentation.css">
</head>
<body>
<div class="container">
<h1>DBGrader — Instructor Documentation</h1>
<p style="font-size: 16px; color: #6c757d; margin-bottom: 20px;">
Author SQL exercises as JSON on a Tsugi placement, preview expected results in the browser,
and grade learners by comparing SQLite outputs. Learner-facing help is in
<a href="documentation.html">documentation.html</a>.
</p>
<div class="nav-tabs">
<button type="button" class="nav-tab active" onclick="showTab('author', this)">Authoring</button>
<button type="button" class="nav-tab" onclick="showTab('modes', this)">Modes</button>
<button type="button" class="nav-tab" onclick="showTab('json', this)">Exercise JSON</button>
<button type="button" class="nav-tab" onclick="showTab('grading', this)">Grading</button>
<button type="button" class="nav-tab" onclick="showTab('runtime', this)">Runtime</button>
</div>
<div id="author" class="tab-content active">
<h2>Authoring an Exercise</h2>
<p>Open <strong>Edit</strong> from the top bar (<code>index.php?mode=author</code>).</p>
<ol>
<li>Set <strong>Title</strong> and <strong>Prompt</strong></li>
<li>Choose <strong>Mode</strong> (<code>query</code> or <code>database-state</code>)</li>
<li>Optionally set <strong>Starter SQL</strong> for the learner editor</li>
<li>Write <strong>Solution</strong> SQL</li>
<li>Write <strong>Setup</strong> SQL (required for query mode; optional for database-state)</li>
<li>For database-state, add <strong>Verification SQL</strong> (semicolon-separated)</li>
<li><strong>Run query / Run verification preview</strong> to validate instructor SQL</li>
<li><strong>Save</strong> to the placement JSON</li>
<li><strong>View Assignment JSON</strong> — copy the exercise object</li>
</ol>
<div class="tip">
<h4>Top bar</h4>
<ul>
<li><strong>Learner</strong> — student view of the exercise</li>
<li><strong>Edit</strong> — exercise authoring UI</li>
<li><strong>Help</strong> — learner documentation</li>
<li><strong>Instructor Help</strong> — this page</li>
<li><strong>Student Data</strong> — grades table</li>
<li><strong>Settings</strong> — choose a built-in assignment and due date</li>
</ul>
</div>
<div class="warn">
<h4>Always preview before publishing</h4>
<p>Broken setup or solution SQL shows up as learner-facing failures. Use the author Run / verification preview first.</p>
</div>
</div>
<div id="modes" class="tab-content">
<h2>Exercise Modes</h2>
<h3>query</h3>
<p>Compare the learner’s result set to the solution’s result set after identical setup on fresh databases.</p>
<ul>
<li>Best for <code>SELECT</code>, joins, aggregates, ordering, CTEs</li>
<li>Setup SQL required</li>
<li>Often tagged <code>compatibility: ["dbgrader", "udemy"]</code></li>
</ul>
<h3>database-state</h3>
<p>Learner may submit multiple statements (DDL/DML). After setup + solution (or learner SQL),
verification queries run; those results are compared.</p>
<div class="example">
<h4>Useful verification patterns</h4>
<div class="code-block">PRAGMA table_info(orders);
PRAGMA foreign_key_list(orders);
SELECT name, type, sql FROM sqlite_schema WHERE name = 'orders';
SELECT COUNT(*) FROM orders;</div>
</div>
<p>Prefer PRAGMA / catalog checks over comparing raw <code>CREATE TABLE</code> strings so formatting differences do not fail students.</p>
<p>Database-state exercises are DBGrader-only (<code>compatibility: ["dbgrader"]</code>).</p>
<h3>upload-check</h3>
<p>Learner uploads a SQLite database file (<code>.sqlite3</code> / <code>.sqlite</code> / <code>.db</code>, max 3M).
The browser opens it with SQLite WASM (no server upload of the DB contents for grading).</p>
<ul>
<li>Author builds an <strong>expected reference DB</strong> with setup (and optional solution) SQL</li>
<li>The same <code>verification_sql</code> runs on the gold DB and on the uploaded file</li>
<li>Results are compared (prefer <code>COUNT(*)</code> / filtered checks over volatile <code>id</code> dumps)</li>
<li>Built-in example: <code>PollsExercise</code> (Polls batch load)</li>
</ul>
<p>Upload-check exercises are DBGrader-only (<code>compatibility: ["dbgrader"]</code>).</p>
<h3>playground</h3>
<p>In-browser SQLite admin: a persistent database (IndexedDB), SQL editor,
<strong>Run SQL</strong>, <strong>Reset database</strong>, <strong>Upload</strong>, and <strong>Download</strong>.
Not graded. Optional setup SQL is applied when the learner resets.</p>
<p>Built-in: <code>PlaygroundExercise</code>.</p>
</div>
<div id="json" class="tab-content">
<h2>Exercise JSON and lessons.json</h2>
<p>Stored in <code>lti_link.json</code>. Core shape:</p>
<div class="code-block">{
"version": 1,
"type": "sqlite",
"mode": "query",
"title": "...",
"prompt": "...",
"instructions_url": "https://www.dj4e.com/assn/dj4e_batch.md",
"setup_sql": "...",
"solution_sql": "...",
"starter_sql": "...",
"verification_sql": [],
"comparison": {
"column_names": true,
"column_order": true,
"row_order": true,
"numeric_tolerance": 0
},
"dialect": "sqlite",
"compatibility": ["dbgrader", "udemy"]
}</div>
<p><code>instructions_url</code> is optional. When set to an <code>http(s)</code> URL, learners see an
<strong>Assignment instructions</strong> link under the prompt.</p>
<h3>Built-in assignments</h3>
<p>Catalog keys live in <code>assignments.php</code>; exercise bodies in <code>assignments/*.php</code>.
Built-ins include <code>PantryExercise</code>, <code>SimpleWhereExercise</code>,
<code>PollsExercise</code>, <code>ModelsExercise</code>, <code>UnescoExercise</code>,
and <code>PlaygroundExercise</code>.</p>
<p>Select under <strong>Settings</strong>, or set on first launch with LTI custom
(copied into the link settings row only when that setting is empty):</p>
<div class="code-block">"custom": [
{
"key": "exercise",
"value": "PantryExercise"
}
]</div>
<p>When Settings and LTI custom are both empty,
<code>?exercise=PantryExercise</code> (catalog exercise name) seeds Settings the same way.</p>
<p>Load order: built-in key from Settings / LTI <code>exercise</code> / <code>?exercise=</code> catalog name;
else existing <code>lti_link.json</code>; else full <code>config</code> JSON
(LTI custom, or lessons via <code>?inherit=<resource_link_id></code>);
else an empty stub.</p>
<h3>Preload full exercise from lessons.json</h3>
<p>Alternate shape when you are not using a built-in — seeds <code>lti_link.json</code> on first empty launch.
Use LTI custom <code>config</code>, or reload from <code>$CFG->lessons</code> with
<code>?inherit=<resource_link_id></code>:</p>
<div class="code-block">"custom": [
{
"key": "config",
"json": { /* exercise object */ }
}
]</div>
</div>
<div id="grading" class="tab-content">
<h2>Grading and Attempts</h2>
<ul>
<li>Learner <strong>Check Answer</strong> grades in the browser, then calls
<code>/api/record-attempt.php</code> and, on success, <code>/api/grade-submit.php</code> with <code>1.0</code>.</li>
<li>Alternate correct SQL is fine if results (or verification results) match.</li>
<li><code>comparison</code> controls column names, column order, row order, and numeric tolerance.</li>
<li>Exploration commands are rejected for Check Answer.</li>
</ul>
<div class="warn">
<h4>Trusted browser environment</h4>
<p>Learners can inspect delivered exercise data. Use DBGrader for practice and formative work,
not as a high-stakes secret exam harness.</p>
</div>
</div>
<div id="runtime" class="tab-content">
<h2>Runtime</h2>
<ul>
<li>SQLite WASM from <code>$CFG->staticroot/js/sqlite/sqlite-wasm-3530300/jswasm/</code> (3.53.3)</li>
<li>Execution in a Web Worker so slow queries do not freeze the UI</li>
<li>Always a fresh <code>:memory:</code> database per Run / Check / preview</li>
</ul>
<p>See <code>DESIGN.md</code> in this tool for the full design and phased roadmap.</p>
<p>Learner explore-command reference: <a href="documentation.html">documentation.html</a>.</p>
</div>
<hr class="doc-rule">
<div class="footer-about">
<h3>About DBGrader</h3>
<p>Tsugi / DJ4E SQL autograder. Design notes live in <code>DESIGN.md</code>.</p>
</div>
</div>
<script>
function showTab(tabName, buttonEl) {
document.querySelectorAll('.tab-content').forEach(function (content) {
content.classList.remove('active');
});
document.querySelectorAll('.nav-tab').forEach(function (tab) {
tab.classList.remove('active');
});
var panel = document.getElementById(tabName);
if (panel) panel.classList.add('active');
if (buttonEl) buttonEl.classList.add('active');
}
</script>
</body>
</html>