========
Tutorial
========

In this tutorial we will use the default classes of the package with minimal configuration to add and
retrieve data from tables.

.. _tutorial-schema:

Database schema
===============

The following schema backs the well-known `RBAC <https://en.wikipedia.org/wiki/Role-based_access_control>`__
approach to restricting system access to authorized users.

.. code-block:: postgres

    create schema rbac;

    create table rbac.users (
        id integer not null generated by default as identity,
        login text not null,
        password_hash text not null,

        constraint users_pkey primary key (id)
    );

    create table rbac.roles (
        id integer not null generated by default as identity,
        name text not null,
        description text,

        constraint roles_pkey primary key (id)
    );

    create table rbac.permissions (
        id integer not null generated by default as identity,
        name text not null,

        constraint permissions_pkey primary key (id)
    );

    create table rbac.users_roles (
        user_id integer not null,
        role_id integer not null,
        valid_from date,
        valid_to date,

        constraint users_roles_pkey primary key (user_id, role_id),
        constraint roles_users_fkey foreign key (user_id)
            references rbac.users (id)
            on delete cascade on update restrict,
        constraint users_roles_fkey foreign key (role_id)
            references rbac.roles (id)
            on delete cascade on update restrict
    );

    create table rbac.roles_permissions (
        role_id integer not null,
        perm_id integer not null,
        allow boolean not null default true,

        constraint roles_permissions_pkey primary key (role_id, perm_id),
        constraint permissions_roles_fkey foreign key (role_id)
            references rbac.roles (id)
            on delete cascade on update restrict,
        constraint roles_permissions_fkey foreign key (perm_id)
            references rbac.permissions (id)
            on delete cascade on update restrict
    );

As ``pg_gateway`` does not provide any tools for schema management, you'll have to create the tables manually.
Either save the above code block to ``rbac.sql`` file and feed it to console ``psql`` client

.. code-block:: sh

    psql -f rbac.sql [your database name]

or just paste it into some Postgres client connected to the target database.

You can remove the tables with a single statement

.. code-block:: postgres

    drop schema rbac cascade;


Creating table gateways
=======================

Once the tables are available, create an instance of ``TableLocator`` class configured with a connection
to the database:

.. code-block:: php

    use sad_spirit\pg_gateway\TableLocator;
    use sad_spirit\pg_wrapper\Connection;

    $locator = new TableLocator(new Connection('host=localhost dbname=pg_gateway user=... password=...'));

This class is responsible for locating an appropriate gateway for a given table name, with its default configuration
it will return an instance of ``sad_spirit\pg_gateway\gateways\GenericTableGateway`` or one of its subclasses.

Let's request a gateway for an existing table:

.. code-block:: php

    $usersGateway = $locator->createGateway('rbac.users');

    echo $usersGateway::class;

this outputs

.. code-block:: output

    sad_spirit\pg_gateway\gateways\PrimaryKeyTableGateway

Note how we are using the qualified name of the table: even if you add ``rbac`` schema to ``search_path`` of your
Postgres database, locator will not process that and will just assume ``public`` schema for all unqualified names.

Note also the class name of the gateway: by default locator checks the table's metadata and chooses a gateway
based on its primary key. Specifically, if a table has a primary key with only one column,
``PrimaryKeyTableGateway`` instance will be returned.
However, if we request a gateway for a table supporting M:N relationship

.. code-block:: php

    $usersRolesGateway = $locator->createGateway('rbac.users_roles');

    echo $usersRolesGateway::class;

we'll get

.. code-block:: output

    sad_spirit\pg_gateway\gateways\CompositePrimaryKeyTableGateway

The third possibility is getting an instance of ``GenericTableGateway`` if the table does not have a primary key.

``TableLocator`` will only return gateways to ordinary non-system tables by default. It will also throw an
exception if a table does not exist, so the below calls will fail:

.. code-block:: php

    $systemGateway  = $locator->createGateway('pg_catalog.pg_class');
    $missingGateway = $locator->createGateway('foo.bar');

Manipulating data
=================

Base ``TableGateway`` interface defines ``insert()`` / ``update()`` / ``delete()`` methods corresponding to SQL
statements, all gateways implement these. ``PrimaryKeyAccess`` interface additionally defines ``updateByPrimaryKey()``
and ``deleteByPrimaryKey()`` methods, those are implemented by gateways to tables having a primary key.
We'll cover those methods in a moment, but first we'll use additional helper methods for data modification
to populate the roles and permissions:

.. code-block:: php

    use sad_spirit\pg_gateway\gateways\CompositePrimaryKeyTableGateway;
    use sad_spirit\pg_gateway\gateways\PrimaryKeyTableGateway;

    [$visitorRole, $editorRole] = $locator->atomic(function (TableLocator $locator) {
        /** @var PrimaryKeyTableGateway $rolesGateway */
        $rolesGateway = $locator->createGateway('rbac.roles');
        /** @var PrimaryKeyTableGateway $permissionsGateway */
        $permissionsGateway = $locator->createGateway('rbac.permissions');
        /** @var CompositePrimaryKeyTableGateway $rolesPermissionsGateway */
        $rolesPermissionsGateway = $locator->createGateway('rbac.roles_permissions');

        $visitorRole = $rolesGateway->upsert(['name' => 'visitor', 'description' => 'can view stuff']);
        $editorRole  = $rolesGateway->upsert(['name' => 'editor', 'description' => 'can edit stuff']);

        $viewPermission   = $permissionsGateway->upsert(['name' => 'view-stuff']);
        $editPermission   = $permissionsGateway->upsert(['name' => 'edit-stuff']);
        $deletePermission = $permissionsGateway->upsert(['name' => 'delete-stuff']);

        $rolesPermissionsGateway->replaceRelated(['role_id' => $visitorRole['id']], [
            ['perm_id' => $viewPermission['id'], 'allow' => true],
            ['perm_id' => $editPermission['id'], 'allow' => false],
            ['perm_id' => $deletePermission['id'], 'allow' => true]
        ]);

        $rolesPermissionsGateway->replaceRelated(['role_id' => $editorRole['id']], [
            ['perm_id' => $viewPermission['id'], 'allow' => true],
            ['perm_id' => $editPermission['id'], 'allow' => true],
            ['perm_id' => $deletePermission['id'], 'allow' => true]
        ]);

        // Oops, let's fix the permission
        $rolesPermissionsGateway->upsert([
            'role_id' => $visitorRole['id'],
            'perm_id' => $deletePermission['id'],
            'allow'   => false
        ]);

        return [$visitorRole, $editorRole];
    });

.. tip::

    You won't need to write ``/** @var PrimaryKeyTableGateway $foo */`` PHPDoc tags if you list the classes returned
    by ``createGateway()`` in ``.phpstorm.meta.php`` file. The file is understood both by PhpStorm
    and by static analysis tools (psalm and phpstan).

``atomic()`` method of ``TableLocator`` takes a closure and executes it atomically, i.e. within a transaction.
It wraps around the same method of ``sad_spirit\pg_wrapper\Connection``.

``upsert()`` method defined in ``PrimaryKeyAccess`` interface performs an ``UPDATE``-or-``INSERT`` type operation.
Specifically, it executes ``INSERT ... ON CONFLICT (<primary key columns>) DO UPDATE ...`` and returns the primary
key of an inserted / updated row.

Finally, ``replaceRelated()`` method of ``CompositePrimaryKeyTableGateway`` does exactly that: it replaces the
rows related to a given part of the primary key (assuming it represents a row in a parent table) with the values
given in an array.

The above methods are implemented with the usual web workflow in mind: we have a form where we add / edit a role with
a list of assigned permissions or a user profile with a list of assigned roles. With those methods the same
code can be used for both C and U of CRUD.

Now let's continue with the more straightforward ``insert()`` / ``update()`` / ``delete()`` to add users
and assign roles to them:

.. code-block:: php

    use sad_spirit\pg_gateway\builders\FluentBuilder;
    use sad_spirit\pg_gateway\Expression;

    $locator->atomic(function (TableLocator $locator) use ($visitorRole, $editorRole) {
        /** @var PrimaryKeyTableGateway $usersGateway */
        $usersGateway = $locator->createGateway('rbac.users');
        /** @var CompositePrimaryKeyTableGateway $usersRolesGateway */
        $usersRolesGateway = $locator->createGateway('rbac.users_roles');

        $unprivileged = $usersGateway->insert([
            'login'         => 'luser',
            'password_hash' => \password_hash('12345', \PASSWORD_DEFAULT),
        ], fn(FluentBuilder $builder) => $builder->returningColumns()->primaryKey())
            ->current();
        $privileged = $usersGateway->insert([
            'login'         => 'megaadmin',
            'password_hash' => \password_hash('nimda', \PASSWORD_DEFAULT),
        ], fn(FluentBuilder $builder) => $builder->returningColumns()->primaryKey())
            ->current();

        foreach ([$visitorRole, $editorRole] as $role) {
            foreach ([$unprivileged, $privileged] as $user) {
                $usersRolesGateway->insert(['user_id' => $user['id'], 'role_id' => $role['id']]);
            }
        }

        // Oops, let's remove the unneeded role
        $usersRolesGateway->deleteByPrimaryKey(['role_id' => $editorRole['id'], 'user_id' => $unprivileged['id']]);

        // Let's limit the duration of assigned editor role
        $dt = new \DateTimeImmutable('previous month');
        $usersRolesGateway->update([
            'valid_from' => $dt,
            'valid_to'   => new Expression(":valid_from + interval '2 months'"),
        ], fn(FluentBuilder $builder) => $builder->equal('role_id', $editorRole['id']));
    });

Here we are customizing the generated queries in the recommended way: using a closure that accepts a table-specific
builder (an instance of ``FluentBuilder`` by default). It has methods for modifying various parts of the query.

The ``insert()`` / ``update()`` / ``delete()`` methods return an instance of ``sad_spirit\pg_wrapper\Result``,
so if we add a ``RETURNING`` clause to a DML statement with ``returningColumns()`` call we can process
the returned rows in the usual way.

The last ``update()`` call shows both adding a condition to the ``WHERE`` clause and using an instance of ``Expression``
to embed an SQL expression into the ``SET`` clause of ``UPDATE``.

Retrieving data
===============

Having populated the tables, we can now retrieve the data. Let's start with getting the first user when sorted
in alphabetical order:

.. code-block:: php

    $result = $locator->createGateway('rbac.users')
        ->select(fn (FluentBuilder $builder) => $builder
            ->orderBy('login')
            ->limit(1));
    echo $result::class . \PHP_EOL;
    foreach ($result as $row) {
        echo $row['login'] . \PHP_EOL;
    }

which outputs

.. code-block:: php

    sad_spirit\pg_gateway\TableSelect
    luser

While methods for the DML statements immediately execute the generated statements and return ``Result``
instances, ``select()`` returns an intermediate object implementing the ``SelectProxy`` interface (``TableSelect``
class being its default implementation).

As you see above, when this object is used as an iterator, it actually executes the query and returns the ``Result``.
Additionally, it can reuse the relevant parts of the query to get the total number of rows
(without ``LIMIT`` / ``OFFSET`` applied) that satisfy it:

.. code-block:: php

    echo $result->executeCount();

outputting

.. code-block:: output

    2

The object is also somewhat similar to a database view: it contains a query that can be used in more complex statements
instead of a physical table. Let's get the permissions that are currently available to a privileged user:

.. code-block:: php

    // A "view" for a list of roles currently assigned to a privileged user, we create that separately
    // to keep the below $permissions expression readable
    $privilegedRoles = $locator->select('rbac.users', fn(FluentBuilder $builder) => $builder
        ->equal('login', 'megaadmin')
        ->join($locator->select('rbac.users_roles', fn(FluentBuilder $builder) => $builder
            ->sqlCondition("current_date between coalesce(self.valid_from, 'yesterday') and coalesce(self.valid_to, 'tomorrow')")
        ))
            ->alias('ur')
    );

    // We are "joining" to the above "view" via the EXISTS() expression and a custom join condition
    $permissions = $locator->select('rbac.permissions', fn (FluentBuilder $builder) => $builder
        ->returningColumns(['name'])
        ->orderBy('name, allow')
        ->join($locator->select('rbac.roles_permissions', fn(FluentBuilder $builder) => $builder
            ->returningColumns(['allow'])
            ->exists($privilegedRoles)
                ->joinOn($builder->createSqlCondition('self.role_id = ur.role_id'))
    )));

    foreach ($permissions as $permission) {
        echo $permission['name'] . ': ' . ($permission['allow'] ? 'allowed' : 'denied') . \PHP_EOL;
    }

which outputs

.. code-block:: output

    delete-stuff: denied
    delete-stuff: allowed
    edit-stuff: denied
    edit-stuff: allowed
    view-stuff: allowed
    view-stuff: allowed

You can print the actual query that was generated

.. code-block:: php

    echo $permissions->createSelectStatement()->getSql();

getting something like the following (the table aliases may be different):

.. code-block:: postgres

    select gw_1.allow, self."name"
    from rbac.permissions as self, rbac.roles_permissions as gw_1
    where exists(
            select 1
            from rbac.users as gw_2, rbac.users_roles as ur
            where current_date between coalesce(ur.valid_from, 'yesterday') and coalesce(ur.valid_to, 'tomorrow')
                and ur.user_id = gw_2.id
                and gw_2.login = $1::"text"
                and gw_1.role_id = ur.role_id
        )
        and gw_1.perm_id = self.id
    order by "name", allow

Note that we had to specify only one join condition and only one explicit alias for a joined table, when
joining ``users_roles`` and ``roles_permissions`` that are not related via a foreign key.
As you can see in the above query, the package will join tables via foreign keys by default
if those are present.

Note also how we used raw SQL fragments for conditions and how table aliases in those were properly replaced.
This is made possible by ``pg_builder`` package that can parse SQL.

This concludes the tutorial: we used the default package classes to retrieve and manipulate data and even performed some
joins with queries generated by gateways. The next chapters contain the complete API reference for the classes
used in this tutorial and possible customizations of those.