.. _tutorial-types:

===================================
Tutorial: Converting Postgres types
===================================

In this tutorial we will convert string representations of Postgres types to PHP values and back, configuring
the converters as needed to properly handle the input.

Complex type representations in Postgres
========================================

Postgres supports complex data types that can be combined in creative ways. We'll first explore the two ways used
to represent values for such types.

Let's connect to any database (e.g. the default ``postgres`` one) using ``psql`` console client
and execute the following query

.. code-block:: postgres

    select row(
        array['string', 'str''i"ng'],
        point(55.75, 37.61),
        daterange('2014-09-28', '2025-02-25'),
        age('2025-02-25', '2014-09-28')
    ) as complex;

We are creating a row (composite) type literal with its fields being an array of text, geometric point, range of
dates and an interval (generated by a function). The values for complex types in the query are represented by
"constructors", so the query itself looks readable. However, its output will look somewhat like this:

.. code-block:: output

                                                 complex
    --------------------------------------------------------------------------------------------------
     ("{string,""str'i\\""ng""}","(55.75,37.61)","[2014-09-28,2025-02-25)","10 years 4 mons 27 days")

Note that your output may, in fact, look different. That's because the representation of datetime and interval fields
may change based on ``DateStyle`` and ``IntervalStyle`` server settings, respectively. Let's change these to some fixed
values with the following statements

.. code-block:: postgres

    set DateStyle = 'postgres,dmy';
    set IntervalStyle = 'sql_standard';

and re-run the query that returns the row literal:

.. code-block:: output

                                               complex
    ---------------------------------------------------------------------------------------------
     ("{string,""str'i\\""ng""}","(55.75,37.61)","[28-09-2014,25-02-2025)","+10-4 +27 +0:00:00")

This time you should get exactly the above output. What we see here is the string representation of composite type
(elements enclosed in parentheses and separated by commas) containing string representations of its fields.
Note the quoting and escaping that was applied, especially to the second element of the array.

Converting string literals to PHP values
========================================

Both PHP extensions that are used for talking to PostgreSQL (``pgsql`` and ``PDO_pgsql``) will return the same
string representation for the ``complex`` field if the above query is run through them.

Let's take that string representation and convert it using the classes available in ``pg_wrapper`` package.
We will use the ``DefaultTypeConverterFactory`` class to create the necessary converter and then pass the string to it:

.. code-block:: php

    use sad_spirit\pg_wrapper\converters\DefaultTypeConverterFactory;

    $converter = (new DefaultTypeConverterFactory())->getConverterForTypeSpecification([
        'one'   => 'text[]',
        'two'   => 'point',
        'three' => 'daterange',
        'four'  => 'interval'
    ]);
    $complex = $converter->input(<<<'LITERAL'
        ("{string,""str'i\\""ng""}","(55.75,37.61)","[28-09-2014,25-02-2025)","+10-4 +27 +0:00:00")
        LITERAL
    ));

``getConverterForTypeSpecification()`` method takes a manually prepared specification for some database type
and returns an appropriate ``TypeConverter`` implementation. In this case the type specification for a composite type
is an array:

 * Its keys will be used in the resultant array. As you can see above, composite type literals in Postgres do not
   contain any info on field names.
 * Its values specify types for fields of the composite type, these specifications are simply type names.

Note also the ``text[]`` specification for the first field containing an array.

The above code, however, will fail with a ``TypeConversionException``: the ``DateConverter`` instance used for
converting ``daterange`` expects a date in ``ISO`` format by default. Normally it can use the database connection to
properly set up the input format, but this time we'll configure it manually:

.. code-block:: php

    use sad_spirit\pg_wrapper\converters\containers\RangeConverter;
    use sad_spirit\pg_wrapper\converters\datetime\DateConverter;
    use sad_spirit\pg_wrapper\converters\DefaultTypeConverterFactory;

    $dateConverter = new DateConverter();
    $dateConverter->setDateStyle('Postgres,dmy');

    $converter = (new DefaultTypeConverterFactory())->getConverterForTypeSpecification([
        'one'   => 'text[]',
        'two'   => 'point',
        'three' => new RangeConverter($dateConverter),
        'four'  => 'interval'
    ]);

    $complex = $converter->input(<<<'LITERAL'
        ("{string,""str'i\\""ng""}","(55.75,37.61)","[28-09-2014,25-02-2025)","+10-4 +27 +0:00:00")
        LITERAL
    );
    print_r($complex);

As you may notice, the converters are combined in pretty much the same way as the types themselves. Thus adding
a converter for a new base type will immediately allow using it for complex types (arrays, ranges, composites)
containing this base type.

Now the code works as expected, outputting the following (some parts are omitted for brevity):

.. code-block:: output

    Array
    (
        [one] => Array
            (
                [0] => string
                [1] => str'i"ng
            )

        [two] => sad_spirit\pg_wrapper\types\Point Object
            (
                [x] => 55.75
                [y] => 37.61
            )

        [three] => sad_spirit\pg_wrapper\types\DateTimeRange Object
            (
                [lower] => DateTimeImmutable Object
                    (
                        [date] => 2014-09-28 00:00:00.000000
                        ...
                    )
                ...
            )

        [four] => DateInterval Object
            (
                [y] => 10
                [m] => 4
                [d] => 27
                ...
            )

    )

Note how the outer composite type is converted to an associative array with the keys from type specification,
the array on Postgres side is mapped to array on PHP side. Postgres dates and intervals are represented by native
``DateTimeImmutable`` and ``DateInterval`` native classes, while types that do not have native equivalents are
represented by ``Point`` and ``DateTimeRange`` classes from the package.

Converting PHP values to string literals
========================================

While it is possible to use constructor expressions for complex types when writing queries manually,
you can't use these for parameters in parametrized queries. Fortunately, type converters implement an ``output()``
method that complements ``input()`` and allows generating string literals from PHP values. You can pass these
to ``pg_execute()``, ``pg_query_params()``, and similar methods.

Let's continue the above example

.. code-block:: php

    use sad_spirit\pg_wrapper\types\DateTimeRange;

    $complex['one'] = [['a', 'b'], ['c', 'd"e']];
    // DateTimeRange is immutable, create a new instance
    $complex['three'] = new DateTimeRange(
        $complex['three']->lower,
        $complex['three']->upper,
        true,
        true
    );

    echo $converter->output($complex);

This prints

.. code-block:: output

    ("{{""a"",""b""},{""c"",""d\\""e""}}","(55.75,37.61)","[""2014-09-28"",""2025-02-25""]","P10Y4M27D")

Note that date and time converters always output in ISO format to prevent ambiguity.

This concludes the tutorial, we have used the type conversion classes on a complex type composed of the built-in
types. The next chapters contain the complete API reference for the converters and describe the means available
to convert custom base types.