X-Git-Url: http://git.hcoop.net/hcoop/portal.git/blobdiff_plain/d6223be994735931643a8205a2c2c9724640fa21..0a817f4036507d8699b1f8aee6e649e91bcd68fe:/tables.sql

diff --git a/tables.sql b/tables.sql
index 5481e29..ccaca80 100644
--- a/tables.sql
+++ b/tables.sql
@@ -1,3 +1,25 @@
+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,
@@ -15,19 +37,24 @@ CREATE TABLE WebUser(
 	rname TEXT NOT NULL,
 	bal INTEGER NOT NULL,
 	joined TIMESTAMP NOT NULL,
-	FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE);
+	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, bal) VALUES
-	(0, 'adam', 0);
+	(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 4;
+CREATE SEQUENCE WebGroupSeq START 2;
 
 INSERT INTO WebGroup
 	(id, name) VALUES
@@ -35,15 +62,7 @@ INSERT INTO WebGroup
 
 INSERT INTO WebGroup
 	(id, name) VALUES
-	(1, 'money');
-
-INSERT INTO WebGroup
-	(id, name) VALUES
-	(2, 'paying');
-
-INSERT INTO WebGroup
-	(id, name) VALUES
-	(3, 'poll');
+	(1, 'paying');
 
 CREATE TABLE Membership(
 	grp INTEGER NOT NULL,
@@ -56,6 +75,10 @@ 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,
@@ -81,6 +104,8 @@ CREATE TABLE Poll(
 	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;
@@ -111,11 +136,238 @@ CREATE TABLE ContactKind(
 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,
-	PRIMARY KEY (usr, knd),
 	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 	FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE);
-	
\ No newline at end of file
+
+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;
