Movatterモバイル変換

Practicalobject-orientedmodels in SQLBill KarwinPostgreSQL Conference West 09 • 2009/10/17

Me• 20+ years experience • Application/SDK developer • Support, Training, Proj Mgmt • C, Java, Perl, PHP• SQL maven • MySQL, PostgreSQL, InterBase • Zend Framework • Oracle, SQL Server, IBM DB2, SQLite• Community contributor

Object-Oriented vs. Relational• Impedance mismatch• OO operates on instances; RDBMS operates on sets• Compromise either OO strengths or relational strengths

Entity-Attribute-ValueIf you try and take a cat apart to see how it works, theﬁrst thing you have on your hands is a non-working cat. — Richard Dawkins

Entity-Attribute-Value • Objective: extensibility • Variable sets of attributesbug_id bug_type priority description severity sponsor crashes when loss of 1234 BUG high saving functionality 3456 FEATURE low support XML Acme Corp.

Entity-Attribute-Value Meta-TableCREATE TABLE eav ( bug_id INT NOT NULL, attr_name VARCHAR(20) NOT NULL, attr_value VARCHAR(100), PRIMARY KEY (bug_id, attr_name), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)); mixing data with metadata

Entity-Attribute-ValueWhat does this look like?bug_id attr_name attr_value 1234 priority high 1234 description crashes when saving 1234 severity loss of functionality 3456 priority low 3456 description support XML 3456 sponsor Acme Corp.

Entity-Attribute-Value Problems: namesbug_id attr_name attr_value 1234 created 2008-04-01 3456 created_date 2008-04-01

Entity-Attribute-Value Problems: valuesbug_id attr_name attr_value 1234 created_date 2008-02-31 3456 created_date banana

Entity-Attribute-Value Problems: constraints NOT NULL• Difﬁcult to enforce mandatory attributes • SQL constraints apply to columns, not rows • No way to declare that a row must exist with a certain attr_name value (‘created_date’)

Entity-Attribute-Value Problems: constraints• Difﬁcult to use lookup tables bug_id attr_name attr_value 1234 priority high 3456 priority medium 5678 priority banana • Constraints apply to all rows in the column, not selected rows depending on value in attr_name

Entity-Attribute-Value Problems: queries • Difﬁcult to reconstruct a row of attributes: SELECT b.bug_id, need one JOIN e1.attr_value AS created_date, e2.attr_value AS priority, per attribute e3.attr_value AS description, e4.attr_value AS status, e5.attr_value AS reported_by FROM Bugs b LEFT JOIN eav e1 ON (b.bug_id = e1.bug_id AND e1.attr_name = ‘created_date’) LEFT JOIN eav e2 ON (b.bug_id = e2.bug_id AND e2.attr_name = ‘priority’) LEFT JOIN eav e3 ON (b.bug_id = e3.bug_id AND e3.attr_name = ‘description’) LEFT JOIN eav e4 ON (b.bug_id = e4.bug_id AND e4.attr_name = ‘status’) LEFT JOIN eav e5 ON (b.bug_id = e5.bug_id AND e5.attr_name = ‘reported_by’);bug_id created_date priority description status reported_by 1234 2008-04-01 high Crashes when I save. NEW Bill

Entity-Attribute-Value Solution?Do it all in application logic? ☒☒☒

Entity-Attribute-Value Solution Use metadata for metadata• Deﬁne attributes in columns• ALTER TABLE to add attribute columns• Deﬁne related tables for related types ☑

Entity-Attribute-Value Single Table Inheritance• One table with many columns• Inapplicable columns are NULL CREATE TABLE Issues ( issue_id SERIAL PRIMARY KEY, created_date DATE NOT NULL, priority VARCHAR(20), description TEXT, issue_type CHAR(1) CHECK (issue_type IN (‘B’, ‘F’)), bug_severity VARCHAR(20), feature_sponsor VARCHAR(100) );

Entity-Attribute-Value Concrete Table Inheritance • Deﬁne similar tables for similar types • Duplicate common columns in each tableCREATE TABLE Bugs ( CREATE TABLE Features ( bug_id SERIAL PRIMARY KEY, bug_id SERIAL PRIMARY KEY, created_date DATE NOT NULL, created_date DATE NOT NULL, priority VARCHAR(20), priority VARCHAR(20), description TEXT, description TEXT, severity VARCHAR(20) sponsor VARCHAR(100) ); );

Entity-Attribute-Value Concrete Table Inheritance• Use UNION to search both tables: SELECT u.* FROM ( SELECT issue_id, description FROM Bugs UNION ALL SELECT issue_id, description FROM Features )u WHERE u.description LIKE ...

Entity-Attribute-Value Class Table Inheritance • Common columns in base table • Subtype-speciﬁc columns in subtype tablesCREATE TABLE Bugs ( CREATE TABLE Features ( issue_id INT PRIMARY KEY, issue_id INT PRIMARY KEY, severity VARCHAR(20), sponsorVARCHAR(100), FOREIGN KEY (issue_id) FOREIGN KEY (issue_id) REFERENCES Issues (issue_id) REFERENCES Issues (issue_id)); ); CREATE TABLE Issues ( issue_id SERIAL PRIMARY KEY, created_date DATE NOT NULL priority VARCHAR(20), description TEXT );

Entity-Attribute-Value Class Table Inheritance• Easy to query common columns: SELECT * FROM Issues WHERE description LIKE ... ;• Easy to query one subtype at a time: SELECT * FROM Issues JOIN Bugs USING (issue_id) WHERE description LIKE ... ;

Entity-Attribute-Value Using EAV appropriately• If attributes must be fully dynamic• Enforce constraints in application code• Don’t try to fetch a single row per entity• Consider non-relational solutions for semi-structured data, e.g. RDF/XML

Polymorphic Associations Of course, some people do go both ways. — The Scarecrow

Polymorphic Assocations• Objective: reference multiple parents BUGS COMMENTS FEATURES

Polymorphic Assocations What does this look like? comment issue_idissue_id comment issue_type issue_id id ... ... 6789 “It crashes” Bugs 1234 1234 2345 9876 “Great idea!” Features 2345 Bugs Features Comments mixing data with metadata

Polymorphic Assocations Problem: constraints• A FOREIGN KEY constraint can’t reference two tables: CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, comment TEXT NOT NULL, issue_type VARCHAR(15) NOT NULL CHECK (issue_type IN (‘Bugs’, ‘Features’)), issue_id INT NOT NULL, FOREIGN KEY issue_id REFERENCES ); you need this to be Bugs or Features

Polymorphic Assocations Problem: constraints• You have to deﬁne table with no referential integrity: CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, comment TEXT NOT NULL, issue_type VARCHAR(15) NOT NULL CHECK (issue_type IN (‘Bugs’, ‘Features’)), issue_id INT NOT NULL );

Polymorphic Assocations Problem: queries• You can’t use a different table for each row. SELECT * FROM Comments JOIN USING (issue_id); you need this to be Bugs or Features

Polymorphic Assocations Problem: queries• You have to join to each parent table: SELECT * FROM Comments c LEFT JOIN Bugs b ON (c.issue_type = ‘Bugs’ AND c.issue_id = b.issue_id) LEFT JOIN Features f ON (c.issue_type = ‘Features’ AND c.issue_id = f.issue_id); you have to get these strings right

Polymorphic Assocations Solutions• Exclusive arcs• Reverse the relationship• Base parent table

Polymorphic Assocations Exclusive ArcsCREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, comment TEXT NOT NULL, bug_id INT NULL, both columns nullable; feature_id INT NULL, exactly one must be non-null FOREIGN KEY bug_id REFERENCES Bugs(bug_id), FOREIGN KEY feature_id REFERENCES Features(feature_id));

Polymorphic Assocations Exclusive Arcs• Referential integrity is enforced• But hard to ensure exactly one is non-null• Queries are easier: SELECT * FROM Comments c LEFT JOIN Bugs b USING (bug_id) LEFT JOIN Features f USING (feature_id);

Polymorphic Assocations Reverse the relationship BUGS COMMENTS BUGSCOMMENTS FEATURES COMMENTS FEATURES

Polymorphic Assocations Reverse the relationshipCREATE TABLE BugsComments ( comment_id INT NOT NULL, not many-to-many bug_id INT NOT NULL, PRIMARY KEY (comment_id), FOREIGN KEY (comment_id) REFERENCES Comments(comment_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id));CREATE TABLE FeaturesComments ( comment_id INT NOT NULL, feature_id INT NOT NULL, PRIMARY KEY (comment_id), FOREIGN KEY (comment_id) REFERENCES Comments(comment_id), FOREIGN KEY (feature_id) REFERENCES Features(feature_id));

Polymorphic Assocations Reverse the relationship• Referential integrity is enforced• Query comments for a given bug: SELECT * FROM BugsComments b JOIN Comments c USING (comment_id) WHERE b.bug_id = 1234;• Query bug/feature for a given comment: SELECT * FROM Comments LEFT JOIN (BugsComments JOIN Bugs USING (bug_id)) USING (comment_id) LEFT JOIN (FeaturesComments JOIN Features USING (feature_id)) USING (comment_id) WHERE comment_id = 9876;

Polymorphic Assocations Base parent tableBUGS ISSUES FEATURES COMMENTS

Polymorphic Assocations Base parent table works great with CREATE TABLE Issues ( Class Table issue_id SERIAL PRIMARY KEY Inheritance );CREATE TABLE Bugs ( CREATE TABLE Features ( issue_id INT PRIMARY KEY, issue_id INT PRIMARY KEY, ... ... FOREIGN KEY (issue_id) FOREIGN KEY (issue_id) REFERENCES Issues(issue_id) REFERENCES Issues(issue_id)); ); CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, comment TEXT NOT NULL, issue_id INT NOT NULL, FOREIGN KEY (issue_id) REFRENCES Issues(issue_id) );

Polymorphic Assocations Base parent table• Referential integrity is enforced• Queries are easier: SELECT * FROM Comments JOIN Issues USING (issue_id) LEFT JOIN Bugs USING (issue_id) LEFT JOIN Features USING (issue_id);

Polymorphic Assocations Enforcing disjoint subtypesCREATE TABLE Issues ( issue_id SERIAL PRIMARY KEY, issue_type CHAR(1) CHECK (issue_type IN (‘B’, ‘F’)), UNIQUE KEY (issue_id, issue_type)); referentialCREATE TABLE Bugs ( integrity issue_id INT PRIMARY KEY, issue_type CHAR(1) CHECK (issue_type = ‘B’), ... FOREIGN KEY (issue_id, issue_type) REFERENCES Issues(issue_id, issue_type));

Naive TreesA tree is a tree – how many more do you need to look at? — Ronald Reagan

Naive Trees• Objective: store & query hierarchical data • Categories/subcategories • Bill of materials • Threaded discussions

Naive TreesWhat does this look like? (1) Fran: What’s the cause of this bug? (2) Ollie: (4) Kukla: I think it’s a null We need to pointer. check valid input. (3) Fran: (6) Fran: (5) Ollie:No, I checked for Yes, please add a Yes, that’s a bug. that. check. (7) Kukla: That ﬁxed it.

Naive Trees Adjacency List design• Naive solution nearly everyone uses• Each entry knows its immediate parent comment_id parent_id author comment 1 NULL Fran What’s the cause of this bug? 2 1 Ollie I think it’s a null pointer. 3 2 Fran No, I checked for that. 4 1 Kukla We need to check valid input. 5 4 Ollie Yes, that’s a bug. 6 4 Fran Yes, please add a check 7 6 Kukla That ﬁxed it.

Naive Trees Adjacency List strengths• Easy to inserting a new comment: INSERT INTO Comments (parent_id, author, comment) VALUES (7, ‘Kukla’, ‘Thanks!’);• Easy to move a subtree to a new position: UPDATE Comments SET parent_id = 3 WHERE comment_id = 6;

Naive Trees Adjacency List strengths• Querying a node’s children is easy: SELECT * FROM Comments c1 LEFT JOIN Comments c2 ON (c2.parent_id = c1.comment_id);• Querying a node’s parent is easy: SELECT * FROM Comments c1 JOIN Comments c2 ON (c1.parent_id = c2.comment_id);

Naive Trees Adjacency List problems• Hard to query all descendants in a deep tree: SELECT * FROM Comments c1 LEFT JOIN Comments c2 ON (c2.parent_id = c1.comment_id) LEFT JOIN Comments c3 ON (c3.parent_id = c2.comment_id) LEFT JOIN Comments c4 ON (c4.parent_id = c3.comment_id) LEFT JOIN Comments c5 ON (c5.parent_id = c4.comment_id) LEFT JOIN Comments c6 ON (c6.parent_id = c5.comment_id) LEFT JOIN Comments c7 ON (c7.parent_id = c6.comment_id) LEFT JOIN Comments c8 ON (c8.parent_id = c7.comment_id) LEFT JOIN Comments c9 ON (c9.parent_id = c8.comment_id) LEFT JOIN Comments c10 ON (c10.parent_id = c9.comment_id) ... it still doesn’t support unlimited depth!

Naive Trees SQL-99 recursive syntaxWITH RECURSIVE CommentTree (comment_id, bug_id, parent_id, author, comment, depth)AS ( SELECT *, 0 AS depth FROM Comments WHERE parent_id IS NULL UNION ALL SELECT c.*, ct.depth+1 AS depth FROM CommentTree ct JOIN Comments c ON (ct.comment_id = c.parent_id))SELECT * FROM CommentTree WHERE bug_id = 1234; supported in PostgreSQL 8.4 & MS SQL Server 2005

Naive Trees Path Enumeration• Store chain of ancestors in each node good for breadcrumbs comment_id path author comment 1 1/ Fran What’s the cause of this bug? 2 1/2/ Ollie I think it’s a null pointer. 3 1/2/3/ Fran No, I checked for that. 4 1/4/ Kukla We need to check valid input. 5 1/4/5/ Ollie Yes, that’s a bug. 6 1/4/6/ Fran Yes, please add a check 7 1/4/6/7/ Kukla That ﬁxed it.

Naive Trees Path Enumeration strengths• Easy to query ancestors of comment #7: SELECT * FROM Comments WHERE ‘1/4/6/7/’ LIKE path || ‘%’;• Easy to query descendants of comment #4: SELECT * FROM Comments WHERE path LIKE ‘1/4/%’;

Naive Trees Path Enumeration strengths• Easy to add child of comment 7: INSERT INTO Comments (author, comment) VALUES (‘Ollie’, ‘Good job!’); SELECT path FROM Comments WHERE comment_id = 7; UPDATE Comments SET path = $parent_path || LASTVAL() || ‘/’ WHERE comment_id = LASTVAL();

Naive Trees Nested Sets• Each comment encodes its descendants using two numbers: • A comment’s left number is less than all numbers used by the comment’s descendants. • A comment’s right number is greater than all numbers used by the comment’s descendants. • A comment’s numbers are between all numbers used by the comment’s ancestors.

Naive TreesNested Sets illustration (1) Fran: What’s the cause of this bug? 1 14 (2) Ollie: (4) Kukla: I think it’s a null We need to check pointer. valid input. 2 5 6 13 (3) Fran: (6) Fran: (5) Ollie: No, I checked for Yes, please add a Yes, that’s a bug. that. check. 3 4 7 8 9 12 (7) Kukla: That ﬁxed it. 10 11

Naive Trees Nested Sets examplecomment_id nsleft nsright author comment 1 1 14 Fran What’s the cause of this bug? 2 2 5 Ollie I think it’s a null pointer. 3 3 4 Fran No, I checked for that. 4 6 13 Kukla We need to check valid input. 5 7 8 Ollie Yes, that’s a bug. 6 9 12 Fran Yes, please add a check 7 10 11 Kukla That ﬁxed it. these are not foreign keys

Naive Trees Nested Sets strengths• Easy to query all ancestors of comment #7: SELECT * FROM Comments child JOIN Comments ancestor ON (child.nsleft BETWEEN ancestor.nsleft AND ancestor.nsright) WHERE child.comment_id = 7;

Naive TreesNested Sets strengths (1) Fran: ancestors What’s the cause of this bug? 1 14 (2) Ollie: (4) Kukla: I think it’s a null We need to check pointer. valid input. 2 5 6 13 child (3) Fran: (6) Fran: (5) Ollie: No, I checked for Yes, please add a Yes, that’s a bug. that. check.3 4 7 8 9 12 (7) Kukla: That ﬁxed it. 10 11

Naive Trees Nested Sets strengths• Easy to query descendants of comment #4: SELECT * FROM Comments parent JOIN Comments descendant ON (descendant.nsleft BETWEEN parent.nsleft AND parent.nsright) WHERE parent.comment_id = 4;

Naive TreesNested Sets strengths (1) Fran: parent What’s the cause of this bug? 1 14 (2) Ollie: (4) Kukla: descendants I think it’s a null We need to check pointer. valid input. 2 5 6 13 (3) Fran: (6) Fran: (5) Ollie: No, I checked for Yes, please add a Yes, that’s a bug. that. check.3 4 7 8 9 12 (7) Kukla: That ﬁxed it. 10 11

Naive Trees Nested Sets problems• Hard to insert a new child of comment #5: UPDATE Comments SETnsleft = CASE WHEN nsleft >= 8 THEN nsleft+2 ELSE nsleft END, nsright = nsright+2 WHERE nsright >= 7; INSERT INTO Comments (nsleft, nsright, author, comment) VALUES (8, 9, 'Fran', 'I agree!');• Recalculate left values for all nodes to the right of the new child. Recalculate right values for all nodes above and to the right.

Naive TreesNested Sets problems (1) Fran: What’s the cause of this bug? 1 16 14 (2) Ollie: (4) Kukla: I think it’s a null We need to check pointer. valid input. 2 5 6 15 13 (3) Fran: (6) Fran: (5) Ollie: No, I checked for Yes, please add a Yes, that’s a bug. that. check.3 4 7 8 9 10 11 14 12 (8) Fran: (7) Kukla: I agree! That ﬁxed it. 8 9 10 12 13 11

Naive Trees Nested Sets problems• Hard to query the parent of comment #6: SELECT parent.* FROM Comments AS c JOIN Comments AS parent ON (c.nsleft BETWEEN parent.nsleft AND parent.nsright) LEFT OUTER JOIN Comments AS in_between ON (c.nsleft BETWEEN in_between.nsleft AND in_between.nsright AND in_between.nsleft BETWEEN parent.nsleft AND parent.nsright) WHERE c.comment_id = 6 AND in_between.comment_id IS NULL;• Parent of #6 is an ancestor who has no descendant who is also an ancestor of #6.• Querying a child is a similar problem.

Naive Trees Closure Tables• Store every path from ancestors to descendants• Requires an additional table: CREATE TABLE TreePaths ( ancestor INT NOT NULL, descendant INT NOT NULL, PRIMARY KEY (ancestor, descendant), FOREIGN KEY(ancestor) REFERENCES Comments(comment_id), FOREIGN KEY(descendant) REFERENCES Comments(comment_id), );

Naive TreesClosure Tables illustration (1) Fran: What’s the cause of this bug? (2) Ollie: (4) Kukla: I think it’s a null We need to check pointer. valid input. (3) Fran: (6) Fran: (5) Ollie: No, I checked for Yes, please add a Yes, that’s a bug. that. check. (7) Kukla: That ﬁxed it.

Naive Trees Closure Tables example ancestor descendant 1 1 1 2 comment_id author comment 1 3 1 Fran What’s the cause of this bug? 1 4 2 Ollie I think it’s a null pointer. 1 5 3 Fran No, I checked for that. 1 6 4 Kukla We need to check valid input. 1 7 5 Ollie Yes, that’s a bug. 2 2 6 Fran Yes, please add a check 2 3 7 Kukla That ﬁxed it. 3 3 4 4 4 5 requires O(n²) rows 4 6(but far fewer in practice) 4 5 7 5 6 6 6 7 7 7

Naive Trees Closure Tables strengths• Easy to query descendants of comment #4: SELECT c.* FROM Comments c JOIN TreePaths t ON (c.comment_id = t.descendant) WHERE t.ancestor = 4;

Naive Trees Closure Tables strengths• Easy to query ancestors of comment #6: SELECT c.* FROM Comments c JOIN TreePaths t ON (c.comment_id = t.ancestor) WHERE t.descendant = 6;

Naive Trees Closure Tables strengths• Easy to insert a new child of comment #5: INSERT INTO Comments VALUES (8, ‘Fran’, ‘I agree!’); INSERT INTO TreePaths (ancestor, descendant) SELECT ancestor, 8 FROM TreePaths WHERE descendant = 5 UNION ALL SELECT 8, 8;

Naive Trees Closure Tables strengths• Easy to delete a child comment #7: DELETE FROM TreePaths WHERE descendant = 7;

Naive Trees Closure Tables strengths• Easy to delete subtree under comment #4: DELETE FROM TreePaths WHERE descendant IN (SELECT descendant FROM TreePaths WHERE ancestor = 4);• PostgreSQL multi-table DELETE syntax: DELETE FROM TreePaths p USING TreePaths a WHERE p.descendant = a.descendant AND a.ancestor = 4;

Naive Trees Closure Tables depth ancestor descendant depth 1 1 0• Add a depth column to make it 1 2 1 1 3 2 easier to query immediate 1 1 4 5 1 2 parent or child: 1 6 2 1 7 3 SELECT c.* 2 2 0 2 3 1 FROM Comments c JOIN TreePaths t 3 3 0 ON (c.comment_id = t.descendant) 4 4 0 WHERE t.ancestor = 4 4 5 1 4 6 1 AND t.depth = 1; 4 7 2 5 5 0 6 6 0 6 7 1 7 7 0

Naive Trees Choose the right design Query Query Delete Add Move Referential Design Tables Child Subtree Node Node Subtree IntegrityAdjacency List 1 Easy Hard Easy Easy Easy Yes Path 1 Easy Easy Easy Easy Easy NoEnumeration Nested Sets 1 Hard Easy Hard Hard Hard NoClosure Table 2 Easy Easy Easy Easy Hard Yes

Magic BeansEssentially, all models are wrong, but some are useful. — George E. P. Box

Magic Beans• Objective: • Model-View-Controller application development • Object-Relational Mapping (ORM)

Magic Beans Active RecordThe Golden Hammer of database access in most MVC frameworks:

Magic BeansModel is-a Active Record? Active Record inheritance (IS-A) Products Bugs Comments Controller View aggregation (HAS-A)

Magic Beans Problem: OO design• “Model” : Active Record inheritance (IS-A)• Model tied to database schema coupling• Product → Bug, or unclear assignment of responsibilities Bug → Product?• Models expose Active Record interface, not domain-speciﬁc interface poor encapsulation

Magic Beans Problem: MVC design• Controllers need to know “T.M.I.” !! database structure• Database changes require not “DRY” code changes• http://www.martinfowler.com/bliki/ (appeal to AnemicDomainModel.html authority)

Magic Beans Problem: testability tests are• Harder to test Model without database slow• Need database “ﬁxtures” tests are• Fat Controller makes it harder even slower to test business logic mocking HTTP Request, scraping HTML output

Magic BeansModel has-a Active Record(s) Active Record inheritance (IS-A) Products Bugs Comments composition (HAS-A) BugReport Controller View (Model) aggregation (HAS-A)

Magic Beans Model characteristics• Extend no base class• Abstract the database• Expose only domain-speciﬁc interface• Encapsulate complex business logic

Magic Beans Model beneﬁts• Models are decoupled from DB • Supports mock objects • Supports dependency injection• Unit-testing Models in isolation is faster• Unit-testing Thin Controllers is easier

Magic BeansDecouple your Models You can use this design ...even in MVC frameworks that encourage the antipattern.

Copyright 2008-2009 Bill Karwin www.slideshare.net/billkarwin Released under a Creative Commons 3.0 License: http://creativecommons.org/licenses/by-nc-nd/3.0/ You are free to share - to copy, distribute and transmit this work, under the following conditions: Attribution. Noncommercial. No Derivative Works.You must attribute this You may not use this work You may not alter, work to Bill Karwin. for commercial purposes. transform, or build upon this work.

Movatterモバイル変換

Change Language

Practical Object Oriented Models In Sql

Embed presentation

Recommended

More Related Content

What's hot

Similar to Practical Object Oriented Models In Sql

More from Karwin Software Solutions LLC

Recently uploaded

Practical Object Oriented Models In Sql