sad_spirit\pg_wrapper

This package has two parts and purposes

• Converter of PostgreSQL data types to their PHP equivalents and back and
• An OO wrapper around PHP's native pgsql extension.

While the converter part can be used separately e.g. with PDO, features like transparent conversion of query results work only with the wrapper.

Why type conversion?

PostgreSQL supports a large (and extensible) set of complex database types: arrays, ranges, geometric and date/time types, composite (row) types, JSON...

create table test (
    strings  text[],
    coords   point,
    occupied daterange,
    age      interval,
    document json
);

insert into test values (
    array['Mary had', 'a little lamb'], point(55.75, 37.61),
    daterange('2014-01-13', '2014-09-19'), age('2014-09-19', '2014-01-13'),
    '{"title":"pg_wrapper","text":"pg_wrapper is cool"}'
);

Unfortunately neither of PHP extensions for talking to PostgreSQL (pgsql and PDO_pgsql) can map these complex types to their PHP equivalents. They return string representations instead:

var_dump(pg_fetch_assoc(pg_query($conn, 'select * from test')));

yields

array(5) {
  'strings' =>
  string(28) "{"Mary had","a little lamb"}"
  'coords' =>
  string(13) "(55.75,37.61)"
  'occupied' =>
  string(23) "[2014-01-13,2014-09-19)"
  'age' =>
  string(13) "8 mons 6 days"
  'document' =>
  string(50) "{"title":"pg_wrapper","text":"pg_wrapper is cool"}"
}

And that is where this library kicks in:

$result = $connection->execute('select * from test');
var_dump($result[0]);

yields

array(5) {
  'strings' =>
  array(2) {
    [0] =>
    string(8) "Mary had"
    [1] =>
    string(13) "a little lamb"
  }
  'coords' =>
  class sad_spirit\pg_wrapper\types\Point#18 (1) {
    private $_coordinates =>
    array(2) {
      'x' =>
      double(55.75)
      'y' =>
      double(37.61)
    }
  }
  'occupied' =>
  class sad_spirit\pg_wrapper\types\DateTimeRange#19 (1) {
    ...
  }
  'age' =>
  class sad_spirit\pg_wrapper\types\DateInterval#22 (16) {
    ...
  }
  'document' =>
  array(2) {
    'title' =>
    string(10) "pg_wrapper"
    'text' =>
    string(18) "pg_wrapper is cool"
  }
}

Why another OO wrapper when we have PDO, Doctrine DBAL, etc?

The goal of an abstraction layer is to target the Lowest Common Denominator and thus it intentionally hides some low-level APIs that we can use with the native extension and / or adds another level of complexity.

• PDO does not expose pg_query_params(), so you have to prepare() / execute() each query even if you execute() it only once. Doctrine DBAL has Connection::executeQuery() but it has PDO's prepare() / execute() under the hood.
• Postgres only supports $1 positional parameters natively, while PDO has positional ? and named :foo parameters. PDO actually rewrites the query to convert the latter to the former, which (before PHP 7.4) prevented using Postgres operators containing ? with PDO and can still lead to problems when using dollar quoting for strings.
• PDO does not expose pg_field_type_oid() and its PDOStatement::getColumnMeta() returns type name without a schema name and may run a metadata query each time to get that.

Another example: a very common problem for database abstraction is providing a list of parameters to a query with an IN clause

SELECT * FROM stuff WHERE id IN (?)

where ? actually represents a variable number of parameters.

On the one hand, Postgres has native array types and this can be easily achieved with the following query

-- in case of using PDO just replace $1 with a PDO-compatible placeholder
SELECT * FROM stuff WHERE id = ANY($1::INTEGER[])

passing an array literal as its parameter value

$factory      = new DefaultTypeConverterFactory();
$arrayLiteral = $factory->getConverterForTypeSpecification('INTEGER[]')->output([1, 2, 3]);

On the other hand, Doctrine DBAL has its own solution for parameter lists which once again depends on rewriting SQL and does not work with prepare() / execute(). It also has "support" for array types, but that just (un)serializes PHP arrays rather than converts them from/to native DB representation, which will obviously not work with the above query.

Documentation

Is in the wiki

Type conversion:

• TypeConverter interface and its implementations
• TypeConverterFactory interface and its default implementation

Working with PostgreSQL:

• Connecting to a DB
• Executing a query
• Working with a query result
• Transactions handling

Requirements

pg_wrapper requires at least PHP 7.2. pgsql extension should be enabled to use classes that actually work with the DB.

Minimum supported PostgreSQL version is 9.3

It is highly recommended to use metadata cache in production to prevent possible metadata lookups from database on each page request.