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 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
109 CREATE SEQUENCE PollSeq
START 1;
111 CREATE TABLE PollChoice(
112 id INTEGER PRIMARY KEY,
113 pol
INTEGER NOT NULL,
116 FOREIGN KEY (pol
) REFERENCES Poll(id) ON DELETE CASCADE);
118 CREATE SEQUENCE PollChoiceSeq
START 1;
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);
127 CREATE TABLE ContactKind(
128 id INTEGER PRIMARY KEY,
130 url
BOOLEAN NOT NULL,
134 CREATE SEQUENCE ContactKindSeq
START 1;
136 CREATE TABLE Contact(
137 id INTEGER PRIMARY KEY,
138 usr
INTEGER NOT NULL,
139 knd
INTEGER NOT NULL,
141 priv
INTEGER NOT NULL,
142 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
143 FOREIGN KEY (knd
) REFERENCES ContactKind(id) ON DELETE CASCADE);
145 CREATE SEQUENCE ContactSeq
START 1;
147 CREATE TABLE Location(
148 id INTEGER PRIMARY KEY,
151 FOREIGN KEY (parent
) REFERENCES Location(id) ON DELETE CASCADE);
153 CREATE SEQUENCE LocationSeq
START 1;
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);
163 id INTEGER PRIMARY KEY,
164 usr
INTEGER NOT NULL,
168 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
170 CREATE SEQUENCE LinkSeq
START 1;
172 CREATE TABLE SupCategory(
173 id INTEGER PRIMARY KEY,
174 grp
INTEGER NOT NULL,
177 FOREIGN KEY (grp
) REFERENCES WebGroup(id) ON DELETE CASCADE);
179 CREATE SEQUENCE SupCategorySeq
START 1;
181 CREATE TABLE SupIssue(
182 id INTEGER PRIMARY KEY,
183 usr
INTEGER NOT NULL,
184 cat
INTEGER NOT NULL,
186 priv
BOOLEAN NOT NULL,
187 status
INTEGER NOT NULL,
188 stamp
TIMESTAMP NOT NULL,
191 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
192 FOREIGN KEY (cat
) REFERENCES SupCategory(id) ON DELETE CASCADE);
194 CREATE SEQUENCE SupIssueSeq
START 1;
196 CREATE TABLE SupPost(
197 id INTEGER PRIMARY KEY,
198 usr
INTEGER NOT NULL,
199 iss
INTEGER 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);
205 CREATE SEQUENCE SupPostSeq
START 1;
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);
214 CREATE TABLE WebNode(
215 id INTEGER PRIMARY KEY,
218 debian
TEXT NOT NULL);
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');
228 id INTEGER PRIMARY KEY,
229 usr
INTEGER NOT NULL,
230 node
INTEGER NOT NULL,
233 status
INTEGER NOT NULL,
234 stamp
TIMESTAMP NOT NULL,
236 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
237 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
239 CREATE SEQUENCE AptSeq
START 1;
242 id INTEGER PRIMARY KEY,
243 usr
INTEGER NOT NULL,
246 status
INTEGER NOT NULL,
247 stamp
TIMESTAMP NOT NULL,
249 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
251 CREATE SEQUENCE DomainSeq
START 1;
253 CREATE TABLE MailingList(
254 id INTEGER PRIMARY KEY,
255 usr
INTEGER NOT NULL,
258 status
INTEGER NOT NULL,
259 stamp
TIMESTAMP NOT NULL,
261 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
263 CREATE SEQUENCE MailingListSeq
START 1;
265 CREATE TABLE DirectoryPref(
266 usr
INTEGER PRIMARY KEY,
267 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
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);
276 CREATE VIEW WebUserPaying
277 AS SELECT id, name, rname
, bal
, joined
, app
, shares
, paypal
, checkout
279 JOIN (SELECT usr
FROM Membership
JOIN WebGroup
281 AND WebGroup.
name = 'paying') AS bob
284 CREATE VIEW WebUserActive
285 AS SELECT id, name, rname
, bal
, joined
, app
, shares
, paypal
, checkout
287 LEFT OUTER JOIN (SELECT usr
FROM Membership
JOIN WebGroup
288 ON grp
= WebGroup.
id AND (WebGroup.
name IN ('retired', 'phantom'))) AS mem
292 CREATE TABLE RollCall(
293 id INTEGER PRIMARY KEY,
296 started
TIMESTAMP NOT NULL);
298 CREATE SEQUENCE RollCallSeq
START 1;
300 CREATE TABLE RollCallEntry(
301 rol
INTEGER NOT NULL,
302 usr
INTEGER NOT NULL,
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);
310 id INTEGER PRIMARY KEY,
311 usr
INTEGER NOT NULL,
312 node
INTEGER NOT NULL,
315 status
INTEGER NOT NULL,
316 stamp
TIMESTAMP NOT NULL,
318 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
319 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
321 CREATE SEQUENCE SecSeq
START 1;
324 id INTEGER PRIMARY KEY,
325 usr
INTEGER NOT NULL,
326 node
INTEGER NOT NULL,
329 status
INTEGER NOT NULL,
330 stamp
TIMESTAMP NOT NULL,
332 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
333 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
335 CREATE SEQUENCE IpSeq
START 1;
338 id INTEGER PRIMARY KEY,
339 usr
INTEGER NOT NULL,
342 status
INTEGER NOT NULL,
343 stamp
TIMESTAMP NOT NULL,
345 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
346 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
348 CREATE SEQUENCE CertSeq
START 1;