Aura for PHP

1.1.6. 3.x Upgrade Notes

The vast majority of changes and breaks from the 2.x version are "under the hood," so to speak. The main functionality methods (query(), exec(), perform(), fetch*(), yield*(), etc.) are unchanged and work just the same as in 2.x.

The remaining changes and breaks can be categorized as:

1.1.6.1. Rebuilder and Parsers

The 2.x Rebuilder has been redone entirely, and now provides separate parsers for the separate database drivers (mysql, pgsql, sqlite, and sqlsrv). This originated from #104, along with #107 and #111, all of which are now resolved. It took a great deal of time and effort to complete, with several approaches attempted.

1.1.6.1.1. Array Placeholders

Given this code ...

$stm = "SELECT * FROM test WHERE foo IN (:foo)";
$sth = $pdo->perform($stm, [
    'foo' => ['bar', 'baz', 'dib'];
]);
echo $sth->queryString;

... 2.x would quote and replace the array values directly into the query:

SELECT * FROM test WHERE foo IN ('bar', 'baz', 'dib')

Now, under 3.x, the placeholder is expanded to match the number of array keys, so that there are multiple placeholders:

SELECT * FROM test WHERE foo IN (:foo_0, :foo_1, :foo_2)

The array values passed to the query will also be bound individually. (If you profile the query, you will see :foo_0 (et al.) in the bindings.)

1.1.6.1.2. Sequential Placeholders

Given this code ...

$stm = 'SELECT * FROM test WHERE foo = ? AND bar = ?';
$sth = $pdo->perform($stm, [
    'foo_value',
    'bar_value',
]);
echo $sth->queryString;

... the 2.x rebuilder would leave ? placeholders alone:

SELECT * FROM test WHERE foo = ? AND bar = ?

Now, under 3.x, sequential placeholders are converted to named placeholders; the name corresponds to the sequential position:

SELECT * FROM test WHERE foo = :__1 AND bar = :__2

The sequential values passed to the query will be bound to the named replacements.

This helps to correct errors associated with binding sequential and named placeholders together, and in identifiying bound values in longer query strings.

1.1.6.1.3. Repetition of Named Placeholders

With 3.x, you can use the same placeholder multiple times. Given this code ...

$stm = 'SELECT * FROM test WHERE foo = :val OR bar = :val';
$sth = $pdo->perform($stm, [
    'val' => 'whatever',
]);
echo $sth->queryString;

... the 3.x parser modifies repeated placeholders by suffixing them each time they reappear, and binds the needed values automatically:

SELECT * FROM test WHERE foo = :val OR bar = :val__1

1.1.6.1.4. Custom Parsers

You can inject your own parsers as well via ExtendedPdo::setParser(). See the src/Parser/ directory for examples of the existing parsers.

1.1.6.2. Profiling and Logging

The 2.x version used a custom profiler system, retaining profiles as array constructs and returning them as such. You needed to inject it yourself.

use Aura\Sql\Profiler;

$pdo->setProfiler(new Profiler());

// ...
// query(), fetch(), beginTransaction()/commit()/rollback() etc.
// ...

// retrieve the profile information as a series of arrays
$profiles = $pdo->getProfiler()->getProfiles();

Under 3.x, the profiler interface itself remains custom, but it is now backed with the PSR-3 logger interface. This means you can use any PSR-3 implementation to capture profiler information. This means that the profiler emits strings, rather than arrays, for the logger to capture.

A profiler is now automatically set on the ExtendedPdo instance, and uses an in-memory logger by default for debugging purposes.

// no need to set a profiler, but you do need to activate it:
$pdo->getProfiler()->setActive();

// ...
// query(), fetch(), beginTransaction()/commit()/rollback() etc.
// ...

// retrieve the profiler logs from the default MemoryLogger
$messages = $pdo->getProfiler()->getLogger()->getMessages();

You can set the log message format and log level through the profiler, to capture just the information you want.

You can set your own profiler and backing logger with the setProfiler() method.

use Aura\Sql\Profiler\Profiler;

$myLogger = new Psr3LoggerImplementation();
$pdo->setProfiler(new Profiler($myLogger));

Finally, under 2.x, ExtendedPdo would profile every function call. Unless you examined very carefully, a call to prepare() followed by perform() looked like 2 executions of the same query. To make it easier to examine logs, the 3.x version does not profile every function call (e.g., prepare() and prepareWithValues() are no longer logged).

1.1.6.3. Instantiation

Under 2.x you would do this:

$pdo = new ExtendedPdo(
    'pgsql:host=localhost;dbname=test',
    'username',
    'password',
    array(), // driver options as key-value pairs
    array()  // attributes as key-value pairs
);

It appears the last argument ended up being extraneous. The 3.x ExtendedPdo changes the last argument to this:

$pdo = new ExtendedPdo(
    'pgsql:host=localhost;dbname=test',
    'username',
    'password',
    [], // driver options as key-value pairs
    []  // queries to execute after connection
);

This lets you execute queries at connection time; e.g., to make connection configuration changes that cannot be made as driver options/attributes. For example:

$pdo = new ExtendedPdo(
    'pgsql:host=localhost;dbname=test',
    'username',
    'password',
    [
        PDO::ATTR_CASE => PDO::CASE_NATURAL,
    ],
    [
        "SET NAMES 'utf8'",
    ]
)

1.1.6.4. Miscellaneous