+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;