Packages

  • Nette
    • Application
      • Diagnostics
      • Responses
      • Routers
      • UI
    • Caching
      • Storages
    • ComponentModel
    • Config
      • Adapters
      • Extensions
    • Database
      • Diagnostics
      • Drivers
      • Reflection
      • Table
    • DI
      • Diagnostics
    • Diagnostics
    • Forms
      • Controls
      • Rendering
    • Http
    • Iterators
    • Latte
      • Macros
    • Loaders
    • Localization
    • Mail
    • Reflection
    • Security
      • Diagnostics
    • Templating
    • Utils
      • PhpGenerator
  • NetteModule
  • None
  • PHP

Classes

  • GroupedTableSelection
  • SqlBuilder
  • TableRow
  • TableSelection
  • Overview
  • Package
  • Class
  • Tree
  1: <?php
  2: 
  3: /**
  4:  * This file is part of the Nette Framework (http://nette.org)
  5:  *
  6:  * Copyright (c) 2004 David Grudl (http://davidgrudl.com)
  7:  *
  8:  * For the full copyright and license information, please view
  9:  * the file license.txt that was distributed with this source code.
 10:  * @package Nette\Database\Table
 11:  */
 12: 
 13: 
 14: 
 15: /**
 16:  * Filtered table representation.
 17:  * Selection is based on the great library NotORM http://www.notorm.com written by Jakub Vrana.
 18:  *
 19:  * @author     Jakub Vrana
 20:  * @author     Jan Skrasek
 21:  *
 22:  * @property-read string $sql
 23:  * @package Nette\Database\Table
 24:  */
 25: class TableSelection extends Object implements Iterator, ArrayAccess, Countable
 26: {
 27:     /** @var Connection */
 28:     protected $connection;
 29: 
 30:     /** @var SqlBuilder */
 31:     protected $sqlBuilder;
 32: 
 33:     /** @var string table name */
 34:     protected $name;
 35: 
 36:     /** @var string primary key field name */
 37:     protected $primary;
 38: 
 39:     /** @var TableRow[] data read from database in [primary key => ActiveRow] format */
 40:     protected $rows;
 41: 
 42:     /** @var TableRow[] modifiable data in [primary key => ActiveRow] format */
 43:     protected $data;
 44: 
 45:     /** @var TableSelection[] */
 46:     protected $referenced = array();
 47: 
 48:     /** @var array of [sqlQuery-hash => grouped data]; used by GroupedSelection */
 49:     protected $referencing = array();
 50: 
 51:     /** @var GroupedTableSelection[] cached array of GroupedSelection prototypes */
 52:     protected $referencingPrototype = array();
 53: 
 54:     /** @var array of [conditions => [key => ActiveRow]]; used by GroupedSelection */
 55:     protected $aggregation = array();
 56: 
 57:     /** @var array of touched columns */
 58:     protected $accessed;
 59: 
 60:     /** @var array of earlier touched columns */
 61:     protected $prevAccessed;
 62: 
 63:     /** @var bool should instance observe accessed columns caching */
 64:     protected $observeCache = FALSE;
 65: 
 66:     /** @var bool recheck referencing keys */
 67:     protected $checkReferenced = FALSE;
 68: 
 69:     /** @var array of primary key values */
 70:     protected $keys = array();
 71: 
 72: 
 73: 
 74:     /**
 75:      * Creates filtered table representation.
 76:      * @param  string  database table name
 77:      * @param  Connection
 78:      */
 79:     public function __construct($table, Connection $connection)
 80:     {
 81:         $this->name = $table;
 82:         $this->connection = $connection;
 83:         $this->primary = $connection->getDatabaseReflection()->getPrimary($table);
 84:         $this->sqlBuilder = new SqlBuilder($this);
 85:     }
 86: 
 87: 
 88: 
 89:     public function __destruct()
 90:     {
 91:         $this->saveCacheState();
 92:     }
 93: 
 94: 
 95: 
 96:     public function __clone()
 97:     {
 98:         $this->sqlBuilder = clone $this->sqlBuilder;
 99:         $this->sqlBuilder->setSelection($this);
100:     }
101: 
102: 
103: 
104:     /**
105:      * @return Connection
106:      */
107:     public function getConnection()
108:     {
109:         return $this->connection;
110:     }
111: 
112: 
113: 
114:     /**
115:      * @return string
116:      */
117:     public function getName()
118:     {
119:         return $this->name;
120:     }
121: 
122: 
123: 
124:     /**
125:      * @return string
126:      */
127:     public function getPrimary()
128:     {
129:         return $this->primary;
130:     }
131: 
132: 
133: 
134:     /**
135:      * @return string
136:      */
137:     public function getSql()
138:     {
139:         return $this->sqlBuilder->buildSelectQuery();
140:     }
141: 
142: 
143: 
144:     /**
145:      * Loads cache of previous accessed columns and returns it
146:      * @internal
147:      * @return array|false
148:      */
149:     public function getPreviousAccessed()
150:     {
151:         $cache = $this->connection->getCache();
152:         if ($this->rows === NULL && $cache && !is_string($this->prevAccessed)) {
153:             $this->accessed = $this->prevAccessed = $cache->load(array(__CLASS__, $this->name, $this->sqlBuilder->getConditions()));
154:         }
155: 
156:         return $this->prevAccessed;
157:     }
158: 
159: 
160: 
161:     /**
162:      * @internal
163:      * @return SqlBuilder
164:      */
165:     public function getSqlBuilder()
166:     {
167:         return $this->sqlBuilder;
168:     }
169: 
170: 
171: 
172:     /********************* quick access ****************d*g**/
173: 
174: 
175: 
176:     /**
177:      * Returns row specified by primary key.
178:      * @param  mixed primary key
179:      * @return TableRow or FALSE if there is no such row
180:      */
181:     public function get($key)
182:     {
183:         $clone = clone $this;
184:         $clone->where($this->primary, $key);
185:         return $clone->fetch();
186:     }
187: 
188: 
189: 
190:     /**
191:      * Returns next row of result.
192:      * @return TableRow or FALSE if there is no row
193:      */
194:     public function fetch()
195:     {
196:         $this->execute();
197:         $return = current($this->data);
198:         next($this->data);
199:         return $return;
200:     }
201: 
202: 
203: 
204:     /**
205:      * Returns all rows as associative array.
206:      * @param  string
207:      * @param  string column name used for an array value or NULL for the whole row
208:      * @return array
209:      */
210:     public function fetchPairs($key, $value = NULL)
211:     {
212:         $return = array();
213:         foreach ($this as $row) {
214:             $return[is_object($row[$key]) ? (string) $row[$key] : $row[$key]] = ($value ? $row[$value] : $row);
215:         }
216:         return $return;
217:     }
218: 
219: 
220: 
221:     /********************* sql selectors ****************d*g**/
222: 
223: 
224: 
225:     /**
226:      * Adds select clause, more calls appends to the end.
227:      * @param  string for example "column, MD5(column) AS column_md5"
228:      * @return TableSelection provides a fluent interface
229:      */
230:     public function select($columns)
231:     {
232:         $this->emptyResultSet();
233:         $this->sqlBuilder->addSelect($columns);
234:         return $this;
235:     }
236: 
237: 
238: 
239:     /**
240:      * Selects by primary key.
241:      * @param  mixed
242:      * @return TableSelection provides a fluent interface
243:      */
244:     public function find($key)
245:     {
246:         return $this->where($this->primary, $key);
247:     }
248: 
249: 
250: 
251:     /**
252:      * Adds where condition, more calls appends with AND.
253:      * @param  string condition possibly containing ?
254:      * @param  mixed
255:      * @param  mixed ...
256:      * @return TableSelection provides a fluent interface
257:      */
258:     public function where($condition, $parameters = array())
259:     {
260:         if (is_array($condition)) { // where(array('column1' => 1, 'column2 > ?' => 2))
261:             foreach ($condition as $key => $val) {
262:                 if (is_int($key)) {
263:                     $this->where($val); // where('full condition')
264:                 } else {
265:                     $this->where($key, $val);   // where('column', 1)
266:                 }
267:             }
268:             return $this;
269:         }
270: 
271:         $args = func_get_args();
272:         if (call_user_func_array(array($this->sqlBuilder, 'addWhere'), $args)) {
273:             $this->emptyResultSet();
274:         }
275: 
276:         return $this;
277:     }
278: 
279: 
280: 
281:     /**
282:      * Adds order clause, more calls appends to the end.
283:      * @param  string for example 'column1, column2 DESC'
284:      * @return TableSelection provides a fluent interface
285:      */
286:     public function order($columns)
287:     {
288:         $this->emptyResultSet();
289:         $this->sqlBuilder->addOrder($columns);
290:         return $this;
291:     }
292: 
293: 
294: 
295:     /**
296:      * Sets limit clause, more calls rewrite old values.
297:      * @param  int
298:      * @param  int
299:      * @return TableSelection provides a fluent interface
300:      */
301:     public function limit($limit, $offset = NULL)
302:     {
303:         $this->emptyResultSet();
304:         $this->sqlBuilder->setLimit($limit, $offset);
305:         return $this;
306:     }
307: 
308: 
309: 
310:     /**
311:      * Sets offset using page number, more calls rewrite old values.
312:      * @param  int
313:      * @param  int
314:      * @return TableSelection provides a fluent interface
315:      */
316:     public function page($page, $itemsPerPage)
317:     {
318:         return $this->limit($itemsPerPage, ($page - 1) * $itemsPerPage);
319:     }
320: 
321: 
322: 
323:     /**
324:      * Sets group clause, more calls rewrite old values.
325:      * @param  string
326:      * @param  string
327:      * @return TableSelection provides a fluent interface
328:      */
329:     public function group($columns, $having = NULL)
330:     {
331:         $this->emptyResultSet();
332:         $this->sqlBuilder->setGroup($columns, $having);
333:         return $this;
334:     }
335: 
336: 
337: 
338:     /********************* aggregations ****************d*g**/
339: 
340: 
341: 
342:     /**
343:      * Executes aggregation function.
344:      * @param  string select call in "FUNCTION(column)" format
345:      * @return string
346:      */
347:     public function aggregation($function)
348:     {
349:         $selection = $this->createSelectionInstance();
350:         $selection->getSqlBuilder()->importConditions($this->getSqlBuilder());
351:         $selection->select($function);
352:         foreach ($selection->fetch() as $val) {
353:             return $val;
354:         }
355:     }
356: 
357: 
358: 
359:     /**
360:      * Counts number of rows.
361:      * @param  string  if it is not provided returns count of result rows, otherwise runs new sql counting query
362:      * @return int
363:      */
364:     public function count($column = NULL)
365:     {
366:         if (!$column) {
367:             $this->execute();
368:             return count($this->data);
369:         }
370:         return $this->aggregation("COUNT($column)");
371:     }
372: 
373: 
374: 
375:     /**
376:      * Returns minimum value from a column.
377:      * @param  string
378:      * @return int
379:      */
380:     public function min($column)
381:     {
382:         return $this->aggregation("MIN($column)");
383:     }
384: 
385: 
386: 
387:     /**
388:      * Returns maximum value from a column.
389:      * @param  string
390:      * @return int
391:      */
392:     public function max($column)
393:     {
394:         return $this->aggregation("MAX($column)");
395:     }
396: 
397: 
398: 
399:     /**
400:      * Returns sum of values in a column.
401:      * @param  string
402:      * @return int
403:      */
404:     public function sum($column)
405:     {
406:         return $this->aggregation("SUM($column)");
407:     }
408: 
409: 
410: 
411:     /********************* internal ****************d*g**/
412: 
413: 
414: 
415:     protected function execute()
416:     {
417:         if ($this->rows !== NULL) {
418:             return;
419:         }
420: 
421:         $this->observeCache = TRUE;
422: 
423:         try {
424:             $result = $this->query($this->sqlBuilder->buildSelectQuery());
425: 
426:         } catch (PDOException $exception) {
427:             if (!$this->sqlBuilder->getSelect() && $this->prevAccessed) {
428:                 $this->prevAccessed = '';
429:                 $this->accessed = array();
430:                 $result = $this->query($this->sqlBuilder->buildSelectQuery());
431:             } else {
432:                 throw $exception;
433:             }
434:         }
435: 
436:         $this->rows = array();
437:         $result->setFetchMode(PDO::FETCH_ASSOC);
438:         foreach ($result as $key => $row) {
439:             $row = $result->normalizeRow($row);
440:             $this->rows[isset($row[$this->primary]) ? $row[$this->primary] : $key] = $this->createRow($row);
441:         }
442:         $this->data = $this->rows;
443: 
444:         if (isset($row[$this->primary]) && !is_string($this->accessed)) {
445:             $this->accessed[$this->primary] = TRUE;
446:         }
447:     }
448: 
449: 
450: 
451:     protected function createRow(array $row)
452:     {
453:         return new TableRow($row, $this);
454:     }
455: 
456: 
457: 
458:     protected function createSelectionInstance($table = NULL)
459:     {
460:         return new TableSelection(($tmp=$table) ? $tmp : $this->name, $this->connection);
461:     }
462: 
463: 
464: 
465:     protected function createGroupedSelectionInstance($table, $column)
466:     {
467:         return new GroupedTableSelection($this, $table, $column);
468:     }
469: 
470: 
471: 
472:     protected function query($query)
473:     {
474:         return $this->connection->queryArgs($query, $this->sqlBuilder->getParameters());
475:     }
476: 
477: 
478: 
479:     protected function emptyResultSet()
480:     {
481:         if ($this->rows === NULL) {
482:             return;
483:         }
484: 
485:         $this->rows = NULL;
486:         $this->saveCacheState();
487:     }
488: 
489: 
490: 
491:     protected function saveCacheState()
492:     {
493:         if ($this->observeCache && ($cache = $this->connection->getCache()) && !$this->sqlBuilder->getSelect() && $this->accessed != $this->prevAccessed) {
494:             $cache->save(array(__CLASS__, $this->name, $this->sqlBuilder->getConditions()), $this->accessed);
495:         }
496:     }
497: 
498: 
499: 
500:     /**
501:      * Returns Selection parent for caching
502:      * @return TableSelection
503:      */
504:     protected function getRefTable(& $refPath)
505:     {
506:         return $this;
507:     }
508: 
509: 
510: 
511:     /**
512:      * @internal
513:      * @param  string column name
514:      * @param  bool|NULL TRUE - cache, FALSE - don't cache, NULL - remove
515:      * @return bool
516:      */
517:     public function access($key, $cache = TRUE)
518:     {
519:         if ($cache === NULL) {
520:             if (is_array($this->accessed)) {
521:                 $this->accessed[$key] = FALSE;
522:             }
523:             return FALSE;
524:         }
525: 
526:         if ($key === NULL) {
527:             $this->accessed = '';
528: 
529:         } elseif (!is_string($this->accessed)) {
530:             $this->accessed[$key] = $cache;
531:         }
532: 
533:         if ($cache && !$this->sqlBuilder->getSelect() && $this->prevAccessed && ($key === NULL || !isset($this->prevAccessed[$key]))) {
534:             $this->prevAccessed = '';
535:             $this->emptyResultSet();
536:             return TRUE;
537:         }
538: 
539:         return FALSE;
540:     }
541: 
542: 
543: 
544:     /********************* manipulation ****************d*g**/
545: 
546: 
547: 
548:     /**
549:      * Inserts row in a table.
550:      * @param  mixed array($column => $value)|Traversable for single row insert or Selection|string for INSERT ... SELECT
551:      * @return TableRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
552:      */
553:     public function insert($data)
554:     {
555:         if ($data instanceof TableSelection) {
556:             $data = $data->getSql();
557: 
558:         } elseif ($data instanceof Traversable) {
559:             $data = iterator_to_array($data);
560:         }
561: 
562:         $return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
563:         $this->checkReferenced = TRUE;
564: 
565:         if (!is_array($data)) {
566:             return $return->rowCount();
567:         }
568: 
569:         if (!isset($data[$this->primary]) && ($id = $this->connection->lastInsertId())) {
570:             $data[$this->primary] = $id;
571:             return $this->rows[$id] = $this->createRow($data);
572: 
573:         } else {
574:             return $this->createRow($data);
575: 
576:         }
577:     }
578: 
579: 
580: 
581:     /**
582:      * Updates all rows in result set.
583:      * Joins in UPDATE are supported only in MySQL
584:      * @param  array|Traversable ($column => $value)
585:      * @return int number of affected rows or FALSE in case of an error
586:      */
587:     public function update($data)
588:     {
589:         if ($data instanceof Traversable) {
590:             $data = iterator_to_array($data);
591: 
592:         } elseif (!is_array($data)) {
593:             throw new InvalidArgumentException;
594:         }
595: 
596:         if (!$data) {
597:             return 0;
598:         }
599: 
600:         return $this->connection->queryArgs(
601:             $this->sqlBuilder->buildUpdateQuery(),
602:             array_merge(array($data), $this->sqlBuilder->getParameters())
603:         )->rowCount();
604:     }
605: 
606: 
607: 
608:     /**
609:      * Deletes all rows in result set.
610:      * @return int number of affected rows or FALSE in case of an error
611:      */
612:     public function delete()
613:     {
614:         return $this->query($this->sqlBuilder->buildDeleteQuery())->rowCount();
615:     }
616: 
617: 
618: 
619:     /********************* references ****************d*g**/
620: 
621: 
622: 
623:     /**
624:      * Returns referenced row.
625:      * @param  string
626:      * @param  string
627:      * @param  bool  checks if rows contains the same primary value relations
628:      * @return TableSelection or array() if the row does not exist
629:      */
630:     public function getReferencedTable($table, $column, $checkReferenced = FALSE)
631:     {
632:         $referenced = & $this->getRefTable($refPath)->referenced[$refPath . "$table.$column"];
633:         if ($referenced === NULL || $checkReferenced || $this->checkReferenced) {
634:             $this->execute();
635:             $this->checkReferenced = FALSE;
636:             $keys = array();
637:             foreach ($this->rows as $row) {
638:                 if ($row[$column] === NULL)
639:                     continue;
640: 
641:                 $key = $row[$column] instanceof TableRow ? $row[$column]->getPrimary() : $row[$column];
642:                 $keys[$key] = TRUE;
643:             }
644: 
645:             if ($referenced !== NULL && array_keys($keys) === array_keys($referenced->rows)) {
646:                 return $referenced;
647:             }
648: 
649:             if ($keys) {
650:                 $referenced = $this->createSelectionInstance($table);
651:                 $referenced->where($referenced->primary, array_keys($keys));
652:             } else {
653:                 $referenced = array();
654:             }
655:         }
656: 
657:         return $referenced;
658:     }
659: 
660: 
661: 
662:     /**
663:      * Returns referencing rows.
664:      * @param  string
665:      * @param  string
666:      * @param  int primary key
667:      * @return GroupedTableSelection
668:      */
669:     public function getReferencingTable($table, $column, $active = NULL)
670:     {
671:         $prototype = & $this->getRefTable($refPath)->referencingPrototype[$refPath . "$table.$column"];
672:         if (!$prototype) {
673:             $prototype = $this->createGroupedSelectionInstance($table, $column);
674:             $prototype->where("$table.$column", array_keys((array) $this->rows));
675:         }
676: 
677:         $clone = clone $prototype;
678:         $clone->setActive($active);
679:         return $clone;
680:     }
681: 
682: 
683: 
684:     /********************* interface Iterator ****************d*g**/
685: 
686: 
687: 
688:     public function rewind()
689:     {
690:         $this->execute();
691:         $this->keys = array_keys($this->data);
692:         reset($this->keys);
693:     }
694: 
695: 
696: 
697:     /** @return TableRow */
698:     public function current()
699:     {
700:         return $this->data[current($this->keys)];
701:     }
702: 
703: 
704: 
705:     /**
706:      * @return string row ID
707:      */
708:     public function key()
709:     {
710:         return current($this->keys);
711:     }
712: 
713: 
714: 
715:     public function next()
716:     {
717:         next($this->keys);
718:     }
719: 
720: 
721: 
722:     public function valid()
723:     {
724:         return current($this->keys) !== FALSE;
725:     }
726: 
727: 
728: 
729:     /********************* interface ArrayAccess ****************d*g**/
730: 
731: 
732: 
733:     /**
734:      * Mimic row.
735:      * @param  string row ID
736:      * @param  TableRow
737:      * @return NULL
738:      */
739:     public function offsetSet($key, $value)
740:     {
741:         $this->execute();
742:         $this->data[$key] = $value;
743:     }
744: 
745: 
746: 
747:     /**
748:      * Returns specified row.
749:      * @param  string row ID
750:      * @return TableRow or NULL if there is no such row
751:      */
752:     public function offsetGet($key)
753:     {
754:         $this->execute();
755:         return $this->data[$key];
756:     }
757: 
758: 
759: 
760:     /**
761:      * Tests if row exists.
762:      * @param  string row ID
763:      * @return bool
764:      */
765:     public function offsetExists($key)
766:     {
767:         $this->execute();
768:         return isset($this->data[$key]);
769:     }
770: 
771: 
772: 
773:     /**
774:      * Removes row from result set.
775:      * @param  string row ID
776:      * @return NULL
777:      */
778:     public function offsetUnset($key)
779:     {
780:         $this->execute();
781:         unset($this->data[$key]);
782:     }
783: 
784: }
785: 
Nette Framework 2.0.5 (for PHP 5.2, un-prefixed) API API documentation generated by ApiGen 2.7.0