-- MySQL Script generated by MySQL Workbench
-- Thu 19 Feb 2015 08:29:01 PM EET
-- 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='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `searchSessionT`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `searchSessionT` ;

CREATE TABLE IF NOT EXISTS `searchSessionT` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `sid` VARCHAR(100) BINARY NOT NULL,
  `added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `sessionId_UNIQUE` (`sid` ASC),
  INDEX `added_idx` (`added` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `searchCriterionT`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `searchCriterionT` ;

CREATE TABLE IF NOT EXISTS `searchCriterionT` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `sessionId` BIGINT UNSIGNED NOT NULL,
  `nodeId` INT UNSIGNED NOT NULL,
  `nodeTypeId` INT UNSIGNED NULL,
  `internalTypeId` VARCHAR(10) BINARY NOT NULL,
  INDEX `fk_searchCriterion_nodeT1_idx` (`nodeId` ASC, `nodeTypeId` ASC),
  INDEX `nodetypeid_node_id` (`sessionId` ASC, `nodeId` ASC, `nodeTypeId` ASC),
  INDEX `fk_searchCriterionT_internalTypeT1_idx` (`internalTypeId` ASC),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `sessionid_nodeid_uq` (`sessionId` ASC, `nodeId` ASC),
  CONSTRAINT `fk_searchCriterion_nodeT1`
    FOREIGN KEY (`nodeId` , `nodeTypeId`)
    REFERENCES `nodeT` (`id` , `nodeTypeId`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_searchCriterionT_internalTypeT1`
    FOREIGN KEY (`internalTypeId`)
    REFERENCES `internalTypeT` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_searchCriterionT_searchSessionT1`
    FOREIGN KEY (`sessionId`)
    REFERENCES `searchSessionT` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `searchCriterionNumericT`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `searchCriterionNumericT` ;

CREATE TABLE IF NOT EXISTS `searchCriterionNumericT` (
  `searchCriterionId` BIGINT UNSIGNED NOT NULL,
  `valueLow` DECIMAL(22,6) NULL,
  `valueHigh` DECIMAL(22,6) NULL,
  PRIMARY KEY (`searchCriterionId`),
  CONSTRAINT `fk_searchCriterionNumericT_searchCriterionT1`
    FOREIGN KEY (`searchCriterionId`)
    REFERENCES `searchCriterionT` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `searchCriterionDatetimeT`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `searchCriterionDatetimeT` ;

CREATE TABLE IF NOT EXISTS `searchCriterionDatetimeT` (
  `searchCriterionId` BIGINT UNSIGNED NOT NULL,
  `valueLow` DATETIME NULL,
  `valueHigh` DATETIME NULL,
  PRIMARY KEY (`searchCriterionId`),
  CONSTRAINT `fk_searchCriterionDatetimeT_searchCriterionT1`
    FOREIGN KEY (`searchCriterionId`)
    REFERENCES `searchCriterionT` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `searchResultT`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `searchResultT` ;

CREATE TABLE IF NOT EXISTS `searchResultT` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `sessionId` BIGINT UNSIGNED NOT NULL,
  `nodeId` INT UNSIGNED NOT NULL,
  `collapsed` ENUM('n','y') NOT NULL DEFAULT 'n',
  `extraObjects` INT UNSIGNED NOT NULL DEFAULT 0,
  `relevancy` DOUBLE NOT NULL DEFAULT 0.0,
  INDEX `collapsed_idx` (`sessionId` ASC, `collapsed` ASC),
  INDEX `relevancy_idx` (`sessionId` ASC, `relevancy` DESC),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `sessionid_nodeid_uq` (`sessionId` ASC, `nodeId` ASC),
  INDEX `fk_searchResultT_nodeT1_idx` (`nodeId` ASC),
  CONSTRAINT `fk_searchResultT_searchSessionT1`
    FOREIGN KEY (`sessionId`)
    REFERENCES `searchSessionT` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_searchResultT_nodeT1`
    FOREIGN KEY (`nodeId`)
    REFERENCES `nodeT` (`id`)
    ON DELETE CASCADE
    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;