The Database Access rEvolution (PHP-WVL meetup)

Slide 1

Slide 1 text

THE DATABASE ACCESS THE DATABASE ACCESS REVOLUTION REVOLUTION & @coudenysj @miljar

Slide 2

Slide 2 text

WHO ARE WE? WHO ARE WE?

Slide 3

Slide 3 text

JACHIM JACHIM (Ab)Using PHP since 2002 (4.1.0) Biker, with a bike, as in bicyclist Photographer

Slide 4

Slide 4 text

TOM TOM PHP'ing since 2002 Javascript Black belt ju-jitsu

Slide 5

Slide 5 text

KING FOO KING FOO

Slide 6

Slide 6 text

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?

Slide 7

Slide 7 text

HEADS UP HEADS UP Patterns of Enterprise Application Architecture

Slide 8

Slide 8 text

FILE STORAGE FILE STORAGE PHP5 and MySQL Bible

Slide 9

Slide 9 text

No content

Slide 10

Slide 10 text

ADVANTAGES ADVANTAGES Easy No DB tools needed (Notepad!)

Slide 11

Slide 11 text

DISADVANTAGES DISADVANTAGES

Slide 12

Slide 12 text

Slow No queries Concurrency Not Web Scale

Slide 13

Slide 13 text

PROBLEM: SPEED & CONCURRENCY PROBLEM: SPEED & CONCURRENCY

Slide 14

Slide 14 text

-FUNCTIONS -FUNCTIONS cubrid_* dbplus_* dbase_* ﬁlepro_* ibase_* fbsql_* db2_* ifx_* ingres_* maxdb_* msql_* mssql_* mysql_ oci_* ovrimos_* px_* pg_* sqlite_* sqlsrv_* sybase_*

Slide 15

Slide 15 text

No content

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

ADVANTAGES ADVANTAGES Speed Querying More complete support

Slide 19

Slide 19 text

DISADVANTAGES DISADVANTAGES Try switching databases...

Slide 20

Slide 20 text

PROBLEM: NOT UNIFIED PROBLEM: NOT UNIFIED

Slide 21

Slide 21 text

PDO PDO The PHP Data Objects (PDO) extension deﬁnes a lightweight, consistent interface for accessing databases in PHP ( ) PHP.net manual

Slide 22

Slide 22 text

No content

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

DISADVANTAGES DISADVANTAGES No database access encapsulation SQL dialects uses dirty hacks Difﬁcult mapping between DB & OO PDOStatement::fetchObject

Slide 26

Slide 26 text

PROBLEM: GAP BETWEEN DB & OBJECTS PROBLEM: GAP BETWEEN DB & OBJECTS

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

No content

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

No content

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

ADVANTAGES ADVANTAGES Simplicity: objects ~ tables Encapsulation

Slide 34

Slide 34 text

DISADVANTAGES DISADVANTAGES Simplicity: Isomorphic schema Tightly coupled to data source "Heavy" objects Conventions

Slide 35

Slide 35 text

PROBLEM: HEAVY OBJECTS PROBLEM: HEAVY OBJECTS

Slide 36

Slide 36 text

GATEWAYS GATEWAYS An object that encapsulates access to an external system or resource ( ) PoEAA: Gateway

Slide 37

Slide 37 text

No content

Slide 38

Slide 38 text

GATEWAY TYPES GATEWAY TYPES Table Data Gateway Row Data Gateway & Zend_Db_Table Zend_Db_Table_Row

Slide 39

Slide 39 text

TABLE GATEWAY EXAMPLE TABLE GATEWAY EXAMPLE class Guestbook extends Zend_Db_Table_Abstract { protected $_name = 'guestbook'; protected $_primary = 'guestbook_id'; }

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

ROW GATEWAY EXAMPLE ROW GATEWAY EXAMPLE class GuestbookEntry extends Zend_Db_Table_Row_Abstract { protected $_name; protected $_title; public function myCustomFunction() { /* ... */ } }

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

ADVANTAGES ADVANTAGES Simplicity Encapsulation Lighter objects

Slide 44

Slide 44 text

DISADVANTAGES DISADVANTAGES Simplicity: Isomorphic schema Tightly coupled to data source Conventions

Slide 45

Slide 45 text

PROBLEM: NO FREEDOM PROBLEM: NO FREEDOM

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

No content

Slide 48

Slide 48 text

EXAMPLE EXAMPLE class GuestbookEntry { protected $name; protected $email; public function getName() { /* ... */ } public function setName($name) { /* ... */ } // ... } POPO (Plain Old PHP Object)

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

ADVANTAGES ADVANTAGES Loose coupling Object Graphs

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

UNDER THE HOOD UNDER THE HOOD

Slide 53

Slide 53 text

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.

Slide 54

Slide 54 text

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.

Slide 55

Slide 55 text

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.

Slide 56

Slide 56 text

No content

Slide 57

Slide 57 text

CUSTOM LOGIC CUSTOM LOGIC REPOSITORY REPOSITORY Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects.

Slide 58

Slide 58 text

No content

Slide 59

Slide 59 text

COMPLETE DATABASE INDEPENDENCE COMPLETE DATABASE INDEPENDENCE QUERY OBJECT QUERY OBJECT An object that represents a database query.

Slide 60

Slide 60 text

DATA STORE - MEMORY WIRING DATA STORE - MEMORY WIRING METADATA MAPPING METADATA MAPPING Holds details of object-relational mapping in metadata.

Slide 61

Slide 61 text

No content

Slide 62

Slide 62 text

No content

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

ODM'S ODM'S Object-document mapping

Slide 65

Slide 65 text

No content

Slide 66

Slide 66 text

SCHEMA SCHEMA Documents are schemaless ODM helps to maintain schema Still need to ensure indexes

Slide 67

Slide 67 text

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 ﬁeld loading 3. Relations are still possible

Slide 68

Slide 68 text

ODM PROJECTS ODM PROJECTS : Doctrine Project MongoDB Object Document Mapper PHPCR Object Document Mapper CouchDB Object Document Mapper Phalcon MongoDB ODM

Slide 69

Slide 69 text

RECAP RECAP SQL stays the link between (low level) PHP and the database ORMs are not the ultimate goal, pick the correct solution

Slide 70

Slide 70 text

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.ﬂaticon.com/free-icon/bicycle_2478