[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   * 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  }