BaseQueryBuilderProvider::batchInsert() - 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 12:00 UTC

BaseQueryBuilderProvider::batchInsert() B

↳ Parent: BaseQueryBuilderProvider

Complexity

Conditions	1
Paths	1

Size

Total Lines	85
Code Lines	60

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	60
nc	1
nop	0
dl	0
loc	85
rs	8.8727
c	0
b	0
f	0

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 addDropChecks(): array
    {
        $tableName = 'T_constraints_1';
        $name = 'CN_check';

        return [
            'drop' => [
                "ALTER TABLE {{{$tableName}}} DROP CONSTRAINT [[$name]]",
                static fn (QueryBuilderInterface $qb) => $qb->dropCheck($name, $tableName),
            ],
            'add' => [
                "ALTER TABLE {{{$tableName}}} ADD CONSTRAINT [[$name]] CHECK ([[C_not_null]] > 100)",
                static fn (QueryBuilderInterface $qb) => $qb->addCheck($name, $tableName, '[[C_not_null]] > 100'),
            ],
        ];
    }

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

yiisoft / db

Pull Request — master (#380)

BaseQueryBuilderProvider::batchInsert() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like