Type converter factories

Factories are used to transparently convert the result fields using metadata provided by pg_field_type_oid() and to simplify passing type information to query execution methods, consider

$result = $connection->executeParams(
    'select row(foo_id, foo_added) from foo where bar = any($1::integer[])',
    [[1, 2, 3]],
    ['integer[]'],
    [['id' => 'integer', 'added' => 'timestamptz']]
);

vs

$result = $connection->executeParams(
    'select * from foo where bar = any($1::integer[])',
    [[1, 2, 3]],
    [new ArrayConverter(new IntegerConverter())],
    [new CompositeConverter(['id' => new IntegerConverter(), 'added' => new TimeStampTzConverter()])]
);

In the “wrapper” part of the package factory methods are called by Connection and PreparedStatement classes when converting query parameters and by Result when converting query results. The only methods that will be called directly are those used to set up custom types conversion, most probably for enum types and the like.

Common interface

Classes that create type converters implement the following interface

namespace sad_spirit\pg_wrapper;

use sad_spirit\pg_wrapper\converters\ConnectionAware;

interface TypeConverterFactory extends ConnectionAware
{
    public function getConverterForTypeSpecification(mixed $type): TypeConverter;
    public function getConverterForTypeOID(int|string $oid): TypeConverter;
    public function getConverterForPHPValue(mixed $value): TypeConverter;
}

getConverterForTypeSpecification()

This method returns a converter based on manually provided type specification (commonly, a type name). It should throw an exception if a matching converter cannot be found as this is most probably an user error.

Values accepted as specification are implementation-specific. Any implementation should, however, accept an instance of TypeConverter and update it with the Connection instance if it implements ConnectionAware.

getConverterForTypeOID()

Returns a converter for the type with the given OID. It expects an OID (internal Postgres object identifier) that is a primary key for some row of system pg_type table.

The method is mainly used by Result to find converters for returned columns. It should not throw an exception if a converter is not found, usually returning an instance of StubConverter in that case.

getConverterForPHPValue()

Tries to return a converter based on type/class of its argument.

This is used by query execution methods if type specification was not given for a query parameter. It should throw an exception if the argument type is ambiguous (e.g. PHP array) or its class is not known.

As the interface extends ConnectionAware it is possible to specify the Connection this factory works with. Usually the factory will be able to perform some conversions without Connection specified, but definitely not those of the custom database types.

The package contains two implementations of TypeConverterFactory: converters\DefaultTypeConverterFactory and converters\StubTypeConverterFactory

`StubTypeConverterFactory`

getConverterForTypeOID() and getConverterForPHPValue() methods of this class return an instance of converters\StubConverter.

Its getConverterForTypeSpecification() method also returns converters\StubConverter if passed anything except an implementation of TypeConverter as a $type argument. Otherwise it will return $type, configured with current Connection if it implements ConnectionAware.

Tip

This class can be used to effectively disable type conversion, making package behave like stock pgsql extension.

`DefaultTypeConverterFactory`

This is the default implementation of TypeConverterFactory interface. Its instance is automatically added to a Connection object unless setTypeConverterFactory() is explicitly used.

Type specifications accepted

getConverterForTypeSpecification() method accepts the following as its $type argument:

Type name as a string. A minimal parser is implemented, so schema-qualified names like pg_catalog.int4, double-quoted identifiers like "CamelCaseType", SQL standard names like CHARACTER VARYING will be understood.

Array types can be specified with square brackets as typename[].
TypeConverter instance. Its properties will be updated from current Connection object if needed (e.g. date and time converters will use DateStyle setting of connected database).
Composite type specification as an array 'column' => 'column type specification'

Additional methods

registerConverter(string|callable|TypeConverter $converter, string|array $type, string $schema = 'pg_catalog'): void

Registers a new converter for a base type. $converter argument is either a name of the class implementing TypeConverter, a callable returning TypeConverter instance or an object implementing TypeConverter that will be used as a prototype for cloning.

registerClassMapping(string $className, string $type, string $schema = 'pg_catalog'): void

Registers a mapping from PHP class to a database type name. If you pass an instance of this class to getConverterForPHPValue() it will return a converter for this type. This is used in query execution methods to convert query parameters that didn’t have their types specified explicitly.

Note that it is only needed to register converters for base types, proper converters for arrays / composites / ranges over these base types will be built automatically:

$factory->registerConverter('BlahConverter', 'blah', 'blah');
$factory->getConverter('blah.blah[]');

will return

new ArrayConverter(new BlahConverter());

DefaultTypeConverterFactory also implements the TypeOIDMapperAware interface

namespace sad_spirit\pg_wrapper\converters;

interface TypeOIDMapperAware
{
    public function setOIDMapper(TypeOIDMapper $mapper): void;
    public function getOIDMapper(): TypeOIDMapper;
}

An implementation of TypeOIDMapper is used, as its name implies, to map type OIDs to type names and is required mostly for getConverterForTypeOID() method.

Type names supported out of the box

The following is a list of base type names and names of built-in range types understood by DefaultTypeConverterFactory, those can be converted without setting up the Connection. This allows using the factory separately e.g. with PDO.

Note the following when reading the table:

Type names in lowercase are PostgreSQL’s internal, corresponding to rows in pg_catalog.pg_type. Those in UPPERCASE are their SQL standard synonyms.
sad_spirit\pg_wrapper\converters namespace prefix is assumed for all converter class names.
sad_spirit\pg_wrapper\types namespace prefix is assumed for all complex type class names that do not start with a backslash.
“Compatible array” is an array that will be accepted by createFromArray() method of type’s class.

Base types
Type names	`TypeConverter` implementation	Non-null PHP value returned	Non-null PHP values accepted
`bool`, `BOOLEAN`	`BooleanConverter`	`bool`	anything, PHP values evaluating to `false` and strings `'false'` and `'f'` are converted to `'f'`, everything else to `'t'`
`bytea`	`ByteaConverter`	`string` (binary)	`string` (binary)
`cstring`, `text`, `char`, `varchar`, `bpchar`, `name`, `CHARACTER`, `NCHAR`, `CHAR VARYING`, `CHARACTER VARYING`, `BIT VARYING`, `NCNAR VARYING`, `NATIONAL CHAR VARYING`, `NATIONAL CHARACTER VARYING`	`StringConverter`	`string`	`string`
`oid`, `cid`, `xid`, `int2`, `int4`, `int8`, `INT`, `INTEGER`, `SMALLINT`, `BIGINT`	`IntegerConverter`	`int`, `numeric-string` if integer is out of range for PHP (e.g. `int8` type on 32-bit PHP)	numeric values
`numeric`, `money` `DECIMAL`, `DEC`	`NumericConverter`	`string`, to prevent loss of precision
`float4`, `float8` `REAL`, `FLOAT`, `DOUBLE PRECISION`	`FloatConverter`	`float`
`json`, `jsonb`	`JsonConverter`	usually an `array`	anything `json_encode()` can handle
`date`	`datetime\DateConverter`	instance of `\DateTimeImmutable`	instance of `\DateTimeInterface` `string` (passed as is) `int` (treated as UNIX timestamp)
`time`, `TIME WITHOUT TIME ZONE`	`datetime\TimeConverter`
`timetz`, `TIME WITH TIME ZONE`	`datetime\TimeTzConverter`
`timestamp`, `TIMESTAMP WITHOUT TIME ZONE`	`datetime\TimeStampConverter`
`timestamptz`, `TIMESTAMP WITH TIME ZONE`	`datetime\TimeStampTzConverter`
`interval`	`datetime\IntervalConverter`	instance of `\DateInterval`	instance of `\DateInterval` `int` / `float` (treated as number of seconds) `string` (passed as is)
`json`, `jsonb`	`JsonConverter`	usually an `array`	anything `json_encode()` can handle
`box`	`geometric\BoxConverter`	instance of `Box`	instance of `Box` or compatible `array`
`circle`	`geometric\CircleConverter`	instance of `Circle`	instance of `Circle` or compatible `array`
`line`	`geometric\LineConverter`	instance of `Line`	instance of `Line` or compatible `array`
`lseg`	`geometric\LSegConverter`	instance of `LineSegment`	instance of `LineSegment` or compatible `array`
`path`	`geometric\PathConverter`	instance of `Path`	instance of `Path` or compatible `array`
`point`	`geometric\PointConverter`	instance of `Point`	instance of `Point` or compatible `array`
`polygon`	`geometric\PolygonConverter`	instance of `Polygon`	instance of `Polygon` or compatible `array`
`tid`	`TidConverter`	instance of `Tid`	instance of `Tid` or compatible `array`
`hstore` (from `contrib/hstore` extension)	`container\HstoreConverter`	`array<string,?string>`	`array` or `object`
`int2vector`, `oidvector`	`containers\IntegerVectorConverter`	`list<int\|numeric-string>`	single-dimension `array` of numeric values

Built-in range and multirange types
Type names	`TypeConverter` implementation	Non-null PHP value returned	Non-null PHP values accepted
`int4range`, `int8range`	`\containers\RangeConverter` with `IntegerConverter`	instance of `NumericRange`	instance of `NumericRange` or compatible `array`
`numrange`	`\containers\RangeConverter` with `NumericConverter`	instance of `NumericRange`	instance of `NumericRange` or compatible `array`
`daterange`	`\containers\RangeConverter` with `datetime\DateConverter`	instance of `DateTimeRange`	instance of `DateTimeRange` or compatible `array`
`tsrange`	`\containers\RangeConverter` with `datetime\TimeStampConverter`
`tstzrange`	`\containers\RangeConverter` with `datetime\TimeStampTzConverter`
`int4multirange`, `int8multirange`	`\containers\MultiRangeConverter` with `IntegerConverter`	instance of `NumericMultiRange`	instance of `NumericMultiRange` or compatible `array`
`nummultirange`	`\containers\MultiRangeConverter` with `NumericConverter`	instance of `NumericMultiRange`	instance of `NumericMultiRange` or compatible `array`
`datemultirange`	`\containers\MultiRangeConverter` with `datetime\DateConverter`	instance of `DateTimeMultiRange`	instance of `DateTimeMultiRange` or compatible `array`
`tsmultirange`	`\containers\MultiRangeConverter` with `datetime\TimeStampConverter`
`tstzmultirange`	`\containers\MultiRangeConverter` with `datetime\TimeStampTzConverter`

Classes mapped to database types

Passing instances of the below classes (sad_spirit\pg_wrapper\types namespace prefix is assumed for all names that do not start with a backslash) as query parameters does not require specifying parameter types. Converters for database types in the second column will be used.

Class name	Database type
`\DateTimeInterface`	`timestamptz`
`\DateInterval`	`interval`
`Box`	`box`
`Circle`	`circle`
`Line`	`line`
`LineSegment`	`lseg`
`Path`	`path`
`Point`	`point`
`Polygon`	`polygon`
`DateTimeRange`	`tstzrange`
`DateTimeMultiRange`	`tstzmultirange`
`NumericRange`	`numrange`
`NumericMultiRange`	`nummultirange`
`Tid`	`tid`

Converting enums

It is not strictly necessary to convert values of Postgres ENUM types: those are returned as strings and string values are accepted for them as parameters.

However, if one wants a mapping between Postgres enum type

CREATE TYPE meta.syntactic AS ENUM ('foo', 'bar', 'baz');

and PHP’s string-backed counterpart

enum MetaSyntactic: string
{
    case FOO = 'foo';
    case BAR = 'bar';
    case BAZ = 'baz';
}

then setting up the factory in this way

use sad_spirit\pg_wrapper\converters\EnumConverter;

$factory->registerConverter(static function () {
    return new EnumConverter(MetaSyntactic::class);
}, 'syntactic', 'meta');
$factory->registerClassMapping(MetaSyntactic::class, 'syntactic', 'meta');

will allow both receiving values of meta.syntactic type as cases of MetaSyntactic and passing these cases as query parameters without the need to specify types.

`TypeOIDMapper` interface and its implementation

This interface defines methods for

Converting type OIDs to type names and back;
Checking whether the given type OID belongs to some type category.

namespace sad_spirit\pg_wrapper\converters;

interface TypeOIDMapper
{
    public function findOIDForTypeName(string $typeName, ?string $schemaName = null): int|string;
    public function findTypeNameForOID(int|string $oid): array;

    public function isBaseTypeOID(int|string $oid): bool;
    public function isArrayTypeOID(int|string $oid, int|string|null &$baseTypeOid = null): bool;
    public function isCompositeTypeOID(int|string $oid, array|null &$members = null): bool;
    public function isDomainTypeOID(int|string $oid, int|string|null &$baseTypeOid = null): bool;
    public function isRangeTypeOID(int|string $oid, int|string|null &$baseTypeOid = null): bool;
    public function isMultiRangeTypeOID(int|string $oid, int|string|null &$baseTypeOid = null): bool;
}

findOIDForTypeName() / findTypeNameForOID(): Convert OIDs to type names and back. Those should throw InvalidArgumentException if the relevant data can not be found or if the input is ambiguous (unqualified $typeName appearing in several schemas).
isBaseTypeOID(): Returns true if type OID does not belong to any of the special categories, false otherwise.
isArrayTypeOID(), isDomainTypeOID(), isRangeTypeOID(), isMultiRangeTypeOID(): These check whether the type OID belongs to the relevant category, if that is the case then $baseTypeOid will be set to the OID of the base type.
isCompositeTypeOID(): Checks whether the type OID represents a composite type. If that is the case, $members will be set to an array 'field name' => field type OID.

`CachedTypeOIDMapper`

This is the default implementation of TypeOIDMapper, an instance of this will be added to DefaultTypeConverterFactory unless setOIDMapper() is called explicitly.

It implements ConnectionAware and will use the provided Connection instance to load types data from the connected database. It will also use Connection‘s metadata cache, if that was provided via setMetadataCache(), to store types data.

Note

Using some sort of cache is highly recommended in production to prevent metadata lookups from database on each page request.

CachedTypeOIDMapper is pre-populated with info on PostgreSQL’s built-in data types, thus it is usable even without a configured connection. There will also be no need to query database for type metadata if only the standard types are used.

If, however, the database has some custom types (ENUMs count), then the class will have to load type info from the database and / or cache.

Warning

While the class is smart enough to reload metadata from database when OID is not found in the cached data (i.e. a new type was added after cache saved) it is unable to handle changes in composite type structure, so either disable caching of that or invalidate the cache manually.

These additional public methods control caching of composite types

setCompositeTypesCaching(bool $caching): $this

Sets whether structure of composite (row) types will be stored in the cache. If the cached list of columns is used to convert the composite value with different columns the conversion will obviously fail, so that should be set to false if you:

Use composite types in the application;
Expect changes to those types.

getCompositeTypesCaching(): bool

Returns whether composite types’ structure is cached

Why use OIDs and not type names directly?

A valid question is why we need TypeOIDMapper in the first place when pgsql extension provides pg_field_type() that returns the type name for the result column? Or when PDO has PDOStatement::getColumnMeta()?

Result metadata in Postgres contains type OIDs for result columns and these are returned by PQftype function of client library. PHP’s pg_field_type_oid() is a thin wrapper around that function.

Type name data should be fetched separately, quoting documentation of PQftype():

You can query the system table pg_type to obtain the names and properties of the various data types.

Well, PHP’s pg_field_type() does exactly that, it just selects all rows of pg_catalog.pg_type on the first call and later searches the fetched data for type OIDs. However, it only fetches the unqualified type name: no schema name, no properties.

CachedTypeOIDMapper does mostly the same, but fetches more info and allows caching and reusing the type data between requests.

Now, speaking of PDO, the huge problem is that its PDOStatement::getColumnMeta() tries to return all the column’s metadata at once with no means to request e.g. only pgsql:oid field. For Postgres this means running two queries to populate table and native_type fields and the driver doesn’t even cache the results. So that’s potentially two metadata queries for every column in the result!

To be fair, there is a short list of built-in types that do not require a query for native_type in getColumnMeta(), but a query for table will always be run.