Merge branch 'wip-MDL-47052-master' of git://github.com/marinaglancy/moodle
[moodle.git] / lib / db / upgradelib.php
CommitLineData
117bd748 1<?php
5b4a78e2
PS
2// This file is part of Moodle - http://moodle.org/
3//
4// Moodle is free software: you can redistribute it and/or modify
5// it under the terms of the GNU General Public License as published by
6// the Free Software Foundation, either version 3 of the License, or
7// (at your option) any later version.
8//
9// Moodle is distributed in the hope that it will be useful,
10// but WITHOUT ANY WARRANTY; without even the implied warranty of
11// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12// GNU General Public License for more details.
13//
14// You should have received a copy of the GNU General Public License
15// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
16
17/**
18 * Upgrade helper functions
19 *
42ff9ce6 20 * This file is used for special upgrade functions - for example groups and gradebook.
56a1a882 21 * These functions must use SQL and database related functions only- no other Moodle API,
42ff9ce6 22 * because it might depend on db structures that are not yet present during upgrade.
23 * (Do not use functions from accesslib.php, grades classes or group functions at all!)
5b4a78e2 24 *
39b90b51
EL
25 * @package core_install
26 * @category upgrade
27 * @copyright 2007 Petr Skoda (http://skodak.org)
28 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
42ff9ce6 29 */
30
5b4a78e2
PS
31defined('MOODLE_INTERNAL') || die();
32
3182d885
PS
33/**
34 * Returns all non-view and non-temp tables with sane names.
35 * Prints list of non-supported tables using $OUTPUT->notification()
36 *
37 * @return array
38 */
39function upgrade_mysql_get_supported_tables() {
40 global $OUTPUT, $DB;
41
42 $tables = array();
43 $patprefix = str_replace('_', '\\_', $DB->get_prefix());
44 $pregprefix = preg_quote($DB->get_prefix(), '/');
45
46 $sql = "SHOW FULL TABLES LIKE '$patprefix%'";
47 $rs = $DB->get_recordset_sql($sql);
48 foreach ($rs as $record) {
49 $record = array_change_key_case((array)$record, CASE_LOWER);
50 $type = $record['table_type'];
51 unset($record['table_type']);
52 $fullname = array_shift($record);
53
54 if ($pregprefix === '') {
55 $name = $fullname;
56 } else {
57 $count = null;
58 $name = preg_replace("/^$pregprefix/", '', $fullname, -1, $count);
59 if ($count !== 1) {
60 continue;
61 }
62 }
63
64 if (!preg_match("/^[a-z][a-z0-9_]*$/", $name)) {
65 echo $OUTPUT->notification("Database table with invalid name '$fullname' detected, skipping.", 'notifyproblem');
66 continue;
67 }
68 if ($type === 'VIEW') {
69 echo $OUTPUT->notification("Unsupported database table view '$fullname' detected, skipping.", 'notifyproblem');
70 continue;
71 }
72 $tables[$name] = $name;
73 }
74 $rs->close();
75
76 return $tables;
77}
7385cc00
PS
78
79/**
801ae0f4
PS
80 * Remove all signed numbers from current database and change
81 * text fields to long texts - mysql only.
7385cc00 82 */
801ae0f4
PS
83function 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.
7385cc00 86
801ae0f4
PS
87 // If anybody already has numbers higher than signed limit the execution stops
88 // and tables must be fixed manually before continuing upgrade.
7385cc00
PS
89
90 global $DB;
91
92 if ($DB->get_dbfamily() !== 'mysql') {
93 return;
94 }
95
801ae0f4 96 $pbar = new progress_bar('mysqlconvertunsignedlobs', 500, true);
7385cc00
PS
97
98 $prefix = $DB->get_prefix();
3182d885 99 $tables = upgrade_mysql_get_supported_tables();
7385cc00
PS
100
101 $tablecount = count($tables);
102 $i = 0;
103 foreach ($tables as $table) {
104 $i++;
7385cc00 105
801ae0f4
PS
106 $changes = array();
107
7385cc00
PS
108 $sql = "SHOW COLUMNS FROM `{{$table}}`";
109 $rs = $DB->get_recordset_sql($sql);
110 foreach ($rs as $column) {
7385cc00
PS
111 $column = (object)array_change_key_case((array)$column, CASE_LOWER);
112 if (stripos($column->type, 'unsigned') !== false) {
72263b8f
PS
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 }
7385cc00
PS
130 $type = preg_replace('/unsigned/i', 'signed', $column->type);
131 $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
e427ad30 132 $default = (!is_null($column->default) and $column->default !== '') ? "DEFAULT '$column->default'" : '';
7385cc00 133 $autoinc = (stripos($column->extra, 'auto_increment') !== false) ? 'AUTO_INCREMENT' : '';
801ae0f4 134 // Primary and unique not necessary here, change_database_structure does not add prefix.
801ae0f4 135 $changes[] = "MODIFY COLUMN `$column->field` $type $notnull $default $autoinc";
436650b0 136
801ae0f4 137 } else if ($column->type === 'tinytext' or $column->type === 'mediumtext' or $column->type === 'text') {
436650b0 138 $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
e427ad30 139 $default = (!is_null($column->default) and $column->default !== '') ? "DEFAULT '$column->default'" : '';
801ae0f4
PS
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') {
436650b0 144 $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
e427ad30 145 $default = (!is_null($column->default) and $column->default !== '') ? "DEFAULT '$column->default'" : '';
801ae0f4
PS
146 // Primary, unique and inc are not supported for blobs.
147 $changes[] = "MODIFY COLUMN `$column->field` LONGBLOB $notnull $default";
436650b0 148 }
801ae0f4 149
436650b0
PS
150 }
151 $rs->close();
152
801ae0f4 153 if ($changes) {
57160c25
PS
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
801ae0f4
PS
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.");
436650b0
PS
165 }
166}
656250de
PS
167
168/**
169 * Migrate NTEXT to NVARCHAR(MAX).
170 */
171function 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 */
226function 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}
47644f7d
MG
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 */
287function 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.
8e97006a 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 */
369function 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}
b5725a97
JF
459
460/**
461 * Updates the mime-types for files that exist in the database, based on their
462 * file extension.
463 *
464 * @param array $filetypes Array with file extension as the key, and mimetype as the value
465 */
466function upgrade_mimetypes($filetypes) {
467 global $DB;
468 $select = $DB->sql_like('filename', '?', false);
469 foreach ($filetypes as $extension=>$mimetype) {
470 $DB->set_field_select(
471 'files',
472 'mimetype',
473 $mimetype,
474 $select,
475 array($extension)
476 );
477 }
478}