tables.sql

   1 CREATE TABLE Balance(
   2         id INTEGER PRIMARY KEY,
   3         name TEXT NOT NULL,
   4         amount REAL NOT NULL);
   5
   6 CREATE SEQUENCE BalanceSeq START 1;
   7
   8 INSERT INTO Balance
   9         (id, name, amount) VALUES
  10         (0, 'adam', 0.0);
  11
  12 CREATE TABLE WebUser(
  13         id INTEGER PRIMARY KEY,
  14         name TEXT NOT NULL,
  15         rname TEXT NOT NULL,
  16         bal INTEGER NOT NULL,
  17         joined TIMESTAMP NOT NULL,
  18         FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE);
  19
  20 CREATE SEQUENCE WebUserSeq START 1;
  21
  22 INSERT INTO WebUser
  23         (id, name, rname, bal, joined) VALUES
  24         (0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);
  25
  26 CREATE TABLE WebGroup(
  27         id INTEGER PRIMARY KEY,
  28         name TEXT NOT NULL);
  29
  30 CREATE SEQUENCE WebGroupSeq START 2;
  31
  32 INSERT INTO WebGroup
  33         (id, name) VALUES
  34         (0, 'root');
  35
  36 INSERT INTO WebGroup
  37         (id, name) VALUES
  38         (1, 'paying');
  39
  40 CREATE TABLE Membership(
  41         grp INTEGER NOT NULL,
  42         usr INTEGER NOT NULL,
  43         PRIMARY KEY (grp, usr),
  44         FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE,
  45         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
  46
  47 INSERT INTO Membership
  48         (grp, usr) VALUES
  49         (0, 0);
  50
  51 INSERT INTO Membership
  52         (grp, usr) VALUES
  53         (1, 0);
  54
  55 CREATE TABLE Transaction(
  56         id INTEGER PRIMARY KEY,
  57         descr TEXT NOT NULL,
  58         amount REAL NOT NULL,
  59         d DATE NOT NULL,
  60         stamp TIMESTAMP NOT NULL);
  61
  62 CREATE SEQUENCE TransactionSeq START 1;
  63
  64 CREATE TABLE Charge(
  65         trn INTEGER NOT NULL,
  66         usr INTEGER NOT NULL,
  67         amount REAL NOT NULL,
  68         PRIMARY KEY (trn, usr),
  69         FOREIGN KEY (trn) REFERENCES Transaction(id) ON DELETE CASCADE,
  70         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
  71
  72 CREATE TABLE Poll(
  73         id INTEGER PRIMARY KEY,
  74         usr INTEGER NOT NULL,
  75         title TEXT NOT NULL,
  76         descr TEXT NOT NULL,
  77         starts DATE NOT NULL,
  78         ends DATE NOT NULL,
  79         votes INTEGER NOT NULL,
  80         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
  81
  82 CREATE SEQUENCE PollSeq START 1;
  83
  84 CREATE TABLE PollChoice(
  85         id INTEGER PRIMARY KEY,
  86         pol INTEGER NOT NULL,
  87         seq REAL NOT NULL,
  88         descr TEXT NOT NULL,
  89         FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE);
  90
  91 CREATE SEQUENCE PollChoiceSeq START 1;
  92
  93 CREATE TABLE Vote(
  94         usr INTEGER NOT NULL,
  95         cho INTEGER NOT NULL,
  96         PRIMARY KEY (usr, cho),
  97         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
  98         FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE);
  99
 100 CREATE TABLE ContactKind(
 101         id INTEGER PRIMARY KEY,
 102         name TEXT NOT NULL,
 103         url BOOLEAN NOT NULL,
 104         urlPrefix TEXT,
 105         urlPostfix TEXT);
 106
 107 CREATE SEQUENCE ContactKindSeq START 1;
 108
 109 CREATE TABLE Contact(
 110         id INTEGER PRIMARY KEY,
 111         usr INTEGER NOT NULL,
 112         knd INTEGER NOT NULL,
 113         v TEXT NOT NULL,
 114         priv INTEGER NOT NULL,
 115         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 116         FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE);
 117
 118 CREATE SEQUENCE ContactSeq START 1;
 119
 120 CREATE TABLE Location(
 121         id INTEGER PRIMARY KEY,
 122         parent INTEGER,
 123         name TEXT NOT NULL,
 124         FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE);
 125
 126 CREATE SEQUENCE LocationSeq START 1;
 127
 128 CREATE TABLE Lives(
 129         usr INTEGER NOT NULL,
 130         loc INTEGER NOT NULL,
 131         PRIMARY KEY (usr, loc),
 132         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 133         FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE);
 134
 135 CREATE TABLE Link(
 136         id INTEGER PRIMARY KEY,
 137         usr INTEGER NOT NULL,
 138         title TEXT NOT NULL,
 139         url TEXT NOT NULL,
 140         descr TEXT NOT NULL,
 141         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 142
 143 CREATE SEQUENCE LinkSeq START 1;
 144
 145 CREATE TABLE SupCategory(
 146         id INTEGER PRIMARY KEY,
 147         grp INTEGER NOT NULL,
 148         name TEXT NOT NULL,
 149         descr TEXT NOT NULL,
 150         FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE);
 151
 152 CREATE SEQUENCE SupCategorySeq START 1;
 153
 154 CREATE TABLE SupIssue(
 155         id INTEGER PRIMARY KEY,
 156         usr INTEGER NOT NULL,
 157         cat INTEGER NOT NULL,
 158         title TEXT NOT NULL,
 159         priv BOOLEAN NOT NULL,
 160         status INTEGER NOT NULL,
 161         stamp TIMESTAMP NOT NULL,
 162         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 163         FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
 164
 165 CREATE SEQUENCE SupIssueSeq START 1;
 166
 167 CREATE TABLE SupPost(
 168         id INTEGER PRIMARY KEY,
 169         usr INTEGER NOT NULL,
 170         iss INTEGER NOT NULL,
 171         body TEXT NOT NULL,
 172         stamp TIMESTAMP NOT NULL,
 173         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 174         FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE);
 175
 176 CREATE SEQUENCE SupPostSeq START 1;
 177
 178 CREATE TABLE SupSubscription(
 179         usr INTEGER NOT NULL,
 180         cat INTEGER NOT NULL,
 181         PRIMARY KEY (usr, cat),
 182         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 183         FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE);
 184
 185 CREATE TABLE Apt(
 186         id INTEGER PRIMARY KEY,
 187         usr INTEGER NOT NULL,
 188         data TEXT NOT NULL,
 189         msg TEXT NOT NULL,
 190         status INTEGER NOT NULL,
 191         stamp TIMESTAMP NOT NULL,
 192         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 193
 194 CREATE SEQUENCE AptSeq START 1;
 195
 196 CREATE TABLE Domain(
 197         id INTEGER PRIMARY KEY,
 198         usr INTEGER NOT NULL,
 199         data TEXT NOT NULL,
 200         msg TEXT NOT NULL,
 201         status INTEGER NOT NULL,
 202         stamp TIMESTAMP NOT NULL,
 203         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 204
 205 CREATE SEQUENCE DomainSeq START 1;
 206
 207 CREATE TABLE MailingList(
 208         id INTEGER PRIMARY KEY,
 209         usr INTEGER NOT NULL,
 210         data TEXT NOT NULL,
 211         msg TEXT NOT NULL,
 212         status INTEGER NOT NULL,
 213         stamp TIMESTAMP NOT NULL,
 214         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 215
 216 CREATE SEQUENCE MailingListSeq START 1;
 217
 218 CREATE TABLE DirectoryPref(
 219         usr INTEGER PRIMARY KEY,
 220         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 221
 222 CREATE TABLE MemberApp(
 223         id INTEGER PRIMARY KEY,
 224         name TEXT NOT NULL,
 225         rname TEXT NOT NULL,
 226         email TEXT NOT NULL,
 227         forward BOOLEAN NOT NULL,
 228         uses TEXT NOT NULL,
 229         other TEXT NOT NULL,
 230         passwd TEXT NOT NULL,
 231         status INTEGER NOT NULL,
 232         applied TIMESTAMP NOT NULL,
 233         confirmed TIMESTAMP,
 234         decided TIMESTAMP,
 235         msg TEXT NOT NULL);
 236
 237 CREATE SEQUENCE MemberAppSeq START 1;
 238
 239 CREATE TABLE AppVote(
 240         app INTEGER NOT NULL,
 241         usr INTEGER NOT NULL,
 242         PRIMARY KEY (app, usr),
 243         FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE,
 244         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 245