sad_spirit/pg-gateway

Table Data Gateway implementation for Postgres based on pg_builder, mix and match manually written SQL with query builder

v0.1.0 2023-09-13 05:43 UTC

Requires

• php: >=7.4.0
• ext-pgsql: *
• sad_spirit/pg_builder: ^2.2
• sad_spirit/pg_wrapper: ^2.2

Requires (Dev)

• phpunit/phpunit: ^9.0
• psr/cache: ^1.0
• squizlabs/php_codesniffer: ^3.6
• vimeo/psalm: ^5.14

Suggests

• psr/cache-implementation: Used for caching generated queries

BSD-2-Clause

• Alexey Borzov

• orm
• sql
• data
• postgresql
• postgres
• gateway
• table

README

This is a Table Data Gateway implementation built upon pg_wrapper and pg_builder packages.

Using those packages immediately allows

• Transparent conversion of PHP types to Postgres types and back;
• Writing parts of the query as SQL strings while later processing those parts as Nodes in the Abstract Syntax Tree.

Design goals

• Code generation is not necessary, default gateway implementations are useful as-is.
• Gateways are aware of the table metadata: columns, primary key, foreign keys.
• It is possible to cache the generated SQL, skipping the whole parsing/building process.
• API encourages building parametrized queries.
• Queries built by several Gateways can be combined via joins / EXISTS() / etc.

Usage example

Assuming the following database schema

create schema example;

create table example.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 example.roles (
    id integer not null generated by default as identity,
    name text not null,
    description text,
    
    constraint roles_pkey primary key (id)
);

create table example.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 example.users (id)
        on delete cascade on update restrict,
    constraint users_roles_fkey foreign key (role_id)
        references example.roles (id)
        on delete cascade on update restrict
);

we can set up default gateways to the above tables

use sad_spirit\pg_gateway\{
    TableLocator,
    gateways\CompositePrimaryKeyTableGateway,
    gateways\PrimaryKeyTableGateway
};
use sad_spirit\pg_wrapper\Connection;

$connection = new Connection('...');
$locator    = new TableLocator($connection);

/** @var PrimaryKeyTableGateway $gwUsers */
$gwUsers    = $locator->get('example.users');
/** @var PrimaryKeyTableGateway $gwRoles */
$gwRoles    = $locator->get('example.roles');
/** @var CompositePrimaryKeyTableGateway $gwLink */
$gwLink     = $locator->get('example.users_roles');

and use these to perform a non-trivial query

$adminRoles = $gwRoles->select([
    $gwRoles->outputColumns()
        ->except(['description'])
        ->replace('/^/', 'role_'),
    $gwRoles->operatorCondition('name', '~*', 'admin')
]);

$activeAdminRoles = $gwLink->select([
    $gwLink->outputColumns()
        ->only(['valid_from', 'valid_to']),
    $gwLink->join($adminRoles)
        ->onForeignKey(),
    $gwLink->sqlCondition("current_date between coalesce(self.valid_from, 'yesterday') and coalesce(self.valid_to, 'tomorrow')")
]);

$activeAdminUsers = $gwUsers->select([
    $gwUsers->outputColumns()
        ->except(['password_hash'])
        ->replace('/^/', 'user_'),
    $gwUsers->join($activeAdminRoles)
        ->onForeignKey(),
    $gwUsers->orderBy('user_login, role_name'),
    $gwUsers->limit(5)
]);

// Let's assume we want to output that list with pagination
echo "Total users with active admin roles: " . $activeAdminUsers->executeCount() . "\n\n";

foreach ($activeAdminUsers as $row) {
    print_r($row);
}

echo $activeAdminUsers->createSelectCountStatement()->getSql() . ";\n\n";
echo $activeAdminUsers->createSelectStatement()->getSql() . ';';

where the last two echo statements will output something similar to

select count(self.*)
from example.users as self, example.users_roles as gw_1, example.roles as gw_2
where gw_2."name" ~* $1::"text"
    and gw_1.role_id = gw_2.id
    and current_date between coalesce(gw_1.valid_from, 'yesterday') and coalesce(gw_1.valid_to, 'tomorrow')
    and gw_1.user_id = self.id;

select gw_2.id as role_id, gw_2."name" as role_name, gw_1.valid_from, gw_1.valid_to, self.id as user_id,
    self.login as user_login
from example.users as self, example.users_roles as gw_1, example.roles as gw_2
where gw_2."name" ~* $1::"text"
    and gw_1.role_id = gw_2.id
    and current_date between coalesce(gw_1.valid_from, 'yesterday') and coalesce(gw_1.valid_to, 'tomorrow')
    and gw_1.user_id = self.id
order by user_login, role_name
limit $2;

Documentation

• Package overview

• TableLocator class

• Working with table metadata

• TableGateway interface and its implementations

• Builder methods of default gateways

• FragmentBuilder implementations

• Query fragments: base interfaces, parameters, aliases

• Fragment implementations

• Condition classes

Requirements

pg_gateway requires at least PHP 7.4 with native pgsql extension.

Minimum supported PostgreSQL version is 10.

It is highly recommended to use PSR-6 compatible cache in production, both for metadata lookup and for generated queries.