text: -- MySQL Script generated by MySQL Workbench
  -- Sat Mar 31 12:19:35 2018
  -- 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 mydb
  -- -----------------------------------------------------
  SHOW WARNINGS;
  
  CREATE TABLE IF NOT EXISTS `ci_sessions` (
          `id` varchar(128) NOT NULL,
          `ip_address` varchar(45) NOT NULL,
          `timestamp` int(10) unsigned DEFAULT 0 NOT NULL,
          `data` blob NOT NULL,
          KEY `ci_sessions_timestamp` (`timestamp`)
  );
  -- -----------------------------------------------------
  -- Table `game_user`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `game_user` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NULL,
    `surname` VARCHAR(100) NULL,
    `email` VARCHAR(100) NOT NULL,
    `password` VARCHAR(45) NOT NULL,
    `type` VARCHAR(45) NOT NULL,
    `is_blocked` TINYINT(1) NOT NULL DEFAULT 0,
    `val_code` VARCHAR(100) NULL,
    `val_code_expiration` DATE NULL,
    PRIMARY KEY (`ID`))
  ENGINE = InnoDB;
  
  SHOW WARNINGS;
  
  -- -----------------------------------------------------
  -- Table `Project`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Project` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `description` VARCHAR(500) NULL,
    `creation_date` DATE NOT NULL,
    PRIMARY KEY (`ID`))
  ENGINE = InnoDB;
  
  SHOW WARNINGS;
  
  -- -----------------------------------------------------
  -- Table `project_user`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `project_user` (
    `game_user_ID` INT NOT NULL,
    `Project_ID` INT NOT NULL,
    PRIMARY KEY (`game_user_ID`, `Project_ID`),
    INDEX `fk_game_user_has_Project_Project1_idx` (`Project_ID` ASC),
    INDEX `fk_game_user_has_Project_game_user_idx` (`game_user_ID` ASC),
    CONSTRAINT `fk_game_user_has_Project_game_user`
      FOREIGN KEY (`game_user_ID`)
      REFERENCES `game_user` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_game_user_has_Project_Project1`
      FOREIGN KEY (`Project_ID`)
      REFERENCES `Project` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  SHOW WARNINGS;
  
  -- -----------------------------------------------------
  -- Table `Bin`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Bin` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `Name` VARCHAR(500) NOT NULL,
    `method` VARCHAR(100) NULL,
    `taxonomy` VARCHAR(500) NULL,
    `size` INT NULL,
    `contig_num` INT NULL,
    `gc_per` DECIMAL(10,3) NULL,
    `chimerism` DECIMAL(10,3) NULL,
    `completeness` DECIMAL(10,3) NULL,
    `contamination` DECIMAL(10,3) NULL,
    `strain_het` DECIMAL(10,3) NULL,
    `project_id` INT NOT NULL,
    PRIMARY KEY (`ID`),
    INDEX `ix_bin_name` USING BTREE (`project_id` ASC, `Name` ASC))
  ENGINE = InnoDB;
  
  SHOW WARNINGS;
  
  -- -----------------------------------------------------
  -- Table `Contig`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Contig` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(500) NULL,
    `taxonomy` VARCHAR(500) NULL,
    `genes_num` INT NULL,
    `size` INT NULL,
    `chimerism` DECIMAL(10,3) NULL,
    `gc_per` DECIMAL(10,3) NULL,
    `project_id` INT NOT NULL,
    PRIMARY KEY (`ID`),
    INDEX `ix_contig_name` (`project_id` ASC, `name` ASC))
  ENGINE = InnoDB;
  
  SHOW WARNINGS;
  
  -- -----------------------------------------------------
  -- Table `Gene`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Gene` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `ORF` VARCHAR(200) NOT NULL,
    `name` VARCHAR(200) NULL,
    `taxonomy` VARCHAR(500) NULL,
    `gc_per` DECIMAL(10,0) NULL,
    `Contig_ID` INT NOT NULL,
    `KEGG_id` VARCHAR(45) NULL,
    `KEGG_function` VARCHAR(500) NULL,
    `KEGG_pathway` VARCHAR(500) NULL,
    `COG_id` VARCHAR(45) NULL,
    `COG_function` VARCHAR(500) NULL,
    `COG_pathway` VARCHAR(500) NULL,
    `Pfam` VARCHAR(500) NULL,
    `project_id` INT NOT NULL,
    PRIMARY KEY (`ID`, `Contig_ID`),
    INDEX `fk_Gene_Contig1_idx` (`Contig_ID` ASC),
    INDEX `ix_Gene_Name` USING BTREE (`project_id` ASC, `name` ASC),
    CONSTRAINT `fk_Gene_Contig1`
      FOREIGN KEY (`Contig_ID`)
      REFERENCES `Contig` (`ID`)
      ON DELETE CASCADE
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  SHOW WARNINGS;
  
  -- -----------------------------------------------------
  -- Table `Sample`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Sample` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(45) NULL,
    `Project_ID` INT NOT NULL,
    PRIMARY KEY (`ID`, `Project_ID`),
    INDEX `fk_Sample_Project1_idx` (`Project_ID` ASC),
    CONSTRAINT `fk_Sample_Project1`
      FOREIGN KEY (`Project_ID`)
      REFERENCES `Project` (`ID`)
      ON DELETE CASCADE
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  SHOW WARNINGS;
  
  -- -----------------------------------------------------
  -- Table `Sample_properties`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Sample_properties` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `property` VARCHAR(45) NOT NULL,
    `value` VARCHAR(450) NOT NULL,
    `Sample_ID` INT NOT NULL,
    PRIMARY KEY (`ID`, `Sample_ID`),
    INDEX `fk_Sample_properties_Sample1_idx` (`Sample_ID` ASC),
    CONSTRAINT `fk_Sample_properties_Sample1`
      FOREIGN KEY (`Sample_ID`)
      REFERENCES `Sample` (`ID`)
      ON DELETE CASCADE
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  SHOW WARNINGS;
  
  -- -----------------------------------------------------
  -- Table `Sample_Bin`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Sample_Bin` (
    `Sample_ID` INT NOT NULL,
    `Bin_ID` INT NOT NULL,
    `norm_counts` DECIMAL(10,3) NULL,
    `coverage` DECIMAL(10,3) NULL,
    PRIMARY KEY (`Sample_ID`, `Bin_ID`),
    INDEX `fk_Sample_has_Bin_Bin1_idx` (`Bin_ID` ASC),
    INDEX `fk_Sample_has_Bin_Sample1_idx` (`Sample_ID` ASC),
    CONSTRAINT `fk_Sample_has_Bin_Sample1`
      FOREIGN KEY (`Sample_ID`)
      REFERENCES `Sample` (`ID`)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_Sample_has_Bin_Bin1`
      FOREIGN KEY (`Bin_ID`)
      REFERENCES `Bin` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  SHOW WARNINGS;
  
  -- -----------------------------------------------------
  -- Table `Sample_Contig`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Sample_Contig` (
    `Sample_ID` INT NOT NULL,
    `Contig_ID` INT NOT NULL,
    `norm_counts` DECIMAL(10,3) NULL,
    `coverage` DECIMAL(10,3) NULL,
    PRIMARY KEY (`Sample_ID`, `Contig_ID`),
    INDEX `fk_Sample_has_Contig_Contig1_idx` (`Contig_ID` ASC),
    INDEX `fk_Sample_has_Contig_Sample1_idx` (`Sample_ID` ASC),
    CONSTRAINT `fk_Sample_has_Contig_Sample1`
      FOREIGN KEY (`Sample_ID`)
      REFERENCES `Sample` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_Sample_has_Contig_Contig1`
      FOREIGN KEY (`Contig_ID`)
      REFERENCES `Contig` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  SHOW WARNINGS;
  
  -- -----------------------------------------------------
  -- Table `Sample_Gene`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Sample_Gene` (
    `Sample_ID` INT NOT NULL,
    `Gene_ID` INT NOT NULL,
    `raw_counts` INT NULL,
    `norm_counts` DECIMAL(10,3) NULL,
    PRIMARY KEY (`Sample_ID`, `Gene_ID`),
    INDEX `fk_Sample_has_Gene_Gene1_idx` (`Gene_ID` ASC),
    INDEX `fk_Sample_has_Gene_Sample1_idx` (`Sample_ID` ASC),
    CONSTRAINT `fk_Sample_has_Gene_Sample1`
      FOREIGN KEY (`Sample_ID`)
      REFERENCES `Sample` (`ID`)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_Sample_has_Gene_Gene1`
      FOREIGN KEY (`Gene_ID`)
      REFERENCES `Gene` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  SHOW WARNINGS;
  
  -- -----------------------------------------------------
  -- Table `Bin_Contig`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Bin_Contig` (
    `Bin_ID` INT NOT NULL,
    `Contig_ID` INT NOT NULL,
    `Method` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`Bin_ID`, `Contig_ID`),
    INDEX `fk_Bin_has_Contig_Contig1_idx` (`Contig_ID` ASC),
    INDEX `fk_Bin_has_Contig_Bin1_idx` (`Bin_ID` ASC),
    CONSTRAINT `fk_Bin_has_Contig_Bin1`
      FOREIGN KEY (`Bin_ID`)
      REFERENCES `Bin` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_Bin_has_Contig_Contig1`
      FOREIGN KEY (`Contig_ID`)
      REFERENCES `Contig` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  SHOW WARNINGS;
  
  -- -----------------------------------------------------
  -- Table `Sequence`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Sequence` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `sequence` MEDIUMTEXT NULL,
    `Gene_ID` INT NOT NULL,
    PRIMARY KEY (`ID`),
    INDEX `fk_Sequence_Gene1_idx` (`Gene_ID` ASC),
    CONSTRAINT `fk_Sequence_Gene1`
      FOREIGN KEY (`Gene_ID`)
      REFERENCES `Gene` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- Insert default user
  INSERT INTO game_user (email, password, type, is_blocked) values ('squeezem@squeezem.com', 'ee1284d95f9dc253f5acfa813a3298f6', 1, 0);
  
  SHOW WARNINGS;
  
  SET SQL_MODE=@OLD_SQL_MODE;
  SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  