USE `vokesscp`;


##############################################################################################################################################################################################
# IPTables                                                                                                                                                                                   #
##############################################################################################################################################################################################


CREATE TABLE IF NOT EXISTS `vokesscp`.`iptables` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ipAddress`                             VARCHAR(39) BINARY NOT NULL,
    `blockDate`                             DATETIME NOT NULL,
    `type`                                  ENUM('manual', 'fail2ban') NOT NULL DEFAULT 'manual',
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_ipAddress` (`ipAddress`)
) ENGINE=InnoDB COMMENT='IPTables' CHARACTER SET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_INSERT_iptables`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_INSERT_iptables` AFTER INSERT ON `vokesscp`.`iptables`
    FOR EACH ROW BEGIN
        CALL `vokesscp`.`job_blockIp` (
            NEW.`id`,
            NEW.`ipAddress`
        );
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_UPDATE_iptables`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_UPDATE_iptables` AFTER UPDATE ON `vokesscp`.`iptables`
    FOR EACH ROW BEGIN
        CALL `vokesscp`.`job_blockIpUpdate` (
            NEW.`id`,
            NEW.`ipAddress`
        );
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_DELETE_iptables`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_DELETE_iptables` AFTER DELETE ON `vokesscp`.`iptables`
    FOR EACH ROW BEGIN
        CALL `vokesscp`.`job_unblockIp` (
            OLD.`id`,
            OLD.`ipAddress`
        );
    END;
|
DELIMITER ;

