5146e435 |
1 | CREATE TABLE MemberApp( |
2 | id INTEGER PRIMARY KEY, |
3 | name TEXT NOT NULL, |
4 | rname TEXT NOT NULL, |
5 | gname TEXT, |
6 | email TEXT NOT NULL, |
7 | forward BOOLEAN NOT NULL, |
8 | uses TEXT NOT NULL, |
9 | other TEXT NOT NULL, |
10 | passwd TEXT NOT NULL, |
11 | status INTEGER NOT NULL, |
12 | applied TIMESTAMP NOT NULL, |
13 | ipaddr TEXT, |
14 | confirmed TIMESTAMP, |
15 | decided TIMESTAMP, |
16 | msg TEXT NOT NULL); |
17 | |
18 | CREATE SEQUENCE MemberAppSeq START 1; |
19 | |
8d347a33 |
20 | CREATE TABLE Balance( |
21 | id INTEGER PRIMARY KEY, |
22 | name TEXT NOT NULL, |
23 | amount REAL NOT NULL); |
24 | |
25 | CREATE SEQUENCE BalanceSeq START 1; |
26 | |
27 | INSERT INTO Balance |
28 | (id, name, amount) VALUES |
29 | (0, 'adam', 0.0); |
30 | |
31 | CREATE TABLE WebUser( |
32 | id INTEGER PRIMARY KEY, |
33 | name TEXT NOT NULL, |
34 | rname TEXT NOT NULL, |
35 | bal INTEGER NOT NULL, |
36 | joined TIMESTAMP NOT NULL, |
5146e435 |
37 | app INTEGER NOT NULL, |
892e3ea1 |
38 | shares INTEGER NOT NULL, |
5146e435 |
39 | FOREIGN KEY (bal) REFERENCES Balance(id) ON DELETE CASCADE, |
40 | FOREIGN KEY (app) REFERENCES MemberApp(id) ON DELETE CASCADE); |
8d347a33 |
41 | |
42 | CREATE SEQUENCE WebUserSeq START 1; |
43 | |
44 | INSERT INTO WebUser |
3d009713 |
45 | (id, name, rname, bal, joined) VALUES |
46 | (0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP); |
8d347a33 |
47 | |
48 | CREATE TABLE WebGroup( |
49 | id INTEGER PRIMARY KEY, |
50 | name TEXT NOT NULL); |
51 | |
3d009713 |
52 | CREATE SEQUENCE WebGroupSeq START 2; |
8d347a33 |
53 | |
54 | INSERT INTO WebGroup |
55 | (id, name) VALUES |
56 | (0, 'root'); |
57 | |
3d009713 |
58 | INSERT INTO WebGroup |
59 | (id, name) VALUES |
60 | (1, 'paying'); |
61 | |
8d347a33 |
62 | CREATE TABLE Membership( |
63 | grp INTEGER NOT NULL, |
64 | usr INTEGER NOT NULL, |
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); |
68 | |
69 | INSERT INTO Membership |
70 | (grp, usr) VALUES |
71 | (0, 0); |
72 | |
3d009713 |
73 | INSERT INTO Membership |
74 | (grp, usr) VALUES |
75 | (1, 0); |
76 | |
8d347a33 |
77 | CREATE TABLE Transaction( |
78 | id INTEGER PRIMARY KEY, |
79 | descr TEXT NOT NULL, |
80 | amount REAL NOT NULL, |
81 | d DATE NOT NULL, |
82 | stamp TIMESTAMP NOT NULL); |
83 | |
84 | CREATE SEQUENCE TransactionSeq START 1; |
85 | |
86 | CREATE TABLE Charge( |
87 | trn INTEGER NOT NULL, |
88 | usr INTEGER NOT NULL, |
89 | amount REAL NOT NULL, |
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); |
93 | |
57c305c1 |
94 | CREATE TABLE Poll( |
95 | id INTEGER PRIMARY KEY, |
96 | usr INTEGER NOT NULL, |
97 | title TEXT NOT NULL, |
98 | descr TEXT NOT NULL, |
99 | starts DATE NOT NULL, |
100 | ends DATE NOT NULL, |
101 | votes INTEGER NOT NULL, |
102 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
103 | |
104 | CREATE SEQUENCE PollSeq START 1; |
105 | |
106 | CREATE TABLE PollChoice( |
107 | id INTEGER PRIMARY KEY, |
108 | pol INTEGER NOT NULL, |
109 | seq REAL NOT NULL, |
110 | descr TEXT NOT NULL, |
111 | FOREIGN KEY (pol) REFERENCES Poll(id) ON DELETE CASCADE); |
112 | |
113 | CREATE SEQUENCE PollChoiceSeq START 1; |
114 | |
115 | CREATE TABLE Vote( |
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); |
121 | |
46b0c2df |
122 | CREATE TABLE ContactKind( |
123 | id INTEGER PRIMARY KEY, |
124 | name TEXT NOT NULL, |
125 | url BOOLEAN NOT NULL, |
126 | urlPrefix TEXT, |
127 | urlPostfix TEXT); |
57c305c1 |
128 | |
46b0c2df |
129 | CREATE SEQUENCE ContactKindSeq START 1; |
8d347a33 |
130 | |
46b0c2df |
131 | CREATE TABLE Contact( |
369e1577 |
132 | id INTEGER PRIMARY KEY, |
46b0c2df |
133 | usr INTEGER NOT NULL, |
134 | knd INTEGER NOT NULL, |
135 | v TEXT NOT NULL, |
136 | priv INTEGER NOT NULL, |
46b0c2df |
137 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE, |
138 | FOREIGN KEY (knd) REFERENCES ContactKind(id) ON DELETE CASCADE); |
369e1577 |
139 | |
140 | CREATE SEQUENCE ContactSeq START 1; |
51520441 |
141 | |
142 | CREATE TABLE Location( |
143 | id INTEGER PRIMARY KEY, |
144 | parent INTEGER, |
145 | name TEXT NOT NULL, |
146 | FOREIGN KEY (parent) REFERENCES Location(id) ON DELETE CASCADE); |
147 | |
148 | CREATE SEQUENCE LocationSeq START 1; |
149 | |
150 | CREATE TABLE Lives( |
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); |
29c3cc58 |
156 | |
157 | CREATE TABLE Link( |
158 | id INTEGER PRIMARY KEY, |
159 | usr INTEGER NOT NULL, |
160 | title TEXT NOT NULL, |
161 | url TEXT NOT NULL, |
162 | descr TEXT NOT NULL, |
163 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
164 | |
165 | CREATE SEQUENCE LinkSeq START 1; |
166 | |
c7311141 |
167 | CREATE TABLE SupCategory( |
168 | id INTEGER PRIMARY KEY, |
169 | grp INTEGER NOT NULL, |
170 | name TEXT NOT NULL, |
171 | descr TEXT NOT NULL, |
172 | FOREIGN KEY (grp) REFERENCES WebGroup(id) ON DELETE CASCADE); |
173 | |
174 | CREATE SEQUENCE SupCategorySeq START 1; |
175 | |
176 | CREATE TABLE SupIssue( |
177 | id INTEGER PRIMARY KEY, |
178 | usr INTEGER NOT NULL, |
179 | cat INTEGER NOT NULL, |
180 | title TEXT 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); |
186 | |
187 | CREATE SEQUENCE SupIssueSeq START 1; |
188 | |
189 | CREATE TABLE SupPost( |
190 | id INTEGER PRIMARY KEY, |
191 | usr INTEGER NOT NULL, |
192 | iss INTEGER NOT NULL, |
193 | body TEXT 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); |
197 | |
198 | CREATE SEQUENCE SupPostSeq START 1; |
199 | |
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); |
206 | |
36d5f176 |
207 | CREATE TABLE Apt( |
208 | id INTEGER PRIMARY KEY, |
209 | usr INTEGER NOT NULL, |
ff2b7604 |
210 | data TEXT NOT NULL, |
36d5f176 |
211 | msg TEXT NOT NULL, |
212 | status INTEGER NOT NULL, |
213 | stamp TIMESTAMP NOT NULL, |
214 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
c7311141 |
215 | |
36d5f176 |
216 | CREATE SEQUENCE AptSeq START 1; |
ff2b7604 |
217 | |
218 | CREATE TABLE Domain( |
219 | id INTEGER PRIMARY KEY, |
220 | usr INTEGER NOT NULL, |
221 | data TEXT NOT NULL, |
222 | msg TEXT NOT NULL, |
223 | status INTEGER NOT NULL, |
224 | stamp TIMESTAMP NOT NULL, |
225 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
226 | |
227 | CREATE SEQUENCE DomainSeq START 1; |
78304862 |
228 | |
229 | CREATE TABLE MailingList( |
230 | id INTEGER PRIMARY KEY, |
231 | usr INTEGER NOT NULL, |
232 | data TEXT NOT NULL, |
233 | msg TEXT NOT NULL, |
234 | status INTEGER NOT NULL, |
235 | stamp TIMESTAMP NOT NULL, |
236 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
237 | |
238 | CREATE SEQUENCE MailingListSeq START 1; |
566fd712 |
239 | |
240 | CREATE TABLE DirectoryPref( |
241 | usr INTEGER PRIMARY KEY, |
242 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
20a679fc |
243 | |
20a679fc |
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); |
250 | |
ea455810 |
251 | CREATE VIEW WebUserPaying |
892e3ea1 |
252 | AS SELECT id, name, rname, bal, joined, app, shares |
ea455810 |
253 | FROM WebUser |
254 | JOIN (SELECT usr FROM Membership JOIN WebGroup |
255 | ON grp = WebGroup.id |
892e3ea1 |
256 | AND WebGroup.name = 'paying') AS bob |
ea455810 |
257 | ON usr = WebUser.id; |
258 | |
47bc9315 |
259 | CREATE VIEW WebUserActive |
892e3ea1 |
260 | AS SELECT id, name, rname, bal, joined, app, shares |
47bc9315 |
261 | FROM WebUser |
262 | LEFT OUTER JOIN (SELECT usr FROM Membership JOIN WebGroup |
add44c00 |
263 | ON grp = WebGroup.id AND (WebGroup.name IN ('retired', 'phantom'))) AS mem |
47bc9315 |
264 | ON usr = WebUser.id |
47bc9315 |
265 | WHERE usr IS NULL; |
266 | |
add44c00 |
267 | CREATE TABLE RollCall( |
268 | id INTEGER PRIMARY KEY, |
269 | title TEXT NOT NULL, |
270 | msg TEXT NOT NULL, |
271 | started TIMESTAMP NOT NULL); |
272 | |
273 | CREATE SEQUENCE RollCallSeq START 1; |
274 | |
275 | CREATE TABLE RollCallEntry( |
276 | rol INTEGER NOT NULL, |
277 | usr INTEGER NOT NULL, |
278 | code TEXT NOT NULL, |
279 | responded TIMESTAMP, |
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); |
3ad30cf6 |
283 | |
284 | CREATE TABLE Sec( |
285 | id INTEGER PRIMARY KEY, |
286 | usr INTEGER NOT NULL, |
287 | data TEXT NOT NULL, |
288 | msg TEXT NOT NULL, |
289 | status INTEGER NOT NULL, |
290 | stamp TIMESTAMP NOT NULL, |
291 | FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE); |
292 | |
293 | CREATE SEQUENCE SecSeq START 1; |