nuBuilder Forum

Code: Select all

/* * * * * * * * * * * * * * * * * * * * * * * *\
** BACK UP YOUR DATABASE BEFORE RUNNING THIS!  **
** * * * * * * * * * * * * * * * * * * * * * * **
** Run this procedure to change the current database to a desired character set and collation
** Adapted from script found at
**     http://stackoverflow.com/questions/105572/a-script-to-change-all-tables-and-fields-to-the-utf-8-bin-collation-in-mysql
*/
//die("This Procedure is Currently Disabled."); //comment out this die() to allow the procedure to run
/*
** Options
** (Change them!)
*/
$use_intermediate_binary_types = true;
$limit_index_length = true;
/*
** End Options
*/

$target_charset = "utf8";
$target_collate = "utf8_general_ci";
//report options
echo "Destination Character Set: $target_charset<br>\n";
echo "Destination Collation: $target_collate<br>\n";
if($use_intermediate_binary_types){
	echo "Using Intermediate Binary Field Types: YES<br>\n";
}else{
	echo "Using Intermediate Binary Field Types: NO<br>\n";
}
if($limit_index_length){
	echo "Limit Index Lengths: YES<br>\n";
}else{
	echo "Limit Index Lengths: NO<br>\n";
}
echo "<br>\n";

//set up list of tables
$tabs = array();
$res = nuRunQuery("SHOW TABLES");
$uibt = $use_intermediate_binary_types;
while (($row = db_fetch_row($res)) != null){
	$tabs[] = $row[0];
}

//convert tables
foreach ($tabs as $tab){
	echo "Operating on $tab<br>\n";
	$res = nuRunQuery("show index from {$tab}");
	
	//kill the indicies
	$indicies = array();
	$usedindexnames = array();
	echo "&nbsp;&nbsp;&nbsp;&nbsp;Dropping Indices ";
	
	while (($row = db_fetch_array($res)) != null){
		
		if ($row[2] != "PRIMARY"){
			
			//place some arbitrary limits on how much of a field is used in an index.
			//this is to prevent an issue that could arise for example if you have an ascii index of some length (~256B?)
			//and are converting to utf8. indicies have a hard limit of 1000B length, and utf8 uses 4B per char.
			//so, ascii 256B converts to utf8 1024B > 1000B *sadface*
			if($limit_index_length && $target_charset=="utf8"){
				$vals = nuRunQuery("select data_type, character_maximum_length from information_schema.columns where table_schema = '{$DBName}' and table_name = '{$tab}' and column_name = '{$row[4]}'");
				$val = db_fetch_row($vals);
				$limitsize = "";
				if($val[0] == "varchar"){
					if($val[1] < 32){
					}else if($val[1] < 128){
						$limitsize = "(32)";
					}else{
						$limitsize = "(64)";
					}
				}
			}
			
			
			//as you can have multiple fields in an index, you need to handle two cases:
			//    1: you haven't encountered the current index name yet (for this table)
			//    2: you previously dropped an index of this name
			//in the case of 1, we need to drop the index and record the fieldname
			//in the case of 2, we need to append the fieldname to the current information about the dropped index
			if(isset($usedindexnames[$row[2]])){
				//append to current entry
				$indicies[$row[2]]["col"] = $indicies[$row[2]]["col"].",".$row[4].$limitsize;
			}else{
				//drop index and new entry
				$indicies[$row[2]] = array("name" => $row[2], "unique" => !($row[1] == "1"), "col" => $row[4].$limitsize);
				nuRunQuery("ALTER TABLE {$tab} DROP INDEX {$row[2]}");
				$usedindexnames[$row[2]] = true;
				echo "&#91;&#93;";//progress indicator
			}
			
		}
		
	}
	
	echo "<br>\n";
	
	//convert each field across to the new character set and collation
	$res = nuRunQuery("DESCRIBE {$tab}");
	echo "&nbsp;&nbsp;&nbsp;&nbsp;Converting Fields ";
	
	while (($row = db_fetch_array($res)) != null){
		$name = $row[0];
		$type = $row[1];
		$notnull = $row[2]=='NO';
		$default = $row[4];
		$extra = $row[5];
		
		$set = false;
		
		if (preg_match("/^varchar\((\d+)\)$/i", $type, $mat)){
			$size = $mat[1];
			if($uibt){
				nuRunQuery("ALTER TABLE {$tab} MODIFY {$name} VARBINARY({$size})");
			}
			
			$query = "ALTER TABLE {$tab} MODIFY {$name} VARCHAR({$size}) CHARACTER SET {$target_charset} COLLATE {$target_collate}";
			$set = true;
			echo "&#91;&#93;";//progress indicator
			
		}else if (!strcasecmp($type, "CHAR")){
			if($uibt){
				nuRunQuery("ALTER TABLE {$tab} MODIFY {$name} BINARY(1)");
			}
			
			$query = "ALTER TABLE {$tab} MODIFY {$name} VARCHAR(1) CHARACTER SET {$target_charset} COLLATE {$target_collate}";
			$set = true;
			echo "&#91;&#93;";//progress indicator
			
		}else if (!strcasecmp($type, "TINYTEXT")){
			if($uibt){
				nuRunQuery("ALTER TABLE {$tab} MODIFY {$name} TINYBLOB");
			}
			
			$query = "ALTER TABLE {$tab} MODIFY {$name} TINYTEXT CHARACTER SET {$target_charset} COLLATE {$target_collate}";
			$set = true;
			echo "&#91;&#93;";//progress indicator
			
		}else if (!strcasecmp($type, "MEDIUMTEXT")){
			if($uibt){
				nuRunQuery("ALTER TABLE {$tab} MODIFY {$name} MEDIUMBLOB");
			}
			
			$query = "ALTER TABLE {$tab} MODIFY {$name} MEDIUMTEXT CHARACTER SET {$target_charset} COLLATE {$target_collate}";
			$set = true;
			echo "&#91;&#93;";//progress indicator
			
		}else if (!strcasecmp($type, "LONGTEXT")){
			if($uibt){
				nuRunQuery("ALTER TABLE {$tab} MODIFY {$name} LONGBLOB");
			}
			
			$query = "ALTER TABLE {$tab} MODIFY {$name} LONGTEXT CHARACTER SET {$target_charset} COLLATE {$target_collate}";
			$set = true;
			echo "&#91;&#93;";//progress indicator
			
		}else if (!strcasecmp($type, "TEXT")){
			if($uibt){
				nuRunQuery("ALTER TABLE {$tab} MODIFY {$name} BLOB");
			}
			
			$query = "ALTER TABLE {$tab} MODIFY {$name} TEXT CHARACTER SET {$target_charset} COLLATE {$target_collate}";
			$set = true;
			echo "&#91;&#93;";//progress indicator
			
		}
		
		//if we had one of the specified field types, then we should execute the query
		//(after adding in any column attributes)
		if($set){
			if($notnull){
				$query.= " NOT NULL";
			}
			if($default != NULL){
				$query.= " DEFAULT '$default'";
			}
			$query.= " $extra";
			nuRunQuery($query);
		}
		
	}
	echo "<br>\n";
	echo "&nbsp;&nbsp;&nbsp;&nbsp;Rebuilding Indices ";
	
	// set up the indicies again
	foreach ($indicies as $index){
	
		if ($index["unique"]){
			nuRunQuery("CREATE UNIQUE INDEX {$index["name"]} ON {$tab} ({$index["col"]})");
		}else{
			nuRunQuery("CREATE INDEX {$index["name"]} ON {$tab} ({$index["col"]})");
		}

		echo "&#91;&#93;";//progress indicator
	}

	// set the table's character set and collation
	nuRunQuery("ALTER TABLE {$tab} DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
	echo "<br><br>\n";

}

// change the database's character set and collation
nuRunQuery("ALTER DATABASE {$db} DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");