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

The Database Access rEvolution (with @miljar)

The Database Access rEvolution (with @miljar)

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 13, 2014
Tweet

Transcript

  1. The Database Access rEvolution & @coudenysj @miljar King Foo

  2. WHO ARE WE?

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

    as in bicyclist Photographer
  4. TOM PHP'ing since 2002 ♥ Javascript Ju-Jitsu

  5. PHP-WVL PHP community in W-VL Tue April 8th @ 8pm

    http://meetup.com/php-wvl/
  6. KING FOO

  7. MEETING TOPICS WHAT? Grand Scheme of Things: Evolution Iteration =

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

  9. FILE STORAGE PHP5 and MySQL Bible

  10. None
  11. FILE STORAGE Textbook example: guestbook Any structured format Serialized Marked

    up
  12. EXAMPLE f u n c t i o n w

    r i t e E n t r y T o G u e s t b o o k ( $ n a m e , $ e m a i l , $ m e s s a g e ) { $ h a n d l e = f o p e n ( ' g u e s t b o o k . t x t ' , ' a ' ) ; f w r i t e ( $ h a n d l e , ' n a m e : ' . $ n a m e . P H P _ E O L ) ; f w r i t e ( $ h a n d l e , ' e m a i l : ' . $ e m a i l . P H P _ E O L ) ; f w r i t e ( $ h a n d l e , ' t i m e s t a m p : ' . t i m e ( ) . P H P _ E O L ) ; f w r i t e ( $ h a n d l e , $ m e s s a g e . P H P _ E O L ) ; f w r i t e ( $ h a n d l e , ' - - - - - - - - - - ' . P H P _ E O L ) ; f c l o s e ( $ h a n d l e ) ; }
  13. EXAMPLE f u n c t i o n g

    e t G u e s t b o o k E n t r i e s ( ) { $ h a n d l e = f o p e n ( ' g u e s t b o o k . t x t ' , ' r ' ) ; $ e n t r i e s = a r r a y ( ) ; $ e n t r y = a r r a y ( ) ; w h i l e ( ! f e o f ( $ h a n d l e ) ) { $ l i n e = t r i m ( f g e t s ( $ h a n d l e ) ) ; i f ( s u b s t r ( $ l i n e , 0 , 5 ) = = = ' n a m e : ' ) { $ e n t r y [ ' n a m e ' ] = s u b s t r ( $ l i n e , 5 ) ; } e l s e i f ( s u b s t r ( $ l i n e , 0 , 6 ) = = = ' e m a i l : ' ) { $ e n t r y [ ' e m a i l ' ] = s u b s t r ( $ l i n e , 6 ) ; } e l s e i f ( s u b s t r ( $ l i n e , 0 , 1 0 ) = = = ' t i m e s t a m p : ' ) { $ e n t r y [ ' t i m e s t a m p ' ] = s u b s t r ( $ l i n e , 1 0 ) ; } e l s e i f ( $ l i n e = = = ' - - - - - - - - - - ' ) { $ e n t r i e s [ ] = $ e n t r y ; $ e n t r y = a r r a y ( ) ; } e l s e { i f ( ! a r r a y _ k e y _ e x i s t s ( ' m e s s a g e ' , $ e n t r y ) ) { $ e n t r y [ ' m e s s a g e ' ] = ' ' ; } $ e n t r y [ ' m e s s a g e ' ] . = $ l i n e . P H P _ E O L ; } } f c l o s e ( $ h a n d l e ) ; r e t u r n $ e n t r i e s ; }
  14. ADVANTAGES Easy No external application needed

  15. DISADVANTAGES Slow No queries Concurrency Not Web Scale

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

    maxdb_* msql_* mssql_* mysql_ oci_* ovrimos_* px_* pg_* sqlite_* sqlsrv_* sybase_*
  17. None
  18. EXAMPLE $ l i n k = m y s

    q l _ c o n n e c t ( ' m y s q l _ h o s t ' , ' m y s q l _ u s e r ' , ' m y s q l _ p a s s w o r d ' ) o r d i e ( ' C o u l d n o t c o n n e c t : ' . m y s q l _ e r r o r ( ) ) ; m y s q l _ s e l e c t _ d b ( ' g u e s t b o o k _ d b ' ) o r d i e ( ' C o u l d n o t s e l e c t d a t a b a s e ' ) ; $ q u e r y = ' S E L E C T * F R O M ` g u e s t b o o k ` ' ; $ r e s u l t = m y s q l _ q u e r y ( $ q u e r y ) o r d i e ( ' Q u e r y f a i l e d : ' . m y s q l _ e r r o r ( ) ) ; w h i l e ( $ l i n e = m y s q l _ f e t c h _ a r r a y ( $ r e s u l t , M Y S Q L _ A S S O C ) ) { e c h o $ l i n e [ ' n a m e ' ] . ' - ' . $ l i n e [ ' e m a i l ' ] . P H P _ E O L ; } m y s q l _ f r e e _ r e s u l t ( $ r e s u l t ) ; m y s q l _ c l o s e ( $ l i n k ) ;
  19. BOILERPLATE CODE EXTRACTED / / t o p o f

    t h e p a g e r e q u i r e _ o n c e ' d b - c o n n e c t i o n . i n c . p h p ' ; / / . . . $ q u e r y = ' S E L E C T * F R O M ` g u e s t b o o k ` ' ; $ r e s u l t = m y s q l _ q u e r y ( $ q u e r y ) o r d i e ( ' Q u e r y f a i l e d : ' . m y s q l _ e r r o r ( ) ) ; w h i l e ( $ l i n e = m y s q l _ f e t c h _ a r r a y ( $ r e s u l t , M Y S Q L _ A S S O C ) ) { e c h o $ l i n e [ ' n a m e ' ] . ' - ' . $ l i n e [ ' e m a i l ' ] . P H P _ E O L ; }
  20. ADVANTAGES Speed Querying More complete support

  21. DISADVANTAGES Try switching databases...

  22. PDO The PHP Data Objects (PDO) extension defines a lightweight,

    consistent interface for accessing databases in PHP ( ) PHP.net manual
  23. None
  24. EXAMPLE t r y { $ d b h =

    n e w P D O ( ' m y s q l : h o s t = l o c a l h o s t ; d b n a m e = g u e s t b o o k _ d b ' , $ u s e r , $ p a s s ) ; f o r e a c h ( $ d b h - > q u e r y ( ' S E L E C T * f r o m ` g u e s t b o o k ` ' ) a s $ r o w ) { e c h o $ r o w [ ' n a m e ' ] . ' - ' . $ r o w [ ' e m a i l ' ] . P H P _ E O L ; } $ d b h = n u l l ; } c a t c h ( P D O E x c e p t i o n $ e ) { p r i n t " E r r o r ! : " . $ e - > g e t M e s s a g e ( ) . " < b r > " ; d i e ( ) ; }
  25. 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
  26. DISADVANTAGES No database access encapsulation "Easy" to switch database systems

    is not completely true Issues with PDOStatement::fetchObject
  27. 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 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 c l a s s G u e s

    t b o o k e x t e n d s D o c t r i n e _ R e c o r d { p u b l i c f u n c t i o n s e t T a b l e D e f i n i t i o n ( ) { $ t h i s - > h a s C o l u m n ( ' n a m e ' , ' s t r i n g ' , 2 5 5 ) ; $ t h i s - > h a s C o l u m n ( ' e m a i l ' , ' s t r i n g ' , 2 5 5 , a r r a y ( ' u n i q u e ' = > t r u e ) ) ; } } Doctrine 1.x
  32. EXAMPLE $ g b = n e w G u

    e s t b o o k ( ) ; $ g b - > n a m e = " j o h n " ; $ g b - > e m a i l = " j o h n @ e x a m p l e . c o m " ; $ g b - > s a v e ( ) ; $ a r r E n t r i e s = $ g b - > f i n d A l l ( ) ; $ e x i s t i n g E n t r y = r e s e t ( $ a r r E n t r i e s ) ; e c h o $ e x i s t i n g E n t r y - > n a m e . ' - ' . $ e x i s t i n g E n t r y - > e m a i l . P H P _ E O L ;
  33. ADVANTAGES Simplicity Objects correspond to tables Encapsulation

  34. DISADVANTAGES Simplicity Isomorphic schema Tightly coupled to data source "Heavy"

    objects
  35. GATEWAYS An object that encapsulates access to an external system

    or resource ( ) PoEAA: Gateway
  36. None
  37. GATEWAY TYPES Table Data Gateway Row Data Gateway & Zend_Db_Table

    Zend_Db_Table_Row
  38. TABLE GATEWAY EXAMPLE c l a s s G u

    e s t b o o k e x t e n d s Z e n d _ D b _ T a b l e _ A b s t r a c t { p r o t e c t e d $ _ n a m e = ' g u e s t b o o k ' ; p r o t e c t e d $ _ p r i m a r y = ' g u e s t b o o k _ i d ' ; }
  39. TABLE GATEWAY EXAMPLE $ t a b l e =

    n e w G u e s t b o o k ( ) ; $ t a b l e - > i n s e r t ( a r r a y ( / * . . . * / ) ) ; $ t a b l e - > u p d a t e ( a r r a y ( / * . . . * / ) , ' g u e s t b o o k _ i d = 1 ' ) ; $ t a b l e - > d e l e t e ( ' g u e s t b o o k _ i d = 1 ' ) ; $ r o w s = $ t a b l e - > f i n d ( 1 ) ; / / r e t u r n s R o w s e t $ r o w s = $ t a b l e - > f e t c h A l l ( ) ; / / r e t u r n s R o w s e t
  40. ROW GATEWAY EXAMPLE c l a s s G u

    e s t b o o k E n t r y e x t e n d s Z e n d _ D b _ T a b l e _ R o w _ A b s t r a c t { p u b l i c f u n c t i o n m y C u s t o m F u n c t i o n ( ) { / * . . . * / } }
  41. ROW GATEWAY EXAMPLE $ t a b l e =

    n e w G u e s t b o o k ( a r r a y ( ' r o w C l a s s ' = > ' G u e s t b o o k E n t r y ' ) ) ; $ e n t r y = $ t a b l e - > f e t c h R o w ( 1 ) ; / / c l a s s = G u e s t b o o k E n t r y e c h o $ e n t r y - > n a m e . ' - ' . $ e n t r y - > e m a i l . P H P _ E O L ; $ e n t r y - > m y C u s t o m F u n c t i o n ( ) ; $ g b E n t r y = n e w G u e s t b o o k E n t r y ( ) ; $ g b E n t r y - > n a m e = ' j o h n ' ; $ g b E n t r y - > e m a i l = ' j o h n @ e x a m p l e . c o m ' $ g b E n t r y - > s a v e ( ) ; / / m a p s t o $ t a b l e - > s a v e ( )
  42. ADVANTAGES Simplicity Encapsulation Lighter objects

  43. DISADVANTAGES Simplicity Isomorphic schema Tightly coupled to data source

  44. 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
  45. None
  46. EXAMPLE c l a s s G u e s

    t b o o k E n t r y { p r o t e c t e d $ n a m e ; p r o t e c t e d $ e m a i l ; p u b l i c f u n c t i o n g e t N a m e ( ) { / * . . . * / } p u b l i c f u n c t i o n s e t N a m e ( $ n a m e ) { / * . . . * / } / / . . . } POPO (Plain Old PHP Object)
  47. EXAMPLE $ m a p p e r = n

    e w D a t a M a p p e r ( $ c o n f i g ) ; $ e n t r y = n e w G u e s t b o o k E n t r y ( ) ; $ e n t r y - > s e t N a m e ( ' j o h n ' ) ; $ e n t r y - > s e t E m a i l ( ' j o h n @ e x a m p l e . c o m ' ) ; $ m a p p e r - > s a v e ( $ e n t r y ) ; e c h o " T h e e n t r y w i t h i d { $ e n t r y - > g e t I D ( ) } h a s b e e n s a v e d . " ;
  48. ADVANTAGES Loose coupling Object Graphs

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

  50. MULTIPLE FETCHES 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.
  51. EFFICIENT WRITES 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.
  52. PARTIAL DATA LAZY LOAD An object that doesn't contain all

    of the data you need but knows how to get it.
  53. None
  54. CUSTOM LOGIC REPOSITORY Mediates between the domain and data mapping

    layers using a collection-like interface for accessing domain objects.
  55. None
  56. COMPLETE DATABASE INDEPENDENCE QUERY OBJECT An object that represents a

    database query.
  57. DATA STORE - MEMORY WIRING METADATA MAPPING Holds details of

    object-relational mapping in metadata.
  58. None
  59. None
  60. 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
  61. ODM'S Object-document mapping

  62. None
  63. ODM PROJECTS : Doctrine Project MongoDB Object Document Mapper PHPCR

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

    the database ORMs are not the ultimate goal, pick the correct solution
  65. 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
  66. THANK YOU @miljar @coudenysj https://joind.in/10840

  67. QUESTIONS?