1
DROP TABLE users CASCADE;
1
-- We need a users database to do authorization, manage groups, &c
6
DROP TABLE group_members;
2
10
CREATE TABLE users (
3
11
login VARCHAR UNIQUE NOT NULL,
4
12
loginid SERIAL PRIMARY KEY NOT NULL,
7
studentid VARCHAR -- may be null
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,
19
DROP TABLE groups CASCADE;
15
studentid VARCHAR, -- may be null
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),
25
UNIQUE (offeringid, groupnm)
23
UNIQUE (offeringid, group)
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)
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)
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)
54
DROP TABLE roles CASCADE;
55
49
CREATE TABLE roles (
56
50
loginid INT4 PRIMARY KEY REFERENCES users (loginid),
60
DROP TABLE projects CASCADE;
61
54
CREATE TABLE projects (
62
55
projectid SERIAL PRIMARY KEY NOT NULL,
69
DROP TABLE project_extension CASCADE;
70
62
CREATE TABLE project_extension (
71
loginid INT4 REFERENCES users (loginid),
72
groupid INT4 REFERENCES groups (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,
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))
82
DROP TABLE project_mark CASCADE;
83
70
CREATE TABLE project_mark (
84
loginid INT4 REFERENCES users (loginid),
85
groupid INT4 REFERENCES groups (groupid),
86
72
projectid INT4 REFERENCES projects (projectid) NOT NULL,
88
74
marker INT4 REFERENCES users (loginid) NOT NULL,
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)
98
DROP TABLE problem CASCADE;
99
82
CREATE TABLE problem (
100
83
problemid SERIAL PRIMARY KEY NOT NULL,
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)
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'))
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)
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)
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);
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,
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,