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