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 /**
  12  * This function is used to migrade old data and settings from old gradebook into new grading system.
  13  * It is executed only once for each course during upgrade to 1.9, all grade tables must be empty initially.
  14  * @param int $courseid
  15  */
  16 function upgrade_18_gradebook($courseid) {
  17     global $CFG;
  18
  19     require_once($CFG->libdir.'/gradelib.php'); // we need constants only
  20
  21     // get all grade items with mod details and categories
  22     $sql = "SELECT gi.*, cm.idnumber as cmidnumber, m.name as modname
  23               FROM {$CFG->prefix}grade_item gi, {$CFG->prefix}course_modules cm, {$CFG->prefix}modules m
  24              WHERE gi.courseid=$courseid AND m.id=gi.modid AND cm.instance=gi.cminstance
  25           ORDER BY gi.sort_order ASC";
  26
  27     if (!$olditems = get_records_sql($sql)) {
  28         //nothing to do - no items present in old gradebook
  29         return true;
  30     }
  31
  32     if (!$oldcats = get_records('grade_category', 'courseid', $courseid, 'id')) {
  33         //there should be at least uncategorised category - hmm, nothing to do
  34         return true;
  35     }
  36
  37     $order = 1;
  38
  39     // create course category
  40     $course_category = new object();
  41     $course_category->courseid     = $courseid;
  42     $course_category->fullname     = 'course grade category';
  43     $course_category->parent       = null;
  44     $course_category->aggregation  = GRADE_AGGREGATE_MEAN_ALL;
  45     $course_category->timemodified = $course_category->timecreated = time();
  46     if (!$course_category->id = insert_record('grade_categories', $course_category)) {
  47         return false;
  48     }
  49     $course_category->depth = 1;
  50     $course_category->path  = '/'.$course_category->id;
  51     if (!update_record('grade_categories', $course_category)) {
  52         return false;
  53     }
  54
  55     // create course item
  56     $course_item = new object();
  57     $course_item->courseid     = $courseid;
  58     $course_item->itemtype     = 'course';
  59     $course_item->iteminstance = $course_category->id;
  60     $course_item->gradetype    = GRADE_TYPE_VALUE;
  61     $course_item->sortorder    = $order++;
  62     $course_item->timemodified = $course_item->timecreated = $course_category->timemodified;
  63     $course_item->needsupdate  = 1;
  64     if (!insert_record('grade_items', $course_item)) {
  65         return false;
  66     }
  67
  68     // existing categories
  69     $categories = array();
  70     $hiddenoldcats = array();
  71     if (count($oldcats) == 1) {
  72         $oldcat = reset($oldcats);
  73         if ($oldcat->drop_x_lowest) {
  74             $course_category->droplow = $oldcat->drop_x_lowest;
  75             update_record('grade_categories', $course_category);
  76         }
  77         $categories[$oldcat->id] = $course_category;
  78
  79     } else {
  80         foreach ($oldcats as $oldcat) {
  81             $category = new object();
  82             $category->courseid     = $courseid;
  83             $category->fullname     = addslashes($oldcat->name);
  84             $category->parent       = $course_category->id;
  85             $category->droplow      = $oldcat->drop_x_lowest;
  86             $category->aggregation  = GRADE_AGGREGATE_MEAN_ALL;
  87             $category->timemodified = $category->timecreated = time();
  88             if (!$category->id = insert_record('grade_categories', $category)) {
  89                 return false;
  90             }
  91             $category->depth = 2;
  92             $category->path  = '/'.$course_category->id.'/'.$category->id;
  93             if (!update_record('grade_categories', $category)) {
  94                 return false;
  95             }
  96
  97             $categories[$oldcat->id] = $category;
  98
  99             $item = new object();
 100             $item->courseid        = $courseid;
 101             $item->itemtype        = 'category';
 102             $item->iteminstance    = $category->id;
 103             $item->gradetype       = GRADE_TYPE_VALUE;
 104             $item->plusfactor      = $oldcat->bonus_points;
 105             $item->hidden          = $oldcat->hidden;
 106             $item->aggregationcoef = $oldcat->weight;
 107             $item->sortorder       = $order++;
 108             $item->timemodified    = $item->timecreated = $category->timemodified;
 109             $item->needsupdate     = 1;
 110             if (!insert_record('grade_items', $item)) {
 111                 return false;
 112             }
 113             if ($item->hidden) {
 114                 $hiddenoldcats[] = $oldcat->id;
 115             }
 116         }
 117
 118         $course_category->aggregation = GRADE_AGGREGATE_WEIGHTED_MEAN_ALL;
 119         update_record('grade_categories', $course_category);
 120     }
 121     unset($oldcats);
 122
 123     // existing items
 124     $newitems = array();
 125     foreach ($olditems as $olditem) {
 126         if (empty($categories[$olditem->category])) {
 127             continue; // faulty record
 128         }
 129         // proper data are set during activity upgrade or legacy grade fetching
 130         $item = new object();
 131         $item->courseid        = $courseid;
 132         $item->itemtype        = 'mod';
 133         $item->itemmodule      = $olditem->modname;
 134         $item->iteminstance    = $olditem->cminstance;
 135         $item->idnumber        = $olditem->cmidnumber;
 136         $item->itemname        = NULL;
 137         $item->itemnumber      = 0;
 138         $item->gradetype       = GRADE_TYPE_VALUE;
 139         $item->multfactor      = $olditem->scale_grade;
 140         $item->hidden          = (int)in_array($olditem->category, $hiddenoldcats);
 141         $item->aggregationcoef = $olditem->extra_credit;
 142         $item->sortorder       = $order++;
 143         $item->timemodified    = $item->timecreated = time();
 144         $item->needsupdate     = 1;
 145         $item->categoryid  = $categories[$olditem->category]->id;
 146         if (!$item->id = insert_record('grade_items', $item)) {
 147             return false;
 148         }
 149
 150         $newitems[$olditem->id] = $item;
 151
 152         if ($olditem->extra_credit and $categories[$olditem->category]->aggregation != GRADE_AGGREGATE_EXTRACREDIT_MEAN_ALL) {
 153             $categories[$olditem->category]->aggregation = GRADE_AGGREGATE_EXTRACREDIT_MEAN_ALL;
 154             update_record('grade_categories', $categories[$olditem->category]);
 155         }
 156     }
 157     unset($olditems);
 158
 159     // setup up exception handling - exclude grade from aggregation
 160     if ($exceptions = get_records('grade_exceptions', 'courseid', $courseid)) {
 161         foreach ($exceptions as $exception) {
 162             if (!array_key_exists($exception->grade_itemid, $newitems)) {
 163                 continue; // broken record
 164             }
 165             $grade = new object();
 166             $grade->excluded     = time();
 167             $grade->itemid       = $newitems[$exception->grade_itemid]->id;
 168             $grade->userid       = $exception->userid;
 169             $grade->timemodified = $grade->timecreated = $grade->excluded;
 170             insert_record('grade_grades', $grade);
 171         }
 172     }
 173
 174     return true;
 175 }
 176
 177
 178
 179 /**
 180  * Create new groupings tables for upgrade from 1.7.*|1.6.* and so on.
 181  */
 182 function upgrade_17_groups() {
 183     global $CFG;
 184
 185     $result = true;
 186
 187 /// Define table groupings to be created
 188     $table = new XMLDBTable('groupings');
 189
 190 /// Adding fields to table groupings
 191     $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
 192     $table->addFieldInfo('courseid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 193     $table->addFieldInfo('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null, null, null);
 194     $table->addFieldInfo('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null, null, null);
 195     $table->addFieldInfo('configdata', XMLDB_TYPE_TEXT, 'small', null, null, null, null, null, null);
 196     $table->addFieldInfo('timecreated', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 197     $table->addFieldInfo('timemodified', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 198
 199 /// Adding keys to table groupings
 200     $table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'));
 201     $table->addKeyInfo('courseid', XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 202
 203 /// Launch create table for groupings
 204     $result = $result && create_table($table);
 205
 206 // ==========================================
 207
 208 /// Define table groupings_groups to be created
 209     $table = new XMLDBTable('groupings_groups');
 210
 211 /// Adding fields to table groupings_groups
 212     $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
 213     $table->addFieldInfo('groupingid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 214     $table->addFieldInfo('groupid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 215     $table->addFieldInfo('timeadded', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
 216
 217 /// Adding keys to table groupings_groups
 218     $table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'));
 219     $table->addKeyInfo('groupingid', XMLDB_KEY_FOREIGN, array('groupingid'), 'groupings', array('id'));
 220     $table->addKeyInfo('groupid', XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
 221
 222 /// Launch create table for groupings_groups
 223     $result = $result && create_table($table);
 224
 225 /// fix not null constrain
 226     $table = new XMLDBTable('groups');
 227     $field = new XMLDBField('password');
 228     $field->setAttributes(XMLDB_TYPE_CHAR, '50', null, null, null, null, null, null, 'description');
 229     $result = $result && change_field_notnull($table, $field);
 230
 231 /// Rename field password in table groups to enrolmentkey
 232     $table = new XMLDBTable('groups');
 233     $field = new XMLDBField('password');
 234     $field->setAttributes(XMLDB_TYPE_CHAR, '50', null, null, null, null, null, null, 'description');
 235     $result = $result && rename_field($table, $field, 'enrolmentkey');
 236
 237     return $result;
 238 }
 239
 240 /**
 241  * Drop, add fields and rename tables for groups upgrade from 1.8.*
 242  * @param XMLDBTable $table 'groups_groupings' table object.
 243  */
 244 function upgrade_18_groups() {
 245     global $CFG, $db;
 246
 247     $result = upgrade_18_groups_drop_keys_indexes();
 248
 249 /// Delete not used columns
 250     $fields_r = array('viewowngroup', 'viewallgroupsmembers', 'viewallgroupsactivities',
 251                       'teachersgroupmark', 'teachersgroupview', 'teachersoverride', 'teacherdeletable');
 252     foreach ($fields_r as $fname) {
 253         $table = new XMLDBTable('groups_groupings');
 254         $field = new XMLDBField($fname);
 255         if (field_exists($table, $field)) {
 256             $result = $result && drop_field($table, $field);
 257         }
 258     }
 259
 260 /// Rename 'groups_groupings' to 'groupings'
 261     $table = new XMLDBTable('groups_groupings');
 262     $result = $result && rename_table($table, 'groupings');
 263
 264 /// Add columns/key 'courseid', exclusivegroups, maxgroupsize, timemodified.
 265     $table = new XMLDBTable('groupings');
 266     $field = new XMLDBField('courseid');
 267     $field->setAttributes(XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null, null, '0', 'id');
 268     $result = $result && add_field($table, $field);
 269
 270     $table = new XMLDBTable('groupings');
 271     $key = new XMLDBKey('courseid');
 272     $key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 273     $result = $result && add_key($table, $key);
 274
 275     $table = new XMLDBTable('groupings');
 276     $field = new XMLDBField('configdata');
 277     $field->setAttributes(XMLDB_TYPE_TEXT, 'small', null, null, null, null, null, null, 'description');
 278     $result = $result && add_field($table, $field);
 279
 280     $table = new XMLDBTable('groupings');
 281     $field = new XMLDBField('timemodified');
 282     $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'timecreated');
 283     $result = $result && add_field($table, $field);
 284
 285 //==================
 286
 287 /// Add columns/key 'courseid' into groups table
 288     $table = new XMLDBTable('groups');
 289     $field = new XMLDBField('courseid');
 290     $field->setAttributes(XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null, null, '0', 'id');
 291     $result = $result && add_field($table, $field);
 292
 293     $table = new XMLDBTable('groups');
 294     $key = new XMLDBKey('courseid');
 295     $key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 296     $result = $result && add_key($table, $key);
 297
 298     /// Changing nullability of field enrolmentkey on table groups to null
 299     $table = new XMLDBTable('groups');
 300     $field = new XMLDBField('enrolmentkey');
 301     $field->setAttributes(XMLDB_TYPE_CHAR, '50', null, null, null, null, null, null, 'description');
 302     $result = $result && change_field_notnull($table, $field);
 303 //==================
 304
 305 /// Now, rename 'groups_groupings_groups' to 'groupings_groups' and add keys
 306     $table = new XMLDBTable('groups_groupings_groups');
 307     $result = $result && rename_table($table, 'groupings_groups');
 308
 309     $table = new XMLDBTable('groupings_groups');
 310     $key = new XMLDBKey('groupingid');
 311     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupingid'), 'groupings', array('id'));
 312     $result = $result && add_key($table, $key);
 313
 314     $table = new XMLDBTable('groupings_groups');
 315     $key = new XMLDBKey('groupid');
 316     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
 317     $result = $result && add_key($table, $key);
 318
 319 ///=================
 320
 321 /// Transfer courseid from 'mdl_groups_courses_groups' to 'mdl_groups'.
 322     if ($result) {
 323         $sql = "UPDATE {$CFG->prefix}groups g
 324                    SET courseid = (
 325                         SELECT MAX(courseid)
 326                           FROM {$CFG->prefix}groups_courses_groups gcg
 327                          WHERE gcg.groupid = g.id)";
 328         execute_sql($sql);
 329     }
 330
 331 /// Transfer courseid from 'groups_courses_groupings' to 'mdl_groupings'.
 332     if ($result) {
 333         $sql = "UPDATE {$CFG->prefix}groupings g
 334                    SET courseid = (
 335                         SELECT MAX(courseid)
 336                           FROM {$CFG->prefix}groups_courses_groupings gcg
 337                          WHERE gcg.groupingid = g.id)";
 338         execute_sql($sql);
 339     }
 340
 341 /// Drop the old tables
 342     if ($result) {
 343         drop_table(new XMLDBTable('groups_courses_groups'));
 344         drop_table(new XMLDBTable('groups_courses_groupings'));
 345         drop_table(new XMLDBTable('groups_temp'));
 346         drop_table(new XMLDBTable('groups_members_temp'));
 347         unset_config('group_version');
 348     }
 349
 350     return $result;
 351 }
 352
 353 /**
 354  * Drop keys & indexes for groups upgrade from 1.8.*
 355  */
 356 function upgrade_18_groups_drop_keys_indexes() {
 357     $result = true;
 358
 359 /// Define index groupid-courseid (unique) to be added to groups_members
 360     $table = new XMLDBTable('groups_members');
 361     $index = new XMLDBIndex('groupid-courseid');
 362     $index->setAttributes(XMLDB_INDEX_UNIQUE, array('groupid', 'userid'));
 363     $result = $result && drop_index($table, $index);
 364
 365 /// Define key courseid (foreign) to be added to groups_courses_groups
 366     $table = new XMLDBTable('groups_courses_groups');
 367     $key = new XMLDBKey('courseid');
 368     $key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 369     $result = $result && drop_key($table, $key);
 370
 371 /// Define key groupid (foreign) to be added to groups_courses_groups
 372     $table = new XMLDBTable('groups_courses_groups');
 373     $key = new XMLDBKey('groupid');
 374     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
 375     $result = $result && drop_key($table, $key);
 376
 377 /// Define index courseid-groupid (unique) to be added to groups_courses_groups
 378     $table = new XMLDBTable('groups_courses_groups');
 379     $index = new XMLDBIndex('courseid-groupid');
 380     $index->setAttributes(XMLDB_INDEX_UNIQUE, array('courseid', 'groupid'));
 381     $result = $result && drop_index($table, $index);
 382
 383 /// Define key courseid (foreign) to be added to groups_courses_groupings
 384     $table = new XMLDBTable('groups_courses_groupings');
 385     $key = new XMLDBKey('courseid');
 386     $key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
 387     $result = $result && drop_key($table, $key);
 388
 389 /// Define key groupingid (foreign) to be added to groups_courses_groupings
 390     $table = new XMLDBTable('groups_courses_groupings');
 391     $key = new XMLDBKey('groupingid');
 392     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupingid'), 'groups_groupings', array('id'));
 393     $result = $result && drop_key($table, $key);
 394
 395 /// Define index courseid-groupingid (unique) to be added to groups_courses_groupings
 396     $table = new XMLDBTable('groups_courses_groupings');
 397     $index = new XMLDBIndex('courseid-groupingid');
 398     $index->setAttributes(XMLDB_INDEX_UNIQUE, array('courseid', 'groupingid'));
 399     $result = $result && drop_index($table, $index);
 400
 401
 402 /// Define key groupingid (foreign) to be added to groups_groupings_groups
 403     $table = new XMLDBTable('groups_groupings_groups');
 404     $key = new XMLDBKey('groupingid');
 405     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupingid'), 'groups_groupings', array('id'));
 406     $result = $result && drop_key($table, $key);
 407
 408 /// Define key groupid (foreign) to be added to groups_groupings_groups
 409     $table = new XMLDBTable('groups_groupings_groups');
 410     $key = new XMLDBKey('groupid');
 411     $key->setAttributes(XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
 412     $result = $result && drop_key($table, $key);
 413
 414 /// Define index groupingid-groupid (unique) to be added to groups_groupings_groups
 415     $table = new XMLDBTable('groups_groupings_groups');
 416     $index = new XMLDBIndex('groupingid-groupid');
 417     $index->setAttributes(XMLDB_INDEX_UNIQUE, array('groupingid', 'groupid'));
 418     $result = $result && drop_index($table, $index);
 419
 420     return $result;
 421 }
 422
 423 ?>