lib/db/upgrade.php

   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  * This file keeps track of upgrades to Moodle.
  19  *
  20  * Sometimes, changes between versions involve
  21  * alterations to database structures and other
  22  * major things that may break installations.
  23  *
  24  * The upgrade function in this file will attempt
  25  * to perform all the necessary actions to upgrade
  26  * your older installation to the current version.
  27  *
  28  * If there's something it cannot do itself, it
  29  * will tell you what you need to do.
  30  *
  31  * The commands in here will all be database-neutral,
  32  * using the methods of database_manager class
  33  *
  34  * Please do not forget to use upgrade_set_timeout()
  35  * before any action that may take longer time to finish.
  36  *
  37  * @package   core_install
  38  * @category  upgrade
  39  * @copyright 2006 onwards Martin Dougiamas  http://dougiamas.com
  40  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  41  */
  42
  43 defined('MOODLE_INTERNAL') || die();
  44
  45 /**
  46  * Main upgrade tasks to be executed on Moodle version bump
  47  *
  48  * This function is automatically executed after one bump in the Moodle core
  49  * version is detected. It's in charge of performing the required tasks
  50  * to raise core from the previous version to the next one.
  51  *
  52  * It's a collection of ordered blocks of code, named "upgrade steps",
  53  * each one performing one isolated (from the rest of steps) task. Usually
  54  * tasks involve creating new DB objects or performing manipulation of the
  55  * information for cleanup/fixup purposes.
  56  *
  57  * Each upgrade step has a fixed structure, that can be summarised as follows:
  58  *
  59  * if ($oldversion < XXXXXXXXXX.XX) {
  60  *     // Explanation of the update step, linking to issue in the Tracker if necessary
  61  *     upgrade_set_timeout(XX); // Optional for big tasks
  62  *     // Code to execute goes here, usually the XMLDB Editor will
  63  *     // help you here. See {@link http://docs.moodle.org/dev/XMLDB_editor}.
  64  *     upgrade_main_savepoint(true, XXXXXXXXXX.XX);
  65  * }
  66  *
  67  * All plugins within Moodle (modules, blocks, reports...) support the existence of
  68  * their own upgrade.php file, using the "Frankenstyle" component name as
  69  * defined at {@link http://docs.moodle.org/dev/Frankenstyle}, for example:
  70  *     - {@link xmldb_page_upgrade($oldversion)}. (modules don't require the plugintype ("mod_") to be used.
  71  *     - {@link xmldb_auth_manual_upgrade($oldversion)}.
  72  *     - {@link xmldb_workshopform_accumulative_upgrade($oldversion)}.
  73  *     - ....
  74  *
  75  * In order to keep the contents of this file reduced, it's allowed to create some helper
  76  * functions to be used here in the {@link upgradelib.php} file at the same directory. Note
  77  * that such a file must be manually included from upgrade.php, and there are some restrictions
  78  * about what can be used within it.
  79  *
  80  * For more information, take a look to the documentation available:
  81  *     - Data definition API: {@link http://docs.moodle.org/dev/Data_definition_API}
  82  *     - Upgrade API: {@link http://docs.moodle.org/dev/Upgrade_API}
  83  *
  84  * @param int $oldversion
  85  * @return bool always true
  86  */
  87 function xmldb_main_upgrade($oldversion) {
  88     global $CFG, $DB;
  89
  90     require_once($CFG->libdir.'/db/upgradelib.php'); // Core Upgrade-related functions.
  91
  92     $dbman = $DB->get_manager(); // Loads ddl manager and xmldb classes.
  93
  94     // Always keep this upgrade step with version being the minimum
  95     // allowed version to upgrade from (v3.5.0 right now).
  96     if ($oldversion < 2018051700) {
  97         // Just in case somebody hacks upgrade scripts or env, we really can not continue.
  98         echo("You need to upgrade to 3.5.x or higher first!\n");
  99         exit(1);
 100         // Note this savepoint is 100% unreachable, but needed to pass the upgrade checks.
 101         upgrade_main_savepoint(true, 2018051700);
 102     }
 103
 104     // Automatically generated Moodle v3.5.0 release upgrade line.
 105     // Put any upgrade step following this.
 106
 107     if ($oldversion < 2018062800.01) {
 108         // Add foreign key fk_user to the comments table.
 109         $table = new xmldb_table('comments');
 110         $key = new xmldb_key('fk_user', XMLDB_KEY_FOREIGN, array('userid'), 'user', array('id'));
 111         $dbman->add_key($table, $key);
 112
 113         upgrade_main_savepoint(true, 2018062800.01);
 114     }
 115
 116     if ($oldversion < 2018062800.02) {
 117         // Add composite index ix_concomitem to the table comments.
 118         $table = new xmldb_table('comments');
 119         $index = new xmldb_index('ix_concomitem', XMLDB_INDEX_NOTUNIQUE, array('contextid', 'commentarea', 'itemid'));
 120
 121         if (!$dbman->index_exists($table, $index)) {
 122             $dbman->add_index($table, $index);
 123         }
 124
 125         upgrade_main_savepoint(true, 2018062800.02);
 126     }
 127
 128     if ($oldversion < 2018062800.03) {
 129         // Define field location to be added to event.
 130         $table = new xmldb_table('event');
 131         $field = new xmldb_field('location', XMLDB_TYPE_TEXT, null, null, null, null, null, 'priority');
 132
 133         // Conditionally launch add field location.
 134         if (!$dbman->field_exists($table, $field)) {
 135             $dbman->add_field($table, $field);
 136         }
 137
 138         // Main savepoint reached.
 139         upgrade_main_savepoint(true, 2018062800.03);
 140     }
 141
 142     if ($oldversion < 2018072500.00) {
 143         // Find all duplicate top level categories per context.
 144         $duplicates = $DB->get_records_sql("SELECT qc1.*
 145                                               FROM {question_categories} qc1
 146                                               JOIN {question_categories} qc2
 147                                                 ON qc1.contextid = qc2.contextid AND qc1.id <> qc2.id
 148                                              WHERE qc1.parent = 0 AND qc2.parent = 0
 149                                           ORDER BY qc1.contextid, qc1.id");
 150
 151         // For each context, let the first top category to remain as top category and make the rest its children.
 152         $currentcontextid = 0;
 153         $chosentopid = 0;
 154         foreach ($duplicates as $duplicate) {
 155             if ($currentcontextid != $duplicate->contextid) {
 156                 $currentcontextid = $duplicate->contextid;
 157                 $chosentopid = $duplicate->id;
 158             } else {
 159                 $DB->set_field('question_categories', 'parent', $chosentopid, ['id' => $duplicate->id]);
 160             }
 161         }
 162
 163         // Main savepoint reached.
 164         upgrade_main_savepoint(true, 2018072500.00);
 165     }
 166
 167     if ($oldversion < 2018073000.00) {
 168         // Main savepoint reached.
 169         if (!file_exists($CFG->dirroot . '/admin/tool/assignmentupgrade/version.php')) {
 170             unset_all_config_for_plugin('tool_assignmentupgrade');
 171         }
 172         upgrade_main_savepoint(true, 2018073000.00);
 173     }
 174
 175     if ($oldversion < 2018083100.01) {
 176         // Remove module associated blog posts for non-existent (deleted) modules.
 177         $sql = "SELECT ba.contextid as modcontextid
 178                   FROM {blog_association} ba
 179                   JOIN {post} p
 180                        ON p.id = ba.blogid
 181              LEFT JOIN {context} c
 182                        ON c.id = ba.contextid
 183                  WHERE p.module = :module
 184                        AND c.contextlevel IS NULL
 185               GROUP BY ba.contextid";
 186         if ($deletedmodules = $DB->get_records_sql($sql, array('module' => 'blog'))) {
 187             foreach ($deletedmodules as $module) {
 188                 $assocblogids = $DB->get_fieldset_select('blog_association', 'blogid',
 189                     'contextid = :contextid', ['contextid' => $module->modcontextid]);
 190                 list($sql, $params) = $DB->get_in_or_equal($assocblogids, SQL_PARAMS_NAMED);
 191
 192                 $DB->delete_records_select('tag_instance', "itemid $sql", $params);
 193                 $DB->delete_records_select('post', "id $sql AND module = :module",
 194                     array_merge($params, ['module' => 'blog']));
 195                 $DB->delete_records('blog_association', ['contextid' => $module->modcontextid]);
 196             }
 197         }
 198
 199         // Main savepoint reached.
 200         upgrade_main_savepoint(true, 2018083100.01);
 201     }
 202
 203     if ($oldversion < 2018091200.00) {
 204         if (!file_exists($CFG->dirroot . '/cache/stores/memcache/settings.php')) {
 205             unset_all_config_for_plugin('cachestore_memcache');
 206         }
 207
 208         upgrade_main_savepoint(true, 2018091200.00);
 209     }
 210
 211     if ($oldversion < 2018091700.01) {
 212         // Remove unused setting.
 213         unset_config('messaginghidereadnotifications');
 214
 215         // Main savepoint reached.
 216         upgrade_main_savepoint(true, 2018091700.01);
 217     }
 218
 219     // Add idnumber fields to question and question_category tables.
 220     // This is done in four parts to aid error recovery during upgrade, should that occur.
 221     if ($oldversion < 2018092100.01) {
 222         $table = new xmldb_table('question');
 223         $field = new xmldb_field('idnumber', XMLDB_TYPE_CHAR, '100', null, null, null, null, 'modifiedby');
 224         if (!$dbman->field_exists($table, $field)) {
 225             $dbman->add_field($table, $field);
 226         }
 227         upgrade_main_savepoint(true, 2018092100.01);
 228     }
 229
 230     if ($oldversion < 2018092100.02) {
 231         $table = new xmldb_table('question');
 232         $index = new xmldb_index('categoryidnumber', XMLDB_INDEX_UNIQUE, array('category', 'idnumber'));
 233         if (!$dbman->index_exists($table, $index)) {
 234             $dbman->add_index($table, $index);
 235         }
 236         upgrade_main_savepoint(true, 2018092100.02);
 237     }
 238
 239     if ($oldversion < 2018092100.03) {
 240         $table = new xmldb_table('question_categories');
 241         $field = new xmldb_field('idnumber', XMLDB_TYPE_CHAR, '100', null, null, null, null, 'sortorder');
 242         if (!$dbman->field_exists($table, $field)) {
 243             $dbman->add_field($table, $field);
 244         }
 245         upgrade_main_savepoint(true, 2018092100.03);
 246     }
 247
 248     if ($oldversion < 2018092100.04) {
 249         $table = new xmldb_table('question_categories');
 250         $index = new xmldb_index('contextididnumber', XMLDB_INDEX_UNIQUE, array('contextid', 'idnumber'));
 251         if (!$dbman->index_exists($table, $index)) {
 252             $dbman->add_index($table, $index);
 253         }
 254         // Main savepoint reached.
 255         upgrade_main_savepoint(true, 2018092100.04);
 256     }
 257
 258     if ($oldversion < 2018092800.00) {
 259         // Alter the table 'message_contacts'.
 260         $table = new xmldb_table('message_contacts');
 261
 262         // Remove index so we can alter the fields.
 263         $index = new xmldb_index('userid-contactid', XMLDB_INDEX_UNIQUE, ['userid', 'contactid']);
 264         if ($dbman->index_exists($table, $index)) {
 265             $dbman->drop_index($table, $index);
 266         }
 267
 268         // Remove defaults of '0' from the 'userid' and 'contactid' fields.
 269         $field = new xmldb_field('userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null, 'id');
 270         $dbman->change_field_default($table, $field);
 271
 272         $field = new xmldb_field('contactid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null, 'userid');
 273         $dbman->change_field_default($table, $field);
 274
 275         // Add the missing FKs that will now be added to new installs.
 276         $key = new xmldb_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
 277         $dbman->add_key($table, $key);
 278
 279         $key = new xmldb_key('contactid', XMLDB_KEY_FOREIGN, ['contactid'], 'user', ['id']);
 280         $dbman->add_key($table, $key);
 281
 282         // Re-add the index.
 283         if (!$dbman->index_exists($table, $index)) {
 284             $dbman->add_index($table, $index);
 285         }
 286
 287         // Add the field 'timecreated'. Allow null, since existing records won't have an accurate value we can use.
 288         $field = new xmldb_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'blocked');
 289         if (!$dbman->field_exists($table, $field)) {
 290             $dbman->add_field($table, $field);
 291         }
 292
 293         // Create new 'message_contact_requests' table.
 294         $table = new xmldb_table('message_contact_requests');
 295         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null);
 296         $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null, 'id');
 297         $table->add_field('requesteduserid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null, 'userid');
 298         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null, 'requesteduserid');
 299
 300         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id'], null, null);
 301         $table->add_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
 302         $table->add_key('requesteduserid', XMLDB_KEY_FOREIGN, ['requesteduserid'], 'user', ['id']);
 303
 304         $table->add_index('userid-requesteduserid', XMLDB_INDEX_UNIQUE, ['userid', 'requesteduserid']);
 305
 306         if (!$dbman->table_exists($table)) {
 307             $dbman->create_table($table);
 308         }
 309
 310         // Create new 'message_users_blocked' table.
 311         $table = new xmldb_table('message_users_blocked');
 312         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null);
 313         $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null, 'id');
 314         $table->add_field('blockeduserid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null, 'userid');
 315         // Allow NULLs in the 'timecreated' field because we will be moving existing data here that has no timestamp.
 316         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'blockeduserid');
 317
 318         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id'], null, null);
 319         $table->add_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
 320         $table->add_key('blockeduserid', XMLDB_KEY_FOREIGN, ['blockeduserid'], 'user', ['id']);
 321
 322         $table->add_index('userid-blockeduserid', XMLDB_INDEX_UNIQUE, ['userid', 'blockeduserid']);
 323
 324         if (!$dbman->table_exists($table)) {
 325             $dbman->create_table($table);
 326         }
 327
 328         upgrade_main_savepoint(true, 2018092800.00);
 329     }
 330
 331     if ($oldversion < 2018092800.01) {
 332         // Move all the 'blocked' contacts to the new table 'message_users_blocked'.
 333         $updatesql = "INSERT INTO {message_users_blocked} (userid, blockeduserid, timecreated)
 334                            SELECT userid, contactid, null as timecreated
 335                              FROM {message_contacts}
 336                             WHERE blocked = :blocked";
 337         $DB->execute($updatesql, ['blocked' => 1]);
 338
 339         // Removed the 'blocked' column from 'message_contacts'.
 340         $table = new xmldb_table('message_contacts');
 341         $field = new xmldb_field('blocked');
 342         $dbman->drop_field($table, $field);
 343
 344         upgrade_main_savepoint(true, 2018092800.01);
 345     }
 346
 347     if ($oldversion < 2018092800.02) {
 348         // Delete any contacts that are not mutual (meaning they both haven't added each other).
 349         do {
 350             $sql = "SELECT c1.id
 351                       FROM {message_contacts} c1
 352                  LEFT JOIN {message_contacts} c2
 353                         ON c1.userid = c2.contactid
 354                        AND c1.contactid = c2.userid
 355                      WHERE c2.id IS NULL";
 356             if ($contacts = $DB->get_records_sql($sql, null, 0, 1000)) {
 357                 list($insql, $inparams) = $DB->get_in_or_equal(array_keys($contacts));
 358                 $DB->delete_records_select('message_contacts', "id $insql", $inparams);
 359             }
 360         } while ($contacts);
 361
 362         upgrade_main_savepoint(true, 2018092800.02);
 363     }
 364
 365     if ($oldversion < 2018092800.03) {
 366         // Remove any duplicate rows - from now on adding contacts just requires 1 row.
 367         // The person who made the contact request (userid) and the person who approved
 368         // it (contactid). Upgrade the table so that the first person to add the contact
 369         // was the one who made the request.
 370         $sql = "SELECT c1.id
 371                   FROM {message_contacts} c1
 372             INNER JOIN {message_contacts} c2
 373                     ON c1.userid = c2.contactid
 374                    AND c1.contactid = c2.userid
 375                  WHERE c1.id > c2.id";
 376         if ($contacts = $DB->get_records_sql($sql)) {
 377             list($insql, $inparams) = $DB->get_in_or_equal(array_keys($contacts));
 378             $DB->delete_records_select('message_contacts', "id $insql", $inparams);
 379         }
 380
 381         upgrade_main_savepoint(true, 2018092800.03);
 382     }
 383
 384     if ($oldversion < 2018101700.01) {
 385         if (empty($CFG->keepmessagingallusersenabled)) {
 386             // When it is not set, $CFG->messagingallusers should be disabled by default.
 387             // When $CFG->messagingallusers = false, the default user preference is MESSAGE_PRIVACY_COURSEMEMBER
 388             // (contacted by users sharing a course).
 389             set_config('messagingallusers', false);
 390         } else {
 391             // When $CFG->keepmessagingallusersenabled is set to true, $CFG->messagingallusers is set to true.
 392             set_config('messagingallusers', true);
 393
 394             // When $CFG->messagingallusers = true, the default user preference is MESSAGE_PRIVACY_SITE
 395             // (contacted by all users site). So we need to set existing values from 0 (MESSAGE_PRIVACY_COURSEMEMBER)
 396             // to 2 (MESSAGE_PRIVACY_SITE).
 397             $DB->set_field(
 398                 'user_preferences',
 399                 'value',
 400                 \core_message\api::MESSAGE_PRIVACY_SITE,
 401                 array('name' => 'message_blocknoncontacts', 'value' => 0)
 402             );
 403         }
 404
 405         // Main savepoint reached.
 406         upgrade_main_savepoint(true, 2018101700.01);
 407     }
 408
 409     if ($oldversion < 2018101800.00) {
 410         // Define table 'favourite' to be created.
 411         $table = new xmldb_table('favourite');
 412
 413         // Adding fields to table favourite.
 414         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 415         $table->add_field('component', XMLDB_TYPE_CHAR, '100', null, XMLDB_NOTNULL, null, null);
 416         $table->add_field('itemtype', XMLDB_TYPE_CHAR, '100', null, XMLDB_NOTNULL, null, null);
 417         $table->add_field('itemid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 418         $table->add_field('contextid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 419         $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 420         $table->add_field('ordering', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
 421         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 422         $table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 423
 424         // Adding keys to table favourite.
 425         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
 426         $table->add_key('contextid', XMLDB_KEY_FOREIGN, ['contextid'], 'context', ['id']);
 427         $table->add_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
 428
 429         // Adding indexes to table favourite.
 430         $table->add_index('uniqueuserfavouriteitem', XMLDB_INDEX_UNIQUE, ['component', 'itemtype', 'itemid', 'contextid', 'userid']);
 431
 432         // Conditionally launch create table for favourite.
 433         if (!$dbman->table_exists($table)) {
 434             $dbman->create_table($table);
 435         }
 436
 437         // Main savepoint reached.
 438         upgrade_main_savepoint(true, 2018101800.00);
 439     }
 440
 441     if ($oldversion < 2018102200.00) {
 442         // Add field 'type' to 'message_conversations'.
 443         $table = new xmldb_table('message_conversations');
 444         $field = new xmldb_field('type', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, 1, 'id');
 445         if (!$dbman->field_exists($table, $field)) {
 446             $dbman->add_field($table, $field);
 447         }
 448
 449         // Add field 'name' to 'message_conversations'.
 450         $field = new xmldb_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, 'type');
 451         if (!$dbman->field_exists($table, $field)) {
 452             $dbman->add_field($table, $field);
 453         }
 454
 455         // Conditionally launch add index 'type'.
 456         $index = new xmldb_index('type', XMLDB_INDEX_NOTUNIQUE, ['type']);
 457         if (!$dbman->index_exists($table, $index)) {
 458             $dbman->add_index($table, $index);
 459         }
 460
 461         // Define table 'message_conversations' to be updated.
 462         $table = new xmldb_table('message_conversations');
 463
 464         // Remove the unique 'convhash' index, change to null and add a new non unique index.
 465         $index = new xmldb_index('convhash', XMLDB_INDEX_UNIQUE, ['convhash']);
 466         if ($dbman->index_exists($table, $index)) {
 467             $dbman->drop_index($table, $index);
 468         }
 469
 470         $field = new xmldb_field('convhash', XMLDB_TYPE_CHAR, '40', null, null, null, null, 'name');
 471         $dbman->change_field_notnull($table, $field);
 472
 473         $index = new xmldb_index('convhash', XMLDB_INDEX_NOTUNIQUE, ['convhash']);
 474         if (!$dbman->index_exists($table, $index)) {
 475             $dbman->add_index($table, $index);
 476         }
 477
 478         upgrade_main_savepoint(true, 2018102200.00);
 479     }
 480
 481     if ($oldversion < 2018102300.02) {
 482         // Alter 'message_conversations' table to support groups.
 483         $table = new xmldb_table('message_conversations');
 484         $field = new xmldb_field('component', XMLDB_TYPE_CHAR, '100', null, null, null, null, 'convhash');
 485         if (!$dbman->field_exists($table, $field)) {
 486             $dbman->add_field($table, $field);
 487         }
 488
 489         $field = new xmldb_field('itemtype', XMLDB_TYPE_CHAR, '100', null, null, null, null, 'component');
 490         if (!$dbman->field_exists($table, $field)) {
 491             $dbman->add_field($table, $field);
 492         }
 493
 494         $field = new xmldb_field('itemid', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'itemtype');
 495         if (!$dbman->field_exists($table, $field)) {
 496             $dbman->add_field($table, $field);
 497         }
 498
 499         $field = new xmldb_field('contextid', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'itemid');
 500         if (!$dbman->field_exists($table, $field)) {
 501             $dbman->add_field($table, $field);
 502         }
 503
 504         $field = new xmldb_field('enabled', XMLDB_TYPE_INTEGER, '1', null, XMLDB_NOTNULL, null, 0, 'contextid');
 505         if (!$dbman->field_exists($table, $field)) {
 506             $dbman->add_field($table, $field);
 507         }
 508
 509         $field = new xmldb_field('timemodified', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'enabled');
 510         if (!$dbman->field_exists($table, $field)) {
 511             $dbman->add_field($table, $field);
 512         }
 513
 514         // Add key.
 515         $key = new xmldb_key('contextid', XMLDB_KEY_FOREIGN, ['contextid'], 'context', ['id']);
 516         $dbman->add_key($table, $key);
 517
 518         // Add index.
 519         $index = new xmldb_index('component-itemtype-itemid-contextid', XMLDB_INDEX_NOTUNIQUE, ['component', 'itemtype',
 520             'itemid', 'contextid']);
 521         if (!$dbman->index_exists($table, $index)) {
 522             $dbman->add_index($table, $index);
 523         }
 524
 525         upgrade_main_savepoint(true, 2018102300.02);
 526     }
 527
 528     if ($oldversion < 2018102900.00) {
 529         // Define field predictionsprocessor to be added to analytics_models.
 530         $table = new xmldb_table('analytics_models');
 531         $field = new xmldb_field('predictionsprocessor', XMLDB_TYPE_CHAR, '255', null, null, null, null, 'timesplitting');
 532
 533         // Conditionally launch add field predictionsprocessor.
 534         if (!$dbman->field_exists($table, $field)) {
 535             $dbman->add_field($table, $field);
 536         }
 537
 538         // Main savepoint reached.
 539         upgrade_main_savepoint(true, 2018102900.00);
 540     }
 541
 542     if ($oldversion < 2018110500.01) {
 543         // Define fields to be added to the 'badge' table.
 544         $tablebadge = new xmldb_table('badge');
 545         $fieldversion = new xmldb_field('version', XMLDB_TYPE_CHAR, '255', null, null, null, null, 'nextcron');
 546         $fieldlanguage = new xmldb_field('language', XMLDB_TYPE_CHAR, '255', null, null, null, null, 'version');
 547         $fieldimageauthorname = new xmldb_field('imageauthorname', XMLDB_TYPE_CHAR, '255', null, null, null, null, 'language');
 548         $fieldimageauthoremail = new xmldb_field('imageauthoremail', XMLDB_TYPE_CHAR, '255', null, null,
 549             null, null, 'imageauthorname');
 550         $fieldimageauthorurl = new xmldb_field('imageauthorurl', XMLDB_TYPE_CHAR, '255', null, null,
 551             null, null, 'imageauthoremail');
 552         $fieldimagecaption = new xmldb_field('imagecaption', XMLDB_TYPE_TEXT, null, null, null, null, null, 'imageauthorurl');
 553
 554         if (!$dbman->field_exists($tablebadge, $fieldversion)) {
 555             $dbman->add_field($tablebadge, $fieldversion);
 556         }
 557         if (!$dbman->field_exists($tablebadge, $fieldlanguage)) {
 558             $dbman->add_field($tablebadge, $fieldlanguage);
 559         }
 560         if (!$dbman->field_exists($tablebadge, $fieldimageauthorname)) {
 561             $dbman->add_field($tablebadge, $fieldimageauthorname);
 562         }
 563         if (!$dbman->field_exists($tablebadge, $fieldimageauthoremail)) {
 564             $dbman->add_field($tablebadge, $fieldimageauthoremail);
 565         }
 566         if (!$dbman->field_exists($tablebadge, $fieldimageauthorurl)) {
 567             $dbman->add_field($tablebadge, $fieldimageauthorurl);
 568         }
 569         if (!$dbman->field_exists($tablebadge, $fieldimagecaption)) {
 570             $dbman->add_field($tablebadge, $fieldimagecaption);
 571         }
 572
 573         // Define table badge_endorsement to be created.
 574         $table = new xmldb_table('badge_endorsement');
 575
 576         // Adding fields to table badge_endorsement.
 577         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 578         $table->add_field('badgeid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 579         $table->add_field('issuername', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
 580         $table->add_field('issuerurl', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
 581         $table->add_field('issueremail', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
 582         $table->add_field('claimid', XMLDB_TYPE_CHAR, '255', null, null, null, null);
 583         $table->add_field('claimcomment', XMLDB_TYPE_TEXT, null, null, null, null, null);
 584         $table->add_field('dateissued', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 585
 586         // Adding keys to table badge_endorsement.
 587         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
 588         $table->add_key('endorsementbadge', XMLDB_KEY_FOREIGN, ['badgeid'], 'badge', ['id']);
 589
 590         // Conditionally launch create table for badge_endorsement.
 591         if (!$dbman->table_exists($table)) {
 592             $dbman->create_table($table);
 593         }
 594
 595         // Define table badge_related to be created.
 596         $table = new xmldb_table('badge_related');
 597
 598         // Adding fields to table badge_related.
 599         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 600         $table->add_field('badgeid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 601         $table->add_field('relatedbadgeid', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
 602
 603         // Adding keys to table badge_related.
 604         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
 605         $table->add_key('badgeid', XMLDB_KEY_FOREIGN, ['badgeid'], 'badge', ['id']);
 606         $table->add_key('relatedbadgeid', XMLDB_KEY_FOREIGN, ['relatedbadgeid'], 'badge', ['id']);
 607         $table->add_key('badgeid-relatedbadgeid', XMLDB_KEY_UNIQUE, ['badgeid', 'relatedbadgeid']);
 608
 609         // Conditionally launch create table for badge_related.
 610         if (!$dbman->table_exists($table)) {
 611             $dbman->create_table($table);
 612         }
 613
 614         // Define table badge_competencies to be created.
 615         $table = new xmldb_table('badge_competencies');
 616
 617         // Adding fields to table badge_competencies.
 618         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 619         $table->add_field('badgeid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 620         $table->add_field('targetname', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
 621         $table->add_field('targeturl', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
 622         $table->add_field('targetdescription', XMLDB_TYPE_TEXT, null, null, null, null, null);
 623         $table->add_field('targetframework', XMLDB_TYPE_CHAR, '255', null, null, null, null);
 624         $table->add_field('targetcode', XMLDB_TYPE_CHAR, '255', null, null, null, null);
 625
 626         // Adding keys to table badge_competencies.
 627         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
 628         $table->add_key('competenciesbadge', XMLDB_KEY_FOREIGN, ['badgeid'], 'badge', ['id']);
 629
 630         // Conditionally launch create table for badge_competencies.
 631         if (!$dbman->table_exists($table)) {
 632             $dbman->create_table($table);
 633         }
 634
 635         // Main savepoint reached.
 636         upgrade_main_savepoint(true, 2018110500.01);
 637     }
 638
 639     if ($oldversion < 2018110700.01) {
 640         // This config setting added and then removed.
 641         unset_config('showcourseimages', 'moodlecourse');
 642
 643         // Main savepoint reached.
 644         upgrade_main_savepoint(true, 2018110700.01);
 645     }
 646
 647     if ($oldversion < 2018111301.00) {
 648         // Define field locked to be added to context.
 649         $table = new xmldb_table('context');
 650         $field = new xmldb_field('locked', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, null, '0', 'depth');
 651
 652         // Conditionally launch add field locked.
 653         if (!$dbman->field_exists($table, $field)) {
 654             $dbman->add_field($table, $field);
 655         }
 656
 657         // Define field locked to be added to context_temp.
 658         $table = new xmldb_table('context_temp');
 659         $field = new xmldb_field('locked', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, null, '0', 'depth');
 660
 661         // Conditionally launch add field locked.
 662         if (!$dbman->field_exists($table, $field)) {
 663             $dbman->add_field($table, $field);
 664         }
 665
 666         // Note: This change also requires a bump in is_major_upgrade_required.
 667         upgrade_main_savepoint(true, 2018111301.00);
 668     }
 669
 670     if ($oldversion < 2018111900.00) {
 671         // Update favourited courses, so they are saved in the particular course context instead of the system.
 672         $favouritedcourses = $DB->get_records('favourite', ['component' => 'core_course', 'itemtype' => 'courses']);
 673
 674         foreach ($favouritedcourses as $fc) {
 675             $coursecontext = \context_course::instance($fc->itemid);
 676             $fc->contextid = $coursecontext->id;
 677             $DB->update_record('favourite', $fc);
 678         }
 679
 680         upgrade_main_savepoint(true, 2018111900.00);
 681     }
 682
 683     if ($oldversion < 2018111900.01) {
 684         // Define table oauth2_access_token to be created.
 685         $table = new xmldb_table('oauth2_access_token');
 686
 687         // Adding fields to table oauth2_access_token.
 688         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 689         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 690         $table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 691         $table->add_field('usermodified', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 692         $table->add_field('issuerid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 693         $table->add_field('token', XMLDB_TYPE_TEXT, null, null, XMLDB_NOTNULL, null, null);
 694         $table->add_field('expires', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 695         $table->add_field('scope', XMLDB_TYPE_TEXT, null, null, XMLDB_NOTNULL, null, null);
 696
 697         // Adding keys to table oauth2_access_token.
 698         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
 699         $table->add_key('issueridkey', XMLDB_KEY_FOREIGN_UNIQUE, ['issuerid'], 'oauth2_issuer', ['id']);
 700
 701         // Conditionally launch create table for oauth2_access_token.
 702         if (!$dbman->table_exists($table)) {
 703             $dbman->create_table($table);
 704         }
 705
 706         // Main savepoint reached.
 707         upgrade_main_savepoint(true, 2018111900.01);
 708     }
 709
 710     if ($oldversion < 2018112000.00) {
 711         // Update favourited conversations, so they are saved in the proper context instead of the system.
 712         $sql = "SELECT f.*, mc.contextid as conversationctx
 713                   FROM {favourite} f
 714                   JOIN {message_conversations} mc
 715                     ON mc.id = f.itemid";
 716         $favouritedconversations = $DB->get_records_sql($sql);
 717         foreach ($favouritedconversations as $fc) {
 718             if (empty($fc->conversationctx)) {
 719                 $conversationidctx = \context_user::instance($fc->userid)->id;
 720             } else {
 721                 $conversationidctx = $fc->conversationctx;
 722             }
 723
 724             $DB->set_field('favourite', 'contextid', $conversationidctx, ['id' => $fc->id]);
 725         }
 726
 727         upgrade_main_savepoint(true, 2018112000.00);
 728     }
 729
 730     // Automatically generated Moodle v3.6.0 release upgrade line.
 731     // Put any upgrade step following this.
 732
 733     if ($oldversion < 2018120300.01) {
 734         // Update the FB logo URL.
 735         $oldurl = 'https://facebookbrand.com/wp-content/themes/fb-branding/prj-fb-branding/assets/images/fb-art.png';
 736         $newurl = 'https://facebookbrand.com/wp-content/uploads/2016/05/flogo_rgb_hex-brc-site-250.png';
 737
 738         $updatesql = "UPDATE {oauth2_issuer}
 739                          SET image = :newimage
 740                        WHERE " . $DB->sql_compare_text('image', 100). " = :oldimage";
 741         $params = [
 742             'newimage' => $newurl,
 743             'oldimage' => $oldurl
 744         ];
 745         $DB->execute($updatesql, $params);
 746
 747         upgrade_main_savepoint(true, 2018120300.01);
 748     }
 749
 750     if ($oldversion < 2018120300.02) {
 751         // Set all individual conversations to enabled.
 752         $updatesql = "UPDATE {message_conversations}
 753                          SET enabled = :enabled
 754                        WHERE type = :type";
 755         $DB->execute($updatesql, ['enabled' => 1, 'type' => 1]);
 756
 757         upgrade_main_savepoint(true, 2018120300.02);
 758     }
 759
 760     if ($oldversion < 2018120301.02) {
 761         upgrade_delete_orphaned_file_records();
 762         upgrade_main_savepoint(true, 2018120301.02);
 763     }
 764
 765     if ($oldversion < 2019011500.00) {
 766         // Define table task_log to be created.
 767         $table = new xmldb_table('task_log');
 768
 769         // Adding fields to table task_log.
 770         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 771         $table->add_field('type', XMLDB_TYPE_INTEGER, '4', null, XMLDB_NOTNULL, null, null);
 772         $table->add_field('component', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
 773         $table->add_field('classname', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
 774         $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 775         $table->add_field('timestart', XMLDB_TYPE_NUMBER, '20, 10', null, XMLDB_NOTNULL, null, null);
 776         $table->add_field('timeend', XMLDB_TYPE_NUMBER, '20, 10', null, XMLDB_NOTNULL, null, null);
 777         $table->add_field('dbreads', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 778         $table->add_field('dbwrites', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 779         $table->add_field('result', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, null, null);
 780
 781         // Adding keys to table task_log.
 782         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
 783
 784         // Adding indexes to table task_log.
 785         $table->add_index('classname', XMLDB_INDEX_NOTUNIQUE, ['classname']);
 786         $table->add_index('timestart', XMLDB_INDEX_NOTUNIQUE, ['timestart']);
 787
 788         // Conditionally launch create table for task_log.
 789         if (!$dbman->table_exists($table)) {
 790             $dbman->create_table($table);
 791         }
 792
 793         // Main savepoint reached.
 794         upgrade_main_savepoint(true, 2019011500.00);
 795     }
 796
 797     if ($oldversion < 2019011501.00) {
 798         // Define field output to be added to task_log.
 799         $table = new xmldb_table('task_log');
 800         $field = new xmldb_field('output', XMLDB_TYPE_TEXT, null, null, XMLDB_NOTNULL, null, null, 'result');
 801
 802         // Conditionally launch add field output.
 803         if (!$dbman->field_exists($table, $field)) {
 804             $dbman->add_field($table, $field);
 805         }
 806
 807         // Main savepoint reached.
 808         upgrade_main_savepoint(true, 2019011501.00);
 809     }
 810
 811     if ($oldversion < 2019011801.00) {
 812
 813         // Define table customfield_category to be created.
 814         $table = new xmldb_table('customfield_category');
 815
 816         // Adding fields to table customfield_category.
 817         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 818         $table->add_field('name', XMLDB_TYPE_CHAR, '400', null, XMLDB_NOTNULL, null, null);
 819         $table->add_field('description', XMLDB_TYPE_TEXT, null, null, null, null, null);
 820         $table->add_field('descriptionformat', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
 821         $table->add_field('sortorder', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
 822         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 823         $table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 824         $table->add_field('component', XMLDB_TYPE_CHAR, '100', null, XMLDB_NOTNULL, null, null);
 825         $table->add_field('area', XMLDB_TYPE_CHAR, '100', null, XMLDB_NOTNULL, null, null);
 826         $table->add_field('itemid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 827         $table->add_field('contextid', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
 828
 829         // Adding keys to table customfield_category.
 830         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
 831         $table->add_key('contextid', XMLDB_KEY_FOREIGN, ['contextid'], 'context', ['id']);
 832
 833         // Adding indexes to table customfield_category.
 834         $table->add_index('component_area_itemid', XMLDB_INDEX_NOTUNIQUE, ['component', 'area', 'itemid', 'sortorder']);
 835
 836         // Conditionally launch create table for customfield_category.
 837         if (!$dbman->table_exists($table)) {
 838             $dbman->create_table($table);
 839         }
 840
 841         // Define table customfield_field to be created.
 842         $table = new xmldb_table('customfield_field');
 843
 844         // Adding fields to table customfield_field.
 845         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 846         $table->add_field('shortname', XMLDB_TYPE_CHAR, '100', null, XMLDB_NOTNULL, null, null);
 847         $table->add_field('name', XMLDB_TYPE_CHAR, '400', null, XMLDB_NOTNULL, null, null);
 848         $table->add_field('type', XMLDB_TYPE_CHAR, '100', null, XMLDB_NOTNULL, null, null);
 849         $table->add_field('description', XMLDB_TYPE_TEXT, null, null, null, null, null);
 850         $table->add_field('descriptionformat', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
 851         $table->add_field('sortorder', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
 852         $table->add_field('categoryid', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
 853         $table->add_field('configdata', XMLDB_TYPE_TEXT, null, null, null, null, null);
 854         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 855         $table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 856
 857         // Adding keys to table customfield_field.
 858         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
 859         $table->add_key('categoryid', XMLDB_KEY_FOREIGN, ['categoryid'], 'customfield_category', ['id']);
 860
 861         // Adding indexes to table customfield_field.
 862         $table->add_index('categoryid_sortorder', XMLDB_INDEX_NOTUNIQUE, ['categoryid', 'sortorder']);
 863
 864         // Conditionally launch create table for customfield_field.
 865         if (!$dbman->table_exists($table)) {
 866             $dbman->create_table($table);
 867         }
 868
 869         // Define table customfield_data to be created.
 870         $table = new xmldb_table('customfield_data');
 871
 872         // Adding fields to table customfield_data.
 873         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 874         $table->add_field('fieldid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 875         $table->add_field('instanceid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 876         $table->add_field('intvalue', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
 877         $table->add_field('decvalue', XMLDB_TYPE_NUMBER, '10, 5', null, null, null, null);
 878         $table->add_field('shortcharvalue', XMLDB_TYPE_CHAR, '255', null, null, null, null);
 879         $table->add_field('charvalue', XMLDB_TYPE_CHAR, '1333', null, null, null, null);
 880         $table->add_field('value', XMLDB_TYPE_TEXT, null, null, XMLDB_NOTNULL, null, null);
 881         $table->add_field('valueformat', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 882         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 883         $table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 884         $table->add_field('contextid', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
 885
 886         // Adding keys to table customfield_data.
 887         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
 888         $table->add_key('fieldid', XMLDB_KEY_FOREIGN, ['fieldid'], 'customfield_field', ['id']);
 889         $table->add_key('contextid', XMLDB_KEY_FOREIGN, ['contextid'], 'context', ['id']);
 890
 891         // Adding indexes to table customfield_data.
 892         $table->add_index('instanceid-fieldid', XMLDB_INDEX_UNIQUE, ['instanceid', 'fieldid']);
 893         $table->add_index('fieldid-intvalue', XMLDB_INDEX_NOTUNIQUE, ['fieldid', 'intvalue']);
 894         $table->add_index('fieldid-shortcharvalue', XMLDB_INDEX_NOTUNIQUE, ['fieldid', 'shortcharvalue']);
 895         $table->add_index('fieldid-decvalue', XMLDB_INDEX_NOTUNIQUE, ['fieldid', 'decvalue']);
 896
 897         // Conditionally launch create table for customfield_data.
 898         if (!$dbman->table_exists($table)) {
 899             $dbman->create_table($table);
 900         }
 901
 902         upgrade_main_savepoint(true, 2019011801.00);
 903     }
 904
 905     if ($oldversion < 2019011801.01) {
 906
 907         // Delete all files that have been used in sections, which are already deleted.
 908         $sql = "SELECT DISTINCT f.itemid as sectionid, f.contextid
 909                   FROM {files} f
 910              LEFT JOIN {course_sections} s ON f.itemid = s.id
 911                  WHERE f.component = :component AND f.filearea = :filearea AND s.id IS NULL ";
 912
 913         $params = [
 914             'component' => 'course',
 915             'filearea' => 'section'
 916         ];
 917
 918         $stalefiles = $DB->get_recordset_sql($sql, $params);
 919
 920         $fs = get_file_storage();
 921         foreach ($stalefiles as $stalefile) {
 922             $fs->delete_area_files($stalefile->contextid, 'course', 'section', $stalefile->sectionid);
 923         }
 924         $stalefiles->close();
 925
 926         upgrade_main_savepoint(true, 2019011801.01);
 927     }
 928
 929     if ($oldversion < 2019011801.02) {
 930         // Add index 'useridfrom' to the table 'notifications'.
 931         $table = new xmldb_table('notifications');
 932         $index = new xmldb_index('useridfrom', XMLDB_INDEX_NOTUNIQUE, ['useridfrom']);
 933
 934         if (!$dbman->index_exists($table, $index)) {
 935             $dbman->add_index($table, $index);
 936         }
 937
 938         upgrade_main_savepoint(true, 2019011801.02);
 939     }
 940
 941     if ($oldversion < 2019011801.03) {
 942         // Remove duplicate entries from group memberships.
 943         // Find records with multiple userid/groupid combinations and find the highest ID.
 944         // Later we will remove all those entries.
 945         $sql = "
 946             SELECT MIN(id) as minid, userid, groupid
 947             FROM {groups_members}
 948             GROUP BY userid, groupid
 949             HAVING COUNT(id) > 1";
 950         if ($duplicatedrows = $DB->get_recordset_sql($sql)) {
 951             foreach ($duplicatedrows as $row) {
 952                 $DB->delete_records_select('groups_members',
 953                     'userid = :userid AND groupid = :groupid AND id <> :minid', (array)$row);
 954             }
 955         }
 956         $duplicatedrows->close();
 957
 958         // Define key useridgroupid (unique) to be added to group_members.
 959         $table = new xmldb_table('groups_members');
 960         $key = new xmldb_key('useridgroupid', XMLDB_KEY_UNIQUE, array('userid', 'groupid'));
 961         // Launch add key useridgroupid.
 962         $dbman->add_key($table, $key);
 963         // Main savepoint reached.
 964         upgrade_main_savepoint(true, 2019011801.03);
 965     }
 966
 967     if ($oldversion < 2019021500.01) {
 968         $insights = $DB->get_record('message_providers', ['component' => 'moodle', 'name' => 'insights']);
 969         if (!empty($insights)) {
 970             $insights->capability = null;
 971             $DB->update_record('message_providers', $insights);
 972         }
 973         upgrade_main_savepoint(true, 2019021500.01);
 974     }
 975
 976     if ($oldversion < 2019021500.02) {
 977         // Default 'off' for existing sites as this is the behaviour they had earlier.
 978         set_config('messagingdefaultpressenter', false);
 979
 980         // Main savepoint reached.
 981         upgrade_main_savepoint(true, 2019021500.02);
 982     }
 983
 984     if ($oldversion < 2019030100.01) {
 985         // Create adhoc task to delete renamed My Course search area (ID core_course-mycourse).
 986         $record = new \stdClass();
 987         $record->classname = '\core\task\clean_up_deleted_search_area_task';
 988         $record->component = 'core';
 989
 990         // Next run time based from nextruntime computation in \core\task\manager::queue_adhoc_task().
 991         $nextruntime = time() - 1;
 992         $record->nextruntime = $nextruntime;
 993         $record->customdata = json_encode('core_course-mycourse');
 994
 995         $DB->insert_record('task_adhoc', $record);
 996
 997         // Main savepoint reached.
 998         upgrade_main_savepoint(true, 2019030100.01);
 999     }
1000
1001     if ($oldversion < 2019030700.01) {
1002
1003         // Define field evaluationmode to be added to analytics_models_log.
1004         $table = new xmldb_table('analytics_models_log');
1005         $field = new xmldb_field('evaluationmode', XMLDB_TYPE_CHAR, '50', null, null, null,
1006             null, 'version');
1007
1008         // Conditionally launch add field evaluationmode.
1009         if (!$dbman->field_exists($table, $field)) {
1010             $dbman->add_field($table, $field);
1011
1012             $updatesql = "UPDATE {analytics_models_log}
1013                              SET evaluationmode = 'configuration'";
1014             $DB->execute($updatesql, []);
1015
1016             // Changing nullability of field evaluationmode on table block_instances to not null.
1017             $field = new xmldb_field('evaluationmode', XMLDB_TYPE_CHAR, '50', null, XMLDB_NOTNULL,
1018                 null, null, 'version');
1019
1020             // Launch change of nullability for field evaluationmode.
1021             $dbman->change_field_notnull($table, $field);
1022         }
1023
1024         // Main savepoint reached.
1025         upgrade_main_savepoint(true, 2019030700.01);
1026     }
1027
1028     if ($oldversion < 2019030800.00) {
1029         // Define table 'message_conversation_actions' to be created.
1030         // Note - I would have preferred 'message_conversation_user_actions' but due to Oracle we can't. Boo.
1031         $table = new xmldb_table('message_conversation_actions');
1032
1033         // Adding fields to table 'message_conversation_actions'.
1034         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1035         $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
1036         $table->add_field('conversationid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
1037         $table->add_field('action', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
1038         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
1039
1040         // Adding keys to table 'message_conversation_actions'.
1041         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
1042         $table->add_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
1043         $table->add_key('conversationid', XMLDB_KEY_FOREIGN, ['conversationid'], 'message_conversations', ['id']);
1044
1045         // Conditionally launch create table for 'message_conversation_actions'.
1046         if (!$dbman->table_exists($table)) {
1047             $dbman->create_table($table);
1048         }
1049
1050         // Main savepoint reached.
1051         upgrade_main_savepoint(true, 2019030800.00);
1052     }
1053
1054     if ($oldversion < 2019030800.02) {
1055         // Remove any conversations and their members associated with non-existent groups.
1056         $sql = "SELECT mc.id
1057                   FROM {message_conversations} mc
1058              LEFT JOIN {groups} g
1059                     ON mc.itemid = g.id
1060                  WHERE mc.component = :component
1061                    AND mc.itemtype = :itemtype
1062                    AND g.id is NULL";
1063         $conversations = $DB->get_records_sql($sql, ['component' => 'core_group', 'itemtype' => 'groups']);
1064
1065         if ($conversations) {
1066             $conversationids = array_keys($conversations);
1067
1068             $DB->delete_records_list('message_conversations', 'id', $conversationids);
1069             $DB->delete_records_list('message_conversation_members', 'conversationid', $conversationids);
1070             $DB->delete_records_list('message_conversation_actions', 'conversationid', $conversationids);
1071
1072             // Now, go through each conversation and delete any messages and related message actions.
1073             foreach ($conversationids as $conversationid) {
1074                 if ($messages = $DB->get_records('messages', ['conversationid' => $conversationid])) {
1075                     $messageids = array_keys($messages);
1076
1077                     // Delete the actions.
1078                     list($insql, $inparams) = $DB->get_in_or_equal($messageids);
1079                     $DB->delete_records_select('message_user_actions', "messageid $insql", $inparams);
1080
1081                     // Delete the messages.
1082                     $DB->delete_records('messages', ['conversationid' => $conversationid]);
1083                 }
1084             }
1085         }
1086
1087         // Main savepoint reached.
1088         upgrade_main_savepoint(true, 2019030800.02);
1089     }
1090
1091     if ($oldversion < 2019030800.03) {
1092
1093         // Add missing indicators to course_dropout.
1094         $params = [
1095             'target' => '\core\analytics\target\course_dropout',
1096             'trained' => 0,
1097             'enabled' => 0,
1098         ];
1099         $models = $DB->get_records('analytics_models', $params);
1100         foreach ($models as $model) {
1101             $indicators = json_decode($model->indicators);
1102
1103             $potentiallymissingindicators = [
1104                 '\core_course\analytics\indicator\completion_enabled',
1105                 '\core_course\analytics\indicator\potential_cognitive_depth',
1106                 '\core_course\analytics\indicator\potential_social_breadth',
1107                 '\core\analytics\indicator\any_access_after_end',
1108                 '\core\analytics\indicator\any_access_before_start',
1109                 '\core\analytics\indicator\any_write_action_in_course',
1110                 '\core\analytics\indicator\read_actions'
1111             ];
1112
1113             $missing = false;
1114             foreach ($potentiallymissingindicators as $potentiallymissingindicator) {
1115                 if (!in_array($potentiallymissingindicator, $indicators)) {
1116                     // Add the missing indicator to sites upgraded before 2017072000.02.
1117                     $indicators[] = $potentiallymissingindicator;
1118                     $missing = true;
1119                 }
1120             }
1121
1122             if ($missing) {
1123                 $model->indicators = json_encode($indicators);
1124                 $model->version = time();
1125                 $model->timemodified = time();
1126                 $DB->update_record('analytics_models', $model);
1127             }
1128         }
1129
1130         // Add missing indicators to no_teaching.
1131         $params = [
1132             'target' => '\core\analytics\target\no_teaching',
1133         ];
1134         $models = $DB->get_records('analytics_models', $params);
1135         foreach ($models as $model) {
1136             $indicators = json_decode($model->indicators);
1137             if (!in_array('\core_course\analytics\indicator\no_student', $indicators)) {
1138                 // Add the missing indicator to sites upgraded before 2017072000.02.
1139
1140                 $indicators[] = '\core_course\analytics\indicator\no_student';
1141
1142                 $model->indicators = json_encode($indicators);
1143                 $model->version = time();
1144                 $model->timemodified = time();
1145                 $DB->update_record('analytics_models', $model);
1146             }
1147         }
1148
1149         // Main savepoint reached.
1150         upgrade_main_savepoint(true, 2019030800.03);
1151     }
1152
1153     if ($oldversion < 2019031500.01) {
1154
1155         $defaulttimesplittings = get_config('analytics', 'timesplittings');
1156         if ($defaulttimesplittings !== false) {
1157             set_config('defaulttimesplittingsevaluation', $defaulttimesplittings, 'analytics');
1158             unset_config('timesplittings', 'analytics');
1159         }
1160
1161         // Main savepoint reached.
1162         upgrade_main_savepoint(true, 2019031500.01);
1163     }
1164
1165     if ($oldversion < 2019032200.02) {
1166         // The no_teaching model might have been marked as not-trained by mistake (static models are always trained).
1167         $DB->set_field('analytics_models', 'trained', 1, ['target' => '\core\analytics\target\no_teaching']);
1168         upgrade_main_savepoint(true, 2019032200.02);
1169     }
1170
1171     if ($oldversion < 2019032900.00) {
1172
1173         // Define table badge_competencies to be renamed to badge_alignment.
1174         $table = new xmldb_table('badge_competencies');
1175
1176         // Be careful if this step gets run twice.
1177         if ($dbman->table_exists($table)) {
1178             $key = new xmldb_key('competenciesbadge', XMLDB_KEY_FOREIGN, ['badgeid'], 'badge', ['id']);
1179
1180             // Launch drop key competenciesbadge.
1181             $dbman->drop_key($table, $key);
1182
1183             $key = new xmldb_key('alignmentsbadge', XMLDB_KEY_FOREIGN, ['badgeid'], 'badge', ['id']);
1184
1185             // Launch add key alignmentsbadge.
1186             $dbman->add_key($table, $key);
1187
1188             // Launch rename table for badge_alignment.
1189             $dbman->rename_table($table, 'badge_alignment');
1190         }
1191
1192         upgrade_main_savepoint(true, 2019032900.00);
1193     }
1194
1195     if ($oldversion < 2019032900.01) {
1196         $sql = "UPDATE {task_scheduled}
1197                    SET classname = ?
1198                  WHERE component = ?
1199                    AND classname = ?";
1200         $DB->execute($sql, [
1201             '\core\task\question_preview_cleanup_task',
1202             'moodle',
1203             '\core\task\question_cron_task'
1204         ]);
1205
1206         // Main savepoint reached.
1207         upgrade_main_savepoint(true, 2019032900.01);
1208      }
1209
1210     if ($oldversion < 2019040200.01) {
1211         // Removing the themes BSB, Clean, More from core.
1212         // If these theme wish to be retained empty this array before upgrade.
1213         $themes = array('theme_bootstrapbase' => 'bootstrapbase',
1214                 'theme_clean' => 'clean', 'theme_more' => 'more');
1215         foreach ($themes as $key => $theme) {
1216             if (check_dir_exists($CFG->dirroot . '/theme/' . $theme, false)) {
1217                 // Ignore the themes that have been re-downloaded.
1218                 unset($themes[$key]);
1219             }
1220         }
1221         // Check we actually have themes to remove.
1222         if (count($themes) > 0) {
1223             list($insql, $inparams) = $DB->get_in_or_equal($themes, SQL_PARAMS_NAMED);
1224
1225             // Replace the theme usage.
1226             $DB->set_field_select('course', 'theme', 'classic', "theme $insql", $inparams);
1227             $DB->set_field_select('course_categories', 'theme', 'classic', "theme $insql", $inparams);
1228             $DB->set_field_select('user', 'theme', 'classic', "theme $insql", $inparams);
1229             $DB->set_field_select('mnet_host', 'theme', 'classic', "theme $insql", $inparams);
1230             $DB->set_field_select('cohort', 'theme', 'classic', "theme $insql", $inparams);
1231
1232             // Replace the theme configs.
1233             if (in_array(get_config('core', 'theme'), $themes)) {
1234                 set_config('theme', 'classic');
1235             }
1236             if (in_array(get_config('core', 'thememobile'), $themes)) {
1237                 set_config('thememobile', 'classic');
1238             }
1239             if (in_array(get_config('core', 'themelegacy'), $themes)) {
1240                 set_config('themelegacy', 'classic');
1241             }
1242             if (in_array(get_config('core', 'themetablet'), $themes)) {
1243                 set_config('themetablet', 'classic');
1244             }
1245
1246             // Hacky emulation of plugin uninstallation.
1247             foreach ($themes as $key => $theme) {
1248                 unset_all_config_for_plugin($key);
1249             }
1250         }
1251
1252         // Main savepoint reached.
1253         upgrade_main_savepoint(true, 2019040200.01);
1254     }
1255
1256     if ($oldversion < 2019040600.02) {
1257
1258         // Define key fileid (foreign) to be dropped form analytics_train_samples.
1259         $table = new xmldb_table('analytics_train_samples');
1260         $key = new xmldb_key('fileid', XMLDB_KEY_FOREIGN, ['fileid'], 'files', ['id']);
1261
1262         // Launch drop key fileid.
1263         $dbman->drop_key($table, $key);
1264
1265         // Define field fileid to be dropped from analytics_train_samples.
1266         $table = new xmldb_table('analytics_train_samples');
1267         $field = new xmldb_field('fileid');
1268
1269         // Conditionally launch drop field fileid.
1270         if ($dbman->field_exists($table, $field)) {
1271             $dbman->drop_field($table, $field);
1272         }
1273
1274         // Main savepoint reached.
1275         upgrade_main_savepoint(true, 2019040600.02);
1276     }
1277
1278     if ($oldversion < 2019040600.04) {
1279         // Define field and index to be added to backup_controllers.
1280         $table = new xmldb_table('backup_controllers');
1281         $field = new xmldb_field('progress', XMLDB_TYPE_NUMBER, '15, 14', null, XMLDB_NOTNULL, null, '0', 'timemodified');
1282         $index = new xmldb_index('useritem_ix', XMLDB_INDEX_NOTUNIQUE, ['userid', 'itemid']);
1283         // Conditionally launch add field progress.
1284         if (!$dbman->field_exists($table, $field)) {
1285             $dbman->add_field($table, $field);
1286         }
1287         // Conditionally launch add index useritem_ix.
1288         if (!$dbman->index_exists($table, $index)) {
1289             $dbman->add_index($table, $index);
1290         }
1291
1292         // Main savepoint reached.
1293         upgrade_main_savepoint(true, 2019040600.04);
1294     }
1295
1296     if ($oldversion < 2019041000.02) {
1297
1298         // Define field fullmessagetrust to be added to messages.
1299         $table = new xmldb_table('messages');
1300         $field = new xmldb_field('fullmessagetrust', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, null, '0', 'timecreated');
1301
1302         // Conditionally launch add field fullmessagetrust.
1303         if (!$dbman->field_exists($table, $field)) {
1304             $dbman->add_field($table, $field);
1305         }
1306
1307         // Main savepoint reached.
1308         upgrade_main_savepoint(true, 2019041000.02);
1309     }
1310
1311     if ($oldversion < 2019041300.01) {
1312         // Add the field 'name' to the 'analytics_models' table.
1313         $table = new xmldb_table('analytics_models');
1314         $field = new xmldb_field('name', XMLDB_TYPE_CHAR, '1333', null, null, null, null, 'trained');
1315
1316         if (!$dbman->field_exists($table, $field)) {
1317             $dbman->add_field($table, $field);
1318         }
1319         // Main savepoint reached.
1320         upgrade_main_savepoint(true, 2019041300.01);
1321     }
1322
1323     if ($oldversion < 2019041800.01) {
1324         // STEP 1. For the existing and migrated self-conversations, set the type to the new MESSAGE_CONVERSATION_TYPE_SELF, update
1325         // the convhash and star them.
1326         $sql = "SELECT mcm.conversationid, mcm.userid, MAX(mcm.id) as maxid
1327                   FROM {message_conversation_members} mcm
1328             INNER JOIN {user} u ON mcm.userid = u.id
1329                  WHERE u.deleted = 0
1330               GROUP BY mcm.conversationid, mcm.userid
1331                 HAVING COUNT(*) > 1";
1332         $selfconversationsrs = $DB->get_recordset_sql($sql);
1333         $maxids = [];
1334         foreach ($selfconversationsrs as $selfconversation) {
1335             $DB->update_record('message_conversations',
1336                 ['id' => $selfconversation->conversationid,
1337                  'type' => \core_message\api::MESSAGE_CONVERSATION_TYPE_SELF,
1338                  'convhash' => \core_message\helper::get_conversation_hash([$selfconversation->userid])
1339                 ]
1340             );
1341
1342             // Star the existing self-conversation.
1343             $favouriterecord = new \stdClass();
1344             $favouriterecord->component = 'core_message';
1345             $favouriterecord->itemtype = 'message_conversations';
1346             $favouriterecord->itemid = $selfconversation->conversationid;
1347             $userctx = \context_user::instance($selfconversation->userid);
1348             $favouriterecord->contextid = $userctx->id;
1349             $favouriterecord->userid = $selfconversation->userid;
1350             if (!$DB->record_exists('favourite', (array)$favouriterecord)) {
1351                 $favouriterecord->timecreated = time();
1352                 $favouriterecord->timemodified = $favouriterecord->timecreated;
1353                 $DB->insert_record('favourite', $favouriterecord);
1354             }
1355
1356             // Set the self-conversation member with maxid to remove it later.
1357             $maxids[] = $selfconversation->maxid;
1358         }
1359         $selfconversationsrs->close();
1360
1361         // Remove the repeated member with the higher id for all the existing self-conversations.
1362         if (!empty($maxids)) {
1363             list($insql, $inparams) = $DB->get_in_or_equal($maxids);
1364             $DB->delete_records_select('message_conversation_members', "id $insql", $inparams);
1365         }
1366
1367         // STEP 2. Migrate existing self-conversation relying on old message tables, setting the type to the new
1368         // MESSAGE_CONVERSATION_TYPE_SELF and the convhash to the proper one. Star them also.
1369
1370         // On the messaging legacy tables, self-conversations are only present in the 'message_read' table, so we don't need to
1371         // check the content in the 'message' table.
1372         $sql = "SELECT mr.*
1373                   FROM {message_read} mr
1374             INNER JOIN {user} u ON mr.useridfrom = u.id
1375                  WHERE mr.useridfrom = mr.useridto AND mr.notification = 0 AND u.deleted = 0";
1376         $legacyselfmessagesrs = $DB->get_recordset_sql($sql);
1377         foreach ($legacyselfmessagesrs as $message) {
1378             // Get the self-conversation or create and star it if doesn't exist.
1379             $conditions = [
1380                 'type' => \core_message\api::MESSAGE_CONVERSATION_TYPE_SELF,
1381                 'convhash' => \core_message\helper::get_conversation_hash([$message->useridfrom])
1382             ];
1383             $selfconversation = $DB->get_record('message_conversations', $conditions);
1384             if (empty($selfconversation)) {
1385                 // Create the self-conversation.
1386                 $selfconversation = new \stdClass();
1387                 $selfconversation->type = \core_message\api::MESSAGE_CONVERSATION_TYPE_SELF;
1388                 $selfconversation->convhash = \core_message\helper::get_conversation_hash([$message->useridfrom]);
1389                 $selfconversation->enabled = 1;
1390                 $selfconversation->timecreated = time();
1391                 $selfconversation->timemodified = $selfconversation->timecreated;
1392
1393                 $selfconversation->id = $DB->insert_record('message_conversations', $selfconversation);
1394
1395                 // Add user to this self-conversation.
1396                 $member = new \stdClass();
1397                 $member->conversationid = $selfconversation->id;
1398                 $member->userid = $message->useridfrom;
1399                 $member->timecreated = time();
1400
1401                 $member->id = $DB->insert_record('message_conversation_members', $member);
1402
1403                 // Star the self-conversation.
1404                 $favouriterecord = new \stdClass();
1405                 $favouriterecord->component = 'core_message';
1406                 $favouriterecord->itemtype = 'message_conversations';
1407                 $favouriterecord->itemid = $selfconversation->id;
1408                 $userctx = \context_user::instance($message->useridfrom);
1409                 $favouriterecord->contextid = $userctx->id;
1410                 $favouriterecord->userid = $message->useridfrom;
1411                 if (!$DB->record_exists('favourite', (array)$favouriterecord)) {
1412                     $favouriterecord->timecreated = time();
1413                     $favouriterecord->timemodified = $favouriterecord->timecreated;
1414                     $DB->insert_record('favourite', $favouriterecord);
1415                 }
1416             }
1417
1418             // Create the object we will be inserting into the database.
1419             $tabledata = new \stdClass();
1420             $tabledata->useridfrom = $message->useridfrom;
1421             $tabledata->conversationid = $selfconversation->id;
1422             $tabledata->subject = $message->subject;
1423             $tabledata->fullmessage = $message->fullmessage;
1424             $tabledata->fullmessageformat = $message->fullmessageformat ?? FORMAT_MOODLE;
1425             $tabledata->fullmessagehtml = $message->fullmessagehtml;
1426             $tabledata->smallmessage = $message->smallmessage;
1427             $tabledata->timecreated = $message->timecreated;
1428
1429             $messageid = $DB->insert_record('messages', $tabledata);
1430
1431             // Check if we need to mark this message as deleted (self-conversations add this information on the
1432             // timeuserfromdeleted field.
1433             if ($message->timeuserfromdeleted) {
1434                 $mua = new \stdClass();
1435                 $mua->userid = $message->useridfrom;
1436                 $mua->messageid = $messageid;
1437                 $mua->action = \core_message\api::MESSAGE_ACTION_DELETED;
1438                 $mua->timecreated = $message->timeuserfromdeleted;
1439
1440                 $DB->insert_record('message_user_actions', $mua);
1441             }
1442
1443             // Mark this message as read.
1444             $mua = new \stdClass();
1445             $mua->userid = $message->useridto;
1446             $mua->messageid = $messageid;
1447             $mua->action = \core_message\api::MESSAGE_ACTION_READ;
1448             $mua->timecreated = $message->timeread;
1449
1450             $DB->insert_record('message_user_actions', $mua);
1451
1452             // The self-conversation message has been migrated. Delete the record from the legacy table as soon as possible
1453             // to avoid migrate it twice.
1454             $DB->delete_records('message_read', ['id' => $message->id]);
1455         }
1456         $legacyselfmessagesrs->close();
1457
1458         // Main savepoint reached.
1459         upgrade_main_savepoint(true, 2019041800.01);
1460     }
1461
1462     if ($oldversion < 2019042200.01) {
1463
1464         // Define table role_sortorder to be dropped.
1465         $table = new xmldb_table('role_sortorder');
1466
1467         // Conditionally launch drop table for role_sortorder.
1468         if ($dbman->table_exists($table)) {
1469             $dbman->drop_table($table);
1470         }
1471
1472         // Main savepoint reached.
1473         upgrade_main_savepoint(true, 2019042200.01);
1474     }
1475
1476     if ($oldversion < 2019042200.02) {
1477
1478         // Let's update all (old core) targets to their new (core_course) locations.
1479         $targets = [
1480             '\core\analytics\target\course_competencies' => '\core_course\analytics\target\course_competencies',
1481             '\core\analytics\target\course_completion' => '\core_course\analytics\target\course_completion',
1482             '\core\analytics\target\course_dropout' => '\core_course\analytics\target\course_dropout',
1483             '\core\analytics\target\course_gradetopass' => '\core_course\analytics\target\course_gradetopass',
1484             '\core\analytics\target\no_teaching' => '\core_course\analytics\target\no_teaching',
1485         ];
1486
1487         foreach ($targets as $oldclass => $newclass) {
1488             $DB->set_field('analytics_models', 'target', $newclass, ['target' => $oldclass]);
1489         }
1490
1491         // Main savepoint reached.
1492         upgrade_main_savepoint(true, 2019042200.02);
1493     }
1494
1495     if ($oldversion < 2019042300.01) {
1496         $sql = "UPDATE {capabilities}
1497                    SET name = ?,
1498                        contextlevel = ?
1499                  WHERE name = ?";
1500         $DB->execute($sql, ['moodle/category:viewcourselist', CONTEXT_COURSECAT, 'moodle/course:browse']);
1501
1502         $sql = "UPDATE {role_capabilities}
1503                    SET capability = ?
1504                  WHERE capability = ?";
1505         $DB->execute($sql, ['moodle/category:viewcourselist', 'moodle/course:browse']);
1506
1507         // Main savepoint reached.
1508         upgrade_main_savepoint(true, 2019042300.01);
1509     }
1510
1511     if ($oldversion < 2019042300.03) {
1512
1513         // Add new customdata field to message table.
1514         $table = new xmldb_table('message');
1515         $field = new xmldb_field('customdata', XMLDB_TYPE_TEXT, null, null, null, null, null, 'eventtype');
1516
1517         // Conditionally launch add field output.
1518         if (!$dbman->field_exists($table, $field)) {
1519             $dbman->add_field($table, $field);
1520         }
1521
1522         // Add new customdata field to notifications and messages table.
1523         $table = new xmldb_table('notifications');
1524         $field = new xmldb_field('customdata', XMLDB_TYPE_TEXT, null, null, null, null, null, 'timecreated');
1525
1526         // Conditionally launch add field output.
1527         if (!$dbman->field_exists($table, $field)) {
1528             $dbman->add_field($table, $field);
1529         }
1530
1531         $table = new xmldb_table('messages');
1532         // Conditionally launch add field output.
1533         if (!$dbman->field_exists($table, $field)) {
1534             $dbman->add_field($table, $field);
1535         }
1536
1537         // Main savepoint reached.
1538         upgrade_main_savepoint(true, 2019042300.03);
1539     }
1540
1541     if ($oldversion < 2019042700.01) {
1542
1543         // Define field firstanalysis to be added to analytics_used_analysables.
1544         $table = new xmldb_table('analytics_used_analysables');
1545
1546         // Declaring it as null initially (although it is NOT NULL).
1547         $field = new xmldb_field('firstanalysis', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'analysableid');
1548
1549         // Conditionally launch add field firstanalysis.
1550         if (!$dbman->field_exists($table, $field)) {
1551             $dbman->add_field($table, $field);
1552
1553             // Set existing values to the current timeanalysed value.
1554             $recordset = $DB->get_recordset('analytics_used_analysables');
1555             foreach ($recordset as $record) {
1556                 $record->firstanalysis = $record->timeanalysed;
1557                 $DB->update_record('analytics_used_analysables', $record);
1558             }
1559             $recordset->close();
1560
1561             // Now make the field 'NOT NULL'.
1562             $field = new xmldb_field('firstanalysis', XMLDB_TYPE_INTEGER, '10',
1563                 null, XMLDB_NOTNULL, null, null, 'analysableid');
1564             $dbman->change_field_notnull($table, $field);
1565         }
1566
1567         // Main savepoint reached.
1568         upgrade_main_savepoint(true, 2019042700.01);
1569     }
1570
1571     if ($oldversion < 2019050300.01) {
1572         // Delete all stale favourite records which were left behind when a course was deleted.
1573         $params = ['component' => 'core_message', 'itemtype' => 'message_conversations'];
1574         $sql = "SELECT fav.id as id
1575                   FROM {favourite} fav
1576              LEFT JOIN {context} ctx ON (ctx.id = fav.contextid)
1577                  WHERE fav.component = :component
1578                        AND fav.itemtype = :itemtype
1579                        AND ctx.id IS NULL";
1580
1581         if ($records = $DB->get_fieldset_sql($sql, $params)) {
1582             // Just for safety, delete by chunks.
1583             $chunks = array_chunk($records, 1000);
1584             foreach ($chunks as $chunk) {
1585                 list($insql, $inparams) = $DB->get_in_or_equal($chunk);
1586                 $DB->delete_records_select('favourite', "id $insql", $inparams);
1587             }
1588         }
1589
1590         upgrade_main_savepoint(true, 2019050300.01);
1591     }
1592
1593     if ($oldversion < 2019050600.00) {
1594
1595         // Define field apiversion to be added to badge_backpack.
1596         $table = new xmldb_table('badge_backpack');
1597         $field = new xmldb_field('apiversion', XMLDB_TYPE_CHAR, '12', null, XMLDB_NOTNULL, null, '1.0', 'password');
1598
1599         // Conditionally launch add field apiversion.
1600         if (!$dbman->field_exists($table, $field)) {
1601             $dbman->add_field($table, $field);
1602         }
1603
1604         // Define table badge_external_backpack to be created.
1605         $table = new xmldb_table('badge_external_backpack');
1606
1607         // Adding fields to table badge_external_backpack.
1608         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1609         $table->add_field('backpackapiurl', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
1610         $table->add_field('backpackweburl', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
1611         $table->add_field('apiversion', XMLDB_TYPE_CHAR, '12', null, XMLDB_NOTNULL, null, '1.0');
1612         $table->add_field('sortorder', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1613         $table->add_field('password', XMLDB_TYPE_CHAR, '255', null, null, null, null);
1614
1615         // Adding keys to table badge_external_backpack.
1616         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
1617         $table->add_key('backpackapiurlkey', XMLDB_KEY_UNIQUE, ['backpackapiurl']);
1618         $table->add_key('backpackweburlkey', XMLDB_KEY_UNIQUE, ['backpackweburl']);
1619
1620         // Conditionally launch create table for badge_external_backpack.
1621         if (!$dbman->table_exists($table)) {
1622             $dbman->create_table($table);
1623         }
1624
1625         // Define field entityid to be added to badge_external.
1626         $table = new xmldb_table('badge_external');
1627         $field = new xmldb_field('entityid', XMLDB_TYPE_CHAR, '255', null, null, null, null, 'collectionid');
1628
1629         // Conditionally launch add field entityid.
1630         if (!$dbman->field_exists($table, $field)) {
1631             $dbman->add_field($table, $field);
1632         }
1633
1634         // Define table badge_external_identifier to be created.
1635         $table = new xmldb_table('badge_external_identifier');
1636
1637         // Adding fields to table badge_external_identifier.
1638         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1639         $table->add_field('sitebackpackid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
1640         $table->add_field('internalid', XMLDB_TYPE_CHAR, '128', null, XMLDB_NOTNULL, null, null);
1641         $table->add_field('externalid', XMLDB_TYPE_CHAR, '128', null, XMLDB_NOTNULL, null, null);
1642         $table->add_field('type', XMLDB_TYPE_CHAR, '16', null, XMLDB_NOTNULL, null, null);
1643
1644         // Adding keys to table badge_external_identifier.
1645         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
1646         $table->add_key('fk_backpackid', XMLDB_KEY_FOREIGN, ['sitebackpackid'], 'badge_backpack', ['id']);
1647         $table->add_key('backpack-internal-external', XMLDB_KEY_UNIQUE, ['sitebackpackid', 'internalid', 'externalid', 'type']);
1648
1649         // Conditionally launch create table for badge_external_identifier.
1650         if (!$dbman->table_exists($table)) {
1651             $dbman->create_table($table);
1652         }
1653
1654         // Define field externalbackpackid to be added to badge_backpack.
1655         $table = new xmldb_table('badge_backpack');
1656         $field = new xmldb_field('externalbackpackid', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'password');
1657
1658         // Conditionally launch add field externalbackpackid.
1659         if (!$dbman->field_exists($table, $field)) {
1660             $dbman->add_field($table, $field);
1661         }
1662
1663         // Define key externalbackpack (foreign) to be added to badge_backpack.
1664         $key = new xmldb_key('externalbackpack', XMLDB_KEY_FOREIGN, ['externalbackpackid'], 'badge_external_backpack', ['id']);
1665
1666         // Launch add key externalbackpack.
1667         $dbman->add_key($table, $key);
1668
1669         $field = new xmldb_field('apiversion');
1670
1671         // Conditionally launch drop field apiversion.
1672         if ($dbman->field_exists($table, $field)) {
1673             $dbman->drop_field($table, $field);
1674         }
1675
1676         $field = new xmldb_field('backpackurl');
1677
1678         // Conditionally launch drop field backpackurl.
1679         if ($dbman->field_exists($table, $field)) {
1680             $dbman->drop_field($table, $field);
1681         }
1682
1683         // Add default backpacks.
1684         require_once($CFG->dirroot . '/badges/upgradelib.php'); // Core install and upgrade related functions only for badges.
1685         badges_install_default_backpacks();
1686
1687         // Main savepoint reached.
1688         upgrade_main_savepoint(true, 2019050600.00);
1689     }
1690
1691     if ($oldversion < 2019051300.01) {
1692         $DB->set_field('analytics_models', 'enabled', '1', ['target' => '\core_user\analytics\target\upcoming_activities_due']);
1693
1694         // Main savepoint reached.
1695         upgrade_main_savepoint(true, 2019051300.01);
1696     }
1697
1698     // Automatically generated Moodle v3.7.0 release upgrade line.
1699     // Put any upgrade step following this.
1700
1701     if ($oldversion < 2019060600.02) {
1702         // Renaming 'opentogoogle' config to 'opentowebcrawlers'.
1703         $opentogooglevalue = get_config('core', 'opentogoogle');
1704
1705         // Move the value over if it was previously configured.
1706         if ($opentogooglevalue !== false) {
1707             set_config('opentowebcrawlers', $opentogooglevalue);
1708         }
1709
1710         // Remove the now unused value.
1711         unset_config('opentogoogle');
1712
1713         // Main savepoint reached.
1714         upgrade_main_savepoint(true, 2019060600.02);
1715     }
1716
1717     if ($oldversion < 2019062900.00) {
1718         // Debugsmtp is now only available via config.php.
1719         $DB->delete_records('config', array('name' => 'debugsmtp'));
1720
1721         // Main savepoint reached.
1722         upgrade_main_savepoint(true, 2019062900.00);
1723     }
1724
1725     if ($oldversion < 2019070400.01) {
1726
1727         $basecolors = ['#81ecec', '#74b9ff', '#a29bfe', '#dfe6e9', '#00b894',
1728             '#0984e3', '#b2bec3', '#fdcb6e', '#fd79a8', '#6c5ce7'];
1729
1730         $colornr = 1;
1731         foreach ($basecolors as $color) {
1732             set_config('coursecolor' .  $colornr, $color, 'core_admin');
1733             $colornr++;
1734         }
1735
1736         upgrade_main_savepoint(true, 2019070400.01);
1737     }
1738
1739     if ($oldversion < 2019072200.00) {
1740
1741         // Define field relativedatesmode to be added to course.
1742         $table = new xmldb_table('course');
1743         $field = new xmldb_field('relativedatesmode', XMLDB_TYPE_INTEGER, '1', null, XMLDB_NOTNULL, null, '0', 'enddate');
1744
1745         // Conditionally launch add field relativedatesmode.
1746         if (!$dbman->field_exists($table, $field)) {
1747             $dbman->add_field($table, $field);
1748         }
1749
1750         // Main savepoint reached.
1751         upgrade_main_savepoint(true, 2019072200.00);
1752     }
1753
1754     if ($oldversion < 2019072500.01) {
1755         // Remove the "popup" processor from the list of default processors for the messagecontactrequests notification.
1756         $oldloggedinconfig = get_config('message', 'message_provider_moodle_messagecontactrequests_loggedin');
1757         $oldloggedoffconfig = get_config('message', 'message_provider_moodle_messagecontactrequests_loggedoff');
1758         $newloggedinconfig = implode(',', array_filter(explode(',', $oldloggedinconfig), function($value) {
1759             return $value != 'popup';
1760         }));
1761         $newloggedoffconfig = implode(',', array_filter(explode(',', $oldloggedoffconfig), function($value) {
1762             return $value != 'popup';
1763         }));
1764         set_config('message_provider_moodle_messagecontactrequests_loggedin', $newloggedinconfig, 'message');
1765         set_config('message_provider_moodle_messagecontactrequests_loggedoff', $newloggedoffconfig, 'message');
1766
1767         upgrade_main_savepoint(true, 2019072500.01);
1768     }
1769
1770     if ($oldversion < 2019072500.03) {
1771         unset_config('httpswwwroot');
1772
1773         upgrade_main_savepoint(true, 2019072500.03);
1774     }
1775
1776     if ($oldversion < 2019073100.00) {
1777         // Update the empty tag instructions to null.
1778         $instructions = get_config('core', 'auth_instructions');
1779
1780         if (trim(html_to_text($instructions)) === '') {
1781             set_config('auth_instructions', '');
1782         }
1783
1784         // Main savepoint reached.
1785         upgrade_main_savepoint(true, 2019073100.00);
1786     }
1787
1788     if ($oldversion < 2019083000.01) {
1789
1790         // If block_community is no longer present, remove it.
1791         if (!file_exists($CFG->dirroot . '/blocks/community/communitycourse.php')) {
1792             // Drop table that is no longer needed.
1793             $table = new xmldb_table('block_community');
1794             if ($dbman->table_exists($table)) {
1795                 $dbman->drop_table($table);
1796             }
1797
1798             // Delete instances.
1799             $instances = $DB->get_records_list('block_instances', 'blockname', ['community']);
1800             $instanceids = array_keys($instances);
1801
1802             if (!empty($instanceids)) {
1803                 $DB->delete_records_list('block_positions', 'blockinstanceid', $instanceids);
1804                 $DB->delete_records_list('block_instances', 'id', $instanceids);
1805                 list($sql, $params) = $DB->get_in_or_equal($instanceids, SQL_PARAMS_NAMED);
1806                 $params['contextlevel'] = CONTEXT_BLOCK;
1807                 $DB->delete_records_select('context', "contextlevel=:contextlevel AND instanceid " . $sql, $params);
1808
1809                 $preferences = array();
1810                 foreach ($instances as $instanceid => $instance) {
1811                     $preferences[] = 'block' . $instanceid . 'hidden';
1812                     $preferences[] = 'docked_block_instance_' . $instanceid;
1813                 }
1814                 $DB->delete_records_list('user_preferences', 'name', $preferences);
1815             }
1816
1817             // Delete the block from the block table.
1818             $DB->delete_records('block', array('name' => 'community'));
1819
1820             // Remove capabilities.
1821             capabilities_cleanup('block_community');
1822             // Clean config.
1823             unset_all_config_for_plugin('block_community');
1824
1825             // Remove Moodle-level community based capabilities.
1826             $capabilitiestoberemoved = ['block/community:addinstance', 'block/community:myaddinstance'];
1827             // Delete any role_capabilities for the old roles.
1828             $DB->delete_records_list('role_capabilities', 'capability', $capabilitiestoberemoved);
1829             // Delete the capability itself.
1830             $DB->delete_records_list('capabilities', 'name', $capabilitiestoberemoved);
1831         }
1832
1833         upgrade_main_savepoint(true, 2019083000.01);
1834     }
1835
1836     if ($oldversion < 2019083000.02) {
1837         // Remove unused config.
1838         unset_config('enablecoursepublishing');
1839         upgrade_main_savepoint(true, 2019083000.02);
1840     }
1841
1842     if ($oldversion < 2019083000.04) {
1843         // Delete "orphaned" subscriptions.
1844         $sql = "SELECT DISTINCT es.userid
1845                   FROM {event_subscriptions} es
1846              LEFT JOIN {user} u ON u.id = es.userid
1847                  WHERE u.deleted = 1 OR u.id IS NULL";
1848         $deletedusers = $DB->get_fieldset_sql($sql);
1849         if ($deletedusers) {
1850             list($sql, $params) = $DB->get_in_or_equal($deletedusers);
1851
1852             // Delete orphaned subscriptions.
1853             $DB->execute("DELETE FROM {event_subscriptions} WHERE userid " . $sql, $params);
1854         }
1855
1856         upgrade_main_savepoint(true, 2019083000.04);
1857     }
1858
1859     if ($oldversion < 2019090500.01) {
1860
1861         // Define index analysableid (not unique) to be added to analytics_used_analysables.
1862         $table = new xmldb_table('analytics_used_analysables');
1863         $index = new xmldb_index('analysableid', XMLDB_INDEX_NOTUNIQUE, ['analysableid']);
1864
1865         // Conditionally launch add index analysableid.
1866         if (!$dbman->index_exists($table, $index)) {
1867             $dbman->add_index($table, $index);
1868         }
1869
1870         // Main savepoint reached.
1871         upgrade_main_savepoint(true, 2019090500.01);
1872     }
1873
1874     if ($oldversion < 2019092700.01) {
1875         upgrade_rename_prediction_actions_useful_incorrectly_flagged();
1876         upgrade_main_savepoint(true, 2019092700.01);
1877     }
1878
1879     if ($oldversion < 2019100800.02) {
1880         // Rename the official moodle sites directory the site is registered with.
1881         $DB->execute("UPDATE {registration_hubs}
1882                          SET hubname = ?, huburl = ?
1883                        WHERE huburl = ?", ['moodle', 'https://stats.moodle.org', 'https://moodle.net']);
1884
1885         // Convert the hub site specific settings to the new naming format without the hub URL in the name.
1886         $hubconfig = get_config('hub');
1887
1888         if (!empty($hubconfig)) {
1889             foreach (upgrade_convert_hub_config_site_param_names($hubconfig, 'https://moodle.net') as $name => $value) {
1890                 set_config($name, $value, 'hub');
1891             }
1892         }
1893
1894         upgrade_main_savepoint(true, 2019100800.02);
1895     }
1896
1897     if ($oldversion < 2019100900.00) {
1898         // If block_participants is no longer present, remove it.
1899         if (!file_exists($CFG->dirroot . '/blocks/participants/block_participants.php')) {
1900             // Delete instances.
1901             $instances = $DB->get_records_list('block_instances', 'blockname', ['participants']);
1902             $instanceids = array_keys($instances);
1903
1904             if (!empty($instanceids)) {
1905                 $DB->delete_records_list('block_positions', 'blockinstanceid', $instanceids);
1906                 $DB->delete_records_list('block_instances', 'id', $instanceids);
1907                 list($sql, $params) = $DB->get_in_or_equal($instanceids, SQL_PARAMS_NAMED);
1908                 $params['contextlevel'] = CONTEXT_BLOCK;
1909                 $DB->delete_records_select('context', "contextlevel=:contextlevel AND instanceid " . $sql, $params);
1910
1911                 $preferences = array();
1912                 foreach ($instances as $instanceid => $instance) {
1913                     $preferences[] = 'block' . $instanceid . 'hidden';
1914                     $preferences[] = 'docked_block_instance_' . $instanceid;
1915                 }
1916                 $DB->delete_records_list('user_preferences', 'name', $preferences);
1917             }
1918
1919             // Delete the block from the block table.
1920             $DB->delete_records('block', array('name' => 'participants'));
1921
1922             // Remove capabilities.
1923             capabilities_cleanup('block_participants');
1924
1925             // Clean config.
1926             unset_all_config_for_plugin('block_participants');
1927         }
1928
1929         upgrade_main_savepoint(true, 2019100900.00);
1930     }
1931
1932     if ($oldversion < 2019101600.01) {
1933
1934         // Change the setting $CFG->requestcategoryselection into $CFG->lockrequestcategory with opposite value.
1935         set_config('lockrequestcategory', empty($CFG->requestcategoryselection));
1936
1937         upgrade_main_savepoint(true, 2019101600.01);
1938     }
1939
1940     if ($oldversion < 2019101800.02) {
1941
1942         // Get the table by its previous name.
1943         $table = new xmldb_table('analytics_models');
1944         if ($dbman->table_exists($table)) {
1945
1946             // Define field contextids to be added to analytics_models.
1947             $field = new xmldb_field('contextids', XMLDB_TYPE_TEXT, null, null, null, null, null, 'version');
1948
1949             // Conditionally launch add field contextids.
1950             if (!$dbman->field_exists($table, $field)) {
1951                 $dbman->add_field($table, $field);
1952             }
1953         }
1954
1955         // Main savepoint reached.
1956         upgrade_main_savepoint(true, 2019101800.02);
1957     }
1958
1959     if ($oldversion < 2019102500.04) {
1960         // Define table h5p_libraries to be created.
1961         $table = new xmldb_table('h5p_libraries');
1962
1963         // Adding fields to table h5p_libraries.
1964         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1965         $table->add_field('machinename', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
1966         $table->add_field('title', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
1967         $table->add_field('majorversion', XMLDB_TYPE_INTEGER, '4', null, XMLDB_NOTNULL, null, null);
1968         $table->add_field('minorversion', XMLDB_TYPE_INTEGER, '4', null, XMLDB_NOTNULL, null, null);
1969         $table->add_field('patchversion', XMLDB_TYPE_INTEGER, '4', null, XMLDB_NOTNULL, null, null);
1970         $table->add_field('runnable', XMLDB_TYPE_INTEGER, '1', null, XMLDB_NOTNULL, null, null);
1971         $table->add_field('fullscreen', XMLDB_TYPE_INTEGER, '1', null, XMLDB_NOTNULL, null, '0');
1972         $table->add_field('embedtypes', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
1973         $table->add_field('preloadedjs', XMLDB_TYPE_TEXT, null, null, null, null, null);
1974         $table->add_field('preloadedcss', XMLDB_TYPE_TEXT, null, null, null, null, null);
1975         $table->add_field('droplibrarycss', XMLDB_TYPE_TEXT, null, null, null, null, null);
1976         $table->add_field('semantics', XMLDB_TYPE_TEXT, null, null, null, null, null);
1977         $table->add_field('addto', XMLDB_TYPE_TEXT, null, null, null, null, null);
1978
1979         // Adding keys to table h5p_libraries.
1980         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
1981
1982         // Adding indexes to table h5p_libraries.
1983         $table->add_index('machinemajorminorpatch', XMLDB_INDEX_NOTUNIQUE,
1984             ['machinename', 'majorversion', 'minorversion', 'patchversion', 'runnable']);
1985
1986         // Conditionally launch create table for h5p_libraries.
1987         if (!$dbman->table_exists($table)) {
1988             $dbman->create_table($table);
1989         }
1990
1991         // Define table h5p_library_dependencies to be created.
1992         $table = new xmldb_table('h5p_library_dependencies');
1993
1994         // Adding fields to table h5p_library_dependencies.
1995         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1996         $table->add_field('libraryid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
1997         $table->add_field('requiredlibraryid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
1998         $table->add_field('dependencytype', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
1999
2000         // Adding keys to table h5p_library_dependencies.
2001         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
2002         $table->add_key('libraryid', XMLDB_KEY_FOREIGN, ['libraryid'], 'h5p_libraries', ['id']);
2003         $table->add_key('requiredlibraryid', XMLDB_KEY_FOREIGN, ['requiredlibraryid'], 'h5p_libraries', ['id']);
2004
2005         // Conditionally launch create table for h5p_library_dependencies.
2006         if (!$dbman->table_exists($table)) {
2007             $dbman->create_table($table);
2008         }
2009
2010         // Define table h5p to be created.
2011         $table = new xmldb_table('h5p');
2012
2013         // Adding fields to table h5p.
2014         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2015         $table->add_field('jsoncontent', XMLDB_TYPE_TEXT, null, null, XMLDB_NOTNULL, null, null);
2016         $table->add_field('mainlibraryid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2017         $table->add_field('displayoptions', XMLDB_TYPE_INTEGER, '4', null, null, null, null);
2018         $table->add_field('pathnamehash', XMLDB_TYPE_CHAR, '40', null, XMLDB_NOTNULL, null, null);
2019         $table->add_field('contenthash', XMLDB_TYPE_CHAR, '40', null, XMLDB_NOTNULL, null, null);
2020         $table->add_field('filtered', XMLDB_TYPE_TEXT, null, null, null, null, null);
2021         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2022         $table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2023
2024         // Adding keys to table h5p.
2025         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
2026         $table->add_key('mainlibraryid', XMLDB_KEY_FOREIGN, ['mainlibraryid'], 'h5p_libraries', ['id']);
2027
2028         // Conditionally launch create table for h5p.
2029         if (!$dbman->table_exists($table)) {
2030             $dbman->create_table($table);
2031         }
2032
2033         // Define table h5p_contents_libraries to be created.
2034         $table = new xmldb_table('h5p_contents_libraries');
2035
2036         // Adding fields to table h5p_contents_libraries.
2037         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2038         $table->add_field('h5pid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2039         $table->add_field('libraryid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2040         $table->add_field('dependencytype', XMLDB_TYPE_CHAR, '10', null, XMLDB_NOTNULL, null, null);
2041         $table->add_field('dropcss', XMLDB_TYPE_INTEGER, '1', null, XMLDB_NOTNULL, null, null);
2042         $table->add_field('weight', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2043
2044         // Adding keys to table h5p_contents_libraries.
2045         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
2046         $table->add_key('h5pid', XMLDB_KEY_FOREIGN, ['h5pid'], 'h5p', ['id']);
2047         $table->add_key('libraryid', XMLDB_KEY_FOREIGN, ['libraryid'], 'h5p_libraries', ['id']);
2048
2049         // Conditionally launch create table for h5p_contents_libraries.
2050         if (!$dbman->table_exists($table)) {
2051             $dbman->create_table($table);
2052         }
2053
2054         // Define table h5p_libraries_cachedassets to be created.
2055         $table = new xmldb_table('h5p_libraries_cachedassets');
2056
2057         // Adding fields to table h5p_libraries_cachedassets.
2058         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2059         $table->add_field('libraryid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2060         $table->add_field('hash', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
2061
2062         // Adding keys to table h5p_libraries_cachedassets.
2063         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
2064         $table->add_key('libraryid', XMLDB_KEY_FOREIGN, ['libraryid'], 'h5p_libraries_cachedassets', ['id']);
2065
2066         // Conditionally launch create table for h5p_libraries_cachedassets.
2067         if (!$dbman->table_exists($table)) {
2068             $dbman->create_table($table);
2069         }
2070
2071         // Main savepoint reached.
2072         upgrade_main_savepoint(true, 2019102500.04);
2073     }
2074
2075     if ($oldversion < 2019103000.13) {
2076
2077         upgrade_analytics_fix_contextids_defaults();
2078
2079         // Main savepoint reached.
2080         upgrade_main_savepoint(true, 2019103000.13);
2081     }
2082
2083     if ($oldversion < 2019111300.00) {
2084
2085         // Define field coremajor to be added to h5p_libraries.
2086         $table = new xmldb_table('h5p_libraries');
2087         $field = new xmldb_field('coremajor', XMLDB_TYPE_INTEGER, '4', null, null, null, null, 'addto');
2088
2089         // Conditionally launch add field coremajor.
2090         if (!$dbman->field_exists($table, $field)) {
2091             $dbman->add_field($table, $field);
2092         }
2093
2094         $field = new xmldb_field('coreminor', XMLDB_TYPE_INTEGER, '4', null, null, null, null, 'coremajor');
2095
2096         // Conditionally launch add field coreminor.
2097         if (!$dbman->field_exists($table, $field)) {
2098             $dbman->add_field($table, $field);
2099         }
2100
2101         // Main savepoint reached.
2102         upgrade_main_savepoint(true, 2019111300.00);
2103     }
2104
2105     // Automatically generated Moodle v3.8.0 release upgrade line.
2106     // Put any upgrade step following this.
2107
2108     if ($oldversion < 2019120500.01) {
2109         // Delete any role assignments for roles which no longer exist.
2110         $DB->delete_records_select('role_assignments', "roleid NOT IN (SELECT id FROM {role})");
2111
2112         // Main savepoint reached.
2113         upgrade_main_savepoint(true, 2019120500.01);
2114     }
2115
2116     if ($oldversion < 2019121800.00) {
2117         // Upgrade MIME types for existing streaming files.
2118         $filetypes = array(
2119             '%.fmp4' => 'video/mp4',
2120             '%.ts' => 'video/MP2T',
2121             '%.mpd' => 'application/dash+xml',
2122             '%.m3u8' => 'application/x-mpegURL',
2123         );
2124
2125         $select = $DB->sql_like('filename', '?', false);
2126         foreach ($filetypes as $extension => $mimetype) {
2127             $DB->set_field_select(
2128                 'files',
2129                 'mimetype',
2130                 $mimetype,
2131                 $select,
2132                 array($extension)
2133             );
2134         }
2135
2136         upgrade_main_savepoint(true, 2019121800.00);
2137     }
2138
2139     if ($oldversion < 2019122000.01) {
2140         // Clean old upgrade setting not used anymore.
2141         unset_config('linkcoursesectionsupgradescriptwasrun');
2142         upgrade_main_savepoint(true, 2019122000.01);
2143     }
2144
2145     if ($oldversion < 2020010900.02) {
2146         $table = new xmldb_table('event');
2147
2148         // This index will improve the performance when the Events API retrieves category and group events.
2149         $index = new xmldb_index('eventtype', XMLDB_INDEX_NOTUNIQUE, ['eventtype']);
2150         if (!$dbman->index_exists($table, $index)) {
2151             $dbman->add_index($table, $index);
2152         }
2153
2154         // This index improves the performance of backups, deletion and visibilty changes on activities.
2155         $index = new xmldb_index('modulename-instance', XMLDB_INDEX_NOTUNIQUE, ['modulename', 'instance']);
2156         if (!$dbman->index_exists($table, $index)) {
2157             $dbman->add_index($table, $index);
2158         }
2159
2160         upgrade_main_savepoint(true, 2020010900.02);
2161     }
2162
2163     if ($oldversion < 2020011700.02) {
2164         // Delete all orphaned subscription events.
2165         $select = "subscriptionid IS NOT NULL
2166                    AND subscriptionid NOT IN (SELECT id from {event_subscriptions})";
2167         $DB->delete_records_select('event', $select);
2168
2169         upgrade_main_savepoint(true, 2020011700.02);
2170     }
2171
2172     if ($oldversion < 2020013000.01) {
2173         global $DB;
2174         // Delete any associated files.
2175         $fs = get_file_storage();
2176         $sql = "SELECT cuc.id, cuc.userid
2177                   FROM {competency_usercomp} cuc
2178              LEFT JOIN {user} u ON cuc.userid = u.id
2179                  WHERE u.deleted = 1";
2180         $usercompetencies = $DB->get_records_sql($sql);
2181         foreach ($usercompetencies as $usercomp) {
2182             $DB->delete_records('competency_evidence', ['usercompetencyid' => $usercomp->id]);
2183             $DB->delete_records('competency_usercompcourse', ['userid' => $usercomp->userid]);
2184             $DB->delete_records('competency_usercompplan', ['userid' => $usercomp->userid]);
2185             $DB->delete_records('competency_usercomp', ['userid' => $usercomp->userid]);
2186         }
2187
2188         $sql = "SELECT cue.id, cue.userid
2189                   FROM {competency_userevidence} cue
2190              LEFT JOIN {user} u ON cue.userid = u.id
2191                  WHERE u.deleted = 1";
2192         $userevidences = $DB->get_records_sql($sql);
2193         foreach ($userevidences as $userevidence) {
2194             $DB->delete_records('competency_userevidencecomp', ['userevidenceid' => $userevidence->id]);
2195             $DB->delete_records('competency_userevidence', ['id' => $userevidence->id]);
2196
2197             if ($record = $DB->get_record('context', ['contextlevel' => CONTEXT_USER, 'instanceid' => $userevidence->userid],
2198                     '*', IGNORE_MISSING)) {
2199                 // Delete all orphaned user evidences files.
2200                 $fs->delete_area_files($record->id, 'core_competency', 'userevidence', $userevidence->userid);
2201             }
2202         }
2203
2204         $sql = "SELECT cp.id
2205                   FROM {competency_plan} cp
2206              LEFT JOIN {user} u ON cp.userid = u.id
2207                  WHERE u.deleted = 1";
2208         $userplans = $DB->get_records_sql($sql);
2209         foreach ($userplans as $userplan) {
2210             $DB->delete_records('competency_plancomp', ['planid' => $userplan->id]);
2211             $DB->delete_records('competency_plan', ['id' => $userplan->id]);
2212         }
2213
2214         // Main savepoint reached.
2215         upgrade_main_savepoint(true, 2020013000.01);
2216     }
2217
2218     if ($oldversion < 2020040200.01) {
2219         // Clean up completion criteria records referring to courses that no longer exist.
2220         $select = 'criteriatype = :type AND courseinstance NOT IN (SELECT id FROM {course})';
2221         $params = ['type' => 8]; // COMPLETION_CRITERIA_TYPE_COURSE.
2222
2223         $DB->delete_records_select('course_completion_criteria', $select, $params);
2224
2225         // Main savepoint reached.
2226         upgrade_main_savepoint(true, 2020040200.01);
2227     }
2228
2229     if ($oldversion < 2020040700.00) {
2230         // Remove deprecated Mozilla OpenBadges backpack.
2231         $url = 'https://backpack.openbadges.org';
2232         $bp = $DB->get_record('badge_external_backpack', ['backpackapiurl' => $url]);
2233         if ($bp) {
2234             // Remove connections for users to this backpack.
2235             $sql = "SELECT DISTINCT bb.id
2236                       FROM {badge_backpack} bb
2237                  LEFT JOIN {badge_external} be ON be. backpackid = bb.externalbackpackid
2238                      WHERE bb.externalbackpackid = :backpackid";
2239             $params = ['backpackid' => $bp->id];
2240             $externalbackpacks = $DB->get_fieldset_sql($sql, $params);
2241             if ($externalbackpacks) {
2242                 list($sql, $params) = $DB->get_in_or_equal($externalbackpacks);
2243
2244                 // Delete user external collections references to this backpack.
2245                 $DB->execute("DELETE FROM {badge_external} WHERE backpackid " . $sql, $params);
2246             }
2247             $DB->delete_records('badge_backpack', ['externalbackpackid' => $bp->id]);
2248
2249             // Delete deprecated backpack entry.
2250             $DB->delete_records('badge_external_backpack', ['backpackapiurl' => $url]);
2251         }
2252
2253         // Set active external backpack to Badgr.io.
2254         $url = 'https://api.badgr.io/v2';
2255         if ($bp = $DB->get_record('badge_external_backpack', ['backpackapiurl' => $url])) {
2256             set_config('badges_site_backpack', $bp->id);
2257         } else {
2258             unset_config('badges_site_backpack');
2259         }
2260
2261         upgrade_main_savepoint(true, 2020040700.00);
2262     }
2263
2264     if ($oldversion < 2020041500.00) {
2265         // Define table to store contentbank contents.
2266         $table = new xmldb_table('contentbank_content');
2267
2268         // Adding fields to table content_bank.
2269         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2270         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
2271         $table->add_field('contenttype', XMLDB_TYPE_CHAR, '100', null, XMLDB_NOTNULL, null, null);
2272         $table->add_field('contextid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2273         $table->add_field('instanceid', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
2274         $table->add_field('configdata', XMLDB_TYPE_TEXT, null, null, null, null, null);
2275         $table->add_field('usercreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2276         $table->add_field('usermodified', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
2277         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2278         $table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', null, null, null, '0');
2279
2280         // Adding keys to table contentbank_content.
2281         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
2282         $table->add_key('contextid', XMLDB_KEY_FOREIGN, ['contextid'], 'context', ['id']);
2283         $table->add_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
2284         $table->add_key('usercreated', XMLDB_KEY_FOREIGN, ['usercreated'], 'user', ['id']);
2285
2286         // Adding indexes to table contentbank_content.
2287         $table->add_index('name', XMLDB_INDEX_NOTUNIQUE, ['name']);
2288         $table->add_index('instance', XMLDB_INDEX_NOTUNIQUE, ['contextid', 'contenttype', 'instanceid']);
2289
2290         if (!$dbman->table_exists($table)) {
2291             $dbman->create_table($table);
2292         }
2293
2294         // Main savepoint reached.
2295         upgrade_main_savepoint(true, 2020041500.00);
2296     }
2297
2298     if ($oldversion < 2020041700.01) {
2299         // Upgrade h5p MIME type for existing h5p files.
2300         $select = $DB->sql_like('filename', '?', false);
2301         $DB->set_field_select(
2302             'files',
2303             'mimetype',
2304             'application/zip.h5p',
2305             $select,
2306             array('%.h5p')
2307         );
2308
2309         upgrade_main_savepoint(true, 2020041700.01);
2310     }
2311
2312     if ($oldversion < 2020042800.01) {
2313         // Delete obsolete config value.
2314         unset_config('enablesafebrowserintegration');
2315         // Clean up config of the old plugin.
2316         unset_all_config_for_plugin('quizaccess_safebrowser');
2317
2318         upgrade_main_savepoint(true, 2020042800.01);
2319     }
2320
2321     if ($oldversion < 2020051900.01) {
2322         // Define field component to be added to event.
2323         $table = new xmldb_table('event');
2324         $field = new xmldb_field('component', XMLDB_TYPE_CHAR, '100', null, null, null, null, 'repeatid');
2325
2326         // Conditionally launch add field component.
2327         if (!$dbman->field_exists($table, $field)) {
2328             $dbman->add_field($table, $field);
2329         }
2330
2331         // Define index component (not unique) to be added to event.
2332         $table = new xmldb_table('event');
2333         $index = new xmldb_index('component', XMLDB_INDEX_NOTUNIQUE, ['component', 'eventtype', 'instance']);
2334
2335         // Conditionally launch add index component.
2336         if (!$dbman->index_exists($table, $index)) {
2337             $dbman->add_index($table, $index);
2338         }
2339
2340         // Main savepoint reached.
2341         upgrade_main_savepoint(true, 2020051900.01);
2342     }
2343
2344     if ($oldversion < 2020052000.00) {
2345         // Define table badge_backpack_oauth2 to be created.
2346         $table = new xmldb_table('badge_backpack_oauth2');
2347
2348         // Adding fields to table badge_backpack_oauth2.
2349         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2350         $table->add_field('usermodified', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2351         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2352         $table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2353         $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2354         $table->add_field('issuerid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2355         $table->add_field('externalbackpackid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2356         $table->add_field('token', XMLDB_TYPE_TEXT, null, null, XMLDB_NOTNULL, null, null);
2357         $table->add_field('refreshtoken', XMLDB_TYPE_TEXT, null, null, XMLDB_NOTNULL, null, null);
2358         $table->add_field('expires', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
2359         $table->add_field('scope', XMLDB_TYPE_TEXT, null, null, null, null, null);
2360
2361         // Adding keys to table badge_backpack_oauth2.
2362         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
2363         $table->add_key('usermodified', XMLDB_KEY_FOREIGN, ['usermodified'], 'user', ['id']);
2364         $table->add_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
2365         $table->add_key('issuerid', XMLDB_KEY_FOREIGN, ['issuerid'], 'oauth2_issuer', ['id']);
2366         $table->add_key('externalbackpackid', XMLDB_KEY_FOREIGN, ['externalbackpackid'], 'badge_external_backpack', ['id']);
2367         // Conditionally launch create table for badge_backpack_oauth2.
2368         if (!$dbman->table_exists($table)) {
2369             $dbman->create_table($table);
2370         }
2371
2372         // Define field oauth2_issuerid to be added to badge_external_backpack.
2373         $tablebadgeexternalbackpack = new xmldb_table('badge_external_backpack');
2374         $fieldoauth2issuerid = new xmldb_field('oauth2_issuerid', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'password');
2375         $keybackpackoauth2key = new xmldb_key('backpackoauth2key', XMLDB_KEY_FOREIGN, ['oauth2_issuerid'], 'oauth2_issuer', ['id']);
2376
2377         // Conditionally launch add field oauth2_issuerid.
2378         if (!$dbman->field_exists($tablebadgeexternalbackpack, $fieldoauth2issuerid)) {
2379             $dbman->add_field($tablebadgeexternalbackpack, $fieldoauth2issuerid);
2380
2381             // Launch add key backpackoauth2key.
2382             $dbman->add_key($tablebadgeexternalbackpack, $keybackpackoauth2key);
2383         }
2384
2385         // Define field assertion to be added to badge_external.
2386         $tablebadgeexternal = new xmldb_table('badge_external');
2387         $fieldassertion = new xmldb_field('assertion', XMLDB_TYPE_TEXT, null, null, null, null, null, 'entityid');
2388
2389         // Conditionally launch add field assertion.
2390         if (!$dbman->field_exists($tablebadgeexternal, $fieldassertion)) {
2391             $dbman->add_field($tablebadgeexternal, $fieldassertion);
2392         }
2393
2394         // Main savepoint reached.
2395         upgrade_main_savepoint(true, 2020052000.00);
2396     }
2397
2398     if ($oldversion < 2020052200.01) {
2399
2400         // Define field custom to be added to license.
2401         $table = new xmldb_table('license');
2402         $field = new xmldb_field('custom', XMLDB_TYPE_INTEGER, '1', null, XMLDB_NOTNULL, null, '0');
2403
2404         // Conditionally launch add field custom.
2405         if (!$dbman->field_exists($table, $field)) {
2406             $dbman->add_field($table, $field);
2407         }
2408
2409         // Define field sortorder to be added to license.
2410         $field = new xmldb_field('sortorder', XMLDB_TYPE_INTEGER, '5', null, XMLDB_NOTNULL, null, '0');
2411
2412         // Conditionally launch add field sortorder.
2413         if (!$dbman->field_exists($table, $field)) {
2414             $dbman->add_field($table, $field);
2415         }
2416
2417         // Define index license (not unique) to be added to files.
2418         $table = new xmldb_table('files');
2419         $index = new xmldb_index('license', XMLDB_INDEX_NOTUNIQUE, ['license']);
2420
2421         // Conditionally launch add index license.
2422         if (!$dbman->index_exists($table, $index)) {
2423             $dbman->add_index($table, $index);
2424         }
2425
2426         // Upgrade the core license details.
2427         upgrade_core_licenses();
2428
2429         // Main savepoint reached.
2430         upgrade_main_savepoint(true, 2020052200.01);
2431     }
2432
2433     if ($oldversion < 2020060500.01) {
2434         // Define field moodlenetprofile to be added to user.
2435         $table = new xmldb_table('user');
2436         $field = new xmldb_field('moodlenetprofile', XMLDB_TYPE_CHAR, '255', null, null, null, null, 'alternatename');
2437
2438         // Conditionally launch add field moodlenetprofile.
2439         if (!$dbman->field_exists($table, $field)) {
2440             $dbman->add_field($table, $field);
2441         }
2442
2443         // Main savepoint reached.
2444         upgrade_main_savepoint(true, 2020060500.01);
2445     }
2446
2447     // Automatically generated Moodle v3.9.0 release upgrade line.
2448     // Put any upgrade step following this.
2449     if ($oldversion < 2020061500.02) {
2450         // Update default digital age consent map according to the current legislation on each country.
2451
2452         // The default age of digital consent map for 38 and below.
2453         $oldageofdigitalconsentmap = implode(PHP_EOL, [
2454             '*, 16',
2455             'AT, 14',
2456             'ES, 14',
2457             'US, 13'
2458         ]);
2459
2460         // Check if the current age of digital consent map matches the old one.
2461         if (get_config('moodle', 'agedigitalconsentmap') === $oldageofdigitalconsentmap) {
2462             // If the site is still using the old defaults, upgrade to the new default.
2463             $ageofdigitalconsentmap = implode(PHP_EOL, [
2464                 '*, 16',
2465                 'AT, 14',
2466                 'BE, 13',
2467                 'BG, 14',
2468                 'CY, 14',
2469                 'CZ, 15',
2470                 'DK, 13',
2471                 'EE, 13',
2472                 'ES, 14',
2473                 'FI, 13',
2474                 'FR, 15',
2475                 'GB, 13',
2476                 'GR, 15',
2477                 'IT, 14',
2478                 'LT, 14',
2479                 'LV, 13',
2480                 'MT, 13',
2481                 'NO, 13',
2482                 'PT, 13',
2483                 'SE, 13',
2484                 'US, 13'
2485             ]);
2486             set_config('agedigitalconsentmap', $ageofdigitalconsentmap);
2487         }
2488
2489         upgrade_main_savepoint(true, 2020061500.02);
2490     }
2491
2492     if ($oldversion < 2020062600.01) {
2493         // Add index to the token field in the external_tokens table.
2494         $table = new xmldb_table('external_tokens');
2495         $index = new xmldb_index('token', XMLDB_INDEX_NOTUNIQUE, ['token']);
2496
2497         if (!$dbman->index_exists($table, $index)) {
2498             $dbman->add_index($table, $index);
2499         }
2500
2501         upgrade_main_savepoint(true, 2020062600.01);
2502     }
2503
2504     if ($oldversion < 2020071100.01) {
2505         // Clean up completion criteria records referring to NULL course prerequisites.
2506         $select = 'criteriatype = :type AND courseinstance IS NULL';
2507         $params = ['type' => 8]; // COMPLETION_CRITERIA_TYPE_COURSE.
2508
2509         $DB->delete_records_select('course_completion_criteria', $select, $params);
2510
2511         // Main savepoint reached.
2512         upgrade_main_savepoint(true, 2020071100.01);
2513     }
2514
2515     if ($oldversion < 2020072300.01) {
2516         // Restore and set the guest user if it has been previously removed via GDPR, or set to an nonexistent
2517         // user account.
2518         $currentguestuser = $DB->get_record('user', array('id' => $CFG->siteguest));
2519
2520         if (!$currentguestuser) {
2521             if (!$guest = $DB->get_record('user', array('username' => 'guest', 'mnethostid' => $CFG->mnet_localhost_id))) {
2522                 // Create a guest user account.
2523                 $guest = new stdClass();
2524                 $guest->auth        = 'manual';
2525                 $guest->username    = 'guest';
2526                 $guest->password    = hash_internal_user_password('guest');
2527                 $guest->firstname   = get_string('guestuser');
2528                 $guest->lastname    = ' ';
2529                 $guest->email       = 'root@localhost';
2530                 $guest->description = get_string('guestuserinfo');
2531                 $guest->mnethostid  = $CFG->mnet_localhost_id;
2532                 $guest->confirmed   = 1;
2533                 $guest->lang        = $CFG->lang;
2534                 $guest->timemodified= time();
2535                 $guest->id = $DB->insert_record('user', $guest);
2536             }
2537             // Set the guest user.
2538             set_config('siteguest', $guest->id);
2539         }
2540
2541         // Main savepoint reached.
2542         upgrade_main_savepoint(true, 2020072300.01);
2543     }
2544
2545     if ($oldversion < 2020081400.01) {
2546         // Delete all user evidence files from users that have been deleted.
2547         $sql = "SELECT DISTINCT f.*
2548                   FROM {files} f
2549              LEFT JOIN {context} c ON f.contextid = c.id
2550                  WHERE f.component = :component
2551                    AND f.filearea = :filearea
2552                    AND c.id IS NULL";
2553         $stalefiles = $DB->get_records_sql($sql, ['component' => 'core_competency', 'filearea' => 'userevidence']);
2554
2555         $fs = get_file_storage();
2556         foreach ($stalefiles as $stalefile) {
2557             $fs->get_file_instance($stalefile)->delete();
2558         }
2559
2560         upgrade_main_savepoint(true, 2020081400.01);
2561     }
2562
2563     if ($oldversion < 2020081400.02) {
2564
2565         // Define field timecreated to be added to task_adhoc.
2566         $table = new xmldb_table('task_adhoc');
2567         $field = new xmldb_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0', 'blocking');
2568
2569         // Conditionally launch add field timecreated.
2570         if (!$dbman->field_exists($table, $field)) {
2571             $dbman->add_field($table, $field);
2572         }
2573
2574         // Main savepoint reached.
2575         upgrade_main_savepoint(true, 2020081400.02);
2576     }
2577
2578     if ($oldversion < 2020082200.01) {
2579         // Define field metadatasettings to be added to h5p_libraries.
2580         $table = new xmldb_table('h5p_libraries');
2581         $field = new xmldb_field('metadatasettings', XMLDB_TYPE_TEXT, null, null, null, null, null, 'coreminor');
2582
2583         // Conditionally launch add field metadatasettings.
2584         if (!$dbman->field_exists($table, $field)) {
2585             $dbman->add_field($table, $field);
2586         }
2587
2588         // Get installed library files that have no metadata settings value.
2589         $params = [
2590             'component' => 'core_h5p',
2591             'filearea' => 'libraries',
2592             'filename' => 'library.json',
2593         ];
2594         $sql = "SELECT l.id, f.id as fileid
2595                   FROM {files} f
2596              LEFT JOIN {h5p_libraries} l ON f.itemid = l.id
2597                  WHERE f.component = :component
2598                        AND f.filearea = :filearea
2599                        AND f.filename = :filename";
2600         $libraries = $DB->get_records_sql($sql, $params);
2601
2602         // Update metadatasettings field when the attribute is present in the library.json file.
2603         $fs = get_file_storage();
2604         foreach ($libraries as $library) {
2605             $jsonfile = $fs->get_file_by_id($library->fileid);
2606             $jsoncontent = json_decode($jsonfile->get_content());
2607             if (isset($jsoncontent->metadataSettings)) {
2608                 unset($library->fileid);
2609                 $library->metadatasettings = json_encode($jsoncontent->metadataSettings);
2610                 $DB->update_record('h5p_libraries', $library);
2611             }
2612         }
2613
2614         // Main savepoint reached.
2615         upgrade_main_savepoint(true, 2020082200.01);
2616     }
2617
2618     if ($oldversion < 2020082200.02) {
2619         // Define fields to be added to task_scheduled.
2620         $table = new xmldb_table('task_scheduled');
2621         $field = new xmldb_field('timestarted', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'disabled');
2622         if (!$dbman->field_exists($table, $field)) {
2623             $dbman->add_field($table, $field);
2624         }
2625         $field = new xmldb_field('hostname', XMLDB_TYPE_CHAR, '255', null, null, null, null, 'timestarted');
2626         if (!$dbman->field_exists($table, $field)) {
2627             $dbman->add_field($table, $field);
2628         }
2629         $field = new xmldb_field('pid', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'hostname');
2630         if (!$dbman->field_exists($table, $field)) {
2631             $dbman->add_field($table, $field);
2632         }
2633
2634         // Define fields to be added to task_adhoc.
2635         $table = new xmldb_table('task_adhoc');
2636         $field = new xmldb_field('timestarted', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'blocking');
2637         if (!$dbman->field_exists($table, $field)) {
2638             $dbman->add_field($table, $field);
2639         }
2640         $field = new xmldb_field('hostname', XMLDB_TYPE_CHAR, '255', null, null, null, null, 'timestarted');
2641         if (!$dbman->field_exists($table, $field)) {
2642             $dbman->add_field($table, $field);
2643         }
2644         $field = new xmldb_field('pid', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'hostname');
2645         if (!$dbman->field_exists($table, $field)) {
2646             $dbman->add_field($table, $field);
2647         }
2648
2649         // Define fields to be added to task_log.
2650         $table = new xmldb_table('task_log');
2651         $field = new xmldb_field('hostname', XMLDB_TYPE_CHAR, '255', null, null, null, null, 'output');
2652         if (!$dbman->field_exists($table, $field)) {
2653             $dbman->add_field($table, $field);
2654         }
2655         $field = new xmldb_field('pid', XMLDB_TYPE_INTEGER, '10', null, null, null, null, 'hostname');
2656         if (!$dbman->field_exists($table, $field)) {
2657             $dbman->add_field($table, $field);
2658         }
2659
2660         // Main savepoint reached.
2661         upgrade_main_savepoint(true, 2020082200.02);
2662     }
2663
2664     if ($oldversion < 2020082200.03) {
2665         // Define table to store virus infected details.
2666         $table = new xmldb_table('infected_files');
2667
2668         // Adding fields to table infected_files.
2669         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2670         $table->add_field('filename', XMLDB_TYPE_TEXT, null, null, XMLDB_NOTNULL, null, null);
2671         $table->add_field('quarantinedfile', XMLDB_TYPE_TEXT, null, null, null, null, null);
2672         $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2673         $table->add_field('reason', XMLDB_TYPE_TEXT, null, null, XMLDB_NOTNULL, null, null);
2674         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2675
2676         // Adding keys to table infected_files.
2677         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
2678         $table->add_key('userid', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
2679
2680         // Conditionally launch create table for infected_files.
2681         if (!$dbman->table_exists($table)) {
2682             $dbman->create_table($table);
2683         }
2684         upgrade_main_savepoint(true, 2020082200.03);
2685     }
2686
2687     if ($oldversion < 2020091000.02) {
2688         // Remove all the files with component='core_h5p' and filearea='editor' because they won't be used anymore.
2689         $fs = get_file_storage();
2690         $syscontext = context_system::instance();
2691         $fs->delete_area_files($syscontext->id, 'core_h5p', 'editor');
2692
2693         // Main savepoint reached.
2694         upgrade_main_savepoint(true, 2020091000.02);
2695     }
2696
2697     if ($oldversion < 2020091800.01) {
2698         // Copy From id captures the id of the source course when a new course originates from a restore
2699         // of another course on the same site.
2700         $table = new xmldb_table('course');
2701         $field = new xmldb_field('originalcourseid', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
2702
2703         if (!$dbman->field_exists($table, $field)) {
2704             $dbman->add_field($table, $field);
2705         }
2706
2707         // Main savepoint reached.
2708         upgrade_main_savepoint(true, 2020091800.01);
2709     }
2710
2711     if ($oldversion < 2020100200.01) {
2712         // Define table oauth2_refresh_token to be created.
2713         $table = new xmldb_table('oauth2_refresh_token');
2714
2715         // Adding fields to table oauth2_refresh_token.
2716         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2717         $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2718         $table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2719         $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2720         $table->add_field('issuerid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2721         $table->add_field('token', XMLDB_TYPE_TEXT, null, null, XMLDB_NOTNULL, null, null);
2722         $table->add_field('scopehash', XMLDB_TYPE_CHAR, 40, null, XMLDB_NOTNULL, null, null);
2723
2724         // Adding keys to table oauth2_refresh_token.
2725         $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
2726         $table->add_key('issueridkey', XMLDB_KEY_FOREIGN, ['issuerid'], 'oauth2_issuer', ['id']);
2727         $table->add_key('useridkey', XMLDB_KEY_FOREIGN, ['userid'], 'user', ['id']);
2728
2729         // Adding indexes to table oauth2_refresh_token.
2730         $table->add_index('userid-issuerid-scopehash', XMLDB_INDEX_UNIQUE, array('userid', 'issuerid', 'scopehash'));
2731
2732         // Conditionally launch create table for oauth2_refresh_token.
2733         if (!$dbman->table_exists($table)) {
2734             $dbman->create_table($table);
2735         }
2736
2737         // Main savepoint reached.
2738         upgrade_main_savepoint(true, 2020100200.01);
2739     }
2740
2741     if ($oldversion < 2020100700.00) {
2742
2743         // Define index modulename-instance-eventtype (not unique) to be added to event.
2744         $table = new xmldb_table('event');
2745         $index = new xmldb_index('modulename-instance-eventtype', XMLDB_INDEX_NOTUNIQUE, ['modulename', 'instance', 'eventtype']);
2746
2747         // Conditionally launch add index modulename-instance-eventtype.
2748         if (!$dbman->index_exists($table, $index)) {
2749             $dbman->add_index($table, $index);
2750         }
2751
2752         // Define index modulename-instance (not unique) to be dropped form event.
2753         $table = new xmldb_table('event');
2754         $index = new xmldb_index('modulename-instance', XMLDB_INDEX_NOTUNIQUE, ['modulename', 'instance']);
2755
2756         // Conditionally launch drop index modulename-instance.
2757         if ($dbman->index_exists($table, $index)) {
2758             $dbman->drop_index($table, $index);
2759         }
2760
2761         // Main savepoint reached.
2762         upgrade_main_savepoint(true, 2020100700.00);
2763     }
2764
2765     if ($oldversion < 2020101300.01) {
2766         // Define fields tutorial and example to be added to h5p_libraries.
2767         $table = new xmldb_table('h5p_libraries');
2768
2769         // Add tutorial field.
2770         $field = new xmldb_field('tutorial', XMLDB_TYPE_TEXT, null, null, null, null, null, 'metadatasettings');
2771         if (!$dbman->field_exists($table, $field)) {
2772             $dbman->add_field($table, $field);
2773         }
2774
2775         // Add example field.
2776         $field = new xmldb_field('example', XMLDB_TYPE_TEXT, null, null, null, null, null, 'tutorial');
2777
2778         if (!$dbman->field_exists($table, $field)) {
2779             $dbman->add_field($table, $field);
2780         }
2781
2782         // Main savepoint reached.
2783         upgrade_main_savepoint(true, 2020101300.01);
2784     }
2785
2786     if ($oldversion < 2020101600.01) {
2787         // Delete orphaned course_modules_completion rows; these were not deleted properly
2788         // by remove_course_contents function.
2789         $DB->delete_records_subquery('course_modules_completion', 'id', 'id',
2790                "SELECT cmc.id
2791                   FROM {course_modules_completion} cmc
2792              LEFT JOIN {course_modules} cm ON cm.id = cmc.coursemoduleid
2793                  WHERE cm.id IS NULL");
2794         upgrade_main_savepoint(true, 2020101600.01);
2795     }
2796
2797     if ($oldversion < 2020101600.02) {
2798         // Script to fix incorrect records of "hidden" field in existing grade items.
2799         $sql = "SELECT cm.instance, cm.course
2800                   FROM {course_modules} cm
2801                   JOIN {modules} m ON m.id = cm.module
2802                  WHERE m.name = :module AND cm.visible = :visible";
2803         $hidequizlist = $DB->get_recordset_sql($sql, ['module' => 'quiz', 'visible' => 0]);
2804
2805         foreach ($hidequizlist as $hidequiz) {
2806             $params = [
2807                 'itemmodule'    => 'quiz',
2808                 'courseid'      => $hidequiz->course,
2809                 'iteminstance'  => $hidequiz->instance,
2810             ];
2811
2812             $DB->set_field('grade_items', 'hidden', 1, $params);
2813         }
2814         $hidequizlist->close();
2815
2816         upgrade_main_savepoint(true, 2020101600.02);
2817     }
2818
2819     if ($oldversion < 2020102100.01) {
2820         // Get the current guest user which is also set as 'deleted'.
2821         $guestuser = $DB->get_record('user', ['id' => $CFG->siteguest, 'deleted' => 1]);
2822         // If there is a deleted guest user, reset the user to not be deleted and make sure the related
2823         // user context exists.
2824         if ($guestuser) {
2825             $guestuser->deleted = 0;
2826             $DB->update_record('user', $guestuser);
2827
2828             // Get the guest user context.
2829             $guestusercontext = $DB->get_record('context',
2830                 ['contextlevel' => CONTEXT_USER, 'instanceid' => $guestuser->id]);
2831
2832             // If the guest user context does not exist, create it.
2833             if (!$guestusercontext) {
2834                 $record = new stdClass();
2835                 $record->contextlevel = CONTEXT_USER;
2836                 $record->instanceid = $guestuser->id;
2837                 $record->depth = 0;
2838                 // The path is not known before insert.
2839                 $record->path = null;
2840                 $record->locked = 0;
2841
2842                 $record->id = $DB->insert_record('context', $record);
2843
2844                 // Update the path.
2845                 $record->path = '/' . SYSCONTEXTID . '/' . $record->id;
2846                 $record->depth = substr_count($record->path, '/');
2847                 $DB->update_record('context', $record);
2848             }
2849         }
2850
2851         // Main savepoint reached.
2852         upgrade_main_savepoint(true, 2020102100.01);
2853     }
2854
2855     if ($oldversion < 2020102100.02) {
2856         // Reset analytics model output dir if it's the default value.
2857         $modeloutputdir = get_config('analytics', 'modeloutputdir');
2858         if (strcasecmp($modeloutputdir, $CFG->dataroot . DIRECTORY_SEPARATOR . 'models') == 0) {
2859             set_config('modeloutputdir', '', 'analytics');
2860         }
2861
2862         // Main savepoint reached.
2863         upgrade_main_savepoint(true, 2020102100.02);
2864     }
2865
2866     if ($oldversion < 2020102300.01) {
2867         // Define field downloadcontent to be added to course.
2868         $table = new xmldb_table('course');
2869         $field = new xmldb_field('downloadcontent', XMLDB_TYPE_INTEGER, '1', null, null, null, null, 'visibleold');
2870
2871         if (!$dbman->field_exists($table, $field)) {
2872             $dbman->add_field($table, $field);
2873         }
2874
2875         // Main savepoint reached.
2876         upgrade_main_savepoint(true, 2020102300.01);
2877     }
2878
2879     if ($oldversion < 2020102300.02) {
2880         $table = new xmldb_table('badge_backpack');
2881
2882         // There is no key_exists, so test the equivalent index.
2883         $oldindex = new xmldb_index('backpackcredentials', XMLDB_KEY_UNIQUE, ['userid', 'externalbackpackid']);
2884         if (!$dbman->index_exists($table, $oldindex)) {
2885             // All external backpack providers/hosts are now exclusively stored in badge_external_backpack.
2886             // All credentials are stored in badge_backpack and are unique per user, backpack.
2887             $uniquekey = new xmldb_key('backpackcredentials', XMLDB_KEY_UNIQUE, ['userid', 'externalbackpackid']);
2888             $dbman->add_key($table, $uniquekey);
2889         }
2890
2891         // Drop the password field as this is moved to badge_backpack.
2892         $table = new xmldb_table('badge_external_backpack');
2893         $field = new xmldb_field('password', XMLDB_TYPE_CHAR, '50');
2894         if ($dbman->field_exists($table, $field)) {
2895             // If there is a current backpack set then copy it across to the new structure.
2896             if ($CFG->badges_defaultissuercontact) {
2897                 // Get the currently used site backpacks.
2898                 $records = $DB->get_records_select('badge_external_backpack', "password IS NOT NULL AND password != ''");
2899                 $backpack = [
2900                     'userid' => '0',
2901                     'email' => $CFG->badges_defaultissuercontact,
2902                     'backpackuid' => -1
2903                 ];
2904
2905                 // Create records corresponding to the site backpacks.
2906                 foreach ($records as $record) {
2907                     $backpack['password'] = $record->password;
2908                     $backpack['externalbackpackid'] = $record->id;
2909                     $DB->insert_record('badge_backpack', (object) $backpack);
2910                 }
2911             }
2912
2913             $dbman->drop_field($table, $field);
2914         }
2915
2916         // Main savepoint reached.
2917         upgrade_main_savepoint(true, 2020102300.02);
2918     }
2919
2920     return true;
2921 }