QueryBuilderTest::checksProvider() - Code Metrics - Inspection of "Fix tests (#5)" - yiisoft/db-sqlite - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 5adaea...ab4b97 )

by Alexander

created 2020-02-26 05:42 UTC

QueryBuilderTest::checksProvider() A

↳ Parent: QueryBuilderTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	1
c	1
b	0
f	0
nc	1
nop	0
dl	0
loc	3
rs	10

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Sqlite\Tests;

use Yiisoft\Db\Querys\Query;
use Yiisoft\Db\Sqlite\Schema;
use Yiisoft\Db\Tests\TraversableObject;
use Yiisoft\Db\Tests\QueryBuilderTest as AbstractQueryBuilderTest;

class QueryBuilderTest extends AbstractQueryBuilderTest
{
    protected ?string $driverName = 'sqlite';

    protected string $likeEscapeCharSql = " ESCAPE '\\'";

    public function columnTypes(): array
    {
        return array_merge(parent::columnTypes(), [
            [
                Schema::TYPE_PK,
                $this->primaryKey()->first()->after('col_before'),
                'integer PRIMARY KEY AUTOINCREMENT NOT NULL',
            ],
        ]);
    }

    public function conditionProvider(): array
    {
        return array_merge(parent::conditionProvider(), [
            'composite in using array objects' => [
                ['in', new TraversableObject(['id', 'name']), new TraversableObject([
                    ['id' => 1, 'name' => 'oy'],
                    ['id' => 2, 'name' => 'yo'],
                ])],
                '(([[id]] = :qp0 AND [[name]] = :qp1) OR ([[id]] = :qp2 AND [[name]] = :qp3))',
                [':qp0' => 1, ':qp1' => 'oy', ':qp2' => 2, ':qp3' => 'yo'],
            ],
            'composite in' => [
                ['in', ['id', 'name'], [['id' => 1, 'name' => 'oy']]],
                '(([[id]] = :qp0 AND [[name]] = :qp1))',
                [':qp0' => 1, ':qp1' => 'oy'],
            ],
        ]);
    }

    public function primaryKeysProvider()
    {
        $this->markTestSkipped('Adding/dropping primary keys is not supported in SQLite.');
    }

    public function foreignKeysProvider()
    {
        $this->markTestSkipped('Adding/dropping foreign keys is not supported in SQLite.');
    }

    public function indexesProvider(): array
    {
        $result = parent::indexesProvider();
        $result['drop'][0] = 'DROP INDEX [[CN_constraints_2_single]]';

        return $result;
    }

    public function uniquesProvider()
    {
        $this->markTestSkipped('Adding/dropping unique constraints is not supported in SQLite.');
    }

    public function checksProvider()
    {
        $this->markTestSkipped('Adding/dropping check constraints is not supported in SQLite.');
    }

    public function defaultValuesProvider()
    {
        $this->markTestSkipped('Adding/dropping default constraints is not supported in SQLite.');
    }

    public function testCommentColumn(): void
    {
        $this->markTestSkipped('Comments are not supported in SQLite');
    }

    public function testCommentTable(): void
    {
        $this->markTestSkipped('Comments are not supported in SQLite');
    }

    public function batchInsertProvider(): array
    {
        $data = parent::batchInsertProvider();
        $data['escape-danger-chars']['expected'] = "INSERT INTO `customer` (`address`) VALUES ('SQL-danger chars are escaped: ''); --')";

        return $data;
    }

    public function testBatchInsertOnOlderVersions()
    {
        $db = $this->getConnection();
        if (version_compare($db->getPdo()->getAttribute(\PDO::ATTR_SERVER_VERSION), '3.7.11', '>=')) {
            $this->markTestSkipped('This test is only relevant for SQLite < 3.7.11');
        }
        $sql = $this->getQueryBuilder()->batchInsert('{{customer}} t', ['t.id', 't.name'], [[1, 'a'], [2, 'b']]);
        $this->assertEquals("INSERT INTO {{customer}} t (`t`.`id`, `t`.`name`) SELECT 1, 'a' UNION SELECT 2, 'b'", $sql);
    }

    public function testRenameTable()
    {
        $sql = $this->getQueryBuilder()->renameTable('table_from', 'table_to');
        $this->assertEquals('ALTER TABLE `table_from` RENAME TO `table_to`', $sql);
    }

    /**
     * {@inheritdoc}
     */
    public function testBuildUnion(): void
    {
        $db = $this->getConnection();

        $expectedQuerySql = $this->replaceQuotes(
            'SELECT `id` FROM `TotalExample` `t1` WHERE (w > 0) AND (x < 2) UNION  SELECT `id` FROM `TotalTotalExample` `t2` WHERE w > 5 UNION ALL  SELECT `id` FROM `TotalTotalExample` `t3` WHERE w = 3'
        );

        $query = new Query($db);

        $secondQuery = new Query($db);

        $secondQuery->select('id')
            ->from('TotalTotalExample t2')
            ->where('w > 5');

        $thirdQuery = new Query($db);

        $thirdQuery->select('id')
            ->from('TotalTotalExample t3')
            ->where('w = 3');
        $query->select('id')
            ->from('TotalExample t1')
            ->where(['and', 'w > 0', 'x < 2'])
            ->union($secondQuery)
            ->union($thirdQuery, true);
        [$actualQuerySql, $queryParams] = $this->getQueryBuilder()->build($query);

        $this->assertEquals($expectedQuerySql, $actualQuerySql);
        $this->assertEquals([], $queryParams);
    }

    public function testResetSequence()
    {
        $qb = $this->getQueryBuilder(true, true);

        $expected = "UPDATE sqlite_sequence SET seq='5' WHERE name='item'";
        $sql = $qb->resetSequence('item');
        $this->assertEquals($expected, $sql);

        $expected = "UPDATE sqlite_sequence SET seq='3' WHERE name='item'";
        $sql = $qb->resetSequence('item', 4);
        $this->assertEquals($expected, $sql);
    }

    public function upsertProvider(): array
    {
        $concreteData = [
            'regular values' => [
                3 => 'WITH "EXCLUDED" (`email`, `address`, `status`, `profile_id`) AS (VALUES (:qp0, :qp1, :qp2, :qp3)) UPDATE `T_upsert` SET `address`=(SELECT `address` FROM `EXCLUDED`), `status`=(SELECT `status` FROM `EXCLUDED`), `profile_id`=(SELECT `profile_id` FROM `EXCLUDED`) WHERE `T_upsert`.`email`=(SELECT `email` FROM `EXCLUDED`); INSERT OR IGNORE INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3);',
            ],
            'regular values with update part' => [
                3 => 'WITH "EXCLUDED" (`email`, `address`, `status`, `profile_id`) AS (VALUES (:qp0, :qp1, :qp2, :qp3)) UPDATE `T_upsert` SET `address`=:qp4, `status`=:qp5, `orders`=T_upsert.orders + 1 WHERE `T_upsert`.`email`=(SELECT `email` FROM `EXCLUDED`); INSERT OR IGNORE INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3);',
            ],
            'regular values without update part' => [
                3 => 'INSERT OR IGNORE INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3)',
            ],
            'query' => [
                3 => 'WITH "EXCLUDED" (`email`, `status`) AS (SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1) UPDATE `T_upsert` SET `status`=(SELECT `status` FROM `EXCLUDED`) WHERE `T_upsert`.`email`=(SELECT `email` FROM `EXCLUDED`); INSERT OR IGNORE INTO `T_upsert` (`email`, `status`) SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1;',
            ],
            'query with update part' => [
                3 => 'WITH "EXCLUDED" (`email`, `status`) AS (SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1) UPDATE `T_upsert` SET `address`=:qp1, `status`=:qp2, `orders`=T_upsert.orders + 1 WHERE `T_upsert`.`email`=(SELECT `email` FROM `EXCLUDED`); INSERT OR IGNORE INTO `T_upsert` (`email`, `status`) SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1;',
            ],
            'query without update part' => [
                3 => 'INSERT OR IGNORE INTO `T_upsert` (`email`, `status`) SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1',
            ],
            'values and expressions' => [
                3 => 'INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, now())',
            ],
            'values and expressions with update part' => [
                3 => 'INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, now())',
            ],
            'values and expressions without update part' => [
                3 => 'INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, now())',
            ],
            'query, values and expressions with update part' => [
                3 => 'WITH "EXCLUDED" (`email`, [[time]]) AS (SELECT :phEmail AS `email`, now() AS [[time]]) UPDATE {{%T_upsert}} SET `ts`=:qp1, [[orders]]=T_upsert.orders + 1 WHERE {{%T_upsert}}.`email`=(SELECT `email` FROM `EXCLUDED`); INSERT OR IGNORE INTO {{%T_upsert}} (`email`, [[time]]) SELECT :phEmail AS `email`, now() AS [[time]];',
            ],
            'query, values and expressions without update part' => [
                3 => 'WITH "EXCLUDED" (`email`, [[time]]) AS (SELECT :phEmail AS `email`, now() AS [[time]]) UPDATE {{%T_upsert}} SET `ts`=:qp1, [[orders]]=T_upsert.orders + 1 WHERE {{%T_upsert}}.`email`=(SELECT `email` FROM `EXCLUDED`); INSERT OR IGNORE INTO {{%T_upsert}} (`email`, [[time]]) SELECT :phEmail AS `email`, now() AS [[time]];',
            ],
            'no columns to update' => [
                3 => 'INSERT OR IGNORE INTO `T_upsert_1` (`a`) VALUES (:qp0)',
            ],
        ];

        $newData = parent::upsertProvider();

        foreach ($concreteData as $testName => $data) {
            $newData[$testName] = array_replace($newData[$testName], $data);
        }

        return $newData;
    }

    public function testBuildWithQuery()
    {
        $db = $this->getConnection();

        $expectedQuerySql = $this->replaceQuotes(
             'WITH a1 AS (SELECT [[id]] FROM [[t1]] WHERE expr = 1), a2 AS (SELECT [[id]] FROM [[t2]] INNER JOIN [[a1]] ON t2.id = a1.id WHERE expr = 2 UNION  SELECT [[id]] FROM [[t3]] WHERE expr = 3) SELECT * FROM [[a2]]'
        );

        $with1Query = (new Query($db))
            ->select('id')
            ->from('t1')
            ->where('expr = 1');

        $with2Query = (new Query($db))
            ->select('id')
            ->from('t2')
            ->innerJoin('a1', 't2.id = a1.id')
            ->where('expr = 2');

        $with3Query = (new Query($db))
            ->select('id')
            ->from('t3')
            ->where('expr = 3');

        $query = (new Query($db))
            ->withQuery($with1Query, 'a1')
            ->withQuery($with2Query->union($with3Query), 'a2')
            ->from('a2');

        [$actualQuerySql, $queryParams] = $this->getQueryBuilder()->build($query);

        $this->assertEquals($expectedQuerySql, $actualQuerySql);
        $this->assertEquals([], $queryParams);
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace Yiisoft\Db\Sqlite\Tests;
6
7			use Yiisoft\Db\Querys\Query;
8			use Yiisoft\Db\Sqlite\Schema;
9			use Yiisoft\Db\Tests\TraversableObject;
10			use Yiisoft\Db\Tests\QueryBuilderTest as AbstractQueryBuilderTest;
11
12			class QueryBuilderTest extends AbstractQueryBuilderTest
13			{
14			protected ?string $driverName = 'sqlite';
15
16			protected string $likeEscapeCharSql = " ESCAPE '\\'";
17
18			public function columnTypes(): array
19			{
20			return array_merge(parent::columnTypes(), [
21			[
22			Schema::TYPE_PK,
23			$this->primaryKey()->first()->after('col_before'),
24			'integer PRIMARY KEY AUTOINCREMENT NOT NULL',
25			],
26			]);
27			}
28
29			public function conditionProvider(): array
30			{
31			return array_merge(parent::conditionProvider(), [
32			'composite in using array objects' => [
33			['in', new TraversableObject(['id', 'name']), new TraversableObject([
34			['id' => 1, 'name' => 'oy'],
35			['id' => 2, 'name' => 'yo'],
36			])],
37			'(([[id]] = :qp0 AND [[name]] = :qp1) OR ([[id]] = :qp2 AND [[name]] = :qp3))',
38			[':qp0' => 1, ':qp1' => 'oy', ':qp2' => 2, ':qp3' => 'yo'],
39			],
40			'composite in' => [
41			['in', ['id', 'name'], [['id' => 1, 'name' => 'oy']]],
42			'(([[id]] = :qp0 AND [[name]] = :qp1))',
43			[':qp0' => 1, ':qp1' => 'oy'],
44			],
45			]);
46			}
47
48			public function primaryKeysProvider()
49			{
50			$this->markTestSkipped('Adding/dropping primary keys is not supported in SQLite.');
51			}
52
53			public function foreignKeysProvider()
54			{
55			$this->markTestSkipped('Adding/dropping foreign keys is not supported in SQLite.');
56			}
57
58			public function indexesProvider(): array
59			{
60			$result = parent::indexesProvider();
61			$result['drop'][0] = 'DROP INDEX [[CN_constraints_2_single]]';
62
63			return $result;
64			}
65
66			public function uniquesProvider()
67			{
68			$this->markTestSkipped('Adding/dropping unique constraints is not supported in SQLite.');
69			}
70
71			public function checksProvider()
72			{
73			$this->markTestSkipped('Adding/dropping check constraints is not supported in SQLite.');
74			}
75
76			public function defaultValuesProvider()
77			{
78			$this->markTestSkipped('Adding/dropping default constraints is not supported in SQLite.');
79			}
80
81			public function testCommentColumn(): void
82			{
83			$this->markTestSkipped('Comments are not supported in SQLite');
84			}
85
86			public function testCommentTable(): void
87			{
88			$this->markTestSkipped('Comments are not supported in SQLite');
89			}
90
91			public function batchInsertProvider(): array
92			{
93			$data = parent::batchInsertProvider();
94			$data['escape-danger-chars']['expected'] = "INSERT INTO `customer` (`address`) VALUES ('SQL-danger chars are escaped: ''); --')";
95
96			return $data;
97			}
98
99			public function testBatchInsertOnOlderVersions()
100			{
101			$db = $this->getConnection();
102			if (version_compare($db->getPdo()->getAttribute(\PDO::ATTR_SERVER_VERSION), '3.7.11', '>=')) {
103			$this->markTestSkipped('This test is only relevant for SQLite < 3.7.11');
104			}
105			$sql = $this->getQueryBuilder()->batchInsert('{{customer}} t', ['t.id', 't.name'], [[1, 'a'], [2, 'b']]);
106			$this->assertEquals("INSERT INTO {{customer}} t (`t`.`id`, `t`.`name`) SELECT 1, 'a' UNION SELECT 2, 'b'", $sql);
107			}
108
109			public function testRenameTable()
110			{
111			$sql = $this->getQueryBuilder()->renameTable('table_from', 'table_to');
112			$this->assertEquals('ALTER TABLE `table_from` RENAME TO `table_to`', $sql);
113			}
114
115			/**
116			* {@inheritdoc}
117			*/
118			public function testBuildUnion(): void
119			{
120			$db = $this->getConnection();
121
122			$expectedQuerySql = $this->replaceQuotes(
123			'SELECT `id` FROM `TotalExample` `t1` WHERE (w > 0) AND (x < 2) UNION SELECT `id` FROM `TotalTotalExample` `t2` WHERE w > 5 UNION ALL SELECT `id` FROM `TotalTotalExample` `t3` WHERE w = 3'
124			);
125
126			$query = new Query($db);
127
128			$secondQuery = new Query($db);
129
130			$secondQuery->select('id')
131			->from('TotalTotalExample t2')
132			->where('w > 5');
133
134			$thirdQuery = new Query($db);
135
136			$thirdQuery->select('id')
137			->from('TotalTotalExample t3')
138			->where('w = 3');
139			$query->select('id')
140			->from('TotalExample t1')
141			->where(['and', 'w > 0', 'x < 2'])
142			->union($secondQuery)
143			->union($thirdQuery, true);
144			[$actualQuerySql, $queryParams] = $this->getQueryBuilder()->build($query);
145
146			$this->assertEquals($expectedQuerySql, $actualQuerySql);
147			$this->assertEquals([], $queryParams);
148			}
149
150			public function testResetSequence()
151			{
152			$qb = $this->getQueryBuilder(true, true);
153
154			$expected = "UPDATE sqlite_sequence SET seq='5' WHERE name='item'";
155			$sql = $qb->resetSequence('item');
156			$this->assertEquals($expected, $sql);
157
158			$expected = "UPDATE sqlite_sequence SET seq='3' WHERE name='item'";
159			$sql = $qb->resetSequence('item', 4);
160			$this->assertEquals($expected, $sql);
161			}
162
163			public function upsertProvider(): array
164			{
165			$concreteData = [
166			'regular values' => [
167			3 => 'WITH "EXCLUDED" (`email`, `address`, `status`, `profile_id`) AS (VALUES (:qp0, :qp1, :qp2, :qp3)) UPDATE `T_upsert` SET `address`=(SELECT `address` FROM `EXCLUDED`), `status`=(SELECT `status` FROM `EXCLUDED`), `profile_id`=(SELECT `profile_id` FROM `EXCLUDED`) WHERE `T_upsert`.`email`=(SELECT `email` FROM `EXCLUDED`); INSERT OR IGNORE INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3);',
168			],
169			'regular values with update part' => [
170			3 => 'WITH "EXCLUDED" (`email`, `address`, `status`, `profile_id`) AS (VALUES (:qp0, :qp1, :qp2, :qp3)) UPDATE `T_upsert` SET `address`=:qp4, `status`=:qp5, `orders`=T_upsert.orders + 1 WHERE `T_upsert`.`email`=(SELECT `email` FROM `EXCLUDED`); INSERT OR IGNORE INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3);',
171			],
172			'regular values without update part' => [
173			3 => 'INSERT OR IGNORE INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3)',
174			],
175			'query' => [
176			3 => 'WITH "EXCLUDED" (`email`, `status`) AS (SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1) UPDATE `T_upsert` SET `status`=(SELECT `status` FROM `EXCLUDED`) WHERE `T_upsert`.`email`=(SELECT `email` FROM `EXCLUDED`); INSERT OR IGNORE INTO `T_upsert` (`email`, `status`) SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1;',
177			],
178			'query with update part' => [
179			3 => 'WITH "EXCLUDED" (`email`, `status`) AS (SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1) UPDATE `T_upsert` SET `address`=:qp1, `status`=:qp2, `orders`=T_upsert.orders + 1 WHERE `T_upsert`.`email`=(SELECT `email` FROM `EXCLUDED`); INSERT OR IGNORE INTO `T_upsert` (`email`, `status`) SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1;',
180			],
181			'query without update part' => [
182			3 => 'INSERT OR IGNORE INTO `T_upsert` (`email`, `status`) SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1',
183			],
184			'values and expressions' => [
185			3 => 'INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, now())',
186			],
187			'values and expressions with update part' => [
188			3 => 'INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, now())',
189			],
190			'values and expressions without update part' => [
191			3 => 'INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, now())',
192			],
193			'query, values and expressions with update part' => [
194			3 => 'WITH "EXCLUDED" (`email`, [[time]]) AS (SELECT :phEmail AS `email`, now() AS [[time]]) UPDATE {{%T_upsert}} SET `ts`=:qp1, [[orders]]=T_upsert.orders + 1 WHERE {{%T_upsert}}.`email`=(SELECT `email` FROM `EXCLUDED`); INSERT OR IGNORE INTO {{%T_upsert}} (`email`, [[time]]) SELECT :phEmail AS `email`, now() AS [[time]];',
195			],
196			'query, values and expressions without update part' => [
197			3 => 'WITH "EXCLUDED" (`email`, [[time]]) AS (SELECT :phEmail AS `email`, now() AS [[time]]) UPDATE {{%T_upsert}} SET `ts`=:qp1, [[orders]]=T_upsert.orders + 1 WHERE {{%T_upsert}}.`email`=(SELECT `email` FROM `EXCLUDED`); INSERT OR IGNORE INTO {{%T_upsert}} (`email`, [[time]]) SELECT :phEmail AS `email`, now() AS [[time]];',
198			],
199			'no columns to update' => [
200			3 => 'INSERT OR IGNORE INTO `T_upsert_1` (`a`) VALUES (:qp0)',
201			],
202			];
203
204			$newData = parent::upsertProvider();
205
206			foreach ($concreteData as $testName => $data) {
207			$newData[$testName] = array_replace($newData[$testName], $data);
208			}
209
210			return $newData;
211			}
212
213			public function testBuildWithQuery()
214			{
215			$db = $this->getConnection();
216
217			$expectedQuerySql = $this->replaceQuotes(
218			'WITH a1 AS (SELECT [[id]] FROM [[t1]] WHERE expr = 1), a2 AS (SELECT [[id]] FROM [[t2]] INNER JOIN [[a1]] ON t2.id = a1.id WHERE expr = 2 UNION SELECT [[id]] FROM [[t3]] WHERE expr = 3) SELECT * FROM [[a2]]'
219			);
220
221			$with1Query = (new Query($db))
222			->select('id')
223			->from('t1')
224			->where('expr = 1');
225
226			$with2Query = (new Query($db))
227			->select('id')
228			->from('t2')
229			->innerJoin('a1', 't2.id = a1.id')
230			->where('expr = 2');
231
232			$with3Query = (new Query($db))
233			->select('id')
234			->from('t3')
235			->where('expr = 3');
236
237			$query = (new Query($db))
238			->withQuery($with1Query, 'a1')
239			->withQuery($with2Query->union($with3Query), 'a2')
240			->from('a2');
241
242			[$actualQuerySql, $queryParams] = $this->getQueryBuilder()->build($query);
243
244			$this->assertEquals($expectedQuerySql, $actualQuerySql);
245			$this->assertEquals([], $queryParams);
246			}
247			}
248

yiisoft / db-sqlite

Push — master ( 5adaea...ab4b97 )

QueryBuilderTest::checksProvider() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like