diff options
Diffstat (limited to 'Mailman/database/mailman.sql')
| -rw-r--r-- | Mailman/database/mailman.sql | 206 |
1 files changed, 206 insertions, 0 deletions
diff --git a/Mailman/database/mailman.sql b/Mailman/database/mailman.sql new file mode 100644 index 000000000..a20b1b118 --- /dev/null +++ b/Mailman/database/mailman.sql @@ -0,0 +1,206 @@ +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_filter_rules 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, + 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, + 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, + 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, + 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); |