[bpt/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,
	unix_passwd 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;

CREATE TABLE Ip(
	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 IpSeq START 1;

CREATE TABLE Cert(
	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,
	FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);

CREATE SEQUENCE CertSeq 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,
a2d53da2 AC	16	msg TEXT NOT NULL,
a2d53da2 AC	17	unix_passwd TEXT NOT NULL);
f3f3ad24 AC	18
	19	CREATE SEQUENCE MemberAppSeq START 1;
	20
208e2cbc AC	21	CREATE TABLE Balance(
	22	id INTEGER PRIMARY KEY,
	23	name TEXT NOT NULL,
	24	amount REAL NOT NULL);
	25
	26	CREATE SEQUENCE BalanceSeq START 1;
	27
	28	INSERT INTO Balance
	29	(id, name, amount) VALUES
	30	(0, 'adam', 0.0);
	31
	32	CREATE TABLE WebUser(
	33	id INTEGER PRIMARY KEY,
	34	name TEXT NOT NULL,
	35	rname TEXT NOT NULL,
	36	bal INTEGER NOT NULL,
	37	joined TIMESTAMP NOT NULL,
f3f3ad24	38	app INTEGER NOT NULL,
aaa50197	39	shares INTEGER NOT NULL,
f3f3ad24 AC	40	FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE,
f3f3ad24 AC	41	FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE);
208e2cbc AC	42
	43	CREATE SEQUENCE WebUserSeq START 1;
	44
	45	INSERT INTO WebUser
c9247c1b AC	46	(id, name, rname, bal, joined) VALUES
c9247c1b AC	47	(0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);
208e2cbc AC	48
	49	CREATE TABLE WebGroup(
	50	id INTEGER PRIMARY KEY,
	51	name TEXT NOT NULL);
	52
c9247c1b	53	CREATE SEQUENCE WebGroupSeq START 2;
208e2cbc AC	54
	55	INSERT INTO WebGroup
	56	(id, name) VALUES
	57	(0, 'root');
	58
c9247c1b AC	59	INSERT INTO WebGroup
	60	(id, name) VALUES
	61	(1, 'paying');
	62
208e2cbc AC	63	CREATE TABLE Membership(
	64	grp INTEGER NOT NULL,
	65	usr INTEGER NOT NULL,
	66	PRIMARY KEY (grp, usr),
	67	FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE,
	68	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	69
	70	INSERT INTO Membership
	71	(grp, usr) VALUES
	72	(0, 0);
	73
c9247c1b AC	74	INSERT INTO Membership
	75	(grp, usr) VALUES
	76	(1, 0);
	77
208e2cbc AC	78	CREATE TABLE Transaction(
	79	id INTEGER PRIMARY KEY,
	80	descr TEXT NOT NULL,
	81	amount REAL NOT NULL,
	82	d DATE NOT NULL,
	83	stamp TIMESTAMP NOT NULL);
	84
	85	CREATE SEQUENCE TransactionSeq START 1;
	86
	87	CREATE TABLE Charge(
	88	trn INTEGER NOT NULL,
	89	usr INTEGER NOT NULL,
	90	amount REAL NOT NULL,
	91	PRIMARY KEY (trn, usr),
	92	FOREIGN KEY (trn) REFERENCES Transaction(id) ON DELETE CASCADE,
	93	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	94
e68ddb80 AC	95	CREATE TABLE Poll(
	96	id INTEGER PRIMARY KEY,
	97	usr INTEGER NOT NULL,
	98	title TEXT NOT NULL,
	99	descr TEXT NOT NULL,
	100	starts DATE NOT NULL,
	101	ends DATE NOT NULL,
	102	votes INTEGER NOT NULL,
	103	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	104
	105	CREATE SEQUENCE PollSeq START 1;
	106
	107	CREATE TABLE PollChoice(
	108	id INTEGER PRIMARY KEY,
	109	pol INTEGER NOT NULL,
	110	seq REAL NOT NULL,
	111	descr TEXT NOT NULL,
	112	FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE);
	113
	114	CREATE SEQUENCE PollChoiceSeq START 1;
	115
	116	CREATE TABLE Vote(
	117	usr INTEGER NOT NULL,
	118	cho INTEGER NOT NULL,
	119	PRIMARY KEY (usr, cho),
	120	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	121	FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE);
	122
d6223be9 AC	123	CREATE TABLE ContactKind(
	124	id INTEGER PRIMARY KEY,
	125	name TEXT NOT NULL,
	126	url BOOLEAN NOT NULL,
	127	urlPrefix TEXT,
	128	urlPostfix TEXT);
e68ddb80	129
d6223be9	130	CREATE SEQUENCE ContactKindSeq START 1;
208e2cbc	131
d6223be9	132	CREATE TABLE Contact(
ee587f7f	133	id INTEGER PRIMARY KEY,
d6223be9 AC	134	usr INTEGER NOT NULL,
	135	knd INTEGER NOT NULL,
	136	v TEXT NOT NULL,
	137	priv INTEGER NOT NULL,
d6223be9 AC	138	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
d6223be9 AC	139	FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE);
ee587f7f AC	140
ee587f7f AC	141	CREATE SEQUENCE ContactSeq START 1;
b340786b AC	142
	143	CREATE TABLE Location(
	144	id INTEGER PRIMARY KEY,
	145	parent INTEGER,
	146	name TEXT NOT NULL,
	147	FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE);
	148
	149	CREATE SEQUENCE LocationSeq START 1;
	150
	151	CREATE TABLE Lives(
	152	usr INTEGER NOT NULL,
	153	loc INTEGER NOT NULL,
	154	PRIMARY KEY (usr, loc),
	155	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	156	FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE);
88a858ea AC	157
	158	CREATE TABLE Link(
	159	id INTEGER PRIMARY KEY,
	160	usr INTEGER NOT NULL,
	161	title TEXT NOT NULL,
	162	url TEXT NOT NULL,
	163	descr TEXT NOT NULL,
	164	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	165
	166	CREATE SEQUENCE LinkSeq START 1;
	167
5a2812ca AC	168	CREATE TABLE SupCategory(
	169	id INTEGER PRIMARY KEY,
	170	grp INTEGER NOT NULL,
	171	name TEXT NOT NULL,
	172	descr TEXT NOT NULL,
	173	FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE);
	174
	175	CREATE SEQUENCE SupCategorySeq START 1;
	176
	177	CREATE TABLE SupIssue(
	178	id INTEGER PRIMARY KEY,
	179	usr INTEGER NOT NULL,
	180	cat INTEGER NOT NULL,
	181	title TEXT NOT NULL,
	182	priv BOOLEAN NOT NULL,
	183	status INTEGER NOT NULL,
	184	stamp TIMESTAMP NOT NULL,
4d46d3eb AC	185	pstamp TIMESTAMP,
4d46d3eb AC	186	cstamp TIMESTAMP,
5a2812ca AC	187	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	188	FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
	189
	190	CREATE SEQUENCE SupIssueSeq START 1;
	191
	192	CREATE TABLE SupPost(
	193	id INTEGER PRIMARY KEY,
	194	usr INTEGER NOT NULL,
	195	iss INTEGER NOT NULL,
	196	body TEXT NOT NULL,
	197	stamp TIMESTAMP NOT NULL,
	198	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	199	FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE);
	200
	201	CREATE SEQUENCE SupPostSeq START 1;
	202
	203	CREATE TABLE SupSubscription(
	204	usr INTEGER NOT NULL,
	205	cat INTEGER NOT NULL,
	206	PRIMARY KEY (usr, cat),
	207	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	208	FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
	209
18eeb749 AC	210	CREATE TABLE WebNode(
	211	id INTEGER PRIMARY KEY,
	212	name TEXT NOT NULL,
	213	descr TEXT NOT NULL,
	214	debian TEXT NOT NULL);
	215
	216	INSERT INTO WebNode (id, name, descr, debian)
	217	VALUES (0, 'fyodor', 'old server', 'testing');
	218	INSERT INTO WebNode (id, name, descr, debian)
	219	VALUES (1, 'deleuze', 'main server', 'stable');
	220	INSERT INTO WebNode (id, name, descr, debian)
	221	VALUES (2, 'mire', 'member web server', 'stable');
	222
6b23a78b AC	223	CREATE TABLE Apt(
	224	id INTEGER PRIMARY KEY,
	225	usr INTEGER NOT NULL,
18eeb749	226	node INTEGER NOT NULL,
5da9f4a9	227	data TEXT NOT NULL,
6b23a78b AC	228	msg TEXT NOT NULL,
	229	status INTEGER NOT NULL,
	230	stamp TIMESTAMP NOT NULL,
a75ed94b	231	cstamp TIMESTAMP,
308f44e7 AC	232	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
308f44e7 AC	233	FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
5a2812ca	234
6b23a78b	235	CREATE SEQUENCE AptSeq START 1;
5da9f4a9 AC	236
	237	CREATE TABLE Domain(
	238	id INTEGER PRIMARY KEY,
	239	usr INTEGER NOT NULL,
	240	data TEXT NOT NULL,
	241	msg TEXT NOT NULL,
	242	status INTEGER NOT NULL,
	243	stamp TIMESTAMP NOT NULL,
a75ed94b	244	cstamp TIMESTAMP,
5da9f4a9 AC	245	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	246
	247	CREATE SEQUENCE DomainSeq START 1;
9d1c0e98 AC	248
	249	CREATE TABLE MailingList(
	250	id INTEGER PRIMARY KEY,
	251	usr INTEGER NOT NULL,
	252	data TEXT NOT NULL,
	253	msg TEXT NOT NULL,
	254	status INTEGER NOT NULL,
	255	stamp TIMESTAMP NOT NULL,
a75ed94b	256	cstamp TIMESTAMP,
9d1c0e98 AC	257	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	258
	259	CREATE SEQUENCE MailingListSeq START 1;
27e48ace AC	260
	261	CREATE TABLE DirectoryPref(
	262	usr INTEGER PRIMARY KEY,
	263	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
a90da8b1	264
a90da8b1 AC	265	CREATE TABLE AppVote(
	266	app INTEGER NOT NULL,
	267	usr INTEGER NOT NULL,
	268	PRIMARY KEY (app, usr),
	269	FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE,
	270	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
	271
711b47a1	272	CREATE VIEW WebUserPaying
aaa50197	273	AS SELECT id, name, rname, bal, joined, app, shares
711b47a1 AC	274	FROM WebUser
	275	JOIN (SELECT usr FROM Membership JOIN WebGroup
	276	ON grp = WebGroup.id
aaa50197	277	AND WebGroup.name = 'paying') AS bob
711b47a1 AC	278	ON usr = WebUser.id;
711b47a1 AC	279
4ac8f639	280	CREATE VIEW WebUserActive
aaa50197	281	AS SELECT id, name, rname, bal, joined, app, shares
4ac8f639 AC	282	FROM WebUser
4ac8f639 AC	283	LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup
a4ccdb5e	284	ON grp = WebGroup.id AND (WebGroup.name IN ('retired', 'phantom'))) AS mem
4ac8f639	285	ON usr = WebUser.id
4ac8f639 AC	286	WHERE usr IS NULL;
4ac8f639 AC	287
a4ccdb5e AC	288	CREATE TABLE RollCall(
	289	id INTEGER PRIMARY KEY,
	290	title TEXT NOT NULL,
	291	msg TEXT NOT NULL,
	292	started TIMESTAMP NOT NULL);
	293
	294	CREATE SEQUENCE RollCallSeq START 1;
	295
	296	CREATE TABLE RollCallEntry(
	297	rol INTEGER NOT NULL,
	298	usr INTEGER NOT NULL,
	299	code TEXT NOT NULL,
	300	responded TIMESTAMP,
	301	PRIMARY KEY (rol, usr),
	302	FOREIGN KEY (rol) REFERENCES RollCall(id) ON DELETE CASCADE,
	303	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
dfb0d0d7 AC	304
	305	CREATE TABLE Sec(
	306	id INTEGER PRIMARY KEY,
	307	usr INTEGER NOT NULL,
3d2ed222	308	node INTEGER NOT NULL,
dfb0d0d7 AC	309	data TEXT NOT NULL,
	310	msg TEXT NOT NULL,
	311	status INTEGER NOT NULL,
	312	stamp TIMESTAMP NOT NULL,
a75ed94b	313	cstamp TIMESTAMP,
308f44e7 AC	314	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
308f44e7 AC	315	FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
dfb0d0d7 AC	316
dfb0d0d7 AC	317	CREATE SEQUENCE SecSeq START 1;
9a4c122a AC	318
	319	CREATE TABLE Ip(
	320	id INTEGER PRIMARY KEY,
	321	usr INTEGER NOT NULL,
	322	node INTEGER NOT NULL,
	323	data TEXT NOT NULL,
	324	msg TEXT NOT NULL,
	325	status INTEGER NOT NULL,
	326	stamp TIMESTAMP NOT NULL,
	327	cstamp TIMESTAMP,
	328	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	329	FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
	330
	331	CREATE SEQUENCE IpSeq START 1;
95a4653e AC	332
	333	CREATE TABLE Cert(
	334	id INTEGER PRIMARY KEY,
	335	usr INTEGER NOT NULL,
	336	data TEXT NOT NULL,
	337	msg TEXT NOT NULL,
	338	status INTEGER NOT NULL,
	339	stamp TIMESTAMP NOT NULL,
	340	cstamp TIMESTAMP,
	341	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
	342	FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
	343
	344	CREATE SEQUENCE CertSeq START 1;