1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
BEGIN;
ALTER TABLE project_set ALTER COLUMN max_students_per_group DROP NOT NULL;
ALTER TABLE project_set ALTER COLUMN max_students_per_group DROP DEFAULT;
ALTER TABLE project ALTER COLUMN synopsis TYPE TEXT;
ALTER TABLE project ALTER COLUMN url TYPE TEXT;
ALTER TABLE project ADD COLUMN short_name TEXT NOT NULL;
ALTER TABLE project ADD COLUMN name TEXT NOT NULL;
CREATE OR REPLACE FUNCTION check_project_namespacing_insertupdate()
RETURNS trigger AS '
DECLARE
oid INTEGER;
BEGIN
IF TG_OP = ''UPDATE'' THEN
IF NEW.projectsetid = OLD.projectsetid AND NEW.short_name = OLD.short_name THEN
RETURN NEW;
END IF;
END IF;
SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
PERFORM 1 FROM project, project_set
WHERE project_set.offeringid = oid AND
project.projectsetid = project_set.projectsetid AND
project.short_name = NEW.short_name;
IF found THEN
RAISE EXCEPTION ''a project named % already exists in offering ID %'', NEW.short_name, oid;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER check_project_namespacing
BEFORE INSERT OR UPDATE ON project
FOR EACH ROW EXECUTE PROCEDURE check_project_namespacing_insertupdate();
ALTER TABLE project_extension ADD COLUMN extensionid SERIAL PRIMARY KEY;
ALTER TABLE project_submission ADD COLUMN submissionid SERIAL PRIMARY KEY;
ALTER TABLE project_submission ADD COLUMN date_submitted TIMESTAMP NOT NULL;
ALTER TABLE project_submission ADD COLUMN submitter INT4 REFERENCES login (loginid) NOT NULL;
COMMIT;
|