The Database Access rEvolution (PHP-WVL meetup)

The Database Access rEvolution (PHP-WVL meetup)

In PHP, we have different tools at our disposition to talk to a database. There are also different ways of working with the data coming from that database. Many people take working with a database abstraction layer or ORM for granted. But how do these modern tools work? Why are we doing things the way we are doing them now? By taking back a step and looking at each itteration in the evolution of working with databases in PHP applications, we will try to explain the principles on which our current tools are built.

4ce755c7f3ddf4e0c92e1aeaeea7677b?s=128

Jachim Coudenys

March 24, 2015
Tweet

Transcript

  1. THE DATABASE ACCESS THE DATABASE ACCESS REVOLUTION REVOLUTION & @coudenysj

    @miljar
  2. WHO ARE WE? WHO ARE WE?

  3. JACHIM JACHIM (Ab)Using PHP since 2002 (4.1.0) Biker, with a

    bike, as in bicyclist Photographer
  4. TOM TOM PHP'ing since 2002  Javascript Black belt ju-jitsu

  5. KING FOO KING FOO

  6. MEETING TOPICS MEETING TOPICS WHAT? WHAT? Grand Scheme of Things:

    Evolution Iteration = small revolution WHY? WHY? Language vs Framework Know Your History What's next?
  7. HEADS UP HEADS UP Patterns of Enterprise Application Architecture

  8. FILE STORAGE FILE STORAGE PHP5 and MySQL Bible

  9. None
  10. ADVANTAGES ADVANTAGES Easy No DB tools needed (Notepad!)

  11. DISADVANTAGES DISADVANTAGES

  12. Slow No queries Concurrency Not Web Scale

  13. PROBLEM: SPEED & CONCURRENCY PROBLEM: SPEED & CONCURRENCY

  14. <DATABASE>-FUNCTIONS <DATABASE>-FUNCTIONS cubrid_* dbplus_* dbase_* filepro_* ibase_* fbsql_* db2_* ifx_*

    ingres_* maxdb_* msql_* mssql_* mysql_ oci_* ovrimos_* px_* pg_* sqlite_* sqlsrv_* sybase_*
  15. None
  16. EXAMPLE EXAMPLE $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') or die('Could not

    connect: ' . mysql_error()); mysql_select_db('guestbook_db') or die('Could not select database'); $query = 'SELECT * FROM `guestbook`'; $result = mysql_query($query) or die('Query failed: ' . mysql_error()); while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { echo $line['name'] . ' - ' . $line['email'] . PHP_EOL; } mysql_free_result($result); mysql_close($link);
  17. BOILERPLATE CODE EXTRACTED BOILERPLATE CODE EXTRACTED // top of the

    page require_once 'db-connection.inc.php'; // ... $query = 'SELECT * FROM `guestbook`'; $result = mysql_query($query) or die('Query failed: ' . mysql_error()); while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { echo $line['name'] . ' - ' . $line['email'] . PHP_EOL; }
  18. ADVANTAGES ADVANTAGES Speed Querying More complete support

  19. DISADVANTAGES DISADVANTAGES Try switching databases...

  20. PROBLEM: NOT UNIFIED PROBLEM: NOT UNIFIED

  21. PDO PDO The PHP Data Objects (PDO) extension defines a

    lightweight, consistent interface for accessing databases in PHP ( ) PHP.net manual
  22. None
  23. EXAMPLE EXAMPLE try { $dbh = new PDO( 'mysql:host=localhost;dbname=guestbook_db', $user,

    $pass ); foreach($dbh->query('SELECT * from `guestbook`') as $row) { echo $row['name'] . ' - ' . $row['email'] . PHP_EOL; } $dbh = null; } catch (PDOException $e) { print "Error!: " . $e->getMessage() . "<br>"; die(); }
  24. ADVANTAGES ADVANTAGES First level of abstraction (data-access) You use the

    same functions to issue queries and fetch data "Easy" to switch database systems OO Still fast
  25. DISADVANTAGES DISADVANTAGES No database access encapsulation SQL dialects uses dirty

    hacks Difficult mapping between DB & OO PDOStatement::fetchObject
  26. PROBLEM: GAP BETWEEN DB & OBJECTS PROBLEM: GAP BETWEEN DB

    & OBJECTS
  27. ORM FRAMEWORK ORM FRAMEWORK Object-relational mapping in computer software is

    a programming technique for converting data between incompatible type systems in relational databases and object- oriented programming languages ( ) Wikipedia
  28. None
  29. ACTIVE RECORD ACTIVE RECORD An object that wraps a row

    in a database table or view, encapsulates the database access, and adds domain logic on that data. ( ) PoEAA: Active Record
  30. None
  31. EXAMPLE EXAMPLE class Comment extends \Eloquent { protected $fillable =

    ['content', 'author']; protected $guarded = ['id']; protected $table = 'comments'; public function blogpost() { return $this->belongsTo('\\MyApp\\Models\\Blogpost'); } } Laravel Eloquent
  32. EXAMPLE EXAMPLE $blogpost = \MyApp\Models\Blogpost::find(1); $comment = new \MyApp\Models\Comment( [

    'content' => 'This is a bit of spam', 'author' => 'Spambot' ] ); $comment->blogpost = $blogpost; $comment->save();
  33. ADVANTAGES ADVANTAGES Simplicity: objects ~ tables Encapsulation

  34. DISADVANTAGES DISADVANTAGES Simplicity: Isomorphic schema Tightly coupled to data source

    "Heavy" objects Conventions
  35. PROBLEM: HEAVY OBJECTS PROBLEM: HEAVY OBJECTS

  36. GATEWAYS GATEWAYS An object that encapsulates access to an external

    system or resource ( ) PoEAA: Gateway
  37. None
  38. GATEWAY TYPES GATEWAY TYPES Table Data Gateway Row Data Gateway

    & Zend_Db_Table Zend_Db_Table_Row
  39. TABLE GATEWAY EXAMPLE TABLE GATEWAY EXAMPLE class Guestbook extends Zend_Db_Table_Abstract

    { protected $_name = 'guestbook'; protected $_primary = 'guestbook_id'; }
  40. TABLE GATEWAY EXAMPLE TABLE GATEWAY EXAMPLE $table = new Guestbook();

    $table->insert(array(/* ... */)); $table->update(array(/* ... */), 'guestbook_id = 1'); $table->delete('guestbook_id = 1'); $rows = $table->find(1); // returns Rowset $rows = $table->fetchAll(); // returns Rowset
  41. ROW GATEWAY EXAMPLE ROW GATEWAY EXAMPLE class GuestbookEntry extends Zend_Db_Table_Row_Abstract

    { protected $_name; protected $_title; public function myCustomFunction() { /* ... */ } }
  42. ROW GATEWAY EXAMPLE ROW GATEWAY EXAMPLE $table = new Guestbook(array('rowClass'

    => 'GuestbookEntry')); $entry = $table->fetchRow(1); // class = GuestbookEntry echo $entry->name . ' - ' . $entry->email . PHP_EOL; $entry->myCustomFunction(); $gbEntry = new GuestbookEntry(); $gbEntry->name = 'john'; $gbEntry->email = 'john@example.com' $gbEntry->save(); // maps to $table->save()
  43. ADVANTAGES ADVANTAGES Simplicity Encapsulation Lighter objects

  44. DISADVANTAGES DISADVANTAGES Simplicity: Isomorphic schema Tightly coupled to data source

    Conventions
  45. PROBLEM: NO FREEDOM PROBLEM: NO FREEDOM

  46. DATA MAPPER DATA MAPPER A layer of Mappers that moves

    data between objects and a database while keeping them independent of each other and the mapper itself. ( ) PoEAA: Data Mapper
  47. None
  48. EXAMPLE EXAMPLE class GuestbookEntry { protected $name; protected $email; public

    function getName() { /* ... */ } public function setName($name) { /* ... */ } // ... } POPO (Plain Old PHP Object)
  49. EXAMPLE EXAMPLE $mapper = new DataMapper($config); $entry = new GuestbookEntry();

    $entry->setName('john'); $entry->setEmail('john@example.com'); $mapper->save($entry); echo "The entry with id {$entry->getID()} has been saved.";
  50. ADVANTAGES ADVANTAGES Loose coupling Object Graphs

  51. DISADVANTAGES DISADVANTAGES Complexity++ (extra layers) Overkill for simple logic

  52. UNDER THE HOOD UNDER THE HOOD

  53. MULTIPLE FETCHES MULTIPLE FETCHES IDENTITY MAP IDENTITY MAP Ensures that

    each object gets loaded only once by keeping every loaded object in a map. Looks up objects using the map when referring to them.
  54. EFFICIENT WRITES EFFICIENT WRITES UNIT OF WORK UNIT OF WORK

    Maintains a list of objects affected by a business transaction and coordinates the writing out of changes and the resolution of concurrency problems.
  55. PARTIAL DATA PARTIAL DATA LAZY LOAD LAZY LOAD An object

    that doesn't contain all of the data you need but knows how to get it.
  56. None
  57. CUSTOM LOGIC CUSTOM LOGIC REPOSITORY REPOSITORY Mediates between the domain

    and data mapping layers using a collection-like interface for accessing domain objects.
  58. None
  59. COMPLETE DATABASE INDEPENDENCE COMPLETE DATABASE INDEPENDENCE QUERY OBJECT QUERY OBJECT

    An object that represents a database query.
  60. DATA STORE - MEMORY WIRING DATA STORE - MEMORY WIRING

    METADATA MAPPING METADATA MAPPING Holds details of object-relational mapping in metadata.
  61. None
  62. None
  63. ORM NOTES ORM NOTES Mix model sources (databases, web services,

    etc...) ORMs cover 80% of the mapping You must/should know your RDBMS ORM does boilerplate code Developer/DBA does the remaining 20% Learn to use an existing ORM
  64. ODM'S ODM'S Object-document mapping

  65. None
  66. SCHEMA SCHEMA Documents are schemaless ODM helps to maintain schema

    Still need to ensure indexes
  67. RELATION VS EMBEDDED RELATION VS EMBEDDED ORM = Object-relational mapping

    1. Normalize DB 2. Load relations (eager vs lazy) ODM = Object-document mapping 1. Denormalize DB 2. Selective field loading 3. Relations are still possible
  68. ODM PROJECTS ODM PROJECTS : Doctrine Project MongoDB Object Document

    Mapper PHPCR Object Document Mapper CouchDB Object Document Mapper Phalcon MongoDB ODM
  69. RECAP RECAP SQL stays the link between (low level) PHP

    and the database ORMs are not the ultimate goal, pick the correct solution
  70. CREDITS CREDITS Photo PHP5 and MySQL Bible: Photo Nuclear explosion:

    Doctrine logo: PoEAA book cover: Bicycle icon: http://eu.wiley.com/WileyCDA/WileyTitle/productCd-0764557467.html http://en.wikipedia.org/wiki/Nuclear_explosion http://symfony.com/projects/doctrine http://www.amazon.com/Patterns-Enterprise-Application-Architecture- Martin/dp/0321127420 http://www.flaticon.com/free-icon/bicycle_2478
  71. THANK YOU THANK YOU @miljar @coudenysj https://joind.in/14178

  72. QUESTIONS? QUESTIONS?