SelectTest::testToStringWithOuterOffsetAndLimits() - Code Metrics - Inspection of "Refactor Combining query support" - abterphp/qb - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( bbfba8...2e1d9e )

by Peter

created 2021-06-27 17:27 UTC

SelectTest::testToStringWithOuterOffsetAndLimits() A

↳ Parent: SelectTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	22
Code Lines	16

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	16
c	0
b	0
f	0
nc	1
nop	0
dl	0
loc	22
rs	9.7333

<?php

declare(strict_types=1);

namespace QB\MySQL\Statement;

use QB\Generic\Clause\Column;
use QB\Generic\Clause\Table;
use QB\Generic\Expr\Expr;
use QB\Generic\Statement\SelectTest as GenericSelectTest;
use QB\MySQL\Clause\Lock;

class SelectTest extends GenericSelectTest
{
    public function testToStringModifiers()
    {
        $modifiers = [
            Select::DISTINCT,
            Select::HIGH_PRIORITY,
            Select::STRAIGHT_JOIN,
            Select::SQL_SMALL_RESULT,
            Select::SQL_BIG_RESULT,
            Select::SQL_BUFFER_RESULT,
            Select::SQL_NO_CACHE,
            Select::SQL_CALC_FOUND_ROWS,
        ];

        $sql = (string)$this->getSut('foo')
            ->addModifier(...$modifiers)
            ->addColumns('id', 'bar_id');

        $parts   = [];
        $parts[] = sprintf('SELECT %s id, bar_id', implode(' ', $modifiers));
        $parts[] = 'FROM foo';

        $expectedSql = implode(PHP_EOL, $parts);

        $this->assertSame($expectedSql, $sql);
    }

    public function testToStringWithOuterLimits()
    {
        $unionQuery = $this->getSut('baz')
            ->addColumns('id');

        $sql = (string)$this->getSut('foo')
            ->addColumns('id')
            ->addUnion($unionQuery)
            ->setOuterLimit(10);

        $parts   = [];
        $parts[] = '(SELECT id';
        $parts[] = 'FROM foo';
        $parts[] = 'UNION';
        $parts[] = 'SELECT id';
        $parts[] = 'FROM baz)';
        $parts[] = 'LIMIT 10';

        $expectedSql = implode(PHP_EOL, $parts);

        $this->assertSame($expectedSql, $sql);
    }

    public function testToStringWithOuterOffsetAndLimits()
    {
        $unionQuery = $this->getSut('baz')
            ->addColumns('id');

        $sql = (string)$this->getSut('foo')
            ->addColumns('id')
            ->addUnion($unionQuery)
            ->setOuterLimit(10)
            ->setOuterOffset(20);

        $parts   = [];
        $parts[] = '(SELECT id';
        $parts[] = 'FROM foo';
        $parts[] = 'UNION';
        $parts[] = 'SELECT id';
        $parts[] = 'FROM baz)';
        $parts[] = 'LIMIT 20, 10';

        $expectedSql = implode(PHP_EOL, $parts);

        $this->assertSame($expectedSql, $sql);
    }

    public function testToStringWithOuterLock()
    {
        $unionQuery = $this->getSut('baz')
            ->addColumns('id');

        $sql = (string)$this->getSut('foo')
            ->addColumns('id')
            ->addUnion($unionQuery)
            ->setOuterLock(new Lock(Lock::FOR_UPDATE, [], Lock::MODIFIER_NOWAIT));

        $parts   = [];
        $parts[] = '(SELECT id';
        $parts[] = 'FROM foo';
        $parts[] = 'UNION';
        $parts[] = 'SELECT id';
        $parts[] = 'FROM baz)';
        $parts[] = 'FOR UPDATE NOWAIT';

        $expectedSql = implode(PHP_EOL, $parts);

        $this->assertSame($expectedSql, $sql);
    }

    public function testToStringWithLimitAndLock()
    {
        $unionQuery = $this->getSut('baz')
            ->addColumns('id');

        $sql = (string)$this->getSut('foo')
            ->addColumns('id')
            ->setLimit(10)
            ->addUnion($unionQuery)
            ->setOuterLock(new Lock(Lock::FOR_UPDATE, [], Lock::MODIFIER_SKIP_LOCKED));

        $parts   = [];
        $parts[] = '(SELECT id';
        $parts[] = 'FROM foo';
        $parts[] = 'LIMIT 10';
        $parts[] = 'UNION';
        $parts[] = 'SELECT id';
        $parts[] = 'FROM baz)';
        $parts[] = 'FOR UPDATE SKIP LOCKED';

        $expectedSql = implode(PHP_EOL, $parts);

        $this->assertSame($expectedSql, $sql);
    }

    public function testToStringComplex()
    {
        $columnQuery = $this->getSut('quix')
            ->addColumns('b')
            ->addWhere(new Expr('id = ?', [7]));

        $columnExpr = new Expr('NOW()');

        $unionQuery = $this->getSut('baz')
            ->addColumns('b', 'f');

        $sql = (string)$this->getSut('foo', 'bar')
            ->addModifier('DISTINCT')
            ->addColumns('COUNT(DISTINCT baz) AS baz_count', new Column($columnQuery, 'quix_b'))
            ->addColumns(new Column($columnExpr, 'now'))
            ->addColumn('bar.id', 'bar_id')
            ->addInnerJoin('quix', 'foo.id = q.foo_id', 'q')
            ->addWhere('foo.bar = "foo-bar"', new Expr('bar.foo = ?', ['bar-foo']))
            ->addGroupBy('q.foo_id', new Expr('q.bar.id'))
            ->setGroupWithRollup()
            ->addHaving('baz_count > 0')
            ->addOrderBy('baz_count', 'ASC')
            ->setLimit(10)
            ->setOffset(20)
            ->setLock(new Lock(Lock::FOR_UPDATE, [], Lock::MODIFIER_NOWAIT))
            ->addUnion($unionQuery)
            ->setOuterLimit(25)
            ->addOuterOrderBy('baz_count', 'DESC');

        $parts   = [];
        $parts[] = '(SELECT DISTINCT COUNT(DISTINCT baz) AS baz_count, (SELECT b FROM quix WHERE id = ?) AS quix_b, NOW() AS now, bar.id AS bar_id'; // nolint
        $parts[] = 'FROM foo, bar';
        $parts[] = 'INNER JOIN quix AS q ON foo.id = q.foo_id';
        $parts[] = 'WHERE foo.bar = "foo-bar" AND bar.foo = ?';
        $parts[] = 'GROUP BY q.foo_id, q.bar.id WITH ROLLUP';
        $parts[] = 'HAVING baz_count > 0';
        $parts[] = 'ORDER BY baz_count ASC';
        $parts[] = 'LIMIT 20, 10';
        $parts[] = 'FOR UPDATE NOWAIT';
        $parts[] = 'UNION';
        $parts[] = 'SELECT b, f';
        $parts[] = 'FROM baz)';
        $parts[] = 'ORDER BY baz_count DESC';
        $parts[] = 'LIMIT 25';

        $expectedSql = implode(PHP_EOL, $parts);

        $this->assertSame($expectedSql, $sql);
    }

    /**
     * @param string|Table ...$tables
     *
     * @return Select
     */
    protected function getSut(string|Table ...$tables): Select
    {
        return (new Select())->addFrom(...$tables);
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace QB\MySQL\Statement;
6
7			use QB\Generic\Clause\Column;
8			use QB\Generic\Clause\Table;
9			use QB\Generic\Expr\Expr;
10			use QB\Generic\Statement\SelectTest as GenericSelectTest;
11			use QB\MySQL\Clause\Lock;
12
13			class SelectTest extends GenericSelectTest
14			{
15			public function testToStringModifiers()
16			{
17			$modifiers = [
18			Select::DISTINCT,
19			Select::HIGH_PRIORITY,
20			Select::STRAIGHT_JOIN,
21			Select::SQL_SMALL_RESULT,
22			Select::SQL_BIG_RESULT,
23			Select::SQL_BUFFER_RESULT,
24			Select::SQL_NO_CACHE,
25			Select::SQL_CALC_FOUND_ROWS,
26			];
27
28			$sql = (string)$this->getSut('foo')
29			->addModifier(...$modifiers)
30			->addColumns('id', 'bar_id');
31
32			$parts = [];
33			$parts[] = sprintf('SELECT %s id, bar_id', implode(' ', $modifiers));
34			$parts[] = 'FROM foo';
35
36			$expectedSql = implode(PHP_EOL, $parts);
37
38			$this->assertSame($expectedSql, $sql);
39			}
40
41			public function testToStringWithOuterLimits()
42			{
43			$unionQuery = $this->getSut('baz')
44			->addColumns('id');
45
46			$sql = (string)$this->getSut('foo')
47			->addColumns('id')
48			->addUnion($unionQuery)
49			->setOuterLimit(10);
50
51			$parts = [];
52			$parts[] = '(SELECT id';
53			$parts[] = 'FROM foo';
54			$parts[] = 'UNION';
55			$parts[] = 'SELECT id';
56			$parts[] = 'FROM baz)';
57			$parts[] = 'LIMIT 10';
58
59			$expectedSql = implode(PHP_EOL, $parts);
60
61			$this->assertSame($expectedSql, $sql);
62			}
63
64			public function testToStringWithOuterOffsetAndLimits()
65			{
66			$unionQuery = $this->getSut('baz')
67			->addColumns('id');
68
69			$sql = (string)$this->getSut('foo')
70			->addColumns('id')
71			->addUnion($unionQuery)
72			->setOuterLimit(10)
73			->setOuterOffset(20);
74
75			$parts = [];
76			$parts[] = '(SELECT id';
77			$parts[] = 'FROM foo';
78			$parts[] = 'UNION';
79			$parts[] = 'SELECT id';
80			$parts[] = 'FROM baz)';
81			$parts[] = 'LIMIT 20, 10';
82
83			$expectedSql = implode(PHP_EOL, $parts);
84
85			$this->assertSame($expectedSql, $sql);
86			}
87
88			public function testToStringWithOuterLock()
89			{
90			$unionQuery = $this->getSut('baz')
91			->addColumns('id');
92
93			$sql = (string)$this->getSut('foo')
94			->addColumns('id')
95			->addUnion($unionQuery)
96			->setOuterLock(new Lock(Lock::FOR_UPDATE, [], Lock::MODIFIER_NOWAIT));
97
98			$parts = [];
99			$parts[] = '(SELECT id';
100			$parts[] = 'FROM foo';
101			$parts[] = 'UNION';
102			$parts[] = 'SELECT id';
103			$parts[] = 'FROM baz)';
104			$parts[] = 'FOR UPDATE NOWAIT';
105
106			$expectedSql = implode(PHP_EOL, $parts);
107
108			$this->assertSame($expectedSql, $sql);
109			}
110
111			public function testToStringWithLimitAndLock()
112			{
113			$unionQuery = $this->getSut('baz')
114			->addColumns('id');
115
116			$sql = (string)$this->getSut('foo')
117			->addColumns('id')
118			->setLimit(10)
119			->addUnion($unionQuery)
120			->setOuterLock(new Lock(Lock::FOR_UPDATE, [], Lock::MODIFIER_SKIP_LOCKED));
121
122			$parts = [];
123			$parts[] = '(SELECT id';
124			$parts[] = 'FROM foo';
125			$parts[] = 'LIMIT 10';
126			$parts[] = 'UNION';
127			$parts[] = 'SELECT id';
128			$parts[] = 'FROM baz)';
129			$parts[] = 'FOR UPDATE SKIP LOCKED';
130
131			$expectedSql = implode(PHP_EOL, $parts);
132
133			$this->assertSame($expectedSql, $sql);
134			}
135
136			public function testToStringComplex()
137			{
138			$columnQuery = $this->getSut('quix')
139			->addColumns('b')
140			->addWhere(new Expr('id = ?', [7]));
141
142			$columnExpr = new Expr('NOW()');
143
144			$unionQuery = $this->getSut('baz')
145			->addColumns('b', 'f');
146
147			$sql = (string)$this->getSut('foo', 'bar')
148			->addModifier('DISTINCT')
149			->addColumns('COUNT(DISTINCT baz) AS baz_count', new Column($columnQuery, 'quix_b'))
150			->addColumns(new Column($columnExpr, 'now'))
151			->addColumn('bar.id', 'bar_id')
152			->addInnerJoin('quix', 'foo.id = q.foo_id', 'q')
153			->addWhere('foo.bar = "foo-bar"', new Expr('bar.foo = ?', ['bar-foo']))
154			->addGroupBy('q.foo_id', new Expr('q.bar.id'))
155			->setGroupWithRollup()
156			->addHaving('baz_count > 0')
157			->addOrderBy('baz_count', 'ASC')
158			->setLimit(10)
159			->setOffset(20)
160			->setLock(new Lock(Lock::FOR_UPDATE, [], Lock::MODIFIER_NOWAIT))
161			->addUnion($unionQuery)
162			->setOuterLimit(25)
163			->addOuterOrderBy('baz_count', 'DESC');
164
165			$parts = [];
166			$parts[] = '(SELECT DISTINCT COUNT(DISTINCT baz) AS baz_count, (SELECT b FROM quix WHERE id = ?) AS quix_b, NOW() AS now, bar.id AS bar_id'; // nolint
167			$parts[] = 'FROM foo, bar';
168			$parts[] = 'INNER JOIN quix AS q ON foo.id = q.foo_id';
169			$parts[] = 'WHERE foo.bar = "foo-bar" AND bar.foo = ?';
170			$parts[] = 'GROUP BY q.foo_id, q.bar.id WITH ROLLUP';
171			$parts[] = 'HAVING baz_count > 0';
172			$parts[] = 'ORDER BY baz_count ASC';
173			$parts[] = 'LIMIT 20, 10';
174			$parts[] = 'FOR UPDATE NOWAIT';
175			$parts[] = 'UNION';
176			$parts[] = 'SELECT b, f';
177			$parts[] = 'FROM baz)';
178			$parts[] = 'ORDER BY baz_count DESC';
179			$parts[] = 'LIMIT 25';
180
181			$expectedSql = implode(PHP_EOL, $parts);
182
183			$this->assertSame($expectedSql, $sql);
184			}
185
186			/**
187			* @param string\|Table ...$tables
188			*
189			* @return Select
190			*/
191			protected function getSut(string\|Table ...$tables): Select
192			{
193			return (new Select())->addFrom(...$tables);
194			}
195			}
196

abterphp / qb

Push — main ( bbfba8...2e1d9e )

SelectTest::testToStringWithOuterOffsetAndLimits() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like