-- MySQL Script generated by MySQL Workbench
-- Mon 26 Nov 2018 03:17:07 PM CET
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema maestro
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema maestro
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `maestro` DEFAULT CHARACTER SET utf8 COLLATE utf8_lithuanian_ci ;
USE `maestro` ;

-- -----------------------------------------------------
-- Table `maestro`.`sut`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`sut` (
  `sut_id` INT NOT NULL AUTO_INCREMENT,
  `sut_name` VARCHAR(64) NOT NULL,
  `sut_version` VARCHAR(45) NOT NULL,
  `sut_jvm_info` VARCHAR(45) NULL,
  `sut_other` VARCHAR(45) NULL,
  `sut_tags` VARCHAR(512) NULL,
  `client_name` VARCHAR(45) NULL,
  `client_version` VARCHAR(45) NULL,
  PRIMARY KEY (`sut_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maestro`.`test`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`test` (
  `test_id` INT NOT NULL AUTO_INCREMENT,
  `test_number` INT NOT NULL,
  `test_name` VARCHAR(45) NULL,
  `test_result` VARCHAR(45) NOT NULL,
  `sut_id` INT NULL,
  `test_report_link` VARCHAR(512) NULL,
  `test_data_storage_info` VARCHAR(512) NULL,
  `test_tags` VARCHAR(512) NULL,
  `test_date` DATETIME NULL,
  `test_duration` INT NULL,
  `test_target_rate` INT NULL DEFAULT 0,
  `test_valid` TINYINT NULL DEFAULT 1,
  `maestro_version` VARCHAR(32) NULL DEFAULT '1.3.0',
  `connection_count` INT NULL,
  `test_duration_type` VARCHAR(8) NULL DEFAULT 'time',
  PRIMARY KEY (`test_id`, `test_number`),
  INDEX `fk_test_sut1_idx` (`sut_id` ASC),
  CONSTRAINT `fk_test_sut1`
    FOREIGN KEY (`sut_id`)
    REFERENCES `maestro`.`sut` (`sut_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maestro`.`test_fail_condition`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`test_fail_condition` (
  `test_id` INT NOT NULL,
  `test_number` INT NOT NULL,
  `test_fail_condition_resource_name` VARCHAR(128) NOT NULL DEFAULT 'all',
  `test_fail_condition_name` VARCHAR(64) NOT NULL,
  `test_fail_condition_value` VARCHAR(128) NOT NULL,
  `test_fail_condition_description` VARCHAR(512) NULL,
  PRIMARY KEY (`test_id`, `test_fail_condition_name`, `test_fail_condition_resource_name`, `test_number`),
  INDEX `fk_test_fail_condition_test1_idx` (`test_id` ASC, `test_number` ASC),
  CONSTRAINT `fk_test_fail_condition_test1`
    FOREIGN KEY (`test_id` , `test_number`)
    REFERENCES `maestro`.`test` (`test_id` , `test_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maestro`.`env_resource`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`env_resource` (
  `env_resource_id` INT NOT NULL AUTO_INCREMENT,
  `env_resource_name` VARCHAR(128) NOT NULL,
  `env_resource_os_name` VARCHAR(64) NULL DEFAULT 'RHEL',
  `env_resource_os_arch` VARCHAR(45) NULL DEFAULT 'x86_64',
  `env_resource_os_version` VARCHAR(45) NULL,
  `env_resource_os_other` VARCHAR(512) NULL,
  `env_resource_hw_name` VARCHAR(64) NULL,
  `env_resource_hw_model` VARCHAR(64) NULL,
  `env_resource_hw_cpu` VARCHAR(45) NULL,
  `env_resource_hw_ram` INT NULL,
  `env_resource_hw_disk_type` VARCHAR(45) NULL DEFAULT 'HD',
  `env_resource_hw_other` VARCHAR(512) NULL,
  PRIMARY KEY (`env_resource_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maestro`.`test_msg_property`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`test_msg_property` (
  `test_id` INT NOT NULL,
  `test_number` INT NOT NULL,
  `test_msg_property_resource_name` VARCHAR(128) NOT NULL DEFAULT 'all',
  `test_msg_property_name` VARCHAR(64) NOT NULL,
  `test_msg_property_value` VARCHAR(128) NOT NULL,
  `test_msg_property_description` VARCHAR(512) NULL,
  PRIMARY KEY (`test_id`, `test_msg_property_resource_name`, `test_msg_property_name`, `test_number`),
  INDEX `fk_test_msg_property_test1_idx` (`test_id` ASC, `test_number` ASC),
  CONSTRAINT `fk_test_msg_property_test1`
    FOREIGN KEY (`test_id` , `test_number`)
    REFERENCES `maestro`.`test` (`test_id` , `test_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maestro`.`env_results`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`env_results` (
  `env_results_id` INT NOT NULL AUTO_INCREMENT,
  `env_resource_id` INT NOT NULL,
  `test_id` INT NOT NULL,
  `test_number` INT NOT NULL,
  `env_name` VARCHAR(128) NOT NULL,
  `env_resource_role` VARCHAR(64) NOT NULL,
  `test_rate_min` INT NULL,
  `test_rate_max` INT NULL,
  `test_rate_error_count` INT NULL,
  `test_rate_samples` INT NULL,
  `test_rate_geometric_mean` DOUBLE NULL,
  `test_rate_standard_deviation` DOUBLE NULL,
  `test_rate_skip_count` INT NULL,
  `connection_count` INT NULL,
  `lat_percentile_90` DOUBLE NULL DEFAULT 0,
  `lat_percentile_95` DOUBLE NULL DEFAULT 0,
  `lat_percentile_99` DOUBLE NULL DEFAULT 0,
  `error` TINYINT NULL,
  PRIMARY KEY (`env_results_id`),
  INDEX `fk_env_results_env_resource1_idx` (`env_resource_id` ASC),
  INDEX `fk_env_results_test1_idx` (`test_id` ASC, `test_number` ASC),
  CONSTRAINT `fk_env_results_env_resource1`
    FOREIGN KEY (`env_resource_id`)
    REFERENCES `maestro`.`env_resource` (`env_resource_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_env_results_test1`
    FOREIGN KEY (`test_id` , `test_number`)
    REFERENCES `maestro`.`test` (`test_id` , `test_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maestro`.`test_history`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`test_history` (
  `test_id` INT NOT NULL,
  `test_number` INT NULL,
  `test_name` VARCHAR(45) NULL,
  `test_result` VARCHAR(45) NULL,
  `sut_id` INT NULL,
  `test_report_link` VARCHAR(512) NULL,
  `test_data_storage_info` VARCHAR(512) NULL,
  `test_tags` VARCHAR(512) NULL,
  `test_date` DATETIME NULL,
  `test_duration` INT NULL,
  `test_target_rate` INT NULL DEFAULT 0,
  `test_valid` TINYINT NULL DEFAULT 1,
  `maestro_version` VARCHAR(32) NULL DEFAULT '1.3.0',
  `test_duration_type` VARCHAR(8) NULL DEFAULT 'time',
  `connection_count` INT NULL,
  `test_update_date` DATETIME NOT NULL)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maestro`.`report`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`report` (
  `report_id` INT NOT NULL AUTO_INCREMENT,
  `test_id` INT NOT NULL,
  `test_number` INT NULL,
  `test_name` VARCHAR(45) NULL,
  `test_script` VARCHAR(128) NULL,
  `test_host` VARCHAR(128) NULL,
  `test_host_role` VARCHAR(64) NULL,
  `test_result` VARCHAR(45) NULL,
  `test_result_message` VARCHAR(2048) NULL,
  `location` VARCHAR(1024) NULL,
  `aggregated` TINYINT NULL DEFAULT 0,
  `test_date` DATETIME NULL DEFAULT now(),
  `test_description` VARCHAR(2048) NULL,
  `test_comments` VARCHAR(2048) NULL,
  `valid` TINYINT NULL,
  `retired` TINYINT NULL,
  `retired_date` DATETIME NULL,
  PRIMARY KEY (`report_id`, `test_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maestro`.`sut_node_info`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`sut_node_info` (
  `sut_node_id` INT NOT NULL AUTO_INCREMENT,
  `sut_node_name` VARCHAR(128) NULL,
  `sut_node_os_name` VARCHAR(64) NULL,
  `sut_node_os_arch` VARCHAR(45) NULL,
  `sut_node_os_version` VARCHAR(45) NULL,
  `sut_node_os_other` VARCHAR(512) NULL,
  `sut_node_hw_name` VARCHAR(64) NULL,
  `sut_node_hw_model` VARCHAR(64) NULL,
  `sut_node_hw_cpu` VARCHAR(45) NULL,
  `sut_node_hw_cpu_count` INT NULL,
  `sut_node_hw_ram` INT NULL,
  `sut_node_hw_disk_type` VARCHAR(45) NULL,
  `sut_node_hw_other` VARCHAR(512) NULL,
  PRIMARY KEY (`sut_node_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maestro`.`test_sut_node_link`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`test_sut_node_link` (
  `test_id` INT NOT NULL,
  `sut_node_id` INT NOT NULL,
  PRIMARY KEY (`test_id`, `sut_node_id`),
  INDEX `fk_report_sut_node_link_sut_node_info1_idx` (`sut_node_id` ASC),
  CONSTRAINT `fk_report_sut_node_link_sut_node_info1`
    FOREIGN KEY (`sut_node_id`)
    REFERENCES `maestro`.`sut_node_info` (`sut_node_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Note that it does not enforce the reference to test_id on report table on MariaDB because MariaDB cannot support referencing only one of the indexes.';


-- -----------------------------------------------------
-- Table `maestro`.`report_history`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`report_history` (
  `report_id` INT NOT NULL,
  `test_id` INT NOT NULL,
  `test_number` INT NULL,
  `test_name` VARCHAR(45) NULL,
  `test_script` VARCHAR(128) NULL,
  `test_host` VARCHAR(128) NULL,
  `test_host_role` VARCHAR(64) NULL,
  `test_result` VARCHAR(45) NULL,
  `test_result_message` VARCHAR(2048) NULL,
  `location` VARCHAR(1024) NULL,
  `aggregated` TINYINT NULL DEFAULT 0,
  `test_date` DATETIME NULL DEFAULT now(),
  `test_description` VARCHAR(2048) NULL,
  `test_comments` VARCHAR(2048) NULL,
  `valid` TINYINT NULL,
  `retired` TINYINT NULL,
  `retired_date` DATETIME NULL,
  `report_update_history` DATETIME NULL)
ENGINE = InnoDB;

USE `maestro` ;

-- -----------------------------------------------------
-- Placeholder table for view `maestro`.`test_results`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`test_results` (`test_id` INT, `test_number` INT, `sut_id` INT, `sut_tags` INT, `sut_name` INT, `sut_version` INT, `test_result` INT, `error` INT, `test_valid` INT, `env_resource_name` INT, `env_resource_role` INT, `test_rate_min` INT, `test_rate_max` INT, `test_rate_geometric_mean` INT, `test_rate_standard_deviation` INT, `test_rate_skip_count` INT, `connection_count` INT, `test_date` INT, `test_name` INT, `test_tags` INT, `test_report_link` INT);

-- -----------------------------------------------------
-- Placeholder table for view `maestro`.`test_results`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`test_results` (`test_id` INT, `test_number` INT, `sut_id` INT, `sut_tags` INT, `sut_name` INT, `sut_version` INT, `test_result` INT, `error` INT, `test_valid` INT, `env_resource_name` INT, `env_resource_role` INT, `test_rate_min` INT, `test_rate_max` INT, `test_rate_geometric_mean` INT, `test_rate_standard_deviation` INT, `test_rate_skip_count` INT, `lat_percentile_90` INT, `lat_percentile_95` INT, `lat_percentile_99` INT, `test_date` INT, `test_name` INT, `test_tags` INT, `test_report_link` INT, `connection_count` INT, `test_target_rate` INT, `test_combined_target_rate` INT, `api_name` INT, `api_version` INT, `durable` INT, `limit_destinations` INT, `message_size` INT, `messaging_protocol` INT, `max_acceptable_latency` INT);

-- -----------------------------------------------------
-- Placeholder table for view `maestro`.`test_results_statistics`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`test_results_statistics` (`test_id` INT, `success` INT, `failures` INT);

-- -----------------------------------------------------
-- Placeholder table for view `maestro`.`test_sut_properties_link`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `maestro`.`test_sut_properties_link` (`test_id` INT, `test_number` INT, `sut_id` INT, `sut_tags` INT, `sut_name` INT, `sut_version` INT, `test_result` INT, `connection_count` INT, `test_name` INT, `test_tags` INT, `test_target_rate` INT, `api_name` INT, `api_version` INT, `durable` INT, `limit_destinations` INT, `message_size` INT, `messaging_protocol` INT, `max_acceptable_latency` INT, `test_date` INT);


CREATE OR REPLACE VIEW `test_properties` AS
select
    tmp.test_id,
    tmp.test_number,
    MAX(IF(tmp.test_msg_property_name = "apiName", tmp.test_msg_property_value, NULL)) AS api_name,
    MAX(IF(tmp.test_msg_property_name = "apiVersion", tmp.test_msg_property_value, NULL)) AS api_version,
    MAX(IF(tmp.test_msg_property_name = "durable", IF(tmp.test_msg_property_value = "true", true, false), false)) AS durable,
    MAX(IF(tmp.test_msg_property_name = "limitDestinations", CAST(tmp.test_msg_property_value AS INTEGER), -1)) AS limit_destinations,
    MAX(IF(tmp.test_msg_property_name = "messageSize", CAST(tmp.test_msg_property_value AS INTEGER), -1)) AS message_size,
    MAX(IF(tmp.test_msg_property_name = "protocol", tmp.test_msg_property_value, NULL)) AS messaging_protocol,
    MAX(IF(tmp.test_msg_property_name = "variableSize", IF(tmp.test_msg_property_value = "1", true, false), false)) AS variable_size,
    MAX(IF(tfc.test_fail_condition_name = "fcl", CAST(tfc.test_fail_condition_value AS INTEGER), -1)) AS max_acceptable_latency
from test_msg_property tmp, test_fail_condition tfc
where tmp.test_id = tfc.test_id and tmp.test_number = tfc.test_number
group by test_id,test_number;

-- -----------------------------------------------------
-- View `maestro`.`test_results`
-- -----------------------------------------------------
USE `maestro`;
CREATE OR REPLACE VIEW `test_results` AS
    SELECT
        t.test_id,
        t.test_number,
        sut.sut_id,
        sut.sut_tags,
        sut.sut_name,
        sut.sut_version,
        t.test_result,
        er.error,
        t.test_valid,
        res.env_resource_name,
        er.env_resource_role,
        er.test_rate_min,
        er.test_rate_max,
        er.test_rate_geometric_mean,
        er.test_rate_standard_deviation,
        er.test_rate_skip_count,
        er.lat_percentile_90,
        er.lat_percentile_95,
        er.lat_percentile_99,
        t.test_date,
        t.test_name,
        t.test_tags,
        t.test_report_link,
        t.connection_count,
        t.test_target_rate,
        (t.test_target_rate * t.connection_count) as test_combined_target_rate,
        tp.api_name,
        tp.api_version,
        tp.durable,
        tp.limit_destinations,
        tp.message_size,
        tp.messaging_protocol,
        tp.max_acceptable_latency
    FROM
        test t,
        env_results er,
        env_resource res,
        sut,
        test_properties tp
    WHERE
        t.test_id = er.test_id
            AND t.test_number = er.test_number
            AND er.env_resource_id = res.env_resource_id
            AND t.sut_id = sut.sut_id
            AND t.test_id = tp.test_id
            AND t.test_number = tp.test_number;

-- -----------------------------------------------------
-- View `maestro`.`test_results`
-- -----------------------------------------------------
USE `maestro`;
CREATE OR REPLACE VIEW `test_results` AS
    SELECT
        t.test_id,
        t.test_number,
        sut.sut_id,
        sut.sut_tags,
        sut.sut_name,
        sut.sut_version,
        t.test_result,
        er.error,
        t.test_valid,
        res.env_resource_name,
        er.env_resource_role,
        er.test_rate_min,
        er.test_rate_max,
        er.test_rate_geometric_mean,
        er.test_rate_standard_deviation,
        er.test_rate_skip_count,
        er.lat_percentile_90,
        er.lat_percentile_95,
        er.lat_percentile_99,
        t.test_date,
        t.test_name,
        t.test_tags,
        t.test_report_link,
        t.connection_count,
        t.test_target_rate,
        (t.test_target_rate * t.connection_count) as test_combined_target_rate,
        tp.api_name,
        tp.api_version,
        tp.durable,
        tp.limit_destinations,
        tp.message_size,
        tp.messaging_protocol,
        tp.max_acceptable_latency
    FROM
        test t,
        env_results er,
        env_resource res,
        sut,
        test_properties tp
    WHERE
        t.test_id = er.test_id
            AND t.test_number = er.test_number
            AND er.env_resource_id = res.env_resource_id
            AND t.sut_id = sut.sut_id
            AND t.test_id = tp.test_id
            AND t.test_number = tp.test_number;

-- -----------------------------------------------------
-- View `maestro`.`test_results_statistics`
-- -----------------------------------------------------
USE `maestro`;
CREATE  OR REPLACE VIEW `test_results_statistics` AS
	select test_id,
        sum(case when test_result = "success" then 1 else 0 end) as success,
        sum(case when test_result = "success" then 0 else 1 end) as failures
    from test_results
    where test_valid = true
    group by test_id;

-- -----------------------------------------------------
-- View `maestro`.`test_sut_properties_link`
-- -----------------------------------------------------
USE `maestro`;
CREATE OR REPLACE VIEW `test_sut_properties_link` AS
	SELECT
        t.test_id,
        t.test_number,
        sut.sut_id,
        sut.sut_tags,
        sut.sut_name,
        sut.sut_version,
        t.test_result,        
        t.connection_count,
        t.test_name,
        t.test_tags,
        t.test_target_rate,
        tp.api_name,
        tp.api_version,
        tp.durable,
        tp.limit_destinations,
        tp.message_size,
        tp.messaging_protocol,
        tp.max_acceptable_latency,
        t.test_date
    FROM
        test t,
        sut,
        test_properties tp
    WHERE t.sut_id = sut.sut_id
            AND t.test_id = tp.test_id
            AND t.test_number = tp.test_number;
USE `maestro`;

DELIMITER $$
USE `maestro`$$
CREATE OR REPLACE DEFINER = CURRENT_USER TRIGGER `maestro`.`test_BEFORE_UPDATE` BEFORE UPDATE ON `test` FOR EACH ROW
BEGIN
	INSERT INTO test_history select *,now() as test_update_date from test where test_id = NEW.test_id and test_number = NEW.test_number;
END$$

DELIMITER ;

