« back to all changes in this revision
Viewing changes to src/mailman/database/schema/sqlite.sql
- browse files at revision 7251.1.8
- compare with another revision
- download diff
- download tarball
- view history from revision 7251.1.8
- Committer: Barry Warsaw
- Date: 2014-09-22 23:32:58 UTC
- mto: This revision was merged to the branch mainline in revision 7267.
- Revision ID: barry@list.org-20140922233258-fvg9e3j3equ2y915
Migrations will be replaced with Alchemy.
We don't need the raw SQL schema stuff any more.
We don't need the Version table any more.
We don't need the raw SQL schema stuff any more.
We don't need the Version table any more.
- files removed:
- src/mailman/database/docs
- src/mailman/database/schema
- src/mailman/database/tests
- src/mailman/database/tests/data
added
removed
src/mailman/database/schema/sqlite.sql
1
2
PRAGMA foreign_keys = ON;
3
4
CREATE TABLE _request (
5
id INTEGER NOT NULL,
6
"key" TEXT,
7
request_type INTEGER,
8
data_hash TEXT,
9
mailing_list_id INTEGER,
10
PRIMARY KEY (id),
11
CONSTRAINT _request_mailing_list_id_fk
12
FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id)
13
);
14
15
CREATE TABLE acceptablealias (
16
id INTEGER NOT NULL,
17
"alias" TEXT NOT NULL,
18
mailing_list_id INTEGER NOT NULL,
19
PRIMARY KEY (id),
20
CONSTRAINT acceptablealias_mailing_list_id_fk
21
FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id)
22
);
23
CREATE INDEX ix_acceptablealias_mailing_list_id
24
ON acceptablealias (mailing_list_id);
25
CREATE INDEX ix_acceptablealias_alias ON acceptablealias ("alias");
26
27
CREATE TABLE address (
28
id INTEGER NOT NULL,
29
email TEXT,
30
_original TEXT,
31
display_name TEXT,
32
verified_on TIMESTAMP,
33
registered_on TIMESTAMP,
34
user_id INTEGER,
35
preferences_id INTEGER,
36
PRIMARY KEY (id),
37
CONSTRAINT address_user_id_fk
38
FOREIGN KEY (user_id) REFERENCES user (id),
39
CONSTRAINT address_preferences_id_fk
40
FOREIGN KEY (preferences_id) REFERENCES preferences (id)
41
);
42
43
CREATE TABLE autoresponserecord (
44
id INTEGER NOT NULL,
45
address_id INTEGER,
46
mailing_list_id INTEGER,
47
response_type INTEGER,
48
date_sent TIMESTAMP,
49
PRIMARY KEY (id),
50
CONSTRAINT autoresponserecord_address_id_fk
51
FOREIGN KEY (address_id) REFERENCES address (id),
52
CONSTRAINT autoresponserecord_mailing_list_id
53
FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id)
54
);
55
CREATE INDEX ix_autoresponserecord_address_id
56
ON autoresponserecord (address_id);
57
CREATE INDEX ix_autoresponserecord_mailing_list_id
58
ON autoresponserecord (mailing_list_id);
59
60
CREATE TABLE bounceevent (
61
id INTEGER NOT NULL,
62
list_name TEXT,
63
email TEXT,
64
'timestamp' TIMESTAMP,
65
message_id TEXT,
66
context INTEGER,
67
processed BOOLEAN,
68
PRIMARY KEY (id)
69
);
70
71
CREATE TABLE contentfilter (
72
id INTEGER NOT NULL,
73
mailing_list_id INTEGER,
74
filter_pattern TEXT,
75
filter_type INTEGER,
76
PRIMARY KEY (id),
77
CONSTRAINT contentfilter_mailing_list_id
78
FOREIGN KEY (mailing_list_id) REFERENCES mailinglist (id)
79
);
80
CREATE INDEX ix_contentfilter_mailing_list_id
81
ON contentfilter (mailing_list_id);
82
83
CREATE TABLE domain (
84
id INTEGER NOT NULL,
85
mail_host TEXT,
86
base_url TEXT,
87
description TEXT,
88
contact_address TEXT,
89
PRIMARY KEY (id)
90
);
91
92
CREATE TABLE language (
93
id INTEGER NOT NULL,
94
code TEXT,
95
PRIMARY KEY (id)
96
);
97
98
CREATE TABLE mailinglist (
99
id INTEGER NOT NULL,
100
-- List identity
101
list_name TEXT,
102
mail_host TEXT,
103
include_list_post_header BOOLEAN,
104
include_rfc2369_headers BOOLEAN,
105
-- Attributes not directly modifiable via the web u/i
106
created_at TIMESTAMP,
107
admin_member_chunksize INTEGER,
108
next_request_id INTEGER,
109
next_digest_number INTEGER,
110
digest_last_sent_at TIMESTAMP,
111
volume INTEGER,
112
last_post_at TIMESTAMP,
113
accept_these_nonmembers BLOB,
114
acceptable_aliases_id INTEGER,
115
admin_immed_notify BOOLEAN,
116
admin_notify_mchanges BOOLEAN,
117
administrivia BOOLEAN,
118
advertised BOOLEAN,
119
anonymous_list BOOLEAN,
120
archive BOOLEAN,
121
archive_private BOOLEAN,
122
archive_volume_frequency INTEGER,
123
-- Automatic responses.
124
autorespond_owner INTEGER,
125
autoresponse_owner_text TEXT,
126
autorespond_postings INTEGER,
127
autoresponse_postings_text TEXT,
128
autorespond_requests INTEGER,
129
autoresponse_request_text TEXT,
130
autoresponse_grace_period TEXT,
131
-- Bounces.
132
forward_unrecognized_bounces_to INTEGER,
133
process_bounces BOOLEAN,
134
bounce_info_stale_after TEXT,
135
bounce_matching_headers TEXT,
136
bounce_notify_owner_on_disable BOOLEAN,
137
bounce_notify_owner_on_removal BOOLEAN,
138
bounce_score_threshold INTEGER,
139
bounce_you_are_disabled_warnings INTEGER,
140
bounce_you_are_disabled_warnings_interval TEXT,
141
-- Content filtering.
142
filter_action INTEGER,
143
filter_content BOOLEAN,
144
collapse_alternatives BOOLEAN,
145
convert_html_to_plaintext BOOLEAN,
146
default_member_action INTEGER,
147
default_nonmember_action INTEGER,
148
description TEXT,
149
digest_footer_uri TEXT,
150
digest_header_uri TEXT,
151
digest_is_default BOOLEAN,
152
digest_send_periodic BOOLEAN,
153
digest_size_threshold FLOAT,
154
digest_volume_frequency INTEGER,
155
digestable BOOLEAN,
156
discard_these_nonmembers BLOB,
157
emergency BOOLEAN,
158
encode_ascii_prefixes BOOLEAN,
159
first_strip_reply_to BOOLEAN,
160
footer_uri TEXT,
161
forward_auto_discards BOOLEAN,
162
gateway_to_mail BOOLEAN,
163
gateway_to_news BOOLEAN,
164
generic_nonmember_action INTEGER,
165
goodbye_message_uri TEXT,
166
header_matches BLOB,
167
header_uri TEXT,
168
hold_these_nonmembers BLOB,
169
info TEXT,
170
linked_newsgroup TEXT,
171
max_days_to_hold INTEGER,
172
max_message_size INTEGER,
173
max_num_recipients INTEGER,
174
member_moderation_notice TEXT,
175
mime_is_default_digest BOOLEAN,
176
moderator_password TEXT,
177
new_member_options INTEGER,
178
news_moderation INTEGER,
179
news_prefix_subject_too BOOLEAN,
180
nntp_host TEXT,
181
nondigestable BOOLEAN,
182
nonmember_rejection_notice TEXT,
183
obscure_addresses BOOLEAN,
184
owner_chain TEXT,
185
owner_pipeline TEXT,
186
personalize INTEGER,
187
post_id INTEGER,
188
posting_chain TEXT,
189
posting_pipeline TEXT,
190
preferred_language TEXT,
191
private_roster BOOLEAN,
192
display_name TEXT,
193
reject_these_nonmembers BLOB,
194
reply_goes_to_list INTEGER,
195
reply_to_address TEXT,
196
require_explicit_destination BOOLEAN,
197
respond_to_post_requests BOOLEAN,
198
scrub_nondigest BOOLEAN,
199
send_goodbye_message BOOLEAN,
200
send_reminders BOOLEAN,
201
send_welcome_message BOOLEAN,
202
subject_prefix TEXT,
203
subscribe_auto_approval BLOB,
204
subscribe_policy INTEGER,
205
topics BLOB,
206
topics_bodylines_limit INTEGER,
207
topics_enabled BOOLEAN,
208
unsubscribe_policy INTEGER,
209
welcome_message_uri TEXT,
210
PRIMARY KEY (id)
211
);
212
213
CREATE TABLE member (
214
id INTEGER NOT NULL,
215
_member_id TEXT,
216
role INTEGER,
217
mailing_list TEXT,
218
moderation_action INTEGER,
219
address_id INTEGER,
220
preferences_id INTEGER,
221
user_id INTEGER,
222
PRIMARY KEY (id),
223
CONSTRAINT member_address_id_fk
224
FOREIGN KEY (address_id) REFERENCES address (id),
225
CONSTRAINT member_preferences_id_fk
226
FOREIGN KEY (preferences_id) REFERENCES preferences (id)
227
CONSTRAINT member_user_id_fk
228
FOREIGN KEY (user_id) REFERENCES user (id)
229
);
230
CREATE INDEX ix_member__member_id ON member (_member_id);
231
CREATE INDEX ix_member_address_id ON member (address_id);
232
CREATE INDEX ix_member_preferences_id ON member (preferences_id);
233
234
CREATE TABLE message (
235
id INTEGER NOT NULL,
236
message_id_hash TEXT,
237
path TEXT,
238
message_id TEXT,
239
PRIMARY KEY (id)
240
);
241
242
CREATE TABLE onelastdigest (
243
id INTEGER NOT NULL,
244
mailing_list_id INTEGER,
245
address_id INTEGER,
246
delivery_mode INTEGER,
247
PRIMARY KEY (id),
248
CONSTRAINT onelastdigest_mailing_list_id_fk
249
FOREIGN KEY (mailing_list_id) REFERENCES mailinglist(id),
250
CONSTRAINT onelastdigest_address_id_fk
251
FOREIGN KEY (address_id) REFERENCES address(id)
252
);
253
254
CREATE TABLE pended (
255
id INTEGER NOT NULL,
256
token TEXT,
257
expiration_date TIMESTAMP,
258
PRIMARY KEY (id)
259
);
260
261
CREATE TABLE pendedkeyvalue (
262
id INTEGER NOT NULL,
263
"key" TEXT,
264
value TEXT,
265
pended_id INTEGER,
266
PRIMARY KEY (id),
267
CONSTRAINT pendedkeyvalue_pended_id_fk
268
FOREIGN KEY (pended_id) REFERENCES pended (id)
269
);
270
271
CREATE TABLE preferences (
272
id INTEGER NOT NULL,
273
acknowledge_posts BOOLEAN,
274
hide_address BOOLEAN,
275
preferred_language TEXT,
276
receive_list_copy BOOLEAN,
277
receive_own_postings BOOLEAN,
278
delivery_mode INTEGER,
279
delivery_status INTEGER,
280
PRIMARY KEY (id)
281
);
282
283
CREATE TABLE user (
284
id INTEGER NOT NULL,
285
display_name TEXT,
286
password BINARY,
287
_user_id TEXT,
288
_created_on TIMESTAMP,
289
_preferred_address_id INTEGER,
290
preferences_id INTEGER,
291
PRIMARY KEY (id),
292
CONSTRAINT user_preferences_id_fk
293
FOREIGN KEY (preferences_id) REFERENCES preferences (id),
294
CONSTRAINT _preferred_address_id_fk
295
FOREIGN KEY (_preferred_address_id) REFERENCES address (id)
296
);
297
CREATE INDEX ix_user_user_id ON user (_user_id);
298
299
CREATE TABLE version (
300
id INTEGER NOT NULL,
301
component TEXT,
302
version TEXT,
303
PRIMARY KEY (id)
304
);
305
306
CREATE INDEX ix__request_mailing_list_id ON _request (mailing_list_id);
307
CREATE INDEX ix_address_preferences_id ON address (preferences_id);
308
CREATE INDEX ix_address_user_id ON address (user_id);
309
CREATE INDEX ix_pendedkeyvalue_pended_id ON pendedkeyvalue (pended_id);
310
CREATE INDEX ix_user_preferences_id ON user (preferences_id);
311
312
CREATE TABLE ban (
313
id INTEGER NOT NULL,
314
email TEXT,
315
mailing_list TEXT,
316
PRIMARY KEY (id)
317
);
318
319
CREATE TABLE uid (
320
-- Keep track of all assigned unique ids to prevent re-use.
321
id INTEGER NOT NULL,
322
uid TEXT,
323
PRIMARY KEY (id)
324
);
325
CREATE INDEX ix_uid_uid ON uid (uid);
Loggerhead is a web-based interface for Breezy
Version: 2.0.1