-- MySQL Script generated by MySQL Workbench
-- Sat 23 May 2020 03:48:57 PM EDT
-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema glenns11_cjms
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `glenns11_cjms` ;

-- -----------------------------------------------------
-- Schema glenns11_cjms
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `glenns11_cjms` ;
USE `glenns11_cjms` ;

-- -----------------------------------------------------
-- Table `glenns11_cjms`.`heartBeat`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `glenns11_cjms`.`heartBeat` ;

CREATE TABLE IF NOT EXISTS `glenns11_cjms`.`heartBeat` (
  `idHeartBeat` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `serverID` VARCHAR(45) NOT NULL,
  `ipAddress` VARCHAR(45) NOT NULL,
  `refreshBy` DATETIME NOT NULL,
  `licenseCode` VARCHAR(45) NOT NULL,
  `pulse` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`idHeartBeat`),
  UNIQUE INDEX `idHeartBeat_UNIQUE` (`idHeartBeat` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `glenns11_cjms`.`history`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `glenns11_cjms`.`history` ;

CREATE TABLE IF NOT EXISTS `glenns11_cjms`.`history` (
  `idHistory` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `submittedDate` DATETIME NOT NULL,
  `startDate` DATETIME NOT NULL,
  `finishDate` DATETIME NOT NULL,
  `planID` VARCHAR(45) NOT NULL,
  `jobName` VARCHAR(45) NOT NULL,
  `folder` VARCHAR(55) NOT NULL,
  `transaction` VARCHAR(3) NOT NULL,
  `queue` VARCHAR(15) NOT NULL,
  `step` CHAR(2) NOT NULL,
  `results` LONGBLOB NOT NULL,
  PRIMARY KEY (`idHistory`),
  UNIQUE INDEX `idHistory_UNIQUE` (`idHistory` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `glenns11_cjms`.`plan`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `glenns11_cjms`.`plan` ;

CREATE TABLE IF NOT EXISTS `glenns11_cjms`.`plan` (
  `idPlan` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '	',
  `planID` VARCHAR(6) NOT NULL,
  `fileSet` VARCHAR(15) NOT NULL,
  `segment` CHAR(2) NOT NULL,
  PRIMARY KEY (`idPlan`),
  UNIQUE INDEX `idPlans_UNIQUE` (`idPlan` ASC),
  UNIQUE INDEX `planID_UNIQUE` (`planID` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `glenns11_cjms`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `glenns11_cjms`.`user` ;

CREATE TABLE IF NOT EXISTS `glenns11_cjms`.`user` (
  `idUser` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `loginID` VARCHAR(45) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `rights` VARCHAR(5) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idUser`),
  UNIQUE INDEX `idUsers_UNIQUE` (`idUser` ASC),
  UNIQUE INDEX `loginID_UNIQUE` (`loginID` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `glenns11_cjms`.`job`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `glenns11_cjms`.`job` ;

CREATE TABLE IF NOT EXISTS `glenns11_cjms`.`job` (
  `idJob` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dateToRun` CHAR(8) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `folder` VARCHAR(55) NOT NULL,
  `step` CHAR(2) NOT NULL,
  `processStep` VARCHAR(2) NOT NULL,
  `transaction` VARCHAR(3) NOT NULL,
  `status` VARCHAR(15) NOT NULL,
  `queue` VARCHAR(15) NOT NULL,
  `submittedDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `startDate` TIMESTAMP NULL,
  `finishDate` TIMESTAMP NULL,
  `comment` VARCHAR(15) NULL,
  `plan_idPlan` INT UNSIGNED NOT NULL,
  `user_idUser` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`idJob`),
  INDEX `fk_job_plan_idx` (`plan_idPlan` ASC),
  INDEX `fk_job_user_idx` (`user_idUser` ASC),
  UNIQUE INDEX `idJob_UNIQUE` (`idJob` ASC),
  CONSTRAINT `fk_job_plan`
    FOREIGN KEY (`plan_idPlan`)
    REFERENCES `glenns11_cjms`.`plan` (`idPlan`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_job_user`
    FOREIGN KEY (`user_idUser`)
    REFERENCES `glenns11_cjms`.`user` (`idUser`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `glenns11_cjms`.`parameter`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `glenns11_cjms`.`parameter` ;

CREATE TABLE IF NOT EXISTS `glenns11_cjms`.`parameter` (
  `idParameter` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `parmKey` VARCHAR(45) NOT NULL,
  `parmValue` VARCHAR(95) NOT NULL,
  PRIMARY KEY (`idParameter`),
  UNIQUE INDEX `idParameters_UNIQUE` (`idParameter` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
