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';

CREATE SCHEMA IF NOT EXISTS `seamless_primers_v2_2` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci ;
USE `seamless_primers_v2_2` ;

-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`organism_species`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`organism_species` (
  `id_organism_species` SMALLINT NOT NULL AUTO_INCREMENT ,
  `species_name` VARCHAR(30) NOT NULL ,
  `ncbi_taxonomy_id` SMALLINT NULL ,
  PRIMARY KEY (`id_organism_species`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`orf`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`orf` (
  `id_orf` INT NOT NULL AUTO_INCREMENT ,
  `id_organism` SMALLINT NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id_orf`) ,
  INDEX `orf_fk` (`id_organism` ASC) ,
  CONSTRAINT `orf_fk`
    FOREIGN KEY (`id_organism` )
    REFERENCES `seamless_primers_v2_2`.`organism_species` (`id_organism_species` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`primer_type`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`primer_type` (
  `id_primer_type` TINYINT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(10) NOT NULL ,
  PRIMARY KEY (`id_primer_type`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`primer`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`primer` (
  `id_primer` INT NOT NULL AUTO_INCREMENT ,
  `id_orf` INT NOT NULL ,
  `id_primer_type` TINYINT NOT NULL ,
  `sequence` VARCHAR(100) NOT NULL ,
  `GC_content` INT NOT NULL ,
  `notes` VARCHAR(200) NULL ,
  PRIMARY KEY (`id_primer`) ,
  INDEX `primer_1_fk` (`id_orf` ASC) ,
  INDEX `primer_2_fk` (`id_primer_type` ASC) ,
  CONSTRAINT `primer_1_fk`
    FOREIGN KEY (`id_orf` )
    REFERENCES `seamless_primers_v2_2`.`orf` (`id_orf` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `primer_2_fk`
    FOREIGN KEY (`id_primer_type` )
    REFERENCES `seamless_primers_v2_2`.`primer_type` (`id_primer_type` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`primer_score`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`primer_score` (
  `id_primer` INT NOT NULL ,
  `id_primer_score_type` INT NOT NULL ,
  `score` FLOAT NOT NULL ,
  PRIMARY KEY (`id_primer`, `id_primer_score_type`) ,
  INDEX `primer_score_1_fk` (`id_primer` ASC) ,
  INDEX `primer_score_2_fk` (`id_primer_score_type` ASC) ,
  CONSTRAINT `primer_score_1_fk`
    FOREIGN KEY (`id_primer` )
    REFERENCES `seamless_primers_v2_2`.`primer` (`id_primer` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `primer_score_2_fk`
    FOREIGN KEY (`id_primer_score_type` )
    REFERENCES `seamless_primers_v2_2`.`primer_score_type` (`id_primer_score_type` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`nsb_result`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`nsb_result` (
  `id_nsb` INT NOT NULL AUTO_INCREMENT ,
  `id_primer` INT NOT NULL ,
  `chromosome` TINYINT NOT NULL ,
  `result` VARCHAR(300) NOT NULL ,
  PRIMARY KEY (`id_nsb`) ,
  INDEX `fk_nsb_result` (`id_primer` ASC) ,
  CONSTRAINT `fk_nsb_result`
    FOREIGN KEY (`id_primer` )
    REFERENCES `seamless_primers_v2_2`.`primer` (`id_primer` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`self_dimer_result`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`self_dimer_result` (
  `id_primer` INT NOT NULL ,
  `result` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id_primer`) ,
  INDEX `fk_self_dimer_result_1` (`id_primer` ASC) ,
  CONSTRAINT `fk_self_dimer_result_1`
    FOREIGN KEY (`id_primer` )
    REFERENCES `seamless_primers_v2_2`.`primer` (`id_primer` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`hairpin_result`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`hairpin_result` (
  `id_primer` INT NOT NULL ,
  `result` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id_primer`) ,
  INDEX `fk_hairpin` (`id_primer` ASC) ,
  CONSTRAINT `fk_hairpin`
    FOREIGN KEY (`id_primer` )
    REFERENCES `seamless_primers_v2_2`.`primer` (`id_primer` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`confirmation_primer_type`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`confirmation_primer_type` (
  `id_confirmation_primer_type` TINYINT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id_confirmation_primer_type`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`confirmation_primer`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`confirmation_primer` (
  `id_primer` INT NOT NULL AUTO_INCREMENT ,
  `id_confirmation_primer_type` TINYINT NOT NULL ,
  `sequence` VARCHAR(45) NOT NULL ,
  `id_orf` INT NOT NULL ,
  `origin` VARCHAR(45) NULL ,
  `Tm` FLOAT NULL ,
  PRIMARY KEY (`id_primer`) ,
  INDEX `confirmation_primer_1_fk` (`id_orf` ASC) ,
  INDEX `confirmation_primer_2_fk` (`id_confirmation_primer_type` ASC) ,
  CONSTRAINT `confirmation_primer_1_fk`
    FOREIGN KEY (`id_orf` )
    REFERENCES `seamless_primers_v2_2`.`orf` (`id_orf` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `confirmation_primer_2_fk`
    FOREIGN KEY (`id_confirmation_primer_type` )
    REFERENCES `seamless_primers_v2_2`.`confirmation_primer_type` (`id_confirmation_primer_type` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`confirmation_pcr_type`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`confirmation_pcr_type` (
  `id_confirmation_pcr_type` TINYINT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id_confirmation_pcr_type`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`confirmation_pcr_pair`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`confirmation_pcr_pair` (
  `id_confirmation_pcr_pair` INT NOT NULL AUTO_INCREMENT ,
  `id_primer_f` INT NOT NULL ,
  `id_primer_r` INT NOT NULL ,
  `id_confirmation_pcr_type` TINYINT NOT NULL ,
  `sequence` VARCHAR(16000) NULL ,
  PRIMARY KEY (`id_confirmation_pcr_pair`) ,
  INDEX `confirmation_pcr_pair_1_fk` (`id_confirmation_pcr_type` ASC) ,
  INDEX `confirmation_pcr_pair_2_fk` (`id_primer_f` ASC) ,
  INDEX `confirmation_pcr_pair_3_fk` (`id_primer_r` ASC) ,
  CONSTRAINT `confirmation_pcr_pair_1_fk`
    FOREIGN KEY (`id_confirmation_pcr_type` )
    REFERENCES `seamless_primers_v2_2`.`confirmation_pcr_type` (`id_confirmation_pcr_type` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `confirmation_pcr_pair_2_fk`
    FOREIGN KEY (`id_primer_f` )
    REFERENCES `seamless_primers_v2_2`.`confirmation_primer` (`id_primer` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `confirmation_pcr_pair_3_fk`
    FOREIGN KEY (`id_primer_r` )
    REFERENCES `seamless_primers_v2_2`.`confirmation_primer` (`id_primer` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`pcr_type`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`pcr_type` (
  `id_pcr_type` SMALLINT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id_pcr_type`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`pcr_pair`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`pcr_pair` (
  `id_pcr_pair` INT NOT NULL AUTO_INCREMENT ,
  `id_primer_f` INT NOT NULL ,
  `id_primer_r` INT NOT NULL ,
  `id_pcr_type` SMALLINT NOT NULL ,
  `product_sequence` VARCHAR(3500) NULL ,
  `number_of_products` INT NULL ,
  INDEX `process_fk1` (`id_primer_f` ASC) ,
  INDEX `process_fk2` (`id_primer_r` ASC) ,
  INDEX `process_fk3` (`id_pcr_type` ASC) ,
  PRIMARY KEY (`id_pcr_pair`) ,
  CONSTRAINT `process_fk1`
    FOREIGN KEY (`id_primer_f` )
    REFERENCES `seamless_primers_v2_2`.`primer` (`id_primer` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `process_fk2`
    FOREIGN KEY (`id_primer_r` )
    REFERENCES `seamless_primers_v2_2`.`primer` (`id_primer` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `process_fk3`
    FOREIGN KEY (`id_pcr_type` )
    REFERENCES `seamless_primers_v2_2`.`pcr_type` (`id_pcr_type` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`orf_synonym`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`orf_synonym` (
  `id_synonym` INT NOT NULL AUTO_INCREMENT ,
  `id_orf` INT NOT NULL ,
  `synonym` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id_synonym`) ,
  INDEX `orf_synonym_fk` (`id_orf` ASC) ,
  CONSTRAINT `orf_synonym_fk`
    FOREIGN KEY (`id_orf` )
    REFERENCES `seamless_primers_v2_2`.`orf` (`id_orf` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`pcr_pair_score`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`pcr_pair_score` (
  `id_pcr_pair_score` INT NOT NULL AUTO_INCREMENT ,
  `id_pcr_pair` INT NOT NULL ,
  `id_pcr_pair_score_type` INT NOT NULL ,
  `score` FLOAT NOT NULL ,
  PRIMARY KEY (`id_pcr_pair_score`) ,
  INDEX `pcr_pair_score` (`id_pcr_pair` ASC) ,
  CONSTRAINT `pcr_pair_score`
    FOREIGN KEY (`id_pcr_pair` )
    REFERENCES `seamless_primers_v2_2`.`pcr_pair` (`id_pcr_pair` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`pcr_pair_score_type`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`pcr_pair_score_type` (
  `id_pcr_pair_score_type` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id_pcr_pair_score_type`) ,
  INDEX `pcr_pair_score_type` (`id_pcr_pair_score_type` ASC) ,
  CONSTRAINT `pcr_pair_score_type`
    FOREIGN KEY (`id_pcr_pair_score_type` )
    REFERENCES `seamless_primers_v2_2`.`pcr_pair_score` (`id_pcr_pair_score` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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


-- -----------------------------------------------------
-- Table `seamless_primers_v2_2`.`primer_component`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `seamless_primers_v2_2`.`primer_component` (
  `id_primer` INT NOT NULL ,
  `id_primer_component_type` INT NOT NULL ,
  `sequence` VARCHAR(100) NOT NULL ,
  PRIMARY KEY (`id_primer`, `id_primer_component_type`) ,
  INDEX `primer_component_FK` (`id_primer` ASC) ,
  INDEX `primer_component_type_FK` (`id_primer_component_type` ASC) ,
  CONSTRAINT `primer_component_FK`
    FOREIGN KEY (`id_primer` )
    REFERENCES `seamless_primers_v2_2`.`primer` (`id_primer` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `primer_component_type_FK`
    FOREIGN KEY (`id_primer_component_type` )
    REFERENCES `seamless_primers_v2_2`.`primer_component_type` (`id_primer_component_type` )
    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;
