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