/** * 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); } }
/** * 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) { // For each record having item_org_id unset, // get the org_id from the container object // // Prerequisite: change null into 0 (workaround to the fact that we cannot use IS NULL in OQL) SetupPage::log_info("Initializing attachment/item_org_id - null to zero"); $sTableName = MetaModel::DBGetTable('Attachment'); $sRepair = "UPDATE `{$sTableName}` SET `item_org_id` = 0 WHERE `item_org_id` IS NULL"; CMDBSource::Query($sRepair); SetupPage::log_info("Initializing attachment/item_org_id - zero to the container"); $oSearch = DBObjectSearch::FromOQL("SELECT Attachment WHERE item_org_id = 0"); $oSet = new DBObjectSet($oSearch); $iUpdated = 0; while ($oAttachment = $oSet->Fetch()) { $oContainer = MetaModel::GetObject($oAttachment->Get('item_class'), $oAttachment->Get('item_id'), false, true); if ($oContainer) { $oAttachment->SetItem($oContainer, true); $iUpdated++; } } SetupPage::log_info("Initializing attachment/item_org_id - {$iUpdated} records have been adjusted"); }
/** * Helper to modify an enum value * The change is made in the datamodel definition, but the value has to be changed in the DB as well * Must be called BEFORE DB update, i.e within an implementation of BeforeDatabaseCreation() * This helper does change ONE value at a time * * @param string $sClass A valid class name * @param string $sAttCode The enum attribute code * @param string $sFrom Original value (already INVALID in the current datamodel) * @param string $sTo New value (valid in the current datamodel) * @return void */ public static function RenameEnumValueInDB($sClass, $sAttCode, $sFrom, $sTo) { try { $sOriginClass = MetaModel::GetAttributeOrigin($sClass, $sAttCode); $sTableName = MetaModel::DBGetTable($sOriginClass); $oAttDef = MetaModel::GetAttributeDef($sOriginClass, $sAttCode); if ($oAttDef instanceof AttributeEnum) { $oValDef = $oAttDef->GetValuesDef(); if ($oValDef) { $aNewValues = array_keys($oValDef->GetValues(array(), "")); if (in_array($sTo, $aNewValues)) { $sEnumCol = $oAttDef->Get("sql"); $aFields = CMDBSource::QueryToArray("SHOW COLUMNS FROM `{$sTableName}` WHERE Field = '{$sEnumCol}'"); if (isset($aFields[0]['Type'])) { $sColType = $aFields[0]['Type']; // Note: the parsing should rely on str_getcsv (requires PHP 5.3) to cope with escaped string if (preg_match("/^enum\\(\\'(.*)\\'\\)\$/", $sColType, $aMatches)) { $aCurrentValues = explode("','", $aMatches[1]); } } if (!in_array($sFrom, $aNewValues)) { if (!in_array($sTo, $aCurrentValues)) { $sNullSpec = $oAttDef->IsNullAllowed() ? 'NULL' : 'NOT NULL'; if (strtolower($sTo) == strtolower($sFrom)) { SetupPage::log_info("Changing enum in DB - {$sClass}::{$sAttCode} from '{$sFrom}' to '{$sTo}' (just a change in the case)"); $aTargetValues = array(); foreach ($aCurrentValues as $sValue) { if ($sValue == $sFrom) { $sValue = $sTo; } $aTargetValues[] = $sValue; } $sColumnDefinition = "ENUM(" . implode(",", CMDBSource::Quote($aTargetValues)) . ") {$sNullSpec}"; $sRepair = "ALTER TABLE `{$sTableName}` MODIFY `{$sEnumCol}` {$sColumnDefinition}"; CMDBSource::Query($sRepair); } else { // 1st - Allow both values in the column definition // SetupPage::log_info("Changing enum in DB - {$sClass}::{$sAttCode} from '{$sFrom}' to '{$sTo}'"); $aAllValues = $aCurrentValues; $aAllValues[] = $sTo; $sColumnDefinition = "ENUM(" . implode(",", CMDBSource::Quote($aAllValues)) . ") {$sNullSpec}"; $sRepair = "ALTER TABLE `{$sTableName}` MODIFY `{$sEnumCol}` {$sColumnDefinition}"; CMDBSource::Query($sRepair); // 2nd - Change the old value into the new value // $sRepair = "UPDATE `{$sTableName}` SET `{$sEnumCol}` = '{$sTo}' WHERE `{$sEnumCol}` = BINARY '{$sFrom}'"; CMDBSource::Query($sRepair); $iAffectedRows = CMDBSource::AffectedRows(); SetupPage::log_info("Changing enum in DB - {$iAffectedRows} rows updated"); // 3rd - Remove the useless value from the column definition // $aTargetValues = array(); foreach ($aCurrentValues as $sValue) { if ($sValue == $sFrom) { $sValue = $sTo; } $aTargetValues[] = $sValue; } $sColumnDefinition = "ENUM(" . implode(",", CMDBSource::Quote($aTargetValues)) . ") {$sNullSpec}"; $sRepair = "ALTER TABLE `{$sTableName}` MODIFY `{$sEnumCol}` {$sColumnDefinition}"; CMDBSource::Query($sRepair); SetupPage::log_info("Changing enum in DB - removed useless value '{$sFrom}'"); } } } else { SetupPage::log_warning("Changing enum in DB - {$sClass}::{$sAttCode} - '{$sFrom}' is still a valid value (" . implode(', ', $aNewValues) . ")"); } } else { SetupPage::log_warning("Changing enum in DB - {$sClass}::{$sAttCode} - '{$sTo}' is not a known value (" . implode(', ', $aNewValues) . ")"); } } } } catch (Exception $e) { SetupPage::log_warning("Changing enum in DB - {$sClass}::{$sAttCode} - '{$sTo}' failed. Reason " . $e->getMessage()); } }
if ($oDataLoader->EndSession(true)) { $iCountCreated = $oDataLoader->GetCountCreated(); CMDBSource::Query('COMMIT'); $oP->p("Data successfully written into the DB: {$iCountCreated} objects created"); } else { CMDBSource::Query('ROLLBACK'); $oP->p("Some issues have been encountered, changes will not be recorded, please review the source data"); $aErrors = $oDataLoader->GetErrors(); if (count($aErrors) > 0) { $oP->p('Errors (' . count($aErrors) . ')'); foreach ($aErrors as $sMsg) { $oP->p(' * ' . $sMsg); } } $aWarnings = $oDataLoader->GetWarnings(); if (count($aWarnings) > 0) { $oP->p('Warnings (' . count($aWarnings) . ')'); foreach ($aWarnings as $sMsg) { $oP->p(' * ' . $sMsg); } } } } catch (Exception $e) { $oP->p("An error happened while loading the data: " . $e->getMessage()); $oP->p("Aborting (no data written)..."); CMDBSource::Query('ROLLBACK'); } if (function_exists('memory_get_peak_usage')) { $oP->p("Information: memory peak usage: " . memory_get_peak_usage()); } $oP->Output();
/** * @param hash $aOrderBy Array of '[<classalias>.]attcode' => bAscending */ public function ToDataArray($aColumns = array(), $aOrderBy = array(), $aArgs = array()) { $sSQL = $this->MakeSelectQuery($aOrderBy, $aArgs); $resQuery = CMDBSource::Query($sSQL); if (!$resQuery) { return; } if (count($aColumns) == 0) { $aColumns = array_keys(MetaModel::ListAttributeDefs($this->GetClass())); // Add the standard id (as first column) array_unshift($aColumns, 'id'); } $aQueryCols = CMDBSource::GetColumns($resQuery); $sClassAlias = $this->GetClassAlias(); $aColMap = array(); foreach ($aColumns as $sAttCode) { $sColName = $sClassAlias . $sAttCode; if (in_array($sColName, $aQueryCols)) { $aColMap[$sAttCode] = $sColName; } } $aRes = array(); while ($aRow = CMDBSource::FetchArray($resQuery)) { $aMappedRow = array(); foreach ($aColMap as $sAttCode => $sColName) { $aMappedRow[$sAttCode] = $aRow[$sColName]; } $aRes[] = $aMappedRow; } CMDBSource::FreeResult($resQuery); return $aRes; }
public function LoadExtendedDataFromTable($sSQLTable) { $sSQL = "SELECT * FROM {$sSQLTable} WHERE id=" . $this->GetKey(); $rQuery = CMDBSource::Query($sSQL); return CMDBSource::FetchArray($rQuery); }
/** * The total number of rows in this set. Independently of the SetLimit used for loading the set and taking into account the rows added in-memory. * * May actually perform the SQL query SELECT COUNT... if the set was not previously loaded, or loaded with a SetLimit * * @return int The total number of rows for this set. */ public function Count() { if (is_null($this->m_iNumTotalDBRows)) { $sSQL = $this->m_oFilter->MakeSelectQuery(array(), $this->m_aArgs, null, null, 0, 0, true); $resQuery = CMDBSource::Query($sSQL); if (!$resQuery) { return 0; } $aRow = CMDBSource::FetchArray($resQuery); CMDBSource::FreeResult($resQuery); $this->m_iNumTotalDBRows = $aRow['COUNT']; } return $this->m_iNumTotalDBRows + count($this->m_aAddedObjects); // Does it fix Trac #887 ?? }
public function RenderContent(WebPage $oPage, $aExtraParams = array()) { if (empty($aExtraParams['currentId'])) { $sId = 'sqlblock_' . $oPage->GetUniqueId(); // Works only if the page is not an Ajax one ! } else { $sId = $aExtraParams['currentId']; } // $oPage->add($this->GetRenderContent($oPage, $aExtraParams, $sId)); $sQuery = $this->BuildQuery(); $res = CMDBSource::Query($sQuery); $aQueryCols = CMDBSource::GetColumns($res); // Prepare column definitions (check + give default values) // foreach ($this->m_aColumns as $sName => $aColumnData) { if (!in_array($sName, $aQueryCols)) { throw new Exception("Unknown column name '{$sName}' in sqlblock column"); } if (!isset($aColumnData['label'])) { $this->m_aColumns[$sName]['label'] = $sName; } if (isset($aColumnData['drilldown']) && !empty($aColumnData['drilldown'])) { // Check if the OQL is valid try { $this->m_aColumns[$sName]['filter'] = DBObjectSearch::FromOQL($aColumnData['drilldown']); } catch (OQLException $e) { unset($aColumnData['drilldown']); } } } if (strlen($this->m_sTitle) > 0) { $oPage->add("<h2>" . Dict::S($this->m_sTitle) . "</h2>\n"); } switch ($this->m_sType) { case 'bars': case 'pie': $aColNames = array_keys($this->m_aColumns); $sXColName = $aColNames[0]; $sYColName = $aColNames[1]; $aData = array(); $aRows = array(); while ($aRow = CMDBSource::FetchArray($res)) { $aData[$aRow[$sXColName]] = $aRow[$sYColName]; $aRows[$aRow[$sXColName]] = $aRow; } $this->RenderChart($oPage, $sId, $aData, $this->m_aColumns[$sYColName]['drilldown'], $aRows); break; default: case 'table': $oAppContext = new ApplicationContext(); $sContext = $oAppContext->GetForLink(); if (!empty($sContext)) { $sContext = '&' . $sContext; } $aDisplayConfig = array(); foreach ($this->m_aColumns as $sName => $aColumnData) { $aDisplayConfig[$sName] = array('label' => $aColumnData['label'], 'description' => ''); } $aDisplayData = array(); while ($aRow = CMDBSource::FetchArray($res)) { $aSQLColNames = array_keys($aRow); $aDisplayRow = array(); foreach ($this->m_aColumns as $sName => $aColumnData) { if (isset($aColumnData['filter'])) { $sFilter = $aColumnData['drilldown']; $sClass = $aColumnData['filter']->GetClass(); $sFilter = str_replace('SELECT ' . $sClass, '', $sFilter); foreach ($aSQLColNames as $sColName) { $sFilter = str_replace(':' . $sColName, "'" . addslashes($aRow[$sColName]) . "'", $sFilter); } $sURL = utils::GetAbsoluteUrlAppRoot() . 'pages/UI.php?operation=search_oql&search_form=0&oql_class=' . $sClass . '&oql_clause=' . urlencode($sFilter) . '&format=html' . $sContext; $aDisplayRow[$sName] = '<a href="' . $sURL . '">' . $aRow[$sName] . "</a>"; } else { $aDisplayRow[$sName] = $aRow[$sName]; } } $aDisplayData[] = $aDisplayRow; } $oPage->table($aDisplayConfig, $aDisplayData); break; } }
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; }
public static function BulkUpdate(DBObjectSearch $oFilter, array $aValues) { // $aValues is an array of $sAttCode => $value $sSQL = $oFilter->MakeUpdateQuery($aValues); if (!self::DBIsReadOnly()) { CMDBSource::Query($sSQL); } }
try { foreach ($aAnalysis as $sClass => $aData) { if (isset($aData['table_fixes'])) { foreach ($aData['table_fixes'] as $sAttCode => $aIssues) { foreach ($aIssues as $sSQL) { CMDBSource::Query($sSQL); echo "<p class=\"sql_ok\">{$sSQL};</p>\n"; } } } } foreach ($aAnalysis as $sClass => $aData) { if (isset($aData['view_fixes'])) { foreach ($aData['view_fixes'] as $sAttCode => $aIssues) { foreach ($aIssues as $sSQL) { CMDBSource::Query($sSQL); echo "<p class=\"sql_ok\">{$sSQL};</p>\n"; } } } } echo "<p>Done.</p>"; } catch (MySQLException $e) { echo "<p class=\"sql_error\">{$sSQL};</p>\n"; echo "<p class=\"sql_error\">" . $e->getHtmlDesc() . "</p>\n"; echo "<p class=\"sql_error\">Operation aborted.</p>\n"; } break; default: echo "The operation {$sOperation} is not supported"; }
public function Exec() { if ($this->sSql != '') { $iRepeat = utils::ReadParam('repeat', 3); try { $fRefTime = MyHelpers::getmicrotime(); for ($i = 0; $i < $iRepeat; $i++) { $resQuery = CMDBSource::Query($this->sSql); } $this->fExecDuration = (MyHelpers::getmicrotime() - $fRefTime) / $iRepeat; // This is not relevant... if (preg_match_all('|\\s*JOIN\\s*\\(\\s*`|', $this->sSql, $aMatches)) { $this->iTableCount = 1 + count($aMatches[0]); } else { $this->iTableCount = 1; } } catch (Exception $e) { $this->aErrors[] = "Failed to execute the SQL:" . $e->getMessage(); $resQuery = null; } if ($resQuery) { while ($aRow = CMDBSource::FetchArray($resQuery)) { $this->aRows[] = $aRow; } CMDBSource::FreeResult($resQuery); } } }
protected function DoExecute() { CMDBSource::Query('START TRANSACTION'); //CMDBSource::Query('ROLLBACK'); automatique ! //////////////////////////////////////////////////////////////////////////////// // Set the stage // $oProvider = new Organization(); $oProvider->Set('name', 'Test-Provider1'); $oProvider->DBInsert(); $iProvider = $oProvider->GetKey(); $oDM1 = new DeliveryModel(); $oDM1->Set('name', 'Test-DM-1'); $oDM1->Set('org_id', $iProvider); $oDM1->DBInsert(); $iDM1 = $oDM1->GetKey(); $oDM2 = new DeliveryModel(); $oDM2->Set('name', 'Test-DM-2'); $oDM2->Set('org_id', $iProvider); $oDM2->DBInsert(); $iDM2 = $oDM2->GetKey(); //////////////////////////////////////////////////////////////////////////////// // Scenarii // $aScenarii = array(array('description' => 'Add the first customer', 'organizations' => array(array('deliverymodel_id' => $iDM1, 'name' => 'Test-Customer-1')), 'expected-res' => array("Test-Customer-1, , active, 0, , {$iDM1}, Test-DM-1, , Test-DM-1"), 'history_added' => 0, 'history_removed' => 0, 'history_modified' => 0), array('description' => 'Remove the customer by loading an empty set', 'organizations' => array(), 'expected-res' => array(), 'history_added' => 0, 'history_removed' => 0, 'history_modified' => 0), array('description' => 'Create two customers at once', 'organizations' => array(array('deliverymodel_id' => $iDM1, 'name' => 'Test-Customer-1'), array('deliverymodel_id' => $iDM1, 'name' => 'Test-Customer-2')), 'expected-res' => array("Test-Customer-1, , active, 0, , {$iDM1}, Test-DM-1, , Test-DM-1", "Test-Customer-2, , active, 0, , {$iDM1}, Test-DM-1, , Test-DM-1"), 'history_added' => 0, 'history_removed' => 0, 'history_modified' => 0), array('description' => 'Move Customer-1 to the second Delivery Model', 'organizations' => array(array('id' => "SELECT Organization WHERE name='Test-Customer-1'", 'deliverymodel_id' => $iDM2, 'name' => 'Test-Customer-1'), array('deliverymodel_id' => $iDM1, 'name' => 'Test-Customer-2')), 'expected-res' => array("Test-Customer-2, , active, 0, , {$iDM1}, Test-DM-1, , Test-DM-1"), 'history_added' => 0, 'history_removed' => 0, 'history_modified' => 0), array('description' => 'Move Customer-1 back to the first Delivery Model and reset Customer-2 (no Delivery Model)', 'organizations' => array(array('id' => "SELECT Organization WHERE name='Test-Customer-1'", 'deliverymodel_id' => $iDM1, 'name' => 'Test-Customer-1'), array('id' => "SELECT Organization WHERE name='Test-Customer-2'", 'deliverymodel_id' => 0, 'name' => 'Test-Customer-2')), 'expected-res' => array("Test-Customer-1, , active, 0, , {$iDM1}, Test-DM-1, , Test-DM-1"), 'history_added' => 0, 'history_removed' => 0, 'history_modified' => 0)); foreach ($aScenarii as $aScenario) { echo "<h4>" . $aScenario['description'] . "</h4>\n"; $oChange = MetaModel::NewObject("CMDBChange"); $oChange->Set("date", time()); $oChange->Set("userinfo", CMDBChange::GetCurrentUserName()); $oChange->Set("origin", 'custom-extension'); $oChange->DBInsert(); CMDBObject::SetCurrentChange($oChange); $iChange = $oChange->GetKey(); // Prepare set $oLinkset = DBObjectSet::FromScratch('Organization'); foreach ($aScenario['organizations'] as $aOrgData) { if (array_key_exists('id', $aOrgData)) { $sOQL = $aOrgData['id']; $oSet = new DBObjectSet(DBObjectSearch::FromOQL($sOQL)); $oOrg = $oSet->Fetch(); if (!is_object($oOrg)) { throw new Exception('Failed to find the Organization: ' . $sOQL); } } else { $oOrg = MetaModel::NewObject('Organization'); } foreach ($aOrgData as $sAttCode => $value) { if ($sAttCode == 'id') { continue; } $oOrg->Set($sAttCode, $value); } $oLinkset->AddObject($oOrg); } // Write $oDM = MetaModel::GetObject('DeliveryModel', $iDM1); $oDM->Set('customers_list', $oLinkset); $oDM->DBWrite(); // Check Results $bFoundIssue = false; $oDM = MetaModel::GetObject('DeliveryModel', $iDM1); $oLinkset = $oDM->Get('customers_list'); $aRes = $this->StandardizedDump($oLinkset, 'zzz'); $sRes = var_export($aRes, true); echo "Found: <pre>" . $sRes . "</pre>\n"; $sExpectedRes = var_export($aScenario['expected-res'], true); if ($sRes != $sExpectedRes) { $bFoundIssue = true; echo "NOT COMPLIANT!!! Expecting: <pre>" . $sExpectedRes . "</pre>\n"; } // Check History $aQueryParams = array('change' => $iChange, 'objclass' => get_class($oDM), 'objkey' => $oDM->GetKey()); $oAdded = new DBObjectSet(DBSearch::FromOQL("SELECT CMDBChangeOpSetAttributeLinksAddRemove WHERE objclass = :objclass AND objkey = :objkey AND change = :change AND type = 'added'"), array(), $aQueryParams); echo "added: " . $oAdded->Count() . "<br/>\n"; if ($aScenario['history_added'] != $oAdded->Count()) { $bFoundIssue = true; echo "NOT COMPLIANT!!! Expecting: " . $aScenario['history_added'] . "<br/>\n"; } $oRemoved = new DBObjectSet(DBSearch::FromOQL("SELECT CMDBChangeOpSetAttributeLinksAddRemove WHERE objclass = :objclass AND objkey = :objkey AND change = :change AND type = 'removed'"), array(), $aQueryParams); echo "removed: " . $oRemoved->Count() . "<br/>\n"; if ($aScenario['history_removed'] != $oRemoved->Count()) { $bFoundIssue = true; echo "NOT COMPLIANT!!! Expecting: " . $aScenario['history_removed'] . "<br/>\n"; } $oModified = new DBObjectSet(DBSearch::FromOQL("SELECT CMDBChangeOpSetAttributeLinksTune WHERE objclass = :objclass AND objkey = :objkey AND change = :change"), array(), $aQueryParams); echo "modified: " . $oModified->Count() . "<br/>\n"; if ($aScenario['history_modified'] != $oModified->Count()) { $bFoundIssue = true; echo "NOT COMPLIANT!!! Expecting: " . $aScenario['history_modified'] . "<br/>\n"; } if ($bFoundIssue) { throw new Exception('Stopping on failed scenario'); } } }
/** * Helper to modify an enum value * The change is made in the datamodel definition, but the value has to be changed in the DB as well * Must be called BEFORE DB update, i.e within an implementation of BeforeDatabaseCreation() * * @param string $sClass A valid class name * @param string $sAttCode The enum attribute code * @param string $sFrom Original value (already INVALID in the current datamodel) * @param string $sTo New value (valid in the current datamodel) * @return void */ public static function RenameEnumValueInDB($sClass, $sAttCode, $sFrom, $sTo) { $sOriginClass = MetaModel::GetAttributeOrigin($sClass, $sAttCode); $sTableName = MetaModel::DBGetTable($sOriginClass); $oAttDef = MetaModel::GetAttributeDef($sOriginClass, $sAttCode); if ($oAttDef instanceof AttributeEnum) { $oValDef = $oAttDef->GetValuesDef(); if ($oValDef) { $aNewValues = array_keys($oValDef->GetValues(array(), "")); if (in_array($sTo, $aNewValues)) { $aAllValues = $aNewValues; $aAllValues[] = $sFrom; if (!in_array($sFrom, $aNewValues)) { $sEnumCol = $oAttDef->Get("sql"); $sNullSpec = $oAttDef->IsNullAllowed() ? 'NULL' : 'NOT NULL'; if (strtolower($sTo) == strtolower($sFrom)) { SetupPage::log_info("Changing enum in DB - {$sClass}::{$sAttCode} from '{$sFrom}' to '{$sTo}' (just a change in the case)"); $sColumnDefinition = "ENUM(" . implode(",", CMDBSource::Quote($aNewValues)) . ") {$sNullSpec}"; $sRepair = "ALTER TABLE `{$sTableName}` MODIFY `{$sEnumCol}` {$sColumnDefinition}"; CMDBSource::Query($sRepair); } else { // 1st - Allow both values in the column definition // SetupPage::log_info("Changing enum in DB - {$sClass}::{$sAttCode} from '{$sFrom}' to '{$sTo}'"); $sColumnDefinition = "ENUM(" . implode(",", CMDBSource::Quote($aAllValues)) . ") {$sNullSpec}"; $sRepair = "ALTER TABLE `{$sTableName}` MODIFY `{$sEnumCol}` {$sColumnDefinition}"; CMDBSource::Query($sRepair); // 2nd - Change the old value into the new value // $sRepair = "UPDATE `{$sTableName}` SET `{$sEnumCol}` = '{$sTo}' WHERE `{$sEnumCol}` = BINARY '{$sFrom}'"; CMDBSource::Query($sRepair); $iAffectedRows = CMDBSource::AffectedRows(); SetupPage::log_info("Changing enum in DB - {$iAffectedRows} rows updated"); // 3rd - Remove the useless value from the column definition // $sColumnDefinition = "ENUM(" . implode(",", CMDBSource::Quote($aNewValues)) . ") {$sNullSpec}"; $sRepair = "ALTER TABLE `{$sTableName}` MODIFY `{$sEnumCol}` {$sColumnDefinition}"; CMDBSource::Query($sRepair); SetupPage::log_info("Changing enum in DB - removed useless value '{$sFrom}'"); } } else { SetupPage::log_warning("Changing enum in DB - {$sClass}::{$sAttCode} - '{$sFrom}' is still a valid value (" . implode(', ', $aNewValues) . ")"); } } else { SetupPage::log_warning("Changing enum in DB - {$sClass}::{$sAttCode} - '{$sTo}' is not a known value (" . implode(', ', $aNewValues) . ")"); } } } }
protected function DoExecute() { // Note: relying on eval() - after upgrading to PHP 5.3 we can move to closure (aka anonymous functions) $aQueries = array('Basic (validate the test)' => array('search' => ' $oSearch = DBObjectSearch::FromOQL("SELECT P FROM Organization AS O JOIN Person AS P ON P.org_id = O.id WHERE org_id = 2"); ', 'oql' => 'SELECT P FROM Organization AS O JOIN Person AS P ON P.org_id = O.id WHERE P.org_id = 2'), 'Double constraint' => array('search' => ' $oSearch = DBObjectSearch::FromOQL("SELECT Contact AS c"); $sClass = $oSearch->GetClass(); $sFilterCode = "org_id"; $oAttDef = MetaModel::GetAttributeDef($sClass, $sFilterCode); if ($oAttDef->IsExternalKey()) { $sHierarchicalKeyCode = MetaModel::IsHierarchicalClass($oAttDef->GetTargetClass()); if ($sHierarchicalKeyCode !== false) { $oFilter = new DBObjectSearch($oAttDef->GetTargetClass(), "ORGA"); $oFilter->AddCondition("id", 2); $oHKFilter = new DBObjectSearch($oAttDef->GetTargetClass(), "ORGA"); $oHKFilter->AddCondition_PointingTo(clone $oFilter, $sHierarchicalKeyCode, TREE_OPERATOR_BELOW); $oSearch->AddCondition_PointingTo(clone $oHKFilter, $sFilterCode); $oFilter = new DBObjectSearch($oAttDef->GetTargetClass(), "ORGA"); $oFilter->AddCondition("id", 2); $oHKFilter = new DBObjectSearch($oAttDef->GetTargetClass(), "ORGA"); $oHKFilter->AddCondition_PointingTo(clone $oFilter, $sHierarchicalKeyCode, TREE_OPERATOR_BELOW); $oSearch->AddCondition_PointingTo(clone $oHKFilter, $sFilterCode); } } ', 'oql' => 'SELECT Contact AS C JOIN Organization ???'), 'Simplified issue' => array('search' => ' $oSearch = DBObjectSearch::FromOQL("SELECT P FROM Organization AS O JOIN Person AS P ON P.org_id = O.id WHERE O.id = 2"); $oOrgSearch = new DBObjectSearch("Organization", "O2"); $oOrgSearch->AddCondition("id", 2); $oSearch->AddCondition_PointingTo($oOrgSearch, "org_id"); ', 'oql' => 'SELECT P FROM Organization AS O JOIN Person AS P ON P.org_id = O.id JOIN Organization AS O2 ON P.org_id = O2.id WHERE O.id = 2 AND O2.id = 2')); foreach ($aQueries as $sQueryDesc => $aQuerySpec) { echo "<h2>Query {$sQueryDesc}</h2>\n"; echo "<p>Using code: " . highlight_string("<?php\n" . trim($aQuerySpec['search']) . "\n?" . '>', true) . "</p>\n"; echo "<p>Expected OQL: " . $aQuerySpec['oql'] . "</p>\n"; if (isset($oSearch)) { unset($oSearch); } eval($aQuerySpec['search']); $sResOQL = $oSearch->ToOQL(); echo "<p>Resulting OQL: " . $sResOQL . "</p>\n"; echo "<pre>"; print_r($oSearch); echo "</pre>"; $sSQL = $oSearch->MakeSelectQuery(); $res = CMDBSource::Query($sSQL); foreach (CMDBSource::ExplainQuery($sSQL) as $aRow) { } } // throw new UnitTestException("Expecting result '{$aWebService['expected result']}', but got '$res'"); }
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}'."); }