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 INSERT INTO WebGroup
  37         (id, name) VALUES
  38         (1, 'money');
  39
  40 INSERT INTO WebGroup
  41         (id, name) VALUES
  42         (2, 'paying');
  43
  44 INSERT INTO WebGroup
  45         (id, name) VALUES
  46         (3, 'poll');
  47
  48 CREATE TABLE Membership(
  49         grp INTEGER NOT NULL,
  50         usr INTEGER NOT NULL,
  51         PRIMARY KEY (grp, usr),
  52         FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE,
  53         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
  54
  55 INSERT INTO Membership
  56         (grp, usr) VALUES
  57         (0, 0);
  58
  59 CREATE TABLE Transaction(
  60         id INTEGER PRIMARY KEY,
  61         descr TEXT NOT NULL,
  62         amount REAL NOT NULL,
  63         d DATE NOT NULL,
  64         stamp TIMESTAMP NOT NULL);
  65
  66 CREATE SEQUENCE TransactionSeq START 1;
  67
  68 CREATE TABLE Charge(
  69         trn INTEGER NOT NULL,
  70         usr INTEGER NOT NULL,
  71         amount REAL NOT NULL,
  72         PRIMARY KEY (trn, usr),
  73         FOREIGN KEY (trn) REFERENCES Transaction(id) ON DELETE CASCADE,
  74         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
  75
  76 CREATE TABLE Poll(
  77         id INTEGER PRIMARY KEY,
  78         usr INTEGER NOT NULL,
  79         title TEXT NOT NULL,
  80         descr TEXT NOT NULL,
  81         starts DATE NOT NULL,
  82         ends DATE NOT NULL,
  83         votes INTEGER NOT NULL,
  84         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
  85
  86 CREATE SEQUENCE PollSeq START 1;
  87
  88 CREATE TABLE PollChoice(
  89         id INTEGER PRIMARY KEY,
  90         pol INTEGER NOT NULL,
  91         seq REAL NOT NULL,
  92         descr TEXT NOT NULL,
  93         FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE);
  94
  95 CREATE SEQUENCE PollChoiceSeq START 1;
  96
  97 CREATE TABLE Vote(
  98         usr INTEGER NOT NULL,
  99         cho INTEGER NOT NULL,
 100         PRIMARY KEY (usr, cho),
 101         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 102         FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE);
 103
 104 CREATE TABLE ContactKind(
 105         id INTEGER PRIMARY KEY,
 106         name TEXT NOT NULL,
 107         url BOOLEAN NOT NULL,
 108         urlPrefix TEXT,
 109         urlPostfix TEXT);
 110
 111 CREATE SEQUENCE ContactKindSeq START 1;
 112
 113 CREATE TABLE Contact(
 114         id INTEGER PRIMARY KEY,
 115         usr INTEGER NOT NULL,
 116         knd INTEGER NOT NULL,
 117         v TEXT NOT NULL,
 118         priv INTEGER NOT NULL,
 119         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 120         FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE);
 121
 122 CREATE SEQUENCE ContactSeq START 1;
 123
 124 CREATE TABLE Location(
 125         id INTEGER PRIMARY KEY,
 126         parent INTEGER,
 127         name TEXT NOT NULL,
 128         FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE);
 129
 130 CREATE SEQUENCE LocationSeq START 1;
 131
 132 CREATE TABLE Lives(
 133         usr INTEGER NOT NULL,
 134         loc INTEGER NOT NULL,
 135         PRIMARY KEY (usr, loc),
 136         FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
 137         FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE);