← Back to branch summary

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

« back to all changes in this revision

Viewing changes to userdb/users.sql

  • Committer: William Grant
  • Date: 2010-02-24 01:38:33 UTC
  • Revision ID: grantw@unimelb.edu.au-20100224013833-u3hff23a3wbwsa9q
Refactor UserEditView to use BaseFormView.

Show diffs side-by-side

added added

removed removed

Lines of Context:
userdb/users.sql
1
 
 
2
 
DROP TABLE roles;
3
 
DROP TABLE enrolment;
4
 
DROP TABLE group_members;
5
 
DROP TABLE users;
6
 
DROP TABLE groups;
7
 
 
8
 
CREATE TABLE users (
9
 
    login       varchar(80) PRIMARY KEY,    -- login id
10
 
    nick        varchar(80)
11
 
);
12
 
 
13
 
CREATE TABLE groups (
14
 
    groupid     varchar(18) PRIMARY KEY,    -- group name Y^4-S^9-G^3
15
 
    nick        varchar(80),                -- group nickname
16
 
    subject     varchar(9),                 -- subject code
17
 
    year        varchar(4)                  -- when
18
 
);
19
 
 
20
 
CREATE TABLE group_members (
21
 
    login       varchar(80) REFERENCES users (login),
22
 
    groupid     varchar(18) REFERENCES groups (groupid)
 
1
BEGIN;
 
2
 
 
3
-- Check that the provided name is sane for use in URLs.
 
4
CREATE OR REPLACE FUNCTION valid_url_name(name text) RETURNS boolean AS 
 
5
$$
 
6
    BEGIN
 
7
        RETURN name ~ E'^[a-z0-9][a-z0-9_\+\.\-]*$';
 
8
    END;
 
9
$$ LANGUAGE 'plpgsql';
 
10
 
 
11
-- Just like valid_url_name, except that @ is permitted (so we can use a
 
12
-- reasonable subset of email addresses as usernames).
 
13
CREATE OR REPLACE FUNCTION valid_login_name(name text) RETURNS boolean AS 
 
14
$$
 
15
    BEGIN
 
16
        RETURN name ~ E'^[a-z0-9][a-z0-9@_\+\.\-]*$';
 
17
    END;
 
18
$$ LANGUAGE 'plpgsql';
 
19
 
 
20
CREATE SEQUENCE login_unixid_seq MINVALUE 1000 MAXVALUE 29999 START WITH 5000;
 
21
 
 
22
CREATE TABLE login (
 
23
    loginid     SERIAL PRIMARY KEY NOT NULL,
 
24
    login       VARCHAR UNIQUE NOT NULL CHECK (valid_login_name(login)),
 
25
    passhash    VARCHAR,
 
26
    state       VARCHAR NOT NULL CHECK (state in ('no_agreement', 'pending',
 
27
                                              'enabled', 'disabled'))
 
28
                                 DEFAULT 'no_agreement',
 
29
    admin       BOOLEAN NOT NULL DEFAULT false,
 
30
    unixid      INT UNIQUE DEFAULT nextval('login_unixid_seq') NOT NULL,
 
31
    nick        VARCHAR NOT NULL,
 
32
    pass_exp    TIMESTAMP,
 
33
    acct_exp    TIMESTAMP,
 
34
    last_login  TIMESTAMP,
 
35
    svn_pass    VARCHAR,
 
36
    email       VARCHAR,
 
37
    fullname    VARCHAR NOT NULL,
 
38
    studentid   VARCHAR, -- may be null
 
39
    settings    VARCHAR
 
40
);
 
41
 
 
42
-- Subjects
 
43
-- --------
 
44
 
 
45
CREATE TABLE subject (
 
46
    subjectid       SERIAL PRIMARY KEY NOT NULL,
 
47
    subj_code       VARCHAR UNIQUE NOT NULL,
 
48
    subj_name       VARCHAR NOT NULL,
 
49
    subj_short_name VARCHAR UNIQUE NOT NULL CHECK (valid_url_name(subj_short_name))
 
50
);
 
51
 
 
52
CREATE TABLE semester (
 
53
    semesterid  SERIAL PRIMARY KEY NOT NULL,
 
54
    year        CHAR(4) NOT NULL CHECK (valid_url_name(year)),
 
55
    semester    CHAR(1) NOT NULL CHECK (valid_url_name(semester)),
 
56
    state       TEXT NOT NULL CHECK (state IN ('disabled', 'past',
 
57
                                    'current', 'future')) DEFAULT 'current',
 
58
    UNIQUE (year, semester)
 
59
);
 
60
 
 
61
CREATE TABLE offering (
 
62
    offeringid  SERIAL PRIMARY KEY NOT NULL,
 
63
    subject     INT4 REFERENCES subject (subjectid) NOT NULL,
 
64
    semesterid  INTEGER REFERENCES semester (semesterid) NOT NULL,
 
65
    description VARCHAR,
 
66
    url         VARCHAR,
 
67
    groups_student_permissions  VARCHAR NOT NULL DEFAULT 'none',
 
68
    CHECK (groups_student_permissions in ('none', 'invite', 'create')),
 
69
    UNIQUE (subject, semesterid)
 
70
);
 
71
 
 
72
-- Projects and groups
 
73
-- -------------------
 
74
 
 
75
CREATE TABLE project_set (
 
76
    projectsetid  SERIAL PRIMARY KEY NOT NULL,
 
77
    offeringid    INTEGER REFERENCES offering (offeringid) NOT NULL,
 
78
    max_students_per_group  INTEGER
 
79
);
 
80
 
 
81
CREATE TABLE project (
 
82
    projectid   SERIAL PRIMARY KEY NOT NULL,
 
83
    short_name  TEXT NOT NULL CHECK (valid_url_name(short_name)),
 
84
    name        TEXT NOT NULL,
 
85
    synopsis    TEXT,
 
86
    url         TEXT,
 
87
    projectsetid  INTEGER REFERENCES project_set (projectsetid) NOT NULL,
 
88
    deadline    TIMESTAMP NOT NULL
 
89
);
 
90
 
 
91
CREATE OR REPLACE FUNCTION check_project_namespacing_insertupdate()
 
92
RETURNS trigger AS '
 
93
    DECLARE
 
94
        oid INTEGER;
 
95
    BEGIN
 
96
        IF TG_OP = ''UPDATE'' THEN
 
97
            IF NEW.projectsetid = OLD.projectsetid AND NEW.short_name = OLD.short_name THEN
 
98
                RETURN NEW;
 
99
            END IF;
 
100
        END IF;
 
101
        SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
 
102
        PERFORM 1 FROM project, project_set
 
103
        WHERE project_set.offeringid = oid AND
 
104
              project.projectsetid = project_set.projectsetid AND
 
105
              project.short_name = NEW.short_name;
 
106
        IF found THEN
 
107
            RAISE EXCEPTION ''a project named % already exists in offering ID %'', NEW.short_name, oid;
 
108
        END IF;
 
109
        RETURN NEW;
 
110
    END;
 
111
' LANGUAGE 'plpgsql';
 
112
 
 
113
CREATE TRIGGER check_project_namespacing
 
114
    BEFORE INSERT OR UPDATE ON project
 
115
    FOR EACH ROW EXECUTE PROCEDURE check_project_namespacing_insertupdate();
 
116
 
 
117
CREATE TABLE project_group (
 
118
    groupnm     VARCHAR NOT NULL CHECK (valid_url_name(groupnm)),
 
119
    groupid     SERIAL PRIMARY KEY NOT NULL,
 
120
    projectsetid  INTEGER REFERENCES project_set (projectsetid) NOT NULL,
 
121
    nick        VARCHAR,
 
122
    createdby   INT4 REFERENCES login (loginid) NOT NULL,
 
123
    epoch       TIMESTAMP NOT NULL,
 
124
    UNIQUE (projectsetid, groupnm)
 
125
);
 
126
 
 
127
CREATE OR REPLACE FUNCTION check_group_namespacing_insertupdate()
 
128
RETURNS trigger AS '
 
129
    DECLARE
 
130
        oid INTEGER;
 
131
    BEGIN
 
132
        SELECT offeringid INTO oid FROM project_set WHERE project_set.projectsetid = NEW.projectsetid;
 
133
        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;
 
134
        IF found THEN
 
135
            RAISE EXCEPTION ''a project group named % already exists in offering ID %'', NEW.groupnm, oid;
 
136
        END IF;
 
137
        RETURN NEW;
 
138
    END;
 
139
' LANGUAGE 'plpgsql';
 
140
 
 
141
CREATE TRIGGER check_group_namespacing
 
142
    BEFORE INSERT OR UPDATE ON project_group
 
143
    FOR EACH ROW EXECUTE PROCEDURE check_group_namespacing_insertupdate();
 
144
 
 
145
CREATE TABLE group_invitation (
 
146
    loginid     INT4 REFERENCES login (loginid) NOT NULL,
 
147
    groupid     INT4 REFERENCES project_group (groupid) NOT NULL,
 
148
    inviter     INT4 REFERENCES login (loginid) NOT NULL,
 
149
    invited     TIMESTAMP NOT NULL,
 
150
    accepted    TIMESTAMP,
 
151
    UNIQUE (loginid,groupid)
 
152
);
 
153
 
 
154
CREATE TABLE group_member (
 
155
    loginid     INT4 REFERENCES login (loginid),
 
156
    groupid     INT4 REFERENCES project_group (groupid),
 
157
    PRIMARY KEY (loginid,groupid)
23
158
);
24
159
 
25
160
CREATE TABLE enrolment (
26
 
    login       varchar(80) REFERENCES users (login),
27
 
    subject     varchar(9),
28
 
    year        varchar(4)
29
 
);
30
 
 
31
 
CREATE TABLE roles (
32
 
    login       varchar(80) REFERENCES users (login),
33
 
    role        varchar(8)
34
 
);
35
 
 
36
 
INSERT INTO users (login,nick) values ('conway', 'Tom');
37
 
INSERT INTO roles (login,role) values ('conway', 'student');
38
 
INSERT INTO users (login,nick) values ('apeel', 'Andrew');
39
 
INSERT INTO roles (login,role) values ('apeel', 'student');
40
 
INSERT INTO users (login,nick) values ('mgiuca', 'Matt');
41
 
INSERT INTO roles (login,role) values ('mgiuca', 'tutor');
42
 
INSERT INTO users (login,nick) values ('sb', 'Steven');
43
 
INSERT INTO roles (login,role) values ('sb', 'lecturer');
44
 
INSERT INTO users (login,nick) values ('mpp', 'Mike');
45
 
INSERT INTO roles (login,role) values ('mpp', 'student');
46
 
INSERT INTO users (login,nick) values ('ivo', 'Ivo');
47
 
INSERT INTO roles (login,role) values ('ivo', 'admin');
48
 
 
49
 
INSERT INTO groups (groupid, nick, subject, year) values ('2007-INFO10001-321', 'Purple Alert', 'INFO10001', '2008');
50
 
INSERT INTO groups (groupid, nick, subject, year) values ('2007-INFO10001-322', 'Blind Illuminati', 'INFO10001', '2008');
51
 
 
52
 
INSERT INTO group_members (login,groupid) values ('conway', '2007-INFO10001-321');
53
 
INSERT INTO group_members (login,groupid) values ('apeel', '2007-INFO10001-321');
54
 
INSERT INTO group_members (login,groupid) values ('mgiuca', '2007-INFO10001-321');
55
 
INSERT INTO group_members (login,groupid) values ('sb', '2007-INFO10001-321');
56
 
INSERT INTO group_members (login,groupid) values ('mpp', '2007-INFO10001-322');
57
 
INSERT INTO group_members (login,groupid) values ('ivo', '2007-INFO10001-322');
58
 
 
59
 
INSERT INTO enrolment (login,subject,year) values ('conway' , 'INFO10001', '2008');
60
 
INSERT INTO enrolment (login,subject,year) values ('apeel' , 'INFO10001', '2008');
61
 
INSERT INTO enrolment (login,subject,year) values ('mgiuca' , 'INFO10001', '2008');
62
 
INSERT INTO enrolment (login,subject,year) values ('sb' , 'INFO10001', '2008');
63
 
INSERT INTO enrolment (login,subject,year) values ('mpp' , 'INFO10001', '2008');
64
 
INSERT INTO enrolment (login,subject,year) values ('ivo' , 'INFO10001', '2008');
 
161
    loginid     INT4 REFERENCES login (loginid),
 
162
    offeringid  INT4 REFERENCES offering (offeringid),
 
163
    role        TEXT NOT NULL CHECK (role IN ('student', 'tutor',
 
164
                                              'lecturer')) DEFAULT 'student',
 
165
    result      INT,
 
166
    special_result VARCHAR,
 
167
    supp_result INT,
 
168
    special_supp_result VARCHAR,
 
169
    notes       VARCHAR,
 
170
    active      BOOL NOT NULL DEFAULT true,
 
171
    PRIMARY KEY (loginid,offeringid)
 
172
);
 
173
 
 
174
CREATE TABLE assessed (
 
175
    assessedid  SERIAL PRIMARY KEY NOT NULL,
 
176
    loginid     INT4 REFERENCES login (loginid),
 
177
    groupid     INT4 REFERENCES project_group (groupid),
 
178
    projectid   INT4 REFERENCES project (projectid) NOT NULL,
 
179
    -- exactly one of loginid and groupid must be non-null
 
180
    CHECK ((loginid IS NOT NULL AND groupid IS NULL)
 
181
        OR (loginid IS NULL AND groupid IS NOT NULL))
 
182
);
 
183
 
 
184
CREATE TABLE project_extension (
 
185
    extensionid SERIAL PRIMARY KEY,
 
186
    assessedid  INT4 REFERENCES assessed (assessedid) NOT NULL,
 
187
    deadline    TIMESTAMP NOT NULL,
 
188
    approver    INT4 REFERENCES login (loginid) NOT NULL,
 
189
    notes       VARCHAR
 
190
);
 
191
 
 
192
CREATE TABLE project_submission (
 
193
    submissionid SERIAL PRIMARY KEY,
 
194
    assessedid  INT4 REFERENCES assessed (assessedid) NOT NULL,
 
195
    path        VARCHAR NOT NULL,
 
196
    revision    INT4 NOT NULL,
 
197
    date_submitted TIMESTAMP NOT NULL,
 
198
    submitter   INT4 REFERENCES login (loginid) NOT NULL
 
199
);
 
200
 
 
201
CREATE TABLE project_mark (
 
202
    assessedid  INT4 REFERENCES assessed (assessedid) NOT NULL,
 
203
    componentid INT4,
 
204
    marker      INT4 REFERENCES login (loginid) NOT NULL,
 
205
    mark        INT,
 
206
    marked      TIMESTAMP,
 
207
    feedback    VARCHAR,
 
208
    notes       VARCHAR
 
209
);
 
210
 
 
211
-- Worksheets
 
212
-- ----------
 
213
CREATE TABLE exercise (
 
214
    identifier  TEXT PRIMARY KEY CHECK (valid_url_name(identifier)),
 
215
    name        TEXT,
 
216
    description TEXT,
 
217
    partial     TEXT,
 
218
    solution    TEXT,
 
219
    include     TEXT,
 
220
    num_rows    INT4
 
221
);
 
222
 
 
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)),
 
227
    name        TEXT NOT NULL,
 
228
    data        TEXT NOT NULL,
 
229
    assessable  BOOLEAN NOT NULL,
 
230
    seq_no      INT4 NOT NULL,
 
231
    format      TEXT NOT NUll,
 
232
    UNIQUE (offeringid, identifier)
 
233
);
 
234
 
 
235
CREATE TABLE worksheet_exercise (
 
236
    ws_ex_id        SERIAL PRIMARY KEY,
 
237
    worksheetid     INT4 REFERENCES worksheet (worksheetid) NOT NULL,
 
238
    exerciseid      TEXT REFERENCES exercise (identifier) NOT NULL,
 
239
    seq_no          INT4 NOT NULL,
 
240
    active          BOOLEAN NOT NULL DEFAULT true,
 
241
    optional        BOOLEAN NOT NULL,
 
242
    UNIQUE (worksheetid, exerciseid)
 
243
);
 
244
 
 
245
CREATE TABLE exercise_attempt (
 
246
    loginid     INT4 REFERENCES login (loginid) NOT NULL,
 
247
    ws_ex_id    INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
 
248
    date        TIMESTAMP NOT NULL,
 
249
    attempt     TEXT NOT NULL,
 
250
    complete    BOOLEAN NOT NULL,
 
251
    active      BOOLEAN NOT NULL DEFAULT true,
 
252
    PRIMARY KEY (loginid, ws_ex_id, date)
 
253
);
 
254
 
 
255
CREATE TABLE exercise_save (
 
256
    loginid     INT4 REFERENCES login (loginid) NOT NULL,
 
257
    ws_ex_id    INT4 REFERENCES worksheet_exercise (ws_ex_id) NOT NULL,
 
258
    date        TIMESTAMP NOT NULL,
 
259
    text        TEXT NOT NULL,
 
260
    PRIMARY KEY (loginid, ws_ex_id)
 
261
);
 
262
 
 
263
CREATE TABLE test_suite (
 
264
    suiteid     SERIAL PRIMARY KEY,
 
265
    exerciseid  TEXT REFERENCES exercise (identifier) NOT NULL,
 
266
    description TEXT,
 
267
    seq_no      INT4,
 
268
    function    TEXT,
 
269
    stdin       TEXT
 
270
);
 
271
 
 
272
CREATE TABLE test_case (
 
273
    testid          SERIAL PRIMARY KEY,
 
274
    suiteid         INT4 REFERENCES test_suite (suiteid) NOT NULL,
 
275
    passmsg         TEXT,
 
276
    failmsg         TEXT,
 
277
    test_default    TEXT,
 
278
    seq_no          INT4
 
279
);
 
280
 
 
281
CREATE TABLE suite_variable (
 
282
    varid       SERIAL PRIMARY KEY,
 
283
    suiteid     INT4 REFERENCES test_suite (suiteid) NOT NULL,
 
284
    var_name    TEXT,
 
285
    var_value   TEXT,
 
286
    var_type    TEXT NOT NULL,
 
287
    arg_no      INT4
 
288
);
 
289
 
 
290
CREATE TABLE test_case_part (
 
291
    partid          SERIAL PRIMARY KEY,
 
292
    testid          INT4 REFERENCES test_case (testid) NOT NULL,
 
293
    part_type       TEXT NOT NULL,
 
294
    test_type       TEXT,
 
295
    data            TEXT,
 
296
    filename        TEXT
 
297
);
 
298
COMMIT;