[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,
	shares 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,
	pstamp TIMESTAMP,
	cstamp TIMESTAMP,
	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 WebNode(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	descr TEXT NOT NULL,
	debian TEXT NOT NULL);

INSERT INTO WebNode (id, name, descr, debian)
	VALUES (0, 'fyodor', 'old server', 'testing');
INSERT INTO WebNode (id, name, descr, debian)
	VALUES (1, 'deleuze', 'main server', 'stable');
INSERT INTO WebNode (id, name, descr, debian)
	VALUES (2, 'mire', 'member web server', 'stable');

CREATE TABLE Apt(
	id INTEGER PRIMARY KEY,
	usr INTEGER NOT NULL,
	node INTEGER NOT NULL,
	data TEXT NOT NULL,
	msg TEXT NOT NULL,
	status INTEGER NOT NULL,
	stamp TIMESTAMP NOT NULL,
	cstamp TIMESTAMP,
	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	FOREIGN KEY (node) REFERENCES WebNode(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,
	cstamp TIMESTAMP,
	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,
	cstamp TIMESTAMP,
	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 WebUserPaying
	AS SELECT id, name, rname, bal, joined, app, shares
		FROM WebUser
			JOIN (SELECT usr FROM Membership JOIN WebGroup
				ON grp = WebGroup.id
					AND WebGroup.name = 'paying') AS bob
				ON usr = WebUser.id;

CREATE VIEW WebUserActive
	AS SELECT id, name, rname, bal, joined, app, shares
		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);

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

CREATE SEQUENCE SecSeq START 1;
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,
892e3ea1	38	shares INTEGER NOT NULL,
5146e435	39	FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE,
5146e435	40	FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE);
8d347a33	41
	42	CREATE SEQUENCE WebUserSeq START 1;
	43
	44	INSERT INTO WebUser
3d009713	45	(id, name, rname, bal, joined) VALUES
3d009713	46	(0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);
8d347a33	47
	48	CREATE TABLE WebGroup(
	49	id INTEGER PRIMARY KEY,
	50	name TEXT NOT NULL);
	51
3d009713	52	CREATE SEQUENCE WebGroupSeq START 2;
8d347a33	53
	54	INSERT INTO WebGroup
	55	(id, name) VALUES
	56	(0, 'root');
	57
3d009713	58	INSERT INTO WebGroup
	59	(id, name) VALUES
	60	(1, 'paying');
	61
8d347a33	62	CREATE TABLE Membership(
	63	grp INTEGER NOT NULL,
	64	usr INTEGER NOT NULL,
	65	PRIMARY KEY (grp, usr),
	66	FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE,
	67	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	68
	69	INSERT INTO Membership
	70	(grp, usr) VALUES
	71	(0, 0);
	72
3d009713	73	INSERT INTO Membership
	74	(grp, usr) VALUES
	75	(1, 0);
	76
8d347a33	77	CREATE TABLE Transaction(
	78	id INTEGER PRIMARY KEY,
	79	descr TEXT NOT NULL,
	80	amount REAL NOT NULL,
	81	d DATE NOT NULL,
	82	stamp TIMESTAMP NOT NULL);
	83
	84	CREATE SEQUENCE TransactionSeq START 1;
	85
	86	CREATE TABLE Charge(
	87	trn INTEGER NOT NULL,
	88	usr INTEGER NOT NULL,
	89	amount REAL NOT NULL,
	90	PRIMARY KEY (trn, usr),
	91	FOREIGN KEY (trn) REFERENCES Transaction(id) ON DELETE CASCADE,
	92	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	93
57c305c1	94	CREATE TABLE Poll(
	95	id INTEGER PRIMARY KEY,
	96	usr INTEGER NOT NULL,
	97	title TEXT NOT NULL,
	98	descr TEXT NOT NULL,
	99	starts DATE NOT NULL,
	100	ends DATE NOT NULL,
	101	votes INTEGER NOT NULL,
	102	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	103
	104	CREATE SEQUENCE PollSeq START 1;
	105
	106	CREATE TABLE PollChoice(
	107	id INTEGER PRIMARY KEY,
	108	pol INTEGER NOT NULL,
	109	seq REAL NOT NULL,
	110	descr TEXT NOT NULL,
	111	FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE);
	112
	113	CREATE SEQUENCE PollChoiceSeq START 1;
	114
	115	CREATE TABLE Vote(
	116	usr INTEGER NOT NULL,
	117	cho INTEGER NOT NULL,
	118	PRIMARY KEY (usr, cho),
	119	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	120	FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE);
	121
46b0c2df	122	CREATE TABLE ContactKind(
	123	id INTEGER PRIMARY KEY,
	124	name TEXT NOT NULL,
	125	url BOOLEAN NOT NULL,
	126	urlPrefix TEXT,
	127	urlPostfix TEXT);
57c305c1	128
46b0c2df	129	CREATE SEQUENCE ContactKindSeq START 1;
8d347a33	130
46b0c2df	131	CREATE TABLE Contact(
369e1577	132	id INTEGER PRIMARY KEY,
46b0c2df	133	usr INTEGER NOT NULL,
	134	knd INTEGER NOT NULL,
	135	v TEXT NOT NULL,
	136	priv INTEGER NOT NULL,
46b0c2df	137	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
46b0c2df	138	FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE);
369e1577	139
369e1577	140	CREATE SEQUENCE ContactSeq START 1;
51520441	141
	142	CREATE TABLE Location(
	143	id INTEGER PRIMARY KEY,
	144	parent INTEGER,
	145	name TEXT NOT NULL,
	146	FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE);
	147
	148	CREATE SEQUENCE LocationSeq START 1;
	149
	150	CREATE TABLE Lives(
	151	usr INTEGER NOT NULL,
	152	loc INTEGER NOT NULL,
	153	PRIMARY KEY (usr, loc),
	154	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	155	FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE);
29c3cc58	156
	157	CREATE TABLE Link(
	158	id INTEGER PRIMARY KEY,
	159	usr INTEGER NOT NULL,
	160	title TEXT NOT NULL,
	161	url TEXT NOT NULL,
	162	descr TEXT NOT NULL,
	163	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	164
	165	CREATE SEQUENCE LinkSeq START 1;
	166
c7311141	167	CREATE TABLE SupCategory(
	168	id INTEGER PRIMARY KEY,
	169	grp INTEGER NOT NULL,
	170	name TEXT NOT NULL,
	171	descr TEXT NOT NULL,
	172	FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE);
	173
	174	CREATE SEQUENCE SupCategorySeq START 1;
	175
	176	CREATE TABLE SupIssue(
	177	id INTEGER PRIMARY KEY,
	178	usr INTEGER NOT NULL,
	179	cat INTEGER NOT NULL,
	180	title TEXT NOT NULL,
	181	priv BOOLEAN NOT NULL,
	182	status INTEGER NOT NULL,
	183	stamp TIMESTAMP NOT NULL,
1365f9a0	184	pstamp TIMESTAMP,
1365f9a0	185	cstamp TIMESTAMP,
c7311141	186	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	187	FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
	188
	189	CREATE SEQUENCE SupIssueSeq START 1;
	190
	191	CREATE TABLE SupPost(
	192	id INTEGER PRIMARY KEY,
	193	usr INTEGER NOT NULL,
	194	iss INTEGER NOT NULL,
	195	body TEXT NOT NULL,
	196	stamp TIMESTAMP NOT NULL,
	197	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	198	FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE);
	199
	200	CREATE SEQUENCE SupPostSeq START 1;
	201
	202	CREATE TABLE SupSubscription(
	203	usr INTEGER NOT NULL,
	204	cat INTEGER NOT NULL,
	205	PRIMARY KEY (usr, cat),
	206	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	207	FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
	208
8023de7b	209	CREATE TABLE WebNode(
	210	id INTEGER PRIMARY KEY,
	211	name TEXT NOT NULL,
	212	descr TEXT NOT NULL,
	213	debian TEXT NOT NULL);
	214
	215	INSERT INTO WebNode (id, name, descr, debian)
	216	VALUES (0, 'fyodor', 'old server', 'testing');
	217	INSERT INTO WebNode (id, name, descr, debian)
	218	VALUES (1, 'deleuze', 'main server', 'stable');
	219	INSERT INTO WebNode (id, name, descr, debian)
	220	VALUES (2, 'mire', 'member web server', 'stable');
	221
36d5f176	222	CREATE TABLE Apt(
	223	id INTEGER PRIMARY KEY,
	224	usr INTEGER NOT NULL,
8023de7b	225	node INTEGER NOT NULL,
ff2b7604	226	data TEXT NOT NULL,
36d5f176	227	msg TEXT NOT NULL,
	228	status INTEGER NOT NULL,
	229	stamp TIMESTAMP NOT NULL,
cac002c5	230	cstamp TIMESTAMP,
9396e2cf	231	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
9396e2cf	232	FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
c7311141	233
36d5f176	234	CREATE SEQUENCE AptSeq START 1;
ff2b7604	235
	236	CREATE TABLE Domain(
	237	id INTEGER PRIMARY KEY,
	238	usr INTEGER NOT NULL,
	239	data TEXT NOT NULL,
	240	msg TEXT NOT NULL,
	241	status INTEGER NOT NULL,
	242	stamp TIMESTAMP NOT NULL,
cac002c5	243	cstamp TIMESTAMP,
ff2b7604	244	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	245
	246	CREATE SEQUENCE DomainSeq START 1;
78304862	247
	248	CREATE TABLE MailingList(
	249	id INTEGER PRIMARY KEY,
	250	usr INTEGER NOT NULL,
	251	data TEXT NOT NULL,
	252	msg TEXT NOT NULL,
	253	status INTEGER NOT NULL,
	254	stamp TIMESTAMP NOT NULL,
cac002c5	255	cstamp TIMESTAMP,
78304862	256	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	257
	258	CREATE SEQUENCE MailingListSeq START 1;
566fd712	259
	260	CREATE TABLE DirectoryPref(
	261	usr INTEGER PRIMARY KEY,
	262	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
20a679fc	263
20a679fc	264	CREATE TABLE AppVote(
	265	app INTEGER NOT NULL,
	266	usr INTEGER NOT NULL,
	267	PRIMARY KEY (app, usr),
	268	FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE,
	269	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	270
ea455810	271	CREATE VIEW WebUserPaying
892e3ea1	272	AS SELECT id, name, rname, bal, joined, app, shares
ea455810	273	FROM WebUser
	274	JOIN (SELECT usr FROM Membership JOIN WebGroup
	275	ON grp = WebGroup.id
892e3ea1	276	AND WebGroup.name = 'paying') AS bob
ea455810	277	ON usr = WebUser.id;
ea455810	278
47bc9315	279	CREATE VIEW WebUserActive
892e3ea1	280	AS SELECT id, name, rname, bal, joined, app, shares
47bc9315	281	FROM WebUser
47bc9315	282	LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup
add44c00	283	ON grp = WebGroup.id AND (WebGroup.name IN ('retired', 'phantom'))) AS mem
47bc9315	284	ON usr = WebUser.id
47bc9315	285	WHERE usr IS NULL;
47bc9315	286
add44c00	287	CREATE TABLE RollCall(
	288	id INTEGER PRIMARY KEY,
	289	title TEXT NOT NULL,
	290	msg TEXT NOT NULL,
	291	started TIMESTAMP NOT NULL);
	292
	293	CREATE SEQUENCE RollCallSeq START 1;
	294
	295	CREATE TABLE RollCallEntry(
	296	rol INTEGER NOT NULL,
	297	usr INTEGER NOT NULL,
	298	code TEXT NOT NULL,
	299	responded TIMESTAMP,
	300	PRIMARY KEY (rol, usr),
	301	FOREIGN KEY (rol) REFERENCES RollCall(id) ON DELETE CASCADE,
	302	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
3ad30cf6	303
	304	CREATE TABLE Sec(
	305	id INTEGER PRIMARY KEY,
	306	usr INTEGER NOT NULL,
08e839b2	307	node INTEGER NOT NULL,
3ad30cf6	308	data TEXT NOT NULL,
	309	msg TEXT NOT NULL,
	310	status INTEGER NOT NULL,
	311	stamp TIMESTAMP NOT NULL,
cac002c5	312	cstamp TIMESTAMP,
9396e2cf	313	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
9396e2cf	314	FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
3ad30cf6	315
3ad30cf6	316	CREATE SEQUENCE SecSeq START 1;