Labellab-sql-script.sql

-- MySQL Script generated by MySQL Workbench
-- Mon Jun  8 20:03:57 2020
-- 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 mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema labellab
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema labellab
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `labellab` DEFAULT CHARACTER SET latin1 ;
USE `labellab` ;

-- -----------------------------------------------------
-- Table `labellab`.`alembic_version`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`alembic_version` (
  `version_num` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`version_num`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `labellab`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(80) NOT NULL,
  `username` VARCHAR(80) NOT NULL,
  `password` VARCHAR(128) NULL DEFAULT NULL,
  `email` VARCHAR(100) NOT NULL,
  `date` DATETIME NULL DEFAULT NULL,
  `thumbnail` VARCHAR(1500) NULL DEFAULT NULL,
  `projects` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email` (`email` ASC) VISIBLE,
  UNIQUE INDEX `username` (`username` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `labellab`.`project`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`project` (
  `id` INT NOT NULL,
  `project_name` VARCHAR(45) NULL,
  `project_description` VARCHAR(150) NULL,
  `admin_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_project_user_idx` (`admin_id` ASC) VISIBLE,
  CONSTRAINT `fk_project_user`
    FOREIGN KEY (`admin_id`)
    REFERENCES `labellab`.`user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `labellab`.`image`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`image` (
  `id` INT NOT NULL,
  `image_name` VARCHAR(45) NOT NULL,
  `imageurl` VARCHAR(45) NOT NULL,
  `height` VARCHAR(45) NULL,
  `width` VARCHAR(45) NULL,
  `labelled` TINYINT NOT NULL DEFAULT 0,
  `date` DATETIME NULL,
  `latitude` DOUBLE NULL,
  `longitude` DOUBLE NULL,
  `project_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_images_project1_idx` (`project_id` ASC) VISIBLE,
  CONSTRAINT `fk_images_project1`
    FOREIGN KEY (`project_id`)
    REFERENCES `labellab`.`project` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `labellab`.`label`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`label` (
  `id` VARCHAR(45) NOT NULL,
  `label_name` VARCHAR(45) NOT NULL,
  `type` VARCHAR(45) NOT NULL,
  `count` INT NOT NULL DEFAULT 0,
  `project_id` INT NOT NULL,
  INDEX `fk_labels_project1_idx` (`project_id` ASC) VISIBLE,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_labels_project1`
    FOREIGN KEY (`project_id`)
    REFERENCES `labellab`.`project` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `labellab`.`team`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`team` (
  `id` INT NOT NULL,
  `roles` VARCHAR(45) NULL,
  `team_name` VARCHAR(45) NOT NULL,
  `project_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_teams_project1_idx` (`project_id` ASC) VISIBLE,
  CONSTRAINT `fk_teams_project1`
    FOREIGN KEY (`project_id`)
    REFERENCES `labellab`.`project` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `labellab`.`project_member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`project_member` (
  `user_id` INT NOT NULL,
  `team_id` INT NULL,
  PRIMARY KEY (`user_id`),
  INDEX `fk_project members_teams1_idx` (`team_id` ASC) VISIBLE,
  CONSTRAINT `fk_project members_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `labellab`.`user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_project members_teams1`
    FOREIGN KEY (`team_id`)
    REFERENCES `labellab`.`team` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `labellab`.`label_data`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`label_data` (
  `id` INT NOT NULL,
  `months_passed` INT NULL,
  `image_id` INT NOT NULL,
  `label_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_label_data_image1_idx` (`image_id` ASC) VISIBLE,
  INDEX `fk_label_data_label1_idx` (`label_id` ASC) VISIBLE,
  CONSTRAINT `fk_label_data_image1`
    FOREIGN KEY (`image_id`)
    REFERENCES `labellab`.`image` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_label_data_label1`
    FOREIGN KEY (`label_id`)
    REFERENCES `labellab`.`label` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `labellab`.`group`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`group` (
  `id` INT NOT NULL,
  `group_name` VARCHAR(45) NULL,
  `project_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_group_project1_idx` (`project_id` ASC) VISIBLE,
  CONSTRAINT `fk_group_project1`
    FOREIGN KEY (`project_id`)
    REFERENCES `labellab`.`project` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `labellab`.`group_has_image`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`group_has_image` (
  `group_id` INT NOT NULL,
  `image_id` INT NOT NULL,
  INDEX `fk_group_has_image_group1_idx` (`group_id` ASC) VISIBLE,
  INDEX `fk_group_has_image_image1_idx` (`image_id` ASC) VISIBLE,
  CONSTRAINT `fk_group_has_image_group1`
    FOREIGN KEY (`group_id`)
    REFERENCES `labellab`.`group` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_group_has_image_image1`
    FOREIGN KEY (`image_id`)
    REFERENCES `labellab`.`image` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `labellab`.`model`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`model` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `type` VARCHAR(45) NOT NULL,
  `source` VARCHAR(45) NOT NULL,
  `preprocessing_steps_json_url` VARCHAR(45) NULL,
  `layers_json_url` VARCHAR(45) NULL,
  `train` DECIMAL(4,2) NULL,
  `test` DECIMAL(4,2) NULL,
  `validation` DECIMAL(4,2) NULL,
  `epochs` INT NULL,
  `batch_size` INT NULL,
  `learning_rate` DECIMAL(6,4) NULL,
  `loss` VARCHAR(45) NULL,
  `optimizer` VARCHAR(45) NULL,
  `metric` VARCHAR(45) NULL,
  `loss_graph_url` VARCHAR(45) NULL,
  `accuracy_graph_url` VARCHAR(45) NULL,
  `saved_model_url` VARCHAR(45) NULL,
  `transfer_source` VARCHAR(45) NULL,
  `project_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_model_project1_idx` (`project_id` ASC) VISIBLE,
  CONSTRAINT `fk_model_project1`
    FOREIGN KEY (`project_id`)
    REFERENCES `labellab`.`project` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `labellab`.`model_has_label`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`model_has_label` (
  `model_id` INT NOT NULL,
  `label_id` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`model_id`, `label_id`),
  INDEX `fk_model_has_label_label1_idx` (`label_id` ASC) VISIBLE,
  INDEX `fk_model_has_label_model1_idx` (`model_id` ASC) VISIBLE,
  CONSTRAINT `fk_model_has_label_model1`
    FOREIGN KEY (`model_id`)
    REFERENCES `labellab`.`model` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_model_has_label_label1`
    FOREIGN KEY (`label_id`)
    REFERENCES `labellab`.`label` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `labellab`.`point`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `labellab`.`point` (
  `id` INT NOT NULL,
  `label_data_id` INT NOT NULL,
  `x_coordinate` FLOAT NOT NULL,
  `y_coordinate` FLOAT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_table1_label_data1_idx` (`label_data_id` ASC) VISIBLE,
  CONSTRAINT `fk_table1_label_data1`
    FOREIGN KEY (`label_data_id`)
    REFERENCES `labellab`.`label_data` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


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