110

# 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.

March 13, 2014

## Transcript

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

as in bicyclist Photographer

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

http://meetup.com/php-wvl/

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

small revolution WHY? Language vs Framework Know Your History What's next?

10. None

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

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

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 ;

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

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

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

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
{"ft_lang_label":"__label__en","ft_lang_prob":0.9607483,"math_prob":0.9962511,"size":518,"snap":"2021-04-2021-17","text_gpt3_token_len":104,"char_repetition_ratio":0.14007781,"word_repetition_ratio":0.0,"special_character_ratio":0.1949807,"punctuation_ratio":0.078431375,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9998351,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-04-21T10:44:17Z\",\"WARC-Record-ID\":\"<urn:uuid:089fcf26-c680-4135-bde8-ed3895a81e1d>\",\"Content-Length\":\"117387\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:accff203-3405-473f-8288-d91d049060ba>\",\"WARC-Concurrent-To\":\"<urn:uuid:e685aed7-73e3-4073-88d1-fcf2ea042cd4>\",\"WARC-IP-Address\":\"52.204.244.158\",\"WARC-Target-URI\":\"https://speakerdeck.com/coudenysj/the-database-access-revolution-with-at-miljar\",\"WARC-Payload-Digest\":\"sha1:2VM5E7X3GAWRMD7TSVKKX5MF32OYPSBT\",\"WARC-Block-Digest\":\"sha1:NOJDRS5I7O6DVGMAIJWWFVVQHBWZILEL\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-17/CC-MAIN-2021-17_segments_1618039536858.83_warc_CC-MAIN-20210421100029-20210421130029-00296.warc.gz\"}"}