README

这是一个基于表数据网关的实现，建立在pg_wrapper和pg_builder包之上。

使用这些包立即允许

• 透明地将PHP类型转换为Postgres类型，反之亦然；
• 将查询的部分作为SQL字符串编写，而在后续处理这些部分时作为抽象语法树的节点。

安装

使用composer安装包

composer require sad_spirit/pg_gateway

设计目标

• 代码生成不是必需的，默认网关实现可以按原样使用。
• 网关了解表元数据：列、主键、外键。
• 可以缓存生成的SQL，跳过整个解析/构建过程。
• API鼓励构建参数化查询。
• 由多个网关构建的查询可以通过连接/《EXISTS()`等组合。

使用示例

假设以下数据库模式

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
);

我们可以使用默认网关和默认构建器来执行对上述表的复杂查询

use sad_spirit\pg_gateway\{
    TableLocator,
    builders\FluentBuilder
};
use sad_spirit\pg_wrapper\Connection;

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

$adminRoles = $locator->createGateway('example.roles')
    ->select(fn(FluentBuilder $builder) => $builder
        ->operatorCondition('name', '~*', 'admin')
        ->outputColumns()
            ->except(['description'])
            ->replace('/^/', 'role_'));

$activeAdminRoles = $locator->createGateway('example.users_roles')
    ->select(fn(FluentBuilder $builder) => $builder
        ->sqlCondition("current_date between coalesce(self.valid_from, 'yesterday') and coalesce(self.valid_to, 'tomorrow')")
        ->join($adminRoles)
            ->onForeignKey()
        ->outputColumns()
            ->only(['valid_from', 'valid_to']));

$activeAdminUsers = $locator->createGateway('example.users')
    ->select(fn(FluentBuilder $builder) => $builder
        ->outputColumns()
            ->except(['password_hash'])
            ->replace('/^/', 'user_')
        ->join($activeAdminRoles)
            ->onForeignKey()
        ->orderBy('user_login, role_name')
        ->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() . ';';

最后两个echo语句将输出类似的内容

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;

文档

• 包概述

• TableLocator类

• 处理表元数据

• TableGateway接口及其实现

• 流畅构建器

• FragmentBuilder实现

• 查询片段：基础接口、参数、别名

• Fragment实现

• Condition类

要求

pg_gateway至少需要PHP 7.4和本机pgsql扩展。

最低支持的PostgreSQL版本是10。

强烈建议在生产环境中使用PSR-6兼容缓存，用于元数据查找和生成的查询。