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

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

-- We need a users database to do authorization, manage groups, &c
-- Here's a first cut.

DROP TABLE roles;
DROP TABLE enrolment;
DROP TABLE group_members;
DROP TABLE users;
DROP TABLE groups;

CREATE TABLE users (
    login       VARCHAR UNIQUE NOT NULL,
    loginid     SERIAL PRIMARY KEY NOT NULL,
    nick        VARCHAR,
    fullname    VARCHAR,
    studentid   VARCHAR, -- may be null
);

CREATE TABLE groups (
    group       VARCHAR NOT NULL,
    groupid     SERIAL PRIMARY KEY NOT NULL,
    offeringid  INT4 REFERENCES offerings (offeringid),
    nick        VARCHAR,
    UNIQUE (offeringid, group)
);

CREATE TABLE group_invitations (
    loginid     INT4 REFERENCES users (loginid),
    groupid     INT4 REFERENCES groups (groupid),
    UNIQUE (loginid,groupid)
);

CREATE TABLE group_members (
    loginid     INT4 REFERENCES users (loginid),
    groupid     INT4 REFERENCES groups (groupid),
    projectid   INT4 REFERENCES projects (projectid),
    UNIQUE (loginid,projectid),
    PRIMARY KEY (loginid,groupid)
);

CREATE TABLE enrolment (
    loginid     INT4 REFERENCES users (loginid),
    offeringid  INT4 REFERENCES offerings (offeringid),
    result      INT,
    supp_result INT,
    notes       VARCHAR,
    PRIMARY KEY (loginid,offeringid)
);

CREATE TABLE roles (
    loginid     INT4 PRIMARY KEY REFERENCES users (loginid),
    role        VARCHAR
);

CREATE TABLE projects (
    projectid   SERIAL PRIMARY KEY NOT NULL,
    synopsis    VARCHAR,
    url         VARCHAR,
    offeringid  INT4 REFERENCES offerings (offeringid) NOT NULL,
    deadline    TIMESTAMP
);

CREATE TABLE project_extension (
    login or groupid
    projectid   INT4 REFERENCES projects (projectid) NOT NULL,
    deadline    TIMESTAMP NOT NULL,
    approver    INT4 REFERENCES users (loginid) NOT NULL,
    notes       VARCHAR
);

CREATE TABLE project_mark (
    loginid or groupid
    projectid   INT4 REFERENCES projects (projectid) NOT NULL,
    componentid INT4,
    marker      INT4 REFERENCES users (loginid) NOT NULL,
    mark        INT,
    marked      TIMESTAMP,
    feedback    VARCHAR,
    notes       VARCHAR,
    PRIMARY KEY (loginid/groupid, projectid, componentid)
);

CREATE TABLE problem (
    problemid   SERIAL PRIMARY KEY NOT NULL,
    spec        VARCHAR
);

CREATE TABLE problem_tags (
    problemid   INT4 REFERENCES tutorial_problem (problemid),
    tag         VARCHAR NOT NULL,
    added_by    INT4 REFERENCES users (loginid) NOT NULL,
    when        TIMESTAMP NOT NULL,
    PRIMARY KEY (problemid,added_by,tag)
);

CREATE TABLE problem_test_case (
    problemid   INT4 REFERENCES problem (problemid) NOT NULL,
    testcaseid  SERIAL UNIQUE NOT NULL,
    testcase    VARCHAR,
    description VARCHAR,
    visibility  VARCHAR CHECK (visibility in ('public', 'protected', 'private'))
);

CREATE TABLE problem_test_case_tags (
    testcaseid  INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
    tag         VARCHAR NOT NULL,
    description VARCHAR,
    added_by    INT4 REFERENCES users (loginid) NOT NULL,
    when        TIMESTAMP NOT NULL,
    PRIMARY KEY (testcaseid,added_by,tag)
);

CREATE TABLE problem_attempt (
    problemid   INT4 REFERENCES problem (problemid) NOT NULL,
    loginid     INT4 REFERENCES users (loginid) NOT NULL,
    when        TIMESTAMP NOT NULL,
    attempt     VARCHAR NOT NULL,
    complete    BOOLEAN NOT NULL,
    PRIMARY KEY (problemid,loginid,when)
);

CREATE INDEX indexname ON problem_attempt (problemid, login);

CREATE TABLE problem_attempt_breakdown (
    problemid   INT4 REFERENCES problem (problemid) NOT NULL,
    testcaseid  INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
    loginid     INT4 REFERENCES users (loginid) NOT NULL,
    when        TIMESTAMP NOT NULL,
    result      BOOLEAN
);

CREATE TABLE problem_prerequisites (
    parent      INT4 REFERENCES problem (problemid) NOT NULL,
    child       INT4 REFERENCES problem (problemid) NOT NULL,
    PRIMARY KEY (parent,child)
);


25 by drtomc A bit more work on the userdb stuff.	1	-- We need a users database to do authorization, manage groups, &c
	2	-- Here's a first cut.
	3
	4	DROP TABLE roles;
	5	DROP TABLE enrolment;
	6	DROP TABLE group_members;
	7	DROP TABLE users;
	8	DROP TABLE groups;
	9
	10	CREATE TABLE users (
296 by drtomc Almost SQLized the database stuff from yesterday's discussion with sb.	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
25 by drtomc A bit more work on the userdb stuff.	16	);
	17
	18	CREATE TABLE groups (
296 by drtomc Almost SQLized the database stuff from yesterday's discussion with sb.	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)
25 by drtomc A bit more work on the userdb stuff.	30	);
	31
	32	CREATE TABLE group_members (
296 by drtomc Almost SQLized the database stuff from yesterday's discussion with sb.	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)
25 by drtomc A bit more work on the userdb stuff.	38	);
	39
	40	CREATE TABLE enrolment (
296 by drtomc Almost SQLized the database stuff from yesterday's discussion with sb.	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)
25 by drtomc A bit more work on the userdb stuff.	47	);
	48
	49	CREATE TABLE roles (
296 by drtomc Almost SQLized the database stuff from yesterday's discussion with sb.	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	);
281 by stevenbird extensive updates based on data modelling discussion with Tom Conway	81
	82	CREATE TABLE problem (
296 by drtomc Almost SQLized the database stuff from yesterday's discussion with sb.	83	problemid SERIAL PRIMARY KEY NOT NULL,
	84	spec VARCHAR
281 by stevenbird extensive updates based on data modelling discussion with Tom Conway	85	);
	86
	87	CREATE TABLE problem_tags (
296 by drtomc Almost SQLized the database stuff from yesterday's discussion with sb.	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)
281 by stevenbird extensive updates based on data modelling discussion with Tom Conway	110	);
	111
	112	CREATE TABLE problem_attempt (
296 by drtomc Almost SQLized the database stuff from yesterday's discussion with sb.	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)
281 by stevenbird extensive updates based on data modelling discussion with Tom Conway	119	);
	120
	121	CREATE INDEX indexname ON problem_attempt (problemid, login);
	122
	123	CREATE TABLE problem_attempt_breakdown (
296 by drtomc Almost SQLized the database stuff from yesterday's discussion with sb.	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