<?php
/**
* XDMoD implements descriptive attributes using GroupBy classes.
* @see iGroupBy
*/
namespace Realm;
use Log as Logger;
use CCR\DB;
use ETL\DbModel\Query as DbQuery;
use \DataWarehouse\Query\Model\OrderBy;
use \DataWarehouse\Query\Model\Parameter;
use \DataWarehouse\Query\Model\Schema;
use \DataWarehouse\Query\Model\Field;
use \DataWarehouse\Query\Model\Table;
use \DataWarehouse\Query\Model\TableField;
use \DataWarehouse\Query\Model\WhereCondition;
class GroupBy extends \CCR\Loggable implements iGroupBy
{
/**
* @var Delimiter used to separate individual column values for attributes with multi-column
* keys.
*/
const FILTER_DELIMITER = '^';
/**
* @var Realm The realm that this GroupBy belongs to.
*/
protected $realm = null;
/**
* @var string The name of the module that defined this group by
*/
protected $moduleName = null;
/**
* @var string The short identifier.
*/
protected $id = null;
/**
* @var string The display name.
*/
protected $name = null;
/**
* @var string Human-readable description supporting basic HTML formatting.
*/
protected $description = null;
/**
* @var string Schema for the attribute table.
*/
protected $attributeTableSchema = null;
/**
* @var string Name of the table where attributes are stored.
*/
protected $attributeTableName = null;
/**
* @var \DataWarehouse\Query\Model\Table Attribute table object.
*/
protected $attributeTableObj = null;
/**
* @var array Two dimensional array describing the mapping between attribute table keys and the
* corresponding aggregate table keys. Keys are the attribute table columns and values are the
* aggregate table columns used for constructing join clauses. Multi-key columns are supported
* by using multiple ordered key-value pairs.
*/
protected $attributeToAggregateKeyMap = null;
/**
* @var array A list of alternate group-by columns to be used in the generated SQL query. The
* list must contain one element for each key provided in the attribute to aggregate key map. In
* some cases, such as group by username, we will want to group by a value that is different
* than the attribute or aggregate keys.
*/
protected $alternateGroupByColumns = array();
/**
* @var array An array of objects describing qdditional constraints to be added to the JOIN
* clause between the attribute and aggregate data tables. The specification contains the
* attribute column (left), operation, and aggregate column (right). This is used in cases where
* we want to add additional where clauses to the JOIN condition between the attribute and
* aggregate data tables to ensure we are restricting data properly.
*/
protected $additionalJoinConstraints = null;
/**
* @var DbQuery The Attribute Values Query provides a mechanism for the GroupBy class to
* enumerate a list of the current values for the dimensional attribute that it describes. This
* is constructed from the same JSON configuration object used by the ETL.
*/
protected $attributeValuesQuery = null;
/**
* @var stdClass The Attribute Values Query represented as a stdClass.
*/
protected $attributeValuesQueryAsStdClass = null;
/**
* @var array An associative array describing how to apply attribute filters to the aggregate
* data using a subquery in cases where there is not a direct mapping. The keys are aggregate
* table colum keys and must be present as values in the attribute to aggregate key map while
* the values are subqueries to be used to filter results.
*/
protected $attributeFilterMapSqlList = null;
/**
* @var int A numerical ordering hint as to how this realm should be displayed visually relative
* to other realms. Lower numbers are displayed first. If no order is specified, 0 is assumed.
*/
protected $order = 0;
/**
* @var string Optional query to retrieve human-readable names for any attribute value filters
* that have been specified in a request. This should only be used when the query to retrieve
* the human-readable names of the specified filter values cannot be derived from the possible
* values query. The _filtervalues_ marker will be replaced by the values of the filters.
*/
protected $attributeDescriptionSql = null;
/**
* @var string Category used to group attributes, used in the Job Viewer filters.
*/
protected $category = 'uncategorized';
/**
* @var boolean Indicates that this dimension should be shown in the metric catalog and is
* available for drill-down in the user interface.
*/
protected $showInCatalog = true;
/**
* @var boolean Indicates that this dimension is an aggregation unit such as day, month,
* quarter, year. This may trigger special handling when constructing queries.
*/
protected $isAggregationUnit = false;
/**
* @var array An associative array specifying chart defaults where the key is the chart property
* and the value is a property value which may be specific to HighCharts. Note that "y" and
* "n" are used rather than true and false to ensure that existing code does not break.
*/
protected $chartOptions = array(
'combine_method' => 'stack',
'dataset_display_type' => array(
'aggregate' => 'h_bar',
'timeseries' => 'line'
),
'dataset_type' => 'aggregate',
'enable_errors' => 'y',
'enable_trend_line' => 'y',
'limit' => array(
'multichart_page' => 3,
'default' => 10
),
'log_scale' => 'n',
'offset' => 0,
'show_aggregate_labels' => 'n',
'show_error_bars' => 'n',
'show_error_labels' => 'n',
'show_guide_lines' => 'y',
'show_legend' => 'y',
'show_trend_line' => 'n'
);
/**
* @var array An associative array specifying the chart type to use for a given dataset type
* when using this group by. The keys are the dataset type (e.g., aggregate or timeseries) and
* the values are chart types. Note that the chart types are currently specific to HighCharts.
*/
protected $datasetTypeToChartDisplayTypeMap = array(
'aggregate' => 'h_bar',
'timeseries' => 'line'
);
/**
* @var int PHP order specificaiton to determine how the query should sort results containing
* this GroupBy.
* @see http://php.net/manual/en/function.array-multisort.php
*/
protected $sortOrder = SORT_DESC;
/**
* @var boolean Set to true if this group by should not be utilized.
*/
protected $disabled = false;
/**
* @see iGroupBy::factory()
*/
public static function factory($shortName, \stdClass $config, Realm $realm, Logger $logger = null)
{
return new static($shortName, $config, $realm, $logger);
}
/**
* This constructor is meant to be called by factory() but it cannot be made private because we
* extend Loggable, which has a public constructor.
*
* @param string $shortName The short name for this statistic
* @param stdClass $config An object contaning the configuration specificaiton.
* @param Realm $realm Realm object that this GroupBy will belong to.
* @param Log|null $logger A Log instance that will be utilized during processing.
*/
public function __construct($shortName, \stdClass $config, Realm $realm, Logger $logger = null)
{
parent::__construct($logger);
// The __toString() method needs these to be set and logAndThrowException() calls
// __toString() so assign these at the top.
$this->id = $shortName;
$this->attributeTableSchema = $realm->getAggregateTableSchema(); // Default to Realm schema
$this->moduleName = $realm->getModuleName();
$this->realm = $realm;
if ( empty($shortName) ) {
$this->logger->logAndThrowException('GroupBy short name not provided');
} elseif ( ! is_string($shortName) ) {
$this->logger->logAndThrowException(
sprintf('GroupBy short name must be a string, %s provided: %s', $shortName, gettype($shortName))
);
} elseif ( null === $config ) {
$this->logger->logAndThrowException('No GroupBy configuration provided');
}
// Verify the types of the various configuration options
$messages = array();
$configTypes = array(
'attribute_to_aggregate_table_key_map' => 'array',
'attribute_values_query' => 'object',
'description_html' => 'string',
'name' => 'string'
);
if ( ! \xd_utilities\verify_object_property_types($config, $configTypes, $messages) ) {
$this->logAndThrowException(
sprintf('Error verifying GroupBy configuration: %s', implode(', ', $messages))
);
}
$optionalConfigTypes = array(
'additional_join_constraints' => 'array',
'alternate_group_by_columns' => 'array',
'attribute_description_query' => 'string',
'attribute_filter_map_query' => 'object',
'attribute_table_schema' => 'string',
'category' => 'string',
'chart_options' => 'object',
'data_sort_order' => 'string',
'disabled' => 'bool',
'is_aggregation_unit' => 'bool',
'module' => 'string',
'order' => 'int',
'show_in_catalog' => 'bool'
);
if ( ! \xd_utilities\verify_object_property_types($config, $optionalConfigTypes, $messages, true) ) {
$this->logAndThrowException(
sprintf('Error verifying GroupBy configuration: %s', implode(', ', $messages))
);
}
foreach ( $config as $key => $value ) {
switch ($key) {
case 'alternate_group_by_columns':
$this->alternateGroupByColumns = $value;
break;
case 'additional_join_constraints':
foreach ( $value as $constraint ) {
if ( ! is_object($constraint) ) {
$this->logAndThrowException(
sprintf("In additional_join_constraints list, expected object, got %s", gettype($constraint))
);
}
$this->additionalJoinConstraints[] = $constraint;
}
break;
case 'attribute_values_query':
$this->attributeValuesQuery = new DbQuery($value, '`', $logger);
break;
case 'attribute_filter_map_query':
$this->attributeFilterMapSqlList = (array) $value;
break;
case 'attribute_table_schema':
$this->attributeTableSchema = trim($value);
break;
case 'attribute_to_aggregate_table_key_map':
// Convert an ordered array of objects into an ordered associative array.
$this->attributeToAggregateKeyMap = array();
foreach ( $value as $obj ) {
foreach ( $obj as $k => $v ) {
$this->attributeToAggregateKeyMap[$k] = $v;
}
}
break;
case 'attribute_description_query':
$this->attributeDescriptionSql = trim($value);
break;
case 'show_in_catalog':
$this->showInCatalog = filter_var($value, FILTER_VALIDATE_BOOLEAN);
break;
case 'category':
$this->category = trim($value);
break;
case 'chart_options':
foreach ( $value as $optionKey => $optionValue ) {
if ( ! array_key_exists($optionKey, $this->chartOptions) ) {
$this->logger->warning(
sprintf("Unsupported option in '%s' for group by '%s': '%s'", $key, $this->id, $optionKey)
);
continue;
}
// Enable special handling of various options
switch ($optionKey) {
case 'enable_errors':
case 'enable_trend_line':
case 'log_scale':
case 'show_aggregate_labels':
case 'show_error_bars':
case 'show_error_labels':
case 'show_guide_lines':
case 'show_legend':
case 'show_trend_line':
// For historical reasons, use 'y' and 'n' as boolean values. This
// may be able to be changed to true/false in the future.
$this->chartOptions[$optionKey] = (
filter_var($optionValue, FILTER_VALIDATE_BOOLEAN)
? 'y'
: 'n'
);
break;
case 'offset':
$this->chartOptions[$optionKey] = filter_var($optionValue, FILTER_VALIDATE_INT);
break;
case 'dataset_display_type':
if ( ! is_object($optionValue) ) {
continue;
}
foreach ($optionValue as $datasetType => $chartDisplayValue ) {
if ( ! array_key_exists($datasetType, $this->chartOptions[$optionKey]) ) {
$this->logger->warning(
sprintf("Unsupported dataset type in '%s' for group by '%s': '%s'", $optionKey, $this->id, $datasetType)
);
continue;
}
$this->chartOptions[$optionKey][$datasetType] = $chartDisplayValue;
}
break;
case 'limit':
if ( ! is_object($optionValue) ) {
continue;
}
foreach ($optionValue as $pageType => $limitValue ) {
if ( ! array_key_exists($pageType, $this->chartOptions[$optionKey]) ) {
$this->logger->warning(
sprintf("Unsupported page type in '%s' for group by '%s': '%s'", $optionKey, $this->id, $pageType)
);
continue;
}
$this->chartOptions[$optionKey][$pageType] = $limitValue;
}
break;
default:
if ( ! array_key_exists($optionKey, $this->chartOptions) ) {
$this->logger->notice(
sprintf(
"Unknown key in chart_defaults for realm '%s' group by '%s': '%s'",
$this->realm->getName(),
$this->id,
$optionKey
)
);
} else {
$this->chartOptions[$optionKey] = $optionValue;
}
break;
}
}
break;
case 'chart_display_type_map':
break;
case 'data_sort_order':
// The sort order is specified in the JSON config file as the string
// representation of a PHP constant so convert it to an integer in order to
// properly use it. See https://php.net/manual/en/function.array-multisort.php
if (!empty($value)){
$this->setSortOrder(constant($value));
} else {
$this->setSortOrder(null);
}
break;
case 'description_html':
$this->description = trim($value);
break;
case 'disabled':
$this->disabled = filter_var($value, FILTER_VALIDATE_BOOLEAN);
break;
case 'is_aggregation_unit':
$this->isAggregationUnit = filter_var($value, FILTER_VALIDATE_BOOLEAN);
break;
case 'module':
$this->moduleName = trim($value);
break;
case 'name':
$this->name = trim($value);
break;
case 'order':
$this->order = filter_var($value, FILTER_VALIDATE_INT);
break;
default:
$this->logger->notice(
sprintf("Unknown key in definition for realm '%s' group by '%s': '%s'", $this->realm->getName(), $this->id, $key)
);
break;
}
}
// Ensure the attribute values query has the required columns as we will rely on these later
// on.
if (
false === $this->attributeValuesQuery->getRecord('id') ||
false === $this->attributeValuesQuery->getRecord('short_name') ||
false === $this->attributeValuesQuery->getRecord('name') // For historical reasons, the long name is simply "name"
) {
$this->logAndThrowException('The attribute_values_query key must specify id, short_name, and name columns');
}
// Ensure that the schema of the joins in the attributes_values_query defaults to the attributes_value_schema
$joins = $this->attributeValuesQuery->joins;
foreach ($joins as $join) {
if (!isset($join->schema)) {
$join->schema = $this->attributeTableSchema;
}
}
$this->attributeValuesQuery->joins = $joins;
$this->attributeValuesQueryAsStdClass = $this->attributeValuesQuery->toStdClass();
$this->attributeTableName = trim($this->attributeValuesQuery->joins[0]->name);
if ( $this->attributeDescriptionSql && count($this->attributeToAggregateKeyMap) > 1 ){
$this->logAndThrowException('The attribute_description_query does not work with more than one ');
}
// Note that we are using the table name itself as an alias. If needed, we can add an
// alias to the group by configuration specification.
$this->attributeTableObj = new Table(
new Schema($this->attributeTableSchema),
$this->attributeTableName,
$this->attributeTableName
);
// If alternate groupby columns have been proivided, ensure that there are the same number
// as the attribute to aggregate column map.
if ( 0 != count($this->alternateGroupByColumns) ) {
if ( count($this->alternateGroupByColumns) != count($this->attributeToAggregateKeyMap) ) {
$this->logAndThrowException(
'Number of alternate group by columns does not match number of columns in attribute to aggregate key map'
);
} else {
// Ensure that the values are not empty (null or empty string)
$valid = array_reduce(
$this->alternateGroupByColumns,
function ($item, $carry) {
return $carry && ! empty($item);
},
true
);
if ( ! $valid ) {
$this->logAndThrowException(
'All items in the alternate group by column list must non-null and non-empty'
);
}
}
}
$this->logger->debug(sprintf('Created %s', $this));
}
/**
* @see iGroupBy::getRealm()
*/
public function getRealm()
{
return $this->realm;
}
/**
* Note: Was getName()
*
* @see iGroupBy::getId()
*/
public function getId()
{
return $this->id;
}
/**
* Note: Was getLabel()
*
* @see iGroupBy::getName()
*/
public function getName()
{
return $this->realm->getVariableStore()->substitute($this->name);
}
/**
* Note: Was getInfo()
*
* @see iGroupBy::getHtmlDescription()
*/
public function getHtmlDescription()
{
return $this->realm->getVariableStore()->substitute($this->description);
}
/**
* Note: Was getDescription()
*
* @see iGroupBy::getHtmlNameAndDescription()
*/
public function getHtmlNameAndDescription()
{
return sprintf("<b>%s</b>: %s", $this->getName(), $this->getHtmlDescription());
}
/**
* @see iGroupBy::getAttributeTable()
*/
public function getAttributeTable($includeSchema = true)
{
return ( $includeSchema ? sprintf("%s.", $this->attributeTableSchema) : "" ) . $this->attributeTableName;
}
/**
* @see iGroupBy::getAttributeKeys()
*/
public function getAttributeKeys()
{
return array_keys($this->attributeToAggregateKeyMap);
}
/**
* @see iGroupBy::getAggregateKeys()
*/
public function getAggregateKeys()
{
return array_values($this->attributeToAggregateKeyMap);
}
/**
* @see iGroupBy::getModuleName()
*/
public function getModuleName()
{
return $this->moduleName;
}
/**
* @see iGroupBy::getOrder()
*/
public function getOrder()
{
return $this->order;
}
/**
* @see iGroupBy::setSortOrder()
*/
public function setSortOrder($sortOrder = SORT_DESC)
{
$validSortOrders = array(
SORT_ASC,
SORT_DESC,
SORT_REGULAR,
SORT_NUMERIC,
SORT_STRING,
SORT_LOCALE_STRING,
SORT_NATURAL
);
if ( null !== $sortOrder && ! in_array($sortOrder, $validSortOrders) ) {
$this->logAndThrowException(sprintf("Invalid sort option: %d", $sortOrder));
}
$this->sortOrder = $sortOrder;
}
/**
* @see iGroupBy::getSortOrder()
*/
public function getSortOrder()
{
return $this->sortOrder;
}
/**
* @see iGroupBy::showInMetricCatalog()
*/
public function showInMetricCatalog()
{
return $this->showInCatalog;
}
/**
* @see iGroupBy::isAggregationUnit()
*/
public function isAggregationUnit()
{
return $this->isAggregationUnit;
}
/**
* Qualify the given column name by prefixing it with the id of this group by so it is unique
* when used in a Query.
*
* @param string $name The name of the column to prefix
*
* @return string The qualified column name
*/
protected function qualifyColumnName($name, $multi = false)
{
return ( true === $multi ? sprintf('%s_%s', $this->id, $name) : $name );
}
/**
* Filters in a request are identified in 2 ways:
* 1) By looking for a parameter with the short name of this group by and creating a filter with
* its (singular) value.
* 2) By looking for a parameter with the short name appended with "_filter" and creating a
* filter with the (possibly multiple) values.
*
* @param array $request The HTTP request
*
* @return array A list of zero or more filters for this group by identified in the request.
*/
protected function pullFilterValuesFromRequest(array $request)
{
$filterList = array();
// Request parameters with "_filter" appended to the short name of this group by may include
// one or more values to filter on.
$filterKey = sprintf('%s_filter', $this->id);
if ( isset($request[$filterKey]) && '' != $request[$filterKey] ) {
$filterList = explode(',', $request[$filterKey]);
}
// Request parameters with the same short name as this group by may contain single values to
// filter on.
if ( isset($request[$this->id]) && '' != $request[$this->id] ) {
$filterList[] = $request[$this->id];
}
return $filterList;
}
/**
* @see iGroupBy::generateQueryFiltersFromRequest()
* @see pullFilterValuesFromRequest()
*
* Was pullQueryParameters()
*/
public function generateQueryFiltersFromRequest(array $request)
{
$filterList = array();
$aggregateFilters = array();
$requestFilters = $this->pullFilterValuesFromRequest($request);
if ( ($this->isAggregationUnit && 'none' == $this->id) || 0 == count($requestFilters) ) {
return $filterList;
}
// In order to support multi-column keys, each filter is a string encoding one or more
// delimited values in the order specified in the attribute_to_aggregate_table_key_map
// specified in the GroupBy definition. We must extract the individual values and create
// query filters for each key column.
// For each request filter, split it on the delimiter to expose the filter components
// for each of the key columns and align the values with the approprite aggregate key based
// on the order specified in the attribute_to_aggregate_table_key_map.
$db = DB::factory('datawarehouse');
foreach ( $requestFilters as $filterValues ) {
$list = explode(self::FILTER_DELIMITER, $filterValues);
foreach ( $this->attributeToAggregateKeyMap as $aggregateKey ) {
$aggregateFilters[$aggregateKey][] = $db->quote(array_shift($list));
}
}
// For each aggregate column filter, check to see if there is a mapping query to translate
// between the filter value and aggregate value. If so, replace the __filter_values__
// placeholder in the attribute filter map sub-query with the list of filters from the
// request. If no attribute filter query was specified simply use the list of values.
foreach ( $aggregateFilters as $aggregateKeyColumn => $filterValues ) {
$substitution = implode(",", $filterValues);
if ( ! isset($this->attributeFilterMapSqlList[$aggregateKeyColumn]) ) {
$fieldIdQuery = $substitution;
} else {
$fieldIdQuery = str_replace('__filter_values__', $substitution, $this->attributeFilterMapSqlList[$aggregateKeyColumn]);
}
// Should the aggregate key column include the aggregate table name?
$filterParameter = new Parameter($aggregateKeyColumn, 'IN', "($fieldIdQuery)");
$this->logger->debug(
sprintf('%s Generate parameter from request: %s', $this, $filterParameter)
);
$filterList[] = $filterParameter;
}
return $filterList;
}
/**
* @see iGroupBy::generateQueryParameterLabelsFromRequest()
* Was pullQueryParameterDescriptions()
*/
public function generateQueryParameterLabelsFromRequest(array $request)
{
$labelList = array();
$attributeKeyFilters = array();
$query = null;
$requestFilters = $this->pullFilterValuesFromRequest($request);
$filterCount = count($requestFilters);
if ( ($this->isAggregationUnit && 'none' == $this->id) || 0 == $filterCount ) {
return $labelList;
}
// For each filter specification, split it on the delimiter to expose the filter components
// for each of the key columns and align the values with the approprite aggregate key based
// on the order specified in the attribute_to_aggregate_table_key_map.
$db = DB::factory('datawarehouse');
foreach ( $requestFilters as $filterValues ) {
$list = explode(self::FILTER_DELIMITER, $filterValues);
foreach ( $this->attributeToAggregateKeyMap as $attributeKey => $aggregateKey ) {
$attributeKeyFilters[$attributeKey][] = $db->quote(array_shift($list));
}
}
// Construct the where conditions for each key column. We will add these to the query or
// replace the placeholder in the attribute description query.
$whereConditions = array();
foreach ( $attributeKeyFilters as $attributeKey => $filterValues ) {
$whereConditions[] = sprintf("%s IN (%s)", $attributeKey, implode(",", $filterValues));
}
// If the attribute description query was not specified we will use the existing
// attribute values query.
if ( null === $this->attributeDescriptionSql ) {
// Clone the attribute values query so we don't modify the original object and remove
// all columns except for the long name and add WHERE conditions from the filters.
// Use the attribute values query as a template so we don't modify the original object
$queryConfig = $this->attributeValuesQuery->toStdClass();
foreach ( $queryConfig->records as $column => $formula ) {
if ( 'name' != $column ) {
unset($queryConfig->records->$column);
}
}
if ( ! isset($queryConfig->where) || ! is_array($queryConfig->where) ) {
$queryConfig->where = $whereConditions;
} else {
$queryConfig->where = array_merge($queryConfig->where, $whereConditions);
}
// Execute the attribute values query, selecting only the long_name column
$query = new DbQuery($queryConfig, '`', $this->logger);
$query = $query->getSql();
} else {
// Construct the where conditions for each key column and replace the placeholder in the
// attribute description query.
foreach ( $attributeKeyFilters as $attributeKey => $filterValues ) {
$filters = implode(",", $filterValues);
}
$query = str_replace(
'__filter_values__',
$filters,
$this->attributeDescriptionSql
);
}
// Return the results of the first column
$this->logger->debug(
sprintf("%s Fetch query parameter labels with query\n%s", $this, $query)
);
$db = DB::factory('datawarehouse');
$stmt = $db->handle()->prepare($query);
$stmt->execute();
$result = $stmt->fetchAll(\PDO::FETCH_COLUMN, 0);
$labels = array();
foreach ( $result as $fieldLabel ) {
$labels[] = $fieldLabel;
}
// Generate the label string using a coma to separate labels. Would it be better to use a
// semi-colon? Some labels that already contain comas such as "Last Name, First Name".
$labelString = sprintf(
( $filterCount > 1 ? '( %s )' : ' %s' ),
implode(', ', $labels)
);
$labelList[] = sprintf('%s = %s', $this->getName(), $labelString);
$this->logger->debug(
sprintf('%s Generated query parameter labels %s', $this, implode('; ', $labelList))
);
return $labelList;
}
/**
* Note: It is likely that we can remove the $multi_group parameter and always assume that
* multiple group bys are being included by always prefixing the group by's attributes with the
* group by id.
*
* @see iGroupBy::applyTo()
*/
public function applyTo(\DataWarehouse\Query\iQuery $query, $multi_group = false)
{
// Apply this GroupBy to the given Query. Note tha the query may have multiple GroupBys and
// other SELECT fields, but this essentially translates the following SQL:
//
// SELECT
// statistic_1
// FROM aggregate_table
//
// to
//
// SELECT
// statistic_1,
// attribute_1_id, attribute_1_short_name, attribute_1_name, attribute_1_order_id
// FROM aggregate_table
// JOIN attribute_table
// WHERE attribute_id = aggregate_id
// GROUP BY attribute_id
// ORDER BY attribute_order_id
// JOIN with the attribute table in the query
$this->logger->debug(sprintf('Apply GroupBy %s to %s', $this, $query));
// When applying an aggregation unit GroupBy the aggregation tables will already have been
// added by Query::setDuration()
if ( ! $this->isAggregationUnit ) {
// Only the first table is added to the join. If you need another table
// to be joined then use the additional_join_constraints
foreach ($this->attributeValuesQuery->joins as $join) {
$query->addTable(new Table(
new Schema($join->schema),
$join->name,
$join->name
));
if ($this->additionalJoinConstraints === null) {
break;
}
}
}
if ( $this->isAggregationUnit && 'none' == $this->id ) {
// Group by none is a special case of time aggregation where there is no group by. Here,
// we do not use the attribute_to_aggregate_key_map at all for the id fields, but rather
// use the id field from the attribute_values_query.
$alias = $this->qualifyColumnName('id', true);
$formula = $this->attributeValuesQuery->getRecord('id');
$field = new Field($formula, $alias);
$query->addField($field);
} else {
// Add the id field. XDMoD expects a single ID field that is always aliased to
// sprintf('%s_id', $groupById)
$alias = $this->qualifyColumnName('id', true);
$formula = $this->verifyAndReplaceTableAlias($this->attributeValuesQuery->getRecord('id'), $query);
$field = new Field($formula, $alias);
$query->addField($field);
$this->logger->trace(sprintf("%s Add ID field '%s AS %s'", $this, $field, $alias));
// Add a GroupBy and Where condition for each field that is part of the key that maps a
// dimensional attribute to the aggregate table.
$mapIndex = 0;
$useAlternateGroupBy = (0 != count($this->alternateGroupByColumns));
foreach ( $this->attributeToAggregateKeyMap as $attributeKey => $aggregateKey ) {
$alias = $this->qualifyColumnName($attributeKey, true);
// Aggregation unit group bys use the date table rather than the attribute table
$tableObj = ( $this->isAggregationUnit ? $query->getDateTable() : $this->attributeTableObj );
$groupByField = new TableField(
$tableObj,
($useAlternateGroupBy ? $this->alternateGroupByColumns[$mapIndex++] : $attributeKey),
$alias
);
$query->addGroup($groupByField);
$this->logger->trace(sprintf("%s Add GROUP BY '%s'", $this, $groupByField));
// The aggregation unit where condition is already added by Query::setDuration()
if ( ! $this->isAggregationUnit ) {
$pieces = explode('.', $attributeKey);
if ( count($pieces) === 2 ) {
$alternateAttributeTableObj = new Table($this->attributeTableObj->getSchema(), $pieces[0], $pieces[0]);
$attributeKey = $pieces[1];
} else {
$alternateAttributeTableObj = null;
}
$where = new WhereCondition(
new TableField(!empty($alternateAttributeTableObj) ? $alternateAttributeTableObj : $tableObj, $attributeKey),
'=',
new TableField($query->getDataTable(), $aggregateKey)
);
$this->logger->trace(sprintf("%s Add JOIN condition '%s'", $this, $where));
$query->addWhereCondition($where);
}
}
}
// If additional join constraints were specified add those here.
if ( null !== $this->additionalJoinConstraints ) {
foreach ( $this->additionalJoinConstraints as $constraint ) {
$where = new WhereCondition(
new TableField(!empty($constraint->attribute_table) ? new Table($this->attributeTableObj->getSchema(), $constraint->attribute_table, $constraint->attribute_table) : $this->attributeTableObj, $constraint->attribute_expr),
$constraint->operation,
new TableField(!empty($constraint->aggregate_table) ? new Table($query->getDataTable()->getSchema(), $constraint->aggregate_table, $constraint->aggregate_table) : $query->getDataTable(), $constraint->aggregate_expr)
);
$this->logger->trace(sprintf("%s Add additional JOIN condition '%s'", $this, $where));
$query->addWhereCondition($where);
}
}
// We use the short and long name fields specified in the attribute_values_query. Note that
// these fields may contain their own local schema and table aliases so we must identify
// these aliases and change them to the configured attribute table for this group by. Given
// the example below, we must identify the "rf" alias, ensure that the table it aliases is
// the same as the attribute table for this group by, and then replace the alias with the
// table name.
//
// "attribute_values_query": {
// "records": {
// "id": "rf.id",
// "short_name": "rf.code",
// "name": "CONCAT(rf.name, '-', rf.code)"
// },
// "joins": [
// {
// "name": "resourcefact",
// "alias": "rf"
// }
// ]
// Note that start_ts is used by SimpleTimeseriesDataset and must be present for aggregation
// unit group bys such as GroupByDay, GroupByMonth, etc.
$fieldList = array('short_name', 'name', 'order_id', 'start_ts');
// If we find that there is an aliased column name (alias.column) in the formula, ensure
// that the aliased table is the attribute table for this group by. Fully qualified
// (schema.table.column) column names will be unchanged.
foreach ($fieldList as $fieldName) {
// Note that the formula specified as part of the attribute values query can be as
// simple as the name of a table column (e.g., "name"), or it could contain a table
// alias (e.g., "r.name"), or it could be a more complex formula (e.g., "CONCAT(rf.name,
// '-', rf.code)").
$formula = $this->attributeValuesQuery->getRecord($fieldName);
if ( false === $formula ) {
continue;
}
// TableField differs from Field in that it provides the table alias in addition to the
// definition where Field provides only the definition. This makes TableField suitable
// for simple values but not general SQL formulae where "CONCAT(name, '-', code)" would
// become "table.CONCAT(name, '-', code)". Here we use Field and include the table
// alias ourselves.
$alias = (
'start_ts' != $fieldName
? $this->qualifyColumnName($fieldName, true)
: sprintf('%s_%s', $query->getAggregationUnit(), $fieldName)
);
$field = new Field($this->verifyAndReplaceTableAlias($formula, $query), $alias);
$this->logger->trace(sprintf("%s Add field: %s AS %s", $this, $field, $alias));
$query->addField($field);
}
// Note that there are a few GroupBys that used $prepend = true such as GroupByJobTime,
// GroupByJobWaitTime, and GroupByNodeCount. Are these really necessary? If so we will need
// a way to specify TRUE here.
$this->addOrder($query, $multi_group);
}
/**
* The formulae provided by the attribute values query can be simple column names (column),
* aliased column names (alias.column), a fully qualified column names (schema.table.column), or
* an SQL formula (such as CONCAT(rf.name, '-'. rf.code)). Since the attribute values query is
* self contained, the alias used may or may not match that of the attribute table specified for
* this group by. If an alias is specified, ensure that the table it refers to matches the
* attribute table for this group by and change the alias to the table name. If the formula did
* not contain an alias, explicitly add the table name so we do not have ambiguous columns in
* the query.
*
* @param string $formula The SQL column or formula
*
* @param Query $query Used to determine the table alias when the GroupBy is an aggregation unit.
*
* @return string The formula with aliases changed to the group by table alias, or with an alias
* added if only a column was specified.
*
* @throws Exception if the formula had an aliased column name but the table that it referred to
* did not match the group by attribute table.
*/
protected function verifyAndReplaceTableAlias($formula, \DataWarehouse\Query\iQuery $query)
{
$formula = $this->realm->getVariableStore()->substitute($formula);
if ( $this->isAggregationUnit && 'none' == $this->id ) {
return $formula;
}
$matches = array();
if ( 0 === preg_match_all('/([a-zA-Z0-9$_]+\.)?([a-zA-Z0-9$_]+\.[a-zA-Z0-9$_]+)/', $formula, $matches, PREG_SET_ORDER) ) {
// The formula did not contain an aliased column name, assume that it is only a column
// name and add our table alias.
return sprintf(
"%s.%s",
( $this->isAggregationUnit ? $query->getDateTable()->getAlias() : $this->attributeTableName ),
$formula
);
}
foreach ( $matches as $match ) {
if ( ! empty($match[1]) ) {
// This is a fully qualified column name including schema, table, and column. (e.g.,
// modw.resourcefact.code)
continue;
}
// We have found a column name that includes an alias. Ensure that the table referenced
// by the alias is the same as the attribute table for this group by.
list($tableAlias, $column) = explode('.', $match[2]);
foreach ( $this->attributeValuesQueryAsStdClass->joins as $joinObj ) {
if ( isset($joinObj->alias) && $joinObj->alias == $tableAlias && $joinObj->name != $this->attributeTableName ) {
$this->logAndThrowException(
sprintf(
"Table for alias '%s' in attribute_value_query field '%s' refers to '%s' != group by attribute_table '%s'",
$tableAlias,
'short_name',
$joinObj->name,
$this->attributeTableName
)
);
}
}
// Replace the table alias with the actual table name
$formula = str_replace(
$match[2],
sprintf(
"%s.%s",
( $this->isAggregationUnit ? $query->getDateTable()->getAlias() : $this->attributeTableName ),
$column
),
$formula
);
}
return $formula;
}
/**
* @see iGroupBy::addWhereJoin()
* @see Query::addWhereAndJoin()
*/
public function addWhereJoin(\DataWarehouse\Query\iQuery $query, $aggregateTableName, $operation, $whereConstraint)
{
// Group by none is a special case where this method is a no-op
if ( $this->isAggregationUnit && 'none' == $this->id ) {
return;
}
$attributeKeyConstraints = array();
// JOIN with the attribute table in the query
$query->addTable($this->attributeTableObj);
// To support multi-column attribute keys, add JOIN conditions for each attribute and
// aggregate column in the key map.
foreach ( $this->attributeToAggregateKeyMap as $attributeKey => $aggregateKey ) {
$pieces = explode('.', $attributeKey);
if ( count($pieces) === 2 ) {
$attributeTableField = new TableField(new Table($this->attributeTableObj->getSchema(), $pieces[0], $pieces[0]), $pieces[1]);
} else {
$attributeTableField = new TableField($this->attributeTableObj, $attributeKey);
}
$aggregateTableField = new TableField($aggregateTableName, $aggregateKey);
$where = new WhereCondition($attributeTableField, '=', $aggregateTableField);
$this->logger->debug(sprintf('%s Add join condition: %s', $this, $where));
$query->addWhereCondition($where);
}
// Normalize the WHERE constraint. We may be able to set this as an array in the parameter
// list.
$whereConstraint = ( ! is_array($whereConstraint) ? array($whereConstraint) : $whereConstraint );
// Add the specified WHERE constraint. Note that to support multi-column keys the individual
// values of the constraint may be encoded with a delimiter
foreach ( $whereConstraint as $constraint ) {
$list = explode(self::FILTER_DELIMITER, $constraint);
foreach ( $this->attributeToAggregateKeyMap as $attributeKey => $aggregateKey ) {
$attributeKeyConstraints[$attributeKey][] = array_shift($list);
}
}
$mapIndex = 0;
$useAlternateGroupBy = (0 != count($this->alternateGroupByColumns));
foreach ( $attributeKeyConstraints as $attributeKey => $valueList ) {
$where = new WhereCondition(
sprintf(
'%s.%s',
$this->attributeTableName,
($useAlternateGroupBy ? $this->alternateGroupByColumns[$mapIndex++] : $attributeKey)
),
$operation,
sprintf("('%s')", implode("','", $valueList))
);
$this->logger->debug(sprintf('%s Add where condition: %s', $this, $where));
$query->addWhereCondition($where);
}
}
/**
* @see iGroupBy::addOrder()
*/
public function addOrder(\DataWarehouse\Query\iQuery $query, $multi_group = false, $direction = 'ASC', $prepend = false)
{
// There can be zero or more order by fields specified in the attribute values query. Add an
// order by clause for each of them.
$queryConfig = $this->attributeValuesQueryAsStdClass;
if ( ! isset($queryConfig->orderby) ) {
return;
}
foreach ( $queryConfig->orderby as $orderByField ) {
$orderBy = new OrderBy(
new Field($this->verifyAndReplaceTableAlias($orderByField, $query)),
$direction,
$this->id
);
if ( $prepend ) {
$this->logger->debug(sprintf('%s Prepending order by to query: %s', $this, $orderBy));
$query->prependOrder($orderBy);
} else {
$this->logger->debug(sprintf('%s Appending order by to query: %s', $this, $orderBy));
$query->addOrder($orderBy);
}
}
}
/**
* @see iGroupBy::getAttributeValues()
*/
public function getAttributeValues(array $restrictions = null)
{
$whereConditions = array();
$queryParameters = array();
// Use the attribute values query as a template so we don't modify the original object
$queryConfig = $this->attributeValuesQuery->toStdClass();
// To handle possible ambiguous columns in the attribute values query, be sure to alias the
// where conditions with the attribute table (first table).
$alias = (isset($queryConfig->joins[0]->alias) ? $queryConfig->joins[0]->alias : $this->attributeTableName);
if ( isset($restrictions['id']) ) {
$list = explode(self::FILTER_DELIMITER, $restrictions['id']);
foreach ( array_keys($this->attributeToAggregateKeyMap) as $attributeKey ) {
$whereConditions[] = sprintf('(%s.%s = :%s)', $alias, $attributeKey, $attributeKey);
$queryParameters[ sprintf(':%s', $attributeKey) ] = array_shift($list);
}
}
if ( isset($restrictions['name']) ) {
$whereConditions[] = sprintf(
'(%s LIKE :name OR %s LIKE :name)',
$this->attributeValuesQuery->getRecord('short_name'),
$this->attributeValuesQuery->getRecord('name')
);
$queryParameters[':name'] = $restrictions['name'];
}
if ( ! isset($queryConfig->where) || ! is_array($queryConfig->where) ) {
$queryConfig->where = $whereConditions;
} else {
$queryConfig->where = array_merge($queryConfig->where, $whereConditions);
}
$queryObj = new DbQuery($queryConfig, '`', $this->logger);
$sql = $queryObj->getSql();
$this->logger->debug(sprintf("%s: Fetch attribute values with query\n%s", $this, $sql));
$this->logger->trace(sprintf("Values: %s", print_r($queryParameters, true)));
return \DataWarehouse::connect()->query($sql, $queryParameters);
}
/**
* @see iGroupBy::getAttributeValuesQuery()
*/
public function getAttributeValuesQuery()
{
return $this->attributeValuesQuery;
}
/**
* Static accessors used to create usage chart settings in DataWarehouse/Access/Usage.php and
* DataWarehouse/Query/GroupBy.php
*/
/**
* @see iGroupBy::getChartSettings()
*/
public function getChartSettings($isMultiChartPage = false)
{
return json_encode(
array(
'dataset_type' => $this->getDefaultDatasetType(),
'display_type' => $this->getDefaultDisplayType($this->getDefaultDatasetType()),
'combine_type' => $this->getDefaultCombineMethod(),
'limit' => $this->getDefaultLimit($isMultiChartPage),
'offset' => $this->getDefaultOffset(),
'log_scale' => $this->getDefaultLogScale(),
'show_legend' => $this->getDefaultShowLegend(),
'show_trend_line' => $this->getDefaultShowTrendLine(),
'show_error_bars' => $this->getDefaultShowErrorBars(),
'show_guide_lines' => $this->getDefaultShowGuideLines(),
'show_aggregate_labels' => $this->getDefaultShowAggregateLabels(),
'show_error_labels' => $this->getDefaultShowErrorLabels(),
'enable_errors' => $this->getDefaultEnableErrors(),
'enable_trend_line' => $this->getDefaultEnableTrendLine(),
)
);
}
/**
* @see iGroupBy::getDefaultDatasetType()
*/
public function getDefaultDatasetType()
{
return $this->chartOptions['dataset_type'];
}
/**
* @see iGroupBy::getDefaultDisplayType()
*/
public function getDefaultDisplayType($dataset_type = 'timeseries')
{
if ( ! array_key_exists($dataset_type, $this->chartOptions['dataset_display_type']) ) {
$this->logAndThrowException(sprintf("Unsupported dataset type: '%s'", $dataset_type));
}
return $this->chartOptions['dataset_display_type'][$dataset_type];
}
/**
* @see iGroupBy::getDefaultCombineMethod()
*/
public function getDefaultCombineMethod()
{
return $this->chartOptions['combine_method'];
}
/**
* @see iGroupBy::getDefaultShowLegend()
*/
public function getDefaultShowLegend()
{
return $this->chartOptions['show_legend'];
}
/**
* @see iGroupBy::getDefaultLimit()
*/
public function getDefaultLimit($isMultiChartPage = false)
{
$pageType = ( $isMultiChartPage ? 'multichart_page' : 'default' );
return $this->chartOptions['limit'][$pageType];
}
/**
* @see iGroupBy::getDefaultOffset()
*/
public function getDefaultOffset()
{
return $this->chartOptions['offset'];
}
/**
* @see iGroupBy::getDefaultLogScale()
*/
public function getDefaultLogScale()
{
return $this->chartOptions['log_scale'];
}
/**
* @see iGroupBy::getDefaultShowTrendLine()
*/
public function getDefaultShowTrendLine()
{
return $this->chartOptions['show_trend_line'];
}
/**
* @see iGroupBy::getDefaultShowErrorBars()
*/
public function getDefaultShowErrorBars()
{
return $this->chartOptions['show_error_bars'];
}
/**
* @see iGroupBy::getDefaultShowGuideLines()
*/
public function getDefaultShowGuideLines()
{
return $this->chartOptions['show_guide_lines'];
}
/**
* @see iGroupBy::getDefaultShowAggregateLabels()
*/
public function getDefaultShowAggregateLabels()
{
return $this->chartOptions['show_aggregate_labels'];
}
/**
* @see iGroupBy::getDefaultShowErrorLabels()
*/
public function getDefaultShowErrorLabels()
{
return $this->chartOptions['show_error_labels'];
}
/**
* @see iGroupBy::getDefaultEnableErrors()
*/
public function getDefaultEnableErrors()
{
return $this->chartOptions['enable_errors'];
}
/**
* @see iGroupBy::getDefaultEnableTrendLine()
*/
public function getDefaultEnableTrendLine()
{
return $this->chartOptions['enable_trend_line'];
}
/**
* @see iGroupBy::getCategory()
*/
public function getCategory()
{
return $this->category;
}
/**
* @see iGroupBy::__toString()
*/
public function __toString()
{
return sprintf('Realm(%s)->GroupBy(id=%s, table=%s)', $this->realm->getId(), $this->id, $this->getAttributeTable());
}
}