BaseCommandProvider::upsert() - Code Metrics - Inspection of "Rework tests #2, `src/QueryBuilder`." - yiisoft/db - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#380)

by Wilmer

created 2022-11-11 14:41 UTC

BaseCommandProvider::upsert() B

↳ Parent: BaseCommandProvider

Complexity

Conditions	1
Paths	1

Size

Total Lines	108
Code Lines	76

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	76
nc	1
nop	1
dl	0
loc	108
rs	8.5236
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\Tests\Support\DbHelper;

final class BaseCommandProvider
{
    public function batchInsertSql(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 bindParamsNonWhere(): array
    {
        return [
            [
                <<<SQL
                SELECT SUBSTR(name, :len) FROM {{customer}} WHERE [[email]] = :email GROUP BY SUBSTR(name, :len)
                SQL,
            ],
            [
                <<<SQL
                SELECT SUBSTR(name, :len) FROM {{customer}} WHERE [[email]] = :email ORDER BY SUBSTR(name, :len)
                SQL,
            ],
            [
                <<<SQL
                SELECT SUBSTR(name, :len) FROM {{customer}} WHERE [[email]] = :email
                SQL,
            ],
        ];
    }

    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 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\Tests\Support\DbHelper;
11
12			final class BaseCommandProvider
13			{
14			public function batchInsertSql(ConnectionPDOInterface $db): array
15			{
16			return [
17			'multirow' => [
18			'type',
19			['int_col', 'float_col', 'char_col', 'bool_col'],
20			'values' => [
21			['0', '0.0', 'test string', true],
22			[false, 0, 'test string2', false],
23			],
24			'expected' => DbHelper::replaceQuotes(
25			<<<SQL
26			INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp0, :qp1, :qp2, :qp3), (:qp4, :qp5, :qp6, :qp7)
27			SQL,
28			$db->getName(),
29			),
30			'expectedParams' => [
31			':qp0' => 0,
32			':qp1' => 0.0,
33			':qp2' => 'test string',
34			':qp3' => true,
35			':qp4' => 0,
36			':qp5' => 0.0,
37			':qp6' => 'test string2',
38			':qp7' => false,
39			],
40			2,
41			],
42			'issue11242' => [
43			'type',
44			['int_col', 'float_col', 'char_col', 'bool_col'],
45			'values' => [[1.0, 1.1, 'Kyiv {{city}}, Ukraine', true]],
46			/**
47			* {@see https://github.com/yiisoft/yii2/issues/11242}
48			*
49			* Make sure curly bracelets (`{{..}}`) in values will not be escaped
50			*/
51			'expected' => DbHelper::replaceQuotes(
52			<<<SQL
53			INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp0, :qp1, :qp2, :qp3)
54			SQL,
55			$db->getName(),
56			),
57			'expectedParams' => [
58			':qp0' => 1,
59			':qp1' => 1.1,
60			':qp2' => 'Kyiv {{city}}, Ukraine',
61			':qp3' => true,
62			],
63			],
64			'wrongBehavior' => [
65			'{{%type}}',
66			['{{%type}}.[[int_col]]', '[[float_col]]', 'char_col', 'bool_col'],
67			'values' => [['0', '0.0', 'Kyiv {{city}}, Ukraine', false]],
68			/**
69			* Test covers potentially wrong behavior and marks it as expected!.
70			*
71			* In case table name or table column is passed with curly or square bracelets, QueryBuilder can not
72			* determine the table schema and typecast values properly.
73			* TODO: make it work. Impossible without BC breaking for public methods.
74			*/
75			'expected' => DbHelper::replaceQuotes(
76			<<<SQL
77			INSERT INTO [[type]] ([[type]].[[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp0, :qp1, :qp2, :qp3)
78			SQL,
79			$db->getName(),
80			),
81			'expectedParams' => [
82			':qp0' => '0',
83			':qp1' => '0.0',
84			':qp2' => 'Kyiv {{city}}, Ukraine',
85			':qp3' => false,
86			],
87			],
88			'batchInsert binds params from expression' => [
89			'{{%type}}',
90			['int_col', 'float_col', 'char_col', 'bool_col'],
91			/**
92			* This example is completely useless. This feature of batchInsert is intended to be used with complex
93			* expression objects, such as JsonExpression.
94			*/
95			'values' => [[new Expression(':exp1', [':exp1' => 42]), 1, 'test', false]],
96			'expected' => DbHelper::replaceQuotes(
97			<<<SQL
98			INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:exp1, :qp1, :qp2, :qp3)
99			SQL,
100			$db->getName(),
101			),
102			'expectedParams' => [
103			':exp1' => 42,
104			':qp1' => 1.0,
105			':qp2' => 'test',
106			':qp3' => false,
107			],
108			],
109			];
110			}
111
112			public function bindParamsNonWhere(): array
113			{
114			return [
115			[
116			<<<SQL
117			SELECT SUBSTR(name, :len) FROM {{customer}} WHERE [[email]] = :email GROUP BY SUBSTR(name, :len)
118			SQL,
119			],
120			[
121			<<<SQL
122			SELECT SUBSTR(name, :len) FROM {{customer}} WHERE [[email]] = :email ORDER BY SUBSTR(name, :len)
123			SQL,
124			],
125			[
126			<<<SQL
127			SELECT SUBSTR(name, :len) FROM {{customer}} WHERE [[email]] = :email
128			SQL,
129			],
130			];
131			}
132
133			public function invalidSelectColumns(): array
134			{
135			return [[[]], [''], [['']]];
136			}
137
138			public function rawSql(): array
139			{
140			return [
141			[
142			<<<SQL
143			SELECT * FROM customer WHERE id = :id
144			SQL,
145			[':id' => 1],
146			<<<SQL
147			SELECT * FROM customer WHERE id = 1
148			SQL,
149			],
150			[
151			<<<SQL
152			SELECT * FROM customer WHERE id = :id
153			SQL,
154			['id' => 1],
155			<<<SQL
156			SELECT * FROM customer WHERE id = 1
157			SQL,
158			],
159			[
160			<<<SQL
161			SELECT * FROM customer WHERE id = :id
162			SQL,
163			['id' => null],
164			<<<SQL
165			SELECT * FROM customer WHERE id = NULL
166			SQL,
167			],
168			[
169			<<<SQL
170			SELECT * FROM customer WHERE id = :base OR id = :basePrefix
171			SQL,
172			['base' => 1, 'basePrefix' => 2],
173			<<<SQL
174			SELECT * FROM customer WHERE id = 1 OR id = 2
175			SQL,
176			],
177			/**
178			* {@see https://github.com/yiisoft/yii2/issues/9268}
179			*/
180			[
181			<<<SQL
182			SELECT * FROM customer WHERE active = :active
183			SQL,
184			[':active' => false],
185			<<<SQL
186			SELECT * FROM customer WHERE active = FALSE
187			SQL,
188			],
189			/**
190			* {@see https://github.com/yiisoft/yii2/issues/15122}
191			*/
192			[
193			<<<SQL
194			SELECT * FROM customer WHERE id IN (:ids)
195			SQL,
196			[':ids' => new Expression(implode(', ', [1, 2]))],
197			<<<SQL
198			SELECT * FROM customer WHERE id IN (1, 2)
199			SQL,
200			],
201			];
202			}
203
204			public function upsert(ConnectionPDOInterface $db): array
205			{
206			return [
207			'regular values' => [
208			['params' => ['T_upsert', ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3]]],
209			['params' => ['T_upsert', ['email' => '[email protected]', 'address' => 'Universe', 'status' => 1]]],
210			],
211			'regular values with update part' => [
212			['params' => [
213			'T_upsert',
214			['email' => '[email protected]', 'address' => 'Earth', 'status' => 3],
215			['address' => 'Moon', 'status' => 2],
216			],
217			],
218			[
219			'params' => [
220			'T_upsert',
221			['email' => '[email protected]', 'address' => 'Universe', 'status' => 1],
222			['address' => 'Moon', 'status' => 2],
223			],
224			'expected' => ['email' => '[email protected]', 'address' => 'Moon', 'status' => 2],
225			],
226			],
227			'regular values without update part' => [
228			['params' => ['T_upsert', ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3], false]],
229			[
230			'params' => [
231			'T_upsert',
232			['email' => '[email protected]', 'address' => 'Universe', 'status' => 1],
233			false,
234			],
235			'expected' => ['email' => '[email protected]', 'address' => 'Earth', 'status' => 3],
236			],
237			],
238			'query' => [
239			[
240			'params' => [
241			'T_upsert',
242			(new query($db))
243			->select(['email', 'address', 'status' => new Expression('1')])
244			->from('customer')
245			->where(['name' => 'user1'])
246			->limit(1),
247			],
248			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
249			],
250			[
251			'params' => [
252			'T_upsert',
253			(new query($db))
254			->select(['email', 'address', 'status' => new Expression('2')])
255			->from('customer')
256			->where(['name' => 'user1'])
257			->limit(1),
258			],
259			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 2],
260			],
261			],
262			'query with update part' => [
263			[
264			'params' => [
265			'T_upsert',
266			(new query($db))
267			->select(['email', 'address', 'status' => new Expression('1')])
268			->from('customer')
269			->where(['name' => 'user1'])
270			->limit(1),
271			['address' => 'Moon', 'status' => 2],
272			],
273			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
274			],
275			[
276			'params' => [
277			'T_upsert',
278			(new query($db))
279			->select(['email', 'address', 'status' => new Expression('3')])
280			->from('customer')
281			->where(['name' => 'user1'])
282			->limit(1),
283			['address' => 'Moon', 'status' => 2],
284			],
285			'expected' => ['email' => '[email protected]', 'address' => 'Moon', 'status' => 2],
286			],
287			],
288			'query without update part' => [
289			[
290			'params' => [
291			'T_upsert',
292			(new query($db))
293			->select(['email', 'address', 'status' => new Expression('1')])
294			->from('customer')
295			->where(['name' => 'user1'])
296			->limit(1),
297			false,
298			],
299			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
300			],
301			[
302			'params' => [
303			'T_upsert',
304			(new query($db))
305			->select(['email', 'address', 'status' => new Expression('2')])
306			->from('customer')
307			->where(['name' => 'user1'])
308			->limit(1),
309			false,
310			],
311			'expected' => ['email' => '[email protected]', 'address' => 'address1', 'status' => 1],
312			],
313			],
314			];
315			}
316			}
317

yiisoft / db

Pull Request — master (#380)

BaseCommandProvider::upsert() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like