[Summary view] [Print] [Text view]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * Upgrade helper functions
  19   *
  20   * This file is used for special upgrade functions - for example groups and gradebook.
  21   * These functions must use SQL and database related functions only- no other Moodle API,
  22   * because it might depend on db structures that are not yet present during upgrade.
  23   * (Do not use functions from accesslib.php, grades classes or group functions at all!)
  24   *
  25   * @package   core_install
  26   * @category  upgrade
  27   * @copyright 2007 Petr Skoda (http://skodak.org)
  28   * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  29   */
  30  
  31  defined('MOODLE_INTERNAL') || die();
  32  
  33  /**
  34   * Returns all non-view and non-temp tables with sane names.
  35   * Prints list of non-supported tables using $OUTPUT->notification()
  36   *
  37   * @return array
  38   */
  39  function upgrade_mysql_get_supported_tables() {
  40      global $OUTPUT, $DB;
  41  
  42      $tables = array();
  43      $patprefix = str_replace('_', '\\_', $DB->get_prefix());
  44      $pregprefix = preg_quote($DB->get_prefix(), '/');
  45  
  46      $sql = "SHOW FULL TABLES LIKE '$patprefix%'";
  47      $rs = $DB->get_recordset_sql($sql);
  48      foreach ($rs as $record) {
  49          $record = array_change_key_case((array)$record, CASE_LOWER);
  50          $type = $record['table_type'];
  51          unset($record['table_type']);
  52          $fullname = array_shift($record);
  53  
  54          if ($pregprefix === '') {
  55              $name = $fullname;
  56          } else {
  57              $count = null;
  58              $name = preg_replace("/^$pregprefix/", '', $fullname, -1, $count);
  59              if ($count !== 1) {
  60                  continue;
  61              }
  62          }
  63  
  64          if (!preg_match("/^[a-z][a-z0-9_]*$/", $name)) {
  65              echo $OUTPUT->notification("Database table with invalid name '$fullname' detected, skipping.", 'notifyproblem');
  66              continue;
  67          }
  68          if ($type === 'VIEW') {
  69              echo $OUTPUT->notification("Unsupported database table view '$fullname' detected, skipping.", 'notifyproblem');
  70              continue;
  71          }
  72          $tables[$name] = $name;
  73      }
  74      $rs->close();
  75  
  76      return $tables;
  77  }
  78  
  79  /**
  80   * Remove all signed numbers from current database and change
  81   * text fields to long texts - mysql only.
  82   */
  83  function upgrade_mysql_fix_unsigned_and_lob_columns() {
  84      // We are not using standard API for changes of column
  85      // because everything 'signed'-related will be removed soon.
  86  
  87      // If anybody already has numbers higher than signed limit the execution stops
  88      // and tables must be fixed manually before continuing upgrade.
  89  
  90      global $DB;
  91  
  92      if ($DB->get_dbfamily() !== 'mysql') {
  93          return;
  94      }
  95  
  96      $pbar = new progress_bar('mysqlconvertunsignedlobs', 500, true);
  97  
  98      $prefix = $DB->get_prefix();
  99      $tables = upgrade_mysql_get_supported_tables();
 100  
 101      $tablecount = count($tables);
 102      $i = 0;
 103      foreach ($tables as $table) {
 104          $i++;
 105  
 106          $changes = array();
 107  
 108          $sql = "SHOW COLUMNS FROM `{{$table}}`";
 109          $rs = $DB->get_recordset_sql($sql);
 110          foreach ($rs as $column) {
 111              $column = (object)array_change_key_case((array)$column, CASE_LOWER);
 112              if (stripos($column->type, 'unsigned') !== false) {
 113                  $maxvalue = 0;
 114                  if (preg_match('/^int/i', $column->type)) {
 115                      $maxvalue = 2147483647;
 116                  } else if (preg_match('/^medium/i', $column->type)) {
 117                      $maxvalue = 8388607;
 118                  } else if (preg_match('/^smallint/i', $column->type)) {
 119                      $maxvalue = 32767;
 120                  } else if (preg_match('/^tinyint/i', $column->type)) {
 121                      $maxvalue = 127;
 122                  }
 123                  if ($maxvalue) {
 124                      // Make sure nobody is abusing our integer ranges - moodle int sizes are in digits, not bytes!!!
 125                      $invalidcount = $DB->get_field_sql("SELECT COUNT('x') FROM `{{$table}}` WHERE `$column->field` > :maxnumber", array('maxnumber'=>$maxvalue));
 126                      if ($invalidcount) {
 127                          throw new moodle_exception('notlocalisederrormessage', 'error', new moodle_url('/admin/'), "Database table '{$table}'' contains unsigned column '{$column->field}' with $invalidcount values that are out of allowed range, upgrade can not continue.");
 128                      }
 129                  }
 130                  $type = preg_replace('/unsigned/i', 'signed', $column->type);
 131                  $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
 132                  $default = (!is_null($column->default) and $column->default !== '') ? "DEFAULT '$column->default'" : '';
 133                  $autoinc = (stripos($column->extra, 'auto_increment') !== false) ? 'AUTO_INCREMENT' : '';
 134                  // Primary and unique not necessary here, change_database_structure does not add prefix.
 135                  $changes[] = "MODIFY COLUMN `$column->field` $type $notnull $default $autoinc";
 136  
 137              } else if ($column->type === 'tinytext' or $column->type === 'mediumtext' or $column->type === 'text') {
 138                  $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
 139                  $default = (!is_null($column->default) and $column->default !== '') ? "DEFAULT '$column->default'" : '';
 140                  // Primary, unique and inc are not supported for texts.
 141                  $changes[] = "MODIFY COLUMN `$column->field` LONGTEXT $notnull $default";
 142  
 143              } else if ($column->type === 'tinyblob' or $column->type === 'mediumblob' or $column->type === 'blob') {
 144                  $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
 145                  $default = (!is_null($column->default) and $column->default !== '') ? "DEFAULT '$column->default'" : '';
 146                  // Primary, unique and inc are not supported for blobs.
 147                  $changes[] = "MODIFY COLUMN `$column->field` LONGBLOB $notnull $default";
 148              }
 149  
 150          }
 151          $rs->close();
 152  
 153          if ($changes) {
 154              // Set appropriate timeout - 1 minute per thousand of records should be enough, min 60 minutes just in case.
 155              $count = $DB->count_records($table, array());
 156              $timeout = ($count/1000)*60;
 157              $timeout = ($timeout < 60*60) ? 60*60 : (int)$timeout;
 158              upgrade_set_timeout($timeout);
 159  
 160              $sql = "ALTER TABLE `{$prefix}$table` ".implode(', ', $changes);
 161              $DB->change_database_structure($sql);
 162          }
 163  
 164          $pbar->update($i, $tablecount, "Converted unsigned/lob columns in MySQL database - $i/$tablecount.");
 165      }
 166  }
 167  
 168  /**
 169   * Migrate NTEXT to NVARCHAR(MAX).
 170   */
 171  function upgrade_mssql_nvarcharmax() {
 172      global $DB;
 173  
 174      if ($DB->get_dbfamily() !== 'mssql') {
 175          return;
 176      }
 177  
 178      $pbar = new progress_bar('mssqlconvertntext', 500, true);
 179  
 180      $prefix = $DB->get_prefix();
 181      $tables = $DB->get_tables(false);
 182  
 183      $tablecount = count($tables);
 184      $i = 0;
 185      foreach ($tables as $table) {
 186          $i++;
 187  
 188          $columns = array();
 189  
 190          $sql = "SELECT column_name
 191                    FROM INFORMATION_SCHEMA.COLUMNS
 192                   WHERE table_name = '{{$table}}' AND UPPER(data_type) = 'NTEXT'";
 193          $rs = $DB->get_recordset_sql($sql);
 194          foreach ($rs as $column) {
 195              $columns[] = $column->column_name;
 196          }
 197          $rs->close();
 198  
 199          if ($columns) {
 200              // Set appropriate timeout - 1 minute per thousand of records should be enough, min 60 minutes just in case.
 201              $count = $DB->count_records($table, array());
 202              $timeout = ($count/1000)*60;
 203              $timeout = ($timeout < 60*60) ? 60*60 : (int)$timeout;
 204              upgrade_set_timeout($timeout);
 205  
 206              $updates = array();
 207              foreach ($columns as $column) {
 208                  // Change the definition.
 209                  $sql = "ALTER TABLE {$prefix}$table ALTER COLUMN $column NVARCHAR(MAX)";
 210                  $DB->change_database_structure($sql);
 211                  $updates[] = "$column = $column";
 212              }
 213  
 214              // Now force the migration of text data to new optimised storage.
 215              $sql = "UPDATE {{$table}} SET ".implode(', ', $updates);
 216              $DB->execute($sql);
 217          }
 218  
 219          $pbar->update($i, $tablecount, "Converted NTEXT to NVARCHAR(MAX) columns in MS SQL Server database - $i/$tablecount.");
 220      }
 221  }
 222  
 223  /**
 224   * Migrate IMAGE to VARBINARY(MAX).
 225   */
 226  function upgrade_mssql_varbinarymax() {
 227      global $DB;
 228  
 229      if ($DB->get_dbfamily() !== 'mssql') {
 230          return;
 231      }
 232  
 233      $pbar = new progress_bar('mssqlconvertimage', 500, true);
 234  
 235      $prefix = $DB->get_prefix();
 236      $tables = $DB->get_tables(false);
 237  
 238      $tablecount = count($tables);
 239      $i = 0;
 240      foreach ($tables as $table) {
 241          $i++;
 242  
 243          $columns = array();
 244  
 245          $sql = "SELECT column_name
 246                    FROM INFORMATION_SCHEMA.COLUMNS
 247                   WHERE table_name = '{{$table}}' AND UPPER(data_type) = 'IMAGE'";
 248          $rs = $DB->get_recordset_sql($sql);
 249          foreach ($rs as $column) {
 250              $columns[] = $column->column_name;
 251          }
 252          $rs->close();
 253  
 254          if ($columns) {
 255              // Set appropriate timeout - 1 minute per thousand of records should be enough, min 60 minutes just in case.
 256              $count = $DB->count_records($table, array());
 257              $timeout = ($count/1000)*60;
 258              $timeout = ($timeout < 60*60) ? 60*60 : (int)$timeout;
 259              upgrade_set_timeout($timeout);
 260  
 261              foreach ($columns as $column) {
 262                  // Change the definition.
 263                  $sql = "ALTER TABLE {$prefix}$table ALTER COLUMN $column VARBINARY(MAX)";
 264                  $DB->change_database_structure($sql);
 265              }
 266  
 267              // Binary columns should not be used, do not waste time optimising the storage.
 268          }
 269  
 270          $pbar->update($i, $tablecount, "Converted IMAGE to VARBINARY(MAX) columns in MS SQL Server database - $i/$tablecount.");
 271      }
 272  }
 273  
 274  /**
 275   * This upgrade script fixes the mismatches between DB fields course_modules.section
 276   * and course_sections.sequence. It makes sure that each module is included
 277   * in the sequence of at least one section.
 278   * Note that this script is different from admin/cli/fix_course_sortorder.php
 279   * in the following ways:
 280   * 1. It does not fix the cases when module appears several times in section(s) sequence(s) -
 281   *    it will be done automatically on the next viewing of the course.
 282   * 2. It does not remove non-existing modules from section sequences - administrator
 283   *    has to run the CLI script to do it.
 284   * 3. When this script finds an orphaned module it adds it to the section but makes hidden
 285   *    where CLI script does not change the visiblity specified in the course_modules table.
 286   */
 287  function upgrade_course_modules_sequences() {
 288      global $DB;
 289  
 290      // Find all modules that point to the section which does not point back to this module.
 291      $sequenceconcat = $DB->sql_concat("','", "s.sequence", "','");
 292      $moduleconcat = $DB->sql_concat("'%,'", "m.id", "',%'");
 293      $sql = "SELECT m.id, m.course, m.section, s.sequence
 294          FROM {course_modules} m LEFT OUTER JOIN {course_sections} s
 295          ON m.course = s.course and m.section = s.id
 296          WHERE s.sequence IS NULL OR ($sequenceconcat NOT LIKE $moduleconcat)
 297          ORDER BY m.course";
 298      $rs = $DB->get_recordset_sql($sql);
 299      $sections = null;
 300      foreach ($rs as $cm) {
 301          if (!isset($sections[$cm->course])) {
 302              // Retrieve all sections for the course (only once for each corrupt course).
 303              $sections = array($cm->course =>
 304                      $DB->get_records('course_sections', array('course' => $cm->course),
 305                              'section', 'id, section, sequence, visible'));
 306              if (empty($sections[$cm->course])) {
 307                  // Very odd - the course has a module in it but has no sections. Create 0-section.
 308                  $newsection = array('sequence' => '', 'section' => 0, 'visible' => 1);
 309                  $newsection['id'] = $DB->insert_record('course_sections',
 310                          $newsection + array('course' => $cm->course, 'summary' => '', 'summaryformat' => FORMAT_HTML));
 311                  $sections[$cm->course] = array($newsection['id'] => (object)$newsection);
 312              }
 313          }
 314          // Attempt to find the section that has this module in it's sequence.
 315          // If there are several of them, pick the last because this is what get_fast_modinfo() does.
 316          $sectionid = null;
 317          foreach ($sections[$cm->course] as $section) {
 318              if (!empty($section->sequence) && in_array($cm->id, preg_split('/,/', $section->sequence))) {
 319                  $sectionid = $section->id;
 320              }
 321          }
 322          if ($sectionid) {
 323              // Found the section. Update course_module to point to the correct section.
 324              $params = array('id' => $cm->id, 'section' => $sectionid);
 325              if (!$sections[$cm->course][$sectionid]->visible) {
 326                  $params['visible'] = 0;
 327              }
 328              $DB->update_record('course_modules', $params);
 329          } else {
 330              // No section in the course has this module in it's sequence.
 331              if (isset($sections[$cm->course][$cm->section])) {
 332                  // Try to add module to the section it points to (if it is valid).
 333                  $sectionid = $cm->section;
 334              } else {
 335                  // Section not found. Just add to the first available section.
 336                  reset($sections[$cm->course]);
 337                  $sectionid = key($sections[$cm->course]);
 338              }
 339              $newsequence = ltrim($sections[$cm->course][$sectionid]->sequence . ',' . $cm->id, ',');
 340              $sections[$cm->course][$sectionid]->sequence = $newsequence;
 341              $DB->update_record('course_sections', array('id' => $sectionid, 'sequence' => $newsequence));
 342              // Make module invisible because it was not displayed at all before this upgrade script.
 343              $DB->update_record('course_modules', array('id' => $cm->id, 'section' => $sectionid, 'visible' => 0, 'visibleold' => 0));
 344          }
 345      }
 346      $rs->close();
 347      unset($sections);
 348  
 349      // Note that we don't need to reset course cache here because it is reset automatically after upgrade.
 350  }
 351  
 352  /**
 353   * Updates a single item (course module or course section) to transfer the
 354   * availability settings from the old to the new format.
 355   *
 356   * Note: We do not convert groupmembersonly for modules at present. If we did,
 357   * $groupmembersonly would be set to the groupmembersonly option for the
 358   * module. Since we don't, it will be set to 0 for modules, and 1 for sections
 359   * if they have a grouping.
 360   *
 361   * @param int $groupmembersonly 1 if activity has groupmembersonly option
 362   * @param int $groupingid Grouping id (0 = none)
 363   * @param int $availablefrom Available from time (0 = none)
 364   * @param int $availableuntil Available until time (0 = none)
 365   * @param int $showavailability Show availability (1) or hide activity entirely
 366   * @param array $availrecs Records from course_modules/sections_availability
 367   * @param array $fieldrecs Records from course_modules/sections_avail_fields
 368   */
 369  function upgrade_availability_item($groupmembersonly, $groupingid,
 370          $availablefrom, $availableuntil, $showavailability,
 371          array $availrecs, array $fieldrecs) {
 372      global $CFG, $DB;
 373      $conditions = array();
 374      $shows = array();
 375  
 376      // Group members only condition (if enabled).
 377      if ($CFG->enablegroupmembersonly && $groupmembersonly) {
 378          if ($groupingid) {
 379              $conditions[] = '{"type":"grouping"' .
 380                      ($groupingid ? ',"id":' . $groupingid : '') . '}';
 381          } else {
 382              // No grouping specified, so allow any group.
 383              $conditions[] = '{"type":"group"}';
 384          }
 385          // Group members only condition was not displayed to students.
 386          $shows[] = 'false';
 387  
 388          // In the unlikely event that the site had enablegroupmembers only
 389          // but NOT enableavailability, we need to turn this on now.
 390          if (!$CFG->enableavailability) {
 391              set_config('enableavailability', 1);
 392          }
 393      }
 394  
 395      // Date conditions.
 396      if ($availablefrom) {
 397          $conditions[] = '{"type":"date","d":">=","t":' . $availablefrom . '}';
 398          $shows[] = $showavailability ? 'true' : 'false';
 399      }
 400      if ($availableuntil) {
 401          $conditions[] = '{"type":"date","d":"<","t":' . $availableuntil . '}';
 402          // Until dates never showed to students.
 403          $shows[] = 'false';
 404      }
 405  
 406      // Conditions from _availability table.
 407      foreach ($availrecs as $rec) {
 408          if (!empty($rec->sourcecmid)) {
 409              // Completion condition.
 410              $conditions[] = '{"type":"completion","cm":' . $rec->sourcecmid .
 411                      ',"e":' . $rec->requiredcompletion . '}';
 412          } else {
 413              // Grade condition.
 414              $minmax = '';
 415              if (!empty($rec->grademin)) {
 416                  $minmax .= ',"min":' . sprintf('%.5f', $rec->grademin);
 417              }
 418              if (!empty($rec->grademax)) {
 419                  $minmax .= ',"max":' . sprintf('%.5f', $rec->grademax);
 420              }
 421              $conditions[] = '{"type":"grade","id":' . $rec->gradeitemid . $minmax . '}';
 422          }
 423          $shows[] = $showavailability ? 'true' : 'false';
 424      }
 425  
 426      // Conditions from _fields table.
 427      foreach ($fieldrecs as $rec) {
 428          if (isset($rec->userfield)) {
 429              // Standard field.
 430              $fieldbit = ',"sf":' . json_encode($rec->userfield);
 431          } else {
 432              // Custom field.
 433              $fieldbit = ',"cf":' . json_encode($rec->shortname);
 434          }
 435          // Value is not included for certain operators.
 436          switch($rec->operator) {
 437              case 'isempty':
 438              case 'isnotempty':
 439                  $valuebit = '';
 440                  break;
 441  
 442              default:
 443                  $valuebit = ',"v":' . json_encode($rec->value);
 444                  break;
 445          }
 446          $conditions[] = '{"type":"profile","op":"' . $rec->operator . '"' .
 447                  $fieldbit . $valuebit . '}';
 448          $shows[] = $showavailability ? 'true' : 'false';
 449      }
 450  
 451      // If there are some conditions, set them into database.
 452      if ($conditions) {
 453          return '{"op":"&","showc":[' . implode(',', $shows) . '],' .
 454                  '"c":[' . implode(',', $conditions) . ']}';
 455      } else {
 456          return null;
 457      }
 458  }
 459  
 460  /**
 461   * Using data for a single course-module that has groupmembersonly enabled,
 462   * returns the new availability value that incorporates the correct
 463   * groupmembersonly option.
 464   *
 465   * Included as a function so that it can be shared between upgrade and restore,
 466   * and unit-tested.
 467   *
 468   * @param int $groupingid Grouping id for the course-module (0 if none)
 469   * @param string $availability Availability JSON data for the module (null if none)
 470   * @return string New value for availability for the module
 471   */
 472  function upgrade_group_members_only($groupingid, $availability) {
 473      // Work out the new JSON object representing this option.
 474      if ($groupingid) {
 475          // Require specific grouping.
 476          $condition = (object)array('type' => 'grouping', 'id' => (int)$groupingid);
 477      } else {
 478          // No grouping specified, so require membership of any group.
 479          $condition = (object)array('type' => 'group');
 480      }
 481  
 482      if (is_null($availability)) {
 483          // If there are no conditions using the new API then just set it.
 484          $tree = (object)array('op' => '&', 'c' => array($condition), 'showc' => array(false));
 485      } else {
 486          // There are existing conditions.
 487          $tree = json_decode($availability);
 488          switch ($tree->op) {
 489              case '&' :
 490                  // For & conditions we can just add this one.
 491                  $tree->c[] = $condition;
 492                  $tree->showc[] = false;
 493                  break;
 494              case '!|' :
 495                  // For 'not or' conditions we can add this one
 496                  // but negated.
 497                  $tree->c[] = (object)array('op' => '!&', 'c' => array($condition));
 498                  $tree->showc[] = false;
 499                  break;
 500              default:
 501                  // For the other two (OR and NOT AND) we have to add
 502                  // an extra level to the tree.
 503                  $tree = (object)array('op' => '&', 'c' => array($tree, $condition),
 504                          'showc' => array($tree->show, false));
 505                  // Inner trees do not have a show option, so remove it.
 506                  unset($tree->c[0]->show);
 507                  break;
 508          }
 509      }
 510  
 511      return json_encode($tree);
 512  }
 513  
 514  /**
 515   * Updates the mime-types for files that exist in the database, based on their
 516   * file extension.
 517   *
 518   * @param array $filetypes Array with file extension as the key, and mimetype as the value
 519   */
 520  function upgrade_mimetypes($filetypes) {
 521      global $DB;
 522      $select = $DB->sql_like('filename', '?', false);
 523      foreach ($filetypes as $extension=>$mimetype) {
 524          $DB->set_field_select(
 525              'files',
 526              'mimetype',
 527              $mimetype,
 528              $select,
 529              array($extension)
 530          );
 531      }
 532  }