postfix-test.sql 1.4 KB

123456789101112131415161718192021222324252627282930
  1. -- CREATE TABLES IN postfix.sql FIRST
  2. -- TEST domain: expect `1 = 1`
  3. SELECT 1 FROM `mailserver`.`virtual_domains`
  4. WHERE `virtual_domains`.`name`='domain.tld';
  5. -- TEST user: expext `1 = 1`
  6. SELECT 1 FROM `mailserver`.`virtual_users`
  7. INNER JOIN `mailserver`.`virtual_domains`
  8. ON `virtual_domains`.`id` = `virtual_users`.`id`
  9. AND `virtual_domains`.`name` = SUBSTRING_INDEX('user@domain.tld', '@', -1)
  10. WHERE `virtual_users`.`username` = SUBSTRING_INDEX('user@domain.tld', '@', 1);
  11. -- TEST alias: expext `destination = user@domain.tld`
  12. SELECT CONCAT(`virtual_users`.`username`, '@', `virtual_domains`.`name`) as destination
  13. FROM `mailserver`.`virtual_aliases`
  14. INNER JOIN `mailserver`.`virtual_users`
  15. ON `virtual_users`.`id` = `virtual_aliases`.`user_id`
  16. INNER JOIN `mailserver`.`virtual_domains`
  17. ON `virtual_domains`.`id` = `virtual_users`.`domain_id`
  18. AND `virtual_domains`.`name` = SUBSTRING_INDEX('alias@domain.tld', '@', -1)
  19. WHERE `virtual_aliases`.`alias` = SUBSTRING_INDEX('alias@domain.tld', '@', 1);
  20. -- TEST email 2 email: expect `email = user@domain.tld`
  21. SELECT CONCAT(`virtual_users`.`username`, '@', `virtual_domains`.`name`) as email
  22. FROM `mailserver`.`virtual_users`
  23. INNER JOIN `mailserver`.`virtual_domains`
  24. ON `virtual_domains`.`id` = `virtual_users`.`domain_id`
  25. AND `virtual_domains`.`name` = SUBSTRING_INDEX('user@domain.tld', '@', -1)
  26. WHERE `virtual_users`.`username` = SUBSTRING_INDEX('user@domain.tld', '@', 1);