Commit | Line | Data |
---|---|---|
208e2cbc AC |
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 |