319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
1 |
DROP TABLE users CASCADE; |
25
by drtomc
A bit more work on the userdb stuff. |
2 |
CREATE TABLE users ( |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
3 |
login VARCHAR UNIQUE NOT NULL, |
4 |
loginid SERIAL PRIMARY KEY NOT NULL, |
|
5 |
nick VARCHAR, |
|
6 |
fullname VARCHAR, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
7 |
studentid VARCHAR -- may be null |
25
by drtomc
A bit more work on the userdb stuff. |
8 |
);
|
9 |
||
324
by drtomc
Should all be okay now. |
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 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
19 |
DROP TABLE groups CASCADE; |
25
by drtomc
A bit more work on the userdb stuff. |
20 |
CREATE TABLE groups ( |
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
21 |
groupnm VARCHAR NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
22 |
groupid SERIAL PRIMARY KEY NOT NULL, |
23 |
offeringid INT4 REFERENCES offerings (offeringid), |
|
24 |
nick VARCHAR, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
25 |
UNIQUE (offeringid, groupnm) |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
26 |
);
|
27 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
28 |
DROP TABLE group_invitations CASCADE; |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
29 |
CREATE TABLE group_invitations ( |
30 |
loginid INT4 REFERENCES users (loginid), |
|
31 |
groupid INT4 REFERENCES groups (groupid), |
|
32 |
UNIQUE (loginid,groupid) |
|
25
by drtomc
A bit more work on the userdb stuff. |
33 |
);
|
34 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
35 |
DROP TABLE group_members CASCADE; |
25
by drtomc
A bit more work on the userdb stuff. |
36 |
CREATE TABLE group_members ( |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
37 |
loginid INT4 REFERENCES users (loginid), |
38 |
groupid INT4 REFERENCES groups (groupid), |
|
39 |
projectid INT4 REFERENCES projects (projectid), |
|
40 |
UNIQUE (loginid,projectid), |
|
41 |
PRIMARY KEY (loginid,groupid) |
|
25
by drtomc
A bit more work on the userdb stuff. |
42 |
);
|
43 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
44 |
DROP TABLE enrolment CASCADE; |
25
by drtomc
A bit more work on the userdb stuff. |
45 |
CREATE TABLE enrolment ( |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
46 |
loginid INT4 REFERENCES users (loginid), |
47 |
offeringid INT4 REFERENCES offerings (offeringid), |
|
48 |
result INT, |
|
49 |
supp_result INT, |
|
50 |
notes VARCHAR, |
|
51 |
PRIMARY KEY (loginid,offeringid) |
|
25
by drtomc
A bit more work on the userdb stuff. |
52 |
);
|
53 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
54 |
DROP TABLE roles CASCADE; |
25
by drtomc
A bit more work on the userdb stuff. |
55 |
CREATE TABLE roles ( |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
56 |
loginid INT4 PRIMARY KEY REFERENCES users (loginid), |
57 |
role VARCHAR |
|
58 |
);
|
|
59 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
60 |
DROP TABLE projects CASCADE; |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
61 |
CREATE TABLE projects ( |
62 |
projectid SERIAL PRIMARY KEY NOT NULL, |
|
63 |
synopsis VARCHAR, |
|
64 |
url VARCHAR, |
|
65 |
offeringid INT4 REFERENCES offerings (offeringid) NOT NULL, |
|
66 |
deadline TIMESTAMP |
|
67 |
);
|
|
68 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
69 |
DROP TABLE project_extension CASCADE; |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
70 |
CREATE TABLE project_extension ( |
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
71 |
loginid INT4 REFERENCES users (loginid), |
72 |
groupid INT4 REFERENCES groups (groupid), |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
73 |
projectid INT4 REFERENCES projects (projectid) NOT NULL, |
74 |
deadline TIMESTAMP NOT NULL, |
|
75 |
approver INT4 REFERENCES users (loginid) NOT NULL, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
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)) |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
80 |
);
|
81 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
82 |
DROP TABLE project_mark CASCADE; |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
83 |
CREATE TABLE project_mark ( |
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
84 |
loginid INT4 REFERENCES users (loginid), |
85 |
groupid INT4 REFERENCES groups (groupid), |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
86 |
projectid INT4 REFERENCES projects (projectid) NOT NULL, |
87 |
componentid INT4, |
|
88 |
marker INT4 REFERENCES users (loginid) NOT NULL, |
|
89 |
mark INT, |
|
90 |
marked TIMESTAMP, |
|
91 |
feedback VARCHAR, |
|
92 |
notes VARCHAR, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
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)) |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
96 |
);
|
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
97 |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
98 |
DROP TABLE problem CASCADE; |
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
99 |
CREATE TABLE problem ( |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
100 |
problemid SERIAL PRIMARY KEY NOT NULL, |
101 |
spec VARCHAR |
|
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
102 |
);
|
103 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
104 |
DROP TABLE problem_tags CASCADE; |
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
105 |
CREATE TABLE problem_tags ( |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
106 |
problemid INT4 REFERENCES tutorial_problem (problemid), |
107 |
tag VARCHAR NOT NULL, |
|
108 |
added_by INT4 REFERENCES users (loginid) NOT NULL, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
109 |
date TIMESTAMP NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
110 |
PRIMARY KEY (problemid,added_by,tag) |
111 |
);
|
|
112 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
113 |
DROP TABLE problem_test_case CASCADE; |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
114 |
CREATE TABLE problem_test_case ( |
115 |
problemid INT4 REFERENCES problem (problemid) NOT NULL, |
|
116 |
testcaseid SERIAL UNIQUE NOT NULL, |
|
117 |
testcase VARCHAR, |
|
118 |
description VARCHAR, |
|
119 |
visibility VARCHAR CHECK (visibility in ('public', 'protected', 'private')) |
|
120 |
);
|
|
121 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
122 |
DROP TABLE problem_test_case_tags CASCADE; |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
123 |
CREATE TABLE problem_test_case_tags ( |
124 |
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL, |
|
125 |
tag VARCHAR NOT NULL, |
|
126 |
description VARCHAR, |
|
127 |
added_by INT4 REFERENCES users (loginid) NOT NULL, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
128 |
date TIMESTAMP NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
129 |
PRIMARY KEY (testcaseid,added_by,tag) |
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
130 |
);
|
131 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
132 |
DROP TABLE problem_attempt CASCADE; |
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
133 |
CREATE TABLE problem_attempt ( |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
134 |
problemid INT4 REFERENCES problem (problemid) NOT NULL, |
135 |
loginid INT4 REFERENCES users (loginid) NOT NULL, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
136 |
date TIMESTAMP NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
137 |
attempt VARCHAR NOT NULL, |
138 |
complete BOOLEAN NOT NULL, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
139 |
PRIMARY KEY (problemid,loginid,date) |
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
140 |
);
|
141 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
142 |
DROP INDEX problem_attempt_index; |
324
by drtomc
Should all be okay now. |
143 |
CREATE INDEX problem_attempt_index ON problem_attempt (problemid, loginid); |
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
144 |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
145 |
DROP TABLE problem_attempt_breakdown CASCADE; |
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
146 |
CREATE TABLE problem_attempt_breakdown ( |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
147 |
problemid INT4 REFERENCES problem (problemid) NOT NULL, |
148 |
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL, |
|
149 |
loginid INT4 REFERENCES users (loginid) NOT NULL, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
150 |
date TIMESTAMP NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
151 |
result BOOLEAN |
152 |
);
|
|
153 |
||
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
154 |
DROP TABLE problem_prerequisites CASCADE; |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
155 |
CREATE TABLE problem_prerequisites ( |
156 |
parent INT4 REFERENCES problem (problemid) NOT NULL, |
|
157 |
child INT4 REFERENCES problem (problemid) NOT NULL, |
|
158 |
PRIMARY KEY (parent,child) |
|
159 |
);
|
|
160 |