203
--TODO: Link worksheets to offerings
203
204
CREATE TABLE worksheet (
204
worksheetid SERIAL PRIMARY KEY,
205
offeringid INT4 REFERENCES offering (offeringid) NOT NULL,
206
identifier TEXT NOT NULL,
209
assessable BOOLEAN NOT NULL,
210
seq_no INT4 NOT NULL,
211
format TEXT NOT NUll,
212
UNIQUE (offeringid, identifier)
215
CREATE TABLE worksheet_exercise (
216
ws_ex_id SERIAL PRIMARY KEY,
217
worksheetid INT4 REFERENCES worksheet (worksheetid) NOT NULL,
218
exerciseid TEXT REFERENCES exercise (identifier) NOT NULL,
219
seq_no INT4 NOT NULL,
220
active BOOLEAN NOT NULL DEFAULT true,
221
optional BOOLEAN NOT NULL,
222
UNIQUE (worksheetid, exerciseid)
225
CREATE TABLE exercise_attempt (
205
worksheetid SERIAL PRIMARY KEY NOT NULL,
206
subject VARCHAR NOT NULL,
207
offeringid INT4 REFERENCES offering (offeringid) NOT NULL,
208
identifier VARCHAR NOT NULL,
211
UNIQUE (subject, identifier)
214
CREATE TABLE worksheet_problem (
215
worksheetid INT4 REFERENCES worksheet (worksheetid) NOT NULL,
216
problemid TEXT REFERENCES problem (identifier) NOT NULL,
218
PRIMARY KEY (worksheetid, problemid)
221
CREATE TABLE problem_attempt (
222
problemid VARCHAR REFERENCES problem (identifier) NOT NULL,
226
223
loginid INT4 REFERENCES login (loginid) NOT NULL,
227
ws_ex_id INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
224
worksheetid INT4 REFERENCES worksheet (worksheetid) NOT NULL,
228
225
date TIMESTAMP NOT NULL,
229
attempt TEXT NOT NULL,
226
attempt VARCHAR NOT NULL,
230
227
complete BOOLEAN NOT NULL,
231
228
active BOOLEAN NOT NULL DEFAULT true,
232
PRIMARY KEY (loginid, ws_ex_id, date)
229
PRIMARY KEY (problemid,loginid,date)
235
CREATE TABLE exercise_save (
232
CREATE TABLE problem_save (
233
problemid INT4 REFERENCES problem (problemid) NOT NULL,
236
234
loginid INT4 REFERENCES login (loginid) NOT NULL,
237
ws_ex_id INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
235
worksheetid INT4 REFERENCES worksheet (worksheetid) NOT NULL,
238
236
date TIMESTAMP NOT NULL,
240
PRIMARY KEY (loginid, ws_ex_id)
237
text VARCHAR NOT NULL,
238
PRIMARY KEY (problemid,loginid)
241
CREATE INDEX problem_attempt_index ON problem_attempt (problemid, loginid);
243
-- TABLES FOR EXERCISES IN DATABASE --
243
244
CREATE TABLE test_suite (
244
suiteid SERIAL PRIMARY KEY,
245
exerciseid TEXT REFERENCES exercise (identifier) NOT NULL,
245
suiteid SERIAL NOT NULL,
246
problemid TEXT REFERENCES problem (identifier) NOT NULL,
246
247
description TEXT,
249
PRIMARY KEY (problemid, suiteid)
252
252
CREATE TABLE test_case (
253
testid SERIAL PRIMARY KEY,
254
suiteid INT4 REFERENCES test_suite (suiteid) NOT NULL,
261
CREATE TABLE suite_variable (
262
varid SERIAL PRIMARY KEY,
253
testid SERIAL NOT NULL,
263
254
suiteid INT4 REFERENCES test_suite (suiteid) NOT NULL,
266
var_type TEXT NOT NULL,
270
CREATE TABLE test_case_part (
271
partid SERIAL PRIMARY KEY,
272
testid INT4 REFERENCES test_case (testid) NOT NULL,
273
part_type TEXT NOT NULL,
262
PRIMARY KEY (testid, suiteid)