How to create custom gateways

Table gateway implementations in the package are designed to be usable as-is, fragments that are passed to gateway methods should be generated by either generic or custom builders.

That being said, there are some valid reasons for creating custom implementations of TableGateway:

• Table access should be read-only (or append-only);

• Gateway needs custom base AST and / or pre-applying some Fragments;

• Gateway will access a relation that is not an ordinary table (view, foreign table, …).

The first case is trivial (just make DML methods throw exceptions) and the third one is way out of scope for a simple how-to: it requires at least a custom implementation of metadata interfaces. Thus we’ll cover the remaining case.

Example: soft deletes

We’ll implement a gateway that marks table rows as deleted rather than permanently deletes them. We will also make sure that “deleted” rows are not accessed.

Similar to builders several steps should be performed

• Create a new implementation of TableGateway;

• Make TableLocator aware of the new class;

• Make IDE and psalm aware of the new class. Gateways aren’t usually type-hinted, so this is more or less required.

Table definition

create schema howto;

create table howto.soft_deletes (
    id integer not null generated by default as identity,
    data text,
    deleted_at timestamp with time zone,

    constraint soft_pkey primary key (id)
);

Limiting the returned rows

We’ll create a subclass of gateways\PrimaryKeyTableGateway as the example table has a scalar primary key and put it in app\howto\db namespace:

namespace app\howto\db;

use sad_spirit\pg_builder\Select;
use sad_spirit\pg_gateway\{
    Fragment,
    FragmentBuilder,
    TableSelect,
    gateways\PrimaryKeyTableGateway
};

class SoftDeletesGateway extends PrimaryKeyTableGateway
{
    public function select(
        iterable|FragmentBuilder|Fragment|\Closure|null $fragments = null,
        array $parameters = []
    ): TableSelect {
        return new TableSelect(
            $this->tableLocator,
            $this,
            $this->convertFragments($fragments, $parameters),
            function (): Select {
                /** @var Select $select */
                $select = $this->tableLocator->createFromString(\sprintf(
                    'select self.* from %s as self where self.deleted_at is null',
                    $this->getDefinition()->getName()
                ));

                return $select;
            },
            function (): Select {
                /** @var Select $select */
                $select = $this->tableLocator->createFromString(\sprintf(
                    'select count(self.*) from %s as self where self.deleted_at is null',
                    $this->getDefinition()->getName()
                ));

                return $select;
            }
        );
    }
}

TableSelect constructor accepts closures that generate the base AST for SELECT / SELECT COUNT(*) statements, here we are creating these from strings for readability.

Implementing soft deletes

We need to

• Make sure that generated UPDATE statements work only with not-yet-deleted rows;

• Instead of DELETE statements generate UPDATEs setting deleted_at to current time.

The first item is done the same way as for SELECT above, by replacing base AST used for UPDATE:

use sad_spirit\pg_builder\NativeStatement;
use sad_spirit\pg_builder\Update;
use sad_spirit\pg_gateway\FragmentList;
use sad_spirit\pg_gateway\StatementType;

public function createUpdateStatement(FragmentList $fragments): NativeStatement
{
    return $this->tableLocator->createNativeStatementUsingCache(
        function () use ($fragments): Update {
            /** @var Update $update */
            $update = $this->tableLocator->createFromString(\sprintf(
                // update does not allow empty set clause, the fake one will be replaced
                'update %s as self set foo = bar where self.deleted_at is null',
                $this->getDefinition()->getName()
            ));
            $fragments->applyTo($update);
            return $update;
        },
        $this->generateStatementKey(StatementType::Update, $fragments)
    );
}

The second one is done by “reimplementing” createDeleteStatement():

use sad_spirit\pg_gateway\Expression;
use sad_spirit\pg_gateway\fragments\SetClauseFragment;

public function createDeleteStatement(FragmentList $fragments): NativeStatement
{
    $fragments->add(new SetClauseFragment(
        $this->getDefinition()->getColumns(),
        $this->tableLocator,
        ['deleted_at' => new Expression('now()')]
    ));
    return $this->createUpdateStatement($fragments);
}

Here we are taking advantage of the fact that all Fragments that can be applied to DELETE can also be applied to UPDATE. So we just add a new Fragment to the list that marks the row as deleted and pass the list to the method creating an UPDATE.

Wrapping up

As with builders, we add an instance of NameMappingGatewayFactory to $locator:

$locator->addTableGatewayFactory(new NameMappingGatewayFactory([
    'howto' => '\\app\\howto\\db'
]));

and add a directive to .phpstorm.meta.php

namespace PHPSTORM_META {
    override(\sad_spirit\pg_gateway\TableLocator::createGateway(), map([
        'howto.soft_deletes' => \app\howto\db\SoftDeletesGateway::class
    ]));
}

After that we can check that whatever is being generated looks good:

$gateway = $locator->createGateway('howto.soft_deletes');

echo $gateway->selectByPrimaryKey(1)
    ->createSelectStatement()
    ->getSql();

echo \PHP_EOL . \PHP_EOL;

echo $gateway->createUpdateStatement(new FragmentList(
    $gateway->createPrimaryKey(1),
    new SetClauseFragment(
        $gateway->getDefinition()->getColumns(),
        $locator,
        ['data' => 'some data']
    )
))
    ->getSql();

echo \PHP_EOL . \PHP_EOL;

echo $gateway->createDeleteStatement(new FragmentList($gateway->createPrimaryKey(1)))
    ->getSql();

outputting

select self.*
from howto.soft_deletes as self
where self.deleted_at is null
    and self.id = $1::int4

update howto.soft_deletes as self
set "data" = $1::"text"
where self.deleted_at is null
    and self.id = $2::int4

update howto.soft_deletes as self
set deleted_at = now()
where self.deleted_at is null
    and self.id = $1::int4