BaseCommandProvider::batchInsertSql() - 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

BaseCommandProvider::batchInsertSql() B

↳ Parent: BaseCommandProvider

Complexity

Conditions	1
Paths	1

Size

Total Lines	93
Code Lines	63

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	63
nc	1
nop	1
dl	0
loc	93
rs	8.8072
c	1
b	0
f	0

How to fix Long Method

<?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']],
            ['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(): array
    {
        return [
            [
                <<<SQL
                SELECT * FROM customer WHERE id = :id
                SQL,
                [':id' => 1],
                <<<SQL
                SELECT * FROM customer WHERE id = 1
                SQL,
            ],
            [
                <<<SQL
                SELECT * FROM customer WHERE id = :id
                SQL,
                ['id' => 1],
                <<<SQL
                SELECT * FROM customer WHERE id = 1
                SQL,
            ],
            [
                <<<SQL
                SELECT * FROM customer WHERE id = :id
                SQL,
                ['id' => null],
                <<<SQL
                SELECT * FROM customer WHERE id = NULL
                SQL,
            ],
            [
                <<<SQL
                SELECT * FROM customer WHERE id = :base OR id = :basePrefix
                SQL,
                ['base' => 1, 'basePrefix' => 2],
                <<<SQL
                SELECT * FROM customer WHERE id = 1 OR id = 2
                SQL,
            ],
            /**
             * {@see https://github.com/yiisoft/yii2/issues/9268}
             */
            [
                <<<SQL
                SELECT * FROM customer WHERE active = :active
                SQL,
                [':active' => false],
                <<<SQL
                SELECT * FROM customer WHERE active = FALSE
                SQL,
            ],
            /**
             * {@see https://github.com/yiisoft/yii2/issues/15122}
             */
            [
                <<<SQL
                SELECT * FROM customer WHERE id IN (:ids)
                SQL,
                [':ids' => new Expression(implode(', ', [1, 2]))],
                <<<SQL
                SELECT * FROM customer WHERE id IN (1, 2)
                SQL,
            ],
        ];
    }

    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']],
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(): array
447			{
448			return [
449			[
450			<<<SQL
451			SELECT * FROM customer WHERE id = :id
452			SQL,
453			[':id' => 1],
454			<<<SQL
455			SELECT * FROM customer WHERE id = 1
456			SQL,
457			],
458			[
459			<<<SQL
460			SELECT * FROM customer WHERE id = :id
461			SQL,
462			['id' => 1],
463			<<<SQL
464			SELECT * FROM customer WHERE id = 1
465			SQL,
466			],
467			[
468			<<<SQL
469			SELECT * FROM customer WHERE id = :id
470			SQL,
471			['id' => null],
472			<<<SQL
473			SELECT * FROM customer WHERE id = NULL
474			SQL,
475			],
476			[
477			<<<SQL
478			SELECT * FROM customer WHERE id = :base OR id = :basePrefix
479			SQL,
480			['base' => 1, 'basePrefix' => 2],
481			<<<SQL
482			SELECT * FROM customer WHERE id = 1 OR id = 2
483			SQL,
484			],
485			/**
486			* {@see https://github.com/yiisoft/yii2/issues/9268}
487			*/
488			[
489			<<<SQL
490			SELECT * FROM customer WHERE active = :active
491			SQL,
492			[':active' => false],
493			<<<SQL
494			SELECT * FROM customer WHERE active = FALSE
495			SQL,
496			],
497			/**
498			* {@see https://github.com/yiisoft/yii2/issues/15122}
499			*/
500			[
501			<<<SQL
502			SELECT * FROM customer WHERE id IN (:ids)
503			SQL,
504			[':ids' => new Expression(implode(', ', [1, 2]))],
505			<<<SQL
506			SELECT * FROM customer WHERE id IN (1, 2)
507			SQL,
508			],
509			];
510			}
511
512			public function update(ConnectionPDOInterface $db): array
513			{
514			return [
515			[
516			'table',
517			['name' => 'test'],
518			[],
519			[],
520			DbHelper::replaceQuotes(
521			<<<SQL
522			UPDATE [[table]] SET [[name]]=:qp0
523			SQL,
524			$db->getName(),
525			),
526			],
527			[
528			'table',
529			['name' => 'test'],
530			['id' => 1],
531			[],
532			DbHelper::replaceQuotes(
533			<<<SQL
534			UPDATE [[table]] SET [[name]]=:qp0 WHERE [[id]]=:qp1
535			SQL,
536			$db->getName(),
537			),
538			],
539			[
540			'table',
541			['name' => 'test'],
542			['id' => 1],
543			['id' => 'integer'],
544			DbHelper::replaceQuotes(
545			<<<SQL
546			UPDATE [[table]] SET [[name]]=:qp1 WHERE [[id]]=:qp2
547			SQL,
548			$db->getName(),
549			),
550			],
551			[
552			'table',
553			['name' => 'test'],
554			['id' => 1],
555			['id' => 'string'],
556			DbHelper::replaceQuotes(
557			<<<SQL
558			UPDATE [[table]] SET [[name]]=:qp1 WHERE [[id]]=:qp2
559			SQL,
560			$db->getName(),
561			),
562			],
563			[
564			'table',
565			['name' => 'test'],
566			['id' => 1],
567			['id' => 'boolean'],
568			DbHelper::replaceQuotes(
569			<<<SQL
570			UPDATE [[table]] SET [[name]]=:qp1 WHERE [[id]]=:qp2
571			SQL,
572			$db->getName(),
573			),
574			],
575			[
576			'table',
577			['name' => 'test'],
578			['id' => 1],
579			['id' => 'float'],
580			DbHelper::replaceQuotes(
581			<<<SQL
582			UPDATE [[table]] SET [[name]]=:qp1 WHERE [[id]]=:qp2
583			SQL,
584			$db->getName(),
585			),
586			],
587			];
588			}
589
590			public function upsert(ConnectionPDOInterface $db): array
591			{
592			return [
593			'regular values' => [
594			['params' => ['T_upsert', ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3]]],
595			['params' => ['T_upsert', ['email' => '[email protected]', 'address' => 'Universe', 'status' => 1]]],
596			],
597			'regular values with update part' => [
598			['params' => [
599			'T_upsert',
600			['email' => '[email protected]', 'address' => 'Earth', 'status' => 3],
601			['address' => 'Moon', 'status' => 2],
602			],
603			],
604			[
605			'params' => [
606			'T_upsert',
607			['email' => '[email protected]', 'address' => 'Universe', 'status' => 1],
608			['address' => 'Moon', 'status' => 2],
609			],
610			'expected' => ['email' => '[email protected]', 'address' => 'Moon', 'status' => 2],
611			],
612			],
613			'regular values without update part' => [
614			['params' => ['T_upsert', ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3], false]],
615			[
616			'params' => [
617			'T_upsert',
618			['email' => '[email protected]', 'address' => 'Universe', 'status' => 1],
619			false,
620			],
621			'expected' => ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3],
622			],
623			],
624			'query' => [
625			[
626			'params' => [
627			'T_upsert',
628			(new query($db))
629			->select(['email', 'address', 'status' => new Expression('1')])
630			->from('customer')
631			->where(['name' => 'user1'])
632			->limit(1),
633			],
634			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
635			],
636			[
637			'params' => [
638			'T_upsert',
639			(new query($db))
640			->select(['email', 'address', 'status' => new Expression('2')])
641			->from('customer')
642			->where(['name' => 'user1'])
643			->limit(1),
644			],
645			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 2],
646			],
647			],
648			'query with update part' => [
649			[
650			'params' => [
651			'T_upsert',
652			(new query($db))
653			->select(['email', 'address', 'status' => new Expression('1')])
654			->from('customer')
655			->where(['name' => 'user1'])
656			->limit(1),
657			['address' => 'Moon', 'status' => 2],
658			],
659			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
660			],
661			[
662			'params' => [
663			'T_upsert',
664			(new query($db))
665			->select(['email', 'address', 'status' => new Expression('3')])
666			->from('customer')
667			->where(['name' => 'user1'])
668			->limit(1),
669			['address' => 'Moon', 'status' => 2],
670			],
671			'expected' => ['email' => '[email protected]', 'address' => 'Moon', 'status' => 2],
672			],
673			],
674			'query without update part' => [
675			[
676			'params' => [
677			'T_upsert',
678			(new query($db))
679			->select(['email', 'address', 'status' => new Expression('1')])
680			->from('customer')
681			->where(['name' => 'user1'])
682			->limit(1),
683			false,
684			],
685			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
686			],
687			[
688			'params' => [
689			'T_upsert',
690			(new query($db))
691			->select(['email', 'address', 'status' => new Expression('2')])
692			->from('customer')
693			->where(['name' => 'user1'])
694			->limit(1),
695			false,
696			],
697			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
698			],
699			],
700			];
701			}
702			}
703

yiisoft / db

Pull Request — master (#397)

BaseCommandProvider::batchInsertSql() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like