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