MDL-45390 gradebook: sql tidy up and version alignment.
[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
b5725a97
JF
133/**
134 * Updates the mime-types for files that exist in the database, based on their
135 * file extension.
136 *
137 * @param array $filetypes Array with file extension as the key, and mimetype as the value
138 */
139function upgrade_mimetypes($filetypes) {
140 global $DB;
141 $select = $DB->sql_like('filename', '?', false);
142 foreach ($filetypes as $extension=>$mimetype) {
143 $DB->set_field_select(
144 'files',
145 'mimetype',
146 $mimetype,
147 $select,
148 array($extension)
149 );
150 }
156d0486
MG
151}
152
153/**
154 * Marks all courses with changes in extra credit weight calculation
155 *
156 * Used during upgrade and in course restore process
157 *
158 * This upgrade script is needed because we changed the algorithm for calculating the automatic weights of extra
159 * credit items and want to prevent changes in the existing student grades.
160 *
161 * @param int $onlycourseid
162 */
163function upgrade_extra_credit_weightoverride($onlycourseid = 0) {
164 global $DB;
165
166 // Find all courses that have categories in Natural aggregation method where there is at least one extra credit
167 // item and at least one item with overridden weight.
168 $courses = $DB->get_fieldset_sql(
169 "SELECT DISTINCT gc.courseid
170 FROM {grade_categories} gc
171 INNER JOIN {grade_items} gi ON gc.id = gi.categoryid AND gi.weightoverride = :weightoverriden
172 INNER JOIN {grade_items} gie ON gc.id = gie.categoryid AND gie.aggregationcoef = :extracredit
173 WHERE gc.aggregation = :naturalaggmethod" . ($onlycourseid ? " AND gc.courseid = :onlycourseid" : ''),
174 array('naturalaggmethod' => 13,
175 'weightoverriden' => 1,
176 'extracredit' => 1,
177 'onlycourseid' => $onlycourseid,
178 )
179 );
180 foreach ($courses as $courseid) {
181 $gradebookfreeze = get_config('core', 'gradebook_calculations_freeze_' . $courseid);
182 if (!$gradebookfreeze) {
183 set_config('gradebook_calculations_freeze_' . $courseid, 20150619);
184 }
185 }
4d4dcc27
AG
186}
187
188/**
189 * Marks all courses that require calculated grade items be updated.
190 *
191 * Used during upgrade and in course restore process.
192 *
193 * This upgrade script is needed because the calculated grade items were stuck with a maximum of 100 and could be changed.
194 * This flags the courses that are affected and the grade book is frozen to retain grade integrity.
195 *
196 * @param int $courseid Specify a course ID to run this script on just one course.
197 */
198function upgrade_calculated_grade_items($courseid = null) {
199 global $DB, $CFG;
200
201 $affectedcourses = array();
202 $possiblecourseids = array();
203 $params = array();
204 $singlecoursesql = '';
205 if (isset($courseid)) {
206 $singlecoursesql = "AND ns.id = :courseid";
207 $params['courseid'] = $courseid;
208 }
316c560c
AG
209 $siteminmaxtouse = 1;
210 if (isset($CFG->grade_minmaxtouse)) {
211 $siteminmaxtouse = $CFG->grade_minmaxtouse;
212 }
4d4dcc27
AG
213 $courseidsql = "SELECT ns.id
214 FROM (
3f8a714f 215 SELECT c.id, coalesce(" . $DB->sql_compare_text('gs.value') . ", :siteminmax) AS gradevalue
4d4dcc27
AG
216 FROM {course} c
217 LEFT JOIN {grade_settings} gs
218 ON c.id = gs.courseid
3f8a714f 219 AND ((gs.name = 'minmaxtouse' AND " . $DB->sql_compare_text('gs.value') . " = '2'))
4d4dcc27 220 ) ns
3f8a714f 221 WHERE " . $DB->sql_compare_text('ns.gradevalue') . " = '2' $singlecoursesql";
4d4dcc27
AG
222 $params['siteminmax'] = $siteminmaxtouse;
223 $courses = $DB->get_records_sql($courseidsql, $params);
224 foreach ($courses as $course) {
225 $possiblecourseids[$course->id] = $course->id;
226 }
227
228 if (!empty($possiblecourseids)) {
229 list($sql, $params) = $DB->get_in_or_equal($possiblecourseids);
230 // A calculated grade item grade min != 0 and grade max != 100 and the course setting is set to
231 // "Initial min and max grades".
232 $coursesql = "SELECT DISTINCT courseid
233 FROM {grade_items}
234 WHERE calculation IS NOT NULL
235 AND itemtype = 'manual'
236 AND (grademax <> 100 OR grademin <> 0)
237 AND courseid $sql";
238 $affectedcourses = $DB->get_records_sql($coursesql, $params);
239 }
240
241 // Check for second type of affected courses.
242 // If we already have the courseid parameter set in the affectedcourses then there is no need to run through this section.
243 if (!isset($courseid) || !in_array($courseid, $affectedcourses)) {
244 $singlecoursesql = '';
245 $params = array();
246 if (isset($courseid)) {
247 $singlecoursesql = "AND courseid = :courseid";
248 $params['courseid'] = $courseid;
249 }
250 $nestedsql = "SELECT id
251 FROM {grade_items}
252 WHERE itemtype = 'category'
253 AND calculation IS NOT NULL $singlecoursesql";
254 $calculatedgradecategories = $DB->get_records_sql($nestedsql, $params);
255 $categoryids = array();
256 foreach ($calculatedgradecategories as $key => $gradecategory) {
257 $categoryids[$key] = $gradecategory->id;
258 }
259
260 if (!empty($categoryids)) {
261 list($sql, $params) = $DB->get_in_or_equal($categoryids);
262 // A category with a calculation where the raw grade min and the raw grade max don't match the grade min and grade max
263 // for the category.
264 $coursesql = "SELECT DISTINCT gi.courseid
265 FROM {grade_grades} gg, {grade_items} gi
266 WHERE gi.id = gg.itemid
267 AND (gg.rawgrademax <> gi.grademax OR gg.rawgrademin <> gi.grademin)
268 AND gi.id $sql";
269 $additionalcourses = $DB->get_records_sql($coursesql, $params);
270 foreach ($additionalcourses as $key => $additionalcourse) {
271 if (!array_key_exists($key, $affectedcourses)) {
272 $affectedcourses[$key] = $additionalcourse;
273 }
274 }
275 }
276 }
277
316c560c
AG
278 foreach ($affectedcourses as $affectedcourseid) {
279 if (isset($CFG->upgrade_calculatedgradeitemsonlyregrade) && !($courseid)) {
280 $DB->set_field('grade_items', 'needsupdate', 1, array('courseid' => $affectedcourseid->courseid));
281 } else {
282 // Check to see if the gradebook freeze is already in affect.
283 $gradebookfreeze = get_config('core', 'gradebook_calculations_freeze_' . $affectedcourseid->courseid);
284 if (!$gradebookfreeze) {
285 set_config('gradebook_calculations_freeze_' . $affectedcourseid->courseid, 20150627);
286 }
4d4dcc27
AG
287 }
288 }
0d1e5456
MG
289}
290
291/**
292 * This upgrade script merges all tag instances pointing to the same course tag
293 *
294 * User id is no longer used for those tag instances
295 */
296function upgrade_course_tags() {
297 global $DB;
298 $sql = "SELECT min(ti.id)
299 FROM {tag_instance} ti
300 LEFT JOIN {tag_instance} tii on tii.itemtype = ? and tii.itemid = ti.itemid and tii.tiuserid = 0 and tii.tagid = ti.tagid
301 where ti.itemtype = ? and ti.tiuserid <> 0 AND tii.id is null
302 group by ti.tagid, ti.itemid";
303 $ids = $DB->get_fieldset_sql($sql, array('course', 'course'));
304 if ($ids) {
305 list($idsql, $idparams) = $DB->get_in_or_equal($ids);
306 $DB->execute('UPDATE {tag_instance} SET tiuserid = 0 WHERE id ' . $idsql, $idparams);
307 }
308 $DB->execute("DELETE FROM {tag_instance} WHERE itemtype = ? AND tiuserid <> 0", array('course'));
309}
8de0b1ab
DW
310
311/**
8de0b1ab
DW
312 * This function creates a default separated/connected scale
313 * so there's something in the database. The locations of
314 * strings and files is a bit odd, but this is because we
315 * need to maintain backward compatibility with many different
316 * existing language translations and older sites.
317 *
318 * @global object
319 * @return void
320 */
321function make_default_scale() {
322 global $DB;
323
324 $defaultscale = new stdClass();
325 $defaultscale->courseid = 0;
326 $defaultscale->userid = 0;
327 $defaultscale->name = get_string('separateandconnected');
328 $defaultscale->description = get_string('separateandconnectedinfo');
329 $defaultscale->scale = get_string('postrating1', 'forum').','.
330 get_string('postrating2', 'forum').','.
331 get_string('postrating3', 'forum');
332 $defaultscale->timemodified = time();
333
334 $defaultscale->id = $DB->insert_record('scale', $defaultscale);
335 return $defaultscale;
336}
337
338
339/**
340 * Create another default scale.
341 *
342 * @param int $oldversion
343 * @return bool always true
344 */
345function make_competence_scale() {
346 global $DB;
347
348 $defaultscale = new stdClass();
349 $defaultscale->courseid = 0;
350 $defaultscale->userid = 0;
351 $defaultscale->name = get_string('defaultcompetencescale');
352 $defaultscale->description = get_string('defaultcompetencescaledesc');
353 $defaultscale->scale = get_string('defaultcompetencescalenotproficient').','.
354 get_string('defaultcompetencescaleproficient');
355 $defaultscale->timemodified = time();
356
357 $defaultscale->id = $DB->insert_record('scale', $defaultscale);
358 return $defaultscale;
359}
405b90bc
AG
360
361/**
362 * Marks all courses that require rounded grade items be updated.
363 *
364 * Used during upgrade and in course restore process.
365 *
366 * This upgrade script is needed because it has been decided that if a grade is rounded up, and it will changed a letter
367 * grade or satisfy a course completion grade criteria, then it should be set as so, and the letter will be awarded and or
368 * the course completion grade will be awarded.
369 *
370 * @param int $courseid Specify a course ID to run this script on just one course.
371 */
372function upgrade_course_letter_boundary($courseid = null) {
373 global $DB, $CFG;
374
375 $coursesql = '';
376 $params = array('contextlevel' => CONTEXT_COURSE);
377 if (!empty($courseid)) {
378 $coursesql = 'AND c.id = :courseid';
379 $params['courseid'] = $courseid;
380 }
381
ece791db
DM
382 // Check to see if the system letter boundaries are borked.
383 $systemcontext = context_system::instance();
384 $systemneedsfreeze = upgrade_letter_boundary_needs_freeze($systemcontext);
405b90bc
AG
385
386 // 3, 13, 23, 31, and 32 are the grade display types that incorporate showing letters. See lib/grade/constants/php.
ece791db
DM
387 $systemletters = (isset($CFG->grade_displaytype) && in_array($CFG->grade_displaytype, array(3, 13, 23, 31, 32)));
388
389 $contextselect = context_helper::get_preload_record_columns_sql('ctx');
390
391 if ($systemletters && $systemneedsfreeze) {
392 // Select courses with no grade setting for display and a grade item that is using the default display,
393 // but have not altered the course letter boundary configuration. These courses are definitely affected.
394
395 $sql = "SELECT DISTINCT c.id AS courseid
41abbbbd
AG
396 FROM {course} c
397 JOIN {grade_items} gi ON c.id = gi.courseid
398 JOIN {context} ctx ON ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel
ece791db 399 LEFT JOIN {grade_settings} gs ON gs.courseid = c.id AND gs.name = 'displaytype'
41abbbbd
AG
400 LEFT JOIN {grade_letters} gl ON gl.contextid = ctx.id
401 WHERE gi.display = 0 AND (gs.value is NULL)
ece791db
DM
402 AND gl.id is NULL $coursesql";
403 $affectedcourseids = $DB->get_recordset_sql($sql, $params);
404 foreach ($affectedcourseids as $courseid) {
41abbbbd 405 set_config('gradebook_calculations_freeze_' . $courseid->courseid, 20160518);
405b90bc 406 }
ece791db
DM
407 $affectedcourseids->close();
408
409 }
410
411 if ($systemletters || $systemneedsfreeze) {
412
405b90bc 413 // If the system letter boundary is okay proceed to check grade item and course grade display settings.
405b90bc
AG
414 $sql = "SELECT DISTINCT c.id AS courseid, $contextselect
415 FROM {course} c
416 JOIN {context} ctx ON ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel
417 JOIN {grade_items} gi ON c.id = gi.courseid
ece791db 418 LEFT JOIN {grade_settings} gs ON c.id = gs.courseid AND gs.name = 'displaytype'
41abbbbd
AG
419 LEFT JOIN {grade_letters} gl ON gl.contextid = ctx.id
420 WHERE gi.display IN (3, 13, 23, 31, 32)
405b90bc 421 OR (" . $DB->sql_compare_text('gs.value') . " IN ('3', '13', '23', '31', '32'))
41abbbbd 422 OR gl.id is NOT NULL
405b90bc
AG
423 $coursesql";
424 } else {
ece791db 425
405b90bc
AG
426 // There is no site setting for letter grades. Just check the modified letter boundaries.
427 $sql = "SELECT DISTINCT c.id AS courseid, $contextselect
41abbbbd
AG
428 FROM {grade_letters} l, {course} c
429 JOIN {context} ctx ON ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel
430 WHERE l.contextid = ctx.id
431 AND ctx.instanceid = c.id
ece791db 432 $coursesql";
405b90bc
AG
433 }
434
435 $potentialcourses = $DB->get_recordset_sql($sql, $params);
436
437 foreach ($potentialcourses as $value) {
438 $gradebookfreeze = 'gradebook_calculations_freeze_' . $value->courseid;
439
440 // Check also if this course id has already been frozen.
441 // If we already have this course ID then move on to the next record.
442 if (!property_exists($CFG, $gradebookfreeze)) {
443 // Check for 57 letter grade issue.
444 context_helper::preload_from_record($value);
445 $coursecontext = context_course::instance($value->courseid);
446 if (upgrade_letter_boundary_needs_freeze($coursecontext)) {
447 // We have a course with a possible score standardisation problem. Flag for freeze.
448 // Flag this course as being frozen.
41abbbbd 449 set_config('gradebook_calculations_freeze_' . $value->courseid, 20160518);
405b90bc
AG
450 }
451 }
452 }
453 $potentialcourses->close();
454}
455
456/**
457 * Checks the letter boundary of the provided context to see if it needs freezing.
458 * Each letter boundary is tested to see if receiving that boundary number will
459 * result in achieving the cosponsoring letter.
460 *
461 * @param object $context Context object
462 * @return bool if the letter boundary for this context should be frozen.
463 */
464function upgrade_letter_boundary_needs_freeze($context) {
465 global $DB;
466
467 $contexts = $context->get_parent_context_ids();
468 array_unshift($contexts, $context->id);
469
470 foreach ($contexts as $ctxid) {
471
472 $letters = $DB->get_records_menu('grade_letters', array('contextid' => $ctxid), 'lowerboundary DESC',
473 'lowerboundary, letter');
474
475 if (!empty($letters)) {
476 foreach ($letters as $boundary => $notused) {
477 $standardisedboundary = upgrade_standardise_score($boundary, 0, 100, 0, 100);
478 if ($boundary != $standardisedboundary) {
479 return true;
480 }
481 }
482 // We found letters but we have no boundary problem.
483 return false;
484 }
485 }
486 return false;
487}
488
489/**
490 * Given a float value situated between a source minimum and a source maximum, converts it to the
491 * corresponding value situated between a target minimum and a target maximum. Thanks to Darlene
492 * for the formula :-)
493 *
494 * @param float $rawgrade
495 * @param float $sourcemin
496 * @param float $sourcemax
497 * @param float $targetmin
498 * @param float $targetmax
499 * @return float Converted value
500 */
501function upgrade_standardise_score($rawgrade, $sourcemin, $sourcemax, $targetmin, $targetmax) {
502 if (is_null($rawgrade)) {
503 return null;
504 }
505
506 if ($sourcemax == $sourcemin or $targetmin == $targetmax) {
507 // Prevent division by 0.
508 return $targetmax;
509 }
510
511 $factor = ($rawgrade - $sourcemin) / ($sourcemax - $sourcemin);
512 $diff = $targetmax - $targetmin;
513 $standardisedvalue = $factor * $diff + $targetmin;
514 return $standardisedvalue;
515}