5146e435 |
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, |
8d550734 |
16 | msg TEXT NOT NULL, |
20acb925 |
17 | unix_passwd TEXT NOT NULL, |
18 | paypal TEXT, |
19 | checkout TEXT); |
5146e435 |
20 | |
21 | CREATE SEQUENCE MemberAppSeq START 1; |
22 | |
8d347a33 |
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, |
5146e435 |
40 | app INTEGER NOT NULL, |
892e3ea1 |
41 | shares INTEGER NOT NULL, |
20acb925 |
42 | paypal TEXT, |
43 | checkout TEXT |
5146e435 |
44 | FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE, |
45 | FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE); |
8d347a33 |
46 | |
47 | CREATE SEQUENCE WebUserSeq START 1; |
48 | |
49 | INSERT INTO WebUser |
3d009713 |
50 | (id, name, rname, bal, joined) VALUES |
51 | (0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP); |
8d347a33 |
52 | |
53 | CREATE TABLE WebGroup( |
54 | id INTEGER PRIMARY KEY, |
55 | name TEXT NOT NULL); |
56 | |
3d009713 |
57 | CREATE SEQUENCE WebGroupSeq START 2; |
8d347a33 |
58 | |
59 | INSERT INTO WebGroup |
60 | (id, name) VALUES |
61 | (0, 'root'); |
62 | |
3d009713 |
63 | INSERT INTO WebGroup |
64 | (id, name) VALUES |
65 | (1, 'paying'); |
66 | |
8d347a33 |
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 | |
3d009713 |
78 | INSERT INTO Membership |
79 | (grp, usr) VALUES |
80 | (1, 0); |
81 | |
8d347a33 |
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 | |
57c305c1 |
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, |
107 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
108 | |
109 | CREATE SEQUENCE PollSeq START 1; |
110 | |
111 | CREATE TABLE PollChoice( |
112 | id INTEGER PRIMARY KEY, |
113 | pol INTEGER NOT NULL, |
114 | seq REAL NOT NULL, |
115 | descr TEXT NOT NULL, |
116 | FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE); |
117 | |
118 | CREATE SEQUENCE PollChoiceSeq START 1; |
119 | |
120 | CREATE TABLE Vote( |
121 | usr INTEGER NOT NULL, |
122 | cho INTEGER NOT NULL, |
123 | PRIMARY KEY (usr, cho), |
124 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
125 | FOREIGN KEY (cho) REFERENCES PollChoice(id) ON DELETE CASCADE); |
126 | |
46b0c2df |
127 | CREATE TABLE ContactKind( |
128 | id INTEGER PRIMARY KEY, |
129 | name TEXT NOT NULL, |
130 | url BOOLEAN NOT NULL, |
131 | urlPrefix TEXT, |
132 | urlPostfix TEXT); |
57c305c1 |
133 | |
46b0c2df |
134 | CREATE SEQUENCE ContactKindSeq START 1; |
8d347a33 |
135 | |
46b0c2df |
136 | CREATE TABLE Contact( |
369e1577 |
137 | id INTEGER PRIMARY KEY, |
46b0c2df |
138 | usr INTEGER NOT NULL, |
139 | knd INTEGER NOT NULL, |
140 | v TEXT NOT NULL, |
141 | priv INTEGER NOT NULL, |
46b0c2df |
142 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
143 | FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE); |
369e1577 |
144 | |
145 | CREATE SEQUENCE ContactSeq START 1; |
51520441 |
146 | |
147 | CREATE TABLE Location( |
148 | id INTEGER PRIMARY KEY, |
149 | parent INTEGER, |
150 | name TEXT NOT NULL, |
151 | FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE); |
152 | |
153 | CREATE SEQUENCE LocationSeq START 1; |
154 | |
155 | CREATE TABLE Lives( |
156 | usr INTEGER NOT NULL, |
157 | loc INTEGER NOT NULL, |
158 | PRIMARY KEY (usr, loc), |
159 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
160 | FOREIGN KEY (loc) REFERENCES Location(id) ON DELETE CASCADE); |
29c3cc58 |
161 | |
162 | CREATE TABLE Link( |
163 | id INTEGER PRIMARY KEY, |
164 | usr INTEGER NOT NULL, |
165 | title TEXT NOT NULL, |
166 | url TEXT NOT NULL, |
167 | descr TEXT NOT NULL, |
168 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
169 | |
170 | CREATE SEQUENCE LinkSeq START 1; |
171 | |
c7311141 |
172 | CREATE TABLE SupCategory( |
173 | id INTEGER PRIMARY KEY, |
174 | grp INTEGER NOT NULL, |
175 | name TEXT NOT NULL, |
176 | descr TEXT NOT NULL, |
177 | FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE); |
178 | |
179 | CREATE SEQUENCE SupCategorySeq START 1; |
180 | |
181 | CREATE TABLE SupIssue( |
182 | id INTEGER PRIMARY KEY, |
183 | usr INTEGER NOT NULL, |
184 | cat INTEGER NOT NULL, |
185 | title TEXT NOT NULL, |
186 | priv BOOLEAN NOT NULL, |
187 | status INTEGER NOT NULL, |
188 | stamp TIMESTAMP NOT NULL, |
1365f9a0 |
189 | pstamp TIMESTAMP, |
190 | cstamp TIMESTAMP, |
c7311141 |
191 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
192 | FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE); |
193 | |
194 | CREATE SEQUENCE SupIssueSeq START 1; |
195 | |
196 | CREATE TABLE SupPost( |
197 | id INTEGER PRIMARY KEY, |
198 | usr INTEGER NOT NULL, |
199 | iss INTEGER NOT NULL, |
200 | body TEXT NOT NULL, |
201 | stamp TIMESTAMP NOT NULL, |
202 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
203 | FOREIGN KEY (iss) REFERENCES SupIssue(id) ON DELETE CASCADE); |
204 | |
205 | CREATE SEQUENCE SupPostSeq START 1; |
206 | |
207 | CREATE TABLE SupSubscription( |
208 | usr INTEGER NOT NULL, |
209 | cat INTEGER NOT NULL, |
210 | PRIMARY KEY (usr, cat), |
211 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
212 | FOREIGN KEY (cat) REFERENCES SupCategory(id) ON DELETE CASCADE); |
213 | |
8023de7b |
214 | CREATE TABLE WebNode( |
215 | id INTEGER PRIMARY KEY, |
216 | name TEXT NOT NULL, |
217 | descr TEXT NOT NULL, |
218 | debian TEXT NOT NULL); |
219 | |
220 | INSERT INTO WebNode (id, name, descr, debian) |
221 | VALUES (0, 'fyodor', 'old server', 'testing'); |
222 | INSERT INTO WebNode (id, name, descr, debian) |
223 | VALUES (1, 'deleuze', 'main server', 'stable'); |
224 | INSERT INTO WebNode (id, name, descr, debian) |
225 | VALUES (2, 'mire', 'member web server', 'stable'); |
226 | |
36d5f176 |
227 | CREATE TABLE Apt( |
228 | id INTEGER PRIMARY KEY, |
229 | usr INTEGER NOT NULL, |
8023de7b |
230 | node INTEGER NOT NULL, |
ff2b7604 |
231 | data TEXT NOT NULL, |
36d5f176 |
232 | msg TEXT NOT NULL, |
233 | status INTEGER NOT NULL, |
234 | stamp TIMESTAMP NOT NULL, |
cac002c5 |
235 | cstamp TIMESTAMP, |
9396e2cf |
236 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
237 | FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE); |
c7311141 |
238 | |
36d5f176 |
239 | CREATE SEQUENCE AptSeq START 1; |
ff2b7604 |
240 | |
241 | CREATE TABLE Domain( |
242 | id INTEGER PRIMARY KEY, |
243 | usr INTEGER NOT NULL, |
244 | data TEXT NOT NULL, |
245 | msg TEXT NOT NULL, |
246 | status INTEGER NOT NULL, |
247 | stamp TIMESTAMP NOT NULL, |
cac002c5 |
248 | cstamp TIMESTAMP, |
ff2b7604 |
249 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
250 | |
251 | CREATE SEQUENCE DomainSeq START 1; |
78304862 |
252 | |
253 | CREATE TABLE MailingList( |
254 | id INTEGER PRIMARY KEY, |
255 | usr INTEGER NOT NULL, |
256 | data TEXT NOT NULL, |
257 | msg TEXT NOT NULL, |
258 | status INTEGER NOT NULL, |
259 | stamp TIMESTAMP NOT NULL, |
cac002c5 |
260 | cstamp TIMESTAMP, |
78304862 |
261 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
262 | |
263 | CREATE SEQUENCE MailingListSeq START 1; |
566fd712 |
264 | |
265 | CREATE TABLE DirectoryPref( |
266 | usr INTEGER PRIMARY KEY, |
267 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
20a679fc |
268 | |
20a679fc |
269 | CREATE TABLE AppVote( |
270 | app INTEGER NOT NULL, |
271 | usr INTEGER NOT NULL, |
272 | PRIMARY KEY (app, usr), |
273 | FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE, |
274 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
275 | |
ea455810 |
276 | CREATE VIEW WebUserPaying |
20acb925 |
277 | AS SELECT id, name, rname, bal, joined, app, shares, paypal, checkout |
ea455810 |
278 | FROM WebUser |
279 | JOIN (SELECT usr FROM Membership JOIN WebGroup |
280 | ON grp = WebGroup.id |
892e3ea1 |
281 | AND WebGroup.name = 'paying') AS bob |
ea455810 |
282 | ON usr = WebUser.id; |
283 | |
47bc9315 |
284 | CREATE VIEW WebUserActive |
20acb925 |
285 | AS SELECT id, name, rname, bal, joined, app, shares, paypal, checkout |
47bc9315 |
286 | FROM WebUser |
287 | LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup |
add44c00 |
288 | ON grp = WebGroup.id AND (WebGroup.name IN ('retired', 'phantom'))) AS mem |
47bc9315 |
289 | ON usr = WebUser.id |
47bc9315 |
290 | WHERE usr IS NULL; |
291 | |
add44c00 |
292 | CREATE TABLE RollCall( |
293 | id INTEGER PRIMARY KEY, |
294 | title TEXT NOT NULL, |
295 | msg TEXT NOT NULL, |
296 | started TIMESTAMP NOT NULL); |
297 | |
298 | CREATE SEQUENCE RollCallSeq START 1; |
299 | |
300 | CREATE TABLE RollCallEntry( |
301 | rol INTEGER NOT NULL, |
302 | usr INTEGER NOT NULL, |
303 | code TEXT NOT NULL, |
304 | responded TIMESTAMP, |
305 | PRIMARY KEY (rol, usr), |
306 | FOREIGN KEY (rol) REFERENCES RollCall(id) ON DELETE CASCADE, |
307 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
3ad30cf6 |
308 | |
309 | CREATE TABLE Sec( |
310 | id INTEGER PRIMARY KEY, |
311 | usr INTEGER NOT NULL, |
08e839b2 |
312 | node INTEGER NOT NULL, |
3ad30cf6 |
313 | data TEXT NOT NULL, |
314 | msg TEXT NOT NULL, |
315 | status INTEGER NOT NULL, |
316 | stamp TIMESTAMP NOT NULL, |
cac002c5 |
317 | cstamp TIMESTAMP, |
9396e2cf |
318 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
319 | FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE); |
3ad30cf6 |
320 | |
321 | CREATE SEQUENCE SecSeq START 1; |
047812d5 |
322 | |
323 | CREATE TABLE Ip( |
324 | id INTEGER PRIMARY KEY, |
325 | usr INTEGER NOT NULL, |
326 | node INTEGER NOT NULL, |
327 | data TEXT NOT NULL, |
328 | msg TEXT NOT NULL, |
329 | status INTEGER NOT NULL, |
330 | stamp TIMESTAMP NOT NULL, |
331 | cstamp TIMESTAMP, |
332 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
333 | FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE); |
334 | |
335 | CREATE SEQUENCE IpSeq START 1; |
d6e189f0 |
336 | |
337 | CREATE TABLE Cert( |
338 | id INTEGER PRIMARY KEY, |
339 | usr INTEGER NOT NULL, |
340 | data TEXT NOT NULL, |
341 | msg TEXT NOT NULL, |
342 | status INTEGER NOT NULL, |
343 | stamp TIMESTAMP NOT NULL, |
344 | cstamp TIMESTAMP, |
345 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
346 | FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE); |
347 | |
348 | CREATE SEQUENCE CertSeq START 1; |