[Summary view]

Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402]

   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   * 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   */
 143  function 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      }
 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   */
 178  function 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      }
 189      $siteminmaxtouse = 1;
 190      if (isset($CFG->grade_minmaxtouse)) {
 191          $siteminmaxtouse = $CFG->grade_minmaxtouse;
 192      }
 193      $courseidsql = "SELECT ns.id
 194                        FROM (
 195                          SELECT c.id, coalesce(" . $DB->sql_compare_text('gs.value') . ", :siteminmax) AS gradevalue
 196                            FROM {course} c
 197                            LEFT JOIN {grade_settings} gs
 198                              ON c.id = gs.courseid
 199                             AND ((gs.name = 'minmaxtouse' AND " . $DB->sql_compare_text('gs.value') . " = '2'))
 200                          ) ns
 201                      WHERE " . $DB->sql_compare_text('ns.gradevalue') . " = '2' $singlecoursesql";
 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  
 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              }
 267          }
 268      }
 269  }
 270  
 271  /**
 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   */
 281  function 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   */
 305  function 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  }
 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   */
 332  function 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  
 342      // Check to see if the system letter boundaries are borked.
 343      $systemcontext = context_system::instance();
 344      $systemneedsfreeze = upgrade_letter_boundary_needs_freeze($systemcontext);
 345  
 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  
 360      // 3, 13, 23, 31, and 32 are the grade display types that incorporate showing letters. See lib/grade/constants/php.
 361      $systemusesletters = (int) (isset($CFG->grade_displaytype) && in_array($CFG->grade_displaytype, array(3, 13, 23, 31, 32)));
 362      $systemletters = $systemusesletters || $usergradelettercolumnsetting;
 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
 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
 374               LEFT JOIN {grade_settings} gs ON gs.courseid = c.id AND gs.name = 'displaytype'
 375               LEFT JOIN {grade_settings} gss ON gss.courseid = c.id AND gss.name = 'report_user_showlettergrade'
 376               LEFT JOIN {grade_letters} gl ON gl.contextid = ctx.id
 377                   WHERE gi.display = 0
 378                   AND ((gs.value is NULL)
 379                        AND ($lettercolumnsql))
 380                   AND gl.id is NULL $coursesql";
 381          $affectedcourseids = $DB->get_recordset_sql($sql, $params);
 382          foreach ($affectedcourseids as $courseid) {
 383              set_config('gradebook_calculations_freeze_' . $courseid->courseid, 20160518);
 384          }
 385          $affectedcourseids->close();
 386      }
 387  
 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";
 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.
 423                  set_config('gradebook_calculations_freeze_' . $value->courseid, 20160518);
 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   */
 438  function 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);
 452                  if ($standardisedboundary < $boundary) {
 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   */
 475  function 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  }
 490  
 491  /**
 492   * Provides a way to check and update a serialized string that uses the deprecated object class.
 493   *
 494   * @param  string $serializeddata Serialized string which may contain the now deprecated object.
 495   * @return array Returns an array where the first variable is a bool with a status of whether the initial data was changed
 496   * or not. The second variable is the said data.
 497   */
 498  function upgrade_fix_serialized_objects($serializeddata) {
 499      $updated = false;
 500      if (strpos($serializeddata, ":6:\"object") !== false) {
 501          $serializeddata = str_replace(":6:\"object", ":8:\"stdClass", $serializeddata);
 502          $updated = true;
 503      }
 504      return [$updated, $serializeddata];
 505  }
 506  
 507  /**
 508   * Deletes file records which have their repository deleted.
 509   *
 510   */
 511  function upgrade_delete_orphaned_file_records() {
 512      global $DB;
 513  
 514      $sql = "SELECT f.id, f.contextid, f.component, f.filearea, f.itemid, fr.id AS referencefileid
 515                FROM {files} f
 516                JOIN {files_reference} fr ON f.referencefileid = fr.id
 517           LEFT JOIN {repository_instances} ri ON fr.repositoryid = ri.id
 518               WHERE ri.id IS NULL";
 519  
 520      $deletedfiles = $DB->get_recordset_sql($sql);
 521  
 522      $deletedfileids = array();
 523  
 524      $fs = get_file_storage();
 525      foreach ($deletedfiles as $deletedfile) {
 526          $fs->delete_area_files($deletedfile->contextid, $deletedfile->component, $deletedfile->filearea, $deletedfile->itemid);
 527          $deletedfileids[] = $deletedfile->referencefileid;
 528      }
 529      $deletedfiles->close();
 530  
 531      $DB->delete_records_list('files_reference', 'id', $deletedfileids);
 532  }
 533  
 534  /**
 535   * Upgrade core licenses shipped with Moodle.
 536   */
 537  function upgrade_core_licenses() {
 538      global $CFG, $DB;
 539  
 540      $expectedlicenses = json_decode(file_get_contents($CFG->dirroot . '/lib/licenses.json'))->licenses;
 541      if (!is_array($expectedlicenses)) {
 542          $expectedlicenses = [];
 543      }
 544      $corelicenses = $DB->get_records('license', ['custom' => 0]);
 545  
 546      // Disable core licenses which are no longer current.
 547      $todisable = array_diff(
 548          array_map(fn ($license) => $license->shortname, $corelicenses),
 549          array_map(fn ($license) => $license->shortname, $expectedlicenses),
 550      );
 551  
 552      // Disable any old *core* license that does not exist in the licenses.json file.
 553      if (count($todisable)) {
 554          [$where, $params] = $DB->get_in_or_equal($todisable, SQL_PARAMS_NAMED);
 555          $DB->set_field_select(
 556              'license',
 557              'enabled',
 558              0,
 559              "shortname {$where}",
 560              $params
 561          );
 562      }
 563  
 564      // Add any new licenses.
 565      foreach ($expectedlicenses as $expectedlicense) {
 566          if (!$expectedlicense->enabled) {
 567              // Skip any license which is no longer enabled.
 568              continue;
 569          }
 570          if (!$DB->record_exists('license', ['shortname' => $expectedlicense->shortname])) {
 571              // If the license replaces an older one, check whether this old license was enabled or not.
 572              $isreplacement = false;
 573              foreach (array_reverse($expectedlicense->replaces ?? []) as $item) {
 574                  foreach ($corelicenses as $corelicense) {
 575                      if ($corelicense->shortname === $item) {
 576                          $expectedlicense->enabled = $corelicense->enabled;
 577                          // Also, keep the old sort order.
 578                          $expectedlicense->sortorder = $corelicense->sortorder * 100;
 579                          $isreplacement = true;
 580                          break 2;
 581                      }
 582                  }
 583              }
 584              if (!isset($CFG->upgraderunning) || during_initial_install() || $isreplacement) {
 585                  // Only install missing core licenses if not upgrading or during initial installation.
 586                  $DB->insert_record('license', $expectedlicense);
 587              }
 588          }
 589      }
 590  
 591      // Add/renumber sortorder to all licenses.
 592      $licenses = $DB->get_records('license', null, 'sortorder');
 593      $sortorder = 1;
 594      foreach ($licenses as $license) {
 595          $license->sortorder = $sortorder++;
 596          $DB->update_record('license', $license);
 597      }
 598  
 599      // Set the license config values, used by file repository for rendering licenses at front end.
 600      $activelicenses = $DB->get_records_menu('license', ['enabled' => 1], 'id', 'id, shortname');
 601      set_config('licenses', implode(',', $activelicenses));
 602  
 603      $sitedefaultlicense = get_config('', 'sitedefaultlicense');
 604      if (empty($sitedefaultlicense) || !in_array($sitedefaultlicense, $activelicenses)) {
 605          set_config('sitedefaultlicense', reset($activelicenses));
 606      }
 607  }
 608  
 609  /**
 610   * Detects if the site may need to get the calendar events fixed or no. With optional output.
 611   *
 612   * @param bool $output true if the function must output information, false if not.
 613   * @return bool true if the site needs to run the fixes, false if not.
 614   */
 615  function upgrade_calendar_site_status(bool $output = true): bool {
 616      global $DB;
 617  
 618      // List of upgrade steps where the bug happened.
 619      $badsteps = [
 620          '3.9.5'   => '2020061504.08',
 621          '3.10.2'  => '2020110901.09',
 622          '3.11dev' => '2021022600.02',
 623          '4.0dev'  => '2021052500.65',
 624      ];
 625  
 626      // List of upgrade steps that ran the fixer.
 627      $fixsteps = [
 628          '3.9.6+'  => '2020061506.05',
 629          '3.10.3+' => '2020110903.05',
 630          '3.11dev' => '2021042100.02',
 631          '4.0dev'  => '2021052500.85',
 632      ];
 633  
 634      $targetsteps = array_merge(array_values($badsteps), array_values( $fixsteps));
 635      list($insql, $inparams) = $DB->get_in_or_equal($targetsteps);
 636      $foundsteps = $DB->get_fieldset_sql("
 637          SELECT DISTINCT version
 638            FROM {upgrade_log}
 639           WHERE plugin = 'core'
 640             AND version " . $insql . "
 641        ORDER BY version", $inparams);
 642  
 643      // Analyse the found steps, to decide if the site needs upgrading or no.
 644      $badfound = false;
 645      $fixfound = false;
 646      foreach ($foundsteps as $foundstep) {
 647          $badfound = $badfound ?: array_search($foundstep, $badsteps, true);
 648          $fixfound = $fixfound ?: array_search($foundstep, $fixsteps, true);
 649      }
 650      $needsfix = $badfound && !$fixfound;
 651  
 652      // Let's output some textual information if required to.
 653      if ($output) {
 654          mtrace("");
 655          if ($badfound) {
 656              mtrace("This site has executed the problematic upgrade step {$badsteps[$badfound]} present in {$badfound}.");
 657          } else {
 658              mtrace("Problematic upgrade steps were NOT found, site should be safe.");
 659          }
 660          if ($fixfound) {
 661              mtrace("This site has executed the fix upgrade step {$fixsteps[$fixfound]} present in {$fixfound}.");
 662          } else {
 663              mtrace("Fix upgrade steps were NOT found.");
 664          }
 665          mtrace("");
 666          if ($needsfix) {
 667              mtrace("This site NEEDS to run the calendar events fix!");
 668              mtrace('');
 669              mtrace("You can use this CLI tool or upgrade to a version of Moodle that includes");
 670              mtrace("the fix and will be executed as part of the normal upgrade procedure.");
 671              mtrace("The following versions or up are known candidates to upgrade to:");
 672              foreach ($fixsteps as $key => $value) {
 673                  mtrace("  - {$key}: {$value}");
 674              }
 675              mtrace("");
 676          }
 677      }
 678      return $needsfix;
 679  }
 680  
 681  /**
 682   * Detects the calendar events needing to be fixed. With optional output.
 683   *
 684   * @param bool $output true if the function must output information, false if not.
 685   * @return stdClass[] an array of event types (as keys) with total and bad counters, plus sql to retrieve them.
 686   */
 687  function upgrade_calendar_events_status(bool $output = true): array {
 688      global $DB;
 689  
 690      // Calculate the list of standard (core) activity plugins.
 691      $plugins = core_plugin_manager::standard_plugins_list('mod');
 692      $coremodules = "modulename IN ('" . implode("', '", $plugins) . "')";
 693  
 694      // Some query parts go here.
 695      $brokenevents = "(userid = 0 AND (eventtype <> 'user' OR priority <> 0))"; // From the original bad upgrade step.
 696      $standardevents = "(eventtype IN ('site', 'category', 'course', 'group', 'user') AND subscriptionid IS NULL)";
 697      $subscriptionevents = "(subscriptionid IS NOT NULL)";
 698      $overrideevents = "({$coremodules} AND priority IS NOT NULL)";
 699      $actionevents = "({$coremodules} AND instance > 0 and priority IS NULL)";
 700      $otherevents = "(NOT ({$standardevents} OR {$subscriptionevents} OR {$overrideevents} OR {$actionevents}))";
 701  
 702      // Detailed query template.
 703      $detailstemplate = "
 704          SELECT ##group## AS groupname, COUNT(1) AS count
 705            FROM {event}
 706           WHERE ##groupconditions##
 707        GROUP BY ##group##";
 708  
 709      // Count total and potentially broken events.
 710      $total = $DB->count_records_select('event', '');
 711      $totalbadsql = $brokenevents;
 712      $totalbad = $DB->count_records_select('event', $totalbadsql);
 713  
 714      // Standard events.
 715      $standard = $DB->count_records_select('event', $standardevents);
 716      $standardbadsql = "{$brokenevents} AND {$standardevents}";
 717      $standardbad = $DB->count_records_select('event', $standardbadsql);
 718      $standarddetails = $DB->get_records_sql(
 719          str_replace(
 720              ['##group##', '##groupconditions##'],
 721              ['eventtype', $standardbadsql],
 722              $detailstemplate
 723          )
 724      );
 725      array_walk($standarddetails, function (&$rec) {
 726          $rec = $rec->groupname . ': ' . $rec->count;
 727      });
 728      $standarddetails = $standarddetails ? '(' . implode(', ', $standarddetails) . ')' : '- all good!';
 729  
 730      // Subscription events.
 731      $subscription = $DB->count_records_select('event', $subscriptionevents);
 732      $subscriptionbadsql = "{$brokenevents} AND {$subscriptionevents}";
 733      $subscriptionbad = $DB->count_records_select('event', $subscriptionbadsql);
 734      $subscriptiondetails = $DB->get_records_sql(
 735          str_replace(
 736              ['##group##', '##groupconditions##'],
 737              ['eventtype', $subscriptionbadsql],
 738              $detailstemplate
 739          )
 740      );
 741      array_walk($subscriptiondetails, function (&$rec) {
 742          $rec = $rec->groupname . ': ' . $rec->count;
 743      });
 744      $subscriptiondetails = $subscriptiondetails ? '(' . implode(', ', $subscriptiondetails) . ')' : '- all good!';
 745  
 746      // Override events.
 747      $override = $DB->count_records_select('event', $overrideevents);
 748      $overridebadsql = "{$brokenevents} AND {$overrideevents}";
 749      $overridebad = $DB->count_records_select('event', $overridebadsql);
 750      $overridedetails = $DB->get_records_sql(
 751          str_replace(
 752              ['##group##', '##groupconditions##'],
 753              ['modulename', $overridebadsql],
 754              $detailstemplate
 755          )
 756      );
 757      array_walk($overridedetails, function (&$rec) {
 758          $rec = $rec->groupname . ': ' . $rec->count;
 759      });
 760      $overridedetails = $overridedetails ? '(' . implode(', ', $overridedetails) . ')' : '- all good!';
 761  
 762      // Action events.
 763      $action = $DB->count_records_select('event', $actionevents);
 764      $actionbadsql = "{$brokenevents} AND {$actionevents}";
 765      $actionbad = $DB->count_records_select('event', $actionbadsql);
 766      $actiondetails = $DB->get_records_sql(
 767          str_replace(
 768              ['##group##', '##groupconditions##'],
 769              ['modulename', $actionbadsql],
 770              $detailstemplate
 771          )
 772      );
 773      array_walk($actiondetails, function (&$rec) {
 774          $rec = $rec->groupname . ': ' . $rec->count;
 775      });
 776      $actiondetails = $actiondetails ? '(' . implode(', ', $actiondetails) . ')' : '- all good!';
 777  
 778      // Other events.
 779      $other = $DB->count_records_select('event', $otherevents);
 780      $otherbadsql = "{$brokenevents} AND {$otherevents}";
 781      $otherbad = $DB->count_records_select('event', $otherbadsql);
 782      $otherdetails = $DB->get_records_sql(
 783          str_replace(
 784              ['##group##', '##groupconditions##'],
 785              ['COALESCE(component, modulename)', $otherbadsql],
 786              $detailstemplate
 787          )
 788      );
 789      array_walk($otherdetails, function (&$rec) {
 790          $rec = ($rec->groupname ?: 'unknown') . ': ' . $rec->count;
 791      });
 792      $otherdetails = $otherdetails ? '(' . implode(', ', $otherdetails) . ')' : '- all good!';
 793  
 794      // Let's output some textual information if required to.
 795      if ($output) {
 796          mtrace("");
 797          mtrace("Totals: {$total} / {$totalbad} (total / wrong)");
 798          mtrace("  - standards events: {$standard} / {$standardbad} {$standarddetails}");
 799          mtrace("  - subscription events: {$subscription} / {$subscriptionbad} {$subscriptiondetails}");
 800          mtrace("  - override events: {$override} / {$overridebad} {$overridedetails}");
 801          mtrace("  - action events: {$action} / {$actionbad} {$actiondetails}");
 802          mtrace("  - other events: {$other} / {$otherbad} {$otherdetails}");
 803          mtrace("");
 804      }
 805  
 806      return [
 807          'total' => (object)['count' => $total, 'bad' => $totalbad, 'sql' => $totalbadsql],
 808          'standard' => (object)['count' => $standard, 'bad' => $standardbad, 'sql' => $standardbadsql],
 809          'subscription' => (object)['count' => $subscription, 'bad' => $subscriptionbad, 'sql' => $subscriptionbadsql],
 810          'override' => (object)['count' => $override, 'bad' => $overridebad, 'sql' => $overridebadsql],
 811          'action' => (object)['count' => $action, 'bad' => $actionbad, 'sql' => $actionbadsql],
 812          'other' => (object)['count' => $other, 'bad' => $otherbad, 'sql' => $otherbadsql],
 813      ];
 814  }
 815  
 816  /**
 817   * Detects the calendar events needing to be fixed. With optional output.
 818   *
 819   * @param stdClass[] an array of event types (as keys) with total and bad counters, plus sql to retrieve them.
 820   * @param bool $output true if the function must output information, false if not.
 821   * @param int $maxseconds Number of seconds the function will run as max, with zero meaning no limit.
 822   * @return bool true if the function has not finished fixing everything, false if it has finished.
 823   */
 824  function upgrade_calendar_events_fix_remaining(array $info, bool $output = true, int $maxseconds = 0): bool {
 825      global $DB;
 826  
 827      upgrade_calendar_events_mtrace('', $output);
 828  
 829      // Initial preparations.
 830      $starttime = time();
 831      $endtime = $maxseconds ? ($starttime + $maxseconds) : 0;
 832  
 833      // No bad events, or all bad events are "other" events, finished.
 834      if ($info['total']->bad == 0 || $info['total']->bad == $info['other']->bad) {
 835          return false;
 836      }
 837  
 838      // Let's fix overriden events first (they are the ones performing worse with the missing userid).
 839      if ($info['override']->bad != 0) {
 840          if (upgrade_calendar_override_events_fix($info['override'], $output, $endtime)) {
 841              return true; // Not finished yet.
 842          }
 843      }
 844  
 845      // Let's fix the subscription events (like standard ones, but with the event_subscriptions table).
 846      if ($info['subscription']->bad != 0) {
 847          if (upgrade_calendar_subscription_events_fix($info['subscription'], $output, $endtime)) {
 848              return true; // Not finished yet.
 849          }
 850      }
 851  
 852      // Let's fix the standard events (site, category, course, group).
 853      if ($info['standard']->bad != 0) {
 854          if (upgrade_calendar_standard_events_fix($info['standard'], $output, $endtime)) {
 855              return true; // Not finished yet.
 856          }
 857      }
 858  
 859      // Let's fix the action events (all them are "general" ones, not user-specific in core).
 860      if ($info['action']->bad != 0) {
 861          if (upgrade_calendar_action_events_fix($info['action'], $output, $endtime)) {
 862              return true; // Not finished yet.
 863          }
 864      }
 865  
 866      // Have arrived here, finished!
 867      return false;
 868  }
 869  
 870  /**
 871   * Wrapper over mtrace() to allow a few more things to be specified.
 872   *
 873   * @param string $string string to output.
 874   * @param bool $output true to perform the output, false to avoid it.
 875   */
 876  function upgrade_calendar_events_mtrace(string $string, bool $output): void {
 877      static $cols = 0;
 878  
 879      // No output, do nothing.
 880      if (!$output) {
 881          return;
 882      }
 883  
 884      // Printing dots... let's output them slightly nicer.
 885      if ($string === '.') {
 886          $cols++;
 887          // Up to 60 cols.
 888          if ($cols < 60) {
 889              mtrace($string, '');
 890          } else {
 891              mtrace($string);
 892              $cols = 0;
 893          }
 894          return;
 895      }
 896  
 897      // Reset cols, have ended printing dots.
 898      if ($cols) {
 899          $cols = 0;
 900          mtrace('');
 901      }
 902  
 903      // Normal output.
 904      mtrace($string);
 905  }
 906  
 907  /**
 908   * Get a valid editing teacher for a given courseid
 909   *
 910   * @param int $courseid The course to look for editing teachers.
 911   * @return int A user id of an editing teacher or, if missing, the admin userid.
 912   */
 913  function upgrade_calendar_events_get_teacherid(int $courseid): int {
 914  
 915      if ($context = context_course::instance($courseid, IGNORE_MISSING)) {
 916          if ($havemanage = get_users_by_capability($context, 'moodle/course:manageactivities', 'u.id')) {
 917              return array_keys($havemanage)[0];
 918          }
 919      }
 920      return get_admin()->id; // Could not find a teacher, default to admin.
 921  }
 922  
 923  /**
 924   * Detects the calendar standard events needing to be fixed. With optional output.
 925   *
 926   * @param stdClass $info an object with total and bad counters, plus sql to retrieve them.
 927   * @param bool $output true if the function must output information, false if not.
 928   * @param int $endtime cutoff time when the process must stop (0 means no cutoff).
 929   * @return bool true if the function has not finished fixing everything, false if it has finished.
 930   */
 931  function upgrade_calendar_standard_events_fix(stdClass $info, bool $output = true, int $endtime = 0): bool {
 932      global $DB;
 933  
 934      $return = false; // Let's assume the function is going to finish by default.
 935      $status = "Finished!"; // To decide the message to be presented on return.
 936  
 937      upgrade_calendar_events_mtrace('Processing standard events', $output);
 938  
 939      $rs = $DB->get_recordset_sql("
 940          SELECT DISTINCT eventtype, courseid
 941            FROM {event}
 942           WHERE {$info->sql}");
 943  
 944      foreach ($rs as $record) {
 945          switch ($record->eventtype) {
 946              case 'site':
 947              case 'category':
 948                  // These are created by admin.
 949                  $DB->set_field('event', 'userid', get_admin()->id, ['eventtype' => $record->eventtype]);
 950                  break;
 951              case 'course':
 952              case 'group':
 953                  // These are created by course teacher.
 954                  $DB->set_field('event', 'userid', upgrade_calendar_events_get_teacherid($record->courseid),
 955                      ['eventtype' => $record->eventtype, 'courseid' => $record->courseid]);
 956                  break;
 957          }
 958  
 959          // Cutoff time, let's exit.
 960          if ($endtime && $endtime <= time()) {
 961              $status = 'Remaining standard events pending';
 962              $return = true; // Not finished yet.
 963              break;
 964          }
 965          upgrade_calendar_events_mtrace('.', $output);
 966      }
 967      $rs->close();
 968      upgrade_calendar_events_mtrace($status, $output);
 969      upgrade_calendar_events_mtrace('', $output);
 970      return $return;
 971  }
 972  
 973  /**
 974   * Detects the calendar subscription events needing to be fixed. With optional output.
 975   *
 976   * @param stdClass $info an object with total and bad counters, plus sql to retrieve them.
 977   * @param bool $output true if the function must output information, false if not.
 978   * @param int $endtime cutoff time when the process must stop (0 means no cutoff).
 979   * @return bool true if the function has not finished fixing everything, false if it has finished.
 980   */
 981  function upgrade_calendar_subscription_events_fix(stdClass $info, bool $output = true, int $endtime = 0): bool {
 982      global $DB;
 983  
 984      $return = false; // Let's assume the function is going to finish by default.
 985      $status = "Finished!"; // To decide the message to be presented on return.
 986  
 987      upgrade_calendar_events_mtrace('Processing subscription events', $output);
 988  
 989      $rs = $DB->get_recordset_sql("
 990          SELECT DISTINCT subscriptionid AS id
 991            FROM {event}
 992           WHERE {$info->sql}");
 993  
 994      foreach ($rs as $subscription) {
 995          // Subscriptions can be site or category level, let's put the admin as userid.
 996          // (note that "user" subscription weren't deleted so there is nothing to recover with them.
 997          $DB->set_field('event_subscriptions', 'userid', get_admin()->id, ['id' => $subscription->id]);
 998          $DB->set_field('event', 'userid', get_admin()->id, ['subscriptionid' => $subscription->id]);
 999  
1000          // Cutoff time, let's exit.
1001          if ($endtime && $endtime <= time()) {
1002              $status = 'Remaining subscription events pending';
1003              $return = true; // Not finished yet.
1004              break;
1005          }
1006          upgrade_calendar_events_mtrace('.', $output);
1007      }
1008      $rs->close();
1009      upgrade_calendar_events_mtrace($status, $output);
1010      upgrade_calendar_events_mtrace('', $output);
1011      return $return;
1012  }
1013  
1014  /**
1015   * Detects the calendar action events needing to be fixed. With optional output.
1016   *
1017   * @param stdClass $info an object with total and bad counters, plus sql to retrieve them.
1018   * @param bool $output true if the function must output information, false if not.
1019   * @param int $endtime cutoff time when the process must stop (0 means no cutoff).
1020   * @return bool true if the function has not finished fixing everything, false if it has finished.
1021   */
1022  function upgrade_calendar_action_events_fix(stdClass $info, bool $output = true, int $endtime = 0): bool {
1023      global $DB;
1024  
1025      $return = false; // Let's assume the function is going to finish by default.
1026      $status = "Finished!"; // To decide the message to be presented on return.
1027  
1028      upgrade_calendar_events_mtrace('Processing action events', $output);
1029  
1030      $rs = $DB->get_recordset_sql("
1031          SELECT DISTINCT modulename, instance, courseid
1032            FROM {event}
1033           WHERE {$info->sql}");
1034  
1035      foreach ($rs as $record) {
1036          // These are created by course teacher.
1037          $DB->set_field('event', 'userid', upgrade_calendar_events_get_teacherid($record->courseid),
1038              ['modulename' => $record->modulename, 'instance' => $record->instance, 'courseid' => $record->courseid]);
1039  
1040          // Cutoff time, let's exit.
1041          if ($endtime && $endtime <= time()) {
1042              $status = 'Remaining action events pending';
1043              $return = true; // Not finished yet.
1044              break;
1045          }
1046          upgrade_calendar_events_mtrace('.', $output);
1047      }
1048      $rs->close();
1049      upgrade_calendar_events_mtrace($status, $output);
1050      upgrade_calendar_events_mtrace('', $output);
1051      return $return;
1052  }
1053  
1054  /**
1055   * Detects the calendar override events needing to be fixed. With optional output.
1056   *
1057   * @param stdClass $info an object with total and bad counters, plus sql to retrieve them.
1058   * @param bool $output true if the function must output information, false if not.
1059   * @param int $endtime cutoff time when the process must stop (0 means no cutoff).
1060   * @return bool true if the function has not finished fixing everything, false if it has finished.
1061   */
1062  function upgrade_calendar_override_events_fix(stdClass $info, bool $output = true, int $endtime = 0): bool {
1063      global $CFG, $DB;
1064  
1065      include_once($CFG->dirroot. '/course/lib.php');
1066      include_once($CFG->dirroot. '/mod/assign/lib.php');
1067      include_once($CFG->dirroot. '/mod/assign/locallib.php');
1068      include_once($CFG->dirroot. '/mod/lesson/lib.php');
1069      include_once($CFG->dirroot. '/mod/lesson/locallib.php');
1070      include_once($CFG->dirroot. '/mod/quiz/lib.php');
1071      include_once($CFG->dirroot. '/mod/quiz/locallib.php');
1072  
1073      $return = false; // Let's assume the function is going to finish by default.
1074      $status = "Finished!"; // To decide the message to be presented on return.
1075  
1076      upgrade_calendar_events_mtrace('Processing override events', $output);
1077  
1078      $rs = $DB->get_recordset_sql("
1079          SELECT DISTINCT modulename, instance
1080            FROM {event}
1081           WHERE {$info->sql}");
1082  
1083      foreach ($rs as $module) {
1084          // Remove all the records from the events table for the module.
1085          $DB->delete_records('event', ['modulename' => $module->modulename, 'instance' => $module->instance]);
1086  
1087          // Get the activity record.
1088          if (!$activityrecord = $DB->get_record($module->modulename, ['id' => $module->instance])) {
1089              // Orphaned calendar event (activity doesn't exists), skip.
1090              continue;
1091          }
1092  
1093          // Let's rebuild it by calling to each module API.
1094          switch ($module->modulename) {
1095              case 'assign';
1096                  if (function_exists('assign_prepare_update_events')) {
1097                      assign_prepare_update_events($activityrecord);
1098                  }
1099                  break;
1100              case 'lesson':
1101                  if (function_exists('lesson_update_events')) {
1102                      lesson_update_events($activityrecord);
1103                  }
1104                  break;
1105              case 'quiz':
1106                  if (function_exists('quiz_update_events')) {
1107                      quiz_update_events($activityrecord);
1108                  }
1109                  break;
1110          }
1111  
1112          // Sometimes, some (group) overrides are created without userid, when that happens, they deserve
1113          // some user (teacher or admin). This doesn't affect to groups calendar events behaviour,
1114          // but allows counters to detect already processed group overrides and makes things
1115          // consistent.
1116          $DB->set_field_select('event', 'userid', upgrade_calendar_events_get_teacherid($activityrecord->course),
1117              'modulename = ? AND instance = ? and priority != 0 and userid = 0',
1118              ['modulename' => $module->modulename, 'instance' => $module->instance]);
1119  
1120          // Cutoff time, let's exit.
1121          if ($endtime && $endtime <= time()) {
1122              $status = 'Remaining override events pending';
1123              $return = true; // Not finished yet.
1124              break;
1125          }
1126          upgrade_calendar_events_mtrace('.', $output);
1127      }
1128      $rs->close();
1129      upgrade_calendar_events_mtrace($status, $output);
1130      upgrade_calendar_events_mtrace('', $output);
1131      return $return;
1132  }
1133  
1134  /**
1135   * Add a new item at the end of the usermenu.
1136   *
1137   * @param string $menuitem
1138   */
1139  function upgrade_add_item_to_usermenu(string $menuitem): void {
1140      global $CFG;
1141  
1142      // Get current configuration data.
1143      $currentcustomusermenuitems = str_replace(["\r\n", "\r"], "\n", $CFG->customusermenuitems);
1144      $lines = preg_split('/\n/', $currentcustomusermenuitems, -1, PREG_SPLIT_NO_EMPTY);
1145      $lines = array_map('trim', $lines);
1146  
1147      if (!in_array($menuitem, $lines)) {
1148          // Add the item to the menu.
1149          $lines[] = $menuitem;
1150          set_config('customusermenuitems', implode("\n", $lines));
1151      }
1152  }
1153  
1154  /**
1155   * Update all instances of a block shown on a pagetype to a new default region, adding missing block instances where
1156   * none is found.
1157   *
1158   * Note: This is intended as a helper to add blocks to all instances of the standard my-page. It will only work where
1159   * the subpagepattern is a string representation of an integer. If there are any string values this will not work.
1160   *
1161   * @param string $blockname The block name, without the block_ frankenstyle component
1162   * @param string $pagename The type of my-page to match
1163   * @param string $pagetypepattern The page type pattern to match for the block
1164   * @param string $newdefaultregion The new region to set
1165   */
1166  function upgrade_block_set_defaultregion(
1167      string $blockname,
1168      string $pagename,
1169      string $pagetypepattern,
1170      string $newdefaultregion
1171  ): void {
1172      global $DB;
1173  
1174      // The subpagepattern is a string.
1175      // In all core blocks it contains a string represnetation of an integer, but it is theoretically possible for a
1176      // community block to do something different.
1177      // This function is not suited to those cases.
1178      $subpagepattern = $DB->sql_cast_char2int('bi.subpagepattern');
1179      $subpageempty = $DB->sql_isnotempty('block_instances', 'bi.subpagepattern', true, false);
1180  
1181      // If a subquery returns any NULL then the NOT IN returns no results at all.
1182      // By adding a join in the inner select on my_pages we remove any possible nulls and prevent any need for
1183      // additional casting to filter out the nulls.
1184      $sql = <<<EOF
1185          INSERT INTO {block_instances} (
1186              blockname,
1187              parentcontextid,
1188              showinsubcontexts,
1189              pagetypepattern,
1190              subpagepattern,
1191              defaultregion,
1192              defaultweight,
1193              timecreated,
1194              timemodified
1195          ) SELECT
1196              :selectblockname AS blockname,
1197              c.id AS parentcontextid,
1198              0 AS showinsubcontexts,
1199              :selectpagetypepattern AS pagetypepattern,
1200              mp.id AS subpagepattern,
1201              :selectdefaultregion AS defaultregion,
1202              0 AS defaultweight,
1203              :selecttimecreated AS timecreated,
1204              :selecttimemodified AS timemodified
1205            FROM {my_pages} mp
1206            JOIN {context} c ON c.instanceid = mp.userid AND c.contextlevel = :contextuser
1207           WHERE mp.id NOT IN (
1208              SELECT mpi.id FROM {my_pages} mpi
1209                JOIN {block_instances} bi
1210                      ON bi.blockname = :blockname
1211                     AND bi.subpagepattern IS NOT NULL AND {$subpageempty}
1212                     AND bi.pagetypepattern = :pagetypepattern
1213                     AND {$subpagepattern} = mpi.id
1214           )
1215           AND mp.private = 1
1216           AND mp.name = :pagename
1217      EOF;
1218  
1219      $DB->execute($sql, [
1220          'selectblockname' => $blockname,
1221          'contextuser' => CONTEXT_USER,
1222          'selectpagetypepattern' => $pagetypepattern,
1223          'selectdefaultregion' => $newdefaultregion,
1224          'selecttimecreated' => time(),
1225          'selecttimemodified' => time(),
1226          'pagetypepattern' => $pagetypepattern,
1227          'blockname' => $blockname,
1228          'pagename' => $pagename,
1229      ]);
1230  
1231      // Update the existing instances.
1232      $sql = <<<EOF
1233          UPDATE {block_instances}
1234             SET defaultregion = :newdefaultregion
1235           WHERE id IN (
1236              SELECT * FROM (
1237                  SELECT bi.id
1238                    FROM {my_pages} mp
1239                    JOIN {block_instances} bi
1240                          ON bi.blockname = :blockname
1241                         AND bi.subpagepattern IS NOT NULL AND {$subpageempty}
1242                         AND bi.pagetypepattern = :pagetypepattern
1243                         AND {$subpagepattern} = mp.id
1244                   WHERE mp.private = 1
1245                     AND mp.name = :pagename
1246                     AND bi.defaultregion <> :existingnewdefaultregion
1247              ) bid
1248           )
1249      EOF;
1250  
1251      $DB->execute($sql, [
1252          'newdefaultregion' => $newdefaultregion,
1253          'pagetypepattern' => $pagetypepattern,
1254          'blockname' => $blockname,
1255          'existingnewdefaultregion' => $newdefaultregion,
1256          'pagename' => $pagename,
1257      ]);
1258  
1259      // Note: This can be time consuming!
1260      \context_helper::create_instances(CONTEXT_BLOCK);
1261  }
1262  
1263  /**
1264   * Remove all instances of a block on pages of the specified pagetypepattern.
1265   *
1266   * Note: This is intended as a helper to add blocks to all instances of the standard my-page. It will only work where
1267   * the subpagepattern is a string representation of an integer. If there are any string values this will not work.
1268   *
1269   * @param string $blockname The block name, without the block_ frankenstyle component
1270   * @param string $pagename The type of my-page to match
1271   * @param string $pagetypepattern This is typically used on the 'my-index'
1272   */
1273  function upgrade_block_delete_instances(
1274      string $blockname,
1275      string $pagename,
1276      string $pagetypepattern
1277  ): void {
1278      global $DB;
1279  
1280      $deleteblockinstances = function (string $instanceselect, array $instanceparams) use ($DB) {
1281          $deletesql = <<<EOF
1282              SELECT c.id AS cid
1283                FROM {context} c
1284                JOIN {block_instances} bi ON bi.id = c.instanceid AND c.contextlevel = :contextlevel
1285               WHERE {$instanceselect}
1286          EOF;
1287          $DB->delete_records_subquery('context', 'id', 'cid', $deletesql, array_merge($instanceparams, [
1288              'contextlevel' => CONTEXT_BLOCK,
1289          ]));
1290  
1291          $deletesql = <<<EOF
1292              SELECT bp.id AS bpid
1293                FROM {block_positions} bp
1294                JOIN {block_instances} bi ON bi.id = bp.blockinstanceid
1295               WHERE {$instanceselect}
1296          EOF;
1297          $DB->delete_records_subquery('block_positions', 'id', 'bpid', $deletesql, $instanceparams);
1298  
1299          $blockhidden = $DB->sql_concat("'block'", 'bi.id', "'hidden'");
1300          $blockdocked = $DB->sql_concat("'docked_block_instance_'", 'bi.id');
1301          $deletesql = <<<EOF
1302              SELECT p.id AS pid
1303                FROM {user_preferences} p
1304                JOIN {block_instances} bi ON p.name IN ({$blockhidden}, {$blockdocked})
1305               WHERE {$instanceselect}
1306          EOF;
1307          $DB->delete_records_subquery('user_preferences', 'id', 'pid', $deletesql, $instanceparams);
1308  
1309          $deletesql = <<<EOF
1310              SELECT bi.id AS bid
1311                FROM {block_instances} bi
1312               WHERE {$instanceselect}
1313          EOF;
1314          $DB->delete_records_subquery('block_instances', 'id', 'bid', $deletesql, $instanceparams);
1315      };
1316  
1317      // Delete the default indexsys version of the block.
1318      $subpagepattern = $DB->get_record('my_pages', [
1319          'userid' => null,
1320          'name' => $pagename,
1321          'private' => MY_PAGE_PRIVATE,
1322      ], 'id', IGNORE_MULTIPLE)->id;
1323  
1324      $instanceselect = <<<EOF
1325              blockname = :blockname
1326          AND pagetypepattern = :pagetypepattern
1327          AND subpagepattern = :subpagepattern
1328      EOF;
1329  
1330      $params = [
1331          'blockname' => $blockname,
1332          'pagetypepattern' => $pagetypepattern,
1333          'subpagepattern' => $subpagepattern,
1334      ];
1335      $deleteblockinstances($instanceselect, $params);
1336  
1337      // The subpagepattern is a string.
1338      // In all core blocks it contains a string represnetation of an integer, but it is theoretically possible for a
1339      // community block to do something different.
1340      // This function is not suited to those cases.
1341      $subpagepattern = $DB->sql_cast_char2int('bi.subpagepattern');
1342  
1343      // Look for any and all instances of the block in customised /my pages.
1344      $subpageempty = $DB->sql_isnotempty('block_instances', 'bi.subpagepattern', true, false);
1345      $instanceselect = <<<EOF
1346           bi.id IN (
1347              SELECT * FROM (
1348                  SELECT bi.id
1349                    FROM {my_pages} mp
1350                    JOIN {block_instances} bi
1351                          ON bi.blockname = :blockname
1352                         AND bi.subpagepattern IS NOT NULL AND {$subpageempty}
1353                         AND bi.pagetypepattern = :pagetypepattern
1354                         AND {$subpagepattern} = mp.id
1355                   WHERE mp.private = :private
1356                     AND mp.name = :pagename
1357              ) bid
1358           )
1359      EOF;
1360  
1361      $params = [
1362          'blockname' => $blockname,
1363          'pagetypepattern' => $pagetypepattern,
1364          'pagename' => $pagename,
1365          'private' => MY_PAGE_PRIVATE,
1366      ];
1367  
1368      $deleteblockinstances($instanceselect, $params);
1369  }
1370  
1371  /**
1372   * Update the block instance parentcontext to point to the correct user context id for the specified block on a my page.
1373   *
1374   * @param string $blockname
1375   * @param string $pagename
1376   * @param string $pagetypepattern
1377   */
1378  function upgrade_block_set_my_user_parent_context(
1379      string $blockname,
1380      string $pagename,
1381      string $pagetypepattern
1382  ): void {
1383      global $DB;
1384  
1385      $subpagepattern = $DB->sql_cast_char2int('bi.subpagepattern');
1386      // Look for any and all instances of the block in customised /my pages.
1387      $subpageempty = $DB->sql_isnotempty('block_instances', 'bi.subpagepattern', true, false);
1388  
1389      $dbman = $DB->get_manager();
1390      $temptablename = 'block_instance_context';
1391      $xmldbtable = new \xmldb_table($temptablename);
1392      $xmldbtable->add_field('instanceid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
1393      $xmldbtable->add_field('contextid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
1394      $xmldbtable->add_key('primary', XMLDB_KEY_PRIMARY, ['instanceid']);
1395      $dbman->create_temp_table($xmldbtable);
1396  
1397      $sql = <<<EOF
1398          INSERT INTO {block_instance_context} (
1399              instanceid,
1400              contextid
1401          ) SELECT
1402              bi.id as instanceid,
1403              c.id as contextid
1404             FROM {my_pages} mp
1405             JOIN {context} c ON c.instanceid = mp.userid AND c.contextlevel = :contextuser
1406             JOIN {block_instances} bi
1407                  ON bi.blockname = :blockname
1408                 AND bi.subpagepattern IS NOT NULL AND {$subpageempty}
1409                 AND bi.pagetypepattern = :pagetypepattern
1410                 AND {$subpagepattern} = mp.id
1411            WHERE mp.name = :pagename AND bi.parentcontextid <> c.id
1412      EOF;
1413  
1414      $DB->execute($sql, [
1415          'blockname' => $blockname,
1416          'pagetypepattern' => $pagetypepattern,
1417          'contextuser' => CONTEXT_USER,
1418          'pagename' => $pagename,
1419      ]);
1420  
1421      $dbfamily = $DB->get_dbfamily();
1422      if ($dbfamily === 'mysql') {
1423          // MariaDB and MySQL.
1424          $sql = <<<EOF
1425              UPDATE {block_instances} bi, {block_instance_context} bic
1426                 SET bi.parentcontextid = bic.contextid
1427               WHERE bi.id = bic.instanceid
1428          EOF;
1429      } else if ($dbfamily === 'oracle') {
1430          $sql = <<<EOF
1431              UPDATE {block_instances} bi
1432              SET (bi.parentcontextid) = (
1433                  SELECT bic.contextid
1434                    FROM {block_instance_context} bic
1435                   WHERE bic.instanceid = bi.id
1436              ) WHERE EXISTS (
1437                  SELECT 'x'
1438                    FROM {block_instance_context} bic
1439                   WHERE bic.instanceid = bi.id
1440              )
1441          EOF;
1442      } else {
1443          // Postgres and sqlsrv.
1444          $sql = <<<EOF
1445              UPDATE {block_instances}
1446              SET parentcontextid = bic.contextid
1447              FROM {block_instance_context} bic
1448              WHERE {block_instances}.id = bic.instanceid
1449          EOF;
1450      }
1451  
1452      $DB->execute($sql);
1453  
1454      $dbman->drop_table($xmldbtable);
1455  }
1456  
1457  /**
1458   * Fix the timestamps for files where their timestamps are older
1459   * than the directory listing that they are contained in.
1460   */
1461  function upgrade_fix_file_timestamps() {
1462      global $DB;
1463  
1464      // Due to incompatability in SQL syntax for updates with joins,
1465      // These will be updated in a select + separate update.
1466      $sql = "SELECT f.id, f2.timecreated
1467                FROM {files} f
1468                JOIN {files} f2
1469                      ON f2.contextid = f.contextid
1470                     AND f2.filepath = f.filepath
1471                     AND f2.component = f.component
1472                     AND f2.filearea = f.filearea
1473                     AND f2.itemid = f.itemid
1474                     AND f2.filename = '.'
1475               WHERE f2.timecreated > f.timecreated";
1476  
1477      $recordset = $DB->get_recordset_sql($sql);
1478  
1479      if (!$recordset->valid()) {
1480          $recordset->close();
1481          return;
1482      }
1483  
1484      foreach ($recordset as $record) {
1485          $record->timemodified = $record->timecreated;
1486          $DB->update_record('files', $record);
1487      }
1488  
1489      $recordset->close();
1490  }
1491  
1492  /**
1493   * Upgrade helper to add foreign keys and indexes for MDL-49795
1494   */
1495  function upgrade_add_foreign_key_and_indexes() {
1496      global $DB;
1497  
1498      $dbman = $DB->get_manager();
1499      // Define key originalcourseid (foreign) to be added to course.
1500      $table = new xmldb_table('course');
1501      $key = new xmldb_key('originalcourseid', XMLDB_KEY_FOREIGN, ['originalcourseid'], 'course', ['id']);
1502      // Launch add key originalcourseid.
1503      $dbman->add_key($table, $key);
1504  
1505      // Define key roleid (foreign) to be added to enrol.
1506      $table = new xmldb_table('enrol');
1507      $key = new xmldb_key('roleid', XMLDB_KEY_FOREIGN, ['roleid'], 'role', ['id']);
1508      // Launch add key roleid.
1509      $dbman->add_key($table, $key);
1510  
1511      // Define key userid (foreign) to be added to scale.
1512      $table = new xmldb_table('scale');
1513      $key = new xmldb_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
1514      // Launch add key userid.
1515      $dbman->add_key($table, $key);
1516  
1517      // Define key userid (foreign) to be added to scale_history.
1518      $table = new xmldb_table('scale_history');
1519      $key = new xmldb_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
1520      // Launch add key userid.
1521      $dbman->add_key($table, $key);
1522  
1523      // Define key courseid (foreign) to be added to post.
1524      $table = new xmldb_table('post');
1525      $key = new xmldb_key('courseid', XMLDB_KEY_FOREIGN, ['courseid'], 'course', ['id']);
1526      // Launch add key courseid.
1527      $dbman->add_key($table, $key);
1528  
1529      // Define key coursemoduleid (foreign) to be added to post.
1530      $table = new xmldb_table('post');
1531      $key = new xmldb_key('coursemoduleid', XMLDB_KEY_FOREIGN, ['coursemoduleid'], 'course_modules', ['id']);
1532      // Launch add key coursemoduleid.
1533      $dbman->add_key($table, $key);
1534  
1535      // Define key questionid (foreign) to be added to question_statistics.
1536      $table = new xmldb_table('question_statistics');
1537      $key = new xmldb_key('questionid', XMLDB_KEY_FOREIGN, ['questionid'], 'question', ['id']);
1538      // Launch add key questionid.
1539      $dbman->add_key($table, $key);
1540  
1541      // Define key questionid (foreign) to be added to question_response_analysis.
1542      $table = new xmldb_table('question_response_analysis');
1543      $key = new xmldb_key('questionid', XMLDB_KEY_FOREIGN, ['questionid'], 'question', ['id']);
1544      // Launch add key questionid.
1545      $dbman->add_key($table, $key);
1546  
1547      // Define index last_log_id (not unique) to be added to mnet_host.
1548      $table = new xmldb_table('mnet_host');
1549      $index = new xmldb_index('last_log_id', XMLDB_INDEX_NOTUNIQUE, ['last_log_id']);
1550      // Conditionally launch add index last_log_id.
1551      if (!$dbman->index_exists($table, $index)) {
1552          $dbman->add_index($table, $index);
1553      }
1554  
1555      // Define key userid (foreign) to be added to mnet_session.
1556      $table = new xmldb_table('mnet_session');
1557      $key = new xmldb_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
1558      // Launch add key userid.
1559      $dbman->add_key($table, $key);
1560  
1561      // Define key mnethostid (foreign) to be added to mnet_session.
1562      $table = new xmldb_table('mnet_session');
1563      $key = new xmldb_key('mnethostid', XMLDB_KEY_FOREIGN, ['mnethostid'], 'mnet_host', ['id']);
1564      // Launch add key mnethostid.
1565      $dbman->add_key($table, $key);
1566  
1567      // Define key userid (foreign) to be added to grade_import_values.
1568      $table = new xmldb_table('grade_import_values');
1569      $key = new xmldb_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
1570      // Launch add key userid.
1571      $dbman->add_key($table, $key);
1572  
1573      // Define key tempdataid (foreign) to be added to portfolio_log.
1574      $table = new xmldb_table('portfolio_log');
1575      $key = new xmldb_key('tempdataid', XMLDB_KEY_FOREIGN, ['tempdataid'], 'portfolio_tempdata', ['id']);
1576      // Launch add key tempdataid.
1577      $dbman->add_key($table, $key);
1578  
1579      // Define key usermodified (foreign) to be added to file_conversion.
1580      $table = new xmldb_table('file_conversion');
1581      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1582      // Launch add key usermodified.
1583      $dbman->add_key($table, $key);
1584  
1585      // Define key userid (foreign) to be added to repository_instances.
1586      $table = new xmldb_table('repository_instances');
1587      $key = new xmldb_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
1588      // Launch add key userid.
1589      $dbman->add_key($table, $key);
1590  
1591      // Define key contextid (foreign) to be added to repository_instances.
1592      $table = new xmldb_table('repository_instances');
1593      $key = new xmldb_key('contextid', XMLDB_KEY_FOREIGN, ['contextid'], 'context', ['id']);
1594      // Launch add key contextid.
1595      $dbman->add_key($table, $key);
1596  
1597      // Define key scaleid (foreign) to be added to rating.
1598      $table = new xmldb_table('rating');
1599      $key = new xmldb_key('scaleid', XMLDB_KEY_FOREIGN, ['scaleid'], 'scale', ['id']);
1600      // Launch add key scaleid.
1601      $dbman->add_key($table, $key);
1602  
1603      // Define key courseid (foreign) to be added to course_published.
1604      $table = new xmldb_table('course_published');
1605      $key = new xmldb_key('courseid', XMLDB_KEY_FOREIGN, ['courseid'], 'course', ['id']);
1606      // Launch add key courseid.
1607      $dbman->add_key($table, $key);
1608  
1609      // Define index hubcourseid (not unique) to be added to course_published.
1610      $table = new xmldb_table('course_published');
1611      $index = new xmldb_index('hubcourseid', XMLDB_INDEX_NOTUNIQUE, ['hubcourseid']);
1612      // Conditionally launch add index hubcourseid.
1613      if (!$dbman->index_exists($table, $index)) {
1614          $dbman->add_index($table, $index);
1615      }
1616  
1617      // Define key courseid (foreign) to be added to event_subscriptions.
1618      $table = new xmldb_table('event_subscriptions');
1619      $key = new xmldb_key('courseid', XMLDB_KEY_FOREIGN, ['courseid'], 'course', ['id']);
1620      // Launch add key courseid.
1621      $dbman->add_key($table, $key);
1622  
1623      // Define key userid (foreign) to be added to event_subscriptions.
1624      $table = new xmldb_table('event_subscriptions');
1625      $key = new xmldb_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
1626      // Launch add key userid.
1627      $dbman->add_key($table, $key);
1628  
1629      // Define key userid (foreign) to be added to task_log.
1630      $table = new xmldb_table('task_log');
1631      $key = new xmldb_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
1632      // Launch add key userid.
1633      $dbman->add_key($table, $key);
1634  
1635      // Define key scaleid (foreign) to be added to competency.
1636      $table = new xmldb_table('competency');
1637      $key = new xmldb_key('scaleid', XMLDB_KEY_FOREIGN, ['scaleid'], 'scale', ['id']);
1638      // Launch add key scaleid.
1639      $dbman->add_key($table, $key);
1640  
1641      // Define key usermodified (foreign) to be added to competency.
1642      $table = new xmldb_table('competency');
1643      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1644      // Launch add key usermodified.
1645      $dbman->add_key($table, $key);
1646  
1647      // Define key usermodified (foreign) to be added to competency_coursecompsetting.
1648      $table = new xmldb_table('competency_coursecompsetting');
1649      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1650      // Launch add key usermodified.
1651      $dbman->add_key($table, $key);
1652  
1653      // Define key contextid (foreign) to be added to competency_framework.
1654      $table = new xmldb_table('competency_framework');
1655      $key = new xmldb_key('contextid', XMLDB_KEY_FOREIGN, ['contextid'], 'context', ['id']);
1656      // Launch add key contextid.
1657      $dbman->add_key($table, $key);
1658  
1659      // Define key scaleid (foreign) to be added to competency_framework.
1660      $table = new xmldb_table('competency_framework');
1661      $key = new xmldb_key('scaleid', XMLDB_KEY_FOREIGN, ['scaleid'], 'scale', ['id']);
1662      // Launch add key scaleid.
1663      $dbman->add_key($table, $key);
1664  
1665      // Define key usermodified (foreign) to be added to competency_framework.
1666      $table = new xmldb_table('competency_framework');
1667      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1668      // Launch add key usermodified.
1669      $dbman->add_key($table, $key);
1670  
1671      // Define key usermodified (foreign) to be added to competency_coursecomp.
1672      $table = new xmldb_table('competency_coursecomp');
1673      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1674      // Launch add key usermodified.
1675      $dbman->add_key($table, $key);
1676  
1677      // Define key actionuserid (foreign) to be added to competency_evidence.
1678      $table = new xmldb_table('competency_evidence');
1679      $key = new xmldb_key('actionuserid', XMLDB_KEY_FOREIGN, ['actionuserid'], 'user', ['id']);
1680      // Launch add key actionuserid.
1681      $dbman->add_key($table, $key);
1682  
1683      // Define key contextid (foreign) to be added to competency_evidence.
1684      $table = new xmldb_table('competency_evidence');
1685      $key = new xmldb_key('contextid', XMLDB_KEY_FOREIGN, ['contextid'], 'context', ['id']);
1686      // Launch add key contextid.
1687      $dbman->add_key($table, $key);
1688  
1689      // Define key usermodified (foreign) to be added to competency_evidence.
1690      $table = new xmldb_table('competency_evidence');
1691      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1692      // Launch add key usermodified.
1693      $dbman->add_key($table, $key);
1694  
1695      // Define key usermodified (foreign) to be added to competency_userevidence.
1696      $table = new xmldb_table('competency_userevidence');
1697      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1698      // Launch add key usermodified.
1699      $dbman->add_key($table, $key);
1700  
1701      // Define key usermodified (foreign) to be added to competency_plan.
1702      $table = new xmldb_table('competency_plan');
1703      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1704      // Launch add key usermodified.
1705      $dbman->add_key($table, $key);
1706  
1707      // Define key usermodified (foreign) to be added to competency_template.
1708      $table = new xmldb_table('competency_template');
1709      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1710      // Launch add key usermodified.
1711      $dbman->add_key($table, $key);
1712  
1713      // Define key contextid (foreign) to be added to competency_template.
1714      $table = new xmldb_table('competency_template');
1715      $key = new xmldb_key('contextid', XMLDB_KEY_FOREIGN, ['contextid'], 'context', ['id']);
1716      // Launch add key contextid.
1717      $dbman->add_key($table, $key);
1718  
1719      // Define key usermodified (foreign) to be added to competency_templatecomp.
1720      $table = new xmldb_table('competency_templatecomp');
1721      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1722      // Launch add key usermodified.
1723      $dbman->add_key($table, $key);
1724  
1725      // Define key usermodified (foreign) to be added to competency_templatecohort.
1726      $table = new xmldb_table('competency_templatecohort');
1727      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1728      // Launch add key usermodified.
1729      $dbman->add_key($table, $key);
1730  
1731      // Define key competencyid (foreign) to be added to competency_relatedcomp.
1732      $table = new xmldb_table('competency_relatedcomp');
1733      $key = new xmldb_key('competencyid', XMLDB_KEY_FOREIGN, ['competencyid'], 'competency', ['id']);
1734      // Launch add key competencyid.
1735      $dbman->add_key($table, $key);
1736  
1737      // Define key relatedcompetencyid (foreign) to be added to competency_relatedcomp.
1738      $table = new xmldb_table('competency_relatedcomp');
1739      $key = new xmldb_key('relatedcompetencyid', XMLDB_KEY_FOREIGN, ['relatedcompetencyid'], 'competency', ['id']);
1740      // Launch add key relatedcompetencyid.
1741      $dbman->add_key($table, $key);
1742  
1743      // Define key usermodified (foreign) to be added to competency_relatedcomp.
1744      $table = new xmldb_table('competency_relatedcomp');
1745      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1746      // Launch add key usermodified.
1747      $dbman->add_key($table, $key);
1748  
1749      // Define key usermodified (foreign) to be added to competency_usercomp.
1750      $table = new xmldb_table('competency_usercomp');
1751      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1752      // Launch add key usermodified.
1753      $dbman->add_key($table, $key);
1754  
1755      // Define key usermodified (foreign) to be added to competency_usercompcourse.
1756      $table = new xmldb_table('competency_usercompcourse');
1757      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1758      // Launch add key usermodified.
1759      $dbman->add_key($table, $key);
1760  
1761      // Define key usermodified (foreign) to be added to competency_usercompplan.
1762      $table = new xmldb_table('competency_usercompplan');
1763      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1764      // Launch add key usermodified.
1765      $dbman->add_key($table, $key);
1766  
1767      // Define key usermodified (foreign) to be added to competency_plancomp.
1768      $table = new xmldb_table('competency_plancomp');
1769      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1770      // Launch add key usermodified.
1771      $dbman->add_key($table, $key);
1772  
1773      // Define key usermodified (foreign) to be added to competency_userevidencecomp.
1774      $table = new xmldb_table('competency_userevidencecomp');
1775      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1776      // Launch add key usermodified.
1777      $dbman->add_key($table, $key);
1778  
1779      // Define key usermodified (foreign) to be added to competency_modulecomp.
1780      $table = new xmldb_table('competency_modulecomp');
1781      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1782      // Launch add key usermodified.
1783      $dbman->add_key($table, $key);
1784  
1785      // Define key usermodified (foreign) to be added to oauth2_endpoint.
1786      $table = new xmldb_table('oauth2_endpoint');
1787      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1788      // Launch add key usermodified.
1789      $dbman->add_key($table, $key);
1790  
1791      // Define key usermodified (foreign) to be added to oauth2_system_account.
1792      $table = new xmldb_table('oauth2_system_account');
1793      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1794      // Launch add key usermodified.
1795      $dbman->add_key($table, $key);
1796  
1797      // Define key usermodified (foreign) to be added to oauth2_user_field_mapping.
1798      $table = new xmldb_table('oauth2_user_field_mapping');
1799      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1800      // Launch add key usermodified.
1801      $dbman->add_key($table, $key);
1802  
1803      // Define key usermodified (foreign) to be added to analytics_models.
1804      $table = new xmldb_table('analytics_models');
1805      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1806      // Launch add key usermodified.
1807      $dbman->add_key($table, $key);
1808  
1809      // Define key usermodified (foreign) to be added to analytics_models_log.
1810      $table = new xmldb_table('analytics_models_log');
1811      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1812      // Launch add key usermodified.
1813      $dbman->add_key($table, $key);
1814  
1815      // Define key usermodified (foreign) to be added to oauth2_access_token.
1816      $table = new xmldb_table('oauth2_access_token');
1817      $key = new xmldb_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
1818      // Launch add key usermodified.
1819      $dbman->add_key($table, $key);
1820  
1821      // Define key contextid (foreign) to be added to payment_accounts.
1822      $table = new xmldb_table('payment_accounts');
1823      $key = new xmldb_key('contextid', XMLDB_KEY_FOREIGN, ['contextid'], 'context', ['id']);
1824      // Launch add key contextid.
1825      $dbman->add_key($table, $key);
1826  }