--
-- Host: localhost    Database: actrasscp
-- ------------------------------------------------------


--
-- Table structure for table `account_traffic`
--

CREATE TABLE `account_traffic` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned DEFAULT NULL,
  `accountID` smallint(5) unsigned NOT NULL DEFAULT '0',
  `date` date NOT NULL,
  `webIn` bigint(20) unsigned NOT NULL DEFAULT '0',
  `webOut` bigint(20) unsigned NOT NULL DEFAULT '0',
  `ftpIn` bigint(20) unsigned NOT NULL DEFAULT '0',
  `ftpOut` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_accountID_date` (`accountID`,`date`),
  KEY `fk_accountTraffic_accountKey` (`accountKey`),
  CONSTRAINT `fk_accountTraffic_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Account traffic';

--
-- Dumping data for table `account_traffic`
--


--
-- Table structure for table `account_traffic_history`
--

CREATE TABLE `account_traffic_history` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned DEFAULT NULL,
  `accountID` smallint(5) unsigned NOT NULL DEFAULT '0',
  `date` date NOT NULL,
  `webIn` bigint(20) unsigned NOT NULL DEFAULT '0',
  `webOut` bigint(20) unsigned NOT NULL DEFAULT '0',
  `ftpIn` bigint(20) unsigned NOT NULL DEFAULT '0',
  `ftpOut` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_accountID_date` (`accountID`,`date`),
  KEY `fk_accountTrafficHistory_accountKey` (`accountKey`),
  CONSTRAINT `fk_accountTrafficHistory_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Account traffic history';

--
-- Dumping data for table `account_traffic_history`
--


--
-- Table structure for table `accounts`
--

CREATE TABLE `accounts` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(25) COLLATE utf8_bin NOT NULL,
  `password` varchar(25) COLLATE utf8_bin NOT NULL,
  `userID` smallint(5) unsigned DEFAULT NULL,
  `groupID` smallint(5) unsigned DEFAULT NULL,
  `shell` enum('none','scp','ssh') COLLATE utf8_bin NOT NULL DEFAULT 'none',
  `quota` mediumint(6) unsigned NOT NULL DEFAULT '250',
  `creationInProgress` tinyint(1) NOT NULL DEFAULT '0',
  `updateInProgress` tinyint(1) NOT NULL DEFAULT '0',
  `deletionInProgress` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Accounts';

--
-- Dumping data for table `accounts`
--


DELIMITER ;;
;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_UPDATE_accounts` BEFORE UPDATE ON `actrasscp`.`accounts`
      FOR EACH ROW BEGIN
            IF OLD.password != NEW.password OR OLD.shell != NEW.shell OR OLD.quota != NEW.quota THEN
                  SET NEW.updateInProgress = 1;
            END IF;
      END */;;
DELIMITER ;
DELIMITER ;;
;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_DELETE_accounts` BEFORE DELETE ON `actrasscp`.`accounts`
      FOR EACH ROW BEGIN
            DELETE FROM `actrasscp`.`account_traffic` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`account_traffic_history` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`domains` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`apache_virtualhosts` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`apache_virtualhost_aliases` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`apache_traffic` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`postfix_domains` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`postfix_mailboxes` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`postfix_aliases` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`yaa_autoresponders` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`spamassassin_userpreferences` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`mysql_databases` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`proftpd_users` WHERE `accountKey` = OLD.id;
            DELETE FROM `actrasscp`.`proftpd_traffic` WHERE `accountKey` = OLD.id;
      END */;;
DELIMITER ;

--
-- Table structure for table `apache_traffic`
--

CREATE TABLE `apache_traffic` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned DEFAULT NULL,
  `domainKey` smallint(5) unsigned DEFAULT NULL,
  `domain` varchar(75) COLLATE utf8_bin DEFAULT NULL,
  `date` datetime NOT NULL,
  `direction` enum('in','out') COLLATE utf8_bin NOT NULL DEFAULT 'in',
  `quantity` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_apacheTraffic_accountKey` (`accountKey`),
  KEY `fk_apacheTraffic_domainKey` (`domainKey`),
  CONSTRAINT `fk_apacheTraffic_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_apacheTraffic_domainKey` FOREIGN KEY (`domainKey`) REFERENCES `domains` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Apache traffic';

--
-- Dumping data for table `apache_traffic`
--


--
-- Table structure for table `apache_virtualhost_aliases`
--

CREATE TABLE `apache_virtualhost_aliases` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned NOT NULL,
  `ssl` tinyint(1) NOT NULL DEFAULT '0',
  `apacheVirtualhostKey` smallint(5) unsigned NOT NULL,
  `domainKey` smallint(5) unsigned NOT NULL,
  `domain` varchar(75) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_ssl_domainKey` (`ssl`,`domainKey`),
  UNIQUE KEY `uidx_ssl_domain` (`ssl`,`domain`),
  KEY `fk_apacheVirtualhostAliases_accountKey` (`accountKey`),
  KEY `fk_apacheVirtualhostAliases_apacheVirtualhostKey` (`apacheVirtualhostKey`),
  KEY `fk_apacheVirtualhostAliases_domainKey` (`domainKey`),
  KEY `fk_apacheVirtualhostAliases_domain` (`domain`),
  CONSTRAINT `fk_apacheVirtualhostAliases_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_apacheVirtualhostAliases_apacheVirtualhostKey` FOREIGN KEY (`apacheVirtualhostKey`) REFERENCES `apache_virtualhosts` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_apacheVirtualhostAliases_domain` FOREIGN KEY (`domain`) REFERENCES `domains` (`domain`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_apacheVirtualhostAliases_domainKey` FOREIGN KEY (`domainKey`) REFERENCES `domains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Apache virtual host aliases';

--
-- Dumping data for table `apache_virtualhost_aliases`
--

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_INSERT_apache_virtualhost_aliases` BEFORE INSERT ON `actrasscp`.`apache_virtualhost_aliases`
      FOR EACH ROW BEGIN
            DELETE FROM `actrasscp`.`apache_virtualhosts` WHERE `accountKey` = NEW.accountKey AND `ssl` = NEW.ssl AND `domainKey` = NEW.domainKey;
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`AFTER_INSERT_apache_virtualhost_aliases` AFTER INSERT ON `actrasscp`.`apache_virtualhost_aliases`
      FOR EACH ROW BEGIN
            INSERT INTO `actrasscp`.`jobs` (`creationDate`, `job`, `parameters`) VALUES (NOW(), 'updateVhost', CONCAT('a:1:{s:2:"id";i:', NEW.apacheVirtualhostKey, ';}'));
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_UPDATE_apache_virtualhost_aliases` BEFORE UPDATE ON `actrasscp`.`apache_virtualhost_aliases`
      FOR EACH ROW BEGIN
            DELETE FROM `actrasscp`.`apache_virtualhosts` WHERE `accountKey` = OLD.accountKey AND `ssl` = OLD.ssl AND `domainKey` = OLD.domainKey;
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`AFTER_UPDATE_apache_virtualhost_aliases` AFTER UPDATE ON `actrasscp`.`apache_virtualhost_aliases`
      FOR EACH ROW BEGIN
            INSERT INTO `actrasscp`.`jobs` (`creationDate`, `job`, `parameters`) VALUES (NOW(), 'updateVhost', CONCAT('a:1:{s:2:"id";i:', OLD.apacheVirtualhostKey, ';}'));
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`AFTER_DELETE_apache_virtualhost_aliases` AFTER DELETE ON `actrasscp`.`apache_virtualhost_aliases`
      FOR EACH ROW BEGIN
	      DECLARE `accountExists` BOOLEAN DEFAULT 0;
	      
	      SELECT IF(COUNT(`id`) > 0, 1, 0) INTO `accountExists` FROM `actrasscp`.`accounts` WHERE `id` = OLD.accountKey AND `deletionInProgress` = 0;
	      
	      IF `accountExists` = 1 THEN
                  INSERT INTO `actrasscp`.`jobs` (`creationDate`, `job`, `parameters`) VALUES (NOW(), 'updateVhost', CONCAT('a:1:{s:2:"id";i:', OLD.apacheVirtualhostKey, ';}'));
            END IF;
      END */;;
DELIMITER ;

--
-- Table structure for table `apache_virtualhosts`
--

CREATE TABLE `apache_virtualhosts` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned NOT NULL,
  `ipKey` smallint(5) unsigned NOT NULL,
  `ssl` tinyint(1) NOT NULL DEFAULT '0',
  `domainKey` smallint(5) unsigned NOT NULL,
  `domain` varchar(75) COLLATE utf8_bin NOT NULL,
  `type` enum('virtualhost','redirect') COLLATE utf8_bin NOT NULL DEFAULT 'virtualhost',
  `redirectTarget` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `redirectCode` enum('301','302','303') COLLATE utf8_bin NOT NULL DEFAULT '301',
  `configuration` longtext COLLATE utf8_bin,
  `sslCertificate` longtext COLLATE utf8_bin,
  `sslKeyfile` longtext COLLATE utf8_bin,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_ssl_domainKey` (`ssl`,`domainKey`),
  UNIQUE KEY `uidx_ssl_domain` (`ssl`,`domain`),
  KEY `fk_apacheVirtualhosts_accountKey` (`accountKey`),
  KEY `fk_apacheVirtualhosts_ipKey` (`ipKey`),
  KEY `fk_apacheVirtualhosts_domainKey` (`domainKey`),
  KEY `fk_apacheVirtualhosts_domain` (`domain`),
  CONSTRAINT `fk_apacheVirtualhosts_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_apacheVirtualhosts_domain` FOREIGN KEY (`domain`) REFERENCES `domains` (`domain`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_apacheVirtualhosts_domainKey` FOREIGN KEY (`domainKey`) REFERENCES `domains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_apacheVirtualhosts_ipKey` FOREIGN KEY (`ipKey`) REFERENCES `ips` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Apache virtual hosts';

--
-- Dumping data for table `apache_virtualhosts`
--

INSERT INTO `apache_virtualhosts` VALUES (1,1,1,0,1,'actra.de','virtualhost',NULL,'301',NULL,NULL,NULL);
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_INSERT_apache_virtualhosts` BEFORE INSERT ON `actrasscp`.`apache_virtualhosts`
      FOR EACH ROW BEGIN
            DELETE FROM `actrasscp`.`apache_virtualhost_aliases` WHERE `accountKey` = NEW.accountKey AND `ssl` = NEW.ssl AND `domainKey` = NEW.domainKey;
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`AFTER_INSERT_apache_virtualhosts` AFTER INSERT ON `actrasscp`.`apache_virtualhosts`
      FOR EACH ROW BEGIN
            INSERT INTO `actrasscp`.`jobs` (`creationDate`, `job`, `parameters`) VALUES (NOW(), 'createVhost', CONCAT('a:1:{s:2:"id";i:', NEW.id, ';}'));
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_UPDATE_apache_virtualhosts` BEFORE UPDATE ON `actrasscp`.`apache_virtualhosts`
      FOR EACH ROW BEGIN
            DELETE FROM `actrasscp`.`apache_virtualhost_aliases` WHERE `accountKey` = OLD.accountKey AND `ssl` = OLD.ssl AND `domainKey` = OLD.domainKey;
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`AFTER_UPDATE_apache_virtualhosts` AFTER UPDATE ON `actrasscp`.`apache_virtualhosts`
      FOR EACH ROW BEGIN
            INSERT INTO `actrasscp`.`jobs` (`creationDate`, `job`, `parameters`) VALUES (NOW(), 'updateVhost', CONCAT('a:1:{s:2:"id";i:', OLD.id, ';}'));
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_DELETE_apache_virtualhosts` BEFORE DELETE ON `actrasscp`.`apache_virtualhosts`
      FOR EACH ROW BEGIN
            DELETE FROM `actrasscp`.`apache_virtualhost_aliases` WHERE `accountKey` = OLD.accountKey AND `apacheVirtualHostKey` = OLD.id;
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`AFTER_DELETE_apache_virtualhosts` AFTER DELETE ON `actrasscp`.`apache_virtualhosts`
      FOR EACH ROW BEGIN
	      DECLARE `accountExists` BOOLEAN DEFAULT 0;
            DECLARE `accountName` VARCHAR(25) BINARY;
            DECLARE `ipAddress` VARCHAR(39) BINARY;
            
            SELECT IF(COUNT(`id`) > 0, 1, 0) INTO `accountExists` FROM `actrasscp`.`accounts` WHERE `id` = OLD.accountKey AND `deletionInProgress` = 0;
            
            IF `accountExists` = 1 THEN
                  SELECT `username` INTO `accountName` FROM `actrasscp`.`accounts` WHERE `id` = OLD.accountKey;
	            SELECT `ip` INTO `ipAddress` FROM `actrasscp`.`ips` WHERE `id` = OLD.ipKey;
	            
	            INSERT INTO `actrasscp`.`jobs` (`creationDate`, `job`, `parameters`) VALUES (NOW(), 'deleteVhost', CONCAT('a:5:{s:2:"id";i:', OLD.id, ';s:3:"ssl";b:',
                        IF(OLD.ssl > 0, 1, 0), ';s:6:"domain";s:', LENGTH(OLD.domain), ':"', OLD.domain, '";s:7:"account";s:', LENGTH(`accountName`), ':"', `accountName`, '";s:2:"ip";s:',
                        LENGTH(`ipAddress`), ':"', `ipAddress`, '"}'));
            END IF;
      END */;;
DELIMITER ;

--
-- Table structure for table `configuration`
--

CREATE TABLE `configuration` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(250) COLLATE utf8_bin NOT NULL,
  `value` text COLLATE utf8_bin,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_key` (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='actra.sscp configuration';

--
-- Dumping data for table `configuration`
--

INSERT INTO `configuration` VALUES (1,'trafficStatisticTotalMonths','12'),(2,'trafficStatisticDetailedMonths','3'),(3,'trafficStatisticApacheDetailDays','14'),(4,'trafficStatisticFTPDetailDays','3'),(5,'apacheVirtualhostTemplate','      ServerAdmin server-admin@%domain%\n      DocumentRoot \"/var/www/%account%/domains/%domain%/html/\"\n      ErrorLog \"/var/www/%account%/domains/%domain%/logfiles/error.log\"\n      LogLevel warn\n      CustomLog \"/var/www/%account%/domains/%domain%/logfiles/access.log\" common\n      \n      <IfModule mod_rewrite.c>\n            RewriteLog \"/var/www/%account%/domains/%domain%/logfiles/rewrite.log\"\n            RewriteLogLevel 0\n      </IfModule>\n      \n      <IfModule mpm_itk_module>\n            AssignUserId %account% %account%\n      </IfModule>\n      \n      <IfModule suexec.c>\n            SuexecUserGroup %account% %account%\n      </IfModule>\n      \n      <IfModule mod_fcgid.c>\n            AddHandler fcgid-script .php\n            Include /etc/apache2/CGIAuth/CGIAuth.conf\n      </IfModule>\n      \n      AddHandler cgi-script .pl .cgi\n      \n      Alias /apacheerrordocs/ \"/var/www/%account%/domains/%domain%/configuration/errordocs/\"\n      <Directory \"/var/www/%account%/domains/%domain%/configuration/errordocs/\">\n            AllowOverride None\n            Options IncludesNoExec\n            AddOutputFilter Includes html\n            AddHandler type-map var\n            Order allow,deny\n            Allow from all\n            LanguagePriority de en cs es fr it nl sv pt-br ro\n            ForceLanguagePriority Prefer Fallback\n      </Directory>\n      \n      <Directory \"/var/www/%account%/domains/%domain%/html/\">\n            Options Indexes FollowSymLinks MultiViews SymLinksIfOwnerMatch\n            Options +ExecCGI\n            \n            <IfModule mod_fcgid.c>\n                  FCGIWrapper \"/var/www/%account%/domains/%domain%/configuration/php/php-fcgi-starter\" .php\n            </IfModule>\n            \n            AllowOverride AuthConfig Indexes Limit FileInfo\n            RLimitCPU 30 60\n            RLimitMEM 134217728 171966464\n            RLimitNPROC 5 10\n            Order allow,deny\n            Allow from all\n            AcceptPathInfo On\n      </Directory>'),(6,'apacheSSLTemplate','      SSLEngine on\n      SSLCipherSuite ALL:!ADH:!EXPORT56:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SSLv2:+EXP:+eNULL\n      SSLCertificateFile /var/www/%account%/domains/%domain%/configuration/ssl/certificate.crt\n      SSLCertificateKeyFile /var/www/%account%/domains/%domain%/configuration/ssl/certificate.key\n      <FilesMatch \".(cgi|pl|shtml|shtm|phtml|phtm|php|php5|php4|py|rb)$\">\n            SSLOptions +StdEnvVars\n      </FilesMatch>\n      BrowserMatch \".*MSIE.*\" nokeepalive ssl-unclean-shutdown downgrade-1.0 force-response-1.0'),(7,'postfixVmailUserID','5000'),(8,'postfixVmailGroupID','5000');

--
-- Table structure for table `domains`
--

CREATE TABLE `domains` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned NOT NULL,
  `domain` varchar(75) COLLATE utf8_bin NOT NULL,
  `useForApache` tinyint(1) NOT NULL DEFAULT '0',
  `useForMail` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_domain` (`domain`),
  KEY `fk_domains_accountKey` (`accountKey`),
  CONSTRAINT `fk_domains_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Domains';

--
-- Dumping data for table `domains`
--

INSERT INTO `domains` VALUES (1,1,'actra.de',1,1);
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`AFTER_INSERT_domains` AFTER INSERT ON `actrasscp`.`domains`
      FOR EACH ROW BEGIN
            IF NEW.useForMail = 1 THEN
                  INSERT INTO `actrasscp`.`postfix_domains` (`accountKey`, `domainKey`, `domain`) VALUES (NEW.accountKey, NEW.id, NEW.domain);
            END IF;
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`AFTER_UPDATE_domains` AFTER UPDATE ON `actrasscp`.`domains`
      FOR EACH ROW BEGIN
            IF OLD.useForApache = 1 AND NEW.useForApache = 0 THEN
                 DELETE FROM `actrasscp`.`apache_virtualhosts` WHERE `accountKey` = OLD.accountKey AND `domainKey` = OLD.id;
                 DELETE FROM `actrasscp`.`apache_virtualhost_aliases` WHERE `accountKey` = OLD.accountKey AND `domainKey` = OLD.id;
            END IF;
            
            IF OLD.useForMail = 1 AND NEW.useForMail = 0 THEN
                  DELETE FROM `actrasscp`.`postfix_domains` WHERE `accountKey` = OLD.accountKey AND `domainKey` = OLD.id;
            END IF;
            
            IF OLD.useForMail = 0 AND NEW.useForMail = 1 THEN
                  INSERT INTO `actrasscp`.`postfix_domains` (`accountKey`, `domainKey`, `domain`) VALUES (NEW.accountKey, NEW.id, NEW.domain);
            END IF;
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_DELETE_domains` BEFORE DELETE ON `actrasscp`.`domains`
      FOR EACH ROW BEGIN
            DELETE FROM `actrasscp`.`apache_virtualhosts` WHERE `accountKey` = OLD.accountKey AND `domainKey` = OLD.id;
            DELETE FROM `actrasscp`.`apache_virtualhost_aliases` WHERE `accountKey` = OLD.accountKey AND `domainKey` = OLD.id;
            DELETE FROM `actrasscp`.`apache_traffic` WHERE `accountKey` = OLD.accountKey AND `domainKey` = OLD.id;
            DELETE FROM `actrasscp`.`postfix_domains` WHERE `accountKey` = OLD.accountKey AND `domainKey` = OLD.id;
      END */;;
DELIMITER ;

--
-- Table structure for table `ips`
--

CREATE TABLE `ips` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(39) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_ip` (`ip`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='IP addresses associated with this server';

--
-- Dumping data for table `ips`
--

INSERT INTO `ips` VALUES (2,'127.0.0.1'),(1,'192.168.65.100');

--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `creationDate` datetime DEFAULT NULL,
  `job` enum('createAccount','updatePassword','updateShell','updateQuota','deleteAccount','createVhost','updateVhost','deleteVhost','createMailbox','deleteMailbox') COLLATE utf8_bin NOT NULL,
  `parameters` text COLLATE utf8_bin NOT NULL,
  `executionDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='actra.sscp jobs to execute';

--
-- Dumping data for table `jobs`
--

INSERT INTO `jobs` VALUES (1,'2010-09-06 23:09:46','createVhost','a:1:{s:2:\"id\";i:1;}',NULL);

--
-- Table structure for table `mysql_databases`
--

CREATE TABLE `mysql_databases` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned NOT NULL,
  `database` varchar(64) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_database` (`database`),
  KEY `fk_mysqlDatabases_accountKey` (`accountKey`),
  CONSTRAINT `fk_mysqlDatabases_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='MySQL user databases';

--
-- Dumping data for table `mysql_databases`
--

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_DELETE_mysql_databases` BEFORE DELETE ON `actrasscp`.`mysql_databases`
      FOR EACH ROW BEGIN
            DELETE FROM `actrasscp`.`mysql_users` WHERE `mysqlDatabaseKey` = OLD.id;
      END */;;
DELIMITER ;

--
-- Table structure for table `mysql_users`
--

CREATE TABLE `mysql_users` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `mysqlDatabaseKey` smallint(5) unsigned NOT NULL,
  `hostname` varchar(60) COLLATE utf8_bin NOT NULL DEFAULT 'localhost',
  `username` varchar(16) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_hostname_username` (`hostname`,`username`),
  KEY `fk_mysqlUsers_mysqlDatabaseKey` (`mysqlDatabaseKey`),
  CONSTRAINT `fk_mysqlUsers_mysqlDatabaseKey` FOREIGN KEY (`mysqlDatabaseKey`) REFERENCES `mysql_databases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='MySQL database users';

--
-- Dumping data for table `mysql_users`
--

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`AFTER_DELETE_mysql_users` AFTER DELETE ON `actrasscp`.`mysql_users`
      FOR EACH ROW BEGIN
            DELETE FROM `mysql`.`user` WHERE `User` = OLD.username AND `Host` = OLD.hostname;
            DELETE FROM `mysql`.`columns_priv` WHERE `User` = OLD.username AND `Host` = OLD.hostname;
            DELETE FROM `mysql`.`db` WHERE `User` = OLD.username AND `Host` = OLD.hostname;
            DELETE FROM `mysql`.`procs_priv` WHERE `User` = OLD.username AND `Host` = OLD.hostname;
            DELETE FROM `mysql`.`tables_priv` WHERE `User` = OLD.username AND `Host` = OLD.hostname;
      END */;;
DELIMITER ;

--
-- Table structure for table `postfix_alias_destinations`
--

CREATE TABLE `postfix_alias_destinations` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `postfixAliasKey` smallint(5) unsigned NOT NULL,
  `type` enum('mailbox','forwarder','autoresponder','file','pipe') COLLATE utf8_bin NOT NULL DEFAULT 'mailbox',
  `destination` varchar(100) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type`),
  KEY `fk_postfixAliasDestinations_postfixAliasKey` (`postfixAliasKey`),
  CONSTRAINT `fk_postfixAliasDestinations_postfixAliasKey` FOREIGN KEY (`postfixAliasKey`) REFERENCES `postfix_aliases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Destinations for the aliases';

--
-- Dumping data for table `postfix_alias_destinations`
--


--
-- Table structure for table `postfix_aliases`
--

CREATE TABLE `postfix_aliases` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned NOT NULL,
  `postfixDomainKey` smallint(5) unsigned NOT NULL,
  `postfixDomain` varchar(75) COLLATE utf8_bin NOT NULL,
  `alias` varchar(75) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_postfixDomainKey_alias` (`postfixDomainKey`,`alias`),
  KEY `fk_postfixAliases_accountKey` (`accountKey`),
  KEY `fk_postfixAliases_postfixDomain` (`postfixDomain`),
  CONSTRAINT `fk_postfixAliases_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_postfixAliases_postfixDomain` FOREIGN KEY (`postfixDomain`) REFERENCES `postfix_domains` (`domain`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_postfixAliases_postfixDomainKey` FOREIGN KEY (`postfixDomainKey`) REFERENCES `postfix_domains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Aliases for the mailboxes ("eMail addresses")';

--
-- Dumping data for table `postfix_aliases`
--

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_DELETE_postfix_aliases` BEFORE DELETE ON `actrasscp`.`postfix_aliases`
      FOR EACH ROW BEGIN
            DELETE FROM `actrasscp`.`postfix_alias_destinations` WHERE `postfixAliasKey` = OLD.id;
            DELETE FROM `actrasscp`.`yaa_autoresponders` WHERE `accountKey` = OLD.accountKey AND `postfixAliasKey` = OLD.id;
            DELETE FROM `actrasscp`.`spamassassin_userpreferences` WHERE `accountKey` = OLD.accountKey AND `postfixAliasKey` = OLD.id;
      END */;;
DELIMITER ;

--
-- Table structure for table `postfix_domains`
--

CREATE TABLE `postfix_domains` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned NOT NULL,
  `domainKey` smallint(5) unsigned NOT NULL,
  `domain` varchar(75) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_domainKey` (`domainKey`),
  UNIQUE KEY `uidx_domain` (`domain`),
  KEY `fk_postfixDomains_accountKey` (`accountKey`),
  CONSTRAINT `fk_postfixDomains_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_postfixDomains_domain` FOREIGN KEY (`domain`) REFERENCES `domains` (`domain`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_postfixDomains_domainKey` FOREIGN KEY (`domainKey`) REFERENCES `domains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Mail domains that Postfix is responsible for';

--
-- Dumping data for table `postfix_domains`
--

INSERT INTO `postfix_domains` VALUES (1,1,1,'actra.de');
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_DELETE_postfix_domains` BEFORE DELETE ON `actrasscp`.`postfix_domains`
      FOR EACH ROW BEGIN
            DELETE FROM `actrasscp`.`postfix_aliases` WHERE `accountKey` = OLD.accountKey AND `postfixDomainKey` = OLD.id;
      END */;;
DELIMITER ;

--
-- Table structure for table `postfix_mailboxes`
--

CREATE TABLE `postfix_mailboxes` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned NOT NULL,
  `mailbox` varchar(50) COLLATE utf8_bin NOT NULL,
  `password` varchar(100) COLLATE utf8_bin NOT NULL,
  `userID` int(10) unsigned NOT NULL,
  `groupID` int(10) unsigned NOT NULL,
  `maildir` varchar(250) COLLATE utf8_bin NOT NULL,
  `quota` mediumint(6) unsigned NOT NULL DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_mailbox` (`mailbox`),
  KEY `fk_postfixMailboxes_accountKey` (`accountKey`),
  CONSTRAINT `fk_postfixMailboxes_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Local POP3/IMAP accounts';

--
-- Dumping data for table `postfix_mailboxes`
--

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_INSERT_postfix_mailboxes` BEFORE INSERT ON `actrasscp`.`postfix_mailboxes`
      FOR EACH ROW BEGIN
            DECLARE `linuxUserID` INT(10);
            DECLARE `linuxGroupID` INT(10);
            
            SELECT CAST(`value` AS UNSIGNED INTEGER) INTO `linuxUserID` FROM `actrasscp`.`configuration` WHERE `key` = 'postfixVmailUserID';
            SELECT CAST(`value` AS UNSIGNED INTEGER) INTO `linuxGroupID` FROM `actrasscp`.`configuration` WHERE `key` = 'postfixVmailGroupID';
            
            SET NEW.userID = `linuxUserID`;
            SET NEW.groupID = `linuxGroupID`;
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`AFTER_INSERT_postfix_mailboxes` AFTER INSERT ON `actrasscp`.`postfix_mailboxes`
      FOR EACH ROW BEGIN
            DECLARE `accountName` VARCHAR(25) BINARY;
            
            SELECT `username` INTO `accountName` FROM `actrasscp`.`accounts` WHERE `id` = NEW.accountKey;
            
            INSERT INTO `actrasscp`.`jobs` (`creationDate`, `job`, `parameters`) VALUES (NOW(), 'createMailbox', CONCAT('a:5:{s:2:"id";i:', NEW.id, ';s:7:"mailbox";s:', LENGTH(NEW.mailbox),
                  ':"', NEW.mailbox, '";s:7:"account";s:', LENGTH(`accountName`), ':"', `accountName`, '";s:3:"uid";i:', NEW.userID, ';s:3:"gid";i:', NEW.groupID, ';}'));
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`BEFORE_DELETE_postfix_mailboxes` BEFORE DELETE ON `actrasscp`.`postfix_mailboxes`
      FOR EACH ROW BEGIN
            DELETE FROM `actrasscp`.`postfix_alias_destinations` WHERE `type` = 'mailbox' AND `destination` = OLD.mailbox;
      END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`asscp`@`localhost`*/ /*!50003 TRIGGER `actrasscp`.`AFTER_DELETE_postfix_mailboxes` AFTER DELETE ON `actrasscp`.`postfix_mailboxes`
      FOR EACH ROW BEGIN
	      DECLARE `accountExists` BOOLEAN DEFAULT 0;
	      DECLARE `accountName` VARCHAR(25) BINARY;
            
            SELECT IF(COUNT(`id`) > 0, 1, 0) INTO `accountExists` FROM `actrasscp`.`accounts` WHERE `id` = OLD.accountKey AND `deletionInProgress` = 0;
            
            IF `accountExists` = 1 THEN
                  SELECT `username` INTO `accountName` FROM `actrasscp`.`accounts` WHERE `id` = OLD.accountKey;
                  
                  INSERT INTO `actrasscp`.`jobs` (`creationDate`, `job`, `parameters`) VALUES (NOW(), 'deleteMailbox', CONCAT('a:3:{s:2:"id";i:', OLD.id, ';s:7:"mailbox";s:',
                        LENGTH(OLD.mailbox), ':"', OLD.mailbox, '";s:7:"account";s:', LENGTH(`accountName`), ':"', `accountName`, '";}'));
            END IF;
      END */;;
DELIMITER ;

--
-- Table structure for table `postfix_transports`
--

CREATE TABLE `postfix_transports` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `domain` varchar(75) COLLATE utf8_bin NOT NULL,
  `destination` varchar(100) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_domain` (`domain`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Transports (mail domains) that are redirected to other SMPTs';

--
-- Dumping data for table `postfix_transports`
--

INSERT INTO `postfix_transports` VALUES (1,'yaa-local-autoreply-domain','yaa');

--
-- Table structure for table `proftpd_traffic`
--

CREATE TABLE `proftpd_traffic` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned DEFAULT NULL,
  `username` varchar(60) COLLATE utf8_bin DEFAULT NULL,
  `date` datetime NOT NULL,
  `direction` enum('in','out') COLLATE utf8_bin NOT NULL DEFAULT 'in',
  `quantity` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_proftpdTraffic_accountKey` (`accountKey`),
  CONSTRAINT `fk_proftpdTraffic_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='FTP traffic';

--
-- Dumping data for table `proftpd_traffic`
--


--
-- Table structure for table `proftpd_users`
--

CREATE TABLE `proftpd_users` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned NOT NULL,
  `userID` smallint(5) unsigned DEFAULT NULL,
  `groupID` smallint(5) unsigned DEFAULT NULL,
  `username` varchar(60) COLLATE utf8_bin NOT NULL,
  `password` char(40) COLLATE utf8_bin NOT NULL,
  `homeDirectory` text COLLATE utf8_bin NOT NULL,
  `shell` varchar(250) COLLATE utf8_bin NOT NULL DEFAULT '/bin/false',
  `loginCounter` int(10) unsigned NOT NULL DEFAULT '0',
  `lastLoginDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_username` (`username`),
  KEY `fk_proftpdUsers_accountKey` (`accountKey`),
  CONSTRAINT `fk_proftpdUsers_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='FTP users';

--
-- Dumping data for table `proftpd_users`
--


--
-- Table structure for table `spamassassin_autowhitelist`
--

CREATE TABLE `spamassassin_autowhitelist` (
  `sender` varchar(250) COLLATE utf8_bin NOT NULL DEFAULT '',
  `recipient` varchar(250) COLLATE utf8_bin NOT NULL DEFAULT '',
  `ip` varchar(39) COLLATE utf8_bin NOT NULL DEFAULT '',
  `count` bigint(20) NOT NULL DEFAULT '0',
  `totalScore` float NOT NULL DEFAULT '0',
  PRIMARY KEY (`sender`,`recipient`,`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='AutoWhitelist (updated by SpamAssassin)';

--
-- Dumping data for table `spamassassin_autowhitelist`
--


--
-- Table structure for table `spamassassin_bayesexpire`
--

CREATE TABLE `spamassassin_bayesexpire` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `runtime` bigint(20) NOT NULL DEFAULT '0',
  KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Bayes expiration';

--
-- Dumping data for table `spamassassin_bayesexpire`
--


--
-- Table structure for table `spamassassin_bayesglobalvariables`
--

CREATE TABLE `spamassassin_bayesglobalvariables` (
  `variable` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '',
  `value` varchar(250) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`variable`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Bayes global settings';

--
-- Dumping data for table `spamassassin_bayesglobalvariables`
--

INSERT INTO `spamassassin_bayesglobalvariables` VALUES ('VERSION','3');

--
-- Table structure for table `spamassassin_bayesseen`
--

CREATE TABLE `spamassassin_bayesseen` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `messageID` varchar(250) COLLATE utf8_bin NOT NULL DEFAULT '',
  `flag` char(1) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`messageID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Bayes "seen"';

--
-- Dumping data for table `spamassassin_bayesseen`
--


--
-- Table structure for table `spamassassin_bayestoken`
--

CREATE TABLE `spamassassin_bayestoken` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `token` char(5) COLLATE utf8_bin NOT NULL DEFAULT '',
  `spamCount` bigint(20) NOT NULL DEFAULT '0',
  `hamCount` bigint(20) NOT NULL DEFAULT '0',
  `accessTime` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`token`),
  KEY `idx_token` (`token`),
  KEY `idx_id_accessTime` (`id`,`accessTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Bayes tokens';

--
-- Dumping data for table `spamassassin_bayestoken`
--


--
-- Table structure for table `spamassassin_bayesvars`
--

CREATE TABLE `spamassassin_bayesvars` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(250) COLLATE utf8_bin NOT NULL DEFAULT '',
  `spamCount` bigint(20) NOT NULL DEFAULT '0',
  `hamCount` bigint(20) NOT NULL DEFAULT '0',
  `tokenCount` bigint(20) NOT NULL DEFAULT '0',
  `lastExpire` bigint(20) NOT NULL DEFAULT '0',
  `lastAccessTimeDelta` bigint(20) NOT NULL DEFAULT '0',
  `lastExpireReduce` bigint(20) NOT NULL DEFAULT '0',
  `oldestTokenAge` bigint(20) NOT NULL DEFAULT '2147483647',
  `newestTokenAge` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Bayes vars';

--
-- Dumping data for table `spamassassin_bayesvars`
--


--
-- Table structure for table `spamassassin_userpreferences`
--

CREATE TABLE `spamassassin_userpreferences` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned DEFAULT NULL,
  `postfixAliasKey` smallint(5) unsigned DEFAULT NULL,
  `username` varchar(250) COLLATE utf8_bin NOT NULL,
  `preference` varchar(250) COLLATE utf8_bin NOT NULL,
  `value` varchar(250) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `fk_spamassassinUserprefs_accountKey` (`accountKey`),
  KEY `fk_spamassassinUserprefs_postfixAliasKey` (`postfixAliasKey`),
  CONSTRAINT `fk_spamassassinUserprefs_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_spamassassinUserprefs_postfixAliasKey` FOREIGN KEY (`postfixAliasKey`) REFERENCES `postfix_aliases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User preferences';

--
-- Dumping data for table `spamassassin_userpreferences`
--

INSERT INTO `spamassassin_userpreferences` VALUES (1,NULL,NULL,'$GLOBAL','required_hits','5.00'),(2,NULL,NULL,'$GLOBAL','rewrite_header subject','[spam? _HITS_]   '),(3,NULL,NULL,'$GLOBAL','subject_tag','[spam? _HITS_]   '),(4,NULL,NULL,'$GLOBAL','add_header all','Status _YESNO_; score=_SCORE_; required=_REQD_; bayes=_BAYES_; tests=_TESTSSCORES(,)_'),(5,NULL,NULL,'$GLOBAL','report_safe','1'),(6,NULL,NULL,'$GLOBAL','use_bayes','1'),(7,NULL,NULL,'$GLOBAL','use_bayes_rules','1'),(8,NULL,NULL,'$GLOBAL','bayes_auto_learn','1'),(9,NULL,NULL,'$GLOBAL','skip_rbl_checks','0'),(10,NULL,NULL,'$GLOBAL','use_razor2','1'),(11,NULL,NULL,'$GLOBAL','use_pyzor','1'),(12,NULL,NULL,'$GLOBAL','use_auto_whitelist','1'),(13,NULL,NULL,'$GLOBAL','score USER_IN_WHITELIST','-10'),(14,NULL,NULL,'$GLOBAL','score USER_IN_BLACKLIST','10'),(15,NULL,NULL,'$GLOBAL','ok_locales','all'),(16,NULL,NULL,'$GLOBAL','ok_languages','all');

--
-- Table structure for table `yaa_autoresponders`
--

CREATE TABLE `yaa_autoresponders` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `accountKey` smallint(5) unsigned NOT NULL,
  `postfixAliasKey` smallint(5) unsigned NOT NULL,
  `postfixAlias` varchar(160) COLLATE utf8_bin NOT NULL,
  `autorespondAlias` varchar(250) COLLATE utf8_bin NOT NULL,
  `senderName` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `sender` varchar(210) COLLATE utf8_bin NOT NULL,
  `localDomains` varchar(250) COLLATE utf8_bin NOT NULL DEFAULT 'yaa-local-autoreply-domain',
  `forward` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `startDate` datetime DEFAULT NULL,
  `endDate` datetime DEFAULT NULL,
  `subject` varchar(100) COLLATE utf8_bin NOT NULL,
  `message` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_yaaAutoresponders_accountKey` (`accountKey`),
  KEY `fk_yaaAutoresponders_postfixAliasKey` (`postfixAliasKey`),
  CONSTRAINT `fk_yaaAutoresponders_accountKey` FOREIGN KEY (`accountKey`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_yaaAutoresponders_postfixAliasKey` FOREIGN KEY (`postfixAliasKey`) REFERENCES `postfix_aliases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Yaa! autoresponders';

--
-- Dumping data for table `yaa_autoresponders`
--


--
-- Dumping routines for database 'actrasscp'
--


