ORM dont kill your DB, developers do

ORM Database Developers http://www.ﬂickr.com/photos/hoyvinmayvin/5103806609/ Guilherme Blanco Thursday, March 1, 2012

ORMs donʼt kill your DB, developers do! @guilhermeblanco http://github.com/guilhermeblanco InstaClick Inc. Learning about good ORM practices on @guilhermeblanco talk at @confooca! Thursday, March 1, 2012

Agenda http://www.ﬂickr.com/photos/emerzh/3072428824/ Thursday, March 1, 2012

General Rule RTFM Thursday, March 1, 2012

Dependency management http://www.ﬂickr.com/photos/ollesvensson/3694625903/ Thursday, March 1, 2012

Dependency Management Thursday, March 1, 2012

Dependency Management namespace AppBundleBlogBundleEntity; /** * @ORMEntity */ class Post { /** * @ORMOneToMany( * targetEntity="AppComment:Comment", * mappedBy="post" * ) */ protected $comments; } namespace AppBundleCommentBundleEntity; /** * @ORMEntity */ class Comment { /** * @ORMManyToOne( * targetEntity="AppBlog:Post", * inversedBy="comments" * ) */ protected $post; } Thursday, March 1, 2012

Dependency Management namespace AppBundleBlogBundleEntity; /** * @ORMEntity */ class Post { // ... } namespace AppBundleCommentBundleEntity; /** * @ORMEntity */ class Comment { /** * @ORMManyToOne(targetEntity="AppBlog:Post") */ protected $post; } Thursday, March 1, 2012

Fetch Mode http://www.ﬂickr.com/photos/comedynose/5318259802/ Thursday, March 1, 2012

Fetch Mode A fetching strategy is what ORM will use to retrieve associated objects if the application needs to navigate through them Thursday, March 1, 2012

Fetch Mode Supported by all association types... Thursday, March 1, 2012

Fetch Mode • OneToOne • OneToMany • ManyToOne • ManyToMany Thursday, March 1, 2012

Fetch Mode Available fetch modes... Thursday, March 1, 2012

Fetch Mode • EAGER • LAZY (default) • EXTRA_LAZY Thursday, March 1, 2012

Fetch Mode An association (collection or attribute), is fetched immediately when owner is loaded • EAGER • LAZY (default) • EXTRA_LAZY Thursday, March 1, 2012

Fetch Mode An association (collection or attribute), is fetched immediately when owner is loaded • EAGER An association (collection or attribute), • LAZY (default) is fetched when the application invokes an operation over it • EXTRA_LAZY Thursday, March 1, 2012

Fetch Mode An association (collection or attribute), is fetched immediately when owner is loaded • EAGER An association (collection or attribute), • LAZY (default) is fetched when the application invokes an operation over it • EXTRA_LAZY Individual elements from association are accessed from Database as needed. ORM does not fetch the whole association unless it is absolutely necessary Thursday, March 1, 2012

Fetch Mode /** * @ORMEntity */ class Post { /** * @ORMManyToOne(targetEntity="User", fetchMode="EAGER") */ protected $author; /** * @ORMManyToMany(targetEntity="Tags", fetchMode="EXTRA_LAZY") * @ORMJoinTable(name="posts_tags") */ protected $tagList; } /** * @ORMEntity */ class Comment { /** * @ORMOneToMany(targetEntity="Post", fetchMode="LAZY") * @JoinColumn(name="post_id", referencedColumnName="id") */ protected $post; } Thursday, March 1, 2012

Fetch Mode /** * @ORMEntity */ Deadly sin class Post { /** * @ORMManyToOne(targetEntity="User", fetchMode="EAGER") */ protected $author; /** * @ORMManyToMany(targetEntity="Tags", fetchMode="EXTRA_LAZY") * @ORMJoinTable(name="posts_tags") */ protected $tagList; } /** * @ORMEntity */ class Comment { /** * @ORMOneToMany(targetEntity="Post", fetchMode="LAZY") * @JoinColumn(name="post_id", referencedColumnName="id") */ protected $post; } Thursday, March 1, 2012

Querying http://www.ﬂickr.com/photos/pschadler/4932737690/ Thursday, March 1, 2012

Querying $query = $entityManager->createQuery(' SELECT p FROM Post p '); $postList = $query->getResult(); foreach ($postList as $post) { $tagList = $post->getTagList(); foreach ($tagList as $tag) { echo $tag->getName(); } } Thursday, March 1, 2012

Querying $query = $entityManager->createQuery(' SELECT p FROM Post p '); $postList = $query->getResult(); foreach ($postList as $post) { $tagList = $post->getTagList(); N + 1 trolls? foreach ($tagList as $tag) { echo $tag->getName(); } } Thursday, March 1, 2012

Querying $query = $entityManager->createQuery(' SELECT p, t FROM Post p LEFT JOIN p.tagList t '); $postList = $query->getResult(); foreach ($postList as $post) { $tagList = $post->getTagList(); foreach ($tagList as $tag) { echo $tag->getName(); } } Thursday, March 1, 2012

Querying $query = $entityManager->createQuery(' SELECT p, t FROM Post p Pardon? DQL is LEFT JOIN p.tagList t '); powerful! RTFM $postList = $query->getResult(); foreach ($postList as $post) { $tagList = $post->getTagList(); foreach ($tagList as $tag) { echo $tag->getName(); } } Thursday, March 1, 2012

Querying I know you all don’t trust me... Thursday, March 1, 2012

Querying ...but take a look at its EBNF grammar... Thursday, March 1, 2012

QueryLanguage ::= SelectStatement | UpdateStatement | DeleteStatement SelectStatement ::= SelectClause FromClause [WhereClause] [GroupByClause] [HavingClause] [OrderByClause] UpdateStatement ::= UpdateClause [WhereClause] DeleteStatement ::= DeleteClause [WhereClause] JoinAssociationPathExpression ::= IdentificationVariable "." (CollectionValuedAssociationField | SingleValuedAssociationField) AssociationPathExpression ::= CollectionValuedPathExpression | SingleValuedAssociationPathExpression SingleValuedPathExpression ::= StateFieldPathExpression | SingleValuedAssociationPathExpression StateFieldPathExpression ::= IdentificationVariable "." StateField | SingleValuedAssociationPathExpression "." StateField SingleValuedAssociationPathExpression ::= IdentificationVariable "." SingleValuedAssociationField CollectionValuedPathExpression ::= IdentificationVariable "." {SingleValuedAssociationField "."}* CollectionValuedAssociationField StateField ::= {EmbeddedClassStateField "."}* SimpleStateField SimpleStateFieldPathExpression ::= IdentificationVariable "." StateField SelectClause ::= "SELECT" ["DISTINCT"] SelectExpression {"," SelectExpression}* SimpleSelectClause ::= "SELECT" ["DISTINCT"] SimpleSelectExpression UpdateClause ::= "UPDATE" AbstractSchemaName ["AS"] AliasIdentificationVariable "SET" UpdateItem {"," UpdateItem}* DeleteClause ::= "DELETE" ["FROM"] AbstractSchemaName ["AS"] AliasIdentificationVariable FromClause ::= "FROM" IdentificationVariableDeclaration {"," IdentificationVariableDeclaration}* SubselectFromClause ::= "FROM" SubselectIdentificationVariableDeclaration {"," SubselectIdentificationVariableDeclaration}* WhereClause ::= "WHERE" ConditionalExpression HavingClause ::= "HAVING" ConditionalExpression GroupByClause ::= "GROUP" "BY" GroupByItem {"," GroupByItem}* OrderByClause ::= "ORDER" "BY" OrderByItem {"," OrderByItem}* Subselect ::= SimpleSelectClause SubselectFromClause [WhereClause] [GroupByClause] [HavingClause] [OrderByClause] UpdateItem ::= IdentificationVariable "." (StateField | SingleValuedAssociationField) "=" NewValue OrderByItem ::= (ResultVariable | SingleValuedPathExpression) ["ASC" | "DESC"] GroupByItem ::= IdentificationVariable | SingleValuedPathExpression NewValue ::= ScalarExpression | SimpleEntityExpression | "NULL" IdentificationVariableDeclaration ::= RangeVariableDeclaration [IndexBy] {JoinVariableDeclaration}* SubselectIdentificationVariableDeclaration ::= IdentificationVariableDeclaration | (AssociationPathExpression ["AS"] AliasIdentificationVariable) JoinVariableDeclaration ::= Join [IndexBy] RangeVariableDeclaration ::= AbstractSchemaName ["AS"] AliasIdentificationVariable Join ::= ["LEFT" ["OUTER"] | "INNER"] "JOIN" JoinAssociationPathExpression ["AS"] AliasIdentificationVariable ["WITH" ConditionalExpression] IndexBy ::= "INDEX" "BY" SimpleStateFieldPathExpression SelectExpression ::= IdentificationVariable | PartialObjectExpression | (AggregateExpression | "(" Subselect ")" | FunctionDeclaration | ScalarExpression) [["AS"] AliasResultVariable] SimpleSelectExpression ::= ScalarExpression | IdentificationVariable | (AggregateExpression [["AS"] AliasResultVariable]) PartialObjectExpression ::= "PARTIAL" IdentificationVariable "." PartialFieldSet PartialFieldSet ::= "{" SimpleStateField {"," SimpleStateField}* "}" ConditionalExpression ::= ConditionalTerm {"OR" ConditionalTerm}* ConditionalTerm ::= ConditionalFactor {"AND" ConditionalFactor}* ConditionalFactor ::= ["NOT"] ConditionalPrimary ConditionalPrimary ::= SimpleConditionalExpression | "(" ConditionalExpression ")" SimpleConditionalExpression ::= ComparisonExpression | BetweenExpression | LikeExpression | InExpression | NullComparisonExpression | ExistsExpression | EmptyCollectionComparisonExpression | CollectionMemberExpression | InstanceOfExpression EmptyCollectionComparisonExpression ::= CollectionValuedPathExpression "IS" ["NOT"] "EMPTY" CollectionMemberExpression ::= EntityExpression ["NOT"] "MEMBER" ["OF"] CollectionValuedPathExpression Literal ::= string | char | integer | float | boolean InParameter ::= Literal | InputParameter InputParameter ::= PositionalParameter | NamedParameter PositionalParameter ::= "?" integer NamedParameter ::= ":" string ArithmeticExpression ::= SimpleArithmeticExpression | "(" Subselect ")" SimpleArithmeticExpression ::= ArithmeticTerm {("+" | "-") ArithmeticTerm}* ArithmeticTerm ::= ArithmeticFactor {("*" | "/") ArithmeticFactor}* ArithmeticFactor ::= [("+" | "-")] ArithmeticPrimary ArithmeticPrimary ::= SingleValuedPathExpression | Literal | "(" SimpleArithmeticExpression ")" | FunctionsReturningNumerics | AggregateExpression | FunctionsReturningStrings | FunctionsReturningDatetime | IdentificationVariable | InputParameter | CaseExpression ScalarExpression ::= SimpleArithmeticExpression | StringPrimary | DateTimePrimary | StateFieldPathExpression | BooleanPrimary | EntityTypeExpression | CaseExpression StringExpression ::= StringPrimary | "(" Subselect ")" StringPrimary ::= StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression | CaseExpression BooleanExpression ::= BooleanPrimary | "(" Subselect ")" BooleanPrimary ::= StateFieldPathExpression | boolean | InputParameter EntityExpression ::= SingleValuedAssociationPathExpression | SimpleEntityExpression SimpleEntityExpression ::= IdentificationVariable | InputParameter DatetimeExpression ::= DatetimePrimary | "(" Subselect ")" DatetimePrimary ::= StateFieldPathExpression | InputParameter | FunctionsReturningDatetime | AggregateExpression AggregateExpression ::= ("AVG" | "MAX" | "MIN" | "SUM") "(" ["DISTINCT"] StateFieldPathExpression ")" | "COUNT" "(" ["DISTINCT"] (IdentificationVariable | SingleValuedPathExpression) ")" CaseExpression ::= GeneralCaseExpression | SimpleCaseExpression | CoalesceExpression | NullifExpression GeneralCaseExpression ::= "CASE" WhenClause {WhenClause}* "ELSE" ScalarExpression "END" WhenClause ::= "WHEN" ConditionalExpression "THEN" ScalarExpression SimpleCaseExpression ::= "CASE" CaseOperand SimpleWhenClause {SimpleWhenClause}* "ELSE" ScalarExpression "END" CaseOperand ::= StateFieldPathExpression | TypeDiscriminator SimpleWhenClause ::= "WHEN" ScalarExpression "THEN" ScalarExpression CoalesceExpression ::= "COALESCE" "(" ScalarExpression {"," ScalarExpression}* ")" NullifExpression ::= "NULLIF" "(" ScalarExpression "," ScalarExpression ")" QuantifiedExpression ::= ("ALL" | "ANY" | "SOME") "(" Subselect ")" BetweenExpression ::= ArithmeticExpression ["NOT"] "BETWEEN" ArithmeticExpression "AND" ArithmeticExpression ComparisonExpression ::= ArithmeticExpression ComparisonOperator ( QuantifiedExpression | ArithmeticExpression ) InExpression ::= StateFieldPathExpression ["NOT"] "IN" "(" (InParameter {"," InParameter}* | Subselect) ")" InstanceOfExpression ::= IdentificationVariable ["NOT"] "INSTANCE" ["OF"] (InstanceOfParameter | "(" InstanceOfParameter {"," InstanceOfParameter}* ")") InstanceOfParameter ::= AbstractSchemaName | InputParameter LikeExpression ::= StringExpression ["NOT"] "LIKE" string ["ESCAPE" char] NullComparisonExpression ::= (SingleValuedPathExpression | InputParameter) "IS" ["NOT"] "NULL" ExistsExpression ::= ["NOT"] "EXISTS" "(" Subselect ")" ComparisonOperator ::= "=" | "<" | "<=" | "<>" | ">" | ">=" | "!=" FunctionDeclaration ::= FunctionsReturningStrings | FunctionsReturningNumerics | FunctionsReturningDateTime FunctionsReturningNumerics ::= "LENGTH" "(" StringPrimary ")" | "LOCATE" "(" StringPrimary "," StringPrimary ["," SimpleArithmeticExpression]")" | "ABS" "(" SimpleArithmeticExpression ")" | "SQRT" "(" SimpleArithmeticExpression ")" | "MOD" "(" SimpleArithmeticExpression "," SimpleArithmeticExpression ")" | "SIZE" "(" CollectionValuedPathExpression ")" FunctionsReturningDateTime ::= "CURRENT_DATE" | "CURRENT_TIME" | "CURRENT_TIMESTAMP" FunctionsReturningStrings ::= "CONCAT" "(" StringPrimary "," StringPrimary ")" | "SUBSTRING" "(" StringPrimary "," SimpleArithmeticExpression "," SimpleArithmeticExpression ")" | "TRIM" "(" [["LEADING" | "TRAILING" | "BOTH"] [char] "FROM"] StringPrimary ")" | "LOWER" "(" StringPrimary ")" | "UPPER" "(" StringPrimary ")" Thursday, March 1, 2012

Querying Since the letter size is 6, you may still not believe me Thursday, March 1, 2012

Querying let’s try a real world example! Thursday, March 1, 2012

Querying Do you know Facebook activity wall? Thursday, March 1, 2012

Querying Do you know Facebook activity wall? Let’s show it! Thursday, March 1, 2012

SELECT e, a FROM Entry e JOIN e.wall w JOIN e.user u LEFT JOIN u.avatar av WHERE u = :user OR w IN ( SELECT uw FROM UserWall uw WHERE uw.user = :user OR uw.user IN ( SELECT CASE WHEN ua = us.subscriber THEN ub.id ELSE ua.id END FROM UserSubscription us JOIN us.friendship f JOIN f.userA ua JOIN f.userB ub WHERE us.muted = FALSE AND us.subscriber = :user AND f.status = 'Approved' ) ) OR w IN ( SELECT gw FROM GroupWall gw WHERE gw.group IN ( SELECT DISTINCT g.id FROM GroupSubscription gs JOIN gs.group g WHERE gs.subscriber = :user AND gs.muted = FALSE AND gs.status = 'Approved' ) ) ORDER BY e.created DESC Thursday, March 1, 2012

SELECT e, a FROM Entry e JOIN e.wall w Entry you wrote JOIN e.user u LEFT JOIN u.avatar av WHERE u = :user OR w IN ( SELECT uw FROM UserWall uw WHERE uw.user = :user OR uw.user IN ( SELECT CASE WHEN ua = us.subscriber THEN ub.id ELSE ua.id END FROM UserSubscription us JOIN us.friendship f JOIN f.userA ua JOIN f.userB ub WHERE us.muted = FALSE AND us.subscriber = :user AND f.status = 'Approved' ) ) OR w IN ( SELECT gw FROM GroupWall gw WHERE gw.group IN ( SELECT DISTINCT g.id FROM GroupSubscription gs JOIN gs.group g WHERE gs.subscriber = :user AND gs.muted = FALSE AND gs.status = 'Approved' ) ) ORDER BY e.created DESC Thursday, March 1, 2012

SELECT e, a FROM Entry e JOIN e.wall w Entry you wrote JOIN e.user u LEFT JOIN u.avatar av WHERE u = :user OR w IN ( Entry was in your Wall SELECT uw FROM UserWall uw WHERE uw.user = :user OR uw.user IN ( SELECT CASE WHEN ua = us.subscriber THEN ub.id ELSE ua.id END FROM UserSubscription us JOIN us.friendship f JOIN f.userA ua JOIN f.userB ub WHERE us.muted = FALSE AND us.subscriber = :user AND f.status = 'Approved' ) ) OR w IN ( SELECT gw FROM GroupWall gw WHERE gw.group IN ( SELECT DISTINCT g.id FROM GroupSubscription gs JOIN gs.group g WHERE gs.subscriber = :user AND gs.muted = FALSE AND gs.status = 'Approved' ) ) ORDER BY e.created DESC Thursday, March 1, 2012

SELECT e, a FROM Entry e JOIN e.wall w Entry you wrote JOIN e.user u LEFT JOIN u.avatar av WHERE u = :user OR w IN ( Entry was in your Wall SELECT uw FROM UserWall uw WHERE uw.user = :user OR uw.user IN ( SELECT CASE WHEN ua = us.subscriber THEN ub.id ELSE ua.id END FROM UserSubscription us User is your friend, the friendship is JOIN us.friendship f approved and subscription is not muted JOIN f.userA ua JOIN f.userB ub WHERE us.muted = FALSE AND us.subscriber = :user AND f.status = 'Approved' ) ) OR w IN ( SELECT gw FROM GroupWall gw WHERE gw.group IN ( SELECT DISTINCT g.id FROM GroupSubscription gs JOIN gs.group g WHERE gs.subscriber = :user AND gs.muted = FALSE AND gs.status = 'Approved' ) ) ORDER BY e.created DESC Thursday, March 1, 2012

SELECT e, a FROM Entry e JOIN e.wall w Entry you wrote JOIN e.user u LEFT JOIN u.avatar av WHERE u = :user OR w IN ( Entry was in your Wall SELECT uw FROM UserWall uw WHERE uw.user = :user OR uw.user IN ( SELECT CASE WHEN ua = us.subscriber THEN ub.id ELSE ua.id END FROM UserSubscription us User is your friend, the friendship is JOIN us.friendship f approved and subscription is not muted JOIN f.userA ua JOIN f.userB ub WHERE us.muted = FALSE AND us.subscriber = :user AND f.status = 'Approved' ) ) OR w IN ( SELECT gw FROM GroupWall gw WHERE gw.group IN ( SELECT DISTINCT g.id FROM GroupSubscription gs JOIN gs.group g Group that you participate, is not WHERE gs.subscriber = :user AND gs.muted = FALSE muted and have an approved status AND gs.status = 'Approved' ) ) ORDER BY e.created DESC Thursday, March 1, 2012

Caching http://www.ﬂickr.com/photos/eszter/3851576235/ Thursday, March 1, 2012

Caching Doctrine supports 3 levels of caching... Thursday, March 1, 2012

Caching • Metadata cache • Query cache • Result cache Thursday, March 1, 2012

Caching Cache mapping of entities • Metadata cache • Query cache • Result cache Thursday, March 1, 2012

Caching Cache mapping of entities • Metadata cache • Query cache Cache DQL to SQL conversion • Result cache Thursday, March 1, 2012

Caching Cache mapping of entities • Metadata cache • Query cache Cache DQL to SQL conversion • Result cache Cache PDO result set Thursday, March 1, 2012

Caching $query = $entityManager->createQuery(' SELECT p FROM Post p '); $query->useResultCache(true, 600, 'find-all-posts'); $postList = $query->getResult(); Thursday, March 1, 2012

Indexing http://www.ﬂickr.com/photos/osuarchives/2659419894/ Thursday, March 1, 2012

Indexing Doctrine is able to hint the Schema Tool to generate database indexes on speciﬁed table columns Thursday, March 1, 2012

Indexing /** * @ORMEntity * @ORMTable( * name = "users", * uniqueConstraints = { * @ORMUniqueConstraint( * name = "user_unique", * columns = { "name" } * ) * }, * indexes = { * @ORMIndex( * name = "login_idx", * columns = { "name", "password" } * ) * } * ) */ class User { // ... } Thursday, March 1, 2012

Indexing @ORMUniqueConstraint( name = "user_unique", columns = { "name" } ) CREATE UNIQUE INDEX user_unique ON TABLE users (name); Thursday, March 1, 2012

Indexing @ORMIndex( name = "login_idx", columns = { "name", "password" } ) CREATE INDEX login_idx ON TABLE users (name, password); Thursday, March 1, 2012

Indexing @ORMIndex( name = "login_idx", columns = { "name", "password" } ) Some drivers restrict to 32 chars as index name CREATE INDEX login_idx ON TABLE users (name, password); Thursday, March 1, 2012

Inheritance http://www.ﬂickr.com/photos/duanekeys/307005468/ Thursday, March 1, 2012

Inheritance Inheritance is a very deep topic in ORMs Thursday, March 1, 2012

Inheritance Each inheritance type has advantages and disadvantages Thursday, March 1, 2012

Inheritance • Concrete Table Inheritance • Single Table Inheritance • Class Table Inheritance Thursday, March 1, 2012

Inheritance One Class, one Table • Concrete Table Inheritance • Single Table Inheritance • Class Table Inheritance Thursday, March 1, 2012

Inheritance One Class, one Table • Concrete Table Inheritance • Single Table Inheritance Multiple Classes, one Table • Class Table Inheritance Thursday, March 1, 2012

Inheritance One Class, one Table • Concrete Table Inheritance • Single Table Inheritance Multiple Classes, one Table • Class Table Inheritance Multiple Classes, multiple Tables Thursday, March 1, 2012

Inheritance • Concrete Table Inheritance Thursday, March 1, 2012

Inheritance • Concrete Table Inheritance • Pros Thursday, March 1, 2012

Inheritance • Concrete Table Inheritance • Pros • No locking problems Thursday, March 1, 2012

Inheritance • Concrete Table Inheritance • Pros • No locking problems • No irrelevant columns Thursday, March 1, 2012

Inheritance • Concrete Table Inheritance • Pros • No locking problems • No irrelevant columns • Cons Thursday, March 1, 2012

Inheritance • Concrete Table Inheritance • Pros • No locking problems • No irrelevant columns • Cons • Difﬁcult to deal with primary keys Thursday, March 1, 2012

Inheritance • Concrete Table Inheritance • Pros • No locking problems • No irrelevant columns • Cons • Difﬁcult to deal with primary keys • Search base class means search all tables Thursday, March 1, 2012

Inheritance • Concrete Table Inheritance • Pros • No locking problems • No irrelevant columns • Cons • Difﬁcult to deal with primary keys • Search base class means search all tables • Update on columns means update on all hierarchy tables Thursday, March 1, 2012

Inheritance /** * @ORMMappedSuperclass */ class Person { /** * @ORMColumn(type="string", length=50) */ protected $firstName; /** * @ORMColumn(type="string", length=50) */ protected $lastName; } /** * @ORMEntity * @ORMTable(name="users") */ class User extends Person { /** * @ORMId * @ORMGeneratedValue * @ORMColumn(type="integer") */ protected $id; } Thursday, March 1, 2012

Inheritance CREATE TABLE users ( id INTEGER AUTO_INCREMENT NOT NULL, firstName VARCHAR(50) NOT NULL, lastName VARCHAR(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB; Thursday, March 1, 2012

Inheritance • Single Table Inheritance Thursday, March 1, 2012

Inheritance • Single Table Inheritance • Pros Thursday, March 1, 2012

Inheritance • Single Table Inheritance • Pros • Only one table for hierarchy Thursday, March 1, 2012

Inheritance • Single Table Inheritance • Pros • Only one table for hierarchy • No joins Thursday, March 1, 2012

Inheritance • Single Table Inheritance • Pros • Only one table for hierarchy • No joins • Refactoring of ﬁelds means no change to database table Thursday, March 1, 2012

Inheritance • Single Table Inheritance • Pros • Only one table for hierarchy • No joins • Refactoring of ﬁelds means no change to database table • Cons Thursday, March 1, 2012

Inheritance • Single Table Inheritance • Pros • Only one table for hierarchy • No joins • Refactoring of ﬁelds means no change to database table • Cons • Waste of space in database Thursday, March 1, 2012

Inheritance • Single Table Inheritance • Pros • Only one table for hierarchy • No joins • Refactoring of ﬁelds means no change to database table • Cons • Waste of space in database • Too many locks due to many accesses Thursday, March 1, 2012

Inheritance • Single Table Inheritance • Pros • Only one table for hierarchy • No joins • Refactoring of ﬁelds means no change to database table • Cons • Waste of space in database • Too many locks due to many accesses • No duplicated ﬁeld names with different meanings Thursday, March 1, 2012

Inheritance namespace MyAppEntity; /** * @ORMEntity * @ORMTable(name= "people") * @ORMInheritanceType("SINGLE_TABLE") * @ORMDiscriminatorColumn(name="discr", type="string") * @ORMDiscriminatorMap({ * "user" = "MyAppEntityUser", * "employee" = "MyAppEntityEmployee" * }) */ class User { // ... } /** * @ORMEntity */ class Employee extends User { // ... } Thursday, March 1, 2012

Inheritance CREATE TABLE people ( id INT AUTO_INCREMENT NOT NULL, firstName VARCHAR(50) NOT NULL, lastName VARCHAR(50) NOT NULL, discr VARCHAR(20) NOT NULL, role VARCHAR(100) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB; Thursday, March 1, 2012

Inheritance • Class Table Inheritance Thursday, March 1, 2012

Inheritance • Class Table Inheritance • Pros Thursday, March 1, 2012

Inheritance • Class Table Inheritance • Pros • Easy to understand Thursday, March 1, 2012

Inheritance • Class Table Inheritance • Pros • Easy to understand • Database space is optimized due to table normalization Thursday, March 1, 2012

Inheritance • Class Table Inheritance • Pros • Easy to understand • Database space is optimized due to table normalization • Direct relationship between Domain Model and Database Thursday, March 1, 2012

Inheritance • Class Table Inheritance • Pros • Easy to understand • Database space is optimized due to table normalization • Direct relationship between Domain Model and Database • Cons Thursday, March 1, 2012

Inheritance • Class Table Inheritance • Pros • Easy to understand • Database space is optimized due to table normalization • Direct relationship between Domain Model and Database • Cons • Too many joins Thursday, March 1, 2012

Inheritance • Class Table Inheritance • Pros • Easy to understand • Database space is optimized due to table normalization • Direct relationship between Domain Model and Database • Cons • Too many joins • Refactoring of ﬁelds needs a database schema update Thursday, March 1, 2012

Inheritance • Class Table Inheritance • Pros • Easy to understand • Database space is optimized due to table normalization • Direct relationship between Domain Model and Database • Cons • Too many joins • Refactoring of ﬁelds needs a database schema update • Superclass table accessed a lot, it means it may enter in lock mode Thursday, March 1, 2012

Inheritance namespace MyAppEntity; /** * @ORMEntity * @ORMTable(name="users") * @ORMInheritanceType("JOINED") * @ORMDiscriminatorColumn( * name = "discr", * type = "string", * length = 20 * ) * @ORMDiscriminatorMap({ * "user" = "MyAppEntityUser", * "employee" = "MyAppEntityEmployee" * }) */ class User { // ... } /** * @ORMEntity * @ORMTable(name= "employees") */ class Employee extends User { // ... } Thursday, March 1, 2012

Inheritance CREATE TABLE users ( id INT AUTO_INCREMENT NOT NULL, firstName VARCHAR(50) NOT NULL, lastName VARCHAR(50) NOT NULL, discr VARCHAR(20) NOT NULL, PRIMARY KEY (id) ) ENGINE = InnoDB; CREATE TABLE employees ( id INT NOT NULL, role VARCHAR(100) NOT NULL, PRIMARY KEY (id) ) ENGINE = InnoDB; ALTER TABLE employees ADD CONSTRAINT FK_BA82C300BF396750 FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE; Thursday, March 1, 2012

Inheritance What’s the problem with inheritance? Thursday, March 1, 2012

Inheritance What’s the problem with inheritance? Hydration Thursday, March 1, 2012

Inheritance Thursday, March 1, 2012

Inheritance • Inverse side *ToOne Thursday, March 1, 2012

Inheritance • Inverse side *ToOne • Owning side *ToOne with Inheritance Thursday, March 1, 2012

Inheritance • Inverse side *ToOne • Owning side *ToOne with Inheritance • Disabled deferEagerLoad while Querying Thursday, March 1, 2012

Cascades http://www.ﬂickr.com/photos/over_kind_man/3158552109/ Thursday, March 1, 2012

Cascades Ability to tell Unit Of Work of Doctrine how it should act when inspecting an association for graph changes Thursday, March 1, 2012

Cascades Use judiciously Thursday, March 1, 2012

Cascades It can heavily increase the graph edges to be navigated once the Compute Changeset algorithm is ran Thursday, March 1, 2012

Cascades This means more time to persist, delete, merge, detach, refresh... Thursday, March 1, 2012

Cascades /** * @ORMEntity */ class User { //... /** * Bidirectional - One-To-Many (INVERSE SIDE) * * @ORMOneToMany( * targetEntity = "Comment", * mappedBy = "author", * cascade = { "persist", "remove" } * ) */ protected $commentsAuthored; } Thursday, March 1, 2012

Hydrators http://crimsonnightstock.deviantart.com/art/Yellow-Fire-Hydrant-Stock-100121929 Thursday, March 1, 2012

Hydrators Doctrine comes with some useful result hydrators Thursday, March 1, 2012

Hydrators This means that not all times you need to retrieve objects Thursday, March 1, 2012

Hydrators • Object • Array • Scalar • Iterable • Simple Object • Single Scalar Thursday, March 1, 2012

Hydrators • Object $resultList = $query->getResult(); • Array • Scalar • Iterable • Simple Object • Single Scalar Thursday, March 1, 2012

Hydrators • Object $resultList = $query->getResult(); • Array $resultArray = $query->getArrayResult(); • Scalar • Iterable • Simple Object • Single Scalar Thursday, March 1, 2012

Hydrators • Object $resultList = $query->getResult(); • Array $resultArray = $query->getArrayResult(); • Scalar $result = $query->getScalarResult(); • Iterable • Simple Object • Single Scalar Thursday, March 1, 2012

Hydrators • Object $resultList = $query->getResult(); • Array $resultArray = $query->getArrayResult(); • Scalar $result = $query->getScalarResult(); • Iterable $iterator = $query->iterate(); • Simple Object • Single Scalar Thursday, March 1, 2012

Hydrators • Object $resultList = $query->getResult(); • Array $resultArray = $query->getArrayResult(); • Scalar $result = $query->getScalarResult(); • Iterable $iterator = $query->iterate(); • Simple Object $result = $query->getSingleResult(); • Single Scalar Thursday, March 1, 2012

Hydrators • Object $resultList = $query->getResult(); • Array $resultArray = $query->getArrayResult(); • Scalar $result = $query->getScalarResult(); • Iterable $iterator = $query->iterate(); • Simple Object $result = $query->getSingleResult(); • Single Scalar $result = $query->getSingleScalarResult(); Thursday, March 1, 2012

http://joind.in/6053 Questions? @guilhermeblanco http://github.com/guilhermeblanco InstaClick Inc. Thursday, March 1, 2012

http://joind.in/6053 Questions? @guilhermeblanco http://github.com/guilhermeblanco InstaClick Inc. We are hiring! Talk to me privately if interested Thursday, March 1, 2012

http://joind.in/6053 Questions? Thank you! @guilhermeblanco http://github.com/guilhermeblanco InstaClick Inc. We are hiring! Talk to me privately if interested Thursday, March 1, 2012

ORM dont kill your DB, developers do

More Related Content

Viewers also liked

Similar to ORM dont kill your DB, developers do

More from Guilherme Blanco

Recently uploaded

ORM dont kill your DB, developers do