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 1;
  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