GilliardDbModels.sql

-- MySQL Script generated by MySQL Workbench
-- 09/06/16 08:50:32
-- 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';

CREATE USER 'insertValues'@'%' IDENTIFIED BY 'pass' ;GRANT SELECT, INSERT, UPDATE, CREATE, INDEX, ALTER, CREATE VIEW, EVENT, TRIGGER, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'insertValues'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

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

-- -----------------------------------------------------
-- Schema testsequelize
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `testsequelize` DEFAULT CHARACTER SET utf8 ;
USE `testsequelize` ;

-- -----------------------------------------------------
-- Table `testsequelize`.`AcquisitionSys`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `testsequelize`.`AcquisitionSys` ;

CREATE TABLE IF NOT EXISTS `testsequelize`.`AcquisitionSys` (
  `IdAcquisitionSys` VARCHAR(45) NOT NULL DEFAULT 'UUID()',
  `Sciper` VARCHAR(7) NOT NULL,
  `Computername` VARCHAR(45) NOT NULL,
  `MACAdress` VARCHAR(45) NULL,
  `IP` VARCHAR(15) NULL,
  `Responsible` VARCHAR(145) NULL DEFAULT NULL,
  `AppVersion` VARCHAR(10) NULL,
  `CreatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `StartedAt` DATETIME NOT NULL,
  PRIMARY KEY (`IdAcquisitionSys`, `Sciper`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `testsequelize`.`Boards`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `testsequelize`.`Boards` ;

CREATE TABLE IF NOT EXISTS `testsequelize`.`Boards` (
  `BID` VARCHAR(20) NOT NULL,
  `Name` VARCHAR(45) NULL,
  `BoardsModel` VARCHAR(45) NULL,
  `Rate` INT NULL DEFAULT NULL,
  `ConnexionPort` VARCHAR(20) NULL DEFAULT NULL,
  `ConnectedAt` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `AcquisitionSys_IdAcquisitionSys` VARCHAR(45) NOT NULL,
  `AcquisitionSys_Sciper` VARCHAR(7) NOT NULL,
  PRIMARY KEY (`BID`, `AcquisitionSys_IdAcquisitionSys`, `AcquisitionSys_Sciper`),
  INDEX `fk_Boards_AcquisitionSys_idx` (`AcquisitionSys_IdAcquisitionSys` ASC, `AcquisitionSys_Sciper` ASC),
  CONSTRAINT `fk_Boards_AcquisitionSys`
    FOREIGN KEY (`AcquisitionSys_IdAcquisitionSys` , `AcquisitionSys_Sciper`)
    REFERENCES `testsequelize`.`AcquisitionSys` (`IdAcquisitionSys` , `Sciper`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `testsequelize`.`Sensors`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `testsequelize`.`Sensors` ;

CREATE TABLE IF NOT EXISTS `testsequelize`.`Sensors` (
  `SID` VARCHAR(45) NOT NULL,
  `Type` VARCHAR(45) NULL DEFAULT NULL,
  `SensorModel` VARCHAR(100) NULL DEFAULT NULL,
  `BoardPins` VARCHAR(100) NULL DEFAULT NULL,
  `ConnectedAt` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `Unit` VARCHAR(100) NULL DEFAULT NULL,
  `Boards_BID` VARCHAR(20) NOT NULL,
  `Boards_AcquisitionSys_IdAcquisitionSys` VARCHAR(45) NOT NULL,
  `Boards_AcquisitionSys_Sciper` VARCHAR(7) NOT NULL,
  PRIMARY KEY (`SID`, `Boards_BID`, `Boards_AcquisitionSys_IdAcquisitionSys`, `Boards_AcquisitionSys_Sciper`),
  INDEX `fk_Sensors_Boards1_idx` (`Boards_BID` ASC, `Boards_AcquisitionSys_IdAcquisitionSys` ASC, `Boards_AcquisitionSys_Sciper` ASC),
  CONSTRAINT `fk_Sensors_Boards1`
    FOREIGN KEY (`Boards_BID` , `Boards_AcquisitionSys_IdAcquisitionSys` , `Boards_AcquisitionSys_Sciper`)
    REFERENCES `testsequelize`.`Boards` (`BID` , `AcquisitionSys_IdAcquisitionSys` , `AcquisitionSys_Sciper`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `testsequelize`.`SensorValues`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `testsequelize`.`SensorValues` ;

CREATE TABLE IF NOT EXISTS `testsequelize`.`SensorValues` (
  `CreatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `Value` FLOAT NULL,
  `Sensors_SID` VARCHAR(45) NOT NULL,
  `Sensors_Boards_BID` VARCHAR(20) NOT NULL,
  `Sensors_Boards_AcquisitionSys_IdAcquisitionSys` VARCHAR(45) NOT NULL,
  `Sensors_Boards_AcquisitionSys_Sciper` VARCHAR(7) NOT NULL,
  PRIMARY KEY (`CreatedAt`, `Sensors_SID`, `Sensors_Boards_BID`, `Sensors_Boards_AcquisitionSys_IdAcquisitionSys`, `Sensors_Boards_AcquisitionSys_Sciper`),
  INDEX `fk_SensorValues_Sensors1_idx` (`Sensors_SID` ASC, `Sensors_Boards_BID` ASC, `Sensors_Boards_AcquisitionSys_IdAcquisitionSys` ASC, `Sensors_Boards_AcquisitionSys_Sciper` ASC),
  CONSTRAINT `fk_SensorValues_Sensors1`
    FOREIGN KEY (`Sensors_SID` , `Sensors_Boards_BID` , `Sensors_Boards_AcquisitionSys_IdAcquisitionSys` , `Sensors_Boards_AcquisitionSys_Sciper`)
    REFERENCES `testsequelize`.`Sensors` (`SID` , `Boards_BID` , `Boards_AcquisitionSys_IdAcquisitionSys` , `Boards_AcquisitionSys_Sciper`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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