USE `vokesscp`;


##############################################################################################################################################################################################
# MySQL                                                                                                                                                                                      #
##############################################################################################################################################################################################


CREATE TABLE IF NOT EXISTS `vokesscp`.`mysql_domains` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED NOT NULL,
    `customerIpToAccountKey`                SMALLINT(5) UNSIGNED NOT NULL,
    `sslEnabled`                            ENUM('false', 'true') NOT NULL DEFAULT 'false',
    `sslCertificate`                        LONGTEXT BINARY DEFAULT NULL,
    `sslKeyfile`                            LONGTEXT BINARY DEFAULT NULL,
    `subdomainKey`                          SMALLINT(5) UNSIGNED NOT NULL,
    `fullDomain`                            VARCHAR(255) BINARY NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_sslEnabled_subdomainKey` (`sslEnabled`, `subdomainKey`),
    UNIQUE INDEX `uidx_sslEnabled_fullDomain` (`sslEnabled`, `fullDomain`),
    CONSTRAINT `fk_mysqlDomains_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_mysqlDomains_customerIpToAccountKey` FOREIGN KEY (`customerIpToAccountKey`)
        REFERENCES `vokesscp`.`customer_ips_to_accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT `fk_mysqlDomains_subdomainKey` FOREIGN KEY (`subdomainKey`)
        REFERENCES `vokesscp`.`subdomains` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_mysqlDomains_fullDomain` FOREIGN KEY (`fullDomain`)
        REFERENCES `vokesscp`.`subdomains` (`fullDomain`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='MySQL user databases' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`mysql_databases` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED NOT NULL,
    `database`                              VARCHAR(64) BINARY NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_database` (`database`),
    CONSTRAINT `fk_mysqlDatabases_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='MySQL user databases' CHARACTER SET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP TRIGGER IF EXISTS `vokesscp`.`BEFORE_DELETE_mysql_databases`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`BEFORE_DELETE_mysql_databases` BEFORE DELETE ON `vokesscp`.`mysql_databases`
    FOR EACH ROW BEGIN
        DELETE FROM `vokesscp`.`mysql_users` WHERE `mysqlDatabaseKey` = OLD.`id`;
    END;
|
DELIMITER ;


CREATE TABLE IF NOT EXISTS `vokesscp`.`mysql_users` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `mysqlDatabaseKey`                      SMALLINT(5) UNSIGNED NOT NULL,
    `hostname`                              VARCHAR(255) BINARY NOT NULL DEFAULT 'localhost',
    `username`                              VARCHAR(16) BINARY NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_hostname_mysqlDatabaseKey_username` (`hostname`, `mysqlDatabaseKey`, `username`),
    CONSTRAINT `fk_mysqlUsers_mysqlDatabaseKey` FOREIGN KEY (`mysqlDatabaseKey`)
        REFERENCES `vokesscp`.`mysql_databases` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='MySQL database users' CHARACTER SET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_DELETE_mysql_users`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_DELETE_mysql_users` AFTER DELETE ON `vokesscp`.`mysql_users`
    FOR EACH ROW BEGIN
        DELETE FROM `mysql`.`user` WHERE `User` = OLD.`username` AND `Host` = OLD.`hostname`;
        DELETE FROM `mysql`.`columns_priv` WHERE `User` = OLD.`username` AND `Host` = OLD.`hostname`;
        DELETE FROM `mysql`.`db` WHERE `User` = OLD.`username` AND `Host` = OLD.`hostname`;
        DELETE FROM `mysql`.`procs_priv` WHERE `User` = OLD.`username` AND `Host` = OLD.`hostname`;
        DELETE FROM `mysql`.`tables_priv` WHERE `User` = OLD.`username` AND `Host` = OLD.`hostname`;
    END;
|
DELIMITER ;

