Migrate trees to int primary keys and add ancestors column in database

7 files changed, 74 insertions, 8 deletions

diff --git a/db/migrations/6.1.2_tree_node_path.php b/db/migrations/6.1.2_tree_node_path.php
new file mode 100644
index 0000000..76468da
--- /dev/null
+++ b/db/migrations/6.1.2_tree_node_path.php
@@ -0,0 +1,66 @@
+<?php
+
+return new class extends Migration
+{
+    public function description()
+    {
+        return 'Adds the path to the current node to all sem_tree and range_tree entries.';
+    }
+
+    public function up()
+    {
+        // Add the new database column for storing node ancestry path.
+        DBManager::get()->exec("ALTER TABLE `sem_tree`
+            ADD `ancestors` VARCHAR(255) NOT NULL AFTER `parent_id`,
+            ADD INDEX `ancestors` (`ancestors`)"
+        );
+        StudipStudyArea::expireTableScheme();
+        $this->buildStructure(StudipStudyArea::class, 'root', 0, 0, '');
+
+        DBManager::get()->exec("ALTER TABLE `range_tree`
+            ADD `ancestors` VARCHAR(255) NOT NULL AFTER `parent_id`,
+            ADD INDEX `ancestors` (`ancestors`)"
+        );
+        RangeTreeNode::expireTableScheme();
+        $this->buildStructure(RangeTreeNode::class, 'root', 0, 0, '');
+
+        DBManager::get()->exec("ALTER TABLE `sem_tree`
+            CHANGE `sem_tree_id` `sem_tree_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
+            CHANGE `parent_id` `parent_id` INT UNSIGNED NOT NULL");
+        DBManager::get()->exec("ALTER TABLE `seminar_sem_tree`
+            CHANGE `sem_tree_id` `sem_tree_id` INT UNSIGNED NOT NULL");
+        DBManager::get()->exec("ALTER TABLE `range_tree`
+            CHANGE `item_id` `item_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
+            CHANGE `parent_id` `parent_id` INT UNSIGNED NOT NULL");
+    }
+
+    public function down()
+    {
+        DBManager::get()->exec("ALTER TABLE `sem_tree` DROP `ancestors`");
+        DBManager::get()->exec("ALTER TABLE `range_tree` DROP `ancestors`");
+    }
+
+    private function buildStructure(string $classname, string $oldParentId, int $newParentId, int $currentId, string $ancestors)
+    {
+        foreach ($classname::findByParent_id($oldParentId, "ORDER BY `priority`") as $child) {
+            $currentId++;
+
+            $oldId = $child->id;
+            $child->id = $currentId;
+            $child->parent_id = $newParentId;
+            $child->ancestors = $ancestors . ($ancestors === '' ? '' : '|') . $newParentId;
+            $child->store();
+
+            DBManager::get()->execute(
+                "UPDATE `seminar_sem_tree` SET `sem_tree_id` = :new WHERE `sem_tree_id` = :old",
+                ['new' => $child->id, 'old' => $oldId]
+            );
+
+            $currentId = $this->buildStructure($classname, $oldId, $child->id, $currentId, $child->ancestors);
+        }
+
+        return $currentId;
+    }
+
+};
+
diff --git a/lib/classes/JsonApi/Routes/Tree/PathinfoOfTreeNodeCourse.php b/lib/classes/JsonApi/Routes/Tree/PathinfoOfTreeNodeCourse.php
index 282b7f9..042ee7e 100644
--- a/lib/classes/JsonApi/Routes/Tree/PathinfoOfTreeNodeCourse.php
+++ b/lib/classes/JsonApi/Routes/Tree/PathinfoOfTreeNodeCourse.php
@@ -24,7 +24,7 @@ class PathinfoOfTreeNodeCourse extends NonJsonApiController
 
         $path = [];
         foreach ($classname::getCourseNodes($args['id']) as $node) {
-            $path[] = $node->getAncestors();
+            $path[] = $node->getAncestorNodes();
         }
 
         $response->getBody()->write(json_encode($path));
diff --git a/lib/classes/JsonApi/Schemas/StudyArea.php b/lib/classes/JsonApi/Schemas/StudyArea.php
index e9779c7..5dc38fc 100644
--- a/lib/classes/JsonApi/Schemas/StudyArea.php
+++ b/lib/classes/JsonApi/Schemas/StudyArea.php
@@ -25,7 +25,7 @@ class StudyArea extends SchemaProvider
             'priority' => (int) $resource['priority'],
             'type-name' => (string) $resource->getTypeName(),
             'has-children' => (bool) $resource->hasChildNodes(),
-            'ancestors' => (array) $resource->getAncestors(),
+            'ancestors' => (array) $resource->getAncestorNodes(),
             'classname' => get_class($resource)
         ];
     }
diff --git a/lib/classes/JsonApi/Schemas/TreeNode.php b/lib/classes/JsonApi/Schemas/TreeNode.php
index 90e6846..9824450 100644
--- a/lib/classes/JsonApi/Schemas/TreeNode.php
+++ b/lib/classes/JsonApi/Schemas/TreeNode.php
@@ -31,7 +31,7 @@ class TreeNode extends SchemaProvider
             'description' => (string) $resource->getDescription(),
             'description-formatted' => (string) formatReady($resource->getDescription()),
             'has-children' => (bool) $resource->hasChildNodes(),
-            'ancestors' => (array) $resource->getAncestors(),
+            'ancestors' => (array) $resource->getAncestorNodes(),
             'classname' => get_class($resource),
             'visible' => true,
             'editable' => true,
diff --git a/lib/classes/StudipTreeNode.php b/lib/classes/StudipTreeNode.php
index a1d7258..6642493 100644
--- a/lib/classes/StudipTreeNode.php
+++ b/lib/classes/StudipTreeNode.php
@@ -109,6 +109,6 @@ interface StudipTreeNode
      *
      * @return array
      */
-    public function getAncestors(): array;
+    public function getAncestorNodes(): array;
 
 }
diff --git a/lib/models/RangeTreeNode.php b/lib/models/RangeTreeNode.php
index db2f4d4..fdea54e 100644
--- a/lib/models/RangeTreeNode.php
+++ b/lib/models/RangeTreeNode.php
@@ -181,7 +181,7 @@ class RangeTreeNode extends SimpleORMap implements StudipTreeNode
         return DBManager::get()->fetchAll($query, $parameters, 'Course::buildExisting');
     }
 
-    public function getAncestors(): array
+    public function getAncestorNodes(): array
     {
         $path = [
             [
@@ -192,7 +192,7 @@ class RangeTreeNode extends SimpleORMap implements StudipTreeNode
         ];
 
         if ($this->parent_id) {
-            $path = array_merge($this->getNode($this->parent_id)->getAncestors(), $path);
+            $path = array_merge($this->getNode($this->parent_id)->getAncestorNodes(), $path);
         }
 
         return $path;
diff --git a/lib/models/StudipStudyArea.php b/lib/models/StudipStudyArea.php
index 7aedeff..a686486 100644
--- a/lib/models/StudipStudyArea.php
+++ b/lib/models/StudipStudyArea.php
@@ -560,7 +560,7 @@ class StudipStudyArea extends SimpleORMap implements StudipTreeNode
         return DBManager::get()->fetchAll($query, $parameters, 'Course::buildExisting');
     }
 
-    public function getAncestors(): array
+    public function getAncestorNodes(): array
     {
         $path = [
             [
@@ -571,7 +571,7 @@ class StudipStudyArea extends SimpleORMap implements StudipTreeNode
         ];
 
         if ($this->parent_id) {
-            $path = array_merge($this->getNode($this->parent_id)->getAncestors(), $path);
+            $path = array_merge($this->getNode($this->parent_id)->getAncestorNodes(), $path);
         }
 
         return $path;