Tweet
Tweet

Slide 1

Slide 1 text

No content

Slide 2

Slide 2 text

Enterprise Database Design Patterns in PHP Hugo Hamon – OSIDays 2011

Slide 3

Slide 3 text

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 §  …

Slide 4

Slide 4 text

Table Data Gateway

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

Same as Data Access Object Martin Fowler  

Slide 7

Slide 7 text

CRUD

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

Finders

Slide 14

Slide 14 text

$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' => '...'));

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

When to use it?

Slide 19

Slide 19 text

Row Data Gateway

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

CRUD

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

$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);

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

Finders

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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"; }

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

When to use it?

Slide 32

Slide 32 text

Active Record

Slide 33

Slide 33 text

« 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  

Slide 34

Slide 34 text

Active Record = Row Data Gateway + Business Logic

Slide 35

Slide 35 text

Active Record = Data + Behaviors

Slide 36

Slide 36 text

Active Record = Properties + Methods

Slide 37

Slide 37 text

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 // ... }

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

$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);

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

Refactoring

Slide 45

Slide 45 text

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 } }

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

When to use it?

Slide 48

Slide 48 text

Data Mapper

Slide 49

Slide 49 text

« 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  

Slide 50

Slide 50 text

« Man in the Middle »

Slide 51

Slide 51 text

http://martinfowler.com  

Slide 52

Slide 52 text

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 } }

Slide 53

Slide 53 text

$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);

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

Unit testing

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

When to use it?

Slide 60

Slide 60 text

IdentityMap

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

$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

Slide 63

Slide 63 text

The solution

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

$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

Slide 71

Slide 71 text

Query Object

Slide 72

Slide 72 text

$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

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

Custom Queries

Slide 76

Slide 76 text

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

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

ORM Tools

Slide 79

Slide 79 text

Zend_DB Doctrine 1.2 Doctrine 2.x Propel Pomm

Slide 80

Slide 80 text

Quizz Can you guess the patterns?

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

$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';

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

Conclusion

Slide 86

Slide 86 text

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 §  …

Slide 87

Slide 87 text

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