MySQLTest::testSelectWithCalcFoundRows() - 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

MySQLTest::testSelectWithCalcFoundRows() A

↳ Parent: MySQLTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	20
Code Lines	12

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	12
c	0
b	0
f	0
nc	1
nop	0
dl	0
loc	20
rs	9.8666

<?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\MySQL\Factory\Factory;
use QB\MySQL\Statement\Select;

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

    protected \PDO $pdo;

    public function setUp(): void
    {
        $this->sut = new Factory();

        $dns      = sprintf(
            'mysql:dbname=%s;host=%s',
            $_ENV['MYSQL_DATABASE'],
            'mysql'
        );
        $username = $_ENV['MYSQL_USER'];
        $password = $_ENV['MYSQL_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));
    }

    /**
     * @deprecated feature, reasons below
     *
     * The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17
     * and will be removed in a future MySQL version.
     *
     * COUNT(*) is subject to certain optimizations. SQL_CALC_FOUND_ROWS causes some optimizations to be disabled.
     *
     * Use these queries instead:
     */
    public function testSelectWithCalcFoundRows()
    {
        $limit = 5;

        $sql = (string)$this->sut->select()
            ->addFrom('customers')
            ->addModifier(Select::SQL_CALC_FOUND_ROWS)
            ->addColumns('customerName')
            ->setLimit($limit);

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

        $this->assertCount($limit, $statement->fetchAll());

        $sql = (string)$this->sut->select()
            ->addColumn(new Expr('FOUND_ROWS()'));

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

        $this->assertGreaterThan($limit, (int)$statement->fetchColumn());
    }

    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());
        }
    }
}


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\MySQL\Factory\Factory;
12			use QB\MySQL\Statement\Select;
13
14			class MySQLTest 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			'mysql:dbname=%s;host=%s',
27			$_ENV['MYSQL_DATABASE'],
28			'mysql'
29			);
30			$username = $_ENV['MYSQL_USER'];
31			$password = $_ENV['MYSQL_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			/**
91			* @deprecated feature, reasons below
92			*
93			* The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17
94			* and will be removed in a future MySQL version.
95			*
96			* COUNT(*) is subject to certain optimizations. SQL_CALC_FOUND_ROWS causes some optimizations to be disabled.
97			*
98			* Use these queries instead:
99			*/
100			public function testSelectWithCalcFoundRows()
101			{
102			$limit = 5;
103
104			$sql = (string)$this->sut->select()
105			->addFrom('customers')
106			->addModifier(Select::SQL_CALC_FOUND_ROWS)
107			->addColumns('customerName')
108			->setLimit($limit);
109
110			$statement = $this->pdo->query($sql);
111
112			$this->assertCount($limit, $statement->fetchAll());
113
114			$sql = (string)$this->sut->select()
115			->addColumn(new Expr('FOUND_ROWS()'));
116
117			$statement = $this->pdo->query($sql);
118
119			$this->assertGreaterThan($limit, (int)$statement->fetchColumn());
120			}
121
122			public function testInsertUpdateDelete()
123			{
124			try {
125			$this->pdo->exec('BEGIN');
126
127			// INSERT
128			$query = $this->sut->insert()
129			->setInto(new Table('offices'))
130			->addColumns('officeCode', 'city', 'phone', 'addressLine1', 'country', 'postalCode', 'territory')
131			->addValues('abc', 'Berlin', '+49 101 123 4567', '', 'Germany', '10111', 'NA');
132
133			$statement = $this->pdo->prepare((string)$query);
134
135			$result = $statement->execute($query->getValues());
136			$this->assertTrue($result);
137
138			// UPDATE
139			$query = $this->sut->update()
140			->addFrom(new Table('offices'))
141			->setValues(['territory' => 'Berlin'])
142			->addWhere('officeCode = \'oc\'');
143
144			$num = 1;
145			$sql = (string)$query;
146			$values = $query->getValues();
147			$params = $query->getParams();
148
149			$statement = $this->pdo->prepare($sql);
150			foreach ($values as $value) {
151			$statement->bindParam($num++, $value);
152			}
153			foreach ($params as $k => $v) {
154			if (is_numeric($k)) {
155			$statement->bindParam($num++, $v[0], $v[1]);
156			} else {
157			$statement->bindParam($k, $v[0], $v[1]);
158			}
159			}
160			$result = $statement->execute($values);
161			$this->assertTrue($result);
162
163			// DELETE
164			$query = $this->sut->delete()
165			->addFrom(new Table('offices'))
166			->addWhere(new Expr('officeCode = ?', ['abc']));
167
168			$sql = (string)$query;
169			$params = $query->getParams();
170
171			$statement = $this->pdo->prepare($sql);
172			foreach ($params as $k => $v) {
173			if (is_numeric($k)) {
174			$statement->bindParam($k + 1, $v[0], $v[1]);
175			} else {
176			$statement->bindParam($k, $v[0], $v[1]);
177			}
178			}
179			$result = $statement->execute();
180			$this->assertTrue($result);
181
182			// COMMIT
183			$this->pdo->exec('COMMIT');
184			} catch (\Exception $e) {
185			if ($this->pdo->inTransaction()) {
186			$this->pdo->exec('ROLLBACK');
187			}
188			$this->fail($e->getMessage() . PHP_EOL . $e->getTraceAsString());
189			}
190			}
191			}
192

abterphp / qb

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

MySQLTest::testSelectWithCalcFoundRows() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like