8
8
state VARCHAR NOT NULL CHECK (state in ('no_agreement', 'pending',
9
9
'enabled', 'disabled'))
10
10
DEFAULT 'no_agreement',
11
rolenm VARCHAR NOT NULL CHECK (rolenm in ('anyone', 'student',
13
'lecturer', 'admin')),
11
admin BOOLEAN NOT NULL DEFAULT false,
14
12
unixid INT UNIQUE DEFAULT nextval('login_unixid_seq') NOT NULL,
15
13
nick VARCHAR NOT NULL,
16
14
pass_exp TIMESTAMP,
30
28
subjectid SERIAL PRIMARY KEY NOT NULL,
31
29
subj_code VARCHAR UNIQUE NOT NULL,
32
30
subj_name VARCHAR NOT NULL,
33
subj_short_name VARCHAR UNIQUE NOT NULL,
31
subj_short_name VARCHAR UNIQUE NOT NULL
37
34
CREATE TABLE semester (
38
35
semesterid SERIAL PRIMARY KEY NOT NULL,
39
36
year CHAR(4) NOT NULL,
40
37
semester CHAR(1) NOT NULL,
38
state TEXT NOT NULL CHECK (state IN ('disabled', 'past',
39
'current', 'future')) DEFAULT 'current',
42
40
UNIQUE (year, semester)
45
CREATE OR REPLACE FUNCTION deactivate_semester_enrolments_update()
48
IF OLD.active = true AND NEW.active = false THEN
49
UPDATE enrolment SET active=false WHERE offeringid IN (
50
SELECT offeringid FROM offering WHERE offering.semesterid = NEW.semesterid);
56
CREATE TRIGGER deactivate_semester_enrolments
57
AFTER UPDATE ON semester
58
FOR EACH ROW EXECUTE PROCEDURE deactivate_semester_enrolments_update();
60
43
CREATE TABLE offering (
61
44
offeringid SERIAL PRIMARY KEY NOT NULL,
62
45
subject INT4 REFERENCES subject (subjectid) NOT NULL,
63
46
semesterid INTEGER REFERENCES semester (semesterid) NOT NULL,
64
49
groups_student_permissions VARCHAR NOT NULL DEFAULT 'none',
65
50
CHECK (groups_student_permissions in ('none', 'invite', 'create')),
66
51
UNIQUE (subject, semesterid)
72
57
CREATE TABLE project_set (
73
58
projectsetid SERIAL PRIMARY KEY NOT NULL,
74
59
offeringid INTEGER REFERENCES offering (offeringid) NOT NULL,
75
max_students_per_group INTEGER NOT NULL DEFAULT 4
60
max_students_per_group INTEGER
78
63
CREATE TABLE project (
79
64
projectid SERIAL PRIMARY KEY NOT NULL,
65
short_name TEXT NOT NULL,
82
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();
86
99
CREATE TABLE project_group (
87
100
groupnm VARCHAR NOT NULL,
88
101
groupid SERIAL PRIMARY KEY NOT NULL,
129
142
CREATE TABLE enrolment (
130
143
loginid INT4 REFERENCES login (loginid),
131
144
offeringid INT4 REFERENCES offering (offeringid),
145
role TEXT NOT NULL CHECK (role IN ('student', 'tutor',
146
'lecturer')) DEFAULT 'student',
133
148
special_result VARCHAR,
138
153
PRIMARY KEY (loginid,offeringid)
141
CREATE OR REPLACE FUNCTION confirm_active_semester_insertupdate()
146
SELECT semester.active INTO active FROM offering, semester WHERE offeringid=NEW.offeringid AND semester.semesterid = offering.semesterid;
147
IF NOT active AND NEW.active = true THEN
148
RAISE EXCEPTION ''cannot have active enrolment for % in offering %, as the semester is inactive'', NEW.loginid, NEW.offeringid;
152
' LANGUAGE 'plpgsql';
154
CREATE TRIGGER confirm_active_semester
155
BEFORE INSERT OR UPDATE ON enrolment
156
FOR EACH ROW EXECUTE PROCEDURE confirm_active_semester_insertupdate();
158
156
CREATE TABLE assessed (
159
157
assessedid SERIAL PRIMARY KEY NOT NULL,
160
158
loginid INT4 REFERENCES login (loginid),
168
166
CREATE TABLE project_extension (
167
extensionid SERIAL PRIMARY KEY,
169
168
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
170
169
deadline TIMESTAMP NOT NULL,
171
170
approver INT4 REFERENCES login (loginid) NOT NULL,
175
174
CREATE TABLE project_submission (
175
submissionid SERIAL PRIMARY KEY,
176
176
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
177
177
path VARCHAR NOT NULL,
178
revision INT4 NOT NULL
178
revision INT4 NOT NULL,
179
date_submitted TIMESTAMP NOT NULL,
180
submitter INT4 REFERENCES login (loginid) NOT NULL
181
183
CREATE TABLE project_mark (