[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,
	unix_passwd TEXT NOT NULL,
	paypal TEXT,
	checkout TEXT);

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,
	paypal TEXT,
	checkout TEXT
	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, paypal, checkout
		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, paypal, checkout
		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);

CREATE SEQUENCE CertSeq START 1;

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