PostgreSQLTest::testUpsert() - Code Metrics - Inspection of "Expr-Params split, PDOHelper" - abterphp/qb - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( b157b0...77200e )

by Peter

created 2021-06-28 00:14 UTC

PostgreSQLTest::testUpsert() B

↳ Parent: PostgreSQLTest

Complexity

Conditions	5
Paths	53

Size

Total Lines	71
Code Lines	49

Duplication

Lines	0
Ratio	0 %

Importance

Changes	3
Bugs	0	Features	0

Metric	Value
cc	5
eloc	49
nc	53
nop	0
dl	0
loc	71
rs	8.8016
c	3
b	0
f	0

How to fix Long Method

<?php

declare(strict_types=1);

namespace QB\Tests;

use PDO;
use PHPUnit\Framework\TestCase;
use QB\Generic\Clause\Column;
use QB\Generic\Clause\Table;
use QB\Generic\Expr\Expr;
use QB\PDOHelper;
use QB\PostgreSQL\Factory\Factory;

class PostgreSQLTest extends TestCase
{
    /** @var Factory */
    protected Factory $sut;

    protected \PDO $pdo;

    public function setUp(): void
    {
        if (!array_key_exists('POSTGRES_USER', $_ENV)) {
            $this->markTestSkipped('no db');
        }

        $this->sut = new Factory();

        $dns      = sprintf(
            'pgsql:dbname=%s;host=%s',
            $_ENV['POSTGRES_DB'],
            'postgres'
        );
        $username = $_ENV['POSTGRES_USER'];
        $password = $_ENV['POSTGRES_PASSWORD'];
        $options  = null;

        $this->pdo = new \PDO($dns, $username, $password, $options);
    }

    public function testSelectOneCustomer()
    {
        $sql = (string)$this->sut->select()
            ->addFrom('customers')
            ->addColumns('customerName')
            ->setLimit(1);

        $statement = $this->pdo->query($sql);

        $this->assertSame('Atelier graphique', $statement->fetchColumn());
    }

    public function testSelectComplex()
    {
        $limit = 5;

        $columnQuery = $this->sut->select()
            ->addFrom(new Table('employees', 'boss'))
            ->addColumns('lastName')
            ->addWhere(new Expr('boss.employeeNumber = employees.reportsTo'));

        $customerTypeColumn = new Column(new Expr("'customer'"), 'type');
        $employeeTypeColumn = new Column(new Expr("'employee'"), 'type');

        $unionQuery = $this->sut->select()
            ->addFrom('customers')
            ->addColumns('contactLastName', 'NULL', $customerTypeColumn);

        $query = $this->sut->select()
            ->addFrom('employees')
            ->addColumns('lastName', new Column($columnQuery, 'bossLastName'), $employeeTypeColumn)
            ->addInnerJoin('offices', 'employees.officeCode = o.officeCode', 'o')
            ->addWhere(new Expr('employees.jobTitle = ?', ['Sales Rep']))
            ->addWhere('o.city = \'NYC\'')
            ->addUnion($unionQuery)
            ->setOuterOrderBy('type', 'DESC')
            ->setOuterOrderBy('lastName')
            ->setOuterLimit($limit);

        $this->assertCount($limit, PDOHelper::fetchAll($this->pdo, $query, PDO::FETCH_ASSOC));
    }

    public function testInsertUpdateDelete()
    {
        try {
            $this->pdo->exec('BEGIN');

            // INSERT
            $query = $this->sut->insert()
                ->setInto(new Table('offices'))
                ->setColumns('officeCode', 'city', 'phone', 'addressLine1', 'country', 'postalCode', 'territory')
                ->addValues('abc', 'Berlin', '+49 101 123 4567', '', 'Germany', '10111', 'NA');

            $statement = $this->pdo->prepare((string)$query);

            $result = $statement->execute($query->getValues());
            $this->assertTrue($result);

            // UPDATE
            $query = $this->sut->update()
                ->addFrom(new Table('offices'))
                ->setValues(['territory' => 'Berlin'])
                ->addWhere('officeCode = \'oc\'');

            $this->assertTrue(PDOHelper::execute($this->pdo, $query));

            // DELETE
            $query = $this->sut->delete()
                ->addFrom(new Table('offices'))
                ->addWhere(new Expr('officeCode = ?', ['abc']));

            $this->assertTrue(PDOHelper::execute($this->pdo, $query));

            // COMMIT
            $this->pdo->exec('COMMIT');
        } catch (\Exception $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->exec('ROLLBACK');
            }
            $this->fail($e->getMessage() . PHP_EOL . $e->getTraceAsString());
        }
    }

    public function testUpsert()
    {
        try {
            $this->pdo->exec('BEGIN');

            // INSERT
            $query = $this->sut->insert()
                ->setInto(new Table('offices'))
                ->setColumns('officeCode', 'city', 'phone', 'addressLine1', 'country', 'postalCode', 'territory')
                ->addValues('abc', 'Berlin', '+49 101 123 4567', '', 'Germany', '10111', 'NA')
                ->addValues('bcd', 'Budapest', '+36 70 101 1234', '', 'Hungary', '1011', 'NA')

                ->setReturning('*');
            $sql   = (string)$query;

            $statement = $this->pdo->prepare($sql);

            $result = $statement->execute($query->getValues());
            $this->assertTrue($result);

            $values = $statement->fetchAll(\PDO::FETCH_ASSOC);

            $expectedValues = [
                [
                    'officecode'   => 'abc',
                    'city'         => 'Berlin',
                    'phone'        => '+49 101 123 4567',
                    'addressline1' => '',
                    'addressline2' => null,
                    'state'        => null,
                    'country'      => 'Germany',
                    'postalcode'   => '10111',
                    'territory'    => 'NA',
                ],
                [
                    'officecode'   => 'bcd',
                    'city'         => 'Budapest',
                    'phone'        => '+36 70 101 1234',
                    'addressline1' => '',
                    'addressline2' => null,
                    'state'        => null,
                    'country'      => 'Hungary',
                    'postalcode'   => '1011',
                    'territory'    => 'NA',
                ],
            ];
            $this->assertSame($expectedValues, $values);

            // DELETE
            $query = $this->sut->delete()
                ->addFrom(new Table('offices'))
                ->addWhere(new Expr('officeCode IN (?)', [['abc', 'bcd']]));

            $statement = $this->pdo->prepare((string)$query);
            foreach ($query->getParams() as $param => $var) {
                if (is_numeric($param)) {
                    $statement->bindParam($param + 1, $var[0], $var[1]);
                } else {
                    $statement->bindParam($param, $var[0], $var[1]);
                }
            }
            $result = $statement->execute();
            $this->assertTrue($result);

            // COMMIT
            $this->pdo->exec('COMMIT');
        } catch (\Exception $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->exec('ROLLBACK');
            }
            $this->fail($e->getMessage() . PHP_EOL . $e->getTraceAsString());
        }
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace QB\Tests;
6
7			use PDO;
8			use PHPUnit\Framework\TestCase;
9			use QB\Generic\Clause\Column;
10			use QB\Generic\Clause\Table;
11			use QB\Generic\Expr\Expr;
12			use QB\PDOHelper;
13			use QB\PostgreSQL\Factory\Factory;
14
15			class PostgreSQLTest extends TestCase
16			{
17			/** @var Factory */
18			protected Factory $sut;
19
20			protected \PDO $pdo;
21
22			public function setUp(): void
23			{
24			if (!array_key_exists('POSTGRES_USER', $_ENV)) {
25			$this->markTestSkipped('no db');
26			}
27
28			$this->sut = new Factory();
29
30			$dns = sprintf(
31			'pgsql:dbname=%s;host=%s',
32			$_ENV['POSTGRES_DB'],
33			'postgres'
34			);
35			$username = $_ENV['POSTGRES_USER'];
36			$password = $_ENV['POSTGRES_PASSWORD'];
37			$options = null;
38
39			$this->pdo = new \PDO($dns, $username, $password, $options);
40			}
41
42			public function testSelectOneCustomer()
43			{
44			$sql = (string)$this->sut->select()
45			->addFrom('customers')
46			->addColumns('customerName')
47			->setLimit(1);
48
49			$statement = $this->pdo->query($sql);
50
51			$this->assertSame('Atelier graphique', $statement->fetchColumn());
52			}
53
54			public function testSelectComplex()
55			{
56			$limit = 5;
57
58			$columnQuery = $this->sut->select()
59			->addFrom(new Table('employees', 'boss'))
60			->addColumns('lastName')
61			->addWhere(new Expr('boss.employeeNumber = employees.reportsTo'));
62
63			$customerTypeColumn = new Column(new Expr("'customer'"), 'type');
64			$employeeTypeColumn = new Column(new Expr("'employee'"), 'type');
65
66			$unionQuery = $this->sut->select()
67			->addFrom('customers')
68			->addColumns('contactLastName', 'NULL', $customerTypeColumn);
69
70			$query = $this->sut->select()
71			->addFrom('employees')
72			->addColumns('lastName', new Column($columnQuery, 'bossLastName'), $employeeTypeColumn)
73			->addInnerJoin('offices', 'employees.officeCode = o.officeCode', 'o')
74			->addWhere(new Expr('employees.jobTitle = ?', ['Sales Rep']))
75			->addWhere('o.city = \'NYC\'')
76			->addUnion($unionQuery)
77			->setOuterOrderBy('type', 'DESC')
78			->setOuterOrderBy('lastName')
79			->setOuterLimit($limit);
80
81			$this->assertCount($limit, PDOHelper::fetchAll($this->pdo, $query, PDO::FETCH_ASSOC));
82			}
83
84			public function testInsertUpdateDelete()
85			{
86			try {
87			$this->pdo->exec('BEGIN');
88
89			// INSERT
90			$query = $this->sut->insert()
91			->setInto(new Table('offices'))
92			->setColumns('officeCode', 'city', 'phone', 'addressLine1', 'country', 'postalCode', 'territory')
93			->addValues('abc', 'Berlin', '+49 101 123 4567', '', 'Germany', '10111', 'NA');
94
95			$statement = $this->pdo->prepare((string)$query);
96
97			$result = $statement->execute($query->getValues());
98			$this->assertTrue($result);
99
100			// UPDATE
101			$query = $this->sut->update()
102			->addFrom(new Table('offices'))
103			->setValues(['territory' => 'Berlin'])
104			->addWhere('officeCode = \'oc\'');
105
106			$this->assertTrue(PDOHelper::execute($this->pdo, $query));
107
108			// DELETE
109			$query = $this->sut->delete()
110			->addFrom(new Table('offices'))
111			->addWhere(new Expr('officeCode = ?', ['abc']));
112
113			$this->assertTrue(PDOHelper::execute($this->pdo, $query));
114
115			// COMMIT
116			$this->pdo->exec('COMMIT');
117			} catch (\Exception $e) {
118			if ($this->pdo->inTransaction()) {
119			$this->pdo->exec('ROLLBACK');
120			}
121			$this->fail($e->getMessage() . PHP_EOL . $e->getTraceAsString());
122			}
123			}
124
125			public function testUpsert()
126			{
127			try {
128			$this->pdo->exec('BEGIN');
129
130			// INSERT
131			$query = $this->sut->insert()
132			->setInto(new Table('offices'))
133			->setColumns('officeCode', 'city', 'phone', 'addressLine1', 'country', 'postalCode', 'territory')
134			->addValues('abc', 'Berlin', '+49 101 123 4567', '', 'Germany', '10111', 'NA')
135			->addValues('bcd', 'Budapest', '+36 70 101 1234', '', 'Hungary', '1011', 'NA')
136
137			->setReturning('*');
138			$sql = (string)$query;
139
140			$statement = $this->pdo->prepare($sql);
141
142			$result = $statement->execute($query->getValues());
143			$this->assertTrue($result);
144
145			$values = $statement->fetchAll(\PDO::FETCH_ASSOC);
146
147			$expectedValues = [
148			[
149			'officecode' => 'abc',
150			'city' => 'Berlin',
151			'phone' => '+49 101 123 4567',
152			'addressline1' => '',
153			'addressline2' => null,
154			'state' => null,
155			'country' => 'Germany',
156			'postalcode' => '10111',
157			'territory' => 'NA',
158			],
159			[
160			'officecode' => 'bcd',
161			'city' => 'Budapest',
162			'phone' => '+36 70 101 1234',
163			'addressline1' => '',
164			'addressline2' => null,
165			'state' => null,
166			'country' => 'Hungary',
167			'postalcode' => '1011',
168			'territory' => 'NA',
169			],
170			];
171			$this->assertSame($expectedValues, $values);
172
173			// DELETE
174			$query = $this->sut->delete()
175			->addFrom(new Table('offices'))
176			->addWhere(new Expr('officeCode IN (?)', [['abc', 'bcd']]));
177
178			$statement = $this->pdo->prepare((string)$query);
179			foreach ($query->getParams() as $param => $var) {
180			if (is_numeric($param)) {
181			$statement->bindParam($param + 1, $var[0], $var[1]);
182			} else {
183			$statement->bindParam($param, $var[0], $var[1]);
184			}
185			}
186			$result = $statement->execute();
187			$this->assertTrue($result);
188
189			// COMMIT
190			$this->pdo->exec('COMMIT');
191			} catch (\Exception $e) {
192			if ($this->pdo->inTransaction()) {
193			$this->pdo->exec('ROLLBACK');
194			}
195			$this->fail($e->getMessage() . PHP_EOL . $e->getTraceAsString());
196			}
197			}
198			}
199

abterphp / qb

Push — main ( b157b0...77200e )

PostgreSQLTest::testUpsert() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like