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