protected function assembleConnectedDatasetSourceStatement(DataControllerCallContext $callcontext, ReferenceLink $link, array $columnNames, array $linkExecutionStack = NULL) { $TABLE_ALIAS__LINK = 'l'; $nestedLinkExecutionStack = $linkExecutionStack; $nestedLinkExecutionStack[] = $link; $selectedColumnNames = ReferenceLinkBuilder::selectReferencedColumnNames4ReferenceLink($nestedLinkExecutionStack, $columnNames); $linkTableAliasPrefix = $TABLE_ALIAS__LINK . $link->linkId; $statement = $this->assembleDatasetSourceStatement($callcontext, $link->dataset, $selectedColumnNames); $statement->addTableAliasPrefix($linkTableAliasPrefix); // adding columns which we use to join with parent dataset if (!$link->isRoot()) { foreach ($link->columnNames as $columnName) { $joinColumnAlias = ReferencePathHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), ReferencePathHelper::assembleReference($link->dataset->name, $columnName)); $joinTable = $statement->findColumnTable($columnName); if (!isset($joinTable)) { $joinTable = $statement->tables[0]; } $joinColumn = $joinTable->findColumnByAlias($joinColumnAlias); if (!isset($joinColumn)) { $joinTable->columns[] = new ColumnSection($columnName, $joinColumnAlias); } } } // adding columns which we use to join with nested datasets if (isset($link->nestedLinks)) { foreach ($link->nestedLinks as $nestedLink) { foreach ($nestedLink->parentColumnNames as $parentColumnName) { $parentColumnAlias = ReferencePathHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), ReferencePathHelper::assembleReference($link->dataset->name, $parentColumnName)); $joinTable = $statement->getColumnTable($parentColumnName); $joinColumn = $joinTable->findColumnByAlias($parentColumnAlias); if (!isset($joinColumn)) { $joinTable->columns[] = new ColumnSection($parentColumnName, $parentColumnAlias); } } } } // collecting columns which we need to mark as invisible $shouldBeInvisibleColumns = NULL; foreach ($statement->tables as $table) { if (isset($table->columns)) { foreach ($table->columns as $column) { $shouldBeInvisibleColumns[$table->alias][$column->alias] = $column; } } else { $table->columns = array(); // We do not need any columns } } // adding or making as visible columns which we need to return if (isset($selectedColumnNames)) { foreach ($selectedColumnNames as $originalColumnName => $selectedColumnName) { // we need to show only those columns which are requested. // All intermediate columns (which are used to link with nested datasets) will not be shown if (array_search($originalColumnName, $columnNames) !== FALSE) { $databaseColumnName = ReferencePathHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), $originalColumnName); $table = $statement->getColumnTable($selectedColumnName, TRUE); $column = $table->findColumnByAlias($databaseColumnName); if (isset($column)) { $column->visible = TRUE; unset($shouldBeInvisibleColumns[$table->alias][$column->alias]); } else { $column = $table->findColumnByAlias($selectedColumnName); if (isset($column)) { // adding clone of the same column with another alias $column = clone $column; $column->visible = TRUE; $column->alias = $databaseColumnName; } else { $column = new ColumnSection($selectedColumnName, $databaseColumnName); } $table->columns[] = $column; } $callcontext->columnMapping[$databaseColumnName] = $originalColumnName; } } } if (isset($shouldBeInvisibleColumns)) { foreach ($shouldBeInvisibleColumns as $tableColumns) { foreach ($tableColumns as $column) { $column->visible = FALSE; } } } // supporting nested links if (isset($link->nestedLinks)) { foreach ($link->nestedLinks as $nestedLink) { $nestedStatement = $this->assembleConnectedDatasetSourceStatement($callcontext, $nestedLink, $columnNames, $nestedLinkExecutionStack); foreach ($nestedLink->parentColumnNames as $referencePointColumnIndex => $parentColumnName) { // preparing parent table alias $parentColumnAlias = ReferencePathHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), ReferencePathHelper::assembleReference($link->dataset->name, $parentColumnName)); $parentTableAlias = $statement->getColumnTable($parentColumnAlias)->alias; // linking with parent $nestedColumnName = $nestedLink->columnNames[$referencePointColumnIndex]; $nestedColumnAlias = ReferencePathHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), ReferencePathHelper::assembleReference($nestedLink->dataset->name, $nestedColumnName)); $nestedStatement->getColumnTable($nestedColumnAlias)->conditions[] = new JoinConditionSection($nestedColumnName, new TableColumnConditionSectionValue($parentTableAlias, $parentColumnName)); } $statement->merge($nestedStatement); } } return $statement; }
public function formatPropertyNameAsDatabaseColumnName($maximumLength) { return ReferencePathHelper::assembleDatabaseColumnName($maximumLength, $this->propertyName); }
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 assemble(AbstractSQLDataSourceQueryHandler $datasourceHandler, DataControllerCallContext $callcontext, DatasetMetaData $dataset, array $columnNames = NULL) { $statement = new Statement(); $TABLE_ALIAS__FACTS = 'dsf'; $TABLE_ALIAS__COLUMN_JOIN = 'cj'; $metamodel = data_controller_get_metamodel(); $cubeName = $dataset->name; $cube = $metamodel->getCube($cubeName); $factsDataset = $metamodel->getDataset($cube->sourceDatasetName); self::$TABLE_ALIAS_SUFFIX__SEQUENCE++; $factsTable = new DatasetSection($factsDataset, $TABLE_ALIAS__FACTS . self::$TABLE_ALIAS_SUFFIX__SEQUENCE); $statement->tables[] = $factsTable; foreach ($dataset->getColumns() as $column) { $columnName = $column->name; $columnDefaultAlias = NULL; if (isset($columnNames)) { $columnDefaultAlias = array_search($columnName, $columnNames); if ($columnDefaultAlias === FALSE) { continue; } // fixing the alias if the list of columns is not associative array if (is_int($columnDefaultAlias)) { $columnDefaultAlias = NULL; } } // FIXME the following code does not work in the following situation: // 1) we need to query a dataset which is defined in GovDashboard using file upload // 2) column names are not provided // 3) for columns which are references to lookup, value from the primary key from lookup table is returned // even if in definition of the dataset we have columns with the following application type: name@lookup, code @lookup and etc $isReferenceUsed = FALSE; $handler = DimensionLookupFactory::getInstance()->findHandler($column->type->applicationType); if (isset($handler)) { // FIXME remove this implementation when we implement 'Executable Tree' functionality list($connectedDatasetName, $connectedColumnName) = $handler->adjustReferencePointColumn($metamodel, $factsDataset->name, $columnName); if ($connectedDatasetName != $factsDataset->name || $connectedColumnName != $columnName) { self::$TABLE_ALIAS_SUFFIX__SEQUENCE++; $columnJoinAliasPrefix = $TABLE_ALIAS__COLUMN_JOIN . self::$TABLE_ALIAS_SUFFIX__SEQUENCE; $connectedDataset = $metamodel->getDataset($connectedDatasetName); $connectedDatasetKeyColumnName = $connectedDataset->getKeyColumn()->name; // preparing list of columns we want to get from connected dataset $connectedDatasetColumnNames = NULL; ArrayHelper::addUniqueValue($connectedDatasetColumnNames, $connectedDatasetKeyColumnName); ArrayHelper::addUniqueValue($connectedDatasetColumnNames, $connectedColumnName); $connectedStatement = $datasourceHandler->assembleDatasetSourceStatement($callcontext, $connectedDataset, $connectedDatasetColumnNames); $connectedStatement->addTableAliasPrefix($columnJoinAliasPrefix); $connectedTable = $connectedStatement->getColumnTable($connectedColumnName); // registering the column for facts table $factsTableColumn = new ColumnSection($columnName, 'fact_' . $columnName); $factsTableColumn->visible = FALSE; $factsTable->columns[] = $factsTableColumn; // adjusting key column from lookup $connectedDatasetKeyColumn = $connectedTable->getColumn($connectedDatasetKeyColumnName); $connectedDatasetKeyColumn->alias = 'lookup_' . $connectedDatasetKeyColumnName; $connectedDatasetKeyColumn->visible = FALSE; // adjusting value column from lookup $connectedDatasetValueColumn = $connectedTable->getColumn($connectedColumnName); $connectedDatasetValueColumn->alias = $columnName; // the key column could be the same as value column $connectedDatasetValueColumn->visible = TRUE; // new value column which uses composite name as column alias if (isset($columnDefaultAlias)) { $connectedDatasetValueColumn2 = new ColumnSection($connectedColumnName, ReferencePathHelper::assembleDatabaseColumnName($datasourceHandler->getMaximumEntityNameLength(), $columnDefaultAlias)); $connectedDatasetValueColumn2->visible = FALSE; $connectedTable->columns[] = $connectedDatasetValueColumn2; } // linking the lookup table with the facts table $connectedTable->conditions[] = new JoinConditionSection($connectedDatasetKeyColumn->alias, new TableColumnConditionSectionValue($factsTable->alias, $columnName)); $statement->merge($connectedStatement); $isReferenceUsed = TRUE; } } if (!$isReferenceUsed) { $factsTable->columns[] = new ColumnSection($columnName); } } return $statement; }