sql/drop_and_create_schema.sql - master - kotkov/vvsu_db2024

sql/drop_and_create_schema.sql
129 строк4 KB
Arseny Kotkov
Added README.md and SQL-files
17 янв 2025, 08:47
17 янв 2025, 08:47ea391c8
-- 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 mydb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;

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

-- -----------------------------------------------------
-- Table `mydb`.`agent_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`agent_type` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`agent`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`agent` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `ip` VARCHAR(45) NULL,
  `port` INT NULL,
  `agent_type_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_agent_1_idx` (`agent_type_id` ASC) VISIBLE,
  CONSTRAINT `fk_agent_1`
    FOREIGN KEY (`agent_type_id`)
    REFERENCES `mydb`.`agent_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`method`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`method` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `agent_type_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_method_1_idx` (`agent_type_id` ASC) VISIBLE,
  CONSTRAINT `fk_method_1`
    FOREIGN KEY (`agent_type_id`)
    REFERENCES `mydb`.`agent_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`parameter_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`parameter_type` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`parameter`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`parameter` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `parameter_type_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_parameter_1_idx` (`parameter_type_id` ASC) VISIBLE,
  CONSTRAINT `fk_parameter_1`
    FOREIGN KEY (`parameter_type_id`)
    REFERENCES `mydb`.`parameter_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`parameter_to_method`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`parameter_to_method` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `parameter_id` INT NULL,
  `method_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_parameter_to_method_1_idx` (`parameter_id` ASC) VISIBLE,
  INDEX `fk_parameter_to_method_2_idx` (`method_id` ASC) VISIBLE,
  CONSTRAINT `fk_parameter_to_method_1`
    FOREIGN KEY (`parameter_id`)
    REFERENCES `mydb`.`parameter` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_parameter_to_method_2`
    FOREIGN KEY (`method_id`)
    REFERENCES `mydb`.`method` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`log`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL,
  `entity` VARCHAR(45) NOT NULL,
  `operation` VARCHAR(45) NULL,
  `info` JSON NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


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