upd_dev_collection.sql

-- create mail_relay_domain and load with current domains from mail_transport table
CREATE TABLE IF NOT EXISTS `mail_relay_domain` (
	  `relay_domain_id` bigint(20) NOT NULL AUTO_INCREMENT,
	  `sys_userid` int(11) NOT NULL DEFAULT '0',
	  `sys_groupid` int(11) NOT NULL DEFAULT '0',
	  `sys_perm_user` varchar(5) DEFAULT NULL,
	  `sys_perm_group` varchar(5) DEFAULT NULL,
	  `sys_perm_other` varchar(5) DEFAULT NULL,
	  `server_id` int(11) NOT NULL DEFAULT '0',
	  `domain` varchar(255) DEFAULT NULL,
	  `access` varchar(255) NOT NULL DEFAULT 'OK',
	  `active` varchar(255) NOT NULL DEFAULT 'y',
	  PRIMARY KEY (`relay_domain_id`),
	  UNIQUE KEY `domain` (`domain`, `server_id`)
	) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `mail_relay_domain` SELECT NULL, `sys_userid`, `sys_groupid`, `sys_perm_user`, `sys_perm_group`, `sys_perm_other`, `server_id`, `domain`, 'OK', `active` FROM `mail_transport` WHERE `domain` NOT LIKE '%@%' AND `domain` LIKE '%.%' GROUP BY `domain`, `server_id`;

ALTER TABLE `dns_soa` ADD `rendered_zone` MEDIUMTEXT NULL AFTER `dnssec_info`;

ALTER TABLE `sys_user` ADD `otp_enabled` SET('n', 'y','v') NOT NULL DEFAULT 'n' COMMENT 'v=waiting for validation of the chosen otp method' AFTER `lost_password_reqtime`, ADD `otp_type` SET('email') NOT NULL DEFAULT 'email' AFTER `otp_enabled`, ADD `otp_data` VARCHAR(255) NULL AFTER `otp_type`, ADD `otp_recovery` VARCHAR(64) NULL AFTER `otp_data`, ADD `otp_attempts` TINYINT NOT NULL DEFAULT '0' AFTER `otp_recovery`;