/**
* Handler called after the creation/update of the database schema
* @param $oConfiguration Config The new configuration of the application
* @param $sPreviousVersion string PRevious version number of the module (empty string in case of first install)
* @param $sCurrentVersion string Current version number of the module
*/
public static function AfterDatabaseCreation(Config $oConfiguration, $sPreviousVersion, $sCurrentVersion)
{
// Bug #464 - start_date was both in Ticket and Change tables
//
$sSourceTable = 'change';
$sSourceKeyField = 'id';
$sTargetTable = 'ticket';
$sTargetKeyField = 'id';
$sField = 'start_date';
if (CMDBSource::IsField($sSourceTable, $sField) && CMDBSource::IsField($sTargetTable, $sField) && CMDBSource::IsField($sSourceTable, $sSourceKeyField) && CMDBSource::IsField($sTargetTable, $sTargetKeyField)) {
SetupPage::log_info("Issue #464 - Copying change/start_date into ticket/start_date");
$sRepair = "UPDATE `{$sTargetTable}`, `{$sSourceTable}` SET `{$sTargetTable}`.`{$sField}` = `{$sSourceTable}`.`{$sField}` WHERE `{$sTargetTable}`.`{$sField}` IS NULL AND`{$sTargetTable}`.`{$sTargetKeyField}` = `{$sSourceTable}`.`{$sSourceKeyField}`";
CMDBSource::Query($sRepair);
}
}
/**
* Checks if the data source definition is consistent with the schema of the target class
* @param $bDiagnostics boolean True to only diagnose the consistency, false to actually apply some changes
* @param $bVerbose boolean True to get some information in the std output (echo)
* @return bool Whether or not the database needs fixing for this data source
*/
public function CheckDBConsistency($bDiagnostics, $bVerbose, $oChange = null)
{
$bFixNeeded = false;
$aMissingFields = array();
$oAttributeSet = $this->Get('attribute_list');
$aAttributes = array();
while ($oAttribute = $oAttributeSet->Fetch()) {
$sAttCode = $oAttribute->Get('attcode');
if (MetaModel::IsValidAttCode($this->GetTargetClass(), $sAttCode)) {
$aAttributes[$sAttCode] = $oAttribute;
} else {
// Old field remaining
if ($bVerbose) {
echo "Irrelevant field description for the field '{$sAttCode}', for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . "), will be removed.\n";
}
$bFixNeeded = true;
if (!$bDiagnostics) {
if ($oChange == null) {
$oChange = MetaModel::NewObject("CMDBChange");
$oChange->Set("date", time());
$sUserString = CMDBChange::GetCurrentUserName();
$oChange->Set("userinfo", $sUserString);
$oChange->DBInsert();
}
// Fix the issue
$oAttribute->DBDeleteTracked($oChange);
}
}
}
$sTable = $this->GetDataTable();
foreach ($this->ListTargetAttributes() as $sAttCode => $oAttDef) {
if (!isset($aAttributes[$sAttCode])) {
$bFixNeeded = true;
$aMissingFields[] = $sAttCode;
// New field missing...
if ($bVerbose) {
echo "Missing field description for the field '{$sAttCode}', for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . "), will be created with default values.\n";
}
if (!$bDiagnostics) {
if ($oChange == null) {
$oChange = MetaModel::NewObject("CMDBChange");
$oChange->Set("date", time());
$sUserString = CMDBChange::GetCurrentUserName();
$oChange->Set("userinfo", $sUserString);
$oChange->DBInsert();
}
// Fix the issue
$oAttribute = $this->CreateSynchroAtt($sAttCode);
$oAttribute->DBInsertTracked($oChange);
}
} else {
$aColumns = $this->GetSQLColumns(array($sAttCode));
foreach ($aColumns as $sColName => $sColumnDef) {
$bOneColIsMissing = false;
if (!CMDBSource::IsField($sTable, $sColName)) {
$bFixNeeded = true;
$bOneColIsMissing = true;
if ($bVerbose) {
if (count($aColumns) > 1) {
echo "Missing column '{$sColName}', in the table '{$sTable}' for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . "). The columns '" . implode("', '", $aColumns) . " will be re-created.'.\n";
} else {
echo "Missing column '{$sColName}', in the table '{$sTable}' for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . "). The column '{$sColName}' will be added.\n";
}
}
} else {
if (strcasecmp(CMDBSource::GetFieldType($sTable, $sColName), $sColumnDef) != 0) {
$bFixNeeded = true;
$bOneColIsMissing = true;
if (count($aColumns) > 1) {
echo "Incorrect column '{$sColName}' (" . CMDBSource::GetFieldType($sTable, $sColName) . " instead of " . $sColumnDef . "), in the table '{$sTable}' for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . "). The columns '" . implode("', '", $aColumns) . " will be re-created.'.\n";
} else {
echo "Incorrect column '{$sColName}' (" . CMDBSource::GetFieldType($sTable, $sColName) . " instead of " . $sColumnDef . "), in the table '{$sTable}' for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . "). The column '{$sColName}' will be added.\n";
}
}
}
if ($bOneColIsMissing) {
$aMissingFields[] = $sAttCode;
}
}
}
}
if ($bFixNeeded && count($aMissingFields) > 0) {
$aRepairQueries = array();
// The structure of the table needs adjusting
$aColumns = $this->GetSQLColumns($aMissingFields);
$aFieldDefs = array();
foreach ($aColumns as $sAttCode => $sColumnDef) {
if (CMDBSource::IsField($sTable, $sAttCode)) {
$aRepairQueries[] = "ALTER TABLE `{$sTable}` CHANGE `{$sAttCode}` `{$sAttCode}` {$sColumnDef}";
} else {
$aFieldDefs[] = "`{$sAttCode}` {$sColumnDef}";
}
}
if (count($aFieldDefs) > 0) {
$aRepairQueries[] = "ALTER TABLE `{$sTable}` ADD (" . implode(',', $aFieldDefs) . ");";
}
// The triggers as well must be adjusted
$aTriggersDefs = $this->GetTriggersDefinition();
$aRepairQueries[] = "DROP TRIGGER IF EXISTS `{$sTable}_bi`;";
$aRepairQueries[] = $aTriggersDefs['bi'];
$aRepairQueries[] = "DROP TRIGGER IF EXISTS `{$sTable}_bu`;";
$aRepairQueries[] = $aTriggersDefs['bu'];
$aRepairQueries[] = "DROP TRIGGER IF EXISTS `{$sTable}_ad`;";
$aRepairQueries[] = $aTriggersDefs['ad'];
if ($bDiagnostics) {
if ($bVerbose) {
// Report the issue
echo "The structure of the table {$sTable} for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . ") must be altered (missing or incorrect fields: " . implode(',', $aMissingFields) . ").\n";
echo "The trigger {$sTable}_bi, {$sTable}_bu, {$sTable}_ad for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . ") must be re-created.\n";
echo implode("\n", $aRepairQueries) . "\n";
}
} else {
// Fix the issue
foreach ($aRepairQueries as $sSQL) {
CMDBSource::Query($sSQL);
if ($bVerbose) {
echo "{$sSQL}\n";
}
}
}
}
return $bFixNeeded;
}
/**
* Checks if the data source definition is consistent with the schema of the target class
* @param $bDiagnostics boolean True to only diagnose the consistency, false to actually apply some changes
* @param $bVerbose boolean True to get some information in the std output (echo)
* @return bool Whether or not the database needs fixing for this data source
*/
public function CheckDBConsistency($bDiagnostics, $bVerbose, $oChange = null)
{
$bFixNeeded = false;
$bTriggerRebuildNeeded = false;
$aMissingFields = array();
$oAttributeSet = $this->Get('attribute_list');
$aAttributes = array();
while ($oAttribute = $oAttributeSet->Fetch()) {
$sAttCode = $oAttribute->Get('attcode');
if (MetaModel::IsValidAttCode($this->GetTargetClass(), $sAttCode)) {
$aAttributes[$sAttCode] = $oAttribute;
} else {
// Old field remaining
if ($bVerbose) {
echo "Irrelevant field description for the field '{$sAttCode}', for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . "), will be removed.\n";
}
$bFixNeeded = true;
if (!$bDiagnostics) {
// Fix the issue
$oAttribute->DBDelete();
}
}
}
$sTable = $this->GetDataTable();
foreach ($this->ListTargetAttributes() as $sAttCode => $oAttDef) {
if (!isset($aAttributes[$sAttCode])) {
$bFixNeeded = true;
$aMissingFields[] = $sAttCode;
// New field missing...
if ($bVerbose) {
echo "Missing field description for the field '{$sAttCode}', for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . "), will be created with default values.\n";
}
if (!$bDiagnostics) {
// Fix the issue
$oAttribute = $this->CreateSynchroAtt($sAttCode);
$oAttribute->DBInsert();
}
} else {
$aColumns = $this->GetSQLColumns(array($sAttCode));
foreach ($aColumns as $sColName => $sColumnDef) {
$bOneColIsMissing = false;
if (!CMDBSource::IsField($sTable, $sColName)) {
$bFixNeeded = true;
$bOneColIsMissing = true;
if ($bVerbose) {
if (count($aColumns) > 1) {
echo "Missing column '{$sColName}', in the table '{$sTable}' for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . "). The columns '" . implode("', '", $aColumns) . " will be re-created.'.\n";
} else {
echo "Missing column '{$sColName}', in the table '{$sTable}' for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . "). The column '{$sColName}' will be added.\n";
}
}
} else {
if (strcasecmp(CMDBSource::GetFieldType($sTable, $sColName), $sColumnDef) != 0) {
$bFixNeeded = true;
$bOneColIsMissing = true;
if (count($aColumns) > 1) {
echo "Incorrect column '{$sColName}' (" . CMDBSource::GetFieldType($sTable, $sColName) . " instead of " . $sColumnDef . "), in the table '{$sTable}' for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . "). The columns '" . implode("', '", $aColumns) . " will be re-created.'.\n";
} else {
echo "Incorrect column '{$sColName}' (" . CMDBSource::GetFieldType($sTable, $sColName) . " instead of " . $sColumnDef . "), in the table '{$sTable}' for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . "). The column '{$sColName}' will be added.\n";
}
}
}
if ($bOneColIsMissing) {
$bTriggerRebuildNeeded = true;
$aMissingFields[] = $sAttCode;
}
}
}
}
$sDBName = MetaModel::GetConfig()->GetDBName();
try {
// Note: as per the MySQL documentation, using information_schema behaves exactly like SHOW TRIGGERS (user privileges)
// and this is in fact the recommended way for better portability
$iTriggerCount = CMDBSource::QueryToScalar("select count(*) from information_schema.triggers where EVENT_OBJECT_SCHEMA='{$sDBName}' and EVENT_OBJECT_TABLE='{$sTable}'");
} catch (Exception $e) {
if ($bVerbose) {
echo "Failed to investigate on the synchro triggers (skipping the check): " . $e->getMessage() . ".\n";
}
// Ignore this error: consider that the trigger are there
$iTriggerCount = 3;
}
if ($iTriggerCount < 3) {
$bFixNeeded = true;
$bTriggerRebuildNeeded = true;
if ($bVerbose) {
echo "Missing trigger(s) for the data synchro task " . $this->GetName() . " (table {$sTable}).\n";
}
}
$aRepairQueries = array();
if (count($aMissingFields) > 0) {
// The structure of the table needs adjusting
$aColumns = $this->GetSQLColumns($aMissingFields);
$aFieldDefs = array();
foreach ($aColumns as $sAttCode => $sColumnDef) {
if (CMDBSource::IsField($sTable, $sAttCode)) {
$aRepairQueries[] = "ALTER TABLE `{$sTable}` CHANGE `{$sAttCode}` `{$sAttCode}` {$sColumnDef}";
} else {
$aFieldDefs[] = "`{$sAttCode}` {$sColumnDef}";
}
}
if (count($aFieldDefs) > 0) {
$aRepairQueries[] = "ALTER TABLE `{$sTable}` ADD (" . implode(',', $aFieldDefs) . ");";
}
if ($bDiagnostics) {
if ($bVerbose) {
echo "The structure of the table {$sTable} for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . ") must be altered (missing or incorrect fields: " . implode(',', $aMissingFields) . ").\n";
}
}
}
// Repair the triggers
// Must be done after updating the columns because MySQL does check the validity of the query found into the procedure!
if ($bTriggerRebuildNeeded) {
// The triggers as well must be adjusted
$aTriggersDefs = $this->GetTriggersDefinition();
$aTriggerRepair = array();
$aTriggerRepair[] = "DROP TRIGGER IF EXISTS `{$sTable}_bi`;";
$aTriggerRepair[] = $aTriggersDefs['bi'];
$aTriggerRepair[] = "DROP TRIGGER IF EXISTS `{$sTable}_bu`;";
$aTriggerRepair[] = $aTriggersDefs['bu'];
$aTriggerRepair[] = "DROP TRIGGER IF EXISTS `{$sTable}_ad`;";
$aTriggerRepair[] = $aTriggersDefs['ad'];
if ($bDiagnostics) {
if ($bVerbose) {
echo "The triggers {$sTable}_bi, {$sTable}_bu, {$sTable}_ad for the data synchro task " . $this->GetName() . " (" . $this->GetKey() . ") must be re-created.\n";
echo implode("\n", $aTriggerRepair) . "\n";
}
}
$aRepairQueries = array_merge($aRepairQueries, $aTriggerRepair);
// The order matters!
}
// Execute the repair statements
//
if (!$bDiagnostics && count($aRepairQueries) > 0) {
// Fix the issue
foreach ($aRepairQueries as $sSQL) {
CMDBSource::Query($sSQL);
if ($bVerbose) {
echo "{$sSQL}\n";
}
}
}
return $bFixNeeded;
}
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);
}