protected function prepareSelectedCubeQueryStatement(AbstractSQLDataSourceQueryHandler $datasourceHandler, DataControllerCallContext $callcontext, CubeQueryRequest $request) { $metamodel = data_controller_get_metamodel(); // loading cube configuration $cubeName = $request->getCubeName(); $cube = $metamodel->getCube($cubeName); // loading cube source configuration $cubeDatasetName = $cube->sourceDatasetName; $cubeDataset = $metamodel->getDataset($cubeDatasetName); // table alias prefix for level datasets $TABLE_ALIAS__SOURCE = 's'; $tableIndex = 1; if (isset($cube->dimensions)) { foreach ($cube->dimensions as $dimension) { $tableIndex += $dimension->getLevelCount(); } } // to store configuration for each accessed table $datasetConfigs = NULL; // preparing cube source dataset configuration $this->registerDatasetConfig($datasetConfigs, 0, $cubeDataset, NULL, NULL); // statement for aggregation portion of final sql $aggrStatement = new Statement(); // adding support for source dataset property queries $sourceDatasetPropertyQueries = $request->findSourceDatasetPropertyQueries(); if (isset($sourceDatasetPropertyQueries)) { foreach ($sourceDatasetPropertyQueries as $sourceDatasetPropertyQuery) { $propertyName = $sourceDatasetPropertyQuery->propertyName; foreach ($sourceDatasetPropertyQuery->values as $propertyValue) { $this->registerDatasetConfig($datasetConfigs, 0, NULL, $propertyName, NULL); $aggrStatement->conditions[] = new WhereConditionSection($TABLE_ALIAS__SOURCE . '0', $propertyName, new ExactConditionSectionValue($datasourceHandler->formatOperatorValue($callcontext, $request, $cubeDataset->name, $propertyName, $propertyValue))); } } } // preparing list of columns which are required to group data for the aggregation $aggrSelectColumns = NULL; if (isset($cube->dimensions)) { foreach ($cube->dimensions as $dimension) { $dimensionName = $dimension->name; $queryDimension = $request->findDimensionQuery($dimensionName); $outputDimension = $request->findDimension($dimensionName); // processing ONLY dimensions which are part of request query or output if (!isset($queryDimension) && !isset($outputDimension)) { continue; } $isDimensionLevelUsed = FALSE; // We navigate starting from the highest level because it is possible to have several references to source // From efficiency prospective we need to use reference to source from the highest level possible // because we would not need to join with lower level datasets // Example (year_id and date_id columns are in source): // * efficient way: year->SOURCE // * inefficient way: year->quarter->month->date->SOURCE $levels = $dimension->levels; for ($i = count($levels) - 1; $i >= 0; $i--) { $level = $levels[$i]; $levelName = $level->name; $isQueryDimensionLevel = isset($queryDimension) && $queryDimension->levelName === $levelName; $isOutputDimensionLevel = isset($outputDimension) && $outputDimension->levelName === $levelName; if (!$isQueryDimensionLevel && !$isOutputDimensionLevel && !$isDimensionLevelUsed) { continue; } $lowerLevelKeyColumnName = isset($level->sourceColumnName) ? NULL : (isset($level->parentKey) ? $level->parentKey : $level->key); // joining with current level dataset ... if necessary $queryPropertyNames = isset($queryDimension) ? $queryDimension->getPropertyNames() : NULL; if ($isDimensionLevelUsed || $isQueryDimensionLevel && isset($queryPropertyNames)) { $levelDataset = $metamodel->getDataset($level->datasetName); // registering the level dataset $tableIndex--; $this->registerDatasetConfig($datasetConfigs, $tableIndex, $levelDataset, NULL, NULL); if ($isQueryDimensionLevel && isset($queryPropertyNames)) { foreach ($queryDimension->values as $propertyValue) { if (isset($propertyValue->name)) { $this->registerDatasetConfig($datasetConfigs, $tableIndex, NULL, $propertyValue->name, NULL); } $conditionPropertyName = isset($propertyValue->name) ? $propertyValue->name : $levelDataset->getKeyColumn()->name; foreach ($propertyValue->values as $value) { $aggrStatement->conditions[] = new WhereConditionSection($TABLE_ALIAS__SOURCE . $tableIndex, $conditionPropertyName, new ExactConditionSectionValue($datasourceHandler->formatOperatorValue($callcontext, $request, $levelDataset->name, $conditionPropertyName, $value))); } } } } // adding returning columns if ($isOutputDimensionLevel) { $responseColumnName = ParameterHelper::assembleParameterName($outputDimension->dimensionName, $outputDimension->levelName); $databaseColumnName = ParameterHelper::assembleDatabaseColumnName($datasourceHandler->getMaximumEntityNameLength(), $request->referenced ? ReferencePathHelper::assembleReference($cubeDataset->name, $outputDimension->dimensionName) : $outputDimension->dimensionName, $outputDimension->levelName); $callcontext->columnMapping[$databaseColumnName] = $responseColumnName; if (isset($level->sourceColumnName)) { $aggrSelectColumns[0][] = new ColumnSection(ReferencePathHelper::assembleDatabaseColumnName($datasourceHandler->getMaximumEntityNameLength(), $level->sourceColumnName), $databaseColumnName); $this->registerDatasetConfig($datasetConfigs, 0, NULL, $level->sourceColumnName, NULL); } else { $lowerLevelTableIndex = $tableIndex - 1; $aggrSelectColumns[$lowerLevelTableIndex][] = new ColumnSection($lowerLevelKeyColumnName, $databaseColumnName); $this->registerDatasetConfig($datasetConfigs, $lowerLevelTableIndex, NULL, $lowerLevelKeyColumnName, NULL); } } if (isset($level->sourceColumnName)) { // joining with source if ($isDimensionLevelUsed || $isQueryDimensionLevel) { $this->registerDatasetConfig($datasetConfigs, 0, NULL, $level->sourceColumnName, NULL); if ($isQueryDimensionLevel && !isset($queryPropertyNames)) { // applying conditions to master source foreach ($queryDimension->values as $propertyValue) { foreach ($propertyValue->values as $value) { $aggrStatement->conditions[] = new WhereConditionSection($TABLE_ALIAS__SOURCE . '0', $level->sourceColumnName, new ExactConditionSectionValue($datasourceHandler->formatOperatorValue($callcontext, $request, $cubeDataset->name, $level->sourceColumnName, $value))); } } } else { // linking the level dataset with master source $this->registerDatasetConfig($datasetConfigs, $tableIndex, NULL, $level->key, new JoinConditionSection($level->key, new TableColumnConditionSectionValue($TABLE_ALIAS__SOURCE . '0', $level->sourceColumnName))); } } // we do not need to go through rest of the levels break; } else { if ($isQueryDimensionLevel && !isset($queryPropertyNames)) { $lowerLevelTableIndex = $tableIndex - 1; $this->registerDatasetConfig($datasetConfigs, $lowerLevelTableIndex, NULL, $lowerLevelKeyColumnName, NULL); $lowerlevelDatasetName = $levels[$i - 1]->datasetName; foreach ($queryDimension->values as $propertyValue) { foreach ($propertyValue->value as $value) { $aggrStatement->conditions[] = new WhereConditionSection($TABLE_ALIAS__SOURCE . $lowerLevelTableIndex, $lowerLevelKeyColumnName, new ExactConditionSectionValue($datasourceHandler->formatOperatorValue($callcontext, $request, $lowerlevelDatasetName, $lowerLevelKeyColumnName, $value))); } } } // FIXME simplify this condition. We just need to check if current level dataset is used if ($isDimensionLevelUsed || $isQueryDimensionLevel && isset($queryPropertyNames)) { // joining with lower level $lowerLevelTableIndex = $tableIndex - 1; $this->registerDatasetConfig($datasetConfigs, $lowerLevelTableIndex, NULL, $lowerLevelKeyColumnName, NULL); $this->registerDatasetConfig($datasetConfigs, $tableIndex, NULL, $level->key, new JoinConditionSection($level->key, new TableColumnConditionSectionValue($TABLE_ALIAS__SOURCE . $lowerLevelTableIndex, $lowerLevelKeyColumnName))); } } $isDimensionLevelUsed = TRUE; } } } // preparing list of measures which are calculated in the aggregation, preparing support for measure conditions $aggrSelectMeasureColumns = NULL; foreach ($cube->measures as $cubeMeasure) { $measureName = $cubeMeasure->name; $selectedMeasure = $request->findMeasure($measureName); $queriedMeasure = $request->findMeasureQuery($measureName); if (!isset($selectedMeasure) && !isset($queriedMeasure)) { continue; } if ($request->referenced) { $measureName = ReferencePathHelper::assembleReference($cubeDataset->name, $measureName); } $databaseColumnName = ParameterHelper::assembleDatabaseColumnName($datasourceHandler->getMaximumEntityNameLength(), $measureName); $columnSection = new CompositeColumnSection($cubeMeasure->function, $databaseColumnName); if (isset($selectedMeasure)) { $callcontext->columnMapping[$databaseColumnName] = $measureName; $aggrSelectMeasureColumns[] = $columnSection; } if (isset($queriedMeasure)) { foreach ($queriedMeasure->values as $measureValue) { $aggrStatement->havingConditions[] = new HavingConditionSection($columnSection, new ExactConditionSectionValue($datasourceHandler->formatOperatorValue($callcontext, $request, $cubeDataset->name, NULL, $measureValue))); } } // looking for possible columns in the measure function. We need to retrieve those from the database $columnNames = $columnSection->parseColumns(); if (isset($columnNames)) { foreach ($columnNames as $columnName) { $this->registerDatasetConfig($datasetConfigs, 0, NULL, $columnName, NULL); } } } // sorting configuration to support joins in correct order ksort($datasetConfigs, SORT_NUMERIC); // preparing dataset source statements foreach ($datasetConfigs as $orderIndex => $datasetConfig) { $tableStatement = $datasourceHandler->prepareDatasetSourceStatement($callcontext, $datasetConfig->dataset, $datasetConfig->usedColumnNames); // adding join conditions if (isset($datasetConfig->conditions)) { foreach ($datasetConfig->conditions as $condition) { $tableStatement->getColumnTable($condition->subjectColumnName)->conditions[] = $condition; } } // we do not need to return any columns from the table by default foreach ($tableStatement->tables as $table) { if (isset($table->columns)) { foreach ($table->columns as $column) { $column->visible = FALSE; } } else { $table->columns = array(); // We do not need any columns } } // preparing the table columns which we want to return if (isset($aggrSelectColumns[$orderIndex])) { $tableSelectColumns = $aggrSelectColumns[$orderIndex]; foreach ($tableSelectColumns as $tableSelectColumn) { // looking for a table in the statement which provides the column for SELECT section $tableSection = $tableStatement->getColumnTable($tableSelectColumn->name); $attachedColumn = $tableSelectColumn->attachTo($tableSection); $aggrStatement->groupByColumns[] = new GroupByColumnSection($attachedColumn); } } // preparing measures which we want to return. Adding those measures to facts table if ($orderIndex == 0 && isset($aggrSelectMeasureColumns)) { foreach ($aggrSelectMeasureColumns as $tableSelectMeasureColumn) { $columnNames = $tableSelectMeasureColumn->parseColumns(); // searching which table contains the column $tableSection = NULL; if (isset($columnNames)) { foreach ($columnNames as $columnName) { $formattedColumnAlias = ReferencePathHelper::assembleDatabaseColumnName($datasourceHandler->getMaximumEntityNameLength(), $columnName); foreach ($tableStatement->tables as $table) { if ($table->findColumnByAlias($formattedColumnAlias) != NULL) { if (isset($tableSection)) { if ($tableSection->alias !== $table->alias) { // FIXME we should not have such functionality // checking if the same column is used for several times in a table under different aliases $tableSectionColumns = $tableSection->findColumns($formattedColumnAlias); $tableColumns = $table->findColumns($formattedColumnAlias); $isTableSelected = FALSE; if ($tableSectionColumns > 0 && $tableColumns > 0) { if ($tableSectionColumns > $tableColumns) { $tableSection = $table; $isTableSelected = TRUE; } elseif ($tableColumns > $tableSectionColumns) { $isTableSelected = TRUE; } } if (!$isTableSelected) { throw new UnsupportedOperationException(t('Aggregation function bases on several tables')); } } } else { $tableSection = $table; } } } } } if (!isset($tableSection)) { $tableSection = $tableStatement->tables[0]; } $tableSelectMeasureColumn->attachTo($tableSection); } } // updating join statement table aliases $sourceTableAlias = $TABLE_ALIAS__SOURCE . $orderIndex; foreach ($tableStatement->tables as $table) { $oldTableAlias = $table->alias; $newTableAlias = $sourceTableAlias . (isset($oldTableAlias) ? '_' . $oldTableAlias : ''); $tableStatement->updateTableAlias($oldTableAlias, $newTableAlias); // updating statement conditions which are used to join levels foreach ($datasetConfigs as $nextOrderIndex => $nextDatasetConfig) { if ($nextOrderIndex <= $orderIndex || !isset($nextDatasetConfig->conditions)) { continue; } foreach ($nextDatasetConfig->conditions as $condition) { if ($condition instanceof JoinConditionSection && $condition->joinValue instanceof TableColumnConditionSectionValue && $condition->joinValue->tableAlias === $sourceTableAlias && ($table->findColumn($condition->joinValue->columnName) != NULL || count($tableStatement->tables) === 1)) { $condition->joinValue->tableAlias = $newTableAlias; } } } // updating aggregation statement conditions if (isset($aggrStatement->conditions)) { foreach ($aggrStatement->conditions as $condition) { if ($condition->subjectTableAlias === $sourceTableAlias && $table->findColumn($condition->subjectColumnName) != NULL) { // checking if any other table in the statement support the column as an alias $otherColumnFound = FALSE; foreach ($tableStatement->tables as $subjectColumnTable) { $subjectColumn = $subjectColumnTable->findColumnByAlias($condition->subjectColumnName); if (isset($subjectColumn) && $subjectColumn instanceof ColumnSection) { if ($subjectColumnTable->alias != $table->alias) { $condition->subjectTableAlias = $sourceTableAlias . (isset($subjectColumnTable->alias) ? '_' . $subjectColumnTable->alias : ''); $condition->subjectColumnName = $subjectColumn->name; $otherColumnFound = TRUE; } } } if (!$otherColumnFound) { $condition->subjectTableAlias = $newTableAlias; } } } } } $aggrStatement->merge($tableStatement); } return $aggrStatement; }
protected function selectBoundary4CubeRequest(DataControllerCallContext $callcontext, CubeQueryRequest $request, $datasetName, $columnName) { $isSortAscending = $this->isSortAscending(); $resultColumnName = NULL; // preparing new cube meta data $expressionRequest = new CubeQueryRequest($request->getCubeName()); // copying ONLY some query objects (excluding at least a reference to this operator) // -- dimension queries $dimensionQueries = $request->findDimensionQueries(); if (isset($dimensionQueries)) { foreach ($dimensionQueries as $query) { foreach ($query->values as $propertyValue) { foreach ($propertyValue->values as $value) { if ($value->isSubsetBased()) { // skipping other instances which are based on subset of data if their weight is greater than of this operator if (isset($value->weight) && $value->weight > $this->operatorHandler->weight) { continue; } } // updating request configuration for the value supported by this class if ($this->operatorHandler === $value) { $resultColumnName = ParameterHelper::assembleParameterName($query->dimensionName, $query->levelName, $propertyValue->name); // returning only observing property of the dimension level $expressionRequest->addDimensionLevelProperty(0, $query->dimensionName, $query->levelName, $propertyValue->name); // ... and excluding NULL values from evaluation $expressionRequest->addDimensionLevelPropertyQueryValue($query->dimensionName, $query->levelName, $propertyValue->name, data_controller_get_operator_factory_instance()->initiateHandler(NotEqualOperatorHandler::$OPERATOR__NAME, NULL)); // sorting data $expressionRequest->addOrderByColumn(PropertyBasedComparator_DefaultSortingConfiguration::assembleDirectionalPropertyName($resultColumnName, $isSortAscending)); } else { $expressionRequest->addDimensionLevelPropertyQueryValue($query->dimensionName, $query->levelName, $propertyValue->name, $value); } } } } } // -- source dataset property queries $sourceDatasetPropertyQueries = $request->findSourceDatasetPropertyQueries(); if (isset($sourceDatasetPropertyQueries)) { foreach ($sourceDatasetPropertyQueries as $query) { foreach ($query->values as $value) { if ($value->isSubsetBased()) { throw new UnsupportedOperationException(t('Boundary-related operator cannot filter cube source dataset property values yet')); } } $expressionRequest->queries[] = clone $query; } } // -- measure queries $measureQueries = $request->findMeasureQueries(); if (isset($measureQueries)) { foreach ($measureQueries as $query) { foreach ($query->values as $value) { if ($value->isSubsetBased()) { throw new UnsupportedOperationException(t('Boundary-related operator cannot filter measure values yet')); } } $expressionRequest->queries[] = clone $query; } } // limiting response to one record $expressionRequest->setPagination(1, 0); return $this->processCubeExpressionRequest($callcontext, $expressionRequest, $resultColumnName); }
public function importSourceDatasetPropertyQueriesFrom(CubeQueryRequest $sourceQueryRequest) { $sourceSourceDatasetPropertyQueries = $sourceQueryRequest->findSourceDatasetPropertyQueries(); if (isset($sourceSourceDatasetPropertyQueries)) { foreach ($sourceSourceDatasetPropertyQueries as $sourceSourceDatasetPropertyQuery) { $this->importSourceDatasetPropertyQueryFrom($sourceSourceDatasetPropertyQuery); } } }