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