blob: 3f38d9b121d1b2d1eca593fb6a76fbacb5f5afc7 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
|
<?php
// Icinga Web 2 Cube Module | (c) 2022 Icinga GmbH | GPLv2
namespace Icinga\Module\Cube\IcingaDb;
use Icinga\Module\Cube\Cube;
use Icinga\Module\Icingadb\Common\Auth;
use Icinga\Module\Icingadb\Common\Database;
use ipl\Orm\Query;
use ipl\Sql\Adapter\Pgsql;
use ipl\Sql\Expression;
use ipl\Sql\Select;
abstract class IcingaDbCube extends Cube
{
use Auth;
use Database;
/** @var Query The inner query fetching all required data */
protected $innerQuery;
/** @var Select The rollup query, creating grouped sums over innerQuery */
protected $rollupQuery;
/** @var Select The outer query, orders respecting NULL values, rollup first */
protected $fullQuery;
protected $objectsFilter;
abstract public function getObjectsFilter();
/**
* An IcingaDbCube must provide a list of all available columns
*
* This is a key/value array with the key being the fact name / column alias
* and
*
* @return array
*/
abstract public function getAvailableFactColumns();
/**
* @return Query
*/
abstract public function prepareInnerQuery();
/**
* Get our inner query
*
* Hint: mostly used to get rid of NULL values
*
* @return Query
*/
public function innerQuery()
{
if ($this->innerQuery === null) {
$this->innerQuery = $this->prepareInnerQuery();
}
return $this->innerQuery;
}
/**
* Get our rollup query
*
* @return Select
*/
protected function rollupQuery()
{
if ($this->rollupQuery === null) {
$this->rollupQuery = $this->prepareRollupQuery();
}
return $this->rollupQuery;
}
/**
* Add a specific named dimension
*
* @param string $name
* @return $this
*/
public function addDimensionByName($name)
{
$this->addDimension($this->createDimension($name));
return $this;
}
/**
* We first prepare the queries and to finalize it later on
*
* This way dimensions can be added one by one, they will be allowed to
* optionally join additional tables or apply other modifications late
* in the process
*
* @return void
*/
protected function finalizeInnerQuery()
{
$query = $this->innerQuery()->getSelectBase();
$columns = [];
foreach ($this->dimensions as $name => $dimension) {
$quotedDimension = $this->getDb()->quoteIdentifier([$name]);
$dimension->addToCube($this);
$columns[$quotedDimension] = $dimension->getColumnExpression($this);
if ($this->hasSlice($name)) {
$query->where(
$dimension->getColumnExpression($this) . ' = ?',
$this->slices[$name]
);
} else {
$columns[$quotedDimension] = $dimension->getColumnExpression($this);
}
}
$query->columns($columns);
}
protected function prepareRollupQuery()
{
$dimensions = $this->listDimensions();
$this->finalizeInnerQuery();
$columns = [];
$groupBy = [];
foreach ($dimensions as $name => $dimension) {
$quotedDimension = $this->getDb()->quoteIdentifier([$name]);
$columns[$quotedDimension] = 'f.' . $quotedDimension;
$groupBy[] = $quotedDimension;
}
$availableFacts = $this->getAvailableFactColumns();
foreach ($this->chosenFacts as $alias) {
$columns[$alias] = new Expression('SUM(f.' . $availableFacts[$alias] . ')');
}
if (! empty($groupBy)) {
if ($this->getDb()->getAdapter() instanceof Pgsql) {
$groupBy = 'ROLLUP(' . implode(', ', $groupBy) . ')';
} else {
$groupBy[count($groupBy) - 1] .= ' WITH ROLLUP';
}
}
$rollupQuery = new Select();
$rollupQuery->from(['f' => $this->innerQuery()->assembleSelect()])
->columns($columns)
->groupBy($groupBy);
return $rollupQuery;
}
protected function prepareFullQuery()
{
$rollupQuery = $this->rollupQuery();
$columns = [];
foreach ($this->listColumns() as $column) {
$quotedColumn = $this->getDb()->quoteIdentifier([$column]);
$columns[$quotedColumn] = 'rollup.' . $this->getDb()->quoteIdentifier([$column]);
}
$fullQuery = new Select();
$fullQuery->from(['rollup' => $rollupQuery])->columns($columns);
foreach ($columns as $quotedColumn => $_) {
$fullQuery->orderBy("($quotedColumn IS NOT NULL)");
$fullQuery->orderBy($quotedColumn);
}
return $fullQuery;
}
/**
* Lazy-load our full query
*
* @return Select
*/
protected function fullQuery()
{
if ($this->fullQuery === null) {
$this->fullQuery = $this->prepareFullQuery();
}
return $this->fullQuery;
}
public function fetchAll()
{
$query = $this->fullQuery();
return $this->getDb()->fetchAll($query);
}
}
|
