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)
2
CREATE SEQUENCE login_unixid_seq MINVALUE 1000 MAXVALUE 29999 START WITH 5000;
5
loginid SERIAL PRIMARY KEY NOT NULL,
6
login VARCHAR UNIQUE NOT NULL,
8
state VARCHAR NOT NULL CHECK (state in ('no_agreement', 'pending',
9
'enabled', 'disabled'))
10
DEFAULT 'no_agreement',
11
admin BOOLEAN NOT NULL DEFAULT false,
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
34
CREATE TABLE semester (
35
semesterid SERIAL PRIMARY KEY NOT NULL,
36
year CHAR(4) NOT NULL,
37
semester CHAR(1) NOT NULL,
38
state TEXT NOT NULL CHECK (state IN ('disabled', 'past',
39
'current', 'future')) DEFAULT 'current',
40
UNIQUE (year, semester)
43
CREATE TABLE offering (
44
offeringid SERIAL PRIMARY KEY NOT NULL,
45
subject INT4 REFERENCES subject (subjectid) NOT NULL,
46
semesterid INTEGER REFERENCES semester (semesterid) NOT NULL,
49
groups_student_permissions VARCHAR NOT NULL DEFAULT 'none',
50
CHECK (groups_student_permissions in ('none', 'invite', 'create')),
51
UNIQUE (subject, semesterid)
54
-- Projects and groups
55
-- -------------------
57
CREATE TABLE project_set (
58
projectsetid SERIAL PRIMARY KEY NOT NULL,
59
offeringid INTEGER REFERENCES offering (offeringid) NOT NULL,
60
max_students_per_group INTEGER
63
CREATE TABLE project (
64
projectid SERIAL PRIMARY KEY NOT NULL,
65
short_name TEXT NOT NULL,
69
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
70
deadline TIMESTAMP NOT NULL
73
CREATE OR REPLACE FUNCTION check_project_namespacing_insertupdate()
78
IF TG_OP = ''UPDATE'' THEN
79
IF NEW.projectsetid = OLD.projectsetid AND NEW.short_name = OLD.short_name THEN
83
SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
84
PERFORM 1 FROM project, project_set
85
WHERE project_set.offeringid = oid AND
86
project.projectsetid = project_set.projectsetid AND
87
project.short_name = NEW.short_name;
89
RAISE EXCEPTION ''a project named % already exists in offering ID %'', NEW.short_name, oid;
95
CREATE TRIGGER check_project_namespacing
96
BEFORE INSERT OR UPDATE ON project
97
FOR EACH ROW EXECUTE PROCEDURE check_project_namespacing_insertupdate();
99
CREATE TABLE project_group (
100
groupnm VARCHAR NOT NULL,
101
groupid SERIAL PRIMARY KEY NOT NULL,
102
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
104
createdby INT4 REFERENCES login (loginid) NOT NULL,
105
epoch TIMESTAMP NOT NULL,
106
UNIQUE (projectsetid, groupnm)
109
CREATE OR REPLACE FUNCTION check_group_namespacing_insertupdate()
114
SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
115
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;
117
RAISE EXCEPTION ''a project group named % already exists in offering ID %'', NEW.groupnm, oid;
121
' LANGUAGE 'plpgsql';
123
CREATE TRIGGER check_group_namespacing
124
BEFORE INSERT OR UPDATE ON project_group
125
FOR EACH ROW EXECUTE PROCEDURE check_group_namespacing_insertupdate();
127
CREATE TABLE group_invitation (
128
loginid INT4 REFERENCES login (loginid) NOT NULL,
129
groupid INT4 REFERENCES project_group (groupid) NOT NULL,
130
inviter INT4 REFERENCES login (loginid) NOT NULL,
131
invited TIMESTAMP NOT NULL,
133
UNIQUE (loginid,groupid)
136
CREATE TABLE group_member (
137
loginid INT4 REFERENCES login (loginid),
138
groupid INT4 REFERENCES project_group (groupid),
139
PRIMARY KEY (loginid,groupid)
25
142
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');
143
loginid INT4 REFERENCES login (loginid),
144
offeringid INT4 REFERENCES offering (offeringid),
145
role TEXT NOT NULL CHECK (role IN ('student', 'tutor',
146
'lecturer')) DEFAULT 'student',
148
special_result VARCHAR,
150
special_supp_result VARCHAR,
152
active BOOL NOT NULL DEFAULT true,
153
PRIMARY KEY (loginid,offeringid)
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
extensionid SERIAL PRIMARY KEY,
168
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
169
deadline TIMESTAMP NOT NULL,
170
approver INT4 REFERENCES login (loginid) NOT NULL,
174
CREATE TABLE project_submission (
175
submissionid SERIAL PRIMARY KEY,
176
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
177
path VARCHAR NOT NULL,
178
revision INT4 NOT NULL,
179
date_submitted TIMESTAMP NOT NULL,
180
submitter INT4 REFERENCES login (loginid) NOT NULL
183
CREATE TABLE project_mark (
184
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
186
marker INT4 REFERENCES login (loginid) NOT NULL,
195
CREATE TABLE exercise (
196
identifier TEXT PRIMARY KEY,
205
CREATE TABLE worksheet (
206
worksheetid SERIAL PRIMARY KEY,
207
offeringid INT4 REFERENCES offering (offeringid) NOT NULL,
208
identifier TEXT NOT NULL,
211
assessable BOOLEAN NOT NULL,
212
seq_no INT4 NOT NULL,
213
format TEXT NOT NUll,
214
UNIQUE (offeringid, identifier)
217
CREATE TABLE worksheet_exercise (
218
ws_ex_id SERIAL PRIMARY KEY,
219
worksheetid INT4 REFERENCES worksheet (worksheetid) NOT NULL,
220
exerciseid TEXT REFERENCES exercise (identifier) NOT NULL,
221
seq_no INT4 NOT NULL,
222
active BOOLEAN NOT NULL DEFAULT true,
223
optional BOOLEAN NOT NULL,
224
UNIQUE (worksheetid, exerciseid)
227
CREATE TABLE exercise_attempt (
228
loginid INT4 REFERENCES login (loginid) NOT NULL,
229
ws_ex_id INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
230
date TIMESTAMP NOT NULL,
231
attempt TEXT NOT NULL,
232
complete BOOLEAN NOT NULL,
233
active BOOLEAN NOT NULL DEFAULT true,
234
PRIMARY KEY (loginid, ws_ex_id, date)
237
CREATE TABLE exercise_save (
238
loginid INT4 REFERENCES login (loginid) NOT NULL,
239
ws_ex_id INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
240
date TIMESTAMP NOT NULL,
242
PRIMARY KEY (loginid, ws_ex_id)
245
CREATE TABLE test_suite (
246
suiteid SERIAL PRIMARY KEY,
247
exerciseid TEXT REFERENCES exercise (identifier) NOT NULL,
254
CREATE TABLE test_case (
255
testid SERIAL PRIMARY KEY,
256
suiteid INT4 REFERENCES test_suite (suiteid) NOT NULL,
263
CREATE TABLE suite_variable (
264
varid SERIAL PRIMARY KEY,
265
suiteid INT4 REFERENCES test_suite (suiteid) NOT NULL,
268
var_type TEXT NOT NULL,
272
CREATE TABLE test_case_part (
273
partid SERIAL PRIMARY KEY,
274
testid INT4 REFERENCES test_case (testid) NOT NULL,
275
part_type TEXT NOT NULL,