Пример #1
0
 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);
 }