public function LoadExtendedDataFromTable($sSQLTable)
{
$sSQL = "SELECT * FROM {$sSQLTable} WHERE id=" . $this->GetKey();
$rQuery = CMDBSource::Query($sSQL);
return CMDBSource::FetchArray($rQuery);
}
/**
* @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;
}
/**
* Fetch the whole row of objects (if several classes have been specified in the query) and move the cursor to the next position
*
* @return hash A hash with the format 'classAlias' => $oObj representing the current row of the set. Returns null when at the end.
*/
public function FetchAssoc()
{
if (!$this->m_bLoaded) {
$this->Load();
}
if ($this->m_iCurrRow >= $this->CountLoaded()) {
return null;
}
if ($this->m_iCurrRow < $this->m_iNumLoadedDBRows) {
// Pick the row from the database
$aRow = CMDBSource::FetchArray($this->m_oSQLResult);
$aRetObjects = array();
foreach ($this->m_oFilter->GetSelectedClasses() as $sClassAlias => $sClass) {
if (is_null($aRow[$sClassAlias . 'id'])) {
$oObj = null;
} else {
$oObj = MetaModel::GetObjectByRow($sClass, $aRow, $sClassAlias, $this->m_aAttToLoad, $this->m_aExtendedDataSpec);
}
$aRetObjects[$sClassAlias] = $oObj;
}
} else {
// Pick the row from the objects added *in memory*
$aRetObjects = array();
foreach ($this->m_oFilter->GetSelectedClasses() as $sClassAlias => $sClass) {
$aRetObjects[$sClassAlias] = $this->m_aAddedObjects[$this->m_iCurrRow - $this->m_iNumLoadedDBRows][$sClassAlias];
}
}
$this->m_iCurrRow++;
return $aRetObjects;
}
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 static function MakeSingleRow($sClass, $iKey, $bMustBeFound = true, $bAllowAllData = false, $aModifierProperties = null)
{
// Build the query cache signature
//
$sQuerySign = $sClass;
if ($bAllowAllData) {
$sQuerySign .= '_all_';
}
if (count($aModifierProperties)) {
array_multisort($aModifierProperties);
$sModifierProperties = json_encode($aModifierProperties);
$sQuerySign .= '_all_' . md5($sModifierProperties);
}
if (!array_key_exists($sQuerySign, self::$aQueryCacheGetObject)) {
// NOTE: Quick and VERY dirty caching mechanism which relies on
// the fact that the string '987654321' will never appear in the
// standard query
// This could be simplified a little, relying solely on the query cache,
// but this would slow down -by how much time?- the application
$oFilter = new DBObjectSearch($sClass);
$oFilter->AddCondition('id', 987654321, '=');
if ($aModifierProperties) {
foreach ($aModifierProperties as $sPluginClass => $aProperties) {
foreach ($aProperties as $sProperty => $value) {
$oFilter->SetModifierProperty($sPluginClass, $sProperty, $value);
}
}
}
if ($bAllowAllData) {
$oFilter->AllowAllData();
}
$sSQL = $oFilter->MakeSelectQuery();
self::$aQueryCacheGetObject[$sQuerySign] = $sSQL;
self::$aQueryCacheGetObjectHits[$sQuerySign] = 0;
} else {
$sSQL = self::$aQueryCacheGetObject[$sQuerySign];
self::$aQueryCacheGetObjectHits[$sQuerySign] += 1;
// echo " -load $sClass/$iKey- ".self::$aQueryCacheGetObjectHits[$sQuerySign]."<br/>\n";
}
$sSQL = str_replace(CMDBSource::Quote(987654321), CMDBSource::Quote($iKey), $sSQL);
$res = CMDBSource::Query($sSQL);
$aRow = CMDBSource::FetchArray($res);
CMDBSource::FreeResult($res);
if ($bMustBeFound && empty($aRow)) {
throw new CoreException("No result for the single row query: '{$sSQL}'");
}
return $aRow;
}
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 DoBenchmark($sOqlQuery)
{
echo "<h5>Testing query: {$sOqlQuery}</h5>";
$fStart = MyHelpers::getmicrotime();
$oFilter = DBObjectSearch::FromOQL($sOqlQuery);
$fParsingDuration = MyHelpers::getmicrotime() - $fStart;
$fStart = MyHelpers::getmicrotime();
$sSQL = $oFilter->MakeSelectQuery();
$fBuildDuration = MyHelpers::getmicrotime() - $fStart;
$fStart = MyHelpers::getmicrotime();
$res = CMDBSource::Query($sSQL);
$fQueryDuration = MyHelpers::getmicrotime() - $fStart;
// The fetch could not be repeated with the same results
// But we've seen so far that is was very very quick to exec
// So it makes sense to benchmark it a single time
$fStart = MyHelpers::getmicrotime();
$aRow = CMDBSource::FetchArray($res);
$fDuration = MyHelpers::getmicrotime() - $fStart;
$fFetchDuration = $fDuration;
$fStart = MyHelpers::getmicrotime();
$sOql = $oFilter->ToOQL();
$fToOqlDuration = MyHelpers::getmicrotime() - $fStart;
if (false) {
echo "<ul style=\"font-size:smaller;\">\n";
echo "<li>Parsing: {$fParsingDuration}</li>\n";
echo "<li>Build: {$fBuildDuration}</li>\n";
echo "<li>Query: {$fQueryDuration}</li>\n";
echo "<li>Fetch: {$fFetchDuration}</li>\n";
echo "<li>ToOql: {$fToOqlDuration}</li>\n";
echo "</ul>\n";
}
// Everything but the ToOQL (wich is interesting, anyhow)
$fTotal = $fParsingDuration + $fBuildDuration + $fQueryDuration + $fFetchDuration;
return array('rows' => CMDBSource::NbRows($res), 'duration (s)' => round($fTotal, 4), 'parsing (%)' => round(100 * $fParsingDuration / $fTotal, 1), 'build SQL (%)' => round(100 * $fBuildDuration / $fTotal, 1), 'query exec (%)' => round(100 * $fQueryDuration / $fTotal, 1), 'fetch (%)' => round(100 * $fFetchDuration / $fTotal, 1), 'to OQL (%)' => round(100 * $fToOqlDuration / $fTotal, 1), 'parsing+build (%)' => round(100 * ($fParsingDuration + $fBuildDuration) / $fTotal, 1));
}
