From 56b961ac3b997215c453487629cfff336cc49da9 Mon Sep 17 00:00:00 2001 From: Elmar Ludwig Date: Thu, 7 Jul 2022 10:51:27 +0000 Subject: fix linear order of migrations for 4.4 and 4.5, fixes #1256 Closes #1256 Merge request studip/studip!773 --- db/migrations/1.262_blubbermessenger.php | 171 -- .../1.262_consultations_option_exclude_expired.php | 31 + db/migrations/1.263_blubbermessenger_keys.php | 22 - .../1.263_fix_missing_consultation_events.php | 96 + db/migrations/1.264_blubbermessenger_search.php | 101 - .../1.264_step_00332_mvv_overlapping_courses.php | 86 + db/migrations/1.265_blubbermessenger_flat.php | 212 -- db/migrations/1.265_jsonapi_cors_origin_config.php | 35 + .../1.266_consultations_option_exclude_expired.php | 31 - .../1.266_step_00338_instituteplaning.php | 79 + .../1.267_config_for_download_counter_display.php | 31 + .../1.267_jsonapi_dangerous_routes_config.php | 36 - ...268_add_enable_free_access_for_courses_only.php | 34 + db/migrations/1.268_unrestricted_userdomains.php | 21 - db/migrations/1.269_additional_mvv_tables.php | 295 +++ .../1.269_fix_missing_consultation_events.php | 96 - .../1.270_step_00332_mvv_overlapping_courses.php | 86 - .../1.270_tic8458_add_upload_description.php | 36 + db/migrations/1.271_jsonapi_cors_origin_config.php | 35 - db/migrations/1.271_room_management_migration.php | 2390 ++++++++++++++++++++ .../1.272_resize_auth_user_md5_email_field.php | 25 + .../1.272_step_00338_instituteplaning.php | 79 - .../1.273_config_for_download_counter_display.php | 31 - db/migrations/1.273_unrestricted_userdomains.php | 21 + ...274_add_enable_free_access_for_courses_only.php | 34 - db/migrations/1.274_blubbermessenger.php | 171 ++ db/migrations/1.275_additional_mvv_tables.php | 295 --- db/migrations/1.275_blubbermessenger_keys.php | 22 + db/migrations/1.276_blubbermessenger_search.php | 101 + .../1.276_tic8458_add_upload_description.php | 36 - db/migrations/1.277_blubbermessenger_flat.php | 212 ++ db/migrations/1.277_room_management_migration.php | 2390 -------------------- .../1.278_jsonapi_dangerous_routes_config.php | 36 + .../1.278_resize_auth_user_md5_email_field.php | 25 - db/migrations/1.283_add_filetypes.php | 48 - ...3_change_schedule_color_with_category_index.php | 61 + ...4_change_schedule_color_with_category_index.php | 61 - db/migrations/1.284_fixes_on_schedule_coloring.php | 79 + db/migrations/1.285_add_filetypes.php | 48 + db/migrations/1.285_fixes_on_schedule_coloring.php | 79 - lib/migrations/DBSchemaVersion.php | 4 +- 41 files changed, 3891 insertions(+), 3891 deletions(-) delete mode 100644 db/migrations/1.262_blubbermessenger.php create mode 100644 db/migrations/1.262_consultations_option_exclude_expired.php delete mode 100644 db/migrations/1.263_blubbermessenger_keys.php create mode 100644 db/migrations/1.263_fix_missing_consultation_events.php delete mode 100644 db/migrations/1.264_blubbermessenger_search.php create mode 100644 db/migrations/1.264_step_00332_mvv_overlapping_courses.php delete mode 100644 db/migrations/1.265_blubbermessenger_flat.php create mode 100644 db/migrations/1.265_jsonapi_cors_origin_config.php delete mode 100644 db/migrations/1.266_consultations_option_exclude_expired.php create mode 100644 db/migrations/1.266_step_00338_instituteplaning.php create mode 100644 db/migrations/1.267_config_for_download_counter_display.php delete mode 100644 db/migrations/1.267_jsonapi_dangerous_routes_config.php create mode 100644 db/migrations/1.268_add_enable_free_access_for_courses_only.php delete mode 100644 db/migrations/1.268_unrestricted_userdomains.php create mode 100644 db/migrations/1.269_additional_mvv_tables.php delete mode 100644 db/migrations/1.269_fix_missing_consultation_events.php delete mode 100644 db/migrations/1.270_step_00332_mvv_overlapping_courses.php create mode 100644 db/migrations/1.270_tic8458_add_upload_description.php delete mode 100644 db/migrations/1.271_jsonapi_cors_origin_config.php create mode 100644 db/migrations/1.271_room_management_migration.php create mode 100644 db/migrations/1.272_resize_auth_user_md5_email_field.php delete mode 100644 db/migrations/1.272_step_00338_instituteplaning.php delete mode 100644 db/migrations/1.273_config_for_download_counter_display.php create mode 100644 db/migrations/1.273_unrestricted_userdomains.php delete mode 100644 db/migrations/1.274_add_enable_free_access_for_courses_only.php create mode 100644 db/migrations/1.274_blubbermessenger.php delete mode 100644 db/migrations/1.275_additional_mvv_tables.php create mode 100644 db/migrations/1.275_blubbermessenger_keys.php create mode 100644 db/migrations/1.276_blubbermessenger_search.php delete mode 100644 db/migrations/1.276_tic8458_add_upload_description.php create mode 100644 db/migrations/1.277_blubbermessenger_flat.php delete mode 100644 db/migrations/1.277_room_management_migration.php create mode 100644 db/migrations/1.278_jsonapi_dangerous_routes_config.php delete mode 100644 db/migrations/1.278_resize_auth_user_md5_email_field.php delete mode 100644 db/migrations/1.283_add_filetypes.php create mode 100644 db/migrations/1.283_change_schedule_color_with_category_index.php delete mode 100644 db/migrations/1.284_change_schedule_color_with_category_index.php create mode 100644 db/migrations/1.284_fixes_on_schedule_coloring.php create mode 100644 db/migrations/1.285_add_filetypes.php delete mode 100644 db/migrations/1.285_fixes_on_schedule_coloring.php diff --git a/db/migrations/1.262_blubbermessenger.php b/db/migrations/1.262_blubbermessenger.php deleted file mode 100644 index 24acb5c..0000000 --- a/db/migrations/1.262_blubbermessenger.php +++ /dev/null @@ -1,171 +0,0 @@ -exec($query); - - $query = "INSERT INTO `blubber_threads` ( - `thread_id`, `context_type`, `context_id`, `user_id`, `external_contact`, `content`, `display_class`, `visible_in_stream`, `chdate`, `mkdate` - ) - SELECT `topic_id`, `context_type`, `Seminar_id`, `user_id`, `external_contact`, `description`, NULL, '1', `chdate`, `mkdate` - FROM blubber - WHERE parent_id = '0'"; - DBManager::get()->exec($query); - - $query = "CREATE TABLE `blubber_comments` ( - `comment_id` CHAR(32) COLLATE latin1_bin NOT NULL, - `thread_id` CHAR(32) COLLATE latin1_bin NOT NULL, - `user_id` CHAR(32) COLLATE latin1_bin NOT NULL DEFAULT '', - `external_contact` TINYINT(1) NOT NULL DEFAULT 0, - `content` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `network` VARCHAR(64) COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, - `chdate` INT(11) DEFAULT NULL, - `mkdate` INT(11) DEFAULT NULL, - PRIMARY KEY (`comment_id`), - KEY `thread_id` (`thread_id`), - KEY `user_id` (`user_id`) - ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC"; - DBManager::get()->exec($query); - - $query = "INSERT INTO `blubber_comments` ( - `comment_id`, `thread_id`, `user_id`, `external_contact`, `content`, `chdate`, `mkdate` - ) - SELECT `topic_id`, `root_id`, `user_id`, `external_contact`, `description`, `chdate`, `mkdate` - FROM blubber - WHERE parent_id != '0'"; - DBManager::get()->exec($query); - - DBManager::get()->exec("DROP TABLE `blubber`"); - - $query = "ALTER TABLE blubber_mentions - CHANGE `topic_id` `thread_id` CHAR(32) COLLATE latin1_bin NOT NULL"; - DBManager::get()->exec($query); - - DBManager::get()->exec("DROP TABLE blubber_reshares"); - DBManager::get()->exec("DROP TABLE blubber_streams"); - - // Create config entries - $query = "INSERT INTO `config` - SET `field` = :field, - `value` = :value, - `type` = :type, - `range` = :range, - `section` = :section, - `mkdate` = UNIX_TIMESTAMP(), - `chdate` = UNIX_TIMESTAMP(), - `description` = :description"; - $config_statement = DBManager::get()->prepare($query); - - $config_statement->execute([ - ':field' => 'BLUBBER_GLOBAL_MESSENGER_ACTIVATE', - ':value' => '1', - ':type' => 'boolean', - ':range' => 'global', - ':section' => 'global', - ':description' => 'Ist Blubber unter Community global aktiv? Blubber in Veranstaltungen wird über das Plugin Blubber aktiviert oder deaktiviert.', - ]); - $config_statement->execute([ - ':field' => 'BLUBBER_DEFAULT_THREAD', - ':value' => '1', - ':type' => 'string', - ':range' => 'user', - ':section' => '', - ':description' => 'Dieses ist bei dem globalen Blubber-Messenger der vorausgewählte Blubber.', - ]); - - // activate routes: - require_once $GLOBALS['STUDIP_BASE_PATH'] . '/app/routes/Blubber.php'; - RESTAPI\ConsumerPermissions::get()->activateRouteMap(new RESTAPI\Routes\Blubber()); - - // Blubber to be the primary messenger in courses - $query = "SELECT pluginid - FROM plugins - WHERE pluginclassname = 'CoreForum'"; - $forum_id = DBManager::get()->fetchColumn($query); - - $query = "SELECT id, modules - FROM sem_classes - WHERE forum = 'CoreForum'"; - $select_sem_class = DBManager::get()->query($query); - $sem_classes = $select_sem_class->fetchAll(PDO::FETCH_ASSOC); - - $query = "INSERT IGNORE INTO plugins_activated (pluginid, range_type, range_id, state) - SELECT :forum_id, 'sem', seminare.Seminar_id, '1' - FROM seminare - JOIN sem_types ON seminare.status = sem_types.id - JOIN sem_classes ON sem_types.class = sem_classes.id - JOIN forum_entries ON forum_entries.seminar_id = seminare.Seminar_id - WHERE sem_classes.id = :sem_class - GROUP BY seminare.Seminar_id - HAVING COUNT(*) > 1"; - $activate_forum_for_courses = DBManager::get()->prepare($query); - - $query = "UPDATE sem_classes - SET modules = :modules, - forum = 'Blubber' - WHERE id = :id"; - $update = DBManager::get()->prepare($query); - - foreach ($sem_classes as $sem_class) { - $modules = json_decode($sem_class['modules'], true); - $forum_was_activated = $modules['CoreForum']['activated']; - - $modules['CoreForum']['activated'] = 0; - $modules['Blubber']['activated'] = 1; - - $update->execute([ - 'id' => $sem_class['id'], - 'modules' => json_encode($modules), - ]); - if ($forum_was_activated) { - // activate old forum in old courses that have more than one posting: - $activate_forum_for_courses->execute([ - 'forum_id' => $forum_id, - 'sem_class' => $sem_class['id'], - ]); - } - } - - // delete old blubber-stream avatars - $blubberstreams_folder = "{$GLOBALS['DYNAMIC_CONTENT_PATH']}/blubberstream"; - foreach (glob("{$blubberstreams_folder}/*") as $file) { - @unlink($blubberstreams_folder . "/" . $file); - } - @rmdir($blubberstreams_folder); - - DBManager::get()->exec(" - DELETE FROM activities - WHERE object_type = 'blubber' - "); - } - - public function down() - { - DBManager::get()->exec("DROP TABLE `blubber_comments`, `blubber_threads`"); - } -} diff --git a/db/migrations/1.262_consultations_option_exclude_expired.php b/db/migrations/1.262_consultations_option_exclude_expired.php new file mode 100644 index 0000000..35bd57e --- /dev/null +++ b/db/migrations/1.262_consultations_option_exclude_expired.php @@ -0,0 +1,31 @@ +exec($query); + } + + public function down() + { + $query = "DELETE `config`, `config_values` + FROM `config` + LEFT JOIN `config_values` USING (`field`) + WHERE `field` = 'CONSULTATION_EXCLUDE_EXPIRED'"; + DBManager::get()->exec($query); + } +} diff --git a/db/migrations/1.263_blubbermessenger_keys.php b/db/migrations/1.263_blubbermessenger_keys.php deleted file mode 100644 index c79e979..0000000 --- a/db/migrations/1.263_blubbermessenger_keys.php +++ /dev/null @@ -1,22 +0,0 @@ -exec($query); - } - - public function down() - { - $query = "ALTER TABLE `blubber_mentions` - DROP COLUMN `mention_id`"; - DBManager::get()->exec($query); - } -} diff --git a/db/migrations/1.263_fix_missing_consultation_events.php b/db/migrations/1.263_fix_missing_consultation_events.php new file mode 100644 index 0000000..a0548e0 --- /dev/null +++ b/db/migrations/1.263_fix_missing_consultation_events.php @@ -0,0 +1,96 @@ +fetchFirst($query); + + LegacyConsultationSlot::findAndMapMany( + function ($slot) { + // This is wrapped in a try/catch block since we can only assure + // that the LegacyConsultationSlot is used for updating the event itself. + // In the subsequent procedure, the related bookings are stored as well + // which will trigger another update of the event - this time on the + // ConsultationSlot object itself, not on the legacy one. Since this + // has code changes for Stud.IP 5.0 this will fail but we can neglect + // that since the event is already updated. + try { + $slot->updateEvents(); + } catch (Exception $e) { + } + }, + $ids + ); + } +} + +class LegacyConsultationSlot extends ConsultationSlot +{ + /** + * Updates the teacher event that belongs to the slot. This will either be + * set to be unoccupied, occupied by only one user or by a group of user. + */ + public function updateEvents() + { + if (count($this->bookings) === 0 && !$this->block->calendar_events) { + $this->events->delete(); + return; + } + + $teacher = User::find($this->block->teacher_id); + if (!$teacher) { + return; + } + + $event = $this->event; + if (!$event) { + $event = $this->createEvent($teacher); + + $this->teacher_event_id = $event->id; + $this->store(); + } + + setTempLanguage($teacher->id); + + if (count($this->bookings) > 0) { + $event->category_intern = 1; + + if (count($this->bookings) === 1) { + $booking = $this->bookings->first(); + + $event->summary = sprintf( + _('Sprechstundentermin mit %s'), + $booking->user->getFullName() + ); + $event->description = $booking->reason; + } else { + $event->summary = sprintf( + _('Sprechstundentermin mit %u Personen'), + count($this->bookings) + ); + $event->description = implode("\n\n----\n\n", $this->bookings->map(function ($booking) { + return "- {$booking->user->getFullName()}:\n{$booking->reason}"; + })); + } + } else { + $event->category_intern = 9; + $event->summary = _('Freier Sprechstundentermin'); + $event->description = _('Dieser Sprechstundentermin ist noch nicht belegt.'); + } + + restoreLanguage(); + + $event->store(); + } +} diff --git a/db/migrations/1.264_blubbermessenger_search.php b/db/migrations/1.264_blubbermessenger_search.php deleted file mode 100644 index 81c78ff..0000000 --- a/db/migrations/1.264_blubbermessenger_search.php +++ /dev/null @@ -1,101 +0,0 @@ -prepare(" - SELECT * - FROM config - WHERE field = 'GLOBALSEARCH_MODULES' - "); - $statement->execute(); - $config = $statement->fetch(PDO::FETCH_ASSOC); - $config['value'] = json_decode($config['value'], true); - $config['value']['GlobalSearchBlubber'] = [ - 'order' => 13, - 'active' => true, - 'fulltext' => true - ]; - - $statement = DBManager::get()->prepare(" - UPDATE config - SET `value` = :json - WHERE field = 'GLOBALSEARCH_MODULES' - "); - $statement->execute([ - 'json' => json_encode($config['value']) - ]); - - $statement = DBManager::get()->prepare(" - SELECT * - FROM config_values - WHERE field = 'GLOBALSEARCH_MODULES' - "); - $statement->execute(); - $config = $statement->fetch(PDO::FETCH_ASSOC); - if ($config) { - $config['value'] = json_decode($config['value'], true); - $config['value']['GlobalSearchBlubber'] = [ - 'order' => 13, - 'active' => true, - 'fulltext' => true - ]; - - $statement = DBManager::get()->prepare(" - UPDATE config_values - SET `value` = :json - WHERE field = 'GLOBALSEARCH_MODULES' - "); - $statement->execute([ - 'json' => json_encode($config['value']) - ]); - } - } - - public function down() - { - $statement = DBManager::get()->prepare(" - SELECT * - FROM config_values - WHERE field = 'GLOBALSEARCH_MODULES' - "); - $statement->execute(); - $config = $statement->fetch(PDO::FETCH_ASSOC); - if ($config) { - $config['value'] = json_decode($config['value'], true); - unset($config['value']['GlobalSearchBlubber']); - $statement = DBManager::get()->prepare(" - UPDATE config_values - SET `value` = :json - WHERE field = 'GLOBALSEARCH_MODULES' - "); - $statement->execute([ - 'json' => json_encode($config['value']) - ]); - } - - $statement = DBManager::get()->prepare(" - SELECT * - FROM config - WHERE field = 'GLOBALSEARCH_MODULES' - "); - $statement->execute(); - $config = $statement->fetch(PDO::FETCH_ASSOC); - $config['value'] = json_decode($config['value'], true); - unset($config['value']['GlobalSearchBlubber']); - $statement = DBManager::get()->prepare(" - UPDATE config - SET `value` = :json - WHERE field = 'GLOBALSEARCH_MODULES' - "); - $statement->execute([ - 'json' => json_encode($config['value']) - ]); - - } -} diff --git a/db/migrations/1.264_step_00332_mvv_overlapping_courses.php b/db/migrations/1.264_step_00332_mvv_overlapping_courses.php new file mode 100644 index 0000000..67cfa79 --- /dev/null +++ b/db/migrations/1.264_step_00332_mvv_overlapping_courses.php @@ -0,0 +1,86 @@ +exec($query); + + $query = "CREATE TABLE IF NOT EXISTS `mvv_ovl_conflicts` ( + `conflict_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, + `selection_id` int(11) NOT NULL, + `base_abschnitt_id` varchar(32) COLLATE latin1_bin NOT NULL, + `base_modulteil_id` varchar(32) COLLATE latin1_bin NOT NULL, + `base_course_id` varchar(32) COLLATE latin1_bin NOT NULL, + `base_metadate_id` varchar(32) COLLATE latin1_bin NOT NULL, + `comp_abschnitt_id` varchar(32) COLLATE latin1_bin NOT NULL, + `comp_modulteil_id` varchar(32) COLLATE latin1_bin NOT NULL, + `comp_course_id` varchar(32) COLLATE latin1_bin NOT NULL, + `comp_metadate_id` varchar(32) COLLATE latin1_bin NOT NULL, + PRIMARY KEY (`conflict_id`), + KEY `selection_id` (`selection_id`) + ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC"; + + DBManager::get()->exec($query); + + $query = "CREATE TABLE IF NOT EXISTS `mvv_ovl_excludes` ( + `selection_id` varchar(32) COLLATE latin1_bin NOT NULL, + `course_id` varchar(32) COLLATE latin1_bin NOT NULL, + PRIMARY KEY (`selection_id`,`course_id`), + KEY `course_id` (`course_id`) USING BTREE + ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC"; + + DBManager::get()->exec($query); + + // Add config (2nd select shows only versions inside a multiple course of study) + $query = "INSERT IGNORE INTO `config` ( + `field`, `value`, `type`, `range`, + `section`, `description`, + `mkdate`, `chdate` + ) VALUES ( + 'MVV_OVERLAPPING_SHOW_VERSIONS_INSIDE_MULTIPLE_STUDY_COURSES', + '0', 'boolean', 'global', + 'global', 'Zeigt als zweite Auswahl bei Mehrfachstudiengängen nur Versionen der dazugehörigen Teilstudiengänge an.', + UNIX_TIMESTAMP(), UNIX_TIMESTAMP() + )"; + + DBManager::get()->exec($query); + + SimpleORMap::expireTableScheme(); + } + + public function down() + { + $query = "DROP TABLE `mvv_ovl_selections`"; + DBManager::get()->exec($query); + $query = "DROP TABLE `mvv_ovl_conflicts`"; + DBManager::get()->exec($query); + $query = "DROP TABLE `mvv_ovl_excludes`"; + DBManager::get()->exec($query); + $query = "DELETE FROM `config` + WHERE `field` = 'MVV_OVERLAPPING_SHOW_VERSIONS_INSIDE_MULTIPLE_STUDY_COURSES'"; + DBManager::get()->exec($query); + + SimpleORMap::expireTableScheme(); + } +} diff --git a/db/migrations/1.265_blubbermessenger_flat.php b/db/migrations/1.265_blubbermessenger_flat.php deleted file mode 100644 index 3b4dfeb..0000000 --- a/db/migrations/1.265_blubbermessenger_flat.php +++ /dev/null @@ -1,212 +0,0 @@ -exec(" - DELETE FROM blubber_comments - WHERE mkdate < 1364601600 - "); //30th March 2013 - - DBManager::get()->exec(" - INSERT IGNORE INTO blubber_threads - SET thread_id = 'global', - context_type = 'public', - context_id = '', - user_id = '', - external_contact = '0', - `content` = NULL, - display_class = 'BlubberGlobalThread', - visible_in_stream = '1', - commentable = '1', - metadata = NULL, - chdate = UNIX_TIMESTAMP(), - mkdate = UNIX_TIMESTAMP() - "); - DBManager::get()->exec(" - UPDATE blubber_comments, blubber_threads - SET blubber_comments.thread_id = 'global' - WHERE blubber_comments.thread_id = blubber_threads.thread_id - AND blubber_threads.context_type = 'public' - "); - DBManager::get()->exec(" - INSERT IGNORE INTO blubber_comments (comment_id, thread_id, user_id, external_contact, `content`, network, chdate, mkdate) - SELECT thread_id, 'global', user_id, external_contact, `content`, null, chdate, mkdate - FROM blubber_threads - WHERE context_type = 'public' - AND thread_id != 'global' - "); - DBManager::get()->exec(" - DELETE FROM blubber_threads - WHERE context_type = 'public' - AND thread_id != 'global' - "); - - $select_threads = DBManager::get()->prepare(" - SELECT * - FROM blubber_threads - WHERE context_type = 'course' - AND `content` IS NOT NULL AND `content` != '' - AND display_class IS NULL - "); - $select_threads->execute(); - $insert_comments = DBManager::get()->prepare(" - UPDATE blubber_comments - SET thread_id = :thread_id - WHERE thread_id = :old_thread - "); - $insert_comment = DBManager::get()->prepare(" - INSERT INTO blubber_comments - SET thread_id = :thread_id, - comment_id = :comment_id, - user_id = :user_id, - external_contact = :external_contact, - `content` = :content, - network = NULL, - chdate = :chdate, - mkdate = :mkdate - "); - $delete_thread = DBManager::get()->prepare(" - DELETE FROM blubber_threads - WHERE thread_id = ? - "); - $select_course_main_thread = DBManager::get()->prepare(" - SELECT * - FROM blubber_threads - WHERE (content IS NULL OR content = '') - AND thread_id != :main_thread_id - AND context_id = :course_id - AND context_type = 'course' - AND display_class IS NULL - "); - while ($row = $select_threads->fetch(PDO::FETCH_ASSOC)) { - $course_thread_id = $this->getCourseThreadId($row['context_id']); - - //Alle anderen mit !content löschen - $select_course_main_thread->execute([ - 'main_thread_id' => $course_thread_id, - 'course_id' => $row['context_id'] - ]); - foreach ($select_course_main_thread->fetchAll(PDO::FETCH_ASSOC) as $row2) { - $insert_comments->execute([ - 'thread_id' => $course_thread_id, - 'old_thread' => $row2['thread_id'] - ]); - $delete_thread->execute([ - $row2['thread_id'] - ]); - } - - if ($row['thread_id'] !== $course_thread_id) { - //Alle Kommentare aus diesem Thread in den Main-Thread verschieben: - $insert_comments->execute([ - 'thread_id' => $course_thread_id, - 'old_thread' => $row['thread_id'] - ]); - - if ($row['content']) { - //Und noch einen Startkommentar in den Haupthread packen, wenn der zu löschende Thread noch einen Hauptinhalt hatte: - $insert_comment->execute([ - 'comment_id' => $row['thread_id'], - 'thread_id' => $course_thread_id, - 'user_id' => $row['user_id'], - 'external_contact' => $row['external_contact'], - 'content' => $row['content'], - 'chdate' => $row['chdate'], - 'mkdate' => $row['mkdate'] - ]); - } - - $delete_thread->execute([ - $row['thread_id'] - ]); - } - } - - $select_private_threads = DBManager::get()->prepare(" - SELECT * - FROM blubber_threads - WHERE context_type = 'private' - AND `content` IS NOT NULL AND content != '' - "); - $select_private_threads->execute(); - $clean_thread = DBManager::get()->prepare(" - UPDATE blubber_threads - SET `content` = NULL - WHERE thread_id = ? - "); - while ($row3 = $select_private_threads->fetch(PDO::FETCH_ASSOC)) { - $insert_comment->execute([ - 'comment_id' => md5($row3['thread_id']."_ersterkommentar"), - 'thread_id' => $row3['thread_id'], - 'user_id' => $row3['user_id'], - 'external_contact' => $row3['external_contact'], - 'content' => $row3['content'], - 'chdate' => $row3['chdate'], - 'mkdate' => $row3['mkdate'] - ]); - $clean_thread->execute([$row3['thread_id']]); - } - - DBManager::get()->exec(" - CREATE TABLE IF NOT EXISTS `blubber_threads_unfollow` ( - `thread_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - `user_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `mkdate` int(11) DEFAULT NULL, - PRIMARY KEY (`thread_id`,`user_id`) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - "); - - // activate routes: - require_once $GLOBALS['STUDIP_BASE_PATH'] . '/app/routes/Blubber.php'; - RESTAPI\ConsumerPermissions::get()->activateRouteMap(new RESTAPI\Routes\Blubber()); - } - - protected function getCourseThreadId($course_id) - { - $select = DBManager::get()->prepare(" - SELECT * - FROM blubber_threads - WHERE context_type = 'course' - AND context_id = ? - AND display_class IS NULL - AND (`content` IS NULL OR `content` = '') - "); - $select->execute([$course_id]); - $thread_id = $select->fetch(PDO::FETCH_COLUMN, 0); - if (!$thread_id) { - $thread_id = md5(uniqid($course_id)); - $insert = DBManager::get()->prepare(" - INSERT IGNORE INTO blubber_threads - SET thread_id = :thread_id, - context_type = 'course', - context_id = :course_id, - user_id = '', - external_contact = '0', - `content` = NULL, - display_class = NULL, - visible_in_stream = '1', - commentable = '1', - chdate = UNIX_TIMESTAMP(), - mkdate = UNIX_TIMESTAMP() - "); - $insert->execute([ - 'thread_id' => $thread_id, - 'course_id' => $course_id - ]); - } - return $thread_id; - } - - public function down() - { - DBManager::exec(" - DROP TABLE `blubber_threads_unfollow`; - "); - } -} diff --git a/db/migrations/1.265_jsonapi_cors_origin_config.php b/db/migrations/1.265_jsonapi_cors_origin_config.php new file mode 100644 index 0000000..58a8fe7 --- /dev/null +++ b/db/migrations/1.265_jsonapi_cors_origin_config.php @@ -0,0 +1,35 @@ +exec($query); + } + + public function down() + { + $query = "DELETE `config`, `config_values` + FROM `config` + LEFT JOIN `config_values` USING (`field`) + WHERE `field` = 'JSONAPI_CORS_ORIGIN'"; + DBManager::get()->exec($query); + } +} diff --git a/db/migrations/1.266_consultations_option_exclude_expired.php b/db/migrations/1.266_consultations_option_exclude_expired.php deleted file mode 100644 index 35bd57e..0000000 --- a/db/migrations/1.266_consultations_option_exclude_expired.php +++ /dev/null @@ -1,31 +0,0 @@ -exec($query); - } - - public function down() - { - $query = "DELETE `config`, `config_values` - FROM `config` - LEFT JOIN `config_values` USING (`field`) - WHERE `field` = 'CONSULTATION_EXCLUDE_EXPIRED'"; - DBManager::get()->exec($query); - } -} diff --git a/db/migrations/1.266_step_00338_instituteplaning.php b/db/migrations/1.266_step_00338_instituteplaning.php new file mode 100644 index 0000000..b19709a --- /dev/null +++ b/db/migrations/1.266_step_00338_instituteplaning.php @@ -0,0 +1,79 @@ +exec($query); + + $query = "INSERT INTO `datafields` ( + `datafield_id`, `name`, `object_type`, `edit_perms`, `view_perms`, + `priority`, `type`, `typeparam`, `is_required`, `is_userfilter`, + `description`, `system` + ) VALUES ( + '69f6485f3c937766866a03d9d642ecbb', 'zugeordnete Planungsspalte', 'sem', 'admin', 'root', + 0, 'textline', '', 0, 0, + 'Gibt die zugeordnete Planungsspalte im Veranstaltungsplan an.', 0 + ), ( + '41cda2be71fe9efd6e28b853fc0681f3', 'zugeordnete Planungsfarbe', 'sem', 'admin', 'root', + 0, 'textline', '', 0, 0, + 'Zugewiesene Farbe im Veranstaltungsplaner', 0 + )"; + DBManager::get()->exec($query); + + $query = "INSERT IGNORE INTO config ( + `field`, `value`, `type`, `range`, `section`, + `mkdate`, `chdate`, `description` + ) VALUES ( + :field, :value, 'string', 'global', 'modules', + UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), :description + )"; + $statement = DBManager::get()->prepare($query); + + $statement->execute([ + ':field' => 'INSTITUTE_COURSE_PLAN_START_HOUR', + ':value' => '08:00', + 'description' => 'The start hour for the default view of the institute course plan.', + ]); + + $statement->execute([ + ':field' => 'INSTITUTE_COURSE_PLAN_END_HOUR', + ':value' => '20:00', + 'description' => 'The end hour for the default view of the institute course plan.', + ]); + } + + public function down() + { + DBManager::get()->exec('DROP TABLE `institute_plan_columns`'); + + $query = "DELETE FROM `datafields` + WHERE `datafield_id` IN ( + '69f6485f3c937766866a03d9d642ecbb', + '41cda2be71fe9efd6e28b853fc0681f3' + )"; + DBManager::get()->exec($query); + + $query = "DELETE `config`, `config_values` + FROM `config` + LEFT JOIN `config_values` USING (`field`) + WHERE `field` IN ( + 'INSTITUTE_COURSE_PLAN_START_HOUR', + 'INSTITUTE_COURSE_PLAN_END_HOUR' + )"; + DBManager::get()->exec($query); + } +} diff --git a/db/migrations/1.267_config_for_download_counter_display.php b/db/migrations/1.267_config_for_download_counter_display.php new file mode 100644 index 0000000..f4df9e5 --- /dev/null +++ b/db/migrations/1.267_config_for_download_counter_display.php @@ -0,0 +1,31 @@ +exec($query); + } + + public function down() + { + $query = "DELETE `config`, `config_values` + FROM `config` + LEFT JOIN `config_values` USING (`field`) + WHERE `field` = 'DISPLAY_DOWNLOAD_COUNTER'"; + DBManager::get()->exec($query); + } +} diff --git a/db/migrations/1.267_jsonapi_dangerous_routes_config.php b/db/migrations/1.267_jsonapi_dangerous_routes_config.php deleted file mode 100644 index 87c23f9..0000000 --- a/db/migrations/1.267_jsonapi_dangerous_routes_config.php +++ /dev/null @@ -1,36 +0,0 @@ -exec($query); - } - - public function down() - { - $query = "DELETE `config`, `config_values` - FROM `config` - LEFT JOIN `config_values` USING (`field`) - WHERE `field` = 'JSONAPI_DANGEROUS_ROUTES_ALLOWED'"; - DBManager::get()->exec($query); - } -} diff --git a/db/migrations/1.268_add_enable_free_access_for_courses_only.php b/db/migrations/1.268_add_enable_free_access_for_courses_only.php new file mode 100644 index 0000000..63fe853 --- /dev/null +++ b/db/migrations/1.268_add_enable_free_access_for_courses_only.php @@ -0,0 +1,34 @@ +exec( + "UPDATE `config` SET `type` = 'string', + `description` = '1: courses and institutes with public access are visible without login. courses_only: only courses with public access are visible without login. 0: disable this feature.' + WHERE `field` = 'ENABLE_FREE_ACCESS'" + ); + } + + + public function down() + { + $db = DBManager::get(); + + $db->exec( + "UPDATE `config` SET `type` = 'boolean', + `description` = 'If true, courses with public access are available' + WHERE `field` = 'ENABLE_FREE_ACCESS'" + ); + } + + + public function description() + { + return 'Adds the "courses_only" option for ENABLE_FREE_ACCESS in the configuration.'; + } +} diff --git a/db/migrations/1.268_unrestricted_userdomains.php b/db/migrations/1.268_unrestricted_userdomains.php deleted file mode 100644 index eb95503..0000000 --- a/db/migrations/1.268_unrestricted_userdomains.php +++ /dev/null @@ -1,21 +0,0 @@ -exec($query); - } - - public function down() - { - $query = "ALTER TABLE `userdomains` - DROP COLUMN `restricted_access`, - DROP COLUMN `mkdate`, - DROP COLUMN `chdate`"; - DBManager::get()->exec($query); - } -} diff --git a/db/migrations/1.269_additional_mvv_tables.php b/db/migrations/1.269_additional_mvv_tables.php new file mode 100644 index 0000000..4175a58 --- /dev/null +++ b/db/migrations/1.269_additional_mvv_tables.php @@ -0,0 +1,295 @@ +exec("CREATE TABLE IF NOT EXISTS `mvv_files` ( + `mvvfile_id` varchar(32) COLLATE latin1_bin NOT NULL, + `year` int(10) DEFAULT NULL, + `type` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `category` text COLLATE utf8mb4_unicode_ci, + `tags` text COLLATE utf8mb4_unicode_ci, + `extern_visible` tinyint(1) DEFAULT NULL, + `author_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, + `editor_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, + `mkdate` int(11) NOT NULL, + `chdate` int(11) NOT NULL, + PRIMARY KEY (`mvvfile_id`) + ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); + + $db->exec("CREATE TABLE IF NOT EXISTS `mvv_files_filerefs` ( + `mvvfile_id` varchar(32) COLLATE latin1_bin NOT NULL, + `file_language` varchar(32) COLLATE latin1_bin NOT NULL, + `name` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL, + `fileref_id` varchar(32) COLLATE latin1_bin NOT NULL, + `author_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, + `editor_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, + `mkdate` int(11) NOT NULL, + `chdate` int(11) NOT NULL, + PRIMARY KEY (`mvvfile_id`,`file_language`) + ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); + + $db->exec("CREATE TABLE IF NOT EXISTS `mvv_files_ranges` ( + `mvvfile_id` VARCHAR(32) COLLATE latin1_bin NOT NULL , + `range_id` VARCHAR(32) COLLATE latin1_bin NOT NULL , + `range_type` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NULL DEFAULT NULL , + `position` INT(10) NULL DEFAULT NULL , + `author_id` VARCHAR(32) COLLATE latin1_bin NULL DEFAULT NULL , + `editor_id` VARCHAR(32) COLLATE latin1_bin NULL DEFAULT NULL , + `mkdate` INT(11) NOT NULL , + `chdate` INT(11) NOT NULL , + PRIMARY KEY (`mvvfile_id`, `range_id`)) + ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); + + $db->exec("CREATE TABLE IF NOT EXISTS `mvv_contacts` ( + `contact_id` varchar(32) COLLATE latin1_bin NOT NULL, + `contact_status` enum('intern','extern','institution') COLLATE latin1_bin NOT NULL, + `alt_mail` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + `author_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, + `editor_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, + `mkdate` int(11) NOT NULL, + `chdate` int(11) NOT NULL, + PRIMARY KEY (`contact_id`), + KEY `contact_status` (`contact_status`)) + ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); + + $db->exec("CREATE TABLE IF NOT EXISTS `mvv_extern_contacts` ( + `extern_contact_id` VARCHAR(32) COLLATE latin1_bin NOT NULL, + `name` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL, + `vorname` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `homepage` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL, + `mail` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL, + `tel` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL, + `author_id` VARCHAR(32) COLLATE latin1_bin DEFAULT NULL, + `editor_id` VARCHAR(32) COLLATE latin1_bin DEFAULT NULL, + `mkdate` INT(11) NOT NULL, + `chdate` INT(11) NOT NULL, + PRIMARY KEY (`extern_contact_id`)) + ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); + + $db->exec("CREATE TABLE IF NOT EXISTS `mvv_contacts_ranges` ( + `contact_range_id` varchar(32) COLLATE latin1_bin NOT NULL, + `contact_id` varchar(32) COLLATE latin1_bin NOT NULL, + `range_id` varchar(32) COLLATE latin1_bin NOT NULL, + `range_type` enum('Modul','Studiengang','StudiengangTeil') COLLATE latin1_bin NOT NULL, + `type` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, + `category` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, + `position` int(10) DEFAULT NULL, + `author_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, + `editor_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, + `mkdate` int(11) NOT NULL, + `chdate` int(11) NOT NULL, + PRIMARY KEY (`contact_range_id`), + KEY `range_id` (`range_id`), + KEY `range_type` (`range_type`), + KEY `type` (`type`), + KEY `category_range` (`category`,`range_id`), + KEY `contact_id` (`contact_id`,`range_id`,`category`) USING BTREE + ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); + + + //Merge old mvv_dokument + foreach ($db->query("SELECT * FROM `mvv_dokument`") as $old_doc) { + $fileref_id = md5('FileRef'. $old_doc['dokument_id']); + $folder_id = md5('Folder'. $old_doc['dokument_id']); + $file_id = md5('File'. $old_doc['dokument_id']); + $mvvfile_id = md5('MvvFile'. $old_doc['dokument_id']); + $db->execute("INSERT IGNORE INTO `mvv_files` (`mvvfile_id`, `year`, `type`, `category`, `tags`, `extern_visible`, `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, NULL, NULL, NULL, NULL , 1, ?, ?, ?, ?)", + [$mvvfile_id, $old_doc['author_id'], $old_doc['editor_id'], $old_doc['mkdate'], $old_doc['chdate']]); + $db->execute("INSERT IGNORE INTO `mvv_files_filerefs` (`mvvfile_id`, `file_language`, `name`, `fileref_id`, `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", + [$mvvfile_id, 'DE', $old_doc['linktext'], $fileref_id, $old_doc['author_id'], $old_doc['editor_id'], $old_doc['mkdate'], $old_doc['chdate']]); + $db->execute("INSERT IGNORE INTO `file_urls` (`file_id`, `url`, `access_type`) VALUES (?, ?, 'proxy')", [$file_id, $old_doc['url']]); + $db->execute("INSERT IGNORE INTO `folders` (`id`, `user_id`, `parent_id`, `range_id`, `range_type`, `folder_type`, `name`, `data_content`, `description`, `mkdate`, `chdate`) + VALUES (?, ?, NULL, ?, ?, 'MVVFolder', ?, NULL, ?, ?, ?)", + [$folder_id, $old_doc['author_id'], $mvvfile_id, 'mvv', $old_doc['name'], $old_doc['linktext'], $old_doc['mkdate'], $old_doc['chdate']]); + $db->execute("INSERT IGNORE INTO `file_refs` (`id`, `file_id`, `folder_id`, `description`, `user_id`, `name`, `mkdate`, `chdate`) + VALUES (?, ?, ?, ?, ?, ?, ?, ?)", + [$fileref_id, $file_id, $folder_id, $old_doc['beschreibung'], $old_doc['author_id'], $old_doc['name'], $old_doc['mkdate'], $old_doc['chdate']]); + } + + //Merge old mvv_dokument_zuord + foreach ($db->query("SELECT * FROM `mvv_dokument_zuord`") as $old_docrange) { + $mvvfile_id = md5('MvvFile'. $old_docrange['dokument_id']); + $db->execute("INSERT IGNORE INTO `mvv_files_ranges` (`mvvfile_id`, `range_id`, `range_type`, `position`, `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", + [$mvvfile_id, $old_docrange['range_id'], $old_docrange['object_type'], $old_docrange['position'], $old_docrange['author_id'], $old_docrange['editor_id'], $old_docrange['mkdate'], $old_docrange['chdate']]); + } + $db->exec('DROP TABLE `mvv_dokument`'); + $db->exec('DROP TABLE `mvv_dokument_zuord`'); + + //Merge old mvv_modul_user + foreach ($db->query("SELECT * FROM `mvv_modul_user`") as $old_modul_user) { + $contact_range_id = md5('MvvContactRange' . $old_modul_user['user_id'] . $old_modul_user['modul_id']); + $db->execute("INSERT IGNORE INTO `mvv_contacts` (`contact_id`, `contact_status`, `alt_mail`, + `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, ?, ?, ?, ?, ? ,?)", + [ + $old_modul_user['user_id'], 'intern', '', $old_modul_user['author_id'], + $old_modul_user['editor_id'], $old_modul_user['mkdate'], $old_modul_user['chdate'] + ]); + $db->execute("INSERT IGNORE INTO `mvv_contacts_ranges` (`contact_range_id`, `range_id`, `contact_id`, `range_type`, `type`, `category`, `position`, `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", + [$contact_range_id, $old_modul_user['modul_id'], $old_modul_user['user_id'], 'Modul', '', + $old_modul_user['gruppe'], $old_modul_user['position'], $old_modul_user['author_id'], + $old_modul_user['editor_id'], $old_modul_user['mkdate'], $old_modul_user['chdate']]); + } + $db->exec('DROP TABLE `mvv_modul_user`'); + + // Merge old Fachberater + foreach ($db->query("SELECT * FROM `mvv_fachberater`") as $old_fachberater) { + $contact_range_id = md5('MvvContactRange' . $old_fachberater['user_id'] . $old_fachberater['stgteil_id']); + $db->execute("INSERT IGNORE INTO `mvv_contacts` (`contact_id`, `contact_status`, `alt_mail`, + `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, ?, ?, ?, ?, ? ,?)", + [ + $old_fachberater['user_id'], 'intern', '', $old_fachberater['author_id'], + $old_fachberater['editor_id'], $old_fachberater['mkdate'], $old_fachberater['chdate'] + ]); + $db->execute("INSERT IGNORE INTO `mvv_contacts_ranges` (`contact_range_id`, `range_id`, `contact_id`, `range_type`, `type`, `category`, `position`, `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", + [$contact_range_id, $old_fachberater['stgteil_id'], $old_fachberater['user_id'], 'StudiengangTeil', '', + 'fachberater', $old_fachberater['position'], $old_fachberater['author_id'], + $old_fachberater['editor_id'], $old_fachberater['mkdate'], $old_fachberater['chdate']]); + } + $db->exec('DROP TABLE `mvv_fachberater`'); + + // datafields for study courses + $db->exec("ALTER TABLE `datafields` + CHANGE `object_type` `object_type` + ENUM('sem','inst','user','userinstrole','usersemdata','roleinstdata', + 'moduldeskriptor','modulteildeskriptor','studycourse') NULL DEFAULT NULL"); + + // switch to enable/disable studycourse info page + $db->exec( + "INSERT INTO `config` (`field`, `value`, `type`, `range`, + `section`, `mkdate`, `chdate`, `description`) + VALUES + ('ENABLE_STUDYCOURSE_INFO_PAGE', '0', 'boolean', 'global', + 'global', UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), + 'Shows an icon to open a dialog with studycourse informations in module search if true.')" + ); + + // new fields for study courses + $db->exec("ALTER TABLE `mvv_studiengang` + ADD `enroll` VARCHAR(50) NULL DEFAULT NULL AFTER `schlagworte`"); + $db->exec("ALTER TABLE `mvv_studiengang` + ADD `abschlussgrad` VARCHAR(32) NULL DEFAULT NULL AFTER `schlagworte`"); + $db->exec("ALTER TABLE `mvv_studiengang` + ADD `studienplaetze` INT UNSIGNED NULL DEFAULT NULL AFTER `schlagworte`"); + $db->exec("ALTER TABLE `mvv_studiengang` + ADD `studienzeit` TINYINT UNSIGNED NULL DEFAULT NULL AFTER `schlagworte`"); + + // postgraduate study courses (Aufbaustudiengänge) + $db->exec("CREATE TABLE `mvv_aufbaustudiengang` ( + `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `grund_stg_id` CHAR(32) COLLATE latin1_bin NOT NULL, + `aufbau_stg_id` CHAR(32) COLLATE latin1_bin NOT NULL, + `typ` VARCHAR(32) COLLATE latin1_bin NOT NULL, + `kommentar` TEXT NULL, + `author_id` CHAR(32) COLLATE latin1_bin NOT NULL, + `editor_id` CHAR(32) COLLATE latin1_bin NOT NULL, + `mkdate` INT(11) NOT NULL, + `chdate` INT(11) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `grund_stg_id` (`grund_stg_id`,`aufbau_stg_id`) + ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); + + // types of study courses + $db->exec("CREATE TABLE `mvv_studycourse_type` ( + `studiengang_id` CHAR(32) COLLATE latin1_bin NOT NULL, + `type` VARCHAR(32) COLLATE latin1_bin NOT NULL, + `author_id` CHAR(32) COLLATE latin1_bin NULL, + `editor_id` CHAR(32) COLLATE latin1_bin NULL, + `mkdate` INT(11) NOT NULL, + `chdate` INT(11) NOT NULL, + PRIMARY KEY (`studiengang_id`, `type`) + ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); + + // assigned languages to study course + $db->execute("CREATE TABLE `mvv_studycourse_language` ( + `studiengang_id` char(32) COLLATE latin1_bin NOT NULL, + `lang` varchar(32) COLLATE latin1_bin NOT NULL, + `position` int(11) NOT NULL DEFAULT '9999', + `author_id` char(32) COLLATE latin1_bin DEFAULT NULL, + `editor_id` char(32) COLLATE latin1_bin DEFAULT NULL, + `mkdate` int(11) NOT NULL, + `chdate` int(11) NOT NULL, + PRIMARY KEY (`studiengang_id`, `lang`) + ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); + + // add index to speed up filters + $db->execute("ALTER TABLE `mvv_modul_inst` ADD INDEX (`institut_id`)"); + } + + public function down() + { + DBManager::get()->exec("DROP TABLE `mvv_files`, `mvv_files_filerefs`, `mvv_files_ranges`, `mvv_contacts`, `mvv_extern_contacts`"); + DBManager::get()->exec("DROP TABLE `mvv_aufbaustudiengang`, `mvv_studycourse_type`, `mvv_studycourse_language`"); + DBManager::get()->exec("ALTER TABLE `mvv_studiengang` + DROP `studienzeit`, + DROP `studienplaetze`, + DROP `abschlussgrad`, + DROP `enroll`;"); + DBManager::get()->exec("ALTER TABLE `datafields` + CHANGE `object_type` `object_type` + ENUM('sem','inst','user','userinstrole','usersemdata','roleinstdata', + 'moduldeskriptor','modulteildeskriptor') NULL DEFAULT NULL"); + DBManager::get()->exec( + "DELETE FROM config WHERE field = 'ENABLE_STUDYCOURSE_INFO_PAGE'" + ); + DBManager::get()->exec( + "CREATE TABLE `mvv_fachberater` ( + `stgteil_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `user_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `position` int(11) NOT NULL, + `author_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `editor_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `mkdate` bigint(20) NOT NULL, + `chdate` bigint(20) NOT NULL, + PRIMARY KEY (`stgteil_id`,`user_id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC"); + DBManager::get()->exec( + "CREATE TABLE `mvv_modul_user` ( + `modul_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `user_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `gruppe` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `position` int(11) NOT NULL DEFAULT '9999', + `author_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `editor_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `mkdate` bigint(20) NOT NULL, + `chdate` bigint(20) NOT NULL, + PRIMARY KEY (`modul_id`,`user_id`,`gruppe`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;"); + DBManager::get()->exec( + "CREATE TABLE `mvv_dokument` ( + `dokument_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `url` tinytext COLLATE utf8mb4_unicode_ci NOT NULL, + `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + `linktext` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + `beschreibung` text COLLATE utf8mb4_unicode_ci, + `author_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `editor_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `mkdate` bigint(20) NOT NULL, + `chdate` bigint(20) NOT NULL, + PRIMARY KEY (`dokument_id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC"); + DBManager::get()->exec( + "CREATE TABLE `mvv_dokument_zuord` ( + `dokument_zuord_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `dokument_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `range_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `object_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, + `position` int(3) NOT NULL DEFAULT '999', + `kommentar` tinytext COLLATE utf8mb4_unicode_ci, + `author_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `editor_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `mkdate` bigint(20) NOT NULL, + `chdate` bigint(20) NOT NULL, + PRIMARY KEY (`dokument_zuord_id`), + UNIQUE KEY `dokument_id` (`dokument_id`,`range_id`,`object_type`) USING BTREE, + KEY `range_id_object_type` (`range_id`,`object_type`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC"); + } +} diff --git a/db/migrations/1.269_fix_missing_consultation_events.php b/db/migrations/1.269_fix_missing_consultation_events.php deleted file mode 100644 index a0548e0..0000000 --- a/db/migrations/1.269_fix_missing_consultation_events.php +++ /dev/null @@ -1,96 +0,0 @@ -fetchFirst($query); - - LegacyConsultationSlot::findAndMapMany( - function ($slot) { - // This is wrapped in a try/catch block since we can only assure - // that the LegacyConsultationSlot is used for updating the event itself. - // In the subsequent procedure, the related bookings are stored as well - // which will trigger another update of the event - this time on the - // ConsultationSlot object itself, not on the legacy one. Since this - // has code changes for Stud.IP 5.0 this will fail but we can neglect - // that since the event is already updated. - try { - $slot->updateEvents(); - } catch (Exception $e) { - } - }, - $ids - ); - } -} - -class LegacyConsultationSlot extends ConsultationSlot -{ - /** - * Updates the teacher event that belongs to the slot. This will either be - * set to be unoccupied, occupied by only one user or by a group of user. - */ - public function updateEvents() - { - if (count($this->bookings) === 0 && !$this->block->calendar_events) { - $this->events->delete(); - return; - } - - $teacher = User::find($this->block->teacher_id); - if (!$teacher) { - return; - } - - $event = $this->event; - if (!$event) { - $event = $this->createEvent($teacher); - - $this->teacher_event_id = $event->id; - $this->store(); - } - - setTempLanguage($teacher->id); - - if (count($this->bookings) > 0) { - $event->category_intern = 1; - - if (count($this->bookings) === 1) { - $booking = $this->bookings->first(); - - $event->summary = sprintf( - _('Sprechstundentermin mit %s'), - $booking->user->getFullName() - ); - $event->description = $booking->reason; - } else { - $event->summary = sprintf( - _('Sprechstundentermin mit %u Personen'), - count($this->bookings) - ); - $event->description = implode("\n\n----\n\n", $this->bookings->map(function ($booking) { - return "- {$booking->user->getFullName()}:\n{$booking->reason}"; - })); - } - } else { - $event->category_intern = 9; - $event->summary = _('Freier Sprechstundentermin'); - $event->description = _('Dieser Sprechstundentermin ist noch nicht belegt.'); - } - - restoreLanguage(); - - $event->store(); - } -} diff --git a/db/migrations/1.270_step_00332_mvv_overlapping_courses.php b/db/migrations/1.270_step_00332_mvv_overlapping_courses.php deleted file mode 100644 index 67cfa79..0000000 --- a/db/migrations/1.270_step_00332_mvv_overlapping_courses.php +++ /dev/null @@ -1,86 +0,0 @@ -exec($query); - - $query = "CREATE TABLE IF NOT EXISTS `mvv_ovl_conflicts` ( - `conflict_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, - `selection_id` int(11) NOT NULL, - `base_abschnitt_id` varchar(32) COLLATE latin1_bin NOT NULL, - `base_modulteil_id` varchar(32) COLLATE latin1_bin NOT NULL, - `base_course_id` varchar(32) COLLATE latin1_bin NOT NULL, - `base_metadate_id` varchar(32) COLLATE latin1_bin NOT NULL, - `comp_abschnitt_id` varchar(32) COLLATE latin1_bin NOT NULL, - `comp_modulteil_id` varchar(32) COLLATE latin1_bin NOT NULL, - `comp_course_id` varchar(32) COLLATE latin1_bin NOT NULL, - `comp_metadate_id` varchar(32) COLLATE latin1_bin NOT NULL, - PRIMARY KEY (`conflict_id`), - KEY `selection_id` (`selection_id`) - ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC"; - - DBManager::get()->exec($query); - - $query = "CREATE TABLE IF NOT EXISTS `mvv_ovl_excludes` ( - `selection_id` varchar(32) COLLATE latin1_bin NOT NULL, - `course_id` varchar(32) COLLATE latin1_bin NOT NULL, - PRIMARY KEY (`selection_id`,`course_id`), - KEY `course_id` (`course_id`) USING BTREE - ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC"; - - DBManager::get()->exec($query); - - // Add config (2nd select shows only versions inside a multiple course of study) - $query = "INSERT IGNORE INTO `config` ( - `field`, `value`, `type`, `range`, - `section`, `description`, - `mkdate`, `chdate` - ) VALUES ( - 'MVV_OVERLAPPING_SHOW_VERSIONS_INSIDE_MULTIPLE_STUDY_COURSES', - '0', 'boolean', 'global', - 'global', 'Zeigt als zweite Auswahl bei Mehrfachstudiengängen nur Versionen der dazugehörigen Teilstudiengänge an.', - UNIX_TIMESTAMP(), UNIX_TIMESTAMP() - )"; - - DBManager::get()->exec($query); - - SimpleORMap::expireTableScheme(); - } - - public function down() - { - $query = "DROP TABLE `mvv_ovl_selections`"; - DBManager::get()->exec($query); - $query = "DROP TABLE `mvv_ovl_conflicts`"; - DBManager::get()->exec($query); - $query = "DROP TABLE `mvv_ovl_excludes`"; - DBManager::get()->exec($query); - $query = "DELETE FROM `config` - WHERE `field` = 'MVV_OVERLAPPING_SHOW_VERSIONS_INSIDE_MULTIPLE_STUDY_COURSES'"; - DBManager::get()->exec($query); - - SimpleORMap::expireTableScheme(); - } -} diff --git a/db/migrations/1.270_tic8458_add_upload_description.php b/db/migrations/1.270_tic8458_add_upload_description.php new file mode 100644 index 0000000..5b26b24 --- /dev/null +++ b/db/migrations/1.270_tic8458_add_upload_description.php @@ -0,0 +1,36 @@ +exec( + "INSERT INTO `config` + (`field`, `value`, `type`, `range`, `section`, `mkdate`, `chdate`, + `description`) + VALUES + ('ENABLE_DESCRIPTION_ENTRY_ON_UPLOAD', '1', 'boolean', 'global', 'files', UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), + 'Whether to allow adding a description directly after file upload (true) or not (false). Defaults to true.')" + ); + } + + + public function down() + { + $db = DBManager::get(); + + $db->exec( + "DELETE FROM `config` + WHERE `field` = 'ENABLE_DESCRIPTION_ENTRY_ON_UPLOAD'" + ); + } + + + public function description() + { + return 'Adds the configuration entry ENABLE_DESCRIPTION_ENTRY_ON_UPLOAD to make it possible to enter a file description directly after uploading the file(s).'; + } +} diff --git a/db/migrations/1.271_jsonapi_cors_origin_config.php b/db/migrations/1.271_jsonapi_cors_origin_config.php deleted file mode 100644 index 58a8fe7..0000000 --- a/db/migrations/1.271_jsonapi_cors_origin_config.php +++ /dev/null @@ -1,35 +0,0 @@ -exec($query); - } - - public function down() - { - $query = "DELETE `config`, `config_values` - FROM `config` - LEFT JOIN `config_values` USING (`field`) - WHERE `field` = 'JSONAPI_CORS_ORIGIN'"; - DBManager::get()->exec($query); - } -} diff --git a/db/migrations/1.271_room_management_migration.php b/db/migrations/1.271_room_management_migration.php new file mode 100644 index 0000000..8fd748b --- /dev/null +++ b/db/migrations/1.271_room_management_migration.php @@ -0,0 +1,2390 @@ + 'Building', + 'Hörsaal' => 'Room', + 'Übungsraum' => 'Room', + 'Gerät' => 'Resource', + ]; + $GLOBALS['RESOURCE_ADMINISTRATION_PERSON_URL'] = 'https://example.org/person/%s'; + $GLOBALS['RESOURCE_MIGRATION_RESOURCE_TREES_TO_BE_DELETED'] = []; + $GLOBALS['RESOURCE_MIGRATION_MIGRATE_COURSE_PERMISSIONS'] = true; + $GLOBALS['RESOURCE_PROPERTIES_TO_BE_DELETED'] = []; + $GLOBALS['RESOURCE_MIGRATION_SPECIALRESOURCESPLUGIN'] = false; + $GLOBALS['RESOURCE_PROPERTIES_TO_BE_MODIFIED'] = [ + 'Adresse' => [ + 'name' => 'address', + 'old_type' => 'text' + ], + 'Audio-Anlage' => [ + 'name' => 'has_loudspeakers', + 'old_type' => 'bool', + 'requestable' => true, + 'searchable' => true + ], + 'Beamer' => [ + 'name' => 'has_projector', + 'old_type' => 'bool', + 'requestable' => true, + 'searchable' => true + ], + 'behindertengerecht' => [ + 'name' => 'accessible', + 'old_type' => 'bool', + 'requestable' => true, + 'searchable' => true + ], + 'Dozentenrechner' => [ + 'name' => 'has_computer', + 'old_type' => 'bool', + 'requestable' => true, + 'searchable' => true + ], + 'Hersteller' => [ + 'name' => 'manufacturer', + 'old_type' => 'select', + 'requestable' => true, + 'searchable' => true + ], + 'Inventarnummer' => [ + 'name' => 'inventory_number', + 'old_type' => 'num', + 'requestable' => false, + 'searchable' => true + ], + 'Seriennummer' => [ + 'name' => 'serial_number', + 'old_type' => 'num', + 'requestable' => false, + 'searchable' => true + ], + 'Sitzplätze' => [ + 'name' => 'seats', + 'old_type' => 'num', + 'requestable' => true, + 'searchable' => true + ], + 'Tageslichtprojektor' => [ + 'name' => 'has_overhead_projector', + 'old_type' => 'bool', + 'requestable' => true, + 'searchable' => true + ], + 'Verdunklung' => [ + 'name' => 'is_dimmable', + 'old_type' => 'bool', + 'requestable' => true, + 'searchable' => true + ], + 'Raumverantwortung' => [ + 'name' => 'responsible_person', + 'display_name' => 'Raumverantwortung', + 'old_type' => 'text', + 'new_type' => 'user', + 'requestable' => false, + 'searchable' => false, + 'info_label' => true + ] + ]; + $GLOBALS['RESOURCE_PROPERTIES_TO_BE_MERGED'] = []; + } + + + //Assign orphaned resources (resources without category_id) + //to a new category: + public function assignOrphanedResources(PDO $db) + { + $orphaned_resources = $db->query( + "SELECT id FROM resources + WHERE (category_id IS NULL) OR (category_id = '') + OR category_id NOT IN (SELECT id from resource_categories);" + )->fetchAll(PDO::FETCH_COLUMN, 0); + + if ($orphaned_resources) { + //Create a resource category for those resources: + + $md5 = md5('OrphanedResourcesCategory' . rand()); + + $db->execute( + "INSERT INTO resource_categories + (id, name, class_name, description, mkdate, chdate) + VALUES + (?, 'Verwaiste Ressourcen', 'Resource', '', UNIX_TIMESTAMP(), UNIX_TIMESTAMP())", + [$md5] + ); + + //Now we assign all orphaned resources to that category: + $db->execute( + "UPDATE resources SET category_id = ? + WHERE (category_id IS NULL) OR (category_id = '')", + [$md5] + ); + } + } + + + public function deleteUnfinishedResources(PDO $db) + { + $db->exec( + "DELETE from resources + WHERE name = 'Neues Objekt';" + ); + } + + + public function migrateBookingRepeatIntervals(PDO $db) + { + //Buchungen unbekannter Räume entfernen + $db->exec("DELETE resource_bookings FROM resource_bookings LEFT JOIN resources ON resources.id=resource_id WHERE resources.id IS NULL"); + + //mehrtägige korrigieren + $db->exec("UPDATE `resource_bookings` SET end=repeat_end WHERE repeat_end > end AND IFNULL(old_rep_interval,0) = 0"); + + + //Get all resource_bookings rows that have repetition intervals set: + $booking_rows = $db->query( + "SELECT * + FROM resource_bookings + WHERE repeat_end > end AND IFNULL(old_rep_interval,0) > 0" + )->fetchAll(); + + $update_stmt = $db->prepare( + "UPDATE resource_bookings + SET repetition_interval = :repetition_interval + WHERE id = :id;" + ); + + foreach ($booking_rows as $row) + { + $date_interval = ''; + if ($row['old_rep_week_of_month']) { + $date_interval = 'P' . $row['old_rep_interval'] . 'M'; + } elseif ($row['old_rep_interval']) { + if ($row['old_rep_month_of_year']) { + $date_interval = 'P' . $row['old_rep_interval'] . 'Y'; + } elseif ($row['old_rep_day_of_month']) { + $date_interval = 'P' . $row['old_rep_interval'] . 'M'; + } elseif ($row['old_rep_day_of_week']) { + $date_interval = 'P' . ($row['old_rep_interval'] * 7) . 'D'; + } else { + $date_interval = 'P' . $row['old_rep_interval'] . 'D'; + } + } + + $update_stmt->execute( + [ + 'id' => $row['id'], + 'repetition_interval' => $date_interval + ] + ); + } + + //Delete the old columns: + $db->exec( + "ALTER TABLE resource_bookings + DROP COLUMN old_rep_interval, + DROP COLUMN old_rep_month_of_year, + DROP COLUMN old_rep_day_of_month, + DROP COLUMN old_rep_week_of_month, + DROP COLUMN old_rep_day_of_week;" + ); + + //set booking_user_id for all bookings made by 'autor' + $db->exec("UPDATE resource_bookings rb INNER JOIN auth_user_md5 aum ON range_id=user_id INNER JOIN resource_permissions rp ON rp.user_id=aum.user_id AND rb.resource_id = rp.resource_id SET booking_user_id = range_id WHERE booking_user_id='' AND rp.perms='autor'"); + } + + + public function migrateCourseBoundPermissions(PDO $db) + { + if ($GLOBALS['RESOURCE_MIGRATION_MIGRATE_COURSE_PERMISSIONS']) { + //Get all permissions where the range_id + //represents a course-ID or an institute-ID: + + $permissions = $db->query( + "SELECT user_id, resource_id, perms, 'course' AS 'range' + FROM resource_permissions + WHERE + user_id IN ( + SELECT seminar_id FROM seminare + ) + UNION + SELECT user_id, resource_id, perms, 'institute' AS 'range' + FROM resource_permissions + WHERE + user_id IN ( + SELECT Institut_id FROM Institute + )" + )->fetchAll(); + + $course_participant_stmt = $db->prepare( + "SELECT user_id + FROM seminar_user + WHERE + Seminar_id = :course_id" + ); + + $institute_participant_stmt = $db->prepare( + "SELECT user_id, inst_perms + FROM user_inst + WHERE + Institut_id = :institute_id" + ); + + $get_user_permission_stmt = $db->prepare( + "SELECT perms + FROM resource_permissions + WHERE user_id = :user_id AND resource_id = :resource_id" + ); + + $update_user_permission_stmt = $db->prepare( + "UPDATE resource_permissions + SET perms = :perms + WHERE user_id = :user_id AND resource_id = :resource_id" + ); + + $create_user_permission_stmt = $db->prepare( + "INSERT INTO resource_permissions + (user_id, resource_id, perms, mkdate, chdate) + VALUES + (:user_id, :resource_id, :perms, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());" + ); + $create_specialresources_entry = $db->prepare( + "INSERT INTO specialresourcesplugin_course_resources + (course_id, resource_id, mkdate, chdate) + VALUES + (:course_id, :resource_id, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());" + ); + + foreach ($permissions as $permission) { + //Get all participants of the course/institute: + $participants = []; + if ($permission['range'] == 'course') { + $course_participant_stmt->execute( + [ + 'course_id' => $permission['user_id'] + ] + ); + $participants = $course_participant_stmt->fetchAll(); + } elseif ($permission['range'] == 'institute') { + $institute_participant_stmt->execute( + [ + 'institute_id' => $permission['user_id'] + ] + ); + $participants = $institute_participant_stmt->fetchAll(); + } + + foreach ($participants as $participant) { + if (($permission['range'] == 'institute') && + !in_array($participant['inst_perms'], ['autor', 'tutor', 'dozent', 'admin'])) { + //The permission level in the institute is too low. + continue; + } + //Check if a permission exists for that participant: + $get_user_permission_stmt->execute( + [ + 'user_id' => $participant['user_id'], + 'resource_id' => $permission['resource_id'] + ] + ); + $existing_perm = $get_user_permission_stmt->fetch(PDO::FETCH_COLUMN); + + if ($existing_perm) { + if (!in_array($existing_perm, ['autor', 'tutor', 'admin'])) { + //Update the permission and set it to "autor": + $update_user_permission_stmt->execute( + [ + 'user_id' => $participant['user_id'], + 'resource_id' => $permission['resource_id'], + 'perms' => 'autor' + ] + ); + } + } else { + //Create a new permission: Give every participant + //'autor' permissions for the resource + //that was specified in the course permission: + $create_user_permission_stmt->execute( + [ + 'user_id' => $participant['user_id'], + 'resource_id' => $permission['resource_id'], + 'perms' => 'autor' + ] + ); + } + } + + if ($GLOBALS['RESOURCE_MIGRATION_SPECIALRESOURCESPLUGIN']) { + $create_specialresources_entry->execute( + [ + 'course_id' => $permission['user_id'], + 'resource_id' => $permission['resource_id'] + ] + ); + } + } + } + } + + public function migrateOwner() + { + $db = DBManager::get(); + $db->exec("INSERT IGNORE INTO resource_permissions + (user_id, resource_id, perms, mkdate, chdate) + SELECT r.owner_id, r.id, 'admin', r.mkdate, r.chdate + FROM resources r INNER JOIN resource_categories rc + ON r.category_id = rc.id AND class_name='Room' + WHERE EXISTS + (SELECT * FROM auth_user_md5 WHERE owner_id = user_id)"); + $responsible_property_id = + $db->fetchColumn("SELECT property_id FROM resource_property_definitions WHERE name = 'responsible_person'"); + if ($responsible_property_id) { + $db->exec("INSERT IGNORE INTO resource_properties + (resource_id, property_id,state,mkdate,chdate) + SELECT r.id, '$responsible_property_id', r.owner_id, r.mkdate, r.chdate + FROM resources r INNER JOIN resource_categories rc + ON r.category_id = rc.id AND class_name='Room' + WHERE EXISTS + (SELECT * FROM auth_user_md5 WHERE owner_id = user_id)"); + } + $db->exec("ALTER TABLE resources DROP COLUMN owner_id"); + } + + public function deleteOldPermissions(PDO $db) { + //Delete all permissions that aren't permissions of existing users: + $db->exec( + "DELETE FROM resource_permissions + WHERE user_id NOT IN ( + SELECT user_id FROM auth_user_md5 + );" + ); + } + + + public function deleteResources() + { + $resource_trees_to_be_deleted = + $GLOBALS['RESOURCE_MIGRATION_RESOURCE_TREES_TO_BE_DELETED']; + if (count($resource_trees_to_be_deleted)) { + foreach ($resource_trees_to_be_deleted as $resource_id) { + $resource = Resource::find($resource_id); + if ($resource instanceof Resource) { + $resource->delete(); + } + } + } + } + + + public function migrateLocations(PDO $db, $location_cat_id = null) + { + if (!$location_cat_id) { + //This should not happen! + throw new Exception('Internal error!'); + } + + //First we create one Location data set for orphaned buildings: + $orphan_location_stmt = $db->prepare( + "INSERT INTO resources + (id, category_id, name) + VALUES (:id, :category_id, :name);" + ); + + $orphan_location_id = md5('SonstigeGebäudeResourceLocation' . rand()); + + $orphan_location_stmt->execute( + [ + 'id' => $orphan_location_id, + 'category_id' => $location_cat_id, + 'name' => 'Sonstige Gebäude' + ] + ); + + //We must get all IDs of resources which are parents + //of building resources. Such resources and their categories + //are Locations. + //Buildings without a parent are attached to a new location + //which is built for this case. + + $building_resources = $db->query( + "SELECT resources.id as id, parent_id FROM resources + INNER JOIN resource_categories + ON resources.category_id = resource_categories.id + WHERE + resource_categories.class_name = 'Building';" + )->fetchAll(); + + //We have the ID and the parent-ID of all building resources. + //Now we get all categories of the building's parents + //and set their class_name to 'Location'. + //Furthermore we remove the parent's parents since Location + //resources must not have parent resources. + + $get_parent_stmt = $db->prepare( + "SELECT id, category_id FROM resources + WHERE id = :parent_id" + ); + + $update_location_category_stmt = $db->prepare( + "UPDATE resource_categories SET class_name = 'Location' + WHERE id = :category_id;" + ); + + $update_parent_stmt = $db->prepare( + "UPDATE resources SET parent_id = :parent_id + WHERE id = :id;" + ); + + $update_category_stmt = $db->prepare( + "UPDATE resources SET category_id = :category_id + WHERE id = :id;" + ); + + //Ok, now we can look for all buildings and give them a parent_id + //if they don't have one and we can remove their parent's parent_id, + //if they have one. + + foreach ($building_resources as $building) { + if ($building['parent_id']) { + //parent_id is set: get the parent: + $get_parent_stmt->execute( + ['parent_id' => $building['parent_id']] + ); + + $parents = $get_parent_stmt->fetchAll(); + + foreach ($parents as $parent) { + if ($parent['category_id']) { + //Update the parent's category since it is a location: + $update_location_category_stmt->execute( + [ + 'category_id' => $parent['category_id'] + ] + ); + } else { + //Parent does not belong to a category: + //set the category to our orphaned location category: + $update_category_stmt->execute( + [ + 'category_id' => $location_cat_id, + 'id' => $parent['id'] + ] + ); + } + + if ($parent['parent_id']) { + //If the parent's parent_id is set we must delete it: + $update_parent_stmt->execute( + [ + 'parent_id' => '', + 'id' => $parent['id'] + ] + ); + } + } + } else { + //The building has no parent_id: We must add it to our + //orphan building location: + $update_parent_stmt->execute( + [ + 'parent_id' => $orphan_location_id, + 'id' => $building['id'] + ] + ); + } + } + + //Now all buildings should be connected to locations and all locations + //should be without a parent. + } + + + public function migrateBuildings(PDO $db) + { + //We must set the class name to 'Building' for all + //resource categories whose name is like 'gebäude'. + + $db->exec( + "UPDATE resource_categories SET class_name = 'Building' + WHERE name LIKE '%gebäude%';" + ); + + } + + + public function migrateRooms(PDO $db) + { + //Set the category's class name to 'Room' for all categories + //which have a name similar to 'Raum' or 'Saal': + + $db->exec( + "UPDATE resource_categories + SET class_name = 'Room' + WHERE name LIKE '%raum%' OR name LIKE '%saal%';" + ); + + + } + + + public function deleteExistingProperties(PDO $db) + { + $properties_to_be_deleted = $GLOBALS['RESOURCE_PROPERTIES_TO_BE_DELETED']; + if (!count($properties_to_be_deleted)) { + //Nothing to do. + return; + } + + $delete_definition_stmt = $db->prepare( + 'DELETE FROM resource_property_definitions + WHERE property_id IN ( :property_ids )' + ); + $delete_resource_property_stmt = $db->prepare( + 'DELETE FROM resource_properties + WHERE property_id IN ( :property_ids )' + ); + $delete_resource_request_property_stmt = $db->prepare( + 'DELETE FROM resource_request_properties + WHERE property_id IN ( :property_ids )' + ); + + $delete_resource_request_property_stmt->execute( + ['property_ids' => $properties_to_be_deleted] + ); + $delete_resource_property_stmt->execute( + ['property_ids' => $properties_to_be_deleted] + ); + $delete_definition_stmt->execute( + ['property_ids' => $properties_to_be_deleted] + ); + } + + + public function migrateExistingProperties(PDO $db) + { + $this->write( + '# Migrating existing properties.' + ); + + //rename resource properties: + $get_property_stmt = $db->prepare( + "SELECT property_id + FROM resource_property_definitions + WHERE + name = :name AND type = :type;" + ); + + $update_property_stmt = $db->prepare( + "UPDATE resource_property_definitions + SET name = :name, + `system` = 1, + searchable = :searchable, + range_search = :range_search + WHERE property_id = :property_id;" + ); + + $insert_new_prop_stmt = $db->prepare( + "INSERT INTO resource_property_definitions + (`property_id`, `name`, `description`, `type`, `options`, + `info_label`, `display_name`, `searchable`, `range_search`, + `write_permission_level`, `system`, `mkdate`, `chdate`) + VALUES + (:property_id, :name, :description, :type, :options, + :info_label, :display_name, :searchable, :range_search, + :write_permission_level, :system, :mkdate, :chdate);" + ); + + $delete_duplicates_stmt = $db->prepare( + 'DELETE FROM resource_property_definitions + WHERE property_id IN ( :duplicate_ids );' + ); + + $requestable_attr_stmt = $db->prepare( + "UPDATE resource_category_properties + SET requestable = :requestable + WHERE property_id = :property_id;" + ); + + $properties_to_be_modified = $GLOBALS['RESOURCE_PROPERTIES_TO_BE_MODIFIED']; + foreach ($properties_to_be_modified as $old_name => $data) { + //Check if the old property exists: + $get_property_stmt->execute( + [ + 'name' => $old_name, + 'type' => $data['old_type'] + ] + ); + $old_property_ids = $get_property_stmt->fetchAll( + PDO::FETCH_COLUMN, + 0 + ); + if ($data['name'] === 'seats' && !count($old_property_ids)) { + $old_property_ids = $db->fetchFirst( + "SELECT `property_id` + FROM `resource_property_definitions` + WHERE `system` = 2"); + } + + //Check if the new property already exists: + $get_property_stmt->execute( + [ + 'name' => $data['name'], + 'type' => $data['old_type'] + ] + ); + + $final_property_id = null; + $duplicate_ids = []; + + $new_property_id = $get_property_stmt->fetchColumn(); + if ($new_property_id) { + //The new property already exists. We must set all values + //from the old property to the new property. + $final_property_id = $new_property_id; + $duplicate_ids = $old_property_ids; + } + + if (!$final_property_id) { + //The new property doesn't exist yet. We must either + //take one of the old properties and rename it + //or we must create a new property with that name. + if ((count($old_property_ids) > 0) && !$final_property_id) { + $this->write( + sprintf( + 'Property-IDs for "%1$s" = [%2$s]', + $old_name, + implode(', ', $old_property_ids) + ) + ); + if (count($old_property_ids) > 1) { + //Remove the first item of the $old_property_list array, + //store it in $final_property and use the other IDs + //in the duplicates array. + $final_property_id = array_shift($old_property_ids); + $duplicate_ids[] = $old_property_ids; + } else { + //The only property ID is the first item in the + //$old_property_ids array. + $final_property_id = $old_property_ids[0]; + } + $this->write( + sprintf( + 'Renaming property "%1$s" to "%2$s".', + $old_name, + $data['name'] + ) + ); + //Update the property: + $update_property_stmt->execute( + [ + 'name' => $data['name'], + 'searchable' => ($data['searchable'] ? '1' : '0'), + 'range_search' => ($data['range_search'] ? '1' : '0'), + 'property_id' => $final_property_id + ] + ); + } else { + $this->write( + sprintf( + 'No property with the name "%1$s" and the type "%2$s" could be found! Creating a new property.', + $old_name, + $data['old_type'] + ) + ); + //There is no old property defined. We must define one. + $now = time(); + $property_id = md5('RRV2NewProperty' . $data['name'] . rand()); + $this->write( + 'INFO: The property-ID of the new property "' + . $data['name'] . '" is: ' . $property_id + ); + $insert_new_prop_stmt->execute( + [ + 'property_id' => $property_id, + 'name' => $data['name'], + 'description' => ( + $data['description'] + ? $data['description'] + : '' + ), + 'system' => 1, + 'type' => $data['new_type'] ? $data['new_type'] : $data['old_type'], + 'options' => ($data['options'] ? $data['options'] : ''), + 'info_label' => $data['info_label'] ? '1' : '0', + 'display_name' => ( + $data['display_name'] + ? $data['display_name'] + : $data['name'] + ), + 'searchable' => ($data['searchable'] ? '1' : '0'), + 'range_search' => ($data['range_search'] ? '1' : '0'), + 'write_permission_level' => 'admin-global', + 'mkdate' => $now, + 'chdate' => $now + ] + ); + $final_property_id = $property_id; + } + } + + if ($duplicate_ids) { + $this->write( + sprintf( + 'Moving old property values to the new property "%s".', + $data['name'] + ) + ); + //Now we must "redirect" all links to the duplicates + //to the property that is left + //and then remove the duplicates. + + $tables = [ + 'resource_category_properties', + 'resource_properties', + 'resource_request_properties' + ]; + foreach ($tables as $table) { + $update_tables_stmt = $db->prepare( + sprintf( + "UPDATE IGNORE %s + SET property_id = :property_id + WHERE property_id IN ( :duplicate_ids );", + $table + ) + ); + $update_tables_stmt->execute( + [ + 'property_id' => $final_property_id, + 'duplicate_ids' => $duplicate_ids + ] + ); + } + + $this->write( + sprintf( + 'Deleting property values for property-IDs [%s].', + implode(', ', $duplicate_ids) + ) + ); + + //Now we delete the duplicates: + $delete_duplicates_stmt->execute( + [ + 'duplicate_ids' => $duplicate_ids + ] + ); + + //After that we have to delete those property relations + //whose property_id couldn't be set above + //to avoid duplicate primary keys. + $delete_relations_stmt = $db->prepare( + sprintf( + "DELETE FROM %s + WHERE property_id IN ( :duplicate_ids );", + $table + ) + ); + foreach ($tables as $table) { + $delete_relations_stmt->execute( + [ + 'duplicate_ids' => $duplicate_ids + ] + ); + } + } + + //Finally we make the property requestable, if configured: + $requestable_attr_stmt->execute( + [ + 'property_id' => $final_property_id, + 'requestable' => ($data['requestable'] ? '1' : '0') + ] + ); + } + + $this->write( + 'Finished migrating existing properties.' + ); + } + + + public function mergeProperties(PDO $db) + { + $this->write( + '# Merging properties.' + ); + + $get_prop_stmt = $db->prepare( + "SELECT property_id, name FROM resource_property_definitions + WHERE name = :name AND type = :type" + ); + + $insert_prop_stmt = $db->prepare( + "INSERT INTO resource_property_definitions + (`property_id`, `name`, `description`, `type`, `options`, + `info_label`, `display_name`, `searchable`, `range_search`, + `write_permission_level`, `system`, `mkdate`, `chdate`) + VALUES + (:property_id, :name, :description, :type, :options, + :info_label, :display_name, :searchable, :range_search, + :write_permission_level, :system, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());" + ); + + $get_property_values_stmt = $db->prepare( + "SELECT property_id, resource_id, state FROM resource_properties + WHERE property_id = :property_id;" + ); + + $insert_new_value_stmt = $db->prepare( + "INSERT IGNORE INTO resource_properties + (property_id, resource_id, state) + VALUES + (:property_id, :resource_id, :state);" + ); + + $get_category_id_stmt = $db->prepare( + "SELECT DISTINCT category_id FROM resource_category_properties + WHERE property_id IN ( :property_ids );" + ); + + $prop_cat_assign_stmt = $db->prepare( + "INSERT INTO resource_category_properties + (`category_id`, `property_id`, `requestable`, `protected`, + `system`, `form_text`, `mkdate`, `chdate`) + VALUES + (:category_id, :property_id, :requestable, :protected, + :system, :form_text, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()) + ON DUPLICATE KEY UPDATE + `requestable` = :requestable, + `protected` = :protected, + `system` = :system, + `form_text` = :form_text;" + ); + + $delete_old_prop_stmt = $db->prepare( + "DELETE FROM resource_property_definitions + WHERE property_id IN ( :property_ids );" + ); + + $delete_resource_request_property_values_stmt = $db->prepare( + "DELETE FROM resource_request_properties + WHERE property_id IN ( :property_ids );" + ); + + $delete_resource_property_values_stmt = $db->prepare( + "DELETE FROM resource_properties + WHERE property_id IN ( :property_ids );" + ); + + $properties_to_be_merged = $GLOBALS['RESOURCE_PROPERTIES_TO_BE_MERGED']; + foreach ($properties_to_be_merged as $merged_prop) { + $get_prop_stmt->execute( + [ + 'name' => $merged_prop['name'], + 'type' => $merged_prop['type'] + ] + ); + + $merged_property_id = $get_prop_stmt->fetchColumn(); + + if (!$merged_property_id) { + $this->write( + sprintf( + 'Creating new property from the type "%1$s", named "%2$s"!', + $merged_prop['type'], + $merged_prop['name'] + ) + ); + + //Create a new property: + $merged_property_id = md5( + 'RRV2NewMergedProperty' + . $merged_prop['name'] . rand() + ); + + $this->write( + 'INFO: The property-ID of the new property "' + . $merged_prop['name'] . '" is: ' . $merged_property_id + ); + $insert_prop_stmt->execute( + [ + 'property_id' => $merged_property_id, + 'name' => $merged_prop['name'], + 'description' => ( + $merged_prop['description'] + ? $merged_prop['description'] + : '' + ), + 'type' => $merged_prop['type'], + 'options' => ( + $merged_prop['options'] + ? $merged_prop['options'] + : '' + ), + 'info_label' => 0, + 'display_name' => ( + $merged_prop['display_name'] + ? $merged_prop['display_name'] + : $merged_prop['name'] + ), + 'searchable' => ($merged_prop['searchable'] ? '1' : '0'), + 'range_search' => ($merged_prop['range_search'] ? '1' : '0'), + 'write_permission_level' => 'admin-global', + 'system' => 1 + ] + ); + } + + //Get the old properties and collect their values: + $old_values = []; + $old_property_ids = []; + foreach ($merged_prop['sources'] as $source_prop) { + //Get the property whose values shall be merged: + $get_prop_stmt->execute( + [ + 'name' => $source_prop['name'], + 'type' => $source_prop['type'] + ] + ); + $old_prop = $get_prop_stmt->fetch(); + if (!$old_prop) { + $this->write( + sprintf( + 'The old property with the name "%1$s" and the type "%2$s" doesn\'t exist!', + $source_prop['name'], + $source_prop['type'] + ) + ); + //The old property doesn't exist. + continue; + } + + $old_property_ids[] = $old_prop['property_id']; + + //Get all property values and convert them: + $get_property_values_stmt->execute( + [ + 'property_id' => $old_prop['property_id'] + ] + ); + + $all_values = $get_property_values_stmt->fetchAll(); + + foreach ($all_values as $value) { + if (!is_array($old_values[$value['resource_id']])) { + $old_values[$value['resource_id']] = []; + } + $old_values[$value['resource_id']][$source_prop['name']] = + $value['state']; + } + } + + //Merge the old property values for each resource: + foreach ($old_values as $resource_id => $o_values) { + $new_value = ''; + if ($merged_prop['value_conversion'] instanceof Closure) { + //Use a closure for the value conversion: + $new_value = $merged_prop['value_conversion']($o_values); + } else { + //Just append the old values: + foreach ($o_values as $old_value) { + $new_value .= $old_value; + } + } + + $this->write( + sprintf( + 'Merged property value(s) [%1$s] to new value "%2$s."', + implode(', ', $o_values), + $new_value + ) + ); + + //Insert the property value for the new property: + $insert_new_value_stmt->execute( + [ + 'property_id' => $merged_property_id, + 'resource_id' => $resource_id, + 'state' => $new_value + ] + ); + } + + //Assign the new property to all categories, + //resources and resource requests where the old properties + //are assigned to: + $get_category_id_stmt->execute( + ['property_ids' => $old_property_ids] + ); + $relevant_category_ids = $get_category_id_stmt->fetchAll( + PDO::FETCH_COLUMN, + 0 + ); + + if (!$relevant_category_ids) { + //The old properties aren't used anywhere. + //We can just delete them. + $delete_old_prop_stmt->execute( + ['property_ids' => $old_property_ids] + ); + return; + } + + foreach ($relevant_category_ids as $category_id) { + $prop_cat_assign_stmt->execute( + [ + 'category_id' => $category_id, + 'property_id' => $merged_property_id, + 'requestable' => $merged_prop['requestable'] ? '1' : '0', + 'protected' => '0', + 'system' => '1', + 'form_text' => '' + ] + ); + } + + $this->write('Deleting old merged properties!'); + + //We can delete the old properties here: + $delete_old_prop_stmt->execute( + [ + 'property_ids' => $old_property_ids + ] + ); + + //Delete entries from resource requests: + //Since the values have changed, the converted property values + //may be inadequate for the resource request and thereby + //the request property value for the property is deleted. + $delete_resource_request_property_values_stmt->execute( + [ + 'property_ids' => $old_property_ids + ] + ); + + //Delete all resource property values from the old property: + $delete_resource_property_values_stmt->execute( + [ + 'property_ids' => $old_property_ids + ] + ); + } + + $this->write( + 'Finished merging properties.' + ); + } + + + public function removeObsoleteConfigEntries(PDO $db) + { + $entries = [ + 'RESOURCES_ALLOW_CREATE_ROOMS', + 'RESOURCES_ALLOW_CREATE_TOP_LEVEL', + 'RESOURCES_ALLOW_DELETE_REQUESTS', + 'RESOURCES_ALLOW_REQUESTABLE_ROOM_REQUESTS', + 'RESOURCES_ALLOW_ROOM_REQUESTS_ALL_ROOMS', + 'RESOURCES_ENABLE_GROUPING', + 'RESOURCES_ENABLE_ORGA_CLASSIFY', + 'RESOURCES_ENABLE_SEM_SCHEDULE', + 'RESOURCES_ENABLE_VIRTUAL_ROOM_GROUPS', + 'RESOURCES_HIDE_PAST_SINGLE_DATES', + 'RESOURCES_INHERITANCE_PERMS', + 'RESOURCES_INHERITANCE_PERMS_ROOMS', + 'RESOURCES_LOCKING_ACTIVE', + 'RESOURCES_ROOM_REQUEST_DEFAULT_ACTION', + 'RESOURCES_SCHEDULE_EXPLAIN_USER_NAME', + 'RESOURCES_SEARCH_ONLY_REQUESTABLE_PROPERTY', + 'RESOURCES_SHOW_ROOM_NOT_BOOKED_HINT', + 'RESOURCES_ENABLE_ORGA_ADMIN_NOTICE' + ]; + + $stmt = $db->prepare( + 'DELETE FROM config WHERE field IN ( :entries );' + ); + + $stmt->execute(['entries' => $entries]); + } + + + public function createPropertyGroups(PDO $db) + { + $get_group_id_stmt = $db->prepare( + "SELECT id FROM resource_property_groups WHERE name = :name" + ); + $create_group_stmt = $db->prepare( + "INSERT INTO resource_property_groups + (`name`, `mkdate`, `chdate`) + VALUES + (:name, UNIX_TIMESTAMP(), UNIX_TIMESTAMP())" + ); + + $property_groups = []; + + if ($GLOBALS['RESOURCE_MIGRATION_PROPERTY_GROUP_PROPERTY_NAME_REGEX']) { + //A regular expression is set to split property names + //into a group part and a proprty name part. + + $group_name_regex = $GLOBALS['RESOURCE_MIGRATION_PROPERTY_GROUP_PROPERTY_NAME_REGEX']; + + $all_properties = $db->query( + "SELECT property_id, name FROM resource_property_definitions + ORDER BY name ASC" + )->fetchAll(); + + foreach ($all_properties as $property) { + $splitted_name = preg_split($group_name_regex, $property['name'], 2); + $group_name = trim($splitted_name[0]); + $new_property_name = trim($splitted_name[1]); + + if ($group_name && $new_property_name) { + //A group name could be extracted. + //Check if a group with that name already exists: + $get_group_id_stmt->execute( + [ + 'name' => $group_name + ] + ); + $group_id = $get_group_id_stmt->fetchColumn(); + if (!$group_id) { + //Create a new group: + $create_group_stmt->execute( + [ + 'name' => $group_name + ] + ); + //Get the ID of the group: + $get_group_id_stmt->execute(['name' => $group_name]); + $group_id = $get_group_id_stmt->fetchColumn(); + + if (!$group_id) { + //No such group. We can only move to the next property. + echo "no group-id!\n"; + continue; + } + } + + //Add the property to the list of properties + //to be added to the group. + if (!is_array($property_groups[$group_id])) { + $property_groups[$group_id] = []; + } + $property_groups[$group_id][] = [ + 'property_id' => $property['property_id'], + 'new_property_name' => $new_property_name + ]; + } + } + + $update_property_stmt = $db->prepare( + "UPDATE resource_property_definitions + SET property_group_id = :group_id + WHERE property_id = :property_id" + ); + + $update_property_with_name_stmt = $db->prepare( + "UPDATE resource_property_definitions + SET property_group_id = :group_id, + name = :new_name, + display_name = :new_name + WHERE property_id = :property_id" + ); + + foreach ($property_groups as $group_id => $property_list) { + foreach ($property_list as $property_data) { + if ($property_data['new_property_name']) { + $update_property_with_name_stmt->execute( + [ + 'group_id' => $group_id, + 'new_name' => $property_data['new_property_name'], + 'property_id' => $property_data['property_id'] + ] + ); + } else { + $update_property_stmt->execute( + [ + 'group_id' => $group_id, + 'property_id' => $property_data['property_id'] + ] + ); + } + } + } + } + + //At this point, some property groups may already have been created + //using the property group regex. But they can also be created + //by explicitly defining a name and the properties that are included + //in the group. + if (count($GLOBALS['RESOURCE_MIGRATION_NEW_PROPERTY_GROUP_LIST'])) { + $property_group_list = $GLOBALS['RESOURCE_MIGRATION_NEW_PROPERTY_GROUP_LIST']; + + $add_properties_stmt = $db->prepare( + "UPDATE resource_property_definitions + SET property_group_id = :group_id + WHERE name IN ( :names )" + ); + + foreach ($property_group_list as $group_name => $property_names) { + $get_group_id_stmt->execute(['name' => $group_name]); + $group_id = $get_group_id_stmt->fetchColumn(); + + if ($group_id) { + $add_properties_stmt->execute( + [ + 'group_id' => $group_id, + 'names' => $property_names + ] + ); + } else { + //Create a group with the specified group name first: + $create_group_stmt->execute(['name' => $group_name]); + + $get_group_id_stmt->execute(['name' => $group_name]); + $group_id = $get_group_id_stmt->fetchColumn(); + + if (!$group_id) { + //There is nothing we can do about it here. + continue; + } + + $add_properties_stmt->execute( + [ + 'group_id' => $group_id, + 'names' => $property_names + ] + ); + } + } + } + } + + + public function createMissingLocation(PDO $db) + { + //Check if there is a location category: + $location_category_id = $db->query( + "SELECT id FROM resource_categories + WHERE class_name = 'Location' + ORDER BY name LIMIT 1" + )->fetchAll(PDO::FETCH_COLUMN, 0); + + $location_resource_id = null; + if (!$location_category_id) { + //Create a location category: + $location_category_id = md5('LocationCategory_' . uniqid()); + + $create_category_stmt = $db->prepare( + "INSERT INTO resource_categories + (`id`, `name`, `system`, `class_name`, `mkdate`, `chdate`) + VALUES + (:id, :name, '1', 'Location', UNIX_TIMESTAMP(), UNIX_TIMESTAMP());" + ); + + $create_category_stmt->execute( + [ + 'id' => $location_category_id, + 'name' => _('Standort') + ] + ); + } + + //Check if all buildings have a location resource + //in their "parent chain": + $building_ids = []; + $get_buildings_stmt = $db->prepare( + "SELECT resources.id FROM resources + INNER JOIN resource_categories rc + ON resources.category_id = rc.id + WHERE rc.class_name = 'Building'" + ); + $get_buildings_stmt->execute(); + $building_ids = $get_buildings_stmt->fetchAll(PDO::FETCH_COLUMN, 0); + + $get_parent_stmt = $db->prepare( + "SELECT r1.parent_id as parent_id, rc.class_name as class_name + FROM resources r1 + LEFT JOIN resources r2 ON r2.id=r1.parent_id + INNER JOIN resource_categories rc ON r2.category_id = rc.id + WHERE r1.id = :resource_id" + ); + + $orphaned_top_level_resource_ids = []; + foreach ($building_ids as $building_id) { + //Traverse the resource tree upwards until + //a location resource is found or a resource + //with no parent is reached. + $building_location_id = null; + + $last_id = null; + $current_id = $building_id; + while($current_id && !$building_location_id) { + $get_parent_stmt->execute( + [ + 'resource_id' => $current_id + ] + ); + + $data = $get_parent_stmt->fetchOne(); + if ($data['class_name'] == 'Location') { + //We have found the location. + $building_location_id = $data['parent_id']; + $last_id = null; + } else { + //No location found. Go one layer up: + $last_id = $current_id; + $current_id = $data['parent_id']; + } + } + + //At this point, we have either found a location resource + //or a top level resource that is not a location but a + //parent resource of a building. + if (!$building_location_id && $last_id) { + $orphaned_top_level_resource_ids[] = $last_id; + } + } + + $location_id = $db->query( + "SELECT resources.id FROM resources + INNER JOIN resource_categories rc + ON resources.category_id = rc.id + WHERE rc.class_name = 'Location' + ORDER BY resources.name LIMIT 1" + )->fetchAll(PDO::FETCH_COLUMN, 0); + + if (!$location_id) { + //No location exists. In that case, we have to + //create a location resource. + + $location_name = Config::get()->UNI_NAME_CLEAN; + if (!$location_name) { + $location_name = _('Standort'); + } + + $create_location_stmt = $db->prepare( + "INSERT INTO resources + (`id`, `parent_id`, `category_id`, `name`, `requestable`, + `mkdate`, `chdate`) + VALUES + (:id, '', :category_id, :name, '0', UNIX_TIMESTAMP(), UNIX_TIMESTAMP());" + ); + + $location_id = md5('Location_' . $location_name . uniqid()); + $create_location_stmt->execute( + [ + 'id' => $location_id, + 'category_id' => $location_category_id, + 'name' => $location_name + ] + ); + } + + $assign_to_location_stmt = $db->prepare( + "UPDATE resources SET parent_id = :parent_id WHERE id = :id;" + ); + + foreach ($orphaned_top_level_resource_ids as $resource_id) { + $assign_to_location_stmt->execute( + [ + 'parent_id' => $location_id, + 'id' => $resource_id + ] + ); + } + } + + + public function fillResourceBookingIntervals(PDO $db) + { + $chunk_size = 100; + + //Delete everything from resource booking intervals first: + $db->exec('DELETE FROM resource_booking_intervals;'); + + $query = "SELECT COUNT(*) + FROM resource_bookings"; + $booking_count = $db->query($query)->fetchColumn(); + + for ($loop = 0; $loop < $booking_count; $loop += $chunk_size) { + $query = "SELECT id, resource_id, range_id, begin, end, booking_type + preparation_time, repeat_end, repeat_quantity, repetition_interval + FROM resource_bookings + LIMIT {$loop}, {$chunk_size}"; + $bookings = $db->query($query); + + $add_interval_stmt = $db->prepare( + "INSERT INTO resource_booking_intervals + (interval_id, booking_id, resource_id, begin, end, takes_place, + mkdate, chdate) + VALUES + (:interval_id, :booking_id, :resource_id, :begin, :end, 1, + UNIX_TIMESTAMP(), UNIX_TIMESTAMP());" + ); + + $bookings->setFetchMode(PDO::FETCH_ASSOC); + foreach ($bookings as $booking) { + //Calculate the time intervals the same way as in + //ResourceBooking::calculateTimeIntervals: + $add_interval = function ($begin, $end) use ($booking, $add_interval_stmt) { + $interval_id = md5( + 'ResourceBookingInterval' . + $booking['booking_id'] . + $booking['begin'] . + $booking['end'] . + uniqid() + ); + $add_interval_stmt->execute([ + ':interval_id' => $interval_id, + ':booking_id' => $booking['id'], + ':resource_id' => $booking['resource_id'], + ':begin' => $begin, + ':end' => $end, + ]); + }; + + $booking_begin = new DateTime(); + $booking_begin->setTimestamp($booking['begin']); + if ($booking['preparation_time']) { + $booking_begin->setTimestamp( + $booking['begin'] - $booking['preparation_time'] + ); + } + $booking_end = new DateTime(); + $booking_end->setTimestamp($booking['end']); + + //use begin and end to create the first interval: + $add_interval( + $booking_begin->getTimestamp(), + $booking_end->getTimestamp() + ); + + if (($booking['repeat_quantity'] > 0) || $booking['repeat_end']) { + //Repetition: we must check which repetition interval has been + //selected and then create entries for each repetition. + //Repetition starts with the begin date and ends with the + //"repeat_end" date. + + $repetition_end = new DateTime(); + $repetition_end->setTimestamp($booking['repeat_end']); + //The DateInterval constructor will throw an exception, + //if it cannot parse the string stored in $this->repetition_interval. + $repetition_interval = null; + if ($booking['repetition_interval']) { + try { + $repetition_interval = new DateInterval($booking['repetition_interval']); + } catch (Exception $e) { + //Invalid repetition interval string. + //Skip this booking since its repetition interval is invalid. + continue; + } + } + + if ($repetition_interval instanceof DateInterval) { + $duration = $booking_begin->diff($booking_end); + + //Check if end is later than begin to avoid + //infinite loops. + if ($repetition_end > $booking_begin) { + $current_begin = clone $booking_begin; + $current_begin->add($repetition_interval); + while ($current_begin < $repetition_end) { + $current_end = clone $current_begin; + $current_end->add($duration); + + $add_interval( + $current_begin->getTimestamp(), + $current_end->getTimestamp() + ); + + $current_begin->add($repetition_interval); + } + } + } + } + } + } + } + + + public function up() + { + //Load the special configuration first: + if (file_exists(__DIR__ . '/../../config/resource_migration.php')) { + require(__DIR__ . '/../../config/resource_migration.php'); + } else { + //The special configuration doesn't exist so that we have to load + //the default configuration for the demo data. + $this->loadDefaultConfiguration(); + } + $db = DBManager::get(); + + //add new configuration variables: + + $db->exec( + "INSERT IGNORE INTO config + ( + `field`, + `value`, + `type`, + `range`, + `section`, + `mkdate`, + `chdate`, + `description` + ) + VALUES + ( + 'RESOURCES_DIRECT_ROOM_REQUESTS_ONLY', + '0', + 'boolean', + 'global', + 'resources', + UNIX_TIMESTAMP(), + UNIX_TIMESTAMP(), + 'Restricts room requests so that only specific rooms can be requested.' + ), + ( + 'RESOURCES_MAP_SERVICE_URL', + 'https://www.openstreetmap.org/#map=19/LATITUDE/LONGITUDE', + 'string', + 'global', + 'resources', + UNIX_TIMESTAMP(), + UNIX_TIMESTAMP(), + 'The URL for a map service if you wish to use another service instead of OpenStreetMap. The default is: https://www.openstreetmap.org/#map=17/LATITUDE/LONGITUDE (LATITUDE and LONGITUDE are placeholders!)' + ), + ( + 'RESOURCES_MAX_PREPARATION_TIME', + '120', + 'integer', + 'global', + 'resources', + UNIX_TIMESTAMP(), + UNIX_TIMESTAMP(), + 'The maximum amount of time that can be used for preparation before the actual booking begins. The value represents minutes, not hours!' + ), + ( + 'RESOURCES_MIN_BOOKING_TIME', + '15', + 'integer', + 'global', + 'resources', + UNIX_TIMESTAMP(), + UNIX_TIMESTAMP(), + 'The minimum amount of minutes for the booking of a resource.' + ), + ( + 'RESOURCES_BOOKING_PLAN_START_HOUR', + '08:00', + 'string', + 'global', + 'resources', + UNIX_TIMESTAMP(), + UNIX_TIMESTAMP(), + 'The start hour for the default view of the booking plan.' + ), + ( + 'RESOURCES_BOOKING_PLAN_END_HOUR', + '20:00', + 'string', + 'global', + 'resources', + UNIX_TIMESTAMP(), + UNIX_TIMESTAMP(), + 'The start hour for the default view of the booking plan.' + ), + ( + 'RESOURCES_MIN_BOOKING_PERMS', + 'autor', + 'string', + 'global', + 'resources', + UNIX_TIMESTAMP(), + UNIX_TIMESTAMP(), + 'The minimum permission level for global booking rights on a resource.' + ), + ( + 'RESOURCES_MIN_REQUEST_PERMISSION', + '', + 'string', + 'global', + 'resources', + UNIX_TIMESTAMP(), + UNIX_TIMESTAMP(), + 'The minimum permission level for creating \"free\" requests that are not bound to a course.' + ), + ( + 'RESOURCES_DISPLAY_CURRENT_REQUESTS_IN_OVERVIEW', + '1', + 'boolean', + 'global', + 'resources', + UNIX_TIMESTAMP(), + UNIX_TIMESTAMP(), + 'Whether to display the list with current requests in the room management overview (true) or not (false).' + ), + ( + 'RESOURCES_SHOW_PUBLIC_ROOM_PLANS', + '0', + 'boolean', + 'global', + 'resources', + UNIX_TIMESTAMP(), + UNIX_TIMESTAMP(), + 'Whether to display the list of available public room plans.' + );" + ); + + //Convert configuration options that may already exist so that + //the format is identical: + + $db->exec( + "UPDATE config SET type = 'boolean', section = 'resources' + WHERE field = 'RESOURCES_DIRECT_ROOM_REQUESTS_ONLY';" + ); + + //Enable API routes (and the API itself). + //The new room and resource management system requires a few routes + //to be activated to work properly. + + $db->exec( + "UPDATE config SET value = '1' + WHERE field = 'API_ENABLED';" + ); + + $enable_api_route_stmt = $db->prepare( + "INSERT INTO api_consumer_permissions + (route_id, consumer_id, method, granted) + VALUES + (:route, 'global', :method, '1') + ON DUPLICATE KEY UPDATE + granted = '1';" + ); + $api_routes_to_enable = [ + '/user/:user_id' => 'get', + '/resources/booking_interval/:interval_id/toggle_takes_place' => 'post', + '/resources/booking/:booking_id/move' => 'post', + '/resources/booking/:booking_id/intervals' => 'get', + '/resources/resource/:resource_id/booking_plan' => 'get', + '/resources/resource/:resource_id/semester_plan' => 'get', + '/resources/request/:request_id/move' => 'post', + '/resources/request/:request_id/edit_reply_comment' => 'post', + '/resources/request/:request_id/toggle_marked' => 'post', + '/room_clipboard/:clipboard_id/booking_plan' => 'get', + '/room_clipboard/:clipboard_id/semester_plan' => 'get', + '/clipboard/:clipboard_id' => ['delete', 'put'], + '/clipboard/:clipboard_id/item' => 'post', + '/clipboard/:clipboard_id/item/:range_id' => 'delete', + '/clipboard/add' => 'post', + '/course/:course_id/members' => 'get', + '/semesters' => 'get' + ]; + + foreach ($api_routes_to_enable as $route => $methods) { + if (is_array($methods)) { + foreach ($methods as $method) { + $enable_api_route_stmt->execute( + [ + 'route' => md5($route), + 'method' => $method + ] + ); + } + } else { + $enable_api_route_stmt->execute( + [ + 'route' => md5($route), + 'method' => $methods + ] + ); + } + } + + + //add new tables: + + $db->exec( + "CREATE TABLE IF NOT EXISTS `resource_temporary_permissions` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `resource_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `user_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `begin` int(11) unsigned NOT NULL DEFAULT '0', + `end` int(11) unsigned NOT NULL DEFAULT '0', + `perms` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `mkdate` int(11) unsigned NOT NULL DEFAULT '0', + `chdate` int(11) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" + ); + + $db->exec( + "CREATE TABLE IF NOT EXISTS `resource_request_appointments` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `request_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `appointment_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `mkdate` int(11) unsigned NOT NULL DEFAULT '0', + `chdate` int(11) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" + ); + + $db->exec( + "CREATE TABLE IF NOT EXISTS `colour_values` ( + `colour_id` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `description` varchar(256) NOT NULL DEFAULT '', + `value` varchar(8) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'ffffffff', + `mkdate` int(11) unsigned NOT NULL DEFAULT '0', + `chdate` int(11) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`colour_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" + ); + + $db->exec( + "CREATE TABLE IF NOT EXISTS `separable_rooms` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `building_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `name` varchar(256) NOT NULL DEFAULT '', + `mkdate` int(11) unsigned NOT NULL DEFAULT '0', + `chdate` int(11) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" + ); + + $db->exec( + "CREATE TABLE IF NOT EXISTS `separable_room_parts` ( + `separable_room_id` int(10) NOT NULL, + `room_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `mkdate` int(11) unsigned NOT NULL DEFAULT '0', + `chdate` int(11) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`separable_room_id`,`room_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" + ); + + $db->exec( + "CREATE TABLE IF NOT EXISTS `clipboards` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `user_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + `name` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + `handler` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'Clipboard', + `allowed_item_class` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'StudipItem', + `mkdate` int(11) unsigned NOT NULL DEFAULT '0', + `chdate` int(11) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" + ); + + //The range_id in clipboard_items is extra large + //so that primary keys consisting of three other + //keys can also be used. + $db->exec( + "CREATE TABLE IF NOT EXISTS `clipboard_items` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `clipboard_id` int(11) NOT NULL, + `range_id` varchar(98) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `range_type` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'SimpleORMap', + `mkdate` int(11) unsigned NOT NULL DEFAULT '0', + `chdate` int(11) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" + ); + + //The property group table defines groups of resource properties + //that shall be displayed together. + $db->exec( + "CREATE TABLE IF NOT EXISTS `resource_property_groups` ( + `id` INT(11) NOT NULL AUTO_INCREMENT, + `name` VARCHAR(255) NOT NULL DEFAULT '', + `position` TINYINT(4) NOT NULL DEFAULT '0', + `mkdate` INT(11) NOT NULL DEFAULT '0', + `chdate` INT(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" + ); + + if ($GLOBALS['RESOURCE_MIGRATION_SPECIALRESOURCESPLUGIN']) { + $db->exec( + "CREATE TABLE IF NOT EXISTS specialresourcesplugin_course_resources( + course_id VARCHAR(32) NOT NULL, + resource_id VARCHAR(32) NOT NULL, + mkdate INT(10) NOT NULL DEFAULT '0', + chdate INT(10) NOT NULL DEFAULT '0', + PRIMARY KEY (course_id, resource_id) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" + ); + + } + + //Add colors: + $colours = [ + 'Resources.BookingPlan.Booking.Fg' => [ + 'ffffffff', + 'Die Textfarbe im Belegungsplan für gewöhnliche Buchungen.' + ], + 'Resources.BookingPlan.Booking.Bg' => [ + '129c94ff', + 'Die Farbe im Belegungsplan für gewöhnliche Buchungen.' + ], + 'Resources.BookingPlan.CourseBooking.Fg' => [ + 'ffffffff', + 'Die Textfarbe im Belegungsplan für veranstaltungsbezogene Buchungen.' + ], + 'Resources.BookingPlan.CourseBooking.Bg' => [ + '682c8bff', + 'Die Farbe im Belegungsplan für veranstaltungsbezogene Buchungen.' + ], + 'Resources.BookingPlan.CourseBookingWithExceptions.Fg' => [ + 'ffffffff', + 'Die Textfarbe im Belegungsplan für veranstaltungsbezogene Buchungen mit Ausfallterminen.' + ], + 'Resources.BookingPlan.CourseBookingWithExceptions.Bg' => [ + 'a480b9ff', + 'Die Farbe im Belegungsplan für veranstaltungsbezogene Buchungen mit Ausfallterminen.' + ], + 'Resources.BookingPlan.SimpleBookingWithExceptions.Fg' => [ + 'ffffffff', + 'Die Textfarbe im Belegungsplan für einfache Buchungen mit Wiederholungen, bei denen es Ausfalltermine gibt.' + ], + 'Resources.BookingPlan.SimpleBookingWithExceptions.Bg' => [ + '70c3bfff', + 'Die Farbe im Belegungsplan für einfache Buchungen mit Wiederholungen, bei denen es Ausfalltermine gibt.' + ], + 'Resources.BookingPlan.Reservation.Fg' => [ + 'ffffffff', + 'Die Textfarbe im Belegungsplan für Reservierungen.' + ], + 'Resources.BookingPlan.Reservation.Bg' => [ + '6ead10ff', + 'Die Farbe im Belegungsplan für Reservierungen.' + ], + 'Resources.BookingPlan.Lock.Fg' => [ + 'ffffffff', + 'Die Textfarbe im Belegungsplan für Sperrbuchungen.' + ], + 'Resources.BookingPlan.Lock.Bg' => [ + 'd60000ff', + 'Die Farbe im Belegungsplan für Sperrbuchungen.' + ], + 'Resources.BookingPlan.PlannedBooking.Fg' => [ + '000000ff', + 'Die Textfarbe im Belegungsplan für geplante Buchungen.' + ], + 'Resources.BookingPlan.PlannedBooking.Bg' => [ + 'f26e00ff', + 'Die Farbe im Belegungsplan für geplante Buchungen.' + ], + 'Resources.BookingPlan.PreparationTime.Fg' => [ + '000000ff', + 'Die Textfarbe im Belegungsplan für Rüstzeiten.' + ], + 'Resources.BookingPlan.PreparationTime.Bg' => [ + 'cf81b0ff', + 'Die Farbe im Belegungsplan für Rüstzeiten.' + ], + 'Resources.BookingPlan.Request.Fg' => [ + '000000ff', + 'Die Textfarbe im Belegungsplan für Anfragen.' + ], + 'Resources.BookingPlan.Request.Bg' => [ + 'ffbd33ff', + 'Die Farbe im Belegungsplan für Anfragen.' + ] + ]; + + $colour_stmt = $db->prepare( + 'INSERT IGNORE INTO colour_values + (colour_id, value, description, mkdate, chdate) + VALUES + (:colour_id, :value, :description, UNIX_TIMESTAMP(), UNIX_TIMESTAMP() + );' + ); + + foreach ($colours as $colour_id => $data) { + $colour_stmt->execute( + [ + 'colour_id' => $colour_id, + 'value' => $data[0], + 'description' => $data[1] + ] + ); + } + + //alter tables: + + $db->exec("RENAME TABLE resources_objects TO resources;"); + $db->exec("ALTER TABLE resources + CHANGE COLUMN resource_id id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + CHANGE COLUMN parent_id parent_id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + CHANGE COLUMN category_id category_id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + CHANGE COLUMN description description text NULL DEFAULT NULL, + CHANGE COLUMN requestable requestable TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN sort_position TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, + DROP COLUMN institut_id, + DROP COLUMN lockable, + DROP COLUMN multiple_assign, + DROP COLUMN root_id"); + + $db->exec("RENAME TABLE resources_categories TO resource_categories;"); + $db->exec("ALTER TABLE resource_categories + CHANGE COLUMN category_id id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + CHANGE COLUMN `system` `system` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN class_name VARCHAR(60) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'Resource', + ADD COLUMN mkdate INT(11) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN chdate INT(11) UNSIGNED NOT NULL DEFAULT 0, + DROP COLUMN is_room"); + + $db->exec("RENAME TABLE resources_categories_properties + TO resource_category_properties;"); + $db->exec("ALTER TABLE resource_category_properties + CHANGE COLUMN requestable requestable TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, + CHANGE COLUMN protected protected TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, + CHANGE COLUMN `system` `system` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN form_text text NULL, + ADD COLUMN mkdate INT(11) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN chdate INT(11) UNSIGNED NOT NULL DEFAULT 0"); + + $db->exec("RENAME TABLE resources_properties + TO resource_property_definitions;"); + $db->exec("ALTER TABLE resource_property_definitions + CHANGE COLUMN type type SET ( + 'bool', 'text', 'num', 'select', 'user', 'institute', + 'position', 'fileref', 'url', 'resource_ref_list' + ) CHARACTER SET latin1 COLLATE latin1_bin, + CHANGE COLUMN description description text NULL DEFAULT NULL, + CHANGE COLUMN `system` `system` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN display_name VARCHAR(512) NOT NULL DEFAULT '', + ADD COLUMN searchable TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN range_search TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN write_permission_level VARCHAR(16) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'admin-global', + ADD COLUMN property_group_id INT(11) NULL, + ADD COLUMN property_group_pos TINYINT(4) NULL, + ADD COLUMN mkdate INT(11) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN chdate INT(11) UNSIGNED NOT NULL DEFAULT 0"); + + //Set the display_name to the property name as default: + + $db->exec( + "UPDATE resource_property_definitions + SET display_name = name;" + ); + + $db->exec("RENAME TABLE resources_objects_properties + TO resource_properties;"); + $db->exec("ALTER TABLE resource_properties + ADD COLUMN mkdate INT(11) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN chdate INT(11) UNSIGNED NOT NULL DEFAULT 0"); + + $db->exec("RENAME TABLE resources_requests + TO resource_requests;"); + $db->exec("ALTER TABLE resource_requests + CHANGE COLUMN request_id id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + CHANGE COLUMN seminar_id course_id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + CHANGE COLUMN closed closed TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, + CHANGE COLUMN category_id category_id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NULL DEFAULT '', + ADD COLUMN begin INT(11) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN end INT(11) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN preparation_time INT(4) NOT NULL DEFAULT 0, + ADD COLUMN marked TINYINT(1) UNSIGNED NOT NULL DEFAULT 0" + ); + + $db->exec("RENAME TABLE resources_requests_properties + TO resource_request_properties;"); + + $db->exec("RENAME TABLE resources_assign TO resource_bookings;"); + $db->exec("ALTER TABLE resource_bookings + CHANGE COLUMN assign_id id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + CHANGE COLUMN user_free_name description TEXT NULL, + CHANGE COLUMN assign_user_id range_id CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + CHANGE COLUMN repeat_interval old_rep_interval INT(2) NULL DEFAULT NULL, + CHANGE COLUMN repeat_month_of_year old_rep_month_of_year INT(2) NULL DEFAULT NULL, + CHANGE COLUMN repeat_day_of_month old_rep_day_of_month INT(2) NULL DEFAULT NULL, + CHANGE COLUMN repeat_week_of_month old_rep_week_of_month INT(2) NULL DEFAULT NULL, + CHANGE COLUMN repeat_day_of_week old_rep_day_of_week INT(2) NULL DEFAULT NULL, + CHANGE COLUMN comment_internal internal_comment text NULL DEFAULT NULL, + ADD COLUMN preparation_time INT(4) NOT NULL DEFAULT 0, + ADD COLUMN booking_type TINYINT(2) NOT NULL DEFAULT 0, + ADD COLUMN booking_user_id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + ADD COLUMN repetition_interval VARCHAR(24) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '';" + ); + $db->exec("ALTER TABLE `resource_bookings` DROP INDEX `resource_id`, ADD INDEX `resource_id` (`resource_id`, `booking_type`)"); + + $db->exec("RENAME TABLE resources_locks + TO global_resource_locks;"); + $db->exec("ALTER TABLE global_resource_locks + CHANGE COLUMN lock_begin begin INT(11) UNSIGNED NOT NULL DEFAULT 0, + CHANGE COLUMN lock_end end INT(11) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN user_id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + ADD COLUMN mkdate INT(11) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN chdate INT(11) UNSIGNED NOT NULL DEFAULT 0"); + + $db->exec("RENAME TABLE resources_user_resources TO resource_permissions;"); + $db->exec("ALTER TABLE resource_permissions + ADD COLUMN mkdate INT(11) UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN chdate INT(11) UNSIGNED NOT NULL DEFAULT 0"); + $db->exec("ALTER TABLE resource_permissions ADD INDEX (resource_id)"); + $db->exec("UPDATE resource_permissions SET resource_id = 'global' + WHERE resource_id = 'all';"); + + $db->exec("DROP TABLE resources_temporary_events;"); + $db->exec( + "CREATE TABLE `resource_booking_intervals` ( + `interval_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `resource_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + `booking_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + `begin` int(20) NOT NULL DEFAULT 0, + `end` int(20) NOT NULL DEFAULT 0, + `mkdate` int(11) unsigned NOT NULL DEFAULT 0, + `chdate` int(11) unsigned NOT NULL DEFAULT 0, + `takes_place` tinyint(1) unsigned NOT NULL DEFAULT 1, + PRIMARY KEY (`interval_id`), + INDEX `resource_id` (`resource_id`,`takes_place`,`end`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"); + + //Delete old tables: + + $db->exec("DROP TABLE resources_requests_user_status;"); + + //Add other nice things and modify stuff: + + $new_log_action_stmt = $db->prepare( + "INSERT IGNORE INTO log_actions (action_id, name, description) + VALUES + (:action_id, :name, :description);" + ); + + $action_id = md5(uniqid('RES_PERM_CHANGE')); + $new_log_action_stmt->execute( + [ + 'action_id' => $action_id, + 'name' => 'RES_PERM_CHANGE', + 'description' => 'Änderung der Berechtigungsstufe an einer Ressource.' + ] + ); + + //Set parent_id = '' where parent_id = 'root' or '0' in + //resources so that root resources always + //have an empty parent_id field: + $db->exec("UPDATE resources + SET parent_id = '' + WHERE parent_id = 'root' OR parent_id = '0';" + ); + + //We can delete resources now that are not needed anymore: + $this->deleteResources(); + + //First we remove those properties that are not needed anymore: + $this->deleteExistingProperties($db); + + //Now we have to migrate existing properties + //that are system properties with a different name: + $this->migrateExistingProperties($db); + //And we have to merge some properties, too: + $this->mergeProperties($db); + + //Now we convert all boolean property values to '0' and '1' + //instead of '' and 'on': + $db->exec( + "UPDATE resource_properties + INNER JOIN resource_property_definitions rpd + USING (property_id) + SET state = '1' + WHERE state = 'on' AND rpd.type = 'bool';" + ); + $db->exec( + "UPDATE resource_properties + INNER JOIN resource_property_definitions rpd + USING (property_id) + SET state = '0' + WHERE state = '' AND rpd.type = 'bool';" + ); + + //Repeat the same for resource request properties: + + $db->exec( + "UPDATE resource_request_properties + INNER JOIN resource_property_definitions rpd + USING (property_id) + SET state = '1' + WHERE state = 'on' AND rpd.type = 'bool';" + ); + $db->exec( + "UPDATE resource_request_properties + INNER JOIN resource_property_definitions rpd + USING (property_id) + SET state = '0' + WHERE state = '' AND rpd.type = 'bool';" + ); + + //rename and update resource categories: + + $db->exec("UPDATE resource_categories + SET class_name = 'Room', `system` = 1 + WHERE + name LIKE '%raum%' OR name LIKE '%Raum%' + OR name LIKE '%saal%' OR name LIKE '%Saal%';"); + + $db->exec("UPDATE resource_categories + SET class_name = 'Building', `system` = 1 + WHERE + name LIKE '%gebäude%' OR name LIKE '%Gebäude%';"); + + //add new resource categories and new properties: + + $add_category_statement = $db->prepare( + "INSERT INTO `resource_categories` + (`id`,`name`, `class_name`, `description`, `system`, `iconnr`) + VALUES + (:id, :name, :class_name, '', 1, 0);" + ); + + $location_cat_id = md5('StandortLocationResourceCategory100'); + + $building_cat_id_rows = $db->query("SELECT id + FROM resource_categories + WHERE name LIKE '%gebäude%';")->fetchAll(); + + $room_cat_id_rows = $db->query("SELECT id + FROM resource_categories + WHERE name LIKE '%raum%' OR name LIKE '%saal%';")->fetchAll(); + + $building_cat_ids = []; + foreach ($building_cat_id_rows as $row) { + $building_cat_ids[] = $row[0]; + } + + $room_cat_ids = []; + foreach ($room_cat_id_rows as $row) { + $room_cat_ids[] = $row[0]; + } + + $add_category_statement->execute( + [ + 'id' => $location_cat_id, + 'name' => 'Standort', + 'class_name' => 'Location' + ] + ); + + //Assign orphaned resources (resources without category-id): + $this->assignOrphanedResources($db); + $this->deleteUnfinishedResources($db); + + if (is_array($GLOBALS['RESOURCE_CATEGORY_CLASS_MAPPING'])) { + //Use the class mapping rules from that configuration setting: + $mapping_stmt = $db->prepare( + 'UPDATE resource_categories + SET class_name = :class_name + WHERE name = :name' + ); + foreach ($GLOBALS['RESOURCE_CATEGORY_CLASS_MAPPING'] as $name => $class_name) { + $mapping_stmt->execute( + [ + 'class_name' => $class_name, + 'name' => $name + ] + ); + } + } else { + //Migrate rooms, buildings and locations, based on best guess. + $this->migrateRooms($db); + $this->migrateBuildings($db); + $this->migrateLocations($db, $location_cat_id); + } + + if (count($GLOBALS['RESOURCE_CATEGORY_RENAME'])) { + $rename_stmt = $db->prepare( + 'UPDATE resource_categories SET name = :name + WHERE name = :old_name' + ); + + foreach ($GLOBALS['RESOURCE_CATEGORY_RENAME'] as $old_name => $name) { + $rename_stmt->execute( + [ + 'old_name' => $old_name, + 'name' => $name + ] + ); + } + } + + //Get all location categories. + //migrateLocations has set the Location class name + //for all location categories. + $location_cat_id_rows = $db->query( + "SELECT id + FROM resource_categories + WHERE class_name = 'Location';" + )->fetchAll(); + $location_cat_ids = []; + foreach ($location_cat_id_rows as $row) { + $location_cat_ids[] = $row[0]; + } + + //Add or create missing default properties: + + $property_exists_statement = $db->prepare( + "SELECT property_id + FROM resource_property_definitions + WHERE + name = :name AND type = :type;" + ); + + $add_property_statement = $db->prepare( + "INSERT INTO `resource_property_definitions` + (`property_id`, `name`, `type`, `options`, `system`) + VALUES + (:id, :name, :type, '', 1);" + ); + + $property_link_exists_statement = $db->prepare( + "SELECT 1 + FROM resource_category_properties + WHERE + category_id = :category_id AND property_id = :property_id;" + ); + + $update_property_link_statement = $db->prepare( + "UPDATE resource_category_properties + SET `system` = 1 + WHERE + category_id = :category_id AND property_id = :property_id;" + ); + + $link_property_statement = $db->prepare( + "INSERT INTO resource_category_properties + (`category_id`, `property_id`, `system`) + VALUES + (:category_id, :property_id, 1);" + ); + + $all_mandatory_properties = [ + 'location' => [ + //location properties: + 'geo_coordinates' => 'position' + ], + + 'building' => [ + //building properties: + 'geo_coordinates' => 'position', + 'number' => 'text', + 'address' => 'text' + ], + + 'room' => [ + //room properties: + 'booking_plan_is_public' => 'bool', + 'room_type' => 'select', + 'seats' => 'num', + 'responsible_person' => 'user' + ] + ]; + + foreach ($all_mandatory_properties as $area_name => $area) { + $area_ids = ['']; + if ($area_name == 'location') { + $area_ids = $location_cat_ids; + } elseif ($area_name == 'building') { + $area_ids = $building_cat_ids; + } elseif ($area_name == 'room') { + $area_ids = $room_cat_ids; + } + foreach ($area as $name => $type) { + //Check if the property exists. If so, link it only. + //Otherwise create it. + $property_exists_statement->execute( + [ + 'name' => $name, + 'type' => $type + ] + ); + + $property_id = $property_exists_statement->fetchColumn(0); + if (!$property_id) { + //property doesn't exist: create it + $property_id = md5($name . $type . rand()); + $add_property_statement->execute( + [ + 'id' => $property_id, + 'name' => $name, + 'type' => $type + ] + ); + } + + foreach ($area_ids as $area_id) { + //Check if the property is linked to the category. + //If so, we must make sure the system attribute + //is set. Otherwise we must create the link. + $property_link_exists_statement->execute( + [ + 'category_id' => $area_id, + 'property_id' => $property_id + ] + ); + + $link_exists = $property_link_exists_statement->fetchColumn(0); + + if ($link_exists) { + $update_property_link_statement->execute( + [ + 'category_id' => $area_id, + 'property_id' => $property_id + ] + ); + } else { + $link_property_statement->execute( + [ + 'category_id' => $area_id, + 'property_id' => $property_id + ] + ); + } + //make plans visible + if ($area_name === 'room' && $name === 'booking_plan_is_public') { + $db->execute("INSERT IGNORE INTO `resource_properties` (`resource_id`, `property_id`, `state`, `mkdate`, `chdate`) SELECT `id` , ?, '1', UNIX_TIMESTAMP(), UNIX_TIMESTAMP() FROM `resources` WHERE `category_id` = ?", [$property_id, $area_id]); + } + } + } + } + + //At this point, we can create the property groups (if any): + $this->createPropertyGroups($db); + + //Migrate resource booking interval data: + $this->migrateBookingRepeatIntervals($db); + + $this->migrateOwner(); + //Migrate course permissions on resources: + $this->migrateCourseBoundPermissions($db); + $this->deleteOldPermissions($db); + + //In case no location resource is in the database, + //one such resource will be created along with the + //corresponding category. + $this->createMissingLocation($db); + + //Create entries in resource_booking_intervals for each booking: + $this->fillResourceBookingIntervals($db); + + SimpleORMap::expireTableScheme(); + } + + + public function down() + { + //I see nothing! I hear nothing! Nothing!! + } +} diff --git a/db/migrations/1.272_resize_auth_user_md5_email_field.php b/db/migrations/1.272_resize_auth_user_md5_email_field.php new file mode 100644 index 0000000..c0efe40 --- /dev/null +++ b/db/migrations/1.272_resize_auth_user_md5_email_field.php @@ -0,0 +1,25 @@ +exec( + "ALTER TABLE `auth_user_md5` + CHANGE COLUMN `Email` `Email` VARCHAR(256) NULL DEFAULT NULL" + ); + } + + + public function down() + { + DBManager::get()->exec( + "ALTER TABLE `auth_user_md5` + CHANGE COLUMN `Email` `Email` VARCHAR(256) NULL DEFAULT NULL" + ); + } +} diff --git a/db/migrations/1.272_step_00338_instituteplaning.php b/db/migrations/1.272_step_00338_instituteplaning.php deleted file mode 100644 index b19709a..0000000 --- a/db/migrations/1.272_step_00338_instituteplaning.php +++ /dev/null @@ -1,79 +0,0 @@ -exec($query); - - $query = "INSERT INTO `datafields` ( - `datafield_id`, `name`, `object_type`, `edit_perms`, `view_perms`, - `priority`, `type`, `typeparam`, `is_required`, `is_userfilter`, - `description`, `system` - ) VALUES ( - '69f6485f3c937766866a03d9d642ecbb', 'zugeordnete Planungsspalte', 'sem', 'admin', 'root', - 0, 'textline', '', 0, 0, - 'Gibt die zugeordnete Planungsspalte im Veranstaltungsplan an.', 0 - ), ( - '41cda2be71fe9efd6e28b853fc0681f3', 'zugeordnete Planungsfarbe', 'sem', 'admin', 'root', - 0, 'textline', '', 0, 0, - 'Zugewiesene Farbe im Veranstaltungsplaner', 0 - )"; - DBManager::get()->exec($query); - - $query = "INSERT IGNORE INTO config ( - `field`, `value`, `type`, `range`, `section`, - `mkdate`, `chdate`, `description` - ) VALUES ( - :field, :value, 'string', 'global', 'modules', - UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), :description - )"; - $statement = DBManager::get()->prepare($query); - - $statement->execute([ - ':field' => 'INSTITUTE_COURSE_PLAN_START_HOUR', - ':value' => '08:00', - 'description' => 'The start hour for the default view of the institute course plan.', - ]); - - $statement->execute([ - ':field' => 'INSTITUTE_COURSE_PLAN_END_HOUR', - ':value' => '20:00', - 'description' => 'The end hour for the default view of the institute course plan.', - ]); - } - - public function down() - { - DBManager::get()->exec('DROP TABLE `institute_plan_columns`'); - - $query = "DELETE FROM `datafields` - WHERE `datafield_id` IN ( - '69f6485f3c937766866a03d9d642ecbb', - '41cda2be71fe9efd6e28b853fc0681f3' - )"; - DBManager::get()->exec($query); - - $query = "DELETE `config`, `config_values` - FROM `config` - LEFT JOIN `config_values` USING (`field`) - WHERE `field` IN ( - 'INSTITUTE_COURSE_PLAN_START_HOUR', - 'INSTITUTE_COURSE_PLAN_END_HOUR' - )"; - DBManager::get()->exec($query); - } -} diff --git a/db/migrations/1.273_config_for_download_counter_display.php b/db/migrations/1.273_config_for_download_counter_display.php deleted file mode 100644 index f4df9e5..0000000 --- a/db/migrations/1.273_config_for_download_counter_display.php +++ /dev/null @@ -1,31 +0,0 @@ -exec($query); - } - - public function down() - { - $query = "DELETE `config`, `config_values` - FROM `config` - LEFT JOIN `config_values` USING (`field`) - WHERE `field` = 'DISPLAY_DOWNLOAD_COUNTER'"; - DBManager::get()->exec($query); - } -} diff --git a/db/migrations/1.273_unrestricted_userdomains.php b/db/migrations/1.273_unrestricted_userdomains.php new file mode 100644 index 0000000..eb95503 --- /dev/null +++ b/db/migrations/1.273_unrestricted_userdomains.php @@ -0,0 +1,21 @@ +exec($query); + } + + public function down() + { + $query = "ALTER TABLE `userdomains` + DROP COLUMN `restricted_access`, + DROP COLUMN `mkdate`, + DROP COLUMN `chdate`"; + DBManager::get()->exec($query); + } +} diff --git a/db/migrations/1.274_add_enable_free_access_for_courses_only.php b/db/migrations/1.274_add_enable_free_access_for_courses_only.php deleted file mode 100644 index 63fe853..0000000 --- a/db/migrations/1.274_add_enable_free_access_for_courses_only.php +++ /dev/null @@ -1,34 +0,0 @@ -exec( - "UPDATE `config` SET `type` = 'string', - `description` = '1: courses and institutes with public access are visible without login. courses_only: only courses with public access are visible without login. 0: disable this feature.' - WHERE `field` = 'ENABLE_FREE_ACCESS'" - ); - } - - - public function down() - { - $db = DBManager::get(); - - $db->exec( - "UPDATE `config` SET `type` = 'boolean', - `description` = 'If true, courses with public access are available' - WHERE `field` = 'ENABLE_FREE_ACCESS'" - ); - } - - - public function description() - { - return 'Adds the "courses_only" option for ENABLE_FREE_ACCESS in the configuration.'; - } -} diff --git a/db/migrations/1.274_blubbermessenger.php b/db/migrations/1.274_blubbermessenger.php new file mode 100644 index 0000000..24acb5c --- /dev/null +++ b/db/migrations/1.274_blubbermessenger.php @@ -0,0 +1,171 @@ +exec($query); + + $query = "INSERT INTO `blubber_threads` ( + `thread_id`, `context_type`, `context_id`, `user_id`, `external_contact`, `content`, `display_class`, `visible_in_stream`, `chdate`, `mkdate` + ) + SELECT `topic_id`, `context_type`, `Seminar_id`, `user_id`, `external_contact`, `description`, NULL, '1', `chdate`, `mkdate` + FROM blubber + WHERE parent_id = '0'"; + DBManager::get()->exec($query); + + $query = "CREATE TABLE `blubber_comments` ( + `comment_id` CHAR(32) COLLATE latin1_bin NOT NULL, + `thread_id` CHAR(32) COLLATE latin1_bin NOT NULL, + `user_id` CHAR(32) COLLATE latin1_bin NOT NULL DEFAULT '', + `external_contact` TINYINT(1) NOT NULL DEFAULT 0, + `content` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `network` VARCHAR(64) COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, + `chdate` INT(11) DEFAULT NULL, + `mkdate` INT(11) DEFAULT NULL, + PRIMARY KEY (`comment_id`), + KEY `thread_id` (`thread_id`), + KEY `user_id` (`user_id`) + ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC"; + DBManager::get()->exec($query); + + $query = "INSERT INTO `blubber_comments` ( + `comment_id`, `thread_id`, `user_id`, `external_contact`, `content`, `chdate`, `mkdate` + ) + SELECT `topic_id`, `root_id`, `user_id`, `external_contact`, `description`, `chdate`, `mkdate` + FROM blubber + WHERE parent_id != '0'"; + DBManager::get()->exec($query); + + DBManager::get()->exec("DROP TABLE `blubber`"); + + $query = "ALTER TABLE blubber_mentions + CHANGE `topic_id` `thread_id` CHAR(32) COLLATE latin1_bin NOT NULL"; + DBManager::get()->exec($query); + + DBManager::get()->exec("DROP TABLE blubber_reshares"); + DBManager::get()->exec("DROP TABLE blubber_streams"); + + // Create config entries + $query = "INSERT INTO `config` + SET `field` = :field, + `value` = :value, + `type` = :type, + `range` = :range, + `section` = :section, + `mkdate` = UNIX_TIMESTAMP(), + `chdate` = UNIX_TIMESTAMP(), + `description` = :description"; + $config_statement = DBManager::get()->prepare($query); + + $config_statement->execute([ + ':field' => 'BLUBBER_GLOBAL_MESSENGER_ACTIVATE', + ':value' => '1', + ':type' => 'boolean', + ':range' => 'global', + ':section' => 'global', + ':description' => 'Ist Blubber unter Community global aktiv? Blubber in Veranstaltungen wird über das Plugin Blubber aktiviert oder deaktiviert.', + ]); + $config_statement->execute([ + ':field' => 'BLUBBER_DEFAULT_THREAD', + ':value' => '1', + ':type' => 'string', + ':range' => 'user', + ':section' => '', + ':description' => 'Dieses ist bei dem globalen Blubber-Messenger der vorausgewählte Blubber.', + ]); + + // activate routes: + require_once $GLOBALS['STUDIP_BASE_PATH'] . '/app/routes/Blubber.php'; + RESTAPI\ConsumerPermissions::get()->activateRouteMap(new RESTAPI\Routes\Blubber()); + + // Blubber to be the primary messenger in courses + $query = "SELECT pluginid + FROM plugins + WHERE pluginclassname = 'CoreForum'"; + $forum_id = DBManager::get()->fetchColumn($query); + + $query = "SELECT id, modules + FROM sem_classes + WHERE forum = 'CoreForum'"; + $select_sem_class = DBManager::get()->query($query); + $sem_classes = $select_sem_class->fetchAll(PDO::FETCH_ASSOC); + + $query = "INSERT IGNORE INTO plugins_activated (pluginid, range_type, range_id, state) + SELECT :forum_id, 'sem', seminare.Seminar_id, '1' + FROM seminare + JOIN sem_types ON seminare.status = sem_types.id + JOIN sem_classes ON sem_types.class = sem_classes.id + JOIN forum_entries ON forum_entries.seminar_id = seminare.Seminar_id + WHERE sem_classes.id = :sem_class + GROUP BY seminare.Seminar_id + HAVING COUNT(*) > 1"; + $activate_forum_for_courses = DBManager::get()->prepare($query); + + $query = "UPDATE sem_classes + SET modules = :modules, + forum = 'Blubber' + WHERE id = :id"; + $update = DBManager::get()->prepare($query); + + foreach ($sem_classes as $sem_class) { + $modules = json_decode($sem_class['modules'], true); + $forum_was_activated = $modules['CoreForum']['activated']; + + $modules['CoreForum']['activated'] = 0; + $modules['Blubber']['activated'] = 1; + + $update->execute([ + 'id' => $sem_class['id'], + 'modules' => json_encode($modules), + ]); + if ($forum_was_activated) { + // activate old forum in old courses that have more than one posting: + $activate_forum_for_courses->execute([ + 'forum_id' => $forum_id, + 'sem_class' => $sem_class['id'], + ]); + } + } + + // delete old blubber-stream avatars + $blubberstreams_folder = "{$GLOBALS['DYNAMIC_CONTENT_PATH']}/blubberstream"; + foreach (glob("{$blubberstreams_folder}/*") as $file) { + @unlink($blubberstreams_folder . "/" . $file); + } + @rmdir($blubberstreams_folder); + + DBManager::get()->exec(" + DELETE FROM activities + WHERE object_type = 'blubber' + "); + } + + public function down() + { + DBManager::get()->exec("DROP TABLE `blubber_comments`, `blubber_threads`"); + } +} diff --git a/db/migrations/1.275_additional_mvv_tables.php b/db/migrations/1.275_additional_mvv_tables.php deleted file mode 100644 index 4175a58..0000000 --- a/db/migrations/1.275_additional_mvv_tables.php +++ /dev/null @@ -1,295 +0,0 @@ -exec("CREATE TABLE IF NOT EXISTS `mvv_files` ( - `mvvfile_id` varchar(32) COLLATE latin1_bin NOT NULL, - `year` int(10) DEFAULT NULL, - `type` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `category` text COLLATE utf8mb4_unicode_ci, - `tags` text COLLATE utf8mb4_unicode_ci, - `extern_visible` tinyint(1) DEFAULT NULL, - `author_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, - `editor_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, - `mkdate` int(11) NOT NULL, - `chdate` int(11) NOT NULL, - PRIMARY KEY (`mvvfile_id`) - ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); - - $db->exec("CREATE TABLE IF NOT EXISTS `mvv_files_filerefs` ( - `mvvfile_id` varchar(32) COLLATE latin1_bin NOT NULL, - `file_language` varchar(32) COLLATE latin1_bin NOT NULL, - `name` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL, - `fileref_id` varchar(32) COLLATE latin1_bin NOT NULL, - `author_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, - `editor_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, - `mkdate` int(11) NOT NULL, - `chdate` int(11) NOT NULL, - PRIMARY KEY (`mvvfile_id`,`file_language`) - ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); - - $db->exec("CREATE TABLE IF NOT EXISTS `mvv_files_ranges` ( - `mvvfile_id` VARCHAR(32) COLLATE latin1_bin NOT NULL , - `range_id` VARCHAR(32) COLLATE latin1_bin NOT NULL , - `range_type` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NULL DEFAULT NULL , - `position` INT(10) NULL DEFAULT NULL , - `author_id` VARCHAR(32) COLLATE latin1_bin NULL DEFAULT NULL , - `editor_id` VARCHAR(32) COLLATE latin1_bin NULL DEFAULT NULL , - `mkdate` INT(11) NOT NULL , - `chdate` INT(11) NOT NULL , - PRIMARY KEY (`mvvfile_id`, `range_id`)) - ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); - - $db->exec("CREATE TABLE IF NOT EXISTS `mvv_contacts` ( - `contact_id` varchar(32) COLLATE latin1_bin NOT NULL, - `contact_status` enum('intern','extern','institution') COLLATE latin1_bin NOT NULL, - `alt_mail` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `author_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, - `editor_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, - `mkdate` int(11) NOT NULL, - `chdate` int(11) NOT NULL, - PRIMARY KEY (`contact_id`), - KEY `contact_status` (`contact_status`)) - ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); - - $db->exec("CREATE TABLE IF NOT EXISTS `mvv_extern_contacts` ( - `extern_contact_id` VARCHAR(32) COLLATE latin1_bin NOT NULL, - `name` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `vorname` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `homepage` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `mail` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `tel` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `author_id` VARCHAR(32) COLLATE latin1_bin DEFAULT NULL, - `editor_id` VARCHAR(32) COLLATE latin1_bin DEFAULT NULL, - `mkdate` INT(11) NOT NULL, - `chdate` INT(11) NOT NULL, - PRIMARY KEY (`extern_contact_id`)) - ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); - - $db->exec("CREATE TABLE IF NOT EXISTS `mvv_contacts_ranges` ( - `contact_range_id` varchar(32) COLLATE latin1_bin NOT NULL, - `contact_id` varchar(32) COLLATE latin1_bin NOT NULL, - `range_id` varchar(32) COLLATE latin1_bin NOT NULL, - `range_type` enum('Modul','Studiengang','StudiengangTeil') COLLATE latin1_bin NOT NULL, - `type` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, - `category` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, - `position` int(10) DEFAULT NULL, - `author_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, - `editor_id` varchar(32) COLLATE latin1_bin DEFAULT NULL, - `mkdate` int(11) NOT NULL, - `chdate` int(11) NOT NULL, - PRIMARY KEY (`contact_range_id`), - KEY `range_id` (`range_id`), - KEY `range_type` (`range_type`), - KEY `type` (`type`), - KEY `category_range` (`category`,`range_id`), - KEY `contact_id` (`contact_id`,`range_id`,`category`) USING BTREE - ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); - - - //Merge old mvv_dokument - foreach ($db->query("SELECT * FROM `mvv_dokument`") as $old_doc) { - $fileref_id = md5('FileRef'. $old_doc['dokument_id']); - $folder_id = md5('Folder'. $old_doc['dokument_id']); - $file_id = md5('File'. $old_doc['dokument_id']); - $mvvfile_id = md5('MvvFile'. $old_doc['dokument_id']); - $db->execute("INSERT IGNORE INTO `mvv_files` (`mvvfile_id`, `year`, `type`, `category`, `tags`, `extern_visible`, `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, NULL, NULL, NULL, NULL , 1, ?, ?, ?, ?)", - [$mvvfile_id, $old_doc['author_id'], $old_doc['editor_id'], $old_doc['mkdate'], $old_doc['chdate']]); - $db->execute("INSERT IGNORE INTO `mvv_files_filerefs` (`mvvfile_id`, `file_language`, `name`, `fileref_id`, `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", - [$mvvfile_id, 'DE', $old_doc['linktext'], $fileref_id, $old_doc['author_id'], $old_doc['editor_id'], $old_doc['mkdate'], $old_doc['chdate']]); - $db->execute("INSERT IGNORE INTO `file_urls` (`file_id`, `url`, `access_type`) VALUES (?, ?, 'proxy')", [$file_id, $old_doc['url']]); - $db->execute("INSERT IGNORE INTO `folders` (`id`, `user_id`, `parent_id`, `range_id`, `range_type`, `folder_type`, `name`, `data_content`, `description`, `mkdate`, `chdate`) - VALUES (?, ?, NULL, ?, ?, 'MVVFolder', ?, NULL, ?, ?, ?)", - [$folder_id, $old_doc['author_id'], $mvvfile_id, 'mvv', $old_doc['name'], $old_doc['linktext'], $old_doc['mkdate'], $old_doc['chdate']]); - $db->execute("INSERT IGNORE INTO `file_refs` (`id`, `file_id`, `folder_id`, `description`, `user_id`, `name`, `mkdate`, `chdate`) - VALUES (?, ?, ?, ?, ?, ?, ?, ?)", - [$fileref_id, $file_id, $folder_id, $old_doc['beschreibung'], $old_doc['author_id'], $old_doc['name'], $old_doc['mkdate'], $old_doc['chdate']]); - } - - //Merge old mvv_dokument_zuord - foreach ($db->query("SELECT * FROM `mvv_dokument_zuord`") as $old_docrange) { - $mvvfile_id = md5('MvvFile'. $old_docrange['dokument_id']); - $db->execute("INSERT IGNORE INTO `mvv_files_ranges` (`mvvfile_id`, `range_id`, `range_type`, `position`, `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", - [$mvvfile_id, $old_docrange['range_id'], $old_docrange['object_type'], $old_docrange['position'], $old_docrange['author_id'], $old_docrange['editor_id'], $old_docrange['mkdate'], $old_docrange['chdate']]); - } - $db->exec('DROP TABLE `mvv_dokument`'); - $db->exec('DROP TABLE `mvv_dokument_zuord`'); - - //Merge old mvv_modul_user - foreach ($db->query("SELECT * FROM `mvv_modul_user`") as $old_modul_user) { - $contact_range_id = md5('MvvContactRange' . $old_modul_user['user_id'] . $old_modul_user['modul_id']); - $db->execute("INSERT IGNORE INTO `mvv_contacts` (`contact_id`, `contact_status`, `alt_mail`, - `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, ?, ?, ?, ?, ? ,?)", - [ - $old_modul_user['user_id'], 'intern', '', $old_modul_user['author_id'], - $old_modul_user['editor_id'], $old_modul_user['mkdate'], $old_modul_user['chdate'] - ]); - $db->execute("INSERT IGNORE INTO `mvv_contacts_ranges` (`contact_range_id`, `range_id`, `contact_id`, `range_type`, `type`, `category`, `position`, `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", - [$contact_range_id, $old_modul_user['modul_id'], $old_modul_user['user_id'], 'Modul', '', - $old_modul_user['gruppe'], $old_modul_user['position'], $old_modul_user['author_id'], - $old_modul_user['editor_id'], $old_modul_user['mkdate'], $old_modul_user['chdate']]); - } - $db->exec('DROP TABLE `mvv_modul_user`'); - - // Merge old Fachberater - foreach ($db->query("SELECT * FROM `mvv_fachberater`") as $old_fachberater) { - $contact_range_id = md5('MvvContactRange' . $old_fachberater['user_id'] . $old_fachberater['stgteil_id']); - $db->execute("INSERT IGNORE INTO `mvv_contacts` (`contact_id`, `contact_status`, `alt_mail`, - `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, ?, ?, ?, ?, ? ,?)", - [ - $old_fachberater['user_id'], 'intern', '', $old_fachberater['author_id'], - $old_fachberater['editor_id'], $old_fachberater['mkdate'], $old_fachberater['chdate'] - ]); - $db->execute("INSERT IGNORE INTO `mvv_contacts_ranges` (`contact_range_id`, `range_id`, `contact_id`, `range_type`, `type`, `category`, `position`, `author_id`, `editor_id`, `mkdate`, `chdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", - [$contact_range_id, $old_fachberater['stgteil_id'], $old_fachberater['user_id'], 'StudiengangTeil', '', - 'fachberater', $old_fachberater['position'], $old_fachberater['author_id'], - $old_fachberater['editor_id'], $old_fachberater['mkdate'], $old_fachberater['chdate']]); - } - $db->exec('DROP TABLE `mvv_fachberater`'); - - // datafields for study courses - $db->exec("ALTER TABLE `datafields` - CHANGE `object_type` `object_type` - ENUM('sem','inst','user','userinstrole','usersemdata','roleinstdata', - 'moduldeskriptor','modulteildeskriptor','studycourse') NULL DEFAULT NULL"); - - // switch to enable/disable studycourse info page - $db->exec( - "INSERT INTO `config` (`field`, `value`, `type`, `range`, - `section`, `mkdate`, `chdate`, `description`) - VALUES - ('ENABLE_STUDYCOURSE_INFO_PAGE', '0', 'boolean', 'global', - 'global', UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), - 'Shows an icon to open a dialog with studycourse informations in module search if true.')" - ); - - // new fields for study courses - $db->exec("ALTER TABLE `mvv_studiengang` - ADD `enroll` VARCHAR(50) NULL DEFAULT NULL AFTER `schlagworte`"); - $db->exec("ALTER TABLE `mvv_studiengang` - ADD `abschlussgrad` VARCHAR(32) NULL DEFAULT NULL AFTER `schlagworte`"); - $db->exec("ALTER TABLE `mvv_studiengang` - ADD `studienplaetze` INT UNSIGNED NULL DEFAULT NULL AFTER `schlagworte`"); - $db->exec("ALTER TABLE `mvv_studiengang` - ADD `studienzeit` TINYINT UNSIGNED NULL DEFAULT NULL AFTER `schlagworte`"); - - // postgraduate study courses (Aufbaustudiengänge) - $db->exec("CREATE TABLE `mvv_aufbaustudiengang` ( - `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, - `grund_stg_id` CHAR(32) COLLATE latin1_bin NOT NULL, - `aufbau_stg_id` CHAR(32) COLLATE latin1_bin NOT NULL, - `typ` VARCHAR(32) COLLATE latin1_bin NOT NULL, - `kommentar` TEXT NULL, - `author_id` CHAR(32) COLLATE latin1_bin NOT NULL, - `editor_id` CHAR(32) COLLATE latin1_bin NOT NULL, - `mkdate` INT(11) NOT NULL, - `chdate` INT(11) NOT NULL, - PRIMARY KEY (`id`), - UNIQUE KEY `grund_stg_id` (`grund_stg_id`,`aufbau_stg_id`) - ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); - - // types of study courses - $db->exec("CREATE TABLE `mvv_studycourse_type` ( - `studiengang_id` CHAR(32) COLLATE latin1_bin NOT NULL, - `type` VARCHAR(32) COLLATE latin1_bin NOT NULL, - `author_id` CHAR(32) COLLATE latin1_bin NULL, - `editor_id` CHAR(32) COLLATE latin1_bin NULL, - `mkdate` INT(11) NOT NULL, - `chdate` INT(11) NOT NULL, - PRIMARY KEY (`studiengang_id`, `type`) - ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); - - // assigned languages to study course - $db->execute("CREATE TABLE `mvv_studycourse_language` ( - `studiengang_id` char(32) COLLATE latin1_bin NOT NULL, - `lang` varchar(32) COLLATE latin1_bin NOT NULL, - `position` int(11) NOT NULL DEFAULT '9999', - `author_id` char(32) COLLATE latin1_bin DEFAULT NULL, - `editor_id` char(32) COLLATE latin1_bin DEFAULT NULL, - `mkdate` int(11) NOT NULL, - `chdate` int(11) NOT NULL, - PRIMARY KEY (`studiengang_id`, `lang`) - ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC"); - - // add index to speed up filters - $db->execute("ALTER TABLE `mvv_modul_inst` ADD INDEX (`institut_id`)"); - } - - public function down() - { - DBManager::get()->exec("DROP TABLE `mvv_files`, `mvv_files_filerefs`, `mvv_files_ranges`, `mvv_contacts`, `mvv_extern_contacts`"); - DBManager::get()->exec("DROP TABLE `mvv_aufbaustudiengang`, `mvv_studycourse_type`, `mvv_studycourse_language`"); - DBManager::get()->exec("ALTER TABLE `mvv_studiengang` - DROP `studienzeit`, - DROP `studienplaetze`, - DROP `abschlussgrad`, - DROP `enroll`;"); - DBManager::get()->exec("ALTER TABLE `datafields` - CHANGE `object_type` `object_type` - ENUM('sem','inst','user','userinstrole','usersemdata','roleinstdata', - 'moduldeskriptor','modulteildeskriptor') NULL DEFAULT NULL"); - DBManager::get()->exec( - "DELETE FROM config WHERE field = 'ENABLE_STUDYCOURSE_INFO_PAGE'" - ); - DBManager::get()->exec( - "CREATE TABLE `mvv_fachberater` ( - `stgteil_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `user_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `position` int(11) NOT NULL, - `author_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `editor_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `mkdate` bigint(20) NOT NULL, - `chdate` bigint(20) NOT NULL, - PRIMARY KEY (`stgteil_id`,`user_id`) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC"); - DBManager::get()->exec( - "CREATE TABLE `mvv_modul_user` ( - `modul_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `user_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `gruppe` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `position` int(11) NOT NULL DEFAULT '9999', - `author_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `editor_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `mkdate` bigint(20) NOT NULL, - `chdate` bigint(20) NOT NULL, - PRIMARY KEY (`modul_id`,`user_id`,`gruppe`) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;"); - DBManager::get()->exec( - "CREATE TABLE `mvv_dokument` ( - `dokument_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `url` tinytext COLLATE utf8mb4_unicode_ci NOT NULL, - `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `linktext` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `beschreibung` text COLLATE utf8mb4_unicode_ci, - `author_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `editor_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `mkdate` bigint(20) NOT NULL, - `chdate` bigint(20) NOT NULL, - PRIMARY KEY (`dokument_id`) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC"); - DBManager::get()->exec( - "CREATE TABLE `mvv_dokument_zuord` ( - `dokument_zuord_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `dokument_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `range_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `object_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, - `position` int(3) NOT NULL DEFAULT '999', - `kommentar` tinytext COLLATE utf8mb4_unicode_ci, - `author_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `editor_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `mkdate` bigint(20) NOT NULL, - `chdate` bigint(20) NOT NULL, - PRIMARY KEY (`dokument_zuord_id`), - UNIQUE KEY `dokument_id` (`dokument_id`,`range_id`,`object_type`) USING BTREE, - KEY `range_id_object_type` (`range_id`,`object_type`) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC"); - } -} diff --git a/db/migrations/1.275_blubbermessenger_keys.php b/db/migrations/1.275_blubbermessenger_keys.php new file mode 100644 index 0000000..c79e979 --- /dev/null +++ b/db/migrations/1.275_blubbermessenger_keys.php @@ -0,0 +1,22 @@ +exec($query); + } + + public function down() + { + $query = "ALTER TABLE `blubber_mentions` + DROP COLUMN `mention_id`"; + DBManager::get()->exec($query); + } +} diff --git a/db/migrations/1.276_blubbermessenger_search.php b/db/migrations/1.276_blubbermessenger_search.php new file mode 100644 index 0000000..81c78ff --- /dev/null +++ b/db/migrations/1.276_blubbermessenger_search.php @@ -0,0 +1,101 @@ +prepare(" + SELECT * + FROM config + WHERE field = 'GLOBALSEARCH_MODULES' + "); + $statement->execute(); + $config = $statement->fetch(PDO::FETCH_ASSOC); + $config['value'] = json_decode($config['value'], true); + $config['value']['GlobalSearchBlubber'] = [ + 'order' => 13, + 'active' => true, + 'fulltext' => true + ]; + + $statement = DBManager::get()->prepare(" + UPDATE config + SET `value` = :json + WHERE field = 'GLOBALSEARCH_MODULES' + "); + $statement->execute([ + 'json' => json_encode($config['value']) + ]); + + $statement = DBManager::get()->prepare(" + SELECT * + FROM config_values + WHERE field = 'GLOBALSEARCH_MODULES' + "); + $statement->execute(); + $config = $statement->fetch(PDO::FETCH_ASSOC); + if ($config) { + $config['value'] = json_decode($config['value'], true); + $config['value']['GlobalSearchBlubber'] = [ + 'order' => 13, + 'active' => true, + 'fulltext' => true + ]; + + $statement = DBManager::get()->prepare(" + UPDATE config_values + SET `value` = :json + WHERE field = 'GLOBALSEARCH_MODULES' + "); + $statement->execute([ + 'json' => json_encode($config['value']) + ]); + } + } + + public function down() + { + $statement = DBManager::get()->prepare(" + SELECT * + FROM config_values + WHERE field = 'GLOBALSEARCH_MODULES' + "); + $statement->execute(); + $config = $statement->fetch(PDO::FETCH_ASSOC); + if ($config) { + $config['value'] = json_decode($config['value'], true); + unset($config['value']['GlobalSearchBlubber']); + $statement = DBManager::get()->prepare(" + UPDATE config_values + SET `value` = :json + WHERE field = 'GLOBALSEARCH_MODULES' + "); + $statement->execute([ + 'json' => json_encode($config['value']) + ]); + } + + $statement = DBManager::get()->prepare(" + SELECT * + FROM config + WHERE field = 'GLOBALSEARCH_MODULES' + "); + $statement->execute(); + $config = $statement->fetch(PDO::FETCH_ASSOC); + $config['value'] = json_decode($config['value'], true); + unset($config['value']['GlobalSearchBlubber']); + $statement = DBManager::get()->prepare(" + UPDATE config + SET `value` = :json + WHERE field = 'GLOBALSEARCH_MODULES' + "); + $statement->execute([ + 'json' => json_encode($config['value']) + ]); + + } +} diff --git a/db/migrations/1.276_tic8458_add_upload_description.php b/db/migrations/1.276_tic8458_add_upload_description.php deleted file mode 100644 index 5b26b24..0000000 --- a/db/migrations/1.276_tic8458_add_upload_description.php +++ /dev/null @@ -1,36 +0,0 @@ -exec( - "INSERT INTO `config` - (`field`, `value`, `type`, `range`, `section`, `mkdate`, `chdate`, - `description`) - VALUES - ('ENABLE_DESCRIPTION_ENTRY_ON_UPLOAD', '1', 'boolean', 'global', 'files', UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), - 'Whether to allow adding a description directly after file upload (true) or not (false). Defaults to true.')" - ); - } - - - public function down() - { - $db = DBManager::get(); - - $db->exec( - "DELETE FROM `config` - WHERE `field` = 'ENABLE_DESCRIPTION_ENTRY_ON_UPLOAD'" - ); - } - - - public function description() - { - return 'Adds the configuration entry ENABLE_DESCRIPTION_ENTRY_ON_UPLOAD to make it possible to enter a file description directly after uploading the file(s).'; - } -} diff --git a/db/migrations/1.277_blubbermessenger_flat.php b/db/migrations/1.277_blubbermessenger_flat.php new file mode 100644 index 0000000..3b4dfeb --- /dev/null +++ b/db/migrations/1.277_blubbermessenger_flat.php @@ -0,0 +1,212 @@ +exec(" + DELETE FROM blubber_comments + WHERE mkdate < 1364601600 + "); //30th March 2013 + + DBManager::get()->exec(" + INSERT IGNORE INTO blubber_threads + SET thread_id = 'global', + context_type = 'public', + context_id = '', + user_id = '', + external_contact = '0', + `content` = NULL, + display_class = 'BlubberGlobalThread', + visible_in_stream = '1', + commentable = '1', + metadata = NULL, + chdate = UNIX_TIMESTAMP(), + mkdate = UNIX_TIMESTAMP() + "); + DBManager::get()->exec(" + UPDATE blubber_comments, blubber_threads + SET blubber_comments.thread_id = 'global' + WHERE blubber_comments.thread_id = blubber_threads.thread_id + AND blubber_threads.context_type = 'public' + "); + DBManager::get()->exec(" + INSERT IGNORE INTO blubber_comments (comment_id, thread_id, user_id, external_contact, `content`, network, chdate, mkdate) + SELECT thread_id, 'global', user_id, external_contact, `content`, null, chdate, mkdate + FROM blubber_threads + WHERE context_type = 'public' + AND thread_id != 'global' + "); + DBManager::get()->exec(" + DELETE FROM blubber_threads + WHERE context_type = 'public' + AND thread_id != 'global' + "); + + $select_threads = DBManager::get()->prepare(" + SELECT * + FROM blubber_threads + WHERE context_type = 'course' + AND `content` IS NOT NULL AND `content` != '' + AND display_class IS NULL + "); + $select_threads->execute(); + $insert_comments = DBManager::get()->prepare(" + UPDATE blubber_comments + SET thread_id = :thread_id + WHERE thread_id = :old_thread + "); + $insert_comment = DBManager::get()->prepare(" + INSERT INTO blubber_comments + SET thread_id = :thread_id, + comment_id = :comment_id, + user_id = :user_id, + external_contact = :external_contact, + `content` = :content, + network = NULL, + chdate = :chdate, + mkdate = :mkdate + "); + $delete_thread = DBManager::get()->prepare(" + DELETE FROM blubber_threads + WHERE thread_id = ? + "); + $select_course_main_thread = DBManager::get()->prepare(" + SELECT * + FROM blubber_threads + WHERE (content IS NULL OR content = '') + AND thread_id != :main_thread_id + AND context_id = :course_id + AND context_type = 'course' + AND display_class IS NULL + "); + while ($row = $select_threads->fetch(PDO::FETCH_ASSOC)) { + $course_thread_id = $this->getCourseThreadId($row['context_id']); + + //Alle anderen mit !content löschen + $select_course_main_thread->execute([ + 'main_thread_id' => $course_thread_id, + 'course_id' => $row['context_id'] + ]); + foreach ($select_course_main_thread->fetchAll(PDO::FETCH_ASSOC) as $row2) { + $insert_comments->execute([ + 'thread_id' => $course_thread_id, + 'old_thread' => $row2['thread_id'] + ]); + $delete_thread->execute([ + $row2['thread_id'] + ]); + } + + if ($row['thread_id'] !== $course_thread_id) { + //Alle Kommentare aus diesem Thread in den Main-Thread verschieben: + $insert_comments->execute([ + 'thread_id' => $course_thread_id, + 'old_thread' => $row['thread_id'] + ]); + + if ($row['content']) { + //Und noch einen Startkommentar in den Haupthread packen, wenn der zu löschende Thread noch einen Hauptinhalt hatte: + $insert_comment->execute([ + 'comment_id' => $row['thread_id'], + 'thread_id' => $course_thread_id, + 'user_id' => $row['user_id'], + 'external_contact' => $row['external_contact'], + 'content' => $row['content'], + 'chdate' => $row['chdate'], + 'mkdate' => $row['mkdate'] + ]); + } + + $delete_thread->execute([ + $row['thread_id'] + ]); + } + } + + $select_private_threads = DBManager::get()->prepare(" + SELECT * + FROM blubber_threads + WHERE context_type = 'private' + AND `content` IS NOT NULL AND content != '' + "); + $select_private_threads->execute(); + $clean_thread = DBManager::get()->prepare(" + UPDATE blubber_threads + SET `content` = NULL + WHERE thread_id = ? + "); + while ($row3 = $select_private_threads->fetch(PDO::FETCH_ASSOC)) { + $insert_comment->execute([ + 'comment_id' => md5($row3['thread_id']."_ersterkommentar"), + 'thread_id' => $row3['thread_id'], + 'user_id' => $row3['user_id'], + 'external_contact' => $row3['external_contact'], + 'content' => $row3['content'], + 'chdate' => $row3['chdate'], + 'mkdate' => $row3['mkdate'] + ]); + $clean_thread->execute([$row3['thread_id']]); + } + + DBManager::get()->exec(" + CREATE TABLE IF NOT EXISTS `blubber_threads_unfollow` ( + `thread_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + `user_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `mkdate` int(11) DEFAULT NULL, + PRIMARY KEY (`thread_id`,`user_id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + "); + + // activate routes: + require_once $GLOBALS['STUDIP_BASE_PATH'] . '/app/routes/Blubber.php'; + RESTAPI\ConsumerPermissions::get()->activateRouteMap(new RESTAPI\Routes\Blubber()); + } + + protected function getCourseThreadId($course_id) + { + $select = DBManager::get()->prepare(" + SELECT * + FROM blubber_threads + WHERE context_type = 'course' + AND context_id = ? + AND display_class IS NULL + AND (`content` IS NULL OR `content` = '') + "); + $select->execute([$course_id]); + $thread_id = $select->fetch(PDO::FETCH_COLUMN, 0); + if (!$thread_id) { + $thread_id = md5(uniqid($course_id)); + $insert = DBManager::get()->prepare(" + INSERT IGNORE INTO blubber_threads + SET thread_id = :thread_id, + context_type = 'course', + context_id = :course_id, + user_id = '', + external_contact = '0', + `content` = NULL, + display_class = NULL, + visible_in_stream = '1', + commentable = '1', + chdate = UNIX_TIMESTAMP(), + mkdate = UNIX_TIMESTAMP() + "); + $insert->execute([ + 'thread_id' => $thread_id, + 'course_id' => $course_id + ]); + } + return $thread_id; + } + + public function down() + { + DBManager::exec(" + DROP TABLE `blubber_threads_unfollow`; + "); + } +} diff --git a/db/migrations/1.277_room_management_migration.php b/db/migrations/1.277_room_management_migration.php deleted file mode 100644 index 8fd748b..0000000 --- a/db/migrations/1.277_room_management_migration.php +++ /dev/null @@ -1,2390 +0,0 @@ - 'Building', - 'Hörsaal' => 'Room', - 'Übungsraum' => 'Room', - 'Gerät' => 'Resource', - ]; - $GLOBALS['RESOURCE_ADMINISTRATION_PERSON_URL'] = 'https://example.org/person/%s'; - $GLOBALS['RESOURCE_MIGRATION_RESOURCE_TREES_TO_BE_DELETED'] = []; - $GLOBALS['RESOURCE_MIGRATION_MIGRATE_COURSE_PERMISSIONS'] = true; - $GLOBALS['RESOURCE_PROPERTIES_TO_BE_DELETED'] = []; - $GLOBALS['RESOURCE_MIGRATION_SPECIALRESOURCESPLUGIN'] = false; - $GLOBALS['RESOURCE_PROPERTIES_TO_BE_MODIFIED'] = [ - 'Adresse' => [ - 'name' => 'address', - 'old_type' => 'text' - ], - 'Audio-Anlage' => [ - 'name' => 'has_loudspeakers', - 'old_type' => 'bool', - 'requestable' => true, - 'searchable' => true - ], - 'Beamer' => [ - 'name' => 'has_projector', - 'old_type' => 'bool', - 'requestable' => true, - 'searchable' => true - ], - 'behindertengerecht' => [ - 'name' => 'accessible', - 'old_type' => 'bool', - 'requestable' => true, - 'searchable' => true - ], - 'Dozentenrechner' => [ - 'name' => 'has_computer', - 'old_type' => 'bool', - 'requestable' => true, - 'searchable' => true - ], - 'Hersteller' => [ - 'name' => 'manufacturer', - 'old_type' => 'select', - 'requestable' => true, - 'searchable' => true - ], - 'Inventarnummer' => [ - 'name' => 'inventory_number', - 'old_type' => 'num', - 'requestable' => false, - 'searchable' => true - ], - 'Seriennummer' => [ - 'name' => 'serial_number', - 'old_type' => 'num', - 'requestable' => false, - 'searchable' => true - ], - 'Sitzplätze' => [ - 'name' => 'seats', - 'old_type' => 'num', - 'requestable' => true, - 'searchable' => true - ], - 'Tageslichtprojektor' => [ - 'name' => 'has_overhead_projector', - 'old_type' => 'bool', - 'requestable' => true, - 'searchable' => true - ], - 'Verdunklung' => [ - 'name' => 'is_dimmable', - 'old_type' => 'bool', - 'requestable' => true, - 'searchable' => true - ], - 'Raumverantwortung' => [ - 'name' => 'responsible_person', - 'display_name' => 'Raumverantwortung', - 'old_type' => 'text', - 'new_type' => 'user', - 'requestable' => false, - 'searchable' => false, - 'info_label' => true - ] - ]; - $GLOBALS['RESOURCE_PROPERTIES_TO_BE_MERGED'] = []; - } - - - //Assign orphaned resources (resources without category_id) - //to a new category: - public function assignOrphanedResources(PDO $db) - { - $orphaned_resources = $db->query( - "SELECT id FROM resources - WHERE (category_id IS NULL) OR (category_id = '') - OR category_id NOT IN (SELECT id from resource_categories);" - )->fetchAll(PDO::FETCH_COLUMN, 0); - - if ($orphaned_resources) { - //Create a resource category for those resources: - - $md5 = md5('OrphanedResourcesCategory' . rand()); - - $db->execute( - "INSERT INTO resource_categories - (id, name, class_name, description, mkdate, chdate) - VALUES - (?, 'Verwaiste Ressourcen', 'Resource', '', UNIX_TIMESTAMP(), UNIX_TIMESTAMP())", - [$md5] - ); - - //Now we assign all orphaned resources to that category: - $db->execute( - "UPDATE resources SET category_id = ? - WHERE (category_id IS NULL) OR (category_id = '')", - [$md5] - ); - } - } - - - public function deleteUnfinishedResources(PDO $db) - { - $db->exec( - "DELETE from resources - WHERE name = 'Neues Objekt';" - ); - } - - - public function migrateBookingRepeatIntervals(PDO $db) - { - //Buchungen unbekannter Räume entfernen - $db->exec("DELETE resource_bookings FROM resource_bookings LEFT JOIN resources ON resources.id=resource_id WHERE resources.id IS NULL"); - - //mehrtägige korrigieren - $db->exec("UPDATE `resource_bookings` SET end=repeat_end WHERE repeat_end > end AND IFNULL(old_rep_interval,0) = 0"); - - - //Get all resource_bookings rows that have repetition intervals set: - $booking_rows = $db->query( - "SELECT * - FROM resource_bookings - WHERE repeat_end > end AND IFNULL(old_rep_interval,0) > 0" - )->fetchAll(); - - $update_stmt = $db->prepare( - "UPDATE resource_bookings - SET repetition_interval = :repetition_interval - WHERE id = :id;" - ); - - foreach ($booking_rows as $row) - { - $date_interval = ''; - if ($row['old_rep_week_of_month']) { - $date_interval = 'P' . $row['old_rep_interval'] . 'M'; - } elseif ($row['old_rep_interval']) { - if ($row['old_rep_month_of_year']) { - $date_interval = 'P' . $row['old_rep_interval'] . 'Y'; - } elseif ($row['old_rep_day_of_month']) { - $date_interval = 'P' . $row['old_rep_interval'] . 'M'; - } elseif ($row['old_rep_day_of_week']) { - $date_interval = 'P' . ($row['old_rep_interval'] * 7) . 'D'; - } else { - $date_interval = 'P' . $row['old_rep_interval'] . 'D'; - } - } - - $update_stmt->execute( - [ - 'id' => $row['id'], - 'repetition_interval' => $date_interval - ] - ); - } - - //Delete the old columns: - $db->exec( - "ALTER TABLE resource_bookings - DROP COLUMN old_rep_interval, - DROP COLUMN old_rep_month_of_year, - DROP COLUMN old_rep_day_of_month, - DROP COLUMN old_rep_week_of_month, - DROP COLUMN old_rep_day_of_week;" - ); - - //set booking_user_id for all bookings made by 'autor' - $db->exec("UPDATE resource_bookings rb INNER JOIN auth_user_md5 aum ON range_id=user_id INNER JOIN resource_permissions rp ON rp.user_id=aum.user_id AND rb.resource_id = rp.resource_id SET booking_user_id = range_id WHERE booking_user_id='' AND rp.perms='autor'"); - } - - - public function migrateCourseBoundPermissions(PDO $db) - { - if ($GLOBALS['RESOURCE_MIGRATION_MIGRATE_COURSE_PERMISSIONS']) { - //Get all permissions where the range_id - //represents a course-ID or an institute-ID: - - $permissions = $db->query( - "SELECT user_id, resource_id, perms, 'course' AS 'range' - FROM resource_permissions - WHERE - user_id IN ( - SELECT seminar_id FROM seminare - ) - UNION - SELECT user_id, resource_id, perms, 'institute' AS 'range' - FROM resource_permissions - WHERE - user_id IN ( - SELECT Institut_id FROM Institute - )" - )->fetchAll(); - - $course_participant_stmt = $db->prepare( - "SELECT user_id - FROM seminar_user - WHERE - Seminar_id = :course_id" - ); - - $institute_participant_stmt = $db->prepare( - "SELECT user_id, inst_perms - FROM user_inst - WHERE - Institut_id = :institute_id" - ); - - $get_user_permission_stmt = $db->prepare( - "SELECT perms - FROM resource_permissions - WHERE user_id = :user_id AND resource_id = :resource_id" - ); - - $update_user_permission_stmt = $db->prepare( - "UPDATE resource_permissions - SET perms = :perms - WHERE user_id = :user_id AND resource_id = :resource_id" - ); - - $create_user_permission_stmt = $db->prepare( - "INSERT INTO resource_permissions - (user_id, resource_id, perms, mkdate, chdate) - VALUES - (:user_id, :resource_id, :perms, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());" - ); - $create_specialresources_entry = $db->prepare( - "INSERT INTO specialresourcesplugin_course_resources - (course_id, resource_id, mkdate, chdate) - VALUES - (:course_id, :resource_id, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());" - ); - - foreach ($permissions as $permission) { - //Get all participants of the course/institute: - $participants = []; - if ($permission['range'] == 'course') { - $course_participant_stmt->execute( - [ - 'course_id' => $permission['user_id'] - ] - ); - $participants = $course_participant_stmt->fetchAll(); - } elseif ($permission['range'] == 'institute') { - $institute_participant_stmt->execute( - [ - 'institute_id' => $permission['user_id'] - ] - ); - $participants = $institute_participant_stmt->fetchAll(); - } - - foreach ($participants as $participant) { - if (($permission['range'] == 'institute') && - !in_array($participant['inst_perms'], ['autor', 'tutor', 'dozent', 'admin'])) { - //The permission level in the institute is too low. - continue; - } - //Check if a permission exists for that participant: - $get_user_permission_stmt->execute( - [ - 'user_id' => $participant['user_id'], - 'resource_id' => $permission['resource_id'] - ] - ); - $existing_perm = $get_user_permission_stmt->fetch(PDO::FETCH_COLUMN); - - if ($existing_perm) { - if (!in_array($existing_perm, ['autor', 'tutor', 'admin'])) { - //Update the permission and set it to "autor": - $update_user_permission_stmt->execute( - [ - 'user_id' => $participant['user_id'], - 'resource_id' => $permission['resource_id'], - 'perms' => 'autor' - ] - ); - } - } else { - //Create a new permission: Give every participant - //'autor' permissions for the resource - //that was specified in the course permission: - $create_user_permission_stmt->execute( - [ - 'user_id' => $participant['user_id'], - 'resource_id' => $permission['resource_id'], - 'perms' => 'autor' - ] - ); - } - } - - if ($GLOBALS['RESOURCE_MIGRATION_SPECIALRESOURCESPLUGIN']) { - $create_specialresources_entry->execute( - [ - 'course_id' => $permission['user_id'], - 'resource_id' => $permission['resource_id'] - ] - ); - } - } - } - } - - public function migrateOwner() - { - $db = DBManager::get(); - $db->exec("INSERT IGNORE INTO resource_permissions - (user_id, resource_id, perms, mkdate, chdate) - SELECT r.owner_id, r.id, 'admin', r.mkdate, r.chdate - FROM resources r INNER JOIN resource_categories rc - ON r.category_id = rc.id AND class_name='Room' - WHERE EXISTS - (SELECT * FROM auth_user_md5 WHERE owner_id = user_id)"); - $responsible_property_id = - $db->fetchColumn("SELECT property_id FROM resource_property_definitions WHERE name = 'responsible_person'"); - if ($responsible_property_id) { - $db->exec("INSERT IGNORE INTO resource_properties - (resource_id, property_id,state,mkdate,chdate) - SELECT r.id, '$responsible_property_id', r.owner_id, r.mkdate, r.chdate - FROM resources r INNER JOIN resource_categories rc - ON r.category_id = rc.id AND class_name='Room' - WHERE EXISTS - (SELECT * FROM auth_user_md5 WHERE owner_id = user_id)"); - } - $db->exec("ALTER TABLE resources DROP COLUMN owner_id"); - } - - public function deleteOldPermissions(PDO $db) { - //Delete all permissions that aren't permissions of existing users: - $db->exec( - "DELETE FROM resource_permissions - WHERE user_id NOT IN ( - SELECT user_id FROM auth_user_md5 - );" - ); - } - - - public function deleteResources() - { - $resource_trees_to_be_deleted = - $GLOBALS['RESOURCE_MIGRATION_RESOURCE_TREES_TO_BE_DELETED']; - if (count($resource_trees_to_be_deleted)) { - foreach ($resource_trees_to_be_deleted as $resource_id) { - $resource = Resource::find($resource_id); - if ($resource instanceof Resource) { - $resource->delete(); - } - } - } - } - - - public function migrateLocations(PDO $db, $location_cat_id = null) - { - if (!$location_cat_id) { - //This should not happen! - throw new Exception('Internal error!'); - } - - //First we create one Location data set for orphaned buildings: - $orphan_location_stmt = $db->prepare( - "INSERT INTO resources - (id, category_id, name) - VALUES (:id, :category_id, :name);" - ); - - $orphan_location_id = md5('SonstigeGebäudeResourceLocation' . rand()); - - $orphan_location_stmt->execute( - [ - 'id' => $orphan_location_id, - 'category_id' => $location_cat_id, - 'name' => 'Sonstige Gebäude' - ] - ); - - //We must get all IDs of resources which are parents - //of building resources. Such resources and their categories - //are Locations. - //Buildings without a parent are attached to a new location - //which is built for this case. - - $building_resources = $db->query( - "SELECT resources.id as id, parent_id FROM resources - INNER JOIN resource_categories - ON resources.category_id = resource_categories.id - WHERE - resource_categories.class_name = 'Building';" - )->fetchAll(); - - //We have the ID and the parent-ID of all building resources. - //Now we get all categories of the building's parents - //and set their class_name to 'Location'. - //Furthermore we remove the parent's parents since Location - //resources must not have parent resources. - - $get_parent_stmt = $db->prepare( - "SELECT id, category_id FROM resources - WHERE id = :parent_id" - ); - - $update_location_category_stmt = $db->prepare( - "UPDATE resource_categories SET class_name = 'Location' - WHERE id = :category_id;" - ); - - $update_parent_stmt = $db->prepare( - "UPDATE resources SET parent_id = :parent_id - WHERE id = :id;" - ); - - $update_category_stmt = $db->prepare( - "UPDATE resources SET category_id = :category_id - WHERE id = :id;" - ); - - //Ok, now we can look for all buildings and give them a parent_id - //if they don't have one and we can remove their parent's parent_id, - //if they have one. - - foreach ($building_resources as $building) { - if ($building['parent_id']) { - //parent_id is set: get the parent: - $get_parent_stmt->execute( - ['parent_id' => $building['parent_id']] - ); - - $parents = $get_parent_stmt->fetchAll(); - - foreach ($parents as $parent) { - if ($parent['category_id']) { - //Update the parent's category since it is a location: - $update_location_category_stmt->execute( - [ - 'category_id' => $parent['category_id'] - ] - ); - } else { - //Parent does not belong to a category: - //set the category to our orphaned location category: - $update_category_stmt->execute( - [ - 'category_id' => $location_cat_id, - 'id' => $parent['id'] - ] - ); - } - - if ($parent['parent_id']) { - //If the parent's parent_id is set we must delete it: - $update_parent_stmt->execute( - [ - 'parent_id' => '', - 'id' => $parent['id'] - ] - ); - } - } - } else { - //The building has no parent_id: We must add it to our - //orphan building location: - $update_parent_stmt->execute( - [ - 'parent_id' => $orphan_location_id, - 'id' => $building['id'] - ] - ); - } - } - - //Now all buildings should be connected to locations and all locations - //should be without a parent. - } - - - public function migrateBuildings(PDO $db) - { - //We must set the class name to 'Building' for all - //resource categories whose name is like 'gebäude'. - - $db->exec( - "UPDATE resource_categories SET class_name = 'Building' - WHERE name LIKE '%gebäude%';" - ); - - } - - - public function migrateRooms(PDO $db) - { - //Set the category's class name to 'Room' for all categories - //which have a name similar to 'Raum' or 'Saal': - - $db->exec( - "UPDATE resource_categories - SET class_name = 'Room' - WHERE name LIKE '%raum%' OR name LIKE '%saal%';" - ); - - - } - - - public function deleteExistingProperties(PDO $db) - { - $properties_to_be_deleted = $GLOBALS['RESOURCE_PROPERTIES_TO_BE_DELETED']; - if (!count($properties_to_be_deleted)) { - //Nothing to do. - return; - } - - $delete_definition_stmt = $db->prepare( - 'DELETE FROM resource_property_definitions - WHERE property_id IN ( :property_ids )' - ); - $delete_resource_property_stmt = $db->prepare( - 'DELETE FROM resource_properties - WHERE property_id IN ( :property_ids )' - ); - $delete_resource_request_property_stmt = $db->prepare( - 'DELETE FROM resource_request_properties - WHERE property_id IN ( :property_ids )' - ); - - $delete_resource_request_property_stmt->execute( - ['property_ids' => $properties_to_be_deleted] - ); - $delete_resource_property_stmt->execute( - ['property_ids' => $properties_to_be_deleted] - ); - $delete_definition_stmt->execute( - ['property_ids' => $properties_to_be_deleted] - ); - } - - - public function migrateExistingProperties(PDO $db) - { - $this->write( - '# Migrating existing properties.' - ); - - //rename resource properties: - $get_property_stmt = $db->prepare( - "SELECT property_id - FROM resource_property_definitions - WHERE - name = :name AND type = :type;" - ); - - $update_property_stmt = $db->prepare( - "UPDATE resource_property_definitions - SET name = :name, - `system` = 1, - searchable = :searchable, - range_search = :range_search - WHERE property_id = :property_id;" - ); - - $insert_new_prop_stmt = $db->prepare( - "INSERT INTO resource_property_definitions - (`property_id`, `name`, `description`, `type`, `options`, - `info_label`, `display_name`, `searchable`, `range_search`, - `write_permission_level`, `system`, `mkdate`, `chdate`) - VALUES - (:property_id, :name, :description, :type, :options, - :info_label, :display_name, :searchable, :range_search, - :write_permission_level, :system, :mkdate, :chdate);" - ); - - $delete_duplicates_stmt = $db->prepare( - 'DELETE FROM resource_property_definitions - WHERE property_id IN ( :duplicate_ids );' - ); - - $requestable_attr_stmt = $db->prepare( - "UPDATE resource_category_properties - SET requestable = :requestable - WHERE property_id = :property_id;" - ); - - $properties_to_be_modified = $GLOBALS['RESOURCE_PROPERTIES_TO_BE_MODIFIED']; - foreach ($properties_to_be_modified as $old_name => $data) { - //Check if the old property exists: - $get_property_stmt->execute( - [ - 'name' => $old_name, - 'type' => $data['old_type'] - ] - ); - $old_property_ids = $get_property_stmt->fetchAll( - PDO::FETCH_COLUMN, - 0 - ); - if ($data['name'] === 'seats' && !count($old_property_ids)) { - $old_property_ids = $db->fetchFirst( - "SELECT `property_id` - FROM `resource_property_definitions` - WHERE `system` = 2"); - } - - //Check if the new property already exists: - $get_property_stmt->execute( - [ - 'name' => $data['name'], - 'type' => $data['old_type'] - ] - ); - - $final_property_id = null; - $duplicate_ids = []; - - $new_property_id = $get_property_stmt->fetchColumn(); - if ($new_property_id) { - //The new property already exists. We must set all values - //from the old property to the new property. - $final_property_id = $new_property_id; - $duplicate_ids = $old_property_ids; - } - - if (!$final_property_id) { - //The new property doesn't exist yet. We must either - //take one of the old properties and rename it - //or we must create a new property with that name. - if ((count($old_property_ids) > 0) && !$final_property_id) { - $this->write( - sprintf( - 'Property-IDs for "%1$s" = [%2$s]', - $old_name, - implode(', ', $old_property_ids) - ) - ); - if (count($old_property_ids) > 1) { - //Remove the first item of the $old_property_list array, - //store it in $final_property and use the other IDs - //in the duplicates array. - $final_property_id = array_shift($old_property_ids); - $duplicate_ids[] = $old_property_ids; - } else { - //The only property ID is the first item in the - //$old_property_ids array. - $final_property_id = $old_property_ids[0]; - } - $this->write( - sprintf( - 'Renaming property "%1$s" to "%2$s".', - $old_name, - $data['name'] - ) - ); - //Update the property: - $update_property_stmt->execute( - [ - 'name' => $data['name'], - 'searchable' => ($data['searchable'] ? '1' : '0'), - 'range_search' => ($data['range_search'] ? '1' : '0'), - 'property_id' => $final_property_id - ] - ); - } else { - $this->write( - sprintf( - 'No property with the name "%1$s" and the type "%2$s" could be found! Creating a new property.', - $old_name, - $data['old_type'] - ) - ); - //There is no old property defined. We must define one. - $now = time(); - $property_id = md5('RRV2NewProperty' . $data['name'] . rand()); - $this->write( - 'INFO: The property-ID of the new property "' - . $data['name'] . '" is: ' . $property_id - ); - $insert_new_prop_stmt->execute( - [ - 'property_id' => $property_id, - 'name' => $data['name'], - 'description' => ( - $data['description'] - ? $data['description'] - : '' - ), - 'system' => 1, - 'type' => $data['new_type'] ? $data['new_type'] : $data['old_type'], - 'options' => ($data['options'] ? $data['options'] : ''), - 'info_label' => $data['info_label'] ? '1' : '0', - 'display_name' => ( - $data['display_name'] - ? $data['display_name'] - : $data['name'] - ), - 'searchable' => ($data['searchable'] ? '1' : '0'), - 'range_search' => ($data['range_search'] ? '1' : '0'), - 'write_permission_level' => 'admin-global', - 'mkdate' => $now, - 'chdate' => $now - ] - ); - $final_property_id = $property_id; - } - } - - if ($duplicate_ids) { - $this->write( - sprintf( - 'Moving old property values to the new property "%s".', - $data['name'] - ) - ); - //Now we must "redirect" all links to the duplicates - //to the property that is left - //and then remove the duplicates. - - $tables = [ - 'resource_category_properties', - 'resource_properties', - 'resource_request_properties' - ]; - foreach ($tables as $table) { - $update_tables_stmt = $db->prepare( - sprintf( - "UPDATE IGNORE %s - SET property_id = :property_id - WHERE property_id IN ( :duplicate_ids );", - $table - ) - ); - $update_tables_stmt->execute( - [ - 'property_id' => $final_property_id, - 'duplicate_ids' => $duplicate_ids - ] - ); - } - - $this->write( - sprintf( - 'Deleting property values for property-IDs [%s].', - implode(', ', $duplicate_ids) - ) - ); - - //Now we delete the duplicates: - $delete_duplicates_stmt->execute( - [ - 'duplicate_ids' => $duplicate_ids - ] - ); - - //After that we have to delete those property relations - //whose property_id couldn't be set above - //to avoid duplicate primary keys. - $delete_relations_stmt = $db->prepare( - sprintf( - "DELETE FROM %s - WHERE property_id IN ( :duplicate_ids );", - $table - ) - ); - foreach ($tables as $table) { - $delete_relations_stmt->execute( - [ - 'duplicate_ids' => $duplicate_ids - ] - ); - } - } - - //Finally we make the property requestable, if configured: - $requestable_attr_stmt->execute( - [ - 'property_id' => $final_property_id, - 'requestable' => ($data['requestable'] ? '1' : '0') - ] - ); - } - - $this->write( - 'Finished migrating existing properties.' - ); - } - - - public function mergeProperties(PDO $db) - { - $this->write( - '# Merging properties.' - ); - - $get_prop_stmt = $db->prepare( - "SELECT property_id, name FROM resource_property_definitions - WHERE name = :name AND type = :type" - ); - - $insert_prop_stmt = $db->prepare( - "INSERT INTO resource_property_definitions - (`property_id`, `name`, `description`, `type`, `options`, - `info_label`, `display_name`, `searchable`, `range_search`, - `write_permission_level`, `system`, `mkdate`, `chdate`) - VALUES - (:property_id, :name, :description, :type, :options, - :info_label, :display_name, :searchable, :range_search, - :write_permission_level, :system, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());" - ); - - $get_property_values_stmt = $db->prepare( - "SELECT property_id, resource_id, state FROM resource_properties - WHERE property_id = :property_id;" - ); - - $insert_new_value_stmt = $db->prepare( - "INSERT IGNORE INTO resource_properties - (property_id, resource_id, state) - VALUES - (:property_id, :resource_id, :state);" - ); - - $get_category_id_stmt = $db->prepare( - "SELECT DISTINCT category_id FROM resource_category_properties - WHERE property_id IN ( :property_ids );" - ); - - $prop_cat_assign_stmt = $db->prepare( - "INSERT INTO resource_category_properties - (`category_id`, `property_id`, `requestable`, `protected`, - `system`, `form_text`, `mkdate`, `chdate`) - VALUES - (:category_id, :property_id, :requestable, :protected, - :system, :form_text, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()) - ON DUPLICATE KEY UPDATE - `requestable` = :requestable, - `protected` = :protected, - `system` = :system, - `form_text` = :form_text;" - ); - - $delete_old_prop_stmt = $db->prepare( - "DELETE FROM resource_property_definitions - WHERE property_id IN ( :property_ids );" - ); - - $delete_resource_request_property_values_stmt = $db->prepare( - "DELETE FROM resource_request_properties - WHERE property_id IN ( :property_ids );" - ); - - $delete_resource_property_values_stmt = $db->prepare( - "DELETE FROM resource_properties - WHERE property_id IN ( :property_ids );" - ); - - $properties_to_be_merged = $GLOBALS['RESOURCE_PROPERTIES_TO_BE_MERGED']; - foreach ($properties_to_be_merged as $merged_prop) { - $get_prop_stmt->execute( - [ - 'name' => $merged_prop['name'], - 'type' => $merged_prop['type'] - ] - ); - - $merged_property_id = $get_prop_stmt->fetchColumn(); - - if (!$merged_property_id) { - $this->write( - sprintf( - 'Creating new property from the type "%1$s", named "%2$s"!', - $merged_prop['type'], - $merged_prop['name'] - ) - ); - - //Create a new property: - $merged_property_id = md5( - 'RRV2NewMergedProperty' - . $merged_prop['name'] . rand() - ); - - $this->write( - 'INFO: The property-ID of the new property "' - . $merged_prop['name'] . '" is: ' . $merged_property_id - ); - $insert_prop_stmt->execute( - [ - 'property_id' => $merged_property_id, - 'name' => $merged_prop['name'], - 'description' => ( - $merged_prop['description'] - ? $merged_prop['description'] - : '' - ), - 'type' => $merged_prop['type'], - 'options' => ( - $merged_prop['options'] - ? $merged_prop['options'] - : '' - ), - 'info_label' => 0, - 'display_name' => ( - $merged_prop['display_name'] - ? $merged_prop['display_name'] - : $merged_prop['name'] - ), - 'searchable' => ($merged_prop['searchable'] ? '1' : '0'), - 'range_search' => ($merged_prop['range_search'] ? '1' : '0'), - 'write_permission_level' => 'admin-global', - 'system' => 1 - ] - ); - } - - //Get the old properties and collect their values: - $old_values = []; - $old_property_ids = []; - foreach ($merged_prop['sources'] as $source_prop) { - //Get the property whose values shall be merged: - $get_prop_stmt->execute( - [ - 'name' => $source_prop['name'], - 'type' => $source_prop['type'] - ] - ); - $old_prop = $get_prop_stmt->fetch(); - if (!$old_prop) { - $this->write( - sprintf( - 'The old property with the name "%1$s" and the type "%2$s" doesn\'t exist!', - $source_prop['name'], - $source_prop['type'] - ) - ); - //The old property doesn't exist. - continue; - } - - $old_property_ids[] = $old_prop['property_id']; - - //Get all property values and convert them: - $get_property_values_stmt->execute( - [ - 'property_id' => $old_prop['property_id'] - ] - ); - - $all_values = $get_property_values_stmt->fetchAll(); - - foreach ($all_values as $value) { - if (!is_array($old_values[$value['resource_id']])) { - $old_values[$value['resource_id']] = []; - } - $old_values[$value['resource_id']][$source_prop['name']] = - $value['state']; - } - } - - //Merge the old property values for each resource: - foreach ($old_values as $resource_id => $o_values) { - $new_value = ''; - if ($merged_prop['value_conversion'] instanceof Closure) { - //Use a closure for the value conversion: - $new_value = $merged_prop['value_conversion']($o_values); - } else { - //Just append the old values: - foreach ($o_values as $old_value) { - $new_value .= $old_value; - } - } - - $this->write( - sprintf( - 'Merged property value(s) [%1$s] to new value "%2$s."', - implode(', ', $o_values), - $new_value - ) - ); - - //Insert the property value for the new property: - $insert_new_value_stmt->execute( - [ - 'property_id' => $merged_property_id, - 'resource_id' => $resource_id, - 'state' => $new_value - ] - ); - } - - //Assign the new property to all categories, - //resources and resource requests where the old properties - //are assigned to: - $get_category_id_stmt->execute( - ['property_ids' => $old_property_ids] - ); - $relevant_category_ids = $get_category_id_stmt->fetchAll( - PDO::FETCH_COLUMN, - 0 - ); - - if (!$relevant_category_ids) { - //The old properties aren't used anywhere. - //We can just delete them. - $delete_old_prop_stmt->execute( - ['property_ids' => $old_property_ids] - ); - return; - } - - foreach ($relevant_category_ids as $category_id) { - $prop_cat_assign_stmt->execute( - [ - 'category_id' => $category_id, - 'property_id' => $merged_property_id, - 'requestable' => $merged_prop['requestable'] ? '1' : '0', - 'protected' => '0', - 'system' => '1', - 'form_text' => '' - ] - ); - } - - $this->write('Deleting old merged properties!'); - - //We can delete the old properties here: - $delete_old_prop_stmt->execute( - [ - 'property_ids' => $old_property_ids - ] - ); - - //Delete entries from resource requests: - //Since the values have changed, the converted property values - //may be inadequate for the resource request and thereby - //the request property value for the property is deleted. - $delete_resource_request_property_values_stmt->execute( - [ - 'property_ids' => $old_property_ids - ] - ); - - //Delete all resource property values from the old property: - $delete_resource_property_values_stmt->execute( - [ - 'property_ids' => $old_property_ids - ] - ); - } - - $this->write( - 'Finished merging properties.' - ); - } - - - public function removeObsoleteConfigEntries(PDO $db) - { - $entries = [ - 'RESOURCES_ALLOW_CREATE_ROOMS', - 'RESOURCES_ALLOW_CREATE_TOP_LEVEL', - 'RESOURCES_ALLOW_DELETE_REQUESTS', - 'RESOURCES_ALLOW_REQUESTABLE_ROOM_REQUESTS', - 'RESOURCES_ALLOW_ROOM_REQUESTS_ALL_ROOMS', - 'RESOURCES_ENABLE_GROUPING', - 'RESOURCES_ENABLE_ORGA_CLASSIFY', - 'RESOURCES_ENABLE_SEM_SCHEDULE', - 'RESOURCES_ENABLE_VIRTUAL_ROOM_GROUPS', - 'RESOURCES_HIDE_PAST_SINGLE_DATES', - 'RESOURCES_INHERITANCE_PERMS', - 'RESOURCES_INHERITANCE_PERMS_ROOMS', - 'RESOURCES_LOCKING_ACTIVE', - 'RESOURCES_ROOM_REQUEST_DEFAULT_ACTION', - 'RESOURCES_SCHEDULE_EXPLAIN_USER_NAME', - 'RESOURCES_SEARCH_ONLY_REQUESTABLE_PROPERTY', - 'RESOURCES_SHOW_ROOM_NOT_BOOKED_HINT', - 'RESOURCES_ENABLE_ORGA_ADMIN_NOTICE' - ]; - - $stmt = $db->prepare( - 'DELETE FROM config WHERE field IN ( :entries );' - ); - - $stmt->execute(['entries' => $entries]); - } - - - public function createPropertyGroups(PDO $db) - { - $get_group_id_stmt = $db->prepare( - "SELECT id FROM resource_property_groups WHERE name = :name" - ); - $create_group_stmt = $db->prepare( - "INSERT INTO resource_property_groups - (`name`, `mkdate`, `chdate`) - VALUES - (:name, UNIX_TIMESTAMP(), UNIX_TIMESTAMP())" - ); - - $property_groups = []; - - if ($GLOBALS['RESOURCE_MIGRATION_PROPERTY_GROUP_PROPERTY_NAME_REGEX']) { - //A regular expression is set to split property names - //into a group part and a proprty name part. - - $group_name_regex = $GLOBALS['RESOURCE_MIGRATION_PROPERTY_GROUP_PROPERTY_NAME_REGEX']; - - $all_properties = $db->query( - "SELECT property_id, name FROM resource_property_definitions - ORDER BY name ASC" - )->fetchAll(); - - foreach ($all_properties as $property) { - $splitted_name = preg_split($group_name_regex, $property['name'], 2); - $group_name = trim($splitted_name[0]); - $new_property_name = trim($splitted_name[1]); - - if ($group_name && $new_property_name) { - //A group name could be extracted. - //Check if a group with that name already exists: - $get_group_id_stmt->execute( - [ - 'name' => $group_name - ] - ); - $group_id = $get_group_id_stmt->fetchColumn(); - if (!$group_id) { - //Create a new group: - $create_group_stmt->execute( - [ - 'name' => $group_name - ] - ); - //Get the ID of the group: - $get_group_id_stmt->execute(['name' => $group_name]); - $group_id = $get_group_id_stmt->fetchColumn(); - - if (!$group_id) { - //No such group. We can only move to the next property. - echo "no group-id!\n"; - continue; - } - } - - //Add the property to the list of properties - //to be added to the group. - if (!is_array($property_groups[$group_id])) { - $property_groups[$group_id] = []; - } - $property_groups[$group_id][] = [ - 'property_id' => $property['property_id'], - 'new_property_name' => $new_property_name - ]; - } - } - - $update_property_stmt = $db->prepare( - "UPDATE resource_property_definitions - SET property_group_id = :group_id - WHERE property_id = :property_id" - ); - - $update_property_with_name_stmt = $db->prepare( - "UPDATE resource_property_definitions - SET property_group_id = :group_id, - name = :new_name, - display_name = :new_name - WHERE property_id = :property_id" - ); - - foreach ($property_groups as $group_id => $property_list) { - foreach ($property_list as $property_data) { - if ($property_data['new_property_name']) { - $update_property_with_name_stmt->execute( - [ - 'group_id' => $group_id, - 'new_name' => $property_data['new_property_name'], - 'property_id' => $property_data['property_id'] - ] - ); - } else { - $update_property_stmt->execute( - [ - 'group_id' => $group_id, - 'property_id' => $property_data['property_id'] - ] - ); - } - } - } - } - - //At this point, some property groups may already have been created - //using the property group regex. But they can also be created - //by explicitly defining a name and the properties that are included - //in the group. - if (count($GLOBALS['RESOURCE_MIGRATION_NEW_PROPERTY_GROUP_LIST'])) { - $property_group_list = $GLOBALS['RESOURCE_MIGRATION_NEW_PROPERTY_GROUP_LIST']; - - $add_properties_stmt = $db->prepare( - "UPDATE resource_property_definitions - SET property_group_id = :group_id - WHERE name IN ( :names )" - ); - - foreach ($property_group_list as $group_name => $property_names) { - $get_group_id_stmt->execute(['name' => $group_name]); - $group_id = $get_group_id_stmt->fetchColumn(); - - if ($group_id) { - $add_properties_stmt->execute( - [ - 'group_id' => $group_id, - 'names' => $property_names - ] - ); - } else { - //Create a group with the specified group name first: - $create_group_stmt->execute(['name' => $group_name]); - - $get_group_id_stmt->execute(['name' => $group_name]); - $group_id = $get_group_id_stmt->fetchColumn(); - - if (!$group_id) { - //There is nothing we can do about it here. - continue; - } - - $add_properties_stmt->execute( - [ - 'group_id' => $group_id, - 'names' => $property_names - ] - ); - } - } - } - } - - - public function createMissingLocation(PDO $db) - { - //Check if there is a location category: - $location_category_id = $db->query( - "SELECT id FROM resource_categories - WHERE class_name = 'Location' - ORDER BY name LIMIT 1" - )->fetchAll(PDO::FETCH_COLUMN, 0); - - $location_resource_id = null; - if (!$location_category_id) { - //Create a location category: - $location_category_id = md5('LocationCategory_' . uniqid()); - - $create_category_stmt = $db->prepare( - "INSERT INTO resource_categories - (`id`, `name`, `system`, `class_name`, `mkdate`, `chdate`) - VALUES - (:id, :name, '1', 'Location', UNIX_TIMESTAMP(), UNIX_TIMESTAMP());" - ); - - $create_category_stmt->execute( - [ - 'id' => $location_category_id, - 'name' => _('Standort') - ] - ); - } - - //Check if all buildings have a location resource - //in their "parent chain": - $building_ids = []; - $get_buildings_stmt = $db->prepare( - "SELECT resources.id FROM resources - INNER JOIN resource_categories rc - ON resources.category_id = rc.id - WHERE rc.class_name = 'Building'" - ); - $get_buildings_stmt->execute(); - $building_ids = $get_buildings_stmt->fetchAll(PDO::FETCH_COLUMN, 0); - - $get_parent_stmt = $db->prepare( - "SELECT r1.parent_id as parent_id, rc.class_name as class_name - FROM resources r1 - LEFT JOIN resources r2 ON r2.id=r1.parent_id - INNER JOIN resource_categories rc ON r2.category_id = rc.id - WHERE r1.id = :resource_id" - ); - - $orphaned_top_level_resource_ids = []; - foreach ($building_ids as $building_id) { - //Traverse the resource tree upwards until - //a location resource is found or a resource - //with no parent is reached. - $building_location_id = null; - - $last_id = null; - $current_id = $building_id; - while($current_id && !$building_location_id) { - $get_parent_stmt->execute( - [ - 'resource_id' => $current_id - ] - ); - - $data = $get_parent_stmt->fetchOne(); - if ($data['class_name'] == 'Location') { - //We have found the location. - $building_location_id = $data['parent_id']; - $last_id = null; - } else { - //No location found. Go one layer up: - $last_id = $current_id; - $current_id = $data['parent_id']; - } - } - - //At this point, we have either found a location resource - //or a top level resource that is not a location but a - //parent resource of a building. - if (!$building_location_id && $last_id) { - $orphaned_top_level_resource_ids[] = $last_id; - } - } - - $location_id = $db->query( - "SELECT resources.id FROM resources - INNER JOIN resource_categories rc - ON resources.category_id = rc.id - WHERE rc.class_name = 'Location' - ORDER BY resources.name LIMIT 1" - )->fetchAll(PDO::FETCH_COLUMN, 0); - - if (!$location_id) { - //No location exists. In that case, we have to - //create a location resource. - - $location_name = Config::get()->UNI_NAME_CLEAN; - if (!$location_name) { - $location_name = _('Standort'); - } - - $create_location_stmt = $db->prepare( - "INSERT INTO resources - (`id`, `parent_id`, `category_id`, `name`, `requestable`, - `mkdate`, `chdate`) - VALUES - (:id, '', :category_id, :name, '0', UNIX_TIMESTAMP(), UNIX_TIMESTAMP());" - ); - - $location_id = md5('Location_' . $location_name . uniqid()); - $create_location_stmt->execute( - [ - 'id' => $location_id, - 'category_id' => $location_category_id, - 'name' => $location_name - ] - ); - } - - $assign_to_location_stmt = $db->prepare( - "UPDATE resources SET parent_id = :parent_id WHERE id = :id;" - ); - - foreach ($orphaned_top_level_resource_ids as $resource_id) { - $assign_to_location_stmt->execute( - [ - 'parent_id' => $location_id, - 'id' => $resource_id - ] - ); - } - } - - - public function fillResourceBookingIntervals(PDO $db) - { - $chunk_size = 100; - - //Delete everything from resource booking intervals first: - $db->exec('DELETE FROM resource_booking_intervals;'); - - $query = "SELECT COUNT(*) - FROM resource_bookings"; - $booking_count = $db->query($query)->fetchColumn(); - - for ($loop = 0; $loop < $booking_count; $loop += $chunk_size) { - $query = "SELECT id, resource_id, range_id, begin, end, booking_type - preparation_time, repeat_end, repeat_quantity, repetition_interval - FROM resource_bookings - LIMIT {$loop}, {$chunk_size}"; - $bookings = $db->query($query); - - $add_interval_stmt = $db->prepare( - "INSERT INTO resource_booking_intervals - (interval_id, booking_id, resource_id, begin, end, takes_place, - mkdate, chdate) - VALUES - (:interval_id, :booking_id, :resource_id, :begin, :end, 1, - UNIX_TIMESTAMP(), UNIX_TIMESTAMP());" - ); - - $bookings->setFetchMode(PDO::FETCH_ASSOC); - foreach ($bookings as $booking) { - //Calculate the time intervals the same way as in - //ResourceBooking::calculateTimeIntervals: - $add_interval = function ($begin, $end) use ($booking, $add_interval_stmt) { - $interval_id = md5( - 'ResourceBookingInterval' . - $booking['booking_id'] . - $booking['begin'] . - $booking['end'] . - uniqid() - ); - $add_interval_stmt->execute([ - ':interval_id' => $interval_id, - ':booking_id' => $booking['id'], - ':resource_id' => $booking['resource_id'], - ':begin' => $begin, - ':end' => $end, - ]); - }; - - $booking_begin = new DateTime(); - $booking_begin->setTimestamp($booking['begin']); - if ($booking['preparation_time']) { - $booking_begin->setTimestamp( - $booking['begin'] - $booking['preparation_time'] - ); - } - $booking_end = new DateTime(); - $booking_end->setTimestamp($booking['end']); - - //use begin and end to create the first interval: - $add_interval( - $booking_begin->getTimestamp(), - $booking_end->getTimestamp() - ); - - if (($booking['repeat_quantity'] > 0) || $booking['repeat_end']) { - //Repetition: we must check which repetition interval has been - //selected and then create entries for each repetition. - //Repetition starts with the begin date and ends with the - //"repeat_end" date. - - $repetition_end = new DateTime(); - $repetition_end->setTimestamp($booking['repeat_end']); - //The DateInterval constructor will throw an exception, - //if it cannot parse the string stored in $this->repetition_interval. - $repetition_interval = null; - if ($booking['repetition_interval']) { - try { - $repetition_interval = new DateInterval($booking['repetition_interval']); - } catch (Exception $e) { - //Invalid repetition interval string. - //Skip this booking since its repetition interval is invalid. - continue; - } - } - - if ($repetition_interval instanceof DateInterval) { - $duration = $booking_begin->diff($booking_end); - - //Check if end is later than begin to avoid - //infinite loops. - if ($repetition_end > $booking_begin) { - $current_begin = clone $booking_begin; - $current_begin->add($repetition_interval); - while ($current_begin < $repetition_end) { - $current_end = clone $current_begin; - $current_end->add($duration); - - $add_interval( - $current_begin->getTimestamp(), - $current_end->getTimestamp() - ); - - $current_begin->add($repetition_interval); - } - } - } - } - } - } - } - - - public function up() - { - //Load the special configuration first: - if (file_exists(__DIR__ . '/../../config/resource_migration.php')) { - require(__DIR__ . '/../../config/resource_migration.php'); - } else { - //The special configuration doesn't exist so that we have to load - //the default configuration for the demo data. - $this->loadDefaultConfiguration(); - } - $db = DBManager::get(); - - //add new configuration variables: - - $db->exec( - "INSERT IGNORE INTO config - ( - `field`, - `value`, - `type`, - `range`, - `section`, - `mkdate`, - `chdate`, - `description` - ) - VALUES - ( - 'RESOURCES_DIRECT_ROOM_REQUESTS_ONLY', - '0', - 'boolean', - 'global', - 'resources', - UNIX_TIMESTAMP(), - UNIX_TIMESTAMP(), - 'Restricts room requests so that only specific rooms can be requested.' - ), - ( - 'RESOURCES_MAP_SERVICE_URL', - 'https://www.openstreetmap.org/#map=19/LATITUDE/LONGITUDE', - 'string', - 'global', - 'resources', - UNIX_TIMESTAMP(), - UNIX_TIMESTAMP(), - 'The URL for a map service if you wish to use another service instead of OpenStreetMap. The default is: https://www.openstreetmap.org/#map=17/LATITUDE/LONGITUDE (LATITUDE and LONGITUDE are placeholders!)' - ), - ( - 'RESOURCES_MAX_PREPARATION_TIME', - '120', - 'integer', - 'global', - 'resources', - UNIX_TIMESTAMP(), - UNIX_TIMESTAMP(), - 'The maximum amount of time that can be used for preparation before the actual booking begins. The value represents minutes, not hours!' - ), - ( - 'RESOURCES_MIN_BOOKING_TIME', - '15', - 'integer', - 'global', - 'resources', - UNIX_TIMESTAMP(), - UNIX_TIMESTAMP(), - 'The minimum amount of minutes for the booking of a resource.' - ), - ( - 'RESOURCES_BOOKING_PLAN_START_HOUR', - '08:00', - 'string', - 'global', - 'resources', - UNIX_TIMESTAMP(), - UNIX_TIMESTAMP(), - 'The start hour for the default view of the booking plan.' - ), - ( - 'RESOURCES_BOOKING_PLAN_END_HOUR', - '20:00', - 'string', - 'global', - 'resources', - UNIX_TIMESTAMP(), - UNIX_TIMESTAMP(), - 'The start hour for the default view of the booking plan.' - ), - ( - 'RESOURCES_MIN_BOOKING_PERMS', - 'autor', - 'string', - 'global', - 'resources', - UNIX_TIMESTAMP(), - UNIX_TIMESTAMP(), - 'The minimum permission level for global booking rights on a resource.' - ), - ( - 'RESOURCES_MIN_REQUEST_PERMISSION', - '', - 'string', - 'global', - 'resources', - UNIX_TIMESTAMP(), - UNIX_TIMESTAMP(), - 'The minimum permission level for creating \"free\" requests that are not bound to a course.' - ), - ( - 'RESOURCES_DISPLAY_CURRENT_REQUESTS_IN_OVERVIEW', - '1', - 'boolean', - 'global', - 'resources', - UNIX_TIMESTAMP(), - UNIX_TIMESTAMP(), - 'Whether to display the list with current requests in the room management overview (true) or not (false).' - ), - ( - 'RESOURCES_SHOW_PUBLIC_ROOM_PLANS', - '0', - 'boolean', - 'global', - 'resources', - UNIX_TIMESTAMP(), - UNIX_TIMESTAMP(), - 'Whether to display the list of available public room plans.' - );" - ); - - //Convert configuration options that may already exist so that - //the format is identical: - - $db->exec( - "UPDATE config SET type = 'boolean', section = 'resources' - WHERE field = 'RESOURCES_DIRECT_ROOM_REQUESTS_ONLY';" - ); - - //Enable API routes (and the API itself). - //The new room and resource management system requires a few routes - //to be activated to work properly. - - $db->exec( - "UPDATE config SET value = '1' - WHERE field = 'API_ENABLED';" - ); - - $enable_api_route_stmt = $db->prepare( - "INSERT INTO api_consumer_permissions - (route_id, consumer_id, method, granted) - VALUES - (:route, 'global', :method, '1') - ON DUPLICATE KEY UPDATE - granted = '1';" - ); - $api_routes_to_enable = [ - '/user/:user_id' => 'get', - '/resources/booking_interval/:interval_id/toggle_takes_place' => 'post', - '/resources/booking/:booking_id/move' => 'post', - '/resources/booking/:booking_id/intervals' => 'get', - '/resources/resource/:resource_id/booking_plan' => 'get', - '/resources/resource/:resource_id/semester_plan' => 'get', - '/resources/request/:request_id/move' => 'post', - '/resources/request/:request_id/edit_reply_comment' => 'post', - '/resources/request/:request_id/toggle_marked' => 'post', - '/room_clipboard/:clipboard_id/booking_plan' => 'get', - '/room_clipboard/:clipboard_id/semester_plan' => 'get', - '/clipboard/:clipboard_id' => ['delete', 'put'], - '/clipboard/:clipboard_id/item' => 'post', - '/clipboard/:clipboard_id/item/:range_id' => 'delete', - '/clipboard/add' => 'post', - '/course/:course_id/members' => 'get', - '/semesters' => 'get' - ]; - - foreach ($api_routes_to_enable as $route => $methods) { - if (is_array($methods)) { - foreach ($methods as $method) { - $enable_api_route_stmt->execute( - [ - 'route' => md5($route), - 'method' => $method - ] - ); - } - } else { - $enable_api_route_stmt->execute( - [ - 'route' => md5($route), - 'method' => $methods - ] - ); - } - } - - - //add new tables: - - $db->exec( - "CREATE TABLE IF NOT EXISTS `resource_temporary_permissions` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `resource_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `user_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `begin` int(11) unsigned NOT NULL DEFAULT '0', - `end` int(11) unsigned NOT NULL DEFAULT '0', - `perms` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `mkdate` int(11) unsigned NOT NULL DEFAULT '0', - `chdate` int(11) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" - ); - - $db->exec( - "CREATE TABLE IF NOT EXISTS `resource_request_appointments` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `request_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `appointment_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `mkdate` int(11) unsigned NOT NULL DEFAULT '0', - `chdate` int(11) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" - ); - - $db->exec( - "CREATE TABLE IF NOT EXISTS `colour_values` ( - `colour_id` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `description` varchar(256) NOT NULL DEFAULT '', - `value` varchar(8) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'ffffffff', - `mkdate` int(11) unsigned NOT NULL DEFAULT '0', - `chdate` int(11) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (`colour_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" - ); - - $db->exec( - "CREATE TABLE IF NOT EXISTS `separable_rooms` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `building_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `name` varchar(256) NOT NULL DEFAULT '', - `mkdate` int(11) unsigned NOT NULL DEFAULT '0', - `chdate` int(11) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" - ); - - $db->exec( - "CREATE TABLE IF NOT EXISTS `separable_room_parts` ( - `separable_room_id` int(10) NOT NULL, - `room_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `mkdate` int(11) unsigned NOT NULL DEFAULT '0', - `chdate` int(11) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (`separable_room_id`,`room_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" - ); - - $db->exec( - "CREATE TABLE IF NOT EXISTS `clipboards` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `user_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - `name` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - `handler` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'Clipboard', - `allowed_item_class` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'StudipItem', - `mkdate` int(11) unsigned NOT NULL DEFAULT '0', - `chdate` int(11) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" - ); - - //The range_id in clipboard_items is extra large - //so that primary keys consisting of three other - //keys can also be used. - $db->exec( - "CREATE TABLE IF NOT EXISTS `clipboard_items` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `clipboard_id` int(11) NOT NULL, - `range_id` varchar(98) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `range_type` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'SimpleORMap', - `mkdate` int(11) unsigned NOT NULL DEFAULT '0', - `chdate` int(11) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" - ); - - //The property group table defines groups of resource properties - //that shall be displayed together. - $db->exec( - "CREATE TABLE IF NOT EXISTS `resource_property_groups` ( - `id` INT(11) NOT NULL AUTO_INCREMENT, - `name` VARCHAR(255) NOT NULL DEFAULT '', - `position` TINYINT(4) NOT NULL DEFAULT '0', - `mkdate` INT(11) NOT NULL DEFAULT '0', - `chdate` INT(11) NOT NULL DEFAULT '0', - PRIMARY KEY (`id`) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" - ); - - if ($GLOBALS['RESOURCE_MIGRATION_SPECIALRESOURCESPLUGIN']) { - $db->exec( - "CREATE TABLE IF NOT EXISTS specialresourcesplugin_course_resources( - course_id VARCHAR(32) NOT NULL, - resource_id VARCHAR(32) NOT NULL, - mkdate INT(10) NOT NULL DEFAULT '0', - chdate INT(10) NOT NULL DEFAULT '0', - PRIMARY KEY (course_id, resource_id) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC" - ); - - } - - //Add colors: - $colours = [ - 'Resources.BookingPlan.Booking.Fg' => [ - 'ffffffff', - 'Die Textfarbe im Belegungsplan für gewöhnliche Buchungen.' - ], - 'Resources.BookingPlan.Booking.Bg' => [ - '129c94ff', - 'Die Farbe im Belegungsplan für gewöhnliche Buchungen.' - ], - 'Resources.BookingPlan.CourseBooking.Fg' => [ - 'ffffffff', - 'Die Textfarbe im Belegungsplan für veranstaltungsbezogene Buchungen.' - ], - 'Resources.BookingPlan.CourseBooking.Bg' => [ - '682c8bff', - 'Die Farbe im Belegungsplan für veranstaltungsbezogene Buchungen.' - ], - 'Resources.BookingPlan.CourseBookingWithExceptions.Fg' => [ - 'ffffffff', - 'Die Textfarbe im Belegungsplan für veranstaltungsbezogene Buchungen mit Ausfallterminen.' - ], - 'Resources.BookingPlan.CourseBookingWithExceptions.Bg' => [ - 'a480b9ff', - 'Die Farbe im Belegungsplan für veranstaltungsbezogene Buchungen mit Ausfallterminen.' - ], - 'Resources.BookingPlan.SimpleBookingWithExceptions.Fg' => [ - 'ffffffff', - 'Die Textfarbe im Belegungsplan für einfache Buchungen mit Wiederholungen, bei denen es Ausfalltermine gibt.' - ], - 'Resources.BookingPlan.SimpleBookingWithExceptions.Bg' => [ - '70c3bfff', - 'Die Farbe im Belegungsplan für einfache Buchungen mit Wiederholungen, bei denen es Ausfalltermine gibt.' - ], - 'Resources.BookingPlan.Reservation.Fg' => [ - 'ffffffff', - 'Die Textfarbe im Belegungsplan für Reservierungen.' - ], - 'Resources.BookingPlan.Reservation.Bg' => [ - '6ead10ff', - 'Die Farbe im Belegungsplan für Reservierungen.' - ], - 'Resources.BookingPlan.Lock.Fg' => [ - 'ffffffff', - 'Die Textfarbe im Belegungsplan für Sperrbuchungen.' - ], - 'Resources.BookingPlan.Lock.Bg' => [ - 'd60000ff', - 'Die Farbe im Belegungsplan für Sperrbuchungen.' - ], - 'Resources.BookingPlan.PlannedBooking.Fg' => [ - '000000ff', - 'Die Textfarbe im Belegungsplan für geplante Buchungen.' - ], - 'Resources.BookingPlan.PlannedBooking.Bg' => [ - 'f26e00ff', - 'Die Farbe im Belegungsplan für geplante Buchungen.' - ], - 'Resources.BookingPlan.PreparationTime.Fg' => [ - '000000ff', - 'Die Textfarbe im Belegungsplan für Rüstzeiten.' - ], - 'Resources.BookingPlan.PreparationTime.Bg' => [ - 'cf81b0ff', - 'Die Farbe im Belegungsplan für Rüstzeiten.' - ], - 'Resources.BookingPlan.Request.Fg' => [ - '000000ff', - 'Die Textfarbe im Belegungsplan für Anfragen.' - ], - 'Resources.BookingPlan.Request.Bg' => [ - 'ffbd33ff', - 'Die Farbe im Belegungsplan für Anfragen.' - ] - ]; - - $colour_stmt = $db->prepare( - 'INSERT IGNORE INTO colour_values - (colour_id, value, description, mkdate, chdate) - VALUES - (:colour_id, :value, :description, UNIX_TIMESTAMP(), UNIX_TIMESTAMP() - );' - ); - - foreach ($colours as $colour_id => $data) { - $colour_stmt->execute( - [ - 'colour_id' => $colour_id, - 'value' => $data[0], - 'description' => $data[1] - ] - ); - } - - //alter tables: - - $db->exec("RENAME TABLE resources_objects TO resources;"); - $db->exec("ALTER TABLE resources - CHANGE COLUMN resource_id id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - CHANGE COLUMN parent_id parent_id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - CHANGE COLUMN category_id category_id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - CHANGE COLUMN description description text NULL DEFAULT NULL, - CHANGE COLUMN requestable requestable TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN sort_position TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, - DROP COLUMN institut_id, - DROP COLUMN lockable, - DROP COLUMN multiple_assign, - DROP COLUMN root_id"); - - $db->exec("RENAME TABLE resources_categories TO resource_categories;"); - $db->exec("ALTER TABLE resource_categories - CHANGE COLUMN category_id id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - CHANGE COLUMN `system` `system` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN class_name VARCHAR(60) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'Resource', - ADD COLUMN mkdate INT(11) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN chdate INT(11) UNSIGNED NOT NULL DEFAULT 0, - DROP COLUMN is_room"); - - $db->exec("RENAME TABLE resources_categories_properties - TO resource_category_properties;"); - $db->exec("ALTER TABLE resource_category_properties - CHANGE COLUMN requestable requestable TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, - CHANGE COLUMN protected protected TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, - CHANGE COLUMN `system` `system` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN form_text text NULL, - ADD COLUMN mkdate INT(11) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN chdate INT(11) UNSIGNED NOT NULL DEFAULT 0"); - - $db->exec("RENAME TABLE resources_properties - TO resource_property_definitions;"); - $db->exec("ALTER TABLE resource_property_definitions - CHANGE COLUMN type type SET ( - 'bool', 'text', 'num', 'select', 'user', 'institute', - 'position', 'fileref', 'url', 'resource_ref_list' - ) CHARACTER SET latin1 COLLATE latin1_bin, - CHANGE COLUMN description description text NULL DEFAULT NULL, - CHANGE COLUMN `system` `system` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN display_name VARCHAR(512) NOT NULL DEFAULT '', - ADD COLUMN searchable TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN range_search TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN write_permission_level VARCHAR(16) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'admin-global', - ADD COLUMN property_group_id INT(11) NULL, - ADD COLUMN property_group_pos TINYINT(4) NULL, - ADD COLUMN mkdate INT(11) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN chdate INT(11) UNSIGNED NOT NULL DEFAULT 0"); - - //Set the display_name to the property name as default: - - $db->exec( - "UPDATE resource_property_definitions - SET display_name = name;" - ); - - $db->exec("RENAME TABLE resources_objects_properties - TO resource_properties;"); - $db->exec("ALTER TABLE resource_properties - ADD COLUMN mkdate INT(11) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN chdate INT(11) UNSIGNED NOT NULL DEFAULT 0"); - - $db->exec("RENAME TABLE resources_requests - TO resource_requests;"); - $db->exec("ALTER TABLE resource_requests - CHANGE COLUMN request_id id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - CHANGE COLUMN seminar_id course_id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - CHANGE COLUMN closed closed TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, - CHANGE COLUMN category_id category_id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NULL DEFAULT '', - ADD COLUMN begin INT(11) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN end INT(11) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN preparation_time INT(4) NOT NULL DEFAULT 0, - ADD COLUMN marked TINYINT(1) UNSIGNED NOT NULL DEFAULT 0" - ); - - $db->exec("RENAME TABLE resources_requests_properties - TO resource_request_properties;"); - - $db->exec("RENAME TABLE resources_assign TO resource_bookings;"); - $db->exec("ALTER TABLE resource_bookings - CHANGE COLUMN assign_id id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - CHANGE COLUMN user_free_name description TEXT NULL, - CHANGE COLUMN assign_user_id range_id CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - CHANGE COLUMN repeat_interval old_rep_interval INT(2) NULL DEFAULT NULL, - CHANGE COLUMN repeat_month_of_year old_rep_month_of_year INT(2) NULL DEFAULT NULL, - CHANGE COLUMN repeat_day_of_month old_rep_day_of_month INT(2) NULL DEFAULT NULL, - CHANGE COLUMN repeat_week_of_month old_rep_week_of_month INT(2) NULL DEFAULT NULL, - CHANGE COLUMN repeat_day_of_week old_rep_day_of_week INT(2) NULL DEFAULT NULL, - CHANGE COLUMN comment_internal internal_comment text NULL DEFAULT NULL, - ADD COLUMN preparation_time INT(4) NOT NULL DEFAULT 0, - ADD COLUMN booking_type TINYINT(2) NOT NULL DEFAULT 0, - ADD COLUMN booking_user_id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - ADD COLUMN repetition_interval VARCHAR(24) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '';" - ); - $db->exec("ALTER TABLE `resource_bookings` DROP INDEX `resource_id`, ADD INDEX `resource_id` (`resource_id`, `booking_type`)"); - - $db->exec("RENAME TABLE resources_locks - TO global_resource_locks;"); - $db->exec("ALTER TABLE global_resource_locks - CHANGE COLUMN lock_begin begin INT(11) UNSIGNED NOT NULL DEFAULT 0, - CHANGE COLUMN lock_end end INT(11) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN user_id VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - ADD COLUMN mkdate INT(11) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN chdate INT(11) UNSIGNED NOT NULL DEFAULT 0"); - - $db->exec("RENAME TABLE resources_user_resources TO resource_permissions;"); - $db->exec("ALTER TABLE resource_permissions - ADD COLUMN mkdate INT(11) UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN chdate INT(11) UNSIGNED NOT NULL DEFAULT 0"); - $db->exec("ALTER TABLE resource_permissions ADD INDEX (resource_id)"); - $db->exec("UPDATE resource_permissions SET resource_id = 'global' - WHERE resource_id = 'all';"); - - $db->exec("DROP TABLE resources_temporary_events;"); - $db->exec( - "CREATE TABLE `resource_booking_intervals` ( - `interval_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `resource_id` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - `booking_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', - `begin` int(20) NOT NULL DEFAULT 0, - `end` int(20) NOT NULL DEFAULT 0, - `mkdate` int(11) unsigned NOT NULL DEFAULT 0, - `chdate` int(11) unsigned NOT NULL DEFAULT 0, - `takes_place` tinyint(1) unsigned NOT NULL DEFAULT 1, - PRIMARY KEY (`interval_id`), - INDEX `resource_id` (`resource_id`,`takes_place`,`end`) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"); - - //Delete old tables: - - $db->exec("DROP TABLE resources_requests_user_status;"); - - //Add other nice things and modify stuff: - - $new_log_action_stmt = $db->prepare( - "INSERT IGNORE INTO log_actions (action_id, name, description) - VALUES - (:action_id, :name, :description);" - ); - - $action_id = md5(uniqid('RES_PERM_CHANGE')); - $new_log_action_stmt->execute( - [ - 'action_id' => $action_id, - 'name' => 'RES_PERM_CHANGE', - 'description' => 'Änderung der Berechtigungsstufe an einer Ressource.' - ] - ); - - //Set parent_id = '' where parent_id = 'root' or '0' in - //resources so that root resources always - //have an empty parent_id field: - $db->exec("UPDATE resources - SET parent_id = '' - WHERE parent_id = 'root' OR parent_id = '0';" - ); - - //We can delete resources now that are not needed anymore: - $this->deleteResources(); - - //First we remove those properties that are not needed anymore: - $this->deleteExistingProperties($db); - - //Now we have to migrate existing properties - //that are system properties with a different name: - $this->migrateExistingProperties($db); - //And we have to merge some properties, too: - $this->mergeProperties($db); - - //Now we convert all boolean property values to '0' and '1' - //instead of '' and 'on': - $db->exec( - "UPDATE resource_properties - INNER JOIN resource_property_definitions rpd - USING (property_id) - SET state = '1' - WHERE state = 'on' AND rpd.type = 'bool';" - ); - $db->exec( - "UPDATE resource_properties - INNER JOIN resource_property_definitions rpd - USING (property_id) - SET state = '0' - WHERE state = '' AND rpd.type = 'bool';" - ); - - //Repeat the same for resource request properties: - - $db->exec( - "UPDATE resource_request_properties - INNER JOIN resource_property_definitions rpd - USING (property_id) - SET state = '1' - WHERE state = 'on' AND rpd.type = 'bool';" - ); - $db->exec( - "UPDATE resource_request_properties - INNER JOIN resource_property_definitions rpd - USING (property_id) - SET state = '0' - WHERE state = '' AND rpd.type = 'bool';" - ); - - //rename and update resource categories: - - $db->exec("UPDATE resource_categories - SET class_name = 'Room', `system` = 1 - WHERE - name LIKE '%raum%' OR name LIKE '%Raum%' - OR name LIKE '%saal%' OR name LIKE '%Saal%';"); - - $db->exec("UPDATE resource_categories - SET class_name = 'Building', `system` = 1 - WHERE - name LIKE '%gebäude%' OR name LIKE '%Gebäude%';"); - - //add new resource categories and new properties: - - $add_category_statement = $db->prepare( - "INSERT INTO `resource_categories` - (`id`,`name`, `class_name`, `description`, `system`, `iconnr`) - VALUES - (:id, :name, :class_name, '', 1, 0);" - ); - - $location_cat_id = md5('StandortLocationResourceCategory100'); - - $building_cat_id_rows = $db->query("SELECT id - FROM resource_categories - WHERE name LIKE '%gebäude%';")->fetchAll(); - - $room_cat_id_rows = $db->query("SELECT id - FROM resource_categories - WHERE name LIKE '%raum%' OR name LIKE '%saal%';")->fetchAll(); - - $building_cat_ids = []; - foreach ($building_cat_id_rows as $row) { - $building_cat_ids[] = $row[0]; - } - - $room_cat_ids = []; - foreach ($room_cat_id_rows as $row) { - $room_cat_ids[] = $row[0]; - } - - $add_category_statement->execute( - [ - 'id' => $location_cat_id, - 'name' => 'Standort', - 'class_name' => 'Location' - ] - ); - - //Assign orphaned resources (resources without category-id): - $this->assignOrphanedResources($db); - $this->deleteUnfinishedResources($db); - - if (is_array($GLOBALS['RESOURCE_CATEGORY_CLASS_MAPPING'])) { - //Use the class mapping rules from that configuration setting: - $mapping_stmt = $db->prepare( - 'UPDATE resource_categories - SET class_name = :class_name - WHERE name = :name' - ); - foreach ($GLOBALS['RESOURCE_CATEGORY_CLASS_MAPPING'] as $name => $class_name) { - $mapping_stmt->execute( - [ - 'class_name' => $class_name, - 'name' => $name - ] - ); - } - } else { - //Migrate rooms, buildings and locations, based on best guess. - $this->migrateRooms($db); - $this->migrateBuildings($db); - $this->migrateLocations($db, $location_cat_id); - } - - if (count($GLOBALS['RESOURCE_CATEGORY_RENAME'])) { - $rename_stmt = $db->prepare( - 'UPDATE resource_categories SET name = :name - WHERE name = :old_name' - ); - - foreach ($GLOBALS['RESOURCE_CATEGORY_RENAME'] as $old_name => $name) { - $rename_stmt->execute( - [ - 'old_name' => $old_name, - 'name' => $name - ] - ); - } - } - - //Get all location categories. - //migrateLocations has set the Location class name - //for all location categories. - $location_cat_id_rows = $db->query( - "SELECT id - FROM resource_categories - WHERE class_name = 'Location';" - )->fetchAll(); - $location_cat_ids = []; - foreach ($location_cat_id_rows as $row) { - $location_cat_ids[] = $row[0]; - } - - //Add or create missing default properties: - - $property_exists_statement = $db->prepare( - "SELECT property_id - FROM resource_property_definitions - WHERE - name = :name AND type = :type;" - ); - - $add_property_statement = $db->prepare( - "INSERT INTO `resource_property_definitions` - (`property_id`, `name`, `type`, `options`, `system`) - VALUES - (:id, :name, :type, '', 1);" - ); - - $property_link_exists_statement = $db->prepare( - "SELECT 1 - FROM resource_category_properties - WHERE - category_id = :category_id AND property_id = :property_id;" - ); - - $update_property_link_statement = $db->prepare( - "UPDATE resource_category_properties - SET `system` = 1 - WHERE - category_id = :category_id AND property_id = :property_id;" - ); - - $link_property_statement = $db->prepare( - "INSERT INTO resource_category_properties - (`category_id`, `property_id`, `system`) - VALUES - (:category_id, :property_id, 1);" - ); - - $all_mandatory_properties = [ - 'location' => [ - //location properties: - 'geo_coordinates' => 'position' - ], - - 'building' => [ - //building properties: - 'geo_coordinates' => 'position', - 'number' => 'text', - 'address' => 'text' - ], - - 'room' => [ - //room properties: - 'booking_plan_is_public' => 'bool', - 'room_type' => 'select', - 'seats' => 'num', - 'responsible_person' => 'user' - ] - ]; - - foreach ($all_mandatory_properties as $area_name => $area) { - $area_ids = ['']; - if ($area_name == 'location') { - $area_ids = $location_cat_ids; - } elseif ($area_name == 'building') { - $area_ids = $building_cat_ids; - } elseif ($area_name == 'room') { - $area_ids = $room_cat_ids; - } - foreach ($area as $name => $type) { - //Check if the property exists. If so, link it only. - //Otherwise create it. - $property_exists_statement->execute( - [ - 'name' => $name, - 'type' => $type - ] - ); - - $property_id = $property_exists_statement->fetchColumn(0); - if (!$property_id) { - //property doesn't exist: create it - $property_id = md5($name . $type . rand()); - $add_property_statement->execute( - [ - 'id' => $property_id, - 'name' => $name, - 'type' => $type - ] - ); - } - - foreach ($area_ids as $area_id) { - //Check if the property is linked to the category. - //If so, we must make sure the system attribute - //is set. Otherwise we must create the link. - $property_link_exists_statement->execute( - [ - 'category_id' => $area_id, - 'property_id' => $property_id - ] - ); - - $link_exists = $property_link_exists_statement->fetchColumn(0); - - if ($link_exists) { - $update_property_link_statement->execute( - [ - 'category_id' => $area_id, - 'property_id' => $property_id - ] - ); - } else { - $link_property_statement->execute( - [ - 'category_id' => $area_id, - 'property_id' => $property_id - ] - ); - } - //make plans visible - if ($area_name === 'room' && $name === 'booking_plan_is_public') { - $db->execute("INSERT IGNORE INTO `resource_properties` (`resource_id`, `property_id`, `state`, `mkdate`, `chdate`) SELECT `id` , ?, '1', UNIX_TIMESTAMP(), UNIX_TIMESTAMP() FROM `resources` WHERE `category_id` = ?", [$property_id, $area_id]); - } - } - } - } - - //At this point, we can create the property groups (if any): - $this->createPropertyGroups($db); - - //Migrate resource booking interval data: - $this->migrateBookingRepeatIntervals($db); - - $this->migrateOwner(); - //Migrate course permissions on resources: - $this->migrateCourseBoundPermissions($db); - $this->deleteOldPermissions($db); - - //In case no location resource is in the database, - //one such resource will be created along with the - //corresponding category. - $this->createMissingLocation($db); - - //Create entries in resource_booking_intervals for each booking: - $this->fillResourceBookingIntervals($db); - - SimpleORMap::expireTableScheme(); - } - - - public function down() - { - //I see nothing! I hear nothing! Nothing!! - } -} diff --git a/db/migrations/1.278_jsonapi_dangerous_routes_config.php b/db/migrations/1.278_jsonapi_dangerous_routes_config.php new file mode 100644 index 0000000..87c23f9 --- /dev/null +++ b/db/migrations/1.278_jsonapi_dangerous_routes_config.php @@ -0,0 +1,36 @@ +exec($query); + } + + public function down() + { + $query = "DELETE `config`, `config_values` + FROM `config` + LEFT JOIN `config_values` USING (`field`) + WHERE `field` = 'JSONAPI_DANGEROUS_ROUTES_ALLOWED'"; + DBManager::get()->exec($query); + } +} diff --git a/db/migrations/1.278_resize_auth_user_md5_email_field.php b/db/migrations/1.278_resize_auth_user_md5_email_field.php deleted file mode 100644 index c0efe40..0000000 --- a/db/migrations/1.278_resize_auth_user_md5_email_field.php +++ /dev/null @@ -1,25 +0,0 @@ -exec( - "ALTER TABLE `auth_user_md5` - CHANGE COLUMN `Email` `Email` VARCHAR(256) NULL DEFAULT NULL" - ); - } - - - public function down() - { - DBManager::get()->exec( - "ALTER TABLE `auth_user_md5` - CHANGE COLUMN `Email` `Email` VARCHAR(256) NULL DEFAULT NULL" - ); - } -} diff --git a/db/migrations/1.283_add_filetypes.php b/db/migrations/1.283_add_filetypes.php deleted file mode 100644 index cade7b0..0000000 --- a/db/migrations/1.283_add_filetypes.php +++ /dev/null @@ -1,48 +0,0 @@ -exec(" - ALTER TABLE files - ADD COLUMN `filetype` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT 'StandardFile' AFTER `name`, - ADD COLUMN `metadata` TEXT NULL AFTER `size` - "); - DBManager::get()->exec(' - UPDATE files - INNER JOIN file_urls ON (file_urls.file_id = files.id) - SET files.metadata = JSON_OBJECT("url", file_urls.url, "access_type", file_urls.access_type), - files.filetype = "URLFile" - '); - DBManager::get()->exec(" - DROP TABLE file_urls - "); - DBManager::get()->exec(" - ALTER TABLE files - DROP COLUMN `storage` - "); - } - - - public function down() - { - DBManager::get()->exec(" - CREATE TABLE `file_urls` ( - `file_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `url` varchar(4096) COLLATE utf8mb4_unicode_ci NOT NULL, - `access_type` enum('proxy','redirect') CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'proxy', - PRIMARY KEY (`file_id`) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC; - "); - DBManager::get()->exec(" - ALTER TABLE files - DROP COLUMN `filetype`, - DROP COLUMN `metadata` - "); - } -} diff --git a/db/migrations/1.283_change_schedule_color_with_category_index.php b/db/migrations/1.283_change_schedule_color_with_category_index.php new file mode 100644 index 0000000..40a7f5c --- /dev/null +++ b/db/migrations/1.283_change_schedule_color_with_category_index.php @@ -0,0 +1,61 @@ +exec("ALTER TABLE `schedule` CHANGE `color` `color` varchar(255) COMMENT 'category index'"); + DBManager::get()->exec("ALTER TABLE `schedule_seminare` CHANGE `color` `color` varchar(255) COMMENT 'category index'"); + + foreach ($PERS_TERMIN_KAT as $index => $cat) { + $query = "UPDATE `schedule` + SET `color` = :index + WHERE `color` = :color"; + $st = DBManager::get()->prepare($query); + $st->bindValue(':index', $index); + $st->bindValue(':color', $cat['color']); + $st->execute(); + + $query = "UPDATE `schedule_seminare` + SET `color` = :index + WHERE `color` = :color"; + $st = DBManager::get()->prepare($query); + $st->bindValue(':index', $index); + $st->bindValue(':color', $cat['color']); + $st->execute(); + } + } + + public function down() + { + global $PERS_TERMIN_KAT; + + DBManager::get()->exec("ALTER TABLE `schedule` CHANGE `color` `color` varchar(7) COMMENT 'color, rgb in hex'"); + DBManager::get()->exec("ALTER TABLE `schedule_seminare` CHANGE `color` `color` varchar(7) COMMENT 'color, rgb in hex'"); + + foreach ($PERS_TERMIN_KAT as $index => $cat) { + $query = "UPDATE `schedule` + SET `color` = :color + WHERE `color` = :index"; + $st = DBManager::get()->prepare($query); + $st->bindValue(':index', $index); + $st->bindValue(':color', $cat['color']); + $st->execute(); + + $query = "UPDATE `schedule_seminare` + SET `color` = :color + WHERE `color` = :index"; + $st = DBManager::get()->prepare($query); + $st->bindValue(':index', $index); + $st->bindValue(':color', $cat['color']); + $st->execute(); + } + } +} diff --git a/db/migrations/1.284_change_schedule_color_with_category_index.php b/db/migrations/1.284_change_schedule_color_with_category_index.php deleted file mode 100644 index 40a7f5c..0000000 --- a/db/migrations/1.284_change_schedule_color_with_category_index.php +++ /dev/null @@ -1,61 +0,0 @@ -exec("ALTER TABLE `schedule` CHANGE `color` `color` varchar(255) COMMENT 'category index'"); - DBManager::get()->exec("ALTER TABLE `schedule_seminare` CHANGE `color` `color` varchar(255) COMMENT 'category index'"); - - foreach ($PERS_TERMIN_KAT as $index => $cat) { - $query = "UPDATE `schedule` - SET `color` = :index - WHERE `color` = :color"; - $st = DBManager::get()->prepare($query); - $st->bindValue(':index', $index); - $st->bindValue(':color', $cat['color']); - $st->execute(); - - $query = "UPDATE `schedule_seminare` - SET `color` = :index - WHERE `color` = :color"; - $st = DBManager::get()->prepare($query); - $st->bindValue(':index', $index); - $st->bindValue(':color', $cat['color']); - $st->execute(); - } - } - - public function down() - { - global $PERS_TERMIN_KAT; - - DBManager::get()->exec("ALTER TABLE `schedule` CHANGE `color` `color` varchar(7) COMMENT 'color, rgb in hex'"); - DBManager::get()->exec("ALTER TABLE `schedule_seminare` CHANGE `color` `color` varchar(7) COMMENT 'color, rgb in hex'"); - - foreach ($PERS_TERMIN_KAT as $index => $cat) { - $query = "UPDATE `schedule` - SET `color` = :color - WHERE `color` = :index"; - $st = DBManager::get()->prepare($query); - $st->bindValue(':index', $index); - $st->bindValue(':color', $cat['color']); - $st->execute(); - - $query = "UPDATE `schedule_seminare` - SET `color` = :color - WHERE `color` = :index"; - $st = DBManager::get()->prepare($query); - $st->bindValue(':index', $index); - $st->bindValue(':color', $cat['color']); - $st->execute(); - } - } -} diff --git a/db/migrations/1.284_fixes_on_schedule_coloring.php b/db/migrations/1.284_fixes_on_schedule_coloring.php new file mode 100644 index 0000000..73c6883 --- /dev/null +++ b/db/migrations/1.284_fixes_on_schedule_coloring.php @@ -0,0 +1,79 @@ + $cat) { + $query = "UPDATE `schedule` + SET `color` = :index + WHERE `color` = :color"; + $st = DBManager::get()->prepare($query); + $st->bindValue(':index', $index); + $st->bindValue(':color', $cat['color']); + $st->execute(); + + $query = "UPDATE `schedule_seminare` + SET `color` = :index + WHERE `color` = :color"; + $st = DBManager::get()->prepare($query); + $st->bindValue(':index', $index); + $st->bindValue(':color', $cat['color']); + $st->execute(); + } + + $default = 1; + $indexes = array_keys($PERS_TERMIN_KAT); + + $query = "UPDATE `schedule` + SET `color` = :default + WHERE `color` NOT IN ( :indexes )"; + $st = DBManager::get()->prepare($query); + $st->bindValue(':default', $default); + $st->bindValue(':indexes', $indexes); + $st->execute(); + + $query = "UPDATE `schedule_seminare` + SET `color` = :default + WHERE `color` NOT IN ( :indexes )"; + $st = DBManager::get()->prepare($query); + $st->bindValue(':default', $default); + $st->bindValue(':indexes', $indexes); + $st->execute(); + + DBManager::get()->exec("ALTER TABLE `schedule` MODIFY `color` tinyint"); + DBManager::get()->exec("ALTER TABLE `schedule_seminare` MODIFY `color` tinyint"); + } + + public function down() + { + global $PERS_TERMIN_KAT; + + DBManager::get()->exec("ALTER TABLE `schedule` CHANGE `color` `color` varchar(7) COMMENT 'color, rgb in hex'"); + DBManager::get()->exec("ALTER TABLE `schedule_seminare` CHANGE `color` `color` varchar(7) COMMENT 'color, rgb in hex'"); + + foreach ($PERS_TERMIN_KAT as $index => $cat) { + $query = "UPDATE `schedule` + SET `color` = :color + WHERE `color` = :index"; + $st = DBManager::get()->prepare($query); + $st->bindValue(':index', $index); + $st->bindValue(':color', $cat['color']); + $st->execute(); + + $query = "UPDATE `schedule_seminare` + SET `color` = :color + WHERE `color` = :index"; + $st = DBManager::get()->prepare($query); + $st->bindValue(':index', $index); + $st->bindValue(':color', $cat['color']); + $st->execute(); + } + } +} diff --git a/db/migrations/1.285_add_filetypes.php b/db/migrations/1.285_add_filetypes.php new file mode 100644 index 0000000..cade7b0 --- /dev/null +++ b/db/migrations/1.285_add_filetypes.php @@ -0,0 +1,48 @@ +exec(" + ALTER TABLE files + ADD COLUMN `filetype` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT 'StandardFile' AFTER `name`, + ADD COLUMN `metadata` TEXT NULL AFTER `size` + "); + DBManager::get()->exec(' + UPDATE files + INNER JOIN file_urls ON (file_urls.file_id = files.id) + SET files.metadata = JSON_OBJECT("url", file_urls.url, "access_type", file_urls.access_type), + files.filetype = "URLFile" + '); + DBManager::get()->exec(" + DROP TABLE file_urls + "); + DBManager::get()->exec(" + ALTER TABLE files + DROP COLUMN `storage` + "); + } + + + public function down() + { + DBManager::get()->exec(" + CREATE TABLE `file_urls` ( + `file_id` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `url` varchar(4096) COLLATE utf8mb4_unicode_ci NOT NULL, + `access_type` enum('proxy','redirect') CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'proxy', + PRIMARY KEY (`file_id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC; + "); + DBManager::get()->exec(" + ALTER TABLE files + DROP COLUMN `filetype`, + DROP COLUMN `metadata` + "); + } +} diff --git a/db/migrations/1.285_fixes_on_schedule_coloring.php b/db/migrations/1.285_fixes_on_schedule_coloring.php deleted file mode 100644 index 73c6883..0000000 --- a/db/migrations/1.285_fixes_on_schedule_coloring.php +++ /dev/null @@ -1,79 +0,0 @@ - $cat) { - $query = "UPDATE `schedule` - SET `color` = :index - WHERE `color` = :color"; - $st = DBManager::get()->prepare($query); - $st->bindValue(':index', $index); - $st->bindValue(':color', $cat['color']); - $st->execute(); - - $query = "UPDATE `schedule_seminare` - SET `color` = :index - WHERE `color` = :color"; - $st = DBManager::get()->prepare($query); - $st->bindValue(':index', $index); - $st->bindValue(':color', $cat['color']); - $st->execute(); - } - - $default = 1; - $indexes = array_keys($PERS_TERMIN_KAT); - - $query = "UPDATE `schedule` - SET `color` = :default - WHERE `color` NOT IN ( :indexes )"; - $st = DBManager::get()->prepare($query); - $st->bindValue(':default', $default); - $st->bindValue(':indexes', $indexes); - $st->execute(); - - $query = "UPDATE `schedule_seminare` - SET `color` = :default - WHERE `color` NOT IN ( :indexes )"; - $st = DBManager::get()->prepare($query); - $st->bindValue(':default', $default); - $st->bindValue(':indexes', $indexes); - $st->execute(); - - DBManager::get()->exec("ALTER TABLE `schedule` MODIFY `color` tinyint"); - DBManager::get()->exec("ALTER TABLE `schedule_seminare` MODIFY `color` tinyint"); - } - - public function down() - { - global $PERS_TERMIN_KAT; - - DBManager::get()->exec("ALTER TABLE `schedule` CHANGE `color` `color` varchar(7) COMMENT 'color, rgb in hex'"); - DBManager::get()->exec("ALTER TABLE `schedule_seminare` CHANGE `color` `color` varchar(7) COMMENT 'color, rgb in hex'"); - - foreach ($PERS_TERMIN_KAT as $index => $cat) { - $query = "UPDATE `schedule` - SET `color` = :color - WHERE `color` = :index"; - $st = DBManager::get()->prepare($query); - $st->bindValue(':index', $index); - $st->bindValue(':color', $cat['color']); - $st->execute(); - - $query = "UPDATE `schedule_seminare` - SET `color` = :color - WHERE `color` = :index"; - $st = DBManager::get()->prepare($query); - $st->bindValue(':index', $index); - $st->bindValue(':color', $cat['color']); - $st->execute(); - } - } -} diff --git a/lib/migrations/DBSchemaVersion.php b/lib/migrations/DBSchemaVersion.php index 0a14558..846a544 100644 --- a/lib/migrations/DBSchemaVersion.php +++ b/lib/migrations/DBSchemaVersion.php @@ -166,9 +166,9 @@ class DBSchemaVersion implements SchemaVersion $result = $db->query("SHOW TABLES LIKE 'schema_versions'"); if ($result && $result->rowCount() > 0) { - $base_version = 269; // 4.4 + $base_version = 263; // 4.4 $schema_mapping = [ - 20200307 => 285, // 4.5 + 20200307 => 284, // 4.5 20200522 => 290, // 4.6 20210511 => 327 // 5.0 ]; -- cgit v1.0