3
CREATE OR REPLACE FUNCTION valid_url_name(name text) RETURNS boolean AS
6
RETURN name ~ E'^[a-z0-9][a-z0-9_\+\.\-]*$';
10
CREATE OR REPLACE FUNCTION valid_login_name(name text) RETURNS boolean AS
13
RETURN name ~ E'^[a-z0-9][a-z0-9@_\+\.\-]*$';
15
$$ LANGUAGE 'plpgsql';
17
CREATE SEQUENCE login_unixid_seq MINVALUE 1000 MAXVALUE 29999 START WITH 5000;
20
loginid SERIAL PRIMARY KEY NOT NULL,
21
login VARCHAR UNIQUE NOT NULL CHECK (valid_login_name(login)),
23
state VARCHAR NOT NULL CHECK (state in ('no_agreement', 'pending',
24
'enabled', 'disabled'))
25
DEFAULT 'no_agreement',
26
admin BOOLEAN NOT NULL DEFAULT false,
27
unixid INT UNIQUE DEFAULT nextval('login_unixid_seq') NOT NULL,
28
nick VARCHAR NOT NULL,
34
fullname VARCHAR NOT NULL,
35
studentid VARCHAR, -- may be null
40
CREATE TABLE subject (
41
subjectid SERIAL PRIMARY KEY NOT NULL,
42
subj_code VARCHAR UNIQUE NOT NULL,
43
subj_name VARCHAR NOT NULL,
44
subj_short_name VARCHAR UNIQUE NOT NULL CHECK (valid_url_name(subj_short_name))
47
CREATE TABLE semester (
48
semesterid SERIAL PRIMARY KEY NOT NULL,
49
year TEXT NOT NULL CHECK (valid_url_name(year)),
50
url_name TEXT NOT NULL CHECK (valid_url_name(url_name)),
52
display_name TEXT NOT NULL,
53
state TEXT NOT NULL CHECK (state IN ('disabled', 'past',
54
'current', 'future')) DEFAULT 'current',
55
UNIQUE (year, url_name),
59
CREATE TABLE offering (
60
offeringid SERIAL PRIMARY KEY NOT NULL,
61
subject INT4 REFERENCES subject (subjectid) NOT NULL,
62
semesterid INTEGER REFERENCES semester (semesterid) NOT NULL,
65
show_worksheet_marks BOOLEAN NOT NULL DEFAULT false,
66
worksheet_cutoff TIMESTAMP,
67
groups_student_permissions VARCHAR NOT NULL DEFAULT 'none',
68
CHECK (groups_student_permissions in ('none', 'invite', 'create')),
69
UNIQUE (subject, semesterid)
73
CREATE TABLE project_set (
74
projectsetid SERIAL PRIMARY KEY NOT NULL,
75
offeringid INTEGER REFERENCES offering (offeringid) NOT NULL,
76
max_students_per_group INTEGER
79
CREATE TABLE project (
80
projectid SERIAL PRIMARY KEY NOT NULL,
81
short_name TEXT NOT NULL CHECK (valid_url_name(short_name)),
85
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
86
deadline TIMESTAMP NOT NULL
89
CREATE OR REPLACE FUNCTION check_project_namespacing_insertupdate()
94
IF TG_OP = ''UPDATE'' THEN
95
IF NEW.projectsetid = OLD.projectsetid AND NEW.short_name = OLD.short_name THEN
99
SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
100
PERFORM 1 FROM project, project_set
101
WHERE project_set.offeringid = oid AND
102
project.projectsetid = project_set.projectsetid AND
103
project.short_name = NEW.short_name;
105
RAISE EXCEPTION ''a project named % already exists in offering ID %'', NEW.short_name, oid;
109
' LANGUAGE 'plpgsql';
111
CREATE TRIGGER check_project_namespacing
112
BEFORE INSERT OR UPDATE ON project
113
FOR EACH ROW EXECUTE PROCEDURE check_project_namespacing_insertupdate();
115
CREATE TABLE project_group (
116
groupnm VARCHAR NOT NULL CHECK (valid_url_name(groupnm)),
117
groupid SERIAL PRIMARY KEY NOT NULL,
118
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
120
createdby INT4 REFERENCES login (loginid) NOT NULL,
121
epoch TIMESTAMP NOT NULL,
122
UNIQUE (projectsetid, groupnm)
125
CREATE OR REPLACE FUNCTION check_group_namespacing_insertupdate()
130
SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
131
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;
133
RAISE EXCEPTION ''a project group named % already exists in offering ID %'', NEW.groupnm, oid;
137
' LANGUAGE 'plpgsql';
139
CREATE TRIGGER check_group_namespacing
140
BEFORE INSERT OR UPDATE ON project_group
141
FOR EACH ROW EXECUTE PROCEDURE check_group_namespacing_insertupdate();
143
CREATE TABLE group_invitation (
144
loginid INT4 REFERENCES login (loginid) NOT NULL,
145
groupid INT4 REFERENCES project_group (groupid) NOT NULL,
146
inviter INT4 REFERENCES login (loginid) NOT NULL,
147
invited TIMESTAMP NOT NULL,
149
UNIQUE (loginid,groupid)
152
CREATE TABLE group_member (
153
loginid INT4 REFERENCES login (loginid),
154
groupid INT4 REFERENCES project_group (groupid),
155
PRIMARY KEY (loginid,groupid)
1
-- We need a users database to do authorization, manage groups, &c
6
DROP TABLE group_members;
11
login varchar(80) PRIMARY KEY, -- login id
16
groupid varchar(18) PRIMARY KEY, -- group name Y^4-S^9-G^3
17
nick varchar(80), -- group nickname
18
subject varchar(9), -- subject code
19
year varchar(4) -- when
22
CREATE TABLE group_members (
23
login varchar(80) REFERENCES users (login),
24
groupid varchar(18) REFERENCES groups (groupid)
158
27
CREATE TABLE enrolment (
159
loginid INT4 REFERENCES login (loginid),
160
offeringid INT4 REFERENCES offering (offeringid),
161
role TEXT NOT NULL CHECK (role IN ('student', 'tutor',
162
'lecturer')) DEFAULT 'student',
164
special_result VARCHAR,
166
special_supp_result VARCHAR,
168
active BOOL NOT NULL DEFAULT true,
169
PRIMARY KEY (loginid,offeringid)
172
CREATE TABLE assessed (
173
assessedid SERIAL PRIMARY KEY NOT NULL,
174
loginid INT4 REFERENCES login (loginid),
175
groupid INT4 REFERENCES project_group (groupid),
176
projectid INT4 REFERENCES project (projectid) NOT NULL,
177
-- exactly one of loginid and groupid must be non-null
178
CHECK ((loginid IS NOT NULL AND groupid IS NULL)
179
OR (loginid IS NULL AND groupid IS NOT NULL))
182
CREATE UNIQUE INDEX assessed_loginid_key ON assessed(loginid, projectid) WHERE loginid IS NOT NULL;
183
CREATE UNIQUE INDEX assessed_groupid_key ON assessed(groupid, projectid) WHERE groupid IS NOT NULL;
185
CREATE TABLE project_extension (
186
extensionid SERIAL PRIMARY KEY,
187
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
189
approver INT4 REFERENCES login (loginid) NOT NULL,
193
CREATE TABLE project_submission (
194
submissionid SERIAL PRIMARY KEY,
195
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
196
path VARCHAR NOT NULL,
197
revision INT4 NOT NULL,
198
date_submitted TIMESTAMP NOT NULL,
199
submitter INT4 REFERENCES login (loginid) NOT NULL
202
CREATE TABLE project_mark (
203
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
205
marker INT4 REFERENCES login (loginid) NOT NULL,
212
CREATE TABLE exercise (
213
identifier TEXT PRIMARY KEY CHECK (valid_url_name(identifier)),
216
description_xhtml_cache TEXT,
223
CREATE TABLE worksheet (
224
worksheetid SERIAL PRIMARY KEY,
225
offeringid INT4 REFERENCES offering (offeringid) NOT NULL,
226
identifier TEXT NOT NULL CHECK (valid_url_name(identifier)),
229
data_xhtml_cache TEXT,
230
assessable BOOLEAN NOT NULL,
231
published BOOLEAN NOT NULL DEFAULT true,
232
seq_no INT4 NOT NULL,
233
format TEXT NOT NUll,
234
UNIQUE (offeringid, identifier)
237
CREATE TABLE worksheet_exercise (
238
ws_ex_id SERIAL PRIMARY KEY,
239
worksheetid INT4 REFERENCES worksheet (worksheetid) NOT NULL,
240
exerciseid TEXT REFERENCES exercise (identifier) NOT NULL,
241
seq_no INT4 NOT NULL,
242
active BOOLEAN NOT NULL DEFAULT true,
243
optional BOOLEAN NOT NULL,
244
UNIQUE (worksheetid, exerciseid)
247
CREATE TABLE exercise_attempt (
248
loginid INT4 REFERENCES login (loginid) NOT NULL,
249
ws_ex_id INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
250
date TIMESTAMP NOT NULL,
251
attempt TEXT NOT NULL,
252
complete BOOLEAN NOT NULL,
253
active BOOLEAN NOT NULL DEFAULT true,
254
PRIMARY KEY (loginid, ws_ex_id, date)
257
CREATE TABLE exercise_save (
258
loginid INT4 REFERENCES login (loginid) NOT NULL,
259
ws_ex_id INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
260
date TIMESTAMP NOT NULL,
262
PRIMARY KEY (loginid, ws_ex_id)
265
CREATE TABLE test_suite (
266
suiteid SERIAL PRIMARY KEY,
267
exerciseid TEXT REFERENCES exercise (identifier) NOT NULL,
274
CREATE TABLE test_case (
275
testid SERIAL PRIMARY KEY,
276
suiteid INT4 REFERENCES test_suite (suiteid) NOT NULL,
283
CREATE TABLE suite_variable (
284
varid SERIAL PRIMARY KEY,
285
suiteid INT4 REFERENCES test_suite (suiteid) NOT NULL,
288
var_type TEXT NOT NULL,
292
CREATE TABLE test_case_part (
293
partid SERIAL PRIMARY KEY,
294
testid INT4 REFERENCES test_case (testid) NOT NULL,
295
part_type TEXT NOT NULL,
28
login varchar(80) REFERENCES users (login),
34
login varchar(80) REFERENCES users (login),
38
INSERT INTO users (login,nick) values ('conway', 'Tom');
39
INSERT INTO roles (login,role) values ('conway', 'student');
40
INSERT INTO users (login,nick) values ('apeel', 'Andrew');
41
INSERT INTO roles (login,role) values ('apeel', 'student');
42
INSERT INTO users (login,nick) values ('mgiuca', 'Matt');
43
INSERT INTO roles (login,role) values ('mgiuca', 'tutor');
44
INSERT INTO users (login,nick) values ('sb', 'Steven');
45
INSERT INTO roles (login,role) values ('sb', 'lecturer');
46
INSERT INTO users (login,nick) values ('mpp', 'Mike');
47
INSERT INTO roles (login,role) values ('mpp', 'student');
48
INSERT INTO users (login,nick) values ('ivo', 'Ivo');
49
INSERT INTO roles (login,role) values ('ivo', 'admin');
51
INSERT INTO groups (groupid, nick, subject, year) values ('2007-INFO10001-321', 'Purple Alert', 'INFO10001', '2008');
52
INSERT INTO groups (groupid, nick, subject, year) values ('2007-INFO10001-322', 'Blind Illuminati', 'INFO10001', '2008');
54
INSERT INTO group_members (login,groupid) values ('conway', '2007-INFO10001-321');
55
INSERT INTO group_members (login,groupid) values ('apeel', '2007-INFO10001-321');
56
INSERT INTO group_members (login,groupid) values ('mgiuca', '2007-INFO10001-321');
57
INSERT INTO group_members (login,groupid) values ('sb', '2007-INFO10001-321');
58
INSERT INTO group_members (login,groupid) values ('mpp', '2007-INFO10001-322');
59
INSERT INTO group_members (login,groupid) values ('ivo', '2007-INFO10001-322');
61
INSERT INTO enrolment (login,subject,year) values ('conway' , 'INFO10001', '2008');
62
INSERT INTO enrolment (login,subject,year) values ('apeel' , 'INFO10001', '2008');
63
INSERT INTO enrolment (login,subject,year) values ('mgiuca' , 'INFO10001', '2008');
64
INSERT INTO enrolment (login,subject,year) values ('sb' , 'INFO10001', '2008');
65
INSERT INTO enrolment (login,subject,year) values ('mpp' , 'INFO10001', '2008');
66
INSERT INTO enrolment (login,subject,year) values ('ivo' , 'INFO10001', '2008');