Skip to main content

Changelog - Version 7.0

Status: in development. This document tracks changes landing on the 7.0 branch. Nothing here is released yet, and the contents may still change.

Overview​

Version 7.0 completes the architectural refactoring started in 6.0: the query methods deprecated on the drivers are now removed, making DatabaseExecutor the single API for executing queries and commands.

On top of that, 7.0 introduces a generic observer mechanism for the DatabaseExecutor and, built on it, a Journal that records INSERT/UPDATE/DELETE operations with old and new values — designed for restoring database state between functional tests.

Breaking Changes​

Removed: Direct Query Methods on Drivers​

The following methods — deprecated since 6.0 — were removed from DbDriverInterface, all drivers (DbPdoDriver and subclasses, DbOci8Driver) and DatabaseRouter:

Removed MethodReplacement
$dbDriver->getIterator($sql, $params)DatabaseExecutor::using($dbDriver)->getIterator($sql, $params)
$dbDriver->getScalar($sql, $params)DatabaseExecutor::using($dbDriver)->getScalar($sql, $params)
$dbDriver->execute($sql, $params)DatabaseExecutor::using($dbDriver)->execute($sql, $params)
$dbDriver->executeAndGetId($sql, $params)DatabaseExecutor::using($dbDriver)->executeAndGetId($sql, $params)
$dbDriver->getAllFields($table)DatabaseExecutor::using($dbDriver)->getAllFields($table)

Transaction methods (beginTransaction, commitTransaction, rollbackTransaction, etc.) and low-level methods (prepareStatement, executeCursor, getDriverIterator, etc.) remain on the driver.

Migration:

<?php
// 6.x (deprecated) - no longer works in 7.0
$iterator = $dbDriver->getIterator('SELECT * FROM users');

// 7.0
$executor = DatabaseExecutor::using($dbDriver);
$iterator = $executor->getIterator('SELECT * FROM users');

See Deprecated Features for the complete migration checklist.

Added: DbDriverInterface::getStatementFields()​

getStatementFields(mixed $statement): array returns the column names of an executed statement. DatabaseExecutor::getAllFields() now delegates to it instead of inspecting the statement type itself. Custom DbDriverInterface implementations must add the method.

This fixes getAllFields() on the OCI8 driver, which always returned an empty array: the columns were read from the first row of a query that returns no rows.

On Cloudflare D1, getAllFields() now throws NotAvailableException instead of returning an empty array.

Added: SqlDialectInterface::getPingSql()​

getPingSql(): string returns the statement the drivers use to check that the connection is alive: SELECT 1, or SELECT 1 FROM DUAL on Oracle. Custom SqlDialectInterface implementations that do not extend BaseSqlDialect must add the method.

Changed: table metadata returns objects, and gains keys, indexes and foreign keys​

SqlDialectInterface::getTableMetadata() returns array<string, ColumnMetadata> (still keyed by the lower-case column name) instead of an array of arrays. Each ColumnMetadata has the same fields as the old array, as read-only public properties, plus three new ones:

  • primaryKey: the column is part of the primary key.
  • autoIncrement: the database fills in the value (auto_increment, IDENTITY, SERIAL/identity, or SQLite's single-column INTEGER PRIMARY KEY, which is an alias of the rowid).
  • generated: the database computes the value from an expression (a generated, computed or virtual column), so it cannot be written. On SQLite the metadata now comes from PRAGMA table_xinfo, because table_info leaves generated columns out.

On PostgreSQL, dbType was empty, with a strtolower() deprecation, for a type with neither a length nor a precision, such as timestamp; it is now the type name (timestamp without time zone).

Two methods are added to the interface, implemented for MySQL, SQLite (and D1), PostgreSQL, SQL Server and Oracle:

  • getTableIndexes($executor, $table) returns a list<IndexMetadata> (name, columns, unique), ordered by name, without the index of the primary key.
  • getTableForeignKeys($executor, $table) returns a list<ForeignKeyMetadata> (columns, parentTable, parentColumns).

Code reading $metadata['id']['required'] becomes $metadata['id']->required. Custom SqlDialectInterface implementations that do not extend BaseSqlDialect must add the two methods; those that do extend it must return ColumnMetadata from parseColumnMetadata() (newColumnMetadata() builds it).

Changed: the PDO OCI driver (PdoOci)​

PdoOci was rebuilt on DbPdoDriver and is now covered by testsdb/PdoOciTest.php. It could not connect before: it registered the scheme oracle and used it as the PDO prefix, which is oci.

  • It answers to both oci:// and oracle://.
  • getUri() returns the URI the driver was created with.
  • The codepage parameter is sent as the PDO charset and defaults to AL32UTF8.
  • A trailing ; is removed from the statement, as the OCI8 driver does.
  • PdoOci::getTnsString() was removed; use DbOci8Driver::getTnsString().
  • isConnected() and reconnect() work on Oracle (they pinged with SELECT 1, which Oracle rejects).

See Installing the PHP database extensions for oci8, pdo_oci, pdo_dblib and pdo_sqlsrv, and for the NLS_LANG requirement of PDO OCI.

Fixed: sqlDate() follows the documented format letters​

The letters of sqlDate() are documented in docs/helper.md (D day with a leading zero, d without, M month name, m month number, y two-digit year, h 12-hour hour). Several dialects did not follow them:

  • SQL Server used patterns FORMAT() does not know (YYYY, YY), so sqlDate('Y-m-d') gave YYYY-10-16, and m, d, M and h gave another field. It now uses the .NET specifiers (yyyy, MM, d, MMMM, hh, tt).
  • PostgreSQL and Oracle gave d with a leading zero and M as the abbreviated month (Mar): d is now 5, and M is March.
  • MySQL gave M as the abbreviated month: it is now March.

SQLite is unchanged: its strftime() has no month names nor a two-digit year, and its day without zero and 12-hour hour only exist in recent versions. A date formatted with M or d on the other databases reads differently than before.

Fixed: the scheme aliases of the PDO drivers​

PdoPgsql declares postgres and postgresql next to pgsql, and PdoMysql declares mariadb next to mysql, but a connection through an alias always failed: Extension 'pdo_postgres' is not loaded. PdoObj takes the PDO driver and the extension to check from the scheme. DbPdoDriver now passes PdoObj the driver's main scheme (the first of schema()), so getUri() of such a driver returns the URI with that scheme.

Fixed: spaces in a PdoLiteral connection string​

PdoLiteral encoded the connection string with urlencode(), which turns a space into +. byjg/uri 7.0 reads + as a literal plus sign, so the string reached PDO with + in place of every space. It is now encoded with rawurlencode().

Changed: a password without a user is not passed to SQL Server and Oracle​

With byjg/uri 7.0 an empty user removes the user information, so PdoSqlsrv, PdoDblib and PdoOci no longer pass the password of a password-only URI (sqlsrv://:pw@host/db): PDO gets no user and no password. These databases cannot log in with a password only (with no user they use integrated/external authentication).

Fixed: DatabaseRouter route order and literal table names​

  • Routes were stored by pattern, so adding the same pattern twice (e.g. addRouteForRead('a', 'users') then addRouteForRead('b', 'users')) replaced the first route: the later name took the earlier one's position. Routes are now kept in the order added and the first match wins, as documented; the second route with the same pattern never matches.
  • The table name of addRouteForSelect/Insert/Update/Delete/Read/Write/Table() and the field and value of addRouteForFilter() went into the regular expression unescaped: a table a.b also matched axb, and a / broke the expression. They are now matched literally (preg_quote()).
  • Routes are now matched case-insensitively. Before, the SQL was lowercased but the patterns were not, so a route for an uppercase table (addRouteForRead('x', 'Users')) or a custom pattern with capitals (addCustomRoute('x', 'GROUP\s+BY')) never matched. As a consequence, the $value of addRouteForFilter() is matched case-insensitively too ('A@B' matches 'a@b').
  • The protected $routes property is now a list of [regEx, routeName] pairs.

Removed: Factory::getDbRelationalInstance()​

Deprecated since 5.0. Use Factory::getDbInstance(), which takes the same connection string (or a Uri). In a dependency injection configuration the method is named as a string, so Psalm does not report it and it fails only when the container builds the driver:

// Before
->withFactoryMethod("getDbRelationalInstance", [Param::get('DBDRIVER_CONNECTION')])
// After
->withFactoryMethod("getDbInstance", [Param::get('DBDRIVER_CONNECTION')])

Enum cases renamed to PascalCase​

Enum cases follow the ByJG standard and are written in PascalCase. Both enums below are pure (non-backed) enums, so any code that names a case (or reads ->name) has to be updated.

EnumBefore (6.x)After (7.0)
IsolationLevelEnumREAD_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLEReadUncommitted, ReadCommitted, RepeatableRead, Serializable
TransactionStageEnumbegin, commit, rollbackBegin, Commit, Rollback

New Features​

1. Executor Observers​

Generic event mechanism to observe queries and commands executed through the DatabaseExecutor:

  • New DatabaseEventObserverInterface attachable with DatabaseExecutor::addObserver() / removable with removeObserver()
  • Events: BeforeQuery, AfterQuery, BeforeExecute, AfterExecute (DatabaseEventTypeEnum)
  • Each notification carries a DatabaseEvent with the SqlStatement (SQL and parameters), the executor and the result
  • Enables decoupled auditing, metrics and query logging
  • Queries answered from the cache do not fire query events
  • No measurable overhead when no observer is attached

Documentation: Executor Observers

2. Journal (Record and Restore Changes)​

Records all INSERT, UPDATE and DELETE statements with the row values before and after each operation:

  • JournalRecorder watches all tables or a specific set (forAllTables() / forTables())
  • Configurable primary keys: withPrimaryKey($table, $column) and withDefaultPrimaryKey($column) (default id)
  • JournalRestorer replays the journal in reverse, restoring the previous database state
  • Optional strict mode (->strict()) throws a JournalException on statements that cannot be journaled/restored, instead of skipping them silently
  • Designed for functional tests (record on setUp, restore on tearDown)

Documentation: Journal

3. Cloudflare D1 Driver​

New DbD1Driver (d1:// scheme) talking to the Cloudflare D1 REST API:

  • Connection string: d1://{account_id}:{api_token}@api.cloudflare.com/{database_id}
  • Works over any PSR-18 HTTP client; uses byjg/webrequest when none is injected
  • Named parameters are translated to the positional ? placeholders the API expects
  • executeAndGetId() reads the generated id from D1's meta.last_row_id instead of issuing a second query, which would run on a different connection
  • SQLite semantics via the new D1Dialect
  • Routing observability: isServedByPrimary(), getServedByRegion(), getServedByColo() and getLastMeta() expose what D1 reports about the last statement

Documentation: Driver: Cloudflare D1

4. DuckDB Driver​

New PdoDuckdb (duckdb:// scheme) for DuckDB, an embedded database for analytics, through the pdo_duckdb extension:

  • Connection string: duckdb:///path/to/file.duckdb, or duckdb:// for an in-memory database
  • DuckdbDialect extends PgsqlDialect. executeAndGetId() reads the id from the sequence in the default value of the primary key; sqlDate() uses strftime(); table metadata, indexes and foreign keys come from DuckDB's catalog functions
  • PdoPdo and PdoLiteral detect a duckdb connection and use DuckdbDialect
  • testsdb/install-db-extensions.sh duckdb builds pdo_duckdb on Alpine against the musl build of libduckdb

Documentation: Driver: DuckDB

Bug Fixes​

  • DatabaseRouter: routes with the same pattern no longer replace each other, table names, filter fields and values are matched literally, and routes match case-insensitively (uppercase table names and custom patterns with capitals now match). See "Fixed: DatabaseRouter route order and literal table names" under Breaking Changes.
  • DbOci8Driver: a URI without a user or password no longer passes null to oci_connect() (PHP 8.1+ deprecation "Passing null to parameter of type string"); the missing part is sent as an empty string.
  • DbD1Driver: an account id, API token or database id of "0" is no longer rejected as missing.
  • PDO drivers and DbOci8Driver: a boolean parameter is bound as 1/0. false was bound as '', which MySQL, PostgreSQL and integer columns rejected, SQLite stored as text and Oracle stored as NULL.

Known Limitations​

  • The Journal recognizes only single-table DML statements; multi-statement SQL, INSERT ... SELECT, multi-table UPDATEs and CTEs are not journaled (strict mode fails loudly on them instead).
  • Observers (and therefore the Journal) are bound to a specific executor instance: writes made through a different DatabaseExecutor are not observed.
  • Cloudflare D1 has no interactive transactions (beginTransaction() throws NotAvailableException), no multiple rowsets and no getAllFields(); the Journal cannot track INSERTs on it.
  • The D1 Sessions API is only available through the Workers binding, so the driver cannot provide sequential consistency (read-your-own-writes) on databases with read replication enabled. See Driver: Cloudflare D1 for the full list.
  • DuckDB: one connection per database file in a process (pdo_duckdb opens a separate database per connection, and they do not see each other's commits), no FOR UPDATE, no isolation levels, and generated columns are not reported as generated. See Driver: DuckDB.

Upgrade Path from 6.x to 7.0​

  1. Migrate every direct driver query call to DatabaseExecutor (see the table above). Code already using DatabaseExecutor — the recommended API since 6.0 — needs no changes.
  2. Update the composer constraint:
{
  "require": {
    "byjg/anydataset-db": "^7.0"
  }
}

Requirements​

  • PHP 8.3, 8.4, 8.5 and 8.6 are now supported: "php": ">=8.3 <8.7". The previous <8.6 upper bound excluded PHP 8.6, since <8.6 is exclusive.

ByJG dependencies​

  • byjg/anydataset is now ^7.0.
  • byjg/cache-engine is now ^7.0.
  • byjg/uri is now ^7.0.
  • byjg/webrequest is now ^7.0.

While 7.0 is unreleased these resolve to 7.0.x-dev from each component's 7.0 branch, via minimum-stability: dev with prefer-stable: true.

Toolchain​

  • PHPUnit updated to ^12.5.

  • Psalm is installed as psalm/phar instead of vimeo/psalm.

    vimeo/psalm lists the PHP versions it supports and no published release includes 8.6, so as a dev dependency it made composer install fail on the 8.6 build job before any test ran. psalm/phar requires only php ^8.2 and bundles its own dependencies, so it installs on every PHP version in the matrix and cannot conflict with the project's. Psalm itself still refuses to run on 8.6, which is why the Psalm job uses 8.5. composer psalm runs it.

  • PHPUnit 13 is deliberately not used. It requires PHP >=8.4.1, breaking the 8.3 floor.

Continuous Integration​

  • The build matrix now includes PHP 8.6.
  • The Psalm job runs on PHP 8.5.
  • The build installs pdo_sqlsrv, so the SQL Server tests also run through the Microsoft driver (not on PHP 8.6: pdo_sqlsrv 5.13 does not compile against it yet).
  • Oracle is not tested in CI. Run testsdb/Oci8Test.php and testsdb/PdoOciTest.php locally; see Running Tests.

Housekeeping​

  • phpunit.xml.dist renamed to phpunit.xml.