← Back to branch summary

~azzar1/unity/add-show-desktop-key

« back to all changes in this revision

Viewing changes to userdb/users.sql

  • Committer: wagrant
  • Date: 2008-07-23 02:42:54 UTC
  • Revision ID: svn-v3-trunk0:2b9c9e99-6f39-0410-b283-7f802c844ae2:trunk:935
userdb: Large changes:
 - Split out semesters into their own table.
 - Give semesters and enrolments an active flag.
   + Enrolments cannot be active if their semester is not.
 - Introduce the project set concept, to link groups to projects.
   + Groups and projects now reference a project set, not an offering.

Also update the DB access code to respect the group changes.

<FONT COLOR="red"><BLINK>

  ** WARNING: This migration will destroy all groups, enrolments and
              offerings. **

</BLINK></FONT>

Show diffs side-by-side

added added

removed removed

Lines of Context:
userdb/users.sql
19
19
    settings    VARCHAR
20
20
);
21
21
 
 
22
-- Subjects
 
23
-- --------
 
24
 
22
25
CREATE TABLE subject (
23
26
    subjectid       SERIAL PRIMARY KEY NOT NULL,
24
27
    subj_code       VARCHAR UNIQUE NOT NULL,
27
30
    url             VARCHAR
28
31
);
29
32
 
 
33
CREATE TABLE semester (
 
34
    semesterid  SERIAL PRIMARY KEY NOT NULL,
 
35
    year        CHAR(4) NOT NULL,
 
36
    semester    CHAR(1) NOT NULL,
 
37
    active      BOOL NOT NULL,
 
38
    UNIQUE (year, semester)
 
39
);
 
40
 
 
41
CREATE OR REPLACE FUNCTION deactivate_semester_enrolments_update()
 
42
RETURNS trigger AS '
 
43
    BEGIN
 
44
        IF OLD.active = true AND NEW.active = false THEN
 
45
            UPDATE enrolment SET active=false WHERE offeringid IN (
 
46
            SELECT offeringid FROM offering WHERE offering.semesterid = NEW.semesterid);
 
47
        END IF;
 
48
        RETURN NULL;
 
49
    END;
 
50
' LANGUAGE 'plpgsql';
 
51
 
 
52
CREATE TRIGGER deactivate_semester_enrolments
 
53
    AFTER UPDATE ON semester
 
54
    FOR EACH ROW EXECUTE PROCEDURE deactivate_semester_enrolments_update();
 
55
 
30
56
CREATE TABLE offering (
31
57
    offeringid  SERIAL PRIMARY KEY NOT NULL,
32
58
    subject     INT4 REFERENCES subject (subjectid) NOT NULL,
33
 
    year        CHAR(4) NOT NULL,
34
 
    semester    CHAR(1) NOT NULL,
35
 
    max_groups_per_student      INT4 DEFAULT 1,
36
 
    max_students_per_group      INT4 DEFAULT 4,
37
 
    groups_student_permissions  VARCHAR NOT NULL
38
 
        CHECK (groups_student_permissions in ('none', 'invite',
39
 
                                              'create'))
40
 
        DEFAULT 'none';
41
 
    UNIQUE (subject, year, semester)
 
59
    semesterid  INTEGER REFERENCES semester (semesterid) NOT NULL,
 
60
    groups_student_permissions  VARCHAR NOT NULL DEFAULT 'none',
 
61
    CHECK (groups_student_permissions in ('none', 'invite', 'create')),
 
62
    UNIQUE (subject, semesterid)
 
63
);
 
64
 
 
65
-- Projects and groups
 
66
-- -------------------
 
67
 
 
68
CREATE TABLE project_set (
 
69
    projectsetid  SERIAL PRIMARY KEY NOT NULL,
 
70
    offeringid    INTEGER REFERENCES offering (offeringid) NOT NULL,
 
71
    max_students_per_group  INTEGER NOT NULL DEFAULT 4
42
72
);
43
73
 
44
74
CREATE TABLE project (
45
75
    projectid   SERIAL PRIMARY KEY NOT NULL,
46
76
    synopsis    VARCHAR,
47
77
    url         VARCHAR,
48
 
    offeringid  INT4 REFERENCES offering (offeringid) NOT NULL,
 
78
    projectsetid  INTEGER REFERENCES project_set (projectsetid) NOT NULL,
49
79
    deadline    TIMESTAMP
50
80
);
51
81
 
52
82
CREATE TABLE project_group (
53
83
    groupnm     VARCHAR NOT NULL,
54
84
    groupid     SERIAL PRIMARY KEY NOT NULL,
55
 
    offeringid  INT4 REFERENCES offering (offeringid),
 
85
    projectsetid  INTEGER REFERENCES project_set (projectsetid) NOT NULL,
56
86
    nick        VARCHAR,
57
87
    createdby   INT4 REFERENCES login (loginid) NOT NULL,
58
88
    epoch       TIMESTAMP NOT NULL,
59
 
    UNIQUE (offeringid, groupnm)
 
89
    UNIQUE (projectsetid, groupnm)
60
90
);
61
91
 
 
92
CREATE OR REPLACE FUNCTION check_group_namespacing_insertupdate()
 
93
RETURNS trigger AS '
 
94
    DECLARE
 
95
        oid INTEGER;
 
96
    BEGIN
 
97
        SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
 
98
        PERFORM 1 FROM project_group, project_set WHERE project_group.projectsetid = project_set.projectsetid AND project_group.groupnm = NEW.groupnm;
 
99
        IF found THEN
 
100
            RAISE EXCEPTION ''a project group named % already exists in offering ID %'', NEW.groupnm, oid;
 
101
        END IF;
 
102
        RETURN NEW;
 
103
    END;
 
104
' LANGUAGE 'plpgsql';
 
105
 
 
106
CREATE TRIGGER check_group_namespacing
 
107
    BEFORE INSERT OR UPDATE ON project_group
 
108
    FOR EACH ROW EXECUTE PROCEDURE check_group_namespacing_insertupdate();
 
109
 
62
110
CREATE TABLE group_invitation (
63
111
    loginid     INT4 REFERENCES login (loginid) NOT NULL,
64
112
    groupid     INT4 REFERENCES project_group (groupid) NOT NULL,
71
119
CREATE TABLE group_member (
72
120
    loginid     INT4 REFERENCES login (loginid),
73
121
    groupid     INT4 REFERENCES project_group (groupid),
74
 
    projectid   INT4 REFERENCES project (projectid),
75
 
    UNIQUE (loginid,projectid),
76
122
    PRIMARY KEY (loginid,groupid)
77
123
);
78
124
 
84
130
    supp_result INT,
85
131
    special_supp_result VARCHAR,
86
132
    notes       VARCHAR,
 
133
    active      BOOL NOT NULL DEFAULT true,
87
134
    PRIMARY KEY (loginid,offeringid)
88
135
);
89
136
 
 
137
CREATE OR REPLACE FUNCTION confirm_active_semester_insertupdate()
 
138
RETURNS trigger AS '
 
139
    DECLARE
 
140
        active BOOL;
 
141
    BEGIN
 
142
        SELECT semester.active INTO active FROM offering, semester WHERE offeringid=NEW.offeringid AND semester.semesterid = offering.semesterid;
 
143
        IF NOT active AND NEW.active = true THEN
 
144
            RAISE EXCEPTION ''cannot have active enrolment for % in offering %, as the semester is inactive'', NEW.loginid, NEW.offeringid;
 
145
        END IF;
 
146
        RETURN NEW;
 
147
    END;
 
148
' LANGUAGE 'plpgsql';
 
149
 
 
150
CREATE TRIGGER confirm_active_semester
 
151
    BEFORE INSERT OR UPDATE ON enrolment
 
152
    FOR EACH ROW EXECUTE PROCEDURE confirm_active_semester_insertupdate();
 
153
 
90
154
CREATE TABLE assessed (
91
155
    assessedid  SERIAL PRIMARY KEY NOT NULL,
92
156
    loginid     INT4 REFERENCES login (loginid),
120
184
    notes       VARCHAR
121
185
);
122
186
 
 
187
-- Worksheets
 
188
-- ----------
 
189
 
123
190
CREATE TABLE problem (
124
191
    problemid   SERIAL PRIMARY KEY NOT NULL,
125
192
    identifier  VARCHAR UNIQUE NOT NULL,