[hcoop/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,
	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,
	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 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,
	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 SecSeq START 1;
Commit	Line	Data
f3f3ad24 AC	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
208e2cbc AC	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,
f3f3ad24	37	app INTEGER NOT NULL,
aaa50197	38	shares INTEGER NOT NULL,
f3f3ad24 AC	39	FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE,
f3f3ad24 AC	40	FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE);
208e2cbc AC	41
	42	CREATE SEQUENCE WebUserSeq START 1;
	43
	44	INSERT INTO WebUser
c9247c1b AC	45	(id, name, rname, bal, joined) VALUES
c9247c1b AC	46	(0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);
208e2cbc AC	47
	48	CREATE TABLE WebGroup(
	49	id INTEGER PRIMARY KEY,
	50	name TEXT NOT NULL);
	51
c9247c1b	52	CREATE SEQUENCE WebGroupSeq START 2;
208e2cbc AC	53
	54	INSERT INTO WebGroup
	55	(id, name) VALUES
	56	(0, 'root');
	57
c9247c1b AC	58	INSERT INTO WebGroup
	59	(id, name) VALUES
	60	(1, 'paying');
	61
208e2cbc AC	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
c9247c1b AC	73	INSERT INTO Membership
	74	(grp, usr) VALUES
	75	(1, 0);
	76
208e2cbc AC	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
e68ddb80 AC	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
d6223be9 AC	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);
e68ddb80	128
d6223be9	129	CREATE SEQUENCE ContactKindSeq START 1;
208e2cbc	130
d6223be9	131	CREATE TABLE Contact(
ee587f7f	132	id INTEGER PRIMARY KEY,
d6223be9 AC	133	usr INTEGER NOT NULL,
	134	knd INTEGER NOT NULL,
	135	v TEXT NOT NULL,
	136	priv INTEGER NOT NULL,
d6223be9 AC	137	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
d6223be9 AC	138	FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE);
ee587f7f AC	139
ee587f7f AC	140	CREATE SEQUENCE ContactSeq START 1;
b340786b AC	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);
88a858ea AC	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
5a2812ca AC	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,
	184	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	185	FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
	186
	187	CREATE SEQUENCE SupIssueSeq START 1;
	188
	189	CREATE TABLE SupPost(
	190	id INTEGER PRIMARY KEY,
	191	usr INTEGER NOT NULL,
	192	iss INTEGER NOT NULL,
	193	body TEXT NOT NULL,
	194	stamp TIMESTAMP NOT NULL,
	195	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	196	FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE);
	197
	198	CREATE SEQUENCE SupPostSeq START 1;
	199
	200	CREATE TABLE SupSubscription(
	201	usr INTEGER NOT NULL,
	202	cat INTEGER NOT NULL,
	203	PRIMARY KEY (usr, cat),
	204	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	205	FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
	206
18eeb749 AC	207	CREATE TABLE WebNode(
	208	id INTEGER PRIMARY KEY,
	209	name TEXT NOT NULL,
	210	descr TEXT NOT NULL,
	211	debian TEXT NOT NULL);
	212
	213	INSERT INTO WebNode (id, name, descr, debian)
	214	VALUES (0, 'fyodor', 'old server', 'testing');
	215	INSERT INTO WebNode (id, name, descr, debian)
	216	VALUES (1, 'deleuze', 'main server', 'stable');
	217	INSERT INTO WebNode (id, name, descr, debian)
	218	VALUES (2, 'mire', 'member web server', 'stable');
	219
6b23a78b AC	220	CREATE TABLE Apt(
	221	id INTEGER PRIMARY KEY,
	222	usr INTEGER NOT NULL,
18eeb749	223	node INTEGER NOT NULL,
5da9f4a9	224	data TEXT NOT NULL,
6b23a78b AC	225	msg TEXT NOT NULL,
	226	status INTEGER NOT NULL,
	227	stamp TIMESTAMP NOT NULL,
	228	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
5a2812ca	229
6b23a78b	230	CREATE SEQUENCE AptSeq START 1;
5da9f4a9 AC	231
	232	CREATE TABLE Domain(
	233	id INTEGER PRIMARY KEY,
	234	usr INTEGER NOT NULL,
	235	data TEXT NOT NULL,
	236	msg TEXT NOT NULL,
	237	status INTEGER NOT NULL,
	238	stamp TIMESTAMP NOT NULL,
	239	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	240
	241	CREATE SEQUENCE DomainSeq START 1;
9d1c0e98 AC	242
	243	CREATE TABLE MailingList(
	244	id INTEGER PRIMARY KEY,
	245	usr INTEGER NOT NULL,
	246	data TEXT NOT NULL,
	247	msg TEXT NOT NULL,
	248	status INTEGER NOT NULL,
	249	stamp TIMESTAMP NOT NULL,
	250	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	251
	252	CREATE SEQUENCE MailingListSeq START 1;
27e48ace AC	253
	254	CREATE TABLE DirectoryPref(
	255	usr INTEGER PRIMARY KEY,
	256	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
a90da8b1	257
a90da8b1 AC	258	CREATE TABLE AppVote(
	259	app INTEGER NOT NULL,
	260	usr INTEGER NOT NULL,
	261	PRIMARY KEY (app, usr),
	262	FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE,
	263	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	264
711b47a1	265	CREATE VIEW WebUserPaying
aaa50197	266	AS SELECT id, name, rname, bal, joined, app, shares
711b47a1 AC	267	FROM WebUser
	268	JOIN (SELECT usr FROM Membership JOIN WebGroup
	269	ON grp = WebGroup.id
aaa50197	270	AND WebGroup.name = 'paying') AS bob
711b47a1 AC	271	ON usr = WebUser.id;
711b47a1 AC	272
4ac8f639	273	CREATE VIEW WebUserActive
aaa50197	274	AS SELECT id, name, rname, bal, joined, app, shares
4ac8f639 AC	275	FROM WebUser
4ac8f639 AC	276	LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup
a4ccdb5e	277	ON grp = WebGroup.id AND (WebGroup.name IN ('retired', 'phantom'))) AS mem
4ac8f639	278	ON usr = WebUser.id
4ac8f639 AC	279	WHERE usr IS NULL;
4ac8f639 AC	280
a4ccdb5e AC	281	CREATE TABLE RollCall(
	282	id INTEGER PRIMARY KEY,
	283	title TEXT NOT NULL,
	284	msg TEXT NOT NULL,
	285	started TIMESTAMP NOT NULL);
	286
	287	CREATE SEQUENCE RollCallSeq START 1;
	288
	289	CREATE TABLE RollCallEntry(
	290	rol INTEGER NOT NULL,
	291	usr INTEGER NOT NULL,
	292	code TEXT NOT NULL,
	293	responded TIMESTAMP,
	294	PRIMARY KEY (rol, usr),
	295	FOREIGN KEY (rol) REFERENCES RollCall(id) ON DELETE CASCADE,
	296	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
dfb0d0d7 AC	297
	298	CREATE TABLE Sec(
	299	id INTEGER PRIMARY KEY,
	300	usr INTEGER NOT NULL,
	301	data TEXT NOT NULL,
	302	msg TEXT NOT NULL,
	303	status INTEGER NOT NULL,
	304	stamp TIMESTAMP NOT NULL,
	305	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	306
	307	CREATE SEQUENCE SecSeq START 1;