← Back to branch summary

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

« back to all changes in this revision

Viewing changes to userdb/users.sql

  • Committer: mattgiuca
  • Date: 2008-01-25 06:28:03 UTC
  • Revision ID: svn-v3-trunk0:2b9c9e99-6f39-0410-b283-7f802c844ae2:trunk:314
tutorialservice: svn:ignore

Show diffs side-by-side

added added

removed removed

Lines of Context:
userdb/users.sql
1
 
DROP TABLE users CASCADE;
 
1
-- We need a users database to do authorization, manage groups, &c
 
2
-- Here's a first cut.
 
3
 
 
4
DROP TABLE roles;
 
5
DROP TABLE enrolment;
 
6
DROP TABLE group_members;
 
7
DROP TABLE users;
 
8
DROP TABLE groups;
 
9
 
2
10
CREATE TABLE users (
3
11
    login       VARCHAR UNIQUE NOT NULL,
4
12
    loginid     SERIAL PRIMARY KEY NOT NULL,
5
13
    nick        VARCHAR,
6
14
    fullname    VARCHAR,
7
 
    studentid   VARCHAR -- may be null
8
 
);
9
 
 
10
 
DROP TABLE offerings CASCADE;
11
 
CREATE TABLE offerings (
12
 
    offeringid  SERIAL PRIMARY KEY NOT NULL,
13
 
    subj_name   VARCHAR NOT NULL,
14
 
    subj_code   VARCHAR NOT NULL,
15
 
    year        CHAR(4) NOT NULL,
16
 
    semester    INT NOT NULL
17
 
);
18
 
 
19
 
DROP TABLE groups CASCADE;
 
15
    studentid   VARCHAR, -- may be null
 
16
);
 
17
 
20
18
CREATE TABLE groups (
21
 
    groupnm       VARCHAR NOT NULL,
 
19
    group       VARCHAR NOT NULL,
22
20
    groupid     SERIAL PRIMARY KEY NOT NULL,
23
21
    offeringid  INT4 REFERENCES offerings (offeringid),
24
22
    nick        VARCHAR,
25
 
    UNIQUE (offeringid, groupnm)
 
23
    UNIQUE (offeringid, group)
26
24
);
27
25
 
28
 
DROP TABLE group_invitations CASCADE;
29
26
CREATE TABLE group_invitations (
30
27
    loginid     INT4 REFERENCES users (loginid),
31
28
    groupid     INT4 REFERENCES groups (groupid),
32
29
    UNIQUE (loginid,groupid)
33
30
);
34
31
 
35
 
DROP TABLE group_members CASCADE;
36
32
CREATE TABLE group_members (
37
33
    loginid     INT4 REFERENCES users (loginid),
38
34
    groupid     INT4 REFERENCES groups (groupid),
41
37
    PRIMARY KEY (loginid,groupid)
42
38
);
43
39
 
44
 
DROP TABLE enrolment CASCADE;
45
40
CREATE TABLE enrolment (
46
41
    loginid     INT4 REFERENCES users (loginid),
47
42
    offeringid  INT4 REFERENCES offerings (offeringid),
51
46
    PRIMARY KEY (loginid,offeringid)
52
47
);
53
48
 
54
 
DROP TABLE roles CASCADE;
55
49
CREATE TABLE roles (
56
50
    loginid     INT4 PRIMARY KEY REFERENCES users (loginid),
57
51
    role        VARCHAR
58
52
);
59
53
 
60
 
DROP TABLE projects CASCADE;
61
54
CREATE TABLE projects (
62
55
    projectid   SERIAL PRIMARY KEY NOT NULL,
63
56
    synopsis    VARCHAR,
66
59
    deadline    TIMESTAMP
67
60
);
68
61
 
69
 
DROP TABLE project_extension CASCADE;
70
62
CREATE TABLE project_extension (
71
 
    loginid     INT4 REFERENCES users (loginid),
72
 
    groupid     INT4 REFERENCES groups (groupid),
 
63
    login or groupid
73
64
    projectid   INT4 REFERENCES projects (projectid) NOT NULL,
74
65
    deadline    TIMESTAMP NOT NULL,
75
66
    approver    INT4 REFERENCES users (loginid) NOT NULL,
76
 
    notes       VARCHAR,
77
 
    -- exactly one of loginid and groupid must be non-null
78
 
    CHECK ((loginid IS NOT NULL AND groupid IS NULL)
79
 
        OR (loginid IS NULL AND groupid IS NOT NULL))
 
67
    notes       VARCHAR
80
68
);
81
69
 
82
 
DROP TABLE project_mark CASCADE;
83
70
CREATE TABLE project_mark (
84
 
    loginid     INT4 REFERENCES users (loginid),
85
 
    groupid     INT4 REFERENCES groups (groupid),
 
71
    loginid or groupid
86
72
    projectid   INT4 REFERENCES projects (projectid) NOT NULL,
87
73
    componentid INT4,
88
74
    marker      INT4 REFERENCES users (loginid) NOT NULL,
90
76
    marked      TIMESTAMP,
91
77
    feedback    VARCHAR,
92
78
    notes       VARCHAR,
93
 
    -- exactly one of loginid and groupid must be non-null
94
 
    CHECK ((loginid IS NOT NULL AND groupid IS NULL)
95
 
        OR (loginid IS NULL AND groupid IS NOT NULL))
 
79
    PRIMARY KEY (loginid/groupid, projectid, componentid)
96
80
);
97
81
 
98
 
DROP TABLE problem CASCADE;
99
82
CREATE TABLE problem (
100
83
    problemid   SERIAL PRIMARY KEY NOT NULL,
101
84
    spec        VARCHAR
102
85
);
103
86
 
104
 
DROP TABLE problem_tags CASCADE;
105
87
CREATE TABLE problem_tags (
106
88
    problemid   INT4 REFERENCES tutorial_problem (problemid),
107
89
    tag         VARCHAR NOT NULL,
108
90
    added_by    INT4 REFERENCES users (loginid) NOT NULL,
109
 
    date        TIMESTAMP NOT NULL,
 
91
    when        TIMESTAMP NOT NULL,
110
92
    PRIMARY KEY (problemid,added_by,tag)
111
93
);
112
94
 
113
 
DROP TABLE problem_test_case CASCADE;
114
95
CREATE TABLE problem_test_case (
115
96
    problemid   INT4 REFERENCES problem (problemid) NOT NULL,
116
97
    testcaseid  SERIAL UNIQUE NOT NULL,
119
100
    visibility  VARCHAR CHECK (visibility in ('public', 'protected', 'private'))
120
101
);
121
102
 
122
 
DROP TABLE problem_test_case_tags CASCADE;
123
103
CREATE TABLE problem_test_case_tags (
124
104
    testcaseid  INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
125
105
    tag         VARCHAR NOT NULL,
126
106
    description VARCHAR,
127
107
    added_by    INT4 REFERENCES users (loginid) NOT NULL,
128
 
    date        TIMESTAMP NOT NULL,
 
108
    when        TIMESTAMP NOT NULL,
129
109
    PRIMARY KEY (testcaseid,added_by,tag)
130
110
);
131
111
 
132
 
DROP TABLE problem_attempt CASCADE;
133
112
CREATE TABLE problem_attempt (
134
113
    problemid   INT4 REFERENCES problem (problemid) NOT NULL,
135
114
    loginid     INT4 REFERENCES users (loginid) NOT NULL,
136
 
    date        TIMESTAMP NOT NULL,
 
115
    when        TIMESTAMP NOT NULL,
137
116
    attempt     VARCHAR NOT NULL,
138
117
    complete    BOOLEAN NOT NULL,
139
 
    PRIMARY KEY (problemid,loginid,date)
 
118
    PRIMARY KEY (problemid,loginid,when)
140
119
);
141
120
 
142
 
DROP INDEX problem_attempt_index;
143
 
CREATE INDEX problem_attempt_index ON problem_attempt (problemid, loginid);
 
121
CREATE INDEX indexname ON problem_attempt (problemid, login);
144
122
 
145
 
DROP TABLE problem_attempt_breakdown CASCADE;
146
123
CREATE TABLE problem_attempt_breakdown (
147
124
    problemid   INT4 REFERENCES problem (problemid) NOT NULL,
148
125
    testcaseid  INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
149
126
    loginid     INT4 REFERENCES users (loginid) NOT NULL,
150
 
    date        TIMESTAMP NOT NULL,
 
127
    when        TIMESTAMP NOT NULL,
151
128
    result      BOOLEAN
152
129
);
153
130
 
154
 
DROP TABLE problem_prerequisites CASCADE;
155
131
CREATE TABLE problem_prerequisites (
156
132
    parent      INT4 REFERENCES problem (problemid) NOT NULL,
157
133
    child       INT4 REFERENCES problem (problemid) NOT NULL,