tables.sql

   1 CREATE TABLE MemberApp(
   2         id INTEGER PRIMARY KEY,
   3         name TEXT NOT NULL,
   4         rname TEXT NOT NULL,
   5         gname TEXT,
   6         email TEXT NOT NULL,
   7         forward BOOLEAN NOT NULL,
   8         uses TEXT NOT NULL,
   9         other TEXT NOT NULL,
  10         passwd TEXT NOT NULL,
  11         status INTEGER NOT NULL,
  12         applied TIMESTAMP NOT NULL,
  13         ipaddr TEXT,
  14         confirmed TIMESTAMP,
  15         decided TIMESTAMP,
  16         msg TEXT NOT NULL);
  17
  18 CREATE SEQUENCE MemberAppSeq START 1;
  19
  20 CREATE TABLE Balance(
  21         id INTEGER PRIMARY KEY,
  22         name TEXT NOT NULL,
  23         amount REAL NOT NULL);
  24
  25 CREATE SEQUENCE BalanceSeq START 1;
  26
  27 INSERT INTO Balance
  28         (id, name, amount) VALUES
  29         (0, 'adam', 0.0);
  30
  31 CREATE TABLE WebUser(
  32         id INTEGER PRIMARY KEY,
  33         name TEXT NOT NULL,
  34         rname TEXT NOT NULL,
  35         bal INTEGER NOT NULL,
  36         joined TIMESTAMP NOT NULL,
  37         app INTEGER NOT NULL,
  38         FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE,
  39         FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE);
  40
  41 CREATE SEQUENCE WebUserSeq START 1;
  42
  43 INSERT INTO WebUser
  44         (id, name, rname, bal, joined) VALUES
  45         (0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);
  46
  47 CREATE TABLE WebGroup(
  48         id INTEGER PRIMARY KEY,
  49         name TEXT NOT NULL);
  50
  51 CREATE SEQUENCE WebGroupSeq START 2;
  52
  53 INSERT INTO WebGroup
  54         (id, name) VALUES
  55         (0, 'root');
  56
  57 INSERT INTO WebGroup
  58         (id, name) VALUES
  59         (1, 'paying');
  60
  61 CREATE TABLE Membership(
  62         grp INTEGER NOT NULL,
  63         usr INTEGER NOT NULL,
  64         PRIMARY KEY (grp, usr),
  65         FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE,
  66         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
  67
  68 INSERT INTO Membership
  69         (grp, usr) VALUES
  70         (0, 0);
  71
  72 INSERT INTO Membership
  73         (grp, usr) VALUES
  74         (1, 0);
  75
  76 CREATE TABLE Transaction(
  77         id INTEGER PRIMARY KEY,
  78         descr TEXT NOT NULL,
  79         amount REAL NOT NULL,
  80         d DATE NOT NULL,
  81         stamp TIMESTAMP NOT NULL);
  82
  83 CREATE SEQUENCE TransactionSeq START 1;
  84
  85 CREATE TABLE Charge(
  86         trn INTEGER NOT NULL,
  87         usr INTEGER NOT NULL,
  88         amount REAL NOT NULL,
  89         PRIMARY KEY (trn, usr),
  90         FOREIGN KEY (trn) REFERENCES Transaction(id) ON DELETE CASCADE,
  91         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
  92
  93 CREATE TABLE Poll(
  94         id INTEGER PRIMARY KEY,
  95         usr INTEGER NOT NULL,
  96         title TEXT NOT NULL,
  97         descr TEXT NOT NULL,
  98         starts DATE NOT NULL,
  99         ends DATE NOT NULL,
 100         votes INTEGER NOT NULL,
 101         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 102
 103 CREATE SEQUENCE PollSeq START 1;
 104
 105 CREATE TABLE PollChoice(
 106         id INTEGER PRIMARY KEY,
 107         pol INTEGER NOT NULL,
 108         seq REAL NOT NULL,
 109         descr TEXT NOT NULL,
 110         FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE);
 111
 112 CREATE SEQUENCE PollChoiceSeq START 1;
 113
 114 CREATE TABLE Vote(
 115         usr INTEGER NOT NULL,
 116         cho INTEGER NOT NULL,
 117         PRIMARY KEY (usr, cho),
 118         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 119         FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE);
 120
 121 CREATE TABLE ContactKind(
 122         id INTEGER PRIMARY KEY,
 123         name TEXT NOT NULL,
 124         url BOOLEAN NOT NULL,
 125         urlPrefix TEXT,
 126         urlPostfix TEXT);
 127
 128 CREATE SEQUENCE ContactKindSeq START 1;
 129
 130 CREATE TABLE Contact(
 131         id INTEGER PRIMARY KEY,
 132         usr INTEGER NOT NULL,
 133         knd INTEGER NOT NULL,
 134         v TEXT NOT NULL,
 135         priv INTEGER NOT NULL,
 136         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 137         FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE);
 138
 139 CREATE SEQUENCE ContactSeq START 1;
 140
 141 CREATE TABLE Location(
 142         id INTEGER PRIMARY KEY,
 143         parent INTEGER,
 144         name TEXT NOT NULL,
 145         FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE);
 146
 147 CREATE SEQUENCE LocationSeq START 1;
 148
 149 CREATE TABLE Lives(
 150         usr INTEGER NOT NULL,
 151         loc INTEGER NOT NULL,
 152         PRIMARY KEY (usr, loc),
 153         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 154         FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE);
 155
 156 CREATE TABLE Link(
 157         id INTEGER PRIMARY KEY,
 158         usr INTEGER NOT NULL,
 159         title TEXT NOT NULL,
 160         url TEXT NOT NULL,
 161         descr TEXT NOT NULL,
 162         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 163
 164 CREATE SEQUENCE LinkSeq START 1;
 165
 166 CREATE TABLE SupCategory(
 167         id INTEGER PRIMARY KEY,
 168         grp INTEGER NOT NULL,
 169         name TEXT NOT NULL,
 170         descr TEXT NOT NULL,
 171         FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE);
 172
 173 CREATE SEQUENCE SupCategorySeq START 1;
 174
 175 CREATE TABLE SupIssue(
 176         id INTEGER PRIMARY KEY,
 177         usr INTEGER NOT NULL,
 178         cat INTEGER NOT NULL,
 179         title TEXT NOT NULL,
 180         priv BOOLEAN NOT NULL,
 181         status INTEGER NOT NULL,
 182         stamp TIMESTAMP NOT NULL,
 183         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 184         FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
 185
 186 CREATE SEQUENCE SupIssueSeq START 1;
 187
 188 CREATE TABLE SupPost(
 189         id INTEGER PRIMARY KEY,
 190         usr INTEGER NOT NULL,
 191         iss INTEGER NOT NULL,
 192         body TEXT NOT NULL,
 193         stamp TIMESTAMP NOT NULL,
 194         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 195         FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE);
 196
 197 CREATE SEQUENCE SupPostSeq START 1;
 198
 199 CREATE TABLE SupSubscription(
 200         usr INTEGER NOT NULL,
 201         cat INTEGER NOT NULL,
 202         PRIMARY KEY (usr, cat),
 203         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 204         FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
 205
 206 CREATE TABLE Apt(
 207         id INTEGER PRIMARY KEY,
 208         usr INTEGER NOT NULL,
 209         data TEXT NOT NULL,
 210         msg TEXT NOT NULL,
 211         status INTEGER NOT NULL,
 212         stamp TIMESTAMP NOT NULL,
 213         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 214
 215 CREATE SEQUENCE AptSeq START 1;
 216
 217 CREATE TABLE Domain(
 218         id INTEGER PRIMARY KEY,
 219         usr INTEGER NOT NULL,
 220         data TEXT NOT NULL,
 221         msg TEXT NOT NULL,
 222         status INTEGER NOT NULL,
 223         stamp TIMESTAMP NOT NULL,
 224         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 225
 226 CREATE SEQUENCE DomainSeq START 1;
 227
 228 CREATE TABLE MailingList(
 229         id INTEGER PRIMARY KEY,
 230         usr INTEGER NOT NULL,
 231         data TEXT NOT NULL,
 232         msg TEXT NOT NULL,
 233         status INTEGER NOT NULL,
 234         stamp TIMESTAMP NOT NULL,
 235         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 236
 237 CREATE SEQUENCE MailingListSeq START 1;
 238
 239 CREATE TABLE DirectoryPref(
 240         usr INTEGER PRIMARY KEY,
 241         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 242
 243 CREATE TABLE AppVote(
 244         app INTEGER NOT NULL,
 245         usr INTEGER NOT NULL,
 246         PRIMARY KEY (app, usr),
 247         FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE,
 248         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 249
 250 CREATE VIEW WebUserActive
 251         AS SELECT id, name, rname, bal, joined, app
 252                 FROM WebUser
 253                         LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup
 254                                 ON grp = WebGroup.id AND WebGroup.name = 'retired') AS mem
 255                                 ON usr = WebUser.id
 256
 257                 WHERE usr IS NULL;
 258