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,
229 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
231 CREATE SEQUENCE AptSeq
START 1;
234 id INTEGER PRIMARY KEY,
235 usr
INTEGER NOT NULL,
238 status
INTEGER NOT NULL,
239 stamp
TIMESTAMP NOT NULL,
240 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
242 CREATE SEQUENCE DomainSeq
START 1;
244 CREATE TABLE MailingList(
245 id INTEGER PRIMARY KEY,
246 usr
INTEGER NOT NULL,
249 status
INTEGER NOT NULL,
250 stamp
TIMESTAMP NOT NULL,
251 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
253 CREATE SEQUENCE MailingListSeq
START 1;
255 CREATE TABLE DirectoryPref(
256 usr
INTEGER PRIMARY KEY,
257 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
259 CREATE TABLE AppVote(
260 app
INTEGER NOT NULL,
261 usr
INTEGER NOT NULL,
262 PRIMARY KEY (app
, usr
),
263 FOREIGN KEY (app
) REFERENCES MemberApp(id) ON DELETE CASCADE,
264 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
266 CREATE VIEW WebUserPaying
267 AS SELECT id, name, rname
, bal
, joined
, app
, shares
269 JOIN (SELECT usr
FROM Membership
JOIN WebGroup
271 AND WebGroup.
name = 'paying') AS bob
274 CREATE VIEW WebUserActive
275 AS SELECT id, name, rname
, bal
, joined
, app
, shares
277 LEFT OUTER JOIN (SELECT usr
FROM Membership
JOIN WebGroup
278 ON grp
= WebGroup.
id AND (WebGroup.
name IN ('retired', 'phantom'))) AS mem
282 CREATE TABLE RollCall(
283 id INTEGER PRIMARY KEY,
286 started
TIMESTAMP NOT NULL);
288 CREATE SEQUENCE RollCallSeq
START 1;
290 CREATE TABLE RollCallEntry(
291 rol
INTEGER NOT NULL,
292 usr
INTEGER NOT NULL,
295 PRIMARY KEY (rol
, usr
),
296 FOREIGN KEY (rol
) REFERENCES RollCall(id) ON DELETE CASCADE,
297 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
300 id INTEGER PRIMARY KEY,
301 usr
INTEGER NOT NULL,
302 node
INTEGER NOT NULL,
305 status
INTEGER NOT NULL,
306 stamp
TIMESTAMP NOT NULL,
307 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
308 FOREIGN KEY (node
) REFERENCES WebNode(id) ON DELETE CASCADE);
310 CREATE SEQUENCE SecSeq
START 1;