Database UTF-8 conversion
Also available in Deutsch[link1] and Русский[link2]
Guide
- migrate all code and data in a single shot
- downtime needed
- trial runs needed
1. Steps
1.1. Pre-Upgrade
- Convert all tables from MyISAM to InnoDB if you have existing MyISAM tables
- Admin Panel -> Database -> Convert
- create a backup[link3] of your current wiki (database & files)
- create a local installation (or remote - to avoid timeouts and script terminations)
- import the backup -> 5.5.17 (ca. 15 minutes)
- run the conversion script[link4] as Admin (-> see action
{{admin_utf8}}below)- Alters table
file,page,page_linkandrevisionto work without key prefixes longer than 767 bytes (optional)- MySQL versions prior to 5.7.7 or MariaDB 10.2.2 do not support index key prefixes up to 3072 bytes by default.
- If you have the possibility to upgrade your database to meet the requirements for the large key prefix, you are advised to do so.
- Converts your database and tables to
utf8mb4 - Converts cross charset records (e.g. Greek saved as Latin1)
- This is only necessary if you have run your wiki in multilanguage mode and pages with a different charset have been created.
- Alters table
- CHECK via phpMyAdmin or Adminer all relevant columns if the conversion was successful before you continue!
- E.g. the scripts may fail to convert the
bodycolumn due timeout or script termination.
- E.g. the scripts may fail to convert the
1.2. Upgrade to R6.0
- switch repo from 5.5 to 6.0 branch (keep! config.php) OR remove the old version and replace it with R6.0.x
- empty all data in
_cache/folder - upgrade via installer to 6.0.x
1.3. Post-Upgrade
- run the post-upgrade conversion scripts
{{admin_utf8}}- Reset up-sized TEXT columns back to TEXT or MEDIUMTEXT
- Convert HTML entities to their corresponding Unicode characters
- Remove column
convertedfrom tables
- Remove the action admin_utf8.php again from the
action/folder - synchronize the upgraded data via the Admin Panel
<-- back to Upgrade notices for 6.0.x[link5]
2. Script
ln -s ../../community/action/admin_utf8.php admin_utf8.php
admin_utf8.php[link4]
<?php if (!defined('IN_WACKO')) { exit; } ########################################################## ## WackoWiki UTF-8 Conversion Routines ## ########################################################## /* * status: stable * https://wackowiki.org/doc/Dev/Release/R6.0/Upgrade/DatabaseConversion * modify the script for your needs, please contribute your improvements * * place the script under action/admin_utf8.php * call the action via {{admin_utf8}} as Administrator * * 1. Pre-Upgrade Routines for R6.x * 1.0. Alter tables to work without key prefixes longer than 767 bytes * 1.1. Convert all tables based on charset to utf8mb4 * 1.2. Convert all cross charset records * 2. Post-Upgrade Routines for R6.x * 2.1. Reset upsized TEXT columns back to TEXT or MEDIUMTEXT * 2.2. Convert HTML entities to their corresponding Unicode characters * 2.3. Remove column 'converted' from tables */ /* TODO: * set progress in config * add check to analyzed the database prior to show the suggested actions * currently it uses only the Mysqli API to update the cross charset records */ $prefix = $this->db->table_prefix; $charset = 'utf8mb4'; $collation = 'utf8mb4_unicode_520_ci'; // Unicode (UCA 5.2.0), case-insensitive, https://dev.mysql.com/doc/refman/8.0/en/charset-collation-names.html // get MariaDB / MySQL version $_db_version = $this->db->load_single("SELECT version()"); $db_version = $_db_version['version()']; $large_prefix = false; echo '<h1>Unicode conversion utilities</h1>'; $info[] = ['WackoWiki version', $this->format('**!!(green)' . $this->db->wacko_version . '!!**', 'wacko')]; $info[] = ['MariaDB / MySQL version', $db_version]; $info[] = ['Database charset', $this->db->database_charset]; $info[] = ['Database collation', $this->db->database_collation]; echo '<table style="max-width:800px; border-spacing: 1px; border-collapse: separate; padding: 4px;" class="formation lined">' . "\n"; foreach ($info as $value) { echo '<tr>' . '<td class="label"><strong>' . $value[0] . '</strong></td>' . '<td> </td>' . '<td>' . $value[1] . '</td>' . "\n"; } echo '</table>' . "\n"; echo '<h2>1. Pre-Upgrade Routines for R6.x</h2>'; if ($this->is_admin()) { // MySQL versions prior to 5.7.7 or MariaDB 10.2.2 do not support index key prefixes up to 3072 bytes by default. $min_db_version = preg_match('/MariaDB/', $db_version, $matches) ? '10.2.2' : '5.7.7'; if (version_compare($db_version, $min_db_version , '>=')) { $large_prefix = true; } if (! $large_prefix) { echo '<h3>1.0. Alter tables to work without key prefixes longer than 767 bytes:</h3>'; if (!isset($_POST['set_large_prefix_tables'])) { echo $this->form_open('large_prefix'); echo '<button type="submit" name="set_large_prefix_tables">' . $this->_t('UpdateButton') . '</button>'; echo $this->form_close(); } else if (isset($_POST['set_large_prefix_tables'])) { $results = '<strong>' . date('H:i:s') . ' - ' . 'Alter tables started' . "\n" . '================================================</strong>' . "\n"; $results .= '<strong>' . 'VARCHAR(191): ' . "\n" . 'Tables: file, page, page_link and revision:</strong>' . "\n\n"; $this->db->sql_query(" ALTER TABLE {$prefix}file CHANGE file_name file_name VARCHAR(191) NOT NULL DEFAULT '';"); $this->db->sql_query(" ALTER TABLE {$prefix}page CHANGE title title VARCHAR(191) NOT NULL DEFAULT '', CHANGE tag tag VARCHAR(191) BINARY NOT NULL DEFAULT '';"); $this->db->sql_query(" ALTER TABLE {$prefix}page_link CHANGE to_tag to_tag VARCHAR(191) BINARY NOT NULL DEFAULT '';"); $this->db->sql_query(" ALTER TABLE {$prefix}revision CHANGE title title VARCHAR(191) NOT NULL DEFAULT '', CHANGE tag tag VARCHAR(191) BINARY NOT NULL DEFAULT '';"); $results .= '<strong>' . date('H:i:s') . ' - ' . 'Tables altered.' . "\n" . '================================================</strong>' . "\n"; echo '<div class="code">' . '<pre>' . $results . '</pre>' . '</div><br>'; } } ######################################################## ## Convert charset for tables ## ######################################################## echo '<h3>1.1. Convert database and tables based on charset to ' . $charset . ':</h3>'; if (!isset($_POST['set_charset_tables'])) { echo $this->form_open('charset_tables'); echo '<button type="submit" name="set_charset_tables">' . $this->_t('UpdateButton') . '</button>'; echo $this->form_close(); } else if (isset($_POST['set_charset_tables'])) { $tables = [ 'acl', 'auth_token', 'cache', 'category', 'category_assignment', 'config', 'external_link', 'file', 'file_link', 'log', 'menu', 'page', 'page_link', 'poll', 'rating', 'referrer', 'revision', 'user', 'usergroup', 'usergroup_member', 'user_setting', 'watch', 'word', ]; $results = '<strong>' . date('H:i:s') . ' - ' . 'Table conversion started' . "\n" . '================================================</strong>' . "\n"; $results .= '<strong>' . 'Charset: ' . $charset . "\n" . 'Collation: ' . ' ' . $collation . ':</strong>' . "\n\n"; // Database $this->db->sql_query("ALTER DATABASE {$this->db->database_database} CHARACTER SET = {$charset} COLLATE = '{$collation}';"); // Tables foreach ($tables as $table) { $this->db->sql_query("ALTER TABLE " . $prefix . $table . " DEFAULT CHARACTER SET {$charset} COLLATE {$collation};"); $this->db->sql_query("ALTER TABLE " . $prefix . $table . " CONVERT TO CHARACTER SET {$charset} COLLATE {$collation};"); // B: convert records in a different charset -> see next routine // C: HTML entities -> see post upgrade routine // TODO: convert mojibake // TODO: deal with old supertag links $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . $table."\n" . "\t" . '------------------------------------------------</strong>' . "\n"; } $results .= '<strong>' . date('H:i:s') . ' - ' . 'Tables converted.' . "\n" . '================================================</strong>' . "\n"; echo '<div class="code">' . '<pre>' . $results . '</pre>' . '</div><br>'; } ######################################################## ## Convert strings of cross charset records ## ######################################################## if ($this->db->database_charset != 'utf8mb4') { echo '<h3>1.2. Convert all cross charset records</h3>'; if (!isset($_POST['set_charset_record'])) { echo $this->form_open('charset_records'); echo '<button type="submit" name="set_charset_record">' . $this->_t('UpdateButton') . '</button>'; echo $this->form_close(); } else if (isset($_POST['set_charset_record'])) { # set_time_limit(3600); // iso-8859-1 -> windows-1252 $_charset = [ 'bg' => 'windows-1251', 'da' => 'windows-1252', 'de' => 'windows-1252', 'el' => 'iso-8859-7', 'en' => 'windows-1252', 'es' => 'windows-1252', 'et' => 'windows-1257', 'fr' => 'windows-1252', 'hu' => 'iso-8859-2', 'it' => 'windows-1252', 'nl' => 'windows-1252', 'pl' => 'iso-8859-2', 'pt' => 'windows-1252', 'ru' => 'windows-1251', ]; // selects the subset of languages to convert according your 'database_charset' if ($this->db->database_charset == 'latin1') // Latin1 (iso-8859-1) { $_lang_set = "'bg', 'el', 'et', 'hu', 'pl', 'ru'"; } else if ($this->db->database_charset == 'latin2') // Latin2 (iso-8859-2) { $_lang_set = "'bg', 'da', 'de', 'el', 'en', 'es', 'et', 'fr', 'it', 'nl', 'pt', 'ru'"; } else if ($this->db->database_charset == 'cp1251') // Kyrillic (windows-1251) { $_lang_set = "'da', 'de', 'el', 'en', 'es', 'et', 'fr', 'hu', 'it', 'nl', 'pl', 'pt'"; } else if ($this->db->database_charset == 'cp1257') // Baltic (windows-1257) { $_lang_set = "'bg', 'da', 'de', 'el', 'en', 'es', 'fr', 'hu', 'it', 'nl', 'pl', 'pt', 'ru'"; } else if ($this->db->database_charset == 'greek') // Greek (iso-8859-7) { $_lang_set = "'bg', 'da', 'de', 'en', 'es', 'et', 'fr', 'hu', 'it', 'nl', 'pl', 'pt', 'ru'"; } /* Tables: sets value 1 in converted record to avoid double conversion * 1 - utf8 * 2 - HTML entities * 3 - mojibake */ $tables = [ 'category', 'file', 'menu', 'page', 'revision', 'user', 'usergroup', ]; // add field 'converted' foreach ($tables as $table) { $this->db->sql_query("ALTER TABLE " . $prefix . $table . " ADD converted TINYINT(1) UNSIGNED NOT NULL DEFAULT '0';"); } ######################################################################################################################### // DATABASE utf8mb4 link $sql_modes = !empty($this->db->sql_mode_strict) ? SQL_MODE_STRICT : SQL_MODE_PERMISSIVE; $dblink = mysqli_connect($this->db->database_host, $this->db->database_user, $this->db->database_password, null, $this->db->database_port); mysqli_select_db($dblink, $this->db->database_database); mysqli_set_charset($dblink, $charset); mysqli_query($dblink, "SET SESSION sql_mode='$sql_modes'"); ######################################################################################################################### $results = '<strong>' . date('H:i:s') . ' - ' . 'Tables record conversion started ' . "\n" . '================================================</strong>' . "\n"; ## 1 .CATEGORIES ## if ($categories = $this->db->load_all( "SELECT category_id, category, category_description, category_lang " . "FROM {$prefix}category " . "WHERE " . "category_lang IN (" . $_lang_set . ") " . "AND converted <> 1 ") ) { $total = 0; $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'category' . "\n" . "\t" . '------------------------------------------------</strong>' . "\n"; foreach($categories as $category) { $category_name = iconv($_charset[$category['category_lang']], 'UTF-8', $category['category']); $category_description = iconv($_charset[$category['category_lang']], 'UTF-8', $category['category_description']); #echo $category_name . '<br>'; // update category mysqli_query($dblink, "UPDATE {$prefix}category SET " . "category = " . $this->db->q($category_name) . ", " . "category_description = " . $this->db->q($category_description) . ", " . "converted = 1 " . "WHERE category_id = " . (int) $category['category_id'] . " " . "LIMIT 1"); # echo mysqli_error($dblink) . '<br>'; $total++; } $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'Records converted' . ': ' . $total . '</strong>' . "\n\n\n"; } ## 2. FILES ## if ($files = $this->db->load_all( "SELECT file_id, file_name, file_description, caption, file_lang " . "FROM {$prefix}file " . "WHERE " . "file_lang IN (" . $_lang_set . ") " . "AND converted <> 1 ") ) { $total = 0; $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'file' . "\n" . "\t" . '------------------------------------------------</strong>' . "\n"; foreach($files as $file) { $file_description = iconv($_charset[$file['file_lang']], 'UTF-8', $file['file_description']); $caption = iconv($_charset[$file['file_lang']], 'UTF-8', $file['caption']); # echo $file['file_name'] . '<br>'; // update file meta data mysqli_query($dblink, "UPDATE {$prefix}file SET " . "file_description = " . $this->db->q($file_description) . ", " . "caption = " . $this->db->q($caption) . ", " . "converted = 1 " . "WHERE file_id = " . (int) $file['file_id'] . " " . "LIMIT 1"); # echo mysqli_error($dblink) . '<br>'; $total++; } $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'Records converted' . ': ' . $total . '</strong>' . "\n\n\n"; } ## 3. MENU ## if ($items = $this->db->load_all( "SELECT menu_id, menu_title, menu_lang " . "FROM {$prefix}menu " . "WHERE " . "menu_lang IN (" . $_lang_set . ") " . "AND converted <> 1 ") ) { $total = 0; $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'menu' . "\n" . "\t" . '------------------------------------------------</strong>' . "\n"; foreach($items as $item) { $menu_title = iconv($_charset[$item['menu_lang']], 'UTF-8', $item['menu_title']); # echo $menu_title . '<br>'; // update menu title mysqli_query($dblink, "UPDATE {$prefix}menu SET " . "menu_title = " . $this->db->q($menu_title) . ", " . "converted = 1 " . "WHERE menu_id = " . (int) $item['menu_id'] . " " . "LIMIT 1"); # echo mysqli_error($dblink) . '<br>'; $total++; } $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'Records converted' . ': ' . $total . '</strong>' . "\n\n\n"; } ## 4. PAGES ## if ($pages = $this->db->load_all( "SELECT page_id, tag, title, body, edit_note, description, keywords, page_lang " . "FROM {$prefix}page " . "WHERE " . "page_lang IN (" . $_lang_set . ") " . "AND converted <> 1 ") ) { $total = 0; $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'page' . "\n" . "\t" . '------------------------------------------------</strong>' . "\n"; foreach($pages as $page) { $tag = iconv($_charset[$page['page_lang']], 'UTF-8', $page['tag']); $title = iconv($_charset[$page['page_lang']], 'UTF-8', $page['title']); $body = iconv($_charset[$page['page_lang']], 'UTF-8', $page['body']); $edit_note = iconv($_charset[$page['page_lang']], 'UTF-8', $page['edit_note']); $description = iconv($_charset[$page['page_lang']], 'UTF-8', $page['description']); $keywords = iconv($_charset[$page['page_lang']], 'UTF-8', $page['keywords']); # echo $tag . '<br>'; // update current page copy mysqli_query($dblink, "UPDATE {$prefix}page SET " . "tag = " . $this->db->q($tag) . ", " . "title = " . $this->db->q($title) . ", " . "body = " . $this->db->q($body) . ", " . "edit_note = " . $this->db->q($edit_note) . ", " . "description = " . $this->db->q($description) . ", " . "keywords = " . $this->db->q($keywords) . ", " . "converted = 1 " . "WHERE page_id = " . (int) $page['page_id'] . " " . "LIMIT 1"); # echo mysqli_error($dblink) . '<br>'; $total++; } $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'Records converted' . ': ' . $total . '</strong>' . "\n\n\n"; } ## 5. REVISIONS ## if ($revisions = $this->db->load_all( "SELECT revision_id, tag, title, body, edit_note, description, keywords, page_lang " . "FROM {$prefix}revision " . "WHERE " . "page_lang IN (" . $_lang_set . ") " . "AND converted <> 1 ") ) { $total = 0; $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'revision' . "\n" . "\t" . '------------------------------------------------</strong>' . "\n"; foreach($revisions as $revision) { $tag = iconv($_charset[$revision['page_lang']], 'UTF-8', $revision['tag']); $title = iconv($_charset[$revision['page_lang']], 'UTF-8', $revision['title']); $body = iconv($_charset[$revision['page_lang']], 'UTF-8', $revision['body']); $edit_note = iconv($_charset[$revision['page_lang']], 'UTF-8', $revision['edit_note']); $description = iconv($_charset[$revision['page_lang']], 'UTF-8', $revision['description']); $keywords = iconv($_charset[$revision['page_lang']], 'UTF-8', $revision['keywords']); # echo $revision['revision_id'] . ': ' . $tag . '<br>'; // update revision mysqli_query($dblink, "UPDATE {$prefix}revision SET " . "tag = " . $this->db->q($tag) . ", " . "title = " . $this->db->q($title) . ", " . "body = " . $this->db->q($body) . ", " . "edit_note = " . $this->db->q($edit_note) . ", " . "description = " . $this->db->q($description) . ", " . "keywords = " . $this->db->q($keywords) . ", " . "converted = 1 " . "WHERE revision_id = " . (int) $revision['revision_id'] . " " . "LIMIT 1"); # echo mysqli_error($dblink) . '<br>'; $total++; } $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'Records converted' . ': ' . $total . '</strong>' . "\n\n\n"; } ## 6. USERS ## if ($users = $this->db->load_all( "SELECT user_id, user_name, real_name, account_lang " . "FROM {$prefix}user " . "WHERE " . "account_lang IN (" . $_lang_set . ") " . "AND converted <> 1 ") ) { $total = 0; $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'user' . "\n" . "\t" . '------------------------------------------------</strong>' . "\n"; foreach($users as $user) { $user_name = iconv($_charset[$user['account_lang']], 'UTF-8', $user['user_name']); $real_name = iconv($_charset[$user['account_lang']], 'UTF-8', $user['real_name']); # echo $user_name . '<br>'; // update user mysqli_query($dblink, "UPDATE {$prefix}user SET " . "user_name = " . $this->db->q($user_name) . ", " . "real_name = " . $this->db->q($real_name) . ", " . "converted = 1 " . "WHERE user_id = " . (int) $user['user_id'] . " " . "LIMIT 1"); # echo mysqli_error($dblink) . '<br>'; $total++; } $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'Records converted' . ': ' . $total . '</strong>' . "\n\n\n"; } ## 7. USERGROUPS ## if ($usergroups = $this->db->load_all( "SELECT group_id, group_name, description, group_lang " . "FROM {$prefix}usergroup " . "WHERE " . "group_lang IN (" . $_lang_set . ") " . "AND converted <> 1 ") ) { $total = 0; $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'usergroup' . "\n" . "\t" . '------------------------------------------------</strong>' . "\n"; foreach($usergroups as $group) { $group_name = iconv($_charset[$group['group_lang']], 'UTF-8', $group['group_name']); $description = iconv($_charset[$group['group_lang']], 'UTF-8', $group['description']); # echo $group_name . '<br>'; // update user group mysqli_query($dblink, "UPDATE {$prefix}usergroup SET " . "group_name = " . $this->db->q($group_name) . ", " . "description = " . $this->db->q($description) . ", " . "converted = 1 " . "WHERE group_id = " . (int) $group['group_id'] . " " . "LIMIT 1"); # echo mysqli_error($dblink) . '<br>'; $total++; } $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'Records converted' . ': ' . $total . '</strong>' . "\n\n\n"; } $results .= '<strong>' . date('H:i:s') . ' - ' . 'Tables records converted.' . "\n" . '================================================</strong>' . "\n"; echo '<div class="code">' . '<pre>' . $results . '</pre>' . '</div><br>'; } } echo '<h2>2. Post-Upgrade Routines for R6.x</h2>'; echo '<h3>2.1. Reset upsized TEXT columns back to TEXT or MEDIUMTEXT:</h3>'; if (!isset($_POST['reset_text_column'])) { echo $this->form_open('reset_text'); echo '<button type="submit" name="reset_text_column">' . $this->_t('UpdateButton') . '</button>'; echo $this->form_close(); } else if (isset($_POST['reset_text_column'])) { /* * tables having converted to utf8mb4 auto upsized the TEXT columns to avoid insufficient storage * TEXT -> MEDIUMTEXT -> LONGTEXT * most of these columns store Latin1 strings and do not require that storage size, set them back to default size * LONGTEXT -> MEDIUMTEXT -> TEXT */ $sql = [ "ALTER TABLE {$prefix}acl CHANGE list list TEXT COLLATE {$collation} NOT NULL AFTER privilege;", "ALTER TABLE {$prefix}config CHANGE config_value config_value TEXT COLLATE {$collation} NULL AFTER config_name;", "ALTER TABLE {$prefix}external_link CHANGE link link TEXT COLLATE {$collation} NOT NULL AFTER page_id;", "ALTER TABLE {$prefix}file CHANGE caption caption TEXT COLLATE {$collation} NOT NULL AFTER file_description;", "ALTER TABLE {$prefix}log CHANGE message message TEXT COLLATE {$collation} NOT NULL AFTER ip;", "ALTER TABLE {$prefix}page CHANGE body body MEDIUMTEXT COLLATE {$collation} NOT NULL AFTER modified, CHANGE body_r body_r MEDIUMTEXT COLLATE {$collation} NOT NULL AFTER body, CHANGE body_toc body_toc TEXT COLLATE {$collation} NOT NULL AFTER body_r;", "ALTER TABLE {$prefix}referrer CHANGE user_agent user_agent TEXT COLLATE {$collation} NOT NULL AFTER ip;", "ALTER TABLE {$prefix}revision CHANGE body body MEDIUMTEXT COLLATE {$collation} NOT NULL AFTER modified, CHANGE body_r body_r MEDIUMTEXT COLLATE {$collation} NOT NULL AFTER body;", ]; $results = '<strong>' . date('H:i:s') . ' - ' . 'Started conversion of TEXT columns' . "\n" . '================================================</strong>' . "\n"; // reset TEXT columns foreach ($sql as $query) { $this->db->sql_query($query); } $results .= '<strong>' . date('H:i:s') . ' - ' . 'Reset converted TEXT columns' . "\n" . '================================================</strong>' . "\n"; echo '<div class="code">' . '<pre>' . $results . '</pre>' . '</div><br>'; } if (version_compare($this->db->wacko_version, '6.0.beta1' , '>=')) { echo '<h3>2.2. Convert HTML entities to their corresponding Unicode characters:</h3>'; if (!isset($_POST['convert_html_entities'])) { echo $this->form_open('html_entities'); echo '<button type="submit" name="convert_html_entities">' . $this->_t('UpdateButton') . '</button>'; echo $this->form_close(); } else if (isset($_POST['convert_html_entities'])) { # set_time_limit(3600); $convert_entities = function($input) { return preg_replace_callback('/(&#[0-9]+;)/', function($m) { return mb_convert_encoding($m[1], 'UTF-8', 'HTML-ENTITIES'); }, $input); }; /* Tables: sets value 2 in converted record to avoid double conversion * 1 - utf8 * 2 - HTML entities * 3 - mojibake */ $tables = [ 'file', 'page', 'revision', ]; // add field 'converted' foreach ($tables as $table) { $this->db->sql_query("ALTER TABLE " . $prefix . $table . " ADD converted TINYINT(1) UNSIGNED NOT NULL DEFAULT '0';"); } $results = '<strong>' . date('H:i:s') . ' - ' . 'Tables record conversion started ' . "\n" . '================================================</strong>' . "\n"; ## 1. FILES ## if ($files = $this->db->load_all( "SELECT file_id, file_name, file_description, caption " . "FROM {$prefix}file " . "WHERE converted <> 2 ") ) { $total = 0; $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'file' . "\n" . "\t" . '------------------------------------------------</strong>' . "\n"; foreach($files as $file) { $file_description = $convert_entities($file['file_description']); $caption = $convert_entities($file['caption']); # echo $file['file_name'] . '<br>'; // update file meta data $this->db->sql_query( "UPDATE {$prefix}file SET " . "file_description = " . $this->db->q($file_description) . ", " . "caption = " . $this->db->q($caption) . ", " . "converted = 2 " . "WHERE file_id = " . (int) $file['file_id'] . " " . "LIMIT 1"); $total++; } $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'Records converted' . ': ' . $total . '</strong>' . "\n\n\n"; } ## 2. PAGES ## if ($pages = $this->db->load_all( "SELECT page_id, title, body, edit_note, description, keywords " . "FROM {$prefix}page " . "WHERE converted <> 2 ") ) { $total = 0; $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'page' . "\n" . "\t" . '------------------------------------------------</strong>' . "\n"; foreach($pages as $page) { $title = $convert_entities($page['title']); $body = $convert_entities($page['body']); $edit_note = $convert_entities($page['edit_note']); $description = $convert_entities($page['description']); $keywords = $convert_entities($page['keywords']); # echo $tag . '<br>'; // update current page copy $this->db->sql_query( "UPDATE {$prefix}page SET " . "title = " . $this->db->q($title) . ", " . "body = " . $this->db->q($body) . ", " . "edit_note = " . $this->db->q($edit_note) . ", " . "description = " . $this->db->q($description) . ", " . "keywords = " . $this->db->q($keywords) . ", " . "converted = 2 " . "WHERE page_id = " . (int) $page['page_id'] . " " . "LIMIT 1"); $total++; } $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'Records converted' . ': ' . $total . '</strong>' . "\n\n\n"; } ## 3. REVISIONS ## if ($revisions = $this->db->load_all( "SELECT revision_id, title, body, edit_note, description, keywords " . "FROM {$prefix}revision " . "WHERE converted <> 2 ") ) { $total = 0; $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'revision' . "\n" . "\t" . '------------------------------------------------</strong>' . "\n"; foreach($revisions as $revision) { $title = $convert_entities($revision['title']); $body = $convert_entities($revision['body']); $edit_note = $convert_entities($revision['edit_note']); $description = $convert_entities($revision['description']); $keywords = $convert_entities($revision['keywords']); # echo $revision['revision_id'] . ': ' . $tag . '<br>'; // update revision $this->db->sql_query( "UPDATE {$prefix}revision SET " . "title = " . $this->db->q($title) . ", " . "body = " . $this->db->q($body) . ", " . "edit_note = " . $this->db->q($edit_note) . ", " . "description = " . $this->db->q($description) . ", " . "keywords = " . $this->db->q($keywords) . ", " . "converted = 2 " . "WHERE revision_id = " . (int) $revision['revision_id'] . " " . "LIMIT 1"); $total++; } $results .= "\t" . '<strong>' . date('H:i:s') . ' - ' . 'Records converted' . ': ' . $total . '</strong>' . "\n\n\n"; } $results .= '<strong>' . date('H:i:s') . ' - ' . 'Tables records converted.' . "\n" . '================================================</strong>' . "\n"; echo '<div class="code">' . '<pre>' . $results . '</pre>' . '</div><br>'; } echo '<h3>2.3. Remove column \'converted\' from tables:</h3>'; if (!isset($_POST['remove_converted_column'])) { echo $this->form_open('converted_column'); echo '<button type="submit" name="remove_converted_column">' . $this->_t('UpdateButton') . '</button>'; echo $this->form_close(); } else if (isset($_POST['remove_converted_column'])) { // tables having 'converted' column to avoid double conversion $tables = [ 'category', 'file', 'menu', 'page', 'revision', 'user', 'usergroup', ]; // drop field 'converted' foreach ($tables as $table) { $this->db->sql_query("ALTER TABLE " . $prefix . $table . " DROP converted;"); } $results = '<strong>' . date('H:i:s') . ' - ' . 'Droped column \'converted\' from tables' . "\n" . '================================================</strong>' . "\n"; echo '<div class="code">' . '<pre>' . $results . '</pre>' . '</div><br>'; } } }
3. ToDo
- comments with another charset (page [ru] -> comment in Russian [en]) -> Mojibake
- the comment was not converted because the user writing the comments was logged in with English as user profile language and therefore the comment was saved with [en] as comment language, since the page set the encoding and that was not an issue but it is NOW
-
SELECT page_id, tag, title, body FROM prefix_page;
- improve process for regular users, add better description and add localized versions in German and Russian.
- add check to analyze the database prior to show the suggested actions
- currently it uses only the MySQL API to update the cross charset records
3.1. Localization
['en'] 'Utf8Utilities' => 'Unicode conversion utilities', 'Utf8PreUpgrade' => 'Pre-Upgrade Routines for R6.x', 'Utf8KeyPrefixes' => 'Alter tables to work without key prefixes longer than 767 bytes', 'Utf8AlterTablesStarted' => 'Alter tables started', 'Utf8Tables' => 'Tables: file, page, page_link and revision', 'Utf8ConvertDatabase' => 'Convert database and tables based on charset to', 'Utf8TableConversionStarted' => 'Table conversion started', 'Utf8TablesConverted' => 'Tables converted.', 'Utf8ConvertCrossCharset' => 'Convert all cross charset records', 'Utf8TablesConversionStarted' => 'Tables record conversion started', 'Utf8RecordsConverted' => 'Records converted', 'Utf8TablesRecordsConverted' => 'Tables records converted.', 'Utf8PostUpgrade' => 'Post-Upgrade Routines for R6.x', 'Utf8RemoveColumnConverted' => 'Remove column \'converted\' from tables', 'Utf8DropedColumnConverted' => 'Droped column \'converted\' from tables', 'Utf8ResetTextColumns' => 'Reset up-sized TEXT columns back to TEXT or MEDIUMTEXT', 'Utf8StartedTextConversion' => 'Started conversion of TEXT columns', 'Utf8ResetTextColumns' => 'Reset converted TEXT columns', 'Utf8ConvertHtmlEntities' => 'Convert HTML entities to their corresponding Unicode characters',
['de'] 'Utf8Utilities' => 'Funktionen zur Unicode-Konvertierung', 'Utf8PreUpgrade' => 'Pre-Upgrade-Routinen für R6.x', 'Utf8KeyPrefixes' => 'Änderung der Tabellen, damit sie ohne Schlüsselpräfixe funktionieren, die länger als 767 Bytes sind.', 'Utf8AlterTablesStarted' => 'Ändern von Tabellen gestartet', 'Utf8Tables' => 'Tabllen: file, page, page_link und revision', 'Utf8ConvertDatabase' => 'Konvertiert Datenbank und Tabellen basierend auf dem Zeichensatz nach', 'Utf8TableConversionStarted' => 'Konvertierung der Tabelle gestartet', 'Utf8TablesConverted' => 'Tabellen konvertiert.', 'Utf8ConvertCrossCharset' => 'Konvertiert alle Zeichensatz-übergreifenden Datensätze.', 'Utf8TablesConversionStarted' => 'Konvertierung der Datensätze in den Tabellen gestartet', 'Utf8RecordsConverted' => 'Datensätze konvertiert', 'Utf8TablesRecordsConverted' => 'Datensätze in den Tabellen konvertiert.', 'Utf8PostUpgrade' => 'Post-Upgrade-Routinen für R6.x', 'Utf8RemoveColumnConverted' => 'Löscht die Spalte \'converted\' aus den Tabellen.', 'Utf8DropedColumnConverted' => 'Die Spalte \'converted\' wurde aus den Tabellen entfernt.', 'Utf8ResetTextColumns' => 'Zurücksetzen der vergrößerten TEXT-Spalten auf TEXT oder MEDIUMTEXT', 'Utf8StartedTextConversion' => 'Konvertierung der TEXT-Spalten gestartet', 'Utf8ResetTextColumns' => 'Zurücksetzen der konvertierten TEXT-Spalten', 'Utf8ConvertHtmlEntities' => 'Konvertierung von HTML-Entitäten in die entsprechenden Unicode-Zeichen',
- [link1] https://wackowiki.org/doc/Dev/Release/R6.0/Upgrade/DatabaseConversion/de
- [link2] https://wackowiki.org/doc/Dev/Release/R6.0/Upgrade/DatabaseConversion/ru
- [link3] https://wackowiki.org/doc/Doc/English/Backup
- [link4] https://bitbucket.org/wackowiki/wackowiki/src/6.0/community/action/admin_utf8.php
- [link5] https://wackowiki.org/doc/Dev/Release/R6.0/Upgrade