qtemp_app_tmp.sql

-- MySQL Script generated by MySQL Workbench
-- Tue Dec  7 22:29:02 2021
-- 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 qtempapp
-- -----------------------------------------------------
-- Application database to hold users, contact info, IOT node information and track state elements like current temperature or new temperature settings. Could also contain min and max temp for individual nodes if this varied by zones under control
DROP SCHEMA IF EXISTS `qtempapp` ;

-- -----------------------------------------------------
-- Schema qtempapp
--
-- Application database to hold users, contact info, IOT node information and track state elements like current temperature or new temperature settings. Could also contain min and max temp for individual nodes if this varied by zones under control
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `qtempapp` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;
USE `qtempapp` ;

-- -----------------------------------------------------
-- Table `qtempapp`.`nodedatatmp`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `qtempapp`.`nodedatatmp` ;

CREATE TABLE IF NOT EXISTS `qtempapp`.`nodedatatmp` (
  `manodeid` INT NOT NULL AUTO_INCREMENT,
  `manodeguid` VARCHAR(36) NOT NULL,
  `curtemp` DECIMAL(4,2) NULL,
  `settemp` DECIMAL(4,2) NULL,
  PRIMARY KEY (`manodeid`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `qtempapp`.`user` (
  `username` VARCHAR(16) NOT NULL,
  `email` VARCHAR(255) NULL,
  `password` VARCHAR(32) NOT NULL,
  `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

SET SQL_MODE = '';
DROP USER IF EXISTS lpappuser;
SET SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
CREATE USER 'lpappuser' IDENTIFIED BY 's0s0@PPcr3ds';

GRANT SELECT, INSERT, UPDATE, TRIGGER ON TABLE `qtempapp`.* TO 'lpappuser';

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