+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,
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
- (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 SEQUENCE WebGroupSeq START 4;
+CREATE SEQUENCE WebGroupSeq START 2;
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,
(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 SEQUENCE LinkSeq START 1;
+CREATE TABLE SupCategory(
+ id INTEGER PRIMARY KEY,
+ grp INTEGER NOT NULL,
+ name TEXT NOT NULL,
+ descr TEXT NOT NULL,
+ FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE SupCategorySeq START 1;
+
+CREATE TABLE SupIssue(
+ id INTEGER PRIMARY KEY,
+ usr INTEGER NOT NULL,
+ cat INTEGER NOT NULL,
+ title TEXT 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);
+
+CREATE SEQUENCE SupIssueSeq START 1;
+
+CREATE TABLE SupPost(
+ id INTEGER PRIMARY KEY,
+ usr INTEGER NOT NULL,
+ iss INTEGER NOT NULL,
+ body TEXT NOT NULL,
+ stamp TIMESTAMP NOT NULL,
+ FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
+ FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE SupPostSeq START 1;
+
+CREATE TABLE SupSubscription(
+ usr INTEGER NOT NULL,
+ cat INTEGER NOT NULL,
+ PRIMARY KEY (usr, cat),
+ 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,
+ 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 AptSeq START 1;
+
+CREATE TABLE Domain(
+ 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 DomainSeq START 1;
+
+CREATE TABLE MailingList(
+ 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 MailingListSeq START 1;
+
+CREATE TABLE DirectoryPref(
+ usr INTEGER PRIMARY KEY,
+ FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
+
+CREATE TABLE AppVote(
+ app INTEGER NOT NULL,
+ usr INTEGER NOT NULL,
+ PRIMARY KEY (app, usr),
+ 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);
+
+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;
HCoop / hcoop / zz_old / portal.git / blobdiff