public function prepareDatasetColumnLookupIds($datasetName, ColumnMetaData $column, array &$lookupValues) { $metamodel = data_controller_get_metamodel(); $logicalDataset = $metamodel->getDataset($this->datasetName); $logicalColumn = $logicalDataset->getColumn($this->columnName); $cubeName = $logicalDataset->name; $cube = $metamodel->getCube($cubeName); $factsDataset = $metamodel->getDataset($cube->factsDatasetName); $attributeColumn = $factsDataset->getColumn($this->columnName); $lookupHandler = DimensionLookupFactory::getInstance()->getHandler($logicalColumn->type->getLogicalApplicationType()); list($adjustedDatasetName, $adjustedColumnName) = $lookupHandler->adjustReferencePointColumn($metamodel, $factsDataset->name, $attributeColumn->name); $adjustedDataset = $metamodel->getDataset($adjustedDatasetName); $adjustedColumn = $adjustedDataset->getColumn($adjustedColumnName); // this request is part of chain of references $adjustedLookupValues = NULL; if (($factsDataset->name != $adjustedDataset->name) || ($attributeColumn->name != $adjustedColumnName)) { $nestedLookupHandler = DimensionLookupFactory::getInstance()->getHandler($adjustedColumn->type->getLogicalApplicationType()); // preparing list of lookup object for nested reference $nestedLookupValues = NULL; foreach ($lookupValues as $lookupKey => $lookupValue) { $value = $lookupValue->getPropertyValue($attributeColumn->name); $nestedLookupValues[$lookupKey] = $nestedLookupHandler->prepareLookupValue($value); } // loading identifiers from nested reference if ($logicalColumn->type->getReferencedDatasetName() == NULL) { $nestedLookupHandler->loadIdentifiers($adjustedDataset->name, array($adjustedColumn), $nestedLookupValues); } else { $nestedLookupHandler->prepareDatasetColumnLookupIds($adjustedDataset->name, $adjustedColumn, $nestedLookupValues); } // using loaded identifiers foreach ($lookupValues as $lookupKey => $lookupValue) { $nestedLookupValue = $nestedLookupValues[$lookupKey]; if (!isset($nestedLookupValue->identifier)) { continue; } $adjustedLookupKey = self::prepareLookupKey($nestedLookupValue->identifier); $lookupValue->setPropertyValue($attributeColumn->name, $nestedLookupValue->identifier); $adjustedLookupValues[$adjustedLookupKey] = $lookupValue; } } else { $adjustedLookupValues = $lookupValues; } // do not use prepareIdentifiers() because new records will be inserted if (isset($adjustedLookupValues)) { $this->loadIdentifiers($factsDataset->name, array($attributeColumn), $adjustedLookupValues); } }
protected function assembleForeignKeyConstraints(DataSourceHandler $handler, DatasetMetaData $dataset, $indent, &$sql) { $metamodel = data_controller_get_metamodel(); foreach ($dataset->getColumns() as $column) { $columnName = $column->name; if (!isset($column->type->sourceApplicationType)) { continue; } // the column has to contain a reference to another dataset $dimensionLookupHandler = DimensionLookupFactory::getInstance()->getHandler($column->type->sourceApplicationType); list($referencedDatasetName) = $dimensionLookupHandler->adjustReferencePointColumn($metamodel, $dataset->name, $column->name); if ($dataset->name == $referencedDatasetName) { continue; } $referencedDataset = $metamodel->getDataset($referencedDatasetName); // we can create a foreign key constraint referenced to a table only $referencedDatasetSourceType = DatasetTypeHelper::detectDatasetSourceType($referencedDataset); if ($referencedDatasetSourceType != DatasetTypeHelper::DATASET_SOURCE_TYPE__TABLE) { continue; } $referencedOwner = NULL; if ($dataset->datasourceName != $referencedDataset->datasourceName) { // if we cannot join datasets we cannot create a foreign key constraint $datasourceQueryHandler = DataSourceQueryFactory::getInstance()->getHandler($handler->getDataSourceType()); if (!$datasourceQueryHandler->isJoinSupported($dataset->datasourceName, $referencedDataset->datasourceName)) { continue; } $referencedOwner = $handler->getDataSourceOwner($referencedDataset->datasourceName); } $referencedTableName = $referencedDataset->source; $referencedColumnName = $referencedDataset->getKeyColumn()->name; $sql .= ",\n{$indent}CONSTRAINT fk_{$dataset->source}_{$columnName} FOREIGN KEY ({$columnName}) REFERENCES " . (isset($referencedOwner) ? $referencedOwner . '.' : '') . "{$referencedTableName} ({$referencedColumnName})"; } }
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 load($datasetName, RecordMetaData $recordMetaData, IndexedRecordsHolder $recordsHolder) { // preparing columns for which we can lookup values $lookupableColumns = $this->selectLookupableColumns($recordMetaData); if (!isset($lookupableColumns)) { return; } // preparing required values for each lookup $columnsLookupValues = $this->prepareLookupValues($recordsHolder, $lookupableColumns); if (!isset($columnsLookupValues)) { return; } // loading identifier for each values foreach ($columnsLookupValues as $columnIndex => &$columnLookupValues) { $column = $lookupableColumns[$columnIndex]; $dimensionLookupHandler = DimensionLookupFactory::getInstance()->getHandler($column->type->getLogicalApplicationType()); $dimensionLookupHandler->prepareDatasetColumnLookupIds($datasetName, $column, $columnLookupValues); // checking if we loaded all values $this->checkMissingIdentifiers($column, $columnLookupValues); } unset($columnLookupValues); // replacing column values with corresponding ids foreach ($recordsHolder->records as $record) { foreach ($lookupableColumns as $columnIndex => $column) { $columnValue = $record->columnValues[$columnIndex]; if (!isset($columnValue)) { continue; } $lookupKey = AbstractDimensionLookupHandler::prepareLookupKey($columnValue); $record->columnValues[$columnIndex] = $columnsLookupValues[$columnIndex][$lookupKey]->identifier; } } }
public static function adjustReferencePointColumn(AbstractMetaModel $metamodel, DatasetReferencePointColumn $referencePointColumn) { $datasetName = $referencePointColumn->datasetName; $dataset = $metamodel->getDataset($datasetName); $column = $dataset->getColumn($referencePointColumn->columnName); $handler = DimensionLookupFactory::getInstance()->getHandler($column->type->getLogicalApplicationType()); list($adjustedDatasetName, $adjustedColumnName, $shared) = $handler->adjustReferencePointColumn($metamodel, $dataset->name, $referencePointColumn->columnName); if ($adjustedDatasetName === $referencePointColumn->datasetName) { if ($adjustedColumnName === $referencePointColumn->columnName) { // we do not need to change anything } else { throw new UnsupportedOperationException(); } } else { $referencePointColumn->datasetName = $adjustedDatasetName; $referencePointColumn->columnName = $adjustedColumnName; } $referencePointColumn->shared = $shared; }