function check_mysql_table_structure()
{
global $DB;
$strError = '';
if ($this->arTestVars['table_charset_fail'])
return $this->Result(null, GetMessage('SC_TABLE_COLLATION_NA'));
$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();
$rs = $DB->Query('SELECT * FROM b_module WHERE id="'.$DB->ForSQL($module).'"');
if ($rs->Fetch()) // ... and is installed
{
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;
$rs = $DB->Query('SHOW TABLES LIKE "'.$table.'"');
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_TABLE', array('#TABLE#' => $table))."<br>";
$_SESSION['FixQueryList'][] = $sql;
$this->arTestVars['iError']++;
$this->arTestVars['iErrorAutoFix']++;
$this->arTestVars['cntNoTables']++;
continue;
}
}
$arTables[$table] = $sql;
$tmp_table = 'site_checker_'.$table;
$DB->Query('DROP TABLE IF EXISTS `'.$tmp_table.'`');
$DB->Query($regs[1].' `'.$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].' `'.$tmp_table.'`'.$regs[4]);
}
elseif (preg_match('#^INSERT INTO *`?([a-z0-9_]+)`?[^\(]*\(?([^)]*)\)?[^V]*VALUES[^\(]*\((.+)\);?$#mis',$sql,$regs))
{
$table = $regs[1];
if (!$arTables[$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 `'.$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 `'.$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))."<br>";
$_SESSION['FixQueryList'][] = $sql;
$this->arTestVars['iError']++;
$this->arTestVars['iErrorAutoFix']++;
$this->arTestVars['cntNoValues']++;
}
}
}
else
echo "Error parsing SQL:\n".$sql."\n";
}
}
foreach($arTables as $table => $sql)
{
$tmp_table = 'site_checker_'.$table;
$arColumns = array();
$rs = $DB->Query('SHOW COLUMNS FROM `'.$table.'`');
while($f = $rs->Fetch())
$arColumns[strtolower($f['Field'])] = $f;
$rs = $DB->Query('SHOW COLUMNS FROM `'.$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 `'.$table.'` MODIFY `'.$f_tmp['Field'].'` '.$tmp;
if ($this->fix_mode)
{
if (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
{
$_SESSION['FixQueryList'][] = $sql;
$strError .= GetMessage('SC_ERR_FIELD_DIFFERS', array('#TABLE#' => $table, '#FIELD#' => $f['Field'], '#CUR#' => $cur, '#NEW#' => $tmp))."<br>";
$this->arTestVars['iError']++;
if (TableFieldCanBeAltered($f, $f_tmp))
$this->arTestVars['iErrorAutoFix']++;
$this->arTestVars['cntDiffFields']++;
}
}
}
else
{
$sql = 'ALTER TABLE `'.$table.'` ADD `'.$f_tmp['Field'].'` '.str_replace('auto_increment', '' , strtolower($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
{
$_SESSION['FixQueryList'][] = $sql;
$strError .= GetMessage('SC_ERR_NO_FIELD', array('#TABLE#' => $table, '#FIELD#' => $f_tmp['Field']))."<br>";
$this->arTestVars['iError']++;
$this->arTestVars['iErrorAutoFix']++;
$this->arTestVars['cntNoFields']++;
}
}
}
$arIndexes = array();
$rs = $DB->Query('SHOW INDEXES FROM `'.$table.'`');
while($f = $rs->Fetch())
{
$ix =& $arIndexes[$f['Key_name']];
$column = strtolower($f['Column_name'].($f['Sub_part'] ? '('.$f['Sub_part'].')' : ''));
if ($ix)
$ix .= ','.$column;
else
$ix = $column;
}
$arIndexes_tmp = array();
$rs = $DB->Query('SHOW INDEXES FROM `'.$tmp_table.'`');
while($f = $rs->Fetch())
{
$ix =& $arIndexes_tmp[$f['Key_name']];
$column = strtolower($f['Column_name'].($f['Sub_part'] ? '('.$f['Sub_part'].')' : ''));
if ($ix)
$ix .= ','.$column;
else
$ix = $column;
}
unset($ix); // unlink the reference
foreach($arIndexes_tmp as $name => $ix)
{
if (!in_array($ix,$arIndexes))
{
while($arIndexes[$name])
$name .= '_sc';
$sql = $name == 'PRIMARY' ? 'ALTER TABLE `'.$table.'` ADD PRIMARY KEY ('.$ix.')' : 'CREATE INDEX `'.$name.'` ON `'.$table.'` ('.$ix.')';
if ($this->fix_mode)
{
if (!$DB->Query($sql, true))
return $this->Result(false, 'Mysql Query Error: '.$sql.' ['.$DB->db_Error.']');
}
else
{
$_SESSION['FixQueryList'][] = $sql;
$strError .= GetMessage('SC_ERR_NO_INDEX', array('#TABLE#' => $table, '#INDEX#' => $name.' ('.$ix.')'))."<br>";
$this->arTestVars['iError']++;
$this->arTestVars['iErrorAutoFix']++;
$this->arTestVars['cntNoIndexes']++;
}
}
}
$DB->Query('DROP TABLE `'.$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)
{
echo implode(";\n", $_SESSION['FixQueryList']).';';
$_SESSION['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;
}
}
function check_mysql_table_structure()
{
global $DB;
$strError = '';
if ($this->arTestVars['table_charset_fail']) {
return $this->Result(null, GetMessage('SC_TABLE_COLLATION_NA'));
}
$module = '';
$cnt = $i = 0;
if ($dir = opendir($path = $_SERVER['DOCUMENT_ROOT'] . '/bitrix/modules')) {
while (false !== ($item = readdir($dir))) {
if ($item == '.' || $item == '..') {
continue;
}
$cnt++;
if ($this->arTestVars['last_value']) {
$i++;
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)) {
$rs = $DB->Query('SELECT * FROM b_module WHERE id="' . $DB->ForSQL($module) . '"');
if ($rs->Fetch()) {
if (!($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;
}
$arTables[$table] = $sql;
$tmp_table = 'site_checker_' . $table;
$DB->Query('DROP TABLE IF EXISTS `' . $tmp_table . '`');
$DB->Query($regs[1] . ' `' . $tmp_table . '`' . $regs[4]);
} elseif (preg_match('#^(ALTER TABLE)( )?`?([a-z0-9_]+)`?(.*);?$#mis', $sql, $regs)) {
$table = $regs[3];
$tmp_table = 'site_checker_' . $table;
$DB->Query($regs[1] . ' `' . $tmp_table . '`' . $regs[4]);
}
}
foreach ($arTables as $table => $sql) {
$rs = $DB->Query('SHOW TABLES LIKE "' . $table . '"');
if (!$rs->Fetch()) {
if ($this->fix_mode) {
if (!$DB->Query($sql)) {
return $this->Result(false, 'Mysql Query Error: ' . $sql . ' [' . $DB->db_Error . ']');
}
} else {
$strError .= GetMessage('SC_ERR_NO_TABLE', array('#TABLE#' => $table)) . "<br>\n";
$this->arTestVars['iError']++;
$this->arTestVars['iErrorAutoFix']++;
$this->arTestVars['cntNoTables']++;
}
continue;
}
$tmp_table = 'site_checker_' . $table;
$arColumns = array();
$rs = $DB->Query('SHOW COLUMNS FROM `' . $table . '`');
while ($f = $rs->Fetch()) {
$arColumns[$f['Field']] = $f;
}
$rs = $DB->Query('SHOW COLUMNS FROM `' . $tmp_table . '`');
while ($f_tmp = $rs->Fetch()) {
$tmp = TableFieldConstruct($f_tmp);
if ($f = $arColumns[$f_tmp['Field']]) {
if (($cur = TableFieldConstruct($f)) != $tmp) {
if ($this->fix_mode) {
if (TableFieldCanBeAltered($f, $f_tmp)) {
if (!$DB->Query($sql = 'ALTER TABLE `' . $table . '` MODIFY `' . $f_tmp['Field'] . '` ' . $tmp, true)) {
return $this->Result(false, 'Mysql Query Error: ' . $sql . ' [' . $DB->db_Error . ']');
}
} else {
$this->arTestVars['iErrorFix']++;
}
} else {
$strError .= GetMessage('SC_ERR_FIELD_DIFFERS', array('#TABLE#' => $table, '#FIELD#' => $f['Field'], '#CUR#' => $cur, '#NEW#' => $tmp)) . "<br>\n";
$this->arTestVars['iError']++;
if (TableFieldCanBeAltered($f, $f_tmp)) {
$this->arTestVars['iErrorAutoFix']++;
}
$this->arTestVars['cntDiffFields']++;
}
}
} else {
if ($this->fix_mode) {
if (!$DB->Query($sql = 'ALTER TABLE `' . $table . '` ADD `' . $f_tmp['Field'] . '` ' . $tmp, true)) {
return $this->Result(false, 'Mysql Query Error: ' . $sql . ' [' . $DB->db_Error . ']');
}
} else {
$strError .= GetMessage('SC_ERR_NO_FIELD', array('#TABLE#' => $table, '#FIELD#' => $f_tmp['Field'])) . "<br>\n";
$this->arTestVars['iError']++;
$this->arTestVars['iErrorAutoFix']++;
$this->arTestVars['cntNoFields']++;
}
}
}
$arIndexes = array();
$rs = $DB->Query('SHOW INDEXES FROM `' . $table . '`');
while ($f = $rs->Fetch()) {
$ix =& $arIndexes[$f['Key_name']];
$column = $f['Column_name'] . ($f['Sub_part'] ? '(' . $f['Sub_part'] . ')' : '');
if ($ix) {
$ix .= ',' . $column;
} else {
$ix = $column;
}
}
$arIndexes_tmp = array();
$rs = $DB->Query('SHOW INDEXES FROM `' . $tmp_table . '`');
while ($f = $rs->Fetch()) {
$ix =& $arIndexes_tmp[$f['Key_name']];
$column = $f['Column_name'] . ($f['Sub_part'] ? '(' . $f['Sub_part'] . ')' : '');
if ($ix) {
$ix .= ',' . $column;
} else {
$ix = $column;
}
}
unset($ix);
// unlink the reference
foreach ($arIndexes_tmp as $name => $ix) {
if (!in_array($ix, $arIndexes)) {
if ($this->fix_mode) {
while ($arIndexes[$name]) {
$name .= '_sc';
}
if (!$DB->Query($sql = 'CREATE INDEX `' . $name . '` ON `' . $table . '` (' . $ix . ')', true)) {
return $this->Result(false, 'Mysql Query Error: ' . $sql . ' [' . $DB->db_Error . ']');
}
} else {
$strError .= GetMessage('SC_ERR_NO_INDEX', array('#TABLE#' => $table, '#INDEX#' => $name . ' (' . $ix . ')')) . "<br>\n";
$this->arTestVars['iError']++;
$this->arTestVars['iErrorAutoFix']++;
$this->arTestVars['cntNoIndexes']++;
}
}
}
$DB->Query('DROP TABLE `' . $tmp_table . '`');
}
echo $strError;
// to log
}
}
if ($i < $cnt) {
$this->arTestVars['last_value'] = $module;
$this->test_percent = floor($i / $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) {
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']))) . ($this->arTestVars['iErrorAutoFix'] > 0 ? fix_link(3) : ''));
}
return true;
}
}
