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