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

Database Design Patterns with PHP 5.3

E2ed7c278c8c49bb3e7fe0b7de039997?s=47 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.

E2ed7c278c8c49bb3e7fe0b7de039997?s=128

Hugo Hamon

November 21, 2011
Tweet

Transcript

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

    2011
  3. 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 §  …
  4. Table Data Gateway

  5. « An object that acts as a Gateway to a

    database table. One instance handles all the rows in the table. » Martin Fowler  
  6. Same as Data Access Object Martin Fowler  

  7. CRUD

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

    358.80, 'paid'); $table->delete(42);
  9. class OrderGateway { private $conn; public function __construct(Connection $conn) {

    $this->conn = $conn; } }
  10. 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(); } }
  11. 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); } }
  12. class OrderGateway { public function delete($pk) { return $this->conn->executeQuery( 'DELETE

    FROM orders WHERE id = ?', array($pk) ); } }
  13. Finders

  14. $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' => '...'));
  15. 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; } }
  16. 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)); }
  17. public function findPaidOrders() { return $this->findBy(array('status' => 'paid')); } public

    function findUnpaidOrders() { return $this->findBy(array('status' => 'unpaid')); }
  18. When to use it?

  19. Row Data Gateway

  20. « An object that acts as a Gateway to a

    single record in a data source. There is one instance per row. » Martin Fowler  
  21. CRUD

  22. class Order { private $id; private $reference; private $amount; private

    $vat; private $total; private $createdAt; // Getters and setters for each property // ... }
  23. $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);
  24. 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(); } }
  25. Finders

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

  27. 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; } }
  28. 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; } }
  29. 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"; }
  30. 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; } }
  31. When to use it?

  32. Active Record

  33. « 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  
  34. Active Record = Row Data Gateway + Business Logic

  35. Active Record = Data + Behaviors

  36. Active Record = Properties + Methods

  37. 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 // ... }
  38. 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(); } }
  39. $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);
  40. 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; } }
  41. class Order { public function isPaid() { return $this->isPaid; }

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

    'complete' == $this->status; } public function ship($address) { $this->doShipment($address); $this->status = 'shipped'; } }
  43. 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)); } }
  44. Refactoring

  45. 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 } }
  46. class Order extends ActiveRecord { private $amount; abstract public function

    getTableName() { return 'tbl_orders'; } public function setAmount($amount) { $this->amount = $amount; $this->fields['amount'] = $amount; } }
  47. When to use it?

  48. Data Mapper

  49. « 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  
  50. « Man in the Middle »

  51. http://martinfowler.com  

  52. 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 } }
  53. $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);
  54. 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; } }
  55. 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; } }
  56. $conn = new Connection('mysql:host=localhost ...'); $mapper = new OrderMapper($conn); $order

    = $mapper->find(42); $order->setAmount(399.00); $order->updateTotal(); $mapper->store($order);
  57. Unit testing

  58. 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()); } }
  59. When to use it?

  60. IdentityMap

  61. « Ensures that each object gets loaded only once by

    keeping every loaded object in a map. » Martin Fowler  
  62. $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
  63. The solution

  64. 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; } }
  65. 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; } }
  66. class Order implements ValueObjectInterface { private $id; private $reference; private

    $amount; // ... public function getId() { return $this->id; } }
  67. class OrderMapper extends DatabaseMapper { private $map; public function __construct(IdentityMap

    $map, ...) { parent::__construct($conn); $this->map = $map; } }
  68. class OrderMapper extends DatabaseMapper { public function store(Order $order) {

    parent::store($order); $this->map->store('Order', $object); } }
  69. 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; } }
  70. $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
  71. Query Object

  72. $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
  73. class Criteria { private $field; private $operator; private $parameters; public

    function __construct($field, $operator, $value) { $this->field = $field; $this->operator = $operator; $this->parameters[] = $value; } }
  74. 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); } }
  75. Custom Queries

  76. 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)); } }
  77. $query = OrderQuery::create() ->filterByPriority(2000) ->filterByReference('%567%') ->orderByAmount('DESC') ->getSql() ;

  78. ORM Tools

  79. Zend_DB Doctrine 1.2 Doctrine 2.x Propel Pomm

  80. Quizz Can you guess the patterns?

  81. $table = new Author(); // New empty row $row =

    $table->createRow(); // Insert a new row $row->firstName = 'Jules'; $row->lastName = 'Verne'; $row->save();
  82. $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';
  83. $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();
  84. $data = array( 'first_name' => 'Jules', 'last_name' => 'Vernes', );

    $table = new AuthorTable(); $table->insert($data);
  85. Conclusion

  86. 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 §  …
  87. Ques&ons?   92-98, boulevard Victor Hugo 92 115 Clichy Cedex

    trainings@sensio.com (+33 (0)1 40 99 82 11) sensiolabs.com - symfony.com – trainings.sensiolabs.com