1 CREATE TABLE MemberApp(
2 id INTEGER PRIMARY KEY,
7 forward BOOLEAN NOT NULL,
11 status
INTEGER NOT NULL,
12 applied
TIMESTAMP NOT NULL,
18 CREATE SEQUENCE MemberAppSeq
START 1;
21 id INTEGER PRIMARY KEY,
23 amount
REAL NOT NULL);
25 CREATE SEQUENCE BalanceSeq
START 1;
28 (id, name, amount
) VALUES
32 id INTEGER PRIMARY KEY,
36 joined
TIMESTAMP NOT NULL,
38 shares
INTEGER NOT NULL,
39 FOREIGN KEY (bal
) REFERENCES Balance(id) ON DELETE CASCADE,
40 FOREIGN KEY (app
) REFERENCES MemberApp(id) ON DELETE CASCADE);
42 CREATE SEQUENCE WebUserSeq
START 1;
45 (id, name, rname
, bal
, joined
) VALUES
46 (0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);
48 CREATE TABLE WebGroup(
49 id INTEGER PRIMARY KEY,
52 CREATE SEQUENCE WebGroupSeq
START 2;
62 CREATE TABLE Membership(
65 PRIMARY KEY (grp
, usr
),
66 FOREIGN KEY (grp
) REFERENCES WebGroup(id) ON DELETE CASCADE,
67 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
69 INSERT INTO Membership
73 INSERT INTO Membership
77 CREATE TABLE Transaction(
78 id INTEGER PRIMARY KEY,
82 stamp
TIMESTAMP NOT NULL);
84 CREATE SEQUENCE TransactionSeq
START 1;
90 PRIMARY KEY (trn
, usr
),
91 FOREIGN KEY (trn
) REFERENCES Transaction(id) ON DELETE CASCADE,
92 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
95 id INTEGER PRIMARY KEY,
101 votes
INTEGER NOT NULL,
102 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
104 CREATE SEQUENCE PollSeq
START 1;
106 CREATE TABLE PollChoice(
107 id INTEGER PRIMARY KEY,
108 pol
INTEGER NOT NULL,
111 FOREIGN KEY (pol
) REFERENCES Poll(id) ON DELETE CASCADE);
113 CREATE SEQUENCE PollChoiceSeq
START 1;
116 usr
INTEGER NOT NULL,
117 cho
INTEGER NOT NULL,
118 PRIMARY KEY (usr
, cho
),
119 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
120 FOREIGN KEY (cho
) REFERENCES PollChoice(id) ON DELETE CASCADE);
122 CREATE TABLE ContactKind(
123 id INTEGER PRIMARY KEY,
125 url
BOOLEAN NOT NULL,
129 CREATE SEQUENCE ContactKindSeq
START 1;
131 CREATE TABLE Contact(
132 id INTEGER PRIMARY KEY,
133 usr
INTEGER NOT NULL,
134 knd
INTEGER NOT NULL,
136 priv
INTEGER NOT NULL,
137 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
138 FOREIGN KEY (knd
) REFERENCES ContactKind(id) ON DELETE CASCADE);
140 CREATE SEQUENCE ContactSeq
START 1;
142 CREATE TABLE Location(
143 id INTEGER PRIMARY KEY,
146 FOREIGN KEY (parent
) REFERENCES Location(id) ON DELETE CASCADE);
148 CREATE SEQUENCE LocationSeq
START 1;
151 usr
INTEGER NOT NULL,
152 loc
INTEGER NOT NULL,
153 PRIMARY KEY (usr
, loc
),
154 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
155 FOREIGN KEY (loc
) REFERENCES Location(id) ON DELETE CASCADE);
158 id INTEGER PRIMARY KEY,
159 usr
INTEGER NOT NULL,
163 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
165 CREATE SEQUENCE LinkSeq
START 1;
167 CREATE TABLE SupCategory(
168 id INTEGER PRIMARY KEY,
169 grp
INTEGER NOT NULL,
172 FOREIGN KEY (grp
) REFERENCES WebGroup(id) ON DELETE CASCADE);
174 CREATE SEQUENCE SupCategorySeq
START 1;
176 CREATE TABLE SupIssue(
177 id INTEGER PRIMARY KEY,
178 usr
INTEGER NOT NULL,
179 cat
INTEGER NOT NULL,
181 priv
BOOLEAN NOT NULL,
182 status
INTEGER NOT NULL,
183 stamp
TIMESTAMP NOT NULL,
186 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
187 FOREIGN KEY (cat
) REFERENCES SupCategory(id) ON DELETE CASCADE);
189 CREATE SEQUENCE SupIssueSeq
START 1;
191 CREATE TABLE SupPost(
192 id INTEGER PRIMARY KEY,
193 usr
INTEGER NOT NULL,
194 iss
INTEGER NOT NULL,
196 stamp
TIMESTAMP NOT NULL,
197 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
198 FOREIGN KEY (iss
) REFERENCES SupIssue(id) ON DELETE CASCADE);
200 CREATE SEQUENCE SupPostSeq
START 1;
202 CREATE TABLE SupSubscription(
203 usr
INTEGER NOT NULL,
204 cat
INTEGER NOT NULL,
205 PRIMARY KEY (usr
, cat
),
206 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
207 FOREIGN KEY (cat
) REFERENCES SupCategory(id) ON DELETE CASCADE);
209 CREATE TABLE WebNode(
210 id INTEGER PRIMARY KEY,
213 debian
TEXT NOT NULL);
215 INSERT INTO WebNode (id, name, descr
, debian
)
216 VALUES (0, 'fyodor', 'old server', 'testing');
217 INSERT INTO WebNode (id, name, descr
, debian
)
218 VALUES (1, 'deleuze', 'main server', 'stable');
219 INSERT INTO WebNode (id, name, descr
, debian
)
220 VALUES (2, 'mire', 'member web server', 'stable');
223 id INTEGER PRIMARY KEY,
224 usr
INTEGER NOT NULL,
225 node
INTEGER NOT NULL,
228 status
INTEGER NOT NULL,
229 stamp
TIMESTAMP NOT NULL,
230 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
231 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
233 CREATE SEQUENCE AptSeq
START 1;
236 id INTEGER PRIMARY KEY,
237 usr
INTEGER NOT NULL,
240 status
INTEGER NOT NULL,
241 stamp
TIMESTAMP NOT NULL,
242 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
244 CREATE SEQUENCE DomainSeq
START 1;
246 CREATE TABLE MailingList(
247 id INTEGER PRIMARY KEY,
248 usr
INTEGER NOT NULL,
251 status
INTEGER NOT NULL,
252 stamp
TIMESTAMP NOT NULL,
253 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
255 CREATE SEQUENCE MailingListSeq
START 1;
257 CREATE TABLE DirectoryPref(
258 usr
INTEGER PRIMARY KEY,
259 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
261 CREATE TABLE AppVote(
262 app
INTEGER NOT NULL,
263 usr
INTEGER NOT NULL,
264 PRIMARY KEY (app
, usr
),
265 FOREIGN KEY (app
) REFERENCES MemberApp(id) ON DELETE CASCADE,
266 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
268 CREATE VIEW WebUserPaying
269 AS SELECT id, name, rname
, bal
, joined
, app
, shares
271 JOIN (SELECT usr
FROM Membership
JOIN WebGroup
273 AND WebGroup.
name = 'paying') AS bob
276 CREATE VIEW WebUserActive
277 AS SELECT id, name, rname
, bal
, joined
, app
, shares
279 LEFT OUTER JOIN (SELECT usr
FROM Membership
JOIN WebGroup
280 ON grp
= WebGroup.
id AND (WebGroup.
name IN ('retired', 'phantom'))) AS mem
284 CREATE TABLE RollCall(
285 id INTEGER PRIMARY KEY,
288 started
TIMESTAMP NOT NULL);
290 CREATE SEQUENCE RollCallSeq
START 1;
292 CREATE TABLE RollCallEntry(
293 rol
INTEGER NOT NULL,
294 usr
INTEGER NOT NULL,
297 PRIMARY KEY (rol
, usr
),
298 FOREIGN KEY (rol
) REFERENCES RollCall(id) ON DELETE CASCADE,
299 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
302 id INTEGER PRIMARY KEY,
303 usr
INTEGER NOT NULL,
304 node
INTEGER NOT NULL,
307 status
INTEGER NOT NULL,
308 stamp
TIMESTAMP NOT NULL,
309 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
310 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
312 CREATE SEQUENCE SecSeq
START 1;