protected function prepareReferencedCubeQueryStatement(AbstractSQLDataSourceQueryHandler $datasourceHandler, DataControllerCallContext $callcontext, Statement $combinedStatement, array $datasetMappedCubeRequests, ReferenceLink $link) { if (!isset($link->nestedLinks)) { return; } $metamodel = data_controller_get_metamodel(); foreach ($link->nestedLinks as $referencedLink) { $referencedRequest = NULL; if (isset($datasetMappedCubeRequests[$referencedLink->dataset->name])) { $referencedRequest = clone $datasetMappedCubeRequests[$referencedLink->dataset->name]; } else { // checking if there is corresponding cube for the referenced dataset $possibleReferencedCube = $metamodel->findCubeByDatasetName($referencedLink->dataset->name); if (isset($possibleReferencedCube)) { $referencedRequest = new CubeQueryRequest($possibleReferencedCube->name); $referencedRequest->referenced = TRUE; $datasetMappedCubeRequests[$possibleReferencedCube->sourceDatasetName] = $referencedRequest; } } if (isset($referencedRequest)) { // preparing parent cube $parentRequest = $datasetMappedCubeRequests[$link->dataset->name]; $parentCubeName = $parentRequest->getCubeName(); $parentCube = $metamodel->getCube($parentCubeName); // preparing referenced cube $referencedCubeName = $referencedRequest->getCubeName(); $referencedCube = $metamodel->getCube($referencedCubeName); // adding required dimensions $joinConditions = NULL; foreach ($referencedLink->parentColumnNames as $columnIndex => $parentColumnName) { // looking for a dimension in parent cube list($parentDimension, ) = $parentCube->getDimensionAndLevelIndexBySourceColumnName($parentColumnName); // looking for a dimension in referenced cube $referencedColumnName = $referencedLink->columnNames[$columnIndex]; list($referencedDimension, ) = $referencedCube->getDimensionAndLevelIndexBySourceColumnName($referencedColumnName); // checking if this dimension is part of query portion of parent request $parentRequestDimensionQuery = $parentRequest->findDimensionQuery($parentDimension->name); if (isset($parentRequestDimensionQuery)) { // preparing level used in this query $parentRequestDimensionLevelIndex4Query = $parentDimension->getLevelIndex($parentRequestDimensionQuery->levelName); // preparing level to be used for the query in referenced request (using the same level index as for parent dimension) $referencedDimensionLevel4Query = $referencedDimension->levels[$parentRequestDimensionLevelIndex4Query]; // copying the query request to referenced cube $referencedRequestDimensionQuery = new __CubeQueryRequest_DimensionQuery($referencedDimension->name, $referencedDimensionLevel4Query->name); $referencedRequestDimensionQuery->values = $parentRequestDimensionQuery->values; $referencedRequest->importDimensionQueryFrom($referencedRequestDimensionQuery); } // checking if there is a related query for parent column name $parentRequestSourceDatasetQuery = $parentRequest->findSourceDatasetPropertyQuery($parentColumnName); if (isset($parentRequestSourceDatasetQuery)) { // copying the query request to referenced cube $referencedRequest->addSourceDatasetPropertyQueryValues($referencedColumnName, $parentRequestSourceDatasetQuery->values); } // checking if this dimension is part of parent request $parentRequestDimension = $parentRequest->findDimension($parentDimension->name); if (!isset($parentRequestDimension)) { // because this dimension is not in list of returned columns we should not use it to link with referenced cube continue; } $selectedParentDimensionLevelIndex = $parentDimension->getLevelIndex($parentRequestDimension->levelName); $selectedParentDimensionLevel = $parentDimension->levels[$selectedParentDimensionLevelIndex]; // preparing level from referenced cube (using the same level index as for parent dimension) $selectedReferencedDimensionLevel = $referencedDimension->levels[$selectedParentDimensionLevelIndex]; $referencedRequest->addDimensionLevel(NULL, $referencedDimension->name, $selectedReferencedDimensionLevel->name); $parentDatabaseColumnName = ParameterHelper::assembleDatabaseColumnName($datasourceHandler->getMaximumEntityNameLength(), $parentRequest->referenced ? ReferencePathHelper::assembleReference($parentCube->sourceDatasetName, $parentDimension->name) : $parentDimension->name, $selectedParentDimensionLevel->name); $referencedDatabaseColumnName = ParameterHelper::assembleDatabaseColumnName($datasourceHandler->getMaximumEntityNameLength(), ReferencePathHelper::assembleReference($referencedCube->sourceDatasetName, $referencedDimension->name), $selectedReferencedDimensionLevel->name); $joinConditions[] = new JoinConditionSection($referencedDatabaseColumnName, new TableColumnConditionSectionValue(self::$TABLE_ALIAS__REFERENCED . $link->linkId, $parentDatabaseColumnName)); } if (!isset($joinConditions)) { throw new IllegalArgumentException(t("There is no common columns to join '@datasetNameA' and '@datasetNameB' datasets", array('@datasetNameA' => $parentCube->publicName, '@datasetNameB' => $referencedCube->publicName))); } // preparing aggregation statement for referenced cube $referencedAggregationStatement = $this->prepareSelectedCubeQueryStatement($datasourceHandler, $callcontext, $referencedRequest); list($isSubqueryRequired, $assembledReferencedCubeSections) = $referencedAggregationStatement->prepareSections(NULL); $referencedCubeSubquerySection = new SubquerySection(Statement::assemble($isSubqueryRequired, NULL, $assembledReferencedCubeSections, Statement::$INDENT_LEFT_OUTER_JOIN_SUBQUERY, FALSE), self::$TABLE_ALIAS__REFERENCED . $referencedLink->linkId); // preparing columns which are returned by referenced aggregation foreach ($referencedAggregationStatement->tables as $table) { if (!isset($table->columns)) { continue; } foreach ($table->columns as $column) { if (!$column->visible) { continue; } $referencedCubeSubquerySection->columns[] = new ColumnSection($column->alias); } } // linking with parent cube foreach ($joinConditions as $joinCondition) { // we do not need to return columns which are used to join with parent cube $referencedCubeSubquerySection->getColumn($joinCondition->subjectColumnName)->visible = FALSE; $referencedCubeSubquerySection->conditions[] = $joinCondition; } // adding to resulting statement $combinedStatement->tables[] = $referencedCubeSubquerySection; // applying referenced cubes measure conditions on resulting statement as well $measureQueries = $referencedRequest->findMeasureQueries(); if (isset($measureQueries)) { foreach ($measureQueries as $measureQuery) { $measureName = ReferencePathHelper::assembleReference($referencedCube->sourceDatasetName, $measureQuery->measureName); $measureDatabaseColumnName = ReferencePathHelper::assembleDatabaseColumnName($datasourceHandler->getMaximumEntityNameLength(), $measureName); foreach ($measureQuery->values as $measureValue) { $combinedStatement->conditions[] = new WhereConditionSection(self::$TABLE_ALIAS__REFERENCED . $referencedLink->linkId, $measureDatabaseColumnName, new ExactConditionSectionValue($datasourceHandler->formatOperatorValue($callcontext, $referencedRequest, $referencedCube->sourceDatasetName, NULL, $measureValue))); } } } } else { throw new UnsupportedOperationException(t('Cube joins using intermediate dataset is not supported yet')); // preparing statement for intermediate dataset $requiredColumnNames = $referencedLink->columnNames; $referencedIntermediateDatasetStatement = $datasourceHandler->prepareDatasetSourceStatement($callcontext, $referencedLink->dataset, $requiredColumnNames); // adding condition to join with parent statement $referencedIntermediateDatasetTableSection = $referencedIntermediateDatasetStatement->tables[0]; foreach ($referencedLink->columnNames as $columnIndex => $referencedColumnName) { $referencedDatabaseColumnName = $referencedColumnName; $parentColumnName = $referencedLink->parentColumnNames[$columnIndex]; $parentDatabaseColumnName = $parentColumnName; $referencedIntermediateDatasetTableSection->conditions[] = new JoinConditionSection($referencedDatabaseColumnName, new TableColumnConditionSectionValue(self::$TABLE_ALIAS__REFERENCED . $link->linkId, $parentDatabaseColumnName)); } $combinedStatement->merge($referencedIntermediateDatasetStatement); } // recursively check nested levels $this->prepareReferencedCubeQueryStatement($datasourceHandler, $callcontext, $combinedStatement, $datasetMappedCubeRequests, $referencedLink); } }
public function queryCube(DataControllerCallContext $callcontext, CubeQueryRequest $request, ResultFormatter $resultFormatter) { $cubeName = $request->getCubeName(); LogHelper::log_notice(t('Querying SQL-based cube: @cubeName', array('@cubeName' => $cubeName))); $environment_metamodel = data_controller_get_environment_metamodel(); $metamodel = data_controller_get_metamodel(); $callcontext->columnMapping = NULL; $cube = $metamodel->getCube($cubeName); LogHelper::log_debug($cube); $cubeDatasetName = $cube->sourceDatasetName; $cubeDataset = $metamodel->getDataset($cubeDatasetName); $datasource = $environment_metamodel->getDataSource($cubeDataset->datasourceName); // aliases for tables $TABLE_ALIAS__JOIN = 'j'; $tableJoinIndex = 0; // preparing statement which aggregates data $aggrStatement = $this->prepareCubeQueryStatement($callcontext, $request); list($isSubqueryRequired, $assembledAggregationSections) = $aggrStatement->prepareSections(NULL); // assembling porting of SQL which is responsible for aggregation if (isset($request->referencedRequests)) { $joinStatement = $aggrStatement; // changing alias of first table. This new alias is expected the following code to join with lookup tables $joinStatement->updateTableAlias($joinStatement->tables[0]->alias, $TABLE_ALIAS__JOIN); } else { $joinStatement = new Statement(); $aggregationTableSection = new SubquerySection(Statement::assemble($isSubqueryRequired, NULL, $assembledAggregationSections, Statement::$INDENT_SUBQUERY, FALSE), $TABLE_ALIAS__JOIN); $joinStatement->tables[] = $aggregationTableSection; } // adding support for dimension level properties if (isset($request->dimensions)) { foreach ($request->dimensions as $requestDimension) { $dimensionName = $requestDimension->dimensionName; $dimension = $cube->getDimension($dimensionName); $levelName = $requestDimension->levelName; // we do not need to map the column. It was done in prepareCubeQueryStatement() $levelDatabaseColumnName = ParameterHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), $dimensionName, $levelName); // adding support for level root column $levelRootColumn = new ColumnSection($levelDatabaseColumnName); $levelRootColumn->requestColumnIndex = $requestDimension->requestColumnIndex; $levelRootColumn->visible = isset($requestDimension->requestColumnIndex); $aggregationTableSection->columns[] = $levelRootColumn; if (!isset($requestDimension->properties)) { continue; } $tableJoinIndex++; $levelTableAlias = $TABLE_ALIAS__JOIN . $tableJoinIndex; $level = $dimension->getLevel($levelName); $levelDataset = $metamodel->getDataset($level->datasetName); // preparing list of columns which are accessed by this dataset $usedColumnNames = NULL; $levelColumnAliasMapping = NULL; foreach ($requestDimension->properties as $property) { $propertyName = $property->name; $responseColumnName = ParameterHelper::assembleParameterName($dimensionName, $levelName, $propertyName); $databaseColumnName = ParameterHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), $dimensionName, $levelName, $propertyName); $callcontext->columnMapping[$databaseColumnName] = $responseColumnName; ArrayHelper::addUniqueValue($usedColumnNames, $propertyName); $levelColumnAliasMapping[$propertyName] = $databaseColumnName; } $isLevelKeyColumnAdded = ArrayHelper::addUniqueValue($usedColumnNames, $level->key); $levelStatement = $this->prepareDatasetSourceStatement($callcontext, $levelDataset, $usedColumnNames); // updating level statement table aliases $levelStatement->addTableAliasPrefix($levelTableAlias); foreach ($levelStatement->tables as $table) { if (!isset($table->columns)) { $table->columns = array(); // We do not need any columns } } // updating level statement column aliases foreach ($requestDimension->properties as $property) { $oldColumnAlias = $property->name; $newColumnAlias = $levelColumnAliasMapping[$oldColumnAlias]; $levelTableSection = $levelStatement->getColumnTable($oldColumnAlias, TRUE); $levelColumnSection = $levelTableSection->findColumnByAlias($oldColumnAlias); if (isset($levelColumnSection)) { $levelColumnSection->alias = $newColumnAlias; } else { $levelColumnSection = new ColumnSection($oldColumnAlias, $newColumnAlias); $levelTableSection->columns[] = $levelColumnSection; } $levelColumnSection->requestColumnIndex = $property->requestColumnIndex; } // adding condition to join with 'main' statement $levelKeyTableSection = $levelStatement->getColumnTable($level->key); $levelKeyTableSection->conditions[] = new JoinConditionSection($level->key, new TableColumnConditionSectionValue($TABLE_ALIAS__JOIN, $levelDatabaseColumnName)); // merging with 'main' statement $joinStatement->merge($levelStatement); // we do not need to return level key column if ($isLevelKeyColumnAdded && isset($levelKeyTableSection)) { // FIXME this code does not work in the following case: // - our lookup dataset is fact dataset // - we need to work with project_id column from that dataset // - the column is present in *_facts and contains numeric value // - the column is present in *_c_project_id table and contains numeric value // - column 'value' in *_c_project_id table assigned an alias project_id // - more about implementation is in ReferenceDimensionDatasetAssembler // - the code is partially fixed by using $visibleOnly parameter $tableSection = $levelStatement->getColumnTable($level->key, TRUE); $keyColumn = $tableSection->findColumnByAlias($level->key); if (isset($keyColumn)) { $keyColumn->visible = FALSE; } } } } $isJoinUsed = $tableJoinIndex > 0; if ($isJoinUsed) { // adding measures if (isset($request->measures)) { foreach ($request->measures as $requestMeasure) { $measureName = $requestMeasure->measureName; // we do not need to map the column. It was done in prepareCubeQueryStatement() $databaseColumnName = ParameterHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), $measureName); $measureSection = new ColumnSection($databaseColumnName); $measureSection->requestColumnIndex = $requestMeasure->requestColumnIndex; $aggregationTableSection->columns[] = $measureSection; } } list($isSubqueryRequired, $assembledJoinSections) = $joinStatement->prepareSections(NULL); $sql = Statement::assemble($isSubqueryRequired, NULL, $assembledJoinSections); } else { $sql = Statement::assemble($isSubqueryRequired, NULL, $assembledAggregationSections); } // applying ordering $sql = $this->applyOrderBy($sql, $request); // applying pagination $this->applyPagination($request, $sql); // processing prepared sql and returning data LogHelper::log_info(new StatementLogMessage('cube.query', $sql)); return $this->executeQuery($callcontext, $datasource, $sql, $resultFormatter); }
public function formatPropertyNameAsDatabaseColumnName($maximumLength) { return ParameterHelper::assembleDatabaseColumnName($maximumLength, $this->elementName, $this->subElementName, $this->elementPropertyName); }