public static function DBCheckFormat()
{
$aErrors = array();
$aSugFix = array();
// A new way of representing things to be done - quicker to execute !
$aCreateTable = array();
// array of <table> => <table options>
$aCreateTableItems = array();
// array of <table> => array of <create definition>
$aAlterTableItems = array();
// array of <table> => <alter specification>
foreach (self::GetClasses() as $sClass) {
if (!self::HasTable($sClass)) {
continue;
}
// Check that the table exists
//
$sTable = self::DBGetTable($sClass);
$sKeyField = self::DBGetKey($sClass);
$sAutoIncrement = self::IsAutoIncrementKey($sClass) ? "AUTO_INCREMENT" : "";
$sKeyFieldDefinition = "`{$sKeyField}` INT(11) NOT NULL {$sAutoIncrement} PRIMARY KEY";
if (!CMDBSource::IsTable($sTable)) {
$aErrors[$sClass]['*'][] = "table '{$sTable}' could not be found into the DB";
$aSugFix[$sClass]['*'][] = "CREATE TABLE `{$sTable}` ({$sKeyFieldDefinition}) ENGINE = " . MYSQL_ENGINE . " CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$aCreateTable[$sTable] = "ENGINE = " . MYSQL_ENGINE . " CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$aCreateTableItems[$sTable][$sKeyField] = $sKeyFieldDefinition;
} elseif (!CMDBSource::IsField($sTable, $sKeyField)) {
$aErrors[$sClass]['id'][] = "key '{$sKeyField}' (table {$sTable}) could not be found";
$aSugFix[$sClass]['id'][] = "ALTER TABLE `{$sTable}` ADD {$sKeyFieldDefinition}";
if (!array_key_exists($sTable, $aCreateTable)) {
$aAlterTableItems[$sTable][$sKeyField] = "ADD {$sKeyFieldDefinition}";
}
} else {
// Check the key field properties
//
if (!CMDBSource::IsKey($sTable, $sKeyField)) {
$aErrors[$sClass]['id'][] = "key '{$sKeyField}' is not a key for table '{$sTable}'";
$aSugFix[$sClass]['id'][] = "ALTER TABLE `{$sTable}`, DROP PRIMARY KEY, ADD PRIMARY key(`{$sKeyField}`)";
if (!array_key_exists($sTable, $aCreateTable)) {
$aAlterTableItems[$sTable][$sKeyField] = "CHANGE `{$sKeyField}` {$sKeyFieldDefinition}";
}
}
if (self::IsAutoIncrementKey($sClass) && !CMDBSource::IsAutoIncrement($sTable, $sKeyField)) {
$aErrors[$sClass]['id'][] = "key '{$sKeyField}' (table {$sTable}) is not automatically incremented";
$aSugFix[$sClass]['id'][] = "ALTER TABLE `{$sTable}` CHANGE `{$sKeyField}` {$sKeyFieldDefinition}";
if (!array_key_exists($sTable, $aCreateTable)) {
$aAlterTableItems[$sTable][$sKeyField] = "CHANGE `{$sKeyField}` {$sKeyFieldDefinition}";
}
}
}
// Check that any defined field exists
//
$aTableInfo = CMDBSource::GetTableInfo($sTable);
$aTableInfo['Fields'][$sKeyField]['used'] = true;
foreach (self::ListAttributeDefs($sClass) as $sAttCode => $oAttDef) {
// Skip this attribute if not originaly defined in this class
if (self::$m_aAttribOrigins[$sClass][$sAttCode] != $sClass) {
continue;
}
foreach ($oAttDef->GetSQLColumns(true) as $sField => $sDBFieldSpec) {
// Keep track of columns used by iTop
$aTableInfo['Fields'][$sField]['used'] = true;
$bIndexNeeded = $oAttDef->RequiresIndex();
$sFieldDefinition = "`{$sField}` {$sDBFieldSpec}";
if (!CMDBSource::IsField($sTable, $sField)) {
$aErrors[$sClass][$sAttCode][] = "field '{$sField}' could not be found in table '{$sTable}'";
$aSugFix[$sClass][$sAttCode][] = "ALTER TABLE `{$sTable}` ADD {$sFieldDefinition}";
if ($bIndexNeeded) {
$aSugFix[$sClass][$sAttCode][] = "ALTER TABLE `{$sTable}` ADD INDEX (`{$sField}`)";
}
if (array_key_exists($sTable, $aCreateTable)) {
$aCreateTableItems[$sTable][$sField] = $sFieldDefinition;
if ($bIndexNeeded) {
$aCreateTableItems[$sTable][] = "INDEX (`{$sField}`)";
}
} else {
$aAlterTableItems[$sTable][$sField] = "ADD {$sFieldDefinition}";
if ($bIndexNeeded) {
$aAlterTableItems[$sTable][] = "ADD INDEX (`{$sField}`)";
}
}
} else {
// The field already exists, does it have the relevant properties?
//
$bToBeChanged = false;
$sActualFieldSpec = CMDBSource::GetFieldSpec($sTable, $sField);
if (strcasecmp($sDBFieldSpec, $sActualFieldSpec) != 0) {
$bToBeChanged = true;
$aErrors[$sClass][$sAttCode][] = "field '{$sField}' in table '{$sTable}' has a wrong type: found '{$sActualFieldSpec}' while expecting '{$sDBFieldSpec}'";
}
if ($bToBeChanged) {
$aSugFix[$sClass][$sAttCode][] = "ALTER TABLE `{$sTable}` CHANGE `{$sField}` {$sFieldDefinition}";
$aAlterTableItems[$sTable][$sField] = "CHANGE `{$sField}` {$sFieldDefinition}";
}
// Create indexes (external keys only... so far)
//
if ($bIndexNeeded && !CMDBSource::HasIndex($sTable, $sField, array($sField))) {
$aErrors[$sClass][$sAttCode][] = "Foreign key '{$sField}' in table '{$sTable}' should have an index";
if (CMDBSource::HasIndex($sTable, $sField)) {
$aSugFix[$sClass][$sAttCode][] = "ALTER TABLE `{$sTable}` DROP INDEX `{$sField}`, ADD INDEX (`{$sField}`)";
$aAlterTableItems[$sTable][] = "DROP INDEX `{$sField}`";
$aAlterTableItems[$sTable][] = "ADD INDEX (`{$sField}`)";
} else {
$aSugFix[$sClass][$sAttCode][] = "ALTER TABLE `{$sTable}` ADD INDEX (`{$sField}`)";
$aAlterTableItems[$sTable][] = "ADD INDEX (`{$sField}`)";
}
}
}
}
}
// Check indexes
foreach (self::DBGetIndexes($sClass) as $aColumns) {
$sIndexId = implode('_', $aColumns);
if (!CMDBSource::HasIndex($sTable, $sIndexId, $aColumns)) {
$sColumns = "`" . implode("`, `", $aColumns) . "`";
if (CMDBSource::HasIndex($sTable, $sIndexId)) {
$aErrors[$sClass]['*'][] = "Wrong index '{$sIndexId}' ({$sColumns}) in table '{$sTable}'";
$aSugFix[$sClass]['*'][] = "ALTER TABLE `{$sTable}` DROP INDEX `{$sIndexId}`, ADD INDEX `{$sIndexId}` ({$sColumns})";
} else {
$aErrors[$sClass]['*'][] = "Missing index '{$sIndexId}' ({$sColumns}) in table '{$sTable}'";
$aSugFix[$sClass]['*'][] = "ALTER TABLE `{$sTable}` ADD INDEX `{$sIndexId}` ({$sColumns})";
}
if (array_key_exists($sTable, $aCreateTable)) {
$aCreateTableItems[$sTable][] = "INDEX `{$sIndexId}` ({$sColumns})";
} else {
if (CMDBSource::HasIndex($sTable, $sIndexId)) {
$aAlterTableItems[$sTable][] = "DROP INDEX `{$sIndexId}`";
}
$aAlterTableItems[$sTable][] = "ADD INDEX `{$sIndexId}` ({$sColumns})";
}
}
}
// Find out unused columns
//
foreach ($aTableInfo['Fields'] as $sField => $aFieldData) {
if (!isset($aFieldData['used']) || !$aFieldData['used']) {
$aErrors[$sClass]['*'][] = "Column '{$sField}' in table '{$sTable}' is not used";
if (!CMDBSource::IsNullAllowed($sTable, $sField)) {
// Allow null values so that new record can be inserted
// without specifying the value of this unknown column
$sFieldDefinition = "`{$sField}` " . CMDBSource::GetFieldType($sTable, $sField) . ' NULL';
$aSugFix[$sClass][$sAttCode][] = "ALTER TABLE `{$sTable}` CHANGE `{$sField}` {$sFieldDefinition}";
$aAlterTableItems[$sTable][$sField] = "CHANGE `{$sField}` {$sFieldDefinition}";
}
}
}
}
$aCondensedQueries = array();
foreach ($aCreateTable as $sTable => $sTableOptions) {
$sTableItems = implode(', ', $aCreateTableItems[$sTable]);
$aCondensedQueries[] = "CREATE TABLE `{$sTable}` ({$sTableItems}) {$sTableOptions}";
}
foreach ($aAlterTableItems as $sTable => $aChangeList) {
$sChangeList = implode(', ', $aChangeList);
$aCondensedQueries[] = "ALTER TABLE `{$sTable}` {$sChangeList}";
}
return array($aErrors, $aSugFix, $aCondensedQueries);
}
