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

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.

Jachim Coudenys

March 24, 2015
Tweet

More Decks by Jachim Coudenys

Other Decks in Programming

Transcript

  1. 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?
  2. <DATABASE>-FUNCTIONS <DATABASE>-FUNCTIONS cubrid_* dbplus_* dbase_* filepro_* ibase_* fbsql_* db2_* ifx_*

    ingres_* maxdb_* msql_* mssql_* mysql_ oci_* ovrimos_* px_* pg_* sqlite_* sqlsrv_* sybase_*
  3. 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);
  4. 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; }
  5. PDO PDO The PHP Data Objects (PDO) extension defines a

    lightweight, consistent interface for accessing databases in PHP ( ) PHP.net manual
  6. 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(); }
  7. 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
  8. DISADVANTAGES DISADVANTAGES No database access encapsulation SQL dialects uses dirty

    hacks Difficult mapping between DB & OO PDOStatement::fetchObject
  9. 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
  10. 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
  11. 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
  12. 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();
  13. TABLE GATEWAY EXAMPLE TABLE GATEWAY EXAMPLE class Guestbook extends Zend_Db_Table_Abstract

    { protected $_name = 'guestbook'; protected $_primary = 'guestbook_id'; }
  14. 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
  15. ROW GATEWAY EXAMPLE ROW GATEWAY EXAMPLE class GuestbookEntry extends Zend_Db_Table_Row_Abstract

    { protected $_name; protected $_title; public function myCustomFunction() { /* ... */ } }
  16. 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 = '[email protected]' $gbEntry->save(); // maps to $table->save()
  17. 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
  18. EXAMPLE EXAMPLE class GuestbookEntry { protected $name; protected $email; public

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

    $entry->setName('john'); $entry->setEmail('[email protected]'); $mapper->save($entry); echo "The entry with id {$entry->getID()} has been saved.";
  20. 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.
  21. 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.
  22. 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.
  23. CUSTOM LOGIC CUSTOM LOGIC REPOSITORY REPOSITORY Mediates between the domain

    and data mapping layers using a collection-like interface for accessing domain objects.
  24. DATA STORE - MEMORY WIRING DATA STORE - MEMORY WIRING

    METADATA MAPPING METADATA MAPPING Holds details of object-relational mapping in metadata.
  25. 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
  26. 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
  27. ODM PROJECTS ODM PROJECTS : Doctrine Project MongoDB Object Document

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

    and the database ORMs are not the ultimate goal, pick the correct solution
  29. 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