diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/migrations/6.1.6_forum3.php | 400 |
1 files changed, 400 insertions, 0 deletions
diff --git a/db/migrations/6.1.6_forum3.php b/db/migrations/6.1.6_forum3.php new file mode 100644 index 0000000..914fae2 --- /dev/null +++ b/db/migrations/6.1.6_forum3.php @@ -0,0 +1,400 @@ +<?php +class Forum3 extends Migration +{ + public function description() + { + return "A new version of the forum for Stud.IP."; + } + + public function up() + { + \DBManager::get()->exec(" + ALTER TABLE `forum_categories` + CHANGE `seminar_id` `range_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + CHANGE `pos` `position` int(11) NOT NULL DEFAULT 0, + CHANGE `entry_name` `name` varchar(255) NOT NULL, + ADD COLUMN `description` text DEFAULT NULL AFTER `name`, + ADD COLUMN `color` VARCHAR(7) AFTER `description`, + ADD COLUMN `chdate` INT(11) DEFAULT NULL AFTER `position`, + ADD COLUMN `mkdate` INT(11) DEFAULT NULL AFTER `chdate` + "); + + \DBManager::get()->exec(" + ALTER TABLE `forum_entries` + ADD KEY `lft` (`lft`), + ADD KEY `rgt` (`rgt`) + "); + + \DBManager::get()->exec(" + CREATE TABLE IF NOT EXISTS `forum_topics` ( + `topic_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `category_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `range_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `name` text NOT NULL, + `description` text DEFAULT NULL, + `position` INT(11) NOT NULL DEFAULT 0, + `chdate` INT(11) NOT NULL, + `mkdate` INT(11) NOT NULL, + PRIMARY KEY (`topic_id`) + ) + "); + + \DBManager::get()->exec(" + INSERT INTO `forum_topics` (`topic_id`, `category_id`, `range_id`, `name`, `description`, `position`, `chdate`, `mkdate`) + SELECT `forum_entries`.`topic_id`, + `forum_categories_entries`.`category_id`, + `forum_entries`.`seminar_id`, + `forum_entries`.`name`, + `forum_entries`.`content`, + IFNULL(`forum_categories_entries`.`pos`, 0), + `forum_entries`.`latest_chdate`, + `forum_entries`.`mkdate` + FROM `forum_entries` + LEFT JOIN `forum_categories_entries` ON (`forum_categories_entries`.`topic_id` = `forum_entries`.`topic_id`) + WHERE `forum_entries`.`depth` = 1 + GROUP BY `forum_entries`.`topic_id` + "); + + \DBManager::get()->exec(" + CREATE TABLE IF NOT EXISTS `forum_discussions` ( + `discussion_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `topic_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `type_id` INT(11) DEFAULT NULL, + `title` text NOT NULL, + `sticky` tinyINT(1) NOT NULL DEFAULT 0, + `closed_at` INT(11), + `view_count` INT NOT NULL DEFAULT 0, + `chdate` INT(11) NOT NULL, + `mkdate` INT(11) NOT NULL, + PRIMARY KEY (`discussion_id`), + KEY `topic_id` (`topic_id`) + ) + "); + \DBManager::get()->exec(" + INSERT INTO `forum_discussions` (`discussion_id`, `topic_id`, `sticky`, `title`, `chdate`, `mkdate`) + SELECT `forum_entries`.`topic_id`, + `parent_fe`.`topic_id`, + `forum_entries`.`sticky`, + `forum_entries`.`name`, + `forum_entries`.`latest_chdate`, + `forum_entries`.`mkdate` + FROM `forum_entries` + LEFT JOIN `forum_entries` AS `parent_fe` ON (`parent_fe`.depth = 1 AND `parent_fe`.`lft` < `forum_entries`.`lft` AND `parent_fe`.`rgt` > `forum_entries`.`rgt` AND `parent_fe`.`seminar_id` = `forum_entries`.`seminar_id`) + WHERE `forum_entries`.`depth` = 2 + GROUP BY `forum_entries`.`topic_id` + "); + + \DBManager::get()->exec(" + CREATE TABLE `forum_postings` ( + `posting_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `discussion_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `range_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `parent_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin, + `content` TEXT NOT NULL, + `quote` TEXT, + `user_id` CHAR(32) NOT NULL, + `anonymous` tinyINT(1) NOT NULL DEFAULT 0, + `chdate` INT(11) NOT NULL, + `mkdate` INT(11) NOT NULL, + PRIMARY KEY (`posting_id`), + KEY `discussion_id` (`discussion_id`) + ) + "); + + \DBManager::get()->exec(" + INSERT INTO `forum_postings` (`posting_id`, `discussion_id`, `range_id`, `content`, `user_id`, `anonymous`, `chdate`, `mkdate`) + SELECT `forum_entries`.`topic_id`, + `forum_entries`.`topic_id`, + `forum_entries`.`seminar_id`, + `forum_entries`.`content`, + `forum_entries`.`user_id`, + `forum_entries`.`anonymous`, + `forum_entries`.`latest_chdate`, + `forum_entries`.`mkdate` + FROM `forum_entries` + LEFT JOIN `forum_entries` AS `parent_fe` ON (`parent_fe`.depth = 1 AND `parent_fe`.`lft` < `forum_entries`.`lft` AND `parent_fe`.`rgt` > `forum_entries`.`rgt` AND `parent_fe`.`seminar_id` = `forum_entries`.`seminar_id`) + WHERE `forum_entries`.`depth` = 2 + GROUP BY `forum_entries`.`topic_id` + "); + + \DBManager::get()->exec(" + INSERT INTO `forum_postings` (`posting_id`, `discussion_id`, `range_id`, `content`, `user_id`, `anonymous`, `chdate`, `mkdate`) + SELECT `forum_entries`.`topic_id`, + `parent_fe`.`topic_id`, + `forum_entries`.`seminar_id`, + `forum_entries`.`content`, + `forum_entries`.`user_id`, + `forum_entries`.`anonymous`, + `forum_entries`.`latest_chdate`, + `forum_entries`.`mkdate` + FROM `forum_entries` + LEFT JOIN `forum_entries` AS `parent_fe` ON (`parent_fe`.depth = 2 AND `parent_fe`.`lft` < `forum_entries`.`lft` AND `parent_fe`.`rgt` > `forum_entries`.`rgt` AND `parent_fe`.`seminar_id` = `forum_entries`.`seminar_id`) + WHERE `forum_entries`.`depth` = 3 + GROUP BY `forum_entries`.`topic_id` + "); + + \DBManager::get()->exec(" + CREATE TABLE `forum_discussion_types` ( + `type_id` INT(11) unsigned NOT NULL AUTO_INCREMENT, + `name` varCHAR(255) NOT NULL, + `icon` varCHAR(50) DEFAULT NULL, + `chdate` INT(11) NOT NULL, + `mkdate` INT(11) NOT NULL, + PRIMARY KEY (`type_id`) + ) + "); + + \DBManager::get()->exec(" + CREATE TABLE `forum_posting_reactions` ( + `id` INT(11) unsigned NOT NULL AUTO_INCREMENT, + `posting_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `user_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `emoji` varCHAR(50) DEFAULT NULL, + `chdate` INT(11) NOT NULL, + `mkdate` INT(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `posting_id` (`posting_id`), + KEY `user_id` (`user_id`), + KEY `emoji` (`emoji`) + ) + "); + \DBManager::get()->exec(" + INSERT INTO `forum_posting_reactions` (`posting_id`, `user_id`, `emoji`, `chdate`, `mkdate`) + SELECT `topic_id`, `user_id`, 'THUMBS UP SIGN', UNIX_TIMESTAMP(), UNIX_TIMESTAMP() + FROM `forum_likes` + "); // THUMBS UP SIGN, THUMBS DOWN SIGN, ROCKET, GRINNING FACE, SMILING FACE WITH SUNGLASSES, CONFUSED FACE, BLACK HEART SUIT, PARTY POPPER + + \DBManager::get()->exec(" + CREATE TABLE `forum_posting_reads` ( + `discussion_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `user_id` CHAR(32) NOT NULL, + `read_index` INT(11) NOT NULL DEFAULT 0, + `chdate` INT(11) NOT NULL, + PRIMARY KEY (`discussion_id`, `user_id`) + ) + "); + + \DBManager::get()->exec(" + CREATE TABLE `forum_subscriptions` ( + `id` INT(11) unsigned NOT NULL AUTO_INCREMENT, + `user_id` CHAR(32) NOT NULL, + `range_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `subject_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `subject` ENUM('discussion', 'topic') NOT NULL DEFAULT 'discussion', + `notification_type` ENUM('all', 'replies_only', 'none') NOT NULL DEFAULT 'all', + `chdate` INT(11) NOT NULL, + `mkdate` INT(11) NOT NULL, + PRIMARY KEY (`id`) + ) + "); + + $insertConfigSql = "INSERT IGNORE INTO `config` VALUES (:field, :value, :type, :range, :section, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), :description)"; + + \DBManager::get()->execute( + $insertConfigSql, + [ + 'field' => 'FORUM_MODERATION_PERMISSION', + 'value' => 'dozent', + 'type' => 'string', + 'range' => 'course', + 'section' => 'Forum', + 'description' => 'Status, den es braucht, um das Forum zu moderieren.' + ] + ); + + \DBManager::get()->execute( + $insertConfigSql, + [ + 'field' => 'FORUM_HIDE_CATEGORIES_NAVIGATION', + 'value' => 0, + 'type' => 'boolean', + 'range' => 'course', + 'section' => 'Forum', + 'description' => 'Bestimmt, ob die Kategorien-Navigation im Forum ausgeblendet wird.' + ] + ); + + \DBManager::get()->execute( + $insertConfigSql, + [ + 'field' => 'FORUM_TILE_LAYOUT', + 'value' => 1, + 'type' => 'boolean', + 'range' => 'user', + 'section' => 'Forum', + 'description' => 'Konfiguration der Ansicht des Forum.' + ] + ); + + \DBManager::get()->exec(" + RENAME TABLE `forum_entries_issues` TO `forum_topics_issues`; + "); + + $insertDiscussionTypeSql = "INSERT IGNORE INTO `forum_discussion_types` VALUES (MD5(:name), :name, :icon, UNIX_TIMESTAMP(), UNIX_TIMESTAMP())"; + + $discussionTypes = [ + [ + 'name' => 'Fragen', + 'icon' => 'question' + ], + [ + 'name' => 'Aufgaben', + 'icon' => 'guestbook' + ], + [ + 'name' => 'Ideen', + 'icon' => 'lightbulb' + ], + [ + 'name' => 'Regeln', + 'icon' => 'info-circle' + ], + [ + 'name' => 'Vorstellung', + 'icon' => 'vcard' + ], + [ + 'name' => 'Organisation', + 'icon' => 'network2' + ] + ]; + + foreach ($discussionTypes as $discussionType) { + \DBManager::get()->execute($insertDiscussionTypeSql, $discussionType); + } + + \DBManager::get()->exec(" + DROP TABLE IF EXISTS + `forum_likes`, + `forum_visits`, + `forum_abo_users`, + `forum_favorites`, + `forum_user_roles`, + `forum_categories_entries`, + `forum_entries` + "); + } + + public function down() + { + $removeConfigSql = "DELETE `config`, `config_values` FROM `config` + LEFT JOIN `config_values` USING (`field`) + WHERE `field` = :field"; + + \DBManager::get()->execute( + $removeConfigSql, + ['field' => 'FORUM_TILE_LAYOUT'] + ); + \DBManager::get()->execute( + $removeConfigSql, + ['field' => 'FORUM_HIDE_CATEGORIES_NAVIGATION'] + ); + \DBManager::get()->execute( + $removeConfigSql, + ['field' => 'FORUM_MODERATION_PERMISSION'] + ); + \DBManager::get()->execute( + $removeConfigSql, + ['field' => 'FORUM_TILE_LAYOUT'] + ); + + \DBManager::get()->exec(" + CREATE TABLE IF NOT EXISTS `forum_entries` ( + `topic_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `seminar_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `user_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `area` tinyint NOT NULL DEFAULT '0', + `mkdate` int unsigned NOT NULL, + `latest_chdate` int unsigned DEFAULT NULL, + `chdate` int unsigned NOT NULL, + `author` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `author_host` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `lft` int NOT NULL, + `rgt` int NOT NULL, + `depth` int NOT NULL, + `anonymous` tinyint NOT NULL DEFAULT '0', + `closed` tinyint unsigned NOT NULL DEFAULT '0', + `sticky` tinyint unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`topic_id`), + KEY `seminar_id` (`seminar_id`,`lft`), + KEY `seminar_id_2` (`seminar_id`,`rgt`), + KEY `user_id` (`user_id`) + ) + "); + + \DBManager::get()->exec(" + CREATE TABLE IF NOT EXISTS `forum_likes` ( + `topic_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `user_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + PRIMARY KEY (`topic_id`,`user_id`) + ) + "); + + \DBManager::get()->exec(" + CREATE TABLE IF NOT EXISTS `forum_visits` ( + `user_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `seminar_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `visitdate` int unsigned NOT NULL, + `last_visitdate` int unsigned NOT NULL, + PRIMARY KEY (`user_id`,`seminar_id`) + ) + "); + + \DBManager::get()->exec(" + CREATE TABLE IF NOT EXISTS `forum_abo_users` ( + `topic_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `user_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + PRIMARY KEY (`topic_id`,`user_id`) + ) + "); + + \DBManager::get()->exec(" + CREATE TABLE IF NOT EXISTS `forum_favorites` ( + `user_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `topic_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + PRIMARY KEY (`user_id`,`topic_id`) + ) + "); + + \DBManager::get()->exec(" + DROP TABLE IF EXISTS `forum_categories` + "); + + \DBManager::get()->exec(" + CREATE TABLE IF NOT EXISTS `forum_categories` ( + `category_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `seminar_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `entry_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `pos` int NOT NULL DEFAULT '0', + PRIMARY KEY (`category_id`), + KEY `seminar_id` (`seminar_id`) + ) + "); + + \DBManager::get()->exec(" + CREATE TABLE IF NOT EXISTS `forum_categories_entries` ( + `category_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `topic_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `pos` int NOT NULL DEFAULT '0', + PRIMARY KEY (`category_id`,`topic_id`) + ) + "); + + \DBManager::get()->exec(" + RENAME TABLE `forum_topics_issues` TO `forum_entries_issues`; + "); + + \DBManager::get()->exec(" + DROP TABLE IF EXISTS + `forum_subscriptions`, + `forum_posting_reads`, + `forum_posting_reactions`, + `forum_discussion_types`, + `forum_postings`, + `forum_discussions`, + `forum_topics` + "); + } +} |