cvsimport

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

diff --git a/tables.sql b/tables.sql
index 682f44d..8ff5251 100644 (file)
--- 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;
 
@@ -36,6 +39,8 @@ CREATE TABLE WebUser(
        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);
 
@@ -227,6 +232,7 @@ CREATE TABLE Apt(
        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);
 
@@ -239,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;
@@ -250,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;
@@ -266,7 +274,7 @@ CREATE TABLE AppVote(
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 
 CREATE VIEW WebUserPaying
-       AS SELECT id, name, rname, bal, joined, app, shares
+       AS SELECT id, name, rname, bal, joined, app, shares, paypal, checkout
                FROM WebUser
                        JOIN (SELECT usr FROM Membership JOIN WebGroup
                                ON grp = WebGroup.id
@@ -274,7 +282,7 @@ CREATE VIEW WebUserPaying
                                ON usr = WebUser.id;
 
 CREATE VIEW WebUserActive
-       AS SELECT id, name, rname, bal, joined, app, shares
+       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
@@ -306,7 +314,46 @@ CREATE TABLE Sec(
        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;