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 dabase 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     = get_string('coursegradecategory', 'grades');
 133     $course_category->parent       = null;
 134     $course_category->aggregation  = GRADE_AGGREGATE_MEAN;
 135     $course_category->timemodified = $course_category->timecreated = time();
 136     if (!$course_category->id = insert_record('grade_categories', $course_category)) {
 137         return false;
 138     }
 139     $course_category->depth = 1;
 140     $course_category->path  = '/'.$course_category->id;
 141     if (!update_record('grade_categories', $course_category)) {
 142         return false;
 143     }
 144
 145     // create course item
 146     $course_item = new object();
 147     $course_item->courseid     = $courseid;
 148     $course_item->itemtype     = 'course';
 149     $course_item->iteminstance = $course_category->id;
 150     $course_item->gradetype    = GRADE_TYPE_VALUE;
 151     $course_item->sortorder    = $order++;
 152     $course_item->timemodified = $course_item->timecreated = $course_category->timemodified;
 153     $course_item->needsupdate  = 1;
 154     if (!insert_record('grade_items', $course_item)) {
 155         return false;
 156     }
 157
 158     // existing categories
 159     $categories = array();
 160     $hiddenoldcats = array();
 161     if (count($oldcats) == 1) {
 162         $oldcat = reset($oldcats);
 163         if ($oldcat->drop_x_lowest) {
 164             $course_category->droplow = $oldcat->drop_x_lowest;
 165             update_record('grade_categories', $course_category);
 166         }
 167         $categories[$oldcat->id] = $course_category;
 168
 169     } else {
 170         foreach ($oldcats as $oldcat) {
 171             $category = new object();
 172             $category->courseid     = $courseid;
 173             $category->fullname     = addslashes($oldcat->name);
 174             $category->parent       = $course_category->id;
 175             $category->droplow      = $oldcat->drop_x_lowest;
 176             $category->aggregation  = GRADE_AGGREGATE_MEAN;
 177             $category->timemodified = $category->timecreated = time();
 178             if (!$category->id = insert_record('grade_categories', $category)) {
 179                 return false;
 180             }
 181             $category->depth = 2;
 182             $category->path  = '/'.$course_category->id.'/'.$category->id;
 183             if (!update_record('grade_categories', $category)) {
 184                 return false;
 185             }
 186
 187             $categories[$oldcat->id] = $category;
 188
 189             $item = new object();
 190             $item->courseid        = $courseid;
 191             $item->itemtype        = 'category';
 192             $item->iteminstance    = $category->id;
 193             $item->gradetype       = GRADE_TYPE_VALUE;
 194             $item->plusfactor      = $oldcat->bonus_points;
 195             $item->hidden          = $oldcat->hidden;
 196             $item->aggregationcoef = $oldcat->weight;
 197             $item->sortorder       = $order++;
 198             $item->timemodified    = $item->timecreated = $category->timemodified;
 199             $item->needsupdate     = 1;
 200             if (!insert_record('grade_items', $item)) {
 201                 return false;
 202             }
 203             if ($item->hidden) {
 204                 $hiddenoldcats[] = $oldcat->id;
 205             }
 206         }
 207
 208         $course_category->aggregation = GRADE_AGGREGATE_WEIGHTED_MEAN;
 209         update_record('grade_categories', $course_category);
 210     }
 211     unset($oldcats);
 212
 213     // existing items
 214     $newitems = array();
 215     foreach ($olditems as $olditem) {
 216         if (empty($categories[$olditem->category])) {
 217             continue; // faulty record
 218         }
 219         // proper data are set during activity upgrade or legacy grade fetching
 220         $item = new object();
 221         $item->courseid        = $courseid;
 222         $item->itemtype        = 'mod';
 223         $item->itemmodule      = $olditem->modname;
 224         $item->iteminstance    = $olditem->cminstance;
 225         $item->idnumber        = $olditem->cmidnumber;
 226         $item->itemname        = NULL;
 227         $item->itemnumber      = 0;
 228         $item->gradetype       = GRADE_TYPE_VALUE;
 229         $item->multfactor      = $olditem->scale_grade;
 230         $item->hidden          = (int)in_array($olditem->category, $hiddenoldcats);
 231         $item->aggregationcoef = $olditem->extra_credit;
 232         $item->sortorder       = $order++;
 233         $item->timemodified    = $item->timecreated = time();
 234         $item->needsupdate     = 1;
 235         $item->categoryid  = $categories[$olditem->category]->id;
 236         if (!$item->id = insert_record('grade_items', $item)) {
 237             return false;
 238         }
 239
 240         $newitems[$olditem->id] = $item;
 241
 242         if ($olditem->extra_credit and $categories[$olditem->category]->aggregation != GRADE_AGGREGATE_EXTRACREDIT_MEAN) {
 243             $categories[$olditem->category]->aggregation = GRADE_AGGREGATE_EXTRACREDIT_MEAN;
 244             update_record('grade_categories', $categories[$olditem->category]);
 245         }
 246     }
 247     unset($olditems);
 248
 249     // setup up exception handling - exclude grade from aggregation
 250     if ($exceptions = get_records('grade_exceptions', 'courseid', $courseid)) {
 251         foreach ($exceptions as $exception) {
 252             if (!array_key_exists($exception->grade_itemid, $newitems)) {
 253                 continue; // broken record
 254             }
 255             $grade = new object();
 256             $grade->excluded     = time();
 257             $grade->itemid       = $newitems[$exception->grade_itemid]->id;
 258             $grade->userid       = $exception->userid;
 259             $grade->timemodified = $grade->timecreated = $grade->excluded;
 260             insert_record('grade_grades', $grade);
 261         }
 262     }
 263
 264     return true;
 265 }
 266
 267
 268
 269 /**
 270  * Create new groupings tables for upgrade from 1.7.*|1.6.* and so on.
 271  */
 272 function upgrade_17_groups() {
 273     global $CFG;
 274
 275     $result = true;
 276
 277 /// Define table groupings to be created
 278     $table = new XMLDBTable('groupings');
 279
 280 /// Adding fields to table groupings
 281     $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
 282     $table->addFieldInfo('courseid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 283     $table->addFieldInfo('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null, null, null);
 284     $table->addFieldInfo('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null, null, null);
 285     $table->addFieldInfo('configdata', XMLDB_TYPE_TEXT, 'small', null, null, null, null, null, null);
 286     $table->addFieldInfo('timecreated', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 287     $table->addFieldInfo('timemodified', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 288
 289 /// Adding keys to table groupings
 290     $table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'));
 291     $table->addKeyInfo('courseid', XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 292
 293 /// Launch create table for groupings
 294     $result = $result && create_table($table);
 295
 296 // ==========================================
 297
 298 /// Define table groupings_groups to be created
 299     $table = new XMLDBTable('groupings_groups');
 300
 301 /// Adding fields to table groupings_groups
 302     $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
 303     $table->addFieldInfo('groupingid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 304     $table->addFieldInfo('groupid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 305     $table->addFieldInfo('timeadded', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 306
 307 /// Adding keys to table groupings_groups
 308     $table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'));
 309     $table->addKeyInfo('groupingid', XMLDB_KEY_FOREIGN, array('groupingid'), 'groupings', array('id'));
 310     $table->addKeyInfo('groupid', XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
 311
 312 /// Launch create table for groupings_groups
 313     $result = $result && create_table($table);
 314
 315 /// fix not null constrain
 316     $table = new XMLDBTable('groups');
 317     $field = new XMLDBField('password');
 318     $field->setAttributes(XMLDB_TYPE_CHAR, '50', null, null, null, null, null, null, 'description');
 319     $result = $result && change_field_notnull($table, $field);
 320
 321 /// Rename field password in table groups to enrolmentkey
 322     $table = new XMLDBTable('groups');
 323     $field = new XMLDBField('password');
 324     $field->setAttributes(XMLDB_TYPE_CHAR, '50', null, null, null, null, null, null, 'description');
 325     $result = $result && rename_field($table, $field, 'enrolmentkey');
 326
 327     return $result;
 328 }
 329
 330 /**
 331  * Drop, add fields and rename tables for groups upgrade from 1.8.*
 332  * @param XMLDBTable $table 'groups_groupings' table object.
 333  */
 334 function upgrade_18_groups() {
 335     global $CFG, $db;
 336
 337     $result = upgrade_18_groups_drop_keys_indexes();
 338
 339 /// Delete not used columns
 340     $fields_r = array('viewowngroup', 'viewallgroupsmembers', 'viewallgroupsactivities',
 341                       'teachersgroupmark', 'teachersgroupview', 'teachersoverride', 'teacherdeletable');
 342     foreach ($fields_r as $fname) {
 343         $table = new XMLDBTable('groups_groupings');
 344         $field = new XMLDBField($fname);
 345         if (field_exists($table, $field)) {
 346             $result = $result && drop_field($table, $field);
 347         }
 348     }
 349
 350 /// Rename 'groups_groupings' to 'groupings'
 351     $table = new XMLDBTable('groups_groupings');
 352     $result = $result && rename_table($table, 'groupings');
 353
 354 /// Add columns/key 'courseid', exclusivegroups, maxgroupsize, timemodified.
 355     $table = new XMLDBTable('groupings');
 356     $field = new XMLDBField('courseid');
 357     $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'id');
 358     $result = $result && add_field($table, $field);
 359
 360     $table = new XMLDBTable('groupings');
 361     $key = new XMLDBKey('courseid');
 362     $key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 363     $result = $result && add_key($table, $key);
 364
 365     $table = new XMLDBTable('groupings');
 366     $field = new XMLDBField('configdata');
 367     $field->setAttributes(XMLDB_TYPE_TEXT, 'small', null, null, null, null, null, null, 'description');
 368     $result = $result && add_field($table, $field);
 369
 370     $table = new XMLDBTable('groupings');
 371     $field = new XMLDBField('timemodified');
 372     $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'timecreated');
 373     $result = $result && add_field($table, $field);
 374
 375 //==================
 376
 377 /// Add columns/key 'courseid' into groups table
 378     $table = new XMLDBTable('groups');
 379     $field = new XMLDBField('courseid');
 380     $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'id');
 381     $result = $result && add_field($table, $field);
 382
 383     $table = new XMLDBTable('groups');
 384     $key = new XMLDBKey('courseid');
 385     $key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 386     $result = $result && add_key($table, $key);
 387
 388     /// Changing nullability of field enrolmentkey on table groups to null
 389     $table = new XMLDBTable('groups');
 390     $field = new XMLDBField('enrolmentkey');
 391     $field->setAttributes(XMLDB_TYPE_CHAR, '50', null, null, null, null, null, null, 'description');
 392     $result = $result && change_field_notnull($table, $field);
 393 //==================
 394
 395 /// Now, rename 'groups_groupings_groups' to 'groupings_groups' and add keys
 396     $table = new XMLDBTable('groups_groupings_groups');
 397     $result = $result && rename_table($table, 'groupings_groups');
 398
 399     $table = new XMLDBTable('groupings_groups');
 400     $key = new XMLDBKey('groupingid');
 401     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupingid'), 'groupings', array('id'));
 402     $result = $result && add_key($table, $key);
 403
 404     $table = new XMLDBTable('groupings_groups');
 405     $key = new XMLDBKey('groupid');
 406     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
 407     $result = $result && add_key($table, $key);
 408
 409 ///=================
 410
 411 /// Transfer courseid from 'mdl_groups_courses_groups' to 'mdl_groups'.
 412     if ($result) {
 413         $sql = "UPDATE {$CFG->prefix}groups
 414                    SET courseid = (
 415                         SELECT MAX(courseid)
 416                           FROM {$CFG->prefix}groups_courses_groups gcg
 417                          WHERE gcg.groupid = {$CFG->prefix}groups.id)";
 418         execute_sql($sql);
 419     }
 420
 421 /// Transfer courseid from 'groups_courses_groupings' to 'mdl_groupings'.
 422     if ($result) {
 423         $sql = "UPDATE {$CFG->prefix}groupings
 424                    SET courseid = (
 425                         SELECT MAX(courseid)
 426                           FROM {$CFG->prefix}groups_courses_groupings gcg
 427                          WHERE gcg.groupingid = {$CFG->prefix}groupings.id)";
 428         execute_sql($sql);
 429     }
 430
 431 /// Drop the old tables
 432     if ($result) {
 433         drop_table(new XMLDBTable('groups_courses_groups'));
 434         drop_table(new XMLDBTable('groups_courses_groupings'));
 435         drop_table(new XMLDBTable('groups_temp'));
 436         drop_table(new XMLDBTable('groups_members_temp'));
 437         unset_config('group_version');
 438     }
 439
 440     return $result;
 441 }
 442
 443 /**
 444  * Drop keys & indexes for groups upgrade from 1.8.*
 445  */
 446 function upgrade_18_groups_drop_keys_indexes() {
 447     $result = true;
 448
 449 /// Define index groupid-courseid (unique) to be added to groups_members
 450     $table = new XMLDBTable('groups_members');
 451     $index = new XMLDBIndex('groupid-courseid');
 452     $index->setAttributes(XMLDB_INDEX_UNIQUE, array('groupid', 'userid'));
 453     $result = $result && drop_index($table, $index);
 454
 455 /// Define key courseid (foreign) to be added to groups_courses_groups
 456     $table = new XMLDBTable('groups_courses_groups');
 457     $key = new XMLDBKey('courseid');
 458     $key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 459     $result = $result && drop_key($table, $key);
 460
 461 /// Define key groupid (foreign) to be added to groups_courses_groups
 462     $table = new XMLDBTable('groups_courses_groups');
 463     $key = new XMLDBKey('groupid');
 464     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
 465     $result = $result && drop_key($table, $key);
 466
 467 /// Define index courseid-groupid (unique) to be added to groups_courses_groups
 468     $table = new XMLDBTable('groups_courses_groups');
 469     $index = new XMLDBIndex('courseid-groupid');
 470     $index->setAttributes(XMLDB_INDEX_UNIQUE, array('courseid', 'groupid'));
 471     $result = $result && drop_index($table, $index);
 472
 473 /// Define key courseid (foreign) to be added to groups_courses_groupings
 474     $table = new XMLDBTable('groups_courses_groupings');
 475     $key = new XMLDBKey('courseid');
 476     $key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 477     $result = $result && drop_key($table, $key);
 478
 479 /// Define key groupingid (foreign) to be added to groups_courses_groupings
 480     $table = new XMLDBTable('groups_courses_groupings');
 481     $key = new XMLDBKey('groupingid');
 482     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupingid'), 'groups_groupings', array('id'));
 483     $result = $result && drop_key($table, $key);
 484
 485 /// Define index courseid-groupingid (unique) to be added to groups_courses_groupings
 486     $table = new XMLDBTable('groups_courses_groupings');
 487     $index = new XMLDBIndex('courseid-groupingid');
 488     $index->setAttributes(XMLDB_INDEX_UNIQUE, array('courseid', 'groupingid'));
 489     $result = $result && drop_index($table, $index);
 490
 491
 492 /// Define key groupingid (foreign) to be added to groups_groupings_groups
 493     $table = new XMLDBTable('groups_groupings_groups');
 494     $key = new XMLDBKey('groupingid');
 495     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupingid'), 'groups_groupings', array('id'));
 496     $result = $result && drop_key($table, $key);
 497
 498 /// Define key groupid (foreign) to be added to groups_groupings_groups
 499     $table = new XMLDBTable('groups_groupings_groups');
 500     $key = new XMLDBKey('groupid');
 501     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
 502     $result = $result && drop_key($table, $key);
 503
 504 /// Define index groupingid-groupid (unique) to be added to groups_groupings_groups
 505     $table = new XMLDBTable('groups_groupings_groups');
 506     $index = new XMLDBIndex('groupingid-groupid');
 507     $index->setAttributes(XMLDB_INDEX_UNIQUE, array('groupingid', 'groupid'));
 508     $result = $result && drop_index($table, $index);
 509
 510     return $result;
 511 }
 512
 513 ?>