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 Alexander

created 2022-11-03 01:28 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 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,
                ),
            ],
        ];
    }

    public function delete(): array
    {
        return [
            [
                'user',
                [
                    'is_enabled' => false,
                    'power' => new Expression('WRONG_POWER()'),
                ],
                DbHelper::replaceQuotes(
                    <<<SQL
                    DELETE FROM [[user]] WHERE ([[is_enabled]]=:qp0) AND ([[power]]=WRONG_POWER())
                    SQL,
                    $this->mock->getDriverName(),
                ),
                [
                    ':qp0' => false,
                ],
            ],
        ];
    }

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

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