PostgreSQLTest::testUpsert() - Code Metrics - Inspection of "PostgreSQL improvements" - abterphp/qb - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( 5e0bcd...9ce40a )

by Peter

created 2021-06-26 23:11 UTC

PostgreSQLTest::testUpsert() B

↳ Parent: PostgreSQLTest

Complexity

Conditions	5
Paths	49

Size

Total Lines	74
Code Lines	51

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	5
eloc	51
c	1
b	0
f	0
nc	49
nop	0
dl	0
loc	74
rs	8.7579

How to fix Long Method

<?php

declare(strict_types=1);

namespace QB\Tests;

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

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

    protected \PDO $pdo;

    public function setUp(): void
    {
        $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)
            ->addOuterOrderBy('type', 'DESC')
            ->addOuterOrderBy('lastName')
            ->setOuterLimit($limit);

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

        $statement->execute();

        $this->assertCount($limit, $statement->fetchAll(\PDO::FETCH_ASSOC));
    }

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

            // INSERT
            $query = $this->sut->insert()
                ->setInto(new Table('offices'))
                ->addColumns('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\'');

            $num    = 1;
            $sql    = (string)$query;
            $values = $query->getValues();
            $params = $query->getParams();

            $statement = $this->pdo->prepare($sql);
            foreach ($values as $value) {
                $statement->bindParam($num++, $value);
            }
            foreach ($params as $k => $v) {
                if (is_numeric($k)) {
                    $statement->bindParam($num++, $v[0], $v[1]);
                } else {
                    $statement->bindParam($k, $v[0], $v[1]);
                }
            }
            $result = $statement->execute($values);
            $this->assertTrue($result);

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

            $sql    = (string)$query;
            $params = $query->getParams();

            $statement = $this->pdo->prepare($sql);
            foreach ($params as $k => $v) {
                if (is_numeric($k)) {
                    $statement->bindParam($k + 1, $v[0], $v[1]);
                } else {
                    $statement->bindParam($k, $v[0], $v[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());
        }
    }

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

            // INSERT
            $query = $this->sut->insert()
                ->setInto(new Table('offices'))
                ->addColumns('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 SuperExpr('officeCode IN (??)', [['abc', 'bcd']], '??'));

            $sql    = (string)$query;
            $params = $query->getParams();

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

abterphp / qb

Push — main ( 5e0bcd...9ce40a )

PostgreSQLTest::testUpsert() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like