Tweakadeedooda while running through our first real application in a while

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

diff --git a/tables.sql b/tables.sql
index 264ecb8..56a68ed 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,
 CREATE TABLE Balance(
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,


@@ -15,24 +37,33 @@ CREATE TABLE WebUser(
        rname TEXT NOT NULL,
        bal INTEGER NOT NULL,
        joined TIMESTAMP NOT NULL,
        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
 
 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 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
        (0, 'root');
 
 
 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,
 CREATE TABLE Membership(
        grp INTEGER NOT NULL,
        usr INTEGER NOT NULL,


@@ -44,6 +75,10 @@ INSERT INTO Membership
        (grp, usr) VALUES
        (0, 0);
 
        (grp, usr) VALUES
        (0, 0);
 

+INSERT INTO Membership
+       (grp, usr) VALUES
+       (1, 0);
+

 CREATE TABLE Transaction(
        id INTEGER PRIMARY KEY,
        descr TEXT NOT NULL,
 CREATE TABLE Transaction(
        id INTEGER PRIMARY KEY,
        descr TEXT NOT NULL,


@@ -151,6 +186,8 @@ CREATE TABLE SupIssue(
        priv BOOLEAN NOT NULL,
        status INTEGER NOT NULL,
        stamp TIMESTAMP 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);
 
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
        FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
 


@@ -174,14 +211,30 @@ CREATE TABLE SupSubscription(
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
        FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
 
        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,
 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,
        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;
 
 
 CREATE SEQUENCE AptSeq START 1;
 


@@ -192,6 +245,7 @@ CREATE TABLE Domain(
        msg TEXT NOT NULL,
        status INTEGER NOT NULL,
        stamp TIMESTAMP 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;
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 
 CREATE SEQUENCE DomainSeq START 1;


@@ -203,6 +257,7 @@ CREATE TABLE MailingList(
        msg TEXT NOT NULL,
        status INTEGER NOT NULL,
        stamp TIMESTAMP 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;
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 
 CREATE SEQUENCE MailingListSeq START 1;


@@ -211,22 +266,6 @@ CREATE TABLE DirectoryPref(
        usr INTEGER PRIMARY KEY,
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 
        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);
-
-CREATE SEQUENCE MemberAppSeq START 1;
-

 CREATE TABLE AppVote(
        app INTEGER NOT NULL,
        usr INTEGER NOT NULL,
 CREATE TABLE AppVote(
        app INTEGER NOT NULL,
        usr INTEGER NOT NULL,


@@ -234,3 +273,76 @@ CREATE TABLE AppVote(
        FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE,
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 
        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,
+       FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE CertSeq START 1;