Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Database Design Patterns with PHP 5.3

Hugo Hamon
November 21, 2011

Database Design Patterns with PHP 5.3

This session introduces most well known design patterns to build PHP classes and objects that need to store and fetch data from a relational databases. The session will describe the difference between of the Active Record, the Table and Row Data Gateway and the Data Mapper pattern. We will also examine some technical advantages and drawbacks of these implementations. This talk will expose some of the best PHP tools, which ease database interactions and are built on top of these patterns.

Hugo Hamon

November 21, 2011
Tweet

More Decks by Hugo Hamon

Other Decks in Technology

Transcript

  1. Enterprise Database Design
    Patterns in PHP
    Hugo Hamon – OSIDays 2011

    View full-size slide

  2. By Martin Fowler
    §  Table Module
    §  Transaction Script
    §  Row Data Gateway
    §  Table Data Gateway
    §  Active Record
    §  Data Mapper
    §  Unit of Work
    §  Identity Map
    §  Data Transfer Object
    §  …

    View full-size slide

  3. Table Data Gateway

    View full-size slide

  4. « An object that acts as a Gateway to
    a database table. One instance
    handles all the rows in the table. »
    Martin Fowler  

    View full-size slide

  5. Same as
    Data Access Object
    Martin Fowler  

    View full-size slide

  6. $table = new OrderGateway(new Connection('...'));
    $table->insert('XX123456789', 358.80, 'unpaid');
    $table->update(42, 'XX123456789', 358.80, 'paid');
    $table->delete(42);

    View full-size slide

  7. class OrderGateway
    {
    private $conn;
    public function __construct(Connection $conn)
    {
    $this->conn = $conn;
    }
    }

    View full-size slide

  8. class OrderGateway
    {
    public function insert($reference, $amount, $status)
    {
    $query = 'INSERT INTO orders (reference, amount,
    status) VALUES (?, ?, ?)';
    $data = array($reference, $amount, $status);
    $this->conn->executeQuery($query, $data);
    return $this->conn->lastInsertId();
    }
    }

    View full-size slide

  9. class OrderGateway
    {
    public function update($pk, $ref, $amount, $status)
    {
    $query = 'UPDATE orders SET reference = ?, amount
    = ?, status = ? WHERE id = ?';
    $data = array($ref, $amount, $status, $pk);
    return $this->conn->executeQuery($query, $data);
    }
    }

    View full-size slide

  10. class OrderGateway
    {
    public function delete($pk)
    {
    return $this->conn->executeQuery(
    'DELETE FROM orders WHERE id = ?',
    array($pk)
    );
    }
    }

    View full-size slide

  11. $orders = $table->findAll();
    $orders = $table->findPaidOrders();
    $orders = $table->findUnpaidOrders();
    $orders = $table->findBy(array(
    'status' => 'paid',
    'amount' => 250.00
    ));
    $order = $table->find(42);
    $order = $table->findOneBy(array('reference' => '...'));

    View full-size slide

  12. class OrderGateway
    {
    public function findAll()
    {
    $query = 'SELECT * FROM orders';
    return $this->conn->fetchAll($query);
    }
    public function find($pk)
    {
    $rs = $this->conn->findBy(array('id' => $pk));
    return 1 === count($rs) ? $rs[0] : false;
    }
    }

    View full-size slide

  13. public function findBy(array $criteria)
    {
    $where = array();
    foreach ($criteria as $field => $value) {
    $where[] = sprintf('%s = ?');
    }
    $q = sprintf(
    'SELECT * FROM orders WHERE %s',
    implode(' AND ', $where)
    );
    return $this->conn->fetchAll($q, array_values($criteria));
    }

    View full-size slide

  14. public function findPaidOrders()
    {
    return $this->findBy(array('status' => 'paid'));
    }
    public function findUnpaidOrders()
    {
    return $this->findBy(array('status' => 'unpaid'));
    }

    View full-size slide

  15. When to use it?

    View full-size slide

  16. Row Data Gateway

    View full-size slide

  17. « An object that acts as a Gateway to
    a single record in a data source. There
    is one instance per row. »
    Martin Fowler  

    View full-size slide

  18. class Order
    {
    private $id;
    private $reference;
    private $amount;
    private $vat;
    private $total;
    private $createdAt;
    // Getters and setters for each property
    // ...
    }

    View full-size slide

  19. $conn = new Connection('...');
    $order = new OrderGateway();
    $order->setReference('XX12345678');
    $order->setAmount(300.00);
    $order->setVat(58.80);
    $order->setTotal(358.80);
    $order->setCreatedAt(new DateTime());
    $order->insert($conn);

    View full-size slide

  20. class OrderGateway
    {
    public function insert(Connection $conn)
    {
    $query = 'INSERT INTO orders (reference, amount, vat,
    total, created_at) VALUES (?, ?, ?, ?, ?)';
    $data = array(
    $this->reference, $this->amount, $this->vat,
    $this->total, $this->createdAt->format('Y-m-d H:i:s')
    );
    $conn->executeQuery($query, $data);
    $this->id = $conn->lastInsertId();
    }
    }

    View full-size slide

  21. OrderFinder::setConnection($conn);
    $order = OrderFinder::findByReference('XX12345678');
    echo sprintf('%01.2f euros', $order->getTotal());

    View full-size slide

  22. class OrderFinder
    {
    static public function findByReference($reference)
    {
    $query = 'SELECT * FROM orders WHERE reference = ?';
    $rs = static::getConnection()
    ->fetchSingle($query, array($reference))
    ;
    return $rs ? OrderGateway::load($rs) : false;
    }
    }

    View full-size slide

  23. class OrderGateway
    {
    static public function load(array $rs)
    {
    $order = new OrderGateway($rs['id']);
    $order->setReference($rs['reference']);
    $order->setAmount($rs['amount']);
    $order->setVat($rs['vat']);
    $order->setTotal($rs['total']);
    $order->setCreatedAt(new DateTime($rs['created_at']));
    return $order;
    }
    }

    View full-size slide

  24. OrderFinder::setConnection($conn);
    $orders = OrderFinder::findMostExpensiveOrders(10);
    foreach ($orders as $order) {
    echo $order->getReference(), "\n";
    echo sprintf('%01.2f euros', $order->getTotal()), "\n";
    echo "\n-----\n";
    }

    View full-size slide

  25. class OrderFinder
    {
    static public function findMostExpensiveOrders($limit)
    {
    $orders = array();
    $query = 'SELECT * FROM orders ORDER BY total DESC LIMIT ?';
    $rs = static::getConnection()->fetchAll($query, array($limit));
    foreach ($rs as $data) {
    $orders[] = OrderGateway::load($data);
    }
    return $orders;
    }
    }

    View full-size slide

  26. When to use it?

    View full-size slide

  27. Active Record

    View full-size slide

  28. « An object that wraps a row in a
    database table or view, encapsulates
    the database access, and adds
    domain logic on that data. »
    Martin Fowler  

    View full-size slide

  29. Active Record
    =
    Row Data Gateway + Business Logic

    View full-size slide

  30. Active Record
    =
    Data + Behaviors

    View full-size slide

  31. Active Record
    =
    Properties + Methods

    View full-size slide

  32. class Order
    {
    private $id;
    private $reference;
    private $amount;
    private $vat;
    private $vatRate;
    private $total;
    private $createdAt;
    private $status;
    private $isPaid;
    // Getters and setters for each property
    // ...
    }

    View full-size slide

  33. class Order
    {
    public function __construct($id = null)
    {
    if (null !== $id) {
    $this->id = $id;
    }
    $this->vatRate = 0.00;
    $this->vat = 0.00;
    $this->amount = 0.00;
    $this->total = 0.00;
    $this->isPaid = false;
    $this->status = 'processing';
    $this->createdAt = new DateTime();
    }
    }

    View full-size slide

  34. $conn = new Connection('...');
    $order = new Order();
    $order->setReference('XX12345678');
    $order->setAmount(300.00);
    $order->setVatRate(0.196);
    $order->applyDiscount(20.00);
    $order->updateTotal();
    $order->save($conn);

    View full-size slide

  35. class Order
    {
    public function applyDiscount($discount)
    {
    $this->amount -= $discount;
    }
    public function updateTotal()
    {
    if ($this->vatRate) {
    $this->vat = $this->amount * $this->vatRate;
    }
    $this->total = $this->amount + $this->vat;
    }
    }

    View full-size slide

  36. class Order
    {
    public function isPaid()
    {
    return $this->isPaid;
    }
    public function setPaid()
    {
    $this->isPaid = true;
    }
    }

    View full-size slide

  37. class Order
    {
    public function isReadyForShipment()
    {
    return $this->isPaid() && 'complete' == $this->status;
    }
    public function ship($address)
    {
    $this->doShipment($address);
    $this->status = 'shipped';
    }
    }

    View full-size slide

  38. class OrderController
    {
    public function confirmAction($reference)
    {
    $conn = $this->getDatabaseConnection();
    $order = ...;
    $order->setPaid();
    $order->save($conn);
    if ($order->isReadyForShipment()) {
    $order->ship();
    return $this->view->render('ship.php', array('order' => $order));
    }
    return $this->view->render('pending.php', array('order' => $order));
    }
    }

    View full-size slide

  39. abstract class ActiveRecord
    {
    protected $fields = array();
    abstract public function getTableName();
    public function save(Connection $conn)
    {
    // insert or update $fields in the database
    }
    public function delete(Connection $conn)
    {
    // delete the object from the database
    }
    }

    View full-size slide

  40. class Order extends ActiveRecord
    {
    private $amount;
    abstract public function getTableName()
    {
    return 'tbl_orders';
    }
    public function setAmount($amount)
    {
    $this->amount = $amount;
    $this->fields['amount'] = $amount;
    }
    }

    View full-size slide

  41. When to use it?

    View full-size slide

  42. « A layer of Mappers that moves data
    between objects and a database
    while keeping them independent of
    each other and the mapper itself. »
    Martin Fowler  

    View full-size slide

  43. « Man in the Middle »

    View full-size slide

  44. http://martinfowler.com  

    View full-size slide

  45. class OrderMapper
    {
    private $conn;
    public function __construct(Connection $conn) {
    $this->conn = $conn;
    }
    public function store(Order $order) {
    // Execute the query to persist the object to the DB
    }
    public function remove(Order $order) {
    // Executes the query to remove the object to the DB
    }
    }

    View full-size slide

  46. $order = new Order();
    $order->setReference('XX12345678');
    $order->setAmount(300.00);
    $order->setVatRate(0.196);
    $order->updateTotal();
    $conn = new Connection('mysql:host=localhost ...');
    $mapper = new OrderMapper($conn);
    $mapper->store($order);

    View full-size slide

  47. class OrderMapper
    {
    public function findAll()
    {
    $objects = array();
    $query = 'SELECT id, reference, vat ... FROM orders';
    foreach ($this->conn->fetchAll($query) as $data) {
    $object = new Order($data['id']);
    $object->load($data);
    $objects[] = $object;
    }
    return $objects;
    }
    }

    View full-size slide

  48. class OrderMapper
    {
    public function find($pk)
    {
    $query = 'SELECT id, vat ... FROM orders WHERE id = ?';
    $object = false;
    if (false !== $data = conn->fetch($query, array($pk))) {
    $object = new Order($data['id']);
    $object->load($data);
    }
    return $object;
    }
    }

    View full-size slide

  49. $conn = new Connection('mysql:host=localhost ...');
    $mapper = new OrderMapper($conn);
    $order = $mapper->find(42);
    $order->setAmount(399.00);
    $order->updateTotal();
    $mapper->store($order);

    View full-size slide

  50. Unit testing

    View full-size slide

  51. class OrderTest extends PHPUnit_Framework_TestCase
    {
    public function testUpdateTotal()
    {
    $order = new Order();
    $order->setAmount(299.00);
    $order->setVatRate(0.196);
    $order->updateTotal();
    $this->assertEquals(58.60, $order->getVat());
    $this->assertEquals(357.60, $order->getTotal());
    }
    }

    View full-size slide

  52. When to use it?

    View full-size slide

  53. « Ensures that each object gets
    loaded only once by keeping every
    loaded object in a map. »
    Martin Fowler  

    View full-size slide

  54. $conn = new Connection('mysql:host=localhost ...');
    $mapper = new OrderMapper($conn);
    $orderA = $mapper->find(42);
    $orderB = $mapper->find(42);
    $orderC = $mapper->find(42);
    // 3 SQL queries for getting the same object

    View full-size slide

  55. The solution

    View full-size slide

  56. class IdentityMap implements IdentityMapInterface
    {
    private $entities;
    public function fetch($class, $pk)
    {
    $key = $this->getKey($class, $pk);
    if (isset($this->entities[$key])) {
    return $this->entities[$key];
    }
    return false;
    }
    }

    View full-size slide

  57. class IdentityMap implements IdentityMapInterface
    {
    public function store(ValueObjectInterface $entity)
    {
    $key = $this->getKey($class, $entity->getId());
    $this->entities[$key] = $entity;
    }
    private function getKey($class, $pk)
    {
    return $class.'-'.$pk;
    }
    }

    View full-size slide

  58. class Order implements ValueObjectInterface
    {
    private $id;
    private $reference;
    private $amount;
    // ...
    public function getId()
    {
    return $this->id;
    }
    }

    View full-size slide

  59. class OrderMapper extends DatabaseMapper
    {
    private $map;
    public function __construct(IdentityMap $map, ...)
    {
    parent::__construct($conn);
    $this->map = $map;
    }
    }

    View full-size slide

  60. class OrderMapper extends DatabaseMapper
    {
    public function store(Order $order)
    {
    parent::store($order);
    $this->map->store('Order', $object);
    }
    }

    View full-size slide

  61. class OrderMapper extends DatabaseMapper
    {
    public function find($pk)
    {
    if (false !== $object = $this->map->fetch($pk)) {
    return $object;
    }
    if (false !== $object = parent::find($pk)) {
    $this->map->store('Order', $object);
    }
    return $object;
    }
    }

    View full-size slide

  62. $conn = new Connection('mysql:host=localhost ...');
    $mapper = new OrderMapper(new IdentityMap(), $conn);
    $orderA = $mapper->find(42); // Query
    $orderB = $mapper->find(42); // No query
    $orderB->setAmount(299.00);
    $orderB->setVatRate(0.196);
    $orderB->updateTotal();
    $mapper->store($orderB);
    $orderC = $mapper->find(42); // No query

    View full-size slide

  63. Query Object

    View full-size slide

  64. $query = Query::create()
    ->select(array('id', 'reference', 'amount', 'status'))
    ->from('orders')
    ->where(Criteria::equals('status', 'paid'))
    ->where(Criteria::greaterThan('amount', 2000))
    ->where(Criteria::like('reference', 'XX123%'))
    ->orderBy('amount', 'desc')
    ->getSql()
    ;
    // SELECT id, reference, amount, status
    // WHERE status = ? AND amount > ? AND reference LIKE ?
    // ORDER BY amount DESC

    View full-size slide

  65. class Criteria
    {
    private $field;
    private $operator;
    private $parameters;
    public function __construct($field, $operator, $value)
    {
    $this->field = $field;
    $this->operator = $operator;
    $this->parameters[] = $value;
    }
    }

    View full-size slide

  66. class Criteria
    {
    static public function equal($field, $value, $vars)
    {
    return new Criteria($field, '=', $vars);
    }
    static public function notEqual($field, $value, $vars)
    {
    return new Criteria($field, '<>', $vars);
    }
    }

    View full-size slide

  67. Custom Queries

    View full-size slide

  68. class OrderQuery extends Query
    {
    public function filterByPriority($amount)
    {
    return $this
    ->where(Criteria::equal('status', 'paid'))
    ->where(Criteria::greaterThan('amount', $amount))
    ;
    }
    public function filterByReference($like)
    {
    return $this->where(Criteria::like('reference', $like));
    }
    }

    View full-size slide

  69. $query = OrderQuery::create()
    ->filterByPriority(2000)
    ->filterByReference('%567%')
    ->orderByAmount('DESC')
    ->getSql()
    ;

    View full-size slide

  70. Zend_DB
    Doctrine 1.2
    Doctrine 2.x
    Propel
    Pomm

    View full-size slide

  71. Quizz
    Can you guess the patterns?

    View full-size slide

  72. $table = new Author();
    // New empty row
    $row = $table->createRow();
    // Insert a new row
    $row->firstName = 'Jules';
    $row->lastName = 'Verne';
    $row->save();

    View full-size slide

  73. $pax1 = new Passenger('Hugo Hamon', '7B');
    $pax2 = new Passenger('John Smith', '3A');
    $aircraft = new Plane();
    $aircraft->setCapacity(120);
    $aircraft->addPassenger($pax1);
    $aircraft->addPassenger($pax2);
    $aircraft->save();
    $pax2->changeSeat('2C');
    $pax2->save();
    $aircraft->isAvailableSeat('3A') ? 'Yes' : 'No';

    View full-size slide

  74. $post = new BlogPost();
    $post->setTitle('My First Blog Post');
    $post->setBody('Some content...');
    $author = new Author();
    $author->setName('Hugo Hamon');
    $author->addPost($post);
    $em->persist($user);
    $em->persist($post);
    $em->flush();

    View full-size slide

  75. $data = array(
    'first_name' => 'Jules',
    'last_name' => 'Vernes',
    );
    $table = new AuthorTable();
    $table->insert($data);

    View full-size slide

  76. By Martin Fowler
    §  Table Module
    §  Transaction Script
    §  Row Data Gateway
    §  Table Data Gateway
    §  Active Record
    §  Data Mapper
    §  Unit of Work
    §  Identity Map
    §  Data Transfer Object
    §  …

    View full-size slide

  77. Ques&ons?  
    92-98, boulevard Victor Hugo
    92 115 Clichy Cedex
    [email protected] (+33 (0)1 40 99 82 11)
    sensiolabs.com - symfony.com – trainings.sensiolabs.com

    View full-size slide