Passgen (admin part)

[bpt/portal.git] / tables.sql

diff --git a/tables.sql b/tables.sql
index 97a890c..17d755b 100644 (file)
--- a/tables.sql
+++ b/tables.sql
@@ -13,7 +13,10 @@ CREATE TABLE MemberApp(
        ipaddr TEXT,
        confirmed TIMESTAMP,
        decided TIMESTAMP,
        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;
 
 
 CREATE SEQUENCE MemberAppSeq START 1;
 


@@ -35,6 +38,9 @@ CREATE TABLE WebUser(
        bal INTEGER NOT NULL,
        joined TIMESTAMP NOT NULL,
        app INTEGER 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);
 
        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,
        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;
        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,
        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);
 


@@ -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);
 
        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;
 


@@ -221,6 +246,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;


@@ -232,6 +258,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;


@@ -247,8 +274,16 @@ 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
 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 IN ('retired', 'phantom'))) AS mem
                FROM WebUser
                        LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup
                                ON grp = WebGroup.id AND (WebGroup.name IN ('retired', 'phantom'))) AS mem


@@ -275,10 +310,63 @@ CREATE TABLE RollCallEntry(
 CREATE TABLE Sec(
        id INTEGER PRIMARY KEY,
        usr INTEGER NOT NULL,
 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,
        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 SecSeq START 1;
 
 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;