Formation Apprendre le PHP : Exercice : QueryBuilder

Maintenant que l'on a vu comment tester le code et utiliser les tests unitaires je vous propose un exercice pratique sur la création d'une classe permettant de générer des requêtes SQL: un QueryBuilder. Pour cet exercice je vous donne les tests et c'est à vous d'essayer d'écrire le code pour les faire fonctionner.

<?php

use PHPUnit\Framework\TestCase;

final class QueryBuilderTest extends TestCase
{

    public function getBuilder(): \App\QueryBuilder
    {
        return new \App\QueryBuilder();
    }

    public function getPDO(): PDO
    {
        $pdo = new PDO("sqlite::memory:");
        $pdo->query('CREATE TABLE products (
    id INTEGER CONSTRAINT products_pk primary key autoincrement,
    name TEXT,
    address TEXT,
    city TEXT)');
        for ($i = 1; $i <= 10; $i++) {
            $pdo->exec("INSERT INTO products (name, address, city) VALUES ('Product $i', 'Addresse $i', 'Ville $i');");
        }
        return $pdo;
    }

    public function testSimpleQuery()
    {
        $q = $this->getBuilder()->from("users", "u")->toSQL();
        $this->assertEquals("SELECT * FROM users u", $q);
    }

    public function testOrderBy()
    {
        $q = $this->getBuilder()->from("users", "u")->orderBy("id", "DESC")->toSQL();
        $this->assertEquals("SELECT * FROM users u ORDER BY id DESC", $q);
    }

    public function testMultipleOrderBy()
    {
        $q = $this->getBuilder()
            ->from("users")
            ->orderBy("id", "ezaearz")
            ->orderBy("name", "DESC")
            ->toSQL();
        $this->assertEquals("SELECT * FROM users ORDER BY id, name DESC", $q);
    }

    public function testLimit()
    {
        $q = $this->getBuilder()
            ->from("users")
            ->limit(10)
            ->orderBy("id", "DESC")
            ->toSQL();
        $this->assertEquals("SELECT * FROM users ORDER BY id DESC LIMIT 10", $q);
    }

    public function testOffset()
    {
        $q = $this->getBuilder()
            ->from("users")
            ->limit(10)
            ->offset(3)
            ->orderBy("id", "DESC")
            ->toSQL();
        $this->assertEquals("SELECT * FROM users ORDER BY id DESC LIMIT 10 OFFSET 3", $q);
    }

    public function testPage()
    {
        $q = $this->getBuilder()
            ->from("users")
            ->limit(10)
            ->page(3)
            ->orderBy("id", "DESC")
            ->toSQL();
        $this->assertEquals("SELECT * FROM users ORDER BY id DESC LIMIT 10 OFFSET 20", $q);
        $q = $this->getBuilder()
            ->from("users")
            ->limit(10)
            ->page(1)
            ->orderBy("id", "DESC")
            ->toSQL();
        $this->assertEquals("SELECT * FROM users ORDER BY id DESC LIMIT 10 OFFSET 0", $q);
    }

    public function testCondition()
    {
        $q = $this->getBuilder()
            ->from("users")
            ->where("id > :id")
            ->setParam("id", 3)
            ->limit(10)
            ->orderBy("id", "DESC")
            ->toSQL();
        $this->assertEquals("SELECT * FROM users WHERE id > :id ORDER BY id DESC LIMIT 10", $q);
    }

    public function testSelect()
    {
        $q = $this->getBuilder()
            ->select("id", "name", "product")
            ->from("users");
        $this->assertEquals("SELECT id, name, product FROM users", $q->toSQL());
    }

    public function testSelectMultiple()
    {
        $q = $this->getBuilder()
            ->select("id", "name")
            ->from("users")
            ->select('product');
        $this->assertEquals("SELECT id, name, product FROM users", $q->toSQL());
    }

    public function testSelectAsArray()
    {
        $q = $this->getBuilder()
            ->select(["id", "name", "product"])
            ->from("users");
        $this->assertEquals("SELECT id, name, product FROM users", $q->toSQL());
    }

    public function testFetch()
    {
        $city = $this->getBuilder()
            ->from("products")
            ->where("name = :name")
            ->setParam("name", "Product 1")
            ->fetch($this->getPDO(), "city");
        $this->assertEquals("Ville 1", $city);
    }

    public function testFetchWithInvalidRow()
    {
        $city = $this->getBuilder()
            ->from("products")
            ->where("name = :name")
            ->setParam("name", "azezaeeazazzaez")
            ->fetch($this->getPDO(), "city");
        $this->assertNull($city);
    }
    public function testCount()
    {
        $query = $this->getBuilder()
            ->from("products")
            ->where("name IN (:name1, :name2)")
            ->setParam("name1", "Product 1")
            ->setParam("name2", "Product 2");
        $this->assertEquals(2, $query->count($this->getPDO()));
    }

    /**
     * L'appel a count ne doit pas modifier les champs de la première requête
     */
    public function testBugCount()
    {
        $q = $this->getBuilder()->from("products");
        $this->assertEquals(10, $q->count($this->getPDO()));
        $this->assertEquals("SELECT * FROM products", $q->toSQL());
    }

}