1<?php
2/* Icinga Web 2 | (c) 2013 Icinga Development Team | GPLv2+ */
3
4namespace Icinga\Module\Monitoring\Backend\Ido\Query;
5
6use Icinga\Data\Filter\FilterNot;
7use Zend_Db_Expr;
8use Icinga\Application\Icinga;
9use Icinga\Application\Hook;
10use Icinga\Application\Logger;
11use Icinga\Data\Db\DbQuery;
12use Icinga\Data\Filter\Filter;
13use Icinga\Data\Filter\FilterExpression;
14use Icinga\Exception\IcingaException;
15use Icinga\Exception\NotImplementedError;
16use Icinga\Exception\ProgrammingError;
17use Icinga\Exception\QueryException;
18use Icinga\Web\Session;
19use Icinga\Module\Monitoring\Data\ColumnFilterIterator;
20
21/**
22 * Base class for Ido Queries
23 *
24 * This is the base class for all Ido queries and should be extended for new queries
25 * The starting point for implementations is the columnMap attribute. This is an asscociative array in the
26 * following form:
27 *
28 * <pre>
29 * <code>
30 * array(
31 *      'virtualTable' => array(
32 *          'fieldalias1' => 'queryColumn1',
33 *          'fieldalias2' => 'queryColumn2',
34 *          ....
35 *      ),
36 *      'virtualTable2' => array(
37 *          'host'       =>  'host_name1'
38 *      )
39 * )
40 * </code>
41 * </pre>
42 *
43 * This allows you to select e.g. fieldalias1, which automatically calls the query code for joining 'virtualTable'. If
44 * you afterwards select 'host', 'virtualTable2' will be joined. The joining logic is up to you, in order to make the
45 * above example work you need to implement the joinVirtualTable() method which contain your
46 * custom (Zend_Db) logic for joining, filtering and querying the data you want.
47 *
48 */
49abstract class IdoQuery extends DbQuery
50{
51    /**
52     * The prefix to use
53     *
54     * @var string
55     */
56    protected $prefix;
57
58    /**
59     * An array to map aliases to column names
60     *
61     * @var array
62     */
63    protected $idxAliasColumn;
64
65    /**
66     * An array to map aliases to table names
67     *
68     * @var array
69     */
70    protected $idxAliasTable;
71
72    /**
73     * An array to map custom aliases to aliases
74     *
75     * @var array
76     */
77    protected $idxCustomAliases;
78
79    /**
80     * The column map containing all filterable columns
81     *
82     * This must be overwritten by child classes, in the format
83     * array(
84     *      'virtualTable' => array(
85     *          'fieldalias1' => 'queryColumn1',
86     *          'fieldalias2' => 'queryColumn2',
87     *          ....
88     *      )
89     * )
90     *
91     * @var array
92     */
93    protected $columnMap = array();
94
95    /**
96     * Custom vars available for this query
97     *
98     * @var array
99     */
100    protected $customVars = array();
101
102    /**
103     * Printf compatible string to joins custom vars
104     *
105     * - %1$s   Source field, contain the object_id
106     * - %2$s   Alias used for the relation
107     * - %3$s   Name of the CustomVariable
108     *
109     * @var string
110     */
111    private $customVarsJoinTemplate = '%1$s = %2$s.object_id AND %2$s.varname = %3$s';
112
113    /**
114     * An array with all 'virtual' tables that are already joined
115     *
116     * Virtual tables are the keys  of the columnMap array and require a
117     * join%VirtualTableName%() method to be defined in the concrete
118     * query
119     *
120     * @var array
121     */
122    protected $joinedVirtualTables = array();
123
124    /**
125     * A map of virtual table names and corresponding hook instances
126     *
127     * Joins for those tables will be delegated to them
128     *
129     * @var array
130     */
131    protected $hookedVirtualTables = array();
132
133    /**
134     * List of column aliases used for sorting the result
135     *
136     * @var array
137     */
138    protected $orderColumns = array();
139
140    /**
141     * Table to columns map which have to be added to the GROUP BY list if the query is grouped
142     *
143     * @var array
144     */
145    protected $groupBase = array();
146
147    /**
148     * List of table names which initiate grouping if one of them is joined
149     *
150     * @var array
151     */
152    protected $groupOrigin = array();
153
154    /**
155     * Map of table names to query names for which to create subquery filters
156     *
157     * @var array
158     */
159    protected $subQueryTargets = array();
160
161    /**
162     * The primary key column for the instances table
163     *
164     * @var string
165     */
166    protected $instance_id = 'instance_id';
167
168    /**
169     * The primary key column for the objects table
170     *
171     * @var string
172     */
173    protected $object_id       = 'object_id';
174
175    /**
176     * The primary key column for the acknowledgements table
177     *
178     * @var string
179     */
180    protected $acknowledgement_id = 'acknowledgement_id';
181
182    /**
183     * The primary key column for the commenthistory table
184     *
185     * @var string
186     */
187    protected $commenthistory_id = 'commenthistory_id';
188
189    /**
190     * The primary key column for the contactnotifications table
191     *
192     * @var string
193     */
194    protected $contactnotification_id = 'contactnotification_id';
195
196    /**
197     * The primary key column for the downtimehistory table
198     *
199     * @var string
200     */
201    protected $downtimehistory_id = 'downtimehistory_id';
202
203    /**
204     * The primary key column for the flappinghistory table
205     *
206     * @var string
207     */
208    protected $flappinghistory_id = 'flappinghistory_id';
209
210    /**
211     * The primary key column for the notifications table
212     *
213     * @var string
214     */
215    protected $notification_id = 'notification_id';
216
217    /**
218     * The primary key column for the statehistory table
219     *
220     * @var string
221     */
222    protected $statehistory_id = 'statehistory_id';
223
224    /**
225     * The primary key column for the comments table
226     *
227     * @var string
228     */
229    protected $comment_id = 'comment_id';
230
231    /**
232     * The primary key column for the customvariablestatus table
233     *
234     * @var string
235     */
236    protected $customvariablestatus_id = 'customvariablestatus_id';
237
238    /**
239     * The primary key column for the hoststatus table
240     *
241     * @var string
242     */
243    protected $hoststatus_id = 'hoststatus_id';
244
245    /**
246     * The primary key column for the programstatus table
247     *
248     * @var string
249     */
250    protected $programstatus_id = 'programstatus_id';
251
252    /**
253     * The primary key column for the runtimevariables table
254     *
255     * @var string
256     */
257    protected $runtimevariable_id = 'runtimevariable_id';
258
259    /**
260     * The primary key column for the scheduleddowntime table
261     *
262     * @var string
263     */
264    protected $scheduleddowntime_id = 'scheduleddowntime_id';
265
266    /**
267     * The primary key column for the servicestatus table
268     *
269     * @var string
270     */
271    protected $servicestatus_id = 'servicestatus_id';
272
273    /**
274     * The primary key column for the contactstatus table
275     *
276     * @var string
277     */
278    protected $contactstatus_id = 'contactstatus_id';
279
280    /**
281     * The primary key column for the commands table
282     *
283     * @var string
284     */
285    protected $command_id = 'command_id';
286
287    /**
288     * The primary key column for the contactgroup_members table
289     *
290     * @var string
291     */
292    protected $contactgroup_member_id = 'contactgroup_member_id';
293
294    /**
295     * The primary key column for the contactgroups table
296     *
297     * @var string
298     */
299    protected $contactgroup_id = 'contactgroup_id';
300
301    /**
302     * The primary key column for the contacts table
303     *
304     * @var string
305     */
306    protected $contact_id = 'contact_id';
307
308    /**
309     * The primary key column for the customvariables table
310     *
311     * @var string
312     */
313    protected $customvariable_id = 'customvariable_id';
314
315    /**
316     * The primary key column for the host_contactgroups table
317     *
318     * @var string
319     */
320    protected $host_contactgroup_id = 'host_contactgroup_id';
321
322    /**
323     * The primary key column for the host_contacts table
324     *
325     * @var string
326     */
327    protected $host_contact_id = 'host_contact_id';
328
329    /**
330     * The primary key column for the hostgroup_members table
331     *
332     * @var string
333     */
334    protected $hostgroup_member_id = 'hostgroup_member_id';
335
336    /**
337     * The primary key column for the hostgroups table
338     *
339     * @var string
340     */
341    protected $hostgroup_id = 'hostgroup_id';
342
343    /**
344     * The primary key column for the hosts table
345     *
346     * @var string
347     */
348    protected $host_id = 'host_id';
349
350    /**
351     * The primary key column for the service_contactgroup table
352     *
353     * @var string
354     */
355    protected $service_contactgroup_id = 'service_contactgroup_id';
356
357    /**
358     * The primary key column for the service_contact table
359     *
360     * @var string
361     */
362    protected $service_contact_id = 'service_contact_id';
363
364    /**
365     * The primary key column for the servicegroup_members table
366     *
367     * @var string
368     */
369    protected $servicegroup_member_id = 'servicegroup_member_id';
370
371    /**
372     * The primary key column for the servicegroups table
373     *
374     * @var string
375     */
376    protected $servicegroup_id = 'servicegroup_id';
377
378    /**
379     * The primary key column for the services table
380     *
381     * @var string
382     */
383    protected $service_id = 'service_id';
384
385    /**
386     * The primary key column for the timeperiods table
387     *
388     * @var string
389     */
390    protected $timeperiod_id = 'timeperiod_id';
391
392    /**
393     * An array containing Column names that cause an aggregation of the query
394     *
395     * @var array
396     */
397    protected $aggregateColumnIdx = array();
398
399    /**
400     * True to allow customvar filters and queries
401     *
402     * @var bool
403     */
404    protected $allowCustomVars = false;
405
406    /**
407     * Current IDO version. This is bullshit and needs to be moved somewhere
408     * else. As someone decided that we need no Backend-specific connection
409     * class unfortunately there is no better place right now. And as of the
410     * 'check_source' patch we need a quick fix immediately. So here you go.
411     *
412     * TODO: Fix this.
413     *
414     * @var string
415     */
416    protected static $idoVersion;
417
418    /**
419     * List of column aliases mapped to their table where the COLLATE SQL-instruction has been removed
420     *
421     * This list is being populated in case of a PostgreSQL backend only,
422     * to ensure case-insensitive string comparison in WHERE clauses.
423     *
424     * @var array
425     */
426    protected $caseInsensitiveColumns;
427
428    /**
429     * Return true when the column is an aggregate column
430     *
431     * @param  String $column       The column to test
432     * @return bool                 True when the column is an aggregate column
433     */
434    public function isAggregateColumn($column)
435    {
436        return array_key_exists($column, $this->aggregateColumnIdx);
437    }
438
439    /**
440     * Order the result by the given alias
441     *
442     * @param   string  $alias  The column alias to order by
443     * @param   int     $dir    The sort direction or null to use the default direction
444     *
445     * @return  $this
446     */
447    public function order($alias, $dir = null)
448    {
449        $this->requireColumn($alias);
450
451        if ($this->isCustomvar($alias)) {
452            $column = $this->getCustomvarColumnName($alias);
453        } elseif ($this->hasAliasName($alias)) {
454            $column = $this->aliasToColumnName($alias);
455            $table = $this->aliasToTableName($alias);
456            if (isset($this->caseInsensitiveColumns[$table][$alias])) {
457                $column = 'LOWER(' . $column . ')';
458            }
459        } else {
460            Logger::info('Can\'t order by column ' . $alias);
461            return $this;
462        }
463
464        $this->orderColumns[] = $alias;
465        return parent::order($column, $dir);
466    }
467
468    /**
469     * Return true when the given field can be used for filtering
470     *
471     * @param String $field     The field to test
472     * @return bool             True when the field can be used for querying, otherwise false
473     */
474    public function isValidFilterTarget($field)
475    {
476        return $this->getMappedField($field) !== null;
477    }
478
479    /**
480     * Return the resolved field for an alias
481     *
482     * @param  String $field     The alias to resolve
483     * @return String           The resolved alias or null if unknown
484     */
485    public function getMappedField($field)
486    {
487        foreach ($this->columnMap as $columnSource => $columnSet) {
488            if (isset($columnSet[$field])) {
489                return $columnSet[$field];
490            }
491        }
492        if ($this->isCustomVar($field)) {
493            return $this->getCustomvarColumnName($field);
494        }
495        return null;
496    }
497
498    public function distinct()
499    {
500        $this->select->distinct();
501        return $this;
502    }
503
504    /**
505     * Prepare the given query so that it can be linked to the parent
506     *
507     * @param   IdoQuery            $query
508     * @param   string              $name
509     * @param   FilterExpression    $filter             The filter which initiated the sub query
510     * @param   bool                $and                Whether it's an AND filter
511     * @param   bool                $negate             Whether it's an != filter
512     * @param   FilterExpression    $additionalFilter   Filters which should be applied to the "parent" query
513     *
514     * @return  array   The first value is their, the second our key column
515     *
516     * @throws  NotImplementedError In case the given query is unknown
517     */
518    protected function joinSubQuery(IdoQuery $query, $name, $filter, $and, $negate, &$additionalFilter)
519    {
520        throw new NotImplementedError('Query "%s" is unknown', $name);
521    }
522
523    /**
524     * Create and return a sub-query filter for the given filter expression
525     *
526     * @param   FilterExpression    $filter
527     * @param   string              $queryName
528     *
529     * @return  Filter
530     *
531     * @throws  QueryException
532     */
533    protected function createSubQueryFilter(FilterExpression $filter, $queryName)
534    {
535        $expr = $filter->getExpression();
536        $op = $filter->getSign();
537
538        if ($op === '=' && ! is_array($expr) && $op !== '!=') {
539            // We're joining a subquery only if the filter is enclosed in parentheses or if it's a != filter,
540            // e.g. hostgroup_name=(linux...), hostgroup_name!=linux, hostgroup_name!=(linux...)
541            throw new NotImplementedError('');
542        }
543
544        $subQuery = $this->createSubQuery($queryName);
545        $subQuery->setIsSubQuery();
546
547        $subQueryFilter = clone $filter;
548
549        if ($op === '!=') {
550            $negate = true;
551            if (! is_array($expr)) {
552                // We assume that expression is an array later on but we'll support subquery joins for != filters
553                // which are not enclosed in parentheses
554                $expr = [$expr];
555            }
556        } else {
557            $negate = false;
558        }
559
560        if (count($expr) === 1 && strpos($expr[0], '&') !== false) {
561            // Our current filter implementation does not specify & as a control character so the count of the
562            // expression array is always one in this case
563            $expr = array_unique(explode('&', $expr[0]));
564            $subQueryFilter->setExpression($expr);
565            $and = true;
566        } else {
567            // Or filters are respected by our filter implementation. No special handling needed here
568            $and = false;
569        }
570
571        $alias = $filter->getColumn();
572        $column = $subQuery->aliasToColumnName($alias);
573        if (isset($this->caseInsensitiveColumns[$subQuery->aliasToTableName($alias)][$alias])) {
574            $column = 'LOWER( ' . $column . ' )';
575            $subQueryFilter->setExpression(array_map('strtolower', (array) $subQueryFilter->getExpression()));
576        }
577
578        $additional = null;
579
580        list($theirs, $ours) = $this->joinSubQuery($subQuery, $queryName, $subQueryFilter, $and, $negate, $additional);
581
582        $zendSelect = $subQuery->select();
583        $fromPart = $zendSelect->getPart($zendSelect::FROM);
584        $zendSelect->reset($zendSelect::FROM);
585
586        foreach ($fromPart as $correlationName => $joinOptions) {
587            if (isset($joinOptions['joinCondition'])) {
588                $joinOptions['joinCondition'] = preg_replace(
589                    '/(?<=^|\s)\w+(?=\.)/',
590                    'sub_$0',
591                    $joinOptions['joinCondition']
592                );
593            }
594
595            $name = ['sub_' . $correlationName => $joinOptions['tableName']];
596            switch ($joinOptions['joinType']) {
597                case $zendSelect::FROM:
598                    $zendSelect->from($name);
599                    break;
600                case $zendSelect::INNER_JOIN:
601                    $zendSelect->joinInner($name, $joinOptions['joinCondition'], null);
602                    break;
603                case $zendSelect::LEFT_JOIN:
604                    $zendSelect->joinLeft($name, $joinOptions['joinCondition'], null);
605                    break;
606                default:
607                    // TODO: Add support for other join types if required?
608                    throw new QueryException(
609                        'Unsupported join type %s. Cannot create subquery filter.',
610                        $joinOptions['joinType']
611                    );
612            }
613        }
614
615        if ($and || $negate && ! $and) {
616            // Having is only required for AND and != filters,
617            // e.g. hostgroup_name=(ping&linux), hostgroup_name!=ping, hostgroup_name!=(ping|linux)
618            $groups = $subQuery->getGroup();
619            $group = $groups[0];
620            $group = preg_replace('/(?<=^|\s)\w+(?=\.)/', 'sub_$0', $group);
621
622            $cnt = count($expr);
623
624            $subQuery->select()->having("COUNT(DISTINCT $group) >= $cnt");
625        }
626
627        $subQueryFilter->setColumn(preg_replace(
628            '/(?<=^|\s)\w+(?=\.)/',
629            'sub_$0',
630            $column
631        ));
632
633        if ($negate) {
634            // != will be NOT EXISTS later
635            $subQueryFilter = $subQueryFilter->setSign('=');
636        }
637
638        $subQueryFilter = $subQueryFilter->andFilter(Filter::where(
639            preg_replace('/(?<=^|\s)\w+(?=\.)/', 'sub_$0', $theirs),
640            new Zend_Db_Expr($ours)
641        ));
642
643        $subQuery
644            ->setFilter($subQueryFilter)
645            ->clearGroupingRules()
646            ->select()
647            ->reset('columns')
648            ->columns([new Zend_Db_Expr('1')]);
649
650        // EXISTS is the column name because without any column $this->isCustomVar() fails badly otherwise.
651        // Additionally it bypasses the non-required optimizations made by our filter rendering implementation.
652        $exists = new FilterExpression($negate ? 'NOT EXISTS' : 'EXISTS', '', new Zend_Db_Expr($subQuery));
653
654        if ($additional !== null) {
655            $alias = $additional->getColumn();
656            $this->requireColumn($alias);
657            $additional->setColumn($this->aliasToColumnName($alias));
658
659            return Filter::matchAll($exists, $additional);
660        }
661
662        return $exists;
663    }
664
665    protected function requireFilterColumns(Filter $filter)
666    {
667        if ($filter instanceof FilterExpression) {
668            if ($filter->getExpression() === '*') {
669                return; // Wildcard only filters are ignored so stop early here to avoid joining a table for nothing
670            }
671
672            $alias = $filter->getColumn();
673
674            $virtualTable = $this->aliasToTableName($alias);
675            if (isset($this->subQueryTargets[$virtualTable])) {
676                try {
677                    return $this->createSubQueryFilter($filter, $this->subQueryTargets[$virtualTable]);
678                } catch (NotImplementedError $e) {
679                    // We don't want to create subquery filters in all cases
680                }
681            }
682
683            $this->requireColumn($alias);
684
685            if ($this->isCustomvar($alias)) {
686                $column = $this->getCustomvarColumnName($alias);
687            } else {
688                $column = $this->aliasToColumnName($alias);
689                if (isset($this->caseInsensitiveColumns[$this->aliasToTableName($alias)][$alias])) {
690                    $column = 'LOWER(' . $column . ')';
691                    $expression = $filter->getExpression();
692                    if (is_array($expression)) {
693                        $filter->setExpression(array_map('strtolower', $expression));
694                    } else {
695                        $filter->setExpression(strtolower($expression));
696                    }
697                }
698            }
699
700            $filter->setColumn($column);
701        } else {
702            if (! $filter instanceof FilterNot) {
703                // Allow subquery filters in a filter chain
704                $columns = $filter->listFilteredColumns();
705                if (count($columns) === 1) {
706                    $column = $columns[0];
707                    $virtualTable = $this->aliasToTableName($column);
708                    if (isset($this->subQueryTargets[$virtualTable])) {
709                        $lastSign = null;
710                        $filters = [];
711                        $expressions = [];
712                        foreach ($filter->filters() as $child) {
713                            switch (true) {
714                                case $child instanceof FilterExpression:
715                                    $expression = $child->getExpression();
716                                    if (! is_array($expression)) {
717                                        break;
718                                    }
719                                    // Move to default
720                                default:
721                                    $filters[] = $child;
722                                    continue 2;
723                            }
724                            if ($lastSign === null) {
725                                $lastSign = $child->getSign();
726                            } else {
727                                $sign = $child->getSign();
728                                if ($sign !== $lastSign) {
729                                    $filters[] = new FilterExpression(
730                                        $column,
731                                        $lastSign,
732                                        $filter->getOperatorSymbol() === '&'
733                                            ? [implode('&', $expressions)]
734                                            : $expressions
735                                    );
736                                    $expressions = [];
737                                    $lastSign = $sign;
738                                }
739                            }
740                            $expressions[] = $expression;
741                        }
742                        if (! empty($expressions)) {
743                            $filters[] = new FilterExpression(
744                                $column,
745                                $lastSign,
746                                $filter->getOperatorSymbol() === '&'
747                                    ? [implode('&', $expressions)]
748                                    : $expressions
749                            );
750                        }
751                        $filter->setFilters($filters);
752                    }
753                }
754            }
755
756            foreach ($filter->filters() as $child) {
757                $replacement = $this->requireFilterColumns($child);
758                if ($replacement !== null) {
759                    // setId($child->getId()) is performed because replaceById() doesn't already do it
760                    $filter->replaceById($child->getId(), $replacement->setId($child->getId()));
761                }
762            }
763        }
764    }
765
766    /**
767     * {@inheritdoc}
768     */
769    public function addFilter(Filter $filter)
770    {
771        $filter = clone $filter;
772        return parent::addFilter($this->requireFilterColumns($filter) ?: $filter);
773    }
774
775    public function where($condition, $value = null)
776    {
777        if ($value === '*') {
778            return $this; // Wildcard only filters are ignored so stop early here to avoid joining a table for nothing
779        }
780
781        $this->requireColumn($condition);
782        $col = $this->getMappedField($condition);
783        if ($col === null) {
784            throw new IcingaException(
785                'No such field: %s',
786                $condition
787            );
788        }
789        return parent::where($col, $value);
790    }
791
792    /**
793     * Return true if an field contains an explicit timestamp
794     *
795     * @param   string  $field      The field to test for containing an timestamp
796     *
797     * @return  bool                True when the field represents an timestamp
798     */
799    public function isTimestamp($field)
800    {
801        if ($this->isCustomVar($field)) {
802            return false;
803        }
804
805        return stripos($this->getMappedField($field) ?: $field, 'UNIX_TIMESTAMP') !== false;
806    }
807
808    /**
809     * Return whether the given alias provides case insensitive value comparison
810     *
811     * @param   string  $alias
812     *
813     * @return  bool
814     */
815    public function isCaseInsensitive($alias)
816    {
817        if ($this->isCustomVar($alias)) {
818            return false;
819        }
820
821        $column = $this->getMappedField($alias);
822        if (! $column) {
823            return false;
824        }
825
826        if (empty($this->caseInsensitiveColumns)) {
827            return preg_match('/ COLLATE .+$/', $column) === 1;
828        }
829
830        if (strpos($column, 'LOWER') === 0) {
831            return true;
832        }
833
834        $table = $this->aliasToTableName($alias);
835        if (! $table) {
836            return false;
837        }
838
839        return isset($this->caseInsensitiveColumns[$table][$alias]);
840    }
841
842    /**
843     * Return our column map
844     *
845     * Might be useful for hooks
846     *
847     * @return array
848     */
849    public function getColumnMap()
850    {
851        return $this->columnMap;
852    }
853
854    /**
855     * Apply oracle specific query initialization
856     */
857    private function initializeForOracle()
858    {
859        // Oracle uses the reserved field 'id' for primary keys, so
860        // these must be used instead of the normally defined ids
861        $this->object_id = $this->host_id = $this->service_id
862            = $this->hostgroup_id = $this->servicegroup_id
863            = $this->contact_id = $this->contactgroup_id = 'id';
864        $this->customVarsJoinTemplate =
865            '%1$s = %2$s.object_id AND LOWER(%2$s.varname) = %3$s';
866        foreach ($this->columnMap as &$columns) {
867            foreach ($columns as &$value) {
868                $value = preg_replace('/UNIX_TIMESTAMP/', 'localts2unixts', $value);
869                $value = preg_replace('/ COLLATE .+$/', '', $value);
870            }
871        }
872    }
873
874    /**
875     * Apply PostgreSQL specific query initialization
876     */
877    private function initializeForPostgres()
878    {
879        $this->customVarsJoinTemplate =
880            '%1$s = %2$s.object_id AND LOWER(%2$s.varname) = %3$s';
881        foreach ($this->columnMap as $table => & $columns) {
882            foreach ($columns as $alias => & $column) {
883                // Using a regex here because COLLATE may occur anywhere in the string
884                $column = preg_replace('/ COLLATE .+$/', '', $column, -1, $count);
885                if ($count > 0) {
886                    $this->caseInsensitiveColumns[$table][$alias] = true;
887                }
888
889                $column = preg_replace(
890                    '/inet_aton\(([[:word:].]+)\)/i',
891                    '(CASE WHEN $1 ~ \'(?:[0-9]{1,3}\\\\.){3}[0-9]{1,3}\' THEN $1::inet - \'0.0.0.0\' ELSE NULL END)',
892                    $column
893                );
894                if (version_compare($this->getIdoVersion(), '1.14.2', '>=')) {
895                    $column = str_replace('NOW()', 'NOW() AT TIME ZONE \'UTC\'', $column);
896                } else {
897                    $column = preg_replace(
898                        '/UNIX_TIMESTAMP(\((?>[^()]|(?-1))*\))/i',
899                        'CASE WHEN ($1 < \'1970-01-03 00:00:00+00\'::timestamp with time zone) THEN 0 ELSE UNIX_TIMESTAMP($1) END',
900                        $column
901                    );
902                }
903            }
904        }
905    }
906
907    /**
908     * Set up this query and join the initial tables
909     *
910     * @see IdoQuery::initializeForPostgres     For postgresql specific setup
911     */
912    protected function init()
913    {
914        parent::init();
915        $this->prefix = $this->ds->getTablePrefix();
916
917        foreach (Hook::all('monitoring/idoQueryExtension') as $hook) {
918            $extensions = $hook->extendColumnMap($this);
919            if (! is_array($extensions)) {
920                continue;
921            }
922
923            foreach ($extensions as $vTable => $cols) {
924                if (! array_key_exists($vTable, $this->columnMap)) {
925                    $this->hookedVirtualTables[$vTable] = $hook;
926                    $this->columMap[$vTable] = array();
927                }
928
929                foreach ($cols as $k => $v) {
930                    $this->columnMap[$vTable][$k] = $v;
931                }
932            }
933        }
934
935        $dbType = $this->ds->getDbType();
936        if ($dbType === 'oracle') {
937            $this->initializeForOracle();
938        } elseif ($dbType === 'pgsql') {
939            $this->initializeForPostgres();
940        } else {
941            $charset = $this->ds->getConfig()->get('charset') ?: 'latin1';
942            $this->customVarsJoinTemplate .= " COLLATE {$charset}_general_ci";
943        }
944        $this->joinBaseTables();
945        $this->select->columns($this->columns);
946        $this->prepareAliasIndexes();
947    }
948
949    /**
950     * Join the base tables for this query
951     */
952    protected function joinBaseTables()
953    {
954        reset($this->columnMap);
955        $table = key($this->columnMap);
956
957        $this->select->from(
958            array($table => $this->prefix . $table),
959            array()
960        );
961
962        $this->joinedVirtualTables = array($table => true);
963    }
964
965    /**
966     * Populates the idxAliasTAble and idxAliasColumn properties
967     */
968    protected function prepareAliasIndexes()
969    {
970        foreach ($this->columnMap as $tbl => & $cols) {
971            foreach ($cols as $alias => $col) {
972                $this->idxAliasTable[$alias] = $tbl;
973                $this->idxAliasColumn[$alias] = preg_replace('~\n\s*~', ' ', $col);
974            }
975        }
976    }
977
978    /**
979     * Resolve columns aliases to their database field using the columnMap
980     *
981     * @param   array $columns
982     *
983     * @return  array
984     */
985    public function resolveColumns($columns)
986    {
987        $resolvedColumns = array();
988
989        foreach ($columns as $alias => $col) {
990            if ($col instanceof Zend_Db_Expr) {
991                // Support selecting NULL as column for example
992                $resolvedColumns[$alias] = $col;
993                continue;
994            }
995            $this->requireColumn($col);
996            if ($this->isCustomvar($col)) {
997                $name = $this->getCustomvarColumnName($col);
998            } else {
999                $name = $this->aliasToColumnName($col);
1000            }
1001            if (is_int($alias)) {
1002                $alias = $col;
1003            } else {
1004                $this->idxCustomAliases[$alias] = $col;
1005            }
1006
1007            $resolvedColumns[$alias] = preg_replace('|\n|', ' ', $name);
1008        }
1009
1010        return $resolvedColumns;
1011    }
1012
1013    /**
1014     * Return all columns that will be selected when no columns are given in the constructor or from
1015     *
1016     * @return array        An array of column aliases
1017     */
1018    public function getDefaultColumns()
1019    {
1020        reset($this->columnMap);
1021        $table = key($this->columnMap);
1022        return array_keys($this->columnMap[$table]);
1023    }
1024
1025    /**
1026     * Modify the query to the given alias can be used in the result set or queries
1027     *
1028     * This calls requireVirtualTable if needed
1029     *
1030     * @param $alias                                The alias of the column to require
1031     *
1032     * @return $this                                 Fluent interface
1033     * @see    IdoQuery::requireVirtualTable        The method initializing required joins
1034     * @throws \Icinga\Exception\ProgrammingError   When an unknown column is requested
1035     */
1036    public function requireColumn($alias)
1037    {
1038        if ($this->hasAliasName($alias)) {
1039            $this->requireVirtualTable($this->aliasToTableName($alias));
1040        } elseif ($this->isCustomVar($alias)) {
1041            $this->requireCustomvar($alias);
1042        } else {
1043            throw new ProgrammingError(
1044                '%s : Got invalid column: %s',
1045                get_called_class(),
1046                $alias
1047            );
1048        }
1049        return $this;
1050    }
1051
1052    /**
1053     * Return true if the given alias exists
1054     *
1055     * @param  String $alias    The alias to test for
1056     * @return bool             True when the alias exists, otherwise false
1057     */
1058    protected function hasAliasName($alias)
1059    {
1060        return array_key_exists($alias, $this->idxAliasColumn);
1061    }
1062
1063    /**
1064     * Require a virtual table for the given table name if not already required
1065     *
1066     * @param  String $name         The table name to require
1067     * @return $this                 Fluent interface
1068     */
1069    protected function requireVirtualTable($name)
1070    {
1071        if ($this->hasJoinedVirtualTable($name)) {
1072            return $this;
1073        }
1074
1075        if ($this->virtualTableIsHooked($name)) {
1076            return $this->joinHookedVirtualTable($name);
1077        } else {
1078            return $this->joinVirtualTable($name);
1079        }
1080    }
1081
1082    /**
1083     * Whether a given virtual table name has been provided by a hook
1084     *
1085     * @param string $name Virtual table name
1086     *
1087     * @return boolean
1088     */
1089    protected function virtualTableIsHooked($name)
1090    {
1091        return array_key_exists($name, $this->hookedVirtualTables);
1092    }
1093
1094    protected function conflictsWithVirtualTable($name)
1095    {
1096        if ($this->hasJoinedVirtualTable($name)) {
1097            throw new ProgrammingError(
1098                'IDO query virtual table conflict with "%s"',
1099                $name
1100            );
1101        }
1102        return $this;
1103    }
1104
1105    /**
1106     * Call the method for joining a virtual table
1107     *
1108     * This requires a join$Table() method to exist
1109     *
1110     * @param  String $table        The table to join by calling join$Table() in the concrete implementation
1111     * @return $this                 Fluent interface
1112     *
1113     * @throws \Icinga\Exception\ProgrammingError   If the join method for this table does not exist
1114     */
1115    protected function joinVirtualTable($table)
1116    {
1117        $func = 'join' . ucfirst($table);
1118        if (method_exists($this, $func)) {
1119            $this->$func();
1120        } else {
1121            throw new ProgrammingError(
1122                'Cannot join "%s", no such table found',
1123                $table
1124            );
1125        }
1126        $this->joinedVirtualTables[$table] = true;
1127        return $this;
1128    }
1129
1130    /**
1131     * Tell a hook to join a virtual table
1132     *
1133     * @param  String $table
1134     * @return $this
1135     */
1136    protected function joinHookedVirtualTable($table)
1137    {
1138        $this->hookedVirtualTables[$table]->joinVirtualTable($this, $table);
1139        $this->joinedVirtualTables[$table] = true;
1140        return $this;
1141    }
1142
1143    /**
1144     * Get the table for a specific alias
1145     *
1146     * @param   String $alias   The alias to request the table for
1147     * @return  String          The table for the alias or null if it doesn't exist
1148     */
1149    protected function aliasToTableName($alias)
1150    {
1151        return isset($this->idxAliasTable[$alias]) ? $this->idxAliasTable[$alias] : null;
1152    }
1153
1154    /**
1155     * Return whether this query allows to join custom variables
1156     *
1157     * @return  bool
1158     */
1159    public function allowsCustomVars()
1160    {
1161        return $this->allowCustomVars;
1162    }
1163
1164    /**
1165     * Return true if the given alias denotes a custom variable
1166     *
1167     * @param  String $alias    The alias to test for being a customvariable
1168     * @return bool             True if the alias is a customvariable, otherwise false
1169     */
1170    protected function isCustomVar($alias)
1171    {
1172        return $this->allowCustomVars && $alias[0] === '_';
1173    }
1174
1175    protected function requireCustomvar($customvar)
1176    {
1177        if (! $this->hasCustomvar($customvar)) {
1178            $this->joinCustomvar($customvar);
1179        }
1180        return $this;
1181    }
1182
1183    protected function hasCustomvar($customvar)
1184    {
1185        return array_key_exists(strtolower($customvar), $this->customVars);
1186    }
1187
1188    protected function joinCustomvar($customvar)
1189    {
1190        // TODO: This is not generic enough yet
1191        list($type, $name) = $this->customvarNameToTypeName($customvar);
1192        $alias = ($type === 'host' ? 'hcv_' : 'scv_') . $name;
1193
1194        $this->customVars[strtolower($customvar)] = $alias;
1195
1196        if ($this->hasJoinedVirtualTable('services')) {
1197            $leftcol = 's.' . $type . '_object_id';
1198        } elseif ($type === 'service') {
1199            $this->requireVirtualTable('services');
1200            $leftcol = 's.service_object_id';
1201        } else {
1202            $this->requireVirtualTable('hosts');
1203            $leftcol = 'h.host_object_id';
1204        }
1205
1206        $mapped = $this->getMappedField($leftcol);
1207        if ($mapped !== null) {
1208            $this->requireColumn($leftcol);
1209            $leftcol = $mapped;
1210        }
1211
1212        $joinOn = sprintf(
1213            $this->customVarsJoinTemplate,
1214            $leftcol,
1215            $alias,
1216            $this->db->quote($name)
1217        );
1218
1219        $this->select->joinLeft(
1220            array($alias => $this->prefix . 'customvariablestatus'),
1221            $joinOn,
1222            array()
1223        );
1224
1225        return $this;
1226    }
1227
1228    protected function customvarNameToTypeName($customvar)
1229    {
1230        $customvar = strtolower($customvar);
1231        if (! preg_match('~^_(host|service)_([a-zA-Z0-9_]+)$~', $customvar, $m)) {
1232            throw new ProgrammingError(
1233                'Got invalid custom var: "%s"',
1234                $customvar
1235            );
1236        }
1237        return array($m[1], $m[2]);
1238    }
1239
1240    protected function hasJoinedVirtualTable($name)
1241    {
1242        return array_key_exists($name, $this->joinedVirtualTables);
1243    }
1244
1245    /**
1246     * Get the query column of a already joined custom variable
1247     *
1248     * @param   string $customvar
1249     *
1250     * @return  string
1251     * @throws  QueryException If the custom variable has not been joined
1252     */
1253    protected function getCustomvarColumnName($customvar)
1254    {
1255        if (! isset($this->customVars[($customvar = strtolower($customvar))])) {
1256            throw new QueryException('Custom variable %s has not been joined', $customvar);
1257        }
1258        return $this->customVars[$customvar] . '.varvalue';
1259    }
1260
1261    public function aliasToColumnName($alias)
1262    {
1263        return $this->idxAliasColumn[$alias];
1264    }
1265
1266    /**
1267     * Get the alias of a column expression as defined in the {@link $columnMap} property.
1268     *
1269     * @param   string $alias Potential custom alias
1270     *
1271     * @return  string
1272     */
1273    public function customAliasToAlias($alias)
1274    {
1275        if (isset($this->idxCustomAliases[$alias])) {
1276            return $this->idxCustomAliases[$alias];
1277        }
1278        return $alias;
1279    }
1280
1281    /**
1282     * Create a sub query
1283     *
1284     * @param   string  $queryName
1285     * @param   array   $columns
1286     *
1287     * @return  static
1288     */
1289    protected function createSubQuery($queryName, $columns = array())
1290    {
1291        $class = '\\'
1292            . substr(__CLASS__, 0, strrpos(__CLASS__, '\\') + 1)
1293            . ucfirst($queryName) . 'Query';
1294        $query = new $class($this->ds, $columns);
1295        return $query;
1296    }
1297
1298    /**
1299     * Set columns to select
1300     *
1301     * @param   array $columns
1302     *
1303     * @return  $this
1304     */
1305    public function columns(array $columns)
1306    {
1307        $this->idxCustomAliases = array();
1308        $this->columns = $this->resolveColumns($columns);
1309        // TODO: we need to refresh our select!
1310        // $this->select->columns($columns);
1311        return $this;
1312    }
1313
1314    public function clearGroupingRules()
1315    {
1316        $this->groupBase = array();
1317        $this->groupOrigin = array();
1318        return $this;
1319    }
1320
1321    /**
1322     * Register the GROUP BY columns required for the given alias
1323     *
1324     * @param   string  $alias              The alias to register columns for
1325     * @param   string  $table              The table the given alias is associated with
1326     * @param   array   $groupedColumns     The grouping columns registered so far
1327     * @param   array   $groupedTables      The tables for which columns were registered so far
1328     */
1329    protected function registerGroupColumns($alias, $table, array &$groupedColumns, array &$groupedTables)
1330    {
1331        switch ($table) {
1332            case 'checktimeperiods':
1333                $groupedColumns[] = 'ctp.timeperiod_id';
1334                break;
1335            case 'contacts':
1336                $groupedColumns[] = 'co.object_id';
1337                $groupedColumns[] = 'c.contact_id';
1338                break;
1339            case 'hostobjects':
1340                $groupedColumns[] = 'ho.object_id';
1341                break;
1342            case 'hosts':
1343                $groupedColumns[] = 'h.host_id';
1344                break;
1345            case 'hostgroups':
1346                $groupedColumns[] = 'hgo.object_id';
1347                $groupedColumns[] = 'hg.hostgroup_id';
1348                break;
1349            case 'hoststatus':
1350                $groupedColumns[] = 'hs.hoststatus_id';
1351                break;
1352            case 'instances':
1353                $groupedColumns[] = 'i.instance_id';
1354                break;
1355            case 'servicegroups':
1356                $groupedColumns[] = 'sgo.object_id';
1357                $groupedColumns[] = 'sg.servicegroup_id';
1358                break;
1359            case 'serviceobjects':
1360                $groupedColumns[] = 'so.object_id';
1361                break;
1362            case 'serviceproblemsummary':
1363                $groupedColumns[] = 'sps.unhandled_services_count';
1364                break;
1365            case 'services':
1366                $groupedColumns[] = 'so.object_id';
1367                $groupedColumns[] = 's.service_id';
1368                break;
1369            case 'servicestatus':
1370                $groupedColumns[] = 'ss.servicestatus_id';
1371                break;
1372            default:
1373                return;
1374        }
1375
1376        $groupedTables[$table] = true;
1377    }
1378
1379    /**
1380     * {@inheritdoc}
1381     */
1382    public function getGroup()
1383    {
1384        $group = parent::getGroup() ?: array();
1385        if (! is_array($group)) {
1386            $group = array($group);
1387        }
1388
1389        $joinedOrigins = array_filter($this->groupOrigin, array($this, 'hasJoinedVirtualTable'));
1390        if (empty($joinedOrigins)) {
1391            return $group;
1392        }
1393
1394        $groupedTables = array();
1395        foreach ($this->groupBase as $baseTable => $aliasedPks) {
1396            if (! $this->hasJoinedVirtualTable($baseTable)) {
1397                continue;
1398            }
1399            $groupedTables[$baseTable] = true;
1400            foreach ($aliasedPks as $aliasedPk) {
1401                $group[] = $aliasedPk;
1402            }
1403        }
1404
1405        foreach (new ColumnFilterIterator($this->columns) as $desiredAlias => $desiredColumn) {
1406            $alias = is_string($desiredAlias) ? $this->customAliasToAlias($desiredAlias) : $desiredColumn;
1407            if ($this->isCustomVar($alias) && $this->getDatasource()->getDbType() === 'pgsql') {
1408                $table = $this->customVars[$alias];
1409                if (! isset($groupedTables[$table])) {
1410                    $group[] = $this->getCustomvarColumnName($alias);
1411                    $groupedTables[$table] = true;
1412                }
1413                continue;
1414            }
1415            $table = $this->aliasToTableName($alias);
1416            if ($table && !isset($groupedTables[$table]) && (
1417                in_array($table, $joinedOrigins, true) || $this->getDatasource()->getDbType() === 'pgsql')
1418            ) {
1419                $this->registerGroupColumns($alias, $table, $group, $groupedTables);
1420            }
1421        }
1422
1423        if (! empty($group) && $this->getDatasource()->getDbType() === 'pgsql') {
1424            foreach (new ColumnFilterIterator($this->orderColumns) as $alias) {
1425                if ($this->isCustomVar($alias)) {
1426                    $table = $this->customVars[$alias];
1427                    if (! isset($groupedTables[$table])) {
1428                        $group[] = $this->getCustomvarColumnName($alias);
1429                        $groupedTables[$table] = true;
1430                    }
1431                    continue;
1432                }
1433                $table = $this->aliasToTableName($alias);
1434                if ($table && !isset($groupedTables[$table])
1435                    && !in_array($this->getMappedField($alias), $this->columns, true)
1436                ) {
1437                    $this->registerGroupColumns($alias, $table, $group, $groupedTables);
1438                }
1439            }
1440        }
1441
1442        return array_unique($group);
1443    }
1444
1445    // TODO: Move this away, see note related to $idoVersion var
1446    protected function getIdoVersion()
1447    {
1448        if (self::$idoVersion === null) {
1449            $dbconf = $this->db->getConfig();
1450            $id = $dbconf['host'] . '/' . $dbconf['dbname'];
1451            $session = null;
1452            if (Icinga::app()->isWeb()) {
1453                // TODO: Once we have version per connection we should choose a
1454                //       namespace based on resource name
1455                $session = Session::getSession()->getNamespace('monitoring/ido/' . $id);
1456                if (isset($session->version)) {
1457                    self::$idoVersion = $session->version;
1458                    return self::$idoVersion;
1459                }
1460            }
1461            self::$idoVersion = $this->db->fetchOne(
1462                $this->db->select()->from($this->prefix . 'dbversion', 'version')
1463            );
1464            if ($session !== null) {
1465                $session->version = self::$idoVersion;
1466            }
1467        }
1468        return self::$idoVersion;
1469    }
1470
1471    /**
1472     * Return the name of the primary key column for the given table name
1473     *
1474     * @param   string  $table
1475     *
1476     * @return  string
1477     *
1478     * @throws ProgrammingError     In case $table is unknown
1479     */
1480    protected function getPrimaryKeyColumn($table)
1481    {
1482        // TODO: For god's sake, make this being a mapping
1483        //       (instead of matching a ton of properties using a ridiculous long switch case)
1484        switch ($table) {
1485            case 'instances':
1486                return $this->instance_id;
1487            case 'objects':
1488                return $this->object_id;
1489            case 'acknowledgements':
1490                return $this->acknowledgement_id;
1491            case 'commenthistory':
1492                return $this->commenthistory_id;
1493            case 'contactnotifiations':
1494                return $this->contactnotification_id;
1495            case 'downtimehistory':
1496                return $this->downtimehistory_id;
1497            case 'flappinghistory':
1498                return $this->flappinghistory_id;
1499            case 'notifications':
1500                return $this->notification_id;
1501            case 'statehistory':
1502                return $this->statehistory_id;
1503            case 'comments':
1504                return $this->comment_id;
1505            case 'customvariablestatus':
1506                return $this->customvariablestatus_id;
1507            case 'hoststatus':
1508                return $this->hoststatus_id;
1509            case 'programstatus':
1510                return $this->programstatus_id;
1511            case 'runtimevariables':
1512                return $this->runtimevariable_id;
1513            case 'scheduleddowntime':
1514                return $this->scheduleddowntime_id;
1515            case 'servicestatus':
1516                return $this->servicestatus_id;
1517            case 'contactstatus':
1518                return $this->contactstatus_id;
1519            case 'commands':
1520                return $this->command_id;
1521            case 'contactgroup_members':
1522                return $this->contactgroup_member_id;
1523            case 'contactgroups':
1524                return $this->contactgroup_id;
1525            case 'contacts':
1526                return $this->contact_id;
1527            case 'customvariables':
1528                return $this->customvariable_id;
1529            case 'host_contactgroups':
1530                return $this->host_contactgroup_id;
1531            case 'host_contacts':
1532                return $this->host_contact_id;
1533            case 'hostgroup_members':
1534                return $this->hostgroup_member_id;
1535            case 'hostgroups':
1536                return $this->hostgroup_id;
1537            case 'hosts':
1538                return $this->host_id;
1539            case 'service_contactgroups':
1540                return $this->service_contactgroup_id;
1541            case 'service_contacts':
1542                return $this->service_contact_id;
1543            case 'servicegroup_members':
1544                return $this->servicegroup_member_id;
1545            case 'servicegroups':
1546                return $this->servicegroup_id;
1547            case 'services':
1548                return $this->service_id;
1549            case 'timeperiods':
1550                return $this->timeperiod_id;
1551            default:
1552                throw new ProgrammingError('Cannot provide a primary key column. Table "%s" is unknown', $table);
1553        }
1554    }
1555}
1556