CommandTest::testAutoQuoting() - Code Metrics - Inspection of "Fix tests (#12)" - yiisoft/db-pgsql - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( bff06a...77db42 )

by Alexander

created 2020-02-26 05:44 UTC

CommandTest::testAutoQuoting() A

↳ Parent: CommandTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	7
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

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

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Pgsql\Tests;

use Yiisoft\Db\Expressions\ArrayExpression;
use Yiisoft\Db\Expressions\JsonExpression;
use Yiisoft\Db\Tests\CommandTest as AbstractCommandTest;

class CommandTest extends AbstractCommandTest
{
    public ?string $driverName = 'pgsql';

    public function testAutoQuoting(): void
    {
        $db = $this->getConnection(false);

        $sql = 'SELECT [[id]], [[t.name]] FROM {{customer}} t';
        $command = $db->createCommand($sql);
        $this->assertEquals('SELECT "id", "t"."name" FROM "customer" t', $command->getSql());
    }

    public function testBooleanValuesInsert(): void
    {
        $db = $this->getConnection();
        $command = $db->createCommand();
        $command->insert('bool_values', ['bool_col' => true]);
        $this->assertEquals(1, $command->execute());

        $command = $db->createCommand();
        $command->insert('bool_values', ['bool_col' => false]);
        $this->assertEquals(1, $command->execute());

        $command = $db->createCommand('SELECT COUNT(*) FROM "bool_values" WHERE bool_col = TRUE;');
        $this->assertEquals(1, $command->queryScalar());
        $command = $db->createCommand('SELECT COUNT(*) FROM "bool_values" WHERE bool_col = FALSE;');
        $this->assertEquals(1, $command->queryScalar());
    }

    public function testBooleanValuesBatchInsert(): void
    {
        $db = $this->getConnection(true, true, true);

        $command = $db->createCommand();

        $command->batchInsert(
            'bool_values',
            ['bool_col'],
            [
                [true],
                [false],
            ]
        );

        $this->assertEquals(2, $command->execute());

        $command = $db->createCommand('SELECT COUNT(*) FROM "bool_values" WHERE bool_col = TRUE;');
        $this->assertEquals(1, $command->queryScalar());
        $command = $db->createCommand('SELECT COUNT(*) FROM "bool_values" WHERE bool_col = FALSE;');
        $this->assertEquals(1, $command->queryScalar());
    }

    public function testLastInsertId(): void
    {
        $db = $this->getConnection();

        $sql = 'INSERT INTO {{profile}}([[description]]) VALUES (\'non duplicate\')';

        $command = $db->createCommand($sql);
        $command->execute();

        $this->assertEquals(3, $db->getSchema()->getLastInsertID('public.profile_id_seq'));

        $sql = 'INSERT INTO {{schema1.profile}}([[description]]) VALUES (\'non duplicate\')';

        $command = $db->createCommand($sql);
        $command->execute();

        $this->assertEquals(3, $db->getSchema()->getLastInsertID('schema1.profile_id_seq'));
    }

    /**
     * @see https://github.com/yiisoft/yii2/issues/11498
     */
    public function testSaveSerializedObject(): void
    {
        if (\defined('HHVM_VERSION')) {
            $this->markTestSkipped(
                'HHVMs PgSQL implementation does not seem to support blob colums in the way they are used here.'
            );
        }

        $db = $this->getConnection();

        $command = $db->createCommand()->insert('type', [
            'int_col' => 1,
            'char_col' => 'serialize',
            'float_col' => 5.6,
            'bool_col' => true,
            'blob_col' => serialize($db),
        ]);
        $this->assertEquals(1, $command->execute());

        $command = $db->createCommand()->update('type', [
            'blob_col' => serialize($db),
        ], ['char_col' => 'serialize']);
        $this->assertEquals(1, $command->execute());
    }

    public function batchInsertSqlProvider(): array
    {
        $data = parent::batchInsertSqlProvider();

        $data['issue11242']['expected'] = 'INSERT INTO "type" ("int_col", "float_col", "char_col") VALUES (NULL, NULL, \'Kyiv {{city}}, Ukraine\')';
        $data['wrongBehavior']['expected'] = 'INSERT INTO "type" ("type"."int_col", "float_col", "char_col") VALUES (\'\', \'\', \'Kyiv {{city}}, Ukraine\')';
        $data['batchInsert binds params from expression']['expected'] = 'INSERT INTO "type" ("int_col") VALUES (:qp1)';
        $data['batchInsert binds params from jsonExpression'] = [
            '{{%type}}',
            ['json_col'],
            [[new JsonExpression(['username' => 'silverfire', 'is_active' => true, 'langs' => ['Ukrainian', 'Russian', 'English']])]],
            'expected' => 'INSERT INTO "type" ("json_col") VALUES (:qp0)',
            'expectedParams' => [':qp0' => '{"username":"silverfire","is_active":true,"langs":["Ukrainian","Russian","English"]}']
        ];
        $data['batchInsert binds params from arrayExpression'] = [
            '{{%type}}',
            ['intarray_col'],
            [[new ArrayExpression([1,null,3], 'int')]],
            'expected' => 'INSERT INTO "type" ("intarray_col") VALUES (ARRAY[:qp0, :qp1, :qp2]::int[])',
            'expectedParams' => [':qp0' => 1, ':qp1' => null, ':qp2' => 3]
        ];
        $data['batchInsert casts string to int according to the table schema'] = [
            '{{%type}}',
            ['int_col'],
            [['3']],
            'expected' => 'INSERT INTO "type" ("int_col") VALUES (3)',
        ];
        $data['batchInsert casts JSON to JSONB when column is JSONB'] = [
            '{{%type}}',
            ['jsonb_col'],
            [[['a' => true]]],
            'expected' => 'INSERT INTO "type" ("jsonb_col") VALUES (:qp0::jsonb)',
            'expectedParams' => [':qp0' => '{"a":true}']
        ];

        return $data;
    }

    /**
     * @see https://github.com/yiisoft/yii2/issues/15827
     */
    public function testIssue15827(): void
    {
        $db = $this->getConnection();

        $inserted = $db->createCommand()->insert('array_and_json_types', [
            'jsonb_col' => new JsonExpression(['Solution date' => '13.01.2011'])
        ])->execute();
        $this->assertSame(1, $inserted);

        $found = $db->createCommand(<<<PGSQL
            SELECT *
            FROM array_and_json_types
            WHERE jsonb_col @> '{"Some not existing key": "random value"}'
PGSQL
        )->execute();
        $this->assertSame(0, $found);

        $found = $db->createCommand(<<<PGSQL
            SELECT *
            FROM array_and_json_types
            WHERE jsonb_col @> '{"Solution date": "13.01.2011"}'
PGSQL
        )->execute();
        $this->assertSame(1, $found);

        $this->assertSame(1, $db->createCommand()->delete('array_and_json_types')->execute());
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace Yiisoft\Db\Pgsql\Tests;
6
7			use Yiisoft\Db\Expressions\ArrayExpression;
8			use Yiisoft\Db\Expressions\JsonExpression;
9			use Yiisoft\Db\Tests\CommandTest as AbstractCommandTest;
10
11			class CommandTest extends AbstractCommandTest
12			{
13			public ?string $driverName = 'pgsql';
14
15			public function testAutoQuoting(): void
16			{
17			$db = $this->getConnection(false);
18
19			$sql = 'SELECT [[id]], [[t.name]] FROM {{customer}} t';
20			$command = $db->createCommand($sql);
21			$this->assertEquals('SELECT "id", "t"."name" FROM "customer" t', $command->getSql());
22			}
23
24			public function testBooleanValuesInsert(): void
25			{
26			$db = $this->getConnection();
27			$command = $db->createCommand();
28			$command->insert('bool_values', ['bool_col' => true]);
29			$this->assertEquals(1, $command->execute());
30
31			$command = $db->createCommand();
32			$command->insert('bool_values', ['bool_col' => false]);
33			$this->assertEquals(1, $command->execute());
34
35			$command = $db->createCommand('SELECT COUNT(*) FROM "bool_values" WHERE bool_col = TRUE;');
36			$this->assertEquals(1, $command->queryScalar());
37			$command = $db->createCommand('SELECT COUNT(*) FROM "bool_values" WHERE bool_col = FALSE;');
38			$this->assertEquals(1, $command->queryScalar());
39			}
40
41			public function testBooleanValuesBatchInsert(): void
42			{
43			$db = $this->getConnection(true, true, true);
44
45			$command = $db->createCommand();
46
47			$command->batchInsert(
48			'bool_values',
49			['bool_col'],
50			[
51			[true],
52			[false],
53			]
54			);
55
56			$this->assertEquals(2, $command->execute());
57
58			$command = $db->createCommand('SELECT COUNT(*) FROM "bool_values" WHERE bool_col = TRUE;');
59			$this->assertEquals(1, $command->queryScalar());
60			$command = $db->createCommand('SELECT COUNT(*) FROM "bool_values" WHERE bool_col = FALSE;');
61			$this->assertEquals(1, $command->queryScalar());
62			}
63
64			public function testLastInsertId(): void
65			{
66			$db = $this->getConnection();
67
68			$sql = 'INSERT INTO {{profile}}([[description]]) VALUES (\'non duplicate\')';
69
70			$command = $db->createCommand($sql);
71			$command->execute();
72
73			$this->assertEquals(3, $db->getSchema()->getLastInsertID('public.profile_id_seq'));
74
75			$sql = 'INSERT INTO {{schema1.profile}}([[description]]) VALUES (\'non duplicate\')';
76
77			$command = $db->createCommand($sql);
78			$command->execute();
79
80			$this->assertEquals(3, $db->getSchema()->getLastInsertID('schema1.profile_id_seq'));
81			}
82
83			/**
84			* @see https://github.com/yiisoft/yii2/issues/11498
85			*/
86			public function testSaveSerializedObject(): void
87			{
88			if (\defined('HHVM_VERSION')) {
89			$this->markTestSkipped(
90			'HHVMs PgSQL implementation does not seem to support blob colums in the way they are used here.'
91			);
92			}
93
94			$db = $this->getConnection();
95
96			$command = $db->createCommand()->insert('type', [
97			'int_col' => 1,
98			'char_col' => 'serialize',
99			'float_col' => 5.6,
100			'bool_col' => true,
101			'blob_col' => serialize($db),
102			]);
103			$this->assertEquals(1, $command->execute());
104
105			$command = $db->createCommand()->update('type', [
106			'blob_col' => serialize($db),
107			], ['char_col' => 'serialize']);
108			$this->assertEquals(1, $command->execute());
109			}
110
111			public function batchInsertSqlProvider(): array
112			{
113			$data = parent::batchInsertSqlProvider();
114
115			$data['issue11242']['expected'] = 'INSERT INTO "type" ("int_col", "float_col", "char_col") VALUES (NULL, NULL, \'Kyiv {{city}}, Ukraine\')';
116			$data['wrongBehavior']['expected'] = 'INSERT INTO "type" ("type"."int_col", "float_col", "char_col") VALUES (\'\', \'\', \'Kyiv {{city}}, Ukraine\')';
117			$data['batchInsert binds params from expression']['expected'] = 'INSERT INTO "type" ("int_col") VALUES (:qp1)';
118			$data['batchInsert binds params from jsonExpression'] = [
119			'{{%type}}',
120			['json_col'],
121			[[new JsonExpression(['username' => 'silverfire', 'is_active' => true, 'langs' => ['Ukrainian', 'Russian', 'English']])]],
122			'expected' => 'INSERT INTO "type" ("json_col") VALUES (:qp0)',
123			'expectedParams' => [':qp0' => '{"username":"silverfire","is_active":true,"langs":["Ukrainian","Russian","English"]}']
124			];
125			$data['batchInsert binds params from arrayExpression'] = [
126			'{{%type}}',
127			['intarray_col'],
128			[[new ArrayExpression([1,null,3], 'int')]],
129			'expected' => 'INSERT INTO "type" ("intarray_col") VALUES (ARRAY[:qp0, :qp1, :qp2]::int[])',
130			'expectedParams' => [':qp0' => 1, ':qp1' => null, ':qp2' => 3]
131			];
132			$data['batchInsert casts string to int according to the table schema'] = [
133			'{{%type}}',
134			['int_col'],
135			[['3']],
136			'expected' => 'INSERT INTO "type" ("int_col") VALUES (3)',
137			];
138			$data['batchInsert casts JSON to JSONB when column is JSONB'] = [
139			'{{%type}}',
140			['jsonb_col'],
141			[[['a' => true]]],
142			'expected' => 'INSERT INTO "type" ("jsonb_col") VALUES (:qp0::jsonb)',
143			'expectedParams' => [':qp0' => '{"a":true}']
144			];
145
146			return $data;
147			}
148
149			/**
150			* @see https://github.com/yiisoft/yii2/issues/15827
151			*/
152			public function testIssue15827(): void
153			{
154			$db = $this->getConnection();
155
156			$inserted = $db->createCommand()->insert('array_and_json_types', [
157			'jsonb_col' => new JsonExpression(['Solution date' => '13.01.2011'])
158			])->execute();
159			$this->assertSame(1, $inserted);
160
161			$found = $db->createCommand(<<<PGSQL
162			SELECT *
163			FROM array_and_json_types
164			WHERE jsonb_col @> '{"Some not existing key": "random value"}'
165			PGSQL
166			)->execute();
167			$this->assertSame(0, $found);
168
169			$found = $db->createCommand(<<<PGSQL
170			SELECT *
171			FROM array_and_json_types
172			WHERE jsonb_col @> '{"Solution date": "13.01.2011"}'
173			PGSQL
174			)->execute();
175			$this->assertSame(1, $found);
176
177			$this->assertSame(1, $db->createCommand()->delete('array_and_json_types')->execute());
178			}
179			}
180

yiisoft / db-pgsql

Push — master ( bff06a...77db42 )

CommandTest::testAutoQuoting() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like