[hcoop/zz_old/portal.git] / tables.sql

CREATE TABLE MemberApp(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	rname TEXT NOT NULL,
	gname TEXT,
	email TEXT NOT NULL,
	forward BOOLEAN NOT NULL,
	uses TEXT NOT NULL,
	other TEXT NOT NULL,
	passwd TEXT NOT NULL,
	status INTEGER NOT NULL,
	applied TIMESTAMP NOT NULL,
	ipaddr TEXT,
	confirmed TIMESTAMP,
	decided TIMESTAMP,
	msg TEXT NOT NULL);

CREATE SEQUENCE MemberAppSeq START 1;

CREATE TABLE Balance(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	amount REAL NOT NULL);

CREATE SEQUENCE BalanceSeq START 1;

INSERT INTO Balance
	(id, name, amount) VALUES
	(0, 'adam', 0.0);

CREATE TABLE WebUser(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	rname TEXT NOT NULL,
	bal INTEGER NOT NULL,
	joined TIMESTAMP NOT NULL,
	app INTEGER NOT NULL,
	FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE,
	FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE);

CREATE SEQUENCE WebUserSeq START 1;

INSERT INTO WebUser
	(id, name, rname, bal, joined) VALUES
	(0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);

CREATE TABLE WebGroup(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL);

CREATE SEQUENCE WebGroupSeq START 2;

INSERT INTO WebGroup
	(id, name) VALUES
	(0, 'root');

INSERT INTO WebGroup
	(id, name) VALUES
	(1, 'paying');

CREATE TABLE Membership(
	grp INTEGER NOT NULL,
	usr INTEGER NOT NULL,
	PRIMARY KEY (grp, usr),
	FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);

INSERT INTO Membership
	(grp, usr) VALUES
	(0, 0);

INSERT INTO Membership
	(grp, usr) VALUES
	(1, 0);

CREATE TABLE Transaction(
	id INTEGER PRIMARY KEY,
	descr TEXT NOT NULL,
	amount REAL NOT NULL,
	d DATE NOT NULL,
	stamp TIMESTAMP NOT NULL);

CREATE SEQUENCE TransactionSeq START 1;

CREATE TABLE Charge(
	trn INTEGER NOT NULL,
	usr INTEGER NOT NULL,
	amount REAL NOT NULL,
	PRIMARY KEY (trn, usr),
	FOREIGN KEY (trn) REFERENCES Transaction(id) ON DELETE CASCADE,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);

CREATE TABLE Poll(
	id INTEGER PRIMARY KEY,
	usr INTEGER NOT NULL,
	title TEXT NOT NULL,
	descr TEXT NOT NULL,
	starts DATE NOT NULL,
	ends DATE NOT NULL,
	votes INTEGER NOT NULL,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);

CREATE SEQUENCE PollSeq START 1;

CREATE TABLE PollChoice(
	id INTEGER PRIMARY KEY,
	pol INTEGER NOT NULL,
	seq REAL NOT NULL,
	descr TEXT NOT NULL,
	FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE);

CREATE SEQUENCE PollChoiceSeq START 1;

CREATE TABLE Vote(
	usr INTEGER NOT NULL,
	cho INTEGER NOT NULL,
	PRIMARY KEY (usr, cho),
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE);

CREATE TABLE ContactKind(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	url BOOLEAN NOT NULL,
	urlPrefix TEXT,
	urlPostfix TEXT);

CREATE SEQUENCE ContactKindSeq START 1;

CREATE TABLE Contact(
	id INTEGER PRIMARY KEY,
	usr INTEGER NOT NULL,
	knd INTEGER NOT NULL,
	v TEXT NOT NULL,
	priv INTEGER NOT NULL,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE);

CREATE SEQUENCE ContactSeq START 1;

CREATE TABLE Location(
	id INTEGER PRIMARY KEY,
	parent INTEGER,
	name TEXT NOT NULL,
	FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE);

CREATE SEQUENCE LocationSeq START 1;

CREATE TABLE Lives(
	usr INTEGER NOT NULL,
	loc INTEGER NOT NULL,
	PRIMARY KEY (usr, loc),
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE);

CREATE TABLE Link(
	id INTEGER PRIMARY KEY,
	usr INTEGER NOT NULL,
	title TEXT NOT NULL,
	url TEXT NOT NULL,
	descr TEXT NOT NULL,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);

CREATE SEQUENCE LinkSeq START 1;

CREATE TABLE SupCategory(
	id INTEGER PRIMARY KEY,
	grp INTEGER NOT NULL,
	name TEXT NOT NULL,
	descr TEXT NOT NULL,
	FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE);

CREATE SEQUENCE SupCategorySeq START 1;

CREATE TABLE SupIssue(
	id INTEGER PRIMARY KEY,
	usr INTEGER NOT NULL,
	cat INTEGER NOT NULL,
	title TEXT NOT NULL,
	priv BOOLEAN NOT NULL,
	status INTEGER NOT NULL,
	stamp TIMESTAMP NOT NULL,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);

CREATE SEQUENCE SupIssueSeq START 1;

CREATE TABLE SupPost(
	id INTEGER PRIMARY KEY,
	usr INTEGER NOT NULL,
	iss INTEGER NOT NULL,
	body TEXT NOT NULL,
	stamp TIMESTAMP NOT NULL,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE);

CREATE SEQUENCE SupPostSeq START 1;

CREATE TABLE SupSubscription(
	usr INTEGER NOT NULL,
	cat INTEGER NOT NULL,
	PRIMARY KEY (usr, cat),
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);

CREATE TABLE Apt(
	id INTEGER PRIMARY KEY,
	usr INTEGER NOT NULL,
	data TEXT NOT NULL,
	msg TEXT NOT NULL,
	status INTEGER NOT NULL,
	stamp TIMESTAMP NOT NULL,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);

CREATE SEQUENCE AptSeq START 1;

CREATE TABLE Domain(
	id INTEGER PRIMARY KEY,
	usr INTEGER NOT NULL,
	data TEXT NOT NULL,
	msg TEXT NOT NULL,
	status INTEGER NOT NULL,
	stamp TIMESTAMP NOT NULL,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);

CREATE SEQUENCE DomainSeq START 1;

CREATE TABLE MailingList(
	id INTEGER PRIMARY KEY,
	usr INTEGER NOT NULL,
	data TEXT NOT NULL,
	msg TEXT NOT NULL,
	status INTEGER NOT NULL,
	stamp TIMESTAMP NOT NULL,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);

CREATE SEQUENCE MailingListSeq START 1;

CREATE TABLE DirectoryPref(
	usr INTEGER PRIMARY KEY,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);

CREATE TABLE AppVote(
	app INTEGER NOT NULL,
	usr INTEGER NOT NULL,
	PRIMARY KEY (app, usr),
	FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);

CREATE VIEW WebUserActive
	AS SELECT id, name, rname, bal, joined, app
		FROM WebUser
			LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup
				ON grp = WebGroup.id AND (WebGroup.name IN ('retired', 'phantom'))) AS mem
				ON usr = WebUser.id 
		WHERE usr IS NULL;

CREATE TABLE RollCall(
	id INTEGER PRIMARY KEY,
	title TEXT NOT NULL,
	msg TEXT NOT NULL,
	started TIMESTAMP NOT NULL);

CREATE SEQUENCE RollCallSeq START 1;

CREATE TABLE RollCallEntry(
	rol INTEGER NOT NULL,
	usr INTEGER NOT NULL,
	code TEXT NOT NULL,
	responded TIMESTAMP,
	PRIMARY KEY (rol, usr),
	FOREIGN KEY (rol) REFERENCES RollCall(id) ON DELETE CASCADE,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
Commit	Line	Data
5146e435	1	CREATE TABLE MemberApp(
	2	id INTEGER PRIMARY KEY,
	3	name TEXT NOT NULL,
	4	rname TEXT NOT NULL,
	5	gname TEXT,
	6	email TEXT NOT NULL,
	7	forward BOOLEAN NOT NULL,
	8	uses TEXT NOT NULL,
	9	other TEXT NOT NULL,
	10	passwd TEXT NOT NULL,
	11	status INTEGER NOT NULL,
	12	applied TIMESTAMP NOT NULL,
	13	ipaddr TEXT,
	14	confirmed TIMESTAMP,
	15	decided TIMESTAMP,
	16	msg TEXT NOT NULL);
	17
	18	CREATE SEQUENCE MemberAppSeq START 1;
	19
8d347a33	20	CREATE TABLE Balance(
	21	id INTEGER PRIMARY KEY,
	22	name TEXT NOT NULL,
	23	amount REAL NOT NULL);
	24
	25	CREATE SEQUENCE BalanceSeq START 1;
	26
	27	INSERT INTO Balance
	28	(id, name, amount) VALUES
	29	(0, 'adam', 0.0);
	30
	31	CREATE TABLE WebUser(
	32	id INTEGER PRIMARY KEY,
	33	name TEXT NOT NULL,
	34	rname TEXT NOT NULL,
	35	bal INTEGER NOT NULL,
	36	joined TIMESTAMP NOT NULL,
5146e435	37	app INTEGER NOT NULL,
	38	FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE,
	39	FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE);
8d347a33	40
	41	CREATE SEQUENCE WebUserSeq START 1;
	42
	43	INSERT INTO WebUser
3d009713	44	(id, name, rname, bal, joined) VALUES
3d009713	45	(0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);
8d347a33	46
	47	CREATE TABLE WebGroup(
	48	id INTEGER PRIMARY KEY,
	49	name TEXT NOT NULL);
	50
3d009713	51	CREATE SEQUENCE WebGroupSeq START 2;
8d347a33	52
	53	INSERT INTO WebGroup
	54	(id, name) VALUES
	55	(0, 'root');
	56
3d009713	57	INSERT INTO WebGroup
	58	(id, name) VALUES
	59	(1, 'paying');
	60
8d347a33	61	CREATE TABLE Membership(
	62	grp INTEGER NOT NULL,
	63	usr INTEGER NOT NULL,
	64	PRIMARY KEY (grp, usr),
	65	FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE,
	66	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	67
	68	INSERT INTO Membership
	69	(grp, usr) VALUES
	70	(0, 0);
	71
3d009713	72	INSERT INTO Membership
	73	(grp, usr) VALUES
	74	(1, 0);
	75
8d347a33	76	CREATE TABLE Transaction(
	77	id INTEGER PRIMARY KEY,
	78	descr TEXT NOT NULL,
	79	amount REAL NOT NULL,
	80	d DATE NOT NULL,
	81	stamp TIMESTAMP NOT NULL);
	82
	83	CREATE SEQUENCE TransactionSeq START 1;
	84
	85	CREATE TABLE Charge(
	86	trn INTEGER NOT NULL,
	87	usr INTEGER NOT NULL,
	88	amount REAL NOT NULL,
	89	PRIMARY KEY (trn, usr),
	90	FOREIGN KEY (trn) REFERENCES Transaction(id) ON DELETE CASCADE,
	91	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	92
57c305c1	93	CREATE TABLE Poll(
	94	id INTEGER PRIMARY KEY,
	95	usr INTEGER NOT NULL,
	96	title TEXT NOT NULL,
	97	descr TEXT NOT NULL,
	98	starts DATE NOT NULL,
	99	ends DATE NOT NULL,
	100	votes INTEGER NOT NULL,
	101	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	102
	103	CREATE SEQUENCE PollSeq START 1;
	104
	105	CREATE TABLE PollChoice(
	106	id INTEGER PRIMARY KEY,
	107	pol INTEGER NOT NULL,
	108	seq REAL NOT NULL,
	109	descr TEXT NOT NULL,
	110	FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE);
	111
	112	CREATE SEQUENCE PollChoiceSeq START 1;
	113
	114	CREATE TABLE Vote(
	115	usr INTEGER NOT NULL,
	116	cho INTEGER NOT NULL,
	117	PRIMARY KEY (usr, cho),
	118	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	119	FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE);
	120
46b0c2df	121	CREATE TABLE ContactKind(
	122	id INTEGER PRIMARY KEY,
	123	name TEXT NOT NULL,
	124	url BOOLEAN NOT NULL,
	125	urlPrefix TEXT,
	126	urlPostfix TEXT);
57c305c1	127
46b0c2df	128	CREATE SEQUENCE ContactKindSeq START 1;
8d347a33	129
46b0c2df	130	CREATE TABLE Contact(
369e1577	131	id INTEGER PRIMARY KEY,
46b0c2df	132	usr INTEGER NOT NULL,
	133	knd INTEGER NOT NULL,
	134	v TEXT NOT NULL,
	135	priv INTEGER NOT NULL,
46b0c2df	136	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
46b0c2df	137	FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE);
369e1577	138
369e1577	139	CREATE SEQUENCE ContactSeq START 1;
51520441	140
	141	CREATE TABLE Location(
	142	id INTEGER PRIMARY KEY,
	143	parent INTEGER,
	144	name TEXT NOT NULL,
	145	FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE);
	146
	147	CREATE SEQUENCE LocationSeq START 1;
	148
	149	CREATE TABLE Lives(
	150	usr INTEGER NOT NULL,
	151	loc INTEGER NOT NULL,
	152	PRIMARY KEY (usr, loc),
	153	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	154	FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE);
29c3cc58	155
	156	CREATE TABLE Link(
	157	id INTEGER PRIMARY KEY,
	158	usr INTEGER NOT NULL,
	159	title TEXT NOT NULL,
	160	url TEXT NOT NULL,
	161	descr TEXT NOT NULL,
	162	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	163
	164	CREATE SEQUENCE LinkSeq START 1;
	165
c7311141	166	CREATE TABLE SupCategory(
	167	id INTEGER PRIMARY KEY,
	168	grp INTEGER NOT NULL,
	169	name TEXT NOT NULL,
	170	descr TEXT NOT NULL,
	171	FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE);
	172
	173	CREATE SEQUENCE SupCategorySeq START 1;
	174
	175	CREATE TABLE SupIssue(
	176	id INTEGER PRIMARY KEY,
	177	usr INTEGER NOT NULL,
	178	cat INTEGER NOT NULL,
	179	title TEXT NOT NULL,
	180	priv BOOLEAN NOT NULL,
	181	status INTEGER NOT NULL,
	182	stamp TIMESTAMP NOT NULL,
	183	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	184	FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
	185
	186	CREATE SEQUENCE SupIssueSeq START 1;
	187
	188	CREATE TABLE SupPost(
	189	id INTEGER PRIMARY KEY,
	190	usr INTEGER NOT NULL,
	191	iss INTEGER NOT NULL,
	192	body TEXT NOT NULL,
	193	stamp TIMESTAMP NOT NULL,
	194	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	195	FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE);
	196
	197	CREATE SEQUENCE SupPostSeq START 1;
	198
	199	CREATE TABLE SupSubscription(
	200	usr INTEGER NOT NULL,
	201	cat INTEGER NOT NULL,
	202	PRIMARY KEY (usr, cat),
	203	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	204	FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
	205
36d5f176	206	CREATE TABLE Apt(
	207	id INTEGER PRIMARY KEY,
	208	usr INTEGER NOT NULL,
ff2b7604	209	data TEXT NOT NULL,
36d5f176	210	msg TEXT NOT NULL,
	211	status INTEGER NOT NULL,
	212	stamp TIMESTAMP NOT NULL,
	213	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
c7311141	214
36d5f176	215	CREATE SEQUENCE AptSeq START 1;
ff2b7604	216
	217	CREATE TABLE Domain(
	218	id INTEGER PRIMARY KEY,
	219	usr INTEGER NOT NULL,
	220	data TEXT NOT NULL,
	221	msg TEXT NOT NULL,
	222	status INTEGER NOT NULL,
	223	stamp TIMESTAMP NOT NULL,
	224	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	225
	226	CREATE SEQUENCE DomainSeq START 1;
78304862	227
	228	CREATE TABLE MailingList(
	229	id INTEGER PRIMARY KEY,
	230	usr INTEGER NOT NULL,
	231	data TEXT NOT NULL,
	232	msg TEXT NOT NULL,
	233	status INTEGER NOT NULL,
	234	stamp TIMESTAMP NOT NULL,
	235	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	236
	237	CREATE SEQUENCE MailingListSeq START 1;
566fd712	238
	239	CREATE TABLE DirectoryPref(
	240	usr INTEGER PRIMARY KEY,
	241	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
20a679fc	242
20a679fc	243	CREATE TABLE AppVote(
	244	app INTEGER NOT NULL,
	245	usr INTEGER NOT NULL,
	246	PRIMARY KEY (app, usr),
	247	FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE,
	248	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	249
47bc9315	250	CREATE VIEW WebUserActive
	251	AS SELECT id, name, rname, bal, joined, app
	252	FROM WebUser
	253	LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup
add44c00	254	ON grp = WebGroup.id AND (WebGroup.name IN ('retired', 'phantom'))) AS mem
47bc9315	255	ON usr = WebUser.id
47bc9315	256	WHERE usr IS NULL;
47bc9315	257
add44c00	258	CREATE TABLE RollCall(
	259	id INTEGER PRIMARY KEY,
	260	title TEXT NOT NULL,
	261	msg TEXT NOT NULL,
	262	started TIMESTAMP NOT NULL);
	263
	264	CREATE SEQUENCE RollCallSeq START 1;
	265
	266	CREATE TABLE RollCallEntry(
	267	rol INTEGER NOT NULL,
	268	usr INTEGER NOT NULL,
	269	code TEXT NOT NULL,
	270	responded TIMESTAMP,
	271	PRIMARY KEY (rol, usr),
	272	FOREIGN KEY (rol) REFERENCES RollCall(id) ON DELETE CASCADE,
	273	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);