X-Git-Url: http://git.hcoop.net/bpt/portal.git/blobdiff_plain/4ac8f6393ed3c9f055168063490fc7503fb71762..5c705bcb58ea122dbd2b8163edc3e24c194d2ab1:/tables.sql

diff --git a/tables.sql b/tables.sql
index 2f40212..17d755b 100644
--- a/tables.sql
+++ b/tables.sql
@@ -13,7 +13,10 @@ CREATE TABLE MemberApp(
 	ipaddr TEXT,
 	confirmed TIMESTAMP,
 	decided TIMESTAMP,
-	msg TEXT NOT NULL);
+	msg TEXT NOT NULL,
+	unix_passwd TEXT NOT NULL,
+	paypal TEXT,
+	checkout TEXT);
 
 CREATE SEQUENCE MemberAppSeq START 1;
 
@@ -35,6 +38,9 @@ CREATE TABLE WebUser(
 	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);
 
@@ -98,6 +104,7 @@ CREATE TABLE Poll(
 	starts DATE NOT NULL,
 	ends DATE NOT NULL,
 	votes INTEGER NOT NULL,
+	official BOOL NOT NULL,
 	FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 
 CREATE SEQUENCE PollSeq START 1;
@@ -180,6 +187,8 @@ CREATE TABLE SupIssue(
 	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);
 
@@ -203,14 +212,30 @@ CREATE TABLE SupSubscription(
 	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);
+	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;
 
@@ -221,6 +246,7 @@ CREATE TABLE Domain(
 	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;
@@ -232,6 +258,7 @@ CREATE TABLE MailingList(
 	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;
@@ -247,12 +274,99 @@ CREATE TABLE AppVote(
 	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
+	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 = 'retired') AS mem
+				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;