.. _tutorial-wrapper:

===============================
Tutorial: Working with database
===============================

In this tutorial we will go through the usual tasks of data retrieval and manipulation, giving special attention
to the package-specific features.

We will be directly using the following classes from ``sad_spirit\pg_wrapper`` namespace:

``Connection``
    This wraps around native ``PgSql\Connection`` and provides methods for connecting / disconnecting, executing
    queries, and managing transactions.

``PreparedStatement``
    This represents a prepared statement and contains methods for executing it either with previously bound values
    or explicitly given ones.

``Result``
    An instance of this wraps around native ``PgSql\Result`` and will be returned by query execution methods
    of the above classes. It provides several ways to iterate over the result but most importantly it automatically
    converts the returned values.

Establishing the connection
===========================

``Connection`` constructor accepts the same connection string that ``pg_connect()`` does. But first let's try passing
an obviously incorrect one:

.. code-block:: php

    use sad_spirit\pg_wrapper\Connection;

    $connection = new Connection('blah-blah');

The above code will not throw an exception because the connection is lazy by default: it will be established when
needed, e.g. to execute a query.

Requesting an eager connection will immediately throw an expected ``ConnectionException``:

.. code-block:: php

    $connection = new Connection('blah-blah', false);

Now let's create a working connection to any database and continue

.. code-block:: php

    // Substitute your own connection settings, obviously
    $connection = new Connection('host=localhost dbname=postgres user=postgres password=secret');

Retrieving data
===============

Using the connection established above, let's get some data from system catalogs:

.. code-block:: php

    $result = $connection->executeParams(
        <<<'SQL'
        select c.relname, array_agg(a.attname order by a.attnum)
        from pg_catalog.pg_class as c, pg_catalog.pg_attribute as a
        where c.oid = any($1) and
              a.attrelid = c.oid and
              a.attnum > 0
        group by c.relname
        SQL
        , [[3602, 3501]],
        ['integer[]']
    );

The ``executeParams()`` method we used wraps around native ``pg_query_params()`` and
allows passing a parametrized query and its parameters separately, greatly lowering the risk of SQL injection.
Unlike ``prepare()`` / ``execute()`` it only requires one round-trip to the database.

We give a type specification (``integer[]``) for the query parameter so that it will be correctly converted
to an array literal. Note that the query stays the same if the array size changes, unlike when using the ``IN()``
predicate.

Let's output the ``$result`` with the following

.. code-block:: php

    foreach ($result->iterateKeyedAssociative('relname') as $k => $v) {
        echo $k . " => ";
        print_r($v);
        echo PHP_EOL;
    }

which prints

.. code-block:: output

    pg_enum => Array
    (
        [0] => oid
        [1] => enumtypid
        [2] => enumsortorder
        [3] => enumlabel
    )

    pg_ts_config => Array
    (
        [0] => oid
        [1] => cfgname
        [2] => cfgnamespace
        [3] => cfgowner
        [4] => cfgparser
    )

We didn't need to provide types for the result fields, the field generated by ``array_agg()`` was automatically
converted to a PHP array using the result metadata.

The ``iterateKeyedAssociative()`` method is a `generator <https://www.php.net/manual/en/language.generators.php>`__
yielding the value of the given column as key and the value of the other column as value
(or an array if there are more than two columns). There are several similar methods and the ``Result`` object itself
is an implementation of ``Iterator``.

Manipulating data
=================

For this part we should create a test table, since writing to system catalog is a bad idea

.. code-block:: postgres

    create schema test;

    create table test.test (
        id integer not null,
        strings text[] not null,

        constraint test_pkey primary key (id)
    );

Assuming the table was created, let's insert several rows into it using prepared statements and transactions

.. code-block:: php

    $statement = $connection->prepare('insert into test.test values ($1, $2)');

    $connection->atomic(function () use ($statement) {
        $statement->executeParams([1, ['test']]);
        $statement->executeParams([2, ['test', 'test']]);
        $statement->executeParams([2, ['test', 'test', 'test']]);
        $statement->executeParams([3, ['additional', 'test']]);
    });

Note that by default we don't have to specify the types for the parameters: those are fetched from the database
when the statement is prepared.

Of course, the above code will fail with a ``ConstraintViolationException``. If you check the contents of
``test.test`` it will have no rows inserted: the code within ``atomic()`` is executed inside a transaction
and that was rolled back.

Let's add a nested ``atomic()`` around the code the *possibly* failing code:

.. code-block:: php

    use sad_spirit\pg_wrapper\Connection;
    use sad_spirit\pg_wrapper\exceptions\server\ConstraintViolationException;

    $statement = $connection->prepare('insert into test.test values ($1, $2)');

    $connection->atomic(function (Connection $connection) use ($statement) {
        $statement->executeParams([1, ['test']]);
        try {
            $connection->atomic(function () use ($statement) {
                $statement->executeParams([2, ['test', 'test']]);
                $statement->executeParams([2, ['test', 'test', 'test']]);
            }, true);
        } catch (ConstraintViolationException $e) {
            echo "Constraint violation: " . $e->getConstraintName() . PHP_EOL;
        }
        $statement->executeParams([3, ['additional', 'test']]);
    });

    print_r(
        $connection->execute('select * from test.test order by id')
            ->fetchAll(\PGSQL_ASSOC)
    );

This time the code runs OK and prints:

.. code-block:: output

    Array
    (
        [0] => Array
            (
                [id] => 1
                [strings] => Array
                    (
                        [0] => test
                    )

            )

        [1] => Array
            (
                [id] => 3
                [strings] => Array
                    (
                        [0] => additional
                        [1] => test
                    )

            )

    )

The nested ``atomic()`` creates a savepoint (this is enabled by setting the second parameter to ``true``)
and rolls back to it once an error happens, allowing the transaction to continue in Postgres.
We were expecting the ``ConstraintViolationException`` and handle it, allowing the outer ``atomic()``
to continue on PHP side.

Finally, we are using the ``fetchAll()`` method of ``Result`` class to receive the whole result as an array.

This concludes the tutorial: we used the most common methods to retrieve and manipulate data and performed some
advanced error handling along the way. The next chapters contain the complete API reference for the classes
used in this tutorial.