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