BaseCommandProvider::createIndex() - 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-26 13:34 UTC

BaseCommandProvider::createIndex() A

↳ Parent: BaseCommandProvider

Complexity

Conditions	1
Paths	1

Size

Total Lines	6
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	4
nc	1
nop	0
dl	0
loc	6
rs	10
c	1
b	0
f	0

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Tests\Provider;

use Yiisoft\Db\Driver\PDO\ConnectionPDOInterface;
use Yiisoft\Db\Expression\Expression;
use Yiisoft\Db\Query\Query;
use Yiisoft\Db\QueryBuilder\QueryBuilder;
use Yiisoft\Db\Tests\Support\DbHelper;

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

    public function addForeignKeySql(ConnectionPDOInterface $db): 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,
                    $db->getName(),
                ),
            ],
            [
                '{{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,
                    $db->getName(),
                ),
            ],
            [
                '{{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,
                    $db->getName(),
                ),
            ],
            [
                '{{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,
                    $db->getName(),
                ),
            ],
            [
                '{{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,
                    $db->getName(),
                ),
            ],
            [
                '{{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,
                    $db->getName(),
                ),
            ],
            [
                '{{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,
                    $db->getName(),
                ),
            ],
            [
                '{{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,
                    $db->getName(),
                ),
            ],
        ];
    }

    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(ConnectionPDOInterface $db): 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,
                    $db->getName(),
                ),
            ],
            [
                '{{test_fk_constraint_2}}',
                '{{test_fk}}',
                ['int1'],
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_2]] PRIMARY KEY ([[int1]])
                    SQL,
                    $db->getName(),
                ),
            ],
            [
                '{{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,
                    $db->getName(),
                ),
            ],
        ];
    }

    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(ConnectionPDOInterface $db): 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,
                    $db->getName(),
                ),
            ],
            [
                '{{test_fk_constraint_2}}',
                '{{test_fk}}',
                ['int1'],
                DbHelper::replaceQuotes(
                    <<<SQL
                    ALTER TABLE [[test_fk]] ADD CONSTRAINT [[test_fk_constraint_2]] UNIQUE ([[int1]])
                    SQL,
                    $db->getName(),
                ),
            ],
            [
                '{{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,
                    $db->getName(),
                ),
            ],
            [
                '{{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,
                    $db->getName(),
                ),
            ],
        ];
    }

    public function batchInsert(ConnectionPDOInterface $db): 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,
                    $db->getName(),
                ),
                '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,
                    $db->getName(),
                ),
                '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,
                    $db->getName(),
                ),
                '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,
                    $db->getName(),
                ),
                'expectedParams' => [
                    ':exp1' => 42,
                    ':qp1' => 1.0,
                    ':qp2' => 'test',
                    ':qp3' => false,
                ],
            ],
        ];
    }

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

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

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

    public function rawSql(ConnectionPDOInterface $db): array
    {
        return [
            [
                <<<SQL
                SELECT * FROM [[customer]] WHERE [[id]] = :id
                SQL,
                [':id' => 1],
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT * FROM [[customer]] WHERE [[id]] = 1
                    SQL,
                    $db->getName()
                ),
            ],
            [
                <<<SQL
                SELECT * FROM [[customer]] WHERE [[id]] = :id
                SQL,
                ['id' => 1],
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT * FROM [[customer]] WHERE [[id]] = 1
                    SQL,
                    $db->getName(),
                ),
            ],
            [
                <<<SQL
                SELECT * FROM [[customer]] WHERE [[id]] = :id
                SQL,
                ['id' => null],
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT * FROM [[customer]] WHERE [[id]] = NULL
                    SQL,
                    $db->getName(),
                ),
            ],
            [
                <<<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,
                    $db->getName(),
                ),
            ],
            /**
             * {@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,
                    $db->getName(),
                ),
            ],
            /**
             * {@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,
                    $db->getName(),
                ),
            ],
        ];
    }

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

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

yiisoft / db

Pull Request — master (#397)

BaseCommandProvider::createIndex() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like