From ccdcd2977c8bdc7c74b89b2fb7c2ea46479692b8 Mon Sep 17 00:00:00 2001 From: Michael Wallner Date: Tue, 14 Oct 2014 09:10:10 +0200 Subject: [PATCH] refactor relations --- lib/pq/Gateway/Row.php | 42 +++++++++++---- lib/pq/Gateway/Table.php | 67 +++++++++--------------- lib/pq/Gateway/Table/Attributes.php | 14 +++-- lib/pq/Gateway/Table/Identity.php | 4 +- lib/pq/Gateway/Table/Reference.php | 53 +++++++++++++++++++ lib/pq/Gateway/Table/Relations.php | 81 ++++++++++++++++++++++------- tests/lib/pq/Gateway/CellTest.php | 4 +- tests/lib/pq/Gateway/RowTest.php | 4 +- tests/lib/pq/Gateway/TableTest.php | 3 +- 9 files changed, 189 insertions(+), 83 deletions(-) create mode 100644 lib/pq/Gateway/Table/Reference.php diff --git a/lib/pq/Gateway/Row.php b/lib/pq/Gateway/Row.php index 78ec4c4..bfda71b 100644 --- a/lib/pq/Gateway/Row.php +++ b/lib/pq/Gateway/Row.php @@ -197,14 +197,11 @@ class Row implements \JsonSerializable } /** - * Get a cell or parent rows + * Get a cell * @param string $p - * @return \pq\Gateway\Cell|\pq\Gateway\Rowset + * @return \pq\Gateway\Cell */ function __get($p) { - if ($this->table->hasRelation($p)) { - return $this->table->by($this, $p); - } return $this->cell($p); } @@ -235,17 +232,40 @@ class Row implements \JsonSerializable return isset($this->data[$p]) || isset($this->cell[$p]); } + /** + * Get the parent row + * @see \pq\Gateway\Table::by() + * @param mixed $foreign table (name) + * @param string $ref + * @return \pq\Gateway\Rowset + */ + function ofWhich($foreign, $ref = null) { + if (!($foreign instanceof Table)) { + $foreign = forward_static_call( + [get_class($this->getTable()), "resolve"], + $foreign + ); + } + return $foreign->by($this, $ref); + } + /** * Get child rows of this row by foreign key * @see \pq\Gateway\Table::of() - * @param string $foreign - * @param array $args [order, limit, offset] + * @param mixed $foreign table (name) + * @param string $order + * @param int $limit + * @param int $offset * @return \pq\Gateway\Rowset */ - function __call($foreign, array $args) { - array_unshift($args, $this); - $table = forward_static_call(array(get_class($this->getTable()), "resolve"), $foreign); - return call_user_func_array(array($table, "of"), $args); + function allOf($foreign, $ref = null, $order = null, $limit = 0, $offset = 0) { + if (!($foreign instanceof Table)) { + $foreign = forward_static_call( + [get_class($this->getTable()), "resolve"], + $foreign + ); + } + return $foreign->of($this, $ref, $order, $limit, $offset); } /** diff --git a/lib/pq/Gateway/Table.php b/lib/pq/Gateway/Table.php index 42977dc..472d003 100644 --- a/lib/pq/Gateway/Table.php +++ b/lib/pq/Gateway/Table.php @@ -109,13 +109,12 @@ class Table implements \SplSubject * @return string */ function __toString() { - return sprintf("postgresql://%s:%s@%s:%d/%s?%s#%s", + return (string) sprintf("postgresql://%s:%s@%s:%d/%s#%s", $this->conn->user, $this->conn->pass, $this->conn->host, $this->conn->port, $this->conn->db, - $this->conn->options, $this->getName() ); } @@ -211,6 +210,10 @@ class Table implements \SplSubject return $this->identity; } + /** + * Get the table attribute definition (column list) + * @return \pq\Table\Attributes + */ function getAttributes() { if (!isset($this->attributes)) { $this->attributes = new Table\Attributes($this); @@ -220,36 +223,23 @@ class Table implements \SplSubject /** * Get foreign key relations - * @param string $to fkey - * @return \pq\Gateway\Table\Relations|stdClass + * @return \pq\Gateway\Table\Relations */ - function getRelations($to = null) { + function getRelations() { if (!isset($this->relations)) { $this->relations = new Table\Relations($this); } - if (isset($to)) { - if (!isset($this->relations->$to)) { - return null; - } - return $this->relations->$to; - } return $this->relations; } /** - * Check whether a certain relation exists - * @param string $name + * Get a foreign key relation * @param string $table - * @return bool + * @param string $ref + * @return \pq\Gateway\Table\Reference */ - function hasRelation($name, $table = null) { - if (!($rel = $this->getRelations($name))) { - return false; - } - if (!isset($table)) { - return true; - } - return isset($rel->$table); + function getRelation($table, $ref = null) { + return $this->getRelations()->getReference($table, $ref); } /** @@ -357,23 +347,18 @@ class Table implements \SplSubject /** * Get the child rows of a row by foreign key * @param \pq\Gateway\Row $foreign - * @param string $name optional fkey name + * @param string $ref optional fkey name * @param string $order * @param int $limit * @param int $offset * @return mixed */ - function of(Row $foreign, $name = null, $order = null, $limit = 0, $offset = 0) { + function of(Row $foreign, $ref = null, $order = null, $limit = 0, $offset = 0) { // select * from $this where $this->$foreignColumn = $foreign->$referencedColumn - if (!isset($name)) { - $name = $foreign->getTable()->getName(); - } - - if (!$foreign->getTable()->hasRelation($name, $this->getName())) { + if (!($rel = $this->getRelation($foreign->getTable()->getName(), $ref))) { return $this->onResult(null); } - $rel = $foreign->getTable()->getRelations($name)->{$this->getName()}; return $this->find( array($rel->foreignColumn . "=" => $foreign->{$rel->referencedColumn}), @@ -383,24 +368,19 @@ class Table implements \SplSubject /** * Get the parent rows of a row by foreign key - * @param \pq\Gateway\Row $me - * @param string $foreign - * @param string $order - * @param int $limit - * @param int $offset + * @param \pq\Gateway\Row $foreign + * @param string $ref * @return mixed */ - function by(Row $me, $foreign, $order = null, $limit = 0, $offset = 0) { - // select * from $foreign where $foreign->$referencedColumn = $me->$foreignColumn + function by(Row $foreign, $ref = null) { + // select * from $this where $this->$referencedColumn = $me->$foreignColumn - if (!$this->hasRelation($foreign, $this->getName())) { + if (!($rel = $foreign->getTable()->getRelation($this->getName(), $ref))) { return $this->onResult(null); } - $rel = $this->getRelations($foreign)->{$this->getName()}; - return static::resolve($rel->referencedTable)->find( - array($rel->referencedColumn . "=" => $me->{$rel->foreignColumn}), - $order, $limit, $offset + return $this->find( + array($rel->referencedColumn . "=" => $foreign->{$rel->foreignColumn}) ); } @@ -418,6 +398,9 @@ class Table implements \SplSubject $query = $this->getQueryWriter()->reset(); $query->write("SELECT", "$qthis.*", "FROM", $qthis); foreach ($relations as $relation) { + if (!($relation instanceof Table\Reference)) { + $relation = static::resolve($relation)->getRelation($this->getName()); + } $query->write("JOIN", $relation->foreignTable)->write("ON")->criteria( array( "{$relation->referencedTable}.{$relation->referencedColumn}=" => diff --git a/lib/pq/Gateway/Table/Attributes.php b/lib/pq/Gateway/Table/Attributes.php index 39c55c9..0ba2d70 100644 --- a/lib/pq/Gateway/Table/Attributes.php +++ b/lib/pq/Gateway/Table/Attributes.php @@ -17,7 +17,7 @@ const ATTRIBUTES_SQL = << 0 SQL; -class Attributes +class Attributes implements \IteratorAggregate { /** * @var array @@ -29,14 +29,14 @@ class Attributes */ function __construct(Table $table) { $cache = $table->getMetadataCache(); - if (!($this->columns = $cache->get("$table#attributes"))) { + if (!($this->columns = $cache->get("$table:attributes"))) { $table->getQueryExecutor()->execute( new \pq\Query\Writer(ATTRIBUTES_SQL, array($table->getName())), function($result) use($table, $cache) { foreach ($result->fetchAll(\pq\Result::FETCH_OBJECT) as $c) { $this->columns[$c->index] = $this->columns[$c->name] = $c; } - $cache->set("$table#attributes", $this->columns); + $cache->set("$table:attributes", $this->columns); } ); } @@ -69,4 +69,12 @@ class Attributes } return $this->columns[$c]; } + + /** + * Implements \IteratorAggregate + * @return \ArrayIterator + */ + function getIterator() { + return new \ArrayIterator($this->columns); + } } diff --git a/lib/pq/Gateway/Table/Identity.php b/lib/pq/Gateway/Table/Identity.php index fcc133b..41b5baf 100644 --- a/lib/pq/Gateway/Table/Identity.php +++ b/lib/pq/Gateway/Table/Identity.php @@ -33,12 +33,12 @@ class Identity implements \Countable, \IteratorAggregate */ function __construct(Table $table) { $cache = $table->getMetadataCache(); - if (!($this->columns = $cache->get("$table#identity"))) { + if (!($this->columns = $cache->get("$table:identity"))) { $table->getQueryExecutor()->execute( new \pq\Query\Writer(IDENTITY_SQL, array($table->getName())), function($result) use($table, $cache) { $this->columns = array_map("current", $result->fetchAll(\pq\Result::FETCH_ARRAY)); - $cache->set("$table#identity", $this->columns); + $cache->set("$table:identity", $this->columns); } ); } diff --git a/lib/pq/Gateway/Table/Reference.php b/lib/pq/Gateway/Table/Reference.php new file mode 100644 index 0000000..6f8e29c --- /dev/null +++ b/lib/pq/Gateway/Table/Reference.php @@ -0,0 +1,53 @@ +name = $state["name"]; + $this->foreignColumn = $state["foreignColumn"]; + $this->foreignTable = $state["foreignTable"]; + $this->referencedColumn = $state["referencedColumn"]; + $this->referencedTable = $state["referencedTable"]; + } + + /** + * @param array $state + * @return \pq\Gateway\Table\Reference + */ + static function __set_state($state) { + return new static($state); + } +} diff --git a/lib/pq/Gateway/Table/Relations.php b/lib/pq/Gateway/Table/Relations.php index 3e4d7fb..700f3fc 100644 --- a/lib/pq/Gateway/Table/Relations.php +++ b/lib/pq/Gateway/Table/Relations.php @@ -4,13 +4,17 @@ namespace pq\Gateway\Table; use \pq\Gateway\Table; +/* + * case when att1.attname like '%\_'||att2.attname then + substring(att1.attname from '^.*(?=_'||att2.attname||'$)') + else + att1.attname + end + */ const RELATION_SQL = <<getMetadataCache(); - if (!($this->references = $cache->get("$table#relations"))) { + if (!($this->references = $cache->get("$table: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(1,2,3,4), \pq\Result::FETCH_OBJECT); - $cache->set("$table#relations", $this->references); + $rel = $result->map([3,0], null, \pq\Result::FETCH_ASSOC); + foreach ($rel as $table => $reference) { + foreach ($reference as $name => $ref) { + $this->references[$table][$name] = new Reference($ref); + } + } + $cache->set("$table:relations", $this->references); } ); } } function __isset($r) { - return isset($this->references->$r); + return isset($this->references[$r]); } function __get($r) { - return $this->references->$r; + return $this->references[$r]; } function __set($r, $v) { - $this->references->$r = $v; + $this->references[$r] = $v; } function __unset($r){ - unset($this->references->$r); + unset($this->references[$r]); + } + + /** + * Get a reference to a table + * @param string $table + * @param string $ref + * @return \pq\Gateway\Table\Reference + */ + function getReference($table, $ref = null) { + if (isset($this->references[$table])) { + if (!strlen($ref)) { + return current($this->references[$table]); + } + if (isset($this->references[$table][$ref])) { + return $this->references[$table][$ref]; + } + } + } + + /** + * Implements \Countable + * @return int + */ + function count() { + return array_sum(array_map("count", $this->references)); + } + + /** + * Implements \IteratorAggregate + * @return \RecursiveArrayIterator + */ + function getIterator() { + return new \RecursiveArrayIterator($this->references); } } diff --git a/tests/lib/pq/Gateway/CellTest.php b/tests/lib/pq/Gateway/CellTest.php index 8ec2859..35ee6a0 100644 --- a/tests/lib/pq/Gateway/CellTest.php +++ b/tests/lib/pq/Gateway/CellTest.php @@ -55,8 +55,8 @@ class CellTest extends \PHPUnit_Framework_TestCase { $rows = $this->table->find(null, "id desc", 2); $reft = new Table("reftest"); $refs = new Rowset($reft); - $refs->append($rows->seek(0)->current()->reftest()->current()); - $refs->append($rows->seek(1)->current()->reftest()->current()); + $refs->append($rows->seek(0)->current()->allOf("reftest")->current()); + $refs->append($rows->seek(1)->current()->allOf("reftest")->current()); $refs->seek(0)->current()->test = $rows->seek(1)->current(); $refs->seek(1)->current()->test = $rows->seek(0)->current(); $refs->update(); diff --git a/tests/lib/pq/Gateway/RowTest.php b/tests/lib/pq/Gateway/RowTest.php index 7b88e19..bee2dbe 100644 --- a/tests/lib/pq/Gateway/RowTest.php +++ b/tests/lib/pq/Gateway/RowTest.php @@ -100,8 +100,8 @@ 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->current()->id->get()); + foreach ($row->allOf("reftest") as $ref) { + $this->assertEquals($row->id->get(), $ref->ofWhich("test")->current()->id->get()); } } } diff --git a/tests/lib/pq/Gateway/TableTest.php b/tests/lib/pq/Gateway/TableTest.php index 3f7b35c..03f71f7 100644 --- a/tests/lib/pq/Gateway/TableTest.php +++ b/tests/lib/pq/Gateway/TableTest.php @@ -85,8 +85,7 @@ class TableTest extends \PHPUnit_Framework_TestCase { } public function testWith() { - $relation = $this->table->getRelations("test")->reftest; - $rowset = $this->table->with([$relation], array("another_test_id=" => 2)); + $rowset = $this->table->with(["reftest"], array("another_test_id=" => 2)); $this->assertCount(1, $rowset); $this->assertEquals(array( "id" => 2, -- 2.30.2