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.

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
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_*
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
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
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
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
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
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.
57. CUSTOM LOGIC CUSTOM LOGIC REPOSITORY REPOSITORY Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects.
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.
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
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?