From 4f93f0224bfd7365589c36e5a9b95adcb14f4246 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Mon, 25 Jun 2012 12:56:07 +0200 Subject: [PATCH 1/1] Switching database to UTF8 encoding on upgrade to FA 2.4. --- admin/db/maintenance_db.inc | 148 +++++++++++++++++++++++++++++++----- admin/inst_upgrade.php | 10 ++- doc/api_changes.txt | 4 +- includes/current_user.inc | 24 ++++-- includes/db/connect_db.inc | 144 ++++++++++++++++++++++++++++++++--- includes/lang/language.php | 2 +- includes/session.inc | 10 ++- sql/alter2.4.php | 103 +++++++++++++++++++++++-- version.php | 2 +- 9 files changed, 394 insertions(+), 53 deletions(-) diff --git a/admin/db/maintenance_db.inc b/admin/db/maintenance_db.inc index ce8e5506..d2a72607 100644 --- a/admin/db/maintenance_db.inc +++ b/admin/db/maintenance_db.inc @@ -252,8 +252,16 @@ function write_lang() } return true; } - -function db_import($filename, $connection, $force=true) +/* + Database import: + $filename - sql file name + $connection - database connection + $force - ignore duplicate errors + $init - presume $filename is initialization file with '0_' prefix + $protect - protect users/roles + $return_errors - return errors instead of display them +*/ +function db_import($filename, $connection, $force=true, $init=true, $protect=false, $return_errors=false) { global $db, $go_debug, $sql_trail; @@ -265,10 +273,17 @@ function db_import($filename, $connection, $force=true) "alter table" => 'table_queries', "insert" => 'data_queries', "update" => 'data_queries', + "set names" => 'set_names', "drop table if exists" => 'drop_queries', "drop function if exists" => 'drop_queries', "drop trigger if exists" => 'drop_queries', ); + + $protected = array( + 'security_roles', + 'users' + ); + $ignored_mysql_errors = array( //errors ignored in normal (non forced) mode '1022', // duplicate key '1050', // Table %s already exists @@ -277,11 +292,15 @@ function db_import($filename, $connection, $force=true) '1062', // duplicate key entry '1091' // can't drop key/column check if exists ); + + $set_names = array(); $data_queries = array(); $drop_queries = array(); $table_queries = array(); $sql_errors = array(); + $old_encoding = mysql_client_encoding(); + ini_set("max_execution_time", "180"); db_query("SET foreign_key_checks=0"); // uncrompress gziped backup files @@ -295,11 +314,13 @@ function db_import($filename, $connection, $force=true) // parse input file $query_table = ''; $delimiter = ';'; + foreach($lines as $line_no => $line) { $line = trim($line); - $line = str_replace("0_", $connection["tbpref"], $line); + if ($init) + $line = str_replace("0_", $connection["tbpref"], $line); if ($query_table == '') { // check if line begins with one of allowed queries @@ -312,7 +333,17 @@ function db_import($filename, $connection, $force=true) continue 2; } $query_table = $table; - ${$query_table}[] = array('', $line_no+1); + $skip = false; + if ($protect) + { + foreach($protected as $protbl) + if (strpos($line, $connection["tbpref"].$protbl) !== false) + { + $skip = true; break; + } + } + if (!$skip) + ${$query_table}[] = array('', $line_no+1); break; } } @@ -326,20 +357,63 @@ function db_import($filename, $connection, $force=true) $line = substr($line, 0, strlen($line) - strlen($delimiter)); // strip delimiter $query_table = ''; } - ${$table}[count(${$table}) - 1][0] .= $line . "\n"; + if (!$skip) + ${$table}[count(${$table}) - 1][0] .= $line . "\n"; } - + } -/* - { // for debugging purposes + + // + // 'set names' or equivalents should be used only on post 2.3 FA versions + // otherwise text encoding can be broken during import + // + $encoding = null; // UI encoding for default site language is the default + $new_db = $init || db_fixed(); + $new_file = count($set_names); + if ($new_db) + { + if ($new_file) + { + if (count($set_names)) // standard db restore + { + if (preg_match('/set\s*names\s*[\']?(\w*)[\']?/i', $set_names[0][0], $match)) + $encoding = $match[1]; + } + // otherwise use default site ui encoding + } + } + else + { + if ($new_file) // import on old db is forbidden: this would destroy db content unless latin1 was used before in UI + { + $msg = _("This is new format backup file which cannot be restored on database not migrated to utf8."); + if ($return_errors) + return $msg; + else + display_error($msg); + return false; + } + else // backup restore during upgrade failure + $encoding = 'latin1'; // standard encoding on mysql client + } + + mysql_set_charset($encoding); + +/*/ { // for debugging purposes global $path_to_root; $f = fopen($path_to_root.'/tmp/dbimport.txt', 'w+'); + fwrite($f, print_r($set_names,true) ."\n"); fwrite($f, print_r($drop_queries,true) ."\n"); fwrite($f, print_r($table_queries,true) ."\n"); fwrite($f, print_r($data_queries,true)); fclose($f); } -*/ +/*/ + if ($return_errors) + { // prevent errors display + $save_debug = $go_debug; + $go_debug = 0; + } // execute drop tables if exists queries if (is_array($drop_queries)) { @@ -379,11 +453,19 @@ function db_import($filename, $connection, $force=true) } } } - + + if ($return_errors) + $go_debug = $save_debug; + db_query("SET foreign_key_checks=1"); if ($delimiter != ';') db_query("delimiter ;"); // just for any case + mysql_set_charset($old_encoding); // restore connection encoding + if (count($sql_errors)) { + if ($return_errors) + return $sql_errors; + // display first failure message; the rest are probably derivative $err = $sql_errors[0]; display_error(sprintf(_("SQL script execution failed in line %d: %s"), @@ -458,15 +540,14 @@ function db_backup($conn, $ext='no', $comm='', $path=null) return db_export($conn, $path . clean_file_name($filename), $ext, $comm); } - -// generates a dump of $db database -// $drop and $zip tell if to include the drop table statement or dry to pack -function db_export($conn, $filename, $zip='no', $comment='', $no_default=false) +// +// Generates a dump of $db database +// +function db_export($conn, $filename, $zip='no', $comment='') { global $app_title, $version, $power_url, $path_to_root; - $error = false; // set max string size before writing to file $max_size = 1048576 * 2; // 2 MB @@ -483,6 +564,9 @@ function db_export($conn, $filename, $zip='no', $comment='', $no_default=false) $backupfile = $filename; $company = $conn['name']; // get_company_pref('coy_name'); + if (file_exists($backupfile)) // prevent appends + unlink($backupfile); + //create comment $out="# MySQL dump of database '".$conn["dbname"]."' on host '".$conn["host"]."'\n"; $out.="# Backup Date and Time: ".date("Y-m-d H:i")."\n"; @@ -490,6 +574,7 @@ function db_export($conn, $filename, $zip='no', $comment='', $no_default=false) $out.="# ".$power_url."\n"; $out.="# Company: ". @html_entity_decode($company, ENT_QUOTES, $_SESSION['language']->encoding)."\n"; $out.="# User: ".$_SESSION["wa_current_user"]->name."\n\n"; + $out.="# Compatibility: ".get_company_pref('version_id')."\n\n"; // write users comment if ($comment) @@ -504,6 +589,12 @@ function db_export($conn, $filename, $zip='no', $comment='', $no_default=false) //$out.="use ".$db.";\n"; we don't use this option. + if (db_fixed()) + { + db_set_encoding(); + if ($mysql_enc = get_mysql_encoding_name($_SESSION['language']->encoding)) + $out .= "\nSET NAMES $mysql_enc;\n"; + } // get auto_increment values and names of all tables $res = db_query("show table status"); $all_tables = array(); @@ -553,8 +644,6 @@ function db_export($conn, $filename, $zip='no', $comment='', $no_default=false) $out.="### Structure of table `".$tablename."` ###\n\n"; $out.="DROP TABLE IF EXISTS `".$tablename."`;\n\n"; - if ($no_default && ($def_pos = strpos($table_sql[$tablename], "DEFAULT CHARSET")) > 0) - $table_sql[$tablename] = substr($table_sql[$tablename], 0, $def_pos); $out.=$table_sql[$tablename]; // add auto_increment value @@ -801,7 +890,7 @@ function save_to_file($path, $zip, $fileData) // total # of entries "on this disk", total # of entries overall, size of central dir, offset to start of central dir, .zip file comment length $fileData .= pack('v', 1) . pack('v', 1) . pack('V', strlen($cdrec)) . pack('V', strlen($fr)) . "\x00\x00"; - if ($zp = @fopen($path, "a")) + if ($zp = @fopen($path, "w")) { @fwrite($zp, $fileData); @fclose($zp); @@ -881,4 +970,27 @@ function check_table($pref, $table, $field=null, $properties=null) return 2; // field not found } +/* + Update or create setting in simple php config file. +*/ +function update_config_var($file, $variable, $value, $comment='') +{ + if (!is_file($file) || !is_writeable($file)) + return false; + $content = file_get_contents($file); + $strvalue = '$'."$variable = ".var_export($value, true).';'; + $pattern = '/'.preg_quote('$'.$variable).'\s*=\s*[^;]*;/m'; + $content = preg_replace($pattern, $strvalue, $content, -1, $result); + if (!$result) + { + $strvalue = ($comment ? "// $comment" : '') ."\n$strvalue\n"; + $content = preg_replace('/\?>\s*/m', $strvalue, $content, -1, $result); + if (!$result) + $content .= $strvalue; + } + + return file_put_contents($file, $content)!=false; +} + + ?> \ No newline at end of file diff --git a/admin/inst_upgrade.php b/admin/inst_upgrade.php index 4e7e05b4..e948d898 100644 --- a/admin/inst_upgrade.php +++ b/admin/inst_upgrade.php @@ -71,14 +71,16 @@ function upgrade_step($index, $company, $conn) error_log(sprintf(_("Database upgrade for company '%s' (%s:%s*) started..."), $conn['name'], $conn['dbname'], $conn['tbpref'])); - + if ($sql != '') - $ret &= db_import($path_to_root.'/sql/'.$sql, $conn, $force); + $ret &= db_import($path_to_root.'/sql/'.$sql, $conn, $force, true); $ret &= $inst->install($company, $force); - error_log(_("Database upgrade finished.")); + if (!$ret && is_callable(array($inst, 'post_fail'))) + $inst->post_fail($pref); + error_log(_("Database upgrade finished.")); } else if ($state!==true) { display_error(_("Upgrade cannot be done because database has been already partially upgraded. Please downgrade database to clean previous version or try forced upgrade.")); @@ -110,11 +112,13 @@ if (get_post('Upgrade')) { $ret = upgrade_step($i, $comp, $conn); if (!$ret) + { display_error( sprintf(_("Database upgrade to version %s failed for company '%s'."), $inst->version, $conn['name']) .'
' ._('You should restore company database from latest backup file')); + } } // db_close($conn); ? if (!$ret) break; diff --git a/doc/api_changes.txt b/doc/api_changes.txt index 6af93f47..e710b550 100644 --- a/doc/api_changes.txt +++ b/doc/api_changes.txt @@ -49,10 +49,10 @@ Description: Before 2.4: db_export($conn, $filename, $zip='no', $comment='', $tbpref = TB_PREF, $no_default=false) Now: - db_export($conn, $filename, $zip='no', $comment='', $no_default=false) + db_export($conn, $filename, $zip='no', $comment='') Description: $tbpref parameter has been removed. Calls should be done after switch to selected company database with - set_global_connection($company). + set_global_connection($company). Also removed obsolete $no_default parameter. $filename now have to contain full path to target directory. Before 2.4: diff --git a/includes/current_user.inc b/includes/current_user.inc index f4533ddf..dc585296 100644 --- a/includes/current_user.inc +++ b/includes/current_user.inc @@ -58,12 +58,15 @@ class current_user function login($company, $loginname, $password) { - global $security_areas, $security_groups, $security_headings, $path_to_root; - + global $security_areas, $security_groups, $security_headings, $path_to_root, $dflt_lang; + $this->set_company($company); $this->logged = false; - set_global_connection(); + set_global_connection($company); + $lang = &$_SESSION['language']; + $lang->set_language($_SESSION['language']->code); + db_set_encoding($_SESSION['language']->encoding); // Use external authentication source if any. // Keep in mind you need to have user data set for $loginname @@ -76,6 +79,12 @@ class current_user if ($Auth_Result) { $myrow = get_user_by_login($loginname); + if ($myrow['language'] != $dflt_lang) + { // refresh language and user data + $lang->set_language($myrow['language']); + db_set_encoding($_SESSION['language']->encoding); + $myrow = get_user_by_login($loginname); + } $this->old_db = isset($myrow["full_access"]); if (! @$myrow["inactive"]) { @@ -141,7 +150,7 @@ class current_user { global $security_groups, $security_areas; if (isset($security_groups)) { - return $this->company == 0 && + return is_admin_company() && in_array(20, $security_groups[$this->access]); } @@ -222,7 +231,7 @@ define('FLOAT_COMP_DELTA', 0.004); function floatcmp($a, $b) { - return $a - $b > FLOAT_COMP_DELTA ? 1 : $b - $a > FLOAT_COMP_DELTA ? -1 : 0; + return $a - $b > FLOAT_COMP_DELTA ? 1 : ($b - $a > FLOAT_COMP_DELTA ? -1 : 0); } // @@ -582,4 +591,9 @@ function company_path($comp=null) . '/'.$comp; } +function is_admin_company() +{ + return $this->company == 0; +} + ?> \ No newline at end of file diff --git a/includes/db/connect_db.inc b/includes/db/connect_db.inc index fda58ab3..ecd84249 100644 --- a/includes/db/connect_db.inc +++ b/includes/db/connect_db.inc @@ -10,36 +10,149 @@ See the License here . ***********************************************************************/ +/* + Converts encoding name to mysql standard. +*/ +function get_mysql_encoding_name($encoding) +{ + $db_encoding = array( + 'UTF-8' => 'utf8', + 'ISO-8859-1' => 'latin1', + 'ISO-8859-2' => 'latin2', + 'ISO-8859-7' => 'greek', + 'ISO-8859-8' => 'hebrew', + 'ISO-8859-9' => 'latin5', + 'ISO-8859-13' => 'latin7', + 'KOI8-R' => 'koi8r', + 'KOI8-U' => 'koi8u', + 'CP850' => 'cp850', + 'CP866' => 'cp866', + 'CP932' => 'cp932', + 'CP1250' => 'cp1250', + 'CP1251' => 'cp1251', + 'CP1252' => 'latin1', + 'CP1256' => 'cp1256', + 'CP1257' => 'cp1257', + 'GB2312' => 'gb2312', + 'EUC-JP' => 'ujis', + 'EUC-KR' => 'euckr', + 'BIG5' => 'big5', + 'GBK' => 'gbk', + 'SHIFT_JIS' => 'sjis', + 'TIS-620' => 'tis620', + 'ASCII' => 'ascii', + ); + $encoding = strtoupper($encoding); + + return isset($db_encoding[$encoding]) ? $db_encoding[$encoding] : null; +} + +/* + Returns 'best' collation for various locale language codes +*/ +function get_mysql_collation($lang=null) +{ + if (!$lang) + $lang = substr($_SESSION['language']->code, 0, 2); + + $db_collation = array( + 'is' => 'icelandic', + 'lv' => 'latvian', + 'ro' => 'romanian', + 'sl' => 'slovenian', + 'pl' => 'polish', + 'et' => 'estonian', + 'es' => 'spanish', // or 'spanish2', + 'sw' => 'swedish', + 'tr' => 'turkish', + 'cs' => 'czech', + 'da' => 'danish', + 'lt' => 'lithuanian', + 'sk' => 'slovak', + 'sp' => 'spanish2', + 'fa' => 'persian', + 'hu' => 'hungarian', + 'fr' => 'roman', + 'it' => 'roman', + ); + + return 'utf8_'.(isset($db_collation[$lang]) ? $db_collation[$lang] : 'general').'_ci'; +} +/* + Later we assume that database with version less than 2.4 is old database, + which is subject to invalid encodings on text columns, + so no SET NAMES or equivalent should be used. +*/ +function db_fixed() +{ + $result = db_query("SELECT value FROM ".TB_PREF."sys_prefs WHERE name='version_id'"); + $data = db_fetch($result); + return !db_num_rows($result) // new database is fixed by default + || ($data[0] > "2.3rc"); +} +/* + Check database default charset. +*/ +function db_get_charset() +{ + $result = db_query("SELECT @@character_set_database"); + $var = db_fetch($result); + return $var[0]; +} + +/* + Set mysql client encoding. + Default is is encoding used by default language. +*/ +function db_set_encoding($ui_encoding=null) +{ + global $dflt_lang, $installed_languages; + + if (!isset($ui_encoding)) + { + $lang = array_search_value($dflt_lang, $installed_languages, 'code'); + $ui_encoding = strtoupper($lang['encoding']); + } + + if ($mysql_enc = get_mysql_encoding_name($ui_encoding)) + mysql_set_charset($mysql_enc); +} + +/* + Connects application to company database. +*/ function set_global_connection($company=-1) { - global $db, $transaction_level, $db_connections; + global $db, $transaction_level, $path_to_root; + global $db_connections; + + include ($path_to_root . "/config_db.php"); + if ($company == -1) + $company = $_SESSION["wa_current_user"]->company ? $_SESSION["wa_current_user"]->company : 0; cancel_transaction(); // cancel all aborted transactions if any $transaction_level = 0; - if ($company == -1) - $company = $_SESSION["wa_current_user"]->company; - $_SESSION["wa_current_user"]->cur_con = $company; $connection = $db_connections[$company]; $db = mysql_connect($connection["host"], $connection["dbuser"], $connection["dbpassword"]); mysql_select_db($connection["dbname"], $db); + return $db; } $db_duplicate_error_code = 1062; -//DB wrapper functions to change only once for whole application - function db_query($sql, $err_msg=null) { global $db, $show_sql, $sql_trail, $select_trail, $go_debug, $sql_queries, $Ajax, $db_connections, $db_last_inserted_id; - + // set current db prefix - $cur_prefix = $db_connections[$_SESSION["wa_current_user"]->cur_con]['tbpref']; + $comp = isset($_SESSION["wa_current_user"]->cur_con) ? $_SESSION["wa_current_user"]->cur_con : 0; + $cur_prefix = $db_connections[$comp]['tbpref']; $sql = str_replace(TB_PREF, $cur_prefix, $sql); if ($show_sql) @@ -48,7 +161,15 @@ function db_query($sql, $err_msg=null) $sql_queries .= "
$sql
\n
"; } - $result = mysql_query($sql, $db); + // mysql profiling + global $profile_sql; + if (@$profile_sql) get_usec(); + $result = mysql_query($sql, $db); + if (@$profile_sql) + { + $profile_sql= false; + _vd($sql.'
:'.db_num_rows($result).'rows, '.get_usec()); + } if($sql_trail) { $db_last_inserted_id = mysql_insert_id($db); // preserve in case trail insert is done @@ -174,7 +295,8 @@ function db_create_db($connection) $connection["dbuser"], $connection["dbpassword"]); if (!mysql_select_db($connection["dbname"], $db)) { - $sql = "CREATE DATABASE " . $connection["dbname"] . ""; + $sql = "CREATE DATABASE " . $connection["dbname"] . " COLLATE ".get_mysql_collation(); + if (!mysql_query($sql) || !mysql_select_db($connection["dbname"], $db)) return 0; } @@ -214,5 +336,3 @@ function db_close($dbase = null) $dbase = $db; return mysql_close($dbase); } - -?> \ No newline at end of file diff --git a/includes/lang/language.php b/includes/lang/language.php index 5b050925..c090f1fc 100644 --- a/includes/lang/language.php +++ b/includes/lang/language.php @@ -74,7 +74,7 @@ class language // version set globally in php.ini ini_set('default_charset', $this->encoding); - if (isset($_SESSION['App']) && $changed) + if (isset($_SESSION['wa_current_user']) && $_SESSION['wa_current_user']->logged_in() && isset($_SESSION['App']) && $changed) $_SESSION['App']->init(); // refresh menu } } diff --git a/includes/session.inc b/includes/session.inc index 98ed4677..983433a9 100644 --- a/includes/session.inc +++ b/includes/session.inc @@ -306,7 +306,8 @@ include_once($path_to_root . "/config.php"); get_text_init(); // Page Initialisation -if (!isset($_SESSION['language']) || !method_exists($_SESSION['language'], 'set_language')) +if (!isset($_SESSION['wa_current_user']) || !$_SESSION['wa_current_user']->logged_in() + || !isset($_SESSION['language']) || !method_exists($_SESSION['language'], 'set_language')) { $l = array_search_value($dflt_lang, $installed_languages, 'code'); $_SESSION['language'] = new language($l['name'], $l['code'], $l['encoding'], @@ -382,11 +383,12 @@ if (strstr($_SERVER['PHP_SELF'], 'logout.php') == false){ // Incorrect password login_fail(); } - $lang = &$_SESSION['language']; - $lang->set_language($_SESSION['language']->code); } } else - set_global_connection(); + { set_global_connection(); + if (db_fixed()) + db_set_encoding($_SESSION['language']->encoding); + } if (!isset($_SESSION["App"])) { $_SESSION["App"] = new front_accounting(); diff --git a/sql/alter2.4.php b/sql/alter2.4.php index 61afa5cf..fb988bd4 100644 --- a/sql/alter2.4.php +++ b/sql/alter2.4.php @@ -10,7 +10,7 @@ See the License here . ***********************************************************************/ class fa2_4 { - var $version = '2.4'; // version installed + var $version = '2.4.0'; // version installed var $description; var $sql = 'alter2.4.sql'; var $preconf = true; @@ -23,21 +23,23 @@ class fa2_4 { // Install procedure. All additional changes // not included in sql file should go here. // - function install($company, $force) + function install($company, $force=false) { - global $db_version; - + global $db_version, $db_connections; + + $pref = $db_connections[$company]['tbpref']; + if (get_company_pref('grn_clearing_act') === null) { // available form 2.3.1, can be not defined on pre-2.4 installations set_company_pref('grn_clearing_act', 'glsetup.purchase', 'varchar', 15, 0); } if (get_company_pref('default_receival_required') === null) { // new in 2.4 installations set_company_pref('default_receival_required', 'glsetup.purchase', 'smallint', 6, 10); } - $result = $this->update_workorders(); + $result = $this->update_workorders() && $this->switch_database_to_utf($pref); if ($result) $result = $this->do_cleanup(); -// return update_company_prefs(array('version_id'=>$db_version)); - return true; + + return update_company_prefs(array('version_id'=>$db_version)); } // // Checking before install @@ -46,6 +48,14 @@ class fa2_4 { { return true; } + + // + // optional procedure done after upgrade fail, before backup is restored + // + function post_fail($pref) + { + db_query("DROP TABLE IF EXISTS " . $pref . 'wo_costing'); + } // // Test if patch was applied before. // @@ -89,6 +99,85 @@ class fa2_4 { return true; } +/* + In previous versions FA ignored encoding settings on database/tables, so it depended on server settings, + but data stored is encoded in user language encoding. Now we switch to utf8 internal database encoding, while + user encoding can be selected independently. + + To perform safe FA database switch to utf-8 encoding we have to first ensure that all text/char columns + have properly set encoding (the same as its content), so the algorithm performed on every table is as follows: + . set default table encoding for the table to currently used on client side; + . for all text/char column: + - suppress autorecoding by change of the type to related binary/blob type + - change column to utf8 encodding and selected collation. + . change default table encoding to utf8 +*/ + function switch_database_to_utf($pref, $test = false) { + + global $installed_languages, $dflt_lang; + + $old_encoding = 'latin1'; // default client encoding + + // site default encoding is presumed as encoding for all databases! + $lang = array_search_value($dflt_lang, $installed_languages, 'code'); + $new_encoding = get_mysql_encoding_name(strtoupper($lang['encoding'])); + // get_usec(); + if ($test) + error_log('Switching database to utf8 encoding from '.$old_encoding); + $collation = get_mysql_collation(); + $tsql = "SHOW TABLES LIKE '".($pref=='' ? '' : substr($pref, 0, -1).'\\_')."%'"; + $tresult = db_query($tsql, "Cannot select all tables with prefix '$pref'"); + while($tbl = db_fetch($tresult)) { + $table = $tbl[0]; + // if ($table != '1_chart_master') continue; _vd($table); get_usec(); // fast debug on single table + + db_query("ALTER TABLE `$table` CONVERT TO CHARACTER SET $old_encoding"); // convert encoding on utf-8 tables + + // set proper default table encoding for current user language (used on binary->text conversion) + db_query("ALTER TABLE `$table` CHARSET $new_encoding"); + $csql = "SHOW COLUMNS FROM $table"; + $cresult = db_query($csql, "Cannot select column names for table '$table'"); + $convert = false; + + $to_binary = $to_default = $to_utf = array(); + while($col = db_fetch($cresult)) { + + $bintype = strtr($col['Type'], array('varchar' => 'varbinary', 'char'=>'varbinary', 'text'=>'blob', 'tinytext'=>'tinyblob')); + + if ($bintype != $col['Type']) + { // this is char/text column, so change encoding to proper encoding + if ($test) + error_log($table.'.'.$col['Field']); + + $null = $col['Null'] === 'YES' ? ' NULL ' : ' NOT NULL '; + $default = $col['Null'] !== 'YES' && isset($col['Default']) ? ' DEFAULT '.db_escape($col['Default']) : ''; + + // to avoid column width multiplication x3 we old->binary->ui->utf column type change instead of column CONVERT + + $to_binary[] = "CHANGE `".$col['Field']."` `".$col['Field']."` ".$bintype; + $to_default[] = "CHANGE `".$col['Field']."` `".$col['Field']."` ".$col['Type'].$null.$default; + $to_utf[] = "MODIFY COLUMN `".$col['Field']."` ".$col['Type']." COLLATE ".$collation.$null.$default; + $convert = true; + } + } + if(count($to_binary)) + { + $sql = "ALTER TABLE `$table` ".implode(',',$to_binary); + db_query($sql); + $sql = "ALTER TABLE `$table` ".implode(',',$to_default); + db_query($sql); + $sql = "ALTER TABLE `$table` ".implode(',',$to_utf); + db_query($sql); + } + db_query("ALTER TABLE `$table` COLLATE $collation"); + } + db_query("ALTER DATABASE COLLATE $collation"); + if ($test) + error_log('Convertion to utf8 done.'); + + return true; + } + function do_cleanup() { $sql = "ALTER TABLE `".TB_PREF."tax_group_items` DROP COLUMN `rate`"; diff --git a/version.php b/version.php index 1b3db02e..9a4bf296 100644 --- a/version.php +++ b/version.php @@ -5,7 +5,7 @@ // // Internal database version compatibility check. Do not change. -$db_version = "2.3rc"; +$db_version = "2.4.0"; // application version - can be overriden in config.php if (!isset($version)) -- 2.30.2