Create.vmail.sql

<source lang="mysql"> 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;

</source> 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