« back to all changes in this revision
Viewing changes to userdb/users.sql
- browse files at revision 296
- compare with another revision
- download diff
- download tarball
- view history from revision 296
- Committer: drtomc
- Date: 2008-01-25 00:45:21 UTC
- Revision ID: svn-v3-trunk0:2b9c9e99-6f39-0410-b283-7f802c844ae2:trunk:296
Almost SQLized the database stuff from yesterday's discussion with sb.
TODO: resolve the loginid/groupid issue; document the design.
TODO: resolve the loginid/groupid issue; document the design.
- files modified:
- userdb/users.sql
added
removed
userdb/users.sql
8
8
DROP TABLE groups;
9
9
10
10
CREATE TABLE users (
11
login varchar(80) PRIMARY KEY, -- login id
12
nick varchar(80),
13
fullname varchar(80),
14
studentid varchar(80) or NULL ****
11
login VARCHAR UNIQUE NOT NULL,
12
loginid SERIAL PRIMARY KEY NOT NULL,
13
nick VARCHAR,
14
fullname VARCHAR,
15
studentid VARCHAR, -- may be null
15
16
);
16
17
17
18
CREATE TABLE groups (
18
groupid varchar(18) PRIMARY KEY, -- group name Y^4-S^9-G^3 **** use offering-id + group number (compound key)
19
nick varchar(80), -- group nickname
20
subject varchar(9), -- subject code **** use "offerings" table from CASMAS
21
year varchar(4) -- when
19
group VARCHAR NOT NULL,
20
groupid SERIAL PRIMARY KEY NOT NULL,
21
offeringid INT4 REFERENCES offerings (offeringid),
22
nick VARCHAR,
23
UNIQUE (offeringid, group)
24
);
25
26
CREATE TABLE group_invitations (
27
loginid INT4 REFERENCES users (loginid),
28
groupid INT4 REFERENCES groups (groupid),
29
UNIQUE (loginid,groupid)
22
30
);
23
31
24
32
CREATE TABLE group_members (
25
login varchar(80) REFERENCES users (login),
26
groupid varchar(18) REFERENCES groups (groupid)
33
loginid INT4 REFERENCES users (loginid),
34
groupid INT4 REFERENCES groups (groupid),
35
projectid INT4 REFERENCES projects (projectid),
36
UNIQUE (loginid,projectid),
37
PRIMARY KEY (loginid,groupid)
27
38
);
28
39
29
40
CREATE TABLE enrolment (
30
login varchar(80) REFERENCES users (login),
31
subject varchar(9),
32
result
33
supp_result
34
year varchar(4)
41
loginid INT4 REFERENCES users (loginid),
42
offeringid INT4 REFERENCES offerings (offeringid),
43
result INT,
44
supp_result INT,
45
notes VARCHAR,
46
PRIMARY KEY (loginid,offeringid)
35
47
);
36
48
37
49
CREATE TABLE roles (
38
login varchar(80) REFERENCES users (login),
39
role varchar(8)
40
);
41
42
CREATE TABLE project (
43
projectid
44
synopsis
45
url
46
subject
47
deadline
48
);
49
50
CREATE TABLE extension (
51
login or groupid
52
projectid
53
deadline
54
approver
55
comment
56
);
57
58
CREATE TABLE mark (
59
login or groupid
60
projectid
61
componentid
62
marker
63
mark
64
timestamp
65
feedback
66
comment
67
KEY: (login/groupid, projectid, componentid)
68
);
69
70
################
50
loginid INT4 PRIMARY KEY REFERENCES users (loginid),
51
role VARCHAR
52
);
53
54
CREATE TABLE projects (
55
projectid SERIAL PRIMARY KEY NOT NULL,
56
synopsis VARCHAR,
57
url VARCHAR,
58
offeringid INT4 REFERENCES offerings (offeringid) NOT NULL,
59
deadline TIMESTAMP
60
);
61
62
CREATE TABLE project_extension (
63
login or groupid
64
projectid INT4 REFERENCES projects (projectid) NOT NULL,
65
deadline TIMESTAMP NOT NULL,
66
approver INT4 REFERENCES users (loginid) NOT NULL,
67
notes VARCHAR
68
);
69
70
CREATE TABLE project_mark (
71
loginid or groupid
72
projectid INT4 REFERENCES projects (projectid) NOT NULL,
73
componentid INT4,
74
marker INT4 REFERENCES users (loginid) NOT NULL,
75
mark INT,
76
marked TIMESTAMP,
77
feedback VARCHAR,
78
notes VARCHAR,
79
PRIMARY KEY (loginid/groupid, projectid, componentid)
80
);
71
81
72
82
CREATE TABLE problem (
73
problemid
74
specification
75
test (xml)
83
problemid SERIAL PRIMARY KEY NOT NULL,
84
spec VARCHAR
76
85
);
77
86
78
87
CREATE TABLE problem_tags (
79
problemid
80
tag
81
added_by
82
timestamp
88
problemid INT4 REFERENCES tutorial_problem (problemid),
89
tag VARCHAR NOT NULL,
90
added_by INT4 REFERENCES users (loginid) NOT NULL,
91
when TIMESTAMP NOT NULL,
92
PRIMARY KEY (problemid,added_by,tag)
93
);
94
95
CREATE TABLE problem_test_case (
96
problemid INT4 REFERENCES problem (problemid) NOT NULL,
97
testcaseid SERIAL UNIQUE NOT NULL,
98
testcase VARCHAR,
99
description VARCHAR,
100
visibility VARCHAR CHECK (visibility in ('public', 'protected', 'private'))
101
);
102
103
CREATE TABLE problem_test_case_tags (
104
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
105
tag VARCHAR NOT NULL,
106
description VARCHAR,
107
added_by INT4 REFERENCES users (loginid) NOT NULL,
108
when TIMESTAMP NOT NULL,
109
PRIMARY KEY (testcaseid,added_by,tag)
83
110
);
84
111
85
112
CREATE TABLE problem_attempt (
86
problemid
87
login
88
datetime
89
submission
90
complete (boolean)
91
KEY: (problemid, login, datetime)
113
problemid INT4 REFERENCES problem (problemid) NOT NULL,
114
loginid INT4 REFERENCES users (loginid) NOT NULL,
115
when TIMESTAMP NOT NULL,
116
attempt VARCHAR NOT NULL,
117
complete BOOLEAN NOT NULL,
118
PRIMARY KEY (problemid,loginid,when)
92
119
);
93
120
94
121
CREATE INDEX indexname ON problem_attempt (problemid, login);
95
122
96
123
CREATE TABLE problem_attempt_breakdown (
97
problemid
98
testcaseid
99
login
100
datetime
101
result (boolean) **** doesnt tell academic which concepts students are struggling with
102
);
103
104
CREATE TABLE problem_test_case (
105
problemid
106
testcaseid
107
testcase (sourced from xml)
108
description
109
visibility
110
tags (xref to tag table; break out)
111
);
112
113
# concept, curriculum, difficulty
114
CREATE TABLE tag (
115
tag
116
documentation
117
added_by
118
timestamp
119
);
120
121
# for multipart problems
122
CREATE TABLE prerequisite_problem (
123
parent_problemid
124
child_problemid
125
);
126
127
128
129
INSERT INTO users (login,nick) values ('conway', 'Tom');
130
INSERT INTO roles (login,role) values ('conway', 'student');
131
INSERT INTO users (login,nick) values ('apeel', 'Andrew');
132
INSERT INTO roles (login,role) values ('apeel', 'student');
133
INSERT INTO users (login,nick) values ('mgiuca', 'Matt');
134
INSERT INTO roles (login,role) values ('mgiuca', 'tutor');
135
INSERT INTO users (login,nick) values ('sb', 'Steven');
136
INSERT INTO roles (login,role) values ('sb', 'lecturer');
137
INSERT INTO users (login,nick) values ('mpp', 'Mike');
138
INSERT INTO roles (login,role) values ('mpp', 'student');
139
INSERT INTO users (login,nick) values ('ivo', 'Ivo');
140
INSERT INTO roles (login,role) values ('ivo', 'admin');
141
142
INSERT INTO groups (groupid, nick, subject, year) values ('2007-INFO10001-321', 'Purple Alert', 'INFO10001', '2008');
143
INSERT INTO groups (groupid, nick, subject, year) values ('2007-INFO10001-322', 'Blind Illuminati', 'INFO10001', '2008');
144
145
INSERT INTO group_members (login,groupid) values ('conway', '2007-INFO10001-321');
146
INSERT INTO group_members (login,groupid) values ('apeel', '2007-INFO10001-321');
147
INSERT INTO group_members (login,groupid) values ('mgiuca', '2007-INFO10001-321');
148
INSERT INTO group_members (login,groupid) values ('sb', '2007-INFO10001-321');
149
INSERT INTO group_members (login,groupid) values ('mpp', '2007-INFO10001-322');
150
INSERT INTO group_members (login,groupid) values ('ivo', '2007-INFO10001-322');
151
152
INSERT INTO enrolment (login,subject,year) values ('conway' , 'INFO10001', '2008');
153
INSERT INTO enrolment (login,subject,year) values ('apeel' , 'INFO10001', '2008');
154
INSERT INTO enrolment (login,subject,year) values ('mgiuca' , 'INFO10001', '2008');
155
INSERT INTO enrolment (login,subject,year) values ('sb' , 'INFO10001', '2008');
156
INSERT INTO enrolment (login,subject,year) values ('mpp' , 'INFO10001', '2008');
157
INSERT INTO enrolment (login,subject,year) values ('ivo' , 'INFO10001', '2008');
124
problemid INT4 REFERENCES problem (problemid) NOT NULL,
125
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
126
loginid INT4 REFERENCES users (loginid) NOT NULL,
127
when TIMESTAMP NOT NULL,
128
result BOOLEAN
129
);
130
131
CREATE TABLE problem_prerequisites (
132
parent INT4 REFERENCES problem (problemid) NOT NULL,
133
child INT4 REFERENCES problem (problemid) NOT NULL,
134
PRIMARY KEY (parent,child)
135
);
136
Loggerhead is a web-based interface for Breezy
Version: 2.0.1