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