SQL structure changes to `vmail.domain`

Issue #60 new
Zhang Huangbin
repo owner created an issue

SQL structure changes to vmail.alias_domain and vmail.domain.

  • Drop vmail.alias_domain, merge its records to vmail.domain.
  • vmail.alias_domain just stores alias domain names, so we can extend vmail.domain to store alias domain names. Proposal:
ALTER TABLE domain ADD COLUMN is_alias TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE domain ADD COLUMN alias_to VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE domain ADD INDEX (alias_to);
CREATE UNIQUE INDEX domain_is_alias ON domain (domain, isalias);

This way we don't need slow SQL queries like this one:

# Part of file: /etc/postfix/mysql/virtual_mailbox_domains.cf

query       = SELECT domain FROM domain WHERE domain='%s' AND backupmx=0 AND active=1 UNION SELECT alias_domain.alias_domain FROM alias_domain,domain WHERE alias_domain.alias_domain='%s' AND alias_domain.active=1 AND alias_domain.target_domain=domain.domain AND domain.active=1 AND domain.backupmx=0

After merged vmail.alias_domain to vmail.domain, we don't need UNION SELECT in above query file. The new query should be:

query       = SELECT domain FROM domain WHERE domain='%s' AND backupmx=0 AND active=1

Comments (1)

  1. Log in to comment