1
CREATE SEQUENCE login_unixid_seq MINVALUE 1000 MAXVALUE 29999 START WITH 5000;
1
-- We need a users database to do authorization, manage groups, &c
6
DROP TABLE group_members;
11
login VARCHAR UNIQUE NOT NULL,
4
12
loginid SERIAL PRIMARY KEY NOT NULL,
5
login VARCHAR UNIQUE NOT NULL,
7
state VARCHAR NOT NULL CHECK (state in ('no_agreement', 'pending',
8
'enabled', 'disabled'))
9
DEFAULT 'no_agreement',
10
rolenm VARCHAR NOT NULL CHECK (rolenm in ('anyone', 'student',
12
'lecturer', 'admin')),
13
unixid INT UNIQUE DEFAULT nextval('login_unixid_seq') NOT NULL,
14
nick VARCHAR NOT NULL,
20
fullname VARCHAR NOT NULL,
21
15
studentid VARCHAR, -- may be null
26
CREATE TABLE subject (
27
subjectid SERIAL PRIMARY KEY NOT NULL,
28
subj_code VARCHAR UNIQUE NOT NULL,
29
subj_name VARCHAR NOT NULL,
30
subj_short_name VARCHAR UNIQUE NOT NULL,
34
CREATE TABLE semester (
35
semesterid SERIAL PRIMARY KEY NOT NULL,
36
year CHAR(4) NOT NULL,
37
semester CHAR(1) NOT NULL,
39
UNIQUE (year, semester)
42
CREATE OR REPLACE FUNCTION deactivate_semester_enrolments_update()
45
IF OLD.active = true AND NEW.active = false THEN
46
UPDATE enrolment SET active=false WHERE offeringid IN (
47
SELECT offeringid FROM offering WHERE offering.semesterid = NEW.semesterid);
53
CREATE TRIGGER deactivate_semester_enrolments
54
AFTER UPDATE ON semester
55
FOR EACH ROW EXECUTE PROCEDURE deactivate_semester_enrolments_update();
57
CREATE TABLE offering (
58
offeringid SERIAL PRIMARY KEY NOT NULL,
59
subject INT4 REFERENCES subject (subjectid) NOT NULL,
60
semesterid INTEGER REFERENCES semester (semesterid) NOT NULL,
61
groups_student_permissions VARCHAR NOT NULL DEFAULT 'none',
62
CHECK (groups_student_permissions in ('none', 'invite', 'create')),
63
UNIQUE (subject, semesterid)
67
CREATE TABLE project_set (
68
projectsetid SERIAL PRIMARY KEY NOT NULL,
69
offeringid INTEGER REFERENCES offering (offeringid) NOT NULL,
70
max_students_per_group INTEGER NOT NULL DEFAULT 4
73
CREATE TABLE project (
74
projectid SERIAL PRIMARY KEY NOT NULL,
77
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
81
CREATE TABLE project_group (
82
groupnm VARCHAR NOT NULL,
19
group VARCHAR NOT NULL,
83
20
groupid SERIAL PRIMARY KEY NOT NULL,
84
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
21
offeringid INT4 REFERENCES offerings (offeringid),
86
createdby INT4 REFERENCES login (loginid) NOT NULL,
87
epoch TIMESTAMP NOT NULL,
88
UNIQUE (projectsetid, groupnm)
23
UNIQUE (offeringid, group)
91
CREATE OR REPLACE FUNCTION check_group_namespacing_insertupdate()
96
SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
97
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;
99
RAISE EXCEPTION ''a project group named % already exists in offering ID %'', NEW.groupnm, oid;
103
' LANGUAGE 'plpgsql';
105
CREATE TRIGGER check_group_namespacing
106
BEFORE INSERT OR UPDATE ON project_group
107
FOR EACH ROW EXECUTE PROCEDURE check_group_namespacing_insertupdate();
109
CREATE TABLE group_invitation (
110
loginid INT4 REFERENCES login (loginid) NOT NULL,
111
groupid INT4 REFERENCES project_group (groupid) NOT NULL,
112
inviter INT4 REFERENCES login (loginid) NOT NULL,
113
invited TIMESTAMP NOT NULL,
26
CREATE TABLE group_invitations (
27
loginid INT4 REFERENCES users (loginid),
28
groupid INT4 REFERENCES groups (groupid),
115
29
UNIQUE (loginid,groupid)
118
CREATE TABLE group_member (
119
loginid INT4 REFERENCES login (loginid),
120
groupid INT4 REFERENCES project_group (groupid),
32
CREATE TABLE group_members (
33
loginid INT4 REFERENCES users (loginid),
34
groupid INT4 REFERENCES groups (groupid),
35
projectid INT4 REFERENCES projects (projectid),
36
UNIQUE (loginid,projectid),
121
37
PRIMARY KEY (loginid,groupid)
124
40
CREATE TABLE enrolment (
125
loginid INT4 REFERENCES login (loginid),
126
offeringid INT4 REFERENCES offering (offeringid),
41
loginid INT4 REFERENCES users (loginid),
42
offeringid INT4 REFERENCES offerings (offeringid),
128
special_result VARCHAR,
130
special_supp_result VARCHAR,
132
active BOOL NOT NULL DEFAULT true,
133
46
PRIMARY KEY (loginid,offeringid)
136
CREATE OR REPLACE FUNCTION confirm_active_semester_insertupdate()
141
SELECT semester.active INTO active FROM offering, semester WHERE offeringid=NEW.offeringid AND semester.semesterid = offering.semesterid;
142
IF NOT active AND NEW.active = true THEN
143
RAISE EXCEPTION ''cannot have active enrolment for % in offering %, as the semester is inactive'', NEW.loginid, NEW.offeringid;
147
' LANGUAGE 'plpgsql';
149
CREATE TRIGGER confirm_active_semester
150
BEFORE INSERT OR UPDATE ON enrolment
151
FOR EACH ROW EXECUTE PROCEDURE confirm_active_semester_insertupdate();
153
CREATE TABLE assessed (
154
assessedid SERIAL PRIMARY KEY NOT NULL,
155
loginid INT4 REFERENCES login (loginid),
156
groupid INT4 REFERENCES project_group (groupid),
157
projectid INT4 REFERENCES project (projectid) NOT NULL,
158
-- exactly one of loginid and groupid must be non-null
159
CHECK ((loginid IS NOT NULL AND groupid IS NULL)
160
OR (loginid IS NULL AND groupid IS NOT NULL))
50
loginid INT4 PRIMARY KEY REFERENCES users (loginid),
54
CREATE TABLE projects (
55
projectid SERIAL PRIMARY KEY NOT NULL,
58
offeringid INT4 REFERENCES offerings (offeringid) NOT NULL,
163
62
CREATE TABLE project_extension (
164
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
64
projectid INT4 REFERENCES projects (projectid) NOT NULL,
165
65
deadline TIMESTAMP NOT NULL,
166
approver INT4 REFERENCES login (loginid) NOT NULL,
66
approver INT4 REFERENCES users (loginid) NOT NULL,
170
CREATE TABLE project_submission (
171
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
172
path VARCHAR NOT NULL,
173
revision INT4 NOT NULL
176
70
CREATE TABLE project_mark (
177
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
72
projectid INT4 REFERENCES projects (projectid) NOT NULL,
179
marker INT4 REFERENCES login (loginid) NOT NULL,
74
marker INT4 REFERENCES users (loginid) NOT NULL,
79
PRIMARY KEY (loginid/groupid, projectid, componentid)
187
82
CREATE TABLE problem (
188
83
problemid SERIAL PRIMARY KEY NOT NULL,
189
identifier VARCHAR UNIQUE NOT NULL,
193
CREATE TABLE worksheet (
194
worksheetid SERIAL PRIMARY KEY NOT NULL,
195
subject VARCHAR NOT NULL,
196
identifier VARCHAR NOT NULL,
199
UNIQUE (subject, identifier)
202
CREATE TABLE worksheet_problem (
203
worksheetid INT4 REFERENCES worksheet (worksheetid) NOT NULL,
204
problemid INT4 REFERENCES problem (problemid) NOT NULL,
206
PRIMARY KEY (worksheetid, problemid)
209
CREATE TABLE problem_tag (
210
problemid INT4 REFERENCES problem (problemid),
87
CREATE TABLE problem_tags (
88
problemid INT4 REFERENCES tutorial_problem (problemid),
211
89
tag VARCHAR NOT NULL,
213
standard BOOLEAN NOT NULL,
214
added_by INT4 REFERENCES login (loginid) NOT NULL,
215
date TIMESTAMP NOT NULL,
90
added_by INT4 REFERENCES users (loginid) NOT NULL,
91
when TIMESTAMP NOT NULL,
216
92
PRIMARY KEY (problemid,added_by,tag)
230
100
visibility VARCHAR CHECK (visibility in ('public', 'protected', 'private'))
233
CREATE TABLE problem_test_case_tag (
103
CREATE TABLE problem_test_case_tags (
234
104
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
235
105
tag VARCHAR NOT NULL,
236
106
description VARCHAR,
237
standard BOOLEAN NOT NULL,
238
added_by INT4 REFERENCES login (loginid) NOT NULL,
239
date TIMESTAMP NOT NULL,
107
added_by INT4 REFERENCES users (loginid) NOT NULL,
108
when TIMESTAMP NOT NULL,
240
109
PRIMARY KEY (testcaseid,added_by,tag)
243
112
CREATE TABLE problem_attempt (
244
113
problemid INT4 REFERENCES problem (problemid) NOT NULL,
245
loginid INT4 REFERENCES login (loginid) NOT NULL,
246
date TIMESTAMP NOT NULL,
114
loginid INT4 REFERENCES users (loginid) NOT NULL,
115
when TIMESTAMP NOT NULL,
247
116
attempt VARCHAR NOT NULL,
248
117
complete BOOLEAN NOT NULL,
249
active BOOLEAN NOT NULL DEFAULT true,
250
PRIMARY KEY (problemid,loginid,date)
253
CREATE TABLE problem_save (
254
problemid INT4 REFERENCES problem (problemid) NOT NULL,
255
loginid INT4 REFERENCES login (loginid) NOT NULL,
256
date TIMESTAMP NOT NULL,
257
text VARCHAR NOT NULL,
258
PRIMARY KEY (problemid,loginid)
261
CREATE INDEX problem_attempt_index ON problem_attempt (problemid, loginid);
118
PRIMARY KEY (problemid,loginid,when)
121
CREATE INDEX indexname ON problem_attempt (problemid, login);
263
123
CREATE TABLE problem_attempt_breakdown (
264
124
problemid INT4 REFERENCES problem (problemid) NOT NULL,
265
125
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
266
loginid INT4 REFERENCES login (loginid) NOT NULL,
267
date TIMESTAMP NOT NULL,
126
loginid INT4 REFERENCES users (loginid) NOT NULL,
127
when TIMESTAMP NOT NULL,
271
CREATE TABLE problem_prerequisite (
131
CREATE TABLE problem_prerequisites (
272
132
parent INT4 REFERENCES problem (problemid) NOT NULL,
273
133
child INT4 REFERENCES problem (problemid) NOT NULL,
274
134
PRIMARY KEY (parent,child)