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,
231 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
232 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
234 CREATE SEQUENCE AptSeq
START 1;
237 id INTEGER PRIMARY KEY,
238 usr
INTEGER NOT NULL,
241 status
INTEGER NOT NULL,
242 stamp
TIMESTAMP NOT NULL,
244 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
246 CREATE SEQUENCE DomainSeq
START 1;
248 CREATE TABLE MailingList(
249 id INTEGER PRIMARY KEY,
250 usr
INTEGER NOT NULL,
253 status
INTEGER NOT NULL,
254 stamp
TIMESTAMP NOT NULL,
256 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
258 CREATE SEQUENCE MailingListSeq
START 1;
260 CREATE TABLE DirectoryPref(
261 usr
INTEGER PRIMARY KEY,
262 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
264 CREATE TABLE AppVote(
265 app
INTEGER NOT NULL,
266 usr
INTEGER NOT NULL,
267 PRIMARY KEY (app
, usr
),
268 FOREIGN KEY (app
) REFERENCES MemberApp(id) ON DELETE CASCADE,
269 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
271 CREATE VIEW WebUserPaying
272 AS SELECT id, name, rname
, bal
, joined
, app
, shares
274 JOIN (SELECT usr
FROM Membership
JOIN WebGroup
276 AND WebGroup.
name = 'paying') AS bob
279 CREATE VIEW WebUserActive
280 AS SELECT id, name, rname
, bal
, joined
, app
, shares
282 LEFT OUTER JOIN (SELECT usr
FROM Membership
JOIN WebGroup
283 ON grp
= WebGroup.
id AND (WebGroup.
name IN ('retired', 'phantom'))) AS mem
287 CREATE TABLE RollCall(
288 id INTEGER PRIMARY KEY,
291 started
TIMESTAMP NOT NULL);
293 CREATE SEQUENCE RollCallSeq
START 1;
295 CREATE TABLE RollCallEntry(
296 rol
INTEGER NOT NULL,
297 usr
INTEGER NOT NULL,
300 PRIMARY KEY (rol
, usr
),
301 FOREIGN KEY (rol
) REFERENCES RollCall(id) ON DELETE CASCADE,
302 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
305 id INTEGER PRIMARY KEY,
306 usr
INTEGER NOT NULL,
307 node
INTEGER NOT NULL,
310 status
INTEGER NOT NULL,
311 stamp
TIMESTAMP NOT NULL,
313 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
314 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
316 CREATE SEQUENCE SecSeq
START 1;