database-updates.sql 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285
  1. -- MySQL Workbench Synchronization
  2. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  3. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  4. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
  5. -- BEGIN HERE --
  6. -- 1.0 => 1.1 --
  7. ALTER TABLE `prednasky`.`user_has_video`
  8. ADD COLUMN `show_email` TINYINT(1) NOT NULL AFTER `role_id`;
  9. -- 1.1 => 1.2 --
  10. ALTER TABLE `prednasky`.`tag`
  11. CHANGE COLUMN `value` `value` VARCHAR(100) NULL DEFAULT NULL ,
  12. ADD UNIQUE INDEX `tag_UNIQUE` (`name` ASC, `value` ASC),
  13. DROP INDEX `name_UNIQUE` ;
  14. DELIMITER $$
  15. USE `prednasky`$$
  16. CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
  17. RETURN "1.2";$$
  18. DELIMITER ;
  19. -- 1.2 => 1.3 --
  20. ALTER TABLE `prednasky`.`token`
  21. ADD COLUMN `template` INT(10) UNSIGNED NOT NULL AFTER `type`,
  22. ADD COLUMN `pending_blocks` TEXT NOT NULL AFTER `created`,
  23. ADD COLUMN `current_state` VARCHAR(45) NULL DEFAULT NULL AFTER `last_update`,
  24. ADD INDEX `fk_token_template1_idx` (`template` ASC);
  25. CREATE TABLE IF NOT EXISTS `prednasky`.`template` (
  26. `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  27. `name` VARCHAR(45) NOT NULL,
  28. `blocks` TEXT NOT NULL,
  29. `description` TEXT NULL DEFAULT NULL,
  30. PRIMARY KEY (`id`),
  31. UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  32. UNIQUE INDEX `name_UNIQUE` (`name` ASC))
  33. ENGINE = InnoDB
  34. DEFAULT CHARACTER SET = utf8;
  35. ALTER TABLE `prednasky`.`token`
  36. ADD CONSTRAINT `fk_token_template1`
  37. FOREIGN KEY (`template`)
  38. REFERENCES `prednasky`.`template` (`id`)
  39. ON DELETE NO ACTION
  40. ON UPDATE NO ACTION;
  41. ALTER TABLE `prednasky`.`file`
  42. CHANGE COLUMN `type` `type` VARCHAR(45) NOT NULL ;
  43. DROP function IF EXISTS `prednasky`.`database_version`;
  44. DELIMITER $$
  45. CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
  46. RETURN "1.3";$$
  47. DELIMITER ;
  48. 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');
  49. -- 1.3 => 1.4 --
  50. ALTER TABLE `prednasky`.`user`
  51. DROP COLUMN `surname`,
  52. CHANGE COLUMN `name` `fullname` VARCHAR(100) NOT NULL ;
  53. ALTER TABLE `prednasky`.`video_has_file`
  54. ADD INDEX `fk_video_has_file_file1_idx` (`file_id` ASC),
  55. DROP INDEX `fk_video_has_file_file1_idx` ;
  56. ALTER TABLE `prednasky`.`user_has_video`
  57. DROP PRIMARY KEY,
  58. ADD PRIMARY KEY (`user_id`, `video_id`, `role_id`);
  59. DROP function IF EXISTS `prednasky`.`database_version`;
  60. DELIMITER $$
  61. CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
  62. RETURN "1.4";$$
  63. DELIMITER ;
  64. -- 1.4 => 1.5 --
  65. CREATE TABLE IF NOT EXISTS `prednasky`.`right` (
  66. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  67. `user_id` INT UNSIGNED NOT NULL,
  68. INDEX `fk_right_user1_idx` (`user_id` ASC),
  69. PRIMARY KEY (`id`),
  70. UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  71. CONSTRAINT `fk_right_user1`
  72. FOREIGN KEY (`user_id`)
  73. REFERENCES `prednasky`.`user` (`id`)
  74. ON DELETE NO ACTION
  75. ON UPDATE NO ACTION)
  76. ENGINE = InnoDB
  77. DEFAULT CHARACTER SET = utf8;
  78. CREATE TABLE IF NOT EXISTS `prednasky`.`right_has_tag` (
  79. `right_id` INT(10) UNSIGNED NOT NULL,
  80. `tag_id` INT(10) UNSIGNED NOT NULL,
  81. PRIMARY KEY (`right_id`, `tag_id`),
  82. INDEX `fk_right_has_tag_tag1_idx` (`tag_id` ASC),
  83. INDEX `fk_right_has_tag_right1_idx` (`right_id` ASC),
  84. CONSTRAINT `fk_right_has_tag_right1`
  85. FOREIGN KEY (`right_id`)
  86. REFERENCES `prednasky`.`right` (`id`)
  87. ON DELETE NO ACTION
  88. ON UPDATE NO ACTION,
  89. CONSTRAINT `fk_right_has_tag_tag1`
  90. FOREIGN KEY (`tag_id`)
  91. REFERENCES `prednasky`.`tag` (`id`)
  92. ON DELETE NO ACTION
  93. ON UPDATE NO ACTION)
  94. ENGINE = InnoDB
  95. DEFAULT CHARACTER SET = utf8;
  96. DROP function IF EXISTS `prednasky`.`database_version`;
  97. DELIMITER $$
  98. USE `prednasky`$$
  99. CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
  100. RETURN "1.5";$$
  101. DELIMITER ;
  102. -- 1.5 => 1.6 --
  103. ALTER TABLE `prednasky`.`video`
  104. ADD COLUMN `complete` TINYINT(1) NOT NULL AFTER `state`;
  105. UPDATE `prednasky`.`video_state` SET `name`='private' WHERE `id`=1;
  106. UPDATE `prednasky`.`video_state` SET `name`='logged_in' WHERE `id`=2;
  107. UPDATE `prednasky`.`video` SET `state`=1 WHERE `state`=2;
  108. UPDATE `prednasky`.`video` SET `state`=1 WHERE `state`=3;
  109. DELETE FROM `prednasky`.`video_state` WHERE `id`=3;
  110. UPDATE `prednasky`.`video` SET `state`=2 WHERE `state`=4;
  111. DELETE FROM `prednasky`.`video_state` WHERE `id`=4;
  112. START TRANSACTION;
  113. INSERT INTO `prednasky`.`video_state` (`id`, `name`) VALUES ('3', 'public');
  114. UPDATE `prednasky`.`video` SET `state`='3' WHERE `state`=5;
  115. DELETE FROM `prednasky`.`video_state` WHERE `id`=5;
  116. COMMIT;
  117. DROP function IF EXISTS `prednasky`.`database_version`;
  118. DELIMITER $$
  119. USE `prednasky`$$
  120. CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
  121. RETURN "1.6";$$
  122. -- 1.6 => 1.7 --
  123. ALTER TABLE `prednasky`.`right` DROP FOREIGN KEY `fk_right_user1`;
  124. ALTER TABLE `prednasky`.`token` DROP FOREIGN KEY `fk_token_video1`;
  125. ALTER TABLE `prednasky`.`video_has_tag` DROP FOREIGN KEY `fk_video_has_tag_video1`;
  126. ALTER TABLE `prednasky`.`user_has_video` DROP FOREIGN KEY `fk_user_has_video_video1`;
  127. ALTER TABLE `prednasky`.`video_has_file` DROP FOREIGN KEY `fk_video_has_file_video1`, DROP FOREIGN KEY `fk_video_has_file_file1`;
  128. ALTER TABLE `prednasky`.`video_relation` DROP FOREIGN KEY `fk_video_has_video_video1`, DROP FOREIGN KEY `fk_video_has_video_video2`;
  129. ALTER TABLE `prednasky`.`video_has_file`
  130. ADD CONSTRAINT `fk_video_has_file_video1`
  131. FOREIGN KEY (`video_id`)
  132. REFERENCES `prednasky`.`video` (`id`)
  133. ON DELETE CASCADE
  134. ON UPDATE NO ACTION,
  135. ADD CONSTRAINT `fk_video_has_file_file1`
  136. FOREIGN KEY (`file_id`)
  137. REFERENCES `prednasky`.`file` (`id`)
  138. ON DELETE CASCADE
  139. ON UPDATE NO ACTION;
  140. ALTER TABLE `prednasky`.`user_has_video`
  141. ADD CONSTRAINT `fk_user_has_video_video1`
  142. FOREIGN KEY (`video_id`)
  143. REFERENCES `prednasky`.`video` (`id`)
  144. ON DELETE CASCADE
  145. ON UPDATE NO ACTION;
  146. ALTER TABLE `prednasky`.`video_relation`
  147. ADD CONSTRAINT `fk_video_has_video_video1`
  148. FOREIGN KEY (`video_from`)
  149. REFERENCES `prednasky`.`video` (`id`)
  150. ON DELETE CASCADE
  151. ON UPDATE NO ACTION,
  152. ADD CONSTRAINT `fk_video_has_video_video2`
  153. FOREIGN KEY (`video_to`)
  154. REFERENCES `prednasky`.`video` (`id`)
  155. ON DELETE CASCADE
  156. ON UPDATE NO ACTION;
  157. ALTER TABLE `prednasky`.`video_has_tag`
  158. ADD CONSTRAINT `fk_video_has_tag_video1`
  159. FOREIGN KEY (`video_id`)
  160. REFERENCES `prednasky`.`video` (`id`)
  161. ON DELETE CASCADE
  162. ON UPDATE NO ACTION;
  163. ALTER TABLE `prednasky`.`token`
  164. ADD CONSTRAINT `fk_token_video1`
  165. FOREIGN KEY (`video`)
  166. REFERENCES `prednasky`.`video` (`id`)
  167. ON DELETE CASCADE
  168. ON UPDATE NO ACTION;
  169. ALTER TABLE `prednasky`.`right`
  170. ADD CONSTRAINT `fk_user_has_tag_user1`
  171. FOREIGN KEY (`user_id`)
  172. REFERENCES `prednasky`.`user` (`id`)
  173. ON DELETE NO ACTION
  174. ON UPDATE NO ACTION,
  175. ADD CONSTRAINT `fk_user_has_tag_tag1`
  176. FOREIGN KEY (`tag_id`)
  177. REFERENCES `prednasky`.`tag` (`id`)
  178. ON DELETE NO ACTION
  179. ON UPDATE NO ACTION;
  180. DROP function IF EXISTS `prednasky`.`database_version`;
  181. DELIMITER $$
  182. USE `prednasky`$$
  183. CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
  184. RETURN "1.7";$$
  185. -- 1.7 => 1.8-
  186. ALTER TABLE `prednasky`.`video`
  187. DROP COLUMN `plane_width`,
  188. DROP COLUMN `plane_points`,
  189. CHANGE COLUMN `complete` `complete` TINYINT(1) NOT NULL ,
  190. ADD FULLTEXT INDEX `fulltext_name` (`name`),
  191. ADD FULLTEXT INDEX `fulltext_abstract` (`abstract`),
  192. ADD FULLTEXT INDEX `fulltext_name_abstract` (`name`, `abstract`);
  193. ;
  194. INSERT INTO `prednasky`.`role` (`id`, `name`) VALUES (DEFAULT, 'owner');
  195. DROP TABLE IF EXISTS `prednasky`.`right_has_tag`;
  196. DROP TABLE IF EXISTS `prednasky`.`right`;
  197. DROP function IF EXISTS `prednasky`.`database_version`;
  198. DELIMITER $$
  199. USE `prednasky`$$
  200. CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
  201. RETURN "1.8";$$
  202. -- 1.8 => 1.9 --
  203. ALTER TABLE `prednasky`.`user`
  204. DROP COLUMN `personal_web`,
  205. DROP COLUMN `institution`,
  206. CHANGE COLUMN `CAS_id` `CAS_id` VARCHAR(45) NULL DEFAULT NULL;
  207. ALTER TABLE `prednasky`.`video`
  208. CHANGE COLUMN `complete` `complete` TINYINT(1) NOT NULL ,
  209. ADD FULLTEXT INDEX `fulltext_name` (`name`),
  210. ADD FULLTEXT INDEX `fulltext_abstract` (`abstract`),
  211. ADD FULLTEXT INDEX `fulltext_name_abstract` (`name`, `abstract`);
  212. ;
  213. DROP function IF EXISTS `prednasky`.`database_version`;
  214. DELIMITER $$
  215. USE `prednasky`$$
  216. CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
  217. RETURN "1.9";$$
  218. DELIMITER ;
  219. -- 1.9 => 1.10 --
  220. ALTER TABLE `prednasky`.`video`
  221. DROP COLUMN `record_end`,
  222. DROP COLUMN `record_begin`,
  223. ADD COLUMN `record_date` DATE NULL DEFAULT NULL AFTER `published`,
  224. ADD COLUMN `record_time_begin` TIME NULL DEFAULT NULL AFTER `record_date`,
  225. ADD COLUMN `record_time_end` TIME NULL DEFAULT NULL AFTER `record_time_begin`,
  226. CHANGE COLUMN `created` `created` TIMESTAMP NOT NULL ,
  227. CHANGE COLUMN `complete` `complete` TINYINT(1) NOT NULL ,
  228. CHANGE COLUMN `duration` `duration` INT(10) UNSIGNED NULL DEFAULT NULL ,
  229. CHANGE COLUMN `public_link` `public_link` VARCHAR(100) NULL DEFAULT NULL ;
  230. ALTER TABLE `prednasky`.`file`
  231. ADD COLUMN `user` INT(10) UNSIGNED NULL DEFAULT NULL AFTER `path`,
  232. ADD INDEX `fk_file_user1_idx` (`user` ASC); ;
  233. ALTER TABLE `prednasky`.`video_has_file`
  234. DROP COLUMN `show`,
  235. ADD COLUMN `type` VARCHAR(45) NOT NULL COMMENT 'thumbnail, video, attachment' AFTER `file_id`;
  236. ALTER TABLE `prednasky`.`file`
  237. ADD CONSTRAINT `fk_file_user1`
  238. FOREIGN KEY (`user`)
  239. REFERENCES `prednasky`.`user` (`id`)
  240. ON DELETE NO ACTION
  241. ON UPDATE NO ACTION;
  242. DROP function IF EXISTS `prednasky`.`database_version`;
  243. DELIMITER $$
  244. USE `prednasky`$$
  245. CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
  246. RETURN "1.10";$$
  247. DELIMITER ;
  248. -- 1.10 => 1.11 --
  249. ALTER TABLE `prednasky`.`user`
  250. DROP COLUMN `active`,
  251. CHANGE COLUMN `right_group` `right_group` TINYINT(3) UNSIGNED NOT NULL COMMENT 'disabled, guest, student, teacher, admin';
  252. DROP function IF EXISTS `prednasky`.`database_version`;
  253. DELIMITER $$
  254. USE `prednasky`$$
  255. CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
  256. RETURN "1.11";$$
  257. DELIMITER ;
  258. -- 1.11 => 1.12 --
  259. ALTER TABLE `prednasky`.`video`
  260. CHANGE COLUMN `name` `name` VARCHAR(200) NOT NULL ;
  261. DROP function IF EXISTS `prednasky`.`database_version`;
  262. DELIMITER $$
  263. USE `prednasky`$$
  264. CREATE FUNCTION `database_version` () RETURNS varchar(5) CHARACTER SET 'utf8'
  265. RETURN "1.12";$$
  266. DELIMITER ;
  267. -- END HERE --
  268. SET SQL_MODE=@OLD_SQL_MODE;
  269. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  270. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;