~azzar1/unity/add-show-desktop-key : contents of userdb/users.sql at revision 400

~azzar1/unity/add-show-desktop-key : (revision 400)

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