README

ZTD Query is a Zero Table Dependency testing library for PHP: it runs the SQL of an application on a real database engine without reading or writing any physical table. Before a query reaches the database, every table it references is replaced by a CTE holding the rows the test has written, and every INSERT, UPDATE, and DELETE is turned into a SELECT whose result is kept in the session, so later queries see the change. Tests therefore need no migrations, seeding, or cleanup, and they can run in parallel against one empty database. This package is the MySQL platform: it reads MySQL statements, rewrites them, and keeps track of the tables the session defines. Use it through the PDO or mysqli adapter.

Requirements

PHP 8.1+
MySQL 8.0–9.1

Installation

With PDO:

composer require --dev k-kinzal/ztd-query-pdo-adapter k-kinzal/ztd-query-mysql

With mysqli:

composer require --dev k-kinzal/ztd-query-mysqli-adapter k-kinzal/ztd-query-mysql

Usage

ZtdPdo extends PDO, so it can be passed wherever the application expects a PDO connection. Tables are created and filled through the same connection; they exist only in the session.

use PDO;
use PHPUnit\Framework\TestCase;
use ZtdQuery\Adapter\Pdo\ZtdPdo;

final class UserQueryTest extends TestCase
{
    public function testSelectsActiveUsers(): void
    {
        $pdo = new ZtdPdo('mysql:host=127.0.0.1;dbname=test', 'root', 'root');
        $pdo->exec('CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(255) NOT NULL, active BOOLEAN NOT NULL)');
        $pdo->exec("INSERT INTO users (id, name, active) VALUES (1, 'Alice', TRUE), (2, 'Bob', FALSE)");

        $statement = $pdo->prepare('SELECT name FROM users WHERE active = ? ORDER BY id');
        $statement->execute([1]);

        self::assertSame(['Alice'], $statement->fetchAll(PDO::FETCH_COLUMN));
    }
}

ZtdPdo::fromPdo($pdo) wraps an existing connection instead of opening a new one, and disableZtd() and enableZtd() switch between the physical database and the session.

Configuration

use ZtdQuery\Adapter\Pdo\ZtdPdo;
use ZtdQuery\Config\UnknownSchemaBehavior;
use ZtdQuery\Config\UnsupportedSqlBehavior;
use ZtdQuery\Config\ZtdConfig;

$config = new ZtdConfig(
    // Unsupported statements: Exception (default), Notice, or Ignore
    unsupportedBehavior: UnsupportedSqlBehavior::Exception,

    // Tables the session does not know: Passthrough to the database (default), or Exception
    unknownSchemaBehavior: UnknownSchemaBehavior::Exception,

    // Per-statement overrides of unsupportedBehavior; the first matching rule wins
    behaviorRules: [
        'CREATE INDEX' => UnsupportedSqlBehavior::Ignore,       // case-insensitive prefix
        '/^SET\s+/i' => UnsupportedSqlBehavior::Notice,          // regular expression
    ],
);

$pdo = new ZtdPdo($dsn, $user, $password, config: $config);

Ignore skips the statement, Notice skips it and raises a PHP notice, and Exception throws an exception.

SQL Support

Statement	MySQL
SELECT, including joins, grouping, set operations, subqueries, CTEs, recursive CTEs, and window functions	Supported
INSERT with VALUES or SELECT	Supported
Upsert	`ON DUPLICATE KEY UPDATE`, `INSERT IGNORE`, `REPLACE`
UPDATE and DELETE	Supported, including multi-table forms and `ORDER BY ... LIMIT`
`RETURNING`	–
TRUNCATE	Supported
CREATE TABLE, DROP TABLE	Supported
ALTER TABLE	Supported
BEGIN, COMMIT, ROLLBACK	Applied to the session: ROLLBACK discards the writes made since BEGIN
Views, indexes, routines, triggers, SET, and server or user administration	Unsupported

Unsupported statements are handled as configured by unsupportedBehavior. The full specification is in docs/spec.md.

License

MIT License. See LICENSE for details.

k-kinzal / ztd-query-mysql

Package info

Security

Statistics