Change granter.sh to give permissions to user specified on command line

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

diff --git a/tables.sql b/tables.sql
index 555d81b..fc7a8cc 100644 (file)
--- a/tables.sql
+++ b/tables.sql
@@ -35,6 +35,7 @@ CREATE TABLE WebUser(
        bal INTEGER NOT NULL,
        joined TIMESTAMP NOT NULL,
        app INTEGER NOT NULL,
+       shares INTEGER NOT NULL,
        FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE,
        FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE);
 
@@ -247,8 +248,16 @@ 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
+               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
                FROM WebUser
                        LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup
                                ON grp = WebGroup.id AND (WebGroup.name IN ('retired', 'phantom'))) AS mem
@@ -271,3 +280,14 @@ CREATE TABLE RollCallEntry(
        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,
+       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);
+
+CREATE SEQUENCE SecSeq START 1;