sad_spirit / pg_builder
Postgres查询构建器,由SQL解析器支持
v2.4.0
2024-05-27 10:41 UTC
Requires
- php: >=7.2.0
- ext-ctype: *
Requires (Dev)
- ext-pdo_pgsql: *
- ext-pgsql: *
- phpstan/phpstan: ^1.10.15
- phpunit/phpunit: ^8.0|^9.0
- psr/cache: ^1.0
- sad_spirit/pg_wrapper: ^2.4.0
- vimeo/psalm: ^4.30
Suggests
- ext-pdo_pgsql: Used for running built queries
- psr/cache-implementation: Used for caching parsed queries
- sad_spirit/pg_wrapper: Used for DB types conversion and running built queries
Conflicts
- sad_spirit/pg_wrapper: <2.4
This package is auto-updated.
Last update: 2024-08-27 11:11:05 UTC
README
这是一个具有特色的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
查询,并在本处进行了重新实现。