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