/**
* @depends testConfiguration
* @group aggregation
* @group slow
*/
function testAggregation()
{
$rowsData = $this->countRows(static::$uuid);
$this->assertGreaterThan(0, $rowsData);
echo $this->formatMsg("DataRows") . number_format($rowsData, 0, '.', '.');
$agg = new Util\Aggregation(self::$conn);
$aggLevels = $agg->getOptimalAggregationLevel(static::$uuid);
foreach ($aggLevels as $level) {
$rowsAgg = $this->countAggregationRows(static::$uuid, $level['type']);
$this->assertGreaterThan(0, $rowsAgg);
echo $this->formatMsg("AggregateRows (" . $level['level'] . ")") . number_format($rowsAgg, 0, '.', '.');
echo $this->formatMsg("AggregateRatio (" . $level['level'] . ")") . "1:" . round($rowsData / $rowsAgg);
}
}
/**
* Calculate valid timestamp boundaries for aggregation table usage
*
* table: --data-- -----aggregate----- -data-
* timestamp: from ... aggFrom ..... aggTo ... to
*
* @param string $type aggregation level (e.g. 'day')
* @return boolean true: aggregate table contains data, aggFrom/aggTo contains valid range
* @author Andreas Goetz <*****@*****.**>
*/
private function getAggregationBoundary($aggFromDelta = null)
{
$dateFormat = Util\Aggregation::getAggregationDateFormat($this->aggLevel);
// day = "%Y-%m-%d"
// aggFrom becomes beginning of first period with aggregate data
$sqlParameters = array($this->channel->getId(), $this->aggType, $this->from);
if (isset($aggFromDelta)) {
// shift 'left' border of aggregate table use by $aggFromDelta units
$sql = 'SELECT UNIX_TIMESTAMP(' . 'DATE_ADD(' . 'FROM_UNIXTIME(MIN(timestamp) / 1000, ' . $dateFormat . '), ' . 'INTERVAL ' . $aggFromDelta . ' ' . $this->aggLevel . ')) * 1000 ' . 'FROM aggregate WHERE channel_id=? AND type=? AND ' . ' UNIX_TIMESTAMP(FROM_UNIXTIME(timestamp / 1000, ' . $dateFormat . ')) * 1000 >=?';
} else {
// find 'left' border of aggregate table after $from
$sql = 'SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(MIN(timestamp) / 1000, ' . $dateFormat . ')) * 1000 ' . 'FROM aggregate WHERE channel_id=? AND type=? AND ' . ' UNIX_TIMESTAMP(FROM_UNIXTIME(timestamp / 1000, ' . $dateFormat . ')) * 1000 >=?';
}
$this->aggFrom = $this->conn->fetchColumn($sql, $sqlParameters, 0);
$this->aggTo = null;
// aggregate table contains relevant data?
if (isset($this->aggFrom)) {
// aggTo becomes beginning of first period without aggregate data
$sqlParameters = array($this->channel->getId(), $this->aggType);
$sql = 'SELECT UNIX_TIMESTAMP(' . 'DATE_ADD(' . 'FROM_UNIXTIME(MAX(timestamp) / 1000, ' . $dateFormat . '), ' . 'INTERVAL 1 ' . $this->aggLevel . ')) * 1000 ' . 'FROM aggregate WHERE channel_id=? AND type=?';
if (isset($this->to)) {
$sqlParameters[] = $this->to;
$sql .= ' AND timestamp<?';
}
$this->aggTo = $this->conn->fetchColumn($sql, $sqlParameters, 0);
}
if (self::$debug) {
printf("from .. aggFrom .. aggTo .. to\n");
printf("%s |%s .. %s| %s\n", self::pd($this->from), self::pd($this->aggFrom), self::pd($this->aggFrom), self::pd($this->to));
}
return isset($this->aggFrom) && isset($this->aggTo) && $this->aggFrom < $this->aggTo && $this->from <= $this->aggFrom && $this->aggTo <= $this->to;
}
protected function execute(InputInterface $input, OutputInterface $output)
{
if (!in_array($mode = $input->getOption('mode'), array('full', 'delta'))) {
throw new \Exception('Unsupported aggregation mode ' . $mode);
}
// loop through all uuids
foreach ($input->getArgument('uuid') as $uuid) {
// loop through all aggregation levels
foreach ($input->getOption('level') as $level) {
if (!Util\Aggregation::isValidAggregationLevel($level)) {
throw new \Exception('Unsupported aggregation level ' . $level);
}
$msg = "Performing '" . $mode . "' aggregation";
if ($uuid) {
$msg .= " for UUID " . $uuid;
}
echo $msg . " on '" . $level . "' level.\n";
$rows = $this->aggregator->aggregate($uuid, $level, $mode, $input->getOption('period'));
echo "Updated {$rows} rows.\n";
}
}
}
* @return int number of affected rows
*/
public function aggregate($uuid = null, $level = 'day', $mode = 'full', $period = null)
{
// validate settings
if (!in_array($mode, array('full', 'delta'))) {
throw new \RuntimeException('Unsupported aggregation mode ' . $mode);
}
if (!$this->isValidAggregationLevel($level)) {
throw new \RuntimeException('Unsupported aggregation level ' . $level);
}
// get channel definition to select correct aggregation function
$sqlParameters = array('channel');
$sql = 'SELECT id, uuid, type FROM entities WHERE class = ?';
if ($uuid) {
$sqlParameters[] = $uuid;
$sql .= ' AND uuid = ?';
}
$rows = 0;
// aggregate each channel
foreach ($this->conn->fetchAll($sql, $sqlParameters) as $row) {
$entity = Definition\EntityDefinition::get($row['type']);
$interpreter = $entity->getInterpreter();
$rows += $this->aggregateChannel($row['id'], $interpreter, $mode, $level, $period);
}
return $rows;
}
}
// initialize static variables
Aggregation::init();
/**
* @depends testGetBaseline
* @group aggregation
*/
function testAggregateOptimizer()
{
$agg = new Util\Aggregation(self::$conn);
// at this point we have aggregates for 'hour' and 'day'
$agg->aggregate(self::$uuid, 'hour', 'delta');
$typeHour = Util\Aggregation::getAggregationLevelTypeValue('hour');
$typeDay = Util\Aggregation::getAggregationLevelTypeValue('day');
// day: 2 rows of aggregation data, day first
$opt = $agg->getOptimalAggregationLevel(self::$uuid);
$ref = array(array('level' => 'day', 'type' => $typeDay, 'count' => $this->countAggregationRows(self::$uuid, $typeDay)), array('level' => 'hour', 'type' => $typeHour, 'count' => $this->countAggregationRows(self::$uuid, $typeHour)));
$this->assertEquals($ref, $opt);
// hour: 1 row of aggregation data
$opt = $agg->getOptimalAggregationLevel(self::$uuid, 'hour');
$ref = array(array('level' => 'hour', 'type' => $typeHour, 'count' => $this->countAggregationRows(self::$uuid, $typeHour)));
$this->assertEquals($ref, $opt);
// minute: no aggregation data => false
$typeMinute = Util\Aggregation::getAggregationLevelTypeValue('minute');
$opt = $agg->getOptimalAggregationLevel(self::$uuid, 'minute');
$this->assertFalse($opt);
// 3 data, cannot use daily aggregates for hourly request
$this->getTuplesRaw(strtotime('2 days ago 0:00') * 1000, strtotime('1 days ago 0:00') * 1000, 'hour');
$this->assertEquals(3, $this->json->data->rows, 'Possibly wrong aggregation level chosen by optimizer');
}