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