Create.vmail.sql

USE mysql;

REPLACE INTO user (host, user, password)
    VALUES (
        'localhost',
        'vmail_admin',
        PASSWORD('SuperSecret')
);

REPLACE INTO db (host, db, user, select_priv)
    VALUES (
        'localhost',
        'vmail',
        'vmail_admin',
        'Y'
);

-- Make sure that priviliges are reloaded.
FLUSH PRIVILEGES;

-- You should drop a pre-existing "vmail"
-- database manually to avoid CREATE errors.
CREATE DATABASE vmail;

USE vmail;

CREATE TABLE virtual_domains (
    id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    vdomain       VARCHAR(50) NOT NULL
    ) ENGINE = InnoDB;

CREATE TABLE relaydomains (
    id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    relaydomain   VARCHAR(80) NOT NULL,
    transport     VARCHAR(80) NOT NULL
    ) ENGINE = InnoDB;

CREATE TABLE virtual_users (
    id            INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    domain_id     INT(11) NOT NULL,
    user          VARCHAR(40) NOT NULL,
    passwd        VARCHAR(32) NOT NULL
    ) ENGINE = InnoDB;

CREATE TABLE virtual_aliases (
    id            INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    domain_id     INT(11) NOT NULL,
    source        VARCHAR(80) NOT NULL,
    destination   VARCHAR(80) NOT NULL,
    FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
    ) ENGINE = InnoDB;

FLUSH PRIVILEGES;

CREATE VIEW view_users AS
    SELECT CONCAT(virtual_users.user, '@', virtual_domains.vdomain)
    AS email, virtual_users.passwd FROM virtual_users
    LEFT JOIN virtual_domains ON virtual_users.domain_id=virtual_domains.id;

CREATE VIEW view_aliases AS
    SELECT CONCAT(virtual_aliases.source, '@', virtual_domains.vdomain)
    AS email, destination FROM virtual_aliases
    LEFT JOIN virtual_domains ON virtual_aliases.domain_id=virtual_domains.id;

Using this script to create the vmail database should result in the following database (log into mysql as a root user, and type the commands shown after mysql> to check):

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| vmail              |
+--------------------+
3 rows in set

mysql> use vmail;

Database changed
mysql> show tables;
+-----------------+
| Tables_in_vmail |
+-----------------+
| relaydomains    |
| view_aliases    |
| view_users      |
| virtual_aliases |
| virtual_domains |
| virtual_users   |
+-----------------+
6 rows in set

mysql> describe relaydomains;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| relaydomain | varchar(80) | NO   |     | NULL    |                |
| transport   | varchar(80) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
3 rows in set

mysql> describe view_aliases;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| email       | varchar(131) | YES  |     | NULL    |       |
| destination | varchar(80)  | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
2 rows in set

mysql> describe view_users;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| email  | varchar(91) | YES  |     | NULL    |       |
| passwd | varchar(32) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set 

mysql> describe virtual_aliases;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| domain_id   | int(11)     | NO   | MUL | NULL    |                |
| source      | varchar(80) | NO   |     | NULL    |                |
| destination | varchar(80) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
4 rows in set

mysql> describe virtual_domains;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| vdomain | varchar(50) | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
2 rows in set

mysql> describe virtual_users;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| domain_id | int(11)     | NO   |     | NULL    |                |
| user      | varchar(40) | NO   |     | NULL    |                |
| passwd    | varchar(32) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set