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 | |
c9247c1b AC |
23 | (id, name, rname, bal, joined) VALUES |
24 | (0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP); | |
208e2cbc AC |
25 | |
26 | CREATE TABLE WebGroup( | |
27 | id INTEGER PRIMARY KEY, | |
28 | name TEXT NOT NULL); | |
29 | ||
c9247c1b | 30 | CREATE SEQUENCE WebGroupSeq START 2; |
208e2cbc AC |
31 | |
32 | INSERT INTO WebGroup | |
33 | (id, name) VALUES | |
34 | (0, 'root'); | |
35 | ||
c9247c1b AC |
36 | INSERT INTO WebGroup |
37 | (id, name) VALUES | |
38 | (1, 'paying'); | |
39 | ||
208e2cbc AC |
40 | CREATE TABLE Membership( |
41 | grp INTEGER NOT NULL, | |
42 | usr INTEGER NOT NULL, | |
43 | PRIMARY KEY (grp, usr), | |
44 | FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE, | |
45 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
46 | ||
47 | INSERT INTO Membership | |
48 | (grp, usr) VALUES | |
49 | (0, 0); | |
50 | ||
c9247c1b AC |
51 | INSERT INTO Membership |
52 | (grp, usr) VALUES | |
53 | (1, 0); | |
54 | ||
208e2cbc AC |
55 | CREATE TABLE Transaction( |
56 | id INTEGER PRIMARY KEY, | |
57 | descr TEXT NOT NULL, | |
58 | amount REAL NOT NULL, | |
59 | d DATE NOT NULL, | |
60 | stamp TIMESTAMP NOT NULL); | |
61 | ||
62 | CREATE SEQUENCE TransactionSeq START 1; | |
63 | ||
64 | CREATE TABLE Charge( | |
65 | trn INTEGER NOT NULL, | |
66 | usr INTEGER NOT NULL, | |
67 | amount REAL NOT NULL, | |
68 | PRIMARY KEY (trn, usr), | |
69 | FOREIGN KEY (trn) REFERENCES Transaction(id) ON DELETE CASCADE, | |
70 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
71 | ||
e68ddb80 AC |
72 | CREATE TABLE Poll( |
73 | id INTEGER PRIMARY KEY, | |
74 | usr INTEGER NOT NULL, | |
75 | title TEXT NOT NULL, | |
76 | descr TEXT NOT NULL, | |
77 | starts DATE NOT NULL, | |
78 | ends DATE NOT NULL, | |
79 | votes INTEGER NOT NULL, | |
80 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
81 | ||
82 | CREATE SEQUENCE PollSeq START 1; | |
83 | ||
84 | CREATE TABLE PollChoice( | |
85 | id INTEGER PRIMARY KEY, | |
86 | pol INTEGER NOT NULL, | |
87 | seq REAL NOT NULL, | |
88 | descr TEXT NOT NULL, | |
89 | FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE); | |
90 | ||
91 | CREATE SEQUENCE PollChoiceSeq START 1; | |
92 | ||
93 | CREATE TABLE Vote( | |
94 | usr INTEGER NOT NULL, | |
95 | cho INTEGER NOT NULL, | |
96 | PRIMARY KEY (usr, cho), | |
97 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, | |
98 | FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE); | |
99 | ||
d6223be9 AC |
100 | CREATE TABLE ContactKind( |
101 | id INTEGER PRIMARY KEY, | |
102 | name TEXT NOT NULL, | |
103 | url BOOLEAN NOT NULL, | |
104 | urlPrefix TEXT, | |
105 | urlPostfix TEXT); | |
e68ddb80 | 106 | |
d6223be9 | 107 | CREATE SEQUENCE ContactKindSeq START 1; |
208e2cbc | 108 | |
d6223be9 | 109 | CREATE TABLE Contact( |
ee587f7f | 110 | id INTEGER PRIMARY KEY, |
d6223be9 AC |
111 | usr INTEGER NOT NULL, |
112 | knd INTEGER NOT NULL, | |
113 | v TEXT NOT NULL, | |
114 | priv INTEGER NOT NULL, | |
d6223be9 AC |
115 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
116 | FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE); | |
ee587f7f AC |
117 | |
118 | CREATE SEQUENCE ContactSeq START 1; | |
b340786b AC |
119 | |
120 | CREATE TABLE Location( | |
121 | id INTEGER PRIMARY KEY, | |
122 | parent INTEGER, | |
123 | name TEXT NOT NULL, | |
124 | FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE); | |
125 | ||
126 | CREATE SEQUENCE LocationSeq START 1; | |
127 | ||
128 | CREATE TABLE Lives( | |
129 | usr INTEGER NOT NULL, | |
130 | loc INTEGER NOT NULL, | |
131 | PRIMARY KEY (usr, loc), | |
132 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, | |
133 | FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE); | |
88a858ea AC |
134 | |
135 | CREATE TABLE Link( | |
136 | id INTEGER PRIMARY KEY, | |
137 | usr INTEGER NOT NULL, | |
138 | title TEXT NOT NULL, | |
139 | url TEXT NOT NULL, | |
140 | descr TEXT NOT NULL, | |
141 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
142 | ||
143 | CREATE SEQUENCE LinkSeq START 1; | |
144 | ||
5a2812ca AC |
145 | CREATE TABLE SupCategory( |
146 | id INTEGER PRIMARY KEY, | |
147 | grp INTEGER NOT NULL, | |
148 | name TEXT NOT NULL, | |
149 | descr TEXT NOT NULL, | |
150 | FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE); | |
151 | ||
152 | CREATE SEQUENCE SupCategorySeq START 1; | |
153 | ||
154 | CREATE TABLE SupIssue( | |
155 | id INTEGER PRIMARY KEY, | |
156 | usr INTEGER NOT NULL, | |
157 | cat INTEGER NOT NULL, | |
158 | title TEXT NOT NULL, | |
159 | priv BOOLEAN NOT NULL, | |
160 | status INTEGER NOT NULL, | |
161 | stamp TIMESTAMP NOT NULL, | |
162 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, | |
163 | FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE); | |
164 | ||
165 | CREATE SEQUENCE SupIssueSeq START 1; | |
166 | ||
167 | CREATE TABLE SupPost( | |
168 | id INTEGER PRIMARY KEY, | |
169 | usr INTEGER NOT NULL, | |
170 | iss INTEGER NOT NULL, | |
171 | body TEXT NOT NULL, | |
172 | stamp TIMESTAMP NOT NULL, | |
173 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, | |
174 | FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE); | |
175 | ||
176 | CREATE SEQUENCE SupPostSeq START 1; | |
177 | ||
178 | CREATE TABLE SupSubscription( | |
179 | usr INTEGER NOT NULL, | |
180 | cat INTEGER NOT NULL, | |
181 | PRIMARY KEY (usr, cat), | |
182 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, | |
183 | FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE); | |
184 | ||
6b23a78b AC |
185 | CREATE TABLE Apt( |
186 | id INTEGER PRIMARY KEY, | |
187 | usr INTEGER NOT NULL, | |
5da9f4a9 | 188 | data TEXT NOT NULL, |
6b23a78b AC |
189 | msg TEXT NOT NULL, |
190 | status INTEGER NOT NULL, | |
191 | stamp TIMESTAMP NOT NULL, | |
192 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
5a2812ca | 193 | |
6b23a78b | 194 | CREATE SEQUENCE AptSeq START 1; |
5da9f4a9 AC |
195 | |
196 | CREATE TABLE Domain( | |
197 | id INTEGER PRIMARY KEY, | |
198 | usr INTEGER NOT NULL, | |
199 | data TEXT NOT NULL, | |
200 | msg TEXT NOT NULL, | |
201 | status INTEGER NOT NULL, | |
202 | stamp TIMESTAMP NOT NULL, | |
203 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
204 | ||
205 | CREATE SEQUENCE DomainSeq START 1; | |
9d1c0e98 AC |
206 | |
207 | CREATE TABLE MailingList( | |
208 | id INTEGER PRIMARY KEY, | |
209 | usr INTEGER NOT NULL, | |
210 | data TEXT NOT NULL, | |
211 | msg TEXT NOT NULL, | |
212 | status INTEGER NOT NULL, | |
213 | stamp TIMESTAMP NOT NULL, | |
214 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
215 | ||
216 | CREATE SEQUENCE MailingListSeq START 1; | |
27e48ace AC |
217 | |
218 | CREATE TABLE DirectoryPref( | |
219 | usr INTEGER PRIMARY KEY, | |
220 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
a90da8b1 AC |
221 | |
222 | CREATE TABLE MemberApp( | |
223 | id INTEGER PRIMARY KEY, | |
224 | name TEXT NOT NULL, | |
225 | rname TEXT NOT NULL, | |
226 | email TEXT NOT NULL, | |
227 | forward BOOLEAN NOT NULL, | |
228 | uses TEXT NOT NULL, | |
229 | other TEXT NOT NULL, | |
230 | passwd TEXT NOT NULL, | |
231 | status INTEGER NOT NULL, | |
232 | applied TIMESTAMP NOT NULL, | |
233 | confirmed TIMESTAMP, | |
6f91863c AC |
234 | decided TIMESTAMP, |
235 | msg TEXT NOT NULL); | |
a90da8b1 AC |
236 | |
237 | CREATE SEQUENCE MemberAppSeq START 1; | |
238 | ||
239 | CREATE TABLE AppVote( | |
240 | app INTEGER NOT NULL, | |
241 | usr INTEGER NOT NULL, | |
242 | PRIMARY KEY (app, usr), | |
243 | FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE, | |
244 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
245 |