1 CREATE TABLE MemberApp(
2 id INTEGER PRIMARY KEY,
7 forward BOOLEAN NOT NULL,
11 status
INTEGER NOT NULL,
12 applied
TIMESTAMP NOT NULL,
17 unix_passwd
TEXT NOT NULL,
21 CREATE SEQUENCE MemberAppSeq
START 1;
24 id INTEGER PRIMARY KEY,
26 amount
REAL NOT NULL);
28 CREATE SEQUENCE BalanceSeq
START 1;
31 (id, name, amount
) VALUES
35 id INTEGER PRIMARY KEY,
39 joined
TIMESTAMP NOT NULL,
41 shares
INTEGER NOT NULL,
44 FOREIGN KEY (bal
) REFERENCES Balance(id) ON DELETE CASCADE,
45 FOREIGN KEY (app
) REFERENCES MemberApp(id) ON DELETE CASCADE);
47 CREATE SEQUENCE WebUserSeq
START 1;
50 (id, name, rname
, bal
, joined
) VALUES
51 (0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);
53 CREATE TABLE WebGroup(
54 id INTEGER PRIMARY KEY,
57 CREATE SEQUENCE WebGroupSeq
START 2;
67 CREATE TABLE Membership(
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);
74 INSERT INTO Membership
78 INSERT INTO Membership
82 CREATE TABLE Transaction(
83 id INTEGER PRIMARY KEY,
87 stamp
TIMESTAMP NOT NULL);
89 CREATE SEQUENCE TransactionSeq
START 1;
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);
100 id INTEGER PRIMARY KEY,
101 usr
INTEGER NOT NULL,
104 starts
DATE NOT NULL,
106 votes
INTEGER NOT NULL,
107 official BOOL
NOT NULL,
109 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
111 CREATE SEQUENCE PollSeq
START 1;
113 CREATE TABLE PollChoice(
114 id INTEGER PRIMARY KEY,
115 pol
INTEGER NOT NULL,
118 FOREIGN KEY (pol
) REFERENCES Poll(id) ON DELETE CASCADE);
120 CREATE SEQUENCE PollChoiceSeq
START 1;
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);
129 CREATE TABLE ContactKind(
130 id INTEGER PRIMARY KEY,
132 url
BOOLEAN NOT NULL,
136 CREATE SEQUENCE ContactKindSeq
START 1;
138 CREATE TABLE Contact(
139 id INTEGER PRIMARY KEY,
140 usr
INTEGER NOT NULL,
141 knd
INTEGER NOT NULL,
143 priv
INTEGER NOT NULL,
144 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
145 FOREIGN KEY (knd
) REFERENCES ContactKind(id) ON DELETE CASCADE);
147 CREATE SEQUENCE ContactSeq
START 1;
149 CREATE TABLE Location(
150 id INTEGER PRIMARY KEY,
153 FOREIGN KEY (parent
) REFERENCES Location(id) ON DELETE CASCADE);
155 CREATE SEQUENCE LocationSeq
START 1;
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);
165 id INTEGER PRIMARY KEY,
166 usr
INTEGER NOT NULL,
170 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
172 CREATE SEQUENCE LinkSeq
START 1;
174 CREATE TABLE SupCategory(
175 id INTEGER PRIMARY KEY,
176 grp
INTEGER NOT NULL,
179 FOREIGN KEY (grp
) REFERENCES WebGroup(id) ON DELETE CASCADE);
181 CREATE SEQUENCE SupCategorySeq
START 1;
183 CREATE TABLE SupIssue(
184 id INTEGER PRIMARY KEY,
185 usr
INTEGER NOT NULL,
186 cat
INTEGER NOT NULL,
188 priv
BOOLEAN NOT NULL,
189 status
INTEGER NOT NULL,
190 stamp
TIMESTAMP NOT NULL,
193 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
194 FOREIGN KEY (cat
) REFERENCES SupCategory(id) ON DELETE CASCADE);
196 CREATE SEQUENCE SupIssueSeq
START 1;
198 CREATE TABLE SupPost(
199 id INTEGER PRIMARY KEY,
200 usr
INTEGER NOT NULL,
201 iss
INTEGER 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);
207 CREATE SEQUENCE SupPostSeq
START 1;
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);
216 CREATE TABLE WebNode(
217 id INTEGER PRIMARY KEY,
220 debian
TEXT NOT NULL);
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');
230 id INTEGER PRIMARY KEY,
231 usr
INTEGER NOT NULL,
232 node
INTEGER NOT NULL,
235 status
INTEGER NOT NULL,
236 stamp
TIMESTAMP NOT NULL,
238 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
239 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
241 CREATE SEQUENCE AptSeq
START 1;
244 id INTEGER PRIMARY KEY,
245 usr
INTEGER NOT NULL,
248 status
INTEGER NOT NULL,
249 stamp
TIMESTAMP NOT NULL,
251 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
253 CREATE SEQUENCE DomainSeq
START 1;
255 CREATE TABLE MailingList(
256 id INTEGER PRIMARY KEY,
257 usr
INTEGER NOT NULL,
260 status
INTEGER NOT NULL,
261 stamp
TIMESTAMP NOT NULL,
263 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
265 CREATE SEQUENCE MailingListSeq
START 1;
267 CREATE TABLE DirectoryPref(
268 usr
INTEGER PRIMARY KEY,
269 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
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);
278 CREATE VIEW WebUserPaying
279 AS SELECT id, name, rname
, bal
, joined
, app
, shares
, paypal
, checkout
281 JOIN (SELECT usr
FROM Membership
JOIN WebGroup
283 AND WebGroup.
name = 'paying') AS bob
286 CREATE VIEW WebUserActive
287 AS SELECT id, name, rname
, bal
, joined
, app
, shares
, paypal
, checkout
289 LEFT OUTER JOIN (SELECT usr
FROM Membership
JOIN WebGroup
290 ON grp
= WebGroup.
id AND (WebGroup.
name IN ('retired', 'phantom'))) AS mem
294 CREATE TABLE RollCall(
295 id INTEGER PRIMARY KEY,
298 started
TIMESTAMP NOT NULL);
300 CREATE SEQUENCE RollCallSeq
START 1;
302 CREATE TABLE RollCallEntry(
303 rol
INTEGER NOT NULL,
304 usr
INTEGER NOT NULL,
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);
312 id INTEGER PRIMARY KEY,
313 usr
INTEGER NOT NULL,
314 node
INTEGER NOT NULL,
317 status
INTEGER NOT NULL,
318 stamp
TIMESTAMP NOT NULL,
320 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
321 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
323 CREATE SEQUENCE SecSeq
START 1;
326 id INTEGER PRIMARY KEY,
327 usr
INTEGER NOT NULL,
328 node
INTEGER NOT NULL,
331 status
INTEGER NOT NULL,
332 stamp
TIMESTAMP NOT NULL,
334 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
335 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
337 CREATE SEQUENCE IpSeq
START 1;
340 id INTEGER PRIMARY KEY,
341 usr
INTEGER NOT NULL,
344 status
INTEGER NOT NULL,
345 stamp
TIMESTAMP NOT NULL,
347 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
349 CREATE SEQUENCE CertSeq
START 1;
352 id INTEGER PRIMARY KEY,
353 usr
INTEGER NOT NULL,
356 status
INTEGER NOT NULL,
357 stamp
TIMESTAMP NOT NULL,
359 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
361 CREATE SEQUENCE SignSeq
START 1;
364 id INTEGER PRIMARY KEY,
365 usr
INTEGER NOT NULL,
368 status
INTEGER NOT NULL,
369 stamp
TIMESTAMP NOT NULL,
371 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
373 CREATE SEQUENCE QuotaSeq
START 1;