MDL-61268 blocks: Fix corrupt configdata in block instances.
[moodle.git] / lib / db / upgradelib.php
CommitLineData
117bd748 1<?php
5b4a78e2
PS
2// This file is part of Moodle - http://moodle.org/
3//
4// Moodle is free software: you can redistribute it and/or modify
5// it under the terms of the GNU General Public License as published by
6// the Free Software Foundation, either version 3 of the License, or
7// (at your option) any later version.
8//
9// Moodle is distributed in the hope that it will be useful,
10// but WITHOUT ANY WARRANTY; without even the implied warranty of
11// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12// GNU General Public License for more details.
13//
14// You should have received a copy of the GNU General Public License
15// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
16
17/**
18 * Upgrade helper functions
19 *
42ff9ce6 20 * This file is used for special upgrade functions - for example groups and gradebook.
56a1a882 21 * These functions must use SQL and database related functions only- no other Moodle API,
42ff9ce6 22 * because it might depend on db structures that are not yet present during upgrade.
23 * (Do not use functions from accesslib.php, grades classes or group functions at all!)
5b4a78e2 24 *
39b90b51
EL
25 * @package core_install
26 * @category upgrade
27 * @copyright 2007 Petr Skoda (http://skodak.org)
28 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
42ff9ce6 29 */
30
5b4a78e2
PS
31defined('MOODLE_INTERNAL') || die();
32
3182d885
PS
33/**
34 * Returns all non-view and non-temp tables with sane names.
35 * Prints list of non-supported tables using $OUTPUT->notification()
36 *
37 * @return array
38 */
39function upgrade_mysql_get_supported_tables() {
40 global $OUTPUT, $DB;
41
42 $tables = array();
43 $patprefix = str_replace('_', '\\_', $DB->get_prefix());
44 $pregprefix = preg_quote($DB->get_prefix(), '/');
45
46 $sql = "SHOW FULL TABLES LIKE '$patprefix%'";
47 $rs = $DB->get_recordset_sql($sql);
48 foreach ($rs as $record) {
49 $record = array_change_key_case((array)$record, CASE_LOWER);
50 $type = $record['table_type'];
51 unset($record['table_type']);
52 $fullname = array_shift($record);
53
54 if ($pregprefix === '') {
55 $name = $fullname;
56 } else {
57 $count = null;
58 $name = preg_replace("/^$pregprefix/", '', $fullname, -1, $count);
59 if ($count !== 1) {
60 continue;
61 }
62 }
63
64 if (!preg_match("/^[a-z][a-z0-9_]*$/", $name)) {
65 echo $OUTPUT->notification("Database table with invalid name '$fullname' detected, skipping.", 'notifyproblem');
66 continue;
67 }
68 if ($type === 'VIEW') {
69 echo $OUTPUT->notification("Unsupported database table view '$fullname' detected, skipping.", 'notifyproblem');
70 continue;
71 }
72 $tables[$name] = $name;
73 }
74 $rs->close();
75
76 return $tables;
77}
7385cc00 78
061e6b28 79/**
80 * Using data for a single course-module that has groupmembersonly enabled,
81 * returns the new availability value that incorporates the correct
82 * groupmembersonly option.
83 *
84 * Included as a function so that it can be shared between upgrade and restore,
85 * and unit-tested.
86 *
87 * @param int $groupingid Grouping id for the course-module (0 if none)
88 * @param string $availability Availability JSON data for the module (null if none)
89 * @return string New value for availability for the module
90 */
91function upgrade_group_members_only($groupingid, $availability) {
92 // Work out the new JSON object representing this option.
93 if ($groupingid) {
94 // Require specific grouping.
95 $condition = (object)array('type' => 'grouping', 'id' => (int)$groupingid);
96 } else {
97 // No grouping specified, so require membership of any group.
98 $condition = (object)array('type' => 'group');
99 }
100
101 if (is_null($availability)) {
102 // If there are no conditions using the new API then just set it.
103 $tree = (object)array('op' => '&', 'c' => array($condition), 'showc' => array(false));
104 } else {
105 // There are existing conditions.
106 $tree = json_decode($availability);
107 switch ($tree->op) {
108 case '&' :
109 // For & conditions we can just add this one.
110 $tree->c[] = $condition;
111 $tree->showc[] = false;
112 break;
113 case '!|' :
114 // For 'not or' conditions we can add this one
115 // but negated.
116 $tree->c[] = (object)array('op' => '!&', 'c' => array($condition));
117 $tree->showc[] = false;
118 break;
119 default:
120 // For the other two (OR and NOT AND) we have to add
121 // an extra level to the tree.
122 $tree = (object)array('op' => '&', 'c' => array($tree, $condition),
123 'showc' => array($tree->show, false));
124 // Inner trees do not have a show option, so remove it.
125 unset($tree->c[0]->show);
126 break;
127 }
128 }
129
130 return json_encode($tree);
131}
132
156d0486
MG
133/**
134 * Marks all courses with changes in extra credit weight calculation
135 *
136 * Used during upgrade and in course restore process
137 *
138 * This upgrade script is needed because we changed the algorithm for calculating the automatic weights of extra
139 * credit items and want to prevent changes in the existing student grades.
140 *
141 * @param int $onlycourseid
142 */
143function upgrade_extra_credit_weightoverride($onlycourseid = 0) {
144 global $DB;
145
146 // Find all courses that have categories in Natural aggregation method where there is at least one extra credit
147 // item and at least one item with overridden weight.
148 $courses = $DB->get_fieldset_sql(
149 "SELECT DISTINCT gc.courseid
150 FROM {grade_categories} gc
151 INNER JOIN {grade_items} gi ON gc.id = gi.categoryid AND gi.weightoverride = :weightoverriden
152 INNER JOIN {grade_items} gie ON gc.id = gie.categoryid AND gie.aggregationcoef = :extracredit
153 WHERE gc.aggregation = :naturalaggmethod" . ($onlycourseid ? " AND gc.courseid = :onlycourseid" : ''),
154 array('naturalaggmethod' => 13,
155 'weightoverriden' => 1,
156 'extracredit' => 1,
157 'onlycourseid' => $onlycourseid,
158 )
159 );
160 foreach ($courses as $courseid) {
161 $gradebookfreeze = get_config('core', 'gradebook_calculations_freeze_' . $courseid);
162 if (!$gradebookfreeze) {
163 set_config('gradebook_calculations_freeze_' . $courseid, 20150619);
164 }
165 }
4d4dcc27
AG
166}
167
168/**
169 * Marks all courses that require calculated grade items be updated.
170 *
171 * Used during upgrade and in course restore process.
172 *
173 * This upgrade script is needed because the calculated grade items were stuck with a maximum of 100 and could be changed.
174 * This flags the courses that are affected and the grade book is frozen to retain grade integrity.
175 *
176 * @param int $courseid Specify a course ID to run this script on just one course.
177 */
178function upgrade_calculated_grade_items($courseid = null) {
179 global $DB, $CFG;
180
181 $affectedcourses = array();
182 $possiblecourseids = array();
183 $params = array();
184 $singlecoursesql = '';
185 if (isset($courseid)) {
186 $singlecoursesql = "AND ns.id = :courseid";
187 $params['courseid'] = $courseid;
188 }
316c560c
AG
189 $siteminmaxtouse = 1;
190 if (isset($CFG->grade_minmaxtouse)) {
191 $siteminmaxtouse = $CFG->grade_minmaxtouse;
192 }
4d4dcc27
AG
193 $courseidsql = "SELECT ns.id
194 FROM (
3f8a714f 195 SELECT c.id, coalesce(" . $DB->sql_compare_text('gs.value') . ", :siteminmax) AS gradevalue
4d4dcc27
AG
196 FROM {course} c
197 LEFT JOIN {grade_settings} gs
198 ON c.id = gs.courseid
3f8a714f 199 AND ((gs.name = 'minmaxtouse' AND " . $DB->sql_compare_text('gs.value') . " = '2'))
4d4dcc27 200 ) ns
3f8a714f 201 WHERE " . $DB->sql_compare_text('ns.gradevalue') . " = '2' $singlecoursesql";
4d4dcc27
AG
202 $params['siteminmax'] = $siteminmaxtouse;
203 $courses = $DB->get_records_sql($courseidsql, $params);
204 foreach ($courses as $course) {
205 $possiblecourseids[$course->id] = $course->id;
206 }
207
208 if (!empty($possiblecourseids)) {
209 list($sql, $params) = $DB->get_in_or_equal($possiblecourseids);
210 // A calculated grade item grade min != 0 and grade max != 100 and the course setting is set to
211 // "Initial min and max grades".
212 $coursesql = "SELECT DISTINCT courseid
213 FROM {grade_items}
214 WHERE calculation IS NOT NULL
215 AND itemtype = 'manual'
216 AND (grademax <> 100 OR grademin <> 0)
217 AND courseid $sql";
218 $affectedcourses = $DB->get_records_sql($coursesql, $params);
219 }
220
221 // Check for second type of affected courses.
222 // If we already have the courseid parameter set in the affectedcourses then there is no need to run through this section.
223 if (!isset($courseid) || !in_array($courseid, $affectedcourses)) {
224 $singlecoursesql = '';
225 $params = array();
226 if (isset($courseid)) {
227 $singlecoursesql = "AND courseid = :courseid";
228 $params['courseid'] = $courseid;
229 }
230 $nestedsql = "SELECT id
231 FROM {grade_items}
232 WHERE itemtype = 'category'
233 AND calculation IS NOT NULL $singlecoursesql";
234 $calculatedgradecategories = $DB->get_records_sql($nestedsql, $params);
235 $categoryids = array();
236 foreach ($calculatedgradecategories as $key => $gradecategory) {
237 $categoryids[$key] = $gradecategory->id;
238 }
239
240 if (!empty($categoryids)) {
241 list($sql, $params) = $DB->get_in_or_equal($categoryids);
242 // A category with a calculation where the raw grade min and the raw grade max don't match the grade min and grade max
243 // for the category.
244 $coursesql = "SELECT DISTINCT gi.courseid
245 FROM {grade_grades} gg, {grade_items} gi
246 WHERE gi.id = gg.itemid
247 AND (gg.rawgrademax <> gi.grademax OR gg.rawgrademin <> gi.grademin)
248 AND gi.id $sql";
249 $additionalcourses = $DB->get_records_sql($coursesql, $params);
250 foreach ($additionalcourses as $key => $additionalcourse) {
251 if (!array_key_exists($key, $affectedcourses)) {
252 $affectedcourses[$key] = $additionalcourse;
253 }
254 }
255 }
256 }
257
316c560c
AG
258 foreach ($affectedcourses as $affectedcourseid) {
259 if (isset($CFG->upgrade_calculatedgradeitemsonlyregrade) && !($courseid)) {
260 $DB->set_field('grade_items', 'needsupdate', 1, array('courseid' => $affectedcourseid->courseid));
261 } else {
262 // Check to see if the gradebook freeze is already in affect.
263 $gradebookfreeze = get_config('core', 'gradebook_calculations_freeze_' . $affectedcourseid->courseid);
264 if (!$gradebookfreeze) {
265 set_config('gradebook_calculations_freeze_' . $affectedcourseid->courseid, 20150627);
266 }
4d4dcc27
AG
267 }
268 }
0d1e5456
MG
269}
270
8de0b1ab 271/**
8de0b1ab
DW
272 * This function creates a default separated/connected scale
273 * so there's something in the database. The locations of
274 * strings and files is a bit odd, but this is because we
275 * need to maintain backward compatibility with many different
276 * existing language translations and older sites.
277 *
278 * @global object
279 * @return void
280 */
281function make_default_scale() {
282 global $DB;
283
284 $defaultscale = new stdClass();
285 $defaultscale->courseid = 0;
286 $defaultscale->userid = 0;
287 $defaultscale->name = get_string('separateandconnected');
288 $defaultscale->description = get_string('separateandconnectedinfo');
289 $defaultscale->scale = get_string('postrating1', 'forum').','.
290 get_string('postrating2', 'forum').','.
291 get_string('postrating3', 'forum');
292 $defaultscale->timemodified = time();
293
294 $defaultscale->id = $DB->insert_record('scale', $defaultscale);
295 return $defaultscale;
296}
297
298
299/**
300 * Create another default scale.
301 *
302 * @param int $oldversion
303 * @return bool always true
304 */
305function make_competence_scale() {
306 global $DB;
307
308 $defaultscale = new stdClass();
309 $defaultscale->courseid = 0;
310 $defaultscale->userid = 0;
311 $defaultscale->name = get_string('defaultcompetencescale');
312 $defaultscale->description = get_string('defaultcompetencescaledesc');
313 $defaultscale->scale = get_string('defaultcompetencescalenotproficient').','.
314 get_string('defaultcompetencescaleproficient');
315 $defaultscale->timemodified = time();
316
317 $defaultscale->id = $DB->insert_record('scale', $defaultscale);
318 return $defaultscale;
319}
405b90bc
AG
320
321/**
322 * Marks all courses that require rounded grade items be updated.
323 *
324 * Used during upgrade and in course restore process.
325 *
326 * This upgrade script is needed because it has been decided that if a grade is rounded up, and it will changed a letter
327 * grade or satisfy a course completion grade criteria, then it should be set as so, and the letter will be awarded and or
328 * the course completion grade will be awarded.
329 *
330 * @param int $courseid Specify a course ID to run this script on just one course.
331 */
332function upgrade_course_letter_boundary($courseid = null) {
333 global $DB, $CFG;
334
335 $coursesql = '';
336 $params = array('contextlevel' => CONTEXT_COURSE);
337 if (!empty($courseid)) {
338 $coursesql = 'AND c.id = :courseid';
339 $params['courseid'] = $courseid;
340 }
341
ece791db
DM
342 // Check to see if the system letter boundaries are borked.
343 $systemcontext = context_system::instance();
344 $systemneedsfreeze = upgrade_letter_boundary_needs_freeze($systemcontext);
405b90bc 345
05156571
AG
346 // Check the setting for showing the letter grade in a column (default is false).
347 $usergradelettercolumnsetting = 0;
348 if (isset($CFG->grade_report_user_showlettergrade)) {
349 $usergradelettercolumnsetting = (int)$CFG->grade_report_user_showlettergrade;
350 }
351 $lettercolumnsql = '';
352 if ($usergradelettercolumnsetting) {
353 // the system default is to show a column with letters (and the course uses the defaults).
354 $lettercolumnsql = '(gss.value is NULL OR ' . $DB->sql_compare_text('gss.value') . ' <> \'0\')';
355 } else {
356 // the course displays a column with letters.
357 $lettercolumnsql = $DB->sql_compare_text('gss.value') . ' = \'1\'';
358 }
359
405b90bc 360 // 3, 13, 23, 31, and 32 are the grade display types that incorporate showing letters. See lib/grade/constants/php.
05156571
AG
361 $systemusesletters = (int) (isset($CFG->grade_displaytype) && in_array($CFG->grade_displaytype, array(3, 13, 23, 31, 32)));
362 $systemletters = $systemusesletters || $usergradelettercolumnsetting;
ece791db
DM
363
364 $contextselect = context_helper::get_preload_record_columns_sql('ctx');
365
366 if ($systemletters && $systemneedsfreeze) {
367 // Select courses with no grade setting for display and a grade item that is using the default display,
368 // but have not altered the course letter boundary configuration. These courses are definitely affected.
369
370 $sql = "SELECT DISTINCT c.id AS courseid
41abbbbd
AG
371 FROM {course} c
372 JOIN {grade_items} gi ON c.id = gi.courseid
373 JOIN {context} ctx ON ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel
ece791db 374 LEFT JOIN {grade_settings} gs ON gs.courseid = c.id AND gs.name = 'displaytype'
05156571 375 LEFT JOIN {grade_settings} gss ON gss.courseid = c.id AND gss.name = 'report_user_showlettergrade'
41abbbbd 376 LEFT JOIN {grade_letters} gl ON gl.contextid = ctx.id
05156571
AG
377 WHERE gi.display = 0
378 AND ((gs.value is NULL)
379 AND ($lettercolumnsql))
ece791db
DM
380 AND gl.id is NULL $coursesql";
381 $affectedcourseids = $DB->get_recordset_sql($sql, $params);
382 foreach ($affectedcourseids as $courseid) {
41abbbbd 383 set_config('gradebook_calculations_freeze_' . $courseid->courseid, 20160518);
405b90bc 384 }
ece791db 385 $affectedcourseids->close();
ece791db
DM
386 }
387
05156571
AG
388 // If the system letter boundary is okay proceed to check grade item and course grade display settings.
389 $sql = "SELECT DISTINCT c.id AS courseid, $contextselect
390 FROM {course} c
391 JOIN {context} ctx ON ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel
392 JOIN {grade_items} gi ON c.id = gi.courseid
393 LEFT JOIN {grade_settings} gs ON c.id = gs.courseid AND gs.name = 'displaytype'
394 LEFT JOIN {grade_settings} gss ON gss.courseid = c.id AND gss.name = 'report_user_showlettergrade'
395 WHERE
396 (
397 -- A grade item is using letters
398 (gi.display IN (3, 13, 23, 31, 32))
399 -- OR the course is using letters
400 OR (" . $DB->sql_compare_text('gs.value') . " IN ('3', '13', '23', '31', '32')
401 -- OR the course using the system default which is letters
402 OR (gs.value IS NULL AND $systemusesletters = 1)
403 )
404 OR ($lettercolumnsql)
405 )
406 -- AND the course matches
407 $coursesql";
405b90bc
AG
408
409 $potentialcourses = $DB->get_recordset_sql($sql, $params);
410
411 foreach ($potentialcourses as $value) {
412 $gradebookfreeze = 'gradebook_calculations_freeze_' . $value->courseid;
413
414 // Check also if this course id has already been frozen.
415 // If we already have this course ID then move on to the next record.
416 if (!property_exists($CFG, $gradebookfreeze)) {
417 // Check for 57 letter grade issue.
418 context_helper::preload_from_record($value);
419 $coursecontext = context_course::instance($value->courseid);
420 if (upgrade_letter_boundary_needs_freeze($coursecontext)) {
421 // We have a course with a possible score standardisation problem. Flag for freeze.
422 // Flag this course as being frozen.
41abbbbd 423 set_config('gradebook_calculations_freeze_' . $value->courseid, 20160518);
405b90bc
AG
424 }
425 }
426 }
427 $potentialcourses->close();
428}
429
430/**
431 * Checks the letter boundary of the provided context to see if it needs freezing.
432 * Each letter boundary is tested to see if receiving that boundary number will
433 * result in achieving the cosponsoring letter.
434 *
435 * @param object $context Context object
436 * @return bool if the letter boundary for this context should be frozen.
437 */
438function upgrade_letter_boundary_needs_freeze($context) {
439 global $DB;
440
441 $contexts = $context->get_parent_context_ids();
442 array_unshift($contexts, $context->id);
443
444 foreach ($contexts as $ctxid) {
445
446 $letters = $DB->get_records_menu('grade_letters', array('contextid' => $ctxid), 'lowerboundary DESC',
447 'lowerboundary, letter');
448
449 if (!empty($letters)) {
450 foreach ($letters as $boundary => $notused) {
451 $standardisedboundary = upgrade_standardise_score($boundary, 0, 100, 0, 100);
05156571 452 if ($standardisedboundary < $boundary) {
405b90bc
AG
453 return true;
454 }
455 }
456 // We found letters but we have no boundary problem.
457 return false;
458 }
459 }
460 return false;
461}
462
463/**
464 * Given a float value situated between a source minimum and a source maximum, converts it to the
465 * corresponding value situated between a target minimum and a target maximum. Thanks to Darlene
466 * for the formula :-)
467 *
468 * @param float $rawgrade
469 * @param float $sourcemin
470 * @param float $sourcemax
471 * @param float $targetmin
472 * @param float $targetmax
473 * @return float Converted value
474 */
475function upgrade_standardise_score($rawgrade, $sourcemin, $sourcemax, $targetmin, $targetmax) {
476 if (is_null($rawgrade)) {
477 return null;
478 }
479
480 if ($sourcemax == $sourcemin or $targetmin == $targetmax) {
481 // Prevent division by 0.
482 return $targetmax;
483 }
484
485 $factor = ($rawgrade - $sourcemin) / ($sourcemax - $sourcemin);
486 $diff = $targetmax - $targetmin;
487 $standardisedvalue = $factor * $diff + $targetmin;
488 return $standardisedvalue;
489}
a5722727
MG
490
491/**
492 * Delete orphaned records in block_positions
493 */
494function upgrade_block_positions() {
495 global $DB;
496 $id = 'id';
497 if ($DB->get_dbfamily() !== 'mysql') {
498 // Field block_positions.subpage has type 'char', it can not be compared to int in db engines except for mysql.
499 $id = $DB->sql_concat('?', 'id');
500 }
501 $sql = "DELETE FROM {block_positions}
502 WHERE pagetype IN ('my-index', 'user-profile') AND subpage NOT IN (SELECT $id FROM {my_pages})";
503 $DB->execute($sql, ['']);
504}
e78849e8
AG
505
506/**
507 * Fix configdata in block instances that are using the old object class that has been removed (deprecated).
508 */
509function upgrade_fix_block_instance_configuration() {
510 global $DB;
511
512 $sql = "SELECT *
513 FROM {block_instances}
514 WHERE configdata <> ''";
515 $blockinstances = $DB->get_recordset_sql($sql);
516 foreach ($blockinstances as $blockinstance) {
517 $configdata = base64_decode($blockinstance->configdata);
518 list($updated, $configdata) = upgrade_fix_serialized_objects($configdata);
519 if ($updated) {
520 $blockinstance->configdata = base64_encode($configdata);
521 $DB->update_record('block_instances', $blockinstance);
522 }
523 }
524 $blockinstances->close();
525}
526
527/**
528 * Provides a way to check and update a serialized string that uses the deprecated object class.
529 *
530 * @param string $serializeddata Serialized string which may contain the now deprecated object.
531 * @return array Returns an array where the first variable is a bool with a status of whether the initial data was changed
532 * or not. The second variable is the said data.
533 */
534function upgrade_fix_serialized_objects($serializeddata) {
535 $updated = false;
536 if (strpos($serializeddata, ":6:\"object") !== false) {
537 $serializeddata = str_replace(":6:\"object", ":8:\"stdClass", $serializeddata);
538 $updated = true;
539 }
540 return [$updated, $serializeddata];
541}