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;