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