← Back to branch summary

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

« back to all changes in this revision

Viewing changes to userdb/users.sql

  • Committer: drtomc
  • Date: 2008-01-29 02:17:43 UTC
  • Revision ID: svn-v3-trunk0:2b9c9e99-6f39-0410-b283-7f802c844ae2:trunk:319
Partial correctness for the user database schema. A few bits and pieces to go.

Show diffs side-by-side

added added

removed removed

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