Commit | Line | Data |
---|---|---|
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 |
31 | defined('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 | */ | |
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 | } | |
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 |
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. | |
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 | */ | |
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 | } | |
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 | */ | |
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. | |
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 | */ | |
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 | } |