CommonCommandTest::testsInsertQueryAsColumnValue() - Code Metrics - Inspection of "Test 3: Move `src\TestSupport\TestCommandTrait::cl..." - yiisoft/db - Measure and Improve Code Quality continuously with Scrutinizer

Passed
Pull Request — master (#397)

by Wilmer
created 2022-11-25 16:51 UTC
CommonCommandTest::testsInsertQueryAsColumnValue() A

↳ Parent: CommonCommandTest
Complexity

Conditions	2
Paths	2
Size

Total Lines	44
Code Lines	35
Duplication

Lines	0
Ratio	0 %
Importance

Changes
Metric	Value
cc	2
eloc	35
nc	2
nop	0
dl	0
loc	44
rs	9.36
c	0
b	0
f	0
<?php

declare(strict_types=1);

namespace Yiisoft\Db\Tests\Common;

use Throwable;
use Yiisoft\Db\Driver\PDO\ConnectionPDOInterface;
use Yiisoft\Db\Exception\Exception;
use Yiisoft\Db\Exception\IntegrityException;
use Yiisoft\Db\Exception\InvalidArgumentException;
use Yiisoft\Db\Exception\InvalidCallException;
use Yiisoft\Db\Expression\Expression;
use Yiisoft\Db\Query\Data\DataReaderInterface;
use Yiisoft\Db\Query\Query;
use Yiisoft\Db\Schema\Schema;
use Yiisoft\Db\Tests\AbstractCommandTest;
use Yiisoft\Db\Tests\Support\Assert;
use Yiisoft\Db\Tests\Support\DbHelper;

use function setlocale;

abstract class CommonCommandTest extends AbstractCommandTest
{
    public function testAddCheck(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema('{{test_ck}}') !== null) {
            $command->dropTable('{{test_ck}}')->execute();
        }

        $command->createTable('{{test_ck}}', ['int1' => 'integer'])->execute();

        $this->assertEmpty($schema->getTableChecks('{{test_ck}}', true));

        $command->addCheck('{{test_ck_constraint}}', '{{test_ck}}', 'int1 > 1')->execute();

        $this->assertMatchesRegularExpression(
            '/^.*int1.*>.*1.*$/',
            $schema->getTableChecks('{{test_ck}}', true)[0]->getExpression()
        );
    }

    public function testAddColumn(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->addColumn('{{customer}}', 'city', Schema::TYPE_STRING)->execute();

        $this->assertTrue($db->getTableSchema('{{customer}}')->getColumn('city') !== null);
        $this->assertSame(Schema::TYPE_STRING, $db->getTableSchema('{{customer}}')->getColumn('city')->getType());
    }

    public function testAddCommentOnColumn(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->addCommentOnColumn('{{customer}}', 'id', 'Primary key.')->execute();
        $commentOnColumn = DbHelper::getCommmentsFromColumn('customer', 'id', $db);

        $this->assertSame(['value' => 'Primary key.'], $commentOnColumn);
    }

    public function testAddCommentOnTable(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->addCommentOnTable('{{customer}}', 'Customer table.')->execute();
        $commentOnTable = DbHelper::getCommmentsFromTable('customer', $db);

        $this->assertSame(['value' => 'Customer table.'], $commentOnTable);
    }

    public function testAddDefaultValue()
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema('{{test_def}}') !== null) {
            $command->dropTable('{{test_def}}')->execute();
        }

        $command->createTable('{{test_def}}', ['int1' => Schema::TYPE_INTEGER])->execute();

        $this->assertEmpty($schema->getTableDefaultValues('{{test_def}}', true));

        $command->addDefaultValue('{{test_def_constraint}}', '{{test_def}}', 'int1', 41)->execute();

        $this->assertMatchesRegularExpression(
            '/^.*41.*$/',
            $schema->getTableDefaultValues('{{test_def}}', true)[0]->getValue(),
        );
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\CommandProvider::addForeignKey()
     */
    public function testAddForeignKey(
        string $name,
        string $tableName,
        array|string $column1,
        array|string $column2
    ): void {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema($tableName) !== null) {
            $command->dropTable($tableName)->execute();
        }

        $command->createTable(
            $tableName,
            [
                'int1' => 'integer not null unique',
                'int2' => 'integer not null unique',
                'int3' => 'integer not null unique',
                'int4' => 'integer not null unique',
                'unique ([[int1]], [[int2]])',
                'unique ([[int3]], [[int4]])',
            ],
        )->execute();

        $this->assertEmpty($schema->getTableForeignKeys($tableName, true));

        $command->addForeignKey($name, $tableName, $column1, $tableName, $column2)->execute();

        $this->assertSame($name, $schema->getTableForeignKeys($tableName, true)[0]->getName());

        if (is_string($column1)) {

            $column1 = [$column1];
        }

        $this->assertSame($column1, $schema->getTableForeignKeys($tableName, true)[0]->getColumnNames());

        if (is_string($column2)) {

            $column2 = [$column2];
        }

        $this->assertSame($column2, $schema->getTableForeignKeys($tableName, true)[0]->getForeignColumnNames());
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\CommandProvider::addPrimaryKey()
     */
    public function testAddPrimaryKey(string $name, string $tableName, array|string $column): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema($tableName) !== null) {
            $command->dropTable($tableName)->execute();
        }

        $command->createTable($tableName, ['int1' => 'integer not null', 'int2' => 'integer not null'])->execute();

        $this->assertNull($schema->getTablePrimaryKey($tableName, true));

        $db->createCommand()->addPrimaryKey($name, $tableName, $column)->execute();

        if (is_string($column)) {

            $column = [$column];
        }

        $this->assertSame($column, $schema->getTablePrimaryKey($tableName, true)->getColumnNames());
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\CommandProvider::addUnique()
     */
    public function testAddUnique(string $name, string $tableName, array|string $column): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema($tableName) !== null) {
            $command->dropTable($tableName)->execute();
        }

        $command->createTable($tableName, ['int1' => 'integer not null', 'int2' => 'integer not null'])->execute();

        $this->assertEmpty($schema->getTableUniques($tableName, true));

        $command->addUnique($name, $tableName, $column)->execute();

        if (is_string($column)) {

            $column = [$column];
        }

        $this->assertSame($column, $schema->getTableUniques($tableName, true)[0]->getColumnNames());
    }

    public function testAlterColumn(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->alterColumn('{{customer}}', 'email', 'ntext')->execute();
        $schema = $db->getSchema();
        $columns = $schema->getTableSchema('{{customer}}')->getColumns();

        $this->assertArrayHasKey('email', $columns);
        $this->assertSame('ntext', $columns['email']->getDbType());
    }

    /**
     * Make sure that `{{something}}` in values will not be encoded.
     *
     * @dataProvider \Yiisoft\Db\Tests\Provider\CommandProvider::batchInsert()
     *
     * {@see https://github.com/yiisoft/yii2/issues/11242}
     */
    public function testBatchInsert(
        string $table,
        array $columns,
        array $values,
        string $expected,
        array $expectedParams = [],
        int $insertedRow = 1
    ): void {
        $db = $this->getConnection('type');

        $command = $db->createCommand();
        $command->batchInsert($table, $columns, $values);
        $command->prepare(false);

        $this->assertSame($expected, $command->getSql());
        $this->assertSame($expectedParams, $command->getParams());

        $command->execute();

        $this->assertEquals($insertedRow, (new Query($db))->from($table)->count());
    }

    /**
     * Test batch insert with different data types.
     *
     * Ensure double is inserted with `.` decimal separator.
     *
     * @link https://github.com/yiisoft/yii2/issues/6526
     */
    public function testBatchInsertDataTypesLocale(): void
    {
        $locale = setlocale(LC_NUMERIC, 0);

        if ($locale === false) {
            $this->markTestSkipped('Your platform does not support locales.');
        }

        $db = $this->getConnection('type');

        $command = $db->createCommand();

        try {
            /* This one sets decimal mark to comma sign */
            setlocale(LC_NUMERIC, 'ru_RU.utf8');

            $cols = ['int_col', 'char_col', 'float_col', 'bool_col'];
            $data = [[1, 'A', 9.735, true], [2, 'B', -2.123, false], [3, 'C', 2.123, false]];

            /* clear data in "type" table */
            $command->delete('{{type}}')->execute();

            /* change, for point oracle. */
            if ($db->getName() === 'oci') {
                $command->setSql(
                    <<<SQL
                    ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'
                    SQL
                )->execute();
            }

            /* batch insert on "type" table */
            $command->batchInsert('{{type}}', $cols, $data)->execute();
            $data = $command->setSql(
                <<<SQL
                SELECT [[int_col]], [[char_col]], [[float_col]], [[bool_col]] FROM {{type}} WHERE [[int_col]] IN (1,2,3) ORDER BY [[int_col]]
                SQL
            )->queryAll();

            $this->assertCount(3, $data);
            $this->assertEquals(1, $data[0]['int_col']);
            $this->assertEquals(2, $data[1]['int_col']);
            $this->assertEquals(3, $data[2]['int_col']);

            /* rtrim because Postgres padds the column with whitespace */
            $this->assertSame('A', rtrim($data[0]['char_col']));
            $this->assertSame('B', rtrim($data[1]['char_col']));
            $this->assertSame('C', rtrim($data[2]['char_col']));
            $this->assertEquals(9.735, $data[0]['float_col']);
            $this->assertEquals(-2.123, $data[1]['float_col']);
            $this->assertEquals(2.123, $data[2]['float_col']);
            $this->assertEquals(1, $data[0]['bool_col']);
            Assert::isOneOf($data[1]['bool_col'], ['0', false]);
            Assert::isOneOf($data[2]['bool_col'], ['0', false]);
        } catch (Exception | Throwable $e) {
            setlocale(LC_NUMERIC, $locale);

            throw $e;
        }

        setlocale(LC_NUMERIC, $locale);
    }

    public function testBatchInsertFailsOld(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->batchInsert(
            '{{customer}}',
            ['email', 'name', 'address'],
            [['[email protected]', 'test_name', 'test_address']],
        );

        $this->assertSame(1, $command->execute());

        $result = (new Query($db))
            ->select(['email', 'name', 'address'])
            ->from('{{customer}}')
            ->where(['=', '[[email]]', '[email protected]'])
            ->one();

        $this->assertCount(3, $result);
        $this->assertSame(['email' => '[email protected]', 'name' => 'test_name', 'address' => 'test_address'], $result);
    }

    public function testBatchInsertWithManyData(): void
    {
        $db = $this->getConnection('customer');

        $values = [];
        $attemptsInsertRows = 200;
        $command = $db->createCommand();

        for ($i = 0; $i < $attemptsInsertRows; $i++) {
            $values[$i] = ['t' . $i . '@any.com', 't' . $i, 't' . $i . ' address'];
        }

        $command->batchInsert('{{customer}}', ['email', 'name', 'address'], $values);

        $this->assertSame($attemptsInsertRows, $command->execute());

        $insertedRowsCount = (new Query($db))->from('{{customer}}')->count();

        $this->assertGreaterThanOrEqual($attemptsInsertRows, $insertedRowsCount);
    }

    public function testBatchInsertWithYield(): void
    {
        $db = $this->getConnection('customer');

        $rows = (
            static function () {
                yield ['[email protected]', 'test name', 'test address'];
            }
        )();
        $command = $db->createCommand();
        $command->batchInsert('{{customer}}', ['email', 'name', 'address'], $rows);

        $this->assertSame(1, $command->execute());
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\CommandProvider::createIndex()
     */
    public function testCreateIndex(string $name, string $tableName, array|string $column): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema($tableName) !== null) {
            $command->dropTable($tableName)->execute();
        }
        $command->createTable($tableName, ['int1' => 'integer not null', 'int2' => 'integer not null'])->execute();

        $this->assertEmpty($schema->getTableIndexes($tableName, true));

        $command->createIndex($name, $tableName, $column)->execute();

        if (is_string($column)) {

            $column = [$column];
        }

        $this->assertSame($column, $schema->getTableIndexes($tableName, true)[0]->getColumnNames());
        $this->assertFalse($schema->getTableIndexes($tableName, true)[0]->isUnique());
    }

    public function testCreateTable(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema('{{testCreateTable}}', true) !== null) {
            $command->dropTable('{{testCreateTable}}')->execute();
        }

        $command->createTable(
            '{{testCreateTable}}',
            ['id' => Schema::TYPE_PK, 'bar' => Schema::TYPE_INTEGER],
        )->execute();
        $command->insert('{{testCreateTable}}', ['bar' => 1])->execute();
        $records = $command->setSql(
            <<<SQL
            SELECT [[id]], [[bar]] FROM {{testCreateTable}};
            SQL
        )->queryAll();

        $this->assertEquals([['id' => 1, 'bar' => 1]], $records);
    }

    public function testCreateView(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();
        $subQuery = (new Query($db))->select('[[bar]]')->from('{{testCreateViewTable}}')->where(['>', 'bar', '5']);

        if ($schema->getTableSchema('{{testCreateView}}') !== null) {
            $command->dropView('{{testCreateView}}')->execute();
        }

        if ($schema->getTableSchema('{{testCreateViewTable}}')) {
            $command->dropTable('{{testCreateViewTable}}')->execute();
        }

        $command->createTable(
            '{{testCreateViewTable}}',
            ['id' => Schema::TYPE_PK, 'bar' => Schema::TYPE_INTEGER],
        )->execute();
        $command->insert('{{testCreateViewTable}}', ['bar' => 1])->execute();
        $command->insert('{{testCreateViewTable}}', ['bar' => 6])->execute();
        $command->createView('{{testCreateView}}', $subQuery)->execute();
        $records = $command->setSql(
            <<<SQL
            SELECT [[bar]] FROM {{testCreateView}};
            SQL
        )->queryAll();

        $this->assertEquals([['bar' => 6]], $records);
    }

    public function testDataReaderRewindException(): void
    {
        $db = $this->getConnection('customer');

        $this->expectException(InvalidCallException::class);
        $this->expectExceptionMessage('DataReader cannot rewind. It is a forward-only reader.');

        $command = $db->createCommand();
        $reader = $command->setSql(
            <<<SQL
            SELECT * FROM {{customer}}
            SQL
        )->query();
        $reader->next();
        $reader->rewind();
    }

    public function testDelete(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->delete('{{customer}}', ['id' => 2])->execute();
        $chekSql = <<<SQL
        SELECT COUNT([[id]]) FROM [[customer]]
        SQL;
        $command->setSql($chekSql);

        $this->assertSame('2', $command->queryScalar());

        $command->delete('{{customer}}', ['id' => 3])->execute();
        $command->setSql($chekSql);

        $this->assertSame('1', $command->queryScalar());
    }

    public function testDropCheck()
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema('{{test_ck}}') !== null) {
            $command->dropTable('{{test_ck}}')->execute();
        }

        $command->createTable('{{test_ck}}', ['int1' => 'integer'])->execute();

        $this->assertEmpty($schema->getTableChecks('{{test_ck}}', true));

        $command->addCheck('{{test_ck_constraint}}', '{{test_ck}}', 'int1 > 1')->execute();

        $this->assertMatchesRegularExpression(
            '/^.*int1.*>.*1.*$/',
            $schema->getTableChecks('{{test_ck}}', true)[0]->getExpression(),
        );

        $command->dropCheck('{{test_ck_constraint}}', '{{test_ck}}')->execute();

        $this->assertEmpty($schema->getTableChecks('{{test_ck}}', true));
    }

    public function testDropColumn(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema('{{testDropColumn}}', true) !== null) {
            $command->dropTable('{{testDropColumn}}')->execute();
        }

        $command->createTable(
            '{{testDropColumn}}',
            ['id' => Schema::TYPE_PK, 'bar' => Schema::TYPE_INTEGER, 'baz' => Schema::TYPE_INTEGER],
        )->execute();
        $command->dropColumn('{{testDropColumn}}', 'bar')->execute();

        $this->assertArrayNotHasKey('bar', $schema->getTableSchema('{{testDropColumn}}')->getColumns());
        $this->assertArrayHasKey('baz', $schema->getTableSchema('{{testDropColumn}}')->getColumns());
    }

    public function testDropCommentFromColumn(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->addCommentOnColumn('{{customer}}', 'id', 'Primary key.')->execute();
        $commentOnColumn = DbHelper::getCommmentsFromColumn('customer', 'id', $db);

        $this->assertSame(['value' => 'Primary key.'], $commentOnColumn);

        $command->dropCommentFromColumn('{{customer}}', 'id')->execute();
        $commentOnColumn = DbHelper::getCommmentsFromColumn('customer', 'id', $db);

        $this->assertNull($commentOnColumn);
    }

    public function testDropCommentFromTable(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->addCommentOnTable('{{customer}}', 'Customer table.')->execute();
        $commentOnTable = DbHelper::getCommmentsFromTable('customer', $db);

        $this->assertSame(['value' => 'Customer table.'], $commentOnTable);

        $command->dropCommentFromTable('{{customer}}')->execute();
        $commentOnTable = DbHelper::getCommmentsFromTable('customer', $db);

        $this->assertNull($commentOnTable);
    }

    public function testDropDefaultValue(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema('{{test_def}}') !== null) {
            $command->dropTable('{{test_def}}')->execute();
        }

        $command->createTable('{{test_def}}', ['int1' => 'integer'])->execute();

        $this->assertEmpty($schema->getTableDefaultValues('{{test_def}}', true));

        $command->addDefaultValue('{{test_def_constraint}}', '{{test_def}}', 'int1', 41)->execute();

        $this->assertMatchesRegularExpression(
            '/^.*41.*$/',
            $schema->getTableDefaultValues('{{test_def}}', true)[0]->getValue(),
        );

        $command->dropDefaultValue('{{test_def_constraint}}', '{{test_def}}')->execute();

        $this->assertEmpty($schema->getTableDefaultValues('{{test_def}}', true));
    }

    public function testDropForeignKey(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema('{{test_fk}}') !== null) {
            $command->dropTable('{{test_fk}}')->execute();
        }

        $command->createTable('{{test_fk}}', ['id' => Schema::TYPE_PK, 'int1' => 'integer'])->execute();

        $this->assertEmpty($schema->getTableForeignKeys('{{test_fk}}', true));

        $command->addForeignKey('{{test_fk_constraint}}', '{{test_fk}}', 'int1', '{{test_fk}}', 'id')->execute();

        $this->assertNotEmpty($schema->getTableForeignKeys('{{test_fk}}', true));

        $command->dropForeignKey('{{test_fk_constraint}}', '{{test_fk}}')->execute();

        $this->assertEmpty($schema->getTableForeignKeys('{{test_fk}}', true));
    }

    public function testDropIndex(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema('{{test_idx}}') !== null) {
            $command->dropTable('{{test_idx}}')->execute();
        }

        $command->createTable('{{test_idx}}', ['int1' => 'integer not null', 'int2' => 'integer not null'])->execute();

        $this->assertEmpty($schema->getTableIndexes('{[test_idx}}', true));

        $command->createIndex('{{test_idx_constraint}}', '{{test_idx}}', ['int1', 'int2'], 'UNIQUE')->execute();

        $this->assertSame(['int1', 'int2'], $schema->getTableIndexes('{{test_idx}}', true)[0]->getColumnNames());
        $this->assertTrue($schema->getTableIndexes('{{test_idx}}', true)[0]->isUnique());

        $command->dropIndex('{{test_idx_constraint}}', '{{test_idx}}')->execute();

        $this->assertEmpty($schema->getTableIndexes('{{test_idx}}', true));
    }

    public function testDropPrimaryKey(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema('{{test_pk}}') !== null) {
            $command->dropTable('{{test_pk}}')->execute();
        }

        $command->createTable('{{test_pk}}', ['int1' => 'integer not null', 'int2' => 'integer not null'])->execute();

        $this->assertEmpty($schema->getTableSchema('{{test_pk}}', true)->getPrimaryKey());

        $command->addPrimaryKey('{{test_pk_constraint}}', '{{test_pk}}', ['int1', 'int2'])->execute();

        $this->assertSame(['int1', 'int2'], $schema->getTableSchema('{{test_pk}}', true)->getColumnNames());

        $command->dropPrimaryKey('{{test_pk_constraint}}', '{{test_pk}}')->execute();

        $this->assertEmpty($schema->getTableSchema('{{test_pk}}', true)->getPrimaryKey());
    }

    public function testDropTable(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema('{{testDropTable}]') !== null) {
            $command->dropTable('{{testDropTable}}')->execute();
        }

        $command->createTable('{{testDropTable}}', ['id' => Schema::TYPE_PK, 'foo' => 'integer'])->execute();

        $this->assertNotNull($schema->getTableSchema('{{testDropTable}}', true));

        $command->dropTable('{{testDropTable}}')->execute();

        $this->assertNull($schema->getTableSchema('{{testDropTable}}', true));
    }

    public function testDropUnique(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema('{{test_uq}}') !== null) {
            $command->dropTable('{{test_uq}}')->execute();
        }

        $command->createTable('{{test_uq}}', ['int1' => 'integer not null', 'int2' => 'integer not null'])->execute();

        $this->assertEmpty($schema->getTableUniques('{{test_uq}}', true));

        $command->addUnique('{{test_uq_constraint}}', '{{test_uq}}', ['int1'])->execute();

        $this->assertSame(['int1'], $schema->getTableUniques('{{test_uq}}', true)[0]->getColumnNames());

        $command->dropUnique('{{test_uq_constraint}}', '{{test_uq}}')->execute();

        $this->assertEmpty($schema->getTableUniques('{{test_uq}}', true));
    }

    public function testDropView(): void
    {
        $db = $this->getConnection('animal');

        /* since it already exists in the fixtures */
        $viewName = '{{animal_view}}';

        $schema = $db->getSchema();

        $this->assertNotNull($schema->getTableSchema($viewName));

        $db->createCommand()->dropView($viewName)->execute();

        $this->assertNull($schema->getTableSchema($viewName));
    }

    public function testExecute(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->setSql(
            <<<SQL
            INSERT INTO {{customer}}([[email]], [[name]], [[address]]) VALUES ('[email protected]', 'user4', 'address4')
            SQL
        );

        $this->assertSame(1, $command->execute());

        $command = $command->setSql(
            <<<SQL
            SELECT COUNT(*) FROM {{customer}} WHERE [[name]] = 'user4'
            SQL
        );

        $this->assertEquals(1, $command->queryScalar());

        $command->setSql('bad SQL');
        $message = match ($db->getName()) {
            'sqlite' => 'SQLSTATE[HY000]: General error: 1 near "bad": syntax error',
            'sqlsrv' => 'SQLSTATE[42000]: [Microsoft]',
        };

        $this->expectException(Exception::class);
        $this->expectExceptionMessage($message);

        $command->execute();
    }

    public function testExecuteWithoutSql(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $result = $command->setSql('')->execute();

        $this->assertSame(0, $result);
    }

    public function testInsert(): void
    {
        $db = $this->getConnection('customer');

        $db->createCommand(
            <<<SQL
            DELETE FROM {{customer}}
            SQL
        )->execute();
        $command = $db->createCommand();
        $command
            ->insert('{{customer}}', ['email' => '[email protected]', 'name' => 'test', 'address' => 'test address'])
            ->execute();

        $this->assertEquals(
            1,
            $db->createCommand(
                <<<SQL
                SELECT COUNT(*) FROM {{customer}};
                SQL
            )->queryScalar(),
        );

        $record = $db->createCommand(
            <<<SQL
            SELECT [[email]], [[name]], [[address]] FROM {{customer}}
            SQL
        )->queryOne();

        $this->assertSame(['email' => '[email protected]', 'name' => 'test', 'address' => 'test address'], $record);
    }

    public function testInsertExpression(): void
    {
        $db = $this->getConnection('order_with_null');

        $command = $db->createCommand();
        $command->setSql(
            <<<SQL
            DELETE FROM {{order_with_null_fk}}
            SQL
        )->execute();
        $expression = match ($db->getName()) {
            'mysql' => 'YEAR(NOW())',
            'pgsql' => "EXTRACT(YEAR FROM TIMESTAMP 'now')",
            'sqlite' => "strftime('%Y')",
            'sqlsrv' => 'YEAR(GETDATE())',
        };
        $command->insert(
            '{{order_with_null_fk}}',
            ['created_at' => new Expression($expression), 'total' => 1],
        )->execute();

        $this->assertEquals(
            1,
            $command->setSql(
                <<<SQL
                SELECT COUNT(*) FROM {{order_with_null_fk}}
                SQL
            )->queryScalar(),
        );

        $record = $command->setSql(
            <<<SQL
            SELECT [[created_at]] FROM {{order_with_null_fk}}
            SQL
        )->queryOne();

        $this->assertEquals(['created_at' => date('Y')], $record);
    }

    public function testsInsertQueryAsColumnValue(): void
    {
        $db = $this->getConnection('order', 'order_with_null');

        $command = $db->createCommand();
        $time = time();
        $command->setSql(
            <<<SQL
            DELETE FROM {{order_with_null_fk}}
            SQL
        )->execute();
        $command->insert('{{order}}', ['customer_id' => 1, 'created_at' => $time, 'total' => 42])->execute();

        if ($db->getName() === 'pgsql') {
            $orderId = $db->getLastInsertID('public.order_id_seq');
        } else {
            $orderId = $db->getLastInsertID();
        }

        $columnValueQuery = (new Query($db))->select('[[created_at]]')->from('{{order}}')->where(['id' => $orderId]);
        $command->insert(
            '{{order_with_null_fk}}',
            ['customer_id' => $orderId, 'created_at' => $columnValueQuery, 'total' => 42],
        )->execute();

        $this->assertEquals(
            $time,
            $command->setSql(
                <<<SQL
                SELECT [[created_at]] FROM {{order_with_null_fk}} WHERE [[customer_id]] = :id
                SQL
            )->bindValues([':id' => $orderId])->queryScalar(),
        );

        $command->setSql(
            <<<SQL
            DELETE FROM {{order_with_null_fk}}
            SQL
        )->execute();
        $command->setSql(
            <<<SQL
            DELETE FROM {{order}}
            SQL
        )->execute();
    }

    public function testInsertSelect(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->setSql(
            <<<SQL
            DELETE FROM {{customer}}
            SQL
        )->execute();
        $command->insert(
            '{{customer}}',
            ['email' => '[email protected]', 'name' => 'test', 'address' => 'test address']
        )->execute();
        $query = (new Query($db))
            ->select(['{{customer}}.[[email]] as name', '[[name]] as email', '[[address]]'])
            ->from('{{customer}}')
            ->where(['and', ['<>', 'name', 'foo'], ['status' => [0, 1, 2, 3]]]);
        $command->insert('{{customer}}', $query)->execute();

        $this->assertEquals(
            2,
            $command->setSql(
                <<<SQL
                SELECT COUNT(*) FROM {{customer}}
                SQL
            )->queryScalar(),
        );

        $record = $command->setSql(
            <<<SQL
            SELECT [[email]], [[name]], [[address]] FROM {{customer}}
            SQL
        )->queryAll();

        $this->assertSame(
            [
                ['email' => '[email protected]', 'name' => 'test', 'address' => 'test address'],
                ['email' => 'test', 'name' => '[email protected]', 'address' => 'test address'],
            ],
            $record,
        );
    }

    public function testInsertSelectAlias(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->setSql(
            <<<SQL
            DELETE FROM {{customer}}
            SQL
        )->execute();
        $command->insert(
            '{{customer}}',
            [
                'email' => '[email protected]',
                'name' => 'test',
                'address' => 'test address',
            ]
        )->execute();
        $query = (new Query($db))
            ->select(['email' => '{{customer}}.[[email]]', 'address' => 'name', 'name' => 'address'])
            ->from('{{customer}}')
            ->where(['and', ['<>', 'name', 'foo'], ['status' => [0, 1, 2, 3]]]);
        $command->insert('{{customer}}', $query)->execute();

        $this->assertEquals(
            2,
            $command->setSql(
                <<<SQL
                SELECT COUNT(*) FROM {{customer}}
                SQL
            )->queryScalar(),
        );

        $record = $command->setSql(
            <<<SQL
            SELECT [[email]], [[name]], [[address]] FROM {{customer}}
            SQL
        )->queryAll();

        $this->assertSame(
            [
                ['email' => '[email protected]', 'name' => 'test', 'address' => 'test address'],
                ['email' => '[email protected]', 'name' => 'test address', 'address' => 'test'],
            ],
            $record,
        );
    }

    /**
     * Test INSERT INTO ... SELECT SQL statement with wrong query object.
     *
     * @dataProvider \Yiisoft\Db\Tests\Provider\CommandProvider::invalidSelectColumns()
     *
     * @throws Exception
     * @throws Throwable
     */
    public function testInsertSelectFailed(array|ExpressionInterface|string $invalidSelectColumns): void
filter:
    dependency_paths: ["lib/*"]
    {
        $db = $this->getConnection();

        $query = new Query($db);
        $query->select($invalidSelectColumns)->from('{{customer}}');
        $command = $db->createCommand();

        $this->expectException(InvalidArgumentException::class);
        $this->expectExceptionMessage('Expected select query object with enumerated (named) parameters');

        $command->insert('{{customer}}', $query)->execute();
    }

    public function testInsertToBlob(): void
    {
        $db = $this->getConnection('type');

        $command = $db->createCommand();
        $command->delete('{{type}}')->execute();
        $columns = [
            'int_col' => 1,
            'char_col' => 'test',
            'float_col' => 3.14,
            'bool_col' => true,
            'blob_col' => serialize(['test' => 'data', 'num' => 222]),
        ];
        $command->insert('{{type}}', $columns)->execute();
        $result = $command->setSql(
            <<<SQL
            SELECT [[blob_col]] FROM {{type}}
            SQL
        )->queryOne();

        $this->assertIsArray($result);

        $resultBlob = is_resource($result['blob_col']) ? stream_get_contents($result['blob_col']) : $result['blob_col'];

        $this->assertSame($columns['blob_col'], $resultBlob);
    }

    public function testIntegrityViolation(): void
    {
        $db = $this->getConnection('profile');

        $this->expectException(IntegrityException::class);

        $command = $db->createCommand(
            <<<SQL
            INSERT INTO {{profile}}([[id]], [[description]]) VALUES (123, 'duplicate')
            SQL
        );
        $command->execute();
        $command->execute();
    }

    public function testNoTablenameReplacement(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->insert(
            '{{customer}}',
            ['name' => 'Some {{weird}} name', 'email' => '[email protected]', 'address' => 'Some {{%weird}} address']
        )->execute();

        if ($db->getName() === 'pgsql') {
            $customerId = $db->getLastInsertID('public.customer_id_seq');
        } else {
            $customerId = $db->getLastInsertID();
        }

        $customer = $command->setSql(
            <<<SQL
            SELECT [[name]], [[email]], [[address]] FROM {{customer}} WHERE [[id]]=:id
            SQL,
        )->bindValues([':id' => $customerId])->queryOne();

        $this->assertIsArray($customer);
        $this->assertSame('Some {{weird}} name', $customer['name']);
        $this->assertSame('Some {{%weird}} address', $customer['address']);

        $command->update(
            '{{customer}}',
            ['name' => 'Some {{updated}} name', 'address' => 'Some {{%updated}} address'],
            ['id' => $customerId]
        )->execute();
        $customer = $command->setSql(
            <<<SQL
            SELECT [[name]], [[email]], [[address]] FROM {{customer}} WHERE [[id]] = :id
            SQL
        )->bindValues([':id' => $customerId])->queryOne();

        $this->assertIsArray($customer);
        $this->assertSame('Some {{updated}} name', $customer['name']);
        $this->assertSame('Some {{%updated}} address', $customer['address']);
    }

    public function testQuery(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();

        $command->setSql(
            <<<SQL
            SELECT * FROM {{customer}}
            SQL
        );

        $this->assertNull($command->getPdoStatement());
class A
{
    function getObject()
    {
        return null;
    }

}

$a = new A();
if ($a->getObject()) {

        $reader = $command->query();

        // check tests that the reader is a valid iterator
        if ($db->getName() !== 'sqlite' && $db->getName() !== 'pgsql' && $db->getName() !== 'sqlsrv') {
            $this->assertEquals(3, $reader->count());
        }

        $this->assertNotNull($command->getPdoStatement());
        $this->assertInstanceOf(DataReaderInterface::class, $reader);
        $this->assertIsInt($reader->count());

        foreach ($reader as $row) {
            $this->assertIsArray($row);
            $this->assertCount(6, $row);
        }

        $command = $db->createCommand('bad SQL');

        $this->expectException(Exception::class);

        $command->query();
    }

    public function testQueryAll(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();

        $command->setSql(
            <<<SQL
            SELECT * FROM {{customer}}
            SQL
        );
        $rows = $command->queryAll();

        $this->assertIsArray($rows);
        $this->assertCount(3, $rows);
        $this->assertIsArray($rows[0]);
        $this->assertCount(6, $rows[0]);

        $command->setSql('bad SQL');

        $this->expectException(Exception::class);

        $command->queryAll();
        $command->setSql(
            <<<SQL
            SELECT * FROM {{customer}} where id = 100
            SQL
        );
        $rows = $command->queryAll();

        $this->assertIsArray($rows);
        $this->assertCount(0, $rows);
        $this->assertSame([], $rows);
    }

    public function testQueryCache(): void
    {
        $db = $this->getConnection('customer');

        $query = (new Query($db))->select(['name'])->from('customer');
        $command = $db->createCommand();
        $update = $command->setSql(
            <<<SQL
            UPDATE {{customer}} SET [[name]] = :name WHERE [[id]] = :id
            SQL
        );

        $this->assertSame('user1', $query->where(['id' => 1])->scalar(), 'Asserting initial value');

        /* No cache */
        $update->bindValues([':id' => 1, ':name' => 'user11'])->execute();

        $this->assertSame(
            'user11',
            $query->where(['id' => 1])->scalar(),
            'Query reflects DB changes when caching is disabled',
        );

        /* Connection cache */
        $db->cache(
            static function (ConnectionPDOInterface $db) use ($query, $update) {
                self::assertSame('user2', $query->where(['id' => 2])->scalar(), 'Asserting initial value for user #2');

                $update->bindValues([':id' => 2, ':name' => 'user22'])->execute();

                self::assertSame(
                    'user2',
                    $query->where(['id' => 2])->scalar(),
                    'Query does NOT reflect DB changes when wrapped in connection caching',
                );

                $db->noCache(
                    static function () use ($query) {
                        self::assertSame(
                            'user22',
                            $query->where(['id' => 2])->scalar(),
                            'Query reflects DB changes when wrapped in connection caching and noCache simultaneously',
                        );
                    }
                );

                self::assertSame(
                    'user2',
                    $query->where(['id' => 2])->scalar(),
                    'Cache does not get changes after getting newer data from DB in noCache block.',
                );
            },
            10,
        );

        $db->queryCacheEnable(false);

        $db->cache(
            static function () use ($query, $update) {
                self::assertSame(
                    'user22',
                    $query->where(['id' => 2])->scalar(),
                    'When cache is disabled for the whole connection, Query inside cache block does not get cached',
                );

                $update->bindValues([':id' => 2, ':name' => 'user2'])->execute();

                self::assertSame('user2', $query->where(['id' => 2])->scalar());
            },
            10,
        );

        $db->queryCacheEnable(true);
        $query->cache();

        $this->assertSame('user11', $query->where(['id' => 1])->scalar());

        $update->bindValues([':id' => 1, ':name' => 'user1'])->execute();

        $this->assertSame(
            'user11',
            $query->where(['id' => 1])->scalar(),
            'When both Connection and Query have cache enabled, we get cached value',
        );
        $this->assertSame(
            'user1',
            $query->noCache()->where(['id' => 1])->scalar(),
            'When Query has disabled cache, we get actual data',
        );

        $db->cache(
            static function () use ($query) {
                self::assertSame('user1', $query->noCache()->where(['id' => 1])->scalar());
                self::assertSame('user11', $query->cache()->where(['id' => 1])->scalar());
            },
            10,
        );
    }

    public function testQueryColumn(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $command->setSql(
            <<<SQL
            SELECT * FROM {{customer}}
            SQL
        );
        $rows = $command->queryColumn();

        $this->assertIsArray($rows);
        $this->assertCount(3, $rows);
        $this->assertEquals('1', $rows[0]);

        $command->setSql('bad SQL');

        $this->expectException(Exception::class);

        $command->queryColumn();
        $command->setSql(
            <<<SQL
            SELECT * FROM {{customer}} where id = 100
            SQL
        );
        $rows = $command->queryColumn();

        $this->assertIsArray($rows);
        $this->assertCount(0, $rows);
        $this->assertSame([], $rows);
    }

    public function testQueryOne(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $sql = <<<SQL
        SELECT * FROM {{customer}} ORDER BY [[id]]
        SQL;
        $row = $command->setSql($sql)->queryOne();

        $this->assertIsArray($row);
        $this->assertEquals(1, $row['id']);
        $this->assertEquals('user1', $row['name']);

        $command->setSql($sql)->prepare();
        $row = $command->queryOne();

        $this->assertIsArray($row);
        $this->assertEquals(1, $row['id']);
        $this->assertEquals('user1', $row['name']);

        $sql = <<<SQL
        SELECT * FROM {{customer}} WHERE [[id]] = 10
        SQL;
        $command = $command->setSql($sql);

        $this->assertNull($command->queryOne());
    }

    public function testQueryScalar(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $sql = <<<SQL
        SELECT * FROM {{customer}} ORDER BY [[id]]
        SQL;

        $this->assertEquals(1, $command->setSql($sql)->queryScalar());

        $sql = <<<SQL
        SELECT [[id]] FROM {{customer}} ORDER BY [[id]]
        SQL;
        $command->setSql($sql)->prepare();

        $this->assertEquals(1, $command->queryScalar());

        $command = $command->setSql(
            <<<SQL
            SELECT [[id]] FROM {{customer}} WHERE [[id]] = 10
            SQL
        );

        $this->assertFalse($command->queryScalar());
    }

    public function testRenameColumn(): void
    {
        $db = $this->getConnection('customer');

        $command = $db->createCommand();
        $schema = $db->getSchema();

        $command->renameColumn('{{customer}}', 'address', 'address_city')->execute();

        $this->assertContains('address_city', $schema->getTableSchema('{{customer}}')->getColumnNames());
        $this->assertNotContains('address', $schema->getTableSchema('{{customer}}')->getColumnNames());
    }

    public function testRenameTable(): void
    {
        $db = $this->getConnection('type');

        $command = $db->createCommand();
        $schema = $db->getSchema();

        if ($schema->getTableSchema('{{new_type}}') !== null) {
            $command->dropTable('{{new_type}}')->execute();
        }

        $this->assertNotNull($schema->getTableSchema('{{type}}'));
        $this->assertNull($schema->getTableSchema('{{new_type}}'));

        $command->renameTable('{{type}}', '{{new_type}}')->execute();

        $this->assertNull($schema->getTableSchema('{{type}}', true));
        $this->assertNotNull($schema->getTableSchema('{{new_type}}', true));
    }

    public function testSetRetryHandler(): void
    {
        $db = $this->getConnection('profile');

        $command = $db->createCommand();

        $this->assertNull($db->getTransaction());

        $command->setSql(
            <<<SQL
            INSERT INTO {{profile}}([[description]]) VALUES('command retry')
            SQL
        )->execute();

        $this->assertNull($db->getTransaction());
        $this->assertEquals(
            1,
            $command->setSql(
                <<<SQL
                SELECT COUNT(*) FROM {{profile}} WHERE [[description]] = 'command retry'
                SQL
            )->queryScalar()
        );

        $attempts = null;
        $hitHandler = false;
        $hitCatch = false;
        $command->setSql(
            <<<SQL
            INSERT INTO {{profile}}([[id]], [[description]]) VALUES(1, 'command retry')
            SQL
        );

        Assert::invokeMethod(
            $command,
            'setRetryHandler',
            [static function ($exception, $attempt) use (&$attempts, &$hitHandler) {
                $attempts = $attempt;
                $hitHandler = true;

                return $attempt <= 2;
            }]
        );

        try {
            $command->execute();
        } catch (Exception $e) {
            $hitCatch = true;

            $this->assertInstanceOf(IntegrityException::class, $e);
        }

        $this->assertNull($db->getTransaction());
        $this->assertSame(3, $attempts);
        $this->assertTrue($hitHandler);
        $this->assertTrue($hitCatch);
    }

    public function testTransaction(): void
    {
        $db = $this->getConnection('profile');

        $this->assertNull($db->getTransaction());

        $command = $db->createCommand();
        $command = $command->setSql(
            <<<SQL
            INSERT INTO {{profile}}([[description]]) VALUES('command transaction')
            SQL
        );

        Assert::invokeMethod($command, 'requireTransaction');

        $command->execute();

        $this->assertNull($db->getTransaction());
        $this->assertEquals(
            1,
            $command->setSql(
                <<<SQL
                SELECT COUNT(*) FROM {{profile}} WHERE [[description]] = 'command transaction'
                SQL
            )->queryScalar(),
        );
    }

    public function testTruncateTable(): void
    {
        $db = $this->getConnection('animal');

        $command = $db->createCommand();
        $rows = $command->setSql(
            <<<SQL
            SELECT * FROM {{animal}}
            SQL
        )->queryAll();

        $this->assertCount(2, $rows);

        $command->truncateTable('animal')->execute();
        $rows = $command->setSql(
            <<<SQL
            SELECT * FROM {{animal}}
            SQL
        )->queryAll();

        $this->assertCount(0, $rows);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\CommandProvider::update()
     */
    public function testUpdate(
        string $table,
        array $columns,
        array|string $conditions,
        array $params,
        string $expected
    ): void {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $sql = $command->update($table, $columns, $conditions, $params)->getSql();

        $this->assertSame($expected, $sql);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\CommandProvider::upsert()
     */
    public function testUpsert(array $firstData, array $secondData): void
    {
        $db = $this->getConnection('customer', 't_upsert');

        $this->assertEquals(0, $db->createCommand('SELECT COUNT(*) FROM {{T_upsert}}')->queryScalar());

        $this->performAndCompareUpsertResult($db, $firstData);

        $this->assertEquals(1, $db->createCommand('SELECT COUNT(*) FROM {{T_upsert}}')->queryScalar());

        $this->performAndCompareUpsertResult($db, $secondData);
    }

    protected function performAndCompareUpsertResult(ConnectionPDOInterface $db, array $data): void
    {
        $params = $data['params'];
        $expected = $data['expected'] ?? $params[1];

        $command = $db->createCommand();

        call_user_func_array([$command, 'upsert'], $params);

        $command->execute();

        $actual = (new Query($db))
            ->select(['email', 'address' => new Expression($this->upsertTestCharCast), 'status'])
            ->from('T_upsert')
            ->one();
        $this->assertEquals($expected, $actual, $this->upsertTestCharCast);
    }
}

yiisoft / db

Pull Request — master (#397)

CommonCommandTest::testsInsertQueryAsColumnValue() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like
