SET CHARACTER SET latin1;

CREATE DATABASE IF NOT EXISTS `vmailgld` DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci;

USE `vmailgld`;


CREATE TABLE IF NOT EXISTS `blacklist` (
  `_blacklist` char(15) collate latin1_german1_ci NOT NULL default '',
  `_description` char(60) collate latin1_german1_ci NOT NULL default '',
  `_expire` int(10) unsigned NOT NULL default '0',
  UNIQUE KEY `_blacklist` (`_blacklist`),
  KEY `_expire` (`_expire`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;



CREATE TABLE IF NOT EXISTS `blacklist_dnsname` (
  `_blacklist` char(80) collate latin1_german1_ci NOT NULL default '',
  `_description` char(60) collate latin1_german1_ci NOT NULL default '',
  `_expire` int(10) unsigned NOT NULL default '0',
  UNIQUE KEY `_blacklist` (`_blacklist`),
  KEY `_expire` (`_expire`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;



CREATE TABLE IF NOT EXISTS `blacklist_helo` (
  `_helo` char(80) collate latin1_german1_ci NOT NULL default '',
  UNIQUE KEY `_helo` (`_helo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;



CREATE TABLE IF NOT EXISTS `blacklist_sender` (
  `_blacklist` char(80) collate latin1_german1_ci NOT NULL default '',
  `_description` char(60) collate latin1_german1_ci NOT NULL default '',
  `_expire` int(10) unsigned NOT NULL default '0',
  UNIQUE KEY `_blacklist` (`_blacklist`),
  KEY `_expire` (`_expire`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;



CREATE TABLE IF NOT EXISTS `helo` (
  `_host` char(15) collate latin1_german1_ci NOT NULL default '',
  `_helo` char(80) collate latin1_german1_ci NOT NULL default '',
  `_expire` int(10) unsigned NOT NULL default '0',
  UNIQUE KEY `_host` (`_host`,`_helo`),
  KEY `_expire` (`_expire`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;



CREATE TABLE IF NOT EXISTS `policy` (
  `_rcpt` char(80) collate latin1_german1_ci NOT NULL default '',
  `_optin` tinyint(1) unsigned NOT NULL default '1',
  `_priority` tinyint(1) unsigned NOT NULL default '0',
  UNIQUE KEY `_rcpt` (`_rcpt`,`_optin`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;



CREATE TABLE IF NOT EXISTS `policy_training` (
  `_rcpt` char(80) collate latin1_german1_ci NOT NULL default '',
  `_expire` int(10) unsigned NOT NULL default '0',
  UNIQUE KEY `_rcpt` (`_rcpt`),
  KEY `_expire` (`_expire`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;



CREATE TABLE IF NOT EXISTS `spamtrap` (
  `_rcpt` char(80) collate latin1_german1_ci NOT NULL default '',
  `_active` tinyint(1) unsigned NOT NULL default '1',
  UNIQUE KEY `_rcpt` (`_rcpt`,`_active`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;



CREATE TABLE IF NOT EXISTS `throttle` (
  `_from` char(80) collate latin1_german1_ci NOT NULL default '',
  `_count_max` mediumint(8) unsigned NOT NULL default '0',
  `_count_cur` mediumint(8) unsigned NOT NULL default '1',
  `_date` int(10) unsigned NOT NULL default '0',
  `_quota_cur` int(10) unsigned NOT NULL default '0',
  `_quota_max` int(10) unsigned NOT NULL default '0',
  `_time_limit` int(10) unsigned NOT NULL default '0',
  `_mail_size` int(10) unsigned NOT NULL default '0',
  `_count_tot` mediumint(8) unsigned NOT NULL default '1',
  `_rcpt_max` mediumint(8) unsigned NOT NULL default '0',
  `_rcpt_cur` mediumint(8) unsigned NOT NULL default '1',
  `_rcpt_tot` mediumint(8) unsigned NOT NULL default '1',
  `_abuse_cur` int(10) unsigned NOT NULL default '0',
  `_abuse_tot` int(10) unsigned NOT NULL default '0',
  `_log_warn` int(10) unsigned NOT NULL default '0',
  `_log_panic` int(10) unsigned NOT NULL default '0',
  `_priority` tinyint(1) unsigned NOT NULL default '0',
  UNIQUE KEY `_from` (`_from`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;




CREATE TABLE IF NOT EXISTS `throttle_from_instance` (
  `_instance` char(80) collate latin1_german1_ci NOT NULL default '',
  `_from` char(80) collate latin1_german1_ci NOT NULL default '',
  `_expire` int(10) unsigned NOT NULL default '0',
  UNIQUE KEY `_instance` (`_instance`),
  KEY `_expire` (`_expire`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;




CREATE TABLE IF NOT EXISTS `throttle_rcpt` (
  `_rcpt` char(80) collate latin1_german1_ci NOT NULL default '',
  `_count_max` mediumint(8) unsigned NOT NULL default '0',
  `_count_cur` mediumint(8) unsigned NOT NULL default '1',
  `_date` int(10) unsigned NOT NULL default '0',
  `_time_limit` int(10) unsigned NOT NULL default '0',
  `_count_tot` mediumint(8) unsigned NOT NULL default '1',
  `_abuse_cur` int(10) unsigned NOT NULL default '0',
  `_abuse_tot` int(10) unsigned NOT NULL default '0',
  `_log_warn` int(10) unsigned NOT NULL default '0',
  `_log_panic` int(10) unsigned NOT NULL default '0',
  UNIQUE KEY `_rcpt` (`_rcpt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;



CREATE TABLE IF NOT EXISTS `triplet` (
  `_from` char(80) collate latin1_german1_ci NOT NULL default '',
  `_rcpt` char(80) collate latin1_german1_ci NOT NULL default '',
  `_host` char(15) collate latin1_german1_ci NOT NULL default '',
  `_hostname` char(80) collate latin1_german1_ci NOT NULL default '',
  `_datenew` int(10) unsigned NOT NULL default '0',
  `_datelast` int(10) unsigned NOT NULL default '0',
  `_count` smallint(5) unsigned NOT NULL default '0',
  UNIQUE KEY `_host` (`_host`,`_from`,`_rcpt`),
  KEY `_datelast` (`_datelast`),
  KEY `_datenew` (`_datenew`),
  KEY `_ordrcpt` (`_rcpt`,`_from`,`_host`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;



CREATE TABLE IF NOT EXISTS `whitelist` (
  `_whitelist` char(15) collate latin1_german1_ci NOT NULL default '',
  `_description` char(60) collate latin1_german1_ci NOT NULL default '',
  `_expire` int(10) unsigned NOT NULL default '0',
  UNIQUE KEY `_whitelist` (`_whitelist`),
  KEY `_expire` (`_expire`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;



CREATE TABLE IF NOT EXISTS `whitelist_dnsname` (
  `_whitelist` char(80) collate latin1_german1_ci NOT NULL default '',
  `_description` char(60) collate latin1_german1_ci NOT NULL default '',
  `_expire` int(10) unsigned NOT NULL default '0',
  UNIQUE KEY `_whitelist` (`_whitelist`),
  KEY `_expire` (`_expire`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;


CREATE TABLE IF NOT EXISTS `whitelist_sender` (
  `_whitelist` char(80) collate latin1_german1_ci NOT NULL default '',
  `_description` char(60) collate latin1_german1_ci NOT NULL default '',
  `_expire` int(10) unsigned NOT NULL default '0',
  UNIQUE KEY `_whitelist` (`_whitelist`),
  KEY `_expire` (`_expire`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;



LOCK TABLES `whitelist` WRITE;
INSERT IGNORE INTO `whitelist` (`_whitelist`, `_description`, `_expire`) VALUES ('64.233.182.%','gmail Google','0'),
    ('62.206.172.34','eisler','0'),
    ('64.12.137.%','AOL','0'),
    ('64.12.138.%','AOL','0'),
    ('66.135.197.%','eBay','0'),
    ('66.135.209.%','eBay','0'),
    ('66.218.66.%','Yahoo Groups servers (common pool, no retry)','0'),
    ('66.218.67.%','Yahoo Groups servers (common pool, no retry)','0'),
    ('66.218.69.%','Yahoo Groups servers (common pool, no retry)','0'),
    ('81.93.164.102','meltwaternews.com','0'),
    ('83.243.43.53','list.linum-software.biz','0'),
    ('83.243.43.54','mx1.linum.com','0'),
    ('194.25.134.%','mailout.t-online.com','0'),
    ('207.171.168.%','Amazon.com (common pool)','0'),
    ('207.171.180.%','Amazon.com (common pool)','0'),
    ('207.171.187.%','Amazon.com (common pool)','0'),
    ('207.171.188.%','Amazon.com (common pool)','0'),
    ('207.171.190.%','Amazon.com (common pool)','0'),
    ('213.133.99.69','intersales.net','0'),
    ('81.169.163.%','.92 STRATO mout22.kundenservices.net','0'),
    ('151.189.21.%','mail-in-xx.arcor-online.net','0'),
    ('213.174.202.174','easyjet.com','0'),
    ('213.174.202.173','easyjet.com','0'),
    ('35.9.18.11','mdlv.h-net.msu.edu','0'),
    ('209.128.93.229','mail.plimus.com','0');
UNLOCK TABLES;


LOCK TABLES whitelist_dnsname WRITE;
INSERT IGNORE INTO whitelist_dnsname (`_whitelist`, `_description`, `_expire`) VALUES ('%.se','Schweden','0'),
    ('%.fi','Finnland','0'),
    ('%.no','Norwegen','0'),
    ('%.dk','Daenemark','0'),
    ('%.gb','England','0'),
    ('%.nl','Niederlande','0'),
    ('%.be','Belgien','0'),
    ('%.lu','Luxembourg','0'),
    ('%.de','Deutschland','0'),
    ('%.at','Austria','0'),
    ('%.ch','Schweiz','0'),
    ('%.fr','Frankreich','0'),
    ('%.it','Italien','0'),
    ('%.es','Spanien','0'),
    ('%.pt','Portugal','0'),
    ('%.google.com','GMail','0'),
    ('%.gmx.net','GMX','0');
UNLOCK TABLES;

