Voting on/approving/denying membership applications

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

diff --git a/tables.sql b/tables.sql
index 3105801..d3554bc 100644 (file)
--- a/tables.sql
+++ b/tables.sql
@@ -27,7 +27,7 @@ CREATE TABLE WebGroup(
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL);
 
-CREATE SEQUENCE WebGroupSeq START 1;
+CREATE SEQUENCE WebGroupSeq START 4;
 
 INSERT INTO WebGroup
        (id, name) VALUES
@@ -61,4 +61,177 @@ CREATE TABLE Charge(
        FOREIGN KEY (trn) REFERENCES Transaction(id) ON DELETE CASCADE,
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 
+CREATE TABLE Poll(
+       id INTEGER PRIMARY KEY,
+       usr INTEGER NOT NULL,
+       title TEXT NOT NULL,
+       descr TEXT NOT NULL,
+       starts DATE NOT NULL,
+       ends DATE NOT NULL,
+       votes INTEGER NOT NULL,
+       FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE PollSeq START 1;
+
+CREATE TABLE PollChoice(
+       id INTEGER PRIMARY KEY,
+       pol INTEGER NOT NULL,
+       seq REAL NOT NULL,
+       descr TEXT NOT NULL,
+       FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE PollChoiceSeq START 1;
+
+CREATE TABLE Vote(
+       usr INTEGER NOT NULL,
+       cho INTEGER NOT NULL,
+       PRIMARY KEY (usr, cho),
+       FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
+       FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE);
+
+CREATE TABLE ContactKind(
+       id INTEGER PRIMARY KEY,
+       name TEXT NOT NULL,
+       url BOOLEAN NOT NULL,
+       urlPrefix TEXT,
+       urlPostfix TEXT);
+
+CREATE SEQUENCE ContactKindSeq START 1;
+
+CREATE TABLE Contact(
+       id INTEGER PRIMARY KEY,
+       usr INTEGER NOT NULL,
+       knd INTEGER NOT NULL,
+       v TEXT NOT NULL,
+       priv INTEGER NOT NULL,
+       FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
+       FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE ContactSeq START 1;
+
+CREATE TABLE Location(
+       id INTEGER PRIMARY KEY,
+       parent INTEGER,
+       name TEXT NOT NULL,
+       FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE);
+
+CREATE SEQUENCE LocationSeq START 1;
+
+CREATE TABLE Lives(
+       usr INTEGER NOT NULL,
+       loc INTEGER NOT NULL,
+       PRIMARY KEY (usr, loc),
+       FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
+       FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE);
+
+CREATE TABLE Link(
+       id INTEGER PRIMARY KEY,
+       usr INTEGER NOT NULL,
+       title TEXT NOT NULL,
+       url TEXT NOT NULL,
+       descr TEXT NOT NULL,
+       FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
+
+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,
+       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 Apt(
+       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 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,
+       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,
+       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 MemberApp(
+       id INTEGER PRIMARY KEY,
+       name TEXT NOT NULL,
+       rname TEXT NOT NULL,
+       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,
+       confirmed TIMESTAMP,
+       decided TIMESTAMP,
+       msg TEXT NOT NULL);
+
+CREATE SEQUENCE MemberAppSeq START 1;
+
+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);