Commit | Line | Data |
---|---|---|
f3f3ad24 AC |
1 | CREATE TABLE MemberApp( |
2 | id INTEGER PRIMARY KEY, | |
3 | name TEXT NOT NULL, | |
4 | rname TEXT NOT NULL, | |
5 | gname TEXT, | |
6 | email TEXT NOT NULL, | |
7 | forward BOOLEAN NOT NULL, | |
8 | uses TEXT NOT NULL, | |
9 | other TEXT NOT NULL, | |
10 | passwd TEXT NOT NULL, | |
11 | status INTEGER NOT NULL, | |
12 | applied TIMESTAMP NOT NULL, | |
13 | ipaddr TEXT, | |
14 | confirmed TIMESTAMP, | |
15 | decided TIMESTAMP, | |
a2d53da2 | 16 | msg TEXT NOT NULL, |
d5f8418b AC |
17 | unix_passwd TEXT NOT NULL, |
18 | paypal TEXT, | |
19 | checkout TEXT); | |
f3f3ad24 AC |
20 | |
21 | CREATE SEQUENCE MemberAppSeq START 1; | |
22 | ||
208e2cbc AC |
23 | CREATE TABLE Balance( |
24 | id INTEGER PRIMARY KEY, | |
25 | name TEXT NOT NULL, | |
26 | amount REAL NOT NULL); | |
27 | ||
28 | CREATE SEQUENCE BalanceSeq START 1; | |
29 | ||
30 | INSERT INTO Balance | |
31 | (id, name, amount) VALUES | |
32 | (0, 'adam', 0.0); | |
33 | ||
34 | CREATE TABLE WebUser( | |
35 | id INTEGER PRIMARY KEY, | |
36 | name TEXT NOT NULL, | |
37 | rname TEXT NOT NULL, | |
38 | bal INTEGER NOT NULL, | |
39 | joined TIMESTAMP NOT NULL, | |
f3f3ad24 | 40 | app INTEGER NOT NULL, |
aaa50197 | 41 | shares INTEGER NOT NULL, |
d5f8418b AC |
42 | paypal TEXT, |
43 | checkout TEXT | |
f3f3ad24 AC |
44 | FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE, |
45 | FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE); | |
208e2cbc AC |
46 | |
47 | CREATE SEQUENCE WebUserSeq START 1; | |
48 | ||
49 | INSERT INTO WebUser | |
c9247c1b AC |
50 | (id, name, rname, bal, joined) VALUES |
51 | (0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP); | |
208e2cbc AC |
52 | |
53 | CREATE TABLE WebGroup( | |
54 | id INTEGER PRIMARY KEY, | |
55 | name TEXT NOT NULL); | |
56 | ||
c9247c1b | 57 | CREATE SEQUENCE WebGroupSeq START 2; |
208e2cbc AC |
58 | |
59 | INSERT INTO WebGroup | |
60 | (id, name) VALUES | |
61 | (0, 'root'); | |
62 | ||
c9247c1b AC |
63 | INSERT INTO WebGroup |
64 | (id, name) VALUES | |
65 | (1, 'paying'); | |
66 | ||
208e2cbc AC |
67 | CREATE TABLE Membership( |
68 | grp INTEGER NOT NULL, | |
69 | usr INTEGER NOT NULL, | |
70 | PRIMARY KEY (grp, usr), | |
71 | FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE, | |
72 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
73 | ||
74 | INSERT INTO Membership | |
75 | (grp, usr) VALUES | |
76 | (0, 0); | |
77 | ||
c9247c1b AC |
78 | INSERT INTO Membership |
79 | (grp, usr) VALUES | |
80 | (1, 0); | |
81 | ||
208e2cbc AC |
82 | CREATE TABLE Transaction( |
83 | id INTEGER PRIMARY KEY, | |
84 | descr TEXT NOT NULL, | |
85 | amount REAL NOT NULL, | |
86 | d DATE NOT NULL, | |
87 | stamp TIMESTAMP NOT NULL); | |
88 | ||
89 | CREATE SEQUENCE TransactionSeq START 1; | |
90 | ||
91 | CREATE TABLE Charge( | |
92 | trn INTEGER NOT NULL, | |
93 | usr INTEGER NOT NULL, | |
94 | amount REAL NOT NULL, | |
95 | PRIMARY KEY (trn, usr), | |
96 | FOREIGN KEY (trn) REFERENCES Transaction(id) ON DELETE CASCADE, | |
97 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
98 | ||
e68ddb80 AC |
99 | CREATE TABLE Poll( |
100 | id INTEGER PRIMARY KEY, | |
101 | usr INTEGER NOT NULL, | |
102 | title TEXT NOT NULL, | |
103 | descr TEXT NOT NULL, | |
104 | starts DATE NOT NULL, | |
105 | ends DATE NOT NULL, | |
106 | votes INTEGER NOT NULL, | |
a0a15865 | 107 | official BOOL NOT NULL, |
ed97a006 | 108 | ready BOOL NOT NULL, |
e68ddb80 AC |
109 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
110 | ||
111 | CREATE SEQUENCE PollSeq START 1; | |
112 | ||
113 | CREATE TABLE PollChoice( | |
114 | id INTEGER PRIMARY KEY, | |
115 | pol INTEGER NOT NULL, | |
116 | seq REAL NOT NULL, | |
117 | descr TEXT NOT NULL, | |
118 | FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE); | |
119 | ||
120 | CREATE SEQUENCE PollChoiceSeq START 1; | |
121 | ||
122 | CREATE TABLE Vote( | |
123 | usr INTEGER NOT NULL, | |
124 | cho INTEGER NOT NULL, | |
125 | PRIMARY KEY (usr, cho), | |
126 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, | |
127 | FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE); | |
128 | ||
d6223be9 AC |
129 | CREATE TABLE ContactKind( |
130 | id INTEGER PRIMARY KEY, | |
131 | name TEXT NOT NULL, | |
132 | url BOOLEAN NOT NULL, | |
133 | urlPrefix TEXT, | |
134 | urlPostfix TEXT); | |
e68ddb80 | 135 | |
d6223be9 | 136 | CREATE SEQUENCE ContactKindSeq START 1; |
208e2cbc | 137 | |
d6223be9 | 138 | CREATE TABLE Contact( |
ee587f7f | 139 | id INTEGER PRIMARY KEY, |
d6223be9 AC |
140 | usr INTEGER NOT NULL, |
141 | knd INTEGER NOT NULL, | |
142 | v TEXT NOT NULL, | |
143 | priv INTEGER NOT NULL, | |
d6223be9 AC |
144 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
145 | FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE); | |
ee587f7f AC |
146 | |
147 | CREATE SEQUENCE ContactSeq START 1; | |
b340786b AC |
148 | |
149 | CREATE TABLE Location( | |
150 | id INTEGER PRIMARY KEY, | |
151 | parent INTEGER, | |
152 | name TEXT NOT NULL, | |
153 | FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE); | |
154 | ||
155 | CREATE SEQUENCE LocationSeq START 1; | |
156 | ||
157 | CREATE TABLE Lives( | |
158 | usr INTEGER NOT NULL, | |
159 | loc INTEGER NOT NULL, | |
160 | PRIMARY KEY (usr, loc), | |
161 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, | |
162 | FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE); | |
88a858ea AC |
163 | |
164 | CREATE TABLE Link( | |
165 | id INTEGER PRIMARY KEY, | |
166 | usr INTEGER NOT NULL, | |
167 | title TEXT NOT NULL, | |
168 | url TEXT NOT NULL, | |
169 | descr TEXT NOT NULL, | |
170 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
171 | ||
172 | CREATE SEQUENCE LinkSeq START 1; | |
173 | ||
5a2812ca AC |
174 | CREATE TABLE SupCategory( |
175 | id INTEGER PRIMARY KEY, | |
176 | grp INTEGER NOT NULL, | |
177 | name TEXT NOT NULL, | |
178 | descr TEXT NOT NULL, | |
179 | FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE); | |
180 | ||
181 | CREATE SEQUENCE SupCategorySeq START 1; | |
182 | ||
183 | CREATE TABLE SupIssue( | |
184 | id INTEGER PRIMARY KEY, | |
185 | usr INTEGER NOT NULL, | |
186 | cat INTEGER NOT NULL, | |
187 | title TEXT NOT NULL, | |
188 | priv BOOLEAN NOT NULL, | |
189 | status INTEGER NOT NULL, | |
190 | stamp TIMESTAMP NOT NULL, | |
4d46d3eb AC |
191 | pstamp TIMESTAMP, |
192 | cstamp TIMESTAMP, | |
5a2812ca AC |
193 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
194 | FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE); | |
195 | ||
196 | CREATE SEQUENCE SupIssueSeq START 1; | |
197 | ||
198 | CREATE TABLE SupPost( | |
199 | id INTEGER PRIMARY KEY, | |
200 | usr INTEGER NOT NULL, | |
201 | iss INTEGER NOT NULL, | |
202 | body TEXT NOT NULL, | |
203 | stamp TIMESTAMP NOT NULL, | |
204 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, | |
205 | FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE); | |
206 | ||
207 | CREATE SEQUENCE SupPostSeq START 1; | |
208 | ||
209 | CREATE TABLE SupSubscription( | |
210 | usr INTEGER NOT NULL, | |
211 | cat INTEGER NOT NULL, | |
212 | PRIMARY KEY (usr, cat), | |
213 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, | |
214 | FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE); | |
215 | ||
18eeb749 AC |
216 | CREATE TABLE WebNode( |
217 | id INTEGER PRIMARY KEY, | |
218 | name TEXT NOT NULL, | |
219 | descr TEXT NOT NULL, | |
220 | debian TEXT NOT NULL); | |
221 | ||
222 | INSERT INTO WebNode (id, name, descr, debian) | |
223 | VALUES (0, 'fyodor', 'old server', 'testing'); | |
224 | INSERT INTO WebNode (id, name, descr, debian) | |
225 | VALUES (1, 'deleuze', 'main server', 'stable'); | |
226 | INSERT INTO WebNode (id, name, descr, debian) | |
227 | VALUES (2, 'mire', 'member web server', 'stable'); | |
228 | ||
6b23a78b AC |
229 | CREATE TABLE Apt( |
230 | id INTEGER PRIMARY KEY, | |
231 | usr INTEGER NOT NULL, | |
18eeb749 | 232 | node INTEGER NOT NULL, |
5da9f4a9 | 233 | data TEXT NOT NULL, |
6b23a78b AC |
234 | msg TEXT NOT NULL, |
235 | status INTEGER NOT NULL, | |
236 | stamp TIMESTAMP NOT NULL, | |
a75ed94b | 237 | cstamp TIMESTAMP, |
308f44e7 AC |
238 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
239 | FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE); | |
5a2812ca | 240 | |
6b23a78b | 241 | CREATE SEQUENCE AptSeq START 1; |
5da9f4a9 AC |
242 | |
243 | CREATE TABLE Domain( | |
244 | id INTEGER PRIMARY KEY, | |
245 | usr INTEGER NOT NULL, | |
246 | data TEXT NOT NULL, | |
247 | msg TEXT NOT NULL, | |
248 | status INTEGER NOT NULL, | |
249 | stamp TIMESTAMP NOT NULL, | |
a75ed94b | 250 | cstamp TIMESTAMP, |
5da9f4a9 AC |
251 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
252 | ||
253 | CREATE SEQUENCE DomainSeq START 1; | |
9d1c0e98 AC |
254 | |
255 | CREATE TABLE MailingList( | |
256 | id INTEGER PRIMARY KEY, | |
257 | usr INTEGER NOT NULL, | |
258 | data TEXT NOT NULL, | |
259 | msg TEXT NOT NULL, | |
260 | status INTEGER NOT NULL, | |
261 | stamp TIMESTAMP NOT NULL, | |
a75ed94b | 262 | cstamp TIMESTAMP, |
9d1c0e98 AC |
263 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
264 | ||
265 | CREATE SEQUENCE MailingListSeq START 1; | |
27e48ace AC |
266 | |
267 | CREATE TABLE DirectoryPref( | |
268 | usr INTEGER PRIMARY KEY, | |
269 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
a90da8b1 | 270 | |
a90da8b1 AC |
271 | CREATE TABLE AppVote( |
272 | app INTEGER NOT NULL, | |
273 | usr INTEGER NOT NULL, | |
274 | PRIMARY KEY (app, usr), | |
275 | FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE, | |
276 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
277 | ||
711b47a1 | 278 | CREATE VIEW WebUserPaying |
d5f8418b | 279 | AS SELECT id, name, rname, bal, joined, app, shares, paypal, checkout |
711b47a1 AC |
280 | FROM WebUser |
281 | JOIN (SELECT usr FROM Membership JOIN WebGroup | |
282 | ON grp = WebGroup.id | |
aaa50197 | 283 | AND WebGroup.name = 'paying') AS bob |
711b47a1 AC |
284 | ON usr = WebUser.id; |
285 | ||
4ac8f639 | 286 | CREATE VIEW WebUserActive |
d5f8418b | 287 | AS SELECT id, name, rname, bal, joined, app, shares, paypal, checkout |
4ac8f639 AC |
288 | FROM WebUser |
289 | LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup | |
a4ccdb5e | 290 | ON grp = WebGroup.id AND (WebGroup.name IN ('retired', 'phantom'))) AS mem |
4ac8f639 | 291 | ON usr = WebUser.id |
4ac8f639 AC |
292 | WHERE usr IS NULL; |
293 | ||
a4ccdb5e AC |
294 | CREATE TABLE RollCall( |
295 | id INTEGER PRIMARY KEY, | |
296 | title TEXT NOT NULL, | |
297 | msg TEXT NOT NULL, | |
298 | started TIMESTAMP NOT NULL); | |
299 | ||
300 | CREATE SEQUENCE RollCallSeq START 1; | |
301 | ||
302 | CREATE TABLE RollCallEntry( | |
303 | rol INTEGER NOT NULL, | |
304 | usr INTEGER NOT NULL, | |
305 | code TEXT NOT NULL, | |
306 | responded TIMESTAMP, | |
307 | PRIMARY KEY (rol, usr), | |
308 | FOREIGN KEY (rol) REFERENCES RollCall(id) ON DELETE CASCADE, | |
309 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
dfb0d0d7 AC |
310 | |
311 | CREATE TABLE Sec( | |
312 | id INTEGER PRIMARY KEY, | |
313 | usr INTEGER NOT NULL, | |
3d2ed222 | 314 | node INTEGER NOT NULL, |
dfb0d0d7 AC |
315 | data TEXT NOT NULL, |
316 | msg TEXT NOT NULL, | |
317 | status INTEGER NOT NULL, | |
318 | stamp TIMESTAMP NOT NULL, | |
a75ed94b | 319 | cstamp TIMESTAMP, |
308f44e7 AC |
320 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
321 | FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE); | |
dfb0d0d7 AC |
322 | |
323 | CREATE SEQUENCE SecSeq START 1; | |
9a4c122a AC |
324 | |
325 | CREATE TABLE Ip( | |
326 | id INTEGER PRIMARY KEY, | |
327 | usr INTEGER NOT NULL, | |
328 | node INTEGER NOT NULL, | |
329 | data TEXT NOT NULL, | |
330 | msg TEXT NOT NULL, | |
331 | status INTEGER NOT NULL, | |
332 | stamp TIMESTAMP NOT NULL, | |
333 | cstamp TIMESTAMP, | |
334 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, | |
335 | FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE); | |
336 | ||
337 | CREATE SEQUENCE IpSeq START 1; | |
95a4653e AC |
338 | |
339 | CREATE TABLE Cert( | |
340 | id INTEGER PRIMARY KEY, | |
341 | usr INTEGER NOT NULL, | |
342 | data TEXT NOT NULL, | |
343 | msg TEXT NOT NULL, | |
344 | status INTEGER NOT NULL, | |
345 | stamp TIMESTAMP NOT NULL, | |
346 | cstamp TIMESTAMP, | |
6704531c | 347 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
95a4653e AC |
348 | |
349 | CREATE SEQUENCE CertSeq START 1; | |
6704531c | 350 | |
eafe3d52 AC |
351 | CREATE TABLE Sign( |
352 | id INTEGER PRIMARY KEY, | |
353 | usr INTEGER NOT NULL, | |
354 | data TEXT NOT NULL, | |
355 | msg TEXT NOT NULL, | |
356 | status INTEGER NOT NULL, | |
357 | stamp TIMESTAMP NOT NULL, | |
358 | cstamp TIMESTAMP, | |
359 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
360 | ||
361 | CREATE SEQUENCE SignSeq START 1; | |
362 | ||
6704531c AC |
363 | CREATE TABLE Quota( |
364 | id INTEGER PRIMARY KEY, | |
365 | usr INTEGER NOT NULL, | |
366 | data TEXT NOT NULL, | |
367 | msg TEXT NOT NULL, | |
368 | status INTEGER NOT NULL, | |
369 | stamp TIMESTAMP NOT NULL, | |
370 | cstamp TIMESTAMP, | |
371 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); | |
372 | ||
373 | CREATE SEQUENCE QuotaSeq START 1; |