protected function collectReferencedFormulaNames(AbstractQueryRequest $request) {
$usedFormulaNames = NULL;
// checking dimensions
if (isset($request->dimensions)) {
foreach ($request->dimensions as $dimension) {
$dimensionName = $dimension->name;
if ($request->findFormula($dimensionName) != NULL) {
ArrayHelper::addUniqueValue($usedFormulaNames, $dimensionName);
}
}
}
// checking measures
if (isset($request->measures)) {
foreach ($request->measures as $measure) {
$measureName = $measure->name;
if ($request->findFormula($measureName) != NULL) {
ArrayHelper::addUniqueValue($usedFormulaNames, $measureName);
}
}
}
// checking conditions
if (isset($request->queries)) {
foreach ($request->queries as $query) {
$name = $query->name;
if ($request->findFormula($name) != NULL) {
ArrayHelper::addUniqueValue($usedFormulaNames, $name);
}
}
}
return $usedFormulaNames;
}
private function registerDatasetConfig(array &$datasetConfigs = NULL, $index, DatasetMetaData $dataset = NULL, $columnName = NULL, AbstractConditionSection $condition = NULL) {
if (isset($datasetConfigs[$index])) {
$datasetConfig = $datasetConfigs[$index];
}
else {
// TODO create a class
$datasetConfig = new stdClass();
$datasetConfig->dataset = NULL;
$datasetConfig->usedColumnNames = NULL;
$datasetConfig->conditions = NULL;
$datasetConfigs[$index] = $datasetConfig;
}
if (isset($dataset)) {
if (isset($datasetConfig->dataset)) {
if ($datasetConfig->dataset->name !== $dataset->name) {
throw new IllegalStateException(t(
'Inconsistent dataset configuration: [@datasetName, @tableDatasetName]',
array('@datasetName' => $dataset->publicName, '@tableDatasetName' => $datasetConfig->dataset->publicName)));
}
}
else {
$datasetConfig->dataset = $dataset;
}
}
if (isset($columnName)) {
ArrayHelper::addUniqueValue($datasetConfig->usedColumnNames, $columnName);
}
if (isset($condition)) {
$datasetConfig->conditions[] = $condition;
}
}
protected function collectReferencedFormulaNames(AbstractQueryRequest $request) {
$usedFormulaNames = NULL;
// checking conditions
if (isset($request->queries)) {
foreach ($request->queries as $index => $query) {
foreach ($query as $columnName => $values) {
if ($request->findFormula($columnName) != NULL) {
ArrayHelper::addUniqueValue($usedFormulaNames, $columnName);
}
}
}
}
// checking columns
if ($request instanceof DatasetQueryRequest) {
if (isset($request->columns)) {
foreach ($request->columns as $columnName) {
if ($request->findFormula($columnName) != NULL) {
ArrayHelper::addUniqueValue($usedFormulaNames, $columnName);
}
}
}
}
return $usedFormulaNames;
}
protected function processMetaModelFilters(array &$processedFilters = NULL, array $filters = NULL) {
if (!isset($filters)) {
return;
}
foreach ($filters as $className => $properties) {
foreach ($properties as $propertyName => $values) {
$uniqueValues = isset($processedFilters[$className][$propertyName])
? $processedFilters[$className][$propertyName]
: NULL;
if ($uniqueValues === FALSE) {
// this property should be ignored
}
else {
foreach ($values as $value) {
if (isset($value)) {
ArrayHelper::addUniqueValue($uniqueValues, $value);
}
else {
// if there is at least one NULL value we ignore the property completely
$uniqueValues = FALSE;
break;
}
}
}
$processedFilters[$className][$propertyName] = $uniqueValues;
}
}
}
public function generate(array $possiblyLinkableDatasetNames = NULL) {
$adjustedPossiblyLinkableDatasetNames = NULL;
if (isset($possiblyLinkableDatasetNames)) {
foreach ($possiblyLinkableDatasetNames as $possiblyLinkableDatasetName) {
$adjustedPossiblyLinkableDatasetName = $this->adjustDatasetName($possiblyLinkableDatasetName);
// we should not try to link with itself
if ($adjustedPossiblyLinkableDatasetName == $this->primaryDatasetName) {
continue;
}
ArrayHelper::addUniqueValue($adjustedPossiblyLinkableDatasetNames, $adjustedPossiblyLinkableDatasetName);
}
}
// preparing reference paths for all datasets which we need to test for possible linkage
$referencePaths = NULL;
if (isset($adjustedPossiblyLinkableDatasetNames)) {
foreach ($adjustedPossiblyLinkableDatasetNames as $adjustedPossiblyLinkableDatasetName) {
// column name should not be provided because we just try to link with the dataset
$referencePath = ReferencePathHelper::assembleReference($adjustedPossiblyLinkableDatasetName, NULL);
$referencePaths[$referencePath] = FALSE; // FALSE - if we cannot find link it is ok. Not everything could be connected
}
}
$linkBuilder = new ReferenceLinkBuilder();
$link = $linkBuilder->prepareReferenceBranches($this->primaryDatasetName, $referencePaths);
return $link;
}
public function insertOrUpdateOrDeleteDatasetRecords(DataControllerCallContext $callcontext, AbstractDatasetManipulationRequest $request) {
$environment_metamodel = data_controller_get_environment_metamodel();
$metamodel = data_controller_get_metamodel();
$datasourceQueryHandler = DataSourceQueryFactory::getInstance()->getHandler($this->getDataSourceType());
$dataset = $metamodel->getDataset($request->datasetName);
$datasource = $environment_metamodel->getDataSource($dataset->datasourceName);
$recordMetaData = isset($request->recordsHolder->recordMetaData) ? $request->recordsHolder->recordMetaData : $dataset;
$isRecordIndexed = $request->recordsHolder instanceof IndexedRecordsHolder;
$keyColumnNames = $recordMetaData->getKeyColumnNames();
$nonkeyColumnNames = $recordMetaData->findNonKeyColumnNames();
// preparing a request to find existing records
$queryRequest = new DatasetQueryRequest($request->datasetName);
// loading only key columns from database
$queryRequest->addColumns($keyColumnNames);
// a request can be more efficient for single column key
if (count($keyColumnNames) == 1) {
list($keyColumnIndex, $keyColumnName) = each($keyColumnNames);
$keyColumnIdentifier = $isRecordIndexed ? $keyColumnIndex : $keyColumnName;
$keyValues = NULL;
foreach ($request->recordsHolder->records as $record) {
ArrayHelper::addUniqueValue($keyValues, $record->getColumnValue($keyColumnIdentifier, TRUE));
}
$queryRequest->addQueryValue(
0,
$keyColumnName, OperatorFactory::getInstance()->initiateHandler(EqualOperatorHandler::OPERATOR__NAME, array($keyValues)));
}
else {
for ($i = 0, $count = count($request->recordsHolder->records); $i < $count; $i++) {
$record = $request->recordsHolder->records[$i];
foreach ($keyColumnNames as $keyColumnIndex => $keyColumnName) {
$keyColumnIdentifier = $isRecordIndexed ? $keyColumnIndex : $keyColumnName;
$keyColumnValue = $record->getColumnValue($keyColumnIdentifier, TRUE);
$queryRequest->addQueryValue(
$i,
$keyColumnName, OperatorFactory::getInstance()->initiateHandler(EqualOperatorHandler::OPERATOR__NAME, $keyColumnValue));
}
}
}
// loading existing records ... if any
$existingRecordFormatter = new QueryKeyResultFormatter($keyColumnNames);
$existingRecords = $existingRecordFormatter->formatRecords($datasourceQueryHandler->queryDataset($callcontext, $queryRequest));
// sorting out records for insert, update and delete operations
$keyedRecords = $insertedRecordKeys = $updatedRecordKeys = $deletedRecordKeys = NULL;
foreach ($request->recordsHolder->records as $record) {
$keyParts = NULL;
foreach ($keyColumnNames as $keyColumnIndex => $keyColumnName) {
$keyColumnIdentifier = $isRecordIndexed ? $keyColumnIndex : $keyColumnName;
$keyParts[] = $record->getColumnValue($keyColumnIdentifier, TRUE);
}
$key = ArrayHelper::prepareCompositeKey($keyParts);
$keyedRecords[$key] = $record;
// checking if the record has to be deleted
$isDeletable = TRUE;
if (isset($nonkeyColumnNames)) {
foreach ($nonkeyColumnNames as $columnIndex => $columnName) {
$columnIdentifier = $isRecordIndexed ? $columnIndex : $columnName;
if ($record->getColumnValue($columnIdentifier) != NULL) {
$isDeletable = FALSE;
break;
}
}
}
else {
// the dataset has NO non-key columns. We should not delete these records
$isDeletable = FALSE;
}
if ($isDeletable) {
unset($insertedRecordKeys[$key]);
unset($updatedRecordKeys[$key]);
// the record physically present in database and needs to be deleted
if (isset($existingRecords[$key])) {
unset($existingRecords[$key]);
$deletedRecordKeys[$key] = TRUE;
}
}
elseif (isset($insertedRecordKeys[$key])) {
// the key has been already used to insert a record within this batch. This record needs to be part of update operation
$updatedRecordKeys[$key] = TRUE;
}
elseif (isset($existingRecords[$key])) {
$updatedRecordKeys[$key] = TRUE;
}
else {
$insertedRecordKeys[$key] = TRUE;
}
}
$sqls = NULL;
// deleting existing records
$deletedRecordCount = 0;
if (isset($deletedRecordKeys)) {
$deleteRecordHolder = $this->prepareRecordHolder($request, $keyedRecords, $deletedRecordKeys);
// preparing request
$deleteRequest = new DatasetDeleteRequest($request->datasetName, $deleteRecordHolder);
// preparing statements to delete records from the database
ArrayHelper::appendValue($sqls, $this->prepareDeleteDatasetRecordStatements($callcontext, $deleteRequest));
$deletedRecordCount = count($deleteRecordHolder->records);
}
// inserting new records
$insertedRecordCount = 0;
if (isset($insertedRecordKeys)) {
$insertRecordHolder = $this->prepareRecordHolder($request, $keyedRecords, $insertedRecordKeys);
// preparing request
$insertRequest = new DatasetInsertRequest($request->datasetName, $insertRecordHolder);
// preparing statements to insert records into the database
ArrayHelper::appendValue($sqls, $this->prepareInsertDatasetRecordStatements($callcontext, $insertRequest));
$insertedRecordCount = count($insertRecordHolder->records);
}
// updating existing records
$updatedRecordCount = 0;
if (isset($updatedRecordKeys)) {
$updateRecordHolder = $this->prepareRecordHolder($request, $keyedRecords, $updatedRecordKeys);
// preparing request
$updateRequest = new DatasetUpdateRequest($request->datasetName, $updateRecordHolder);
// preparing statements to update records in the database
ArrayHelper::appendValue($sqls, $this->prepareUpdateDatasetRecordStatements($callcontext, $updateRequest));
$updatedRecordCount = count($updateRecordHolder->records);
}
$affectedRecordCount = isset($sqls) ? $this->executeManipulationStatementBatch($datasource, $sqls) : 0;
if (($insertedRecordCount + $updatedRecordCount + $deletedRecordCount) < $affectedRecordCount) {
throw new IllegalStateException(t('Number of affected records is greater than expected number of inserted, updated and deleted records'));
}
return array($insertedRecordCount, $updatedRecordCount, $deletedRecordCount);
}
public function addColumn($column) {
$isFormula = $this->findFormula($column) != NULL;
ReferencePathHelper::checkReference($column, TRUE, !$isFormula);
ArrayHelper::addUniqueValue($this->columns, $column);
}
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 loadIdentifiers($lookupDatasetName, array $uniqueSetColumns, array &$lookupValues) {
$dataQueryController = data_controller_get_instance();
$metamodel = data_controller_get_metamodel();
$lookupDataset = $metamodel->getDataset($lookupDatasetName);
$identifierColumnName = $lookupDataset->getKeyColumn()->name;
$lookupCacheKey = $this->prepareLookupCacheKey($lookupDataset->name);
$isCompositeUniqueSet = count($uniqueSetColumns) > 1;
// preparing parameters for the query
$queryParameters = NULL;
foreach ($lookupValues as $lookupKey => $lookupValue) {
if (isset($lookupValue->identifier)) {
continue;
}
if (isset($this->cachedIdentifiers[$lookupCacheKey][$lookupKey])) {
$lookupValues[$lookupKey]->identifier = $this->cachedIdentifiers[$lookupCacheKey][$lookupKey];
continue;
}
if ($isCompositeUniqueSet) {
$keyColumnValues = NULL;
foreach ($uniqueSetColumns as $column) {
$columnName = $column->name;
$keyColumnValues[$columnName] = $lookupValue->$columnName;
}
$queryParameters[] = $keyColumnValues;
}
else {
$columnName = $uniqueSetColumns[0]->name;
$queryParameters[$columnName][] = $lookupValue->$columnName;
}
}
if (!isset($queryParameters)) {
return;
}
// preparing columns for the query
$queryColumns = array($identifierColumnName);
foreach ($uniqueSetColumns as $column) {
ArrayHelper::addUniqueValue($queryColumns, $column->name);
}
// loading data from database for 'missing' records
$loadedLookupProperties = $dataQueryController->queryDataset($lookupDataset->name, $queryColumns, $queryParameters);
// processing found records
if (isset($loadedLookupProperties)) {
$foundUnmatchedIdentifiers = FALSE;
foreach ($loadedLookupProperties as $lookupProperties) {
$identifier = $lookupProperties[$identifierColumnName];
// preparing lookup key
$keyItems = NULL;
foreach ($uniqueSetColumns as $column) {
$keyItems[] = $lookupProperties[$column->name];
}
$lookupKey = self::prepareLookupKey($keyItems);
if (!isset($lookupValues[$lookupKey])) {
if (count($lookupValues) == 1) {
// 04/23/2014 if only one record requested and one record received, but the received key does not match the request
// it means that character encoding functionality is more sophisticated on server and we actually have a match
// storing the value into cache for further usage
$this->cachedIdentifiers[$lookupCacheKey][$lookupKey] = $identifier;
reset($lookupValues);
$alternativeLookupKey = key($lookupValues);
$lookupKey = $alternativeLookupKey;
}
else {
$foundUnmatchedIdentifiers = TRUE;
continue;
}
}
if (isset($lookupValues[$lookupKey]->identifier)) {
$searchCriteria = array();
foreach ($uniqueSetColumns as $column) {
$searchCriteria[$column->name] = $lookupProperties[$column->name];
}
LogHelper::log_error(t(
'Key: @searchCriteria. Loaded identifiers: @identifiers',
array(
'@searchCriteria' => ArrayHelper::serialize($searchCriteria, ', ', TRUE, FALSE),
'@identifiers' => ArrayHelper::serialize(array($lookupValues[$lookupKey]->identifier, $identifier), ', ', TRUE, FALSE))));
throw new IllegalArgumentException(t(
'Several records in %datasetName dataset match search criteria',
array('%datasetName' => $lookupDataset->publicName)));
}
$lookupValues[$lookupKey]->identifier = $identifier;
// storing the value into cache for further usage
$this->cachedIdentifiers[$lookupCacheKey][$lookupKey] = $identifier;
}
// found unmatched values. Processing unprocessed lookups one by one
if ($foundUnmatchedIdentifiers) {
foreach ($lookupValues as $lookupKey => $lookupValue) {
if (!isset($lookupValue->identifier)) {
$singleLookupValue = array($lookupKey => $lookupValue);
$this->loadIdentifiers($lookupDatasetName, $uniqueSetColumns, $singleLookupValue);
}
}
}
$this->freeSpaceInIdentifierCache($lookupDataset->name);
}
}
protected function prepareDatasetSequence(ReferenceCallContext $callcontext, MetaModel $metamodel, ReferenceLink $link, $referencePath) {
$referenceParts = ReferencePathHelper::splitReference($referencePath);
$parentColumnName = array_pop($referenceParts);
$parentDatasetName = array_pop($referenceParts);
$referencedColumnName = $referencedDatasetName = NULL;
$leftReferencePartCount = count($referenceParts);
$nestedReferencePath = $referencePath;
if ($link->dataset->name == $parentDatasetName) {
// dataset is there and it is link's dataset
if ($leftReferencePartCount == 0) {
return;
}
// assembling new reference path
$nestedReferencePath = ReferencePathHelper::assembleReferenceParts($referenceParts);
$referencedColumnName = array_pop($referenceParts);
$referencedDatasetName = array_pop($referenceParts);
if (!isset($referencedDatasetName)) {
throw new UnsupportedOperationException(t(
'Dataset name is not set in the reference path: %referencePath',
array('%referencePath' => $referencePath)));
}
}
elseif (isset($parentDatasetName)) {
$referencedColumnName = $parentColumnName;
$referencedDatasetName = $parentDatasetName;
$parentColumnName = $parentDatasetName = NULL;
}
else {
if ($leftReferencePartCount > 0) {
throw new UnsupportedOperationException(t(
'Dataset name is not set in the reference path: %referencePath',
array('%referencePath' => $referencePath)));
}
// it means that we just point to column in link's dataset
return;
}
// checking if there any references which could be used to find required dataset
$references = $metamodel->findReferencesByDatasetName($link->dataset->name);
if (!isset($references)) {
return;
}
// maximum number of columns in direct references
$directReferencePointColumnCount = NULL;
// checking if there is any reference which directly link to referenced dataset
$parentReferencePointIndex4References = $directReferencePointIndex4References = $transitionalReferencePointIndexes4References = NULL;
foreach ($references as $reference) {
$referencePointColumnCount = $reference->getPointColumnCount();
// checking if the reference can be used to link with other datasets
$parentReferencePointIndex4Reference = $directReferencePointIndex4Reference = $transitionalReferencePointIndexes4Reference = NULL;
for ($referencePointColumnIndex = 0; $referencePointColumnIndex < $referencePointColumnCount; $referencePointColumnIndex++) {
$parentReferencePointIndex = $directReferencePointIndex = $transitionalReferencePointIndexes = NULL;
foreach ($reference->points as $referencePointIndex => $referencePoint) {
$datasetName = $referencePoint->columns[$referencePointColumnIndex]->datasetName;
if (($link->dataset->name == $datasetName)
&& (!isset($parentColumnName) || ($parentColumnName == $referencePoint->columns[$referencePointColumnIndex]->columnName))) {
if (isset($parentReferencePointIndex)) {
// Found several possible ways to start a join from the referring dataset.
// That happens because we do not have parent column name and possible join is ambiguous
// We cannot use this way to proceed
continue 3;
}
else {
$parentReferencePointIndex = $referencePointIndex;
}
}
elseif ($datasetName == $referencedDatasetName) {
if (isset($directReferencePointIndex)) {
// found several possible ways to join with the referenced dataset
continue 3;
}
else {
$directReferencePointIndex[$referencePointIndex] = TRUE;
}
}
else {
$transitionalReferencePointIndexes[$referencePointIndex] = FALSE;
}
}
// this reference cannot be used because none of the reference points linked with parent dataset
if (!isset($parentReferencePointIndex)) {
continue 2;
}
if (isset($directReferencePointIndex)) {
// if we have direct reference we do not care about indirect ones
$transitionalReferencePointIndexes = NULL;
}
else {
// there is no direct or indirect ways. This reference is useless :)
if (!isset($transitionalReferencePointIndexes)) {
continue 2;
}
}
$parentReferencePointIndex4Reference[$referencePointColumnIndex] = $parentReferencePointIndex;
if (isset($directReferencePointIndex)) {
$directReferencePointIndex4Reference[$referencePointColumnIndex] = $directReferencePointIndex;
}
if (isset($transitionalReferencePointIndexes)) {
$transitionalReferencePointIndexes4Reference[$referencePointColumnIndex] = $transitionalReferencePointIndexes;
}
}
// we support only direct references between datasets
// in this case we have direct reference based on some columns only. Rest columns are connected indirectly
if (isset($directReferencePointIndex4Reference) && isset($transitionalReferencePointIndexes4Reference)) {
continue;
}
$parentReferencePointIndex4References[$reference->name] = $parentReferencePointIndex4Reference;
if (isset($directReferencePointIndex4Reference)) {
$directReferencePointIndex4References[$reference->name] = $directReferencePointIndex4Reference;
$directReferencePointColumnCount = MathHelper::max($directReferencePointColumnCount, $referencePointColumnCount);
}
if (isset($transitionalReferencePointIndexes4Reference)) {
$transitionalReferencePointIndexes4References[$reference->name] = $transitionalReferencePointIndexes4Reference;
}
}
// we could use none of the selected references
if (!isset($parentReferencePointIndex4References)) {
return;
}
// removing all useless direct and indirect references if there is a direct way
if (isset($directReferencePointColumnCount)) {
foreach ($parentReferencePointIndex4References as $referenceName => $parentReferencePointIndex4Reference) {
$referencePointColumnCount = count($parentReferencePointIndex4Reference);
if (isset($directReferencePointIndex4References[$referenceName])) {
// we preserve only direct ways with maximum number of columns
if ($referencePointColumnCount == $directReferencePointColumnCount) {
continue;
}
}
else {
// we preserve only indirect ways with more columns than in direct way
if ($referencePointColumnCount > $directReferencePointColumnCount) {
continue;
}
}
unset($parentReferencePointIndex4References[$referenceName]);
unset($directReferencePointIndex4References[$referenceName]);
unset($transitionalReferencePointIndexes4References[$referenceName]);
}
}
foreach ($parentReferencePointIndex4References as $referenceName => $parentReferencePointIndex4Reference) {
$reference = $references[ArrayHelper::search($references, 'name', $referenceName)];
$referencePointColumnCount = $reference->getPointColumnCount();
$referencePointIndexes4Reference = isset($directReferencePointIndex4References[$referenceName])
? $directReferencePointIndex4References[$referenceName]
: NULL;
$isDirectReference = isset($referencePointIndexes4Reference);
if (!$isDirectReference) {
// 01/09/2014 reference can be reused only for direct references
if (isset($callcontext->referenceNameStack[$referenceName])) {
continue;
}
$referencePointIndexes4Reference = isset($transitionalReferencePointIndexes4References[$referenceName])
? $transitionalReferencePointIndexes4References[$referenceName]
: NULL;
}
// registering the reference in a stack to avoid excessive calls
// registration was moved here because we could have date[->month->quarter->year] and year columns in one dataset
// if references related to date and year are registered before we start to process individual references
// we will end up with nested links for date->month->quarter->year which do not contain a reference to year
// which leads to GOVDB-1313 issue
$callcontext->referenceNameStack[$reference->name] = TRUE;
// preparing dataset names for each reference point
$referencePointDatasetNames = NULL;
for ($referencePointColumnIndex = 0; $referencePointColumnIndex < $referencePointColumnCount; $referencePointColumnIndex++) {
foreach ($referencePointIndexes4Reference[$referencePointColumnIndex] as $referencePointIndex => $directReferencePointFlag) {
$referencePointColumn = $reference->points[$referencePointIndex]->columns[$referencePointColumnIndex];
$datasetName = $referencePointColumn->datasetName;
// it is expected that dataset name is the same for all columns in a reference point
if (isset($referencePointDatasetNames[$referencePointIndex])) {
if ($referencePointDatasetNames[$referencePointIndex] != $datasetName) {
// Dataset name is not the same for all columns for the reference point
$referencePointDatasetNames[$referencePointIndex] = FALSE;
}
}
else {
$referencePointDatasetNames[$referencePointIndex] = $datasetName;
}
}
}
// removing all reference points which we cannot support now
foreach ($referencePointDatasetNames as $referencePointIndex => $datasetName) {
if ($datasetName === FALSE) {
unset($referencePointDatasetNames[$referencePointIndex]);
}
}
// if nothing left there is not need to proceed
if (count($referencePointDatasetNames) == 0) {
continue;
}
// preparing list of parent column names
$parentColumnNames = NULL;
for ($referencePointColumnIndex = 0; $referencePointColumnIndex < $referencePointColumnCount; $referencePointColumnIndex++) {
$parentReferencePointIndex = $parentReferencePointIndex4Reference[$referencePointColumnIndex];
$parentReferencePointColumnName = $reference->points[$parentReferencePointIndex]->columns[$referencePointColumnIndex]->columnName;
$parentColumnNames[$referencePointColumnIndex] = $parentReferencePointColumnName;
}
$referenceCallContext = $isDirectReference ? $callcontext : (clone $callcontext);
// adding all indirect datasets in stack to prevent recursive calls
if (!$isDirectReference) {
foreach ($referencePointDatasetNames as $referencePointIndex => $datasetName) {
if (isset($referenceCallContext->datasetNameStack[$datasetName])) {
unset($referencePointDatasetNames[$referencePointIndex]);
}
else {
$referenceCallContext->datasetNameStack[$datasetName] = TRUE;
}
}
}
foreach ($referencePointDatasetNames as $referencePointIndex => $datasetName) {
// looking for existing link
$referencedLink = $link->findNestedLinkByDatasetNameAndParentColumnNames($datasetName, $parentColumnNames);
if (!isset($referencedLink)) {
$dataset = $metamodel->getDataset($datasetName);
$referencedLink = new ReferenceLink($dataset);
foreach ($parentColumnNames as $referencePointColumnIndex => $parentReferencePointColumnName) {
$referencePointColumn = $reference->points[$referencePointIndex]->columns[$referencePointColumnIndex];
$referencedLink->linkColumnWithParent($referencePointColumnIndex, $parentReferencePointColumnName, $referencePointColumn->columnName);
}
$link->registerNestedLink($referencedLink);
}
ArrayHelper::addUniqueValue($referencedLink->referenceNames, $referenceName);
// marking the link as required for the branch so it will not be deleted by the optimizer later
if ($isDirectReference) {
$referencedLink->required = TRUE;
}
// because this reference path is not processed completely we need to continue scanning this branch
if (isset($nestedReferencePath)) {
LogHelper::log_debug(t(
'Checking sub-path[linkId: @linkId; stackSize: @stackSize]: @referencePath',
array('@linkId' => $referencedLink->linkId, '@stackSize' => count($callcontext->referenceNameStack), '@referencePath' => $referencePath)));
$referencePointCallContext = clone $referenceCallContext;
$this->prepareDatasetSequence($referencePointCallContext, $metamodel, $referencedLink, $nestedReferencePath);
}
}
}
}
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);
}
public function collectExpressionInTotalFunctionCall(ParserCallback $callback, &$expressions) {
ArrayHelper::addUniqueValue($expressions, $callback->marker);
}
public function assemble(AbstractSQLDataSourceQueryHandler $datasourceHandler, AbstractQueryRequest $request, 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->factsDatasetName);
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->getLogicalApplicationType());
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($request, $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,
DataSourceColumnNameHelper::generateFromParameterElements($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;
}
public function addColumn($column)
{
ReferencePathHelper::checkReference($column);
ArrayHelper::addUniqueValue($this->columns, $column);
}
public function collectColumnNames(ParserCallback $callback, &$columnNames) {
ArrayHelper::addUniqueValue($columnNames, $callback->marker);
}
public function autoDetectPrimaryDataType(array $values = NULL, $handlerType = DATA_TYPE__PRIMITIVE)
{
$primaryDataType = NULL;
if (isset($values)) {
$compatibleDataTypes = NULL;
foreach ($values as $value) {
$valueDateType = $this->autoDetectDataType($value, $handlerType);
ArrayHelper::addUniqueValue($compatibleDataTypes, $valueDateType);
}
$primaryDataType = $this->selectDataType($compatibleDataTypes);
}
return $primaryDataType;
}
public function getConnectedDatasetNames($includeRoot = TRUE, $includeTransitional = TRUE) {
$datasetNames = NULL;
if ($includeRoot) {
ArrayHelper::addUniqueValue($datasetNames, $this->dataset->name);
}
ArrayHelper::addUniqueValues($datasetNames, $this->getConnectedNestedDatasetNames($includeTransitional));
return $datasetNames;
}
protected static function detectConnectedDatasetsBySelectedColumns(array $parsedUIMetaDataNames) {
$datasetNames = NULL;
foreach ($parsedUIMetaDataNames as $parsedUIMetaDataName) {
ArrayHelper::addUniqueValue($datasetNames, $parsedUIMetaDataName->datasetName);
}
return $datasetNames;
}
