PostgreSQLTest::testUpsert() - Code Metrics - abterphp/qb - Measure and Improve Code Quality continuously with Scrutinizer

PostgreSQLTest::testUpsert() A
last analyzed 2021-07-05 23:35 UTC

↳ Parent: PostgreSQLTest

Complexity

Conditions	3
Paths	15

Size

Total Lines	55
Code Lines	38

Duplication

Lines	0
Ratio	0 %

Importance

Changes	7
Bugs	0	Features	0

Metric	Value
cc	3
eloc	38
c	7
b	0
f	0
nc	15
nop	0
dl	0
loc	55
rs	9.312

How to fix Long Method

<?php

declare(strict_types=1);

namespace QB\Tests;

use PDO;
use PHPUnit\Framework\TestCase;
use QB\Extra\PDOWrapper;
use QB\Generic\Clause\Column;
use QB\Generic\Clause\Table;
use QB\Generic\Expr\Expr;
use QB\PostgreSQL\QueryBuilder\QueryBuilder;
use QB\PostgreSQL\Statement\Select;

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

    protected PDO $pdo;

    protected PDOWrapper $pdoWrapper;

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

        $this->sut = new QueryBuilder();

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

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

        $this->pdoWrapper = new PDOWrapper($this->pdo);
    }

    public function testSelectOneCustomer()
    {
        $sql = (string)$this->sut->select()
            ->from('customers')
            ->columns('customerName')
            ->limit(1);

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

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

    public function testSelectComplex()
    {
        $limit = 5;

        $columnQuery = $this->sut->select()
            ->from(new Table('employees', 'boss'))
            ->columns('lastName')
            ->where(new Expr('boss.employeeNumber = employees.reportsTo'));

        $customerTypeColumn = new Column(new Expr("'customers'"), 'type');
        $employeeTypeColumn = new Column(new Expr("'employees'"), 'type');

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

        $query = $this->sut->select()
            ->from('employees')
            ->columns('lastName', new Column($columnQuery, 'bossLastName'), $employeeTypeColumn)
            ->innerJoin(new Table('offices', 'o'), 'employees.officeCode = o.officeCode')
            ->where(new Expr('employees.jobTitle = ?', ['Sales Rep']))
            ->where('o.city = \'NYC\'')
            ->union($unionQuery)
            ->outerOrderBy('type', Select::DIRECTION_DESC)
            ->outerOrderBy('lastName')
            ->outerLimit($limit);

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

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

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

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

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

            // UPDATE
            $query = $this->sut->update(new Table('offices'))
                ->values(['territory' => "'Berlin'"])
                ->where("officeCode = 'oc'");

            $this->assertTrue($this->pdoWrapper->execute($query));

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

            $this->assertTrue($this->pdoWrapper->execute($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()
                ->into(new Table('offices'))
                ->columns('officeCode', 'city', 'phone', 'addressLine1', 'country', 'postalCode', 'territory')
                ->values("'abc'", "'Berlin'", "'+49 101 123 4567'", "''", "'Germany'", "'10111'", "'NA'")
                ->values("'bcd'", "'Budapest'", "'+36 70 101 1234'", "''", "'Hungary'", "'1011'", "'NA'")
                ->returning('*');

            $values = $this->pdoWrapper->fetchAll($query, \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()
                ->from(new Table('offices'))
                ->where(new Expr('officeCode IN (?)', [['abc', 'bcd']]));

            $this->assertTrue($this->pdoWrapper->execute($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());
        }
    }
}


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

abterphp / qb

PostgreSQLTest::testUpsert() A last analyzed 2021-07-05 23:35 UTC

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like

PostgreSQLTest::testUpsert() A
last analyzed 2021-07-05 23:35 UTC