1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
|
CREATE TABLE _request (
id INTEGER NOT NULL,
"key" TEXT,
request_type TEXT,
data_hash TEXT,
mailing_list_id INTEGER,
PRIMARY KEY (id),
CONSTRAINT _request_mailing_list_id_fk FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id)
);
CREATE TABLE address (
id INTEGER NOT NULL,
address TEXT,
_original TEXT,
real_name TEXT,
verified_on TIMESTAMP,
registered_on TIMESTAMP,
user_id INTEGER,
preferences_id INTEGER,
PRIMARY KEY (id),
CONSTRAINT address_user_id_fk FOREIGN KEY(user_id) REFERENCES user (id),
CONSTRAINT address_preferences_id_fk FOREIGN KEY(preferences_id) REFERENCES preferences (id)
);
CREATE TABLE language (
id INTEGER NOT NULL,
code TEXT,
PRIMARY KEY (id)
);
CREATE TABLE mailinglist (
id INTEGER NOT NULL,
list_name TEXT,
host_name TEXT,
created_at TIMESTAMP,
web_page_url TEXT,
admin_member_chunksize INTEGER,
hold_and_cmd_autoresponses BLOB,
next_request_id INTEGER,
next_digest_number INTEGER,
admin_responses BLOB,
postings_responses BLOB,
request_responses BLOB,
digest_last_sent_at NUMERIC(10, 2),
one_last_digest BLOB,
volume INTEGER,
last_post_time TIMESTAMP,
accept_these_nonmembers BLOB,
acceptable_aliases BLOB,
admin_immed_notify BOOLEAN,
admin_notify_mchanges BOOLEAN,
administrivia BOOLEAN,
advertised BOOLEAN,
anonymous_list BOOLEAN,
archive BOOLEAN,
archive_private BOOLEAN,
archive_volume_frequency INTEGER,
autorespond_admin BOOLEAN,
autorespond_postings BOOLEAN,
autorespond_requests INTEGER,
autoresponse_admin_text TEXT,
autoresponse_graceperiod TEXT,
autoresponse_postings_text TEXT,
autoresponse_request_text TEXT,
ban_list BLOB,
bounce_info_stale_after TEXT,
bounce_matching_headers TEXT,
bounce_notify_owner_on_disable BOOLEAN,
bounce_notify_owner_on_removal BOOLEAN,
bounce_processing BOOLEAN,
bounce_score_threshold INTEGER,
bounce_unrecognized_goes_to_list_owner BOOLEAN,
bounce_you_are_disabled_warnings INTEGER,
bounce_you_are_disabled_warnings_interval TEXT,
collapse_alternatives BOOLEAN,
convert_html_to_plaintext BOOLEAN,
default_member_moderation BOOLEAN,
description TEXT,
digest_footer TEXT,
digest_header TEXT,
digest_is_default BOOLEAN,
digest_send_periodic BOOLEAN,
digest_size_threshold INTEGER,
digest_volume_frequency INTEGER,
digestable BOOLEAN,
discard_these_nonmembers BLOB,
emergency BOOLEAN,
encode_ascii_prefixes BOOLEAN,
filter_action INTEGER,
filter_content BOOLEAN,
filter_filename_extensions BLOB,
filter_mime_types BLOB,
first_strip_reply_to BOOLEAN,
forward_auto_discards BOOLEAN,
gateway_to_mail BOOLEAN,
gateway_to_news BOOLEAN,
generic_nonmember_action INTEGER,
goodbye_msg TEXT,
header_matches BLOB,
hold_these_nonmembers BLOB,
include_list_post_header BOOLEAN,
include_rfc2369_headers BOOLEAN,
info TEXT,
linked_newsgroup TEXT,
max_days_to_hold INTEGER,
max_message_size INTEGER,
max_num_recipients INTEGER,
member_moderation_action BOOLEAN,
member_moderation_notice TEXT,
mime_is_default_digest BOOLEAN,
moderator_password TEXT,
msg_footer TEXT,
msg_header TEXT,
new_member_options INTEGER,
news_moderation TEXT,
news_prefix_subject_too BOOLEAN,
nntp_host TEXT,
nondigestable BOOLEAN,
nonmember_rejection_notice TEXT,
obscure_addresses BOOLEAN,
pass_filename_extensions BLOB,
pass_mime_types BLOB,
personalize TEXT,
pipeline TEXT,
post_id INTEGER,
preferred_language TEXT,
private_roster BOOLEAN,
real_name TEXT,
reject_these_nonmembers BLOB,
reply_goes_to_list TEXT,
reply_to_address TEXT,
require_explicit_destination BOOLEAN,
respond_to_post_requests BOOLEAN,
scrub_nondigest BOOLEAN,
send_goodbye_msg BOOLEAN,
send_reminders BOOLEAN,
send_welcome_msg BOOLEAN,
start_chain TEXT,
subject_prefix TEXT,
subscribe_auto_approval BLOB,
subscribe_policy INTEGER,
topics BLOB,
topics_bodylines_limit INTEGER,
topics_enabled BOOLEAN,
unsubscribe_policy INTEGER,
welcome_msg TEXT,
PRIMARY KEY (id)
);
CREATE TABLE member (
id INTEGER NOT NULL,
role TEXT,
mailing_list TEXT,
is_moderated BOOLEAN,
address_id INTEGER,
preferences_id INTEGER,
PRIMARY KEY (id),
CONSTRAINT member_address_id_fk FOREIGN KEY(address_id) REFERENCES address (id),
CONSTRAINT member_preferences_id_fk FOREIGN KEY(preferences_id) REFERENCES preferences (id)
);
CREATE TABLE message (
id INTEGER NOT NULL,
message_id_hash TEXT,
path TEXT,
message_id TEXT,
PRIMARY KEY (id)
);
CREATE TABLE pended (
id INTEGER NOT NULL,
token TEXT,
expiration_date TIMESTAMP,
PRIMARY KEY (id)
);
CREATE TABLE pendedkeyvalue (
id INTEGER NOT NULL,
"key" TEXT,
value TEXT,
pended_id INTEGER,
PRIMARY KEY (id),
CONSTRAINT pendedkeyvalue_pended_id_fk FOREIGN KEY(pended_id) REFERENCES pended (id)
);
CREATE TABLE preferences (
id INTEGER NOT NULL,
acknowledge_posts BOOLEAN,
hide_address BOOLEAN,
preferred_language TEXT,
receive_list_copy BOOLEAN,
receive_own_postings BOOLEAN,
delivery_mode TEXT,
delivery_status TEXT,
PRIMARY KEY (id)
);
CREATE TABLE user (
id INTEGER NOT NULL,
real_name TEXT,
password TEXT,
preferences_id INTEGER,
PRIMARY KEY (id),
CONSTRAINT user_preferences_id_fk FOREIGN KEY(preferences_id) REFERENCES preferences (id)
);
CREATE TABLE version (
id INTEGER NOT NULL,
component TEXT,
version INTEGER,
PRIMARY KEY (id)
);
CREATE INDEX ix__request_mailing_list_id ON _request (mailing_list_id);
CREATE INDEX ix_address_preferences_id ON address (preferences_id);
CREATE INDEX ix_address_user_id ON address (user_id);
CREATE INDEX ix_member_address_id ON member (address_id);
CREATE INDEX ix_member_preferences_id ON member (preferences_id);
CREATE INDEX ix_pendedkeyvalue_pended_id ON pendedkeyvalue (pended_id);
CREATE INDEX ix_user_preferences_id ON user (preferences_id);
|