-- -----------------------------------------------------------------------------
-- Management for Virtual Domains for ISP / PostFix Management
--
-- (C) 2009 Matthias Nott, SAP
--
-- -----------------------------------------------------------------------------
-- This script uses a table mail_domain_mappings that is defined as
--
-- +--------+--------------+------+-----+---------+-------+
-- | Field | Type | Null | Key | Default | Extra |
-- +--------+--------------+------+-----+---------+-------+
-- | domain | varchar(255) | NO | | NULL | |
-- | alias | varchar(255) | NO | | NULL | |
-- +--------+--------------+------+-----+---------+-------+
--
-- While ISP manages its mail addresses through the mail_user table, we
-- want to avoid having to manage userx@domaina if the same user exists
-- as userx@domainb. We enter domaina in the above table into the first
-- column (domain), and a matching alias domain into the second column.
-- domaina can exist multiple times, so that we can more than just one
-- alias domain.
--
-- The stored procedure addVirtualDomains adds, to the mail_user table,
-- records for the virtual domains for all users that are already there
-- and of which the domains are defined in the domain column above.
--
-- The stored procedure deleteVirtualDomains removes all of these users
-- and at the same time cleans up all virtual mappings that are not any
-- more needed as the master record (userx@domaina) was deleted in ISP.
--
-- Also, to make this work we map all of
--
-- /var/vmail/domainb -> /var/vmail/domaina
--
-- using symbolic links.
-- -----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS deleteVirtualDomains;
DELIMITER /
CREATE PROCEDURE deleteVirtualDomains()
BEGIN
delete from mail_user where substr(email, locate('@', email)+1) not in (select domain from mail_domain_mappings);
END
/
DELIMITER ;
DROP PROCEDURE IF EXISTS addVirtualDomains;
DELIMITER /
CREATE PROCEDURE addVirtualDomains ()
READS SQL DATA
BEGIN
-- ---------------------------------------------------------------------------
-- Declare our variables
-- ---------------------------------------------------------------------------
DECLARE v_done int DEFAULT FALSE; -- Loop Exit Handler
DECLARE v_domain varchar(255); -- Current virtual domain
DECLARE v_user varchar(255); -- Current virtual user
DECLARE v_adomain varchar(255); -- Current alias domain
DECLARE v_auser varchar(255); -- Current alias user
DECLARE v_test varchar(255); -- Test whether alias exists
-- ---------------------------------------------------------------------------
-- Declare variables we're going to duplicate
-- ---------------------------------------------------------------------------
DECLARE v_mailuser_id int(11);
DECLARE v_sys_groupid int(11);
DECLARE v_sys_perm_user varchar(5);
DECLARE v_sys_perm_group varchar(5);
DECLARE v_sys_perm_other varchar(5);
DECLARE v_server_id int(11);
DECLARE v_password varchar(255);
DECLARE v_name varchar(128);
DECLARE v_uid int(10) unsigned;
DECLARE v_gid int(10) unsigned;
DECLARE v_maildir varchar(255);
DECLARE v_quota int(11);
DECLARE v_homedir varchar(255);
DECLARE v_autoresponder char(1);
DECLARE v_autoresponder_text tinytext;
DECLARE v_custom_mailfilter text;
DECLARE v_postfix char(1);
DECLARE v_access char(1);
DECLARE v_disableimap char(1);
DECLARE v_disablepop3 char(1);
-- ---------------------------------------------------------------------------
-- Declare our cursors
-- ---------------------------------------------------------------------------
DECLARE c_vdomain CURSOR FOR select distinct domain from mail_domain_mappings;
DECLARE c_vuser CURSOR FOR
select
mailuser_id,
sys_groupid,
sys_perm_user,
sys_perm_group,
sys_perm_other,
server_id,
substr(email, 1, locate('@', email)-1),
password,
name,
uid,
gid,
maildir,
quota,
homedir,
autoresponder,
autoresponder_text,
custom_mailfilter,
postfix,
access,
disableimap,
disablepop3
from mail_user where substr(email, locate('@', email)+1) = v_domain;
DECLARE c_adomain CURSOR FOR select alias from mail_domain_mappings where domain = v_domain;
DECLARE c_auser CURSOR FOR select email from mail_user where email = concat(concat(v_user, '@'), v_adomain);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done := TRUE;
-- ---------------------------------------------------------------------------
-- Loop over all domains
-- ---------------------------------------------------------------------------
OPEN c_vdomain; l_cvdomain: LOOP
FETCH c_vdomain INTO v_domain;
IF v_done THEN CLOSE c_vdomain; SET v_done := false; LEAVE l_cvdomain; END IF;
-- -------------------------------------------------------------------------
-- For each v_domain, loop over all users for that domain
-- -------------------------------------------------------------------------
OPEN c_vuser; l_cvuser: LOOP
FETCH c_vuser into
v_mailuser_id,
v_sys_groupid,
v_sys_perm_user,
v_sys_perm_group,
v_sys_perm_other,
v_server_id,
v_user,
v_password,
v_name,
v_uid,
v_gid,
v_maildir,
v_quota,
v_homedir,
v_autoresponder,
v_autoresponder_text,
v_custom_mailfilter,
v_postfix,
v_access,
v_disableimap,
v_disablepop3;
IF v_done THEN CLOSE c_vuser; SET v_done := FALSE; LEAVE l_cvuser; END IF;
-- -----------------------------------------------------------------------
-- For each v_user, loop over all alias domains
-- -----------------------------------------------------------------------
OPEN c_adomain; l_cadomain: LOOP
FETCH c_adomain into v_adomain;
IF v_done THEN CLOSE c_adomain; SET v_done := FALSE; LEAVE l_cadomain; END IF;
-- ---------------------------------------------------------------------
-- For each alias domain, see if alias user it is already registered
-- ---------------------------------------------------------------------
select email into v_test from mail_user where email = concat(concat(v_user, '@'), v_adomain);
IF v_done THEN SET v_done := FALSE; set v_test := null; END IF;
-- ---------------------------------------------------------------------
-- If we are missing a user entry, we add it
-- ---------------------------------------------------------------------
IF v_test is null THEN
-- select concat(concat(v_user, '@'), v_adomain);
insert into mail_user (
sys_groupid,
sys_perm_user,
sys_perm_group,
sys_perm_other,
server_id,
email,
password,
name,
uid,
gid,
maildir,
quota,
homedir,
autoresponder,
autoresponder_text,
custom_mailfilter,
postfix,
access,
disableimap,
disablepop3
) values (
v_sys_groupid,
v_sys_perm_user,
v_sys_perm_group,
v_sys_perm_other,
v_server_id,
concat(concat(v_user, '@'), v_adomain),
v_password,
v_name,
v_uid,
v_gid,
v_maildir,
v_quota,
v_homedir,
v_autoresponder,
v_autoresponder_text,
v_custom_mailfilter,
v_postfix,
v_access,
v_disableimap,
v_disablepop3
);
END IF;
END LOOP l_cadomain;
END LOOP l_cvuser;
END LOOP l_cvdomain;
END;
/
DELIMITER ;
call deleteVirtualDomains;
call addVirtualDomains;
|
