4 files changed, 678 insertions, 0 deletions
diff --git a/library/Cube/IcingaDb/CustomVariableDimension.php b/library/Cube/IcingaDb/CustomVariableDimension.php
new file mode 100644
index 0000000..34a395c
--- /dev/null
+++ b/library/Cube/IcingaDb/CustomVariableDimension.php
@@ -0,0 +1,166 @@
+<?php
+
+// Icinga Web 2 Cube Module | (c) 2022 Icinga GmbH | GPLv2
+
+namespace Icinga\Module\Cube\IcingaDb;
+
+use Icinga\Module\Cube\Cube;
+use Icinga\Module\Cube\Dimension;
+use Icinga\Module\Icingadb\Common\Auth;
+use Icinga\Module\Icingadb\Model\CustomvarFlat;
+use Icinga\Module\Icingadb\Model\Service;
+use ipl\Sql\Expression;
+use ipl\Stdlib\Filter;
+
+class CustomVariableDimension implements Dimension
+{
+    use Auth;
+
+    /** @var string Prefix for host custom variable */
+    public const HOST_PREFIX = 'host.vars.';
+
+    /** @var string Prefix for service custom variable */
+    public const SERVICE_PREFIX = 'service.vars.';
+
+    /** @var ?string variable source name */
+    protected $sourceName;
+
+    /** @var ?string Variable name without prefix */
+    protected $varName;
+
+    /** @var string Variable name with prefix */
+    protected $name;
+
+    protected $label;
+
+    protected $wantNull = false;
+
+    public function __construct($name)
+    {
+        if (preg_match('/^(host|service)\.vars\.(.*)/', $name, $matches)) {
+            $this->sourceName = $matches[1];
+            $this->varName = $matches[2];
+        }
+
+        $this->name = $name;
+    }
+
+    /**
+     * Get the variable name without prefix
+     *
+     * @return string
+     */
+    public function getVarName(): string
+    {
+        return $this->varName ?? $this->getName();
+    }
+
+    /**
+     * Get the variable source name
+     *
+     * @return ?string
+     */
+    public function getSourceName(): ?string
+    {
+        return $this->sourceName;
+    }
+
+    public function getName()
+    {
+        return $this->name;
+    }
+
+    public function getLabel()
+    {
+        return $this->label ?: $this->getName();
+    }
+
+    public function setLabel($label)
+    {
+        $this->label = $label;
+
+        return $this;
+    }
+
+    public function addLabel($label)
+    {
+        if ($this->label === null) {
+            $this->setLabel($label);
+        } else {
+            $this->label .= ' & ' . $label;
+        }
+
+        return $this;
+    }
+
+    /**
+     * Define whether null values should be shown
+     *
+     * @param bool $wantNull
+     * @return $this
+     */
+    public function wantNull($wantNull = true)
+    {
+        $this->wantNull = $wantNull;
+
+        return $this;
+    }
+
+    /**
+     * @param IcingaDbCube $cube
+     * @return Expression|string
+     */
+    public function getColumnExpression(Cube $cube)
+    {
+        $expression = $cube->getDb()->quoteIdentifier([$this->createCustomVarAlias(), 'flatvalue']);
+
+        if ($this->wantNull) {
+            return new Expression("COALESCE($expression, '-')");
+        }
+
+        return $expression;
+    }
+
+    public function addToCube(Cube $cube)
+    {
+        /** @var IcingaDbCube $cube */
+        $innerQuery = $cube->innerQuery();
+        $sourceTable = $this->getSourceName() ?? $innerQuery->getModel()->getTableName();
+
+        $subQuery = $innerQuery->createSubQuery(new CustomvarFlat(), $sourceTable . '.vars');
+        $subQuery->getSelectBase()->resetWhere(); // The link to the outer query is the ON condition
+        $subQuery->columns(['flatvalue', 'object_id' => $sourceTable . '.id']);
+        $subQuery->filter(Filter::like('flatname', $this->getVarName()));
+
+        // Values might not be unique (wildcard dimensions)
+        $subQueryModelAlias = $subQuery->getResolver()->getAlias($subQuery->getModel());
+        $subQuery->getSelectBase()->groupBy([
+            $subQueryModelAlias . '.flatname', // Required by postgres, if there are any custom variable protections
+            $subQueryModelAlias . '.flatvalue',
+            'object_id'
+        ]);
+
+        $this->applyRestrictions($subQuery);
+
+        $subQueryAlias = $cube->getDb()->quoteIdentifier([$this->createCustomVarAlias()]);
+        $innerQuery->getSelectBase()->groupBy($subQueryAlias . '.flatvalue');
+
+        $sourceIdPath = '.id';
+        if ($innerQuery->getModel() instanceof Service && $sourceTable === 'host') {
+            $sourceIdPath = '.host_id';
+        }
+
+        $innerQuery->getSelectBase()->join(
+            [$subQueryAlias => $subQuery->assembleSelect()],
+            [
+                $subQueryAlias . '.object_id = '
+                . $innerQuery->getResolver()->getAlias($innerQuery->getModel()) . $sourceIdPath
+            ]
+        );
+    }
+
+    protected function createCustomVarAlias(): string
+    {
+        return implode('_', ['c', $this->getSourceName(), $this->getVarName()]);
+    }
+}
diff --git a/library/Cube/IcingaDb/IcingaDbCube.php b/library/Cube/IcingaDb/IcingaDbCube.php
new file mode 100644
index 0000000..44c7619
--- /dev/null
+++ b/library/Cube/IcingaDb/IcingaDbCube.php
@@ -0,0 +1,338 @@
+<?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 Icinga\Module\Icingadb\Model\Host;
+use Icinga\Module\Icingadb\Model\Service;
+use ipl\Orm\Common\SortUtil;
+use ipl\Orm\Query;
+use ipl\Sql\Adapter\Pgsql;
+use ipl\Sql\Expression;
+use ipl\Sql\Select;
+use ipl\Stdlib\BaseFilter;
+
+abstract class IcingaDbCube extends Cube
+{
+    use Auth;
+    use BaseFilter;
+    use Database;
+
+    public const SLICE_PREFIX = 'slice.';
+    public const IS_USING_ICINGADB = true;
+
+    /** @var bool Whether to show problems only */
+    protected $problemsOnly = false;
+
+    /** @var string Sort param used to sort dimensions by value */
+    public const DIMENSION_VALUE_SORT_PARAM = 'value';
+
+    /** @var string Sort param used to sort dimensions by severity */
+    public const DIMENSION_SEVERITY_SORT_PARAM = 'severity';
+
+    /** @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;
+
+    /** @var array The sort order of dimensions, column as key and direction as value */
+    protected $sortBy;
+
+    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;
+    }
+
+    /**
+     * Set whether to show problems only
+     *
+     * @param bool $problemOnly
+     *
+     * @return $this
+     */
+    public function problemsOnly(bool $problemOnly = true): self
+    {
+        $this->problemsOnly = $problemOnly;
+
+        return $this;
+    }
+
+
+    /**
+     * Get whether to show problems only
+     *
+     * @return bool
+     */
+    public function isProblemsOnly(): bool
+    {
+        return $this->problemsOnly;
+    }
+
+    /**
+     * Fetch the host variable dimensions
+     *
+     * @return array
+     */
+    public function fetchHostVariableDimensions(): array
+    {
+        $query = Host::on($this->getDb())
+            ->with('customvar_flat')
+            ->columns('customvar_flat.flatname')
+            ->orderBy('customvar_flat.flatname');
+
+        $this->applyRestrictions($query);
+
+        $query->getSelectBase()->groupBy('flatname');
+
+        $dimensions = [];
+        foreach ($query as $row) {
+            // Replaces array index notations with [*] to get results for arbitrary indexes
+            $name = preg_replace('/\\[\d+](?=\\.|$)/', '[*]', $row->customvar_flat->flatname);
+            $name = strtolower($name);
+            $dimensions[CustomVariableDimension::HOST_PREFIX . $name] = 'Host ' . $name;
+        }
+
+        return $dimensions;
+    }
+
+    /**
+     * Fetch the service variable dimensions
+     *
+     * @return array
+     */
+    public function fetchServiceVariableDimensions(): array
+    {
+        $query = Service::on($this->getDb())
+            ->with('customvar_flat')
+            ->columns('customvar_flat.flatname')
+            ->orderBy('customvar_flat.flatname');
+
+        $this->applyRestrictions($query);
+
+        $query->getSelectBase()->groupBy('flatname');
+
+        $dimensions = [];
+        foreach ($query as $row) {
+            // Replaces array index notations with [*] to get results for arbitrary indexes
+            $name = preg_replace('/\\[\d+](?=\\.|$)/', '[*]', $row->customvar_flat->flatname);
+            $name = strtolower($name);
+            $dimensions[CustomVariableDimension::SERVICE_PREFIX . $name] = 'Service ' . $name;
+        }
+
+        return $dimensions;
+    }
+
+    /**
+     * Set sort by columns
+     *
+     * @param ?string $sortBy
+     *
+     * @return $this
+     */
+    public function sortBy(?string $sortBy): self
+    {
+        if (empty($sortBy)) {
+            return $this;
+        }
+
+        $this->sortBy = SortUtil::createOrderBy($sortBy)[0];
+
+        return $this;
+    }
+
+    /**
+     * Get sort by columns
+     *
+     * @return ?array Column as key and direction as value
+     */
+    public function getSortBy(): ?array
+    {
+        return $this->sortBy;
+    }
+
+    /**
+     * 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();
+        $select = $query->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)) {
+                $select->where(
+                    $dimension->getColumnExpression($this) . ' = ?',
+                    $this->slices[$name]
+                );
+            } else {
+                $columns[$quotedDimension] = $dimension->getColumnExpression($this);
+            }
+        }
+
+        $select->columns($columns);
+
+        $this->applyRestrictions($query);
+        if ($this->hasBaseFilter()) {
+            $query->filter($this->getBaseFilter());
+        }
+    }
+
+    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 = [];
+        $orderBy = [];
+        $sortBy = $this->getSortBy();
+        foreach ($this->listColumns() as $column) {
+            $quotedColumn = $this->getDb()->quoteIdentifier([$column]);
+            $columns[$quotedColumn] = 'rollup.' . $quotedColumn;
+
+            if ($this->hasDimension($column)) {
+                $orderBy["($quotedColumn IS NOT NULL)"] = null;
+
+                $sortDir = 'ASC';
+                if ($sortBy && self::DIMENSION_VALUE_SORT_PARAM === $sortBy[0]) {
+                    $sortDir = $sortBy[1] ?? 'ASC';
+                }
+
+                $orderBy[$quotedColumn] = $sortDir;
+            }
+        }
+
+        return (new Select())
+            ->from(['rollup' => $rollupQuery])
+            ->columns($columns)
+            ->orderBy($orderBy);
+    }
+
+    /**
+     * 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);
+    }
+}
diff --git a/library/Cube/IcingaDb/IcingaDbHostStatusCube.php b/library/Cube/IcingaDb/IcingaDbHostStatusCube.php
new file mode 100644
index 0000000..14e083f
--- /dev/null
+++ b/library/Cube/IcingaDb/IcingaDbHostStatusCube.php
@@ -0,0 +1,80 @@
+<?php
+
+// Icinga Web 2 Cube Module | (c) 2022 Icinga GmbH | GPLv2
+
+namespace Icinga\Module\Cube\IcingaDb;
+
+use Icinga\Module\Cube\CubeRenderer\HostStatusCubeRenderer;
+use Icinga\Module\Icingadb\Model\Host;
+use Icinga\Module\Icingadb\Model\HoststateSummary;
+use ipl\Stdlib\Filter;
+use ipl\Stdlib\Str;
+
+class IcingaDbHostStatusCube extends IcingaDbCube
+{
+    public function getRenderer()
+    {
+        return new HostStatusCubeRenderer($this);
+    }
+
+    public function getAvailableFactColumns()
+    {
+        return [
+            'hosts_cnt' => 'hosts_total',
+            'hosts_down' => 'hosts_down_handled + f.hosts_down_unhandled',
+            'hosts_unhandled_down' => 'hosts_down_unhandled',
+        ];
+    }
+
+    public function createDimension($name)
+    {
+        $this->registerAvailableDimensions();
+
+        if (isset($this->availableDimensions[$name])) {
+            return clone $this->availableDimensions[$name];
+        }
+
+        return new CustomVariableDimension($name);
+    }
+
+    public function listAvailableDimensions()
+    {
+        return $this->fetchHostVariableDimensions();
+    }
+
+    public function prepareInnerQuery()
+    {
+        $query = HoststateSummary::on($this->getDb());
+        $query->columns(array_diff_key($query->getModel()->getColumns(), (new Host())->getColumns()));
+        $query->disableDefaultSort();
+        $this->applyRestrictions($query);
+
+        $this->innerQuery = $query;
+        return $this->innerQuery;
+    }
+
+    /**
+     * Return Filter for Hosts cube.
+     *
+     * @return Filter\Any|Filter\Chain
+     */
+    public function getObjectsFilter()
+    {
+        if ($this->objectsFilter === null) {
+            $this->finalizeInnerQuery();
+
+            $hosts = $this->innerQuery()->columns(['host' => 'host.name']);
+            $hosts->getSelectBase()->resetGroupBy();
+
+            $filter = Filter::any();
+
+            foreach ($hosts as $object) {
+                $filter->add(Filter::equal('host.name', $object->host));
+            }
+
+            $this->objectsFilter = $filter;
+        }
+
+        return $this->objectsFilter;
+    }
+}
diff --git a/library/Cube/IcingaDb/IcingaDbServiceStatusCube.php b/library/Cube/IcingaDb/IcingaDbServiceStatusCube.php
new file mode 100644
index 0000000..ac59de2
--- /dev/null
+++ b/library/Cube/IcingaDb/IcingaDbServiceStatusCube.php
@@ -0,0 +1,94 @@
+<?php
+
+// Icinga Web 2 Cube Module | (c) 2022 Icinga GmbH | GPLv2
+
+namespace Icinga\Module\Cube\IcingaDb;
+
+use Icinga\Module\Cube\CubeRenderer\ServiceStatusCubeRenderer;
+use Icinga\Module\Icingadb\Model\Service;
+use Icinga\Module\Icingadb\Model\ServicestateSummary;
+use ipl\Stdlib\Filter;
+use ipl\Stdlib\Str;
+
+class IcingaDbServiceStatusCube extends IcingaDbCube
+{
+    public function getRenderer()
+    {
+        return new ServiceStatusCubeRenderer($this);
+    }
+
+    public function createDimension($name)
+    {
+        $this->registerAvailableDimensions();
+
+        if (isset($this->availableDimensions[$name])) {
+            return clone $this->availableDimensions[$name];
+        }
+
+        return new CustomVariableDimension($name);
+    }
+
+    public function getAvailableFactColumns()
+    {
+        return [
+            'services_cnt' => 'services_total',
+            'services_critical' => 'services_critical_handled + f.services_critical_unhandled',
+            'services_unhandled_critical' => 'services_critical_unhandled',
+            'services_warning' => 'services_warning_handled + f.services_warning_unhandled',
+            'services_unhandled_warning' => 'services_warning_unhandled',
+            'services_unknown' => 'services_unknown_handled + f.services_unknown_unhandled',
+            'services_unhandled_unknown' => 'services_unknown_unhandled',
+        ];
+    }
+
+    public function listAvailableDimensions()
+    {
+        return array_merge(
+            $this->fetchServiceVariableDimensions(),
+            $this->fetchHostVariableDimensions()
+        );
+    }
+
+    public function prepareInnerQuery()
+    {
+        $query = ServicestateSummary::on($this->getDb());
+        $query->columns(array_diff_key($query->getModel()->getColumns(), (new Service())->getColumns()));
+        $query->disableDefaultSort();
+        $this->applyRestrictions($query);
+
+        return $query;
+    }
+
+    /**
+     * Return Filter for Services cube.
+     *
+     * @return Filter\Any|Filter\Chain
+     */
+    public function getObjectsFilter()
+    {
+        if ($this->objectsFilter === null) {
+            $this->finalizeInnerQuery();
+
+            $services = $this->innerQuery()->columns([
+                'host_name' => 'host.name',
+                'service_name' => 'service.name'
+            ]);
+
+            $services->getSelectBase()->resetGroupBy();
+            $filter = Filter::any();
+
+            foreach ($services as $service) {
+                $filter->add(
+                    Filter::all(
+                        Filter::equal('service.name', $service->service_name),
+                        Filter::equal('host.name', $service->host_name)
+                    )
+                );
+            }
+
+            $this->objectsFilter = $filter;
+        }
+
+        return $this->objectsFilter;
+    }
+}