cvsimport

[hcoop/zz_old/portal.git] / tables.sql

diff --git a/tables.sql b/tables.sql
index 9335981..8ff5251 100644 (file)
--- 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,7 +37,12 @@ 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;
 
@@ -159,6 +186,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);
 
@@ -182,14 +211,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;
 
@@ -200,6 +245,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;
@@ -211,6 +257,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;
@@ -219,23 +266,6 @@ CREATE TABLE DirectoryPref(
        usr INTEGER PRIMARY KEY,
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 
-CREATE TABLE MemberApp(
-       id INTEGER PRIMARY KEY,
-       name TEXT NOT NULL,
-       rname TEXT NOT NULL,
-       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,
-       confirmed TIMESTAMP,
-       decided TIMESTAMP,
-       msg TEXT NOT NULL);
-
-CREATE SEQUENCE MemberAppSeq START 1;
-
 CREATE TABLE AppVote(
        app INTEGER NOT NULL,
        usr INTEGER NOT NULL,
@@ -243,3 +273,87 @@ 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, 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;