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

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

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