merged fix for MDL-11709, print_header() warning for blogs
[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.
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
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;
c2efb501 134 $course_category->aggregation = GRADE_AGGREGATE_MEAN;
42ff9ce6 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;
c2efb501 176 $category->aggregation = GRADE_AGGREGATE_MEAN;
42ff9ce6 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
c2efb501 208 $course_category->aggregation = GRADE_AGGREGATE_WEIGHTED_MEAN;
42ff9ce6 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;
8942ac90 225 $item->idnumber = $olditem->cmidnumber;
42ff9ce6 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
c2efb501 242 if ($olditem->extra_credit and $categories[$olditem->category]->aggregation != GRADE_AGGREGATE_EXTRACREDIT_MEAN) {
243 $categories[$olditem->category]->aggregation = GRADE_AGGREGATE_EXTRACREDIT_MEAN;
42ff9ce6 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
2524b0f2 267
268
269/**
270 * Create new groupings tables for upgrade from 1.7.*|1.6.* and so on.
271 */
272function 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
abd30252 281 $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
2524b0f2 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
abd30252 302 $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
2524b0f2 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 */
334function upgrade_18_groups() {
f4d30b22 335 global $CFG, $db;
2524b0f2 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');
abd30252 357 $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'id');
2524b0f2 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');
abd30252 380 $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0', 'id');
2524b0f2 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
f4d30b22 411/// Transfer courseid from 'mdl_groups_courses_groups' to 'mdl_groups'.
2524b0f2 412 if ($result) {
6db368e1 413 $sql = "UPDATE {$CFG->prefix}groups
f4d30b22 414 SET courseid = (
415 SELECT MAX(courseid)
416 FROM {$CFG->prefix}groups_courses_groups gcg
6db368e1 417 WHERE gcg.groupid = {$CFG->prefix}groups.id)";
f4d30b22 418 execute_sql($sql);
2524b0f2 419 }
420
f4d30b22 421/// Transfer courseid from 'groups_courses_groupings' to 'mdl_groupings'.
2524b0f2 422 if ($result) {
6db368e1 423 $sql = "UPDATE {$CFG->prefix}groupings
f4d30b22 424 SET courseid = (
425 SELECT MAX(courseid)
426 FROM {$CFG->prefix}groups_courses_groupings gcg
6db368e1 427 WHERE gcg.groupingid = {$CFG->prefix}groupings.id)";
f4d30b22 428 execute_sql($sql);
2524b0f2 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 */
446function 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
42ff9ce6 513?>