From 3c8b32baaac62855e2c9f5bfdb5ede9685ce2b76 Mon Sep 17 00:00:00 2001 From: Michael Wallner Date: Sat, 13 Apr 2013 23:28:31 +0200 Subject: [PATCH] added Table::with() --- lib/autoload.php | 2 +- lib/pq/Gateway/Row.php | 14 ++++---- lib/pq/Gateway/Table.php | 45 ++++++++++++++++++++++-- lib/pq/Gateway/Table/LockInterface.php | 5 ++- lib/pq/Gateway/Table/OptimisticLock.php | 4 +-- lib/pq/Gateway/Table/PessimisticLock.php | 2 +- lib/pq/Gateway/Table/Relations.php | 2 +- tests/lib/pq/Gateway/RowTest.php | 3 +- tests/lib/pq/Gateway/TableTest.php | 13 +++++++ tests/setup.inc | 7 +--- 10 files changed, 74 insertions(+), 23 deletions(-) diff --git a/lib/autoload.php b/lib/autoload.php index d7b0cd8..cc5e906 100644 --- a/lib/autoload.php +++ b/lib/autoload.php @@ -1,7 +1,7 @@ getTable()->getLock())) { - $lock->criteria($this, $where); - } return $where; } @@ -262,7 +258,11 @@ class Row implements \JsonSerializable * @return \pq\Gateway\Row */ function update() { - $rowset = $this->table->update($this->criteria(), $this->changes()); + $criteria = $this->criteria(); + if (($lock = $this->getTable()->getLock())) { + $lock->onUpdate($this, $criteria); + } + $rowset = $this->table->update($criteria, $this->changes()); if (!count($rowset)) { throw new \UnexpectedValueException("No row updated"); } diff --git a/lib/pq/Gateway/Table.php b/lib/pq/Gateway/Table.php index fe5efbd..a25b2ad 100644 --- a/lib/pq/Gateway/Table.php +++ b/lib/pq/Gateway/Table.php @@ -2,6 +2,7 @@ namespace pq\Gateway; +use \pq\Query\Expr as QueryExpr; use \pq\Query\Writer as QueryWriter; use \pq\Query\Executor as QueryExecutor; @@ -88,8 +89,12 @@ class Table * @param \pq\Connection $conn * @param array $dependents */ - function __construct($name, \pq\Connection $conn = null) { - $this->name = $name; + function __construct($name = null, \pq\Connection $conn = null) { + if (isset($name)) { + $this->name = $name; + } elseif (!isset($this->name)) { + throw new \InvalidArgumentException("Table must have a name"); + } $this->conn = $conn ?: static::$defaultConnection ?: new \pq\Connection; } @@ -374,6 +379,42 @@ class Table $order, $limit, $offset ); } + + /** + * Get rows dependent on other rows by foreign keys + * @param array $relations + * @param array $where + * @param string $order + * @param int $limit + * @param int $offset + * @return mixed + */ + function with(array $relations, array $where = null, $order = null, $limit = 0, $offset = 0) { + $qthis = $this->conn->quoteName($this->getName()); + $query = $this->getQueryWriter()->reset(); + $query->write("SELECT", "$qthis.*", "FROM", $qthis); + foreach ($relations as $relation) { + $query->write("JOIN", $relation->foreignTable)->write("ON")->criteria( + array( + "{$relation->referencedTable}.{$relation->referencedColumn}=" => + new QueryExpr("{$relation->foreignTable}.{$relation->foreignColumn}") + ) + ); + } + if ($where) { + $query->write("WHERE")->criteria($where); + } + if ($order) { + $query->write("ORDER BY", $order); + } + if ($limit) { + $query->write("LIMIT", $limit); + } + if ($offset) { + $query->write("OFFSET", $offset); + } + return $this->execute($query); + } /** * Insert a row into the table diff --git a/lib/pq/Gateway/Table/LockInterface.php b/lib/pq/Gateway/Table/LockInterface.php index ff8e858..0cefb68 100644 --- a/lib/pq/Gateway/Table/LockInterface.php +++ b/lib/pq/Gateway/Table/LockInterface.php @@ -4,7 +4,10 @@ namespace pq\Gateway\Table; use \pq\Gateway\Row; +/** + * Lazy row lock on update/delete + */ interface LockInterface { - function criteria(Row $row, array &$where); + function onUpdate(Row $row, array &$where); } diff --git a/lib/pq/Gateway/Table/OptimisticLock.php b/lib/pq/Gateway/Table/OptimisticLock.php index 08e5ae3..8c2ec7b 100644 --- a/lib/pq/Gateway/Table/OptimisticLock.php +++ b/lib/pq/Gateway/Table/OptimisticLock.php @@ -23,11 +23,11 @@ class OptimisticLock implements LockInterface } /** - * @implements LockInterface + * @inheritdoc * @param \pq\Gateway\Row $row * @param array $where reference to the criteria */ - function criteria(Row $row, array &$where) { + function onUpdate(Row $row, array &$where) { $where["{$this->column}="] = $row->getData()[$this->column]; $row->{$this->column}->mod(+1); } diff --git a/lib/pq/Gateway/Table/PessimisticLock.php b/lib/pq/Gateway/Table/PessimisticLock.php index e711b46..2076dbd 100644 --- a/lib/pq/Gateway/Table/PessimisticLock.php +++ b/lib/pq/Gateway/Table/PessimisticLock.php @@ -15,7 +15,7 @@ class PessimisticLock implements LockInterface * @param array $ignore * @throws \UnexpectedValueException if the row has already been modified */ - function criteria(Row $row, array &$ignore) { + function onUpdate(Row $row, array &$ignore) { $where = array(); foreach ($row->getIdentity() as $col => $val) { if (isset($val)) { diff --git a/lib/pq/Gateway/Table/Relations.php b/lib/pq/Gateway/Table/Relations.php index ab74d40..7ad5326 100644 --- a/lib/pq/Gateway/Table/Relations.php +++ b/lib/pq/Gateway/Table/Relations.php @@ -46,7 +46,7 @@ class Relations $table->getQueryExecutor()->execute( new \pq\Query\Writer(RELATION_SQL, array($table->getName())), function($result) use($table, $cache) { - $this->references = $result->map(array(0,1), array(2,3,4), \pq\Result::FETCH_OBJECT); + $this->references = $result->map(array(0,1), array(1,2,3,4), \pq\Result::FETCH_OBJECT); $cache->set("$table#relations", $this->references); } ); diff --git a/tests/lib/pq/Gateway/RowTest.php b/tests/lib/pq/Gateway/RowTest.php index 00dbbbb..6a738d3 100644 --- a/tests/lib/pq/Gateway/RowTest.php +++ b/tests/lib/pq/Gateway/RowTest.php @@ -90,7 +90,6 @@ class RowTest extends \PHPUnit_Framework_TestCase { function testOptimisticLockFail() { $this->table->setLock(new Table\OptimisticLock("counter")); $row = $this->table->find(null, null, 1)->current(); - $cnt = $row->counter->get(); $row->data = "foo"; executeInConcurrentTransaction( $this->table->getQueryExecutor(), @@ -103,7 +102,7 @@ class RowTest extends \PHPUnit_Framework_TestCase { function testRef() { foreach ($this->table->find() as $row) { foreach ($row->reftest() as $ref) { - $this->assertEquals($row->id->get(), $ref->test->id->get()); + $this->assertEquals($row->id->get(), $ref->test->current()->id->get()); } } } diff --git a/tests/lib/pq/Gateway/TableTest.php b/tests/lib/pq/Gateway/TableTest.php index e1b6687..f3d9558 100644 --- a/tests/lib/pq/Gateway/TableTest.php +++ b/tests/lib/pq/Gateway/TableTest.php @@ -84,4 +84,17 @@ class TableTest extends \PHPUnit_Framework_TestCase { $this->table->delete(array("id!=" => 0)); $this->assertCount(0, $this->table->find()); } + + public function testWith() { + $relation = $this->table->getRelations("test")->reftest; + $rowset = $this->table->with([$relation], array("another_test_id=" => 2)); + $this->assertCount(1, $rowset); + $this->assertEquals(array( + "id" => 2, + "created" => date_create("today")->format("Y-m-d H:i:s"), + "counter" => 0, + "number" => 0, + "data" => "today" + ), $rowset->current()->getData()); + } } diff --git a/tests/setup.inc b/tests/setup.inc index bec6c71..38dff5a 100644 --- a/tests/setup.inc +++ b/tests/setup.inc @@ -74,12 +74,7 @@ class QueryLogger implements \SplObserver if (isset($result)) { fprintf($this->fp, "[%s] R %s\n", date_create()->format("Y-m-d H:i:s"), - json_encode([ - "S" => $result->statusMessage, - "N" => $result->numRows, - "C" => $result->numCols, - "A" => $result->affectedRows - ])); + json_encode($result)); } elseif (($query = $executor->getQuery())) { fprintf($this->fp, "[%s] Q %s %% %s\n", date_create()->format("Y-m-d H:i:s"), -- 2.30.2