sad_spirit/pg_builder

Postgres查询构建器,由SQL解析器支持

v2.4.0 2024-05-27 10:41 UTC

README

Build Status

Static Analysis

这是一个具有特色的Postgres查询构建器:它包含了对PostgreSQL自身查询解析器的部分重新实现。这使得它与常见的“只写”查询构建器有所区别。

  • 查询以与PostgreSQL内部表示相似的抽象语法树表示。
  • 可以将查询部分作为对象或字符串(将由解析器处理)添加到AST中。
  • 可以在AST中删除和替换节点。
  • AST可以被分析和转换,该包利用这一点允许使用命名参数,如:foo,而不是标准的PostgreSQL位置参数$1,并从SQL类型转换中推断参数类型。
  • 支持PostgreSQL 16中几乎所有用于SELECT(和VALUES)/ INSERT / UPDATE / DELETE / MERGE的语法,构建的查询将自动检查语法正确性。

对解析进行了大量优化,但无论如何,解析总是更快,因此有方法可以缓存AST和结果查询的部分。

使用示例

use sad_spirit\pg_builder\{
    Select,
    StatementFactory,
    converters\BuilderSupportDecorator
};
use sad_spirit\pg_wrapper\{
    Connection,
    converters\DefaultTypeConverterFactory
};

$wantPDO = false;

if ($wantPDO) {
    $pdo       = new \PDO('pgsql:host=localhost;user=username;dbname=cms');
    // Uses DB connection properties to set up parsing and building of SQL 
    $factory   = StatementFactory::forPDO($pdo);
    // NB: This still requires sad_spirit/pg_wrapper for type conversion code
    $converter = new BuilderSupportDecorator(new DefaultTypeConverterFactory(), $factory->getParser());
} else {
    $connection = new Connection('host=localhost user=username dbname=cms');
    // Uses DB connection properties to set up parsing and building of SQL 
    $factory    = StatementFactory::forConnection($connection);
    // Needed for handling type info extracted from query
    $connection->setTypeConverterFactory(new BuilderSupportDecorator(
        $connection->getTypeConverterFactory(),
        $factory->getParser()
    ));
}

// latest 5 news
/** @var Select $query */
$query      = $factory->createFromString(
    'select n.* from news as n order by news_added desc limit 5'
);

// we also need pictures for these...
$query->list[] = 'p.*';
$query->from[0]->leftJoin('pictures as p')->on = 'n.picture_id = p.picture_id';

// ...and need to limit them to only specific rubrics
$query->from[] = 'objects_rubrics as ro';
$query->where->and('ro.rubric_id = any(:rubric::integer[]) and ro.obj_id = n.news_id');

// ...and keep 'em fresh
$query->where->and('age(news_added) < :age::interval');

// $generated contains a query, mapping from named parameters to positional ones, types info
// it can be easily cached to prevent parsing/building SQL on each request
$generated = $factory->createFromAST($query);

// Note that we don't have to specify parameter types, these are extracted from query
if ($wantPDO) {
    $result = $pdo->prepare($generated->getSql());
    $result->execute($converter->convertParameters(
        $generated,
        [
            'rubric' => [19, 20, 21],
            'age'    => 30 * 24 * 3600        
        ]       
    ));
} else {
    $result = $generated->executeParams(
        $connection, 
        [
            'rubric' => [19, 20, 21],
            'age'    => 30 * 24 * 3600
        ]
    );
}


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

echo $generated->getSql();

最后的echo语句将输出类似以下内容:

select n.*, p.*
from news as n left join pictures as p on n.picture_id = p.picture_id, objects_rubrics as ro
where ro.rubric_id = any($1::pg_catalog.int4[])
    and ro.obj_id = n.news_id
    and age(news_added) < $2::interval
order by news_added desc
limit 5

如果针对Connection,则类似以下内容:

select n.*, p.*
from news as n left join pictures as p on n.picture_id = p.picture_id, objects_rubrics as ro
where ro.rubric_id = any(:rubric::pg_catalog.int4[])
    and ro.obj_id = n.news_id
    and age(news_added) < :age::interval
order by news_added desc
limit 5

如果针对PDO

文档

位于wiki

1:“部分”在这里意味着以下内容:PostgreSQL语法文件src/backend/parser/gram.y大约有19K行。其中大约有5K行用于SELECT / INSERT / UPDATE / DELETE / MERGE查询,并在本处进行了重新实现。