4
DROP TABLE group_members;
9
login varchar(80) PRIMARY KEY, -- login id
14
groupid varchar(18) PRIMARY KEY, -- group name Y^4-S^9-G^3
15
nick varchar(80), -- group nickname
16
subject varchar(9), -- subject code
17
year varchar(4) -- when
20
CREATE TABLE group_members (
21
login varchar(80) REFERENCES users (login),
22
groupid varchar(18) REFERENCES groups (groupid)
1
CREATE SEQUENCE login_unixid_seq MINVALUE 1000 MAXVALUE 29999 START WITH 5000;
4
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
rolenm VARCHAR NOT NULL CHECK (rolenm in ('anyone', 'student',
11
'lecturer', 'admin')),
12
unixid INT UNIQUE DEFAULT nextval('login_unixid_seq') NOT NULL,
13
nick VARCHAR NOT NULL,
19
fullname VARCHAR NOT NULL,
20
studentid VARCHAR, -- may be null
27
CREATE TABLE subject (
28
subjectid SERIAL PRIMARY KEY NOT NULL,
29
subj_code VARCHAR UNIQUE NOT NULL,
30
subj_name VARCHAR NOT NULL,
31
subj_short_name VARCHAR UNIQUE NOT NULL,
35
CREATE TABLE semester (
36
semesterid SERIAL PRIMARY KEY NOT NULL,
37
year CHAR(4) NOT NULL,
38
semester CHAR(1) NOT NULL,
40
UNIQUE (year, semester)
43
CREATE OR REPLACE FUNCTION deactivate_semester_enrolments_update()
46
IF OLD.active = true AND NEW.active = false THEN
47
UPDATE enrolment SET active=false WHERE offeringid IN (
48
SELECT offeringid FROM offering WHERE offering.semesterid = NEW.semesterid);
54
CREATE TRIGGER deactivate_semester_enrolments
55
AFTER UPDATE ON semester
56
FOR EACH ROW EXECUTE PROCEDURE deactivate_semester_enrolments_update();
58
CREATE TABLE offering (
59
offeringid SERIAL PRIMARY KEY NOT NULL,
60
subject INT4 REFERENCES subject (subjectid) NOT NULL,
61
semesterid INTEGER REFERENCES semester (semesterid) NOT NULL,
62
groups_student_permissions VARCHAR NOT NULL DEFAULT 'none',
63
CHECK (groups_student_permissions in ('none', 'invite', 'create')),
64
UNIQUE (subject, semesterid)
67
-- Projects and groups
68
-- -------------------
70
CREATE TABLE project_set (
71
projectsetid SERIAL PRIMARY KEY NOT NULL,
72
offeringid INTEGER REFERENCES offering (offeringid) NOT NULL,
73
max_students_per_group INTEGER NOT NULL DEFAULT 4
76
CREATE TABLE project (
77
projectid SERIAL PRIMARY KEY NOT NULL,
80
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
84
CREATE TABLE project_group (
85
groupnm VARCHAR NOT NULL,
86
groupid SERIAL PRIMARY KEY NOT NULL,
87
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
89
createdby INT4 REFERENCES login (loginid) NOT NULL,
90
epoch TIMESTAMP NOT NULL,
91
UNIQUE (projectsetid, groupnm)
94
CREATE OR REPLACE FUNCTION check_group_namespacing_insertupdate()
99
SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
100
PERFORM 1 FROM project_group, project_set WHERE project_group.projectsetid = project_set.projectsetid AND project_group.groupnm = NEW.groupnm;
102
RAISE EXCEPTION ''a project group named % already exists in offering ID %'', NEW.groupnm, oid;
106
' LANGUAGE 'plpgsql';
108
CREATE TRIGGER check_group_namespacing
109
BEFORE INSERT OR UPDATE ON project_group
110
FOR EACH ROW EXECUTE PROCEDURE check_group_namespacing_insertupdate();
112
CREATE TABLE group_invitation (
113
loginid INT4 REFERENCES login (loginid) NOT NULL,
114
groupid INT4 REFERENCES project_group (groupid) NOT NULL,
115
inviter INT4 REFERENCES login (loginid) NOT NULL,
116
invited TIMESTAMP NOT NULL,
118
UNIQUE (loginid,groupid)
121
CREATE TABLE group_member (
122
loginid INT4 REFERENCES login (loginid),
123
groupid INT4 REFERENCES project_group (groupid),
124
PRIMARY KEY (loginid,groupid)
25
127
CREATE TABLE enrolment (
26
login varchar(80) REFERENCES users (login),
32
login varchar(80) REFERENCES users (login),
36
INSERT INTO users (login,nick) values ('conway', 'Tom');
37
INSERT INTO roles (login,role) values ('conway', 'student');
38
INSERT INTO users (login,nick) values ('apeel', 'Andrew');
39
INSERT INTO roles (login,role) values ('apeel', 'student');
40
INSERT INTO users (login,nick) values ('mgiuca', 'Matt');
41
INSERT INTO roles (login,role) values ('mgiuca', 'tutor');
42
INSERT INTO users (login,nick) values ('sb', 'Steven');
43
INSERT INTO roles (login,role) values ('sb', 'lecturer');
44
INSERT INTO users (login,nick) values ('mpp', 'Mike');
45
INSERT INTO roles (login,role) values ('mpp', 'student');
46
INSERT INTO users (login,nick) values ('ivo', 'Ivo');
47
INSERT INTO roles (login,role) values ('ivo', 'admin');
49
INSERT INTO groups (groupid, nick, subject, year) values ('2007-INFO10001-321', 'Purple Alert', 'INFO10001', '2008');
50
INSERT INTO groups (groupid, nick, subject, year) values ('2007-INFO10001-322', 'Blind Illuminati', 'INFO10001', '2008');
52
INSERT INTO group_members (login,groupid) values ('conway', '2007-INFO10001-321');
53
INSERT INTO group_members (login,groupid) values ('apeel', '2007-INFO10001-321');
54
INSERT INTO group_members (login,groupid) values ('mgiuca', '2007-INFO10001-321');
55
INSERT INTO group_members (login,groupid) values ('sb', '2007-INFO10001-321');
56
INSERT INTO group_members (login,groupid) values ('mpp', '2007-INFO10001-322');
57
INSERT INTO group_members (login,groupid) values ('ivo', '2007-INFO10001-322');
59
INSERT INTO enrolment (login,subject,year) values ('conway' , 'INFO10001', '2008');
60
INSERT INTO enrolment (login,subject,year) values ('apeel' , 'INFO10001', '2008');
61
INSERT INTO enrolment (login,subject,year) values ('mgiuca' , 'INFO10001', '2008');
62
INSERT INTO enrolment (login,subject,year) values ('sb' , 'INFO10001', '2008');
63
INSERT INTO enrolment (login,subject,year) values ('mpp' , 'INFO10001', '2008');
64
INSERT INTO enrolment (login,subject,year) values ('ivo' , 'INFO10001', '2008');
128
loginid INT4 REFERENCES login (loginid),
129
offeringid INT4 REFERENCES offering (offeringid),
131
special_result VARCHAR,
133
special_supp_result VARCHAR,
135
active BOOL NOT NULL DEFAULT true,
136
PRIMARY KEY (loginid,offeringid)
139
CREATE OR REPLACE FUNCTION confirm_active_semester_insertupdate()
144
SELECT semester.active INTO active FROM offering, semester WHERE offeringid=NEW.offeringid AND semester.semesterid = offering.semesterid;
145
IF NOT active AND NEW.active = true THEN
146
RAISE EXCEPTION ''cannot have active enrolment for % in offering %, as the semester is inactive'', NEW.loginid, NEW.offeringid;
150
' LANGUAGE 'plpgsql';
152
CREATE TRIGGER confirm_active_semester
153
BEFORE INSERT OR UPDATE ON enrolment
154
FOR EACH ROW EXECUTE PROCEDURE confirm_active_semester_insertupdate();
156
CREATE TABLE assessed (
157
assessedid SERIAL PRIMARY KEY NOT NULL,
158
loginid INT4 REFERENCES login (loginid),
159
groupid INT4 REFERENCES project_group (groupid),
160
projectid INT4 REFERENCES project (projectid) NOT NULL,
161
-- exactly one of loginid and groupid must be non-null
162
CHECK ((loginid IS NOT NULL AND groupid IS NULL)
163
OR (loginid IS NULL AND groupid IS NOT NULL))
166
CREATE TABLE project_extension (
167
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
168
deadline TIMESTAMP NOT NULL,
169
approver INT4 REFERENCES login (loginid) NOT NULL,
173
CREATE TABLE project_submission (
174
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
175
path VARCHAR NOT NULL,
176
revision INT4 NOT NULL
179
CREATE TABLE project_mark (
180
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
182
marker INT4 REFERENCES login (loginid) NOT NULL,
192
CREATE TABLE problem (
193
problemid SERIAL PRIMARY KEY NOT NULL,
194
identifier VARCHAR UNIQUE NOT NULL,
198
CREATE TABLE worksheet (
199
worksheetid SERIAL PRIMARY KEY NOT NULL,
200
subject VARCHAR NOT NULL,
201
identifier VARCHAR NOT NULL,
204
UNIQUE (subject, identifier)
207
CREATE TABLE worksheet_problem (
208
worksheetid INT4 REFERENCES worksheet (worksheetid) NOT NULL,
209
problemid INT4 REFERENCES problem (problemid) NOT NULL,
211
PRIMARY KEY (worksheetid, problemid)
214
CREATE TABLE problem_tag (
215
problemid INT4 REFERENCES problem (problemid),
216
tag VARCHAR NOT NULL,
218
standard BOOLEAN NOT NULL,
219
added_by INT4 REFERENCES login (loginid) NOT NULL,
220
date TIMESTAMP NOT NULL,
221
PRIMARY KEY (problemid,added_by,tag)
224
CREATE TABLE problem_test_case (
225
problemid INT4 REFERENCES problem (problemid) NOT NULL,
226
testcaseid SERIAL UNIQUE NOT NULL,
229
visibility VARCHAR CHECK (visibility in ('public', 'protected', 'private'))
232
CREATE TABLE problem_test_case_tag (
233
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
234
tag VARCHAR NOT NULL,
236
standard BOOLEAN NOT NULL,
237
added_by INT4 REFERENCES login (loginid) NOT NULL,
238
date TIMESTAMP NOT NULL,
239
PRIMARY KEY (testcaseid,added_by,tag)
242
CREATE TABLE problem_attempt (
243
problemid INT4 REFERENCES problem (problemid) NOT NULL,
244
loginid INT4 REFERENCES login (loginid) NOT NULL,
245
date TIMESTAMP NOT NULL,
246
attempt VARCHAR NOT NULL,
247
complete BOOLEAN NOT NULL,
248
active BOOLEAN NOT NULL DEFAULT true,
249
PRIMARY KEY (problemid,loginid,date)
252
CREATE TABLE problem_save (
253
problemid INT4 REFERENCES problem (problemid) NOT NULL,
254
loginid INT4 REFERENCES login (loginid) NOT NULL,
255
date TIMESTAMP NOT NULL,
256
text VARCHAR NOT NULL,
257
PRIMARY KEY (problemid,loginid)
260
CREATE INDEX problem_attempt_index ON problem_attempt (problemid, loginid);
262
CREATE TABLE problem_attempt_breakdown (
263
problemid INT4 REFERENCES problem (problemid) NOT NULL,
264
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
265
loginid INT4 REFERENCES login (loginid) NOT NULL,
266
date TIMESTAMP NOT NULL,
270
CREATE TABLE problem_prerequisite (
271
parent INT4 REFERENCES problem (problemid) NOT NULL,
272
child INT4 REFERENCES problem (problemid) NOT NULL,
273
PRIMARY KEY (parent,child)