BaseQueryBuilderProvider::buildConditions() - 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-03 00:41 UTC

BaseQueryBuilderProvider::buildConditions() B

↳ Parent: BaseQueryBuilderProvider

Complexity

Conditions	1
Paths	1

Size

Total Lines	309
Code Lines	176

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	176
c	1
b	0
f	0
nc	1
nop	0
dl	0
loc	309
rs	8

How to fix Long Method

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Tests\Provider;

use Closure;
use Yiisoft\Db\Expression\Expression;
use Yiisoft\Db\QueryBuilder\Conditions\BetweenColumnsCondition;
use Yiisoft\Db\QueryBuilder\Conditions\InCondition;
use Yiisoft\Db\QueryBuilder\QueryBuilder;
use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
use Yiisoft\Db\Tests\Support\DbHelper;
use Yiisoft\Db\Tests\Support\Mock;
use Yiisoft\Db\Tests\Support\TraversableObject;

final class BaseQueryBuilderProvider
{
    public function __construct(private Mock $mock)
    {
    }

    public function batchInsert(): array
    {
        return [
            'simple' => [
                'customer',
                ['email', 'name', 'address'],
                [['[email protected]', 'silverfire', 'Kyiv {{city}}, Ukraine']],
                'expected' => DbHelper::replaceQuotes(
                    <<<SQL
                    INSERT INTO [[customer]] ([[email]], [[name]], [[address]]) VALUES (:qp0, :qp1, :qp2)
                    SQL,
                    $this->mock->getDriverName(),
                ),
                [
                    ':qp0' => '[email protected]',
                    ':qp1' => 'silverfire',
                    ':qp2' => 'Kyiv {{city}}, Ukraine',
                ],
            ],
            'escape-danger-chars' => [
                'customer',
                ['address'],
                [["SQL-danger chars are escaped: '); --"]],
                'expected' => DbHelper::replaceQuotes(
                    <<<SQL
                    INSERT INTO [[customer]] ([[address]]) VALUES (:qp0)
                    SQL,
                    $this->mock->getDriverName(),
                ),
                [
                    ':qp0' => "SQL-danger chars are escaped: '); --",
                ],
            ],
            'customer2' => [
                'customer',
                ['address'],
                [],
                '',
            ],
            'customer3' => [
                'customer',
                [],
                [['no columns passed']],
                'expected' => DbHelper::replaceQuotes(
                    <<<SQL
                    INSERT INTO [[customer]] () VALUES (:qp0)
                    SQL,
                    $this->mock->getDriverName(),
                ),
                [
                    ':qp0' => 'no columns passed',
                ],
            ],
            'bool-false, bool2-null' => [
                'type',
                ['bool_col', 'bool_col2'],
                [[false, null]],
                'expected' => DbHelper::replaceQuotes(
                    <<<SQL
                    INSERT INTO [[type]] ([[bool_col]], [[bool_col2]]) VALUES (:qp0, :qp1)
                    SQL,
                    $this->mock->getDriverName(),
                ),
                [
                    ':qp0' => false,
                    ':qp1' => null,
                ],
            ],
            'wrong' => [
                '{{%type}}',
                ['{{%type}}.[[float_col]]', '[[time]]'],
                [[null, new Expression('now()')], [null, new Expression('now()')]],
                'expected' => 'INSERT INTO {{%type}} ({{%type}}.[[float_col]], [[time]]) VALUES (:qp0, now()), (:qp1, now())',
                [
                    ':qp0' => null,
                    ':qp1' => null,
                ],
            ],
            'bool-false, time-now()' => [
                '{{%type}}',
                ['{{%type}}.[[bool_col]]', '[[time]]'],
                [[false, new Expression('now()')]],
                'expected' => 'INSERT INTO {{%type}} ({{%type}}.[[bool_col]], [[time]]) VALUES (:qp0, now())',
                [
                    ':qp0' => false,
                ],
            ],
        ];
    }

    public function buildConditions(): array
    {
        $conditions = [
            /* empty values */
            [['like', 'name', []], '0=1', []],
            [['not like', 'name', []], '', []],
            [['or like', 'name', []], '0=1', []],
            [['or not like', 'name', []], '', []],

            /* not */
            [['not', ''], '', []],
            [['not', 'name'], 'NOT (name)', []],
            [
                [
                    'not',
                    $this->mock->query()->select('exists')->from('some_table'),
                ],
                'NOT ((SELECT [[exists]] FROM [[some_table]]))', [],
            ],

            /* and */
            [['and', '', ''], '', []],
            [['and', '', 'id=2'], 'id=2', []],
            [['and', 'id=1', 'id=2'], '(id=1) AND (id=2)', []],
            [['and', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) AND ((id=1) OR (id=2))', []],
            [['and', 'id=1', new Expression('id=:qp0', [':qp0' => 2])], '(id=1) AND (id=:qp0)', [':qp0' => 2]],
            [
                [
                    'and',
                    ['expired' => false],
                    $this->mock->query()->select('count(*) > 1')->from('queue'),
                ],
                '([[expired]]=:qp0) AND ((SELECT count(*) > 1 FROM [[queue]]))',
                [':qp0' => false],
            ],

            /* or */
            [['or', 'id=1', 'id=2'], '(id=1) OR (id=2)', []],
            [['or', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) OR ((id=1) OR (id=2))', []],
            [['or', 'type=1', new Expression('id=:qp0', [':qp0' => 1])], '(type=1) OR (id=:qp0)', [':qp0' => 1]],

            /* between */
            [['between', 'id', 1, 10], '[[id]] BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]],
            [['not between', 'id', 1, 10], '[[id]] NOT BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]],
            [
                ['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')],
                '[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()',
                [],
            ],
            [
                ['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123],
                '[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0',
                [':qp0' => 123],
            ],
            [
                ['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')],
                '[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()',
                [],
            ],
            [
                ['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123],
                '[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0',
                [':qp0' => 123],
            ],
            [
                new BetweenColumnsCondition('2018-02-11', 'BETWEEN', 'create_time', 'update_time'),
                ':qp0 BETWEEN [[create_time]] AND [[update_time]]',
                [':qp0' => '2018-02-11'],
            ],
            [
                new BetweenColumnsCondition('2018-02-11', 'NOT BETWEEN', 'NOW()', 'update_time'),
                ':qp0 NOT BETWEEN NOW() AND [[update_time]]',
                [':qp0' => '2018-02-11'],
            ],
            [
                new BetweenColumnsCondition(new Expression('NOW()'), 'BETWEEN', 'create_time', 'update_time'),
                'NOW() BETWEEN [[create_time]] AND [[update_time]]',
                [],
            ],
            [
                new BetweenColumnsCondition(new Expression('NOW()'), 'NOT BETWEEN', 'create_time', 'update_time'),
                'NOW() NOT BETWEEN [[create_time]] AND [[update_time]]',
                [],
            ],
            [
                new BetweenColumnsCondition(
                    new Expression('NOW()'),
                    'NOT BETWEEN',
                    $this->mock->query()->select('min_date')->from('some_table'),
                    'max_date'
                ),
                'NOW() NOT BETWEEN (SELECT [[min_date]] FROM [[some_table]]) AND [[max_date]]',
                [],
            ],
            [
                new BetweenColumnsCondition(
                    new Expression('NOW()'),
                    'NOT BETWEEN',
                    new Expression('min_date'),
                    $this->mock->query()->select('max_date')->from('some_table'),
                ),
                'NOW() NOT BETWEEN min_date AND (SELECT [[max_date]] FROM [[some_table]])',
                [],
            ],

            /* in */
            [
                ['in', 'id', [1, 2, $this->mock->query()->select('three')->from('digits')]],
                '[[id]] IN (:qp0, :qp1, (SELECT [[three]] FROM [[digits]]))',
                [':qp0' => 1, ':qp1' => 2],
            ],
            [
                ['not in', 'id', [1, 2, 3]],
                '[[id]] NOT IN (:qp0, :qp1, :qp2)',
                [':qp0' => 1, ':qp1' => 2, ':qp2' => 3],
            ],
            [
                ['in', 'id', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
                '[[id]] IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
                [':qp0' => 1],
            ],
            [
                ['not in', 'id', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
                '[[id]] NOT IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
                [':qp0' => 1],
            ],
            [['in', 'id', 1], '[[id]]=:qp0', [':qp0' => 1]],
            [['in', 'id', [1]], '[[id]]=:qp0', [':qp0' => 1]],
            [['in', 'id', new TraversableObject([1])], '[[id]]=:qp0', [':qp0' => 1]],
            'composite in' => [
                ['in', ['id', 'name'], [['id' => 1, 'name' => 'oy']]],
                '([[id]], [[name]]) IN ((:qp0, :qp1))',
                [':qp0' => 1, ':qp1' => 'oy'],
            ],
            'composite in (just one column)' => [
                ['in', ['id'], [['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]],
                '[[id]] IN (:qp0, :qp1)',
                [':qp0' => 1, ':qp1' => 2],
            ],
            'composite in using array objects (just one column)' => [
                [
                    'in',
                    new TraversableObject(['id']),
                    new TraversableObject([['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]),
                ],
                '[[id]] IN (:qp0, :qp1)',
                [':qp0' => 1, ':qp1' => 2],
            ],

            /* in using array objects. */
            [['id' => new TraversableObject([1, 2])], '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
            [
                ['in', 'id', new TraversableObject([1, 2, 3])],
                '[[id]] IN (:qp0, :qp1, :qp2)',
                [':qp0' => 1, ':qp1' => 2, ':qp2' => 3],
            ],

            /* in using array objects containing null value */
            [['in', 'id', new TraversableObject([1, null])], '[[id]]=:qp0 OR [[id]] IS NULL', [':qp0' => 1]],
            [
                ['in', 'id', new TraversableObject([1, 2, null])],
                '[[id]] IN (:qp0, :qp1) OR [[id]] IS NULL', [':qp0' => 1, ':qp1' => 2],
            ],

            /* not in using array object containing null value */
            [
                ['not in', 'id', new TraversableObject([1, null])],
                '[[id]]<>:qp0 AND [[id]] IS NOT NULL', [':qp0' => 1],
            ],
            [
                ['not in', 'id', new TraversableObject([1, 2, null])],
                '[[id]] NOT IN (:qp0, :qp1) AND [[id]] IS NOT NULL',
                [':qp0' => 1, ':qp1' => 2],
            ],

            /* in using array object containing only null value */
            [['in', 'id', new TraversableObject([null])], '[[id]] IS NULL', []],
            [['not in', 'id', new TraversableObject([null])], '[[id]] IS NOT NULL', []],
            'composite in using array objects' => [
                [
                    'in',
                    new TraversableObject(['id', 'name']),
                    new TraversableObject([['id' => 1, 'name' => 'oy'], ['id' => 2, 'name' => 'yo']]),
                ],
                '([[id]], [[name]]) IN ((:qp0, :qp1), (:qp2, :qp3))',
                [':qp0' => 1, ':qp1' => 'oy', ':qp2' => 2, ':qp3' => 'yo'],
            ],

            /* in object conditions */
            [new InCondition('id', 'in', 1), '[[id]]=:qp0', [':qp0' => 1]],
            [new InCondition('id', 'in', [1]), '[[id]]=:qp0', [':qp0' => 1]],
            [new InCondition('id', 'not in', 1), '[[id]]<>:qp0', [':qp0' => 1]],
            [new InCondition('id', 'not in', [1]), '[[id]]<>:qp0', [':qp0' => 1]],
            [new InCondition('id', 'in', [1, 2]), '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
            [new InCondition('id', 'not in', [1, 2]), '[[id]] NOT IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
            [new InCondition([], 'in', 1), '0=1', []],
            [new InCondition([], 'in', [1]), '0=1', []],
            [new InCondition(['id', 'name'], 'in', []), '0=1', []],
            [
                new InCondition(
                    ['id'],
                    'in',
                    $this->mock->query()->select('id')->from('users')->where(['active' => 1]),
                ),
                '([[id]]) IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
                [':qp0' => 1],
            ],
            [
                new InCondition(['id', 'name'], 'in', [['id' => 1]]),
                '([[id]], [[name]]) IN ((:qp0, NULL))',
                [':qp0' => 1],
            ],
            [
                new InCondition(['id', 'name'], 'in', [['name' => 'oy']]),
                '([[id]], [[name]]) IN ((NULL, :qp0))',
                [':qp0' => 'oy'],
            ],
            [
                new InCondition(['id', 'name'], 'in', [['id' => 1, 'name' => 'oy']]),
                '([[id]], [[name]]) IN ((:qp0, :qp1))',
                [':qp0' => 1, ':qp1' => 'oy'],
            ],

            /* exists */
            [
                [
                    'exists',
                    $this->mock->query()->select('id')->from('users')->where(['active' => 1]),
                ],
                'EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
                [':qp0' => 1],
            ],
            [
                ['not exists', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
                'NOT EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', [':qp0' => 1],
            ],

            /* simple conditions */
            [['=', 'a', 'b'], '[[a]] = :qp0', [':qp0' => 'b']],
            [['>', 'a', 1], '[[a]] > :qp0', [':qp0' => 1]],
            [['>=', 'a', 'b'], '[[a]] >= :qp0', [':qp0' => 'b']],
            [['<', 'a', 2], '[[a]] < :qp0', [':qp0' => 2]],
            [['<=', 'a', 'b'], '[[a]] <= :qp0', [':qp0' => 'b']],
            [['<>', 'a', 3], '[[a]] <> :qp0', [':qp0' => 3]],
            [['!=', 'a', 'b'], '[[a]] != :qp0', [':qp0' => 'b']],
            [
                ['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL 1 MONTH)')],
                '[[date]] >= DATE_SUB(NOW(), INTERVAL 1 MONTH)',
                [],
            ],
            [
                ['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL :month MONTH)', [':month' => 2])],
                '[[date]] >= DATE_SUB(NOW(), INTERVAL :month MONTH)',
                [':month' => 2],
            ],
            [
                ['=', 'date', $this->mock->query()->select('max(date)')->from('test')->where(['id' => 5])],
                '[[date]] = (SELECT max(date) FROM [[test]] WHERE [[id]]=:qp0)',
                [':qp0' => 5],
            ],
            [['=', 'a', null], '[[a]] = NULL', []],

            /* operand1 is Expression */
            [
                ['=', new Expression('date'), '2019-08-01'],
                'date = :qp0',
                [':qp0' => '2019-08-01'],
            ],
            [
                ['=', $this->mock->query()->select('COUNT(*)')->from('test')->where(['id' => 6]), 0],
                '(SELECT COUNT(*) FROM [[test]] WHERE [[id]]=:qp0) = :qp1',
                [':qp0' => 6, ':qp1' => 0],
            ],

            /* hash condition */
            [['a' => 1, 'b' => 2], '([[a]]=:qp0) AND ([[b]]=:qp1)', [':qp0' => 1, ':qp1' => 2]],
            [
                ['a' => new Expression('CONCAT(col1, col2)'), 'b' => 2],
                '([[a]]=CONCAT(col1, col2)) AND ([[b]]=:qp0)',
                [':qp0' => 2],
            ],
            [['a' => null], '[[a]] IS NULL', []],

            /* direct conditions */
            ['a = CONCAT(col1, col2)', 'a = CONCAT(col1, col2)', []],
            [
                new Expression('a = CONCAT(col1, :param1)', ['param1' => 'value1']),
                'a = CONCAT(col1, :param1)',
                ['param1' => 'value1'],
            ],

            /* Expression with params as operand of 'not' */
            [
                ['not', new Expression('any_expression(:a)', [':a' => 1])],
                'NOT (any_expression(:a))', [':a' => 1],
            ],
            [new Expression('NOT (any_expression(:a))', [':a' => 1]), 'NOT (any_expression(:a))', [':a' => 1]],

            /* like */
            [['like', 'a', 'b'], '[[a]] LIKE :qp0', [':qp0' => '%b%']],
            [
                ['like', 'a', new Expression(':qp0', [':qp0' => '%b%'])],
                '[[a]] LIKE :qp0',
                [':qp0' => '%b%'],
            ],
            [['like', new Expression('CONCAT(col1, col2)'), 'b'], 'CONCAT(col1, col2) LIKE :qp0', [':qp0' => '%b%']],
        ];

        return $this->replaceQuotes($conditions);
    }

    public function buildFilterCondition(): array
    {
        $conditions = [
            /* like */
            [['like', 'name', []], '', []],
            [['not like', 'name', []], '', []],
            [['or like', 'name', []], '', []],
            [['or not like', 'name', []], '', []],

            /* not */
            [['not', ''], '', []],

            /* and */
            [['and', '', ''], '', []],
            [['and', '', 'id=2'], 'id=2', []],
            [['and', 'id=1', ''], 'id=1', []],
            [['and', 'type=1', ['or', '', 'id=2']], '(type=1) AND (id=2)', []],

            /* or */
            [['or', 'id=1', ''], 'id=1', []],
            [['or', 'type=1', ['or', '', 'id=2']], '(type=1) OR (id=2)', []],

            /* between */
            [['between', 'id', 1, null], '', []],
            [['not between', 'id', null, 10], '', []],

            /* in */
            [['in', 'id', []], '', []],
            [['not in', 'id', []], '', []],

            /* simple conditions */
            [['=', 'a', ''], '', []],
            [['>', 'a', ''], '', []],
            [['>=', 'a', ''], '', []],
            [['<', 'a', ''], '', []],
            [['<=', 'a', ''], '', []],
            [['<>', 'a', ''], '', []],
            [['!=', 'a', ''], '', []],
        ];

        return $this->replaceQuotes($conditions);
    }

    public function buildFrom(): array
    {
        return [
            ['test t1', '[[test]] [[t1]]'],
            ['test as t1', '[[test]] [[t1]]'],
            ['test AS t1', '[[test]] [[t1]]'],
            ['test', '[[test]]'],
        ];
    }

    public function buildWhereExists(): array
    {
        return [
            [
                'exists',
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE EXISTS (SELECT [[1]] FROM [[Website]] [[w]])
                    SQL,
                    $this->mock->getDriverName(),
                ),
            ],
            [
                'not exists',
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE NOT EXISTS (SELECT [[1]] FROM [[Website]] [[w]])
                    SQL,
                    $this->mock->getDriverName(),
                ),
            ],
        ];
    }

    public function createDropIndex(): array
    {
        $tableName = 'T_constraints_2';
        $name1 = 'CN_constraints_2_single';
        $name2 = 'CN_constraints_2_multi';

        return [
            'drop' => [
                <<<SQL
                DROP INDEX [[$name1]] ON {{{$tableName}}}
                SQL,
                static fn (QueryBuilderInterface $qb) => $qb->dropIndex($name1, $tableName),
            ],
            'create' => [
                <<<SQL
                CREATE INDEX [[$name1]] ON {{{$tableName}}} ([[C_index_1]])
                SQL,
                static fn (QueryBuilderInterface $qb) => $qb->createIndex($name1, $tableName, 'C_index_1'),
            ],
            'create (2 columns)' => [
                <<<SQL
                CREATE INDEX [[$name2]] ON {{{$tableName}}} ([[C_index_2_1]], [[C_index_2_2]])
                SQL,
                static fn (QueryBuilderInterface $qb) => $qb->createIndex(
                    $name2,
                    $tableName,
                    'C_index_2_1,
                    C_index_2_2',
                ),
            ],
            'create unique' => [
                <<<SQL
                CREATE UNIQUE INDEX [[$name1]] ON {{{$tableName}}} ([[C_index_1]])
                SQL,
                static fn (QueryBuilderInterface $qb) => $qb->createIndex(
                    $name1,
                    $tableName,
                    'C_index_1',
                    QueryBuilder::INDEX_UNIQUE,
                ),
            ],
            'create unique (2 columns)' => [
                <<<SQL
                CREATE UNIQUE INDEX [[$name2]] ON {{{$tableName}}} ([[C_index_2_1]], [[C_index_2_2]])
                SQL,
                static fn (QueryBuilderInterface $qb) => $qb->createIndex(
                    $name2,
                    $tableName,
                    'C_index_2_1,
                    C_index_2_2',
                    QueryBuilder::INDEX_UNIQUE,
                ),
            ],
        ];
    }

    private function replaceQuotes(array $conditions): array
    {
        /* adjust dbms specific escaping */
        foreach ($conditions as $i => $condition) {
            $conditions[$i][1] = DbHelper::replaceQuotes($condition[1], $this->mock->getDriverName());
        }

        return $conditions;
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace Yiisoft\Db\Tests\Provider;
6
7			use Closure;
8			use Yiisoft\Db\Expression\Expression;
9			use Yiisoft\Db\QueryBuilder\Conditions\BetweenColumnsCondition;
10			use Yiisoft\Db\QueryBuilder\Conditions\InCondition;
11			use Yiisoft\Db\QueryBuilder\QueryBuilder;
12			use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
13			use Yiisoft\Db\Tests\Support\DbHelper;
14			use Yiisoft\Db\Tests\Support\Mock;
15			use Yiisoft\Db\Tests\Support\TraversableObject;
16
17			final class BaseQueryBuilderProvider
18			{
19			public function __construct(private Mock $mock)
20			{
21			}
22
23			public function batchInsert(): array
24			{
25			return [
26			'simple' => [
27			'customer',
28			['email', 'name', 'address'],
29			[['[email protected]', 'silverfire', 'Kyiv {{city}}, Ukraine']],
30			'expected' => DbHelper::replaceQuotes(
31			<<<SQL
32			INSERT INTO [[customer]] ([[email]], [[name]], [[address]]) VALUES (:qp0, :qp1, :qp2)
33			SQL,
34			$this->mock->getDriverName(),
35			),
36			[
37			':qp0' => '[email protected]',
38			':qp1' => 'silverfire',
39			':qp2' => 'Kyiv {{city}}, Ukraine',
40			],
41			],
42			'escape-danger-chars' => [
43			'customer',
44			['address'],
45			[["SQL-danger chars are escaped: '); --"]],
46			'expected' => DbHelper::replaceQuotes(
47			<<<SQL
48			INSERT INTO [[customer]] ([[address]]) VALUES (:qp0)
49			SQL,
50			$this->mock->getDriverName(),
51			),
52			[
53			':qp0' => "SQL-danger chars are escaped: '); --",
54			],
55			],
56			'customer2' => [
57			'customer',
58			['address'],
59			[],
60			'',
61			],
62			'customer3' => [
63			'customer',
64			[],
65			[['no columns passed']],
66			'expected' => DbHelper::replaceQuotes(
67			<<<SQL
68			INSERT INTO [[customer]] () VALUES (:qp0)
69			SQL,
70			$this->mock->getDriverName(),
71			),
72			[
73			':qp0' => 'no columns passed',
74			],
75			],
76			'bool-false, bool2-null' => [
77			'type',
78			['bool_col', 'bool_col2'],
79			[[false, null]],
80			'expected' => DbHelper::replaceQuotes(
81			<<<SQL
82			INSERT INTO [[type]] ([[bool_col]], [[bool_col2]]) VALUES (:qp0, :qp1)
83			SQL,
84			$this->mock->getDriverName(),
85			),
86			[
87			':qp0' => false,
88			':qp1' => null,
89			],
90			],
91			'wrong' => [
92			'{{%type}}',
93			['{{%type}}.[[float_col]]', '[[time]]'],
94			[[null, new Expression('now()')], [null, new Expression('now()')]],
95			'expected' => 'INSERT INTO {{%type}} ({{%type}}.[[float_col]], [[time]]) VALUES (:qp0, now()), (:qp1, now())',
96			[
97			':qp0' => null,
98			':qp1' => null,
99			],
100			],
101			'bool-false, time-now()' => [
102			'{{%type}}',
103			['{{%type}}.[[bool_col]]', '[[time]]'],
104			[[false, new Expression('now()')]],
105			'expected' => 'INSERT INTO {{%type}} ({{%type}}.[[bool_col]], [[time]]) VALUES (:qp0, now())',
106			[
107			':qp0' => false,
108			],
109			],
110			];
111			}
112
113			public function buildConditions(): array
114			{
115			$conditions = [
116			/* empty values */
117			[['like', 'name', []], '0=1', []],
118			[['not like', 'name', []], '', []],
119			[['or like', 'name', []], '0=1', []],
120			[['or not like', 'name', []], '', []],
121
122			/* not */
123			[['not', ''], '', []],
124			[['not', 'name'], 'NOT (name)', []],
125			[
126			[
127			'not',
128			$this->mock->query()->select('exists')->from('some_table'),
129			],
130			'NOT ((SELECT [[exists]] FROM [[some_table]]))', [],
131			],
132
133			/* and */
134			[['and', '', ''], '', []],
135			[['and', '', 'id=2'], 'id=2', []],
136			[['and', 'id=1', 'id=2'], '(id=1) AND (id=2)', []],
137			[['and', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) AND ((id=1) OR (id=2))', []],
138			[['and', 'id=1', new Expression('id=:qp0', [':qp0' => 2])], '(id=1) AND (id=:qp0)', [':qp0' => 2]],
139			[
140			[
141			'and',
142			['expired' => false],
143			$this->mock->query()->select('count(*) > 1')->from('queue'),
144			],
145			'([[expired]]=:qp0) AND ((SELECT count(*) > 1 FROM [[queue]]))',
146			[':qp0' => false],
147			],
148
149			/* or */
150			[['or', 'id=1', 'id=2'], '(id=1) OR (id=2)', []],
151			[['or', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) OR ((id=1) OR (id=2))', []],
152			[['or', 'type=1', new Expression('id=:qp0', [':qp0' => 1])], '(type=1) OR (id=:qp0)', [':qp0' => 1]],
153
154			/* between */
155			[['between', 'id', 1, 10], '[[id]] BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]],
156			[['not between', 'id', 1, 10], '[[id]] NOT BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]],
157			[
158			['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')],
159			'[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()',
160			[],
161			],
162			[
163			['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123],
164			'[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0',
165			[':qp0' => 123],
166			],
167			[
168			['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')],
169			'[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()',
170			[],
171			],
172			[
173			['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123],
174			'[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0',
175			[':qp0' => 123],
176			],
177			[
178			new BetweenColumnsCondition('2018-02-11', 'BETWEEN', 'create_time', 'update_time'),
179			':qp0 BETWEEN [[create_time]] AND [[update_time]]',
180			[':qp0' => '2018-02-11'],
181			],
182			[
183			new BetweenColumnsCondition('2018-02-11', 'NOT BETWEEN', 'NOW()', 'update_time'),
184			':qp0 NOT BETWEEN NOW() AND [[update_time]]',
185			[':qp0' => '2018-02-11'],
186			],
187			[
188			new BetweenColumnsCondition(new Expression('NOW()'), 'BETWEEN', 'create_time', 'update_time'),
189			'NOW() BETWEEN [[create_time]] AND [[update_time]]',
190			[],
191			],
192			[
193			new BetweenColumnsCondition(new Expression('NOW()'), 'NOT BETWEEN', 'create_time', 'update_time'),
194			'NOW() NOT BETWEEN [[create_time]] AND [[update_time]]',
195			[],
196			],
197			[
198			new BetweenColumnsCondition(
199			new Expression('NOW()'),
200			'NOT BETWEEN',
201			$this->mock->query()->select('min_date')->from('some_table'),
202			'max_date'
203			),
204			'NOW() NOT BETWEEN (SELECT [[min_date]] FROM [[some_table]]) AND [[max_date]]',
205			[],
206			],
207			[
208			new BetweenColumnsCondition(
209			new Expression('NOW()'),
210			'NOT BETWEEN',
211			new Expression('min_date'),
212			$this->mock->query()->select('max_date')->from('some_table'),
213			),
214			'NOW() NOT BETWEEN min_date AND (SELECT [[max_date]] FROM [[some_table]])',
215			[],
216			],
217
218			/* in */
219			[
220			['in', 'id', [1, 2, $this->mock->query()->select('three')->from('digits')]],
221			'[[id]] IN (:qp0, :qp1, (SELECT [[three]] FROM [[digits]]))',
222			[':qp0' => 1, ':qp1' => 2],
223			],
224			[
225			['not in', 'id', [1, 2, 3]],
226			'[[id]] NOT IN (:qp0, :qp1, :qp2)',
227			[':qp0' => 1, ':qp1' => 2, ':qp2' => 3],
228			],
229			[
230			['in', 'id', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
231			'[[id]] IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
232			[':qp0' => 1],
233			],
234			[
235			['not in', 'id', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
236			'[[id]] NOT IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
237			[':qp0' => 1],
238			],
239			[['in', 'id', 1], '[[id]]=:qp0', [':qp0' => 1]],
240			[['in', 'id', [1]], '[[id]]=:qp0', [':qp0' => 1]],
241			[['in', 'id', new TraversableObject([1])], '[[id]]=:qp0', [':qp0' => 1]],
242			'composite in' => [
243			['in', ['id', 'name'], [['id' => 1, 'name' => 'oy']]],
244			'([[id]], [[name]]) IN ((:qp0, :qp1))',
245			[':qp0' => 1, ':qp1' => 'oy'],
246			],
247			'composite in (just one column)' => [
248			['in', ['id'], [['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]],
249			'[[id]] IN (:qp0, :qp1)',
250			[':qp0' => 1, ':qp1' => 2],
251			],
252			'composite in using array objects (just one column)' => [
253			[
254			'in',
255			new TraversableObject(['id']),
256			new TraversableObject([['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]),
257			],
258			'[[id]] IN (:qp0, :qp1)',
259			[':qp0' => 1, ':qp1' => 2],
260			],
261
262			/* in using array objects. */
263			[['id' => new TraversableObject([1, 2])], '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
264			[
265			['in', 'id', new TraversableObject([1, 2, 3])],
266			'[[id]] IN (:qp0, :qp1, :qp2)',
267			[':qp0' => 1, ':qp1' => 2, ':qp2' => 3],
268			],
269
270			/* in using array objects containing null value */
271			[['in', 'id', new TraversableObject([1, null])], '[[id]]=:qp0 OR [[id]] IS NULL', [':qp0' => 1]],
272			[
273			['in', 'id', new TraversableObject([1, 2, null])],
274			'[[id]] IN (:qp0, :qp1) OR [[id]] IS NULL', [':qp0' => 1, ':qp1' => 2],
275			],
276
277			/* not in using array object containing null value */
278			[
279			['not in', 'id', new TraversableObject([1, null])],
280			'[[id]]<>:qp0 AND [[id]] IS NOT NULL', [':qp0' => 1],
281			],
282			[
283			['not in', 'id', new TraversableObject([1, 2, null])],
284			'[[id]] NOT IN (:qp0, :qp1) AND [[id]] IS NOT NULL',
285			[':qp0' => 1, ':qp1' => 2],
286			],
287
288			/* in using array object containing only null value */
289			[['in', 'id', new TraversableObject([null])], '[[id]] IS NULL', []],
290			[['not in', 'id', new TraversableObject([null])], '[[id]] IS NOT NULL', []],
291			'composite in using array objects' => [
292			[
293			'in',
294			new TraversableObject(['id', 'name']),
295			new TraversableObject([['id' => 1, 'name' => 'oy'], ['id' => 2, 'name' => 'yo']]),
296			],
297			'([[id]], [[name]]) IN ((:qp0, :qp1), (:qp2, :qp3))',
298			[':qp0' => 1, ':qp1' => 'oy', ':qp2' => 2, ':qp3' => 'yo'],
299			],
300
301			/* in object conditions */
302			[new InCondition('id', 'in', 1), '[[id]]=:qp0', [':qp0' => 1]],
303			[new InCondition('id', 'in', [1]), '[[id]]=:qp0', [':qp0' => 1]],
304			[new InCondition('id', 'not in', 1), '[[id]]<>:qp0', [':qp0' => 1]],
305			[new InCondition('id', 'not in', [1]), '[[id]]<>:qp0', [':qp0' => 1]],
306			[new InCondition('id', 'in', [1, 2]), '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
307			[new InCondition('id', 'not in', [1, 2]), '[[id]] NOT IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
308			[new InCondition([], 'in', 1), '0=1', []],
309			[new InCondition([], 'in', [1]), '0=1', []],
310			[new InCondition(['id', 'name'], 'in', []), '0=1', []],
311			[
312			new InCondition(
313			['id'],
314			'in',
315			$this->mock->query()->select('id')->from('users')->where(['active' => 1]),
316			),
317			'([[id]]) IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
318			[':qp0' => 1],
319			],
320			[
321			new InCondition(['id', 'name'], 'in', [['id' => 1]]),
322			'([[id]], [[name]]) IN ((:qp0, NULL))',
323			[':qp0' => 1],
324			],
325			[
326			new InCondition(['id', 'name'], 'in', [['name' => 'oy']]),
327			'([[id]], [[name]]) IN ((NULL, :qp0))',
328			[':qp0' => 'oy'],
329			],
330			[
331			new InCondition(['id', 'name'], 'in', [['id' => 1, 'name' => 'oy']]),
332			'([[id]], [[name]]) IN ((:qp0, :qp1))',
333			[':qp0' => 1, ':qp1' => 'oy'],
334			],
335
336			/* exists */
337			[
338			[
339			'exists',
340			$this->mock->query()->select('id')->from('users')->where(['active' => 1]),
341			],
342			'EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
343			[':qp0' => 1],
344			],
345			[
346			['not exists', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
347			'NOT EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', [':qp0' => 1],
348			],
349
350			/* simple conditions */
351			[['=', 'a', 'b'], '[[a]] = :qp0', [':qp0' => 'b']],
352			[['>', 'a', 1], '[[a]] > :qp0', [':qp0' => 1]],
353			[['>=', 'a', 'b'], '[[a]] >= :qp0', [':qp0' => 'b']],
354			[['<', 'a', 2], '[[a]] < :qp0', [':qp0' => 2]],
355			[['<=', 'a', 'b'], '[[a]] <= :qp0', [':qp0' => 'b']],
356			[['<>', 'a', 3], '[[a]] <> :qp0', [':qp0' => 3]],
357			[['!=', 'a', 'b'], '[[a]] != :qp0', [':qp0' => 'b']],
358			[
359			['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL 1 MONTH)')],
360			'[[date]] >= DATE_SUB(NOW(), INTERVAL 1 MONTH)',
361			[],
362			],
363			[
364			['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL :month MONTH)', [':month' => 2])],
365			'[[date]] >= DATE_SUB(NOW(), INTERVAL :month MONTH)',
366			[':month' => 2],
367			],
368			[
369			['=', 'date', $this->mock->query()->select('max(date)')->from('test')->where(['id' => 5])],
370			'[[date]] = (SELECT max(date) FROM [[test]] WHERE [[id]]=:qp0)',
371			[':qp0' => 5],
372			],
373			[['=', 'a', null], '[[a]] = NULL', []],
374
375			/* operand1 is Expression */
376			[
377			['=', new Expression('date'), '2019-08-01'],
378			'date = :qp0',
379			[':qp0' => '2019-08-01'],
380			],
381			[
382			['=', $this->mock->query()->select('COUNT(*)')->from('test')->where(['id' => 6]), 0],
383			'(SELECT COUNT(*) FROM [[test]] WHERE [[id]]=:qp0) = :qp1',
384			[':qp0' => 6, ':qp1' => 0],
385			],
386
387			/* hash condition */
388			[['a' => 1, 'b' => 2], '([[a]]=:qp0) AND ([[b]]=:qp1)', [':qp0' => 1, ':qp1' => 2]],
389			[
390			['a' => new Expression('CONCAT(col1, col2)'), 'b' => 2],
391			'([[a]]=CONCAT(col1, col2)) AND ([[b]]=:qp0)',
392			[':qp0' => 2],
393			],
394			[['a' => null], '[[a]] IS NULL', []],
395
396			/* direct conditions */
397			['a = CONCAT(col1, col2)', 'a = CONCAT(col1, col2)', []],
398			[
399			new Expression('a = CONCAT(col1, :param1)', ['param1' => 'value1']),
400			'a = CONCAT(col1, :param1)',
401			['param1' => 'value1'],
402			],
403
404			/* Expression with params as operand of 'not' */
405			[
406			['not', new Expression('any_expression(:a)', [':a' => 1])],
407			'NOT (any_expression(:a))', [':a' => 1],
408			],
409			[new Expression('NOT (any_expression(:a))', [':a' => 1]), 'NOT (any_expression(:a))', [':a' => 1]],
410
411			/* like */
412			[['like', 'a', 'b'], '[[a]] LIKE :qp0', [':qp0' => '%b%']],
413			[
414			['like', 'a', new Expression(':qp0', [':qp0' => '%b%'])],
415			'[[a]] LIKE :qp0',
416			[':qp0' => '%b%'],
417			],
418			[['like', new Expression('CONCAT(col1, col2)'), 'b'], 'CONCAT(col1, col2) LIKE :qp0', [':qp0' => '%b%']],
419			];
420
421			return $this->replaceQuotes($conditions);
422			}
423
424			public function buildFilterCondition(): array
425			{
426			$conditions = [
427			/* like */
428			[['like', 'name', []], '', []],
429			[['not like', 'name', []], '', []],
430			[['or like', 'name', []], '', []],
431			[['or not like', 'name', []], '', []],
432
433			/* not */
434			[['not', ''], '', []],
435
436			/* and */
437			[['and', '', ''], '', []],
438			[['and', '', 'id=2'], 'id=2', []],
439			[['and', 'id=1', ''], 'id=1', []],
440			[['and', 'type=1', ['or', '', 'id=2']], '(type=1) AND (id=2)', []],
441
442			/* or */
443			[['or', 'id=1', ''], 'id=1', []],
444			[['or', 'type=1', ['or', '', 'id=2']], '(type=1) OR (id=2)', []],
445
446			/* between */
447			[['between', 'id', 1, null], '', []],
448			[['not between', 'id', null, 10], '', []],
449
450			/* in */
451			[['in', 'id', []], '', []],
452			[['not in', 'id', []], '', []],
453
454			/* simple conditions */
455			[['=', 'a', ''], '', []],
456			[['>', 'a', ''], '', []],
457			[['>=', 'a', ''], '', []],
458			[['<', 'a', ''], '', []],
459			[['<=', 'a', ''], '', []],
460			[['<>', 'a', ''], '', []],
461			[['!=', 'a', ''], '', []],
462			];
463
464			return $this->replaceQuotes($conditions);
465			}
466
467			public function buildFrom(): array
468			{
469			return [
470			['test t1', '[[test]] [[t1]]'],
471			['test as t1', '[[test]] [[t1]]'],
472			['test AS t1', '[[test]] [[t1]]'],
473			['test', '[[test]]'],
474			];
475			}
476
477			public function buildWhereExists(): array
478			{
479			return [
480			[
481			'exists',
482			DbHelper::replaceQuotes(
483			<<<SQL
484			SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE EXISTS (SELECT [[1]] FROM [[Website]] [[w]])
485			SQL,
486			$this->mock->getDriverName(),
487			),
488			],
489			[
490			'not exists',
491			DbHelper::replaceQuotes(
492			<<<SQL
493			SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE NOT EXISTS (SELECT [[1]] FROM [[Website]] [[w]])
494			SQL,
495			$this->mock->getDriverName(),
496			),
497			],
498			];
499			}
500
501			public function createDropIndex(): array
502			{
503			$tableName = 'T_constraints_2';
504			$name1 = 'CN_constraints_2_single';
505			$name2 = 'CN_constraints_2_multi';
506
507			return [
508			'drop' => [
509			<<<SQL
510			DROP INDEX [[$name1]] ON {{{$tableName}}}
511			SQL,
512			static fn (QueryBuilderInterface $qb) => $qb->dropIndex($name1, $tableName),
513			],
514			'create' => [
515			<<<SQL
516			CREATE INDEX [[$name1]] ON {{{$tableName}}} ([[C_index_1]])
517			SQL,
518			static fn (QueryBuilderInterface $qb) => $qb->createIndex($name1, $tableName, 'C_index_1'),
519			],
520			'create (2 columns)' => [
521			<<<SQL
522			CREATE INDEX [[$name2]] ON {{{$tableName}}} ([[C_index_2_1]], [[C_index_2_2]])
523			SQL,
524			static fn (QueryBuilderInterface $qb) => $qb->createIndex(
525			$name2,
526			$tableName,
527			'C_index_2_1,
528			C_index_2_2',
529			),
530			],
531			'create unique' => [
532			<<<SQL
533			CREATE UNIQUE INDEX [[$name1]] ON {{{$tableName}}} ([[C_index_1]])
534			SQL,
535			static fn (QueryBuilderInterface $qb) => $qb->createIndex(
536			$name1,
537			$tableName,
538			'C_index_1',
539			QueryBuilder::INDEX_UNIQUE,
540			),
541			],
542			'create unique (2 columns)' => [
543			<<<SQL
544			CREATE UNIQUE INDEX [[$name2]] ON {{{$tableName}}} ([[C_index_2_1]], [[C_index_2_2]])
545			SQL,
546			static fn (QueryBuilderInterface $qb) => $qb->createIndex(
547			$name2,
548			$tableName,
549			'C_index_2_1,
550			C_index_2_2',
551			QueryBuilder::INDEX_UNIQUE,
552			),
553			],
554			];
555			}
556
557			private function replaceQuotes(array $conditions): array
558			{
559			/* adjust dbms specific escaping */
560			foreach ($conditions as $i => $condition) {
561			$conditions[$i][1] = DbHelper::replaceQuotes($condition[1], $this->mock->getDriverName());
562			}
563
564			return $conditions;
565			}
566			}
567

yiisoft / db

Pull Request — master (#380)

BaseQueryBuilderProvider::buildConditions() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like