MDL-36493 improve performance of mysql unsigned/lob upgrade
[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++;
801ae0f4 105 // Set appropriate timeout - 1 minute per thousand of records should be enough, min 60 minutes just in case.
7385cc00 106 $count = $DB->count_records($table, array());
801ae0f4 107 $timeout = ($count/1000)*60;
7385cc00
PS
108 $timeout = ($timeout < 60*60) ? 60*60 : (int)$timeout;
109
801ae0f4
PS
110 $changes = array();
111
7385cc00
PS
112 $sql = "SHOW COLUMNS FROM `{{$table}}`";
113 $rs = $DB->get_recordset_sql($sql);
114 foreach ($rs as $column) {
115 upgrade_set_timeout($timeout);
116
117 $column = (object)array_change_key_case((array)$column, CASE_LOWER);
118 if (stripos($column->type, 'unsigned') !== false) {
72263b8f
PS
119 $maxvalue = 0;
120 if (preg_match('/^int/i', $column->type)) {
121 $maxvalue = 2147483647;
122 } else if (preg_match('/^medium/i', $column->type)) {
123 $maxvalue = 8388607;
124 } else if (preg_match('/^smallint/i', $column->type)) {
125 $maxvalue = 32767;
126 } else if (preg_match('/^tinyint/i', $column->type)) {
127 $maxvalue = 127;
128 }
129 if ($maxvalue) {
130 // Make sure nobody is abusing our integer ranges - moodle int sizes are in digits, not bytes!!!
131 $invalidcount = $DB->get_field_sql("SELECT COUNT('x') FROM `{{$table}}` WHERE `$column->field` > :maxnumber", array('maxnumber'=>$maxvalue));
132 if ($invalidcount) {
133 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.");
134 }
135 }
7385cc00
PS
136 $type = preg_replace('/unsigned/i', 'signed', $column->type);
137 $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
e427ad30 138 $default = (!is_null($column->default) and $column->default !== '') ? "DEFAULT '$column->default'" : '';
7385cc00 139 $autoinc = (stripos($column->extra, 'auto_increment') !== false) ? 'AUTO_INCREMENT' : '';
801ae0f4 140 // Primary and unique not necessary here, change_database_structure does not add prefix.
436650b0 141
801ae0f4 142 $changes[] = "MODIFY COLUMN `$column->field` $type $notnull $default $autoinc";
436650b0 143
801ae0f4 144 } else if ($column->type === 'tinytext' or $column->type === 'mediumtext' or $column->type === 'text') {
436650b0 145 $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
e427ad30 146 $default = (!is_null($column->default) and $column->default !== '') ? "DEFAULT '$column->default'" : '';
801ae0f4
PS
147 // Primary, unique and inc are not supported for texts.
148 $changes[] = "MODIFY COLUMN `$column->field` LONGTEXT $notnull $default";
149
150 } else if ($column->type === 'tinyblob' or $column->type === 'mediumblob' or $column->type === 'blob') {
436650b0 151 $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
e427ad30 152 $default = (!is_null($column->default) and $column->default !== '') ? "DEFAULT '$column->default'" : '';
801ae0f4
PS
153 // Primary, unique and inc are not supported for blobs.
154 $changes[] = "MODIFY COLUMN `$column->field` LONGBLOB $notnull $default";
436650b0 155 }
801ae0f4 156
436650b0
PS
157 }
158 $rs->close();
159
801ae0f4
PS
160 if ($changes) {
161 $sql = "ALTER TABLE `{$prefix}$table` ".implode(', ', $changes);
162 $DB->change_database_structure($sql);
163 }
164
165 $pbar->update($i, $tablecount, "Converted unsigned/lob columns in MySQL database - $i/$tablecount.");
436650b0
PS
166 }
167}