CSiteCheckerTest::check_mysql_table_charset

function check_mysql_table_charset()
{
	global $DB;
	$strError = '';

	if ($this->arTestVars['check_mbstring_fail'])
		return $this->Result(null, GetMessage('SC_MBSTRING_NA'));
	elseif ($this->arTestVars['check_table_status_fail'])
		return $this->Result(null, GetMessage('SC_TABLES_NEED_REPAIR'));
	elseif ($this->arTestVars['check_connection_charset_fail'])
		return $this->Result(null, GetMessage('SC_CONNECTION_CHARSET_NA'));
	elseif ($this->arTestVars['db_charset_fail'])
		return $this->Result(null, GetMessage('SC_TABLE_CHECK_NA'));

	$res = $DB->Query('SHOW VARIABLES LIKE "character_set_database"');
	$f = $res->Fetch();
	$charset = trim($f['Value']);

	$res = $DB->Query('SHOW VARIABLES LIKE "collation_database"');
	$f = $res->Fetch();
	$collation = trim($f['Value']);

	$time = time();
	$i = 0;
	$res = $DB->Query('SHOW TABLES LIKE "b_%"');
	$cnt = $res->SelectedRowsCount();

	$arExclusion = array(
		'b_sale_loc_search_word' => 'WORD',
		'b_search_content_stem' => 'STEM',
		'b_search_content_freq' => 'STEM',
		'b_search_stem' => 'STEM',
		'b_search_tags' => 'NAME',
		'b_translate_path' => 'NAME',
		'b_translate_phrase' => 'CODE',
	);
	while($f = $res->Fetch())
	{
		$i++;
		$table = current($f);

		if ($this->arTestVars['last_value'])
		{
			if ($this->arTestVars['last_value'] == $table)
				unset($this->arTestVars['last_value']);
			continue;
		}

		$res0 = $DB->Query('SHOW CREATE TABLE ' . $DB->quote($table), true);
		if ($res0 === false)
		{
			if ($this->fix_mode)
			{
				$res0 = $DB->Query('DROP TABLE ' . $DB->quote($table), true);
			}
			else
			{
				$strError .= GetMessage('SC_TABLE_BROKEN',array('#TABLE#'=>$table))."
";
				$this->arTestVars['iError']++;
				$this->arTestVars['iErrorAutoFix']++;
			}
			continue;
		}
		$f0 = $res0->Fetch();

		if (preg_match('/DEFAULT CHARSET=([a-z0-9-_]+)/i', $f0['Create Table'], $regs))
		{
			$t_charset = $regs[1];
			if (preg_match('/COLLATE=([a-z0-9-_]+)/i', $f0['Create Table'], $regs))
				$t_collation = $regs[1];
			else
			{
				$res0 = $DB->Query('SHOW CHARSET LIKE "' . $t_charset . '"');
				$f0 = $res0->Fetch();
				$t_collation = $f0['Default collation'];
			}
		}
		else
		{
			$res0 = $DB->Query('SHOW TABLE STATUS LIKE "' . $table . '"');
			$f0 = $res0->Fetch();
			if (!$t_collation = $f0['Collation'])
				continue;
			$t_charset = getCharsetByCollation($t_collation);
		}

		if ($charset != $t_charset)
		{
			// table charset differs
			if (!$this->fix_mode)
			{
				$strError .= GetMessage('SC_DB_MISC_CHARSET',array('#TABLE#' => $table,'#VAL1#' => $t_charset,'#VAL0#'=>$charset)) . "
";
				$this->arTestVars['iError']++;
				if ($this->force_repair)
					$this->arTestVars['iErrorAutoFix']++;
			}
			elseif ($this->force_repair && !$DB->Query($sql = 'ALTER TABLE ' . $DB->quote($table) . ' CHARACTER SET ' . $charset, true))
			{
				$strError .= $sql . ' [' . $DB->db_Error . ']';
				break;
			}
		}
		elseif ($t_collation != $collation)
		{	// table collation differs
			if (!$this->fix_mode)
			{
				$strError .= GetMessage('SC_COLLATE_WARN',array('#TABLE#'=>$table,'#VAL1#'=>$t_collation,'#VAL0#'=>$collation))."
";
				$this->arTestVars['iError']++;
				$this->arTestVars['iErrorAutoFix']++;
			}
			elseif (!$DB->Query($sql = 'ALTER TABLE ' . $DB->quote($table) . ' COLLATE ' . $collation, true))
			{
				$strError .= $sql . ' [' . $DB->db_Error . ']';
				break;
			}
		}

		// fields check
		$arFix = array();
		$res0 = $DB->Query("SHOW FULL COLUMNS FROM " . $DB->quote($table));
		while($f0 = $res0->Fetch())
		{
			$f_collation = $f0['Collation'];
			if ($f_collation === null || $f_collation === "NULL")
				continue;

			$f_charset = getCharsetByCollation($f_collation);

			if ($charset != $f_charset)
			{
				// field charset differs
				if (!$this->fix_mode)
				{
					$strError .= GetMessage('SC_TABLE_CHARSET_WARN',array('#TABLE#' => $table, '#VAL0#' => $charset, '#VAL1#' => $f_charset, '#FIELD#' => $f0['Field'])) . "
";
					$this->arTestVars['iError']++;
					if ($this->force_repair)
						$this->arTestVars['iErrorAutoFix']++;
				}
				elseif ($this->force_repair)
					$arFix[] = ' MODIFY ' . $DB->quote($f0['Field'])
						. ' ' . $f0['Type']
						. ' CHARACTER SET ' . $charset
						. ($f0['Null'] == 'YES' ? ' NULL' : ' NOT NULL')
						. ($f0['Default'] === null ? ($f0['Null'] == 'YES' ? ' DEFAULT NULL ' : '') : ' DEFAULT ' . ($f0['Type'] == 'timestamp' && $f0['Default'] ? $f0['Default'] : '"' . $DB->ForSQL($f0['Default']) . '"'))
						. ' ' . str_ireplace('DEFAULT_GENERATED', '', $f0['Extra'])
						. ($f0['Comment'] ? ' COMMENT '' . $DB->ForSql($f0['Comment']) . ''': '')
					;
			}
			elseif ($collation != $f_collation)
			{
				if ($arExclusion[$table] && strtoupper($f0['Field']) == $arExclusion[$table])
					continue;

				// field collation differs
				if (!$this->fix_mode)
				{
					$strError .= GetMessage('SC_FIELDS_COLLATE_WARN',array('#TABLE#' => $table, '#VAL0#' => $collation, '#VAL1#' => $f_collation, '#FIELD#' => $f0['Field'])) . "
";
					$this->arTestVars['iError']++;
					$this->arTestVars['iErrorAutoFix']++;
				}
				else
					$arFix[] = ' MODIFY ' . $DB->quote($f0['Field'])
						. ' '.$f0['Type']
						. ' COLLATE ' . $collation.($f0['Null'] == 'YES' ? ' NULL' : ' NOT NULL')
						.($f0['Default'] === null ? ($f0['Null'] == 'YES' ? ' DEFAULT NULL ' : '') : ' DEFAULT '.($f0['Type'] == 'timestamp' && $f0['Default'] ? $f0['Default'] : '"'.$DB->ForSQL($f0['Default']).'"'))
						. ' ' . str_ireplace('DEFAULT_GENERATED', '', $f0['Extra'])
						. ($f0['Comment'] ? ' COMMENT '' . $DB->ForSql($f0['Comment']) . ''': '')
					;
			}
		}

		if ($this->fix_mode && count($arFix))
		{
			if (!$DB->Query($sql = 'ALTER TABLE ' . $DB->quote($table) . ' ' . implode(",n", $arFix), true))
			{
				$strError .= $sql . ' [' . $DB->db_Error . ']';
				break;
			}
		}

		if (time()-$time >= $this->timeout)
		{
			$this->arTestVars['last_value'] = $table;
			$this->test_percent = floor($i / $cnt * 100);
			return true;
		}
	}

	if (!$strError)
		return true;

	$this->arTestVars['table_charset_fail'] = true;

	if ($this->fix_mode)
		return $this->Result(false, $strError);
	else
	{
		echo $strError; // to log
		return $this->Result(false, GetMessage('SC_CHECK_TABLES_ERRORS', array('#VAL#' => intval($this->arTestVars['iError']), '#VAL1#' => intval($this->arTestVars['iErrorAutoFix']))) . ($this->arTestVars['iErrorAutoFix'] > 0 ? fix_link() : ''));
	}
}