iso-8859-1 to UTF-8 conversion.

Tuesday 23 August 2005 6:27:51 am

Hi Laurent,

1st of all, you need a newer mysql, (the version should be greater or equal than 4.1.11).

Then you need to change the charset of the ez tables. I've rewritten a small script for that purpous which I found at the forums of mysql:

<?php
// put in your username, password
$conn = mysql_connect("localhost", "root", "mypassword");

//change this to false to alter on the fly
$printonly=true; 

$charset="utf8";
$collate="utf8_general_ci";

$altertablecharset=true;
$alterdatabasecharser=true;

// put here your databases ...
$currentDBArray = array();
$currentDBArray[] = "mydb";


function PMA_getDbCollation($db)
{
	$sq='SHOW CREATE DATABASE `'.$db.'`;';
	$res = mysql_query($sq);
	if(!$res) echo "\n\n".$sq."\n".mysql_error()."\n\n"; else
	if($row = mysql_fetch_assoc($res))
	{
		$tokenized = explode(' ', $row[1]);
		unset($row, $res, $sql_query);
		for ($i = 1; $i + 3 < count($tokenized); $i++)
		{
			if ($tokenized[$i] == 'DEFAULT' && $tokenized[$i + 1] == 'CHARACTER' && $tokenized[$i + 2] == 'SET')
			{
				if (isset($tokenized[$i + 5]) && $tokenized[$i + 4] == 'COLLATE')
				{
					 return array($tokenized [$i + 3],$tokenized[$i + 5]); // We found the collation!
				}
				else
				{
					return array($tokenized [$i + 3]);
				}
			}
		} 
	}
	return '';
}

$rs2 = mysql_query("SHOW DATABASES"); 
if(!$rs2)
	echo "\n\n".$sq."\n".mysql_error()."\n\n";
else
	while ($data2 = mysql_fetch_row($rs2))
	{
		$db=$data2[0];
		$db_cha=PMA_getDbCollation($db);
		if ( in_array ( $db, $currentDBArray ) )
			if ( substr($db_cha[0],0,4)!='utf8' ) // limit to charset
			{
				mysql_select_db($db);
				$rs = mysql_query("SHOW TABLES"); 
				if(!$rs)
					echo "\n\n".$sq."\n".mysql_error()."\n\n";
				else
					while ($data = mysql_fetch_row($rs))
					{
						if ( substr ( $data[0], 0,2 ) == "ez" )
						{
							$rs1 = mysql_query("show FULL columns from $data[0]");
							
							if(!$rs1)
								echo "\n\n".$sq."\n".mysql_error()."\n\n";
							else
								while ($data1 = mysql_fetch_assoc($rs1))
								{
									if(in_array(array_shift(split("\\(",$data1['Type'],2)),array(
																				//'national char',
																				//'nchar',
																				//'national varchar',
																				//'nvarchar',
																				'char',
																				'varchar',
																				'tinytext',
																				'text',
																				'mediumtext',
																				'longtext',
																				'enum',
																				'set'
																				  ))) 
									 {
										if(substr($data1['Collation'],0,4)!='utf8') // limit to charset
										{
											$sq="ALTER TABLE `$data[0]` CHANGE `".$data1['Field'].'` `'.$data1['Field'].'` '.$data1['Type'].' CHARACTER SET binary '.($data1['Default']==''?'':($data1['Default']=='NULL'?' DEFAULT NULL':' DEFAULT \''.mysql_escape_string($data1['Default']).'\'')).($data1['Null']=='YES'?' NULL ':' NOT NULL').';';
											if(!$printonly&&!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n"; 
											else
											{
												echo ($sq."\n") ; 
												$sq="ALTER TABLE `$data[0]` CHANGE `".$data1['Field'].'` `'.$data1['Field'].'` '.$data1['Type']." CHARACTER SET $charset ".($collate==''?'':"COLLATE $collate").($data1['Default']==''?'':($data1['Default']=='NULL'?' DEFAULT NULL':' DEFAULT \''.mysql_escape_string($data1['Default']).'\'')).($data1['Null']=='YES'?' NULL ':' NOT NULL').($data1['Comment']==''?'':' COMMENT \''.mysql_escape_string($data1['Comment']).'\'').';';
												if(!$printonly&&!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n"; 
												else echo ($sq."\n") ; 
											}
										}
									}
								}
								if($altertablecharset)
								{
									/*
									  $sq='ALTER TABLE `'.$data[0]."` DEFAULT CHARACTER SET binary";
									  echo ($sq."\n") ; 
									  if(!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
									*/
									$sq='ALTER TABLE `'.$data[0]."` DEFAULT CHARACTER SET $charset ".($collate==''?'':"COLLATE $collate");
									echo ($sq."\n") ; 
									if(!$printonly)
										if(!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
								}
						}
						else
							echo $data[0] . " nicht geändert.\n";
						if( $alterdatabasecharser )
						{
						  /*
						  $sq='ALTER DATABASE `'.$data2[0]."` DEFAULT CHARACTER SET binary";
						  echo ($sq."\n") ; 
						  if(!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
						  */ 
						  $sq='ALTER DATABASE `'.$data2[0]."` DEFAULT CHARACTER SET $charset ".($collate==''?'':"COLLATE $collate");
						  echo ($sq."\n") ; 
							if(!$printonly)
								if(!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
						}
					}
				}
			}
?>

Then you need to change the ini-settings of ezpublish.

-> site.ini.append: charset at db entry
-> i18n.ini.append: charset-setting

After that, don't forget to clear the ezpublish cache completly.

HTH.

Best wishes,
Georg.

--
http://www.schicksal.com Horoskop website which uses eZ Publish since 2004

Tuesday 23 August 2005 9:56:46 am

Hi Laurent,

backup - backup - backup ... of course :-))

The script converts the tables first to a binary format and then to utf8, so no data should be lost.

The script simply produces sql strings for the conversion. If you run it the first time and the var $printonly is set to true (at the begin of the script), only the sql strings are written to the screen, nothing else happen.

If you really want to do the conversion, set $printonly to false.

Best wishes,
Georg.

--
http://www.schicksal.com Horoskop website which uses eZ Publish since 2004