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:

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:

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

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

// 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:

$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

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

which prints

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 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

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

$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 possibly failing code:

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:

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.