path: root/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php

<?php
/* Icinga Web 2 | (c) 2013 Icinga Development Team | GPLv2+ */

namespace Icinga\Module\Monitoring\Backend\Ido\Query;

use Icinga\Data\Filter\FilterNot;
use Zend_Db_Expr;
use Icinga\Application\Icinga;
use Icinga\Application\Hook;
use Icinga\Application\Logger;
use Icinga\Data\Db\DbQuery;
use Icinga\Data\Filter\Filter;
use Icinga\Data\Filter\FilterExpression;
use Icinga\Exception\IcingaException;
use Icinga\Exception\NotImplementedError;
use Icinga\Exception\ProgrammingError;
use Icinga\Exception\QueryException;
use Icinga\Web\Session;
use Icinga\Module\Monitoring\Data\ColumnFilterIterator;
use Zend_Db_Select;

/**
 * Base class for Ido Queries
 *
 * This is the base class for all Ido queries and should be extended for new queries
 * The starting point for implementations is the columnMap attribute. This is an asscociative array in the
 * following form:
 *
 * <pre>
 * <code>
 * array(
 *      'virtualTable' => array(
 *          'fieldalias1' => 'queryColumn1',
 *          'fieldalias2' => 'queryColumn2',
 *          ....
 *      ),
 *      'virtualTable2' => array(
 *          'host'       =>  'host_name1'
 *      )
 * )
 * </code>
 * </pre>
 *
 * This allows you to select e.g. fieldalias1, which automatically calls the query code for joining 'virtualTable'. If
 * you afterwards select 'host', 'virtualTable2' will be joined. The joining logic is up to you, in order to make the
 * above example work you need to implement the joinVirtualTable() method which contain your
 * custom (Zend_Db) logic for joining, filtering and querying the data you want.
 *
 */
abstract class IdoQuery extends DbQuery
{
    /**
     * The prefix to use
     *
     * @var string
     */
    protected $prefix;

    /**
     * An array to map aliases to column names
     *
     * @var array
     */
    protected $idxAliasColumn;

    /**
     * An array to map aliases to table names
     *
     * @var array
     */
    protected $idxAliasTable;

    /**
     * An array to map custom aliases to aliases
     *
     * @var array
     */
    protected $idxCustomAliases;

    /**
     * The column map containing all filterable columns
     *
     * This must be overwritten by child classes, in the format
     * array(
     *      'virtualTable' => array(
     *          'fieldalias1' => 'queryColumn1',
     *          'fieldalias2' => 'queryColumn2',
     *          ....
     *      )
     * )
     *
     * @var array
     */
    protected $columnMap = array();

    /**
     * Custom vars available for this query
     *
     * @var array
     */
    protected $customVars = array();

    /**
     * Printf compatible string to joins custom vars
     *
     * - %1$s   Source field, contain the object_id
     * - %2$s   Alias used for the relation
     * - %3$s   Name of the CustomVariable
     *
     * @var string
     */
    private $customVarsJoinTemplate = '%1$s = %2$s.object_id AND %2$s.varname = %3$s';

    /**
     * An array with all 'virtual' tables that are already joined
     *
     * Virtual tables are the keys  of the columnMap array and require a
     * join%VirtualTableName%() method to be defined in the concrete
     * query
     *
     * @var array
     */
    protected $joinedVirtualTables = array();

    /**
     * A map of virtual table names and corresponding hook instances
     *
     * Joins for those tables will be delegated to them
     *
     * @var array
     */
    protected $hookedVirtualTables = array();

    /**
     * List of column aliases used for sorting the result
     *
     * @var array
     */
    protected $orderColumns = array();

    /**
     * Table to columns map which have to be added to the GROUP BY list if the query is grouped
     *
     * @var array
     */
    protected $groupBase = array();

    /**
     * List of table names which initiate grouping if one of them is joined
     *
     * @var array
     */
    protected $groupOrigin = array();

    /**
     * Map of table names to query names for which to create subquery filters
     *
     * @var array
     */
    protected $subQueryTargets = array();

    /**
     * The primary key column for the instances table
     *
     * @var string
     */
    protected $instance_id = 'instance_id';

    /**
     * The primary key column for the objects table
     *
     * @var string
     */
    protected $object_id       = 'object_id';

    /**
     * The primary key column for the acknowledgements table
     *
     * @var string
     */
    protected $acknowledgement_id = 'acknowledgement_id';

    /**
     * The primary key column for the commenthistory table
     *
     * @var string
     */
    protected $commenthistory_id = 'commenthistory_id';

    /**
     * The primary key column for the contactnotifications table
     *
     * @var string
     */
    protected $contactnotification_id = 'contactnotification_id';

    /**
     * The primary key column for the downtimehistory table
     *
     * @var string
     */
    protected $downtimehistory_id = 'downtimehistory_id';

    /**
     * The primary key column for the flappinghistory table
     *
     * @var string
     */
    protected $flappinghistory_id = 'flappinghistory_id';

    /**
     * The primary key column for the notifications table
     *
     * @var string
     */
    protected $notification_id = 'notification_id';

    /**
     * The primary key column for the statehistory table
     *
     * @var string
     */
    protected $statehistory_id = 'statehistory_id';

    /**
     * The primary key column for the comments table
     *
     * @var string
     */
    protected $comment_id = 'comment_id';

    /**
     * The primary key column for the customvariablestatus table
     *
     * @var string
     */
    protected $customvariablestatus_id = 'customvariablestatus_id';

    /**
     * The primary key column for the hoststatus table
     *
     * @var string
     */
    protected $hoststatus_id = 'hoststatus_id';

    /**
     * The primary key column for the programstatus table
     *
     * @var string
     */
    protected $programstatus_id = 'programstatus_id';

    /**
     * The primary key column for the runtimevariables table
     *
     * @var string
     */
    protected $runtimevariable_id = 'runtimevariable_id';

    /**
     * The primary key column for the scheduleddowntime table
     *
     * @var string
     */
    protected $scheduleddowntime_id = 'scheduleddowntime_id';

    /**
     * The primary key column for the servicestatus table
     *
     * @var string
     */
    protected $servicestatus_id = 'servicestatus_id';

    /**
     * The primary key column for the contactstatus table
     *
     * @var string
     */
    protected $contactstatus_id = 'contactstatus_id';

    /**
     * The primary key column for the commands table
     *
     * @var string
     */
    protected $command_id = 'command_id';

    /**
     * The primary key column for the contactgroup_members table
     *
     * @var string
     */
    protected $contactgroup_member_id = 'contactgroup_member_id';

    /**
     * The primary key column for the contactgroups table
     *
     * @var string
     */
    protected $contactgroup_id = 'contactgroup_id';

    /**
     * The primary key column for the contacts table
     *
     * @var string
     */
    protected $contact_id = 'contact_id';

    /**
     * The primary key column for the customvariables table
     *
     * @var string
     */
    protected $customvariable_id = 'customvariable_id';

    /**
     * The primary key column for the host_contactgroups table
     *
     * @var string
     */
    protected $host_contactgroup_id = 'host_contactgroup_id';

    /**
     * The primary key column for the host_contacts table
     *
     * @var string
     */
    protected $host_contact_id = 'host_contact_id';

    /**
     * The primary key column for the hostgroup_members table
     *
     * @var string
     */
    protected $hostgroup_member_id = 'hostgroup_member_id';

    /**
     * The primary key column for the hostgroups table
     *
     * @var string
     */
    protected $hostgroup_id = 'hostgroup_id';

    /**
     * The primary key column for the hosts table
     *
     * @var string
     */
    protected $host_id = 'host_id';

    /**
     * The primary key column for the service_contactgroup table
     *
     * @var string
     */
    protected $service_contactgroup_id = 'service_contactgroup_id';

    /**
     * The primary key column for the service_contact table
     *
     * @var string
     */
    protected $service_contact_id = 'service_contact_id';

    /**
     * The primary key column for the servicegroup_members table
     *
     * @var string
     */
    protected $servicegroup_member_id = 'servicegroup_member_id';

    /**
     * The primary key column for the servicegroups table
     *
     * @var string
     */
    protected $servicegroup_id = 'servicegroup_id';

    /**
     * The primary key column for the services table
     *
     * @var string
     */
    protected $service_id = 'service_id';

    /**
     * The primary key column for the timeperiods table
     *
     * @var string
     */
    protected $timeperiod_id = 'timeperiod_id';

    /**
     * An array containing Column names that cause an aggregation of the query
     *
     * @var array
     */
    protected $aggregateColumnIdx = array();

    /**
     * True to allow customvar filters and queries
     *
     * @var bool
     */
    protected $allowCustomVars = false;

    /**
     * Current IDO version. This is bullshit and needs to be moved somewhere
     * else. As someone decided that we need no Backend-specific connection
     * class unfortunately there is no better place right now. And as of the
     * 'check_source' patch we need a quick fix immediately. So here you go.
     *
     * TODO: Fix this.
     *
     * @var string
     */
    protected static $idoVersion;

    /**
     * List of column aliases mapped to their table where the COLLATE SQL-instruction has been removed
     *
     * This list is being populated in case of a PostgreSQL backend only,
     * to ensure case-insensitive string comparison in WHERE clauses.
     *
     * @var array
     */
    protected $caseInsensitiveColumns;

    /**
     * Return true when the column is an aggregate column
     *
     * @param  String $column       The column to test
     * @return bool                 True when the column is an aggregate column
     */
    public function isAggregateColumn($column)
    {
        return array_key_exists($column, $this->aggregateColumnIdx);
    }

    /**
     * Order the result by the given alias
     *
     * @param   string  $alias  The column alias to order by
     * @param   int     $dir    The sort direction or null to use the default direction
     *
     * @return  $this
     */
    public function order($alias, $dir = null)
    {
        $this->requireColumn($alias);

        if ($this->isCustomvar($alias)) {
            $column = $this->getCustomvarColumnName($alias);
        } elseif ($this->hasAliasName($alias)) {
            $column = $this->aliasToColumnName($alias);
            $table = $this->aliasToTableName($alias);
            if (isset($this->caseInsensitiveColumns[$table][$alias])) {
                $column = 'LOWER(' . $column . ')';
            }
        } else {
            Logger::info('Can\'t order by column ' . $alias);
            return $this;
        }

        $this->orderColumns[] = $alias;
        return parent::order($column, $dir);
    }

    /**
     * Return true when the given field can be used for filtering
     *
     * @param String $field     The field to test
     * @return bool             True when the field can be used for querying, otherwise false
     */
    public function isValidFilterTarget($field)
    {
        return $this->getMappedField($field) !== null;
    }

    /**
     * Return the resolved field for an alias
     *
     * @param  String $field     The alias to resolve
     * @return String           The resolved alias or null if unknown
     */
    public function getMappedField($field)
    {
        foreach ($this->columnMap as $columnSource => $columnSet) {
            if (isset($columnSet[$field])) {
                return $columnSet[$field];
            }
        }
        if ($this->isCustomVar($field)) {
            return $this->getCustomvarColumnName($field);
        }
        return null;
    }

    public function distinct()
    {
        $this->select->distinct();
        return $this;
    }

    /**
     * Prepare the given query so that it can be linked to the parent
     *
     * @param   IdoQuery            $query
     * @param   string              $name
     * @param   FilterExpression    $filter             The filter which initiated the sub query
     * @param   bool                $and                Whether it's an AND filter
     * @param   bool                $negate             Whether it's an != filter
     * @param   FilterExpression    $additionalFilter   Filters which should be applied to the "parent" query
     *
     * @return  array   The first value is their, the second our key column
     *
     * @throws  NotImplementedError In case the given query is unknown
     */
    protected function joinSubQuery(IdoQuery $query, $name, $filter, $and, $negate, &$additionalFilter)
    {
        throw new NotImplementedError('Query "%s" is unknown', $name);
    }

    /**
     * Create and return a sub-query filter for the given filter expression
     *
     * @param   FilterExpression    $filter
     * @param   string              $queryName
     *
     * @return  Filter
     *
     * @throws  QueryException
     */
    protected function createSubQueryFilter(FilterExpression $filter, $queryName)
    {
        $expr = $filter->getExpression();
        $op = $filter->getSign();

        if ($op === '=' && ! is_array($expr) && $op !== '!=') {
            // We're joining a subquery only if the filter is enclosed in parentheses or if it's a != filter,
            // e.g. hostgroup_name=(linux...), hostgroup_name!=linux, hostgroup_name!=(linux...)
            throw new NotImplementedError('');
        }

        $subQuery = $this->createSubQuery($queryName);
        $subQuery->setIsSubQuery();

        $subQueryFilter = clone $filter;

        if ($op === '!=') {
            $negate = true;
            if (! is_array($expr)) {
                // We assume that expression is an array later on but we'll support subquery joins for != filters
                // which are not enclosed in parentheses
                $expr = [$expr];
            }
        } else {
            $negate = false;
        }

        if (count($expr) === 1 && strpos($expr[0], '&') !== false) {
            // Our current filter implementation does not specify & as a control character so the count of the
            // expression array is always one in this case
            $expr = array_unique(explode('&', $expr[0]));
            $subQueryFilter->setExpression($expr);
            $and = true;
        } else {
            // Or filters are respected by our filter implementation. No special handling needed here
            $and = false;
        }

        $alias = $filter->getColumn();
        $column = $subQuery->aliasToColumnName($alias);
        if (isset($this->caseInsensitiveColumns[$subQuery->aliasToTableName($alias)][$alias])) {
            $column = 'LOWER( ' . $column . ' )';
            $subQueryFilter->setColumn($column);
            $subQueryFilter->setExpression(array_map('strtolower', (array) $subQueryFilter->getExpression()));
        } else {
            $subQueryFilter->setColumn($column);
        }

        $additional = null;

        list($theirs, $ours) = $this->joinSubQuery($subQuery, $queryName, $subQueryFilter, $and, $negate, $additional);

        $zendSelect = $subQuery->select();
        $fromPart = $zendSelect->getPart($zendSelect::FROM);
        $zendSelect->reset($zendSelect::FROM);

        foreach ($fromPart as $correlationName => $joinOptions) {
            if (isset($joinOptions['joinCondition'])) {
                $joinOptions['joinCondition'] = preg_replace(
                    '/(?<=^|\s)\w+(?=\.)/',
                    'sub_$0',
                    $joinOptions['joinCondition']
                );
            }

            $name = ['sub_' . $correlationName => $joinOptions['tableName']];
            switch ($joinOptions['joinType']) {
                case $zendSelect::FROM:
                    $zendSelect->from($name);
                    break;
                case $zendSelect::INNER_JOIN:
                    $zendSelect->joinInner($name, $joinOptions['joinCondition'], null);
                    break;
                case $zendSelect::LEFT_JOIN:
                    $zendSelect->joinLeft($name, $joinOptions['joinCondition'], null);
                    break;
                default:
                    // TODO: Add support for other join types if required?
                    throw new QueryException(
                        'Unsupported join type %s. Cannot create subquery filter.',
                        $joinOptions['joinType']
                    );
            }
        }

        if ($and || $negate) {
            // Having is only required for AND and != filters,
            // e.g. hostgroup_name=(ping&linux), hostgroup_name!=ping, hostgroup_name!=(ping|linux)
            $groups = $subQuery->getGroup();
            if (! empty($groups)) {
                $group = $groups[0];
                $group = preg_replace('/(?<=^|\s)\w+(?=\.)/', 'sub_$0', $group);

                $cnt = count($expr);

                $subQuery->select()->having("COUNT(DISTINCT $group) >= $cnt");
            }
        }

        $subQueryFilter->setColumn(preg_replace(
            '/(?<=^|\s)\w+(?=\.)/',
            'sub_$0',
            $column
        ));

        if ($negate) {
            // != will be NOT EXISTS later
            $subQueryFilter = $subQueryFilter->setSign('=');
        }

        $subQueryFilter = $subQueryFilter->andFilter(Filter::where(
            preg_replace('/(?<=^|\s)\w+(?=\.)/', 'sub_$0', $theirs),
            new Zend_Db_Expr($ours)
        ));

        $subQuery
            ->setFilter($subQueryFilter)
            ->clearGroupingRules()
            ->select()
            ->reset('columns')
            ->columns([new Zend_Db_Expr('1')]);

        // EXISTS is the column name because without any column $this->isCustomVar() fails badly otherwise.
        // Additionally it bypasses the non-required optimizations made by our filter rendering implementation.
        $exists = new FilterExpression($negate ? 'NOT EXISTS' : 'EXISTS', '', new Zend_Db_Expr($subQuery));

        if ($additional !== null) {
            return Filter::matchAll($exists, $additional);
        }

        return $exists;
    }

    protected function requireFilterColumns(Filter $filter)
    {
        if ($filter instanceof FilterExpression) {
            $alias = $filter->getColumn();

            $virtualTable = $this->aliasToTableName($alias);
            if (isset($this->subQueryTargets[$virtualTable])) {
                try {
                    return $this->createSubQueryFilter($filter, $this->subQueryTargets[$virtualTable]);
                } catch (NotImplementedError $e) {
                    // We don't want to create subquery filters in all cases
                }
            }

            $this->requireColumn($alias);

            if ($this->isCustomvar($alias)) {
                $column = $this->getCustomvarColumnName($alias);
            } else {
                $column = $this->aliasToColumnName($alias);
                if (isset($this->caseInsensitiveColumns[$this->aliasToTableName($alias)][$alias])) {
                    $column = 'LOWER(' . $column . ')';
                    $expression = $filter->getExpression();
                    if (is_array($expression)) {
                        $filter->setExpression(array_map('strtolower', $expression));
                    } else {
                        $filter->setExpression(strtolower($expression));
                    }
                }
            }

            $filter->setColumn($column);
        } else {
            if (! $filter instanceof FilterNot) {
                // Allow subquery filters in a filter chain
                $columns = $filter->listFilteredColumns();
                if (count($columns) === 1) {
                    $column = $columns[0];
                    $virtualTable = $this->aliasToTableName($column);
                    if (isset($this->subQueryTargets[$virtualTable])) {
                        $lastSign = null;
                        $filters = [];
                        $expressions = [];
                        foreach ($filter->filters() as $child) {
                            switch (true) {
                                case $child instanceof FilterExpression:
                                    $expression = $child->getExpression();
                                    if (! is_array($expression)) {
                                        break;
                                    }
                                    // Move to default
                                default:
                                    $filters[] = $child;
                                    continue 2;
                            }
                            if ($lastSign === null) {
                                $lastSign = $child->getSign();
                            } else {
                                $sign = $child->getSign();
                                if ($sign !== $lastSign) {
                                    $filters[] = new FilterExpression(
                                        $column,
                                        $lastSign,
                                        $filter->getOperatorSymbol() === '&'
                                            ? [implode('&', $expressions)]
                                            : $expressions
                                    );
                                    $expressions = [];
                                    $lastSign = $sign;
                                }
                            }
                            $expressions[] = $expression;
                        }
                        if (! empty($expressions)) {
                            $filters[] = new FilterExpression(
                                $column,
                                $lastSign,
                                $filter->getOperatorSymbol() === '&'
                                    ? [implode('&', $expressions)]
                                    : $expressions
                            );
                        }
                        $filter->setFilters($filters);
                    }
                }
            }

            foreach ($filter->filters() as $child) {
                $replacement = $this->requireFilterColumns($child);
                if ($replacement !== null) {
                    // setId($child->getId()) is performed because replaceById() doesn't already do it
                    $filter->replaceById($child->getId(), $replacement->setId($child->getId()));
                }
            }
        }
    }

    /**
     * {@inheritdoc}
     */
    public function addFilter(Filter $filter)
    {
        $filter = clone $filter;
        return parent::addFilter($this->requireFilterColumns($filter) ?: $filter);
    }

    public function where($condition, $value = null)
    {
        $this->requireColumn($condition);
        $col = $this->getMappedField($condition);
        if ($col === null) {
            throw new IcingaException(
                'No such field: %s',
                $condition
            );
        }
        return parent::where($col, $value);
    }

    /**
     * Add a filter expression, with as less validation as possible
     *
     * @param FilterExpression $ex
     *
     * @internal If you use this outside the monitoring module, it's your fault if something breaks
     * @return $this
     */
    public function whereEx(FilterExpression $ex)
    {
        $this->requireColumn($ex->getColumn());
        $col = $this->getMappedField($ex->getColumn());
        if ($col === null) {
            throw new IcingaException(
                'No such field: %s',
                $ex->getColumn()
            );
        }

        parent::addFilter((clone $ex)->setColumn($col));

        return $this;
    }

    /**
     * Return true if an field contains an explicit timestamp
     *
     * @param   string  $field      The field to test for containing an timestamp
     *
     * @return  bool                True when the field represents an timestamp
     */
    public function isTimestamp($field)
    {
        if ($this->isCustomVar($field)) {
            return false;
        }

        return stripos($this->getMappedField($field) ?: $field, 'UNIX_TIMESTAMP') !== false;
    }

    /**
     * Return whether the given alias provides case insensitive value comparison
     *
     * @param   string  $alias
     *
     * @return  bool
     */
    public function isCaseInsensitive($alias)
    {
        if ($this->isCustomVar($alias)) {
            return false;
        }

        $column = $this->getMappedField($alias);
        if (! $column) {
            return false;
        }

        if (empty($this->caseInsensitiveColumns)) {
            return preg_match('/ COLLATE .+$/', $column) === 1;
        }

        if (strpos($column, 'LOWER') === 0) {
            return true;
        }

        $table = $this->aliasToTableName($alias);
        if (! $table) {
            return false;
        }

        return isset($this->caseInsensitiveColumns[$table][$alias]);
    }

    /**
     * Return our column map
     *
     * Might be useful for hooks
     *
     * @return array
     */
    public function getColumnMap()
    {
        return $this->columnMap;
    }

    /**
     * Apply oracle specific query initialization
     */
    private function initializeForOracle()
    {
        // Oracle uses the reserved field 'id' for primary keys, so
        // these must be used instead of the normally defined ids
        $this->object_id = $this->host_id = $this->service_id
            = $this->hostgroup_id = $this->servicegroup_id
            = $this->contact_id = $this->contactgroup_id = 'id';
        $this->customVarsJoinTemplate =
            '%1$s = %2$s.object_id AND LOWER(%2$s.varname) = %3$s';
        foreach ($this->columnMap as &$columns) {
            foreach ($columns as &$value) {
                $value = preg_replace('/UNIX_TIMESTAMP/', 'localts2unixts', $value);
                $value = preg_replace('/ COLLATE .+$/', '', $value);
            }
        }
    }

    /**
     * Apply PostgreSQL specific query initialization
     */
    private function initializeForPostgres()
    {
        $this->customVarsJoinTemplate =
            '%1$s = %2$s.object_id AND LOWER(%2$s.varname) = %3$s';
        foreach ($this->columnMap as $table => & $columns) {
            foreach ($columns as $alias => & $column) {
                if ($column === null) {
                    continue;
                }

                // Using a regex here because COLLATE may occur anywhere in the string
                $column = preg_replace('/ COLLATE .+$/', '', $column, -1, $count);
                if ($count > 0) {
                    $this->caseInsensitiveColumns[$table][$alias] = true;
                }

                $column = preg_replace(
                    '/inet_aton\(([[:word:].]+)\)/i',
                    '(CASE WHEN $1 ~ \'(?:[0-9]{1,3}\\\\.){3}[0-9]{1,3}\' THEN $1::inet - \'0.0.0.0\' ELSE NULL END)',
                    $column
                );
                if (version_compare($this->getIdoVersion(), '1.14.2', '>=')) {
                    $column = str_replace('NOW()', 'NOW() AT TIME ZONE \'UTC\'', $column);
                } else {
                    $column = preg_replace(
                        '/UNIX_TIMESTAMP(\((?>[^()]|(?-1))*\))/i',
                        'CASE WHEN ($1 < \'1970-01-03 00:00:00+00\'::timestamp with time zone) THEN 0 ELSE UNIX_TIMESTAMP($1) END',
                        $column
                    );
                }
            }
        }
    }

    /**
     * Set up this query and join the initial tables
     *
     * @see IdoQuery::initializeForPostgres     For postgresql specific setup
     */
    protected function init()
    {
        parent::init();
        $this->prefix = $this->ds->getTablePrefix();

        foreach (Hook::all('monitoring/idoQueryExtension') as $hook) {
            $extensions = $hook->extendColumnMap($this);
            if (! is_array($extensions)) {
                continue;
            }

            foreach ($extensions as $vTable => $cols) {
                if (! array_key_exists($vTable, $this->columnMap)) {
                    $this->hookedVirtualTables[$vTable] = $hook;
                    $this->columMap[$vTable] = array();
                }

                foreach ($cols as $k => $v) {
                    $this->columnMap[$vTable][$k] = $v;
                }
            }
        }

        $dbType = $this->ds->getDbType();
        if ($dbType === 'oracle') {
            $this->initializeForOracle();
        } elseif ($dbType === 'pgsql') {
            $this->initializeForPostgres();
        } else {
            $charset = $this->ds->getConfig()->get('charset') ?: 'latin1';
            $this->customVarsJoinTemplate .= " COLLATE {$charset}_general_ci";
        }
        $this->joinBaseTables();
        $this->select->columns($this->columns);
        $this->prepareAliasIndexes();
    }

    /**
     * Join the base tables for this query
     */
    protected function joinBaseTables()
    {
        reset($this->columnMap);
        $table = key($this->columnMap);

        $this->select->from(
            array($table => $this->prefix . $table),
            array()
        );

        $this->joinedVirtualTables = array($table => true);
    }

    /**
     * Populates the idxAliasTAble and idxAliasColumn properties
     */
    protected function prepareAliasIndexes()
    {
        foreach ($this->columnMap as $tbl => & $cols) {
            foreach ($cols as $alias => $col) {
                $this->idxAliasTable[$alias] = $tbl;
                $this->idxAliasColumn[$alias] = preg_replace('~\n\s*~', ' ', $col);
            }
        }
    }

    /**
     * Resolve columns aliases to their database field using the columnMap
     *
     * @param   array $columns
     *
     * @return  array
     */
    public function resolveColumns($columns)
    {
        $resolvedColumns = array();

        foreach ($columns as $alias => $col) {
            if ($col instanceof Zend_Db_Expr) {
                // Support selecting NULL as column for example
                $resolvedColumns[$alias] = $col;
                continue;
            }
            $this->requireColumn($col);
            if ($this->isCustomvar($col)) {
                $name = $this->getCustomvarColumnName($col);
            } else {
                $name = $this->aliasToColumnName($col);
            }
            if (is_int($alias)) {
                $alias = $col;
            } else {
                $this->idxCustomAliases[$alias] = $col;
            }

            $resolvedColumns[$alias] = preg_replace('|\n|', ' ', $name);
        }

        return $resolvedColumns;
    }

    /**
     * Return all columns that will be selected when no columns are given in the constructor or from
     *
     * @return array        An array of column aliases
     */
    public function getDefaultColumns()
    {
        reset($this->columnMap);
        $table = key($this->columnMap);
        return array_keys($this->columnMap[$table]);
    }

    /**
     * Modify the query to the given alias can be used in the result set or queries
     *
     * This calls requireVirtualTable if needed
     *
     * @param string $alias                          The alias of the column to require
     *
     * @return $this                                 Fluent interface
     * @see    IdoQuery::requireVirtualTable        The method initializing required joins
     * @throws \Icinga\Exception\ProgrammingError   When an unknown column is requested
     */
    public function requireColumn($alias)
    {
        if ($this->hasAliasName($alias)) {
            $this->requireVirtualTable($this->aliasToTableName($alias));
        } elseif ($this->isCustomVar($alias)) {
            $this->requireCustomvar($alias);
        } else {
            throw new ProgrammingError(
                '%s : Got invalid column: %s',
                get_called_class(),
                $alias
            );
        }
        return $this;
    }

    /**
     * Return true if the given alias exists
     *
     * @param  String $alias    The alias to test for
     * @return bool             True when the alias exists, otherwise false
     */
    protected function hasAliasName($alias)
    {
        return array_key_exists($alias, $this->idxAliasColumn);
    }

    /**
     * Require a virtual table for the given table name if not already required
     *
     * @param  String $name         The table name to require
     * @return $this                 Fluent interface
     */
    protected function requireVirtualTable($name)
    {
        if ($this->hasJoinedVirtualTable($name)) {
            return $this;
        }

        if ($this->virtualTableIsHooked($name)) {
            return $this->joinHookedVirtualTable($name);
        } else {
            return $this->joinVirtualTable($name);
        }
    }

    /**
     * Whether a given virtual table name has been provided by a hook
     *
     * @param string $name Virtual table name
     *
     * @return boolean
     */
    protected function virtualTableIsHooked($name)
    {
        return array_key_exists($name, $this->hookedVirtualTables);
    }

    protected function conflictsWithVirtualTable($name)
    {
        if ($this->hasJoinedVirtualTable($name)) {
            throw new ProgrammingError(
                'IDO query virtual table conflict with "%s"',
                $name
            );
        }
        return $this;
    }

    /**
     * Call the method for joining a virtual table
     *
     * This requires a join$Table() method to exist
     *
     * @param  String $table        The table to join by calling join$Table() in the concrete implementation
     * @return $this                 Fluent interface
     *
     * @throws \Icinga\Exception\ProgrammingError   If the join method for this table does not exist
     */
    protected function joinVirtualTable($table)
    {
        $func = 'join' . ucfirst($table);
        if (method_exists($this, $func)) {
            $this->$func();
        } else {
            throw new ProgrammingError(
                'Cannot join "%s", no such table found',
                $table
            );
        }
        $this->joinedVirtualTables[$table] = true;
        return $this;
    }

    /**
     * Tell a hook to join a virtual table
     *
     * @param  String $table
     * @return $this
     */
    protected function joinHookedVirtualTable($table)
    {
        $this->hookedVirtualTables[$table]->joinVirtualTable($this, $table);
        $this->joinedVirtualTables[$table] = true;
        return $this;
    }

    /**
     * Get the table for a specific alias
     *
     * @param   String $alias   The alias to request the table for
     * @return  String          The table for the alias or null if it doesn't exist
     */
    protected function aliasToTableName($alias)
    {
        return isset($this->idxAliasTable[$alias]) ? $this->idxAliasTable[$alias] : null;
    }

    /**
     * Return whether this query allows to join custom variables
     *
     * @return  bool
     */
    public function allowsCustomVars()
    {
        return $this->allowCustomVars;
    }

    /**
     * Return true if the given alias denotes a custom variable
     *
     * @param  String $alias    The alias to test for being a customvariable
     * @return bool             True if the alias is a customvariable, otherwise false
     */
    protected function isCustomVar($alias)
    {
        return $this->allowCustomVars && $alias[0] === '_';
    }

    protected function requireCustomvar($customvar)
    {
        if (! $this->hasCustomvar($customvar)) {
            $this->joinCustomvar($customvar);
        }
        return $this;
    }

    protected function hasCustomvar($customvar)
    {
        return array_key_exists(strtolower($customvar), $this->customVars);
    }

    protected function joinCustomvar($customvar)
    {
        // TODO: This is not generic enough yet
        list($type, $name) = $this->customvarNameToTypeName($customvar);
        $alias = ($type === 'host' ? 'hcv_' : 'scv_') . preg_replace('~[^a-zA-Z0-9_]~', '_', $name);

        // We're replacing any problematic char with an underscore, which will lead to duplicates, this avoids them
        $from = $this->select->getPart(Zend_Db_Select::FROM);
        for ($i = 2; array_key_exists($alias, $from); $i++) {
            $alias = $alias . '_' . $i;
        }

        $this->customVars[strtolower($customvar)] = $alias;

        if ($type === 'host') {
            if ($this instanceof ServicecommentQuery
                || $this instanceof ServicedowntimeQuery
                || $this instanceof ServicecommenthistoryQuery
                || $this instanceof ServicedowntimestarthistoryQuery
                || $this instanceof ServiceflappingstarthistoryQuery
                || $this instanceof ServicegroupQuery
                || $this instanceof ServicenotificationQuery
                || $this instanceof ServicestatehistoryQuery
                || $this instanceof ServicestatusQuery
            ) {
                $this->requireVirtualTable('services');
                $leftcol = 's.host_object_id';
            } else {
                $leftcol = 'ho.object_id';
                if (! $this->hasJoinedTable('ho')) {
                    $this->requireVirtualTable('hosts');
                }
            }
        } else { // $type === 'service'
            $leftcol = 'so.object_id';
            if (! $this->hasJoinedTable('so')) {
                $this->requireVirtualTable('services');
            }
        }

        $mapped = $this->getMappedField($leftcol);
        if ($mapped !== null) {
            $this->requireColumn($leftcol);
            $leftcol = $mapped;
        }

        $joinOn = sprintf(
            $this->customVarsJoinTemplate,
            $leftcol,
            $alias,
            $this->db->quote($name)
        );

        $this->select->joinLeft(
            array($alias => $this->prefix . 'customvariablestatus'),
            $joinOn,
            array()
        );

        return $this;
    }

    protected function customvarNameToTypeName($customvar)
    {
        $customvar = strtolower($customvar);
        if (! preg_match('~^_(host|service)_(.+)$~', $customvar, $m)) {
            throw new ProgrammingError(
                'Got invalid custom var: "%s"',
                $customvar
            );
        }
        return array($m[1], $m[2]);
    }

    protected function hasJoinedVirtualTable($name)
    {
        return array_key_exists($name, $this->joinedVirtualTables);
    }

    /**
     * Get the query column of a already joined custom variable
     *
     * @param   string $customvar
     *
     * @return  string
     * @throws  QueryException If the custom variable has not been joined
     */
    protected function getCustomvarColumnName($customvar)
    {
        if (! isset($this->customVars[($customvar = strtolower($customvar))])) {
            throw new QueryException('Custom variable %s has not been joined', $customvar);
        }
        return $this->customVars[$customvar] . '.varvalue';
    }

    public function aliasToColumnName($alias)
    {
        return $this->idxAliasColumn[$alias];
    }

    /**
     * Get the alias of a column expression as defined in the {@link $columnMap} property.
     *
     * @param   string $alias Potential custom alias
     *
     * @return  string
     */
    public function customAliasToAlias($alias)
    {
        if (isset($this->idxCustomAliases[$alias])) {
            return $this->idxCustomAliases[$alias];
        }
        return $alias;
    }

    /**
     * Create a sub query
     *
     * @param   string  $queryName
     * @param   array   $columns
     *
     * @return  static
     */
    protected function createSubQuery($queryName, $columns = array())
    {
        $class = '\\'
            . substr(__CLASS__, 0, strrpos(__CLASS__, '\\') + 1)
            . ucfirst($queryName) . 'Query';
        $query = new $class($this->ds, $columns);
        return $query;
    }

    /**
     * Set columns to select
     *
     * @param   array $columns
     *
     * @return  $this
     */
    public function columns(array $columns)
    {
        $this->idxCustomAliases = array();
        $this->columns = $this->resolveColumns($columns);
        // TODO: we need to refresh our select!
        // $this->select->columns($columns);
        return $this;
    }

    public function clearGroupingRules()
    {
        $this->groupBase = array();
        $this->groupOrigin = array();
        return $this;
    }

    /**
     * Register the GROUP BY columns required for the given alias
     *
     * @param   string  $alias              The alias to register columns for
     * @param   string  $table              The table the given alias is associated with
     * @param   array   $groupedColumns     The grouping columns registered so far
     * @param   array   $groupedTables      The tables for which columns were registered so far
     */
    protected function registerGroupColumns($alias, $table, array &$groupedColumns, array &$groupedTables)
    {
        switch ($table) {
            case 'checktimeperiods':
                $groupedColumns[] = 'ctp.timeperiod_id';
                break;
            case 'contacts':
                $groupedColumns[] = 'co.object_id';
                $groupedColumns[] = 'c.contact_id';
                break;
            case 'hostobjects':
                $groupedColumns[] = 'ho.object_id';
                break;
            case 'hosts':
                $groupedColumns[] = 'h.host_id';
                break;
            case 'hostgroups':
                $groupedColumns[] = 'hgo.object_id';
                $groupedColumns[] = 'hg.hostgroup_id';
                break;
            case 'hoststatus':
                $groupedColumns[] = 'hs.hoststatus_id';
                break;
            case 'instances':
                $groupedColumns[] = 'i.instance_id';
                break;
            case 'servicegroups':
                $groupedColumns[] = 'sgo.object_id';
                $groupedColumns[] = 'sg.servicegroup_id';
                break;
            case 'serviceobjects':
                $groupedColumns[] = 'so.object_id';
                break;
            case 'serviceproblemsummary':
                $groupedColumns[] = 'sps.unhandled_services_count';
                break;
            case 'services':
                $groupedColumns[] = 'so.object_id';
                $groupedColumns[] = 's.service_id';
                break;
            case 'servicestatus':
                $groupedColumns[] = 'ss.servicestatus_id';
                break;
            default:
                return;
        }

        $groupedTables[$table] = true;
    }

    /**
     * {@inheritdoc}
     */
    public function getGroup()
    {
        $group = parent::getGroup() ?: array();
        if (! is_array($group)) {
            $group = array($group);
        }

        $joinedOrigins = array_filter($this->groupOrigin, array($this, 'hasJoinedVirtualTable'));
        if (empty($joinedOrigins)) {
            return $group;
        }

        $groupedTables = array();
        foreach ($this->groupBase as $baseTable => $aliasedPks) {
            if (! $this->hasJoinedVirtualTable($baseTable)) {
                continue;
            }
            $groupedTables[$baseTable] = true;
            foreach ($aliasedPks as $aliasedPk) {
                $group[] = $aliasedPk;
            }
        }

        foreach (new ColumnFilterIterator($this->columns) as $desiredAlias => $desiredColumn) {
            $alias = is_string($desiredAlias) ? $this->customAliasToAlias($desiredAlias) : $desiredColumn;
            if ($this->isCustomVar($alias) && $this->getDatasource()->getDbType() === 'pgsql') {
                $table = $this->customVars[$alias];
                if (! isset($groupedTables[$table])) {
                    $group[] = $this->getCustomvarColumnName($alias);
                    $groupedTables[$table] = true;
                }
                continue;
            }
            $table = $this->aliasToTableName($alias);
            if ($table && !isset($groupedTables[$table]) && (
                in_array($table, $joinedOrigins, true) || $this->getDatasource()->getDbType() === 'pgsql')
            ) {
                $this->registerGroupColumns($alias, $table, $group, $groupedTables);
            }
        }

        if (! empty($group) && $this->getDatasource()->getDbType() === 'pgsql') {
            foreach (new ColumnFilterIterator($this->orderColumns) as $alias) {
                if ($this->isCustomVar($alias)) {
                    $table = $this->customVars[$alias];
                    if (! isset($groupedTables[$table])) {
                        $group[] = $this->getCustomvarColumnName($alias);
                        $groupedTables[$table] = true;
                    }
                    continue;
                }
                $table = $this->aliasToTableName($alias);
                if ($table && !isset($groupedTables[$table])
                    && !in_array($this->getMappedField($alias), $this->columns, true)
                ) {
                    $this->registerGroupColumns($alias, $table, $group, $groupedTables);
                }
            }
        }

        return array_unique($group);
    }

    // TODO: Move this away, see note related to $idoVersion var
    protected function getIdoVersion()
    {
        if (self::$idoVersion === null) {
            $dbconf = $this->db->getConfig();
            $id = $dbconf['host'] . '/' . $dbconf['dbname'];
            $session = null;
            if (Icinga::app()->isWeb()) {
                // TODO: Once we have version per connection we should choose a
                //       namespace based on resource name
                $session = Session::getSession()->getNamespace('monitoring/ido/' . $id);
                if (isset($session->version)) {
                    self::$idoVersion = $session->version;
                    return self::$idoVersion;
                }
            }
            self::$idoVersion = $this->db->fetchOne(
                $this->db->select()->from($this->prefix . 'dbversion', 'version')
            );
            if ($session !== null) {
                $session->version = self::$idoVersion;
            }
        }
        return self::$idoVersion;
    }

    /**
     * Return the name of the primary key column for the given table name
     *
     * @param   string  $table
     *
     * @return  string
     *
     * @throws ProgrammingError     In case $table is unknown
     */
    protected function getPrimaryKeyColumn($table)
    {
        // TODO: For god's sake, make this being a mapping
        //       (instead of matching a ton of properties using a ridiculous long switch case)
        switch ($table) {
            case 'instances':
                return $this->instance_id;
            case 'objects':
                return $this->object_id;
            case 'acknowledgements':
                return $this->acknowledgement_id;
            case 'commenthistory':
                return $this->commenthistory_id;
            case 'contactnotifiations':
                return $this->contactnotification_id;
            case 'downtimehistory':
                return $this->downtimehistory_id;
            case 'flappinghistory':
                return $this->flappinghistory_id;
            case 'notifications':
                return $this->notification_id;
            case 'statehistory':
                return $this->statehistory_id;
            case 'comments':
                return $this->comment_id;
            case 'customvariablestatus':
                return $this->customvariablestatus_id;
            case 'hoststatus':
                return $this->hoststatus_id;
            case 'programstatus':
                return $this->programstatus_id;
            case 'runtimevariables':
                return $this->runtimevariable_id;
            case 'scheduleddowntime':
                return $this->scheduleddowntime_id;
            case 'servicestatus':
                return $this->servicestatus_id;
            case 'contactstatus':
                return $this->contactstatus_id;
            case 'commands':
                return $this->command_id;
            case 'contactgroup_members':
                return $this->contactgroup_member_id;
            case 'contactgroups':
                return $this->contactgroup_id;
            case 'contacts':
                return $this->contact_id;
            case 'customvariables':
                return $this->customvariable_id;
            case 'host_contactgroups':
                return $this->host_contactgroup_id;
            case 'host_contacts':
                return $this->host_contact_id;
            case 'hostgroup_members':
                return $this->hostgroup_member_id;
            case 'hostgroups':
                return $this->hostgroup_id;
            case 'hosts':
                return $this->host_id;
            case 'service_contactgroups':
                return $this->service_contactgroup_id;
            case 'service_contacts':
                return $this->service_contact_id;
            case 'servicegroup_members':
                return $this->servicegroup_member_id;
            case 'servicegroups':
                return $this->servicegroup_id;
            case 'services':
                return $this->service_id;
            case 'timeperiods':
                return $this->timeperiod_id;
            default:
                throw new ProgrammingError('Cannot provide a primary key column. Table "%s" is unknown', $table);
        }
    }
}