database.sql

-- MySQL Script generated by MySQL Workbench
-- Tue Feb  5 16:02:52 2019
-- Model: Prednasky.com    Version: 1.12
-- 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 prednasky
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema prednasky
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `prednasky` DEFAULT CHARACTER SET utf8 ;
USE `prednasky` ;

-- -----------------------------------------------------
-- Table `prednasky`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`user` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `CAS_id` VARCHAR(45) NULL,
  `fullname` VARCHAR(100) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `right_group` TINYINT UNSIGNED NOT NULL COMMENT 'disabled, guest, student, teacher, admin',
  `last_login` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `prednasky`.`video_state`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`video_state` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`video_state` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `prednasky`.`video`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`video` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`video` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(200) NOT NULL,
  `created` TIMESTAMP NOT NULL,
  `state` INT UNSIGNED NOT NULL,
  `complete` TINYINT(1) NOT NULL,
  `published` TIMESTAMP NULL,
  `record_date` DATE NULL,
  `record_time_begin` TIME NULL,
  `record_time_end` TIME NULL,
  `duration` INT UNSIGNED NULL,
  `abstract` TEXT NULL,
  `public_link` VARCHAR(100) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `fk_video_video_state1_idx` (`state` ASC),
  FULLTEXT INDEX `fulltext_name` (`name`),
  FULLTEXT INDEX `fulltext_abstract` (`abstract`),
  FULLTEXT INDEX `fulltext_name_abstract` (`name`, `abstract`),
  CONSTRAINT `fk_video_video_state1`
    FOREIGN KEY (`state`)
    REFERENCES `prednasky`.`video_state` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `prednasky`.`tag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`tag` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`tag` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `value` VARCHAR(100) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  UNIQUE INDEX `tag_UNIQUE` (`name` ASC, `value` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `prednasky`.`file`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`file` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`file` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(45) NOT NULL,
  `path` VARCHAR(250) NOT NULL,
  `user` INT UNSIGNED NULL,
  `name` VARCHAR(45) NULL,
  `downloads` INT UNSIGNED NULL,
  `uploaded` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `fk_file_user1_idx` (`user` ASC),
  CONSTRAINT `fk_file_user1`
    FOREIGN KEY (`user`)
    REFERENCES `prednasky`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `prednasky`.`video_has_file`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`video_has_file` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`video_has_file` (
  `video_id` INT UNSIGNED NOT NULL,
  `file_id` INT UNSIGNED NOT NULL,
  `type` VARCHAR(45) NOT NULL COMMENT 'thumbnail, video, attachment',
  PRIMARY KEY (`video_id`, `file_id`),
  INDEX `fk_video_has_file_video1_idx` (`video_id` ASC),
  INDEX `fk_video_has_file_file1_idx` (`file_id` ASC),
  CONSTRAINT `fk_video_has_file_video1`
    FOREIGN KEY (`video_id`)
    REFERENCES `prednasky`.`video` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_video_has_file_file1`
    FOREIGN KEY (`file_id`)
    REFERENCES `prednasky`.`file` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `prednasky`.`role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`role` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`role` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `prednasky`.`user_has_video`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`user_has_video` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`user_has_video` (
  `user_id` INT UNSIGNED NOT NULL,
  `video_id` INT UNSIGNED NOT NULL,
  `role_id` INT UNSIGNED NOT NULL,
  `show_email` TINYINT(1) NOT NULL,
  PRIMARY KEY (`user_id`, `video_id`, `role_id`),
  INDEX `fk_user_has_video_video1_idx` (`video_id` ASC),
  INDEX `fk_user_has_video_user1_idx` (`user_id` ASC),
  INDEX `fk_user_has_video_participant1_idx` (`role_id` ASC),
  CONSTRAINT `fk_user_has_video_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `prednasky`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_has_video_video1`
    FOREIGN KEY (`video_id`)
    REFERENCES `prednasky`.`video` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_has_video_participant1`
    FOREIGN KEY (`role_id`)
    REFERENCES `prednasky`.`role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `prednasky`.`relation_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`relation_type` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`relation_type` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `prednasky`.`video_relation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`video_relation` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`video_relation` (
  `video_from` INT UNSIGNED NOT NULL,
  `video_to` INT UNSIGNED NOT NULL,
  `relation_type_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`video_from`, `video_to`, `relation_type_id`),
  INDEX `fk_video_has_video_video2_idx` (`video_to` ASC),
  INDEX `fk_video_has_video_video1_idx` (`video_from` ASC),
  INDEX `fk_video_relation_relation_type1_idx` (`relation_type_id` ASC),
  CONSTRAINT `fk_video_has_video_video1`
    FOREIGN KEY (`video_from`)
    REFERENCES `prednasky`.`video` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_video_has_video_video2`
    FOREIGN KEY (`video_to`)
    REFERENCES `prednasky`.`video` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_video_relation_relation_type1`
    FOREIGN KEY (`relation_type_id`)
    REFERENCES `prednasky`.`relation_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `prednasky`.`video_has_tag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`video_has_tag` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`video_has_tag` (
  `video_id` INT UNSIGNED NOT NULL,
  `tag_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`video_id`, `tag_id`),
  INDEX `fk_video_has_tag_tag1_idx` (`tag_id` ASC),
  INDEX `fk_video_has_tag_video1_idx` (`video_id` ASC),
  CONSTRAINT `fk_video_has_tag_video1`
    FOREIGN KEY (`video_id`)
    REFERENCES `prednasky`.`video` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_video_has_tag_tag1`
    FOREIGN KEY (`tag_id`)
    REFERENCES `prednasky`.`tag` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `prednasky`.`token_state`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`token_state` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`token_state` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `prednasky`.`token_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`token_type` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`token_type` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `prednasky`.`template`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`template` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`template` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `blocks` TEXT NOT NULL,
  `description` TEXT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `prednasky`.`token`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `prednasky`.`token` ;

CREATE TABLE IF NOT EXISTS `prednasky`.`token` (
  `id` VARCHAR(41) NOT NULL,
  `state` INT UNSIGNED NOT NULL,
  `type` INT UNSIGNED NOT NULL,
  `template` INT UNSIGNED NOT NULL,
  `video` INT UNSIGNED NOT NULL,
  `public_hash` VARCHAR(32) NOT NULL,
  `private_hash` VARCHAR(32) NOT NULL,
  `created` TIMESTAMP NOT NULL,
  `pending_blocks` TEXT NOT NULL,
  `last_update` TIMESTAMP NULL,
  `current_state` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `fk_token_token_state1_idx` (`state` ASC),
  INDEX `fk_token_token_type1_idx` (`type` ASC),
  INDEX `fk_token_video1_idx` (`video` ASC),
  INDEX `fk_token_template1_idx` (`template` ASC),
  CONSTRAINT `fk_token_token_state1`
    FOREIGN KEY (`state`)
    REFERENCES `prednasky`.`token_state` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_token_token_type1`
    FOREIGN KEY (`type`)
    REFERENCES `prednasky`.`token_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_token_video1`
    FOREIGN KEY (`video`)
    REFERENCES `prednasky`.`video` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_token_template1`
    FOREIGN KEY (`template`)
    REFERENCES `prednasky`.`template` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `prednasky` ;

-- -----------------------------------------------------
-- function database_version
-- -----------------------------------------------------

USE `prednasky`;
DROP function IF EXISTS `prednasky`.`database_version`;

DELIMITER $$
USE `prednasky`$$
CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
RETURN "1.12";$$

DELIMITER ;

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

-- -----------------------------------------------------
-- Data for table `prednasky`.`video_state`
-- -----------------------------------------------------
START TRANSACTION;
USE `prednasky`;
INSERT INTO `prednasky`.`video_state` (`id`, `name`) VALUES (DEFAULT, 'private');
INSERT INTO `prednasky`.`video_state` (`id`, `name`) VALUES (DEFAULT, 'logged_in');
INSERT INTO `prednasky`.`video_state` (`id`, `name`) VALUES (DEFAULT, 'public');

COMMIT;


-- -----------------------------------------------------
-- Data for table `prednasky`.`tag`
-- -----------------------------------------------------
START TRANSACTION;
USE `prednasky`;
INSERT INTO `prednasky`.`tag` (`id`, `name`, `value`) VALUES (DEFAULT, 'branch', NULL);
INSERT INTO `prednasky`.`tag` (`id`, `name`, `value`) VALUES (DEFAULT, 'semester', NULL);
INSERT INTO `prednasky`.`tag` (`id`, `name`, `value`) VALUES (DEFAULT, 'course', NULL);
INSERT INTO `prednasky`.`tag` (`id`, `name`, `value`) VALUES (DEFAULT, 'type', NULL);

COMMIT;


-- -----------------------------------------------------
-- Data for table `prednasky`.`role`
-- -----------------------------------------------------
START TRANSACTION;
USE `prednasky`;
INSERT INTO `prednasky`.`role` (`id`, `name`) VALUES (DEFAULT, 'guarantor');
INSERT INTO `prednasky`.`role` (`id`, `name`) VALUES (DEFAULT, 'lecturer');
INSERT INTO `prednasky`.`role` (`id`, `name`) VALUES (DEFAULT, 'owner');

COMMIT;


-- -----------------------------------------------------
-- Data for table `prednasky`.`relation_type`
-- -----------------------------------------------------
START TRANSACTION;
USE `prednasky`;
INSERT INTO `prednasky`.`relation_type` (`id`, `name`) VALUES (DEFAULT, 'next_video');
INSERT INTO `prednasky`.`relation_type` (`id`, `name`) VALUES (DEFAULT, 'prev_video');
INSERT INTO `prednasky`.`relation_type` (`id`, `name`) VALUES (DEFAULT, 'related_video');

COMMIT;


-- -----------------------------------------------------
-- Data for table `prednasky`.`token_state`
-- -----------------------------------------------------
START TRANSACTION;
USE `prednasky`;
INSERT INTO `prednasky`.`token_state` (`id`, `name`) VALUES (DEFAULT, 'submitted');
INSERT INTO `prednasky`.`token_state` (`id`, `name`) VALUES (DEFAULT, 'start');
INSERT INTO `prednasky`.`token_state` (`id`, `name`) VALUES (DEFAULT, 'error');
INSERT INTO `prednasky`.`token_state` (`id`, `name`) VALUES (DEFAULT, 'done');
INSERT INTO `prednasky`.`token_state` (`id`, `name`) VALUES (DEFAULT, 'info');

COMMIT;


-- -----------------------------------------------------
-- Data for table `prednasky`.`token_type`
-- -----------------------------------------------------
START TRANSACTION;
USE `prednasky`;
INSERT INTO `prednasky`.`token_type` (`id`, `name`) VALUES (DEFAULT, 'video');

COMMIT;


-- -----------------------------------------------------
-- Data for table `prednasky`.`template`
-- -----------------------------------------------------
START TRANSACTION;
USE `prednasky`;
INSERT INTO `prednasky`.`template` (`id`, `name`, `blocks`, `description`) VALUES (DEFAULT, 'config_youtube_downloader.ini', 'msg_download_start;youtubedl;msg_download_end;avprobe;msg_input_audiolength;msg_input_videolength;msg_input_hasaudio;msg_input_hasvideo;has_audio;has_video;msg_video_start;msg_video_end;convert_video;thumbnail_video;copyresults_video;avprobe_outmedia;msg_output_videolength;msg_output_audiolength;finish', 'Download video from YouTube');
INSERT INTO `prednasky`.`template` (`id`, `name`, `blocks`, `description`) VALUES (DEFAULT, 'config_video_convert.ini', 'avprobe;msg_input_audiolength;msg_input_videolength;msg_input_hasaudio;msg_input_hasvideo;has_audio;has_video;msg_video_start;msg_video_end;convert_video;thumbnail_video;copyresults_video;avprobe_outmedia;msg_output_videolength;msg_output_audiolength;finish', 'Convert video into MP4');

COMMIT;