lib/db/upgradelib.php

   1 <?php  //$Id$
   2
   3 /*
   4  * This file is used for special upgrade functions - for example groups and gradebook.
   5  * These functions must use SQL and database related functions only- no other Moodle API,
   6  * because it might depend on db structures that are not yet present during upgrade.
   7  * (Do not use functions from accesslib.php, grades classes or group functions at all!)
   8  */
   9
  10 /**
  11  * Migrates the grade_letter data to grade_letters
  12  */
  13 function upgrade_18_letters() {
  14     global $CFG;
  15
  16     $table = new XMLDBTable('grade_letters');
  17
  18     if (table_exists($table)) {
  19         // already converted or development site
  20         return true;
  21     }
  22
  23     $result = true;
  24
  25 /// Rename field grade_low on table grade_letter to lowerboundary
  26     $table = new XMLDBTable('grade_letter');
  27     $field = new XMLDBField('grade_low');
  28     $field->setAttributes(XMLDB_TYPE_NUMBER, '5, 2', null, XMLDB_NOTNULL, null, null, null, '0.00', 'grade_high');
  29
  30 /// Launch rename field grade_low
  31     $result = $result && rename_field($table, $field, 'lowerboundary');
  32
  33 /// Define field grade_high to be dropped from grade_letter
  34     $table = new XMLDBTable('grade_letter');
  35     $field = new XMLDBField('grade_high');
  36
  37 /// Launch drop field grade_high
  38     $result = $result && drop_field($table, $field);
  39
  40 /// Define index courseid (not unique) to be dropped form grade_letter
  41     $table = new XMLDBTable('grade_letter');
  42     $index = new XMLDBIndex('courseid');
  43     $index->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('courseid'));
  44
  45 /// Launch drop index courseid
  46     $result = $result && drop_index($table, $index);
  47
  48 /// Rename field courseid on table grade_letter to contextid
  49     $table = new XMLDBTable('grade_letter');
  50     $field = new XMLDBField('courseid');
  51     $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'id');
  52
  53 /// Launch rename field courseid
  54     $result = $result && rename_field($table, $field, 'contextid');
  55
  56     $sql = "UPDATE {$CFG->prefix}grade_letter
  57                SET contextid=COALESCE((SELECT c.id
  58                                         FROM {$CFG->prefix}context c
  59                                        WHERE c.instanceid={$CFG->prefix}grade_letter.contextid AND c.contextlevel=".CONTEXT_COURSE."), 0)";
  60     execute_sql($sql);
  61
  62 /// remove broken records
  63     execute_sql("DELETE FROM {$CFG->prefix}grade_letter WHERE contextid=0");
  64
  65 /// Define table grade_letter to be renamed to grade_letters
  66     $table = new XMLDBTable('grade_letter');
  67
  68 /// Launch rename table for grade_letter
  69     $result = $result && rename_table($table, 'grade_letters');
  70
  71 /// Changing type of field lowerboundary on table grade_letters to number
  72     $table = new XMLDBTable('grade_letters');
  73     $field = new XMLDBField('lowerboundary');
  74     $field->setAttributes(XMLDB_TYPE_NUMBER, '10, 5', null, XMLDB_NOTNULL, null, null, null, null, 'contextid');
  75
  76 /// Launch change of type for field lowerboundary
  77     $result = $result && change_field_precision($table, $field);
  78     $result = $result && change_field_default($table, $field);
  79
  80 /// Changing the default of field letter on table grade_letters to drop it
  81     $table = new XMLDBTable('grade_letters');
  82     $field = new XMLDBField('letter');
  83     $field->setAttributes(XMLDB_TYPE_CHAR, '255', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, null, 'lowerboundary');
  84
  85 /// Launch change of default for field letter
  86     $result = $result && change_field_precision($table, $field);
  87     $result = $result && change_field_default($table, $field);
  88
  89 /// Define index contextidlowerboundary (not unique) to be added to grade_letters
  90     $table = new XMLDBTable('grade_letters');
  91     $index = new XMLDBIndex('contextid-lowerboundary');
  92     $index->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('contextid', 'lowerboundary'));
  93
  94 /// Launch add index contextidlowerboundary
  95     $result = $result && add_index($table, $index);
  96
  97     return $result;
  98 }
  99
 100
 101 /**
 102  * This function is used to migrade old data and settings from old gradebook into new grading system.
 103  * It is executed only once for each course during upgrade to 1.9, all grade tables must be empty initially.
 104  * @param int $courseid
 105  */
 106 function upgrade_18_gradebook($courseid) {
 107     global $CFG;
 108
 109     require_once($CFG->libdir.'/gradelib.php'); // we need constants only
 110
 111     // get all grade items with mod details and categories
 112     $sql = "SELECT gi.*, cm.idnumber as cmidnumber, m.name as modname
 113               FROM {$CFG->prefix}grade_item gi, {$CFG->prefix}course_modules cm, {$CFG->prefix}modules m
 114              WHERE gi.courseid=$courseid AND m.id=gi.modid AND cm.instance=gi.cminstance
 115           ORDER BY gi.sort_order ASC";
 116
 117     if (!$olditems = get_records_sql($sql)) {
 118         //nothing to do - no items present in old gradebook
 119         return true;
 120     }
 121
 122     if (!$oldcats = get_records('grade_category', 'courseid', $courseid, 'id')) {
 123         //there should be at least uncategorised category - hmm, nothing to do
 124         return true;
 125     }
 126
 127     $order = 1;
 128
 129     // create course category
 130     $course_category = new object();
 131     $course_category->courseid     = $courseid;
 132     $course_category->fullname     = '?';
 133     $course_category->parent       = null;
 134     $course_category->aggregation  = GRADE_AGGREGATE_WEIGHTED_MEAN2;
 135     $course_category->timemodified = $course_category->timecreated = time();
 136     $course_category->aggregateonlygraded = 0;
 137     if (!$course_category->id = insert_record('grade_categories', $course_category)) {
 138         return false;
 139     }
 140     $course_category->depth = 1;
 141     $course_category->path  = '/'.$course_category->id;
 142     if (!update_record('grade_categories', $course_category)) {
 143         return false;
 144     }
 145
 146     // create course item
 147     $course_item = new object();
 148     $course_item->courseid     = $courseid;
 149     $course_item->itemtype     = 'course';
 150     $course_item->iteminstance = $course_category->id;
 151     $course_item->gradetype    = GRADE_TYPE_VALUE;
 152     $course_item->display = GRADE_DISPLAY_TYPE_PERCENTAGE;
 153     $course_item->sortorder    = $order++;
 154     $course_item->timemodified = $course_item->timecreated = $course_category->timemodified;
 155     $course_item->needsupdate  = 1;
 156     if (!insert_record('grade_items', $course_item)) {
 157         return false;
 158     }
 159
 160     // existing categories
 161     $categories = array();
 162     $hiddenoldcats = array();
 163     if (count($oldcats) == 1) {
 164         $oldcat = reset($oldcats);
 165         if ($oldcat->drop_x_lowest) {
 166             $course_category->droplow = $oldcat->drop_x_lowest;
 167             update_record('grade_categories', $course_category);
 168         }
 169         $categories[$oldcat->id] = $course_category;
 170
 171     } else {
 172         foreach ($oldcats as $oldcat) {
 173             $category = new object();
 174             $category->courseid     = $courseid;
 175             $category->fullname     = addslashes($oldcat->name);
 176             $category->parent       = $course_category->id;
 177             $category->droplow      = $oldcat->drop_x_lowest;
 178             $category->aggregation  = GRADE_AGGREGATE_WEIGHTED_MEAN2;
 179             $category->timemodified = $category->timecreated = time();
 180             $category->aggregateonlygraded = 0;
 181             if (!$category->id = insert_record('grade_categories', $category)) {
 182                 return false;
 183             }
 184             $category->depth = 2;
 185             $category->path  = '/'.$course_category->id.'/'.$category->id;
 186             if (!update_record('grade_categories', $category)) {
 187                 return false;
 188             }
 189
 190             $categories[$oldcat->id] = $category;
 191
 192             $item = new object();
 193             $item->courseid        = $courseid;
 194             $item->itemtype        = 'category';
 195             $item->iteminstance    = $category->id;
 196             $item->gradetype       = GRADE_TYPE_VALUE;
 197             $item->display         = GRADE_DISPLAY_TYPE_PERCENTAGE;
 198             $item->plusfactor      = $oldcat->bonus_points;
 199             $item->hidden          = $oldcat->hidden;
 200             $item->aggregationcoef = $oldcat->weight;
 201             $item->sortorder       = $order++;
 202             $item->timemodified    = $item->timecreated = $category->timemodified;
 203             $item->needsupdate     = 1;
 204             if (!insert_record('grade_items', $item)) {
 205                 return false;
 206             }
 207             if ($item->hidden) {
 208                 $hiddenoldcats[] = $oldcat->id;
 209             }
 210         }
 211
 212         $course_category->aggregation = GRADE_AGGREGATE_WEIGHTED_MEAN2;
 213         update_record('grade_categories', $course_category);
 214     }
 215     unset($oldcats);
 216
 217     // existing items
 218     $newitems = array();
 219     foreach ($olditems as $olditem) {
 220         if (empty($categories[$olditem->category])) {
 221             continue; // faulty record
 222         }
 223         // proper data are set during activity upgrade or legacy grade fetching
 224         $item = new object();
 225         $item->courseid        = $courseid;
 226         $item->itemtype        = 'mod';
 227         $item->itemmodule      = $olditem->modname;
 228         $item->iteminstance    = $olditem->cminstance;
 229         $item->idnumber        = $olditem->cmidnumber;
 230         $item->itemname        = NULL;
 231         $item->itemnumber      = 0;
 232         $item->gradetype       = GRADE_TYPE_VALUE;
 233         $item->multfactor      = $olditem->scale_grade;
 234         $item->hidden          = (int)in_array($olditem->category, $hiddenoldcats);
 235         $item->aggregationcoef = $olditem->extra_credit;
 236         $item->sortorder       = $order++;
 237         $item->timemodified    = $item->timecreated = time();
 238         $item->needsupdate     = 1;
 239         $item->categoryid  = $categories[$olditem->category]->id;
 240         if (!$item->id = insert_record('grade_items', $item)) {
 241             return false;
 242         }
 243
 244         $newitems[$olditem->id] = $item;
 245
 246         if ($olditem->extra_credit and $categories[$olditem->category]->aggregation != GRADE_AGGREGATE_EXTRACREDIT_MEAN) {
 247             $categories[$olditem->category]->aggregation = GRADE_AGGREGATE_EXTRACREDIT_MEAN;
 248             update_record('grade_categories', $categories[$olditem->category]);
 249         }
 250     }
 251     unset($olditems);
 252
 253     // setup up exception handling - exclude grade from aggregation
 254     if ($exceptions = get_records('grade_exceptions', 'courseid', $courseid)) {
 255         foreach ($exceptions as $exception) {
 256             if (!array_key_exists($exception->grade_itemid, $newitems)) {
 257                 continue; // broken record
 258             }
 259             $grade = new object();
 260             $grade->excluded     = time();
 261             $grade->itemid       = $newitems[$exception->grade_itemid]->id;
 262             $grade->userid       = $exception->userid;
 263             $grade->timemodified = $grade->timecreated = $grade->excluded;
 264             insert_record('grade_grades', $grade);
 265         }
 266     }
 267
 268     return true;
 269 }
 270
 271
 272
 273 /**
 274  * Create new groupings tables for upgrade from 1.7.*|1.6.* and so on.
 275  */
 276 function upgrade_17_groups() {
 277     global $CFG;
 278
 279     $result = true;
 280
 281 /// Define table groupings to be created
 282     $table = new XMLDBTable('groupings');
 283
 284 /// Adding fields to table groupings
 285     $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
 286     $table->addFieldInfo('courseid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 287     $table->addFieldInfo('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null, null, null);
 288     $table->addFieldInfo('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null, null, null);
 289     $table->addFieldInfo('configdata', XMLDB_TYPE_TEXT, 'small', null, null, null, null, null, null);
 290     $table->addFieldInfo('timecreated', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 291     $table->addFieldInfo('timemodified', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 292
 293 /// Adding keys to table groupings
 294     $table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'));
 295     $table->addKeyInfo('courseid', XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 296
 297 /// Launch create table for groupings
 298     $result = $result && create_table($table);
 299
 300 // ==========================================
 301
 302 /// Define table groupings_groups to be created
 303     $table = new XMLDBTable('groupings_groups');
 304
 305 /// Adding fields to table groupings_groups
 306     $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
 307     $table->addFieldInfo('groupingid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 308     $table->addFieldInfo('groupid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 309     $table->addFieldInfo('timeadded', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 310
 311 /// Adding keys to table groupings_groups
 312     $table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'));
 313     $table->addKeyInfo('groupingid', XMLDB_KEY_FOREIGN, array('groupingid'), 'groupings', array('id'));
 314     $table->addKeyInfo('groupid', XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
 315
 316 /// Launch create table for groupings_groups
 317     $result = $result && create_table($table);
 318
 319 /// fix not null constrain
 320     $table = new XMLDBTable('groups');
 321     $field = new XMLDBField('password');
 322     $field->setAttributes(XMLDB_TYPE_CHAR, '50', null, null, null, null, null, null, 'description');
 323     $result = $result && change_field_notnull($table, $field);
 324
 325 /// Rename field password in table groups to enrolmentkey
 326     $table = new XMLDBTable('groups');
 327     $field = new XMLDBField('password');
 328     $field->setAttributes(XMLDB_TYPE_CHAR, '50', null, null, null, null, null, null, 'description');
 329     $result = $result && rename_field($table, $field, 'enrolmentkey');
 330
 331     return $result;
 332 }
 333
 334 /**
 335  * Drop, add fields and rename tables for groups upgrade from 1.8.*
 336  * @param XMLDBTable $table 'groups_groupings' table object.
 337  */
 338 function upgrade_18_groups() {
 339     global $CFG, $db;
 340
 341     $result = upgrade_18_groups_drop_keys_indexes();
 342
 343 /// Delete not used columns
 344     $fields_r = array('viewowngroup', 'viewallgroupsmembers', 'viewallgroupsactivities',
 345                       'teachersgroupmark', 'teachersgroupview', 'teachersoverride', 'teacherdeletable');
 346     foreach ($fields_r as $fname) {
 347         $table = new XMLDBTable('groups_groupings');
 348         $field = new XMLDBField($fname);
 349         if (field_exists($table, $field)) {
 350             $result = $result && drop_field($table, $field);
 351         }
 352     }
 353
 354 /// Rename 'groups_groupings' to 'groupings'
 355     $table = new XMLDBTable('groups_groupings');
 356     $result = $result && rename_table($table, 'groupings');
 357
 358 /// Add columns/key 'courseid', exclusivegroups, maxgroupsize, timemodified.
 359     $table = new XMLDBTable('groupings');
 360     $field = new XMLDBField('courseid');
 361     $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'id');
 362     $result = $result && add_field($table, $field);
 363
 364     $table = new XMLDBTable('groupings');
 365     $key = new XMLDBKey('courseid');
 366     $key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 367     $result = $result && add_key($table, $key);
 368
 369     $table = new XMLDBTable('groupings');
 370     $field = new XMLDBField('configdata');
 371     $field->setAttributes(XMLDB_TYPE_TEXT, 'small', null, null, null, null, null, null, 'description');
 372     $result = $result && add_field($table, $field);
 373
 374     $table = new XMLDBTable('groupings');
 375     $field = new XMLDBField('timemodified');
 376     $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'timecreated');
 377     $result = $result && add_field($table, $field);
 378
 379 //==================
 380
 381 /// Add columns/key 'courseid' into groups table
 382     $table = new XMLDBTable('groups');
 383     $field = new XMLDBField('courseid');
 384     $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'id');
 385     $result = $result && add_field($table, $field);
 386
 387     $table = new XMLDBTable('groups');
 388     $key = new XMLDBKey('courseid');
 389     $key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 390     $result = $result && add_key($table, $key);
 391
 392     /// Changing nullability of field enrolmentkey on table groups to null
 393     $table = new XMLDBTable('groups');
 394     $field = new XMLDBField('enrolmentkey');
 395     $field->setAttributes(XMLDB_TYPE_CHAR, '50', null, null, null, null, null, null, 'description');
 396     $result = $result && change_field_notnull($table, $field);
 397 //==================
 398
 399 /// Now, rename 'groups_groupings_groups' to 'groupings_groups' and add keys
 400     $table = new XMLDBTable('groups_groupings_groups');
 401     $result = $result && rename_table($table, 'groupings_groups');
 402
 403     $table = new XMLDBTable('groupings_groups');
 404     $key = new XMLDBKey('groupingid');
 405     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupingid'), 'groupings', array('id'));
 406     $result = $result && add_key($table, $key);
 407
 408     $table = new XMLDBTable('groupings_groups');
 409     $key = new XMLDBKey('groupid');
 410     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
 411     $result = $result && add_key($table, $key);
 412
 413 ///=================
 414
 415 /// Transfer courseid from 'mdl_groups_courses_groups' to 'mdl_groups'.
 416     if ($result) {
 417         $sql = "UPDATE {$CFG->prefix}groups
 418                    SET courseid = (
 419                         SELECT MAX(courseid)
 420                           FROM {$CFG->prefix}groups_courses_groups gcg
 421                          WHERE gcg.groupid = {$CFG->prefix}groups.id)";
 422         execute_sql($sql);
 423     }
 424
 425 /// Transfer courseid from 'groups_courses_groupings' to 'mdl_groupings'.
 426     if ($result) {
 427         $sql = "UPDATE {$CFG->prefix}groupings
 428                    SET courseid = (
 429                         SELECT MAX(courseid)
 430                           FROM {$CFG->prefix}groups_courses_groupings gcg
 431                          WHERE gcg.groupingid = {$CFG->prefix}groupings.id)";
 432         execute_sql($sql);
 433     }
 434
 435 /// Drop the old tables
 436     if ($result) {
 437         drop_table(new XMLDBTable('groups_courses_groups'));
 438         drop_table(new XMLDBTable('groups_courses_groupings'));
 439         drop_table(new XMLDBTable('groups_temp'));
 440         drop_table(new XMLDBTable('groups_members_temp'));
 441         unset_config('group_version');
 442     }
 443
 444     return $result;
 445 }
 446
 447 /**
 448  * Drop keys & indexes for groups upgrade from 1.8.*
 449  */
 450 function upgrade_18_groups_drop_keys_indexes() {
 451     $result = true;
 452
 453 /// Define index groupid-courseid (unique) to be added to groups_members
 454     $table = new XMLDBTable('groups_members');
 455     $index = new XMLDBIndex('groupid-courseid');
 456     $index->setAttributes(XMLDB_INDEX_UNIQUE, array('groupid', 'userid'));
 457     $result = $result && drop_index($table, $index);
 458
 459 /// Define key courseid (foreign) to be added to groups_courses_groups
 460     $table = new XMLDBTable('groups_courses_groups');
 461     $key = new XMLDBKey('courseid');
 462     $key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 463     $result = $result && drop_key($table, $key);
 464
 465 /// Define key groupid (foreign) to be added to groups_courses_groups
 466     $table = new XMLDBTable('groups_courses_groups');
 467     $key = new XMLDBKey('groupid');
 468     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
 469     $result = $result && drop_key($table, $key);
 470
 471 /// Define index courseid-groupid (unique) to be added to groups_courses_groups
 472     $table = new XMLDBTable('groups_courses_groups');
 473     $index = new XMLDBIndex('courseid-groupid');
 474     $index->setAttributes(XMLDB_INDEX_UNIQUE, array('courseid', 'groupid'));
 475     $result = $result && drop_index($table, $index);
 476
 477 /// Define key courseid (foreign) to be added to groups_courses_groupings
 478     $table = new XMLDBTable('groups_courses_groupings');
 479     $key = new XMLDBKey('courseid');
 480     $key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 481     $result = $result && drop_key($table, $key);
 482
 483 /// Define key groupingid (foreign) to be added to groups_courses_groupings
 484     $table = new XMLDBTable('groups_courses_groupings');
 485     $key = new XMLDBKey('groupingid');
 486     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupingid'), 'groups_groupings', array('id'));
 487     $result = $result && drop_key($table, $key);
 488
 489 /// Define index courseid-groupingid (unique) to be added to groups_courses_groupings
 490     $table = new XMLDBTable('groups_courses_groupings');
 491     $index = new XMLDBIndex('courseid-groupingid');
 492     $index->setAttributes(XMLDB_INDEX_UNIQUE, array('courseid', 'groupingid'));
 493     $result = $result && drop_index($table, $index);
 494
 495
 496 /// Define key groupingid (foreign) to be added to groups_groupings_groups
 497     $table = new XMLDBTable('groups_groupings_groups');
 498     $key = new XMLDBKey('groupingid');
 499     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupingid'), 'groups_groupings', array('id'));
 500     $result = $result && drop_key($table, $key);
 501
 502 /// Define key groupid (foreign) to be added to groups_groupings_groups
 503     $table = new XMLDBTable('groups_groupings_groups');
 504     $key = new XMLDBKey('groupid');
 505     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
 506     $result = $result && drop_key($table, $key);
 507
 508 /// Define index groupingid-groupid (unique) to be added to groups_groupings_groups
 509     $table = new XMLDBTable('groups_groupings_groups');
 510     $index = new XMLDBIndex('groupingid-groupid');
 511     $index->setAttributes(XMLDB_INDEX_UNIQUE, array('groupingid', 'groupid'));
 512     $result = $result && drop_index($table, $index);
 513
 514     return $result;
 515 }
 516
 517 function upgrade_fix_category_depths() {
 518     global $CFG, $db;
 519
 520     // first fix incorrect parents
 521     $sql = "SELECT c.id
 522               FROM {$CFG->prefix}course_categories c
 523              WHERE c.parent > 0 AND c.parent NOT IN (SELECT pc.id FROM {$CFG->prefix}course_categories pc)";
 524     if ($rs = get_recordset_sql($sql)) {
 525         while ($cat = rs_fetch_next_record($rs)) {
 526             $cat->depth  = 1;
 527             $cat->path   = '/'.$cat->id;
 528             $cat->parent = 0;
 529             update_record('course_categories', $cat);
 530         }
 531         rs_close($rs);
 532     }
 533
 534     // now add path and depth to top level categories
 535     $sql = "UPDATE {$CFG->prefix}course_categories
 536                SET depth = 1, path = ".sql_concat("'/'", "id")."
 537              WHERE parent = 0";
 538     execute_sql($sql);
 539
 540     // now fix all other levels - slow but works in all supported dbs
 541     $parentdepth = 1;
 542     $db->debug = true;
 543     while (record_exists('course_categories', 'depth', 0)) {
 544         $sql = "SELECT c.id, pc.path
 545                   FROM {$CFG->prefix}course_categories c, {$CFG->prefix}course_categories pc
 546                  WHERE c.parent=pc.id AND c.depth=0 AND pc.depth=$parentdepth";
 547         if ($rs = get_recordset_sql($sql)) {
 548             while ($cat = rs_fetch_next_record($rs)) {
 549                 $cat->depth = $parentdepth+1;
 550                 $cat->path  = $cat->path.'/'.$cat->id;
 551                 update_record('course_categories', $cat);
 552             }
 553             rs_close($rs);
 554         }
 555         $parentdepth++;
 556         if ($parentdepth > 100) {
 557             //something must have gone wrong - nobody can have more than 100 levels of categories, right?
 558             debugging('Unknown error fixing category depths');
 559             break;
 560         }
 561     }
 562     $db->debug = true;
 563 }
 564
 565 ?>