public function DBUpdate() { if (!$this->m_bIsInDB) { throw new CoreException("DBUpdate: could not update a newly created object, please call DBInsert instead"); } // Protect against reentrance (e.g. cascading the update of ticket logs) static $aUpdateReentrance = array(); $sKey = get_class($this) . '::' . $this->GetKey(); if (array_key_exists($sKey, $aUpdateReentrance)) { return; } $aUpdateReentrance[$sKey] = true; try { // Stop watches $sState = $this->GetState(); if ($sState != '') { foreach (MetaModel::ListAttributeDefs(get_class($this)) as $sAttCode => $oAttDef) { if ($oAttDef instanceof AttributeStopWatch) { if (in_array($sState, $oAttDef->GetStates())) { // Compute or recompute the deadlines $oSW = $this->Get($sAttCode); $oSW->ComputeDeadlines($this, $oAttDef); $this->Set($sAttCode, $oSW); } } } } $this->DoComputeValues(); $this->OnUpdate(); $aChanges = $this->ListChanges(); if (count($aChanges) == 0) { // Attempting to update an unchanged object unset($aUpdateReentrance[$sKey]); return $this->m_iKey; } // Ultimate check - ensure DB integrity list($bRes, $aIssues) = $this->CheckToWrite(); if (!$bRes) { $sIssues = implode(', ', $aIssues); throw new CoreException("Object not following integrity rules", array('issues' => $sIssues, 'class' => get_class($this), 'id' => $this->GetKey())); } // Save the original values (will be reset to the new values when the object get written to the DB) $aOriginalValues = $this->m_aOrigValues; $bHasANewExternalKeyValue = false; $aHierarchicalKeys = array(); foreach ($aChanges as $sAttCode => $valuecurr) { $oAttDef = MetaModel::GetAttributeDef(get_class($this), $sAttCode); if ($oAttDef->IsExternalKey()) { $bHasANewExternalKeyValue = true; } if (!$oAttDef->IsDirectField()) { unset($aChanges[$sAttCode]); } if ($oAttDef->IsHierarchicalKey()) { $aHierarchicalKeys[$sAttCode] = $oAttDef; } } if (!MetaModel::DBIsReadOnly()) { // Update the left & right indexes for each hierarchical key foreach ($aHierarchicalKeys as $sAttCode => $oAttDef) { $sTable = $sTable = MetaModel::DBGetTable(get_class($this), $sAttCode); $sSQL = "SELECT `" . $oAttDef->GetSQLRight() . "` AS `right`, `" . $oAttDef->GetSQLLeft() . "` AS `left` FROM `{$sTable}` WHERE id=" . $this->GetKey(); $aRes = CMDBSource::QueryToArray($sSQL); $iMyLeft = $aRes[0]['left']; $iMyRight = $aRes[0]['right']; $iDelta = $iMyRight - $iMyLeft + 1; MetaModel::HKTemporaryCutBranch($iMyLeft, $iMyRight, $oAttDef, $sTable); if ($aChanges[$sAttCode] == 0) { // No new parent, insert completely at the right of the tree $sSQL = "SELECT max(`" . $oAttDef->GetSQLRight() . "`) AS max FROM `{$sTable}`"; $aRes = CMDBSource::QueryToArray($sSQL); if (count($aRes) == 0) { $iNewLeft = 1; } else { $iNewLeft = $aRes[0]['max'] + 1; } } else { // Insert at the right of the specified parent $sSQL = "SELECT `" . $oAttDef->GetSQLRight() . "` FROM `{$sTable}` WHERE id=" . (int) $aChanges[$sAttCode]; $iNewLeft = CMDBSource::QueryToScalar($sSQL); } MetaModel::HKReplugBranch($iNewLeft, $iNewLeft + $iDelta - 1, $oAttDef, $sTable); $aHKChanges = array(); $aHKChanges[$sAttCode] = $aChanges[$sAttCode]; $aHKChanges[$oAttDef->GetSQLLeft()] = $iNewLeft; $aHKChanges[$oAttDef->GetSQLRight()] = $iNewLeft + $iDelta - 1; $aChanges[$sAttCode] = $aHKChanges; // the 3 values will be stored by MakeUpdateQuery below } // Update scalar attributes if (count($aChanges) != 0) { $oFilter = new DBObjectSearch(get_class($this)); $oFilter->AddCondition('id', $this->m_iKey, '='); $sSQL = MetaModel::MakeUpdateQuery($oFilter, $aChanges); CMDBSource::Query($sSQL); } } $this->DBWriteLinks(); $this->m_bDirty = false; $this->AfterUpdate(); // Reload to get the external attributes if ($bHasANewExternalKeyValue) { $this->Reload(); } else { // Reset original values although the object has not been reloaded foreach ($this->m_aLoadedAtt as $sAttCode => $bLoaded) { if ($bLoaded) { $value = $this->m_aCurrValues[$sAttCode]; $this->m_aOrigValues[$sAttCode] = is_object($value) ? clone $value : $value; } } } if (count($aChanges) != 0) { $this->RecordAttChanges($aChanges, $aOriginalValues); } } catch (Exception $e) { unset($aUpdateReentrance[$sKey]); throw $e; } unset($aUpdateReentrance[$sKey]); return $this->m_iKey; }
/** * Initializes (i.e converts) a hierarchy stored using a 'parent_id' external key * into a hierarchy stored with a HierarchicalKey, by initializing the _left and _right values * to correspond to the existing hierarchy in the database * @param $sClass string Name of the class to process * @param $sAttCode string Code of the attribute to process * @param $bDiagnosticsOnly boolean If true only a diagnostic pass will be run, returning true or false * @param $bVerbose boolean Displays some information about what is done/what needs to be done * @param $bForceComputation boolean If true, the _left and _right parameters will be recomputed even if some values already exist in the DB * @return true if an update is needed (diagnostics only) / was performed */ public static function HKInit($sClass, $sAttCode, $bDiagnosticsOnly = false, $bVerbose = false, $bForceComputation = false) { $idx = 1; $bUpdateNeeded = $bForceComputation; $oAttDef = self::GetAttributeDef($sClass, $sAttCode); $sTable = self::DBGetTable($sClass, $sAttCode); if ($oAttDef->IsHierarchicalKey()) { // Check if some values already exist in the table for the _right value, if so, do nothing $sRight = $oAttDef->GetSQLRight(); $sSQL = "SELECT MAX(`{$sRight}`) AS MaxRight FROM `{$sTable}`"; $iMaxRight = CMDBSource::QueryToScalar($sSQL); $sSQL = "SELECT COUNT(*) AS Count FROM `{$sTable}`"; // Note: COUNT(field) returns zero if the given field contains only NULLs $iCount = CMDBSource::QueryToScalar($sSQL); if (!$bForceComputation && $iCount != 0 && $iMaxRight == 0) { $bUpdateNeeded = true; if ($bVerbose) { echo "The table '{$sTable}' must be updated to compute the fields {$sRight} and " . $oAttDef->GetSQLLeft() . "\n"; } } if ($bForceComputation && !$bDiagnosticsOnly) { echo "Rebuilding the fields {$sRight} and " . $oAttDef->GetSQLLeft() . " from table '{$sTable}'...\n"; } if ($bUpdateNeeded && !$bDiagnosticsOnly) { try { CMDBSource::Query('START TRANSACTION'); self::HKInitChildren($sTable, $sAttCode, $oAttDef, 0, $idx); CMDBSource::Query('COMMIT'); if ($bVerbose) { echo "Ok, table '{$sTable}' successfully updated.\n"; } } catch (Exception $e) { CMDBSource::Query('ROLLBACK'); throw new Exception("An error occured (" . $e->getMessage() . ") while initializing the hierarchy for ({$sClass}, {$sAttCode}). The database was not modified."); } } } return $bUpdateNeeded; }
/** * 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; }
protected static function DoUpdateDBSchema($sMode, $aSelectedModules, $sModulesDir, $sDBServer, $sDBUser, $sDBPwd, $sDBName, $sDBPrefix, $sTargetEnvironment = '', $bOldAddon = false) { SetupPage::log_info("Update Database Schema for environment '{$sTargetEnvironment}'."); $oConfig = new Config(); $aParamValues = array('mode' => $sMode, 'db_server' => $sDBServer, 'db_user' => $sDBUser, 'db_pwd' => $sDBPwd, 'db_name' => $sDBName, 'db_prefix' => $sDBPrefix); $oConfig->UpdateFromParams($aParamValues, $sModulesDir); if ($bOldAddon) { // Old version of the add-on for backward compatibility with pre-2.0 data models $oConfig->SetAddons(array('user rights' => 'addons/userrights/userrightsprofile.db.class.inc.php')); } $oProductionEnv = new RunTimeEnvironment($sTargetEnvironment); $oProductionEnv->InitDataModel($oConfig, true); // load data model only // Migrate application data format // // priv_internalUser caused troubles because MySQL transforms table names to lower case under Windows // This becomes an issue when moving your installation data to/from Windows // Starting 2.0, all table names must be lowercase if ($sMode != 'install') { SetupPage::log_info("Renaming '{$sDBPrefix}priv_internalUser' into '{$sDBPrefix}priv_internaluser' (lowercase)"); // This command will have no effect under Windows... // and it has been written in two steps so as to make it work under windows! CMDBSource::SelectDB($sDBName); try { $sRepair = "RENAME TABLE `{$sDBPrefix}priv_internalUser` TO `{$sDBPrefix}priv_internaluser_other`, `{$sDBPrefix}priv_internaluser_other` TO `{$sDBPrefix}priv_internaluser`"; CMDBSource::Query($sRepair); } catch (Exception $e) { SetupPage::log_info("Renaming '{$sDBPrefix}priv_internalUser' failed (already done in a previous upgrade?)"); } // let's remove the records in priv_change which have no counterpart in priv_changeop SetupPage::log_info("Cleanup of '{$sDBPrefix}priv_change' to remove orphan records"); CMDBSource::SelectDB($sDBName); try { $sTotalCount = "SELECT COUNT(*) FROM `{$sDBPrefix}priv_change`"; $iTotalCount = (int) CMDBSource::QueryToScalar($sTotalCount); SetupPage::log_info("There is a total of {$iTotalCount} records in {$sDBPrefix}priv_change."); $sOrphanCount = "SELECT COUNT(c.id) FROM `{$sDBPrefix}priv_change` AS c left join `{$sDBPrefix}priv_changeop` AS o ON c.id = o.changeid WHERE o.id IS NULL"; $iOrphanCount = (int) CMDBSource::QueryToScalar($sOrphanCount); SetupPage::log_info("There are {$iOrphanCount} useless records in {$sDBPrefix}priv_change (" . sprintf('%.2f', 100.0 * $iOrphanCount / $iTotalCount) . "%)"); if ($iOrphanCount > 0) { SetupPage::log_info("Removing the orphan records..."); $sCleanup = "DELETE FROM `{$sDBPrefix}priv_change` USING `{$sDBPrefix}priv_change` LEFT JOIN `{$sDBPrefix}priv_changeop` ON `{$sDBPrefix}priv_change`.id = `{$sDBPrefix}priv_changeop`.changeid WHERE `{$sDBPrefix}priv_changeop`.id IS NULL;"; CMDBSource::Query($sCleanup); SetupPage::log_info("Cleanup completed successfully."); } else { SetupPage::log_info("Ok, nothing to cleanup."); } } catch (Exception $e) { SetupPage::log_info("Cleanup of orphan records in `{$sDBPrefix}priv_change` failed: " . $e->getMessage()); } } // Module specific actions (migrate the data) // $aAvailableModules = $oProductionEnv->AnalyzeInstallation(MetaModel::GetConfig(), APPROOT . $sModulesDir); foreach ($aAvailableModules as $sModuleId => $aModule) { if ($sModuleId != ROOT_MODULE && in_array($sModuleId, $aSelectedModules) && isset($aAvailableModules[$sModuleId]['installer'])) { $sModuleInstallerClass = $aAvailableModules[$sModuleId]['installer']; SetupPage::log_info("Calling Module Handler: {$sModuleInstallerClass}::BeforeDatabaseCreation(oConfig, {$aModule['version_db']}, {$aModule['version_code']})"); $aCallSpec = array($sModuleInstallerClass, 'BeforeDatabaseCreation'); call_user_func_array($aCallSpec, array(MetaModel::GetConfig(), $aModule['version_db'], $aModule['version_code'])); } } if (!$oProductionEnv->CreateDatabaseStructure(MetaModel::GetConfig(), $sMode)) { throw new Exception("Failed to create/upgrade the database structure for environment '{$sTargetEnvironment}'"); } // priv_change now has an 'origin' field to distinguish between the various input sources // Let's initialize the field with 'interactive' for all records were it's null // Then check if some records should hold a different value, based on a pattern matching in the userinfo field CMDBSource::SelectDB($sDBName); try { $sCount = "SELECT COUNT(*) FROM `{$sDBPrefix}priv_change` WHERE `origin` IS NULL"; $iCount = (int) CMDBSource::QueryToScalar($sCount); if ($iCount > 0) { SetupPage::log_info("Initializing '{$sDBPrefix}priv_change.origin' ({$iCount} records to update)"); // By default all uninitialized values are considered as 'interactive' $sInit = "UPDATE `{$sDBPrefix}priv_change` SET `origin` = 'interactive' WHERE `origin` IS NULL"; CMDBSource::Query($sInit); // CSV Import was identified by the comment at the end $sInit = "UPDATE `{$sDBPrefix}priv_change` SET `origin` = 'csv-import.php' WHERE `userinfo` LIKE '%Web Service (CSV)'"; CMDBSource::Query($sInit); // CSV Import was identified by the comment at the end $sInit = "UPDATE `{$sDBPrefix}priv_change` SET `origin` = 'csv-interactive' WHERE `userinfo` LIKE '%(CSV)' AND origin = 'interactive'"; CMDBSource::Query($sInit); // Syncho data sources were identified by the comment at the end // Unfortunately the comment is localized, so we have to search for all possible patterns $sCurrentLanguage = Dict::GetUserLanguage(); foreach (Dict::GetLanguages() as $sLangCode => $aLang) { Dict::SetUserLanguage($sLangCode); $sSuffix = CMDBSource::Quote('%' . Dict::S('Core:SyncDataExchangeComment')); $aSuffixes[$sSuffix] = true; } Dict::SetUserLanguage($sCurrentLanguage); $sCondition = "`userinfo` LIKE " . implode(" OR `userinfo` LIKE ", array_keys($aSuffixes)); $sInit = "UPDATE `{$sDBPrefix}priv_change` SET `origin` = 'synchro-data-source' WHERE ({$sCondition})"; CMDBSource::Query($sInit); SetupPage::log_info("Initialization of '{$sDBPrefix}priv_change.origin' completed."); } else { SetupPage::log_info("'{$sDBPrefix}priv_change.origin' already initialized, nothing to do."); } } catch (Exception $e) { SetupPage::log_error("Initializing '{$sDBPrefix}priv_change.origin' failed: " . $e->getMessage()); } // priv_async_task now has a 'status' field to distinguish between the various statuses rather than just relying on the date columns // Let's initialize the field with 'planned' or 'error' for all records were it's null CMDBSource::SelectDB($sDBName); try { $sCount = "SELECT COUNT(*) FROM `{$sDBPrefix}priv_async_task` WHERE `status` IS NULL"; $iCount = (int) CMDBSource::QueryToScalar($sCount); if ($iCount > 0) { SetupPage::log_info("Initializing '{$sDBPrefix}priv_async_task.status' ({$iCount} records to update)"); $sInit = "UPDATE `{$sDBPrefix}priv_async_task` SET `status` = 'planned' WHERE (`status` IS NULL) AND (`started` IS NULL)"; CMDBSource::Query($sInit); $sInit = "UPDATE `{$sDBPrefix}priv_async_task` SET `status` = 'error' WHERE (`status` IS NULL) AND (`started` IS NOT NULL)"; CMDBSource::Query($sInit); SetupPage::log_info("Initialization of '{$sDBPrefix}priv_async_task.status' completed."); } else { SetupPage::log_info("'{$sDBPrefix}priv_async_task.status' already initialized, nothing to do."); } } catch (Exception $e) { SetupPage::log_error("Initializing '{$sDBPrefix}priv_async_task.status' failed: " . $e->getMessage()); } SetupPage::log_info("Database Schema Successfully Updated for environment '{$sTargetEnvironment}'."); }