25
22
CREATE TABLE subject (
26
23
subjectid SERIAL PRIMARY KEY NOT NULL,
27
24
subj_code VARCHAR UNIQUE NOT NULL,
28
25
subj_name VARCHAR NOT NULL,
29
subj_short_name VARCHAR UNIQUE NOT NULL,
26
subj_short_name VARCHAR, -- may be null
33
CREATE TABLE semester (
34
semesterid SERIAL PRIMARY KEY NOT NULL,
35
year CHAR(4) NOT NULL,
36
semester CHAR(1) NOT NULL,
37
state TEXT NOT NULL CHECK (state IN ('disabled', 'past',
38
'current', 'future')) DEFAULT 'current',
39
UNIQUE (year, semester)
42
30
CREATE TABLE offering (
43
31
offeringid SERIAL PRIMARY KEY NOT NULL,
44
32
subject INT4 REFERENCES subject (subjectid) NOT NULL,
45
semesterid INTEGER REFERENCES semester (semesterid) NOT NULL,
46
groups_student_permissions VARCHAR NOT NULL DEFAULT 'none',
47
CHECK (groups_student_permissions in ('none', 'invite', 'create')),
48
UNIQUE (subject, semesterid)
52
CREATE TABLE project_set (
53
projectsetid SERIAL PRIMARY KEY NOT NULL,
54
offeringid INTEGER REFERENCES offering (offeringid) NOT NULL,
55
max_students_per_group INTEGER
33
year CHAR(4) NOT NULL,
34
semester CHAR(1) NOT NULL
58
37
CREATE TABLE project (
59
38
projectid SERIAL PRIMARY KEY NOT NULL,
60
short_name TEXT NOT NULL,
64
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
41
offeringid INT4 REFERENCES offering (offeringid) NOT NULL,
68
CREATE OR REPLACE FUNCTION check_project_namespacing_insertupdate()
73
IF TG_OP = ''UPDATE'' THEN
74
IF NEW.projectsetid = OLD.projectsetid AND NEW.short_name = OLD.short_name THEN
78
SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
79
PERFORM 1 FROM project, project_set
80
WHERE project_set.offeringid = oid AND
81
project.projectsetid = project_set.projectsetid AND
82
project.short_name = NEW.short_name;
84
RAISE EXCEPTION ''a project named % already exists in offering ID %'', NEW.short_name, oid;
90
CREATE TRIGGER check_project_namespacing
91
BEFORE INSERT OR UPDATE ON project
92
FOR EACH ROW EXECUTE PROCEDURE check_project_namespacing_insertupdate();
94
45
CREATE TABLE project_group (
95
46
groupnm VARCHAR NOT NULL,
96
47
groupid SERIAL PRIMARY KEY NOT NULL,
97
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
48
offeringid INT4 REFERENCES offering (offeringid),
99
50
createdby INT4 REFERENCES login (loginid) NOT NULL,
100
51
epoch TIMESTAMP NOT NULL,
101
UNIQUE (projectsetid, groupnm)
52
UNIQUE (offeringid, groupnm)
104
CREATE OR REPLACE FUNCTION check_group_namespacing_insertupdate()
109
SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
110
PERFORM 1 FROM project_group, project_set WHERE project_set.offeringid = oid AND project_group.projectsetid = project_set.projectsetid AND project_group.groupnm = NEW.groupnm;
112
RAISE EXCEPTION ''a project group named % already exists in offering ID %'', NEW.groupnm, oid;
116
' LANGUAGE 'plpgsql';
118
CREATE TRIGGER check_group_namespacing
119
BEFORE INSERT OR UPDATE ON project_group
120
FOR EACH ROW EXECUTE PROCEDURE check_group_namespacing_insertupdate();
122
55
CREATE TABLE group_invitation (
123
56
loginid INT4 REFERENCES login (loginid) NOT NULL,
124
57
groupid INT4 REFERENCES project_group (groupid) NOT NULL,
135
64
CREATE TABLE group_member (
136
65
loginid INT4 REFERENCES login (loginid),
137
66
groupid INT4 REFERENCES project_group (groupid),
67
projectid INT4 REFERENCES project (projectid),
68
UNIQUE (loginid,projectid),
138
69
PRIMARY KEY (loginid,groupid)
141
72
CREATE TABLE enrolment (
142
73
loginid INT4 REFERENCES login (loginid),
143
74
offeringid INT4 REFERENCES offering (offeringid),
144
role TEXT NOT NULL CHECK (role IN ('student', 'tutor',
145
'lecturer')) DEFAULT 'student',
147
76
special_result VARCHAR,
149
78
special_supp_result VARCHAR,
151
active BOOL NOT NULL DEFAULT true,
152
80
PRIMARY KEY (loginid,offeringid)
156
84
assessedid SERIAL PRIMARY KEY NOT NULL,
157
85
loginid INT4 REFERENCES login (loginid),
158
86
groupid INT4 REFERENCES project_group (groupid),
159
projectid INT4 REFERENCES project (projectid) NOT NULL,
160
87
-- exactly one of loginid and groupid must be non-null
161
88
CHECK ((loginid IS NOT NULL AND groupid IS NULL)
162
89
OR (loginid IS NULL AND groupid IS NOT NULL))
165
92
CREATE TABLE project_extension (
166
extensionid SERIAL PRIMARY KEY,
167
93
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
94
projectid INT4 REFERENCES project (projectid) NOT NULL,
168
95
deadline TIMESTAMP NOT NULL,
169
96
approver INT4 REFERENCES login (loginid) NOT NULL,
173
100
CREATE TABLE project_submission (
174
submissionid SERIAL PRIMARY KEY,
175
101
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
102
projectid INT4 REFERENCES project (projectid) NOT NULL,
176
103
path VARCHAR NOT NULL,
177
revision INT4 NOT NULL,
178
date_submitted TIMESTAMP NOT NULL,
179
submitter INT4 REFERENCES login (loginid) NOT NULL
104
revision INT4 NOT NULL
182
107
CREATE TABLE project_mark (
183
108
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
109
projectid INT4 REFERENCES project (projectid) NOT NULL,
184
110
componentid INT4,
185
111
marker INT4 REFERENCES login (loginid) NOT NULL,
192
CREATE TABLE exercise (
193
identifier TEXT PRIMARY KEY,
118
CREATE TABLE problem (
119
problemid SERIAL PRIMARY KEY NOT NULL,
120
identifier VARCHAR UNIQUE NOT NULL,
202
124
CREATE TABLE worksheet (
203
worksheetid SERIAL PRIMARY KEY,
204
offeringid INT4 REFERENCES offering (offeringid) NOT NULL,
205
identifier TEXT NOT NULL,
208
assessable BOOLEAN NOT NULL,
209
seq_no INT4 NOT NULL,
210
format TEXT NOT NUll,
211
UNIQUE (offeringid, identifier)
214
CREATE TABLE worksheet_exercise (
215
ws_ex_id SERIAL PRIMARY KEY,
216
worksheetid INT4 REFERENCES worksheet (worksheetid) NOT NULL,
217
exerciseid TEXT REFERENCES exercise (identifier) NOT NULL,
218
seq_no INT4 NOT NULL,
219
active BOOLEAN NOT NULL DEFAULT true,
220
optional BOOLEAN NOT NULL,
221
UNIQUE (worksheetid, exerciseid)
224
CREATE TABLE exercise_attempt (
125
worksheetid SERIAL PRIMARY KEY NOT NULL,
126
subject VARCHAR NOT NULL,
127
identifier VARCHAR NOT NULL,
130
UNIQUE (subject, identifier)
133
CREATE TABLE worksheet_problem (
134
worksheetid INT4 REFERENCES worksheet (worksheetid) NOT NULL,
135
problemid INT4 REFERENCES problem (problemid) NOT NULL,
137
PRIMARY KEY (worksheetid, problemid)
140
CREATE TABLE problem_tag (
141
problemid INT4 REFERENCES problem (problemid),
142
tag VARCHAR NOT NULL,
144
standard BOOLEAN NOT NULL,
145
added_by INT4 REFERENCES login (loginid) NOT NULL,
146
date TIMESTAMP NOT NULL,
147
PRIMARY KEY (problemid,added_by,tag)
150
CREATE TABLE problem_test_case (
151
problemid INT4 REFERENCES problem (problemid) NOT NULL,
152
testcaseid SERIAL UNIQUE NOT NULL,
155
visibility VARCHAR CHECK (visibility in ('public', 'protected', 'private'))
158
CREATE TABLE problem_test_case_tag (
159
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
160
tag VARCHAR NOT NULL,
162
standard BOOLEAN NOT NULL,
163
added_by INT4 REFERENCES login (loginid) NOT NULL,
164
date TIMESTAMP NOT NULL,
165
PRIMARY KEY (testcaseid,added_by,tag)
168
CREATE TABLE problem_attempt (
169
problemid INT4 REFERENCES problem (problemid) NOT NULL,
225
170
loginid INT4 REFERENCES login (loginid) NOT NULL,
226
ws_ex_id INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
227
171
date TIMESTAMP NOT NULL,
228
attempt TEXT NOT NULL,
172
attempt VARCHAR NOT NULL,
229
173
complete BOOLEAN NOT NULL,
230
active BOOLEAN NOT NULL DEFAULT true,
231
PRIMARY KEY (loginid, ws_ex_id, date)
234
CREATE TABLE exercise_save (
235
loginid INT4 REFERENCES login (loginid) NOT NULL,
236
ws_ex_id INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
237
date TIMESTAMP NOT NULL,
239
PRIMARY KEY (loginid, ws_ex_id)
242
CREATE TABLE test_suite (
243
suiteid SERIAL PRIMARY KEY,
244
exerciseid TEXT REFERENCES exercise (identifier) NOT NULL,
251
CREATE TABLE test_case (
252
testid SERIAL PRIMARY KEY,
253
suiteid INT4 REFERENCES test_suite (suiteid) NOT NULL,
260
CREATE TABLE suite_variable (
261
varid SERIAL PRIMARY KEY,
262
suiteid INT4 REFERENCES test_suite (suiteid) NOT NULL,
265
var_type TEXT NOT NULL,
269
CREATE TABLE test_case_part (
270
partid SERIAL PRIMARY KEY,
271
testid INT4 REFERENCES test_case (testid) NOT NULL,
272
part_type TEXT NOT NULL,
174
PRIMARY KEY (problemid,loginid,date)
177
CREATE TABLE problem_save (
178
problemid INT4 REFERENCES problem (problemid) NOT NULL,
179
loginid INT4 REFERENCES login (loginid) NOT NULL,
180
date TIMESTAMP NOT NULL,
181
text VARCHAR NOT NULL,
182
PRIMARY KEY (problemid,loginid)
185
CREATE INDEX problem_attempt_index ON problem_attempt (problemid, loginid);
187
CREATE TABLE problem_attempt_breakdown (
188
problemid INT4 REFERENCES problem (problemid) NOT NULL,
189
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
190
loginid INT4 REFERENCES login (loginid) NOT NULL,
191
date TIMESTAMP NOT NULL,
195
CREATE TABLE problem_prerequisite (
196
parent INT4 REFERENCES problem (problemid) NOT NULL,
197
child INT4 REFERENCES problem (problemid) NOT NULL,
198
PRIMARY KEY (parent,child)