-- MySQL Script generated by MySQL Workbench
-- Fri Jul 12 12:36:45 2019
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema ac_advert
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table `advert`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `advert` ;
CREATE TABLE IF NOT EXISTS `advert` (
`adv_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`msg_type` INT NOT NULL,
`msg_text` VARCHAR(512) NOT NULL,
`date_from` DATETIME NULL DEFAULT NULL,
`date_to` DATETIME NULL DEFAULT NULL,
`hours` VARCHAR(64) NULL DEFAULT '0-24;',
`is_vip` TINYINT NULL DEFAULT 0,
`admin_flags` VARCHAR(64) NULL DEFAULT NULL,
`views` INT NULL DEFAULT -1,
`day_of_week` VARCHAR(64) NULL DEFAULT '1-7;',
`show` TINYINT NULL DEFAULT 1,
`order` INT NULL DEFAULT 1000,
PRIMARY KEY (`adv_id`),
UNIQUE INDEX `adv_id_UNIQUE` (`adv_id` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `hud_style`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `hud_style` ;
CREATE TABLE IF NOT EXISTS `hud_style` (
`adv_id` INT UNSIGNED NOT NULL,
`color1` VARCHAR(24) NULL DEFAULT '255 255 255 255',
`color2` VARCHAR(24) NULL DEFAULT '255 255 255 255',
`effect` INT NULL DEFAULT 1,
`fadein` FLOAT NULL DEFAULT 0.1,
`fadeout` FLOAT NULL DEFAULT 0.1,
`holdtime` FLOAT NULL DEFAULT 10,
`x` FLOAT NULL DEFAULT 0.5,
`y` FLOAT NULL DEFAULT 0.5,
`fxtime` FLOAT NULL DEFAULT 0.1,
PRIMARY KEY (`adv_id`),
UNIQUE INDEX `style_id_UNIQUE` (`adv_id` ASC),
CONSTRAINT `hud_style_adv_id`
FOREIGN KEY (`adv_id`)
REFERENCES `advert` (`adv_id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `magic_words`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `magic_words` ;
CREATE TABLE IF NOT EXISTS `magic_words` (
`word_id` INT NOT NULL AUTO_INCREMENT,
`key` VARCHAR(64) NOT NULL,
`value` VARCHAR(256) NOT NULL,
PRIMARY KEY (`word_id`),
UNIQUE INDEX `word_id_UNIQUE` (`word_id` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `server_ads`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `server_ads` ;
CREATE TABLE IF NOT EXISTS `server_ads` (
`srv_id` INT UNSIGNED NOT NULL,
`adv_id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`srv_id`, `adv_id`),
INDEX `server_ads_adv_id_idx` (`adv_id` ASC),
CONSTRAINT `server_ads_srv_id`
FOREIGN KEY (`srv_id`)
REFERENCES `servers` (`srv_id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `server_ads_adv_id`
FOREIGN KEY (`adv_id`)
REFERENCES `advert` (`adv_id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `servers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `servers` ;
CREATE TABLE IF NOT EXISTS `servers` (
`srv_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`ip` VARCHAR(64) NOT NULL,
`port` INT NOT NULL,
`title` VARCHAR(128) NULL DEFAULT 'Server',
`rcon` VARCHAR(192) NULL,
`adv_time` FLOAT NULL DEFAULT 45,
PRIMARY KEY (`srv_id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Placeholder table for view `ads`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ads` (`srv_id` INT, `adv_id` INT, `msg_type` INT, `msg_text` INT, `date_from` INT, `date_to` INT, `hours` INT, `is_vip` INT, `admin_flags` INT, `views` INT, `day_of_week` INT, `show` INT, `order` INT, `color1` INT, `color2` INT, `effect` INT, `fadein` INT, `fadeout` INT, `holdtime` INT, `x` INT, `y` INT, `fxtime` INT);
-- -----------------------------------------------------
-- View `ads`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ads`;
DROP VIEW IF EXISTS `ads` ;
create OR REPLACE view ads as
SELECT s.srv_id, a.*, h.color1, h.color2, h.effect, h.fadein, h.fadeout, h.holdtime, h.x, h.y, h.fxtime FROM server_ads as s join advert as a using(adv_id) left join hud_style as h using(adv_id) where a.show = 1 order by a.`order`;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
