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,
184 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
185 FOREIGN KEY (cat
) REFERENCES SupCategory(id) ON DELETE CASCADE);
187 CREATE SEQUENCE SupIssueSeq
START 1;
189 CREATE TABLE SupPost(
190 id INTEGER PRIMARY KEY,
191 usr
INTEGER NOT NULL,
192 iss
INTEGER NOT NULL,
194 stamp
TIMESTAMP NOT NULL,
195 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
196 FOREIGN KEY (iss
) REFERENCES SupIssue(id) ON DELETE CASCADE);
198 CREATE SEQUENCE SupPostSeq
START 1;
200 CREATE TABLE SupSubscription(
201 usr
INTEGER NOT NULL,
202 cat
INTEGER NOT NULL,
203 PRIMARY KEY (usr
, cat
),
204 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
205 FOREIGN KEY (cat
) REFERENCES SupCategory(id) ON DELETE CASCADE);
207 CREATE TABLE WebNode(
208 id INTEGER PRIMARY KEY,
211 debian
TEXT NOT NULL);
213 INSERT INTO WebNode (id, name, descr
, debian
)
214 VALUES (0, 'fyodor', 'old server', 'testing');
215 INSERT INTO WebNode (id, name, descr
, debian
)
216 VALUES (1, 'deleuze', 'main server', 'stable');
217 INSERT INTO WebNode (id, name, descr
, debian
)
218 VALUES (2, 'mire', 'member web server', 'stable');
221 id INTEGER PRIMARY KEY,
222 usr
INTEGER NOT NULL,
223 node
INTEGER NOT NULL,
226 status
INTEGER NOT NULL,
227 stamp
TIMESTAMP NOT NULL,
228 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
230 CREATE SEQUENCE AptSeq
START 1;
233 id INTEGER PRIMARY KEY,
234 usr
INTEGER NOT NULL,
237 status
INTEGER NOT NULL,
238 stamp
TIMESTAMP NOT NULL,
239 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
241 CREATE SEQUENCE DomainSeq
START 1;
243 CREATE TABLE MailingList(
244 id INTEGER PRIMARY KEY,
245 usr
INTEGER NOT NULL,
248 status
INTEGER NOT NULL,
249 stamp
TIMESTAMP NOT NULL,
250 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
252 CREATE SEQUENCE MailingListSeq
START 1;
254 CREATE TABLE DirectoryPref(
255 usr
INTEGER PRIMARY KEY,
256 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
258 CREATE TABLE AppVote(
259 app
INTEGER NOT NULL,
260 usr
INTEGER NOT NULL,
261 PRIMARY KEY (app
, usr
),
262 FOREIGN KEY (app
) REFERENCES MemberApp(id) ON DELETE CASCADE,
263 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
265 CREATE VIEW WebUserPaying
266 AS SELECT id, name, rname
, bal
, joined
, app
, shares
268 JOIN (SELECT usr
FROM Membership
JOIN WebGroup
270 AND WebGroup.
name = 'paying') AS bob
273 CREATE VIEW WebUserActive
274 AS SELECT id, name, rname
, bal
, joined
, app
, shares
276 LEFT OUTER JOIN (SELECT usr
FROM Membership
JOIN WebGroup
277 ON grp
= WebGroup.
id AND (WebGroup.
name IN ('retired', 'phantom'))) AS mem
281 CREATE TABLE RollCall(
282 id INTEGER PRIMARY KEY,
285 started
TIMESTAMP NOT NULL);
287 CREATE SEQUENCE RollCallSeq
START 1;
289 CREATE TABLE RollCallEntry(
290 rol
INTEGER NOT NULL,
291 usr
INTEGER NOT NULL,
294 PRIMARY KEY (rol
, usr
),
295 FOREIGN KEY (rol
) REFERENCES RollCall(id) ON DELETE CASCADE,
296 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
299 id INTEGER PRIMARY KEY,
300 usr
INTEGER NOT NULL,
301 node
INTEGER NOT NULL,
304 status
INTEGER NOT NULL,
305 stamp
TIMESTAMP NOT NULL,
306 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
308 CREATE SEQUENCE SecSeq
START 1;