converter_latin1_para_utf8.php

<?php
/*
  Uninuni.com on 30 july 2008
  Fixed:
    MySql sintax when setting columns to latin1
    Now we have 6 types of binary instead of just blob (avoid 'key length' error)
    No more need to set the DB name on the source code
*/

/*
Template Name: Convert Database to UTF8 

Copyright 2007  Anders Stalheim Oefsdahl  (email : anders@apt.no)
Detail at http://www.mydigitallife.info/2007/07/22/how-to-convert-character-set-and-collation-of-wordpress-database/

Bug fix by My Digital Life on 22 June 2007.

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

*/

echo '<h1>Converter Banco de Dados de latin1 para utf8</h1>';

echo '<p>Esta p�gina gera um c�digo SQL que deve ser executado no seu cliente MySQL (PHPMyAdmin, por exemplo)</p>';
echo '<p>Maiores informa��es no nosso site (<a href="http://uninuni.com/convertendo-o-banco-de-dados-do-wordpress-de-latin1-para-utf8/">Uninuni</a>)</p>';

require_once("wp-config.php");
global $wpdb;

/**
 * Fetch all tables
 */
$tables = array();
$sql_tables = "SHOW TABLES";
$res_tables = $wpdb->get_results( $sql_tables );

if( is_array( $res_tables ) ){
	foreach( $res_tables as $res_table ){
    // Uninuni
		// if(strpos($res_table->Tables_in_DATABASENAME, $table_prefix) === 0)
		// $tables[$res_table->Tables_in_DATABASENAME] = array();

    // Uninuni    
    $db_name = "Tables_in_" . DB_NAME;
		if(strpos($res_table->$db_name, $table_prefix) !== false)    
      $tables[$res_table->$db_name] = array();
	}
}

/**
 * Loop all tables fetching each table's fields and filter out fields of type CHAR, VARCHAR, TEXT, ENUM, and SET (and related variations)
 */
if( count( $tables )>0 ){
	foreach( $tables as $table=>$fields ){
//		if (strpos($table_prefix, $table) == 0)
//		{
			$sql_fields = "EXPLAIN ".$table;
			$res_fields = $wpdb->get_results( $sql_fields );
			if( is_array( $res_fields ) ){      
				foreach( $res_fields as $field ){
          $value = array();
          
          // Uninuni - lots of changes
          
					switch( TRUE ){ 
						case stripos( $field->Type, 'varchar'   ) !== false: // varchar must come first, otherwise char would be set as varchar
              $value["type_lc"] = 'varchar';
          
						case stripos( $field->Type, 'char'      ) !== false:
              $value["type_lc"] = ($value["type_lc"]== "varchar"?"varchar":"char");
                            
              $begin = stripos( $field->Type, '(');
              $end   = stripos( $field->Type, ')');
              
              if ($begin !== false && $end !== false)
                $value["size"] = substr($field->Type, $begin + 1, $end - $begin - 1);

						case stripos( $field->Type, 'text'      ) !== false: // text, tinytext, mediumtext, longtext
						case stripos( $field->Type, 'enum'      ) !== false:
						case stripos( $field->Type, 'set'       ) !== false:
							$value["name"]    = $field->Field;
              $value["type"]    = $field->Type;
              $value["type_lc"] = ($value["type_lc"]== ""?strtolower($field->Type):$value["type_lc"]);

//            echo "<br><br> ".$value["name"]." - ".$value["type"]." - ".$value["type_lc"]." - ".$value["size"];

							$tables[$table][] = $value;
							break;
						
						default:
							break;
					}
				}
//			}
		}
	}
}

$tables_to_latin1	= '';
$tables_to_utf8		= '';
$columns_to_latin1	= '';
$columns_to_blob	= '';
$columns_to_utf8	= '';

foreach( $tables as $table=>$fields ){
	$tables_to_latin1 .= "\nALTER TABLE ".$table." DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;";
	$tables_to_utf8 .= "\nALTER TABLE ".$table." DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;";
	if( count( $fields )>0 ){
		foreach( $fields as $field ){
      $name    = $field["name"];
      $size    = $field["size"];
      $type    = $field["type"];
      $type_lc = $field["type_lc"]; // lower case
      
//    Uninuni
//    $columns_to_latin1 .= "\nALTER TABLE ".$table." MODIFY ".$name." CHARACTER SET latin1 COLLATE latin1_swedish_ci;";
      $columns_to_latin1 .= "\nALTER TABLE ".$table." MODIFY ".$name." ".$type." CHARACTER SET latin1 COLLATE latin1_swedish_ci;";

//    Uninuni
//    $columns_to_blob .= "\nALTER TABLE ".$table." MODIFY ".$name." BLOB;";         
      $columns_to_blob .= "\nALTER TABLE ".$table." MODIFY ".$name;
      
//    Uninuni      
      switch( $type_lc ) {
        case "char":
          $columns_to_blob .= " binary(".$size.");";
          break;
        case "varchar":
          $columns_to_blob .= " varbinary(".$size.");";
          break;
        case "tinytext":
          $columns_to_blob .= " tinyblob;";
          break;
        case "text":
          $columns_to_blob .= " blob;";
          break;
        case "mediumtext":
          $columns_to_blob .= " mediumblob;";
          break;
        case "longtext":
          $columns_to_blob .= " longblob;";
          break;
          
			  default:
          $columns_to_blob .= " blob;";
          break;          
      }

			$columns_to_utf8 .= "\nALTER TABLE ".$table." MODIFY ".$name." ".$type.";";
		}
	}
}

//$complete_sql = $sql_to_blob."\nALTER DATABASE ".DB_NAME." charset=utf8;".$sql_to_utf8.$sql_to_original;
$complete_sql = $tables_to_latin1."\n".$columns_to_latin1."\n".$columns_to_blob."\n".$tables_to_utf8."\n".$columns_to_utf8;

echo nl2br( $complete_sql );

?>