admin/cli/mysql_collation.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  * MySQL collation conversion tool.
  19  *
  20  * @package    core
  21  * @copyright  2012 Petr Skoda (http://skodak.org)
  22  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23  */
  24
  25 define('CLI_SCRIPT', true);
  26
  27 require(__DIR__.'/../../config.php');
  28 require_once($CFG->libdir.'/clilib.php');      // cli only functions
  29
  30 if ($DB->get_dbfamily() !== 'mysql') {
  31     cli_error('This function is designed for MySQL databases only!');
  32 }
  33
  34 // now get cli options
  35 list($options, $unrecognized) = cli_get_params(array('help'=>false, 'list'=>false, 'collation'=>false, 'available'=>false),
  36     array('h'=>'help', 'l'=>'list', 'a'=>'available'));
  37
  38 if ($unrecognized) {
  39     $unrecognized = implode("\n  ", $unrecognized);
  40     cli_error(get_string('cliunknowoption', 'admin', $unrecognized));
  41 }
  42
  43 $help =
  44     "MySQL collation conversions script.
  45
  46 It is strongly recommended to stop the web server before the conversion.
  47 This script may be executed before the main upgrade - 1.9.x data for example.
  48
  49 Options:
  50 --collation=COLLATION Convert MySQL tables to different collation
  51 -l, --list            Show table and column information
  52 -a, --available       Show list of available collations
  53 -h, --help            Print out this help
  54
  55 Example:
  56 \$ sudo -u www-data /usr/bin/php admin/cli/mysql_collation.php --collation=utf8_general_ci
  57 ";
  58
  59 if (!empty($options['collation'])) {
  60     $collations = mysql_get_collations();
  61     $collation = clean_param($options['collation'], PARAM_ALPHANUMEXT);
  62     $collation = strtolower($collation);
  63     if (!isset($collations[$collation])) {
  64         cli_error("Error: collation '$collation' is not available on this server!");
  65     }
  66
  67     echo "Converting tables and columns to '$collation' for $CFG->wwwroot:\n";
  68     $prefix = $DB->get_prefix();
  69     $prefix = str_replace('_', '\\_', $prefix);
  70     $sql = "SHOW TABLE STATUS WHERE Name LIKE BINARY '$prefix%'";
  71     $rs = $DB->get_recordset_sql($sql);
  72     $converted = 0;
  73     $skipped   = 0;
  74     $errors    = 0;
  75     foreach ($rs as $table) {
  76         echo str_pad($table->name, 40). " - ";
  77
  78         if ($table->collation === $collation) {
  79             echo "NO CHANGE\n";
  80             $skipped++;
  81
  82         } else {
  83             $DB->change_database_structure("ALTER TABLE $table->name DEFAULT COLLATE = $collation");
  84             echo "CONVERTED\n";
  85             $converted++;
  86         }
  87
  88         $sql = "SHOW FULL COLUMNS FROM $table->name WHERE collation IS NOT NULL";
  89         $rs2 = $DB->get_recordset_sql($sql);
  90         foreach ($rs2 as $column) {
  91             $column = (object)array_change_key_case((array)$column, CASE_LOWER);
  92             echo '    '.str_pad($column->field, 36). " - ";
  93             if ($column->collation === $collation) {
  94                 echo "NO CHANGE\n";
  95                 $skipped++;
  96                 continue;
  97             }
  98
  99             if ($column->type === 'tinytext' or $column->type === 'mediumtext' or $column->type === 'text' or $column->type === 'longtext') {
 100                 $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
 101                 $default = (!is_null($column->default) and $column->default !== '') ? "DEFAULT '$column->default'" : '';
 102                 // primary, unique and inc are not supported for texts
 103                 $sql = "ALTER TABLE $table->name MODIFY COLUMN $column->field $column->type COLLATE $collation $notnull $default";
 104                 $DB->change_database_structure($sql);
 105
 106             } else if (strpos($column->type, 'varchar') === 0) {
 107                 $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
 108                 $default = !is_null($column->default) ? "DEFAULT '$column->default'" : '';
 109                 // primary, unique and inc are not supported for texts
 110                 $sql = "ALTER TABLE $table->name MODIFY COLUMN $column->field $column->type COLLATE $collation $notnull $default";
 111                 $DB->change_database_structure($sql);
 112             } else {
 113                 echo "ERROR (unknown column type: $column->type)\n";
 114                 $error++;
 115                 continue;
 116             }
 117             echo "CONVERTED\n";
 118             $converted++;
 119         }
 120         $rs2->close();
 121     }
 122     $rs->close();
 123     echo "Converted: $converted, skipped: $skipped, errors: $errors\n";
 124     exit(0); // success
 125
 126 } else if (!empty($options['list'])) {
 127     echo "List of tables for $CFG->wwwroot:\n";
 128     $prefix = $DB->get_prefix();
 129     $prefix = str_replace('_', '\\_', $prefix);
 130     $sql = "SHOW TABLE STATUS WHERE Name LIKE BINARY '$prefix%'";
 131     $rs = $DB->get_recordset_sql($sql);
 132     $counts = array();
 133     foreach ($rs as $table) {
 134         if (isset($counts[$table->collation])) {
 135             $counts[$table->collation]++;
 136         } else {
 137             $counts[$table->collation] = 1;
 138         }
 139         echo str_pad($table->name, 40);
 140         echo $table->collation.  "\n";
 141         $collations = mysql_get_column_collations($table->name);
 142         foreach ($collations as $columname=>$collation) {
 143             if (isset($counts[$collation])) {
 144                 $counts[$collation]++;
 145             } else {
 146                 $counts[$collation] = 1;
 147             }
 148             echo '    ';
 149             echo str_pad($columname, 36);
 150             echo $collation.  "\n";
 151         }
 152     }
 153     $rs->close();
 154
 155     echo "\n";
 156     echo "Table collations summary for $CFG->wwwroot:\n";
 157     foreach ($counts as $collation => $count) {
 158         echo "$collation: $count\n";
 159     }
 160     exit(0); // success
 161
 162 } else if (!empty($options['available'])) {
 163     echo "List of available MySQL collations for $CFG->wwwroot:\n";
 164     $collations = mysql_get_collations();
 165     foreach ($collations as $collation) {
 166         echo " $collation\n";
 167     }
 168     die;
 169
 170 } else {
 171     echo $help;
 172     die;
 173 }
 174
 175
 176
 177 // ========== Some functions ==============
 178
 179 function mysql_get_collations() {
 180     global $DB;
 181
 182     $collations = array();
 183     $sql = "SHOW COLLATION WHERE Collation LIKE 'utf8\_%' AND Charset = 'utf8'";
 184     $rs = $DB->get_recordset_sql($sql);
 185     foreach ($rs as $collation) {
 186         $collations[$collation->collation] = $collation->collation;
 187     }
 188     $rs->close();
 189
 190     $collation = $DB->get_dbcollation();
 191     if (isset($collations[$collation])) {
 192         $collations[$collation] .= ' (default)';
 193     }
 194
 195     return $collations;
 196 }
 197
 198 function mysql_get_column_collations($tablename) {
 199     global $DB;
 200
 201     $collations = array();
 202     $sql = "SELECT column_name, collation_name
 203               FROM INFORMATION_SCHEMA.COLUMNS
 204              WHERE table_schema = DATABASE() AND table_name = ? AND collation_name IS NOT NULL";
 205     $rs = $DB->get_recordset_sql($sql, array($tablename));
 206     foreach($rs as $record) {
 207         $collations[$record->column_name] = $record->collation_name;
 208     }
 209     $rs->close();
 210     return $collations;
 211 }