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);
208 id INTEGER PRIMARY KEY,
209 usr
INTEGER NOT NULL,
212 status
INTEGER NOT NULL,
213 stamp
TIMESTAMP NOT NULL,
214 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
216 CREATE SEQUENCE AptSeq
START 1;
219 id INTEGER PRIMARY KEY,
220 usr
INTEGER NOT NULL,
223 status
INTEGER NOT NULL,
224 stamp
TIMESTAMP NOT NULL,
225 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
227 CREATE SEQUENCE DomainSeq
START 1;
229 CREATE TABLE MailingList(
230 id INTEGER PRIMARY KEY,
231 usr
INTEGER NOT NULL,
234 status
INTEGER NOT NULL,
235 stamp
TIMESTAMP NOT NULL,
236 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
238 CREATE SEQUENCE MailingListSeq
START 1;
240 CREATE TABLE DirectoryPref(
241 usr
INTEGER PRIMARY KEY,
242 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
244 CREATE TABLE AppVote(
245 app
INTEGER NOT NULL,
246 usr
INTEGER NOT NULL,
247 PRIMARY KEY (app
, usr
),
248 FOREIGN KEY (app
) REFERENCES MemberApp(id) ON DELETE CASCADE,
249 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
251 CREATE VIEW WebUserPaying
252 AS SELECT id, name, rname
, bal
, joined
, app
, shares
254 JOIN (SELECT usr
FROM Membership
JOIN WebGroup
256 AND WebGroup.
name = 'paying') AS bob
259 CREATE VIEW WebUserActive
260 AS SELECT id, name, rname
, bal
, joined
, app
, shares
262 LEFT OUTER JOIN (SELECT usr
FROM Membership
JOIN WebGroup
263 ON grp
= WebGroup.
id AND (WebGroup.
name IN ('retired', 'phantom'))) AS mem
267 CREATE TABLE RollCall(
268 id INTEGER PRIMARY KEY,
271 started
TIMESTAMP NOT NULL);
273 CREATE SEQUENCE RollCallSeq
START 1;
275 CREATE TABLE RollCallEntry(
276 rol
INTEGER NOT NULL,
277 usr
INTEGER NOT NULL,
280 PRIMARY KEY (rol
, usr
),
281 FOREIGN KEY (rol
) REFERENCES RollCall(id) ON DELETE CASCADE,
282 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
285 id INTEGER PRIMARY KEY,
286 usr
INTEGER NOT NULL,
289 status
INTEGER NOT NULL,
290 stamp
TIMESTAMP NOT NULL,
291 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
293 CREATE SEQUENCE SecSeq
START 1;