cakephp/tests/TestCase/Database/ConnectionTest.php

<?php
declare(strict_types=1);

/**
 * CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
 * Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
 *
 * Licensed under The MIT License
 * For full copyright and license information, please see the LICENSE.txt
 * Redistributions of files must retain the above copyright notice.
 *
 * @copyright     Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
 * @link          https://cakephp.org CakePHP(tm) Project
 * @since         3.0.0
 * @license       https://opensource.org/licenses/mit-license.php MIT License
 */
namespace Cake\Test\TestCase\Database;

use Cake\Cache\Engine\NullEngine;
use Cake\Collection\Collection;
use Cake\Core\App;
use Cake\Database\Connection;
use Cake\Database\Driver;
use Cake\Database\Driver\Mysql;
use Cake\Database\Driver\Sqlite;
use Cake\Database\Driver\Sqlserver;
use Cake\Database\Exception\MissingConnectionException;
use Cake\Database\Exception\MissingDriverException;
use Cake\Database\Exception\MissingExtensionException;
use Cake\Database\Exception\NestedTransactionRollbackException;
use Cake\Database\Log\LoggingStatement;
use Cake\Database\Log\QueryLogger;
use Cake\Database\Query;
use Cake\Database\Schema\CachedCollection;
use Cake\Database\StatementInterface;
use Cake\Datasource\ConnectionManager;
use Cake\Log\Log;
use Cake\TestSuite\TestCase;
use DateTime;
use Error;
use Exception;
use InvalidArgumentException;
use PDO;
use ReflectionMethod;
use ReflectionProperty;
use TestApp\Database\Driver\DisabledDriver;
use TestApp\Log\Engine\TestBaseLog;
use function Cake\Core\namespaceSplit;

/**
 * Tests Connection class
 */
class ConnectionTest extends TestCase
{
    /**
     * @var array<string>
     */
    protected $fixtures = ['core.Things'];

    /**
     * Where the NestedTransactionRollbackException was created.
     *
     * @var int
     */
    protected $rollbackSourceLine = -1;

    /**
     * Internal states of nested transaction.
     *
     * @var array
     */
    protected $nestedTransactionStates = [];

    /**
     * @var bool
     */
    protected $logState;

    /**
     * @var \Cake\Datasource\ConnectionInterface
     */
    protected $connection;

    /**
     * @var \Cake\Database\Log\QueryLogger
     */
    protected $defaultLogger;

    public function setUp(): void
    {
        parent::setUp();
        $this->connection = ConnectionManager::get('test');
        $this->defaultLogger = $this->connection->getLogger();

        $this->deprecated(function () {
            $this->logState = $this->connection->isQueryLoggingEnabled();
            $this->connection->disableQueryLogging();
        });

        static::setAppNamespace();
    }

    public function tearDown(): void
    {
        parent::tearDown();
        $this->connection->disableSavePoints();
        $this->connection->setLogger($this->defaultLogger);
        $this->deprecated(function () {
            $this->connection->enableQueryLogging($this->logState);
        });

        Log::reset();
        unset($this->connection);
    }

    /**
     * Auxiliary method to build a mock for a driver so it can be injected into
     * the connection object
     *
     * @return \Cake\Database\Driver|\PHPUnit\Framework\MockObject\MockObject
     */
    public function getMockFormDriver()
    {
        $driver = $this->getMockBuilder(Driver::class)->getMock();
        $driver->expects($this->once())
            ->method('enabled')
            ->will($this->returnValue(true));

        return $driver;
    }

    /**
     * Tests connecting to database
     */
    public function testConnect(): void
    {
        $this->deprecated(function () {
            $this->assertTrue($this->connection->connect());
            $this->assertTrue($this->connection->isConnected());
        });
    }

    /**
     * Tests creating a connection using no driver throws an exception
     */
    public function testNoDriver(): void
    {
        $this->expectException(MissingDriverException::class);
        $this->expectExceptionMessage('Could not find driver `` for connection ``.');
        $connection = new Connection([]);
    }

    /**
     * Tests creating a connection using an invalid driver throws an exception
     */
    public function testEmptyDriver(): void
    {
        $this->expectException(Error::class);
        $connection = new Connection(['driver' => false]);
    }

    /**
     * Tests creating a connection using an invalid driver throws an exception
     */
    public function testMissingDriver(): void
    {
        $this->expectException(MissingDriverException::class);
        $this->expectExceptionMessage('Could not find driver `\Foo\InvalidDriver` for connection ``.');
        $connection = new Connection(['driver' => '\Foo\InvalidDriver']);
    }

    /**
     * Tests trying to use a disabled driver throws an exception
     */
    public function testDisabledDriver(): void
    {
        $this->expectException(MissingExtensionException::class);
        $this->expectExceptionMessage(
            'Database driver DriverMock cannot be used due to a missing PHP extension or unmet dependency. ' .
            'Requested by connection "custom_connection_name"'
        );
        $mock = $this->getMockBuilder(Mysql::class)
            ->onlyMethods(['enabled'])
            ->setMockClassName('DriverMock')
            ->getMock();
        $connection = new Connection(['driver' => $mock, 'name' => 'custom_connection_name']);
    }

    /**
     * Tests that the `driver` option supports the short classname/plugin syntax.
     */
    public function testDriverOptionClassNameSupport(): void
    {
        $connection = new Connection(['driver' => 'TestDriver']);
        $this->assertInstanceOf('TestApp\Database\Driver\TestDriver', $connection->getDriver());

        $connection = new Connection(['driver' => 'TestPlugin.TestDriver']);
        $this->assertInstanceOf('TestPlugin\Database\Driver\TestDriver', $connection->getDriver());

        [, $name] = namespaceSplit(get_class($this->connection->getDriver()));
        $connection = new Connection(['driver' => $name]);
        $this->assertInstanceOf(get_class($this->connection->getDriver()), $connection->getDriver());
    }

    /**
     * Test providing a unique read config only creates separate drivers.
     */
    public function testDifferentReadDriver(): void
    {
        $this->skipIf(!extension_loaded('pdo_sqlite'), 'Skipping as SQLite extension is missing');
        $config = ConnectionManager::getConfig('test') + ['read' => ['database' => 'read_test.db']];
        $connection = new Connection($config);
        $this->assertNotSame($connection->getDriver(Connection::ROLE_READ), $connection->getDriver(Connection::ROLE_WRITE));
        $this->assertSame(Connection::ROLE_READ, $connection->getDriver(Connection::ROLE_READ)->getRole());
        $this->assertSame(Connection::ROLE_WRITE, $connection->getDriver(Connection::ROLE_WRITE)->getRole());
    }

    /**
     * Test providing a unique write config only creates separate drivers.
     */
    public function testDifferentWriteDriver(): void
    {
        $this->skipIf(!extension_loaded('pdo_sqlite'), 'Skipping as SQLite extension is missing');
        $config = ConnectionManager::getConfig('test') + ['write' => ['database' => 'read_test.db']];
        $connection = new Connection($config);
        $this->assertNotSame($connection->getDriver(Connection::ROLE_READ), $connection->getDriver(Connection::ROLE_WRITE));
        $this->assertSame(Connection::ROLE_READ, $connection->getDriver(Connection::ROLE_READ)->getRole());
        $this->assertSame(Connection::ROLE_WRITE, $connection->getDriver(Connection::ROLE_WRITE)->getRole());
    }

    /**
     * Test providing the same read and write config uses a shared driver.
     */
    public function testSameReadWriteDriver(): void
    {
        $this->skipIf(!extension_loaded('pdo_sqlite'), 'Skipping as SQLite extension is missing');
        $config = ConnectionManager::getConfig('test') + ['read' => ['database' => 'read_test.db'], 'write' => ['database' => 'read_test.db']];
        $connection = new Connection($config);
        $this->assertSame($connection->getDriver(Connection::ROLE_READ), $connection->getDriver(Connection::ROLE_WRITE));
        $this->assertSame(Connection::ROLE_WRITE, $connection->getDriver(Connection::ROLE_READ)->getRole());
        $this->assertSame(Connection::ROLE_WRITE, $connection->getDriver(Connection::ROLE_WRITE)->getRole());
    }

    public function testDisabledReadWriteDriver(): void
    {
        $this->skipIf(!extension_loaded('pdo_sqlite'), 'Skipping as SQLite extension is missing');
        $config = ['driver' => DisabledDriver::class] + ConnectionManager::getConfig('test');

        $this->expectException(MissingExtensionException::class);
        $connection = new Connection($config);
    }

    /**
     * Tests that connecting with invalid credentials or database name throws an exception
     */
    public function testWrongCredentials(): void
    {
        $this->deprecated(function () {
            $config = ConnectionManager::getConfig('test');
            $this->skipIf(isset($config['url']), 'Datasource has dsn, skipping.');
            $connection = new Connection(['database' => '/dev/nonexistent'] + ConnectionManager::getConfig('test'));

            $e = null;
            try {
                $connection->connect();
            } catch (MissingConnectionException $e) {
            }

            $this->assertNotNull($e);
            $this->assertStringStartsWith(
                sprintf(
                    'Connection to %s could not be established:',
                    App::shortName(get_class($connection->getDriver()), 'Database/Driver')
                ),
                $e->getMessage()
            );
            $this->assertInstanceOf('PDOException', $e->getPrevious());
        });
    }

    public function testConnectRetry(): void
    {
        $this->skipIf(!ConnectionManager::get('test')->getDriver() instanceof Sqlserver);

        $connection = new Connection(['driver' => 'RetryDriver']);
        $this->assertInstanceOf('TestApp\Database\Driver\RetryDriver', $connection->getDriver());

        try {
            $connection->connect();
        } catch (MissingConnectionException $e) {
        }

        $this->assertSame(4, $connection->getDriver()->getConnectRetries());
    }

    /**
     * Tests creation of prepared statements
     */
    public function testPrepare(): void
    {
        $this->deprecated(function () {
            $sql = 'SELECT 1 + 1';
            $result = $this->connection->prepare($sql);
            $this->assertInstanceOf('Cake\Database\StatementInterface', $result);
            $this->assertEquals($sql, $result->queryString);

            $query = $this->connection->selectQuery('1 + 1');
            $result = $this->connection->prepare($query);
            $this->assertInstanceOf('Cake\Database\StatementInterface', $result);
            $sql = '#SELECT [`"\[]?1 \+ 1[`"\]]?#';
            $this->assertMatchesRegularExpression($sql, $result->queryString);
        });
    }

    /**
     * Tests executing a simple query using bound values
     */
    public function testExecuteWithArguments(): void
    {
        $sql = 'SELECT 1 + ?';
        $statement = $this->connection->execute($sql, [1], ['integer']);
        $this->assertCount(1, $statement);
        $result = $statement->fetch();
        $this->assertEquals([2], $result);
        $statement->closeCursor();

        $sql = 'SELECT 1 + ? + ? AS total';
        $statement = $this->connection->execute($sql, [2, 3], ['integer', 'integer']);
        $this->assertCount(1, $statement);
        $result = $statement->fetch('assoc');
        $this->assertEquals(['total' => 6], $result);
        $statement->closeCursor();

        $sql = 'SELECT 1 + :one + :two AS total';
        $statement = $this->connection->execute($sql, ['one' => 2, 'two' => 3], ['one' => 'integer', 'two' => 'integer']);
        $this->assertCount(1, $statement);
        $result = $statement->fetch('assoc');
        $statement->closeCursor();
        $this->assertEquals(['total' => 6], $result);
    }

    /**
     * Tests executing a query with params and associated types
     */
    public function testExecuteWithArgumentsAndTypes(): void
    {
        $sql = "SELECT '2012-01-01' = ?";
        $statement = $this->connection->execute($sql, [new DateTime('2012-01-01')], ['date']);
        $result = $statement->fetch();
        $statement->closeCursor();
        $this->assertTrue((bool)$result[0]);
    }

    /**
     * test executing a buffered query interacts with Collection well.
     */
    public function testBufferedStatementCollectionWrappingStatement(): void
    {
        $this->skipIf(
            !($this->connection->getDriver() instanceof Sqlite),
            'Only required for SQLite driver which does not support buffered results natively'
        );

        $this->deprecated(function () {
            $statement = $this->connection->query('SELECT * FROM things LIMIT 3');

            $collection = new Collection($statement);
            $result = $collection->extract('id')->toArray();
            $this->assertEquals(['1', '2'], $result);

            // Check iteration after extraction
            $result = [];
            foreach ($collection as $v) {
                $result[] = $v['id'];
            }
            $this->assertEquals(['1', '2'], $result);
        });
    }

    /**
     * Tests that passing a unknown value to a query throws an exception
     */
    public function testExecuteWithMissingType(): void
    {
        $this->expectException(InvalidArgumentException::class);
        $sql = 'SELECT ?';
        $statement = $this->connection->execute($sql, [new DateTime('2012-01-01')], ['bar']);
    }

    /**
     * Tests executing a query with no params also works
     */
    public function testExecuteWithNoParams(): void
    {
        $sql = 'SELECT 1';
        $statement = $this->connection->execute($sql);
        $result = $statement->fetch();
        $this->assertCount(1, $result);
        $this->assertEquals([1], $result);
        $statement->closeCursor();
    }

    /**
     * Tests it is possible to insert data into a table using matching types by key name
     */
    public function testInsertWithMatchingTypes(): void
    {
        $data = ['id' => '3', 'title' => 'a title', 'body' => 'a body'];
        $result = $this->connection->insert(
            'things',
            $data,
            ['id' => 'integer', 'title' => 'string', 'body' => 'string']
        );
        $this->assertInstanceOf('Cake\Database\StatementInterface', $result);
        $result->closeCursor();
        $result = $this->connection->execute('SELECT * from things where id = 3');
        $this->assertCount(1, $result);
        $row = $result->fetch('assoc');
        $result->closeCursor();
        $this->assertEquals($data, $row);
    }

    /**
     * Tests insertQuery
     */
    public function testInsertQuery(): void
    {
        $data = ['id' => '3', 'title' => 'a title', 'body' => 'a body'];
        $query = $this->connection->insertQuery(
            'things',
            $data,
            ['id' => 'integer', 'title' => 'string', 'body' => 'string']
        );
        $result = $query->execute();
        $this->assertInstanceOf('Cake\Database\StatementInterface', $result);
        $result->closeCursor();

        $result = $this->connection->execute('SELECT * from things where id = 3');
        $this->assertCount(1, $result);
        $row = $result->fetch('assoc');
        $result->closeCursor();
        $this->assertEquals($data, $row);
    }

    /**
     * Tests it is possible to insert data into a table using matching types by array position
     */
    public function testInsertWithPositionalTypes(): void
    {
        $data = ['id' => '3', 'title' => 'a title', 'body' => 'a body'];
        $result = $this->connection->insert(
            'things',
            $data,
            ['integer', 'string', 'string']
        );
        $result->closeCursor();
        $this->assertInstanceOf('Cake\Database\StatementInterface', $result);
        $result = $this->connection->execute('SELECT * from things where id  = 3');
        $this->assertCount(1, $result);
        $row = $result->fetch('assoc');
        $result->closeCursor();
        $this->assertEquals($data, $row);
    }

    /**
     * Tests an statement class can be reused for multiple executions
     */
    public function testStatementReusing(): void
    {
        $total = $this->connection->execute('SELECT COUNT(*) AS total FROM things');
        $result = $total->fetch('assoc');
        $this->assertEquals(2, $result['total']);
        $total->closeCursor();

        $total->execute();
        $result = $total->fetch('assoc');
        $this->assertEquals(2, $result['total']);
        $total->closeCursor();

        $result = $this->connection->execute('SELECT title, body  FROM things');
        $row = $result->fetch('assoc');
        $this->assertSame('a title', $row['title']);
        $this->assertSame('a body', $row['body']);

        $row = $result->fetch('assoc');
        $result->closeCursor();
        $this->assertSame('another title', $row['title']);
        $this->assertSame('another body', $row['body']);

        $result->execute();
        $row = $result->fetch('assoc');
        $result->closeCursor();
        $this->assertSame('a title', $row['title']);
    }

    /**
     * Tests that it is possible to pass PDO constants to the underlying statement
     * object for using alternate fetch types
     */
    public function testStatementFetchObject(): void
    {
        $statement = $this->connection->execute('SELECT title, body  FROM things');
        $row = $statement->fetch(PDO::FETCH_OBJ);
        $this->assertSame('a title', $row->title);
        $this->assertSame('a body', $row->body);
        $statement->closeCursor();
    }

    /**
     * Tests rows can be updated without specifying any conditions nor types
     */
    public function testUpdateWithoutConditionsNorTypes(): void
    {
        $title = 'changed the title!';
        $body = 'changed the body!';
        $this->connection->update('things', ['title' => $title, 'body' => $body]);
        $result = $this->connection->execute('SELECT * FROM things WHERE title = ? AND body = ?', [$title, $body]);
        $this->assertCount(2, $result);
        $result->closeCursor();
    }

    /**
     * Tests it is possible to use key => value conditions for update
     */
    public function testUpdateWithConditionsNoTypes(): void
    {
        $title = 'changed the title!';
        $body = 'changed the body!';
        $this->connection->update('things', ['title' => $title, 'body' => $body], ['id' => 2]);
        $result = $this->connection->execute('SELECT * FROM things WHERE title = ? AND body = ?', [$title, $body]);
        $this->assertCount(1, $result);
        $result->closeCursor();
    }

    /**
     * Tests it is possible to use key => value and string conditions for update
     */
    public function testUpdateWithConditionsCombinedNoTypes(): void
    {
        $title = 'changed the title!';
        $body = 'changed the body!';
        $this->connection->update('things', ['title' => $title, 'body' => $body], ['id' => 2, 'body is not null']);
        $result = $this->connection->execute('SELECT * FROM things WHERE title = ? AND body = ?', [$title, $body]);
        $this->assertCount(1, $result);
        $result->closeCursor();
    }

    /**
     * Tests you can bind types to update values
     */
    public function testUpdateWithTypes(): void
    {
        $title = 'changed the title!';
        $body = new DateTime('2012-01-01');
        $values = compact('title', 'body');
        $this->connection->update('things', $values, [], ['body' => 'date']);
        $result = $this->connection->execute('SELECT * FROM things WHERE title = :title AND body = :body', $values, ['body' => 'date']);
        $this->assertCount(2, $result);
        $row = $result->fetch('assoc');
        $this->assertSame('2012-01-01', $row['body']);
        $row = $result->fetch('assoc');
        $this->assertSame('2012-01-01', $row['body']);
        $result->closeCursor();
    }

    /**
     * Tests you can bind types to update values
     */
    public function testUpdateWithConditionsAndTypes(): void
    {
        $title = 'changed the title!';
        $body = new DateTime('2012-01-01');
        $values = compact('title', 'body');
        $this->connection->update('things', $values, ['id' => '1'], ['body' => 'date', 'id' => 'integer']);
        $result = $this->connection->execute('SELECT * FROM things WHERE title = :title AND body = :body', $values, ['body' => 'date']);
        $this->assertCount(1, $result);
        $row = $result->fetch('assoc');
        $this->assertSame('2012-01-01', $row['body']);
        $result->closeCursor();
    }

    /**
     * Tests you can bind types to update values
     */
    public function testUpdateQueryWithConditionsAndTypes(): void
    {
        $title = 'changed the title!';
        $body = new DateTime('2012-01-01');
        $values = compact('title', 'body');
        $query = $this->connection->updateQuery('things', $values, ['id' => '1'], ['body' => 'date', 'id' => 'integer']);
        $query->execute()->closeCursor();

        $result = $this->connection->execute('SELECT * FROM things WHERE title = :title AND body = :body', $values, ['body' => 'date']);
        $this->assertCount(1, $result);
        $row = $result->fetch('assoc');
        $this->assertSame('2012-01-01', $row['body']);
        $result->closeCursor();
    }

    /**
     * Tests delete from table with no conditions
     */
    public function testDeleteNoConditions(): void
    {
        $this->connection->delete('things');
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(0, $result);
        $result->closeCursor();
    }

    /**
     * Tests delete from table with conditions
     */
    public function testDeleteWithConditions(): void
    {
        $this->connection->delete('things', ['id' => '1'], ['id' => 'integer']);
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(1, $result);
        $result->closeCursor();

        $this->connection->delete('things', ['id' => '2'], ['id' => 'integer']);
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(0, $result);
        $result->closeCursor();
    }

    /**
     * Tests delete from table with conditions
     */
    public function testDeleteQuery(): void
    {
        $query = $this->connection->deleteQuery('things', ['id' => '1'], ['id' => 'integer']);
        $query->execute()->closeCursor();
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(1, $result);
        $result->closeCursor();

        $query = $this->connection->deleteQuery('things')->where(['id' => 2], ['id' => 'integer']);
        $query->execute()->closeCursor();
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(0, $result);
        $result->closeCursor();
    }

    /**
     * Test basic selectQuery behavior
     */
    public function testSelectQuery(): void
    {
        $query = $this->connection->selectQuery(['*'], 'things');
        $statement = $query->execute();
        $row = $statement->fetchAssoc();
        $statement->closeCursor();

        $this->assertArrayHasKey('title', $row);
        $this->assertArrayHasKey('body', $row);
    }

    /**
     * Tests that it is possible to use simple database transactions
     */
    public function testSimpleTransactions(): void
    {
        $this->connection->begin();
        $this->assertTrue($this->connection->getDriver()->inTransaction());
        $this->connection->delete('things', ['id' => 1]);
        $this->connection->rollback();
        $this->assertFalse($this->connection->getDriver()->inTransaction());
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(2, $result);
        $result->closeCursor();

        $this->connection->begin();
        $this->assertTrue($this->connection->getDriver()->inTransaction());
        $this->connection->delete('things', ['id' => 1]);
        $this->connection->commit();
        $this->assertFalse($this->connection->getDriver()->inTransaction());
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(1, $result);
    }

    /**
     * Tests that the destructor of Connection generates a warning log
     * when transaction is not closed
     */
    public function testDestructorWithUncommittedTransaction(): void
    {
        $driver = $this->getMockFormDriver();
        $connection = new Connection(['driver' => $driver]);
        $connection->begin();
        $this->assertTrue($connection->inTransaction());

        $logger = $this->createMock('Psr\Log\AbstractLogger');
        $logger->expects($this->once())
            ->method('log')
            ->with('warning', $this->stringContains('The connection is going to be closed'));

        Log::setConfig('error', $logger);

        // Destroy the connection
        unset($connection);
    }

    /**
     * Tests that it is possible to use virtualized nested transaction
     * with early rollback algorithm
     */
    public function testVirtualNestedTransaction(): void
    {
        //starting 3 virtual transaction
        $this->connection->begin();
        $this->connection->begin();
        $this->connection->begin();
        $this->assertTrue($this->connection->getDriver()->inTransaction());

        $this->connection->delete('things', ['id' => 1]);
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(1, $result);

        $this->connection->commit();
        $this->assertTrue($this->connection->getDriver()->inTransaction());
        $this->connection->rollback();
        $this->assertFalse($this->connection->getDriver()->inTransaction());

        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(2, $result);
    }

    /**
     * Tests that it is possible to use virtualized nested transaction
     * with early rollback algorithm
     */
    public function testVirtualNestedTransaction2(): void
    {
        //starting 3 virtual transaction
        $this->connection->begin();
        $this->connection->begin();
        $this->connection->begin();

        $this->connection->delete('things', ['id' => 1]);
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(1, $result);
        $this->connection->rollback();

        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(2, $result);
    }

    /**
     * Tests that it is possible to use virtualized nested transaction
     * with early rollback algorithm
     */

    public function testVirtualNestedTransaction3(): void
    {
        //starting 3 virtual transaction
        $this->connection->begin();
        $this->connection->begin();
        $this->connection->begin();

        $this->connection->delete('things', ['id' => 1]);
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(1, $result);
        $this->connection->commit();
        $this->connection->commit();
        $this->connection->commit();

        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(1, $result);
    }

    /**
     * Tests that it is possible to real use  nested transactions
     */
    public function testSavePoints(): void
    {
        $this->connection->enableSavePoints(true);
        $this->skipIf(!$this->connection->isSavePointsEnabled(), 'Database driver doesn\'t support save points');

        $this->connection->begin();
        $this->connection->delete('things', ['id' => 1]);

        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(1, $result);

        $this->connection->begin();
        $this->connection->delete('things', ['id' => 2]);
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(0, $result);

        $this->connection->rollback();
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(1, $result);

        $this->connection->rollback();
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(2, $result);
    }

    /**
     * Tests that it is possible to real use  nested transactions
     */

    public function testSavePoints2(): void
    {
        $this->connection->enableSavePoints(true);
        $this->skipIf(!$this->connection->isSavePointsEnabled(), 'Database driver doesn\'t support save points');

        $this->connection->begin();
        $this->connection->delete('things', ['id' => 1]);

        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(1, $result);

        $this->connection->begin();
        $this->connection->delete('things', ['id' => 2]);
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(0, $result);

        $this->connection->rollback();
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(1, $result);

        $this->connection->commit();
        $result = $this->connection->execute('SELECT * FROM things');
        $this->assertCount(1, $result);
    }

    /**
     * Tests inTransaction()
     */
    public function testInTransaction(): void
    {
        $this->connection->begin();
        $this->assertTrue($this->connection->inTransaction());

        $this->connection->begin();
        $this->assertTrue($this->connection->inTransaction());

        $this->connection->commit();
        $this->assertTrue($this->connection->inTransaction());

        $this->connection->commit();
        $this->assertFalse($this->connection->inTransaction());

        $this->connection->begin();
        $this->assertTrue($this->connection->inTransaction());

        $this->connection->begin();
        $this->connection->rollback();
        $this->assertFalse($this->connection->inTransaction());
    }

    /**
     * Tests inTransaction() with save points
     */
    public function testInTransactionWithSavePoints(): void
    {
        $this->deprecated(function () {
            $this->skipIf(
                $this->connection->getDriver() instanceof Sqlserver,
                'SQLServer fails when this test is included.'
            );

            $this->connection->enableSavePoints(true);
            $this->skipIf(!$this->connection->isSavePointsEnabled(), 'Database driver doesn\'t support save points');

            $this->connection->begin();
            $this->assertTrue($this->connection->inTransaction());

            $this->connection->begin();
            $this->assertTrue($this->connection->inTransaction());

            $this->connection->commit();
            $this->assertTrue($this->connection->inTransaction());

            $this->connection->commit();
            $this->assertFalse($this->connection->inTransaction());

            $this->connection->begin();
            $this->assertTrue($this->connection->inTransaction());

            $this->connection->begin();
            $this->connection->rollback();
            $this->assertTrue($this->connection->inTransaction());

            $this->connection->rollback();
            $this->assertFalse($this->connection->inTransaction());
        });
    }

    /**
     * Tests connection can quote values to be safely used in query strings
     */
    public function testQuote(): void
    {
        $this->deprecated(function () {
            $this->skipIf(!$this->connection->supportsQuoting());
            $expected = "'2012-01-01'";
            $result = $this->connection->quote(new DateTime('2012-01-01'), 'date');
            $this->assertEquals($expected, $result);

            $expected = "'1'";
            $result = $this->connection->quote(1, 'string');
            $this->assertEquals($expected, $result);

            $expected = "'hello'";
            $result = $this->connection->quote('hello', 'string');
            $this->assertEquals($expected, $result);
        });
    }

    /**
     * Tests identifier quoting
     */
    public function testQuoteIdentifier(): void
    {
        $this->deprecated(function () {
            $driver = $this->getMockBuilder('Cake\Database\Driver\Sqlite')
                ->onlyMethods(['enabled'])
                ->getMock();
            $driver->expects($this->once())
                ->method('enabled')
                ->will($this->returnValue(true));
            $connection = new Connection(['driver' => $driver]);

            $result = $connection->quoteIdentifier('name');
            $expected = '"name"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Model.*');
            $expected = '"Model".*';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Items.No_ 2');
            $expected = '"Items"."No_ 2"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Items.No_ 2 thing');
            $expected = '"Items"."No_ 2 thing"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Items.No_ 2 thing AS thing');
            $expected = '"Items"."No_ 2 thing" AS "thing"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Items.Item Category Code = :c1');
            $expected = '"Items"."Item Category Code" = :c1';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('MTD()');
            $expected = 'MTD()';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('(sm)');
            $expected = '(sm)';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('name AS x');
            $expected = '"name" AS "x"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Model.name AS x');
            $expected = '"Model"."name" AS "x"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Function(Something.foo)');
            $expected = 'Function("Something"."foo")';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Function(SubFunction(Something.foo))');
            $expected = 'Function(SubFunction("Something"."foo"))';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Function(Something.foo) AS x');
            $expected = 'Function("Something"."foo") AS "x"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('name-with-minus');
            $expected = '"name-with-minus"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('my-name');
            $expected = '"my-name"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Foo-Model.*');
            $expected = '"Foo-Model".*';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Team.P%');
            $expected = '"Team"."P%"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Team.G/G');
            $expected = '"Team"."G/G"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Model.name as y');
            $expected = '"Model"."name" AS "y"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('nämé');
            $expected = '"nämé"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('aßa.nämé');
            $expected = '"aßa"."nämé"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('aßa.*');
            $expected = '"aßa".*';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Modeß.nämé as y');
            $expected = '"Modeß"."nämé" AS "y"';
            $this->assertEquals($expected, $result);

            $result = $connection->quoteIdentifier('Model.näme Datum as y');
            $expected = '"Model"."näme Datum" AS "y"';
            $this->assertEquals($expected, $result);
        });
    }

    /**
     * Tests default return vale for logger() function
     */
    public function testGetLoggerDefault(): void
    {
        $logger = $this->connection->getLogger();
        $this->assertInstanceOf('Cake\Database\Log\QueryLogger', $logger);
        $this->assertSame($logger, $this->connection->getLogger());
    }

    /**
     * Tests setting and getting the logger object
     */
    public function testGetAndSetLogger(): void
    {
        $logger = new QueryLogger();
        $this->connection->setLogger($logger);
        $this->assertSame($logger, $this->connection->getLogger());
    }

    /**
     * Tests that statements are decorated with a logger when logQueries is set to true
     */
    public function testLoggerDecorator(): void
    {
        $this->deprecated(function () {
            $logger = new QueryLogger();
            $this->connection->enableQueryLogging(true);
            $this->connection->setLogger($logger);
            $st = $this->connection->prepare('SELECT 1');
            $this->assertInstanceOf(LoggingStatement::class, $st);
            $this->assertSame($logger, $st->getLogger());

            $this->connection->enableQueryLogging(false);
            $st = $this->connection->prepare('SELECT 1');
            $this->assertNotInstanceOf('Cake\Database\Log\LoggingStatement', $st);
        });
    }

    /**
     * test enableQueryLogging method
     */
    public function testEnableQueryLogging(): void
    {
        $this->deprecated(function () {
            $this->connection->enableQueryLogging(true);
            $this->assertTrue($this->connection->isQueryLoggingEnabled());

            $this->connection->disableQueryLogging();
            $this->assertFalse($this->connection->isQueryLoggingEnabled());
        });
    }

    /**
     * Tests that log() function logs to the configured query logger
     */
    public function testLogFunction(): void
    {
        Log::setConfig('queries', ['className' => 'Array']);
        $this->deprecated(function () {
            $this->connection->enableQueryLogging();
            $this->connection->log('SELECT 1');

            $messages = Log::engine('queries')->read();
            $this->assertCount(1, $messages);
            $this->assertSame('debug: connection=test role= duration=0 rows=0 SELECT 1', $messages[0]);
        });
    }

    /**
     * @see https://github.com/cakephp/cakephp/issues/14676
     */
    public function testLoggerDecoratorDoesNotPrematurelyFetchRecords(): void
    {
        Log::setConfig('queries', ['className' => 'Array']);
        $this->deprecated(function () {
            $logger = new QueryLogger();
            $this->connection->enableQueryLogging(true);
            $this->connection->setLogger($logger);
            $st = $this->connection->execute('SELECT * FROM things');
            $this->assertInstanceOf(LoggingStatement::class, $st);

            $messages = Log::engine('queries')->read();
            $this->assertCount(0, $messages);

            $expected = [
                [1, 'a title', 'a body'],
                [2, 'another title', 'another body'],
            ];
            $results = $st->fetchAll();
            $this->assertEquals($expected, $results);

            $messages = Log::engine('queries')->read();
            $this->assertCount(1, $messages);

            $st = $this->connection->execute('SELECT * FROM things WHERE id = 0');
            $this->assertSame(0, $st->rowCount());

            $messages = Log::engine('queries')->read();
            $this->assertCount(2, $messages, 'Select queries without any matching rows should also be logged.');
        });
    }

    /**
     * Tests that begin and rollback are also logged
     */
    public function testLogBeginRollbackTransaction(): void
    {
        Log::setConfig('queries', ['className' => 'Array']);

        $this->deprecated(function () {
            $connection = $this
                ->getMockBuilder(Connection::class)
                ->onlyMethods(['connect'])
                ->disableOriginalConstructor()
                ->getMock();
            $connection->enableQueryLogging(true);

            $this->deprecated(function () use ($connection) {
                $driver = $this->getMockFormDriver();
                $connection->setDriver($driver);
            });

            $connection->begin();
            $connection->begin(); //This one will not be logged
            $connection->rollback();

            $messages = Log::engine('queries')->read();
            $this->assertCount(2, $messages);
            $this->assertSame('debug: connection= role= duration=0 rows=0 BEGIN', $messages[0]);
            $this->assertSame('debug: connection= role= duration=0 rows=0 ROLLBACK', $messages[1]);
        });
    }

    /**
     * Tests that commits are logged
     */
    public function testLogCommitTransaction(): void
    {
        $this->deprecated(function () {
            $driver = $this->getMockFormDriver();
            $connection = $this->getMockBuilder(Connection::class)
                ->onlyMethods(['connect'])
                ->setConstructorArgs([['driver' => $driver]])
                ->getMock();

            Log::setConfig('queries', ['className' => 'Array']);
            $connection->enableQueryLogging(true);
            $connection->begin();
            $connection->commit();

            $messages = Log::engine('queries')->read();
            $this->assertCount(2, $messages);
            $this->assertSame('debug: connection= role= duration=0 rows=0 BEGIN', $messages[0]);
            $this->assertSame('debug: connection= role= duration=0 rows=0 COMMIT', $messages[1]);
        });
    }

    /**
     * Tests setting and getting the cacher object
     */
    public function testGetAndSetCacher(): void
    {
        $cacher = new NullEngine();
        $this->connection->setCacher($cacher);
        $this->assertSame($cacher, $this->connection->getCacher());
    }

    /**
     * Tests that the transactional method will start and commit a transaction
     * around some arbitrary function passed as argument
     */
    public function testTransactionalSuccess(): void
    {
        $driver = $this->getMockFormDriver();
        $connection = $this->getMockBuilder(Connection::class)
            ->onlyMethods(['connect', 'commit', 'begin'])
            ->setConstructorArgs([['driver' => $driver]])
            ->getMock();
        $connection->expects($this->once())->method('begin');
        $connection->expects($this->once())->method('commit');
        $result = $connection->transactional(function ($conn) use ($connection) {
            $this->assertSame($connection, $conn);

            return 'thing';
        });
        $this->assertSame('thing', $result);
    }

    /**
     * Tests that the transactional method will rollback the transaction if false
     * is returned from the callback
     */
    public function testTransactionalFail(): void
    {
        $driver = $this->getMockFormDriver();
        $connection = $this->getMockBuilder(Connection::class)
            ->onlyMethods(['connect', 'commit', 'begin', 'rollback'])
            ->setConstructorArgs([['driver' => $driver]])
            ->getMock();
        $connection->expects($this->once())->method('begin');
        $connection->expects($this->once())->method('rollback');
        $connection->expects($this->never())->method('commit');
        $result = $connection->transactional(function ($conn) use ($connection) {
            $this->assertSame($connection, $conn);

            return false;
        });
        $this->assertFalse($result);
    }

    /**
     * Tests that the transactional method will rollback the transaction
     * and throw the same exception if the callback raises one
     *
     * @throws \InvalidArgumentException
     */
    public function testTransactionalWithException(): void
    {
        $this->expectException(InvalidArgumentException::class);
        $driver = $this->getMockFormDriver();
        $connection = $this->getMockBuilder(Connection::class)
            ->onlyMethods(['connect', 'commit', 'begin', 'rollback'])
            ->setConstructorArgs([['driver' => $driver]])
            ->getMock();
        $connection->expects($this->once())->method('begin');
        $connection->expects($this->once())->method('rollback');
        $connection->expects($this->never())->method('commit');
        $connection->transactional(function ($conn) use ($connection): void {
            $this->assertSame($connection, $conn);
            throw new InvalidArgumentException();
        });
    }

    /**
     * Tests it is possible to set a schema collection object
     */
    public function testSetSchemaCollection(): void
    {
        $driver = $this->getMockFormDriver();
        $connection = $this->getMockBuilder(Connection::class)
            ->onlyMethods(['connect'])
            ->setConstructorArgs([['driver' => $driver]])
            ->getMock();

        $schema = $connection->getSchemaCollection();
        $this->assertInstanceOf('Cake\Database\Schema\Collection', $schema);

        $schema = $this->getMockBuilder('Cake\Database\Schema\Collection')
            ->setConstructorArgs([$connection])
            ->getMock();
        $connection->setSchemaCollection($schema);
        $this->assertSame($schema, $connection->getSchemaCollection());
    }

    /**
     * Test CachedCollection creation with default and custom cache key prefix.
     */
    public function testGetCachedCollection(): void
    {
        $driver = $this->getMockFormDriver();
        $connection = $this->getMockBuilder(Connection::class)
            ->onlyMethods(['connect'])
            ->setConstructorArgs([[
                'driver' => $driver,
                'name' => 'default',
                'cacheMetadata' => true,
            ]])
            ->getMock();

        $schema = $connection->getSchemaCollection();
        $this->assertInstanceOf(CachedCollection::class, $schema);
        $this->assertSame('default_key', $schema->cacheKey('key'));

        $driver = $this->getMockFormDriver();
        $connection = $this->getMockBuilder(Connection::class)
            ->onlyMethods(['connect'])
            ->setConstructorArgs([[
                'driver' => $driver,
                'name' => 'default',
                'cacheMetadata' => true,
                'cacheKeyPrefix' => 'foo',
            ]])
            ->getMock();

        $schema = $connection->getSchemaCollection();
        $this->assertInstanceOf(CachedCollection::class, $schema);
        $this->assertSame('foo_key', $schema->cacheKey('key'));
    }

    /**
     * Tests that allowed nesting of commit/rollback operations doesn't
     * throw any exceptions.
     */
    public function testNestedTransactionRollbackExceptionNotThrown(): void
    {
        $this->connection->transactional(function () {
            $this->connection->transactional(function () {
                return true;
            });

            return true;
        });
        $this->assertFalse($this->connection->inTransaction());

        $this->connection->transactional(function () {
            $this->connection->transactional(function () {
                return true;
            });

            return false;
        });
        $this->assertFalse($this->connection->inTransaction());

        $this->connection->transactional(function () {
            $this->connection->transactional(function () {
                return false;
            });

            return false;
        });
        $this->assertFalse($this->connection->inTransaction());
    }

    /**
     * Tests that not allowed nesting of commit/rollback operations throws
     * a NestedTransactionRollbackException.
     */
    public function testNestedTransactionRollbackExceptionThrown(): void
    {
        $this->rollbackSourceLine = -1;

        $e = null;
        try {
            $this->connection->transactional(function () {
                $this->connection->transactional(function () {
                    return false;
                });
                $this->rollbackSourceLine = __LINE__ - 1;
                if (PHP_VERSION_ID >= 80200) {
                    $this->rollbackSourceLine -= 2;
                }

                return true;
            });

            $this->fail('NestedTransactionRollbackException should be thrown');
        } catch (NestedTransactionRollbackException $e) {
        }

        $trace = $e->getTrace();
        $this->assertEquals(__FILE__, $trace[1]['file']);
        $this->assertEquals($this->rollbackSourceLine, $trace[1]['line']);
    }

    /**
     * Tests more detail about that not allowed nesting of rollback/commit
     * operations throws a NestedTransactionRollbackException.
     */
    public function testNestedTransactionStates(): void
    {
        $this->rollbackSourceLine = -1;
        $this->nestedTransactionStates = [];

        $e = null;
        try {
            $this->connection->transactional(function () {
                $this->pushNestedTransactionState();

                $this->connection->transactional(function () {
                    return true;
                });

                $this->connection->transactional(function () {
                    $this->pushNestedTransactionState();

                    $this->connection->transactional(function () {
                        return false;
                    });
                    $this->rollbackSourceLine = __LINE__ - 1;
                    if (PHP_VERSION_ID >= 80200) {
                        $this->rollbackSourceLine -= 2;
                    }

                    $this->pushNestedTransactionState();

                    return true;
                });

                $this->connection->transactional(function () {
                    return false;
                });

                $this->pushNestedTransactionState();

                return true;
            });

            $this->fail('NestedTransactionRollbackException should be thrown');
        } catch (NestedTransactionRollbackException $e) {
        }

        $this->pushNestedTransactionState();

        $this->assertSame([false, false, true, true, false], $this->nestedTransactionStates);
        $this->assertFalse($this->connection->inTransaction());

        $trace = $e->getTrace();
        $this->assertEquals(__FILE__, $trace[1]['file']);
        $this->assertEquals($this->rollbackSourceLine, $trace[1]['line']);
    }

    /**
     * Helper method to trace nested transaction states.
     */
    public function pushNestedTransactionState(): void
    {
        $method = new ReflectionMethod($this->connection, 'wasNestedTransactionRolledback');
        $method->setAccessible(true);
        $this->nestedTransactionStates[] = $method->invoke($this->connection);
    }

    /**
     * Tests that the connection is restablished whenever it is interrupted
     * after having used the connection at least once.
     */
    public function testAutomaticReconnect2(): void
    {
        $this->deprecated(function () {
            $conn = clone $this->connection;
            $statement = $conn->query('SELECT 1');
            $statement->execute();
            $statement->closeCursor();

            $newDriver = $this->getMockBuilder(Driver::class)->getMock();
            $prop = new ReflectionProperty($conn, 'readDriver');
            $prop->setAccessible(true);
            $prop->setValue($conn, $newDriver);
            $prop = new ReflectionProperty($conn, 'writeDriver');
            $prop->setAccessible(true);
            $prop->setValue($conn, $newDriver);

            $newDriver->expects($this->exactly(2))
                ->method('prepare')
                ->will($this->onConsecutiveCalls(
                    $this->throwException(new Exception('server gone away')),
                    $this->returnValue($statement)
                ));

            $res = $conn->query('SELECT 1');
            $this->assertInstanceOf(StatementInterface::class, $res);
        });
    }

    /**
     * Tests that the connection is not restablished whenever it is interrupted
     * inside a transaction.
     */
    public function testNoAutomaticReconnect(): void
    {
        $this->deprecated(function () {
            $conn = clone $this->connection;
            $statement = $conn->query('SELECT 1');
            $statement->execute();
            $statement->closeCursor();

            $conn->begin();

            $newDriver = $this->getMockBuilder(Driver::class)->getMock();
            $prop = new ReflectionProperty($conn, 'readDriver');
            $prop->setAccessible(true);
            $prop->setValue($conn, $newDriver);
            $prop = new ReflectionProperty($conn, 'writeDriver');
            $prop->setAccessible(true);
            $oldDriver = $prop->getValue($conn);
            $prop->setValue($conn, $newDriver);

            $newDriver->expects($this->once())
                ->method('prepare')
                ->will($this->throwException(new Exception('server gone away')));

            try {
                $conn->query('SELECT 1');
            } catch (Exception $e) {
            }
            $this->assertInstanceOf(Exception::class, $e ?? null);

            $prop->setValue($conn, $oldDriver);
            $conn->rollback();
        });
    }

    public function testAutomaticReconnectWithoutQueryLogging(): void
    {
        $this->deprecated(function () {
            $conn = clone $this->connection;

            $logger = new TestBaseLog();
            $conn->setLogger($logger);
            $conn->disableQueryLogging();

            $statement = $conn->query('SELECT 1');
            $statement->execute();
            $statement->closeCursor();

            $newDriver = $this->getMockBuilder(Driver::class)->getMock();
            $prop = new ReflectionProperty($conn, 'readDriver');
            $prop->setAccessible(true);
            $prop->setValue($conn, $newDriver);
            $prop = new ReflectionProperty($conn, 'writeDriver');
            $prop->setAccessible(true);
            $prop->setValue($conn, $newDriver);

            $newDriver->expects($this->exactly(2))
                ->method('prepare')
                ->will($this->onConsecutiveCalls(
                    $this->throwException(new Exception('server gone away')),
                    $this->returnValue($statement)
                ));

            $conn->query('SELECT 1');

            $this->assertEmpty($logger->getMessage());
        });
    }

    public function testAutomaticReconnectWithQueryLogging(): void
    {
        $this->deprecated(function () {
            $conn = clone $this->connection;

            $logger = new TestBaseLog();
            $conn->setLogger($logger);
            $conn->enableQueryLogging();

            $statement = $conn->query('SELECT 1');
            $statement->execute();
            $statement->closeCursor();

            $newDriver = $this->getMockBuilder(Driver::class)->getMock();
            $prop = new ReflectionProperty($conn, 'readDriver');
            $prop->setAccessible(true);
            $prop->setValue($conn, $newDriver);
            $prop = new ReflectionProperty($conn, 'writeDriver');
            $prop->setAccessible(true);
            $oldDriver = $prop->getValue($conn);
            $prop->setValue($conn, $newDriver);

            $newDriver->expects($this->exactly(2))
                ->method('prepare')
                ->will($this->onConsecutiveCalls(
                    $this->throwException(new Exception('server gone away')),
                    $this->returnValue($statement)
                ));

            $conn->query('SELECT 1');

            $this->assertSame('[RECONNECT]', $logger->getMessage());
        });
    }

    public function testNewQuery()
    {
        $this->deprecated(function () {
            $query = $this->connection->newQuery();
            $this->assertInstanceOf(Query::class, $query);
        });
    }
}