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.

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