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         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 109
 110 CREATE SEQUENCE PollSeq START 1;
 111
 112 CREATE TABLE PollChoice(
 113         id INTEGER PRIMARY KEY,
 114         pol INTEGER NOT NULL,
 115         seq REAL NOT NULL,
 116         descr TEXT NOT NULL,
 117         FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE);
 118
 119 CREATE SEQUENCE PollChoiceSeq START 1;
 120
 121 CREATE TABLE Vote(
 122         usr INTEGER NOT NULL,
 123         cho INTEGER NOT NULL,
 124         PRIMARY KEY (usr, cho),
 125         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 126         FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE);
 127
 128 CREATE TABLE ContactKind(
 129         id INTEGER PRIMARY KEY,
 130         name TEXT NOT NULL,
 131         url BOOLEAN NOT NULL,
 132         urlPrefix TEXT,
 133         urlPostfix TEXT);
 134
 135 CREATE SEQUENCE ContactKindSeq START 1;
 136
 137 CREATE TABLE Contact(
 138         id INTEGER PRIMARY KEY,
 139         usr INTEGER NOT NULL,
 140         knd INTEGER NOT NULL,
 141         v TEXT NOT NULL,
 142         priv INTEGER NOT NULL,
 143         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 144         FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE);
 145
 146 CREATE SEQUENCE ContactSeq START 1;
 147
 148 CREATE TABLE Location(
 149         id INTEGER PRIMARY KEY,
 150         parent INTEGER,
 151         name TEXT NOT NULL,
 152         FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE);
 153
 154 CREATE SEQUENCE LocationSeq START 1;
 155
 156 CREATE TABLE Lives(
 157         usr INTEGER NOT NULL,
 158         loc INTEGER NOT NULL,
 159         PRIMARY KEY (usr, loc),
 160         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 161         FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE);
 162
 163 CREATE TABLE Link(
 164         id INTEGER PRIMARY KEY,
 165         usr INTEGER NOT NULL,
 166         title TEXT NOT NULL,
 167         url TEXT NOT NULL,
 168         descr TEXT NOT NULL,
 169         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 170
 171 CREATE SEQUENCE LinkSeq START 1;
 172
 173 CREATE TABLE SupCategory(
 174         id INTEGER PRIMARY KEY,
 175         grp INTEGER NOT NULL,
 176         name TEXT NOT NULL,
 177         descr TEXT NOT NULL,
 178         FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE);
 179
 180 CREATE SEQUENCE SupCategorySeq START 1;
 181
 182 CREATE TABLE SupIssue(
 183         id INTEGER PRIMARY KEY,
 184         usr INTEGER NOT NULL,
 185         cat INTEGER NOT NULL,
 186         title TEXT NOT NULL,
 187         priv BOOLEAN NOT NULL,
 188         status INTEGER NOT NULL,
 189         stamp TIMESTAMP NOT NULL,
 190         pstamp TIMESTAMP,
 191         cstamp TIMESTAMP,
 192         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 193         FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
 194
 195 CREATE SEQUENCE SupIssueSeq START 1;
 196
 197 CREATE TABLE SupPost(
 198         id INTEGER PRIMARY KEY,
 199         usr INTEGER NOT NULL,
 200         iss INTEGER NOT NULL,
 201         body TEXT NOT NULL,
 202         stamp TIMESTAMP NOT NULL,
 203         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 204         FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE);
 205
 206 CREATE SEQUENCE SupPostSeq START 1;
 207
 208 CREATE TABLE SupSubscription(
 209         usr INTEGER NOT NULL,
 210         cat INTEGER NOT NULL,
 211         PRIMARY KEY (usr, cat),
 212         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 213         FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
 214
 215 CREATE TABLE WebNode(
 216         id INTEGER PRIMARY KEY,
 217         name TEXT NOT NULL,
 218         descr TEXT NOT NULL,
 219         debian TEXT NOT NULL);
 220
 221 INSERT INTO WebNode (id, name, descr, debian)
 222         VALUES (0, 'fyodor', 'old server', 'testing');
 223 INSERT INTO WebNode (id, name, descr, debian)
 224         VALUES (1, 'deleuze', 'main server', 'stable');
 225 INSERT INTO WebNode (id, name, descr, debian)
 226         VALUES (2, 'mire', 'member web server', 'stable');
 227
 228 CREATE TABLE Apt(
 229         id INTEGER PRIMARY KEY,
 230         usr INTEGER NOT NULL,
 231         node INTEGER NOT NULL,
 232         data TEXT NOT NULL,
 233         msg TEXT NOT NULL,
 234         status INTEGER NOT NULL,
 235         stamp TIMESTAMP NOT NULL,
 236         cstamp TIMESTAMP,
 237         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 238         FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
 239
 240 CREATE SEQUENCE AptSeq START 1;
 241
 242 CREATE TABLE Domain(
 243         id INTEGER PRIMARY KEY,
 244         usr INTEGER NOT NULL,
 245         data TEXT NOT NULL,
 246         msg TEXT NOT NULL,
 247         status INTEGER NOT NULL,
 248         stamp TIMESTAMP NOT NULL,
 249         cstamp TIMESTAMP,
 250         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 251
 252 CREATE SEQUENCE DomainSeq START 1;
 253
 254 CREATE TABLE MailingList(
 255         id INTEGER PRIMARY KEY,
 256         usr INTEGER NOT NULL,
 257         data TEXT NOT NULL,
 258         msg TEXT NOT NULL,
 259         status INTEGER NOT NULL,
 260         stamp TIMESTAMP NOT NULL,
 261         cstamp TIMESTAMP,
 262         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 263
 264 CREATE SEQUENCE MailingListSeq START 1;
 265
 266 CREATE TABLE DirectoryPref(
 267         usr INTEGER PRIMARY KEY,
 268         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 269
 270 CREATE TABLE AppVote(
 271         app INTEGER NOT NULL,
 272         usr INTEGER NOT NULL,
 273         PRIMARY KEY (app, usr),
 274         FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE,
 275         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 276
 277 CREATE VIEW WebUserPaying
 278         AS SELECT id, name, rname, bal, joined, app, shares, paypal, checkout
 279                 FROM WebUser
 280                         JOIN (SELECT usr FROM Membership JOIN WebGroup
 281                                 ON grp = WebGroup.id
 282                                         AND WebGroup.name = 'paying') AS bob
 283                                 ON usr = WebUser.id;
 284
 285 CREATE VIEW WebUserActive
 286         AS SELECT id, name, rname, bal, joined, app, shares, paypal, checkout
 287                 FROM WebUser
 288                         LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup
 289                                 ON grp = WebGroup.id AND (WebGroup.name IN ('retired', 'phantom'))) AS mem
 290                                 ON usr = WebUser.id
 291                 WHERE usr IS NULL;
 292
 293 CREATE TABLE RollCall(
 294         id INTEGER PRIMARY KEY,
 295         title TEXT NOT NULL,
 296         msg TEXT NOT NULL,
 297         started TIMESTAMP NOT NULL);
 298
 299 CREATE SEQUENCE RollCallSeq START 1;
 300
 301 CREATE TABLE RollCallEntry(
 302         rol INTEGER NOT NULL,
 303         usr INTEGER NOT NULL,
 304         code TEXT NOT NULL,
 305         responded TIMESTAMP,
 306         PRIMARY KEY (rol, usr),
 307         FOREIGN KEY (rol) REFERENCES RollCall(id) ON DELETE CASCADE,
 308         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 309
 310 CREATE TABLE Sec(
 311         id INTEGER PRIMARY KEY,
 312         usr INTEGER NOT NULL,
 313         node INTEGER NOT NULL,
 314         data TEXT NOT NULL,
 315         msg TEXT NOT NULL,
 316         status INTEGER NOT NULL,
 317         stamp TIMESTAMP NOT NULL,
 318         cstamp TIMESTAMP,
 319         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 320         FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
 321
 322 CREATE SEQUENCE SecSeq START 1;
 323
 324 CREATE TABLE Ip(
 325         id INTEGER PRIMARY KEY,
 326         usr INTEGER NOT NULL,
 327         node INTEGER NOT NULL,
 328         data TEXT NOT NULL,
 329         msg TEXT NOT NULL,
 330         status INTEGER NOT NULL,
 331         stamp TIMESTAMP NOT NULL,
 332         cstamp TIMESTAMP,
 333         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 334         FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
 335
 336 CREATE SEQUENCE IpSeq START 1;
 337
 338 CREATE TABLE Cert(
 339         id INTEGER PRIMARY KEY,
 340         usr INTEGER NOT NULL,
 341         data TEXT NOT NULL,
 342         msg TEXT NOT NULL,
 343         status INTEGER NOT NULL,
 344         stamp TIMESTAMP NOT NULL,
 345         cstamp TIMESTAMP,
 346         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 347
 348 CREATE SEQUENCE CertSeq START 1;
 349
 350 CREATE TABLE Quota(
 351         id INTEGER PRIMARY KEY,
 352         usr INTEGER NOT NULL,
 353         data TEXT NOT NULL,
 354         msg TEXT NOT NULL,
 355         status INTEGER NOT NULL,
 356         stamp TIMESTAMP NOT NULL,
 357         cstamp TIMESTAMP,
 358         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 359
 360 CREATE SEQUENCE QuotaSeq START 1;