-- $Id: maia-pgsql.sql,v 1.14 2004/07/05 07:19:25 rjl Exp $ -- -- MAIA MAILGUARD LICENSE v.1.0 -- -- Copyright 2004 by Robert LeBlanc -- All rights reserved. -- -- PREAMBLE -- -- This License is designed for users of Maia Mailguard -- ("the Software") who wish to support the Maia Mailguard project by -- leaving "Maia Mailguard" branding information in the HTML output -- of the pages generated by the Software, and providing links back -- to the Maia Mailguard home page. Users who wish to remove this -- branding information should contact the copyright owner to obtain -- a Rebranding License. -- -- DEFINITION OF TERMS -- -- The "Software" refers to Maia Mailguard, including all of the -- associated PHP, Perl, and SQL scripts, documentation files, graphic -- icons and logo images. -- -- GRANT OF LICENSE -- -- Redistribution and use in source and binary forms, with or without -- modification, are permitted provided that the following conditions -- are met: -- -- 1. Redistributions of source code must retain the above copyright -- notice, this list of conditions and the following disclaimer. -- -- 2. Redistributions in binary form must reproduce the above copyright -- notice, this list of conditions and the following disclaimer in the -- documentation and/or other materials provided with the distribution. -- -- 3. The end-user documentation included with the redistribution, if -- any, must include the following acknowledgment: -- -- "This product includes software developed by Robert LeBlanc -- ." -- -- Alternately, this acknowledgment may appear in the software itself, -- if and wherever such third-party acknowledgments normally appear. -- -- 4. At least one of the following branding conventions must be used: -- -- a. The Maia Mailguard logo appears in the page-top banner of -- all HTML output pages in an unmodified form, and links -- directly to the Maia Mailguard home page; or -- -- b. The "Powered by Maia Mailguard" graphic appears in the HTML -- output of all gateway pages that lead to this software, -- linking directly to the Maia Mailguard home page; or -- -- c. A separate Rebranding License is obtained from the copyright -- owner, exempting the Licensee from 4(a) and 4(b), subject to -- the additional conditions laid out in that license document. -- -- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDER AND CONTRIBUTORS -- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT -- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS -- FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE -- COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, -- INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, -- BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS -- OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND -- ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR -- TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE -- USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. -- [users] contains amavisd-new's per-address settings, and -- links e-mail addresses to Maia users. CREATE TABLE users ( id SERIAL PRIMARY KEY, priority INTEGER DEFAULT '7' NOT NULL, policy_id INTEGER DEFAULT '1' NOT NULL, -- policy.id email VARCHAR(255) NOT NULL UNIQUE, maia_user_id INTEGER NOT NULL, -- maia_users.id maia_domain_id INTEGER NOT NULL -- maia_domains.id ); CREATE UNIQUE INDEX users_idx_email ON users(email); -- [mailaddr] contains a list of sender e-mail addresses -- referenced by users' whitelists and blacklists. CREATE TABLE mailaddr ( id SERIAL PRIMARY KEY, priority INTEGER DEFAULT '7' NOT NULL, email VARCHAR(255) NOT NULL UNIQUE ); CREATE UNIQUE INDEX mailaddr_idx_email ON mailaddr(email); -- [wblist] contains the whitelist and blacklist records, on -- a per-user (not per-address) basis. CREATE TABLE wblist ( rid INTEGER NOT NULL, -- maia_users.id sid INTEGER NOT NULL, -- mailaddr.id wb CHAR(1) NOT NULL, PRIMARY KEY (rid,sid) ); -- [policy] contains amavisd-new's policy settings, which -- Maia applies on a per-address basis (i.e. each e-mail address -- is assigned its own unique policy record). Each domain also -- has a set of policy defaults, and the system default policy -- is stored as the '@.' user's policy record. CREATE TABLE policy ( id SERIAL PRIMARY KEY, policy_name VARCHAR(255), virus_lover CHAR(1) DEFAULT 'Y', spam_lover CHAR(1) DEFAULT 'Y', banned_files_lover CHAR(1) DEFAULT 'Y', bad_header_lover CHAR(1) DEFAULT 'Y', bypass_virus_checks CHAR(1) DEFAULT 'Y', bypass_spam_checks CHAR(1) DEFAULT 'Y', bypass_banned_checks CHAR(1) DEFAULT 'Y', bypass_header_checks CHAR(1) DEFAULT 'Y', discard_viruses CHAR(1) DEFAULT 'N', discard_spam CHAR(1) DEFAULT 'N', discard_banned_files CHAR(1) DEFAULT 'N', discard_bad_headers CHAR(1) DEFAULT 'N', spam_modifies_subj CHAR(1) DEFAULT 'N', spam_quarantine_to VARCHAR(64) DEFAULT NULL, spam_tag_level NUMERIC(7,3) DEFAULT '999', spam_tag2_level NUMERIC(7,3) DEFAULT '999', spam_kill_level NUMERIC(7,3) DEFAULT '999' ); -- [maia_config] contains Maia's configuration settings, as set -- and modified by the super-administrator. CREATE TABLE maia_config ( id INTEGER DEFAULT '0' PRIMARY KEY, enable_user_autocreation CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N' enable_false_negative_management CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' enable_stats_tracking CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' enable_virus_scanning CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' enable_spam_filtering CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' enable_banned_files_checking CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' enable_bad_header_checking CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' enable_charts CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N' enable_spamtraps CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N' enable_stats_reporting CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N' enable_address_linking CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' enable_privacy_invasion CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N' enable_username_changes CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' internal_auth CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N' system_default_user_is_local CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' user_virus_scanning CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' user_spam_filtering CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' user_banned_files_checking CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' user_bad_header_checking CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' admin_email VARCHAR(255), expiry_period INTEGER DEFAULT '30', -- days ham_cache_expiry_period INTEGER DEFAULT '5', -- days reminder_threshold_count INTEGER DEFAULT '100', -- items reminder_threshold_size INTEGER DEFAULT '500000', -- bytes reminder_template_file VARCHAR(255) DEFAULT 'reminder.tpl', reminder_login_url VARCHAR(255), newuser_template_file VARCHAR(255) DEFAULT 'newuser.tpl', smtp_server VARCHAR(255) DEFAULT 'localhost', smtp_port INTEGER DEFAULT '10025', currency_label VARCHAR(15) DEFAULT '$', bandwidth_cost NUMERIC(14,2) DEFAULT '0.0' NOT NULL, chart_ham_colour VARCHAR(32) DEFAULT '#DDDDB7', chart_spam_colour VARCHAR(32) DEFAULT '#FFAAAA', chart_virus_colour VARCHAR(32) DEFAULT '#CCFFCC', chart_fp_colour VARCHAR(32) DEFAULT '#C4CA73', chart_fn_colour VARCHAR(32) DEFAULT '#FF7575', chart_suspected_ham_colour VARCHAR(32) DEFAULT '#FFFFB7', chart_suspected_spam_colour VARCHAR(32) DEFAULT '#FFCCCC', chart_wl_colour VARCHAR(32) DEFAULT '#eeeeee', chart_bl_colour VARCHAR(32) DEFAULT '#888888', chart_background_colour VARCHAR(32) DEFAULT '#B0ECFF', chart_font_colour VARCHAR(32) DEFAULT '#3D3D50', chart_autogeneration_interval INTEGER DEFAULT '60', -- minutes banner_title VARCHAR(255) DEFAULT 'Maia Mailguard', use_icons CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' use_logo CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' logo_url VARCHAR(255) DEFAULT 'http://www.renaissoft.com/maia/', logo_file VARCHAR(255) DEFAULT 'images/maia-logotoolbar.gif', logo_alt_text VARCHAR(255) DEFAULT 'Maia Mailguard Home Page', virus_info_url VARCHAR(255) DEFAULT 'http://www.google.com/search?q=%%VIRUSNAME%%+virus+information', virus_lookup VARCHAR(20) DEFAULT 'google', primary_report_server VARCHAR(255) DEFAULT 'maia.renaissoft.com', primary_report_port INTEGER DEFAULT '443', secondary_report_server VARCHAR(255), secondary_report_port INTEGER DEFAULT '443', reporter_sitename VARCHAR(255), reporter_username VARCHAR(50), reporter_password VARCHAR(50), size_limit INTEGER DEFAULT '1000000', oversize_policy CHAR(1) DEFAULT 'B' NOT NULL, -- 'P', 'B' sa_score_set INTEGER DEFAULT '0' NOT NULL, key_file VARCHAR(255) DEFAULT 'blowfish.key' ); -- [maia_languages] contains a list of the installed languages -- and their ISO-639 two-letter abbreviations. CREATE TABLE maia_languages ( id SERIAL PRIMARY KEY, language_name VARCHAR(100) NOT NULL, -- e.g. 'English' abbreviation CHAR(2) NOT NULL UNIQUE, -- e.g. 'en' installed CHAR(1) DEFAULT 'N' NOT NULL ); CREATE UNIQUE INDEX maia_languages_idx_abbreviation ON maia_languages(abbreviation); -- [maia_users] contains mail filter settings that apply to -- e-mail recipients who have registered with Maia. The -- user_level is stored as one of (U)ser, (A)dministrator, -- or (S)uper-Administrator. CREATE TABLE maia_users ( id SERIAL PRIMARY KEY, user_name VARCHAR(255) NOT NULL UNIQUE, user_level CHAR(1) DEFAULT 'U' NOT NULL, -- 'U', 'A', 'S' reminders CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' charts CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N' primary_email_id INTEGER DEFAULT '0' NOT NULL, -- users.id language VARCHAR(10) DEFAULT 'en' NOT NULL, charset VARCHAR(20) DEFAULT 'ISO-8859-1' NOT NULL, spamtrap CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N' password VARCHAR(32), -- 32-byte MD5 hash auto_whitelist CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' items_per_page INTEGER DEFAULT '50' NOT NULL, spam_quarantine_sort CHAR(2) DEFAULT 'XA' NOT NULL, -- [XDFS][AD] virus_quarantine_sort CHAR(2) DEFAULT 'DA' NOT NULL, -- [DFS][AD] header_quarantine_sort CHAR(2) DEFAULT 'DA' NOT NULL, -- [DFS][AD] attachment_quarantine_sort CHAR(2) DEFAULT 'DA' NOT NULL, -- [DFS][AD] ham_cache_sort CHAR(2) DEFAULT 'XD' NOT NULL, -- [XDFS][AD] discard_ham CHAR(1) DEFAULT 'N' NOT NULL -- 'Y', 'N' ); CREATE UNIQUE INDEX maia_users_idx_user_name ON maia_users(user_name); -- [maia_domains] contains mail filter settings that apply to -- entire e-mail domains as defaults for users who are not -- registered with Maia. CREATE TABLE maia_domains ( id SERIAL PRIMARY KEY, domain VARCHAR(255) NOT NULL UNIQUE, reminders CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N' charts CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N' enable_user_autocreation CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N' language VARCHAR(10) DEFAULT 'en' NOT NULL, charset VARCHAR(20) DEFAULT 'ISO-8859-1' NOT NULL ); CREATE UNIQUE INDEX maia_domains_idx_domain ON maia_domains(domain); -- [maia_domain_admins] is a one-to-many mapping of domains to -- users with administrator privileges (domains can have -- multiple administrators). CREATE TABLE maia_domain_admins ( domain_id INTEGER NOT NULL, -- maia_domains.id admin_id INTEGER NOT NULL, -- maia_users.id PRIMARY KEY (domain_id, admin_id) ); -- [maia_stats] is a table of per-user statistics that keeps -- track of the total number of mail items of each type, -- along with total sizes and superlatives (e.g. largest, -- smallest, oldest, newest, etc.). CREATE TABLE maia_stats ( user_id INTEGER DEFAULT '0' NOT NULL, -- maia_users.id -- suspected ham oldest_suspected_ham_date TIMESTAMP, newest_suspected_ham_date TIMESTAMP, smallest_suspected_ham_size INTEGER DEFAULT '0' NOT NULL, largest_suspected_ham_size INTEGER DEFAULT '0' NOT NULL, total_suspected_ham_size INTEGER DEFAULT '0' NOT NULL, lowest_suspected_ham_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, highest_suspected_ham_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, total_suspected_ham_score NUMERIC(14,3) DEFAULT '0.0' NOT NULL, total_suspected_ham_items INTEGER DEFAULT '0' NOT NULL, -- ham oldest_ham_date TIMESTAMP, newest_ham_date TIMESTAMP, smallest_ham_size INTEGER DEFAULT '0' NOT NULL, largest_ham_size INTEGER DEFAULT '0' NOT NULL, total_ham_size INTEGER DEFAULT '0' NOT NULL, lowest_ham_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, highest_ham_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, total_ham_score NUMERIC(14,3) DEFAULT '0.0' NOT NULL, total_ham_items INTEGER DEFAULT '0' NOT NULL, -- mail from whitelisted senders oldest_wl_date TIMESTAMP, newest_wl_date TIMESTAMP, smallest_wl_size INTEGER DEFAULT '0' NOT NULL, largest_wl_size INTEGER DEFAULT '0' NOT NULL, total_wl_size INTEGER DEFAULT '0' NOT NULL, total_wl_items INTEGER DEFAULT '0' NOT NULL, -- mail from blacklisted senders oldest_bl_date TIMESTAMP, newest_bl_date TIMESTAMP, smallest_bl_size INTEGER DEFAULT '0' NOT NULL, largest_bl_size INTEGER DEFAULT '0' NOT NULL, total_bl_size INTEGER DEFAULT '0' NOT NULL, total_bl_items INTEGER DEFAULT '0' NOT NULL, -- suspected spam oldest_suspected_spam_date TIMESTAMP, newest_suspected_spam_date TIMESTAMP, smallest_suspected_spam_size INTEGER DEFAULT '0' NOT NULL, largest_suspected_spam_size INTEGER DEFAULT '0' NOT NULL, total_suspected_spam_size INTEGER DEFAULT '0' NOT NULL, lowest_suspected_spam_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, highest_suspected_spam_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, total_suspected_spam_score NUMERIC(14,3) DEFAULT '0.0' NOT NULL, total_suspected_spam_items INTEGER DEFAULT '0' NOT NULL, -- false positives (i.e. rescued ham) oldest_fp_date TIMESTAMP, newest_fp_date TIMESTAMP, smallest_fp_size INTEGER DEFAULT '0' NOT NULL, largest_fp_size INTEGER DEFAULT '0' NOT NULL, total_fp_size INTEGER DEFAULT '0' NOT NULL, lowest_fp_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, highest_fp_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, total_fp_score NUMERIC(14,3) DEFAULT '0.0' NOT NULL, total_fp_items INTEGER DEFAULT '0' NOT NULL, -- false negatives (i.e. reported spam) oldest_fn_date TIMESTAMP, newest_fn_date TIMESTAMP, smallest_fn_size INTEGER DEFAULT '0' NOT NULL, largest_fn_size INTEGER DEFAULT '0' NOT NULL, total_fn_size INTEGER DEFAULT '0' NOT NULL, lowest_fn_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, highest_fn_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, total_fn_score NUMERIC(14,3) DEFAULT '0.0' NOT NULL, total_fn_items INTEGER DEFAULT '0' NOT NULL, -- confirmed spam oldest_spam_date TIMESTAMP, newest_spam_date TIMESTAMP, smallest_spam_size INTEGER DEFAULT '0' NOT NULL, largest_spam_size INTEGER DEFAULT '0' NOT NULL, total_spam_size INTEGER DEFAULT '0' NOT NULL, lowest_spam_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, highest_spam_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, total_spam_score NUMERIC(14,3) DEFAULT '0.0' NOT NULL, total_spam_items INTEGER DEFAULT '0' NOT NULL, -- viruses oldest_virus_date TIMESTAMP, newest_virus_date TIMESTAMP, smallest_virus_size INTEGER DEFAULT '0' NOT NULL, largest_virus_size INTEGER DEFAULT '0' NOT NULL, total_virus_size INTEGER DEFAULT '0' NOT NULL, total_virus_items INTEGER DEFAULT '0' NOT NULL, -- mail with invalid headers oldest_bad_header_date TIMESTAMP, newest_bad_header_date TIMESTAMP, smallest_bad_header_size INTEGER DEFAULT '0' NOT NULL, largest_bad_header_size INTEGER DEFAULT '0' NOT NULL, total_bad_header_size INTEGER DEFAULT '0' NOT NULL, total_bad_header_items INTEGER DEFAULT '0' NOT NULL, -- mail containing banned file attachments oldest_banned_file_date TIMESTAMP, newest_banned_file_date TIMESTAMP, smallest_banned_file_size INTEGER DEFAULT '0' NOT NULL, largest_banned_file_size INTEGER DEFAULT '0' NOT NULL, total_banned_file_size INTEGER DEFAULT '0' NOT NULL, total_banned_file_items INTEGER DEFAULT '0' NOT NULL, -- oversized items oldest_oversized_date TIMESTAMP, newest_oversized_date TIMESTAMP, smallest_oversized_size INTEGER DEFAULT '0' NOT NULL, largest_oversized_size INTEGER DEFAULT '0' NOT NULL, total_oversized_size INTEGER DEFAULT '0' NOT NULL, total_oversized_items INTEGER DEFAULT '0' NOT NULL, PRIMARY KEY (user_id) ); -- [maia_mail] stores mail items of five types: -- Suspected (S)pam, (V)iruses, Banned (F)ile Attachments, -- (B)ad Headers, and Suspected (H)am. CREATE TABLE maia_mail ( id SERIAL PRIMARY KEY, received_date TIMESTAMP NOT NULL, size INTEGER NOT NULL, sender_email VARCHAR(255) NOT NULL, envelope_to TEXT NOT NULL, subject VARCHAR(255) NOT NULL, contents TEXT NOT NULL, score NUMERIC(7,3) -- only supplied for (S)pam ); CREATE INDEX maia_mail_idx_received_date ON maia_mail(received_date); -- [maia_mail_recipients] is a one-to-many mapping of -- mail items to recipients. These records -- are deleted when a recipient rescues an item, or the -- item is deleted. CREATE TABLE maia_mail_recipients ( mail_id INTEGER NOT NULL, -- maia_mail.id recipient_id INTEGER NOT NULL, -- maia_users.id type CHAR(1) NOT NULL, -- 'S', 'V', 'F', 'B', 'H', 'L', 'W' PRIMARY KEY (mail_id, recipient_id) ); CREATE INDEX maia_mail_recipients_idx_type ON maia_mail_recipients(type); CREATE INDEX maia_mail_recipients_idx_mail_id ON maia_mail_recipients(mail_id); -- [maia_viruses] contains a list of the "official" names of -- viruses that have been detected by the virus scanners. -- These are the names that will be displayed in stats -- tables and charts. CREATE TABLE maia_viruses ( id SERIAL PRIMARY KEY, virus_name VARCHAR(255) NOT NULL UNIQUE, count INTEGER DEFAULT '0' NOT NULL ); CREATE UNIQUE INDEX maia_viruses_idx_virus_name ON maia_viruses(virus_name); -- [maia_virus_aliases] is a one-to-many mapping of viruses -- to aliases for those viruses, as detected by other -- virus scanners. CREATE TABLE maia_virus_aliases ( virus_id INTEGER NOT NULL, -- maia_viruses.id virus_alias VARCHAR(255) NOT NULL, PRIMARY KEY (virus_id, virus_alias) ); -- [maia_viruses_detected] is a one-to-many mapping of -- mail items to viruses found. These -- entries are deleted when a mail item is rescued -- or deleted. CREATE TABLE maia_viruses_detected ( mail_id INTEGER NOT NULL, -- maia_mail.id virus_id INTEGER NOT NULL, -- maia_viruses.id PRIMARY KEY (mail_id, virus_id) ); -- [maia_sa_rules] contains a list of all the SpamAssassin -- rules installed on the system, along with their -- text descriptions and score values. CREATE TABLE maia_sa_rules ( id SERIAL PRIMARY KEY, rule_name VARCHAR(255) NOT NULL UNIQUE, rule_description VARCHAR(255) DEFAULT '' NOT NULL, rule_score_0 NUMERIC(7,3) DEFAULT '1.0' NOT NULL, rule_score_1 NUMERIC(7,3) DEFAULT '1.0' NOT NULL, rule_score_2 NUMERIC(7,3) DEFAULT '1.0' NOT NULL, rule_score_3 NUMERIC(7,3) DEFAULT '1.0' NOT NULL, rule_count INTEGER DEFAULT '0' NOT NULL ); CREATE UNIQUE INDEX maia_sa_rules_idx_rule_name ON maia_sa_rules(rule_name); -- [maia_sa_rules_triggered] is a one-to-many mapping of -- mail items to SpamAssassin rules triggered -- by that mail item. These entries are deleted when a -- mail item is rescued or deleted. CREATE TABLE maia_sa_rules_triggered ( mail_id INTEGER NOT NULL, -- maia_mail.id rule_id INTEGER NOT NULL, -- maia_sa_rules.id rule_score NUMERIC(7,3) DEFAULT '0.0' NOT NULL, PRIMARY KEY (mail_id, rule_id) ); -- [maia_banned_attachments_found] is a one-to-many mapping of -- mail items to file attachments that were found in that -- mail item. These entries are deleted when a mail item -- is rescued or deleted. CREATE TABLE maia_banned_attachments_found ( mail_id INTEGER NOT NULL, -- maia_mail.id file_name VARCHAR(255) NOT NULL, file_type VARCHAR(20) DEFAULT 'Unknown' NOT NULL, PRIMARY KEY (mail_id, file_name) ); -- [maia_stats_history] is a table used to store snapshots of the -- more relevant items from the [maia_stats] table at (H)ourly, -- (D)aily, (M)onthly, and (Y)early intervals. Entries are -- automatically expired, so this table has a more or less -- fixed size of ((24 + 31 + 12 + 1/year) * users) rows. -- CREATE TABLE maia_stats_history ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, -- maia_users.id type CHAR(1) DEFAULT 'H' NOT NULL, -- 'H', 'D', 'M', 'Y' taken_at TIMESTAMP NOT NULL, total_ham_items INTEGER DEFAULT '0' NOT NULL, total_ham_size INTEGER DEFAULT '0' NOT NULL, total_spam_items INTEGER DEFAULT '0' NOT NULL, total_spam_size INTEGER DEFAULT '0' NOT NULL, total_virus_items INTEGER DEFAULT '0' NOT NULL, total_virus_size INTEGER DEFAULT '0' NOT NULL, total_fp_items INTEGER DEFAULT '0' NOT NULL, total_fp_size INTEGER DEFAULT '0' NOT NULL, total_fn_items INTEGER DEFAULT '0' NOT NULL, total_fn_size INTEGER DEFAULT '0' NOT NULL, total_banned_file_items INTEGER DEFAULT '0' NOT NULL, total_banned_file_size INTEGER DEFAULT '0' NOT NULL, total_bad_header_items INTEGER DEFAULT '0' NOT NULL, total_bad_header_size INTEGER DEFAULT '0' NOT NULL, total_wl_items INTEGER DEFAULT '0' NOT NULL, total_wl_size INTEGER DEFAULT '0' NOT NULL, total_bl_items INTEGER DEFAULT '0' NOT NULL, total_bl_size INTEGER DEFAULT '0' NOT NULL, total_oversized_items INTEGER DEFAULT '0' NOT NULL, total_oversized_size INTEGER DEFAULT '0' NOT NULL ); -- Database initialization script -- Create a permissive system default policy and create the '@.' domain INSERT INTO policy VALUES (1, 'Default', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', NULL, 999, 999, 999); INSERT INTO users VALUES (1, 0, 1, '@.', 1, 1); INSERT INTO maia_users (id, user_name, primary_email_id, reminders, discard_ham) VALUES (1, '@.', 1, 'N', 'Y'); INSERT INTO maia_domains (id, domain) VALUES (1, '@.'); -- Instantiate a default system configuration INSERT INTO maia_config (id) VALUES (0); -- Load the ISO-639 language names and abbreviations INSERT INTO maia_languages (abbreviation, language_name) VALUES ('aa', 'Afar'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ab', 'Abkhazian'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('af', 'Afrikaans'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('am', 'Amharic'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ar', 'ﺔﻴﺐﺮﻌﻠﺍ '); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('as', 'Assamese'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ay', 'Aymara'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('az', 'Azцri'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ba', 'Bashkir'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('be', 'Беларуская'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bg', 'Български'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bh', 'Bihari'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bi', 'Bislama'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bn', 'Bengali'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bo', 'Tibetan'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('br', 'Brezhoneg'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ca', 'Català'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('co', 'Corsican'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('cs', 'Čeština'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('cy', 'Cymraeg'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('da', 'Dansk'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('de', 'Deutsch'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('dz', 'Bhutani'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('el', 'Ελληνικά'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('en', 'English'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('eo', 'Esperanto'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('es', 'Español'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('et', 'Eesti'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('eu', 'Euskaraz'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fa', 'Persian'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fi', 'Suomeksi'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fj', 'Fiji'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fo', 'Føroyskt'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fr', 'Français'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fy', 'Frysk'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ga', 'Gaeilge'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gd', 'Gàidhlig'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gl', 'Galician'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gn', 'Guarani'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gu', 'Gujarati'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gv', 'Ghaelg'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ha', 'Hausa'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('he', 'עברית'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('hi', 'Hindi'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('hr', 'Hrvatski'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('hu', 'Magyar'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('hy', 'Armenian'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ia', 'Interlingua'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('id', 'Bahasa Indonesia'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ie', 'Interlingue'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ik', 'Inupiak'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('is', 'Íslenska'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('it', 'Italiano'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('iu', 'Inuktitut'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ja', 'Nihongo'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('jw', 'Javanese'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ka', 'Georgian'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('kk', 'Kazakh'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('kl', 'Kalaallísut'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('km', 'Cambodian'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('kn', 'Kannada'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ko', 'Korean'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ks', 'Kashmiri'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ku', 'Kurmancî (Kurdî)'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ky', 'Kirghiz'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('la', 'Latina'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('lb', 'Lëtzebuergesch'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ln', 'Lingala'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('lo', 'Laothian'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('lt', 'Lietuvių'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('lv', 'Latviešu'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mg', 'Malagasy'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mi', 'Maori'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mk', 'Македонски'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ml', 'Malayalam'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mn', 'Mongolian'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mo', 'Moldavian'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mr', 'Marathi'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ms', 'Malay'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mt', 'Malti'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('my', 'Burmese'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('na', 'Nauru'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ne', 'Nepali'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('nl', 'Nederlands'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('no', 'Norsk'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('oc', 'Occitan'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('om', 'Oromo'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('or', 'Oriya'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('pa', 'Punjabi'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('pl', 'Polski'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ps', 'Pashto'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('pt', 'Português'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('qu', 'Quechua'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('rm', 'Rumantsch'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('rn', 'Kirundi'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ro', 'Română'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ru', 'Русский'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('rw', 'Kinyarwanda'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sa', 'Sanskrit'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sd', 'Sindhi'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('se', 'Davvisámegiella'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sg', 'Sangho'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sh', 'Serbo-Croatian'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('si', 'Sinhalese'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sk', 'Slovenčina'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sl', 'Slovenski'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sm', 'Samoan'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sn', 'Shona'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('so', 'af Soomaali'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sq', 'Shqip'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sr', 'Српски'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ss', 'Siswati'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('st', 'Sesotho'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('su', 'Sundanese'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sv', 'Svenska'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sw', 'Kiswahili'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ta', 'Tamil'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('te', 'Telugu'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tg', 'Tajik'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('th', 'Thai'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ti', 'Tigrinya'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tk', 'Turkmen'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tl', 'Tagalog'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tn', 'Setswana'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('to', 'Tonga'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tr', 'Türkçe'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ts', 'Tsonga'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tt', 'Tatar'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tw', 'Twi'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ug', 'Uighur'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('uk', 'Украïнська'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ur', 'Urdu'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('uz', 'Uzbek'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('vi', 'Tiếng Việt'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('vo', 'Volapuk'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('wo', 'Wolof'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('xh', 'Xhosa'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('yi', 'Jiddiš'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('yo', 'Yoruba'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('za', 'Zhuang'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('zh', 'Zhōng-wén'); INSERT INTO maia_languages (abbreviation, language_name) VALUES ('zu', 'Zulu'); -- Mark the default language (English) as "installed" UPDATE maia_languages SET installed = 'Y' WHERE abbreviation = 'en'; -- End of database initialization script