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:         if ($this->count() > 0) {
 890: 
 891:             $this->db->seek(0);
 892: 
 893:             while ($this->db->nextRecord()) {
 894:                 foreach ($aFields as $alias => $field) {
 895:                     if ($alias != '') {
 896:                         $aTable[$row][$alias] = $this->db->f($field);
 897:                     } else {
 898:                         $aTable[$row][$field] = $this->db->f($field);
 899:                     }
 900:                 }
 901: 
 902:                 // Fetch objects
 903:                 foreach ($aObjects as $alias => $object) {
 904:                     if ($alias != '') {
 905:                         if (isset($aTable[$row][$alias])) {
 906:                             // Is set, check for array. If no array, create one
 907:                             if (is_array($aTable[$row][$alias])) {
 908:                                 $aTable[$row][$alias][] = $this->fetchObject($object);
 909:                             } else {
 910:                                 // $tmpObj = $aTable[$row][$alias];
 911:                                 $aTable[$row][$alias] = array();
 912:                                 $aTable[$row][$alias][] = $this->fetchObject($object);
 913:                             }
 914:                         } else {
 915:                             $aTable[$row][$alias] = $this->fetchObject($object);
 916:                         }
 917:                     } else {
 918:                         $aTable[$row][$object] = $this->fetchObject($object);
 919:                     }
 920:                 }
 921:                 $row++;
 922:             }
 923: 
 924:             $this->db->seek(0);
 925: 
 926:         }
 927: 
 928:         return $aTable;
 929:     }
 930: 
 931:     /**
 932:      * Returns an array of arrays
 933:      *
 934:      * @param array $aObjects With the correct order of the objects
 935:      * @return array Result
 936:      */
 937:     public function queryAndFetchStructured(array $aObjects) {
 938:         $aOrder = array();
 939:         $aFetchObjects = array();
 940:         $aResult = array();
 941: 
 942:         foreach ($aObjects as $object) {
 943:             $x = new $object();
 944:             $object = strtolower($object);
 945:             $aOrder[] = $object . '.' . $x->primaryKey . ' ASC';
 946:             $aFetchObjects[] = $x;
 947:         }
 948: 
 949:         $this->setOrder(implode(', ', $aOrder));
 950:         $this->query();
 951: 
 952:         $this->db->seek(0);
 953: 
 954:         while ($this->db->nextRecord()) {
 955:             $aResult = $this->_recursiveStructuredFetch($aFetchObjects, $aResult);
 956:         }
 957: 
 958:         return $aResult;
 959:     }
 960: 
 961:     /**
 962:      *
 963:      * @param array $aObjects
 964:      * @param array $aResult
 965:      * @return array
 966:      */
 967:     protected function _recursiveStructuredFetch(array $aObjects, array $aResult) {
 968:         $i = array_shift($aObjects);
 969: 
 970:         $value = $this->db->f($i->primaryKey);
 971: 
 972:         if (!is_null($value)) {
 973:             $aResult[$value]['class'] = strtolower(get_class($i));
 974:             $aResult[$value]['object'] = $i->loadItem($value);
 975: 
 976:             if (count($aObjects) > 0) {
 977:                 $aResult[$value]['items'] = $this->_recursiveStructuredFetch($aObjects, $aResult[$value]['items']);
 978:             }
 979:         }
 980: 
 981:         return $aResult;
 982:     }
 983: 
 984:     /**
 985:      * Returns the amount of returned items
 986:      *
 987:      * @return int Number of rows
 988:      */
 989:     public function count() {
 990:         return ($this->db->numRows());
 991:     }
 992: 
 993:     /**
 994:      * Loads a single entry by it's id.
 995:      *
 996:      * @param string|int $id The primary key of the item to load.
 997:      * @return Item The loaded item
 998:      */
 999:     public function fetchById($id) {
1000:         if (is_numeric($id)) {
1001:             $id = (int) $id;
1002:         } elseif (is_string($id)) {
1003:             $id = $this->escape($id);
1004:         }
1005:         return $this->loadItem($id);
1006:     }
1007: 
1008:     /**
1009:      * Loads a single object from the database.
1010:      *
1011:      * @param mixed $mItem The primary key of the item to load or a recordset
1012:      *        with itemdata (array) to inject to the item object.
1013:      * @throws cException If item class is not set
1014:      * @return Item The newly created object
1015:      */
1016:     public function loadItem($mItem) {
1017:         if (empty($this->_itemClass)) {
1018:             $sMsg = "ItemClass has to be set in the constructor of class " . get_class($this) . ")";
1019:             throw new cException($sMsg);
1020:         }
1021: 
1022:         if (!is_object($this->_iteratorItem)) {
1023:             $this->_iteratorItem = new $this->_itemClass();
1024:         }
1025:         $obj = clone $this->_iteratorItem;
1026: 
1027:         if (is_array($mItem)) {
1028:             $obj->loadByRecordSet($mItem);
1029:         } else {
1030:             $obj->loadByPrimaryKey($mItem);
1031:         }
1032: 
1033:         return $obj;
1034:     }
1035: 
1036:     /**
1037:      * Creates a new item in the table and loads it afterwards.
1038:      *
1039:      * @param string|array $data optional parameter for direct input of
1040:      *        primary key value (string) or multiple column name - value pairs
1041:      * @return Item The newly created object
1042:      */
1043:     public function createNewItem($data = NULL) {
1044:         $this->_executeCallbacks(self::CREATE_BEFORE, get_class($this), array());
1045: 
1046:         $db = $this->_getSecondDBInstance();
1047: 
1048:         $primaryKeyValue = NULL;
1049:         // prepare the primary key value and the data depending on the type of
1050:         // $data
1051:         if (is_array($data)) {
1052:             if (array_key_exists($this->primaryKey, $data)) {
1053:                 $primaryKeyValue = $data[$this->primaryKey];
1054:             }
1055:         } else {
1056:             // data is the primary key
1057:             $primaryKeyValue = $data;
1058:             $data = array(
1059:                 $this->primaryKey => $data
1060:             );
1061:         }
1062: 
1063:         // build the insert statement and execute it
1064:         $sql = $db->buildInsert($this->table, $data);
1065:         $db->query($sql);
1066: 
1067:         if ($primaryKeyValue === NULL) {
1068:             $primaryKeyValue = $db->getLastInsertedId();
1069:         }
1070: 
1071:         if ($db->affectedRows() == 0) {
1072:             $this->_executeCallbacks(self::CREATE_FAILURE, $this->_itemClass, array());
1073:         } else {
1074:             $this->_executeCallbacks(self::CREATE_SUCCESS, $this->_itemClass, array(
1075:                 $primaryKeyValue
1076:             ));
1077:         }
1078: 
1079:         return $this->loadItem($primaryKeyValue);
1080:     }
1081: 
1082:     /**
1083:      * Inserts a new item entry by using a existing item entry.
1084:      *
1085:      * @param object $srcItem Source Item instance to copy
1086:      * @param array $fieldsToOverwrite Assoziative list of fields to overwrite.
1087:      * @throws cInvalidArgumentException If Item class doesn't match the defined
1088:      *         _itemClass property
1089:      *         or passed Item instance has no loaded recordset
1090:      * @return object Item NULL
1091:      */
1092:     public function copyItem($srcItem, array $fieldsToOverwrite = array()) {
1093:         if (get_class($srcItem) !== $this->_itemClass) {
1094:             throw new cInvalidArgumentException("Item class doesn't match");
1095:         } elseif (!$srcItem->isLoaded()) {
1096:             throw new cInvalidArgumentException("Item instance has no loaded recordset");
1097:         }
1098: 
1099:         $destItem = self::createNewItem();
1100:         if (!is_object($destItem)) {
1101:             return NULL;
1102:         }
1103: 
1104:         $rs = $srcItem->toArray();
1105: 
1106:         foreach ($rs as $field => $value) {
1107:             if (is_numeric($field)) {
1108:                 // Skip index based field
1109:                 continue;
1110:             } elseif ($field == $this->primaryKey) {
1111:                 // Skip primary key
1112:                 continue;
1113:             }
1114: 
1115:             if (isset($fieldsToOverwrite[$field])) {
1116:                 $value = $fieldsToOverwrite[$field];
1117:             }
1118: 
1119:             $destItem->set($field, $value);
1120:         }
1121: 
1122:         $destItem->store();
1123:         return $destItem;
1124:     }
1125: 
1126:     /**
1127:      * Returns all ids of recordsets in the table matching the rules in the
1128:      * passed where clause.
1129:      *
1130:      * @param string $sWhere The where clause of the SQL statement
1131:      * @return array List of ids
1132:      */
1133:     public function getIdsByWhereClause($sWhere) {
1134:         $oDb = $this->_getSecondDBInstance();
1135: 
1136:         $aIds = array();
1137: 
1138:         // Get all ids
1139:         $sql = 'SELECT ' . $this->primaryKey . ' AS pk FROM `' . $this->table . '` WHERE ' . $sWhere;
1140:         $oDb->query($sql);
1141:         while ($oDb->nextRecord()) {
1142:             $aIds[] = $oDb->f('pk');
1143:         }
1144: 
1145:         return $aIds;
1146:     }
1147: 
1148:     /**
1149:      * Returns all specified fields of recordsets in the table matching the
1150:      * rules in the passed where clause.
1151:      *
1152:      * @param array $aFields List of fields to get
1153:      * @param string $sWhere The where clause of the SQL statement
1154:      * @return array List of entries with specified fields
1155:      */
1156:     public function getFieldsByWhereClause(array $aFields, $sWhere) {
1157:         $oDb = $this->_getSecondDBInstance();
1158: 
1159:         $aEntries = array();
1160: 
1161:         if (count($aFields) == 0) {
1162:             return $aEntries;
1163:         }
1164: 
1165:         // Delete multiple db entries at once
1166:         $aEscapedFields = array_map(array(
1167:             $oDb,
1168:             'escape'
1169:         ), $aFields);
1170: 
1171:         $fields = implode(', ', $aEscapedFields);
1172: 
1173:         // Get all fields
1174:         $sql = 'SELECT ' . $fields . ' FROM `' . $this->table . '` WHERE ' . $sWhere;
1175:         $oDb->query($sql);
1176:         while ($oDb->nextRecord()) {
1177:             $data = array();
1178:             foreach ($aFields as $field) {
1179:                 $data[$field] = $oDb->f($field);
1180:             }
1181:             $aEntries[] = $data;
1182:         }
1183: 
1184:         return $aEntries;
1185:     }
1186: 
1187:     /**
1188:      * Returns all ids of recordsets in the table.
1189:      *
1190:      * @return array List of ids
1191:      */
1192:     public function getAllIds() {
1193:         $oDb = $this->_getSecondDBInstance();
1194: 
1195:         $aIds = array();
1196: 
1197:         // Get all ids
1198:         $sql = 'SELECT ' . $this->primaryKey . ' AS pk FROM `' . $this->table . '`';
1199:         $oDb->query($sql);
1200:         while ($oDb->nextRecord()) {
1201:             $aIds[] = $oDb->f('pk');
1202:         }
1203: 
1204:         return $aIds;
1205:     }
1206: 
1207:     /**
1208:      * Deletes an item in the table.
1209:      * Deletes also cached e entry and any existing properties.
1210:      *
1211:      * @param mixed $mId Id of entry to delete
1212:      * @return bool
1213:      */
1214:     public function delete($mId) {
1215:         $result = $this->_delete($mId);
1216:         return $result;
1217:     }
1218: 
1219:     /**
1220:      * Deletes all found items in the table matching the rules in the passed
1221:      * where clause.
1222:      * Deletes also cached e entries and any existing properties.
1223:      *
1224:      * @param string $sWhere The where clause of the SQL statement
1225:      * @return int Number of deleted entries
1226:      */
1227:     public function deleteByWhereClause($sWhere) {
1228:         // Get all ids and delete related entries
1229:         $aIds = $this->getIdsByWhereClause($sWhere);
1230: 
1231:         if (!is_array($aIds) || 0 >= count($aIds)) {
1232:             return 0;
1233:         }
1234: 
1235:         $numDeleted = $this->_deleteMultiple($aIds);
1236:         return $numDeleted;
1237:     }
1238: 
1239:     /**
1240:      * Deletes all found items in the table matching the passed field and it's
1241:      * value.
1242:      * Deletes also cached e entries and any existing properties.
1243:      *
1244:      * @param string $sField The field name
1245:      * @param mixed $mValue The value of the field
1246:      * @return int Number of deleted entries
1247:      */
1248:     public function deleteBy($sField, $mValue) {
1249:         $where = (is_string($mValue)) ? "`%s` = '%s'" : "`%s` = %d";
1250:         $where = $this->db->prepare($where, $sField, $mValue);
1251: 
1252:         return $this->deleteByWhereClause($where);
1253:     }
1254: 
1255:     // TODO
1256:     // /**
1257:     // * Deletes all found items in the table matching the passed field and it's
1258:     // value.
1259:     // * Deletes also cached e entries and any existing properties.
1260:     // *
1261:     // * @param mixed $mValue The value of the field
1262:     // * @return bool
1263:     // */
1264:     // public function deleteByMany($values) {
1265:     // }
1266: 
1267:     /**
1268:      * Deletes an item in the table, deletes also existing cache entries and
1269:      * properties of the item.
1270:      *
1271:      * @param mixed $mId Id of entry to delete
1272:      * @return bool
1273:      */
1274:     protected function _delete($mId) {
1275:         $this->_executeCallbacks(self::DELETE_BEFORE, $this->_itemClass, array(
1276:             $mId
1277:         ));
1278: 
1279:         $oDb = $this->_getSecondDBInstance();
1280: 
1281:         // delete db entry
1282:         $sql = "DELETE FROM `%s` WHERE %s = '%s'";
1283:         $oDb->query($sql, $this->table, $this->primaryKey, $mId);
1284:         $success = $oDb->affectedRows();
1285: 
1286:         // delete cache entry
1287:         $this->_oCache->removeItem($mId);
1288: 
1289:         // delete the property values
1290:         $oProperties = $this->_getPropertiesCollectionInstance();
1291:         $oProperties->deleteProperties($this->primaryKey, $mId);
1292: 
1293:         if ($success == 0) {
1294:             $this->_executeCallbacks(self::DELETE_FAILURE, $this->_itemClass, array(
1295:                 $mId
1296:             ));
1297:             return false;
1298:         } else {
1299:             $this->_executeCallbacks(self::DELETE_SUCCESS, $this->_itemClass, array(
1300:                 $mId
1301:             ));
1302:             return true;
1303:         }
1304:     }
1305: 
1306:     /**
1307:      * Deletes all items in the table, deletes also existing cache entries and
1308:      * properties of the item.
1309:      *
1310:      * @param array $aIds Id of entries to delete (has to be called w/ an array!)
1311:      * @return int Number of affected records
1312:      */
1313:     protected function _deleteMultiple(array $aIds) {
1314:         foreach ($aIds as $mId) {
1315:             $this->_executeCallbacks(self::DELETE_BEFORE, $this->_itemClass, array(
1316:                 $mId
1317:             ));
1318:         }
1319: 
1320:         $oDb = $this->_getSecondDBInstance();
1321: 
1322:         // Delete multiple db entries at once
1323:         $aEscapedIds = array_map(array(
1324:             $oDb,
1325:             'escape'
1326:         ), $aIds);
1327:         $in = "'" . implode("', '", $aEscapedIds) . "'";
1328:         $sql = "DELETE FROM `%s` WHERE %s IN (" . $in . ")";
1329:         $oDb->query($sql, $this->table, $this->primaryKey);
1330:         $numAffected = $oDb->affectedRows();
1331: 
1332:         // Delete cache entries
1333:         $this->_oCache->removeItems($aIds);
1334: 
1335:         // Delete the property values
1336:         $oProperties = $this->_getPropertiesCollectionInstance();
1337:         $oProperties->deletePropertiesMultiple($this->primaryKey, $aIds);
1338: 
1339:         // NOTE: Deleteing multiple entries at once has a drawback. There is no
1340:         // way to detect faulty ids, if one or more entries couldn't deleted.
1341:         if ($numAffected == 0) {
1342:             foreach ($aIds as $mId) {
1343:                 $this->_executeCallbacks(self::DELETE_FAILURE, $this->_itemClass, array(
1344:                     $mId
1345:                 ));
1346:             }
1347:         } else {
1348:             foreach ($aIds as $mId) {
1349:                 $this->_executeCallbacks(self::DELETE_SUCCESS, $this->_itemClass, array(
1350:                     $mId
1351:                 ));
1352:             }
1353:         }
1354:         return $numAffected;
1355:     }
1356: 
1357:     /**
1358:      * Fetches an array of fields from the database.
1359:      *
1360:      * Example:
1361:      * $i = $object->fetchArray('idartlang', array('idlang', 'name'));
1362:      *
1363:      * could result in:
1364:      * $i[5] = array('idlang' => 5, 'name' => 'My Article');
1365:      *
1366:      * Important: If you don't pass an array for fields, the function
1367:      * doesn't create an array.
1368:      *
1369:      * @param string $sKey Name of the field to use for the key
1370:      * @param mixed $mFields String or array
1371:      * @return array Resulting array
1372:      */
1373:     public function fetchArray($sKey, $mFields) {
1374:         $aResult = array();
1375: 
1376:         while (($item = $this->next()) !== false) {
1377:             if (is_array($mFields)) {
1378:                 foreach ($mFields as $value) {
1379:                     $aResult[$item->get($sKey)][$value] = $item->get($value);
1380:                 }
1381:             } else {
1382:                 $aResult[$item->get($sKey)] = $item->get($mFields);
1383:             }
1384:         }
1385: 
1386:         return $aResult;
1387:     }
1388: }
1389: