inputClean('step', TYPE_UINT); // columns to convert per table $columnConversions = array(); // primary keys that are text $primaryKeys = array(); // fulltext $fulltext = array(); // ################################################################### function PrintContinue($step) { global $admin; $admin->form_start('convert_database_charset.php', 'convert'); $admin->form_hidden_field('step', $step); echo "\n"; $admin->form_end(); } $admin->page_start('Convert Database Character Set'); // ################################################################### if ($input->in['step'] == 0) { $collation = $db->queryFirst("SHOW VARIABLES LIKE 'collation_database'"); echo '

Convert Database Character Set

'; echo '

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 ' . TARGET . '. This script can migrate all of data from your current character set/collation of ' . $collation['Value'] . '. If you would like to convert your database to utf8, please click the link below.

'; echo '

Please back up your database before continuing! While this script is tested and should be safe, it is better to be cautious.

'; PrintContinue(1); } // ################################################################### else if ($input->in['step'] == 1) { echo '

Preserve Existing Data

'; echo '

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.

'; $admin->table_start(); $admin->table_head('Column Preservation'); $admin->table_column_head(array('Table', 'Column')); // get rid of indices that are strings $db->showerrors = false; $db->query("ALTER TABLE " . TABLE_PREFIX . "bug DROP INDEX summary"); $db->query("ALTER TABLE " . TABLE_PREFIX . "comment DROP INDEX comment"); $db->query("ALTER TABLE " . TABLE_PREFIX . "language DROP INDEX languagecode"); $db->query("ALTER TABLE " . TABLE_PREFIX . "language DROP INDEX langcode"); $db->showerrors = true; $tables = $db->query("SHOW TABLES"); while ($table = $db->fetch_array($tables, false)) { $columns = $db->query("SHOW FULL COLUMNS FROM $table[0]"); foreach ($columns as $col) { if (!is_null($col['Collation']) AND (strpos($col['Type'], 'char') !== false OR strpos($col['Type'], 'text') !== false)) { $columnConversions[$table[0]][$col['Field']] = $col['Type']; if ($col['Key'] == 'PRI') { $db->query("ALTER TABLE $table[0] DROP PRIMARY KEY"); $primaryKeys[$table[0]] = $col['Field']; } $db->query("ALTER TABLE $table[0] MODIFY $col[Field] BLOB"); $admin->row_text($table[0], $col['Field']); } } } $admin->table_end(); echo '

Convert Database Character Set

'; echo '

This step changes the database\'s character set.

'; require './includes/config.php'; $db->query("ALTER DATABASE $database COLLATE " . TARGET); echo '

Convert Table Character Set

'; echo '

This converts each table\'s character set and collation.

'; $admin->table_start(); $admin->table_head('Table Conversion'); $admin->table_column_head(array('Table Name', 'Result')); $tables = $db->query("SHOW TABLES"); while ($table = $db->fetch_array($tables, false)) { $db->query("ALTER TABLE $table[0] COLLATE " . TARGET); $admin->row_text($table[0], 'Good'); } $admin->table_end(); echo '

Convert Table Columns

'; echo '

This converts all the table columns that are stored with a character set.

'; $admin->table_start(); $admin->table_head('Column Conversion'); $admin->table_column_head(array('Table', 'Column')); foreach ($columnConversions AS $table => $columns) { foreach ($columns AS $column => $type) { $db->query("ALTER TABLE $table MODIFY $column $type COLLATE " . TARGET); $admin->row_text($table, $column); } } foreach ($primaryKeys AS $table => $field) { $db->query("ALTER TABLE $table ADD PRIMARY KEY ($field)"); } $db->query("ALTER TABLE " . TABLE_PREFIX . "bug ADD FULLTEXT(summary)"); $db->query("ALTER TABLE " . TABLE_PREFIX . "comment ADD FULLTEXT(comment)"); $db->query("ALTER TABLE " . TABLE_PREFIX . "language ADD UNIQUE(langcode)"); $admin->table_end(); PrintContinue(2); } // ################################################################### else if ($input->in['step'] == 2) { echo '

Conversion Complete

'; echo '

Your database has now been converted to the ' . TARGET . ' collation. Congratulations. Please check that everything is operating correctly.

'; } // ################################################################### $admin->page_end(); ?>