3
-- Check that the provided name is sane for use in URLs.
4
CREATE OR REPLACE FUNCTION valid_url_name(name text) RETURNS boolean AS
7
RETURN name ~ E'^[a-z0-9][a-z0-9_\+\.\-]*$';
11
-- Just like valid_url_name, except that @ is permitted (so we can use a
12
-- reasonable subset of email addresses as usernames).
13
CREATE OR REPLACE FUNCTION valid_login_name(name text) RETURNS boolean AS
16
RETURN name ~ E'^[a-z0-9][a-z0-9@_\+\.\-]*$';
18
$$ LANGUAGE 'plpgsql';
20
CREATE SEQUENCE login_unixid_seq MINVALUE 1000 MAXVALUE 29999 START WITH 5000;
1
22
CREATE TABLE login (
2
login VARCHAR UNIQUE NOT NULL,
3
23
loginid SERIAL PRIMARY KEY NOT NULL,
4
unixid INT UNIQUE NOT NULL, -- unix user id
24
login VARCHAR UNIQUE NOT NULL CHECK (valid_login_name(login)),
9
studentid VARCHAR -- may be null
26
state VARCHAR NOT NULL CHECK (state in ('no_agreement', 'pending',
27
'enabled', 'disabled'))
28
DEFAULT 'no_agreement',
29
admin BOOLEAN NOT NULL DEFAULT false,
30
unixid INT UNIQUE DEFAULT nextval('login_unixid_seq') NOT NULL,
31
nick VARCHAR NOT NULL,
37
fullname VARCHAR NOT NULL,
38
studentid VARCHAR, -- may be null
45
CREATE TABLE subject (
46
subjectid SERIAL PRIMARY KEY NOT NULL,
47
subj_code VARCHAR UNIQUE NOT NULL,
48
subj_name VARCHAR NOT NULL,
49
subj_short_name VARCHAR UNIQUE NOT NULL CHECK (valid_url_name(subj_short_name))
52
CREATE TABLE semester (
53
semesterid SERIAL PRIMARY KEY NOT NULL,
54
year TEXT NOT NULL CHECK (valid_url_name(year)),
55
url_name TEXT NOT NULL CHECK (valid_url_name(url_name)),
57
display_name TEXT NOT NULL,
58
state TEXT NOT NULL CHECK (state IN ('disabled', 'past',
59
'current', 'future')) DEFAULT 'current',
60
UNIQUE (year, url_name),
12
64
CREATE TABLE offering (
13
65
offeringid SERIAL PRIMARY KEY NOT NULL,
14
subj_name VARCHAR NOT NULL,
15
subj_code VARCHAR NOT NULL,
16
year CHAR(4) NOT NULL,
17
semester CHAR(1) NOT NULL,
66
subject INT4 REFERENCES subject (subjectid) NOT NULL,
67
semesterid INTEGER REFERENCES semester (semesterid) NOT NULL,
70
show_worksheet_marks BOOLEAN NOT NULL DEFAULT false,
71
worksheet_cutoff TIMESTAMP,
72
groups_student_permissions VARCHAR NOT NULL DEFAULT 'none',
73
CHECK (groups_student_permissions in ('none', 'invite', 'create')),
74
UNIQUE (subject, semesterid)
77
-- Projects and groups
78
-- -------------------
80
CREATE TABLE project_set (
81
projectsetid SERIAL PRIMARY KEY NOT NULL,
82
offeringid INTEGER REFERENCES offering (offeringid) NOT NULL,
83
max_students_per_group INTEGER
21
86
CREATE TABLE project (
22
87
projectid SERIAL PRIMARY KEY NOT NULL,
25
offeringid INT4 REFERENCES offering (offeringid) NOT NULL,
88
short_name TEXT NOT NULL CHECK (valid_url_name(short_name)),
92
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
93
deadline TIMESTAMP NOT NULL
96
CREATE OR REPLACE FUNCTION check_project_namespacing_insertupdate()
101
IF TG_OP = ''UPDATE'' THEN
102
IF NEW.projectsetid = OLD.projectsetid AND NEW.short_name = OLD.short_name THEN
106
SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
107
PERFORM 1 FROM project, project_set
108
WHERE project_set.offeringid = oid AND
109
project.projectsetid = project_set.projectsetid AND
110
project.short_name = NEW.short_name;
112
RAISE EXCEPTION ''a project named % already exists in offering ID %'', NEW.short_name, oid;
116
' LANGUAGE 'plpgsql';
118
CREATE TRIGGER check_project_namespacing
119
BEFORE INSERT OR UPDATE ON project
120
FOR EACH ROW EXECUTE PROCEDURE check_project_namespacing_insertupdate();
29
122
CREATE TABLE project_group (
30
groupnm VARCHAR NOT NULL,
123
groupnm VARCHAR NOT NULL CHECK (valid_url_name(groupnm)),
31
124
groupid SERIAL PRIMARY KEY NOT NULL,
32
offeringid INT4 REFERENCES offering (offeringid),
125
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
34
127
createdby INT4 REFERENCES login (loginid) NOT NULL,
35
128
epoch TIMESTAMP NOT NULL,
36
UNIQUE (offeringid, groupnm)
129
UNIQUE (projectsetid, groupnm)
132
CREATE OR REPLACE FUNCTION check_group_namespacing_insertupdate()
137
SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
138
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;
140
RAISE EXCEPTION ''a project group named % already exists in offering ID %'', NEW.groupnm, oid;
144
' LANGUAGE 'plpgsql';
146
CREATE TRIGGER check_group_namespacing
147
BEFORE INSERT OR UPDATE ON project_group
148
FOR EACH ROW EXECUTE PROCEDURE check_group_namespacing_insertupdate();
39
150
CREATE TABLE group_invitation (
40
151
loginid INT4 REFERENCES login (loginid) NOT NULL,
41
152
groupid INT4 REFERENCES project_group (groupid) NOT NULL,
48
159
CREATE TABLE group_member (
49
160
loginid INT4 REFERENCES login (loginid),
50
161
groupid INT4 REFERENCES project_group (groupid),
51
projectid INT4 REFERENCES project (projectid),
52
UNIQUE (loginid,projectid),
53
162
PRIMARY KEY (loginid,groupid)
56
165
CREATE TABLE enrolment (
57
166
loginid INT4 REFERENCES login (loginid),
58
167
offeringid INT4 REFERENCES offering (offeringid),
168
role TEXT NOT NULL CHECK (role IN ('student', 'tutor',
169
'lecturer')) DEFAULT 'student',
60
171
special_result VARCHAR,
62
173
special_supp_result VARCHAR,
175
active BOOL NOT NULL DEFAULT true,
64
176
PRIMARY KEY (loginid,offeringid)
68
180
assessedid SERIAL PRIMARY KEY NOT NULL,
69
181
loginid INT4 REFERENCES login (loginid),
70
182
groupid INT4 REFERENCES project_group (groupid),
183
projectid INT4 REFERENCES project (projectid) NOT NULL,
71
184
-- exactly one of loginid and groupid must be non-null
72
185
CHECK ((loginid IS NOT NULL AND groupid IS NULL)
73
186
OR (loginid IS NULL AND groupid IS NOT NULL))
189
-- We must use conditional constraints here -- NULL != NULL.
190
CREATE UNIQUE INDEX assessed_loginid_key ON assessed(loginid, projectid) WHERE loginid IS NOT NULL;
191
CREATE UNIQUE INDEX assessed_groupid_key ON assessed(groupid, projectid) WHERE groupid IS NOT NULL;
76
193
CREATE TABLE project_extension (
194
extensionid SERIAL PRIMARY KEY,
77
195
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
78
projectid INT4 REFERENCES project (projectid) NOT NULL,
79
deadline TIMESTAMP NOT NULL,
80
197
approver INT4 REFERENCES login (loginid) NOT NULL,
201
CREATE TABLE project_submission (
202
submissionid SERIAL PRIMARY KEY,
203
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
204
path VARCHAR NOT NULL,
205
revision INT4 NOT NULL,
206
date_submitted TIMESTAMP NOT NULL,
207
submitter INT4 REFERENCES login (loginid) NOT NULL
84
210
CREATE TABLE project_mark (
85
211
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
86
projectid INT4 REFERENCES project (projectid) NOT NULL,
88
213
marker INT4 REFERENCES login (loginid) NOT NULL,
95
CREATE TABLE problem (
96
problemid SERIAL PRIMARY KEY NOT NULL,
100
CREATE TABLE problem_tag (
101
problemid INT4 REFERENCES problem (problemid),
102
tag VARCHAR NOT NULL,
104
standard BOOLEAN NOT NULL,
105
added_by INT4 REFERENCES login (loginid) NOT NULL,
106
date TIMESTAMP NOT NULL,
107
PRIMARY KEY (problemid,added_by,tag)
110
CREATE TABLE problem_test_case (
111
problemid INT4 REFERENCES problem (problemid) NOT NULL,
112
testcaseid SERIAL UNIQUE NOT NULL,
115
visibility VARCHAR CHECK (visibility in ('public', 'protected', 'private'))
118
CREATE TABLE problem_test_case_tag (
119
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
120
tag VARCHAR NOT NULL,
122
standard BOOLEAN NOT NULL,
123
added_by INT4 REFERENCES login (loginid) NOT NULL,
124
date TIMESTAMP NOT NULL,
125
PRIMARY KEY (testcaseid,added_by,tag)
128
CREATE TABLE problem_attempt (
129
problemid INT4 REFERENCES problem (problemid) NOT NULL,
222
CREATE TABLE exercise (
223
identifier TEXT PRIMARY KEY CHECK (valid_url_name(identifier)),
226
description_xhtml_cache TEXT,
233
CREATE TABLE worksheet (
234
worksheetid SERIAL PRIMARY KEY,
235
offeringid INT4 REFERENCES offering (offeringid) NOT NULL,
236
identifier TEXT NOT NULL CHECK (valid_url_name(identifier)),
239
data_xhtml_cache TEXT,
240
assessable BOOLEAN NOT NULL,
241
published BOOLEAN NOT NULL DEFAULT true,
242
seq_no INT4 NOT NULL,
243
format TEXT NOT NUll,
244
UNIQUE (offeringid, identifier)
247
CREATE TABLE worksheet_exercise (
248
ws_ex_id SERIAL PRIMARY KEY,
249
worksheetid INT4 REFERENCES worksheet (worksheetid) NOT NULL,
250
exerciseid TEXT REFERENCES exercise (identifier) NOT NULL,
251
seq_no INT4 NOT NULL,
252
active BOOLEAN NOT NULL DEFAULT true,
253
optional BOOLEAN NOT NULL,
254
UNIQUE (worksheetid, exerciseid)
257
CREATE TABLE exercise_attempt (
130
258
loginid INT4 REFERENCES login (loginid) NOT NULL,
259
ws_ex_id INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
131
260
date TIMESTAMP NOT NULL,
132
attempt VARCHAR NOT NULL,
261
attempt TEXT NOT NULL,
133
262
complete BOOLEAN NOT NULL,
134
PRIMARY KEY (problemid,loginid,date)
263
active BOOLEAN NOT NULL DEFAULT true,
264
PRIMARY KEY (loginid, ws_ex_id, date)
137
CREATE INDEX problem_attempt_index ON problem_attempt (problemid, loginid);
139
CREATE TABLE problem_attempt_breakdown (
140
problemid INT4 REFERENCES problem (problemid) NOT NULL,
141
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
267
CREATE TABLE exercise_save (
142
268
loginid INT4 REFERENCES login (loginid) NOT NULL,
269
ws_ex_id INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
143
270
date TIMESTAMP NOT NULL,
147
CREATE TABLE problem_prerequisite (
148
parent INT4 REFERENCES problem (problemid) NOT NULL,
149
child INT4 REFERENCES problem (problemid) NOT NULL,
150
PRIMARY KEY (parent,child)
272
PRIMARY KEY (loginid, ws_ex_id)
275
CREATE TABLE test_suite (
276
suiteid SERIAL PRIMARY KEY,
277
exerciseid TEXT REFERENCES exercise (identifier) NOT NULL,
284
CREATE TABLE test_case (
285
testid SERIAL PRIMARY KEY,
286
suiteid INT4 REFERENCES test_suite (suiteid) NOT NULL,
293
CREATE TABLE suite_variable (
294
varid SERIAL PRIMARY KEY,
295
suiteid INT4 REFERENCES test_suite (suiteid) NOT NULL,
298
var_type TEXT NOT NULL,
302
CREATE TABLE test_case_part (
303
partid SERIAL PRIMARY KEY,
304
testid INT4 REFERENCES test_case (testid) NOT NULL,
305
part_type TEXT NOT NULL,