1<?php 2/* Icinga Web 2 | (c) 2015 Icinga Development Team | GPLv2+ */ 3 4namespace Icinga\Repository; 5 6use Zend_Db_Expr; 7use Icinga\Data\Db\DbConnection; 8use Icinga\Data\Extensible; 9use Icinga\Data\Filter\Filter; 10use Icinga\Data\Filter\FilterExpression; 11use Icinga\Data\Reducible; 12use Icinga\Data\Updatable; 13use Icinga\Exception\IcingaException; 14use Icinga\Exception\ProgrammingError; 15use Icinga\Exception\StatementException; 16use Icinga\Util\StringHelper; 17 18/** 19 * Abstract base class for concrete database repository implementations 20 * 21 * Additionally provided features: 22 * <ul> 23 * <li>Support for table aliases</li> 24 * <li>Automatic table prefix handling</li> 25 * <li>Insert, update and delete capabilities</li> 26 * <li>Differentiation between statement and query columns</li> 27 * <li>Capability to join additional tables depending on the columns being selected or used in a filter</li> 28 * </ul> 29 * 30 * @method DbConnection getDataSource($table = null) 31 */ 32abstract class DbRepository extends Repository implements Extensible, Updatable, Reducible 33{ 34 /** 35 * The datasource being used 36 * 37 * @var DbConnection 38 */ 39 protected $ds; 40 41 /** 42 * The table aliases being applied 43 * 44 * This must be initialized by repositories which are going to make use of table aliases. Every table for which 45 * aliased columns are provided must be defined in this array using its name as key and the alias being used as 46 * value. Failure to do so will result in invalid queries. 47 * 48 * @var array 49 */ 50 protected $tableAliases; 51 52 /** 53 * The join probability rules 54 * 55 * This may be initialized by repositories which make use of the table join capability. It allows to define 56 * probability rules to enhance control how ambiguous column aliases are associated with the correct table. 57 * To define a rule use the name of a base table as key and another array of table names as probable join 58 * targets ordered by priority. (Ascending: Lower means higher priority) 59 * <code> 60 * array( 61 * 'table_name' => array('target1', 'target2', 'target3') 62 * ) 63 * </code> 64 * 65 * @todo Support for tree-ish rules 66 * 67 * @var array 68 */ 69 protected $joinProbabilities; 70 71 /** 72 * The statement columns being provided 73 * 74 * This may be initialized by repositories which are going to make use of table aliases. It allows to provide 75 * alias-less column names to be used for a statement. The array needs to be in the following format: 76 * <code> 77 * array( 78 * 'table_name' => array( 79 * 'column1', 80 * 'alias1' => 'column2', 81 * 'alias2' => 'column3' 82 * ) 83 * ) 84 * </code> 85 * 86 * @var array 87 */ 88 protected $statementColumns; 89 90 /** 91 * An array to map table names to statement columns/aliases 92 * 93 * @var array 94 */ 95 protected $statementAliasTableMap; 96 97 /** 98 * A flattened array to map statement columns to aliases 99 * 100 * @var array 101 */ 102 protected $statementAliasColumnMap; 103 104 /** 105 * An array to map table names to statement columns 106 * 107 * @var array 108 */ 109 protected $statementColumnTableMap; 110 111 /** 112 * A flattened array to map aliases to statement columns 113 * 114 * @var array 115 */ 116 protected $statementColumnAliasMap; 117 118 /** 119 * List of column names or aliases mapped to their table where the COLLATE SQL-instruction has been removed 120 * 121 * This list is being populated in case of a PostgreSQL backend only, 122 * to ensure case-insensitive string comparison in WHERE clauses. 123 * 124 * @var array 125 */ 126 protected $caseInsensitiveColumns; 127 128 /** 129 * Create a new DB repository object 130 * 131 * In case $this->queryColumns has already been initialized, this initializes 132 * $this->caseInsensitiveColumns in case of a PostgreSQL connection. 133 * 134 * @param DbConnection $ds The datasource to use 135 */ 136 public function __construct(DbConnection $ds) 137 { 138 parent::__construct($ds); 139 140 if ($ds->getDbType() === 'pgsql' && $this->queryColumns !== null) { 141 $this->queryColumns = $this->removeCollateInstruction($this->queryColumns); 142 } 143 } 144 145 /** 146 * Return the query columns being provided 147 * 148 * Initializes $this->caseInsensitiveColumns in case of a PostgreSQL connection. 149 * 150 * @return array 151 */ 152 public function getQueryColumns() 153 { 154 if ($this->queryColumns === null) { 155 $this->queryColumns = parent::getQueryColumns(); 156 if ($this->ds->getDbType() === 'pgsql') { 157 $this->queryColumns = $this->removeCollateInstruction($this->queryColumns); 158 } 159 } 160 161 return $this->queryColumns; 162 } 163 164 /** 165 * Return the table aliases to be applied 166 * 167 * Calls $this->initializeTableAliases() in case $this->tableAliases is null. 168 * 169 * @return array 170 */ 171 public function getTableAliases() 172 { 173 if ($this->tableAliases === null) { 174 $this->tableAliases = $this->initializeTableAliases(); 175 } 176 177 return $this->tableAliases; 178 } 179 180 /** 181 * Overwrite this in your repository implementation in case you need to initialize the table aliases lazily 182 * 183 * @return array 184 */ 185 protected function initializeTableAliases() 186 { 187 return array(); 188 } 189 190 /** 191 * Return the join probability rules 192 * 193 * Calls $this->initializeJoinProbabilities() in case $this->joinProbabilities is null. 194 * 195 * @return array 196 */ 197 public function getJoinProbabilities() 198 { 199 if ($this->joinProbabilities === null) { 200 $this->joinProbabilities = $this->initializeJoinProbabilities(); 201 } 202 203 return $this->joinProbabilities; 204 } 205 206 /** 207 * Overwrite this in your repository implementation in case you need to initialize the join probabilities lazily 208 * 209 * @return array 210 */ 211 protected function initializeJoinProbabilities() 212 { 213 return array(); 214 } 215 216 /** 217 * Remove each COLLATE SQL-instruction from all given query columns 218 * 219 * @param array $queryColumns 220 * 221 * @return array $queryColumns, the updated version 222 */ 223 protected function removeCollateInstruction($queryColumns) 224 { 225 foreach ($queryColumns as $table => & $columns) { 226 foreach ($columns as $alias => & $column) { 227 // Using a regex here because COLLATE may occur anywhere in the string 228 $column = preg_replace('/ COLLATE .+$/', '', $column, -1, $count); 229 if ($count > 0) { 230 $this->caseInsensitiveColumns[$table][is_string($alias) ? $alias : $column] = true; 231 } 232 } 233 } 234 235 return $queryColumns; 236 } 237 238 /** 239 * Initialize table, column and alias maps 240 * 241 * @throws ProgrammingError In case $this->queryColumns does not provide any column information 242 */ 243 protected function initializeAliasMaps() 244 { 245 parent::initializeAliasMaps(); 246 247 foreach ($this->aliasTableMap as $alias => $table) { 248 if ($table !== null) { 249 if (strpos($alias, '.') !== false) { 250 $prefixedAlias = str_replace('.', '_', $alias); 251 } else { 252 $prefixedAlias = $table . '_' . $alias; 253 } 254 255 if (array_key_exists($prefixedAlias, $this->aliasTableMap)) { 256 if ($this->aliasTableMap[$prefixedAlias] !== null) { 257 $existingTable = $this->aliasTableMap[$prefixedAlias]; 258 $existingColumn = $this->aliasColumnMap[$prefixedAlias]; 259 $this->aliasTableMap[$existingTable . '.' . $prefixedAlias] = $existingTable; 260 $this->aliasColumnMap[$existingTable . '.' . $prefixedAlias] = $existingColumn; 261 $this->aliasTableMap[$prefixedAlias] = null; 262 $this->aliasColumnMap[$prefixedAlias] = null; 263 } 264 265 $this->aliasTableMap[$table . '.' . $prefixedAlias] = $table; 266 $this->aliasColumnMap[$table . '.' . $prefixedAlias] = $this->aliasColumnMap[$alias]; 267 } else { 268 $this->aliasTableMap[$prefixedAlias] = $table; 269 $this->aliasColumnMap[$prefixedAlias] = $this->aliasColumnMap[$alias]; 270 } 271 } 272 } 273 } 274 275 /** 276 * Return the given table with the datasource's prefix being prepended 277 * 278 * @param array|string $table 279 * 280 * @return array|string 281 * 282 * @throws IcingaException In case $table is not of a supported type 283 */ 284 protected function prependTablePrefix($table) 285 { 286 $prefix = $this->ds->getTablePrefix(); 287 if (! $prefix) { 288 return $table; 289 } 290 291 if (is_array($table)) { 292 foreach ($table as & $tableName) { 293 if (strpos($tableName, $prefix) === false) { 294 $tableName = $prefix . $tableName; 295 } 296 } 297 } elseif (is_string($table)) { 298 $table = (strpos($table, $prefix) === false ? $prefix : '') . $table; 299 } else { 300 throw new IcingaException('Table prefix handling for type "%s" is not supported', type($table)); 301 } 302 303 return $table; 304 } 305 306 /** 307 * Remove the datasource's prefix from the given table name and return the remaining part 308 * 309 * @param array|string $table 310 * 311 * @return array|string 312 * 313 * @throws IcingaException In case $table is not of a supported type 314 */ 315 protected function removeTablePrefix($table) 316 { 317 $prefix = $this->ds->getTablePrefix(); 318 if (! $prefix) { 319 return $table; 320 } 321 322 if (is_array($table)) { 323 foreach ($table as & $tableName) { 324 if (strpos($tableName, $prefix) === 0) { 325 $tableName = str_replace($prefix, '', $tableName); 326 } 327 } 328 } elseif (is_string($table)) { 329 if (strpos($table, $prefix) === 0) { 330 $table = str_replace($prefix, '', $table); 331 } 332 } else { 333 throw new IcingaException('Table prefix handling for type "%s" is not supported', type($table)); 334 } 335 336 return $table; 337 } 338 339 /** 340 * Return the given table with its alias being applied 341 * 342 * @param array|string $table 343 * @param string $virtualTable 344 * 345 * @return array|string 346 */ 347 protected function applyTableAlias($table, $virtualTable = null) 348 { 349 if (! is_array($table)) { 350 $tableAliases = $this->getTableAliases(); 351 if ($virtualTable !== null && isset($tableAliases[$virtualTable])) { 352 return array($tableAliases[$virtualTable] => $table); 353 } 354 355 if (isset($tableAliases[($nonPrefixedTable = $this->removeTablePrefix($table))])) { 356 return array($tableAliases[$nonPrefixedTable] => $table); 357 } 358 } 359 360 return $table; 361 } 362 363 /** 364 * Return the given table with its alias being cleared 365 * 366 * @param array|string $table 367 * 368 * @return string 369 * 370 * @throws IcingaException In case $table is not of a supported type 371 */ 372 protected function clearTableAlias($table) 373 { 374 if (is_string($table)) { 375 return $table; 376 } 377 378 if (is_array($table)) { 379 return reset($table); 380 } 381 382 throw new IcingaException('Table alias handling for type "%s" is not supported', type($table)); 383 } 384 385 /** 386 * Insert a table row with the given data 387 * 388 * Note that the base implementation does not perform any quoting on the $table argument. 389 * Pass an array with a column name (the same as in $bind) and a PDO::PARAM_* constant as value 390 * as third parameter $types to define a different type than string for a particular column. 391 * 392 * @param string $table 393 * @param array $bind 394 * @param array $types 395 * 396 * @return int The number of affected rows 397 */ 398 public function insert($table, array $bind, array $types = array()) 399 { 400 $realTable = $this->clearTableAlias($this->requireTable($table)); 401 402 foreach ($types as $alias => $type) { 403 unset($types[$alias]); 404 $types[$this->requireStatementColumn($table, $alias)] = $type; 405 } 406 407 return $this->ds->insert($realTable, $this->requireStatementColumns($table, $bind), $types); 408 } 409 410 /** 411 * Update table rows with the given data, optionally limited by using a filter 412 * 413 * Note that the base implementation does not perform any quoting on the $table argument. 414 * Pass an array with a column name (the same as in $bind) and a PDO::PARAM_* constant as value 415 * as fourth parameter $types to define a different type than string for a particular column. 416 * 417 * @param string $table 418 * @param array $bind 419 * @param Filter $filter 420 * @param array $types 421 * 422 * @return int The number of affected rows 423 */ 424 public function update($table, array $bind, Filter $filter = null, array $types = array()) 425 { 426 $realTable = $this->clearTableAlias($this->requireTable($table)); 427 428 if ($filter) { 429 $filter = $this->requireFilter($table, $filter); 430 } 431 432 foreach ($types as $alias => $type) { 433 unset($types[$alias]); 434 $types[$this->requireStatementColumn($table, $alias)] = $type; 435 } 436 437 return $this->ds->update($realTable, $this->requireStatementColumns($table, $bind), $filter, $types); 438 } 439 440 /** 441 * Delete table rows, optionally limited by using a filter 442 * 443 * @param string $table 444 * @param Filter $filter 445 * 446 * @return int The number of affected rows 447 */ 448 public function delete($table, Filter $filter = null) 449 { 450 $realTable = $this->clearTableAlias($this->requireTable($table)); 451 452 if ($filter) { 453 $filter = $this->requireFilter($table, $filter); 454 } 455 456 return $this->ds->delete($realTable, $filter); 457 } 458 459 /** 460 * Return the statement columns being provided 461 * 462 * Calls $this->initializeStatementColumns() in case $this->statementColumns is null. 463 * 464 * @return array 465 */ 466 public function getStatementColumns() 467 { 468 if ($this->statementColumns === null) { 469 $this->statementColumns = $this->initializeStatementColumns(); 470 } 471 472 return $this->statementColumns; 473 } 474 475 /** 476 * Overwrite this in your repository implementation in case you need to initialize the statement columns lazily 477 * 478 * @return array 479 */ 480 protected function initializeStatementColumns() 481 { 482 return array(); 483 } 484 485 /** 486 * Return an array to map table names to statement columns/aliases 487 * 488 * @return array 489 */ 490 protected function getStatementAliasTableMap() 491 { 492 if ($this->statementAliasTableMap === null) { 493 $this->initializeStatementMaps(); 494 } 495 496 return $this->statementAliasTableMap; 497 } 498 499 /** 500 * Return a flattened array to map statement columns to aliases 501 * 502 * @return array 503 */ 504 protected function getStatementAliasColumnMap() 505 { 506 if ($this->statementAliasColumnMap === null) { 507 $this->initializeStatementMaps(); 508 } 509 510 return $this->statementAliasColumnMap; 511 } 512 513 /** 514 * Return an array to map table names to statement columns 515 * 516 * @return array 517 */ 518 protected function getStatementColumnTableMap() 519 { 520 if ($this->statementColumnTableMap === null) { 521 $this->initializeStatementMaps(); 522 } 523 524 return $this->statementColumnTableMap; 525 } 526 527 /** 528 * Return a flattened array to map aliases to statement columns 529 * 530 * @return array 531 */ 532 protected function getStatementColumnAliasMap() 533 { 534 if ($this->statementColumnAliasMap === null) { 535 $this->initializeStatementMaps(); 536 } 537 538 return $this->statementColumnAliasMap; 539 } 540 541 /** 542 * Initialize $this->statementAliasTableMap and $this->statementAliasColumnMap 543 */ 544 protected function initializeStatementMaps() 545 { 546 $this->statementAliasTableMap = array(); 547 $this->statementAliasColumnMap = array(); 548 $this->statementColumnTableMap = array(); 549 $this->statementColumnAliasMap = array(); 550 foreach ($this->getStatementColumns() as $table => $columns) { 551 foreach ($columns as $alias => $column) { 552 $key = is_string($alias) ? $alias : $column; 553 if (array_key_exists($key, $this->statementAliasTableMap)) { 554 if ($this->statementAliasTableMap[$key] !== null) { 555 $existingTable = $this->statementAliasTableMap[$key]; 556 $existingColumn = $this->statementAliasColumnMap[$key]; 557 $this->statementAliasTableMap[$existingTable . '.' . $key] = $existingTable; 558 $this->statementAliasColumnMap[$existingTable . '.' . $key] = $existingColumn; 559 $this->statementAliasTableMap[$key] = null; 560 $this->statementAliasColumnMap[$key] = null; 561 } 562 563 $this->statementAliasTableMap[$table . '.' . $key] = $table; 564 $this->statementAliasColumnMap[$table . '.' . $key] = $column; 565 } else { 566 $this->statementAliasTableMap[$key] = $table; 567 $this->statementAliasColumnMap[$key] = $column; 568 } 569 570 if (array_key_exists($column, $this->statementColumnTableMap)) { 571 if ($this->statementColumnTableMap[$column] !== null) { 572 $existingTable = $this->statementColumnTableMap[$column]; 573 $existingAlias = $this->statementColumnAliasMap[$column]; 574 $this->statementColumnTableMap[$existingTable . '.' . $column] = $existingTable; 575 $this->statementColumnAliasMap[$existingTable . '.' . $column] = $existingAlias; 576 $this->statementColumnTableMap[$column] = null; 577 $this->statementColumnAliasMap[$column] = null; 578 } 579 580 $this->statementColumnTableMap[$table . '.' . $column] = $table; 581 $this->statementColumnAliasMap[$table . '.' . $column] = $key; 582 } else { 583 $this->statementColumnTableMap[$column] = $table; 584 $this->statementColumnAliasMap[$column] = $key; 585 } 586 } 587 } 588 } 589 590 /** 591 * Return whether this repository is capable of converting values for the given table and optional column 592 * 593 * This does not check whether any conversion for the given table is available if $column is not given, as it 594 * may be possible that columns from another table where joined in which would otherwise not being converted. 595 * 596 * @param string $table 597 * @param string $column 598 * 599 * @return bool 600 */ 601 public function providesValueConversion($table, $column = null) 602 { 603 if ($column !== null) { 604 if ($column instanceof Zend_Db_Expr) { 605 return false; 606 } 607 608 if ($this->validateQueryColumnAssociation($table, $column)) { 609 return parent::providesValueConversion($table, $column); 610 } 611 612 if (($tableName = $this->findTableName($column, $table))) { 613 return parent::providesValueConversion($tableName, $column); 614 } 615 616 return false; 617 } 618 619 $conversionRules = $this->getConversionRules(); 620 return !empty($conversionRules); 621 } 622 623 /** 624 * Return the name of the conversion method for the given alias or column name and context 625 * 626 * If a query column or a filter column, which is part of a query filter, needs to be converted, 627 * you'll need to pass $query, otherwise the column is considered a statement column. 628 * 629 * @param string $table The datasource's table 630 * @param string $name The alias or column name for which to return a conversion method 631 * @param string $context The context of the conversion: persist or retrieve 632 * @param RepositoryQuery $query If given the column is considered a query column, 633 * statement column otherwise 634 * 635 * @return string 636 * 637 * @throws ProgrammingError In case a conversion rule is found but not any conversion method 638 */ 639 protected function getConverter($table, $name, $context, RepositoryQuery $query = null) 640 { 641 if ($name instanceof Zend_Db_Expr) { 642 return; 643 } 644 645 if (! ($query !== null && $this->validateQueryColumnAssociation($table, $name)) 646 && !($query === null && $this->validateStatementColumnAssociation($table, $name)) 647 ) { 648 $table = $this->findTableName($name, $table); 649 if (! $table) { 650 if ($query !== null) { 651 // It may be an aliased Zend_Db_Expr 652 $desiredColumns = $query->getColumns(); 653 if (isset($desiredColumns[$name]) && $desiredColumns[$name] instanceof Zend_Db_Expr) { 654 return; 655 } 656 } 657 658 throw new ProgrammingError('Column name validation seems to have failed. Did you require the column?'); 659 } 660 } 661 662 return parent::getConverter($table, $name, $context, $query); 663 } 664 665 /** 666 * Validate that the requested table exists 667 * 668 * This will prepend the datasource's table prefix and will apply the table's alias, if any. 669 * 670 * @param string $table The table to validate 671 * @param RepositoryQuery $query An optional query to pass as context 672 * (unused by the base implementation) 673 * 674 * @return array|string 675 * 676 * @throws ProgrammingError In case the given table does not exist 677 */ 678 public function requireTable($table, RepositoryQuery $query = null) 679 { 680 $virtualTable = null; 681 $statementColumns = $this->getStatementColumns(); 682 if (! isset($statementColumns[$table])) { 683 $newTable = parent::requireTable($table); 684 if ($newTable !== $table) { 685 $virtualTable = $table; 686 } 687 688 $table = $newTable; 689 } else { 690 $virtualTables = $this->getVirtualTables(); 691 if (isset($virtualTables[$table])) { 692 $virtualTable = $table; 693 $table = $virtualTables[$table]; 694 } 695 } 696 697 return $this->prependTablePrefix($this->applyTableAlias($table, $virtualTable)); 698 } 699 700 /** 701 * Return the alias for the given table or null if none has been defined 702 * 703 * @param string $table 704 * 705 * @return string|null 706 */ 707 public function resolveTableAlias($table) 708 { 709 $tableAliases = $this->getTableAliases(); 710 if (isset($tableAliases[$table])) { 711 return $tableAliases[$table]; 712 } 713 } 714 715 /** 716 * Return the alias for the given query column name or null in case the query column name does not exist 717 * 718 * @param string $table 719 * @param string $column 720 * 721 * @return string|null 722 */ 723 public function reassembleQueryColumnAlias($table, $column) 724 { 725 $alias = parent::reassembleQueryColumnAlias($table, $column); 726 if ($alias === null 727 && !$this->validateQueryColumnAssociation($table, $column) 728 && ($tableName = $this->findTableName($column, $table)) 729 ) { 730 return parent::reassembleQueryColumnAlias($tableName, $column); 731 } 732 733 return $alias; 734 } 735 736 /** 737 * Validate that the given column is a valid query target and return it or the actual name if it's an alias 738 * 739 * Attempts to join the given column from a different table if its association to the given table cannot be 740 * verified. 741 * 742 * @param string $table The table where to look for the column or alias 743 * @param string $name The name or alias of the column to validate 744 * @param RepositoryQuery $query An optional query to pass as context, 745 * if not given no join will be attempted 746 * 747 * @return string The given column's name 748 * 749 * @throws QueryException In case the given column is not a valid query column 750 * @throws ProgrammingError In case the given column is not found in $table and cannot be joined in 751 */ 752 public function requireQueryColumn($table, $name, RepositoryQuery $query = null) 753 { 754 if ($name instanceof Zend_Db_Expr) { 755 return $name; 756 } 757 758 if ($query === null || $this->validateQueryColumnAssociation($table, $name)) { 759 return parent::requireQueryColumn($table, $name, $query); 760 } 761 762 $column = $this->joinColumn($name, $table, $query); 763 if ($column === null) { 764 if ($query !== null) { 765 // It may be an aliased Zend_Db_Expr 766 $desiredColumns = $query->getColumns(); 767 if (isset($desiredColumns[$name]) && $desiredColumns[$name] instanceof Zend_Db_Expr) { 768 $column = $desiredColumns[$name]; 769 } 770 } 771 772 if ($column === null) { 773 throw new ProgrammingError( 774 'Unable to find a valid table for column "%s" to join into "%s"', 775 $name, 776 $table 777 ); 778 } 779 } 780 781 return $column; 782 } 783 784 /** 785 * Validate that the given column is a valid filter target and return it or the actual name if it's an alias 786 * 787 * Attempts to join the given column from a different table if its association to the given table cannot be 788 * verified. In case of a PostgreSQL connection and if a COLLATE SQL-instruction is part of the resolved column, 789 * this applies LOWER() on the column and, if given, strtolower() on the filter's expression. 790 * 791 * @param string $table The table where to look for the column or alias 792 * @param string $name The name or alias of the column to validate 793 * @param RepositoryQuery $query An optional query to pass as context, 794 * if not given the column is considered being used for a statement filter 795 * @param FilterExpression $filter An optional filter to pass as context 796 * 797 * @return string The given column's name 798 * 799 * @throws QueryException In case the given column is not a valid filter column 800 * @throws ProgrammingError In case the given column is not found in $table and cannot be joined in 801 */ 802 public function requireFilterColumn($table, $name, RepositoryQuery $query = null, FilterExpression $filter = null) 803 { 804 if ($name instanceof Zend_Db_Expr) { 805 return $name; 806 } 807 808 $joined = false; 809 if ($query === null) { 810 $column = $this->requireStatementColumn($table, $name); 811 } elseif ($this->validateQueryColumnAssociation($table, $name)) { 812 $column = parent::requireFilterColumn($table, $name, $query, $filter); 813 } else { 814 $column = $this->joinColumn($name, $table, $query); 815 if ($column === null) { 816 if ($query !== null) { 817 // It may be an aliased Zend_Db_Expr 818 $desiredColumns = $query->getColumns(); 819 if (isset($desiredColumns[$name]) && $desiredColumns[$name] instanceof Zend_Db_Expr) { 820 $column = $desiredColumns[$name]; 821 } 822 } 823 824 if ($column === null) { 825 throw new ProgrammingError( 826 'Unable to find a valid table for column "%s" to join into "%s"', 827 $name, 828 $table 829 ); 830 } 831 } else { 832 $joined = true; 833 } 834 } 835 836 if (! empty($this->caseInsensitiveColumns)) { 837 if ($joined) { 838 $table = $this->findTableName($name, $table); 839 } 840 841 if ($column === $name) { 842 if ($query === null) { 843 $name = $this->reassembleStatementColumnAlias($table, $name); 844 } else { 845 $name = $this->reassembleQueryColumnAlias($table, $name); 846 } 847 } 848 849 if (isset($this->caseInsensitiveColumns[$table][$name])) { 850 $column = 'LOWER(' . $column . ')'; 851 if ($filter !== null) { 852 $expression = $filter->getExpression(); 853 if (is_array($expression)) { 854 $filter->setExpression(array_map('strtolower', $expression)); 855 } else { 856 $filter->setExpression(strtolower($expression)); 857 } 858 } 859 } 860 } 861 862 return $column; 863 } 864 865 /** 866 * Return the statement column name for the given alias or null in case the alias does not exist 867 * 868 * @param string $table 869 * @param string $alias 870 * 871 * @return string|null 872 */ 873 public function resolveStatementColumnAlias($table, $alias) 874 { 875 $statementAliasColumnMap = $this->getStatementAliasColumnMap(); 876 if (isset($statementAliasColumnMap[$alias])) { 877 return $statementAliasColumnMap[$alias]; 878 } 879 880 $prefixedAlias = $table . '.' . $alias; 881 if (isset($statementAliasColumnMap[$prefixedAlias])) { 882 return $statementAliasColumnMap[$prefixedAlias]; 883 } 884 } 885 886 /** 887 * Return the alias for the given statement column name or null in case the statement column does not exist 888 * 889 * @param string $table 890 * @param string $column 891 * 892 * @return string|null 893 */ 894 public function reassembleStatementColumnAlias($table, $column) 895 { 896 $statementColumnAliasMap = $this->getStatementColumnAliasMap(); 897 if (isset($statementColumnAliasMap[$column])) { 898 return $statementColumnAliasMap[$column]; 899 } 900 901 $prefixedColumn = $table . '.' . $column; 902 if (isset($statementColumnAliasMap[$prefixedColumn])) { 903 return $statementColumnAliasMap[$prefixedColumn]; 904 } 905 } 906 907 /** 908 * Return whether the given alias or statement column name is available in the given table 909 * 910 * @param string $table 911 * @param string $alias 912 * 913 * @return bool 914 */ 915 public function validateStatementColumnAssociation($table, $alias) 916 { 917 $statementAliasTableMap = $this->getStatementAliasTableMap(); 918 if (isset($statementAliasTableMap[$alias])) { 919 return $statementAliasTableMap[$alias] === $table; 920 } 921 922 $prefixedAlias = $table . '.' . $alias; 923 if (isset($statementAliasTableMap[$prefixedAlias])) { 924 return true; 925 } 926 927 $statementColumnTableMap = $this->getStatementColumnTableMap(); 928 if (isset($statementColumnTableMap[$alias])) { 929 return $statementColumnTableMap[$alias] === $table; 930 } 931 932 return isset($statementColumnTableMap[$prefixedAlias]); 933 } 934 935 /** 936 * Return whether the given column name or alias of the given table is a valid statement column 937 * 938 * @param string $table The table where to look for the column or alias 939 * @param string $name The column name or alias to check 940 * 941 * @return bool 942 */ 943 public function hasStatementColumn($table, $name) 944 { 945 if (($this->resolveStatementColumnAlias($table, $name) === null 946 && $this->reassembleStatementColumnAlias($table, $name) === null) 947 || !$this->validateStatementColumnAssociation($table, $name) 948 ) { 949 return parent::hasStatementColumn($table, $name); 950 } 951 952 return true; 953 } 954 955 /** 956 * Validate that the given column is a valid statement column and return it or the actual name if it's an alias 957 * 958 * @param string $table The table for which to require the column 959 * @param string $name The name or alias of the column to validate 960 * 961 * @return string The given column's name 962 * 963 * @throws StatementException In case the given column is not a statement column 964 */ 965 public function requireStatementColumn($table, $name) 966 { 967 if (($column = $this->resolveStatementColumnAlias($table, $name)) !== null) { 968 $alias = $name; 969 } elseif (($alias = $this->reassembleStatementColumnAlias($table, $name)) !== null) { 970 $column = $name; 971 } else { 972 return parent::requireStatementColumn($table, $name); 973 } 974 975 if (! $this->validateStatementColumnAssociation($table, $alias)) { 976 throw new StatementException('Statement column "%s" not found in table "%s"', $name, $table); 977 } 978 979 return $column; 980 } 981 982 /** 983 * Join alias or column $name into $table using $query 984 * 985 * Attempts to find a valid table for the given alias or column name and a method labelled join<TableName> 986 * to process the actual join logic. If neither of those is found, null is returned. 987 * The method is called with the same parameters but in reversed order. 988 * 989 * @param string $name The alias or column name to join into $target 990 * @param string $target The table to join $name into 991 * @param RepositoryQUery $query The query to apply the JOIN-clause on 992 * 993 * @return string|null The resolved alias or $name, null if no join logic is found 994 */ 995 public function joinColumn($name, $target, RepositoryQuery $query) 996 { 997 if (! ($tableName = $this->findTableName($name, $target))) { 998 return; 999 } 1000 1001 if (($column = $this->resolveQueryColumnAlias($tableName, $name)) === null) { 1002 $column = $name; 1003 } 1004 1005 if (($joinIdentifier = $this->resolveTableAlias($tableName)) === null) { 1006 $joinIdentifier = $this->prependTablePrefix($tableName); 1007 } 1008 if ($query->getQuery()->hasJoinedTable($joinIdentifier)) { 1009 return $column; 1010 } 1011 1012 $joinMethod = 'join' . StringHelper::cname($tableName); 1013 if (! method_exists($this, $joinMethod)) { 1014 throw new ProgrammingError( 1015 'Unable to join table "%s" into "%s". Method "%s" not found', 1016 $tableName, 1017 $target, 1018 $joinMethod 1019 ); 1020 } 1021 1022 $this->$joinMethod($query, $target, $name); 1023 return $column; 1024 } 1025 1026 /** 1027 * Return the table name for the given alias or column name 1028 * 1029 * @param string $column The alias or column name 1030 * @param string $origin The base table of a SELECT query 1031 * 1032 * @return string|null null in case no table is found 1033 */ 1034 protected function findTableName($column, $origin) 1035 { 1036 // First, try to produce an exact match since it's faster and cheaper 1037 $aliasTableMap = $this->getAliasTableMap(); 1038 if (isset($aliasTableMap[$column])) { 1039 $table = $aliasTableMap[$column]; 1040 } else { 1041 $columnTableMap = $this->getColumnTableMap(); 1042 if (isset($columnTableMap[$column])) { 1043 $table = $columnTableMap[$column]; 1044 } 1045 } 1046 1047 // But only return it if it's a probable join... 1048 $joinProbabilities = $this->getJoinProbabilities(); 1049 if (isset($joinProbabilities[$origin])) { 1050 $probableJoins = $joinProbabilities[$origin]; 1051 } 1052 1053 // ...if probability can be determined 1054 if (isset($table) && (empty($probableJoins) || in_array($table, $probableJoins, true))) { 1055 return $table; 1056 } 1057 1058 // Without a proper exact match, there is only one fast and cheap way to find a suitable table.. 1059 if (! empty($probableJoins)) { 1060 foreach ($probableJoins as $table) { 1061 if (isset($aliasTableMap[$table . '.' . $column])) { 1062 return $table; 1063 } 1064 } 1065 } 1066 1067 // Last chance to find a table. Though, this usually ends up with a QueryException.. 1068 foreach ($aliasTableMap as $prefixedAlias => $table) { 1069 if (strpos($prefixedAlias, '.') !== false) { 1070 list($_, $alias) = explode('.', $prefixedAlias, 2); 1071 if ($alias === $column) { 1072 return $table; 1073 } 1074 } 1075 } 1076 } 1077} 1078