MDL-11561 admin tree improvements and bugfixing
[moodle.git] / lib / db / upgradelib.php
CommitLineData
42ff9ce6 1<?php //$Id$
2
3/*
4 * This file is used for special upgrade functions - for example groups and gradebook.
56a1a882 5 * These functions must use SQL and database related functions only- no other Moodle API,
42ff9ce6 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
5cf5e181 10/**
11 * Migrates the grade_letter data to grade_letters
12 */
13function 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
42ff9ce6 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 */
106function 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;
d61d8c09 132 $course_category->fullname = get_string('coursegradecategory', 'grades');
42ff9ce6 133 $course_category->parent = null;
6e17e472 134 $course_category->aggregation = GRADE_AGGREGATE_WEIGHTED_MEAN2;
42ff9ce6 135 $course_category->timemodified = $course_category->timecreated = time();
0c87b5aa 136 $course_category->aggregateonlygraded = 0;
42ff9ce6 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;
0c87b5aa 152 $course_item->display = GRADE_DISPLAY_TYPE_PERCENTAGE;
42ff9ce6 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;
6e17e472 178 $category->aggregation = GRADE_AGGREGATE_WEIGHTED_MEAN2;
42ff9ce6 179 $category->timemodified = $category->timecreated = time();
0c87b5aa 180 $category->aggregateonlygraded = 0;
42ff9ce6 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;
0c87b5aa 197 $item->display = GRADE_DISPLAY_TYPE_PERCENTAGE;
42ff9ce6 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
6e17e472 212 $course_category->aggregation = GRADE_AGGREGATE_WEIGHTED_MEAN2;
42ff9ce6 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;
8942ac90 229 $item->idnumber = $olditem->cmidnumber;
42ff9ce6 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
c2efb501 246 if ($olditem->extra_credit and $categories[$olditem->category]->aggregation != GRADE_AGGREGATE_EXTRACREDIT_MEAN) {
247 $categories[$olditem->category]->aggregation = GRADE_AGGREGATE_EXTRACREDIT_MEAN;
42ff9ce6 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
2524b0f2 271
272
273/**
274 * Create new groupings tables for upgrade from 1.7.*|1.6.* and so on.
275 */
276function 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
abd30252 285 $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
2524b0f2 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
abd30252 306 $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
2524b0f2 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 */
338function upgrade_18_groups() {
f4d30b22 339 global $CFG, $db;
2524b0f2 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');
abd30252 361 $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'id');
2524b0f2 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');
abd30252 384 $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'id');
2524b0f2 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
f4d30b22 415/// Transfer courseid from 'mdl_groups_courses_groups' to 'mdl_groups'.
2524b0f2 416 if ($result) {
6db368e1 417 $sql = "UPDATE {$CFG->prefix}groups
f4d30b22 418 SET courseid = (
419 SELECT MAX(courseid)
420 FROM {$CFG->prefix}groups_courses_groups gcg
6db368e1 421 WHERE gcg.groupid = {$CFG->prefix}groups.id)";
f4d30b22 422 execute_sql($sql);
2524b0f2 423 }
424
f4d30b22 425/// Transfer courseid from 'groups_courses_groupings' to 'mdl_groupings'.
2524b0f2 426 if ($result) {
6db368e1 427 $sql = "UPDATE {$CFG->prefix}groupings
f4d30b22 428 SET courseid = (
429 SELECT MAX(courseid)
430 FROM {$CFG->prefix}groups_courses_groupings gcg
6db368e1 431 WHERE gcg.groupingid = {$CFG->prefix}groupings.id)";
f4d30b22 432 execute_sql($sql);
2524b0f2 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 */
450function 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
42ff9ce6 517?>