-- $Id: maia-mysql.sql,v 1.13 2004/06/30 08:30:03 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 int unsigned DEFAULT '0' NOT NULL auto_increment, priority int DEFAULT '7' NOT NULL, policy_id int unsigned DEFAULT '1' NOT NULL, -- policy.id email varchar(255) NOT NULL, maia_user_id int unsigned NOT NULL, -- maia_users.id maia_domain_id int unsigned NOT NULL, -- maia_domains.id PRIMARY KEY (id), KEY email (email) ) TYPE=InnoDB; 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 int unsigned DEFAULT '0' NOT NULL auto_increment, priority int DEFAULT '7' NOT NULL, email varchar(255) NOT NULL, PRIMARY KEY (id), KEY email (email) ) TYPE=InnoDB; 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 int unsigned NOT NULL, -- maia_users.id sid int unsigned NOT NULL, -- mailaddr.id wb char(1) NOT NULL, PRIMARY KEY (rid,sid) ) TYPE=InnoDB; -- [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 int unsigned DEFAULT '0' NOT NULL auto_increment, 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 float DEFAULT '999', spam_tag2_level float DEFAULT '999', spam_kill_level float DEFAULT '999', PRIMARY KEY (id) ) TYPE=InnoDB; -- [maia_config] contains Maia's configuration settings, as set -- and modified by the super-administrator. CREATE TABLE maia_config ( id int unsigned DEFAULT '0' NOT NULL, 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 int unsigned DEFAULT '30', -- days ham_cache_expiry_period int unsigned DEFAULT '5', -- days reminder_threshold_count int unsigned DEFAULT '100', -- items reminder_threshold_size int unsigned 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 int unsigned DEFAULT '10025', currency_label varchar(15) DEFAULT '$', bandwidth_cost float 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 int unsigned 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 int unsigned DEFAULT '443', secondary_report_server varchar(255), secondary_report_port int unsigned DEFAULT '443', reporter_sitename varchar(255), reporter_username varchar(50), reporter_password varchar(50), size_limit int unsigned DEFAULT '1000000', oversize_policy char(1) DEFAULT 'B' NOT NULL, -- 'P', 'B' sa_score_set int unsigned DEFAULT '0' NOT NULL, key_file varchar(255) DEFAULT 'blowfish.key', PRIMARY KEY (id) ) TYPE=InnoDB; -- [maia_languages] contains a list of the installed languages -- and their ISO-639 two-letter abbreviations. CREATE TABLE maia_languages ( id int unsigned DEFAULT '0' NOT NULL auto_increment, language_name varchar(100) NOT NULL, -- e.g. 'English' abbreviation char(2) NOT NULL, -- e.g. 'en' installed char(1) DEFAULT 'N' NOT NULL, PRIMARY KEY (id), KEY abbreviation (abbreviation) ) TYPE=InnoDB; 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 int unsigned DEFAULT '0' NOT NULL auto_increment, user_name varchar(255) NOT NULL, 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 int unsigned 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 int unsigned 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' PRIMARY KEY (id), KEY user_name (user_name) ) TYPE=InnoDB; 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 int unsigned DEFAULT '0' NOT NULL auto_increment, domain varchar(255) NOT NULL, 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, PRIMARY KEY (id), KEY domain (domain) ) TYPE=InnoDB; 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 int unsigned NOT NULL, -- maia_domains.id admin_id int unsigned NOT NULL, -- maia_users.id PRIMARY KEY (domain_id, admin_id) ) TYPE=InnoDB; -- [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 int unsigned DEFAULT '0' NOT NULL, -- maia_users.id -- suspected ham oldest_suspected_ham_date datetime, newest_suspected_ham_date datetime, smallest_suspected_ham_size int unsigned DEFAULT '0' NOT NULL, largest_suspected_ham_size int unsigned DEFAULT '0' NOT NULL, total_suspected_ham_size int unsigned DEFAULT '0' NOT NULL, lowest_suspected_ham_score float DEFAULT '0.0' NOT NULL, highest_suspected_ham_score float DEFAULT '0.0' NOT NULL, total_suspected_ham_score float DEFAULT '0.0' NOT NULL, total_suspected_ham_items int unsigned DEFAULT '0' NOT NULL, -- ham oldest_ham_date datetime, newest_ham_date datetime, smallest_ham_size int unsigned DEFAULT '0' NOT NULL, largest_ham_size int unsigned DEFAULT '0' NOT NULL, total_ham_size int unsigned DEFAULT '0' NOT NULL, lowest_ham_score float DEFAULT '0.0' NOT NULL, highest_ham_score float DEFAULT '0.0' NOT NULL, total_ham_score float DEFAULT '0.0' NOT NULL, total_ham_items int unsigned DEFAULT '0' NOT NULL, -- mail from whitelisted senders oldest_wl_date datetime, newest_wl_date datetime, smallest_wl_size int unsigned DEFAULT '0' NOT NULL, largest_wl_size int unsigned DEFAULT '0' NOT NULL, total_wl_size int unsigned DEFAULT '0' NOT NULL, total_wl_items int unsigned DEFAULT '0' NOT NULL, -- mail from blacklisted senders oldest_bl_date datetime, newest_bl_date datetime, smallest_bl_size int unsigned DEFAULT '0' NOT NULL, largest_bl_size int unsigned DEFAULT '0' NOT NULL, total_bl_size int unsigned DEFAULT '0' NOT NULL, total_bl_items int unsigned DEFAULT '0' NOT NULL, -- suspected spam oldest_suspected_spam_date datetime, newest_suspected_spam_date datetime, smallest_suspected_spam_size int unsigned DEFAULT '0' NOT NULL, largest_suspected_spam_size int unsigned DEFAULT '0' NOT NULL, total_suspected_spam_size int unsigned DEFAULT '0' NOT NULL, lowest_suspected_spam_score float DEFAULT '0.0' NOT NULL, highest_suspected_spam_score float DEFAULT '0.0' NOT NULL, total_suspected_spam_score float DEFAULT '0.0' NOT NULL, total_suspected_spam_items int unsigned DEFAULT '0' NOT NULL, -- false positives (i.e. rescued ham) oldest_fp_date datetime, newest_fp_date datetime, smallest_fp_size int unsigned DEFAULT '0' NOT NULL, largest_fp_size int unsigned DEFAULT '0' NOT NULL, total_fp_size int unsigned DEFAULT '0' NOT NULL, lowest_fp_score float DEFAULT '0.0' NOT NULL, highest_fp_score float DEFAULT '0.0' NOT NULL, total_fp_score float DEFAULT '0.0' NOT NULL, total_fp_items int unsigned DEFAULT '0' NOT NULL, -- false negatives (i.e. reported spam) oldest_fn_date datetime, newest_fn_date datetime, smallest_fn_size int unsigned DEFAULT '0' NOT NULL, largest_fn_size int unsigned DEFAULT '0' NOT NULL, total_fn_size int unsigned DEFAULT '0' NOT NULL, lowest_fn_score float DEFAULT '0.0' NOT NULL, highest_fn_score float DEFAULT '0.0' NOT NULL, total_fn_score float DEFAULT '0.0' NOT NULL, total_fn_items int unsigned DEFAULT '0' NOT NULL, -- confirmed spam oldest_spam_date datetime, newest_spam_date datetime, smallest_spam_size int unsigned DEFAULT '0' NOT NULL, largest_spam_size int unsigned DEFAULT '0' NOT NULL, total_spam_size int unsigned DEFAULT '0' NOT NULL, lowest_spam_score float DEFAULT '0.0' NOT NULL, highest_spam_score float DEFAULT '0.0' NOT NULL, total_spam_score float DEFAULT '0.0' NOT NULL, total_spam_items int unsigned DEFAULT '0' NOT NULL, -- viruses oldest_virus_date datetime, newest_virus_date datetime, smallest_virus_size int unsigned DEFAULT '0' NOT NULL, largest_virus_size int unsigned DEFAULT '0' NOT NULL, total_virus_size int unsigned DEFAULT '0' NOT NULL, total_virus_items int unsigned DEFAULT '0' NOT NULL, -- mail with invalid headers oldest_bad_header_date datetime, newest_bad_header_date datetime, smallest_bad_header_size int unsigned DEFAULT '0' NOT NULL, largest_bad_header_size int unsigned DEFAULT '0' NOT NULL, total_bad_header_size int unsigned DEFAULT '0' NOT NULL, total_bad_header_items int unsigned DEFAULT '0' NOT NULL, -- mail containing banned file attachments oldest_banned_file_date datetime, newest_banned_file_date datetime, smallest_banned_file_size int unsigned DEFAULT '0' NOT NULL, largest_banned_file_size int unsigned DEFAULT '0' NOT NULL, total_banned_file_size int unsigned DEFAULT '0' NOT NULL, total_banned_file_items int unsigned DEFAULT '0' NOT NULL, -- oversized items oldest_oversized_date datetime, newest_oversized_date datetime, smallest_oversized_size int unsigned DEFAULT '0' NOT NULL, largest_oversized_size int unsigned DEFAULT '0' NOT NULL, total_oversized_size int unsigned DEFAULT '0' NOT NULL, total_oversized_items int unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (user_id) ) TYPE=InnoDB; -- [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 int unsigned DEFAULT '0' NOT NULL auto_increment, received_date datetime NOT NULL, size int unsigned NOT NULL, sender_email varchar(255) NOT NULL, envelope_to text NOT NULL, subject varchar(255) NOT NULL, contents longtext NOT NULL, score float, -- only supplied for (S)pam PRIMARY KEY (id) ) TYPE=InnoDB; 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 int unsigned NOT NULL, -- maia_mail.id recipient_id int unsigned NOT NULL, -- maia_users.id type char(1) NOT NULL, -- 'S', 'V', 'F', 'B', 'H', 'L', 'W' PRIMARY KEY (mail_id, recipient_id) ) TYPE=InnoDB; 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 int unsigned DEFAULT '0' NOT NULL auto_increment, virus_name varchar(255) NOT NULL, count int unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (id), KEY virus_name (virus_name) ) TYPE=InnoDB; 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 int unsigned NOT NULL, -- maia_viruses.id virus_alias varchar(255) NOT NULL, PRIMARY KEY (virus_id, virus_alias) ) TYPE=InnoDB; -- [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 int unsigned NOT NULL, -- maia_mail.id virus_id int unsigned NOT NULL, -- maia_viruses.id PRIMARY KEY (mail_id, virus_id) ) TYPE=InnoDB; -- [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 int unsigned DEFAULT '0' NOT NULL auto_increment, rule_name varchar(255) NOT NULL, rule_description varchar(255) DEFAULT '' NOT NULL, rule_score_0 float DEFAULT '1.0' NOT NULL, rule_score_1 float DEFAULT '1.0' NOT NULL, rule_score_2 float DEFAULT '1.0' NOT NULL, rule_score_3 float DEFAULT '1.0' NOT NULL, rule_count int unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (id), KEY rule_name (rule_name) ) TYPE=InnoDB; 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 int unsigned NOT NULL, -- maia_mail.id rule_id int unsigned NOT NULL, -- maia_sa_rules.id rule_score float default '0.0' NOT NULL, PRIMARY KEY (mail_id, rule_id) ) TYPE=InnoDB; -- [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 int unsigned 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) ) TYPE=InnoDB; -- [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 int unsigned DEFAULT '0' NOT NULL auto_increment, user_id int unsigned NOT NULL, -- maia_users.id type char(1) DEFAULT 'H' NOT NULL, -- 'H', 'D', 'M', 'Y' taken_at datetime NOT NULL, total_ham_items int unsigned DEFAULT '0' NOT NULL, total_ham_size int unsigned DEFAULT '0' NOT NULL, total_spam_items int unsigned DEFAULT '0' NOT NULL, total_spam_size int unsigned DEFAULT '0' NOT NULL, total_virus_items int unsigned DEFAULT '0' NOT NULL, total_virus_size int unsigned DEFAULT '0' NOT NULL, total_fp_items int unsigned DEFAULT '0' NOT NULL, total_fp_size int unsigned DEFAULT '0' NOT NULL, total_fn_items int unsigned DEFAULT '0' NOT NULL, total_fn_size int unsigned DEFAULT '0' NOT NULL, total_banned_file_items int unsigned DEFAULT '0' NOT NULL, total_banned_file_size int unsigned DEFAULT '0' NOT NULL, total_bad_header_items int unsigned DEFAULT '0' NOT NULL, total_bad_header_size int unsigned DEFAULT '0' NOT NULL, total_wl_items int unsigned DEFAULT '0' NOT NULL, total_wl_size int unsigned DEFAULT '0' NOT NULL, total_bl_items int unsigned DEFAULT '0' NOT NULL, total_bl_size int unsigned DEFAULT '0' NOT NULL, total_oversized_items int unsigned DEFAULT '0' NOT NULL, total_oversized_size int unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (id) ) TYPE=InnoDB; -- 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 (user_name, primary_email_id, reminders, discard_ham) VALUES ('@.', 1, 'N', 'Y'); INSERT INTO maia_domains (domain) VALUES ('@.'); -- 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