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 __construct() {
parent::__construct();
$this->handlerConfigurations = module_invoke_all('dp_star_schema_lookup');
}
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;
}
