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 FOREIGN KEY (bal
) REFERENCES Balance(id) ON DELETE CASCADE,
39 FOREIGN KEY (app
) REFERENCES MemberApp(id) ON DELETE CASCADE);
41 CREATE SEQUENCE WebUserSeq
START 1;
44 (id, name, rname
, bal
, joined
) VALUES
45 (0, 'adam', 'Adam Chlipala', 0, CURRENT_TIMESTAMP);
47 CREATE TABLE WebGroup(
48 id INTEGER PRIMARY KEY,
51 CREATE SEQUENCE WebGroupSeq
START 2;
61 CREATE TABLE Membership(
64 PRIMARY KEY (grp
, usr
),
65 FOREIGN KEY (grp
) REFERENCES WebGroup(id) ON DELETE CASCADE,
66 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
68 INSERT INTO Membership
72 INSERT INTO Membership
76 CREATE TABLE Transaction(
77 id INTEGER PRIMARY KEY,
81 stamp
TIMESTAMP NOT NULL);
83 CREATE SEQUENCE TransactionSeq
START 1;
89 PRIMARY KEY (trn
, usr
),
90 FOREIGN KEY (trn
) REFERENCES Transaction(id) ON DELETE CASCADE,
91 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
94 id INTEGER PRIMARY KEY,
100 votes
INTEGER NOT NULL,
101 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
103 CREATE SEQUENCE PollSeq
START 1;
105 CREATE TABLE PollChoice(
106 id INTEGER PRIMARY KEY,
107 pol
INTEGER NOT NULL,
110 FOREIGN KEY (pol
) REFERENCES Poll(id) ON DELETE CASCADE);
112 CREATE SEQUENCE PollChoiceSeq
START 1;
115 usr
INTEGER NOT NULL,
116 cho
INTEGER NOT NULL,
117 PRIMARY KEY (usr
, cho
),
118 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
119 FOREIGN KEY (cho
) REFERENCES PollChoice(id) ON DELETE CASCADE);
121 CREATE TABLE ContactKind(
122 id INTEGER PRIMARY KEY,
124 url
BOOLEAN NOT NULL,
128 CREATE SEQUENCE ContactKindSeq
START 1;
130 CREATE TABLE Contact(
131 id INTEGER PRIMARY KEY,
132 usr
INTEGER NOT NULL,
133 knd
INTEGER NOT NULL,
135 priv
INTEGER NOT NULL,
136 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
137 FOREIGN KEY (knd
) REFERENCES ContactKind(id) ON DELETE CASCADE);
139 CREATE SEQUENCE ContactSeq
START 1;
141 CREATE TABLE Location(
142 id INTEGER PRIMARY KEY,
145 FOREIGN KEY (parent
) REFERENCES Location(id) ON DELETE CASCADE);
147 CREATE SEQUENCE LocationSeq
START 1;
150 usr
INTEGER NOT NULL,
151 loc
INTEGER NOT NULL,
152 PRIMARY KEY (usr
, loc
),
153 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
154 FOREIGN KEY (loc
) REFERENCES Location(id) ON DELETE CASCADE);
157 id INTEGER PRIMARY KEY,
158 usr
INTEGER NOT NULL,
162 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
164 CREATE SEQUENCE LinkSeq
START 1;
166 CREATE TABLE SupCategory(
167 id INTEGER PRIMARY KEY,
168 grp
INTEGER NOT NULL,
171 FOREIGN KEY (grp
) REFERENCES WebGroup(id) ON DELETE CASCADE);
173 CREATE SEQUENCE SupCategorySeq
START 1;
175 CREATE TABLE SupIssue(
176 id INTEGER PRIMARY KEY,
177 usr
INTEGER NOT NULL,
178 cat
INTEGER NOT NULL,
180 priv
BOOLEAN NOT NULL,
181 status
INTEGER NOT NULL,
182 stamp
TIMESTAMP NOT NULL,
183 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
184 FOREIGN KEY (cat
) REFERENCES SupCategory(id) ON DELETE CASCADE);
186 CREATE SEQUENCE SupIssueSeq
START 1;
188 CREATE TABLE SupPost(
189 id INTEGER PRIMARY KEY,
190 usr
INTEGER NOT NULL,
191 iss
INTEGER NOT NULL,
193 stamp
TIMESTAMP NOT NULL,
194 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
195 FOREIGN KEY (iss
) REFERENCES SupIssue(id) ON DELETE CASCADE);
197 CREATE SEQUENCE SupPostSeq
START 1;
199 CREATE TABLE SupSubscription(
200 usr
INTEGER NOT NULL,
201 cat
INTEGER NOT NULL,
202 PRIMARY KEY (usr
, cat
),
203 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE,
204 FOREIGN KEY (cat
) REFERENCES SupCategory(id) ON DELETE CASCADE);
207 id INTEGER PRIMARY KEY,
208 usr
INTEGER NOT NULL,
211 status
INTEGER NOT NULL,
212 stamp
TIMESTAMP NOT NULL,
213 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
215 CREATE SEQUENCE AptSeq
START 1;
218 id INTEGER PRIMARY KEY,
219 usr
INTEGER NOT NULL,
222 status
INTEGER NOT NULL,
223 stamp
TIMESTAMP NOT NULL,
224 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
226 CREATE SEQUENCE DomainSeq
START 1;
228 CREATE TABLE MailingList(
229 id INTEGER PRIMARY KEY,
230 usr
INTEGER NOT NULL,
233 status
INTEGER NOT NULL,
234 stamp
TIMESTAMP NOT NULL,
235 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
237 CREATE SEQUENCE MailingListSeq
START 1;
239 CREATE TABLE DirectoryPref(
240 usr
INTEGER PRIMARY KEY,
241 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);
243 CREATE TABLE AppVote(
244 app
INTEGER NOT NULL,
245 usr
INTEGER NOT NULL,
246 PRIMARY KEY (app
, usr
),
247 FOREIGN KEY (app
) REFERENCES MemberApp(id) ON DELETE CASCADE,
248 FOREIGN KEY (usr
) REFERENCES WebUser(id) ON DELETE CASCADE);