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         shares INTEGER NOT NULL,
  39         FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE,
  40         FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE);
  41
  42 CREATE SEQUENCE WebUserSeq START 1;
  43
  44 INSERT INTO WebUser
  45         (id, name, rname, bal, joined) VALUES
  46         (0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);
  47
  48 CREATE TABLE WebGroup(
  49         id INTEGER PRIMARY KEY,
  50         name TEXT NOT NULL);
  51
  52 CREATE SEQUENCE WebGroupSeq START 2;
  53
  54 INSERT INTO WebGroup
  55         (id, name) VALUES
  56         (0, 'root');
  57
  58 INSERT INTO WebGroup
  59         (id, name) VALUES
  60         (1, 'paying');
  61
  62 CREATE TABLE Membership(
  63         grp INTEGER NOT NULL,
  64         usr INTEGER NOT NULL,
  65         PRIMARY KEY (grp, usr),
  66         FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE,
  67         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
  68
  69 INSERT INTO Membership
  70         (grp, usr) VALUES
  71         (0, 0);
  72
  73 INSERT INTO Membership
  74         (grp, usr) VALUES
  75         (1, 0);
  76
  77 CREATE TABLE Transaction(
  78         id INTEGER PRIMARY KEY,
  79         descr TEXT NOT NULL,
  80         amount REAL NOT NULL,
  81         d DATE NOT NULL,
  82         stamp TIMESTAMP NOT NULL);
  83
  84 CREATE SEQUENCE TransactionSeq START 1;
  85
  86 CREATE TABLE Charge(
  87         trn INTEGER NOT NULL,
  88         usr INTEGER NOT NULL,
  89         amount REAL NOT NULL,
  90         PRIMARY KEY (trn, usr),
  91         FOREIGN KEY (trn) REFERENCES Transaction(id) ON DELETE CASCADE,
  92         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
  93
  94 CREATE TABLE Poll(
  95         id INTEGER PRIMARY KEY,
  96         usr INTEGER NOT NULL,
  97         title TEXT NOT NULL,
  98         descr TEXT NOT NULL,
  99         starts DATE NOT NULL,
 100         ends DATE NOT NULL,
 101         votes INTEGER NOT NULL,
 102         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 103
 104 CREATE SEQUENCE PollSeq START 1;
 105
 106 CREATE TABLE PollChoice(
 107         id INTEGER PRIMARY KEY,
 108         pol INTEGER NOT NULL,
 109         seq REAL NOT NULL,
 110         descr TEXT NOT NULL,
 111         FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE);
 112
 113 CREATE SEQUENCE PollChoiceSeq START 1;
 114
 115 CREATE TABLE Vote(
 116         usr INTEGER NOT NULL,
 117         cho INTEGER NOT NULL,
 118         PRIMARY KEY (usr, cho),
 119         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 120         FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE);
 121
 122 CREATE TABLE ContactKind(
 123         id INTEGER PRIMARY KEY,
 124         name TEXT NOT NULL,
 125         url BOOLEAN NOT NULL,
 126         urlPrefix TEXT,
 127         urlPostfix TEXT);
 128
 129 CREATE SEQUENCE ContactKindSeq START 1;
 130
 131 CREATE TABLE Contact(
 132         id INTEGER PRIMARY KEY,
 133         usr INTEGER NOT NULL,
 134         knd INTEGER NOT NULL,
 135         v TEXT NOT NULL,
 136         priv INTEGER NOT NULL,
 137         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 138         FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE);
 139
 140 CREATE SEQUENCE ContactSeq START 1;
 141
 142 CREATE TABLE Location(
 143         id INTEGER PRIMARY KEY,
 144         parent INTEGER,
 145         name TEXT NOT NULL,
 146         FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE);
 147
 148 CREATE SEQUENCE LocationSeq START 1;
 149
 150 CREATE TABLE Lives(
 151         usr INTEGER NOT NULL,
 152         loc INTEGER NOT NULL,
 153         PRIMARY KEY (usr, loc),
 154         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 155         FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE);
 156
 157 CREATE TABLE Link(
 158         id INTEGER PRIMARY KEY,
 159         usr INTEGER NOT NULL,
 160         title TEXT NOT NULL,
 161         url TEXT NOT NULL,
 162         descr TEXT NOT NULL,
 163         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 164
 165 CREATE SEQUENCE LinkSeq START 1;
 166
 167 CREATE TABLE SupCategory(
 168         id INTEGER PRIMARY KEY,
 169         grp INTEGER NOT NULL,
 170         name TEXT NOT NULL,
 171         descr TEXT NOT NULL,
 172         FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE);
 173
 174 CREATE SEQUENCE SupCategorySeq START 1;
 175
 176 CREATE TABLE SupIssue(
 177         id INTEGER PRIMARY KEY,
 178         usr INTEGER NOT NULL,
 179         cat INTEGER NOT NULL,
 180         title TEXT NOT NULL,
 181         priv BOOLEAN NOT NULL,
 182         status INTEGER NOT NULL,
 183         stamp TIMESTAMP NOT NULL,
 184         pstamp TIMESTAMP,
 185         cstamp TIMESTAMP,
 186         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 187         FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
 188
 189 CREATE SEQUENCE SupIssueSeq START 1;
 190
 191 CREATE TABLE SupPost(
 192         id INTEGER PRIMARY KEY,
 193         usr INTEGER NOT NULL,
 194         iss INTEGER NOT NULL,
 195         body TEXT NOT NULL,
 196         stamp TIMESTAMP NOT NULL,
 197         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 198         FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE);
 199
 200 CREATE SEQUENCE SupPostSeq START 1;
 201
 202 CREATE TABLE SupSubscription(
 203         usr INTEGER NOT NULL,
 204         cat INTEGER NOT NULL,
 205         PRIMARY KEY (usr, cat),
 206         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 207         FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
 208
 209 CREATE TABLE WebNode(
 210         id INTEGER PRIMARY KEY,
 211         name TEXT NOT NULL,
 212         descr TEXT NOT NULL,
 213         debian TEXT NOT NULL);
 214
 215 INSERT INTO WebNode (id, name, descr, debian)
 216         VALUES (0, 'fyodor', 'old server', 'testing');
 217 INSERT INTO WebNode (id, name, descr, debian)
 218         VALUES (1, 'deleuze', 'main server', 'stable');
 219 INSERT INTO WebNode (id, name, descr, debian)
 220         VALUES (2, 'mire', 'member web server', 'stable');
 221
 222 CREATE TABLE Apt(
 223         id INTEGER PRIMARY KEY,
 224         usr INTEGER NOT NULL,
 225         node INTEGER NOT NULL,
 226         data TEXT NOT NULL,
 227         msg TEXT NOT NULL,
 228         status INTEGER NOT NULL,
 229         stamp TIMESTAMP NOT NULL,
 230         cstamp TIMESTAMP,
 231         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 232         FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
 233
 234 CREATE SEQUENCE AptSeq START 1;
 235
 236 CREATE TABLE Domain(
 237         id INTEGER PRIMARY KEY,
 238         usr INTEGER NOT NULL,
 239         data TEXT NOT NULL,
 240         msg TEXT NOT NULL,
 241         status INTEGER NOT NULL,
 242         stamp TIMESTAMP NOT NULL,
 243         cstamp TIMESTAMP,
 244         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 245
 246 CREATE SEQUENCE DomainSeq START 1;
 247
 248 CREATE TABLE MailingList(
 249         id INTEGER PRIMARY KEY,
 250         usr INTEGER NOT NULL,
 251         data TEXT NOT NULL,
 252         msg TEXT NOT NULL,
 253         status INTEGER NOT NULL,
 254         stamp TIMESTAMP NOT NULL,
 255         cstamp TIMESTAMP,
 256         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 257
 258 CREATE SEQUENCE MailingListSeq START 1;
 259
 260 CREATE TABLE DirectoryPref(
 261         usr INTEGER PRIMARY KEY,
 262         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 263
 264 CREATE TABLE AppVote(
 265         app INTEGER NOT NULL,
 266         usr INTEGER NOT NULL,
 267         PRIMARY KEY (app, usr),
 268         FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE,
 269         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 270
 271 CREATE VIEW WebUserPaying
 272         AS SELECT id, name, rname, bal, joined, app, shares
 273                 FROM WebUser
 274                         JOIN (SELECT usr FROM Membership JOIN WebGroup
 275                                 ON grp = WebGroup.id
 276                                         AND WebGroup.name = 'paying') AS bob
 277                                 ON usr = WebUser.id;
 278
 279 CREATE VIEW WebUserActive
 280         AS SELECT id, name, rname, bal, joined, app, shares
 281                 FROM WebUser
 282                         LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup
 283                                 ON grp = WebGroup.id AND (WebGroup.name IN ('retired', 'phantom'))) AS mem
 284                                 ON usr = WebUser.id
 285                 WHERE usr IS NULL;
 286
 287 CREATE TABLE RollCall(
 288         id INTEGER PRIMARY KEY,
 289         title TEXT NOT NULL,
 290         msg TEXT NOT NULL,
 291         started TIMESTAMP NOT NULL);
 292
 293 CREATE SEQUENCE RollCallSeq START 1;
 294
 295 CREATE TABLE RollCallEntry(
 296         rol INTEGER NOT NULL,
 297         usr INTEGER NOT NULL,
 298         code TEXT NOT NULL,
 299         responded TIMESTAMP,
 300         PRIMARY KEY (rol, usr),
 301         FOREIGN KEY (rol) REFERENCES RollCall(id) ON DELETE CASCADE,
 302         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 303
 304 CREATE TABLE Sec(
 305         id INTEGER PRIMARY KEY,
 306         usr INTEGER NOT NULL,
 307         node INTEGER NOT NULL,
 308         data TEXT NOT NULL,
 309         msg TEXT NOT NULL,
 310         status INTEGER NOT NULL,
 311         stamp TIMESTAMP NOT NULL,
 312         cstamp TIMESTAMP,
 313         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 314         FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
 315
 316 CREATE SEQUENCE SecSeq START 1;