2013 - Benjamin Eberlei - Doctrine 2

75 %
25 %
Information about 2013 - Benjamin Eberlei - Doctrine 2
Technology

Published on January 17, 2014

Author: PHPConferenceArgentina

Source: slideshare.net

Description

PHP Conference Argentina 2013

Talk to your database with Doctrine PHP Conference Argentina 2013 Benjamin Eberlei, @beberlei 5th October 2013

About me Helping people to create high quality web applications. http://qafoo.com Doctrine Developer Symfony Contributor Twitter @beberlei and @qafoo

What is Doctrine? The Doctrine Project is the home of a selected set of PHP libraries primarily focused on databases and related functionality.

Databases? Relational databases MySQL PostgreSQL Oracle SQL Server Sqlite Experimental: Drizzle, DB2, Sybase Non-relational databases Document: CouchDB, MongoDB, JCR Graph: OrientDB Caches: Riak, Redis, Memcache and many more

Doctrine DBAL Provides Driver abstraction SQL dialect abstraction (Both DML and DDL) Convenience APIs for database access SQL type abstraction Database schema abstraction Indepedent of the ORM

Driver abstraction No need to use driver APIs directly in your code API is similar to PDO Supported (stable) drivers PDO mysqli oci8 sqlsrv

Driver abstraction 1 <?php 2 3 class PostTable 4 { p r i v a t e $pdo ; 5 6 7 public function connect ( ) 8 { $ t h i s −>pdo = new PDO( ’ mysql : dbname= b l o g ; h o s t = 1 2 7 . 0 . 0 . 1 ’ , ’ r o o t ’ , ’ ’ ) ; $ t h i s −>pdo−> s e t A t t r i b u t e (PDO : : ATTR ERRMODE, PDO : : ERRMODE EXCEPTION) ; 9 10 } 11 12 13 public function l i s t A l l ( ) 14 { $ s q l = ’SELECT ∗ FROM p o s t s WHERE s t a t u s = ” PUBLISHED ” ORDER BY p u b l i s h d a t e DESC LIMIT 0 ,20 ’ ; $stmt = $ t h i s −>pdo−> query ( $ s q l ) ; 15 16 17 18 19 20 21 r e t u r n $stmt −> f e t c h A l l ( ) ; 22 } 23 24 }

Driver abstraction 1 <?php 2 3 4 $ t a b l e = new PostTable ( ) ; $ t a b l e −> connect ( ) ; 5 6 $posts = $ t a b l e −> l i s t A l l ( ) ; 7 8 $twig −> r e n d e r ( ’ p o s t s . h t m l . t w i g ’ , a r r a y ( ’ p o s t s ’ => $posts ) ) ;

Driver abstraction 1 <?php 2 use D o c t r i n e DBAL DriverManager ; 3 4 class PostTable 5 { p r i v a t e $conn ; 6 7 8 public function connect ( ) 9 { $ t h i s −>conn = DriverManager : : getConnection ( a r r a y ( ’ driver ’ => ’ pdo mysql ’ , ’ dbname ’ => ’ b l o g ’ , ’ user ’ => ’ r o o t ’ , )); 10 11 12 13 14 } 15 16 17 public function l i s t A l l ( ) 18 { $ s q l = ’SELECT ∗ FROM p o s t s WHERE s t a t u s = ” PUBLISHED ” ORDER BY p u b l i s h d a t e DESC LIMIT 0 ,20 ’ ; $stmt = $ t h i s −>conn −> query ( $ s q l ) ; 19 20 21 22 23 24 25 r e t u r n $stmt −> f e t c h A l l ( ) ; 26 } 27 28 }

SQL dialect abstraction Concept: Platform APIs to control different SQL styles and names Examples: LIMIT queries usual SQL functions

SQL dialect abstraction 1 <?php 2 3 class PostTable 4 { 5 /∗ ∗ 6 ∗ @var D o c t r i n e DBAL P l a t f o r m A b s t r a c t P l a t f o r m ∗/ private $platform ; 7 8 9 10 public function connect ( ) 11 { // ... $ t h i s −> p l a t f o r m = $ t h i s −>conn −> ge tDa tab ase Pl atf orm ( ) ; 12 13 } 14 15 }

SQL dialect abstraction 1 <?php 2 3 class PostTable 4 { 5 public function l i s t A l l ( ) 6 { $ s q l = $ t h i s −> p l a t f o r m −> m o d i f y L i m i t Q u e r y ( ’SELECT ∗ FROM p o s t s WHERE s t a t u s = ” PUBLISHED ” ORDER BY p u b l i s h d a t e DESC ’ , 0 , 20 ); 7 8 9 10 11 12 13 14 $stmt = $ t h i s −>conn −> query ( $ s q l ) ; r e t u r n $stmt −> f e t c h A l l ( ) ; 15 16 } 17 18 }

Convenience APIs Doctrine Connection has methods to insert($table, array $data) update($table, array $data, $where) delete($table, $where) SQL QueryBuilder

Convenience APIs: Insert 1 <?php 2 3 class PostTable 4 { 5 public function i n s e r t ( a r r a y $post ) 6 { $ t h i s −>conn −> i n s e r t ( ’ p o s t s ’ , $post ) ; 7 } 8 9 }

Convenience APIs: Insert 1 <?php 2 3 4 5 6 7 8 9 10 $ t a b l e = new PostTable ( ) ; $ t a b l e −> connect ( ) ; $ t a b l e −> i n s e r t ( a r r a y ( ’ title ’ => ’ H e l l o World ! ’ , ’ content ’ => ’ T h i s i s my f i r s t p o s t ’ , ’ p u b l i s h s t a t u s ’ => ’PUBLISHED ’ , ’ publish date ’ => date ( ’ Y−m−d H: i : s ’ ) , ));

Convenience APIs: Update 1 <?php 2 3 class PostTable 4 { 5 public function update ( $id , a r r a y $post ) 6 { $ t h i s −>conn −> update ( ’ p o s t s ’ , $post , a r r a y ( ’ i d ’ => $ i d ) ) ; 7 } 8 9 }

Convenience APIs: Delete 1 <?php 2 3 class PostTable 4 { 5 public function d e l e t e ( $ i d ) 6 { $ t h i s −>conn −> d e l e t e ( ’ p o s t s ’ , a r r a y ( ’ i d ’ => $ i d ) ) ; 7 } 8 9 }

Convenience APIs: SQL QueryBuilder 1 <?php 2 3 class PostTable 4 { 5 public function l i s t A l l ( ) 6 { $query = $ t h i s −>conn −> c r e a t e Q u e r y B u i l d e r ( ) ; $query −> s e l e c t ( ’ ∗ ’ ) −> from ( ’ p o s t s ’ ) −>where ( ’ s t a t u s = ” PUBLISHED ” ’ ) −> orderBy ( ’ p u b l i s h d a t e ’ , ’DESC ’ ) −> s e t F i r s t R e s u l t ( 0 ) −> setMaxResults ( 2 0 ) ; 7 8 9 10 11 12 13 14 $stmt = $query −> execute ( ) ; r e t u r n $stmt −> f e t c h A l l ( ) ; 15 16 } 17 18 }

Create simple abstractions 1 <?php 2 3 a b s t r a c t class Table 4 { a b s t r a c t public function getName ( ) ; 5 6 public function i n s e r t ( a r r a y $data ) 7 { 8 $ t h i s −>conn −> i n s e r t ( $ t h i s −>getName ( ) , $data ) ; 9 } 10 11 12 public function update ( $id , a r r a y $data ) 13 { $ t h i s −>conn −> update ( $ t h i s −>getName ( ) , $data , a r r a y ( ’ i d ’ => $ i d ) ) ; 14 } 15 16 17 public function c r e a t e Q u e r y B u i l d e r ( ) 18 { $query = $ t h i s −>conn −> c r e a t e Q u e r y B u i l d e r ( ) ; $query −> s e l e c t ( ’ ∗ ’ ) −> from ( $ t h i s −>getName ( ) ) ; 19 20 21 22 r e t u r n $query ; 23 } 24 25 }

Create simple abstractions 1 <?php 2 3 class PostTable extends Table 4 { 5 public function getName ( ) 6 { return ’ posts ’ ; 7 } 8 9 10 public function l i s t A l l ( ) 11 { $stmt = $ t h i s −> c r e a t e Q u e r y B u i l d e r ( ) −>where ( ’ s t a t u s = ” PUBLISHED ” ’ ) −> orderBy ( ’ p u b l i s h d a t e ’ , ’DESC ’ ) −> s e t F i r s t R e s u l t ( 0 ) −> setMaxResults ( 2 0 ) −> execute ( ) ; 12 13 14 15 16 17 18 19 r e t u r n $stmt −> f e t c h A l l ( ) ; 20 } 21 22 }

SQL Type abstraction ANSI SQL does not standardize types Each vendor has lots of different types and semantics Doctrine Type API is abstraction for Converting PHP to SQL with convertToDatabaseValue() Converting SQL to PHP with convertToPhpValue() Supported types String and long texts Numbers, decimals, floats Date, Time and Datetime, with and without TZ offsets Blob Booleans

SQL Type abstraction 1 <?php 2 3 use D o c t r i n e DBAL Types Type ; 4 5 6 $now = new DateTime ( ’ now ’ ) ; $type = Type : : getType ( ’ d a t e t i m e ’ ) ; 7 8 9 $ s q l V a lu e = $type −> convertToDatabaseValue ( $now , $ p l a t f o r m ) ; / / Formatted 2013 −10 −05 1 5: 2 8 : 2 7 10 11 12 $phpValue = $type −> convertToPhpValue ( $sqlValue , $ p l a t f o r m ) ; / / DateTime i n s t a n c e again

SQL Type abstraction 1 <?php 2 3 use D o c t r i n e DBAL Types Type ; 4 5 a b s t r a c t class Table 6 { 7 public function i n s e r t ( a r r a y $data ) 8 { $ t h i s −>conn −> i n s e r t ( $ t h i s −>getName ( ) , $ t h i s −> convertToSqlValues ( $data ) ) ; 9 } 10 11 12 protected function convertToSqlValues ( a r r a y $data ) 13 { $columnTypes = $ t h i s −>getColumnTypes ( ) ; 14 15 f o r e a c h ( $data as $columnName => $phpValue ) { i f ( i s s e t ( $columnTypes [ $columnName ] ) ) { $type = Type : : getType ( $columnTypes [ $columnName ] ) ; $data [ $columnName ] = $type −> convertToDatabaseValue ( $phpValue , $ t h i s −> p l a t f o r m ) ; 16 17 18 19 20 } 21 } 22 23 r e t u r n $data ; 24 } 25 26 a b s t r a c t protected function getColumnTypes ( ) ; 27 28 }

SQL Type abstraction 1 <?php 2 3 class PostTable extends Table 4 { protected function getColumnTypes ( ) 5 { 6 r e t u r n a r r a y ( ’ p u b l i s h d a t e ’ => ’ d a t e t i m e ’ ) ; 7 } 8 9 } 10 11 12 13 14 15 16 17 18 $ t a b l e = new PostTable ( ) ; $ t a b l e −> connect ( ) ; $ t a b l e −> i n s e r t ( a r r a y ( ’ title ’ => ’ H e l l o World ! ’ , ’ content ’ => ’ T h i s i s my f i r s t p o s t ’ , ’ p u b l i s h s t a t u s ’ => ’PUBLISHED ’ , ’ publish date ’ => new DateTime ( ’ now ’ ) , ));

Database Schema Abstraction API to fetch current state of database schema Object-Oriented Graph of Tables Columns Indices Foreign Keys Sequences Compare different schema graphs Beware: Database diffs are not perfect!

Database Schema Abstraction 1 <?php 2 3 use D o c t r i n e DBAL Schema Table ; 4 5 class PostTable extends Table 6 { 7 public function g e t D e f i n i t i o n ( ) 8 { $ t a b l e = new Table ( ) ; $ t a b l e −>addColumn ( ’ i d ’ , ’ i n t e g e r ’ ) ; $ t a b l e −>addColumn ( ’ t i t l e ’ , ’ s t r i n g ’ ) ; $ t a b l e −>addColumn ( ’ c o n t e n t ’ , ’ t e x t ’ ) ; $ t a b l e −>addColumn ( ’ p u b l i s h s t a t u s ’ , ’ s t r i n g ’ ) ; $ t a b l e −>addColumn ( ’ p u b l i s h d a t e ’ , ’ d a t e t i m e ’ ) ; 9 10 11 12 13 14 15 $ t a b l e −> setPrimaryKey ( a r r a y ( ’ i d ’ ) ) ; $ t a b l e −> addIndex ( a r r a y ( ’ p u b l i s h s t a t u s ’ , ’ p u b l i s h d a t e ’ ) ) ; 16 17 18 return $table ; 19 } 20 21 }

Database Schema Abstraction 1 <?php 2 3 a b s t r a c t class Table 4 { 5 public function c r e a t e T a b l e ( ) 6 { $schemaManager = $ t h i s −>conn −>getSchemaManager ( ) ; $ t a b l e D e f i n i t i o n = $ t h i s −> g e t D e f i n i t i o n ( ) ; 7 8 9 $schemaManager−> c r e a t e T a b l e ( $ t a b l e D e f i n i t i o n ) ; 10 } 11 12 }

Database Schema Abstraction 1 <?php 2 use D o c t r i n e DBAL Schema Comparator ; 3 4 a b s t r a c t class Table 5 { 6 public function updateTable ( ) 7 { $schemaManager = $ t h i s −>conn −>getSchemaManager ( ) ; $ t a b l e D e f i n i t i o n = $ t h i s −> g e t D e f i n i t i o n ( ) ; 8 9 10 $ c u r r e n t = $schemaManager−> l i s t T a b l e D e t a i l s ( $ t h i s −>getName ( ) ) ; 11 12 $comparator = new Comparator ( ) ; $ t a b l e D i f f = $comparator −> d i f f T a b l e ( $ c u r r e n t , $ t a b l e D e f i n i t i o n ) ; 13 14 15 $ s q l s = $ t h i s −> p l a t f o r m −> getAlterTableSQL ( $ t a b l e D i f f ) ; 16 17 f o r e a c h ( $ s q l s as $ s q l ) { $ t h i s −>conn −>exec ( $ s q l ) ; 18 19 } 20 } 21 22 }

Doctrine ORM Provides Mapping PHP Objects to Database Manages Association between objects Creates SQL Schema from PHP Objects ORMs are leaky abstraction Knowledge of underyling SQL is highly recommended Mapping between SQL and Objects has performance penalty Not always the best solution for all problems

Defining PHP Objects PHP Objects managed with Doctrine are called Entities Doctrine uses DataMapper pattern: No base class or interface required for your entities Usage of the constructor is allowed Configuration of the mapping is necessary

1. Maps PHP Objects to DB Tables 1 <?php 2 class Post 3 { protected $ i d ; protected $ t i t l e ; protected $body ; 4 5 6 7 } 1 CREATE TABLE Post ( i d INT AUTO INCREMENT PRIMARY KEY, t i t l e VARCHAR( 2 5 5 ) , body TEXT ); 2 3 4

2. Metadata Mapping with Annotations, XML, Yaml 2 <?php / ∗ ∗ @Entity ∗ ∗ / 3 class Post 4 { 1 / ∗ ∗ @Id @GeneratedValue @Column ( t y p e =” i n t e g e r ” ) ∗ ∗ / protected $ i d ; / ∗ ∗ @Column ( t y p e =” s t r i n g ” ) ∗ ∗ / protected $ t i t l e ; / ∗ ∗ @Column ( t y p e =” t e x t ” ) ∗ ∗ / protected $body ; 5 6 7 8 9 10 11 }

Defininig Associations Doctrine manages foreign keys by looking at object references No explicit foreign key management necessary Reference to a single object is N:1 or 1:1 Reference to a collection is 1:N or M:N

3. Object-References map to Foreign Keys 2 <?php / ∗ ∗ @Entity ∗ ∗ / 3 class Post 4 { 1 /∗ ∗ 5 ∗ @ManyToOne( t a r g e t E n t i t y =” User ” ) ∗ ∗/ 6 7 protected $ a u t h o r ; 8 9 10 public function 11 c o n s t r u c t ( User $user ) { $ t h i s −> a u t h o r = $user ; 12 } 13 14 } 15 16 17 $user = new User ( ) ; $post = new Post ( $user ) ;

4. ”Collections” contain many object references 1 <?php 2 use D o c t r i n e Common C o l l e c t i o n s A r r a y C o l l e c t i o n ; 3 4 class Post 5 { /∗ ∗ 6 ∗ @OneToMany( t a r g e t E n t i t y =”Comment ” , mappedBy=” p o s t ” , ∗ cascade = { ” p e r s i s t ” } ) ∗ ∗/ 7 8 9 protected $comments ; 10 11 12 public function 13 construct ( ) { $ t h i s −>commments = new A r r a y C o l l e c t i o n ( ) ; 14 } 15 16 17 public function addComment ( $ t e x t ) 18 { $ t h i s −>comments [ ] = new Comment ( $ t h i s , $ t e x t ) ; 19 } 20 21 }

EntityManager The EntityManager is facade to all Doctrine APIs Allows to add and remove objects from the database Seperation of notification and actual transaction persist and remove methods flush batches SQL operations in single transaction

5. EntityManager has to know about objects 1 <?php 2 3 4 $entityManager −> p e r s i s t ( $post ) ; $entityManager −> p e r s i s t ( $user ) ;

6. EntityManager#flush() batches SQL operations 1 <?php 2 3 $entityManager −> f l u s h ( ) ;

Finding Objects Using simple finders By ID By Key=Value Conditions Using Criteria Object-Oriented API Allows more comparison operators Doctrine Query Lanuage (DQL)

7. Find by ID 1 <?php 2 3 $post = $entityManager −> f i n d ( ” Post ” , $ i d ) ;

8. Find by Criteria 1 <?php 2 3 4 $ a u t h o r R e p o s i t o r y = $entityManager −> g e t R e p o s i t o r y ( ” Author ” ) ; $ a u t h o r = $ a u t h o r R e p o s i t o r y −> f i n d ( $ a u t h o r I d ) ; 5 6 7 $ p o s t R e p o s i t o r y = $entityManager −> g e t R e p o s i t o r y ( ” Post ” ) ; $post = $ p o s t R e p o s i t o r y −> findOneBy ( a r r a y ( ” t i t l e ” => ” H e l l o World ! ” ) ) ; 8 9 10 11 12 $posts = $ p o s t R e p o s i t o r y −> f i n d B y ( a r r a y ( ” a u t h o r ” => $ a u t h o r ) , a r r a y ( ” t i t l e ” => ”ASC” ) );

Doctrine Query Language DQL is not SQL (its own Object Query Language) Classes and fields instead of tables and columns Real (cachable) parser manually constructed from EBNF Uses Runtime Metadata Information

9. Find with DQL 1 <?php 2 3 4 5 $ d q l = ”SELECT p AS post , count ( c . i d ) AS comments ” . ”FROM Post p JOIN p . comments c GROUP BY p ” ; $ r e s u l t s = $entityManager −> createQuery ( $ d q l ) −> g e t R e s u l t ( ) ; 6 8 f o r e a c h ( $ r e s u l t s as $row ) { echo $row [ ’ p o s t ’ ] −> g e t T i t l e ( ) . ” ( ” . $row [ ’ comments ’ ] . ” ) ” ; 9 } 7

Add a comment

Related presentations

Related pages

Doctrine 2.4 released — Doctrine Project

... 2013, posted by Benjamin Eberlei in categories Release. Doctrine 2.4 released. We are happy to announce the ... {"require": {"doctrine/common": "2.4 ...
Read more

Benjamin Eberlei | Software-Craftsman | kontakt@beberlei.de

... Project-Leader of the successful PHP ORM Doctrine and regular contributor to the ... the 2.0 final release end ... Benjamin Eberlei ...
Read more

Doctrine-Projektleiter Benjamin Eberlei jetzt bei Qafoo ...

Januar ist Benjamin Eberlei Teil des Consulting-Teams Qafoo. ... Doctrine-Projektleiter Benjamin Eberlei jetzt bei Qafoo Online seit 4 Jahren online.
Read more

Doctrine 2.4 Beta2 released — Doctrine Project

Doctrine 2.4 Beta2 released. 11.05.2013. ... 2013, posted by Benjamin Eberlei in categories Release. Doctrine 2.4 Beta2 released. 11.05.2013. We have ...
Read more

Talk to your database with Doctrine - phpconference.com.ar

Talk to your database with Doctrine PHP Conference Argentina 2013 Benjamin Eberlei, @beberlei 5th October 2013
Read more

Benjamin Eberlei | Qafoo GmbH | ZoomInfo.com

View Benjamin Eberlei's business profile at Qafoo GmbH and see work ... 2013, posted by Benjamin Eberlei in categories Release. Doctrine 2.4.1 released. ...
Read more

Benjamin Eberlei: Habla con tu base de datos con Doctrine ...

... Habla con tu base de datos con Doctrine ... a los componentes DBAL y ORM de Doctrine 2. ... Portland 2013 - Benjamin Eberlei ...
Read more

SymfonyLive Portland 2013 - Benjamin Eberlei - Functional ...

... Benjamin Eberlei - Functional Testing with Symfony2 ... 27 2. Don't like this ... SymfonyCon Warsaw 2013 - Benjamin Eberlei ...
Read more