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