354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
1 |
CREATE TABLE login ( |
446
by drtomc
users.sql: constrain the rolenm to be from a given set of roles. |
2 |
loginid SERIAL PRIMARY KEY NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
3 |
login VARCHAR UNIQUE NOT NULL, |
446
by drtomc
users.sql: constrain the rolenm to be from a given set of roles. |
4 |
passhash VARCHAR, |
475
by mattgiuca
Commited some earlier changes to users.sql (not committed earlier due to |
5 |
state VARCHAR NOT NULL CHECK (state in ('no_agreement', 'pending', |
6 |
'enabled', 'disabled')), |
|
7 |
rolenm VARCHAR NOT NULL CHECK (rolenm in ('guest', 'student', |
|
8 |
'tutor', 'lecturer', |
|
9 |
'admin')), |
|
415
by drtomc
userdb: added a unixid field to store the uid of the user. |
10 |
unixid INT UNIQUE NOT NULL, -- unix user id |
475
by mattgiuca
Commited some earlier changes to users.sql (not committed earlier due to |
11 |
nick VARCHAR NOT NULL, |
12 |
pass_exp TIMESTAMP, |
|
13 |
acct_exp TIMESTAMP, |
|
14 |
last_login TIMESTAMP, |
|
462
by mattgiuca
userdb/users.sql: Added email field to login. |
15 |
email VARCHAR, |
446
by drtomc
users.sql: constrain the rolenm to be from a given set of roles. |
16 |
fullname VARCHAR NOT NULL, |
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
17 |
studentid VARCHAR -- may be null |
25
by drtomc
A bit more work on the userdb stuff. |
18 |
);
|
19 |
||
353
by drtomc
Start addressing sb's suggestions. |
20 |
CREATE TABLE offering ( |
324
by drtomc
Should all be okay now. |
21 |
offeringid SERIAL PRIMARY KEY NOT NULL, |
22 |
subj_name VARCHAR NOT NULL, |
|
23 |
subj_code VARCHAR NOT NULL, |
|
24 |
year CHAR(4) NOT NULL, |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
25 |
semester CHAR(1) NOT NULL, |
26 |
url VARCHAR |
|
324
by drtomc
Should all be okay now. |
27 |
);
|
28 |
||
355
by drtomc
Fix a few typos and glitches to actually create the ivle database. |
29 |
CREATE TABLE project ( |
30 |
projectid SERIAL PRIMARY KEY NOT NULL, |
|
31 |
synopsis VARCHAR, |
|
32 |
url VARCHAR, |
|
33 |
offeringid INT4 REFERENCES offering (offeringid) NOT NULL, |
|
34 |
deadline TIMESTAMP |
|
35 |
);
|
|
36 |
||
37 |
CREATE TABLE project_group ( |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
38 |
groupnm VARCHAR NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
39 |
groupid SERIAL PRIMARY KEY NOT NULL, |
353
by drtomc
Start addressing sb's suggestions. |
40 |
offeringid INT4 REFERENCES offering (offeringid), |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
41 |
nick VARCHAR, |
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
42 |
createdby INT4 REFERENCES login (loginid) NOT NULL, |
43 |
epoch TIMESTAMP NOT NULL, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
44 |
UNIQUE (offeringid, groupnm) |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
45 |
);
|
46 |
||
353
by drtomc
Start addressing sb's suggestions. |
47 |
CREATE TABLE group_invitation ( |
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
48 |
loginid INT4 REFERENCES login (loginid) NOT NULL, |
355
by drtomc
Fix a few typos and glitches to actually create the ivle database. |
49 |
groupid INT4 REFERENCES project_group (groupid) NOT NULL, |
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
50 |
inviter INT4 REFERENCES login (loginid) NOT NULL, |
51 |
invited TIMESTAMP NOT NULL, |
|
52 |
accepted TIMESTAMP, |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
53 |
UNIQUE (loginid,groupid) |
25
by drtomc
A bit more work on the userdb stuff. |
54 |
);
|
55 |
||
353
by drtomc
Start addressing sb's suggestions. |
56 |
CREATE TABLE group_member ( |
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
57 |
loginid INT4 REFERENCES login (loginid), |
355
by drtomc
Fix a few typos and glitches to actually create the ivle database. |
58 |
groupid INT4 REFERENCES project_group (groupid), |
353
by drtomc
Start addressing sb's suggestions. |
59 |
projectid INT4 REFERENCES project (projectid), |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
60 |
UNIQUE (loginid,projectid), |
61 |
PRIMARY KEY (loginid,groupid) |
|
25
by drtomc
A bit more work on the userdb stuff. |
62 |
);
|
63 |
||
64 |
CREATE TABLE enrolment ( |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
65 |
loginid INT4 REFERENCES login (loginid), |
353
by drtomc
Start addressing sb's suggestions. |
66 |
offeringid INT4 REFERENCES offering (offeringid), |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
67 |
result INT, |
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
68 |
special_result VARCHAR, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
69 |
supp_result INT, |
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
70 |
special_supp_result VARCHAR, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
71 |
notes VARCHAR, |
72 |
PRIMARY KEY (loginid,offeringid) |
|
25
by drtomc
A bit more work on the userdb stuff. |
73 |
);
|
74 |
||
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
75 |
CREATE TABLE assessed ( |
76 |
assessedid SERIAL PRIMARY KEY NOT NULL, |
|
77 |
loginid INT4 REFERENCES login (loginid), |
|
355
by drtomc
Fix a few typos and glitches to actually create the ivle database. |
78 |
groupid INT4 REFERENCES project_group (groupid), |
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
79 |
-- exactly one of loginid and groupid must be non-null
|
80 |
CHECK ((loginid IS NOT NULL AND groupid IS NULL) |
|
81 |
OR (loginid IS NULL AND groupid IS NOT NULL)) |
|
82 |
);
|
|
83 |
||
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
84 |
CREATE TABLE project_extension ( |
391
by drtomc
Fix a couple of typos. |
85 |
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL, |
353
by drtomc
Start addressing sb's suggestions. |
86 |
projectid INT4 REFERENCES project (projectid) NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
87 |
deadline TIMESTAMP NOT NULL, |
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
88 |
approver INT4 REFERENCES login (loginid) NOT NULL, |
89 |
notes VARCHAR |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
90 |
);
|
91 |
||
92 |
CREATE TABLE project_mark ( |
|
391
by drtomc
Fix a couple of typos. |
93 |
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL, |
353
by drtomc
Start addressing sb's suggestions. |
94 |
projectid INT4 REFERENCES project (projectid) NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
95 |
componentid INT4, |
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
96 |
marker INT4 REFERENCES login (loginid) NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
97 |
mark INT, |
98 |
marked TIMESTAMP, |
|
99 |
feedback VARCHAR, |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
100 |
notes VARCHAR |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
101 |
);
|
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
102 |
|
103 |
CREATE TABLE problem ( |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
104 |
problemid SERIAL PRIMARY KEY NOT NULL, |
105 |
spec VARCHAR |
|
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
106 |
);
|
107 |
||
353
by drtomc
Start addressing sb's suggestions. |
108 |
CREATE TABLE problem_tag ( |
392
by drtomc
Fix another glitch. |
109 |
problemid INT4 REFERENCES problem (problemid), |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
110 |
tag VARCHAR NOT NULL, |
366
by drtomc
Somehow a couple of changed that address issues sb raised didn't get committed previously. Here they are. |
111 |
description VARCHAR, |
112 |
standard BOOLEAN NOT NULL, |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
113 |
added_by INT4 REFERENCES login (loginid) NOT NULL, |
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
114 |
date TIMESTAMP NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
115 |
PRIMARY KEY (problemid,added_by,tag) |
116 |
);
|
|
117 |
||
118 |
CREATE TABLE problem_test_case ( |
|
119 |
problemid INT4 REFERENCES problem (problemid) NOT NULL, |
|
120 |
testcaseid SERIAL UNIQUE NOT NULL, |
|
121 |
testcase VARCHAR, |
|
122 |
description VARCHAR, |
|
123 |
visibility VARCHAR CHECK (visibility in ('public', 'protected', 'private')) |
|
124 |
);
|
|
125 |
||
353
by drtomc
Start addressing sb's suggestions. |
126 |
CREATE TABLE problem_test_case_tag ( |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
127 |
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL, |
128 |
tag VARCHAR NOT NULL, |
|
129 |
description VARCHAR, |
|
366
by drtomc
Somehow a couple of changed that address issues sb raised didn't get committed previously. Here they are. |
130 |
standard BOOLEAN NOT NULL, |
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
131 |
added_by INT4 REFERENCES login (loginid) NOT NULL, |
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
132 |
date TIMESTAMP NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
133 |
PRIMARY KEY (testcaseid,added_by,tag) |
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
134 |
);
|
135 |
||
136 |
CREATE TABLE problem_attempt ( |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
137 |
problemid INT4 REFERENCES problem (problemid) NOT NULL, |
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
138 |
loginid INT4 REFERENCES login (loginid) NOT NULL, |
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
139 |
date TIMESTAMP NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
140 |
attempt VARCHAR NOT NULL, |
141 |
complete BOOLEAN NOT NULL, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
142 |
PRIMARY KEY (problemid,loginid,date) |
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
143 |
);
|
144 |
||
324
by drtomc
Should all be okay now. |
145 |
CREATE INDEX problem_attempt_index ON problem_attempt (problemid, loginid); |
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
146 |
|
147 |
CREATE TABLE problem_attempt_breakdown ( |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
148 |
problemid INT4 REFERENCES problem (problemid) NOT NULL, |
149 |
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL, |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
150 |
loginid INT4 REFERENCES login (loginid) NOT NULL, |
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
151 |
date TIMESTAMP NOT NULL, |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
152 |
result BOOLEAN |
153 |
);
|
|
154 |
||
353
by drtomc
Start addressing sb's suggestions. |
155 |
CREATE TABLE problem_prerequisite ( |
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
156 |
parent INT4 REFERENCES problem (problemid) NOT NULL, |
157 |
child INT4 REFERENCES problem (problemid) NOT NULL, |
|
158 |
PRIMARY KEY (parent,child) |
|
159 |
);
|
|
160 |