1: <?php
   2: /**
   3:  * This file contains the generic db item collection class.
   4:  *
   5:  * @package Core
   6:  * @subpackage GenericDB
   7:  * @version SVN Revision $Rev:$
   8:  *
   9:  * @author Timo Hummel
  10:  * @author Murat Purc <murat@purc.de>
  11:  * @copyright four for business AG <www.4fb.de>
  12:  * @license http://www.contenido.org/license/LIZENZ.txt
  13:  * @link http://www.4fb.de
  14:  * @link http://www.contenido.org
  15:  */
  16: 
  17: defined('CON_FRAMEWORK') || die('Illegal call: Missing framework initialization - request aborted.');
  18: 
  19: /**
  20:  * Class ItemCollection
  21:  * Abstract class for database based item collections.
  22:  *
  23:  * @package Core
  24:  * @subpackage GenericDB
  25:  */
  26: abstract class ItemCollection extends cItemBaseAbstract {
  27: 
  28:     /**
  29:      * Storage of all result items.
  30:      * Contains all result items.
  31:      *
  32:      * @var string
  33:      */
  34:     protected $objects;
  35: 
  36:     /**
  37:      * GenericDB driver object
  38:      *
  39:      * @var cGenericDbDriver
  40:      */
  41:     protected $_driver;
  42: 
  43:     /**
  44:      * List of instances of ItemCollection implementations
  45:      *
  46:      * @var array
  47:      */
  48:     protected $_collectionCache = array();
  49: 
  50:     /**
  51:      * Single item class
  52:      *
  53:      * @var string
  54:      */
  55:     protected $_itemClass;
  56: 
  57:     /**
  58:      * Iterator object for the next() method
  59:      *
  60:      * @var object
  61:      */
  62:     protected $_iteratorItem;
  63: 
  64:     /**
  65:      * Reverse join partners for this data object
  66:      *
  67:      * @var array
  68:      */
  69:     protected $_JoinPartners = array();
  70: 
  71:     /**
  72:      * Forward join partners for this data object
  73:      *
  74:      * @var array
  75:      */
  76:     protected $_forwardJoinPartners;
  77: 
  78:     /**
  79:      * Where restrictions for the query
  80:      *
  81:      * @var array
  82:      */
  83:     protected $_whereRestriction;
  84: 
  85:     /**
  86:      * Inner group conditions
  87:      *
  88:      * @var array
  89:      */
  90:     protected $_innerGroupConditions = array();
  91: 
  92:     /**
  93:      * Group conditions
  94:      *
  95:      * @var array
  96:      */
  97:     protected $_groupConditions;
  98: 
  99:     /**
 100:      * Result fields for the query
 101:      *
 102:      * @var array
 103:      */
 104:     protected $_resultFields = array();
 105: 
 106:     /**
 107:      * Encoding
 108:      *
 109:      * @var string
 110:      */
 111:     protected $_encoding;
 112: 
 113:     /**
 114:      * Item class instance
 115:      *
 116:      * @var object
 117:      */
 118:     protected $_itemClassInstance;
 119: 
 120:     /**
 121:      * Stores all operators which are supported by GenericDB
 122:      * Unsupported operators are passed trough as-is.
 123:      *
 124:      * @var array
 125:      */
 126:     protected $_aOperators;
 127: 
 128:     /**
 129:      * Flag to select all fields in a query.
 130:      * Reduces the number of queries send to the database.
 131:      *
 132:      * @var bool
 133:      */
 134:     protected $_bAllMode = false;
 135: 
 136:     /**
 137:      * Array with where conditions
 138:      *
 139:      * @var array
 140:      */
 141:     protected $_where;
 142: 
 143:     /**
 144:      * Order mode with direction
 145:      *
 146:      * @var string
 147:      */
 148:     protected $_order;
 149: 
 150:     /**
 151:      * Starting limit
 152:      *
 153:      * @var int
 154:      */
 155:     protected $_limitStart;
 156: 
 157:     /**
 158:      * Amount of items for limit
 159:      *
 160:      * @var int
 161:      */
 162:     protected $_limitCount;
 163: 
 164:     /**
 165:      * Last SQL statement
 166:      *
 167:      * @var string
 168:      */
 169:     protected $_lastSQL;
 170: 
 171:     /**
 172:      * Array with linked tables
 173:      *
 174:      * @var array
 175:      */
 176:     protected $_links;
 177: 
 178:     /**
 179:      * Constructor Function
 180:      *
 181:      * @param string $sTable The table to use as information source
 182:      * @param string $sPrimaryKey The primary key to use
 183:      */
 184:     public function __construct($sTable, $sPrimaryKey) {
 185:         parent::__construct($sTable, $sPrimaryKey, get_parent_class($this));
 186: 
 187:         $this->resetQuery();
 188: 
 189:         // Try to load driver
 190:         $this->_initializeDriver();
 191: 
 192:         // Try to find out the current encoding
 193:         if (isset($GLOBALS['lang']) && isset($GLOBALS['aLanguageEncodings'])) {
 194:             $this->setEncoding($GLOBALS['aLanguageEncodings'][$GLOBALS['lang']]);
 195:         }
 196: 
 197:         $this->_aOperators = array(
 198:             '=',
 199:             '!=',
 200:             '<>',
 201:             '<',
 202:             '>',
 203:             '<=',
 204:             '>=',
 205:             'LIKE',
 206:             'DIACRITICS'
 207:         );
 208:     }
 209: 
 210:     /**
 211:      * Defines the reverse links for this table.
 212:      *
 213:      * Important: The class specified by $sForeignCollectionClass needs to be a
 214:      * collection class and has to exist.
 215:      * Define all links in the constructor of your object.
 216:      *
 217:      * @param string $sForeignCollectionClass Specifies the foreign class to use
 218:      * @throws cInvalidArgumentException if the given foreign class can not be
 219:      *         instantiated
 220:      */
 221:     protected function _setJoinPartner($sForeignCollectionClass) {
 222:         if (class_exists($sForeignCollectionClass)) {
 223:             // Add class
 224:             if (!in_array($sForeignCollectionClass, $this->_JoinPartners)) {
 225:                 $this->_JoinPartners[] = strtolower($sForeignCollectionClass);
 226:             }
 227:         } else {
 228:             $msg = "Could not instantiate class [$sForeignCollectionClass] for use " . "with _setJoinPartner in class " . get_class($this);
 229:             throw new cInvalidArgumentException($msg);
 230:         }
 231:     }
 232: 
 233:     /**
 234:      * Method to set the accompanying item object.
 235:      *
 236:      * @param string $sClassName Specifies the classname of item
 237:      * @throws cInvalidArgumentException if the given class can not be
 238:      *         instantiated
 239:      */
 240:     protected function _setItemClass($sClassName) {
 241:         if (class_exists($sClassName)) {
 242:             $this->_itemClass = $sClassName;
 243:             $this->_itemClassInstance = new $sClassName();
 244: 
 245:             // Initialize driver in case the developer does a setItemClass-Call
 246:             // before calling the parent constructor
 247:             $this->_initializeDriver();
 248:             $this->_driver->setItemClassInstance($this->_itemClassInstance);
 249:         } else {
 250:             $msg = "Could not instantiate class [$sClassName] for use with " . "_setItemClass in class " . get_class($this);
 251:             throw new cInvalidArgumentException($msg);
 252:         }
 253:     }
 254: 
 255:     /**
 256:      * Initializes the driver to use with GenericDB.
 257:      *
 258:      * @param $bForceInit boolean If true, forces the driver to initialize, even
 259:      *        if it already exists.
 260:      */
 261:     protected function _initializeDriver($bForceInit = false) {
 262:         if (!is_object($this->_driver) || $bForceInit == true) {
 263:             $this->_driver = new cGenericDbDriverMysql();
 264:         }
 265:     }
 266: 
 267:     /**
 268:      * Sets the encoding.
 269:      *
 270:      * @param string $sEncoding
 271:      */
 272:     public function setEncoding($sEncoding) {
 273:         $this->_encoding = $sEncoding;
 274:         $this->_driver->setEncoding($sEncoding);
 275:     }
 276: 
 277:     /**
 278:      * Sets the query to use foreign tables in the resultset
 279:      *
 280:      * @param string $sForeignClass The class of foreign table to use
 281:      * @throws cInvalidArgumentException if the given foreign class does not
 282:      *         exist
 283:      */
 284:     public function link($sForeignClass) {
 285:         if (class_exists($sForeignClass)) {
 286:             $this->_links[$sForeignClass] = new $sForeignClass();
 287:         } else {
 288:             $msg = "Could not find class [$sForeignClass] for use with link in class " . get_class($this);
 289:             throw new cInvalidArgumentException($msg);
 290:         }
 291:     }
 292: 
 293:     /**
 294:      * Sets the limit for results
 295:      *
 296:      * @param int $iRowStart
 297:      * @param int $iRowCount
 298:      */
 299:     public function setLimit($iRowStart, $iRowCount) {
 300:         $this->_limitStart = $iRowStart;
 301:         $this->_limitCount = $iRowCount;
 302:     }
 303: 
 304:     /**
 305:      * Restricts a query with a where clause
 306:      *
 307:      * @param string $sField
 308:      * @param mixed $mRestriction
 309:      * @param string $sOperator
 310:      */
 311:     public function setWhere($sField, $mRestriction, $sOperator = '=') {
 312:         $sField = strtolower($sField);
 313:         $this->_where['global'][$sField]['operator'] = $sOperator;
 314:         $this->_where['global'][$sField]['restriction'] = $mRestriction;
 315:     }
 316: 
 317:     /**
 318:      * Removes a previous set where clause (@see ItemCollection::setWhere).
 319:      *
 320:      * @param string $sField
 321:      * @param mixed $mRestriction
 322:      * @param string $sOperator
 323:      */
 324:     public function deleteWhere($sField, $mRestriction, $sOperator = '=') {
 325:         $sField = strtolower($sField);
 326:         if (isset($this->_where['global'][$sField]) && is_array($this->_where['global'][$sField])) {
 327:             if ($this->_where['global'][$sField]['operator'] == $sOperator && $this->_where['global'][$sField]['restriction'] == $mRestriction) {
 328:                 unset($this->_where['global'][$sField]);
 329:             }
 330:         }
 331:     }
 332: 
 333:     /**
 334:      * Restricts a query with a where clause, groupable
 335:      *
 336:      * @param string $sGroup
 337:      * @param string $sField
 338:      * @param mixed $mRestriction
 339:      * @param string $sOperator
 340:      */
 341:     public function setWhereGroup($sGroup, $sField, $mRestriction, $sOperator = '=') {
 342:         $sField = strtolower($sField);
 343:         $this->_where['groups'][$sGroup][$sField]['operator'] = $sOperator;
 344:         $this->_where['groups'][$sGroup][$sField]['restriction'] = $mRestriction;
 345:     }
 346: 
 347:     /**
 348:      * Removes a previous set groupable where clause (@see
 349:      * ItemCollection::setWhereGroup).
 350:      *
 351:      * @param string $sGroup
 352:      * @param string $sField
 353:      * @param mixed $mRestriction
 354:      * @param string $sOperator
 355:      */
 356:     public function deleteWhereGroup($sGroup, $sField, $mRestriction, $sOperator = '=') {
 357:         $sField = strtolower($sField);
 358:         if (is_array($this->_where['groups'][$sGroup]) && isset($this->_where['groups'][$sGroup][$sField]) && is_array($this->_where['groups'][$sGroup][$sField])) {
 359:             if ($this->_where['groups'][$sGroup][$sField]['operator'] == $sOperator && $this->_where['groups'][$sGroup][$sField]['restriction'] == $mRestriction) {
 360:                 unset($this->_where['groups'][$sGroup][$sField]);
 361:             }
 362:         }
 363:     }
 364: 
 365:     /**
 366:      * Defines how relations in one group are linked each together
 367:      *
 368:      * @param string $sGroup
 369:      * @param string $sCondition
 370:      */
 371:     public function setInnerGroupCondition($sGroup, $sCondition = 'AND') {
 372:         $this->_innerGroupConditions[$sGroup] = $sCondition;
 373:     }
 374: 
 375:     /**
 376:      * Defines how groups are linked to each other
 377:      *
 378:      * @param string $sGroup1
 379:      * @param string $sGroup2
 380:      * @param string $sCondition
 381:      */
 382:     public function setGroupCondition($sGroup1, $sGroup2, $sCondition = 'AND') {
 383:         $this->_groupConditions[$sGroup1][$sGroup2] = $sCondition;
 384:     }
 385: 
 386:     /**
 387:      * Builds a where statement out of the setGroupWhere calls
 388:      *
 389:      * @return array With all where statements
 390:      */
 391:     protected function _buildGroupWhereStatements() {
 392:         $aWheres = array();
 393:         $aGroupWhere = array();
 394: 
 395:         $mLastGroup = false;
 396:         $sGroupWhereStatement = '';
 397: 
 398:         // Find out if there are any defined groups
 399:         if (count($this->_where['groups']) > 0) {
 400:             // Step trough all groups
 401:             foreach ($this->_where['groups'] as $groupname => $group) {
 402:                 $aWheres = array();
 403: 
 404:                 // Fetch restriction, fields and operators and build single
 405:                 // group
 406:                 // where statements
 407:                 foreach ($group as $field => $item) {
 408:                     $aWheres[] = $this->_driver->buildOperator($field, $item['operator'], $item['restriction']);
 409:                 }
 410: 
 411:                 // Add completed substatements
 412:                 $sOperator = 'AND';
 413:                 if (isset($this->_innerGroupConditions[$groupname])) {
 414:                     $sOperator = $this->_innerGroupConditions[$groupname];
 415:                 }
 416: 
 417:                 $aGroupWhere[$groupname] = implode(' ' . $sOperator . ' ', $aWheres);
 418:             }
 419:         }
 420: 
 421:         // Combine groups
 422:         foreach ($aGroupWhere as $groupname => $group) {
 423:             if ($mLastGroup != false) {
 424:                 $sOperator = 'AND';
 425:                 // Check if there's a group condition
 426:                 if (isset($this->_groupConditions[$groupname])) {
 427:                     if (isset($this->_groupConditions[$groupname][$mLastGroup])) {
 428:                         $sOperator = $this->_groupConditions[$groupname][$mLastGroup];
 429:                     }
 430:                 }
 431: 
 432:                 // Reverse check
 433:                 if (isset($this->_groupConditions[$mLastGroup])) {
 434:                     if (isset($this->_groupConditions[$mLastGroup][$groupname])) {
 435:                         $sOperator = $this->_groupConditions[$mLastGroup][$groupname];
 436:                     }
 437:                 }
 438: 
 439:                 $sGroupWhereStatement .= ' ' . $sOperator . ' (' . $group . ')';
 440:             } else {
 441:                 $sGroupWhereStatement .= '(' . $group . ')';
 442:             }
 443: 
 444:             $mLastGroup = $groupname;
 445:         }
 446: 
 447:         return $sGroupWhereStatement;
 448:     }
 449: 
 450:     /**
 451:      * Builds a where statement out of the setWhere calls
 452:      *
 453:      * @return array With all where statements
 454:      */
 455:     protected function _buildWhereStatements() {
 456:         $aWheres = array();
 457: 
 458:         // Build global where condition
 459:         foreach ($this->_where['global'] as $field => $item) {
 460:             $aWheres[] = $this->_driver->buildOperator($field, $item['operator'], $item['restriction']);
 461:         }
 462: 
 463:         return (implode(' AND ', $aWheres));
 464:     }
 465: 
 466:     /**
 467:      * Fetches all tables which will be joined later on.
 468:      *
 469:      * The returned array has the following format:
 470:      * <pre>
 471:      * array(
 472:      * array(fields),
 473:      * array(tables),
 474:      * array(joins),
 475:      * array(wheres)
 476:      * );
 477:      * </pre>
 478:      *
 479:      * Notes:
 480:      * The table is the table name which needs to be added to the FROM clause
 481:      * The join statement which is inserted after the master table
 482:      * The where statement is combined with all other where statements
 483:      * The fields to select from
 484:      *
 485:      * @throws cException if no join partner could be found
 486:      * @return array Array structure, see above
 487:      */
 488:     protected function _fetchJoinTables() {
 489:         $aParameters = array();
 490:         $aFields = array();
 491:         $aTables = array();
 492:         $aJoins = array();
 493:         $aWheres = array();
 494: 
 495:         // Fetch linked tables
 496:         foreach ($this->_links as $link => $object) {
 497:             $matches = $this->_findReverseJoinPartner(strtolower(get_class($this)), $link);
 498:             if ($matches !== false) {
 499:                 if (isset($matches['desttable'])) {
 500:                     // Driver function: Build query parts
 501:                     $aParameters[] = $this->_driver->buildJoinQuery($matches['desttable'], strtolower($matches['destclass']), $matches['key'], strtolower($matches['sourceclass']), $matches['key']);
 502:                 } else {
 503:                     foreach ($matches as $match) {
 504:                         $aParameters[] = $this->_driver->buildJoinQuery($match['desttable'], strtolower($match['destclass']), $match['key'], strtolower($match['sourceclass']), $match['key']);
 505:                     }
 506:                 }
 507:             } else {
 508:                 throw new cUnexpectedValueException("The join partner '" . get_class($this) . "' is not registered and can not be used with link().");
 509:             }
 510:         }
 511: 
 512:         // Add this class
 513:         $aFields[] = strtolower(strtolower(get_class($this))) . '.' . $this->primaryKey;
 514: 
 515:         // Make the parameters unique
 516:         foreach ($aParameters as $parameter) {
 517:             array_unshift($aFields, $parameter['field']);
 518:             array_unshift($aTables, $parameter['table']);
 519:             array_unshift($aJoins, $parameter['join']);
 520:             array_unshift($aWheres, $parameter['where']);
 521:         }
 522: 
 523:         $aFields = array_filter(array_unique($aFields));
 524:         $aTables = array_filter(array_unique($aTables));
 525:         $aJoins = array_filter(array_unique($aJoins));
 526:         $aWheres = array_filter(array_unique($aWheres));
 527: 
 528:         return array(
 529:             'fields' => $aFields,
 530:             'tables' => $aTables,
 531:             'joins' => $aJoins,
 532:             'wheres' => $aWheres
 533:         );
 534:     }
 535: 
 536:     /**
 537:      * Resolves links (class names of joined partners)
 538:      *
 539:      * @return array
 540:      */
 541:     protected function _resolveLinks() {
 542:         $aResolvedLinks = array();
 543:         $aResolvedLinks[] = strtolower(get_class($this));
 544: 
 545:         foreach ($this->_JoinPartners as $link) {
 546:             $class = new $link();
 547:             $aResolvedLinks = array_merge($class->_resolveLinks(), $aResolvedLinks);
 548:         }
 549:         return $aResolvedLinks;
 550:     }
 551: 
 552:     /**
 553:      * Resets the properties
 554:      */
 555:     public function resetQuery() {
 556:         $this->setLimit(0, 0);
 557:         $this->_forwardJoinPartners = array();
 558:         $this->_links = array();
 559:         $this->_where['global'] = array();
 560:         $this->_where['groups'] = array();
 561:         $this->_groupConditions = array();
 562:         $this->_resultFields = array();
 563:     }
 564: 
 565:     /**
 566:      * Builds and runs the query
 567:      *
 568:      * @throws cException if no item class has been set
 569:      * @return bool
 570:      */
 571:     public function query() {
 572:         if (!isset($this->_itemClassInstance)) {
 573:             throw new cException('GenericDB can\'t use query() if no item class is set via setItemClass');
 574:         }
 575: 
 576:         $aGroupWhereStatements = $this->_buildGroupWhereStatements();
 577:         $sWhereStatements = $this->_buildWhereStatements();
 578:         $aParameters = $this->_fetchJoinTables();
 579: 
 580:         $aStatement = array(
 581:             'SELECT',
 582:             implode(', ', (array_merge($aParameters['fields'], $this->_resultFields))),
 583:             'FROM',
 584:             '`' . $this->table . '` AS ' . strtolower(get_class($this))
 585:         );
 586: 
 587:         if (count($aParameters['tables']) > 0) {
 588:             $aStatement[] = implode(', ', $aParameters['tables']);
 589:         }
 590: 
 591:         if (count($aParameters['joins']) > 0) {
 592:             $aStatement[] = implode(' ', $aParameters['joins']);
 593:         }
 594: 
 595:         $aWheres = array();
 596: 
 597:         if (count($aParameters['wheres']) > 0) {
 598:             $aWheres[] = implode(', ', $aParameters['wheres']);
 599:         }
 600: 
 601:         if ($aGroupWhereStatements != '') {
 602:             $aWheres[] = $aGroupWhereStatements;
 603:         }
 604: 
 605:         if ($sWhereStatements != '') {
 606:             $aWheres[] = $sWhereStatements;
 607:         }
 608: 
 609:         if (count($aWheres) > 0) {
 610:             $aStatement[] = 'WHERE ' . implode(' AND ', $aWheres);
 611:         }
 612: 
 613:         if ($this->_order != '') {
 614:             $aStatement[] = 'ORDER BY ' . $this->_order;
 615:         }
 616: 
 617:         if ($this->_limitStart > 0 || $this->_limitCount > 0) {
 618:             $iRowStart = intval($this->_limitStart);
 619:             $iRowCount = intval($this->_limitCount);
 620:             $aStatement[] = "LIMIT $iRowStart, $iRowCount";
 621:         }
 622: 
 623:         $sql = implode(' ', $aStatement);
 624: 
 625:         $result = $this->db->query($sql);
 626:         $this->_lastSQL = $sql;
 627:         // @todo disable all mode in this method for the moment. It has to be
 628:         // verified,
 629:         // if enabling will result in negative side effects.
 630:         $this->_bAllMode = false;
 631:         return ($result) ? true : false;
 632:     }
 633: 
 634:     /**
 635:      * Sets the result order part of the query
 636:      * (e.
 637:      * g. "fieldname", "fieldname DESC", "fieldname DESC, field2name ASC")
 638:      *
 639:      * @param string $order
 640:      */
 641:     public function setOrder($order) {
 642:         $this->_order = strtolower($order);
 643:     }
 644: 
 645:     /**
 646:      * Adds a result field
 647:      *
 648:      * @param string $sField
 649:      */
 650:     public function addResultField($sField) {
 651:         $sField = strtolower($sField);
 652:         if (!in_array($sField, $this->_resultFields)) {
 653:             $this->_resultFields[] = $sField;
 654:         }
 655:     }
 656: 
 657:     /**
 658:      * Removes existing result field
 659:      *
 660:      * @param string $sField
 661:      */
 662:     public function removeResultField($sField) {
 663:         $sField = strtolower($sField);
 664:         $key = array_search($sField, $this->_resultFields);
 665:         if ($key !== false) {
 666:             unset($this->_resultFields[$key]);
 667:         }
 668:     }
 669: 
 670:     /**
 671:      * Returns reverse join partner.
 672:      *
 673:      * @param string $sParentClass
 674:      * @param string $sClassName
 675:      * @param array|bool
 676:      */
 677:     protected function _findReverseJoinPartner($sParentClass, $sClassName) {
 678:         // Make the parameters lowercase, as get_class is buggy
 679:         $sClassName = strtolower($sClassName);
 680:         $sParentClass = strtolower($sParentClass);
 681: 
 682:         // Check if we found a direct link
 683:         if (in_array($sClassName, $this->_JoinPartners)) {
 684:             $obj = new $sClassName();
 685:             return array(
 686:                 'desttable' => $obj->table,
 687:                 'destclass' => $sClassName,
 688:                 'sourceclass' => $sParentClass,
 689:                 'key' => $obj->primaryKey
 690:             );
 691:         } else {
 692:             // Recurse all items
 693:             foreach ($this->_JoinPartners as $join => $tmpClassname) {
 694:                 $obj = new $tmpClassname();
 695:                 $status = $obj->_findReverseJoinPartner($tmpClassname, $sClassName);
 696: 
 697:                 if (is_array($status)) {
 698:                     $returns = array();
 699: 
 700:                     if (!isset($status['desttable'])) {
 701:                         foreach ($status as $subitem) {
 702:                             $returns[] = $subitem;
 703:                         }
 704:                     } else {
 705:                         $returns[] = $status;
 706:                     }
 707: 
 708:                     $returns[] = array(
 709:                         'desttable' => $obj->table,
 710:                         'destclass' => $tmpClassname,
 711:                         'sourceclass' => $sParentClass,
 712:                         'key' => $obj->primaryKey
 713:                     );
 714:                     return ($returns);
 715:                 }
 716:             }
 717:         }
 718:         return false;
 719:     }
 720: 
 721:     /**
 722:      * Selects all entries from the database.
 723:      * Objects are loaded using their primary key.
 724:      *
 725:      * @param string $sWhere Specifies the where clause.
 726:      * @param string $sGroupBy Specifies the group by clause.
 727:      * @param string $sOrderBy Specifies the order by clause.
 728:      * @param string $sLimit Specifies the limit by clause.
 729:      * @return bool True on success, otherwhise false
 730:      */
 731:     public function select($sWhere = '', $sGroupBy = '', $sOrderBy = '', $sLimit = '') {
 732:         unset($this->objects);
 733: 
 734:         if ($sWhere == '') {
 735:             $sWhere = '';
 736:         } else {
 737:             $sWhere = ' WHERE ' . $sWhere;
 738:         }
 739: 
 740:         if ($sGroupBy != '') {
 741:             $sGroupBy = ' GROUP BY ' . $sGroupBy;
 742:         }
 743: 
 744:         if ($sOrderBy != '') {
 745:             $sOrderBy = ' ORDER BY ' . $sOrderBy;
 746:         }
 747: 
 748:         if ($sLimit != '') {
 749:             $sLimit = ' LIMIT ' . $sLimit;
 750:         }
 751: 
 752:         $sFields = ($this->_settings['select_all_mode']) ? '*' : $this->primaryKey;
 753:         $sql = 'SELECT ' . $sFields . ' FROM `' . $this->table . '`' . $sWhere . $sGroupBy . $sOrderBy . $sLimit;
 754:         $this->db->query($sql);
 755:         $this->_lastSQL = $sql;
 756:         $this->_bAllMode = $this->_settings['select_all_mode'];
 757: 
 758:         if ($this->db->numRows() == 0) {
 759:             return false;
 760:         } else {
 761:             return true;
 762:         }
 763:     }
 764: 
 765:     /**
 766:      * Selects all entries from the database.
 767:      * Objects are loaded using their primary key.
 768:      *
 769:      * @param string $sDistinct Specifies if distinct will be added to the SQL
 770:      *        statement ($sDistinct !== '' -> DISTINCT)
 771:      * @param string $sFrom Specifies the additional from clause (e.g.
 772:      *        'con_news_groups AS groups, con_news_groupmembers AS
 773:      *        groupmembers').
 774:      * @param string $sWhere Specifies the where clause.
 775:      * @param string $sGroupBy Specifies the group by clause.
 776:      * @param string $sOrderBy Specifies the order by clause.
 777:      * @param string $sLimit Specifies the limit by clause.
 778:      * @return bool True on success, otherwhise false
 779:      */
 780:     public function flexSelect($sDistinct = '', $sFrom = '', $sWhere = '', $sGroupBy = '', $sOrderBy = '', $sLimit = '') {
 781:         unset($this->objects);
 782: 
 783:         if ($sDistinct != '') {
 784:             $sDistinct = 'DISTINCT ';
 785:         }
 786: 
 787:         if ($sFrom != '') {
 788:             $sFrom = ', ' . $sFrom;
 789:         }
 790: 
 791:         if ($sWhere != '') {
 792:             $sWhere = ' WHERE ' . $sWhere;
 793:         }
 794: 
 795:         if ($sGroupBy != '') {
 796:             $sGroupBy = ' GROUP BY ' . $sGroupBy;
 797:         }
 798: 
 799:         if ($sOrderBy != '') {
 800:             $sOrderBy = ' ORDER BY ' . $sOrderBy;
 801:         }
 802: 
 803:         if ($sLimit != '') {
 804:             $sLimit = ' LIMIT ' . $sLimit;
 805:         }
 806: 
 807:         $sql = 'SELECT ' . $sDistinct . strtolower(get_class($this)) . '.' . $this->primaryKey . ' AS ' . $this->primaryKey . ' FROM `' . $this->table . '` AS ' . strtolower(get_class($this)) . $sFrom . $sWhere . $sGroupBy . $sOrderBy . $sLimit;
 808: 
 809:         $this->db->query($sql);
 810:         $this->_lastSQL = $sql;
 811:         // @todo disable all mode in this method
 812:         $this->_bAllMode = false;
 813: 
 814:         if ($this->db->numRows() == 0) {
 815:             return false;
 816:         } else {
 817:             return true;
 818:         }
 819:     }
 820: 
 821:     /**
 822:      * Checks if a specific entry exists.
 823:      *
 824:      * @param mixed $mId The id to check for (could be numeric or string)
 825:      * @return bool True if object exists, false if not
 826:      */
 827:     public function exists($mId) {
 828:         $oDb = $this->_getSecondDBInstance();
 829:         $sql = "SELECT `%s` FROM %s WHERE %s='%s'";
 830:         $oDb->query($sql, $this->primaryKey, $this->table, $this->primaryKey, $mId);
 831:         return ($oDb->nextRecord()) ? true : false;
 832:     }
 833: 
 834:     /**
 835:      * Advances to the next item in the database.
 836:      *
 837:      * @return Item bool next object, or false if no more objects
 838:      */
 839:     public function next() {
 840:         $ret = false;
 841:         while ($this->db->nextRecord()) {
 842:             if ($this->_bAllMode) {
 843:                 $aRs = $this->db->toArray(cDb::FETCH_BOTH);
 844:                 $ret = $this->loadItem($aRs);
 845:             } else {
 846:                 $ret = $this->loadItem($this->db->f($this->primaryKey));
 847:             }
 848: 
 849:             if ($ret->get($this->primaryKey) == "") {
 850:                 continue;
 851:             } else {
 852:                 break;
 853:             }
 854:         }
 855:         return $ret;
 856:     }
 857: 
 858:     /**
 859:      * Fetches the resultset related to current loaded primary key as an object
 860:      *
 861:      * @param string $sClassName
 862:      */
 863:     public function fetchObject($sClassName) {
 864:         $sKey = strtolower($sClassName);
 865: 
 866:         if (!is_object($this->_collectionCache[$sKey])) {
 867:             $this->_collectionCache[$sKey] = new $sClassName();
 868:         }
 869:         $obj = $this->_collectionCache[$sKey];
 870:         return $obj->loadItem($this->db->f($obj->primaryKey));
 871:     }
 872: 
 873:     /**
 874:      * Notes:
 875:      * If the array contains keys, the key will be used as alias for the field.
 876:      * Example: array('id' => 'idcat') will put 'idcat' into field 'id'
 877:      * $aObjects = array with the objects to fetch. Notes: If the array contains
 878:      * keys, the key will be used as alias for the object. If you specify more
 879:      * than one object with the same key, the array will be multi-dimensional.
 880:      *
 881:      * @param array $aFields array with the fields to fetch
 882:      * @param array $aObjects
 883:      * @return array
 884:      */
 885:     public function fetchTable(array $aFields = array(), array $aObjects = array()) {
 886:         $row = 1;
 887:         $aTable = array();
 888: 
 889:         $this->db->seek(0);
 890: 
 891:         while ($this->db->nextRecord()) {
 892:             foreach ($aFields as $alias => $field) {
 893:                 if ($alias != '') {
 894:                     $aTable[$row][$alias] = $this->db->f($field);
 895:                 } else {
 896:                     $aTable[$row][$field] = $this->db->f($field);
 897:                 }
 898:             }
 899: 
 900:             // Fetch objects
 901:             foreach ($aObjects as $alias => $object) {
 902:                 if ($alias != '') {
 903:                     if (isset($aTable[$row][$alias])) {
 904:                         // Is set, check for array. If no array, create one
 905:                         if (is_array($aTable[$row][$alias])) {
 906:                             $aTable[$row][$alias][] = $this->fetchObject($object);
 907:                         } else {
 908:                             // $tmpObj = $aTable[$row][$alias];
 909:                             $aTable[$row][$alias] = array();
 910:                             $aTable[$row][$alias][] = $this->fetchObject($object);
 911:                         }
 912:                     } else {
 913:                         $aTable[$row][$alias] = $this->fetchObject($object);
 914:                     }
 915:                 } else {
 916:                     $aTable[$row][$object] = $this->fetchObject($object);
 917:                 }
 918:             }
 919:             $row++;
 920:         }
 921: 
 922:         $this->db->seek(0);
 923: 
 924:         return $aTable;
 925:     }
 926: 
 927:     /**
 928:      * Returns an array of arrays
 929:      *
 930:      * @param array $aObjects With the correct order of the objects
 931:      * @return array Result
 932:      */
 933:     public function queryAndFetchStructured(array $aObjects) {
 934:         $aOrder = array();
 935:         $aFetchObjects = array();
 936:         $aResult = array();
 937: 
 938:         foreach ($aObjects as $object) {
 939:             $x = new $object();
 940:             $object = strtolower($object);
 941:             $aOrder[] = $object . '.' . $x->primaryKey . ' ASC';
 942:             $aFetchObjects[] = $x;
 943:         }
 944: 
 945:         $this->setOrder(implode(', ', $aOrder));
 946:         $this->query();
 947: 
 948:         $this->db->seek(0);
 949: 
 950:         while ($this->db->nextRecord()) {
 951:             $aResult = $this->_recursiveStructuredFetch($aFetchObjects, $aResult);
 952:         }
 953: 
 954:         return $aResult;
 955:     }
 956: 
 957:     /**
 958:      *
 959:      * @param array $aObjects
 960:      * @param array $aResult
 961:      * @return array
 962:      */
 963:     protected function _recursiveStructuredFetch(array $aObjects, array $aResult) {
 964:         $i = array_shift($aObjects);
 965: 
 966:         $value = $this->db->f($i->primaryKey);
 967: 
 968:         if (!is_null($value)) {
 969:             $aResult[$value]['class'] = strtolower(get_class($i));
 970:             $aResult[$value]['object'] = $i->loadItem($value);
 971: 
 972:             if (count($aObjects) > 0) {
 973:                 $aResult[$value]['items'] = $this->_recursiveStructuredFetch($aObjects, $aResult[$value]['items']);
 974:             }
 975:         }
 976: 
 977:         return $aResult;
 978:     }
 979: 
 980:     /**
 981:      * Returns the amount of returned items
 982:      *
 983:      * @return int Number of rows
 984:      */
 985:     public function count() {
 986:         return ($this->db->numRows());
 987:     }
 988: 
 989:     /**
 990:      * Loads a single entry by it's id.
 991:      *
 992:      * @param string|int $id The primary key of the item to load.
 993:      * @return Item The loaded item
 994:      */
 995:     public function fetchById($id) {
 996:         if (is_numeric($id)) {
 997:             $id = (int) $id;
 998:         } elseif (is_string($id)) {
 999:             $id = $this->escape($id);
1000:         }
1001:         return $this->loadItem($id);
1002:     }
1003: 
1004:     /**
1005:      * Loads a single object from the database.
1006:      *
1007:      * @param mixed $mItem The primary key of the item to load or a recordset
1008:      *        with itemdata (array) to inject to the item object.
1009:      * @throws cException If item class is not set
1010:      * @return Item The newly created object
1011:      */
1012:     public function loadItem($mItem) {
1013:         if (empty($this->_itemClass)) {
1014:             $sMsg = "ItemClass has to be set in the constructor of class " . get_class($this) . ")";
1015:             throw new cException($sMsg);
1016:         }
1017: 
1018:         if (!is_object($this->_iteratorItem)) {
1019:             $this->_iteratorItem = new $this->_itemClass();
1020:         }
1021:         $obj = clone $this->_iteratorItem;
1022: 
1023:         if (is_array($mItem)) {
1024:             $obj->loadByRecordSet($mItem);
1025:         } else {
1026:             $obj->loadByPrimaryKey($mItem);
1027:         }
1028: 
1029:         return $obj;
1030:     }
1031: 
1032:     /**
1033:      * Creates a new item in the table and loads it afterwards.
1034:      *
1035:      * @param string|array $data optional parameter for direct input of
1036:      *        primary key value (string) or multiple column name - value pairs
1037:      * @return Item The newly created object
1038:      */
1039:     public function createNewItem($data = NULL) {
1040:         $this->_executeCallbacks(self::CREATE_BEFORE, get_class($this), array());
1041: 
1042:         $db = $this->_getSecondDBInstance();
1043: 
1044:         $primaryKeyValue = NULL;
1045:         // prepare the primary key value and the data depending on the type of
1046:         // $data
1047:         if (is_array($data)) {
1048:             if (array_key_exists($this->primaryKey, $data)) {
1049:                 $primaryKeyValue = $data[$this->primaryKey];
1050:             }
1051:         } else {
1052:             // data is the primary key
1053:             $primaryKeyValue = $data;
1054:             $data = array(
1055:                 $this->primaryKey => $data
1056:             );
1057:         }
1058: 
1059:         // build the insert statement and execute it
1060:         $sql = $db->buildInsert($this->table, $data);
1061:         $db->query($sql);
1062: 
1063:         if ($primaryKeyValue === NULL) {
1064:             $primaryKeyValue = $db->getLastInsertedId($this->table);
1065:         }
1066: 
1067:         if ($db->affectedRows() == 0) {
1068:             $this->_executeCallbacks(self::CREATE_FAILURE, $this->_itemClass, array());
1069:         } else {
1070:             $this->_executeCallbacks(self::CREATE_SUCCESS, $this->_itemClass, array(
1071:                 $primaryKeyValue
1072:             ));
1073:         }
1074: 
1075:         return $this->loadItem($primaryKeyValue);
1076:     }
1077: 
1078:     /**
1079:      * Inserts a new item entry by using a existing item entry.
1080:      *
1081:      * @param object $srcItem Source Item instance to copy
1082:      * @param array $fieldsToOverwrite Assoziative list of fields to overwrite.
1083:      * @throws cInvalidArgumentException If Item class doesn't match the defined
1084:      *         _itemClass property
1085:      *         or passed Item instance has no loaded recordset
1086:      * @return object Item NULL
1087:      */
1088:     public function copyItem($srcItem, array $fieldsToOverwrite = array()) {
1089:         if (get_class($srcItem) !== $this->_itemClass) {
1090:             throw new cInvalidArgumentException("Item class doesn't match");
1091:         } elseif (!$srcItem->isLoaded()) {
1092:             throw new cInvalidArgumentException("Item instance has no loaded recordset");
1093:         }
1094: 
1095:         $destItem = self::createNewItem();
1096:         if (!is_object($destItem)) {
1097:             return NULL;
1098:         }
1099: 
1100:         $rs = $srcItem->toArray();
1101: 
1102:         foreach ($rs as $field => $value) {
1103:             if (is_numeric($field)) {
1104:                 // Skip index based field
1105:                 continue;
1106:             } elseif ($field == $this->primaryKey) {
1107:                 // Skip primary key
1108:                 continue;
1109:             }
1110: 
1111:             if (isset($fieldsToOverwrite[$field])) {
1112:                 $value = $fieldsToOverwrite[$field];
1113:             }
1114: 
1115:             $destItem->set($field, $value);
1116:         }
1117: 
1118:         $destItem->store();
1119:         return $destItem;
1120:     }
1121: 
1122:     /**
1123:      * Returns all ids of recordsets in the table matching the rules in the
1124:      * passed where clause.
1125:      *
1126:      * @param string $sWhere The where clause of the SQL statement
1127:      * @return array List of ids
1128:      */
1129:     public function getIdsByWhereClause($sWhere) {
1130:         $oDb = $this->_getSecondDBInstance();
1131: 
1132:         $aIds = array();
1133: 
1134:         // Get all ids
1135:         $sql = 'SELECT ' . $this->primaryKey . ' AS pk FROM `' . $this->table . '` WHERE ' . $sWhere;
1136:         $oDb->query($sql);
1137:         while ($oDb->nextRecord()) {
1138:             $aIds[] = $oDb->f('pk');
1139:         }
1140: 
1141:         return $aIds;
1142:     }
1143: 
1144:     /**
1145:      * Returns all specified fields of recordsets in the table matching the
1146:      * rules in the passed where clause.
1147:      *
1148:      * @param array $aFields List of fields to get
1149:      * @param string $sWhere The where clause of the SQL statement
1150:      * @return array List of entries with specified fields
1151:      */
1152:     public function getFieldsByWhereClause(array $aFields, $sWhere) {
1153:         $oDb = $this->_getSecondDBInstance();
1154: 
1155:         $aEntries = array();
1156: 
1157:         if (count($aFields) == 0) {
1158:             return $aEntries;
1159:         }
1160: 
1161:         // Delete multiple db entries at once
1162:         $aEscapedFields = array_map(array(
1163:             $oDb,
1164:             'escape'
1165:         ), $aFields);
1166: 
1167:         $fields = implode(', ', $aEscapedFields);
1168: 
1169:         // Get all fields
1170:         $sql = 'SELECT ' . $fields . ' FROM `' . $this->table . '` WHERE ' . $sWhere;
1171:         $oDb->query($sql);
1172:         while ($oDb->nextRecord()) {
1173:             $data = array();
1174:             foreach ($aFields as $field) {
1175:                 $data[$field] = $oDb->f($field);
1176:             }
1177:             $aEntries[] = $data;
1178:         }
1179: 
1180:         return $aEntries;
1181:     }
1182: 
1183:     /**
1184:      * Returns all ids of recordsets in the table.
1185:      *
1186:      * @return array List of ids
1187:      */
1188:     public function getAllIds() {
1189:         $oDb = $this->_getSecondDBInstance();
1190: 
1191:         $aIds = array();
1192: 
1193:         // Get all ids
1194:         $sql = 'SELECT ' . $this->primaryKey . ' AS pk FROM `' . $this->table . '`';
1195:         $oDb->query($sql);
1196:         while ($oDb->nextRecord()) {
1197:             $aIds[] = $oDb->f('pk');
1198:         }
1199: 
1200:         return $aIds;
1201:     }
1202: 
1203:     /**
1204:      * Deletes an item in the table.
1205:      * Deletes also cached e entry and any existing properties.
1206:      *
1207:      * @param mixed $mId Id of entry to delete
1208:      * @return bool
1209:      */
1210:     public function delete($mId) {
1211:         $result = $this->_delete($mId);
1212:         return $result;
1213:     }
1214: 
1215:     /**
1216:      * Deletes all found items in the table matching the rules in the passed
1217:      * where clause.
1218:      * Deletes also cached e entries and any existing properties.
1219:      *
1220:      * @param string $sWhere The where clause of the SQL statement
1221:      * @return int Number of deleted entries
1222:      */
1223:     public function deleteByWhereClause($sWhere) {
1224:         // Get all ids and delete related entries
1225:         $aIds = $this->getIdsByWhereClause($sWhere);
1226:         $numDeleted = $this->_deleteMultiple($aIds);
1227:         return $numDeleted;
1228:     }
1229: 
1230:     /**
1231:      * Deletes all found items in the table matching the passed field and it's
1232:      * value.
1233:      * Deletes also cached e entries and any existing properties.
1234:      *
1235:      * @param string $sField The field name
1236:      * @param mixed $mValue The value of the field
1237:      * @return int Number of deleted entries
1238:      */
1239:     public function deleteBy($sField, $mValue) {
1240:         $where = (is_string($mValue)) ? "`%s` = '%s'" : "`%s` = %d";
1241:         $where = $this->db->prepare($where, $sField, $mValue);
1242: 
1243:         return $this->deleteByWhereClause($where);
1244:     }
1245: 
1246:     // TODO
1247:     // /**
1248:     // * Deletes all found items in the table matching the passed field and it's
1249:     // value.
1250:     // * Deletes also cached e entries and any existing properties.
1251:     // *
1252:     // * @param mixed $mValue The value of the field
1253:     // * @return bool
1254:     // */
1255:     // public function deleteByMany($values) {
1256:     // }
1257: 
1258:     /**
1259:      * Deletes an item in the table, deletes also existing cache entries and
1260:      * properties of the item.
1261:      *
1262:      * @param mixed $mId Id of entry to delete
1263:      * @return bool
1264:      */
1265:     protected function _delete($mId) {
1266:         $this->_executeCallbacks(self::DELETE_BEFORE, $this->_itemClass, array(
1267:             $mId
1268:         ));
1269: 
1270:         $oDb = $this->_getSecondDBInstance();
1271: 
1272:         // delete db entry
1273:         $sql = "DELETE FROM `%s` WHERE %s = '%s'";
1274:         $oDb->query($sql, $this->table, $this->primaryKey, $mId);
1275:         $success = $oDb->affectedRows();
1276: 
1277:         // delete cache entry
1278:         $this->_oCache->removeItem($mId);
1279: 
1280:         // delete the property values
1281:         $oProperties = $this->_getPropertiesCollectionInstance();
1282:         $oProperties->deleteProperties($this->primaryKey, $mId);
1283: 
1284:         if ($success == 0) {
1285:             $this->_executeCallbacks(self::DELETE_FAILURE, $this->_itemClass, array(
1286:                 $mId
1287:             ));
1288:             return false;
1289:         } else {
1290:             $this->_executeCallbacks(self::DELETE_SUCCESS, $this->_itemClass, array(
1291:                 $mId
1292:             ));
1293:             return true;
1294:         }
1295:     }
1296: 
1297:     /**
1298:      * Deletes all items in the table, deletes also existing cache entries and
1299:      * properties of the item.
1300:      *
1301:      * @param array $aIds Id of entries to delete
1302:      * @return int Number of affected records
1303:      */
1304:     protected function _deleteMultiple(array $aIds) {
1305:         foreach ($aIds as $mId) {
1306:             $this->_executeCallbacks(self::DELETE_BEFORE, $this->_itemClass, array(
1307:                 $mId
1308:             ));
1309:         }
1310: 
1311:         $oDb = $this->_getSecondDBInstance();
1312: 
1313:         // Delete multiple db entries at once
1314:         $aEscapedIds = array_map(array(
1315:             $oDb,
1316:             'escape'
1317:         ), $aIds);
1318:         $in = "'" . implode("', '", $aEscapedIds) . "'";
1319:         $sql = "DELETE FROM `%s` WHERE %s IN (" . $in . ")";
1320:         $oDb->query($sql, $this->table, $this->primaryKey);
1321:         $numAffected = $oDb->affectedRows();
1322: 
1323:         // Delete cache entries
1324:         $this->_oCache->removeItems($aIds);
1325: 
1326:         // Delete the property values
1327:         $oProperties = $this->_getPropertiesCollectionInstance();
1328:         $oProperties->deletePropertiesMultiple($this->primaryKey, $aIds);
1329: 
1330:         // NOTE: Deleteing multiple entries at once has a drawback. There is no
1331:         // way to detect faulty ids, if one or more entries couldn't deleted.
1332:         if ($numAffected == 0) {
1333:             foreach ($aIds as $mId) {
1334:                 $this->_executeCallbacks(self::DELETE_FAILURE, $this->_itemClass, array(
1335:                     $mId
1336:                 ));
1337:             }
1338:         } else {
1339:             foreach ($aIds as $mId) {
1340:                 $this->_executeCallbacks(self::DELETE_SUCCESS, $this->_itemClass, array(
1341:                     $mId
1342:                 ));
1343:             }
1344:         }
1345:         return $numAffected;
1346:     }
1347: 
1348:     /**
1349:      * Fetches an array of fields from the database.
1350:      *
1351:      * Example:
1352:      * $i = $object->fetchArray('idartlang', array('idlang', 'name'));
1353:      *
1354:      * could result in:
1355:      * $i[5] = array('idlang' => 5, 'name' => 'My Article');
1356:      *
1357:      * Important: If you don't pass an array for fields, the function
1358:      * doesn't create an array.
1359:      *
1360:      * @param string $sKey Name of the field to use for the key
1361:      * @param mixed $mFields String or array
1362:      * @return array Resulting array
1363:      */
1364:     public function fetchArray($sKey, $mFields) {
1365:         $aResult = array();
1366: 
1367:         while (($item = $this->next()) !== false) {
1368:             if (is_array($mFields)) {
1369:                 foreach ($mFields as $value) {
1370:                     $aResult[$item->get($sKey)][$value] = $item->get($value);
1371:                 }
1372:             } else {
1373:                 $aResult[$item->get($sKey)] = $item->get($mFields);
1374:             }
1375:         }
1376: 
1377:         return $aResult;
1378:     }
1379: }
1380: