create_database.sql

-- MySQL Script generated by MySQL Workbench
-- 10/08/14 23:50:57
-- 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 tosol
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema tosol
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `tosol` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `tosol` ;

-- -----------------------------------------------------
-- Table `tosol`.`language`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`language` (
  `language_id` INT NOT NULL AUTO_INCREMENT,
  `language_name_from` VARCHAR(255) NULL,
  `language_name_to` VARCHAR(255) NOT NULL,
  `language_image` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`language_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`user` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `nickname` VARCHAR(255) NULL,
  `first_name` VARCHAR(255) NULL,
  `last_name` VARCHAR(255) NULL,
  `birthday` DATETIME NULL,
  `password` CHAR(255) NULL,
  `registration_date` DATETIME NULL DEFAULT NOW(),
  `email_address` VARCHAR(255) NULL,
  `subscription_date` DATETIME NOT NULL,
  `o2auth_id` VARCHAR(255) NULL,
  PRIMARY KEY (`user_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`bonus_level`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`bonus_level` (
  `bonus_level_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  PRIMARY KEY (`bonus_level_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`lesson`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`lesson` (
  `lesson_id` INT NOT NULL AUTO_INCREMENT,
  `language_id` INT NOT NULL,
  `lesson_name` VARCHAR(255) NULL,
  `lesson_sub_header` VARCHAR(255) NULL,
  `lession_description` TEXT NULL,
  `bonus_level` INT NULL DEFAULT NULL,
  PRIMARY KEY (`lesson_id`),
  INDEX `fk_lesson_language_idx` (`language_id` ASC),
  INDEX `fk_lesson_access_level1_idx` (`bonus_level` ASC),
  CONSTRAINT `fk_lesson_language`
    FOREIGN KEY (`language_id`)
    REFERENCES `tosol`.`language` (`language_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_lesson_access_level1`
    FOREIGN KEY (`bonus_level`)
    REFERENCES `tosol`.`bonus_level` (`bonus_level_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = '	';


-- -----------------------------------------------------
-- Table `tosol`.`task`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`task` (
  `task_id` INT NOT NULL AUTO_INCREMENT,
  `lesson_id` INT NOT NULL,
  `task_name` VARCHAR(255) NULL,
  `task_description` TEXT NULL,
  `max_score` INT NULL,
  PRIMARY KEY (`task_id`),
  INDEX `fk_task_lesson1_idx` (`lesson_id` ASC),
  CONSTRAINT `fk_task_lesson1`
    FOREIGN KEY (`lesson_id`)
    REFERENCES `tosol`.`lesson` (`lesson_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`vocabulary`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`vocabulary` (
  `vocabulary_id` BIGINT NOT NULL,
  `language_id` INT NOT NULL,
  `word_from` VARCHAR(255) NULL,
  `word_to` VARCHAR(255) NULL,
  PRIMARY KEY (`vocabulary_id`),
  INDEX `fk_vocabulary_language1_idx` (`language_id` ASC),
  CONSTRAINT `fk_vocabulary_language1`
    FOREIGN KEY (`language_id`)
    REFERENCES `tosol`.`language` (`language_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`grammar`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`grammar` (
  `grammar_id` INT NOT NULL,
  `language` INT NOT NULL,
  `title` TEXT NULL,
  `description` TEXT NULL,
  `grammar` TEXT NULL,
  PRIMARY KEY (`grammar_id`),
  INDEX `fk_grammar_language1_idx` (`language` ASC),
  CONSTRAINT `fk_grammar_language1`
    FOREIGN KEY (`language`)
    REFERENCES `tosol`.`language` (`language_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`phrase`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`phrase` (
  `phrase_id` BIGINT NOT NULL,
  `language_id` INT NOT NULL,
  `phrase_from` TEXT NULL,
  `phrase_to` TEXT NULL,
  PRIMARY KEY (`phrase_id`),
  INDEX `fk_vocabulary_language1_idx` (`language_id` ASC),
  CONSTRAINT `fk_vocabulary_language11`
    FOREIGN KEY (`language_id`)
    REFERENCES `tosol`.`language` (`language_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`user_has_language`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`user_has_language` (
  `language_language_id` INT NOT NULL,
  `user_user_id` INT NOT NULL,
  `progress` INT NULL,
  `completing_date` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`language_language_id`, `user_user_id`),
  INDEX `fk_language_has_user_user1_idx` (`user_user_id` ASC),
  INDEX `fk_language_has_user_language1_idx` (`language_language_id` ASC),
  CONSTRAINT `fk_language_has_user_language1`
    FOREIGN KEY (`language_language_id`)
    REFERENCES `tosol`.`language` (`language_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_language_has_user_user1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `tosol`.`user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`user_has_lesson`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`user_has_lesson` (
  `lesson_lesson_id` INT NOT NULL,
  `user_user_id` INT NOT NULL,
  `completing_date` DATETIME NULL DEFAULT NOW(),
  PRIMARY KEY (`lesson_lesson_id`, `user_user_id`),
  INDEX `fk_lesson_has_user_user1_idx` (`user_user_id` ASC),
  INDEX `fk_lesson_has_user_lesson1_idx` (`lesson_lesson_id` ASC),
  CONSTRAINT `fk_lesson_has_user_lesson1`
    FOREIGN KEY (`lesson_lesson_id`)
    REFERENCES `tosol`.`lesson` (`lesson_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_lesson_has_user_user1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `tosol`.`user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`user_has_task`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`user_has_task` (
  `task_task_id` INT NOT NULL,
  `user_user_id` INT NOT NULL,
  `score` INT NULL,
  `starting_date` DATETIME NULL DEFAULT NOW(),
  `completing_date` DATETIME NULL,
  PRIMARY KEY (`task_task_id`, `user_user_id`),
  INDEX `fk_task_has_user_user1_idx` (`user_user_id` ASC),
  INDEX `fk_task_has_user_task1_idx` (`task_task_id` ASC),
  CONSTRAINT `fk_task_has_user_task1`
    FOREIGN KEY (`task_task_id`)
    REFERENCES `tosol`.`task` (`task_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_task_has_user_user1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `tosol`.`user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`user_has_vocabulary`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`user_has_vocabulary` (
  `user_user_id` INT NOT NULL,
  `vocabulary_vocabulary_id` BIGINT NOT NULL,
  `completing_date` DATETIME NULL DEFAULT NOW(),
  PRIMARY KEY (`user_user_id`, `vocabulary_vocabulary_id`),
  INDEX `fk_user_has_vocabulary_vocabulary1_idx` (`vocabulary_vocabulary_id` ASC),
  INDEX `fk_user_has_vocabulary_user1_idx` (`user_user_id` ASC),
  CONSTRAINT `fk_user_has_vocabulary_user1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `tosol`.`user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_user_has_vocabulary_vocabulary1`
    FOREIGN KEY (`vocabulary_vocabulary_id`)
    REFERENCES `tosol`.`vocabulary` (`vocabulary_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`user_has_phrase`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`user_has_phrase` (
  `user_user_id` INT NOT NULL,
  `phrase_phrase_id` BIGINT NOT NULL,
  `completing_date` DATETIME NULL DEFAULT NOW(),
  PRIMARY KEY (`user_user_id`, `phrase_phrase_id`),
  INDEX `fk_user_has_phrase_phrase1_idx` (`phrase_phrase_id` ASC),
  INDEX `fk_user_has_phrase_user1_idx` (`user_user_id` ASC),
  CONSTRAINT `fk_user_has_phrase_user1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `tosol`.`user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_user_has_phrase_phrase1`
    FOREIGN KEY (`phrase_phrase_id`)
    REFERENCES `tosol`.`phrase` (`phrase_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`user_has_grammar`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`user_has_grammar` (
  `user_user_id` INT NOT NULL,
  `grammar_grammar_id` INT NOT NULL,
  `completing_date` DATETIME NULL DEFAULT NOW(),
  PRIMARY KEY (`user_user_id`, `grammar_grammar_id`),
  INDEX `fk_user_has_grammar_grammar1_idx` (`grammar_grammar_id` ASC),
  INDEX `fk_user_has_grammar_user1_idx` (`user_user_id` ASC),
  CONSTRAINT `fk_user_has_grammar_user1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `tosol`.`user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_user_has_grammar_grammar1`
    FOREIGN KEY (`grammar_grammar_id`)
    REFERENCES `tosol`.`grammar` (`grammar_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`user_has_bonus_level`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`user_has_bonus_level` (
  `user_user_id` INT NOT NULL,
  `bonus_level_id` INT NOT NULL,
  `completing_date` DATETIME NULL DEFAULT NOW(),
  PRIMARY KEY (`user_user_id`, `bonus_level_id`),
  INDEX `fk_user_has_access_level_access_level1_idx` (`bonus_level_id` ASC),
  INDEX `fk_user_has_access_level_user1_idx` (`user_user_id` ASC),
  CONSTRAINT `fk_user_has_access_level_user1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `tosol`.`user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_user_has_access_level_access_level1`
    FOREIGN KEY (`bonus_level_id`)
    REFERENCES `tosol`.`bonus_level` (`bonus_level_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`test`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`test` (
  `test_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `description` VARCHAR(255) NULL,
  `bonus_level_bonus_level_id` INT NOT NULL,
  `grammar_grammar_id` INT NOT NULL,
  `phrase_phrase_id` BIGINT NOT NULL,
  `vocabulary_vocabulary_id` BIGINT NOT NULL,
  PRIMARY KEY (`test_id`),
  INDEX `fk_test_bonus_level1_idx` (`bonus_level_bonus_level_id` ASC),
  INDEX `fk_test_grammar1_idx` (`grammar_grammar_id` ASC),
  INDEX `fk_test_phrase1_idx` (`phrase_phrase_id` ASC),
  INDEX `fk_test_vocabulary1_idx` (`vocabulary_vocabulary_id` ASC),
  CONSTRAINT `fk_test_bonus_level1`
    FOREIGN KEY (`bonus_level_bonus_level_id`)
    REFERENCES `tosol`.`bonus_level` (`bonus_level_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_test_grammar1`
    FOREIGN KEY (`grammar_grammar_id`)
    REFERENCES `tosol`.`grammar` (`grammar_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_test_phrase1`
    FOREIGN KEY (`phrase_phrase_id`)
    REFERENCES `tosol`.`phrase` (`phrase_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_test_vocabulary1`
    FOREIGN KEY (`vocabulary_vocabulary_id`)
    REFERENCES `tosol`.`vocabulary` (`vocabulary_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`achievement`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`achievement` (
  `achievement_id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NULL,
  `description` VARCHAR(255) NULL,
  `number_words` INT NULL,
  `number_phrases` INT NULL,
  `number_grammar` INT NULL,
  `number_lessons` INT NULL,
  `language_language_id` INT NOT NULL,
  PRIMARY KEY (`achievement_id`),
  INDEX `fk_achievement_language1_idx` (`language_language_id` ASC),
  CONSTRAINT `fk_achievement_language1`
    FOREIGN KEY (`language_language_id`)
    REFERENCES `tosol`.`language` (`language_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`user_has_achievement`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`user_has_achievement` (
  `user_user_id` INT NOT NULL,
  `achievement_achievement_id` INT NOT NULL,
  `completing_date` DATETIME NULL DEFAULT NOW(),
  PRIMARY KEY (`user_user_id`, `achievement_achievement_id`),
  INDEX `fk_user_has_achievement_achievement1_idx` (`achievement_achievement_id` ASC),
  INDEX `fk_user_has_achievement_user1_idx` (`user_user_id` ASC),
  CONSTRAINT `fk_user_has_achievement_user1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `tosol`.`user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_user_has_achievement_achievement1`
    FOREIGN KEY (`achievement_achievement_id`)
    REFERENCES `tosol`.`achievement` (`achievement_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`task_has_vocabulary`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`task_has_vocabulary` (
  `task_task_id` INT NOT NULL,
  `vocabulary_vocabulary_id` BIGINT NOT NULL,
  PRIMARY KEY (`task_task_id`, `vocabulary_vocabulary_id`),
  INDEX `fk_task_has_vocabulary_vocabulary1_idx` (`vocabulary_vocabulary_id` ASC),
  INDEX `fk_task_has_vocabulary_task1_idx` (`task_task_id` ASC),
  CONSTRAINT `fk_task_has_vocabulary_task1`
    FOREIGN KEY (`task_task_id`)
    REFERENCES `tosol`.`task` (`task_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_task_has_vocabulary_vocabulary1`
    FOREIGN KEY (`vocabulary_vocabulary_id`)
    REFERENCES `tosol`.`vocabulary` (`vocabulary_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`task_has_phrase`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`task_has_phrase` (
  `task_task_id` INT NOT NULL,
  `phrase_phrase_id` BIGINT NOT NULL,
  PRIMARY KEY (`task_task_id`, `phrase_phrase_id`),
  INDEX `fk_task_has_phrase_phrase1_idx` (`phrase_phrase_id` ASC),
  INDEX `fk_task_has_phrase_task1_idx` (`task_task_id` ASC),
  CONSTRAINT `fk_task_has_phrase_task1`
    FOREIGN KEY (`task_task_id`)
    REFERENCES `tosol`.`task` (`task_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_task_has_phrase_phrase1`
    FOREIGN KEY (`phrase_phrase_id`)
    REFERENCES `tosol`.`phrase` (`phrase_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tosol`.`task_has_grammar`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tosol`.`task_has_grammar` (
  `task_task_id` INT NOT NULL,
  `grammar_grammar_id` INT NOT NULL,
  PRIMARY KEY (`task_task_id`, `grammar_grammar_id`),
  INDEX `fk_task_has_grammar_grammar1_idx` (`grammar_grammar_id` ASC),
  INDEX `fk_task_has_grammar_task1_idx` (`task_task_id` ASC),
  CONSTRAINT `fk_task_has_grammar_task1`
    FOREIGN KEY (`task_task_id`)
    REFERENCES `tosol`.`task` (`task_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_task_has_grammar_grammar1`
    FOREIGN KEY (`grammar_grammar_id`)
    REFERENCES `tosol`.`grammar` (`grammar_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;