项目作者: sad-spirit

项目描述 :
Query builder for Postgres backed by SQL parser
高级语言: PHP
项目地址: git://github.com/sad-spirit/pg-builder.git
创建时间: 2014-09-20T05:58:34Z
项目社区:https://github.com/sad-spirit/pg-builder

开源协议:BSD 2-Clause "Simplified" License

下载


sad_spirit/pg_builder

Continuous Integration
Static Analysis

This is a query builder for Postgres with a twist: it contains a partial1 reimplementation of PostgreSQL’s own
query parser. This sets it aside from the usual breed of “write-only” query builders:

  • Query is represented as an Abstract Syntax Tree quite similar to PostgreSQL’s internal representation.
  • Query parts can be added to the AST either as objects or as strings (that will be processed by Parser).
  • Nodes can be removed and replaced in AST.
  • AST can be analyzed and transformed, the package takes advantage of this to allow named parameters like
    :foo instead of standard PostgreSQL’s positional parameters $1 and to infer parameters’ types
    from SQL typecasts.
  • Almost all syntax available for SELECT (and VALUES) / INSERT / UPDATE / DELETE / MERGE in PostgreSQL 17
    is supported, query being built is automatically checked for correct syntax.

Substantial effort was made to optimise parsing, but not parsing is faster anyway, so there are means to cache parts
of AST and the resultant query.

Usage example

  1. use sad_spirit\pg_builder\{
  2. Select,
  3. StatementFactory,
  4. converters\BuilderSupportDecorator
  5. };
  6. use sad_spirit\pg_wrapper\{
  7. Connection,
  8. converters\DefaultTypeConverterFactory
  9. };
  10. $wantPDO = false;
  11. if ($wantPDO) {
  12. $pdo = new \PDO('pgsql:host=localhost;user=username;dbname=cms');
  13. // Uses DB connection properties to set up parsing and building of SQL
  14. $factory = StatementFactory::forPDO($pdo);
  15. // NB: This still requires sad_spirit/pg_wrapper for type conversion code
  16. $converter = new BuilderSupportDecorator(new DefaultTypeConverterFactory(), $factory->getParser());
  17. } else {
  18. $connection = new Connection('host=localhost user=username dbname=cms');
  19. // Uses DB connection properties to set up parsing and building of SQL
  20. $factory = StatementFactory::forConnection($connection);
  21. // Needed for handling type info extracted from query
  22. $connection->setTypeConverterFactory(new BuilderSupportDecorator(
  23. $connection->getTypeConverterFactory(),
  24. $factory->getParser()
  25. ));
  26. }
  27. // latest 5 news
  28. /** @var Select $query */
  29. $query = $factory->createFromString(
  30. 'select n.* from news as n order by news_added desc limit 5'
  31. );
  32. // we also need pictures for these...
  33. $query->list[] = 'p.*';
  34. $query->from[0]->leftJoin('pictures as p')->on = 'n.picture_id = p.picture_id';
  35. // ...and need to limit them to only specific rubrics
  36. $query->from[] = 'objects_rubrics as ro';
  37. $query->where->and('ro.rubric_id = any(:rubric::integer[]) and ro.obj_id = n.news_id');
  38. // ...and keep 'em fresh
  39. $query->where->and('age(news_added) < :age::interval');
  40. // $generated contains a query, mapping from named parameters to positional ones, types info
  41. // it can be easily cached to prevent parsing/building SQL on each request
  42. $generated = $factory->createFromAST($query);
  43. // Note that we don't have to specify parameter types, these are extracted from query
  44. if ($wantPDO) {
  45. $result = $pdo->prepare($generated->getSql());
  46. $result->execute($converter->convertParameters(
  47. $generated,
  48. [
  49. 'rubric' => [19, 20, 21],
  50. 'age' => 30 * 24 * 3600
  51. ]
  52. ));
  53. } else {
  54. $result = $generated->executeParams(
  55. $connection,
  56. [
  57. 'rubric' => [19, 20, 21],
  58. 'age' => 30 * 24 * 3600
  59. ]
  60. );
  61. }
  62. foreach ($result as $row) {
  63. print_r($row);
  64. }
  65. echo $generated->getSql();

the last echo statement will output something like

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

if targeting Connection and something like

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

if targeting PDO

Installation

Require the package with composer:

  1. composer require "sad_spirit/pg_builder:^3"

pg_builder requires at least PHP 8.2. Either native pgsql extension with
pg_wrapper package or PDO
with pgsql support can be used to run the built queries.

Minimum supported PostgreSQL version is 12.

It is highly recommended to use PSR-6 compatible cache in production.

Documentation

For in-depth description of package features, visit pg_builder manual.


1: “Partial” here means the following: PostgreSQL grammar file src/backend/parser/gram.y is about 19K lines long.
Of these about 5K lines are used for SELECT / INSERT / UPDATE / DELETE / MERGE queries and are reimplemented here.