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
|
BEGIN;
DO NOT APPLY THIS MIGRATION WITHOUT READING THE FOLLOWING;
-- This migration will delete all problem attempts and saves.
-- The new database schema links attempts and saves to specific worksheets.
-- Worksheets are linked to specific offerings.
-- Problems are no longer referenced by and id number, instead they are
-- referenced by an identifier TEXT field.
-- This means that in order to save your current data, you must link its
-- worksheet to an offering, and link the attempt to a problem identifier.
-- TODO: Write a script to save the problem attempts somehow.
-- Move the exercises from being stored as flat files, to being stored in
-- The Database
-- Drop Old, Unused tables.
DROP TABLE problem_attempt_breakdown;
DROP TABLE problem_test_case_tag;
DROP TABLE problem_tag;
DROP TABLE problem_test_case;
DROP TABLE problem_prerequisite;
DROP TABLE problem_save;
DROP TABLE problem_attempt;
DROP TABLE worksheet_problem;
DROP TABLE problem;
DROP TABLE worksheet;
CREATE TABLE exercise (
identifier TEXT PRIMARY KEY,
name TEXT,
description TEXT,
partial TEXT,
solution TEXT,
include TEXT,
num_rows INT4
);
CREATE TABLE worksheet (
worksheetid SERIAL PRIMARY KEY,
offeringid INT4 REFERENCES offering (offeringid) NOT NULL,
identifier TEXT NOT NULL,
name TEXT NOT NULL,
data TEXT NOT NULL,
assessable BOOLEAN NOT NULL,
seq_no INT4 NOT NULL,
format TEXT NOT NUll,
UNIQUE (offeringid, identifier)
);
CREATE TABLE worksheet_exercise (
ws_ex_id SERIAL PRIMARY KEY,
worksheetid INT4 REFERENCES worksheet (worksheetid) NOT NULL,
exerciseid TEXT REFERENCES exercise (identifier) NOT NULL,
seq_no INT4 NOT NULL,
active BOOLEAN NOT NULL DEFAULT true,
optional BOOLEAN NOT NULL,
UNIQUE (worksheetid, exerciseid)
);
CREATE TABLE exercise_attempt (
loginid INT4 REFERENCES login (loginid) NOT NULL,
ws_ex_id INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
date TIMESTAMP NOT NULL,
attempt TEXT NOT NULL,
complete BOOLEAN NOT NULL,
active BOOLEAN NOT NULL DEFAULT true,
PRIMARY KEY (loginid, ws_ex_id, date)
);
CREATE TABLE exercise_save (
loginid INT4 REFERENCES login (loginid) NOT NULL,
ws_ex_id INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
date TIMESTAMP NOT NULL,
text TEXT NOT NULL,
PRIMARY KEY (loginid, ws_ex_id)
);
CREATE TABLE test_suite (
suiteid SERIAL PRIMARY KEY,
exerciseid TEXT REFERENCES exercise (identifier) NOT NULL,
description TEXT,
seq_no INT4,
function TEXT,
stdin TEXT
);
CREATE TABLE test_case (
testid SERIAL PRIMARY KEY,
suiteid INT4 REFERENCES test_suite (suiteid) NOT NULL,
passmsg TEXT,
failmsg TEXT,
test_default TEXT,
seq_no INT4
);
CREATE TABLE suite_variable (
varid SERIAL PRIMARY KEY,
suiteid INT4 REFERENCES test_suite (suiteid) NOT NULL,
var_name TEXT,
var_value TEXT,
var_type TEXT NOT NULL,
arg_no INT4
);
CREATE TABLE test_case_part (
partid SERIAL PRIMARY KEY,
testid INT4 REFERENCES test_case (testid) NOT NULL,
part_type TEXT NOT NULL,
test_type TEXT,
data TEXT,
filename TEXT
);
COMMIT;
|