From 863850d2d7b85042f8ad3da11662b956d22bae59 Mon Sep 17 00:00:00 2001
From: skodak <skodak>
Date: Wed, 19 Dec 2007 17:48:21 +0000
Subject: [PATCH] MDL-12858 fix course category depths during upgrade to 1.9;
 merged from MOODLE_19_STABLE

---
 lib/db/upgrade.php    |  5 +----
 lib/db/upgradelib.php | 48 +++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 49 insertions(+), 4 deletions(-)

diff --git a/lib/db/upgrade.php b/lib/db/upgrade.php
index bd28995cea..41ab52e081 100644
--- a/lib/db/upgrade.php
+++ b/lib/db/upgrade.php
@@ -2055,10 +2055,7 @@ function xmldb_main_upgrade($oldversion=0) {
         $result = $result && create_table($table);
 
     /// make sure category depths, parents and paths are ok, categories from 1.5 may not be properly initialized (MDL-12585)
-        $sql = "UPDATE {$CFG->prefix}course_categories
-                   SET depth = 1, parent = 0, path = ".sql_concat("'/'", "id")."
-                 WHERE depth = 0 OR parent = 0";
-        execute_sql($sql); 
+        upgrade_fix_category_depths();
 
     /// Recalculate depths, paths and so on
         if (!empty($CFG->rolesactive)) {
diff --git a/lib/db/upgradelib.php b/lib/db/upgradelib.php
index 2c038a76c7..f27b7862f5 100644
--- a/lib/db/upgradelib.php
+++ b/lib/db/upgradelib.php
@@ -514,4 +514,52 @@ function upgrade_18_groups_drop_keys_indexes() {
     return $result;
 }
 
+function upgrade_fix_category_depths() {
+    global $CFG, $db;
+
+    // first fix incorrect parents
+    $sql = "SELECT c.id
+              FROM {$CFG->prefix}course_categories c
+             WHERE c.parent > 0 AND c.parent NOT IN (SELECT pc.id FROM {$CFG->prefix}course_categories pc)";
+    if ($rs = get_recordset_sql($sql)) {
+        while ($cat = rs_fetch_next_record($rs)) {
+            $cat->depth  = 1;
+            $cat->path   = '/'.$cat->id;
+            $cat->parent = 0;
+            update_record('course_categories', $cat);
+        }
+        rs_close($rs);
+    }
+
+    // now add path and depth to top level categories
+    $sql = "UPDATE {$CFG->prefix}course_categories
+               SET depth = 1, path = ".sql_concat("'/'", "id")."
+             WHERE parent = 0";
+    execute_sql($sql);
+
+    // now fix all other levels - slow but works in all supported dbs
+    $parentdepth = 1;
+    $db->debug = true;
+    while (record_exists('course_categories', 'depth', 0)) {
+        $sql = "SELECT c.id, pc.path
+                  FROM {$CFG->prefix}course_categories c, {$CFG->prefix}course_categories pc
+                 WHERE c.parent=pc.id AND c.depth=0 AND pc.depth=$parentdepth";
+        if ($rs = get_recordset_sql($sql)) {
+            while ($cat = rs_fetch_next_record($rs)) {
+                $cat->depth = $parentdepth+1;
+                $cat->path  = $cat->path.'/'.$cat->id;
+                update_record('course_categories', $cat);
+            }
+            rs_close($rs);
+        }
+        $parentdepth++;
+        if ($parentdepth > 100) {
+            //something must have gone wrong - nobody can have more than 100 levels of categories, right?
+            debugging('Unknown error fixing category depths');
+            break;
+        }
+    }
+    $db->debug = true;
+}
+
 ?>
-- 
2.39.5