Database Design Patterns

Enterprise Database Design
Patterns in PHP
Hugo Hamon – OSIDays 2011

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

« An object that acts as a Gateway to

a database table. One instance

handles all the rows in the table. »
Martin Fowler

Same as
Data Access Object
Martin Fowler

$table = new OrderGateway(new Connection('...'));

$table->insert('XX123456789', 358.80, 'unpaid');

$table->update(42, 'XX123456789', 358.80, 'paid');

$table->delete(42);

class OrderGateway
{
private $conn;

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

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

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

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

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

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

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

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

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

« An object that acts as a Gateway to

a single record in a data source. There

is one instance per row. »
Martin Fowler

class Order
{
private $id;
private $reference;
private $amount;
private $vat;
private $total;
private $createdAt;

// Getters and setters for each property
// ...
}

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

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

OrderFinder::setConnection($conn);

$order = OrderFinder::findByReference('XX12345678');

echo sprintf('%01.2f euros', $order->getTotal());

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

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

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

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

« 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

Active Record
=
Row Data Gateway + Business Logic

Active Record
=
Data + Behaviors

Active Record
=
Properties + Methods

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

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

$conn = new Connection('...');

$order = new Order();
$order->setVatRate(0.196);

$order->applyDiscount(20.00);
$order->updateTotal();

$order->save($conn);

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

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

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

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

public function ship($address)
{
$this->doShipment($address);
$this->status = 'shipped';
}
}

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

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

class Order extends ActiveRecord
{
private $amount;

abstract public function getTableName()
{
return 'tbl_orders';
}

public function setAmount($amount)
{
$this->amount = $amount;
$this->fields['amount'] = $amount;
}
}

« 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

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


$conn = new Connection('mysql:host=localhost ...');

$mapper = new OrderMapper($conn);
$mapper->store($order);

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

class OrderMapper
{
{
$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;
}
}



$order = $mapper->find(42);


$mapper->store($order);

class OrderTest extends PHPUnit_Framework_TestCase
{
public function testUpdateTotal()
{

$this->assertEquals(58.60, $order->getVat());
$this->assertEquals(357.60, $order->getTotal());
}
}

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



$orderA = $mapper->find(42);
$orderB = $mapper->find(42);
$orderC = $mapper->find(42);

// 3 SQL queries for getting the same object

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

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

class Order implements ValueObjectInterface
{
private $id;
private $reference;
private $amount;
// ...

public function getId()
{
return $this->id;
}
}

class OrderMapper extends DatabaseMapper
{
private $map;

public function __construct(IdentityMap $map, ...)
{
parent::__construct($conn);

$this->map = $map;
}
}

{
public function store(Order $order)
{
parent::store($order);

$this->map->store('Order', $object);
}
}

{
{
if (false !== $object = $this->map->fetch($pk)) {
return $object;
}

if (false !== $object = parent::find($pk)) {
$this->map->store('Order', $object);
}

return $object;
}
}

$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

$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

class Criteria
{
private $field;
private $operator;
private $parameters;

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

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

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

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

Zend_DB Propel

Doctrine 2.x

Pomm Doctrine 1.2

Quizz

Can you guess the patterns?

$table = new Author();

// New empty row
$row = $table->createRow();

// Insert a new row
$row->firstName = 'Jules';
$row->lastName = 'Verne';
$row->save();

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

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

$data = array(
'first_name' => 'Jules',
'last_name' => 'Vernes',
);

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

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

Database Design Patterns

More Related Content

What's hot

Viewers also liked

Similar to Database Design Patterns

More from Hugo Hamon

Recently uploaded

Database Design Patterns