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 | ||
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 | ||
e68ddb80 AC |
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 | ||
d6223be9 AC |
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); | |
e68ddb80 | 98 | |
d6223be9 | 99 | CREATE SEQUENCE ContactKindSeq START 1; |
208e2cbc | 100 | |
d6223be9 | 101 | CREATE TABLE Contact( |
ee587f7f | 102 | id INTEGER PRIMARY KEY, |
d6223be9 AC |
103 | usr INTEGER NOT NULL, |
104 | knd INTEGER NOT NULL, | |
105 | v TEXT NOT NULL, | |
106 | priv INTEGER NOT NULL, | |
d6223be9 AC |
107 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
108 | FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE); | |
ee587f7f AC |
109 | |
110 | CREATE SEQUENCE ContactSeq START 1; | |
b340786b AC |
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); | |
88a858ea AC |
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 |