install/convert_database_charset.php

   1 <?php
   2 /*=====================================================================*\
   3 || ###################################################################
   4 || # Bugdar
   5 || # Copyright ©2002-2007 Blue Static
   6 || #
   7 || # This program is free software; you can redistribute it and/or modify
   8 || # it under the terms of the GNU General Public License as published by
   9 || # the Free Software Foundation; version 2 of the License.
  10 || #
  11 || # This program is distributed in the hope that it will be useful, but
  12 || # WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
  13 || # or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
  14 || # more details.
  15 || #
  16 || # You should have received a copy of the GNU General Public License along
  17 || # with this program; if not, write to the Free Software Foundation, Inc.,
  18 || # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
  19 || ###################################################################
  20 \*=====================================================================*/
  21
  22 define('SVN', '$Id$');
  23
  24 chdir('../admin/');
  25 require_once('./global.php');
  26
  27 define('ISSO_PRINTER_NO_NAVIGATION', 1);
  28
  29 // you can change what you want to conver to here
  30 define('TARGET', 'utf8_general_ci');
  31
  32 $input->inputClean('step', TYPE_UINT);
  33
  34 // columns to convert per table
  35 $columnConversions = array();
  36
  37 // primary keys that are text
  38 $primaryKeys = array();
  39
  40 // fulltext
  41 $fulltext = array();
  42
  43 // ###################################################################
  44
  45 function PrintContinue($step)
  46 {
  47         global $admin;
  48
  49         $admin->form_start('convert_database_charset.php', 'convert');
  50         $admin->form_hidden_field('step', $step);
  51         echo "\n<input type=\"submit\" value=\"  Continue &#8594;  \" name=\"__submit__\" />";
  52         $admin->form_end();
  53 }
  54
  55 $admin->page_start('Convert Database Character Set');
  56
  57 // ###################################################################
  58
  59 if ($input->in['step'] == 0)
  60 {
  61         $collation = $db->queryFirst("SHOW VARIABLES LIKE 'collation_database'");
  62
  63         echo '<h1>Convert Database Character Set</h1>';
  64         echo '<p>Earlier versions of Bugdar did not check or enforce the MySQL database character set or collation. It is recommended that the database be set at <strong>' . TARGET . '</strong>. This script can migrate all of data from your current character set/collation of <strong>' . $collation['Value'] . '</strong>. If you would like to convert your database to utf8, please click the link below.</p>';
  65
  66         echo '<h3>Please back up your database before continuing! While this script is tested and should be safe, it is better to be cautious.</h3>';
  67
  68         PrintContinue(1);
  69 }
  70
  71 // ###################################################################
  72
  73 else if ($input->in['step'] == 1)
  74 {
  75         echo '<h1>Preserve Existing Data</h1>';
  76         echo '<p>This converts all the table columns that are stored with a character set to a BLOB type to ensure there are no problems with the target character set.</p>';
  77
  78         $admin->table_start();
  79         $admin->table_head('Column Preservation');
  80         $admin->table_column_head(array('Table', 'Column'));
  81
  82         // get rid of indices that are strings
  83         $db->showerrors = false;
  84         $db->query("ALTER TABLE " . TABLE_PREFIX . "bug DROP INDEX summary");
  85         $db->query("ALTER TABLE " . TABLE_PREFIX . "comment DROP INDEX comment");
  86         $db->query("ALTER TABLE " . TABLE_PREFIX . "language DROP INDEX languagecode");
  87         $db->query("ALTER TABLE " . TABLE_PREFIX . "language DROP INDEX langcode");
  88         $db->showerrors = true;
  89
  90         $tables = $db->query("SHOW TABLES");
  91         while ($table = $db->fetch_array($tables, false))
  92         {
  93                 $columns = $db->query("SHOW FULL COLUMNS FROM $table[0]");
  94                 foreach ($columns as $col)
  95                 {
  96                         if (!is_null($col['Collation']) AND (strpos($col['Type'], 'char') !== false OR strpos($col['Type'], 'text') !== false))
  97                         {
  98                                 $columnConversions[$table[0]][$col['Field']] = $col['Type'];
  99                                 if ($col['Key'] == 'PRI')
 100                                 {
 101                                         $db->query("ALTER TABLE $table[0] DROP PRIMARY KEY");
 102                                         $primaryKeys[$table[0]] = $col['Field'];
 103                                 }
 104                                 $db->query("ALTER TABLE $table[0] MODIFY $col[Field] BLOB");
 105                                 $admin->row_text($table[0], $col['Field']);
 106                         }
 107                 }
 108         }
 109
 110         $admin->table_end();
 111
 112         echo '<h1>Convert Database Character Set</h1>';
 113         echo '<p>This step changes the database\'s character set.</p>';
 114
 115         require './includes/config.php';
 116         $db->query("ALTER DATABASE $database COLLATE " . TARGET);
 117
 118         echo '<h1>Convert Table Character Set</h1>';
 119         echo '<p>This converts each table\'s character set and collation.</p>';
 120
 121         $admin->table_start();
 122         $admin->table_head('Table Conversion');
 123         $admin->table_column_head(array('Table Name', 'Result'));
 124
 125         $tables = $db->query("SHOW TABLES");
 126         while ($table = $db->fetch_array($tables, false))
 127         {
 128                 $db->query("ALTER TABLE $table[0] COLLATE " . TARGET);
 129                 $admin->row_text($table[0], 'Good');
 130         }
 131
 132         $admin->table_end();
 133
 134         echo '<h1>Convert Table Columns</h1>';
 135         echo '<p>This converts all the table columns that are stored with a character set.</p>';
 136
 137         $admin->table_start();
 138         $admin->table_head('Column Conversion');
 139         $admin->table_column_head(array('Table', 'Column'));
 140
 141         foreach ($columnConversions AS $table => $columns)
 142         {
 143                 foreach ($columns AS $column => $type)
 144                 {
 145                         $db->query("ALTER TABLE $table MODIFY $column $type COLLATE " . TARGET);
 146                         $admin->row_text($table, $column);
 147                 }
 148         }
 149
 150         foreach ($primaryKeys AS $table => $field)
 151         {
 152                 $db->query("ALTER TABLE $table ADD PRIMARY KEY ($field)");
 153         }
 154
 155         $db->query("ALTER TABLE " . TABLE_PREFIX . "bug ADD FULLTEXT(summary)");
 156         $db->query("ALTER TABLE " . TABLE_PREFIX . "comment ADD FULLTEXT(comment)");
 157         $db->query("ALTER TABLE " . TABLE_PREFIX . "language ADD UNIQUE(langcode)");
 158
 159         $admin->table_end();
 160
 161         PrintContinue(2);
 162 }
 163
 164 // ###################################################################
 165
 166 else if ($input->in['step'] == 2)
 167 {
 168         echo '<h1>Conversion Complete</h1>';
 169         echo '<p>Your database has now been converted to the <strong>' . TARGET . '</strong> collation. Congratulations. Please check that everything is operating correctly.</p>';
 170 }
 171
 172 // ###################################################################
 173
 174 $admin->page_end();
 175
 176 /*=====================================================================*\
 177 || ###################################################################
 178 || # $HeadURL$
 179 || # $Id$
 180 || ###################################################################
 181 \*=====================================================================*/
 182 ?>