123456789101112131415161718192021222324252627282930 |
- -- CREATE TABLES IN postfix.sql FIRST
-
- -- TEST domain: expect `1 = 1`
- SELECT 1 FROM `mailserver`.`virtual_domains`
- WHERE `virtual_domains`.`name`='domain.tld';
- -- TEST user: expext `1 = 1`
- SELECT 1 FROM `mailserver`.`virtual_users`
- INNER JOIN `mailserver`.`virtual_domains`
- ON `virtual_domains`.`id` = `virtual_users`.`id`
- AND `virtual_domains`.`name` = SUBSTRING_INDEX('user@domain.tld', '@', -1)
- WHERE `virtual_users`.`username` = SUBSTRING_INDEX('user@domain.tld', '@', 1);
- -- TEST alias: expext `destination = user@domain.tld`
- SELECT CONCAT(`virtual_users`.`username`, '@', `virtual_domains`.`name`) as destination
- FROM `mailserver`.`virtual_aliases`
- INNER JOIN `mailserver`.`virtual_users`
- ON `virtual_users`.`id` = `virtual_aliases`.`user_id`
- INNER JOIN `mailserver`.`virtual_domains`
- ON `virtual_domains`.`id` = `virtual_users`.`domain_id`
- AND `virtual_domains`.`name` = SUBSTRING_INDEX('alias@domain.tld', '@', -1)
- WHERE `virtual_aliases`.`alias` = SUBSTRING_INDEX('alias@domain.tld', '@', 1);
- -- TEST email 2 email: expect `email = user@domain.tld`
- SELECT CONCAT(`virtual_users`.`username`, '@', `virtual_domains`.`name`) as email
- FROM `mailserver`.`virtual_users`
- INNER JOIN `mailserver`.`virtual_domains`
- ON `virtual_domains`.`id` = `virtual_users`.`domain_id`
- AND `virtual_domains`.`name` = SUBSTRING_INDEX('user@domain.tld', '@', -1)
- WHERE `virtual_users`.`username` = SUBSTRING_INDEX('user@domain.tld', '@', 1);
|