database.sql 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466
  1. -- MySQL Script generated by MySQL Workbench
  2. -- Tue Feb 5 16:02:52 2019
  3. -- Model: Prednasky.com Version: 1.12
  4. -- MySQL Workbench Forward Engineering
  5. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  6. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  7. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
  8. -- -----------------------------------------------------
  9. -- Schema prednasky
  10. -- -----------------------------------------------------
  11. -- -----------------------------------------------------
  12. -- Schema prednasky
  13. -- -----------------------------------------------------
  14. CREATE SCHEMA IF NOT EXISTS `prednasky` DEFAULT CHARACTER SET utf8 ;
  15. USE `prednasky` ;
  16. -- -----------------------------------------------------
  17. -- Table `prednasky`.`user`
  18. -- -----------------------------------------------------
  19. DROP TABLE IF EXISTS `prednasky`.`user` ;
  20. CREATE TABLE IF NOT EXISTS `prednasky`.`user` (
  21. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  22. `CAS_id` VARCHAR(45) NULL,
  23. `fullname` VARCHAR(100) NOT NULL,
  24. `email` VARCHAR(45) NOT NULL,
  25. `right_group` TINYINT UNSIGNED NOT NULL COMMENT 'disabled, guest, student, teacher, admin',
  26. `last_login` VARCHAR(45) NULL,
  27. PRIMARY KEY (`id`),
  28. UNIQUE INDEX `id_UNIQUE` (`id` ASC))
  29. ENGINE = InnoDB
  30. DEFAULT CHARACTER SET = utf8
  31. COLLATE = utf8_czech_ci;
  32. -- -----------------------------------------------------
  33. -- Table `prednasky`.`video_state`
  34. -- -----------------------------------------------------
  35. DROP TABLE IF EXISTS `prednasky`.`video_state` ;
  36. CREATE TABLE IF NOT EXISTS `prednasky`.`video_state` (
  37. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  38. `name` VARCHAR(45) NOT NULL,
  39. PRIMARY KEY (`id`),
  40. UNIQUE INDEX `id_UNIQUE` (`id` ASC))
  41. ENGINE = InnoDB;
  42. -- -----------------------------------------------------
  43. -- Table `prednasky`.`video`
  44. -- -----------------------------------------------------
  45. DROP TABLE IF EXISTS `prednasky`.`video` ;
  46. CREATE TABLE IF NOT EXISTS `prednasky`.`video` (
  47. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  48. `name` VARCHAR(200) NOT NULL,
  49. `created` TIMESTAMP NOT NULL,
  50. `state` INT UNSIGNED NOT NULL,
  51. `complete` TINYINT(1) NOT NULL,
  52. `published` TIMESTAMP NULL,
  53. `record_date` DATE NULL,
  54. `record_time_begin` TIME NULL,
  55. `record_time_end` TIME NULL,
  56. `duration` INT UNSIGNED NULL,
  57. `abstract` TEXT NULL,
  58. `public_link` VARCHAR(100) NULL,
  59. PRIMARY KEY (`id`),
  60. UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  61. INDEX `fk_video_video_state1_idx` (`state` ASC),
  62. FULLTEXT INDEX `fulltext_name` (`name`),
  63. FULLTEXT INDEX `fulltext_abstract` (`abstract`),
  64. FULLTEXT INDEX `fulltext_name_abstract` (`name`, `abstract`),
  65. CONSTRAINT `fk_video_video_state1`
  66. FOREIGN KEY (`state`)
  67. REFERENCES `prednasky`.`video_state` (`id`)
  68. ON DELETE NO ACTION
  69. ON UPDATE NO ACTION)
  70. ENGINE = InnoDB
  71. DEFAULT CHARACTER SET = utf8
  72. COLLATE = utf8_czech_ci;
  73. -- -----------------------------------------------------
  74. -- Table `prednasky`.`tag`
  75. -- -----------------------------------------------------
  76. DROP TABLE IF EXISTS `prednasky`.`tag` ;
  77. CREATE TABLE IF NOT EXISTS `prednasky`.`tag` (
  78. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  79. `name` VARCHAR(45) NOT NULL,
  80. `value` VARCHAR(100) NULL,
  81. PRIMARY KEY (`id`),
  82. UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  83. UNIQUE INDEX `tag_UNIQUE` (`name` ASC, `value` ASC))
  84. ENGINE = InnoDB
  85. DEFAULT CHARACTER SET = utf8
  86. COLLATE = utf8_czech_ci;
  87. -- -----------------------------------------------------
  88. -- Table `prednasky`.`file`
  89. -- -----------------------------------------------------
  90. DROP TABLE IF EXISTS `prednasky`.`file` ;
  91. CREATE TABLE IF NOT EXISTS `prednasky`.`file` (
  92. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  93. `type` VARCHAR(45) NOT NULL,
  94. `path` VARCHAR(250) NOT NULL,
  95. `user` INT UNSIGNED NULL,
  96. `name` VARCHAR(45) NULL,
  97. `downloads` INT UNSIGNED NULL,
  98. `uploaded` TIMESTAMP NULL,
  99. PRIMARY KEY (`id`),
  100. UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  101. INDEX `fk_file_user1_idx` (`user` ASC),
  102. CONSTRAINT `fk_file_user1`
  103. FOREIGN KEY (`user`)
  104. REFERENCES `prednasky`.`user` (`id`)
  105. ON DELETE NO ACTION
  106. ON UPDATE NO ACTION)
  107. ENGINE = InnoDB
  108. DEFAULT CHARACTER SET = utf8
  109. COLLATE = utf8_czech_ci;
  110. -- -----------------------------------------------------
  111. -- Table `prednasky`.`video_has_file`
  112. -- -----------------------------------------------------
  113. DROP TABLE IF EXISTS `prednasky`.`video_has_file` ;
  114. CREATE TABLE IF NOT EXISTS `prednasky`.`video_has_file` (
  115. `video_id` INT UNSIGNED NOT NULL,
  116. `file_id` INT UNSIGNED NOT NULL,
  117. `type` VARCHAR(45) NOT NULL COMMENT 'thumbnail, video, attachment',
  118. PRIMARY KEY (`video_id`, `file_id`),
  119. INDEX `fk_video_has_file_video1_idx` (`video_id` ASC),
  120. INDEX `fk_video_has_file_file1_idx` (`file_id` ASC),
  121. CONSTRAINT `fk_video_has_file_video1`
  122. FOREIGN KEY (`video_id`)
  123. REFERENCES `prednasky`.`video` (`id`)
  124. ON DELETE CASCADE
  125. ON UPDATE NO ACTION,
  126. CONSTRAINT `fk_video_has_file_file1`
  127. FOREIGN KEY (`file_id`)
  128. REFERENCES `prednasky`.`file` (`id`)
  129. ON DELETE CASCADE
  130. ON UPDATE NO ACTION)
  131. ENGINE = InnoDB
  132. DEFAULT CHARACTER SET = utf8
  133. COLLATE = utf8_czech_ci;
  134. -- -----------------------------------------------------
  135. -- Table `prednasky`.`role`
  136. -- -----------------------------------------------------
  137. DROP TABLE IF EXISTS `prednasky`.`role` ;
  138. CREATE TABLE IF NOT EXISTS `prednasky`.`role` (
  139. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  140. `name` VARCHAR(45) NOT NULL,
  141. PRIMARY KEY (`id`),
  142. UNIQUE INDEX `id_UNIQUE` (`id` ASC))
  143. ENGINE = InnoDB
  144. DEFAULT CHARACTER SET = utf8
  145. COLLATE = utf8_czech_ci;
  146. -- -----------------------------------------------------
  147. -- Table `prednasky`.`user_has_video`
  148. -- -----------------------------------------------------
  149. DROP TABLE IF EXISTS `prednasky`.`user_has_video` ;
  150. CREATE TABLE IF NOT EXISTS `prednasky`.`user_has_video` (
  151. `user_id` INT UNSIGNED NOT NULL,
  152. `video_id` INT UNSIGNED NOT NULL,
  153. `role_id` INT UNSIGNED NOT NULL,
  154. `show_email` TINYINT(1) NOT NULL,
  155. PRIMARY KEY (`user_id`, `video_id`, `role_id`),
  156. INDEX `fk_user_has_video_video1_idx` (`video_id` ASC),
  157. INDEX `fk_user_has_video_user1_idx` (`user_id` ASC),
  158. INDEX `fk_user_has_video_participant1_idx` (`role_id` ASC),
  159. CONSTRAINT `fk_user_has_video_user1`
  160. FOREIGN KEY (`user_id`)
  161. REFERENCES `prednasky`.`user` (`id`)
  162. ON DELETE NO ACTION
  163. ON UPDATE NO ACTION,
  164. CONSTRAINT `fk_user_has_video_video1`
  165. FOREIGN KEY (`video_id`)
  166. REFERENCES `prednasky`.`video` (`id`)
  167. ON DELETE CASCADE
  168. ON UPDATE NO ACTION,
  169. CONSTRAINT `fk_user_has_video_participant1`
  170. FOREIGN KEY (`role_id`)
  171. REFERENCES `prednasky`.`role` (`id`)
  172. ON DELETE NO ACTION
  173. ON UPDATE NO ACTION)
  174. ENGINE = InnoDB
  175. DEFAULT CHARACTER SET = utf8
  176. COLLATE = utf8_czech_ci;
  177. -- -----------------------------------------------------
  178. -- Table `prednasky`.`relation_type`
  179. -- -----------------------------------------------------
  180. DROP TABLE IF EXISTS `prednasky`.`relation_type` ;
  181. CREATE TABLE IF NOT EXISTS `prednasky`.`relation_type` (
  182. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  183. `name` VARCHAR(45) NOT NULL,
  184. PRIMARY KEY (`id`),
  185. UNIQUE INDEX `id_UNIQUE` (`id` ASC))
  186. ENGINE = InnoDB;
  187. -- -----------------------------------------------------
  188. -- Table `prednasky`.`video_relation`
  189. -- -----------------------------------------------------
  190. DROP TABLE IF EXISTS `prednasky`.`video_relation` ;
  191. CREATE TABLE IF NOT EXISTS `prednasky`.`video_relation` (
  192. `video_from` INT UNSIGNED NOT NULL,
  193. `video_to` INT UNSIGNED NOT NULL,
  194. `relation_type_id` INT UNSIGNED NOT NULL,
  195. PRIMARY KEY (`video_from`, `video_to`, `relation_type_id`),
  196. INDEX `fk_video_has_video_video2_idx` (`video_to` ASC),
  197. INDEX `fk_video_has_video_video1_idx` (`video_from` ASC),
  198. INDEX `fk_video_relation_relation_type1_idx` (`relation_type_id` ASC),
  199. CONSTRAINT `fk_video_has_video_video1`
  200. FOREIGN KEY (`video_from`)
  201. REFERENCES `prednasky`.`video` (`id`)
  202. ON DELETE CASCADE
  203. ON UPDATE NO ACTION,
  204. CONSTRAINT `fk_video_has_video_video2`
  205. FOREIGN KEY (`video_to`)
  206. REFERENCES `prednasky`.`video` (`id`)
  207. ON DELETE CASCADE
  208. ON UPDATE NO ACTION,
  209. CONSTRAINT `fk_video_relation_relation_type1`
  210. FOREIGN KEY (`relation_type_id`)
  211. REFERENCES `prednasky`.`relation_type` (`id`)
  212. ON DELETE NO ACTION
  213. ON UPDATE NO ACTION)
  214. ENGINE = InnoDB
  215. DEFAULT CHARACTER SET = utf8
  216. COLLATE = utf8_czech_ci;
  217. -- -----------------------------------------------------
  218. -- Table `prednasky`.`video_has_tag`
  219. -- -----------------------------------------------------
  220. DROP TABLE IF EXISTS `prednasky`.`video_has_tag` ;
  221. CREATE TABLE IF NOT EXISTS `prednasky`.`video_has_tag` (
  222. `video_id` INT UNSIGNED NOT NULL,
  223. `tag_id` INT UNSIGNED NOT NULL,
  224. PRIMARY KEY (`video_id`, `tag_id`),
  225. INDEX `fk_video_has_tag_tag1_idx` (`tag_id` ASC),
  226. INDEX `fk_video_has_tag_video1_idx` (`video_id` ASC),
  227. CONSTRAINT `fk_video_has_tag_video1`
  228. FOREIGN KEY (`video_id`)
  229. REFERENCES `prednasky`.`video` (`id`)
  230. ON DELETE CASCADE
  231. ON UPDATE NO ACTION,
  232. CONSTRAINT `fk_video_has_tag_tag1`
  233. FOREIGN KEY (`tag_id`)
  234. REFERENCES `prednasky`.`tag` (`id`)
  235. ON DELETE NO ACTION
  236. ON UPDATE NO ACTION)
  237. ENGINE = InnoDB
  238. DEFAULT CHARACTER SET = utf8
  239. COLLATE = utf8_czech_ci;
  240. -- -----------------------------------------------------
  241. -- Table `prednasky`.`token_state`
  242. -- -----------------------------------------------------
  243. DROP TABLE IF EXISTS `prednasky`.`token_state` ;
  244. CREATE TABLE IF NOT EXISTS `prednasky`.`token_state` (
  245. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  246. `name` VARCHAR(45) NOT NULL,
  247. PRIMARY KEY (`id`),
  248. UNIQUE INDEX `id_UNIQUE` (`id` ASC))
  249. ENGINE = InnoDB;
  250. -- -----------------------------------------------------
  251. -- Table `prednasky`.`token_type`
  252. -- -----------------------------------------------------
  253. DROP TABLE IF EXISTS `prednasky`.`token_type` ;
  254. CREATE TABLE IF NOT EXISTS `prednasky`.`token_type` (
  255. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  256. `name` VARCHAR(50) NOT NULL,
  257. PRIMARY KEY (`id`),
  258. UNIQUE INDEX `id_UNIQUE` (`id` ASC))
  259. ENGINE = InnoDB;
  260. -- -----------------------------------------------------
  261. -- Table `prednasky`.`template`
  262. -- -----------------------------------------------------
  263. DROP TABLE IF EXISTS `prednasky`.`template` ;
  264. CREATE TABLE IF NOT EXISTS `prednasky`.`template` (
  265. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  266. `name` VARCHAR(45) NOT NULL,
  267. `blocks` TEXT NOT NULL,
  268. `description` TEXT NULL,
  269. PRIMARY KEY (`id`),
  270. UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  271. UNIQUE INDEX `name_UNIQUE` (`name` ASC))
  272. ENGINE = InnoDB;
  273. -- -----------------------------------------------------
  274. -- Table `prednasky`.`token`
  275. -- -----------------------------------------------------
  276. DROP TABLE IF EXISTS `prednasky`.`token` ;
  277. CREATE TABLE IF NOT EXISTS `prednasky`.`token` (
  278. `id` VARCHAR(41) NOT NULL,
  279. `state` INT UNSIGNED NOT NULL,
  280. `type` INT UNSIGNED NOT NULL,
  281. `template` INT UNSIGNED NOT NULL,
  282. `video` INT UNSIGNED NOT NULL,
  283. `public_hash` VARCHAR(32) NOT NULL,
  284. `private_hash` VARCHAR(32) NOT NULL,
  285. `created` TIMESTAMP NOT NULL,
  286. `pending_blocks` TEXT NOT NULL,
  287. `last_update` TIMESTAMP NULL,
  288. `current_state` VARCHAR(45) NULL,
  289. PRIMARY KEY (`id`),
  290. UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  291. INDEX `fk_token_token_state1_idx` (`state` ASC),
  292. INDEX `fk_token_token_type1_idx` (`type` ASC),
  293. INDEX `fk_token_video1_idx` (`video` ASC),
  294. INDEX `fk_token_template1_idx` (`template` ASC),
  295. CONSTRAINT `fk_token_token_state1`
  296. FOREIGN KEY (`state`)
  297. REFERENCES `prednasky`.`token_state` (`id`)
  298. ON DELETE NO ACTION
  299. ON UPDATE NO ACTION,
  300. CONSTRAINT `fk_token_token_type1`
  301. FOREIGN KEY (`type`)
  302. REFERENCES `prednasky`.`token_type` (`id`)
  303. ON DELETE NO ACTION
  304. ON UPDATE NO ACTION,
  305. CONSTRAINT `fk_token_video1`
  306. FOREIGN KEY (`video`)
  307. REFERENCES `prednasky`.`video` (`id`)
  308. ON DELETE CASCADE
  309. ON UPDATE NO ACTION,
  310. CONSTRAINT `fk_token_template1`
  311. FOREIGN KEY (`template`)
  312. REFERENCES `prednasky`.`template` (`id`)
  313. ON DELETE NO ACTION
  314. ON UPDATE NO ACTION)
  315. ENGINE = InnoDB;
  316. USE `prednasky` ;
  317. -- -----------------------------------------------------
  318. -- function database_version
  319. -- -----------------------------------------------------
  320. USE `prednasky`;
  321. DROP function IF EXISTS `prednasky`.`database_version`;
  322. DELIMITER $$
  323. USE `prednasky`$$
  324. CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
  325. RETURN "1.12";$$
  326. DELIMITER ;
  327. SET SQL_MODE=@OLD_SQL_MODE;
  328. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  329. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  330. -- -----------------------------------------------------
  331. -- Data for table `prednasky`.`video_state`
  332. -- -----------------------------------------------------
  333. START TRANSACTION;
  334. USE `prednasky`;
  335. INSERT INTO `prednasky`.`video_state` (`id`, `name`) VALUES (DEFAULT, 'private');
  336. INSERT INTO `prednasky`.`video_state` (`id`, `name`) VALUES (DEFAULT, 'logged_in');
  337. INSERT INTO `prednasky`.`video_state` (`id`, `name`) VALUES (DEFAULT, 'public');
  338. COMMIT;
  339. -- -----------------------------------------------------
  340. -- Data for table `prednasky`.`tag`
  341. -- -----------------------------------------------------
  342. START TRANSACTION;
  343. USE `prednasky`;
  344. INSERT INTO `prednasky`.`tag` (`id`, `name`, `value`) VALUES (DEFAULT, 'branch', NULL);
  345. INSERT INTO `prednasky`.`tag` (`id`, `name`, `value`) VALUES (DEFAULT, 'semester', NULL);
  346. INSERT INTO `prednasky`.`tag` (`id`, `name`, `value`) VALUES (DEFAULT, 'course', NULL);
  347. INSERT INTO `prednasky`.`tag` (`id`, `name`, `value`) VALUES (DEFAULT, 'type', NULL);
  348. COMMIT;
  349. -- -----------------------------------------------------
  350. -- Data for table `prednasky`.`role`
  351. -- -----------------------------------------------------
  352. START TRANSACTION;
  353. USE `prednasky`;
  354. INSERT INTO `prednasky`.`role` (`id`, `name`) VALUES (DEFAULT, 'guarantor');
  355. INSERT INTO `prednasky`.`role` (`id`, `name`) VALUES (DEFAULT, 'lecturer');
  356. INSERT INTO `prednasky`.`role` (`id`, `name`) VALUES (DEFAULT, 'owner');
  357. COMMIT;
  358. -- -----------------------------------------------------
  359. -- Data for table `prednasky`.`relation_type`
  360. -- -----------------------------------------------------
  361. START TRANSACTION;
  362. USE `prednasky`;
  363. INSERT INTO `prednasky`.`relation_type` (`id`, `name`) VALUES (DEFAULT, 'next_video');
  364. INSERT INTO `prednasky`.`relation_type` (`id`, `name`) VALUES (DEFAULT, 'prev_video');
  365. INSERT INTO `prednasky`.`relation_type` (`id`, `name`) VALUES (DEFAULT, 'related_video');
  366. COMMIT;
  367. -- -----------------------------------------------------
  368. -- Data for table `prednasky`.`token_state`
  369. -- -----------------------------------------------------
  370. START TRANSACTION;
  371. USE `prednasky`;
  372. INSERT INTO `prednasky`.`token_state` (`id`, `name`) VALUES (DEFAULT, 'submitted');
  373. INSERT INTO `prednasky`.`token_state` (`id`, `name`) VALUES (DEFAULT, 'start');
  374. INSERT INTO `prednasky`.`token_state` (`id`, `name`) VALUES (DEFAULT, 'error');
  375. INSERT INTO `prednasky`.`token_state` (`id`, `name`) VALUES (DEFAULT, 'done');
  376. INSERT INTO `prednasky`.`token_state` (`id`, `name`) VALUES (DEFAULT, 'info');
  377. COMMIT;
  378. -- -----------------------------------------------------
  379. -- Data for table `prednasky`.`token_type`
  380. -- -----------------------------------------------------
  381. START TRANSACTION;
  382. USE `prednasky`;
  383. INSERT INTO `prednasky`.`token_type` (`id`, `name`) VALUES (DEFAULT, 'video');
  384. COMMIT;
  385. -- -----------------------------------------------------
  386. -- Data for table `prednasky`.`template`
  387. -- -----------------------------------------------------
  388. START TRANSACTION;
  389. USE `prednasky`;
  390. 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');
  391. 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');
  392. COMMIT;