CSiteCheckerTest::check_mysql_table_structure

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

	$arInsertExclude = array(
		'b_seo_search_engine' => 1,
		'b_hot_keys_code' => 1,
	);

	if ($this->arTestVars['table_charset_fail'])
		return $this->Result(null, GetMessage('SC_TABLE_COLLATION_NA'));

	$DB->Query("SET SQL_MODE=''");

	$f = $DB->Query('SELECT VERSION() v')->Fetch();
	$this->db_ver = $f['v'];

	$module = '';
	$cnt = $iCurrent = 0;
	if ($dir = opendir($path = $_SERVER['DOCUMENT_ROOT'].'/bitrix/modules'))
	{
		while(false !== ($item = readdir($dir)))
		{
//				if ($item == '.' || $item == '..')
			if (strpos($item, '.') !== false) // skipping all external modules
				continue;

			$cnt++;

			if ($this->arTestVars['last_value'])
			{
				$iCurrent++;
				if ($this->arTestVars['last_value'] == $item)
					unset($this->arTestVars['last_value']);
			}
			elseif (!$module)
				$module = $item;
		}
		closedir($dir);
	}
	else
		return false;

	$file = $_SERVER['DOCUMENT_ROOT'].'/bitrix/modules/'.$module.'/install/db/mysql/install.sql';
	if (!file_exists($file))
		$file = $_SERVER['DOCUMENT_ROOT'].'/bitrix/modules/'.$module.'/install/mysql/install.sql';
	if (file_exists($file)) // uses database...
	{
		$arTableColumns = array();
		$bModuleInstalled = $DB->Query('SELECT * FROM b_module WHERE id="'.$DB->ForSQL($module).'"')->Fetch();

		if (false === ($query = file_get_contents($file)))
			return false;

		$arTables = array();
		$arQuery = $DB->ParseSQLBatch(str_replace("r", "", $query));
		foreach($arQuery as $sql)
		{
			if (preg_match('#^(CREATE TABLE )(IF NOT EXISTS)? *`?([a-z0-9_]+)`?(.*);?$#mis', $sql, $regs))
			{
				$table = $regs[3];
				if (preg_match('#^site_checker_#', $table))
					continue;

				$bTableExists = $DB->Query('SHOW TABLES LIKE "'.$table.'"')->Fetch();
				if (!$bTableExists && $bModuleInstalled)
				{
					if ($this->fix_mode)
					{
						if (!$DB->Query($sql, true))
							return $this->Result(false, 'Mysql Query Error: '.$sql.' ['.$DB->db_Error.']');
					}
					else
					{
						$strError .= GetMessage('SC_ERR_NO_TABLE', array('#TABLE#' => $table))."
";
						BitrixMainApplication::getInstance()->getSession()['FixQueryList'][] = $sql;
						$this->arTestVars['iError']++;
						$this->arTestVars['iErrorAutoFix']++;
						$this->arTestVars['cntNoTables']++;
						continue;
					}
				}

				if ($bTableExists || $bModuleInstalled)
				{
					$arTables[$table] = $sql;
					$tmp_table = 'site_checker_'.$table;
					$DB->Query('DROP TABLE IF EXISTS ' . $DB->quote($tmp_table));
					$DB->Query($regs[1].' ' . $DB->quote($tmp_table) . $regs[4]);
				}
			}
			elseif (preg_match('#^(ALTER TABLE)( )?`?([a-z0-9_]+)`?(.*);?$#mis', $sql, $regs))
			{
				$table = $regs[3];
				if (!$arTables[$table])
					continue;
				$tmp_table = 'site_checker_'.$table;
				$DB->Query($regs[1] . ' ' . $DB->quote($tmp_table) . $regs[4]);
			}
			elseif (preg_match('#^INSERT INTO *`?([a-z0-9_]+)`?[^(]*(?([^)]*))?[^V]*VALUES[^(]*((.+));?$#mis',$sql,$regs))
			{
				$table = $regs[1];
				if (!$arTables[$table] || $arInsertExclude[$table])
					continue;
				$tmp_table = 'site_checker_'.$table;

				if ($regs[2])
					$arColumns = explode(',', $regs[2]);
				else
				{
					if (!$arTableColumns[$tmp_table])
					{
						$rs = $DB->Query('SHOW COLUMNS FROM ' . $DB->quote($tmp_table));
						while($f = $rs->Fetch())
							$arTableColumns[$tmp_table][] = $f['Field'];
					}
					$arColumns = $arTableColumns[$tmp_table];
				}

				$strValues = $regs[3];
				$ar = explode(",",$strValues);
				$arValues = array();
				$i = 0;
				$str = '';
				foreach($ar as $v)
				{
					$str .= ($str ? ',' : '').$v;
					if (preg_match('#^ *(-?[0-9]+|'.*'|".*"|null|now()) *$#i',$str))
					{
						$arValues[$i] = $str;
						$str = '';
						$i++;
					}
				}

				if (!$str)
				{
					$sqlSelect = 'SELECT * FROM ' . $DB->quote($table) . ' WHERE 1=1 ';
					foreach($arColumns as $k => $c)
					{
						$v = $arValues[$k];
						if (!preg_match('#null|now()#i',$v))
							$sqlSelect .= ' AND '.$c.'='.$v;
					}
					$rs = $DB->Query($sqlSelect);
					if (!$rs->Fetch())
					{
						if ($this->fix_mode)
						{
							if (!$DB->Query($sql, true))
								return $this->Result(false, 'Mysql Query Error: '.$sql.' ['.$DB->db_Error.']');
						}
						else
						{
							$strError .= GetMessage('SC_ERR_NO_VALUE', array('#TABLE#' => $table, '#SQL#' => $sql))."
";
							BitrixMainApplication::getInstance()->getSession()['FixQueryList'][] = $sql;
							$this->arTestVars['iError']++;
							$this->arTestVars['iErrorAutoFix']++;
							$this->arTestVars['cntNoValues']++;
						}
					}
				}
				else
					echo "Error parsing SQL:n".$sql."n";
			}
		}

		if (file_exists($file = str_replace('/install.sql', '/install_ft.sql', $file)))
		{
			if (false === ($query = file_get_contents($file)))
				return false;
			$query = preg_replace('# on +([a-z_0-9]+) (#i', ' on site_checker_\1 (', $query);
			$arQuery = $DB->ParseSQLBatch(str_replace("r", "", $query));
			foreach($arQuery as $sql)
			{
				if (!$DB->Query($sql, true))
					break;
			}
		}

		foreach($arTables as $table => $sql)
		{
			$tmp_table = 'site_checker_'.$table;
			$arIndexes = array();
			$rs = $DB->Query('SHOW INDEXES FROM ' . $DB->quote($table));
			while($f = $rs->Fetch())
			{
				$column = strtolower($f['Column_name'].($f['Sub_part']? '('.$f['Sub_part'].')' : ''));
				if ($arIndexes[$f['Key_name']])
					$arIndexes[$f['Key_name']] .= ','.$column;
				else
					$arIndexes[$f['Key_name']] = $column;
			}

			$arIndexes_tmp = array();
			$arFT = array();
			$rs = $DB->Query('SHOW INDEXES FROM ' . $DB->quote($tmp_table));
			while($f = $rs->Fetch())
			{
				$column = strtolower($f['Column_name'].($f['Sub_part']? '('.$f['Sub_part'].')' : ''));
				if ($arIndexes_tmp[$f['Key_name']])
					$arIndexes_tmp[$f['Key_name']] .= ','.$column;
				else
					$arIndexes_tmp[$f['Key_name']] = $column;
				if ($f['Index_type'] == 'FULLTEXT')
					$arFT[$f['Key_name']] = true;
			}

			foreach($arIndexes_tmp as $name => $ix)
			{
				if (!in_array($ix,$arIndexes))
				{
					if($arIndexes[$name])
					{
						if ($name == 'PRIMARY') // dropping primary is not supported
							continue;

						$sql = 'ALTER TABLE ' . $DB->quote($table) . ' DROP INDEX ' . $DB->quote($name);
						if ($this->fix_mode)
						{
							if (!$DB->Query($sql, true))
								return $this->Result(false, 'Mysql Query Error: '.$sql.' ['.$DB->db_Error.']');
						}
						else
						{
							BitrixMainApplication::getInstance()->getSession()['FixQueryList'][] = $sql;
							$this->arTestVars['iError']++;
							$this->arTestVars['iErrorAutoFix']++;
						}
					}
				}
			}

			$arColumns = array();
			$rs = $DB->Query('SHOW COLUMNS FROM ' . $DB->quote($table));
			while($f = $rs->Fetch())
				$arColumns[strtolower($f['Field'])] = $f;

			$rs = $DB->Query('SHOW COLUMNS FROM ' . $DB->quote($tmp_table));
			while($f_tmp = $rs->Fetch())
			{
				$tmp = TableFieldConstruct($f_tmp);
				if ($f = $arColumns[strtolower($f_tmp['Field'])])
				{
					if (($cur = TableFieldConstruct($f)) != $tmp)
					{
						$sql = 'ALTER TABLE ' . $DB->quote($table) . ' CHANGE ' . $DB->quote($f['Field']) . ' '.$tmp;
						if ($this->fix_mode)
						{
							if ($this->TableFieldCanBeAltered($f, $f_tmp))
							{
								if (!$DB->Query($sql, true))
									return $this->Result(false, 'Mysql Query Error: '.$sql.' ['.$DB->db_Error.']');
							}
							else
								$this->arTestVars['iErrorFix']++;
						}
						else
						{
							BitrixMainApplication::getInstance()->getSession()['FixQueryList'][] = $sql;
							$strError .= GetMessage('SC_ERR_FIELD_DIFFERS', array('#TABLE#' => $table, '#FIELD#' => $f['Field'], '#CUR#' => $cur, '#NEW#' => $tmp))."
";
							$this->arTestVars['iError']++;
							if ($this->TableFieldCanBeAltered($f, $f_tmp))
								$this->arTestVars['iErrorAutoFix']++;
							$this->arTestVars['cntDiffFields']++;
						}
					}
				}
				else
				{
					$sql = 'ALTER TABLE ' . $DB->quote($table) . ' ADD ' . preg_replace('#auto_increment#i', '' , $tmp); // if only Primary Key is missing we will have to pass the test twice
					if ($this->fix_mode)
					{
						if (!$DB->Query($sql, true))
							return $this->Result(false, 'Mysql Query Error: '.$sql.' ['.$DB->db_Error.']');
					}
					else
					{
						BitrixMainApplication::getInstance()->getSession()['FixQueryList'][] = $sql;
						$strError .= GetMessage('SC_ERR_NO_FIELD', array('#TABLE#' => $table, '#FIELD#' => $f_tmp['Field']))."
";
						$this->arTestVars['iError']++;
						$this->arTestVars['iErrorAutoFix']++;
						$this->arTestVars['cntNoFields']++;
					}
				}
			}

			foreach($arIndexes_tmp as $name => $ix)
			{
				if (!in_array($ix,$arIndexes))
				{
					if ($name == 'PRIMARY' && $arIndexes['PRIMARY']) // Primary key exists
					{
						$this->arTestVars['iError']++;
						$strError .= GetMessage('SC_ERR_NO_INDEX', array('#TABLE#' => $table, '#INDEX#' => $name.' ('.$ix.')'))."
";
						continue;
					}

					$sql = $name == 'PRIMARY' ? 'ALTER TABLE ' . $DB->quote($table) . ' ADD PRIMARY KEY ('.$ix.')' : 'CREATE '.($arFT[$name] ? 'FULLTEXT ' : '').'INDEX ' . $DB->quote($name) .' ON ' . $DB->quote($table) . ' ('.$ix.')';
					if ($this->fix_mode)
					{
						if (!$DB->Query($sql, true))
							return $this->Result(false, 'Mysql Query Error: '.$sql.' ['.$DB->db_Error.']');
					}
					else
					{
						BitrixMainApplication::getInstance()->getSession()['FixQueryList'][] = $sql;
						$strError .= GetMessage('SC_ERR_NO_INDEX', array('#TABLE#' => $table, '#INDEX#' => $name.' ('.$ix.')'))."
";
						$this->arTestVars['iError']++;
						$this->arTestVars['iErrorAutoFix']++;
						$this->arTestVars['cntNoIndexes']++;
					}
				}
			}

			$DB->Query('DROP TABLE ' . $DB->quote($tmp_table));
		}

		echo $strError; // to log
	}

	if ($iCurrent < $cnt) // partial
	{
		$this->arTestVars['last_value'] = $module;
		$this->test_percent = floor($iCurrent / $cnt * 100);
		return true;
	}

	if ($this->fix_mode)
	{
		if ($this->arTestVars['iErrorFix'] > 0)
			return $this->Result(null, GetMessage('SC_CHECK_TABLES_STRUCT_ERRORS_FIX',
				array(
					'#VAL#' => intval($this->arTestVars['iErrorFix']),
				)));
		return true;
	}
	else
	{
		if ($this->arTestVars['iError'] > 0)
		{
			if (is_array(BitrixMainApplication::getInstance()->getSession()['FixQueryList']) && count(BitrixMainApplication::getInstance()->getSession()['FixQueryList']))
				echo implode(";n", BitrixMainApplication::getInstance()->getSession()['FixQueryList']).';';
			BitrixMainApplication::getInstance()->getSession()['FixQueryList'] = array();
			return $this->Result(false, GetMessage('SC_CHECK_TABLES_STRUCT_ERRORS',
				array(
					'#VAL#' => intval($this->arTestVars['iError']),
					'#VAL1#' => intval($this->arTestVars['iErrorAutoFix']),
					'#NO_TABLES#' => intval($this->arTestVars['cntNoTables']),
					'#NO_FIELDS#' => intval($this->arTestVars['cntNoFields']),
					'#DIFF_FIELDS#' => intval($this->arTestVars['cntDiffFields']),
					'#NO_INDEXES#' => intval($this->arTestVars['cntNoIndexes']),
					'#NO_VALUES#' => intval($this->arTestVars['cntNoValues']),
				)).($this->arTestVars['iErrorAutoFix'] > 0 ? fix_link(3) : ''));
		}
		return true;
	}
}