AbstractCommandProvider::rawSql() - Code Metrics - Inspection of "Big refactoring #2." - yiisoft/db - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#372)

by Wilmer

created 2022-10-19 12:15 UTC

AbstractCommandProvider::rawSql() A

↳ Parent: AbstractCommandProvider

Complexity

Conditions	1
Paths	1

Size

Total Lines	41
Code Lines	20

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	20
c	1
b	0
f	0
nc	1
nop	0
dl	0
loc	41
rs	9.6

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Tests\Provider;

use Yiisoft\Db\Connection\ConnectionInterface;
use Yiisoft\Db\Expression\Expression;
use Yiisoft\Db\Query\Query;
use Yiisoft\Db\Tests\Support\DbHelper;

use function implode;

abstract class AbstractCommandProvider
{
    public function bindParamsNonWhere(): array
    {
        return [
            ['SELECT SUBSTR(name, :len) FROM {{customer}} WHERE [[email]] = :email GROUP BY SUBSTR(name, :len)'],
            ['SELECT SUBSTR(name, :len) FROM {{customer}} WHERE [[email]] = :email ORDER BY SUBSTR(name, :len)'],
            ['SELECT SUBSTR(name, :len) FROM {{customer}} WHERE [[email]] = :email'],
        ];
    }

    protected function getBatchInsertSql(string $driverName): 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(
                    'INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]])'
                    . ' VALUES (:qp0, :qp1, :qp2, :qp3), (:qp4, :qp5, :qp6, :qp7)',
                    $driverName,
                ),
                '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(
                    'INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]])'
                    . ' VALUES (:qp0, :qp1, :qp2, :qp3)',
                    $driverName,
                ),
                '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(
                    'INSERT INTO [[type]] ([[type]].[[int_col]], [[float_col]], [[char_col]], [[bool_col]])'
                    . ' VALUES (:qp0, :qp1, :qp2, :qp3)',
                    $driverName,
                ),
                '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(
                    'INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]])'
                    . ' VALUES (:exp1, :qp1, :qp2, :qp3)',
                    $driverName,
                ),
                'expectedParams' => [
                    ':exp1' => 42,
                    ':qp1' => 1.0,
                    ':qp2' => 'test',
                    ':qp3' => false,
                ],
            ],
        ];
    }

    public function getUpsert(ConnectionInterface $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',
                    ],
                ],
            ],
        ];
    }

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

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


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

yiisoft / db

Pull Request — master (#372)

AbstractCommandProvider::rawSql() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like