public function assemble(AbstractSQLDataSourceQueryHandler $datasourceHandler, AbstractQueryRequest $request, DatasetMetaData $dataset, array $columnNames = NULL) { $engine = QueryEngineFactory::getInstance()->getHandler(); $statement = $engine->newSelectStatement(); // FIXME use the following code /* // the following code was commended out because functionality in Statement::findColumnTableByReferencePath() $table->dataset->name $tableName = assemble_database_entity_name($datasourceHandler, $dataset->datasourceName, $dataset->source); $table = $statement->newTable($tableName); */ $table = new DatasetSection($dataset); $statement->tables[] = $table; if (isset($columnNames)) { $columnReferenceFactory = new CompositeColumnReferenceFactory(array( $dataset, new FormulaReferenceFactory($request->getFormulas()))); $expressionAssembler = new FormulaExpressionAssembler($columnReferenceFactory); foreach ($columnNames as $columnName) { $column = $columnReferenceFactory->findColumn($columnName); if (isset($column) && ($column->persistence == FormulaMetaData::PERSISTENCE__CALCULATED)) { $table->newCalculatedColumn($expressionAssembler->assemble($column), $columnName); } else { $table->newColumn($columnName); } } } return $statement; }
public function assemble(AbstractSQLDataSourceQueryHandler $datasourceHandler, AbstractQueryRequest $request, DatasetMetaData $dataset, array $columnNames = NULL) { $engine = QueryEngineFactory::getInstance()->getHandler(); $statement = $engine->newSelectStatement(); $query = $statement->newSubquery($dataset->source); if (isset($columnNames)) { foreach ($columnNames as $columnName) { $query->newColumn($columnName); } } return $statement; }
public function queryCube(DataControllerCallContext $callcontext, CubeQueryRequest $request) { $cubeName = $request->getCubeName(); LogHelper::log_info(t('Querying SQL-based cube: @cubeName', array('@cubeName' => $cubeName))); $environment_metamodel = data_controller_get_environment_metamodel(); $metamodel = data_controller_get_metamodel(); $this->getExtension('adjustRequest')->adjustCubeQueryRequest($this, $request); $callcontext->columnMapping = NULL; $cube = $metamodel->getCube($cubeName); $factsDataset = $metamodel->getDataset($cube->factsDatasetName); $datasource = $environment_metamodel->getDataSource($factsDataset->datasourceName); $engine = QueryEngineFactory::getInstance()->getHandler(); $generationContext = $engine->prepareStatementGenerationContext($request, $cube); // 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 portion 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, SelectStatementPrint::INDENT__SUBQUERY, FALSE), $TABLE_ALIAS__JOIN); $joinStatement->tables[] = $aggregationTableSection; } // adding support for dimension columns if (isset($request->dimensions)) { foreach ($request->dimensions as $requestDimension) { $dimensionName = $requestDimension->name; $dimension = $cube->findDimension($dimensionName); // we do not need to map the column. It was done in prepareCubeQueryStatement() $dimensionDatabaseColumnName = DataSourceColumnNameHelper::generateFromParameterElements($this->getMaximumEntityNameLength(), $dimensionName); // adding support for dimension column $dimensionColumn = new ColumnSection($dimensionDatabaseColumnName); $dimensionColumn->requestColumnIndex = $requestDimension->requestColumnIndex; $dimensionColumn->visible = isset($requestDimension->requestColumnIndex); $aggregationTableSection->columns[] = $dimensionColumn; if (!isset($requestDimension->columns)) { continue; } // preparing list of columns which are accessed by this dataset $usedColumnNames = NULL; $dimensionColumnAliasMapping = NULL; foreach ($requestDimension->columns as $requestColumn) { $responseColumnName = ParameterNameHelper::assemble($dimensionName, $requestColumn->name); $databaseColumnName = DataSourceColumnNameHelper::generateFromParameterElements( $this->getMaximumEntityNameLength(), $dimensionName, $requestColumn->name); $callcontext->columnMapping[$databaseColumnName] = $responseColumnName; ArrayHelper::addUniqueValue($usedColumnNames, $requestColumn->name); $dimensionColumnAliasMapping[$requestColumn->name] = $databaseColumnName; } $isJoinWithDimensionDatasetRequired = $generationContext->dimensionJoinPhase[__DefaultQueryEngine_StatementGenerationContext::DIMENSION_JOIN_PHASE__GROUPING_WITH_LOOKUP_AFTER][$dimensionName]; if ($isJoinWithDimensionDatasetRequired) { $tableJoinIndex++; $dimensionTableAlias = $TABLE_ALIAS__JOIN . $tableJoinIndex; $dimensionDataset = $metamodel->getDataset($dimension->datasetName); $isDimensionKeyColumnAdded = ArrayHelper::addUniqueValue($usedColumnNames, $dimension->key); $dimensionStatement = $this->prepareDatasetSourceStatement($callcontext, $request, $dimensionDataset, $usedColumnNames); // updating dimension statement table aliases $dimensionStatement->addTableAliasPrefix($dimensionTableAlias); foreach ($dimensionStatement->tables as $table) { if (!isset($table->columns)) { $table->columns = array(); // We do not need any columns } } // updating dimension statement column aliases foreach ($requestDimension->columns as $requestColumn) { $oldColumnAlias = $requestColumn->name; $newColumnAlias = $dimensionColumnAliasMapping[$oldColumnAlias]; $dimensionTableSection = $dimensionStatement->getColumnTable($oldColumnAlias, TRUE); $dimensionColumnSection = $dimensionTableSection->findColumnByAlias($oldColumnAlias); if (isset($dimensionColumnSection)) { $dimensionColumnSection->alias = $newColumnAlias; } else { $dimensionColumnSection = new ColumnSection($oldColumnAlias, $newColumnAlias); $dimensionTableSection->columns[] = $dimensionColumnSection; } $dimensionColumnSection->requestColumnIndex = $requestColumn->requestColumnIndex; } // adding condition to join with 'main' statement $dimensionKeyTableSection = $dimensionStatement->getColumnTable($dimension->key); $dimensionKeyTableSection->conditions[] = new JoinConditionSection( $dimension->key, new TableColumnConditionSectionValue($TABLE_ALIAS__JOIN, $dimensionDatabaseColumnName)); // merging with 'main' statement $joinStatement->merge($dimensionStatement); // we do not need to return dimension key column if ($isDimensionKeyColumnAdded && isset($dimensionKeyTableSection)) { // 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 = $dimensionStatement->getColumnTable($dimension->key, TRUE); $keyColumn = $tableSection->findColumnByAlias($dimension->key); if (isset($keyColumn)) { $keyColumn->visible = FALSE; } } } else { foreach ($requestDimension->columns as $requestColumn) { $oldColumnAlias = $requestColumn->name; $newColumnAlias = $dimensionColumnAliasMapping[$oldColumnAlias]; $column = new ColumnSection($newColumnAlias); $column->requestColumnIndex = $requestColumn->requestColumnIndex; $aggregationTableSection->columns[] = $column; } } } } $isJoinUsed = $tableJoinIndex > 0; if ($isJoinUsed) { // adding measures if (isset($request->measures)) { foreach ($request->measures as $requestMeasure) { $measureName = $requestMeasure->name; // we do not need to map the column. It was done in prepareCubeQueryStatement() $databaseColumnName = DataSourceColumnNameHelper::generateFromParameterElements( $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 sorting if (isset($request->sortingConfigurations)) { $adjustedColumns = NULL; foreach ($request->sortingConfigurations as $sortingConfiguration) { // TODO try to use the same functionality for list and cube requests $adjustedColumn = DataSourceColumnNameHelper::generateFromParameterElements( $this->getMaximumEntityNameLength(), $sortingConfiguration->rootName, $sortingConfiguration->leafName); // adjusting direction of the sorting if (!$sortingConfiguration->isSortAscending) { $adjustedColumn = $adjustedColumn . ' DESC'; } $adjustedColumns[] = $adjustedColumn; } if (count($adjustedColumns) > 0) { $sql .= "\n ORDER BY " . implode(', ', $adjustedColumns); } } // 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); }