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);
}
