[Summary view] [Print] [Text view]

   1  <?php
   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   *
  20   * This file is used for special upgrade functions - for example groups and gradebook.
  21   * These functions must use SQL and database related functions only- no other Moodle API,
  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!)
  24   *
  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
  29   */
  30  
  31  defined('MOODLE_INTERNAL') || die();
  32  
  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   */
  39  function 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  }
  78  
  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   */
  91  function 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  
 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   */
 139  function 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      }
 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   */
 163  function 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      }
 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   */
 198  function 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      }
 209      $siteminmaxtouse = 1;
 210      if (isset($CFG->grade_minmaxtouse)) {
 211          $siteminmaxtouse = $CFG->grade_minmaxtouse;
 212      }
 213      $courseidsql = "SELECT ns.id
 214                        FROM (
 215                          SELECT c.id, coalesce(" . $DB->sql_compare_text('gs.value') . ", :siteminmax) AS gradevalue
 216                            FROM {course} c
 217                            LEFT JOIN {grade_settings} gs
 218                              ON c.id = gs.courseid
 219                             AND ((gs.name = 'minmaxtouse' AND " . $DB->sql_compare_text('gs.value') . " = '2'))
 220                          ) ns
 221                      WHERE " . $DB->sql_compare_text('ns.gradevalue') . " = '2' $singlecoursesql";
 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  
 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              }
 287          }
 288      }
 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   */
 296  function 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  }
 310  
 311  /**
 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   */
 321  function 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   */
 345  function 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  }
 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   */
 372  function 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  
 382      // Check to see if the system letter boundaries are borked.
 383      $systemcontext = context_system::instance();
 384      $systemneedsfreeze = upgrade_letter_boundary_needs_freeze($systemcontext);
 385  
 386      // Check the setting for showing the letter grade in a column (default is false).
 387      $usergradelettercolumnsetting = 0;
 388      if (isset($CFG->grade_report_user_showlettergrade)) {
 389          $usergradelettercolumnsetting = (int)$CFG->grade_report_user_showlettergrade;
 390      }
 391      $lettercolumnsql = '';
 392      if ($usergradelettercolumnsetting) {
 393          // the system default is to show a column with letters (and the course uses the defaults).
 394          $lettercolumnsql = '(gss.value is NULL OR ' . $DB->sql_compare_text('gss.value') .  ' <> \'0\')';
 395      } else {
 396          // the course displays a column with letters.
 397          $lettercolumnsql = $DB->sql_compare_text('gss.value') .  ' = \'1\'';
 398      }
 399  
 400      // 3, 13, 23, 31, and 32 are the grade display types that incorporate showing letters. See lib/grade/constants/php.
 401      $systemusesletters = (int) (isset($CFG->grade_displaytype) && in_array($CFG->grade_displaytype, array(3, 13, 23, 31, 32)));
 402      $systemletters = $systemusesletters || $usergradelettercolumnsetting;
 403  
 404      $contextselect = context_helper::get_preload_record_columns_sql('ctx');
 405  
 406      if ($systemletters && $systemneedsfreeze) {
 407          // Select courses with no grade setting for display and a grade item that is using the default display,
 408          // but have not altered the course letter boundary configuration. These courses are definitely affected.
 409  
 410          $sql = "SELECT DISTINCT c.id AS courseid
 411                    FROM {course} c
 412                    JOIN {grade_items} gi ON c.id = gi.courseid
 413                    JOIN {context} ctx ON ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel
 414               LEFT JOIN {grade_settings} gs ON gs.courseid = c.id AND gs.name = 'displaytype'
 415               LEFT JOIN {grade_settings} gss ON gss.courseid = c.id AND gss.name = 'report_user_showlettergrade'
 416               LEFT JOIN {grade_letters} gl ON gl.contextid = ctx.id
 417                   WHERE gi.display = 0
 418                   AND ((gs.value is NULL)
 419                        AND ($lettercolumnsql))
 420                   AND gl.id is NULL $coursesql";
 421          $affectedcourseids = $DB->get_recordset_sql($sql, $params);
 422          foreach ($affectedcourseids as $courseid) {
 423              set_config('gradebook_calculations_freeze_' . $courseid->courseid, 20160518);
 424          }
 425          $affectedcourseids->close();
 426      }
 427  
 428      // If the system letter boundary is okay proceed to check grade item and course grade display settings.
 429      $sql = "SELECT DISTINCT c.id AS courseid, $contextselect
 430                FROM {course} c
 431                JOIN {context} ctx ON ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel
 432                JOIN {grade_items} gi ON c.id = gi.courseid
 433           LEFT JOIN {grade_settings} gs ON c.id = gs.courseid AND gs.name = 'displaytype'
 434           LEFT JOIN {grade_settings} gss ON gss.courseid = c.id AND gss.name = 'report_user_showlettergrade'
 435               WHERE
 436                  (
 437                      -- A grade item is using letters
 438                      (gi.display IN (3, 13, 23, 31, 32))
 439                      -- OR the course is using letters
 440                      OR (" . $DB->sql_compare_text('gs.value') . " IN ('3', '13', '23', '31', '32')
 441                          -- OR the course using the system default which is letters
 442                          OR (gs.value IS NULL AND $systemusesletters = 1)
 443                      )
 444                      OR ($lettercolumnsql)
 445                  )
 446                  -- AND the course matches
 447                  $coursesql";
 448  
 449      $potentialcourses = $DB->get_recordset_sql($sql, $params);
 450  
 451      foreach ($potentialcourses as $value) {
 452          $gradebookfreeze = 'gradebook_calculations_freeze_' . $value->courseid;
 453  
 454          // Check also if this course id has already been frozen.
 455          // If we already have this course ID then move on to the next record.
 456          if (!property_exists($CFG, $gradebookfreeze)) {
 457              // Check for 57 letter grade issue.
 458              context_helper::preload_from_record($value);
 459              $coursecontext = context_course::instance($value->courseid);
 460              if (upgrade_letter_boundary_needs_freeze($coursecontext)) {
 461                  // We have a course with a possible score standardisation problem. Flag for freeze.
 462                  // Flag this course as being frozen.
 463                  set_config('gradebook_calculations_freeze_' . $value->courseid, 20160518);
 464              }
 465          }
 466      }
 467      $potentialcourses->close();
 468  }
 469  
 470  /**
 471   * Checks the letter boundary of the provided context to see if it needs freezing.
 472   * Each letter boundary is tested to see if receiving that boundary number will
 473   * result in achieving the cosponsoring letter.
 474   *
 475   * @param object $context Context object
 476   * @return bool if the letter boundary for this context should be frozen.
 477   */
 478  function upgrade_letter_boundary_needs_freeze($context) {
 479      global $DB;
 480  
 481      $contexts = $context->get_parent_context_ids();
 482      array_unshift($contexts, $context->id);
 483  
 484      foreach ($contexts as $ctxid) {
 485  
 486          $letters = $DB->get_records_menu('grade_letters', array('contextid' => $ctxid), 'lowerboundary DESC',
 487                  'lowerboundary, letter');
 488  
 489          if (!empty($letters)) {
 490              foreach ($letters as $boundary => $notused) {
 491                  $standardisedboundary = upgrade_standardise_score($boundary, 0, 100, 0, 100);
 492                  if ($standardisedboundary < $boundary) {
 493                      return true;
 494                  }
 495              }
 496              // We found letters but we have no boundary problem.
 497              return false;
 498          }
 499      }
 500      return false;
 501  }
 502  
 503  /**
 504   * Given a float value situated between a source minimum and a source maximum, converts it to the
 505   * corresponding value situated between a target minimum and a target maximum. Thanks to Darlene
 506   * for the formula :-)
 507   *
 508   * @param float $rawgrade
 509   * @param float $sourcemin
 510   * @param float $sourcemax
 511   * @param float $targetmin
 512   * @param float $targetmax
 513   * @return float Converted value
 514   */
 515  function upgrade_standardise_score($rawgrade, $sourcemin, $sourcemax, $targetmin, $targetmax) {
 516      if (is_null($rawgrade)) {
 517          return null;
 518      }
 519  
 520      if ($sourcemax == $sourcemin or $targetmin == $targetmax) {
 521          // Prevent division by 0.
 522          return $targetmax;
 523      }
 524  
 525      $factor = ($rawgrade - $sourcemin) / ($sourcemax - $sourcemin);
 526      $diff = $targetmax - $targetmin;
 527      $standardisedvalue = $factor * $diff + $targetmin;
 528      return $standardisedvalue;
 529  }