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);
19 CREATE SEQUENCE MemberAppSeq
START 1;
22 id INTEGER PRIMARY KEY,
24 amount
REAL NOT NULL);
26 CREATE SEQUENCE BalanceSeq
START 1;
29 (id, name, amount
) VALUES
33 id INTEGER PRIMARY KEY,
37 joined
TIMESTAMP NOT NULL,
39 shares
INTEGER NOT NULL,
40 FOREIGN KEY (bal
) REFERENCES Balance(id) ON DELETE CASCADE,
41 FOREIGN KEY (app
) REFERENCES MemberApp(id) ON DELETE CASCADE);
43 CREATE SEQUENCE WebUserSeq
START 1;
46 (id, name, rname
, bal
, joined
) VALUES
47 (0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);
49 CREATE TABLE WebGroup(
50 id INTEGER PRIMARY KEY,
53 CREATE SEQUENCE WebGroupSeq
START 2;
63 CREATE TABLE Membership(
66 PRIMARY KEY (grp
, usr
),
67 FOREIGN KEY (grp
) REFERENCES WebGroup(id) ON DELETE CASCADE,
68 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
70 INSERT INTO Membership
74 INSERT INTO Membership
78 CREATE TABLE Transaction(
79 id INTEGER PRIMARY KEY,
83 stamp
TIMESTAMP NOT NULL);
85 CREATE SEQUENCE TransactionSeq
START 1;
91 PRIMARY KEY (trn
, usr
),
92 FOREIGN KEY (trn
) REFERENCES Transaction(id) ON DELETE CASCADE,
93 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
96 id INTEGER PRIMARY KEY,
100 starts
DATE NOT NULL,
102 votes
INTEGER NOT NULL,
103 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
105 CREATE SEQUENCE PollSeq
START 1;
107 CREATE TABLE PollChoice(
108 id INTEGER PRIMARY KEY,
109 pol
INTEGER NOT NULL,
112 FOREIGN KEY (pol
) REFERENCES Poll(id) ON DELETE CASCADE);
114 CREATE SEQUENCE PollChoiceSeq
START 1;
117 usr
INTEGER NOT NULL,
118 cho
INTEGER NOT NULL,
119 PRIMARY KEY (usr
, cho
),
120 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
121 FOREIGN KEY (cho
) REFERENCES PollChoice(id) ON DELETE CASCADE);
123 CREATE TABLE ContactKind(
124 id INTEGER PRIMARY KEY,
126 url
BOOLEAN NOT NULL,
130 CREATE SEQUENCE ContactKindSeq
START 1;
132 CREATE TABLE Contact(
133 id INTEGER PRIMARY KEY,
134 usr
INTEGER NOT NULL,
135 knd
INTEGER NOT NULL,
137 priv
INTEGER NOT NULL,
138 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
139 FOREIGN KEY (knd
) REFERENCES ContactKind(id) ON DELETE CASCADE);
141 CREATE SEQUENCE ContactSeq
START 1;
143 CREATE TABLE Location(
144 id INTEGER PRIMARY KEY,
147 FOREIGN KEY (parent
) REFERENCES Location(id) ON DELETE CASCADE);
149 CREATE SEQUENCE LocationSeq
START 1;
152 usr
INTEGER NOT NULL,
153 loc
INTEGER NOT NULL,
154 PRIMARY KEY (usr
, loc
),
155 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
156 FOREIGN KEY (loc
) REFERENCES Location(id) ON DELETE CASCADE);
159 id INTEGER PRIMARY KEY,
160 usr
INTEGER NOT NULL,
164 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
166 CREATE SEQUENCE LinkSeq
START 1;
168 CREATE TABLE SupCategory(
169 id INTEGER PRIMARY KEY,
170 grp
INTEGER NOT NULL,
173 FOREIGN KEY (grp
) REFERENCES WebGroup(id) ON DELETE CASCADE);
175 CREATE SEQUENCE SupCategorySeq
START 1;
177 CREATE TABLE SupIssue(
178 id INTEGER PRIMARY KEY,
179 usr
INTEGER NOT NULL,
180 cat
INTEGER NOT NULL,
182 priv
BOOLEAN NOT NULL,
183 status
INTEGER NOT NULL,
184 stamp
TIMESTAMP NOT NULL,
187 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
188 FOREIGN KEY (cat
) REFERENCES SupCategory(id) ON DELETE CASCADE);
190 CREATE SEQUENCE SupIssueSeq
START 1;
192 CREATE TABLE SupPost(
193 id INTEGER PRIMARY KEY,
194 usr
INTEGER NOT NULL,
195 iss
INTEGER NOT NULL,
197 stamp
TIMESTAMP NOT NULL,
198 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
199 FOREIGN KEY (iss
) REFERENCES SupIssue(id) ON DELETE CASCADE);
201 CREATE SEQUENCE SupPostSeq
START 1;
203 CREATE TABLE SupSubscription(
204 usr
INTEGER NOT NULL,
205 cat
INTEGER NOT NULL,
206 PRIMARY KEY (usr
, cat
),
207 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
208 FOREIGN KEY (cat
) REFERENCES SupCategory(id) ON DELETE CASCADE);
210 CREATE TABLE WebNode(
211 id INTEGER PRIMARY KEY,
214 debian
TEXT NOT NULL);
216 INSERT INTO WebNode (id, name, descr
, debian
)
217 VALUES (0, 'fyodor', 'old server', 'testing');
218 INSERT INTO WebNode (id, name, descr
, debian
)
219 VALUES (1, 'deleuze', 'main server', 'stable');
220 INSERT INTO WebNode (id, name, descr
, debian
)
221 VALUES (2, 'mire', 'member web server', 'stable');
224 id INTEGER PRIMARY KEY,
225 usr
INTEGER NOT NULL,
226 node
INTEGER NOT NULL,
229 status
INTEGER NOT NULL,
230 stamp
TIMESTAMP NOT NULL,
232 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
233 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
235 CREATE SEQUENCE AptSeq
START 1;
238 id INTEGER PRIMARY KEY,
239 usr
INTEGER NOT NULL,
242 status
INTEGER NOT NULL,
243 stamp
TIMESTAMP NOT NULL,
245 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
247 CREATE SEQUENCE DomainSeq
START 1;
249 CREATE TABLE MailingList(
250 id INTEGER PRIMARY KEY,
251 usr
INTEGER NOT NULL,
254 status
INTEGER NOT NULL,
255 stamp
TIMESTAMP NOT NULL,
257 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
259 CREATE SEQUENCE MailingListSeq
START 1;
261 CREATE TABLE DirectoryPref(
262 usr
INTEGER PRIMARY KEY,
263 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
265 CREATE TABLE AppVote(
266 app
INTEGER NOT NULL,
267 usr
INTEGER NOT NULL,
268 PRIMARY KEY (app
, usr
),
269 FOREIGN KEY (app
) REFERENCES MemberApp(id) ON DELETE CASCADE,
270 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
272 CREATE VIEW WebUserPaying
273 AS SELECT id, name, rname
, bal
, joined
, app
, shares
275 JOIN (SELECT usr
FROM Membership
JOIN WebGroup
277 AND WebGroup.
name = 'paying') AS bob
280 CREATE VIEW WebUserActive
281 AS SELECT id, name, rname
, bal
, joined
, app
, shares
283 LEFT OUTER JOIN (SELECT usr
FROM Membership
JOIN WebGroup
284 ON grp
= WebGroup.
id AND (WebGroup.
name IN ('retired', 'phantom'))) AS mem
288 CREATE TABLE RollCall(
289 id INTEGER PRIMARY KEY,
292 started
TIMESTAMP NOT NULL);
294 CREATE SEQUENCE RollCallSeq
START 1;
296 CREATE TABLE RollCallEntry(
297 rol
INTEGER NOT NULL,
298 usr
INTEGER NOT NULL,
301 PRIMARY KEY (rol
, usr
),
302 FOREIGN KEY (rol
) REFERENCES RollCall(id) ON DELETE CASCADE,
303 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
306 id INTEGER PRIMARY KEY,
307 usr
INTEGER NOT NULL,
308 node
INTEGER NOT NULL,
311 status
INTEGER NOT NULL,
312 stamp
TIMESTAMP NOT NULL,
314 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
315 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
317 CREATE SEQUENCE SecSeq
START 1;
320 id INTEGER PRIMARY KEY,
321 usr
INTEGER NOT NULL,
322 node
INTEGER NOT NULL,
325 status
INTEGER NOT NULL,
326 stamp
TIMESTAMP NOT NULL,
328 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
329 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
331 CREATE SEQUENCE IpSeq
START 1;
334 id INTEGER PRIMARY KEY,
335 usr
INTEGER NOT NULL,
338 status
INTEGER NOT NULL,
339 stamp
TIMESTAMP NOT NULL,
341 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
342 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
344 CREATE SEQUENCE CertSeq
START 1;