From d424e988576befd30f2268860473b8bc58de9664 Mon Sep 17 00:00:00 2001 From: Michael Wallner Date: Wed, 15 Oct 2014 06:04:23 +0200 Subject: [PATCH] finished Table docs --- pq-gateway/pq/Gateway/Table/Relations.md | 18 +-- .../Gateway/Table/Relations/getReference.md | 33 +++-- pq-gateway/pq/Gateway/Table/__toString.md | 5 +- pq-gateway/pq/Gateway/Table/by.md | 44 ++++-- pq-gateway/pq/Gateway/Table/create.md | 30 +++++ pq-gateway/pq/Gateway/Table/delete.md | 18 +++ pq-gateway/pq/Gateway/Table/getRelation.md | 52 ++++++++ pq-gateway/pq/Gateway/Table/getRelations.md | 125 ++++++++++-------- pq-gateway/pq/Gateway/Table/hasRelation.md | 58 -------- pq-gateway/pq/Gateway/Table/of.md | 4 +- pq-gateway/pq/Gateway/Table/update.md | 36 +++++ pq-gateway/pq/Gateway/Table/with.md | 6 +- 12 files changed, 273 insertions(+), 156 deletions(-) create mode 100644 pq-gateway/pq/Gateway/Table/create.md create mode 100644 pq-gateway/pq/Gateway/Table/delete.md create mode 100644 pq-gateway/pq/Gateway/Table/getRelation.md delete mode 100644 pq-gateway/pq/Gateway/Table/hasRelation.md create mode 100644 pq-gateway/pq/Gateway/Table/update.md diff --git a/pq-gateway/pq/Gateway/Table/Relations.md b/pq-gateway/pq/Gateway/Table/Relations.md index 65fb833..a174d8d 100644 --- a/pq-gateway/pq/Gateway/Table/Relations.md +++ b/pq-gateway/pq/Gateway/Table/Relations.md @@ -8,11 +8,8 @@ See pq\Gateway\Table::by() and pq\Gateway\Table::with(). The following query is executed by the current executor of the table to retrieve the table references: select - case when att1.attname like '%\_'||att2.attname then - substring(att1.attname from '^.*(?=_'||att2.attname||'$)') - else - att1.attname - end as "id" + regexp_replace(att1.attname, '_'||att2.attname||'$', '') + as "name" ,cl1.relname as "foreignTable" ,att1.attname as "foreignColumn" ,cl2.relname as "referencedTable" @@ -24,16 +21,14 @@ The following query is executed by the current executor of the table to retrieve ,pg_attribute att1 ,pg_attribute att2 where - ( cl1.relname = \$1 - or cl2.relname = \$1) + cl1.relname = \$1 and co.confrelid != 0 and co.conrelid = cl1.oid and co.conkey[1] = att1.attnum and cl1.oid = att1.attrelid and co.confrelid = cl2.oid and co.confkey[1] = att2.attnum and cl2.oid = att2.attrelid order by - cl1.relname - ,att1.attnum + att1.attnum ## Cache: @@ -51,9 +46,8 @@ Relations can be accessed as virtual properties or through pq\Gateway\Table\Rela $relations = new Table\Relations(new Table("account_email")); - // $relations->reference->account - $account_rel = $relations->getReference("account"); - $account_rel = $relations->account; + var_dump($relations->getReference("account", "account")); + var_dump($relations->account["account"]); ?> diff --git a/pq-gateway/pq/Gateway/Table/Relations/getReference.md b/pq-gateway/pq/Gateway/Table/Relations/getReference.md index 78714ad..7f86a3b 100644 --- a/pq-gateway/pq/Gateway/Table/Relations/getReference.md +++ b/pq-gateway/pq/Gateway/Table/Relations/getReference.md @@ -1,15 +1,18 @@ -# stdClass pq\Gateway\Table\Relations::getReference(string $to) +# pq\Gateway\Table\Reference pq\Gateway\Table\Relations::getReference(string $table[, string $ref = NULL]) Retrieve the foreign key of the table to another table. +See pq\Gateway\Table::getRelation(). ## Params: -* string $to +* string $table The table name referenced by a foreign key of the table. +* Optional string $ref = NULL + A specific relation id if there are more foreign keys to the same table. ## Returns: -* stdClass, the table relation. +* pq\Gateway\Table\Reference, the foreign key. * NULL, if the tables are not related. ## Example: @@ -28,17 +31,23 @@ Retrieve the foreign key of the table to another table. Yields: - object(stdClass)#13 (1) { - ["account_email"]=> - object(stdClass)#14 (4) { - ["foreignTable"]=> - string(13) "account_email" - ["foreignColumn"]=> + object(pq\Gateway\Table\Reference)#12 (5) { + ["name"]=> + string(7) "account" + ["foreignTable"]=> + string(13) "account_email" + ["foreignColumns"]=> + array(1) { + [0]=> string(10) "account_id" - ["referencedTable"]=> - string(7) "account" - ["referencedColumn"]=> + } + ["referencedTable"]=> + string(7) "account" + ["referencedColumns"]=> + array(1) { + [0]=> string(2) "id" } } + diff --git a/pq-gateway/pq/Gateway/Table/__toString.md b/pq-gateway/pq/Gateway/Table/__toString.md index e514707..3fed1b6 100644 --- a/pq-gateway/pq/Gateway/Table/__toString.md +++ b/pq-gateway/pq/Gateway/Table/__toString.md @@ -14,11 +14,10 @@ None. Yields: - postgresql://mike:@:5432/mike?#account + postgresql://mike:@:5432/mike#account diff --git a/pq-gateway/pq/Gateway/Table/by.md b/pq-gateway/pq/Gateway/Table/by.md index ca55689..6e42f85 100644 --- a/pq-gateway/pq/Gateway/Table/by.md +++ b/pq-gateway/pq/Gateway/Table/by.md @@ -1,20 +1,15 @@ -# mixed pq\Gateway\Table::by(pq\Gateway\Row $me, string $foreign[, $order = NULL[, int $limit = 0[, int $offset = 0]]]) +# mixed pq\Gateway\Table::by(pq\Gateway\Row $me[, string $ref = NULL]) -Find rows in another table by foreign key. +Find the row inthis table referenced by another table's row by foreign key. See pq\Gateway\Table::find(), pq\Gateway\Table::of() and pq\Gateway\Row::ofWhich(). ## Params: -* pq\Gateway\Row $row - A row of this table referenced by another table through a foreign key. -* Optional string $name = NULL +* pq\Gateway\Row $me + A row of another table referenced by this table through a foreign key. +* Optional string $ref = NULL The identifying name of the relation. -* Optional string $order = NULL - Sorting clause. -* Optional int $limit = 0 - Row count limit. -* Optional int $offset = 0 - Row count offset. + ## Returns: @@ -30,9 +25,32 @@ See pq\Gateway\Table::find(), pq\Gateway\Table::of() and pq\Gateway\Row::ofWhich use pq\Gateway\Table; - $account_emails = new Table("account_emails"); + $accounts = new Table("account"); + $account_emails = new Table("account_email"); $email = $account_emails->find(["email=" => "mike@php.net"])->current(); - $account = $account_emails->by($email, "account")->current(); + $account1 = $accounts->by($email)->current(); + $account2 = $email->ofWhich("account")->current(); + + var_dump($account1->getData(), $account2->getData()); ?> + +Yields: + + array(3) { + ["id"]=> + string(36) "f9710801-c900-4774-848c-22c85deecd83" + ["password"]=> + NULL + ["name"]=> + string(4) "mike" + } + array(3) { + ["id"]=> + string(36) "f9710801-c900-4774-848c-22c85deecd83" + ["password"]=> + NULL + ["name"]=> + string(4) "mike" + } diff --git a/pq-gateway/pq/Gateway/Table/create.md b/pq-gateway/pq/Gateway/Table/create.md new file mode 100644 index 0000000..f5b0b7f --- /dev/null +++ b/pq-gateway/pq/Gateway/Table/create.md @@ -0,0 +1,30 @@ +# mixed pq\Gateway\Table::create([array $data = NULL[, string $returning = "*"]]) + +Create a row in the table. + +## Params: + +* Optional array $data = NULL + The row data. +* Optional string $returning = "*" + What the INSERT should return. + +## Returns: + +* a [deferred promise of React/Promise](https://github.com/reactphp/promise#deferred-1), when using pq\Query\AsyncExecutor, the asynchronous executor. + Else: +* pq\Result, if pq\Result::$status != pq\Result::TUPLES_OK. +* pq\Result, if the rowset prototype pq\Gateway\Table::$rowset is empty. +* pq\Gateway\Rowset, an instance of the rowset prototype. + + +## Example: + + create(["name" => "mike"])->current(); + + ?> diff --git a/pq-gateway/pq/Gateway/Table/delete.md b/pq-gateway/pq/Gateway/Table/delete.md new file mode 100644 index 0000000..3ac0765 --- /dev/null +++ b/pq-gateway/pq/Gateway/Table/delete.md @@ -0,0 +1,18 @@ +# mixed pq\Gateway\Table::delete(array $where,[, $returning = NULL]) + +Delete rows from the table. + +## Params: + +* array $where + Removal criteria. +* Optional string $returning = NULL + What the DELETE should return. + +## Returns: + +* a [deferred promise of React/Promise](https://github.com/reactphp/promise#deferred-1), when using pq\Query\AsyncExecutor, the asynchronous executor. + Else: +* pq\Result, if pq\Result::$status != pq\Result::TUPLES_OK. +* pq\Result, if the rowset prototype pq\Gateway\Table::$rowset is empty. +* pq\Gateway\Rowset, an instance of the rowset prototype. diff --git a/pq-gateway/pq/Gateway/Table/getRelation.md b/pq-gateway/pq/Gateway/Table/getRelation.md new file mode 100644 index 0000000..632711f --- /dev/null +++ b/pq-gateway/pq/Gateway/Table/getRelation.md @@ -0,0 +1,52 @@ +# pq\Gateway\Table\Reference pq\Gateway\Table::getRelation(string $table[, string $ref = NULL]) + +Retrieve the foreign key of the table to another table. +See pq\Gateway\Table\Relations::getReference(). + +## Params: + +* string $table + The table name referenced by a foreign key of the table. +* Optional string $ref = NULL + A specific relation id if there are more foreign keys to the same table. + +## Returns: + +* pq\Gateway\Table\Reference, the foreign key. +* NULL, if the tables are not related. + +## Example: + + getRelation("account"); + + var_dump($ref); + + ?> + +Yields: + + object(pq\Gateway\Table\Reference)#12 (5) { + ["name"]=> + string(7) "account" + ["foreignTable"]=> + string(13) "account_email" + ["foreignColumns"]=> + array(1) { + [0]=> + string(10) "account_id" + } + ["referencedTable"]=> + string(7) "account" + ["referencedColumns"]=> + array(1) { + [0]=> + string(2) "id" + } + } + + diff --git a/pq-gateway/pq/Gateway/Table/getRelations.md b/pq-gateway/pq/Gateway/Table/getRelations.md index 352f423..d817c6a 100644 --- a/pq-gateway/pq/Gateway/Table/getRelations.md +++ b/pq-gateway/pq/Gateway/Table/getRelations.md @@ -1,21 +1,14 @@ -# \pq\Gateway\Table\Relations|stdClass pq\Gateway\Table::getRelations([string $to = NULL]) +# pq\Gateway\Table\Relations pq\Gateway\Table::getRelations() Get the relations (by foreign key) of this table. -See pq\Gateway\Table::hasRelation(). - -> ***NOTE:*** - The relation name is the column name of the foreign key with the column name of the referenced column cut off the end. ## Params: -* Optional string $to = NULL - The table name of which to get the relation to. +None. ## Returns: -* stdClass, if $to is given and a relation exists. -* NULL, if $to is given and a relation does not exist. -* pq\Gateway\Table\Relations, all relations if $to is omitted. +* pq\Gateway\Table\Relations, the table's foreign keys. ## Example: @@ -25,28 +18,27 @@ See pq\Gateway\Table::hasRelation(). $conn = new pq\Connection; $conn->exec(" - drop table if exists reftable cascade; -- drop table if exists account cascade; - -- drop table if exists account_email cascade; - -- create table account ( -- id uuid default uuid_generate_v4() primary key, -- password char(60), -- name varchar(68) -- ); + -- drop table if exists account_email cascade; -- create table account_email ( -- account_id uuid not null references account(id) on delete cascade, -- email varchar(255) not null unique, -- primary key (account_id, email) -- ); + drop table if exists reftable cascade; create table reftable ( id serial primary key, - my_account integer references account(id), - account_id integer references account(id), - second_account_id integer references account(id), - email integer references account_email(email) + my_account uuid references account(id), + account_id uuid references account(id), + second_account_id uuid references account(id), + email varchar(255) not null references account_email(email) ); "); @@ -57,64 +49,91 @@ See pq\Gateway\Table::hasRelation(). Yields: - object(pq\Gateway\Table\Relations)#10 (1) { + object(pq\Gateway\Table\Relations)#7 (1) { ["references":protected]=> - object(stdClass)#17 (4) { - ["my_account"]=> - object(stdClass)#18 (1) { - ["reftable"]=> - object(stdClass)#19 (4) { + array(2) { + ["account"]=> + array(3) { + ["my_account"]=> + object(pq\Gateway\Table\Reference)#14 (5) { + ["name"]=> + string(10) "my_account" ["foreignTable"]=> string(8) "reftable" - ["foreignColumn"]=> - string(10) "my_account" + ["foreignColumns"]=> + array(1) { + [0]=> + string(10) "my_account" + } ["referencedTable"]=> string(7) "account" - ["referencedColumn"]=> - string(2) "id" + ["referencedColumns"]=> + array(1) { + [0]=> + string(2) "id" + } } - } - ["account"]=> - object(stdClass)#20 (1) { - ["reftable"]=> - object(stdClass)#21 (4) { + ["account"]=> + object(pq\Gateway\Table\Reference)#15 (5) { + ["name"]=> + string(7) "account" ["foreignTable"]=> string(8) "reftable" - ["foreignColumn"]=> - string(10) "account_id" + ["foreignColumns"]=> + array(1) { + [0]=> + string(10) "account_id" + } ["referencedTable"]=> string(7) "account" - ["referencedColumn"]=> - string(2) "id" + ["referencedColumns"]=> + array(1) { + [0]=> + string(2) "id" + } } - } - ["second_account"]=> - object(stdClass)#22 (1) { - ["reftable"]=> - object(stdClass)#23 (4) { + ["second_account"]=> + object(pq\Gateway\Table\Reference)#16 (5) { + ["name"]=> + string(14) "second_account" ["foreignTable"]=> string(8) "reftable" - ["foreignColumn"]=> - string(17) "second_account_id" + ["foreignColumns"]=> + array(1) { + [0]=> + string(17) "second_account_id" + } ["referencedTable"]=> string(7) "account" - ["referencedColumn"]=> - string(2) "id" + ["referencedColumns"]=> + array(1) { + [0]=> + string(2) "id" + } } } - ["email"]=> - object(stdClass)#24 (1) { - ["reftable"]=> - object(stdClass)#25 (4) { + ["account_email"]=> + array(1) { + ["email"]=> + object(pq\Gateway\Table\Reference)#17 (5) { + ["name"]=> + string(5) "email" ["foreignTable"]=> string(8) "reftable" - ["foreignColumn"]=> - string(5) "email" + ["foreignColumns"]=> + array(1) { + [0]=> + string(5) "email" + } ["referencedTable"]=> string(13) "account_email" - ["referencedColumn"]=> - string(5) "email" + ["referencedColumns"]=> + array(1) { + [0]=> + string(5) "email" + } } } } } + diff --git a/pq-gateway/pq/Gateway/Table/hasRelation.md b/pq-gateway/pq/Gateway/Table/hasRelation.md deleted file mode 100644 index 987c9ae..0000000 --- a/pq-gateway/pq/Gateway/Table/hasRelation.md +++ /dev/null @@ -1,58 +0,0 @@ -# bool pq\Gateway\Table::hasRelation(string $name[, string $table = NULL]) - -Check whether a relation to another table exists with the specified name. -See pq\Gateway\Table\Relations - -> ***NOTE:*** - The relation name is the column name of the foreign key with the column name of the referenced column cut off the end. - -## Params: - -* string $name - The name of the relation. -* Optional string $table = NULL - The bare table name, if the relation has another name (e.g. because of multiple foreign keys to the same table). - -## Returns: - -* bool, whether the specified relation exists. - -## Example: - - exec(" - drop table if exists account cascade; - drop table if exists email cascade; - drop table if exists reftable cascade; - - create table account ( - id serial primary key - ); - - create table email ( - id serial primary key, - account_id integer references account(id), - email text - ); - - create table reftable ( - id serial primary key, - my_account integer references account(id), - account_id integer references account(id), - second_account_id integer references account(id), - email integer references email(id) - ); - "); - - $fgn_table = new Table("reftable"); - var_dump($fgn_table->hasRelation("my_account")); - - ?> - -Yields: - - TRUE diff --git a/pq-gateway/pq/Gateway/Table/of.md b/pq-gateway/pq/Gateway/Table/of.md index 68134b7..1e12779 100644 --- a/pq-gateway/pq/Gateway/Table/of.md +++ b/pq-gateway/pq/Gateway/Table/of.md @@ -1,4 +1,4 @@ -# mixed pq\Gateway\Table::of(pq\Gateway\Row $foreign[, string $name = NULL[, string $order = NULL[, int $limit = 0[, int $offset = 0]]]]) +# mixed pq\Gateway\Table::of(pq\Gateway\Row $foreign[, string $ref = NULL[, string $order = NULL[, int $limit = 0[, int $offset = 0]]]]) Find rows in table by foreign key. See pq\Gateway\Table::find(), pq\Gateway\Table::by() and pq\Gateway\Row::allOf(). @@ -7,7 +7,7 @@ See pq\Gateway\Table::find(), pq\Gateway\Table::by() and pq\Gateway\Row::allOf() * pq\Gateway\Row $row A row of a table referencing this table through a foreign key. -* Optional string $name = NULL +* Optional string $ref = NULL The identifying name of the relation. * Optional string $order = NULL Sorting clause. diff --git a/pq-gateway/pq/Gateway/Table/update.md b/pq-gateway/pq/Gateway/Table/update.md new file mode 100644 index 0000000..f3e643c --- /dev/null +++ b/pq-gateway/pq/Gateway/Table/update.md @@ -0,0 +1,36 @@ +# mixed pq\Gateway\Table::update(array $where, array $data[, string $returning = "*"]) + +Update rows in the table. +See pq\Query\Expr for using SQL constructs as parameters or arguments. + +## Params: + +* array $where + Criteria for the update. +* array $data + Updated row data. +* Optional string $returniung = "*" + What the UPDATE should return. + +## Returns: + +* a [deferred promise of React/Promise](https://github.com/reactphp/promise#deferred-1), when using pq\Query\AsyncExecutor, the asynchronous executor. + Else: +* pq\Result, if pq\Result::$status != pq\Result::TUPLES_OK. +* pq\Result, if the rowset prototype pq\Gateway\Table::$rowset is empty. +* pq\Gateway\Rowset, an instance of the rowset prototype. + +## Example: + + update(["id=" => 1], [ + "foo" => "bar", + "ts" => new Expr("NOW()" + ]); + + ?> diff --git a/pq-gateway/pq/Gateway/Table/with.md b/pq-gateway/pq/Gateway/Table/with.md index 8e3a054..94e52b7 100644 --- a/pq-gateway/pq/Gateway/Table/with.md +++ b/pq-gateway/pq/Gateway/Table/with.md @@ -6,7 +6,7 @@ See pq\Gateway\Table::of(), pq\Gateway\Table::by() and pq\Gateway\Table::find(). ## Params: * array $relations - list of stdClass instances representing relations to join for the query; see pq\Gateway\Table::getRelations(). + list of pq\Gateway\Table\Reference instances representing relations to join for the query; see pq\Gateway\Table::getRelations(). * Optional array $where = NULL Additional lookup criteria. * Optional string $order = NULL @@ -35,8 +35,8 @@ See pq\Gateway\Table::of(), pq\Gateway\Table::by() and pq\Gateway\Table::find(). $notifications = new Table("notification"); $relations = [ - $emails->getRelations("account")->account_email, - $notifications->getRelations("email")->notification + $emails->getRelation("account"), + $notifications->getRelation("account_email", "email") ]; $bouncers = $accounts->with($relations, ["bounces>" => 3]); -- 2.30.2