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 | ||
e68ddb80 | 30 | CREATE SEQUENCE WebGroupSeq START 4; |
208e2cbc AC |
31 | |
32 | INSERT INTO WebGroup | |
33 | (id, name) VALUES | |
34 | (0, 'root'); | |
35 | ||
e68ddb80 AC |
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 | ||
208e2cbc AC |
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 | ||
e68ddb80 AC |
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 | ||
208e2cbc | 105 |