CREATE DATABASE IF NOT EXISTS `mon` DEFAULT CHARACTER SET UTF8 ;
GRANT ALL ON mon.* TO 'monitor'@'localhost' IDENTIFIED BY 'monmon';
USE `mon` ;
-- -----------------------------------------------------
-- Table `mon`.`server_t`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mon`.`server_t` (
`server_id` INT NOT NULL AUTO_INCREMENT ,
`server_name` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`server_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mon`.`url_t`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mon`.`url_t` (
`url_id` INT NOT NULL AUTO_INCREMENT ,
`server_id` INT NOT NULL ,
`host` VARCHAR(150) NOT NULL,
`port` INT NOT NULL,
`timeout` INT NOT NULL,
PRIMARY KEY (`url_id`) ,
INDEX `fk_url_t_server_t` (`server_id` ASC) ,
CONSTRAINT `fk_url_t_server_t`
FOREIGN KEY (`server_id` )
REFERENCES `mon`.`server_t` (`server_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mon`.`state_t`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mon`.`state_t` (
`state_id` INT NOT NULL AUTO_INCREMENT ,
`state_name` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`state_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mon`.`monitoring_t`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mon`.`monitoring_t` (
`monitoring_id` INT NOT NULL AUTO_INCREMENT ,
`server_id` INT NOT NULL ,
`state_id` INT NOT NULL ,
`date` TIMESTAMP NOT NULL,
PRIMARY KEY (`monitoring_id`) ,
INDEX `fk_monitoring_1` (`server_id` ASC) ,
INDEX `fk_monitoring_2` (`state_id` ASC) ,
CONSTRAINT `fk_monitoring_1`
FOREIGN KEY (`server_id` )
REFERENCES `mon`.`server_t` (`server_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_monitoring_2`
FOREIGN KEY (`state_id` )
REFERENCES `mon`.`state_t` (`state_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
INSERT INTO state_t VALUES
(1,"oK"),
(2,"have problems"),
(3,"offline");