56
56
CREATE TABLE project_set (
57
57
projectsetid SERIAL PRIMARY KEY NOT NULL,
58
58
offeringid INTEGER REFERENCES offering (offeringid) NOT NULL,
59
max_students_per_group INTEGER
59
max_students_per_group INTEGER NOT NULL DEFAULT 4
62
62
CREATE TABLE project (
63
63
projectid SERIAL PRIMARY KEY NOT NULL,
64
short_name TEXT NOT NULL,
68
66
projectsetid INTEGER REFERENCES project_set (projectsetid) NOT NULL,
72
CREATE OR REPLACE FUNCTION check_project_namespacing_insertupdate()
77
IF TG_OP = ''UPDATE'' THEN
78
IF NEW.projectsetid = OLD.projectsetid AND NEW.short_name = OLD.short_name THEN
82
SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
83
PERFORM 1 FROM project, project_set
84
WHERE project_set.offeringid = oid AND
85
project.projectsetid = project_set.projectsetid AND
86
project.short_name = NEW.short_name;
88
RAISE EXCEPTION ''a project named % already exists in offering ID %'', NEW.short_name, oid;
94
CREATE TRIGGER check_project_namespacing
95
BEFORE INSERT OR UPDATE ON project
96
FOR EACH ROW EXECUTE PROCEDURE check_project_namespacing_insertupdate();
98
70
CREATE TABLE project_group (
99
71
groupnm VARCHAR NOT NULL,
100
72
groupid SERIAL PRIMARY KEY NOT NULL,
165
137
CREATE TABLE project_extension (
166
extensionid SERIAL PRIMARY KEY,
167
138
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
168
139
deadline TIMESTAMP NOT NULL,
169
140
approver INT4 REFERENCES login (loginid) NOT NULL,
173
144
CREATE TABLE project_submission (
174
submissionid SERIAL PRIMARY KEY,
175
145
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
176
146
path VARCHAR NOT NULL,
177
revision INT4 NOT NULL,
178
date_submitted TIMESTAMP NOT NULL,
179
submitter INT4 REFERENCES login (loginid) NOT NULL
147
revision INT4 NOT NULL
182
150
CREATE TABLE project_mark (