AbstractCommandProvider::upsert() - Code Metrics - Inspection of "Use cache global, raise code coverage 100% `Schema..." - yiisoft/db - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Branch — add-more-test-schema (1ebac6)

by Wilmer

created 2022-12-24 12:44 UTC

AbstractCommandProvider::upsert() B

↳ Parent: Project

Complexity

Conditions	1
Paths	1

Size

Total Lines	110
Code Lines	77

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	77
nc	1
nop	0
dl	0
loc	110
rs	8.5018
c	0
b	0
f	0

How to fix Long Method

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Tests\Provider;

use Yiisoft\Db\Expression\Expression;
use Yiisoft\Db\Query\Query;
use Yiisoft\Db\QueryBuilder\QueryBuilder;
use Yiisoft\Db\Tests\Support\DbHelper;
use Yiisoft\Db\Tests\Support\TestTrait;

abstract class AbstractCommandProvider
{
    use TestTrait;

    public function addForeignKey(): array
    {
        return [
            ['{{test_fk_constraint_1}}', '{{test_fk}}', 'int1', 'int3', 'test_fk_constraint_1'],
            ['{{test_fk_constraint_2}}', '{{test_fk}}', ['int1'], 'int3', 'test_fk_constraint_2'],
            ['{{test_fk_constraint_3}}', '{{test_fk}}', ['int1'], ['int3'], 'test_fk_constraint_3'],
            ['{{test_fk_constraint_4}}', '{{test_fk}}', ['int1', 'int2'], ['int3', 'int4'], 'test_fk_constraint_4'],
        ];
    }

    public function addForeignKeySql(): array
    {
        return [
            [
                '{{test_fk_constraint_1}}',
                '{{test_fk}}',
                'int1',
                'int3',
                null,
                null,
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_1]] FOREIGN KEY ([[int1]]) REFERENCES [[test_fk]] ([[int3]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{test_fk_constraint_2}}',
                '{{test_fk}}',
                ['int1'],
                'int3',
                null,
                null,
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_2]] FOREIGN KEY ([[int1]]) REFERENCES [[test_fk]] ([[int3]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{test_fk_constraint_3}}',
                '{{test_fk}}',
                ['int1'],
                ['int3'],
                null,
                null,
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_3]] FOREIGN KEY ([[int1]]) REFERENCES [[test_fk]] ([[int3]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{test_fk_constraint_4}}',
                '{{test_fk}}',
                ['int1'],
                ['int3'],
                'CASCADE',
                null,
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_4]] FOREIGN KEY ([[int1]]) REFERENCES [[test_fk]] ([[int3]]) ON DELETE CASCADE
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{test_fk_constraint_5}}',
                '{{test_fk}}',
                ['int1'],
                ['int3'],
                'CASCADE',
                'CASCADE',
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_5]] FOREIGN KEY ([[int1]]) REFERENCES [[test_fk]] ([[int3]]) ON DELETE CASCADE ON UPDATE CASCADE
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{test_fk_constraint_6}}',
                '{{test_fk}}',
                ['int1', 'int2'],
                ['int3', 'int4'],
                null,
                null,
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_6]] FOREIGN KEY ([[int1]], [[int2]]) REFERENCES [[test_fk]] ([[int3]], [[int4]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{test_fk_constraint_7}}',
                '{{test_fk}}',
                ['int1', 'int2'],
                ['int3', 'int4'],
                'CASCADE',
                null,
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_7]] FOREIGN KEY ([[int1]], [[int2]]) REFERENCES [[test_fk]] ([[int3]], [[int4]]) ON DELETE CASCADE
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{test_fk_constraint_8}}',
                '{{test_fk}}',
                ['int1', 'int2'],
                ['int3', 'int4'],
                'CASCADE',
                'CASCADE',
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_8]] FOREIGN KEY ([[int1]], [[int2]]) REFERENCES [[test_fk]] ([[int3]], [[int4]]) ON DELETE CASCADE ON UPDATE CASCADE
                    SQL,
                    $this->getDriverName(),
                ),
            ],
        ];
    }

    public function addPrimaryKey(): array
    {
        return [
            ['{{test_pk_constraint_1}}', '{{test_pk}}', 'int1'],
            ['{{test_pk_constraint_2}}', '{{test_pk}}', ['int1']],
            ['{{test_pk_constraint_3}}', 'test_pk', ['int1', 'int2']],
        ];
    }

    public function addPrimaryKeySql(): array
    {
        return [
            [
                '{{test_fk_constraint_1}}',
                '{{test_fk}}',
                'int1',
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_1]] PRIMARY KEY ([[int1]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{test_fk_constraint_2}}',
                '{{test_fk}}',
                ['int1'],
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_2]] PRIMARY KEY ([[int1]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{test_fk_constraint_3}}',
                '{{test_fk}}',
                ['int3', 'int4'],
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_3]] PRIMARY KEY ([[int3]], [[int4]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
        ];
    }

    public function addUnique(): array
    {
        return [
            ['{{test_unique_constraint_1}}', '{{test_unique}}', 'int1'],
            ['{{test_unique_constraint_2}}', '{{test_unique}}', ['int1']],
            ['{{test_unique_constraint_3}}', '{{test_unique}}', ['int1', 'int2']],
        ];
    }

    public function addUniqueSql(): array
    {
        return [
            [
                '{{test_fk_constraint_1}}',
                '{{test_fk}}',
                'int1',
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_1]] UNIQUE ([[int1]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{test_fk_constraint_2}}',
                '{{test_fk}}',
                ['int1'],
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_2]] UNIQUE ([[int1]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{test_fk_constraint_3}}',
                '{{test_fk}}',
                ['int3', 'int4'],
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_3]] UNIQUE ([[int3]], [[int4]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{test_fk_constraint_3}}',
                '{{test_fk}}',
                ['int1', 'int2'],
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_3]] UNIQUE ([[int1]], [[int2]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
        ];
    }

    public function batchInsert(): array
    {
        return [
            'multirow' => [
                'type',
                ['int_col', 'float_col', 'char_col', 'bool_col'],
                'values' => [
                    ['0', '0.0', 'test string', true],
                    [false, 0, 'test string2', false],
                ],
                'expected' => DbHelper::replaceQuotes(
                    <<<SQL
                    INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp0, :qp1, :qp2, :qp3), (:qp4, :qp5, :qp6, :qp7)
                    SQL,
                    $this->getDriverName(),
                ),
                'expectedParams' => [
                    ':qp0' => 0,
                    ':qp1' => 0.0,
                    ':qp2' => 'test string',
                    ':qp3' => true,
                    ':qp4' => 0,
                    ':qp5' => 0.0,
                    ':qp6' => 'test string2',
                    ':qp7' => false,
                ],
                2,
            ],
            'issue11242' => [
                'type',
                ['int_col', 'float_col', 'char_col', 'bool_col'],
                'values' => [[1.0, 1.1, 'Kyiv {{city}}, Ukraine', true]],
                /**
                 * {@see https://github.com/yiisoft/yii2/issues/11242}
                 *
                 * Make sure curly bracelets (`{{..}}`) in values will not be escaped
                 */
                'expected' => DbHelper::replaceQuotes(
                    <<<SQL
                    INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp0, :qp1, :qp2, :qp3)
                    SQL,
                    $this->getDriverName(),
                ),
                'expectedParams' => [
                    ':qp0' => 1,
                    ':qp1' => 1.1,
                    ':qp2' => 'Kyiv {{city}}, Ukraine',
                    ':qp3' => true,
                ],
            ],
            'wrongBehavior' => [
                '{{%type}}',
                ['{{%type}}.[[int_col]]', '[[float_col]]', 'char_col', 'bool_col'],
                'values' => [['0', '0.0', 'Kyiv {{city}}, Ukraine', false]],
                /**
                 * Test covers potentially wrong behavior and marks it as expected!.
                 *
                 * In case table name or table column is passed with curly or square bracelets, QueryBuilder can not
                 * determine the table schema and typecast values properly.
                 * TODO: make it work. Impossible without BC breaking for public methods.
                 */
                'expected' => DbHelper::replaceQuotes(
                    <<<SQL
                    INSERT INTO [[type]] ([[type]].[[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp0, :qp1, :qp2, :qp3)
                    SQL,
                    $this->getDriverName(),
                ),
                'expectedParams' => [
                    ':qp0' => '0',
                    ':qp1' => '0.0',
                    ':qp2' => 'Kyiv {{city}}, Ukraine',
                    ':qp3' => false,
                ],
            ],
            'batchInsert binds params from expression' => [
                '{{%type}}',
                ['int_col', 'float_col', 'char_col', 'bool_col'],
                /**
                 * This example is completely useless. This feature of batchInsert is intended to be used with complex
                 * expression objects, such as JsonExpression.
                 */
                'values' => [[new Expression(':exp1', [':exp1' => 42]), 1, 'test', false]],
                'expected' => DbHelper::replaceQuotes(
                    <<<SQL
                    INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:exp1, :qp1, :qp2, :qp3)
                    SQL,
                    $this->getDriverName(),
                ),
                'expectedParams' => [
                    ':exp1' => 42,
                    ':qp1' => 1.0,
                    ':qp2' => 'test',
                    ':qp3' => false,
                ],
            ],
        ];
    }

    public function createIndex(): array
    {
        return [
            ['{{test_idx_constraint_1}}', '{{test_idx}}', 'int1', null, null],
            ['{{test_idx_constraint_2}}', '{{test_idx}}', ['int1'], QueryBuilder::INDEX_UNIQUE, null],
            ['{{test_idx_constraint_3}}', '{{test_idx}}', ['int1', 'int2'], null, null],
        ];
    }

    public function createIndexSql(): array
    {
        return [
            [
                '{{name}}',
                '{{table}}',
                'column',
                '',
                '',
                DbHelper::replaceQuotes(
                    <<<SQL
                    CREATE INDEX [[name]] ON [[table]] ([[column]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{name}}',
                '{{table}}',
                ['column1', 'column2'],
                '',
                '',
                DbHelper::replaceQuotes(
                    <<<SQL
                    CREATE INDEX [[name]] ON [[table]] ([[column1]], [[column2]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{name}}',
                '{{table}}',
                ['column1', 'column2'],
                QueryBuilder::INDEX_UNIQUE,
                '',
                DbHelper::replaceQuotes(
                    <<<SQL
                    CREATE UNIQUE INDEX [[name]] ON [[table]] ([[column1]], [[column2]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{name}}',
                '{{table}}',
                ['column1', 'column2'],
                'FULLTEXT',
                '',
                DbHelper::replaceQuotes(
                    <<<SQL
                    CREATE FULLTEXT INDEX [[name]] ON [[table]] ([[column1]], [[column2]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{name}}',
                '{{table}}',
                ['column1', 'column2'],
                'SPATIAL',
                '',
                DbHelper::replaceQuotes(
                    <<<SQL
                    CREATE SPATIAL INDEX [[name]] ON [[table]] ([[column1]], [[column2]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{name}}',
                '{{table}}',
                ['column1', 'column2'],
                'BITMAP',
                '',
                DbHelper::replaceQuotes(
                    <<<SQL
                    CREATE BITMAP INDEX [[name]] ON [[table]] ([[column1]], [[column2]])
                    SQL,
                    $this->getDriverName(),
                ),
            ],
        ];
    }

    public function invalidSelectColumns(): array
    {
        return [[[]], ['*'], [['*']]];
    }

    public function rawSql(): array
    {
        return [
            [
                <<<SQL
                SELECT * FROM [[customer]] WHERE [[id]] = :id
                SQL,
                [':id' => 1],
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT * FROM [[customer]] WHERE [[id]] = 1
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                <<<SQL
                SELECT * FROM [[customer]] WHERE [[id]] = :id
                SQL,
                ['id' => 1],
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT * FROM [[customer]] WHERE [[id]] = 1
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                <<<SQL
                SELECT * FROM [[customer]] WHERE [[id]] = :id
                SQL,
                ['id' => null],
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT * FROM [[customer]] WHERE [[id]] = NULL
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                <<<SQL
                SELECT * FROM [[customer]] WHERE [[id]] = :base OR [[id]] = :basePrefix
                SQL,
                ['base' => 1, 'basePrefix' => 2],
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT * FROM [[customer]] WHERE [[id]] = 1 OR [[id]] = 2
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            /**
             * {@see https://github.com/yiisoft/yii2/issues/9268}
             */
            [
                <<<SQL
                SELECT * FROM [[customer]] WHERE [[active]] = :active
                SQL,
                [':active' => false],
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT * FROM [[customer]] WHERE [[active]] = FALSE
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            /**
             * {@see https://github.com/yiisoft/yii2/issues/15122}
             */
            [
                <<<SQL
                SELECT * FROM [[customer]] WHERE [[id]] IN (:ids)
                SQL,
                [':ids' => new Expression(implode(', ', [1, 2]))],
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT * FROM [[customer]] WHERE [[id]] IN (1, 2)
                    SQL,
                    $this->getDriverName(),
                ),
            ],
        ];
    }

    public function update(): array
    {
        return [
            [
                '{{table}}',
                ['name' => '{{test}}'],
                [],
                [],
                DbHelper::replaceQuotes(
                    <<<SQL
                    UPDATE [[table]] SET [[name]]=:qp0
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{table}}',
                ['name' => '{{test}}'],
                ['id' => 1],
                [],
                DbHelper::replaceQuotes(
                    <<<SQL
                    UPDATE [[table]] SET [[name]]=:qp0 WHERE [[id]]=:qp1
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{table}}',
                ['name' => '{{test}}'],
                ['id' => 1],
                ['id' => 'integer'],
                DbHelper::replaceQuotes(
                    <<<SQL
                    UPDATE [[table]] SET [[name]]=:qp1 WHERE [[id]]=:qp2
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{table}}',
                ['name' => '{{test}}'],
                ['id' => 1],
                ['id' => 'string'],
                DbHelper::replaceQuotes(
                    <<<SQL
                    UPDATE [[table]] SET [[name]]=:qp1 WHERE [[id]]=:qp2
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{table}}',
                ['name' => '{{test}}'],
                ['id' => 1],
                ['id' => 'boolean'],
                DbHelper::replaceQuotes(
                    <<<SQL
                    UPDATE [[table]] SET [[name]]=:qp1 WHERE [[id]]=:qp2
                    SQL,
                    $this->getDriverName(),
                ),
            ],
            [
                '{{table}}',
                ['name' => '{{test}}'],
                ['id' => 1],
                ['id' => 'float'],
                DbHelper::replaceQuotes(
                    <<<SQL
                    UPDATE [[table]] SET [[name]]=:qp1 WHERE [[id]]=:qp2
                    SQL,
                    $this->getDriverName(),
                ),
            ],
        ];
    }

    public function upsert(): array
    {
        $db = $this->getConnection();

        return [
            'regular values' => [
                ['params' => ['T_upsert', ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3]]],
                ['params' => ['T_upsert', ['email' => '[email protected]', 'address' => 'Universe', 'status' => 1]]],
            ],
            'regular values with update part' => [
                ['params' => [
                    'T_upsert',
                    ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3],
                    ['address' => 'Moon', 'status' => 2],
                ],
                ],
                [
                    'params' => [
                        'T_upsert',
                        ['email' => '[email protected]', 'address' => 'Universe', 'status' => 1],
                        ['address' => 'Moon', 'status' => 2],
                    ],
                    'expected' => ['email' => '[email protected]', 'address' => 'Moon', 'status' => 2],
                ],
            ],
            'regular values without update part' => [
                ['params' => ['T_upsert', ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3], false]],
                [
                    'params' => [
                        'T_upsert',
                        ['email' => '[email protected]', 'address' => 'Universe', 'status' => 1],
                        false,
                    ],
                    'expected' => ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3],
                ],
            ],
            'query' => [
                [
                    'params' => [
                        'T_upsert',
                        (new query($db))
                            ->select(['email', 'address', 'status' => new Expression('1')])
                            ->from('{{customer}}')
                            ->where(['name' => 'user1'])
                            ->limit(1),
                    ],
                    'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
                ],
                [
                    'params' => [
                        'T_upsert',
                        (new query($db))
                            ->select(['email', 'address', 'status' => new Expression('2')])
                            ->from('{{customer}}')
                            ->where(['name' => 'user1'])
                            ->limit(1),
                    ],
                    'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 2],
                ],
            ],
            'query with update part' => [
                [
                    'params' => [
                        'T_upsert',
                        (new query($db))
                            ->select(['email', 'address', 'status' => new Expression('1')])
                            ->from('{{customer}}')
                            ->where(['name' => 'user1'])
                            ->limit(1),
                        ['address' => 'Moon', 'status' => 2],
                    ],
                    'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
                ],
                [
                    'params' => [
                        'T_upsert',
                        (new query($db))
                            ->select(['email', 'address', 'status' => new Expression('3')])
                            ->from('{{customer}}')
                            ->where(['name' => 'user1'])
                            ->limit(1),
                        ['address' => 'Moon', 'status' => 2],
                    ],
                    'expected' => ['email' => '[email protected]', 'address' => 'Moon', 'status' => 2],
                ],
            ],
            'query without update part' => [
                [
                    'params' => [
                        'T_upsert',
                        (new query($db))
                            ->select(['email', 'address', 'status' => new Expression('1')])
                            ->from('{{customer}}')
                            ->where(['name' => 'user1'])
                            ->limit(1),
                        false,
                    ],
                    'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
                ],
                [
                    'params' => [
                        'T_upsert',
                        (new query($db))
                            ->select(['email', 'address', 'status' => new Expression('2')])
                            ->from('{{customer}}')
                            ->where(['name' => 'user1'])
                            ->limit(1),
                        false,
                    ],
                    'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
                ],
            ],
        ];
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace Yiisoft\Db\Tests\Provider;
6
7			use Yiisoft\Db\Expression\Expression;
8			use Yiisoft\Db\Query\Query;
9			use Yiisoft\Db\QueryBuilder\QueryBuilder;
10			use Yiisoft\Db\Tests\Support\DbHelper;
11			use Yiisoft\Db\Tests\Support\TestTrait;
12
13			abstract class AbstractCommandProvider
14			{
15			use TestTrait;
16
17			public function addForeignKey(): array
18			{
19			return [
20			['{{test_fk_constraint_1}}', '{{test_fk}}', 'int1', 'int3', 'test_fk_constraint_1'],
21			['{{test_fk_constraint_2}}', '{{test_fk}}', ['int1'], 'int3', 'test_fk_constraint_2'],
22			['{{test_fk_constraint_3}}', '{{test_fk}}', ['int1'], ['int3'], 'test_fk_constraint_3'],
23			['{{test_fk_constraint_4}}', '{{test_fk}}', ['int1', 'int2'], ['int3', 'int4'], 'test_fk_constraint_4'],
24			];
25			}
26
27			public function addForeignKeySql(): array
28			{
29			return [
30			[
31			'{{test_fk_constraint_1}}',
32			'{{test_fk}}',
33			'int1',
34			'int3',
35			null,
36			null,
37			DbHelper::replaceQuotes(
38			<<<SQL
39			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_1]] FOREIGN KEY ([[int1]]) REFERENCES [[test_fk]] ([[int3]])
40			SQL,
41			$this->getDriverName(),
42			),
43			],
44			[
45			'{{test_fk_constraint_2}}',
46			'{{test_fk}}',
47			['int1'],
48			'int3',
49			null,
50			null,
51			DbHelper::replaceQuotes(
52			<<<SQL
53			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_2]] FOREIGN KEY ([[int1]]) REFERENCES [[test_fk]] ([[int3]])
54			SQL,
55			$this->getDriverName(),
56			),
57			],
58			[
59			'{{test_fk_constraint_3}}',
60			'{{test_fk}}',
61			['int1'],
62			['int3'],
63			null,
64			null,
65			DbHelper::replaceQuotes(
66			<<<SQL
67			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_3]] FOREIGN KEY ([[int1]]) REFERENCES [[test_fk]] ([[int3]])
68			SQL,
69			$this->getDriverName(),
70			),
71			],
72			[
73			'{{test_fk_constraint_4}}',
74			'{{test_fk}}',
75			['int1'],
76			['int3'],
77			'CASCADE',
78			null,
79			DbHelper::replaceQuotes(
80			<<<SQL
81			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_4]] FOREIGN KEY ([[int1]]) REFERENCES [[test_fk]] ([[int3]]) ON DELETE CASCADE
82			SQL,
83			$this->getDriverName(),
84			),
85			],
86			[
87			'{{test_fk_constraint_5}}',
88			'{{test_fk}}',
89			['int1'],
90			['int3'],
91			'CASCADE',
92			'CASCADE',
93			DbHelper::replaceQuotes(
94			<<<SQL
95			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_5]] FOREIGN KEY ([[int1]]) REFERENCES [[test_fk]] ([[int3]]) ON DELETE CASCADE ON UPDATE CASCADE
96			SQL,
97			$this->getDriverName(),
98			),
99			],
100			[
101			'{{test_fk_constraint_6}}',
102			'{{test_fk}}',
103			['int1', 'int2'],
104			['int3', 'int4'],
105			null,
106			null,
107			DbHelper::replaceQuotes(
108			<<<SQL
109			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_6]] FOREIGN KEY ([[int1]], [[int2]]) REFERENCES [[test_fk]] ([[int3]], [[int4]])
110			SQL,
111			$this->getDriverName(),
112			),
113			],
114			[
115			'{{test_fk_constraint_7}}',
116			'{{test_fk}}',
117			['int1', 'int2'],
118			['int3', 'int4'],
119			'CASCADE',
120			null,
121			DbHelper::replaceQuotes(
122			<<<SQL
123			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_7]] FOREIGN KEY ([[int1]], [[int2]]) REFERENCES [[test_fk]] ([[int3]], [[int4]]) ON DELETE CASCADE
124			SQL,
125			$this->getDriverName(),
126			),
127			],
128			[
129			'{{test_fk_constraint_8}}',
130			'{{test_fk}}',
131			['int1', 'int2'],
132			['int3', 'int4'],
133			'CASCADE',
134			'CASCADE',
135			DbHelper::replaceQuotes(
136			<<<SQL
137			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_8]] FOREIGN KEY ([[int1]], [[int2]]) REFERENCES [[test_fk]] ([[int3]], [[int4]]) ON DELETE CASCADE ON UPDATE CASCADE
138			SQL,
139			$this->getDriverName(),
140			),
141			],
142			];
143			}
144
145			public function addPrimaryKey(): array
146			{
147			return [
148			['{{test_pk_constraint_1}}', '{{test_pk}}', 'int1'],
149			['{{test_pk_constraint_2}}', '{{test_pk}}', ['int1']],
150			['{{test_pk_constraint_3}}', 'test_pk', ['int1', 'int2']],
151			];
152			}
153
154			public function addPrimaryKeySql(): array
155			{
156			return [
157			[
158			'{{test_fk_constraint_1}}',
159			'{{test_fk}}',
160			'int1',
161			DbHelper::replaceQuotes(
162			<<<SQL
163			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_1]] PRIMARY KEY ([[int1]])
164			SQL,
165			$this->getDriverName(),
166			),
167			],
168			[
169			'{{test_fk_constraint_2}}',
170			'{{test_fk}}',
171			['int1'],
172			DbHelper::replaceQuotes(
173			<<<SQL
174			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_2]] PRIMARY KEY ([[int1]])
175			SQL,
176			$this->getDriverName(),
177			),
178			],
179			[
180			'{{test_fk_constraint_3}}',
181			'{{test_fk}}',
182			['int3', 'int4'],
183			DbHelper::replaceQuotes(
184			<<<SQL
185			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_3]] PRIMARY KEY ([[int3]], [[int4]])
186			SQL,
187			$this->getDriverName(),
188			),
189			],
190			];
191			}
192
193			public function addUnique(): array
194			{
195			return [
196			['{{test_unique_constraint_1}}', '{{test_unique}}', 'int1'],
197			['{{test_unique_constraint_2}}', '{{test_unique}}', ['int1']],
198			['{{test_unique_constraint_3}}', '{{test_unique}}', ['int1', 'int2']],
199			];
200			}
201
202			public function addUniqueSql(): array
203			{
204			return [
205			[
206			'{{test_fk_constraint_1}}',
207			'{{test_fk}}',
208			'int1',
209			DbHelper::replaceQuotes(
210			<<<SQL
211			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_1]] UNIQUE ([[int1]])
212			SQL,
213			$this->getDriverName(),
214			),
215			],
216			[
217			'{{test_fk_constraint_2}}',
218			'{{test_fk}}',
219			['int1'],
220			DbHelper::replaceQuotes(
221			<<<SQL
222			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_2]] UNIQUE ([[int1]])
223			SQL,
224			$this->getDriverName(),
225			),
226			],
227			[
228			'{{test_fk_constraint_3}}',
229			'{{test_fk}}',
230			['int3', 'int4'],
231			DbHelper::replaceQuotes(
232			<<<SQL
233			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_3]] UNIQUE ([[int3]], [[int4]])
234			SQL,
235			$this->getDriverName(),
236			),
237			],
238			[
239			'{{test_fk_constraint_3}}',
240			'{{test_fk}}',
241			['int1', 'int2'],
242			DbHelper::replaceQuotes(
243			<<<SQL
244			ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_3]] UNIQUE ([[int1]], [[int2]])
245			SQL,
246			$this->getDriverName(),
247			),
248			],
249			];
250			}
251
252			public function batchInsert(): array
253			{
254			return [
255			'multirow' => [
256			'type',
257			['int_col', 'float_col', 'char_col', 'bool_col'],
258			'values' => [
259			['0', '0.0', 'test string', true],
260			[false, 0, 'test string2', false],
261			],
262			'expected' => DbHelper::replaceQuotes(
263			<<<SQL
264			INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp0, :qp1, :qp2, :qp3), (:qp4, :qp5, :qp6, :qp7)
265			SQL,
266			$this->getDriverName(),
267			),
268			'expectedParams' => [
269			':qp0' => 0,
270			':qp1' => 0.0,
271			':qp2' => 'test string',
272			':qp3' => true,
273			':qp4' => 0,
274			':qp5' => 0.0,
275			':qp6' => 'test string2',
276			':qp7' => false,
277			],
278			2,
279			],
280			'issue11242' => [
281			'type',
282			['int_col', 'float_col', 'char_col', 'bool_col'],
283			'values' => [[1.0, 1.1, 'Kyiv {{city}}, Ukraine', true]],
284			/**
285			* {@see https://github.com/yiisoft/yii2/issues/11242}
286			*
287			* Make sure curly bracelets (`{{..}}`) in values will not be escaped
288			*/
289			'expected' => DbHelper::replaceQuotes(
290			<<<SQL
291			INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp0, :qp1, :qp2, :qp3)
292			SQL,
293			$this->getDriverName(),
294			),
295			'expectedParams' => [
296			':qp0' => 1,
297			':qp1' => 1.1,
298			':qp2' => 'Kyiv {{city}}, Ukraine',
299			':qp3' => true,
300			],
301			],
302			'wrongBehavior' => [
303			'{{%type}}',
304			['{{%type}}.[[int_col]]', '[[float_col]]', 'char_col', 'bool_col'],
305			'values' => [['0', '0.0', 'Kyiv {{city}}, Ukraine', false]],
306			/**
307			* Test covers potentially wrong behavior and marks it as expected!.
308			*
309			* In case table name or table column is passed with curly or square bracelets, QueryBuilder can not
310			* determine the table schema and typecast values properly.
311			* TODO: make it work. Impossible without BC breaking for public methods.
312			*/
313			'expected' => DbHelper::replaceQuotes(
314			<<<SQL
315			INSERT INTO [[type]] ([[type]].[[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp0, :qp1, :qp2, :qp3)
316			SQL,
317			$this->getDriverName(),
318			),
319			'expectedParams' => [
320			':qp0' => '0',
321			':qp1' => '0.0',
322			':qp2' => 'Kyiv {{city}}, Ukraine',
323			':qp3' => false,
324			],
325			],
326			'batchInsert binds params from expression' => [
327			'{{%type}}',
328			['int_col', 'float_col', 'char_col', 'bool_col'],
329			/**
330			* This example is completely useless. This feature of batchInsert is intended to be used with complex
331			* expression objects, such as JsonExpression.
332			*/
333			'values' => [[new Expression(':exp1', [':exp1' => 42]), 1, 'test', false]],
334			'expected' => DbHelper::replaceQuotes(
335			<<<SQL
336			INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:exp1, :qp1, :qp2, :qp3)
337			SQL,
338			$this->getDriverName(),
339			),
340			'expectedParams' => [
341			':exp1' => 42,
342			':qp1' => 1.0,
343			':qp2' => 'test',
344			':qp3' => false,
345			],
346			],
347			];
348			}
349
350			public function createIndex(): array
351			{
352			return [
353			['{{test_idx_constraint_1}}', '{{test_idx}}', 'int1', null, null],
354			['{{test_idx_constraint_2}}', '{{test_idx}}', ['int1'], QueryBuilder::INDEX_UNIQUE, null],
355			['{{test_idx_constraint_3}}', '{{test_idx}}', ['int1', 'int2'], null, null],
356			];
357			}
358
359			public function createIndexSql(): array
360			{
361			return [
362			[
363			'{{name}}',
364			'{{table}}',
365			'column',
366			'',
367			'',
368			DbHelper::replaceQuotes(
369			<<<SQL
370			CREATE INDEX [[name]] ON [[table]] ([[column]])
371			SQL,
372			$this->getDriverName(),
373			),
374			],
375			[
376			'{{name}}',
377			'{{table}}',
378			['column1', 'column2'],
379			'',
380			'',
381			DbHelper::replaceQuotes(
382			<<<SQL
383			CREATE INDEX [[name]] ON [[table]] ([[column1]], [[column2]])
384			SQL,
385			$this->getDriverName(),
386			),
387			],
388			[
389			'{{name}}',
390			'{{table}}',
391			['column1', 'column2'],
392			QueryBuilder::INDEX_UNIQUE,
393			'',
394			DbHelper::replaceQuotes(
395			<<<SQL
396			CREATE UNIQUE INDEX [[name]] ON [[table]] ([[column1]], [[column2]])
397			SQL,
398			$this->getDriverName(),
399			),
400			],
401			[
402			'{{name}}',
403			'{{table}}',
404			['column1', 'column2'],
405			'FULLTEXT',
406			'',
407			DbHelper::replaceQuotes(
408			<<<SQL
409			CREATE FULLTEXT INDEX [[name]] ON [[table]] ([[column1]], [[column2]])
410			SQL,
411			$this->getDriverName(),
412			),
413			],
414			[
415			'{{name}}',
416			'{{table}}',
417			['column1', 'column2'],
418			'SPATIAL',
419			'',
420			DbHelper::replaceQuotes(
421			<<<SQL
422			CREATE SPATIAL INDEX [[name]] ON [[table]] ([[column1]], [[column2]])
423			SQL,
424			$this->getDriverName(),
425			),
426			],
427			[
428			'{{name}}',
429			'{{table}}',
430			['column1', 'column2'],
431			'BITMAP',
432			'',
433			DbHelper::replaceQuotes(
434			<<<SQL
435			CREATE BITMAP INDEX [[name]] ON [[table]] ([[column1]], [[column2]])
436			SQL,
437			$this->getDriverName(),
438			),
439			],
440			];
441			}
442
443			public function invalidSelectColumns(): array
444			{
445			return [[[]], [''], [['']]];
446			}
447
448			public function rawSql(): array
449			{
450			return [
451			[
452			<<<SQL
453			SELECT * FROM [[customer]] WHERE [[id]] = :id
454			SQL,
455			[':id' => 1],
456			DbHelper::replaceQuotes(
457			<<<SQL
458			SELECT * FROM [[customer]] WHERE [[id]] = 1
459			SQL,
460			$this->getDriverName(),
461			),
462			],
463			[
464			<<<SQL
465			SELECT * FROM [[customer]] WHERE [[id]] = :id
466			SQL,
467			['id' => 1],
468			DbHelper::replaceQuotes(
469			<<<SQL
470			SELECT * FROM [[customer]] WHERE [[id]] = 1
471			SQL,
472			$this->getDriverName(),
473			),
474			],
475			[
476			<<<SQL
477			SELECT * FROM [[customer]] WHERE [[id]] = :id
478			SQL,
479			['id' => null],
480			DbHelper::replaceQuotes(
481			<<<SQL
482			SELECT * FROM [[customer]] WHERE [[id]] = NULL
483			SQL,
484			$this->getDriverName(),
485			),
486			],
487			[
488			<<<SQL
489			SELECT * FROM [[customer]] WHERE [[id]] = :base OR [[id]] = :basePrefix
490			SQL,
491			['base' => 1, 'basePrefix' => 2],
492			DbHelper::replaceQuotes(
493			<<<SQL
494			SELECT * FROM [[customer]] WHERE [[id]] = 1 OR [[id]] = 2
495			SQL,
496			$this->getDriverName(),
497			),
498			],
499			/**
500			* {@see https://github.com/yiisoft/yii2/issues/9268}
501			*/
502			[
503			<<<SQL
504			SELECT * FROM [[customer]] WHERE [[active]] = :active
505			SQL,
506			[':active' => false],
507			DbHelper::replaceQuotes(
508			<<<SQL
509			SELECT * FROM [[customer]] WHERE [[active]] = FALSE
510			SQL,
511			$this->getDriverName(),
512			),
513			],
514			/**
515			* {@see https://github.com/yiisoft/yii2/issues/15122}
516			*/
517			[
518			<<<SQL
519			SELECT * FROM [[customer]] WHERE [[id]] IN (:ids)
520			SQL,
521			[':ids' => new Expression(implode(', ', [1, 2]))],
522			DbHelper::replaceQuotes(
523			<<<SQL
524			SELECT * FROM [[customer]] WHERE [[id]] IN (1, 2)
525			SQL,
526			$this->getDriverName(),
527			),
528			],
529			];
530			}
531
532			public function update(): array
533			{
534			return [
535			[
536			'{{table}}',
537			['name' => '{{test}}'],
538			[],
539			[],
540			DbHelper::replaceQuotes(
541			<<<SQL
542			UPDATE [[table]] SET [[name]]=:qp0
543			SQL,
544			$this->getDriverName(),
545			),
546			],
547			[
548			'{{table}}',
549			['name' => '{{test}}'],
550			['id' => 1],
551			[],
552			DbHelper::replaceQuotes(
553			<<<SQL
554			UPDATE [[table]] SET [[name]]=:qp0 WHERE [[id]]=:qp1
555			SQL,
556			$this->getDriverName(),
557			),
558			],
559			[
560			'{{table}}',
561			['name' => '{{test}}'],
562			['id' => 1],
563			['id' => 'integer'],
564			DbHelper::replaceQuotes(
565			<<<SQL
566			UPDATE [[table]] SET [[name]]=:qp1 WHERE [[id]]=:qp2
567			SQL,
568			$this->getDriverName(),
569			),
570			],
571			[
572			'{{table}}',
573			['name' => '{{test}}'],
574			['id' => 1],
575			['id' => 'string'],
576			DbHelper::replaceQuotes(
577			<<<SQL
578			UPDATE [[table]] SET [[name]]=:qp1 WHERE [[id]]=:qp2
579			SQL,
580			$this->getDriverName(),
581			),
582			],
583			[
584			'{{table}}',
585			['name' => '{{test}}'],
586			['id' => 1],
587			['id' => 'boolean'],
588			DbHelper::replaceQuotes(
589			<<<SQL
590			UPDATE [[table]] SET [[name]]=:qp1 WHERE [[id]]=:qp2
591			SQL,
592			$this->getDriverName(),
593			),
594			],
595			[
596			'{{table}}',
597			['name' => '{{test}}'],
598			['id' => 1],
599			['id' => 'float'],
600			DbHelper::replaceQuotes(
601			<<<SQL
602			UPDATE [[table]] SET [[name]]=:qp1 WHERE [[id]]=:qp2
603			SQL,
604			$this->getDriverName(),
605			),
606			],
607			];
608			}
609
610			public function upsert(): array
611			{
612			$db = $this->getConnection();
613
614			return [
615			'regular values' => [
616			['params' => ['T_upsert', ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3]]],
617			['params' => ['T_upsert', ['email' => '[email protected]', 'address' => 'Universe', 'status' => 1]]],
618			],
619			'regular values with update part' => [
620			['params' => [
621			'T_upsert',
622			['email' => '[email protected]', 'address' => 'Earth', 'status' => 3],
623			['address' => 'Moon', 'status' => 2],
624			],
625			],
626			[
627			'params' => [
628			'T_upsert',
629			['email' => '[email protected]', 'address' => 'Universe', 'status' => 1],
630			['address' => 'Moon', 'status' => 2],
631			],
632			'expected' => ['email' => '[email protected]', 'address' => 'Moon', 'status' => 2],
633			],
634			],
635			'regular values without update part' => [
636			['params' => ['T_upsert', ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3], false]],
637			[
638			'params' => [
639			'T_upsert',
640			['email' => '[email protected]', 'address' => 'Universe', 'status' => 1],
641			false,
642			],
643			'expected' => ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3],
644			],
645			],
646			'query' => [
647			[
648			'params' => [
649			'T_upsert',
650			(new query($db))
651			->select(['email', 'address', 'status' => new Expression('1')])
652			->from('{{customer}}')
653			->where(['name' => 'user1'])
654			->limit(1),
655			],
656			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
657			],
658			[
659			'params' => [
660			'T_upsert',
661			(new query($db))
662			->select(['email', 'address', 'status' => new Expression('2')])
663			->from('{{customer}}')
664			->where(['name' => 'user1'])
665			->limit(1),
666			],
667			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 2],
668			],
669			],
670			'query with update part' => [
671			[
672			'params' => [
673			'T_upsert',
674			(new query($db))
675			->select(['email', 'address', 'status' => new Expression('1')])
676			->from('{{customer}}')
677			->where(['name' => 'user1'])
678			->limit(1),
679			['address' => 'Moon', 'status' => 2],
680			],
681			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
682			],
683			[
684			'params' => [
685			'T_upsert',
686			(new query($db))
687			->select(['email', 'address', 'status' => new Expression('3')])
688			->from('{{customer}}')
689			->where(['name' => 'user1'])
690			->limit(1),
691			['address' => 'Moon', 'status' => 2],
692			],
693			'expected' => ['email' => '[email protected]', 'address' => 'Moon', 'status' => 2],
694			],
695			],
696			'query without update part' => [
697			[
698			'params' => [
699			'T_upsert',
700			(new query($db))
701			->select(['email', 'address', 'status' => new Expression('1')])
702			->from('{{customer}}')
703			->where(['name' => 'user1'])
704			->limit(1),
705			false,
706			],
707			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
708			],
709			[
710			'params' => [
711			'T_upsert',
712			(new query($db))
713			->select(['email', 'address', 'status' => new Expression('2')])
714			->from('{{customer}}')
715			->where(['name' => 'user1'])
716			->limit(1),
717			false,
718			],
719			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
720			],
721			],
722			];
723			}
724			}
725

yiisoft / db

Branch — add-more-test-schema (1ebac6)

AbstractCommandProvider::upsert() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like