1
<?php //$Id: upgradelib.php,v 1.9.2.6 2008/08/31 20:59:14 skodak Exp $
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!)
11
* Migrates the grade_letter data to grade_letters
13
function upgrade_18_letters() {
16
$table = new XMLDBTable('grade_letters');
18
if (table_exists($table)) {
19
// already converted or development site
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');
30
/// Launch rename field grade_low
31
$result = $result && rename_field($table, $field, 'lowerboundary');
33
/// Define field grade_high to be dropped from grade_letter
34
$table = new XMLDBTable('grade_letter');
35
$field = new XMLDBField('grade_high');
37
/// Launch drop field grade_high
38
$result = $result && drop_field($table, $field);
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'));
45
/// Launch drop index courseid
46
$result = $result && drop_index($table, $index);
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');
53
/// Launch rename field courseid
54
$result = $result && rename_field($table, $field, 'contextid');
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)";
62
/// remove broken records
63
execute_sql("DELETE FROM {$CFG->prefix}grade_letter WHERE contextid=0");
65
/// Define table grade_letter to be renamed to grade_letters
66
$table = new XMLDBTable('grade_letter');
68
/// Launch rename table for grade_letter
69
$result = $result && rename_table($table, 'grade_letters');
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');
76
/// Launch change of type for field lowerboundary
77
$result = $result && change_field_precision($table, $field);
78
$result = $result && change_field_default($table, $field);
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');
85
/// Launch change of default for field letter
86
$result = $result && change_field_precision($table, $field);
87
$result = $result && change_field_default($table, $field);
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'));
94
/// Launch add index contextidlowerboundary
95
$result = $result && add_index($table, $index);
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
106
function upgrade_18_gradebook($courseid) {
109
require_once($CFG->libdir.'/gradelib.php'); // we need constants only
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";
117
if (!$olditems = get_records_sql($sql)) {
118
//nothing to do - no items present in old gradebook
122
if (!$oldcats = get_records('grade_category', 'courseid', $courseid, 'id')) {
123
//there should be at least uncategorised category - hmm, nothing to do
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)) {
140
$course_category->depth = 1;
141
$course_category->path = '/'.$course_category->id;
142
if (!update_record('grade_categories', $course_category)) {
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)) {
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);
169
$categories[$oldcat->id] = $course_category;
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)) {
184
$category->depth = 2;
185
$category->path = '/'.$course_category->id.'/'.$category->id;
186
if (!update_record('grade_categories', $category)) {
190
$categories[$oldcat->id] = $category;
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)) {
208
$hiddenoldcats[] = $oldcat->id;
212
$course_category->aggregation = GRADE_AGGREGATE_WEIGHTED_MEAN2;
213
update_record('grade_categories', $course_category);
219
foreach ($olditems as $olditem) {
220
if (empty($categories[$olditem->category])) {
221
continue; // faulty record
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)) {
244
$newitems[$olditem->id] = $item;
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]);
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
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);
274
* Create new groupings tables for upgrade from 1.7.*|1.6.* and so on.
276
function upgrade_17_groups() {
281
/// Define table groupings to be created
282
$table = new XMLDBTable('groupings');
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');
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'));
297
/// Launch create table for groupings
298
$result = $result && create_table($table);
300
// ==========================================
302
/// Define table groupings_groups to be created
303
$table = new XMLDBTable('groupings_groups');
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');
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'));
316
/// Launch create table for groupings_groups
317
$result = $result && create_table($table);
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);
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');
335
* Try to fix broken groups from 1.8 - at least partially
337
function upgrade_18_broken_groups() {
340
/// Undo password -> enrolmentkey
341
$table = new XMLDBTable('groups');
342
$field = new XMLDBField('enrolmentkey');
343
$field->setAttributes(XMLDB_TYPE_CHAR, '50', null, XMLDB_NOTNULL, null, null, null, null, 'description');
344
rename_field($table, $field, 'password');
347
/// Readd courseid field
348
$table = new XMLDBTable('groups');
349
$field = new XMLDBField('courseid');
350
$field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'id');
351
add_field($table, $field);
354
$table = new XMLDBTable('groups');
355
$key = new XMLDBKey('courseid');
356
$key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
357
add_key($table, $key);
361
* Drop, add fields and rename tables for groups upgrade from 1.8.*
362
* @param XMLDBTable $table 'groups_groupings' table object.
364
function upgrade_18_groups() {
367
$result = upgrade_18_groups_drop_keys_indexes();
369
/// Delete not used columns
370
$fields_r = array('viewowngroup', 'viewallgroupsmembers', 'viewallgroupsactivities',
371
'teachersgroupmark', 'teachersgroupview', 'teachersoverride', 'teacherdeletable');
372
foreach ($fields_r as $fname) {
373
$table = new XMLDBTable('groups_groupings');
374
$field = new XMLDBField($fname);
375
if (field_exists($table, $field)) {
376
$result = $result && drop_field($table, $field);
380
/// Rename 'groups_groupings' to 'groupings'
381
$table = new XMLDBTable('groups_groupings');
382
$result = $result && rename_table($table, 'groupings');
384
/// Add columns/key 'courseid', exclusivegroups, maxgroupsize, timemodified.
385
$table = new XMLDBTable('groupings');
386
$field = new XMLDBField('courseid');
387
$field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'id');
388
$result = $result && add_field($table, $field);
390
$table = new XMLDBTable('groupings');
391
$key = new XMLDBKey('courseid');
392
$key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
393
$result = $result && add_key($table, $key);
395
$table = new XMLDBTable('groupings');
396
$field = new XMLDBField('configdata');
397
$field->setAttributes(XMLDB_TYPE_TEXT, 'small', null, null, null, null, null, null, 'description');
398
$result = $result && add_field($table, $field);
400
$table = new XMLDBTable('groupings');
401
$field = new XMLDBField('timemodified');
402
$field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'timecreated');
403
$result = $result && add_field($table, $field);
407
/// Add columns/key 'courseid' into groups table
408
$table = new XMLDBTable('groups');
409
$field = new XMLDBField('courseid');
410
$field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'id');
411
$result = $result && add_field($table, $field);
413
$table = new XMLDBTable('groups');
414
$key = new XMLDBKey('courseid');
415
$key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
416
$result = $result && add_key($table, $key);
418
/// Changing nullability of field enrolmentkey on table groups to null
419
$table = new XMLDBTable('groups');
420
$field = new XMLDBField('enrolmentkey');
421
$field->setAttributes(XMLDB_TYPE_CHAR, '50', null, null, null, null, null, null, 'description');
422
$result = $result && change_field_notnull($table, $field);
425
/// Now, rename 'groups_groupings_groups' to 'groupings_groups' and add keys
426
$table = new XMLDBTable('groups_groupings_groups');
427
$result = $result && rename_table($table, 'groupings_groups');
429
$table = new XMLDBTable('groupings_groups');
430
$key = new XMLDBKey('groupingid');
431
$key->setAttributes(XMLDB_KEY_FOREIGN, array('groupingid'), 'groupings', array('id'));
432
$result = $result && add_key($table, $key);
434
$table = new XMLDBTable('groupings_groups');
435
$key = new XMLDBKey('groupid');
436
$key->setAttributes(XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
437
$result = $result && add_key($table, $key);
441
/// Transfer courseid from 'mdl_groups_courses_groups' to 'mdl_groups'.
443
$sql = "UPDATE {$CFG->prefix}groups
446
FROM {$CFG->prefix}groups_courses_groups gcg
447
WHERE gcg.groupid = {$CFG->prefix}groups.id)";
451
/// Transfer courseid from 'groups_courses_groupings' to 'mdl_groupings'.
453
$sql = "UPDATE {$CFG->prefix}groupings
456
FROM {$CFG->prefix}groups_courses_groupings gcg
457
WHERE gcg.groupingid = {$CFG->prefix}groupings.id)";
461
/// Drop the old tables
463
drop_table(new XMLDBTable('groups_courses_groups'));
464
drop_table(new XMLDBTable('groups_courses_groupings'));
465
drop_table(new XMLDBTable('groups_temp'));
466
drop_table(new XMLDBTable('groups_members_temp'));
467
unset_config('group_version');
474
* Drop keys & indexes for groups upgrade from 1.8.*
476
function upgrade_18_groups_drop_keys_indexes() {
479
/// Define index groupid-courseid (unique) to be added to groups_members
480
$table = new XMLDBTable('groups_members');
481
$index = new XMLDBIndex('groupid-courseid');
482
$index->setAttributes(XMLDB_INDEX_UNIQUE, array('groupid', 'userid'));
483
$result = $result && drop_index($table, $index);
485
/// Define key courseid (foreign) to be added to groups_courses_groups
486
$table = new XMLDBTable('groups_courses_groups');
487
$key = new XMLDBKey('courseid');
488
$key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
489
$result = $result && drop_key($table, $key);
491
/// Define key groupid (foreign) to be added to groups_courses_groups
492
$table = new XMLDBTable('groups_courses_groups');
493
$key = new XMLDBKey('groupid');
494
$key->setAttributes(XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
495
$result = $result && drop_key($table, $key);
497
/// Define index courseid-groupid (unique) to be added to groups_courses_groups
498
$table = new XMLDBTable('groups_courses_groups');
499
$index = new XMLDBIndex('courseid-groupid');
500
$index->setAttributes(XMLDB_INDEX_UNIQUE, array('courseid', 'groupid'));
501
$result = $result && drop_index($table, $index);
503
/// Define key courseid (foreign) to be added to groups_courses_groupings
504
$table = new XMLDBTable('groups_courses_groupings');
505
$key = new XMLDBKey('courseid');
506
$key->setAttributes(XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
507
$result = $result && drop_key($table, $key);
509
/// Define key groupingid (foreign) to be added to groups_courses_groupings
510
$table = new XMLDBTable('groups_courses_groupings');
511
$key = new XMLDBKey('groupingid');
512
$key->setAttributes(XMLDB_KEY_FOREIGN, array('groupingid'), 'groups_groupings', array('id'));
513
$result = $result && drop_key($table, $key);
515
/// Define index courseid-groupingid (unique) to be added to groups_courses_groupings
516
$table = new XMLDBTable('groups_courses_groupings');
517
$index = new XMLDBIndex('courseid-groupingid');
518
$index->setAttributes(XMLDB_INDEX_UNIQUE, array('courseid', 'groupingid'));
519
$result = $result && drop_index($table, $index);
522
/// Define key groupingid (foreign) to be added to groups_groupings_groups
523
$table = new XMLDBTable('groups_groupings_groups');
524
$key = new XMLDBKey('groupingid');
525
$key->setAttributes(XMLDB_KEY_FOREIGN, array('groupingid'), 'groups_groupings', array('id'));
526
$result = $result && drop_key($table, $key);
528
/// Define key groupid (foreign) to be added to groups_groupings_groups
529
$table = new XMLDBTable('groups_groupings_groups');
530
$key = new XMLDBKey('groupid');
531
$key->setAttributes(XMLDB_KEY_FOREIGN, array('groupid'), 'groups', array('id'));
532
$result = $result && drop_key($table, $key);
534
/// Define index groupingid-groupid (unique) to be added to groups_groupings_groups
535
$table = new XMLDBTable('groups_groupings_groups');
536
$index = new XMLDBIndex('groupingid-groupid');
537
$index->setAttributes(XMLDB_INDEX_UNIQUE, array('groupingid', 'groupid'));
538
$result = $result && drop_index($table, $index);
543
function upgrade_fix_category_depths() {
546
// first fix incorrect parents
548
FROM {$CFG->prefix}course_categories c
549
WHERE c.parent > 0 AND c.parent NOT IN (SELECT pc.id FROM {$CFG->prefix}course_categories pc)";
550
if ($rs = get_recordset_sql($sql)) {
551
while ($cat = rs_fetch_next_record($rs)) {
553
$cat->path = '/'.$cat->id;
555
update_record('course_categories', $cat);
560
// now add path and depth to top level categories
561
$sql = "UPDATE {$CFG->prefix}course_categories
562
SET depth = 1, path = ".sql_concat("'/'", "id")."
566
// now fix all other levels - slow but works in all supported dbs
569
while (record_exists('course_categories', 'depth', 0)) {
570
$sql = "SELECT c.id, pc.path
571
FROM {$CFG->prefix}course_categories c, {$CFG->prefix}course_categories pc
572
WHERE c.parent=pc.id AND c.depth=0 AND pc.depth=$parentdepth";
573
if ($rs = get_recordset_sql($sql)) {
574
while ($cat = rs_fetch_next_record($rs)) {
575
$cat->depth = $parentdepth+1;
576
$cat->path = $cat->path.'/'.$cat->id;
577
update_record('course_categories', $cat);
582
if ($parentdepth > 100) {
583
//something must have gone wrong - nobody can have more than 100 levels of categories, right?
584
debugging('Unknown error fixing category depths');