Mapping a Tree with GORM and Grails

Slides Deck of the GR8Conf EU 2017 talk by Sergio del Amo

1. MAPPING A TREE WITH GRAILS objectcomputing.com/grails
2. http://sergiodelamo.es Sergio del Amo @sdelamo
3. objectcomputing.com/grails http://groovycalamari.com
4. http://guides.grails.org
5. objectcomputing.com/grails
6. objectcomputing.com/grails PROBLEM STORE & QUERY HIERARCHICAL DATA
7. objectcomputing.com/grails TREE DATA STRUCTURE IN A TREE DATA STRUCTURE, EACH ENTRY IS CALLED A NODE. A NODE MAY HAVE A NUMBER OF CHILDREN AND ONE PARENT. THE TOP NODE, WHICH HAS NO PARENT, IS CALLED THE ROOT. THE NODES AT THE BOOTOM WHICH HAVE NO CHILDREN ARE CALLED LEAVES. THE NODES IN THE MIDDLE ARE SIMPLE NOLEAF NODES.
10. objectcomputing.com/grails
11. objectcomputing.com/grails EXAMPLE: BUG REPORT COMMENTS
12. objectcomputing.com/grails SOLUTIONS • ADJACENCY LIST • PATH ENUMERATION • NESTED SETS • CLOSURE TABLE
13. ADJACENCY LIST
14. objectcomputing.com/grails ADJANCENY LIST • NAIVE SOLUTION EVERYONE USES • EACH ENTRY KNOWS ITS IMMEDIATE PARENT
15. objectcomputing.com/grails CREATE TABLE comment ( comment_id BIGINT PRIMARY KEY, parent_id BIGINT UNSIGNED, author VARCHAR(255) NOT NULL, comment TEXT NOT NULL, FOREIGN KEY (parent_id) REFERENCES comment(comment_id) ); ADJACENCY LIST
16. objectcomputing.com/grails class Comment { String comment String author Comment parent static mapping = { id column: 'comment_id' version false comment type: 'text' } static constraints = { comment nullable: false author nullable: false parent nullable: true } } ADJACENCY LIST - GORM
17. objectcomputing.com/grails ID PARENT_ID AUTHOR COMMENT 1 NULL FRAN WHAT’S THE CAUSE OF THIS BUG? 2 1 OLLIE I THINK IT IS A NULL POINTER 3 2 FRAN NO, I CHECKED FOR THAT 4 1 KUKLA WE NEED TO CHECK FOR VALID INPUT 5 4 OLLIE YES, THAT’S A BUG 6 4 FRAN YES, PLEASE ADD A CHECK 7 6 KUKLA THAT FIXED IT
18. objectcomputing.com/grails INSERT INTO comment ( parent_id, author, comment) VALUES (5, ‘FRAN’, ‘I agree!’) INSERT A NEW NODE
19. objectcomputing.com/grails INSERT INTO comment ( parent_id, author, comment) VALUES (5, ‘FRAN’, ‘I agree!’) INSERT A NEW NODE
20. objectcomputing.com/grails INSERT INTO comment ( parent_id, author, comment) VALUES (5, ‘FRAN’, ‘I agree!’) INSERT A NEW NODE Comment saveComment(String comment, String author, Comment ancestorComment) { def commentEntity = new Comment(comment: comment, author: author, parent: ancestorComment) if ( !commentEntity.save() ) { log.error "Could not save comment ${commentEntity.errors}" } commentEntity }
21. objectcomputing.com/grails UPDATE comment SET parent_id=3 WHERE comment_id=6; MOVE A NODE OR SUBTREE
22. objectcomputing.com/grails UPDATE comment SET parent_id=3 WHERE comment_id=6; MOVE A NODE OR SUBTREE
23. objectcomputing.com/grails UPDATE comment SET parent_id=3 WHERE comment_id=6; MOVE A NODE OR SUBTREE void moveCommentToParent(Comment comment, Comment newAncestor) { comment?.parent = newAncestor if ( !comment?.save() ) { log.error "Could not change parent of comment ${comment.errors}" } comment }
24. objectcomputing.com/grails SELECT * FROM COMMENTS C1 LEFT JOIN COMMENTS C2 ON (C2.PARENT_ID = C1.COMMENT_ID); QUERY INMEDIATE CHILD / PARENT QUERY A NODE’S CHILDREN QUERY A NODE’S PARENT SELECT * FROM COMMENTS C1 LEFT JOIN COMMENTS C2 ON (C1.PARENT_ID = C2.COMMENT_ID);
25. objectcomputing.com/grails List<Comment> childsOfComment(Comment commentEntity) { Comment.where { parent == commentEntity }.list() } QUERY INMEDIATE CHILD / PARENT QUERY A NODE’S CHILDREN QUERY A NODE’S PARENT List<Comment> parentsOfComment(Comment commentEntity) { if ( commentEntity.parent == null ) { return [] } [commentEntity.parent] }
26. objectcomputing.com/grails List<Comment> ancestorsOfComment(Comment commentEntity) { def comments = [] for ( Comment comment : parentsOfComment(commentEntity)) { comments << comment comments += ancestorsOfComment(comment) } comments } List<Comment> parentsOfComment(Comment commentEntity) { if ( commentEntity.parent == null ) { return [] } [commentEntity.parent] } QUERY ALL ANCESTORS
27. objectcomputing.com/grails List<Comment> ancestorsOfComment(Comment commentEntity) { def comments = [] for ( Comment comment : parentsOfComment(commentEntity)) { comments << comment comments += ancestorsOfComment(comment) } comments } List<Comment> parentsOfComment(Comment commentEntity) { if ( commentEntity.parent == null ) { return [] } [commentEntity.parent] } QUERY ALL ANCESTORS
28. objectcomputing.com/grails 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) … CAN’T HANDLE DEEP TREES
29. objectcomputing.com/grails 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) … CAN’T HANDLE DEEP TREES It still doesn’t support unlimited depth!
30. RECURSIVE QUERY
31. objectcomputing.com/grails WTIH [RECURSIVE] commenttree (comment_id, parent_id, author, comment, depth) AS ( SELECT *, 0 AS depth FROM comment 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; SQL-99 Recursive Syntax Mysql, SQLite, Informix, Firebird, etc PostgresSQL, Oracle 11g, IBM DB2, Microsoft SQL Server, Apache Derby
32. PATH ENUMERATION
33. objectcomputing.com/grails CREATE TABLE comment ( comment_id BIGINT PRIMARY KEY, path VARCHAR(1000), length BIGINT, author VARCHAR(255) NOT NULL, comment TEXT NOT NULL ); PATH ENUMERATION
34. objectcomputing.com/grails class Comment { String comment String author String path Integer length static constraints = { comment nullable: false path nullable: false, blank: true author nullable: false length nullable: false, min: 0 } static mapping = { version false table 'comment' comment type: 'text' path sqlType: 'varchar', length: 1000 } } PATH ENUMERATION - GORM
35. objectcomputing.com/grails ID PATH AUTHOR COMMENT 1 1/ FRAN WHAT’S THE CAUSE OF THIS BUG? 2 1/2/ OLLIE I THINK IT IS A NULL POINTER 3 1/2/3/ FRAN NO, I CHECKED FOR THAT 4 1/4/ KUKLA WE NEED TO CHECK FOR 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 FIXED IT
36. objectcomputing.com/grails ID PATH LENGTH AUTHOR COMMENT 1 1/ 1 FRAN WHAT’S THE CAUSE OF THIS BUG? 2 1/2/ 2 OLLIE I THINK IT IS A NULL POINTER 3 1/2/3/ 3 FRAN NO, I CHECKED FOR THAT 4 1/4/ 2 KUKLA WE NEED TO CHECK FOR VALID INPUT 5 1/4/5/ 3 OLLIE YES, THAT’S A BUG 6 1/4/6/ 3 FRAN YES, PLEASE ADD A CHECK 7 1/4/6/7/ 4 KUKLA THAT FIXED IT If elements between separators are not of consistent length, this column allow you to easily sort a set of rows by hierarchy
37. objectcomputing.com/grails SELECT * FROM comment WHERE ‘1/4/6/7/‘ LIKE path || ‘%’; QUERY ANCESTORS AND SUBTREES QUERY ANCESTORS OF comment #7 QUERY DESCENDANTS OF comment #4 SELECT * FROM comment WHERE PATH LIKE ‘1/4/’ || ‘%’ It matches the patterns formed from paths of ancestors ‘1/4/6/%’, ‘1/4/%’, ‘1/%’
38. objectcomputing.com/grails Comment.createCriteria().list { or { paths.each { String subpath -> and { like('path', subpath) eq('length', (subpath.split(SEPARATOR).size() - 1 )) } } } ne('id', comment.id) } QUERY ANCESTORS AND SUBTREES QUERY ANCESTORS OF comment #7 => path 1/4/6/7/ paths = ‘1/4/6/%’, ‘1/4/%’, ‘1/%’
39. objectcomputing.com/grails Comment.where { id != commentEntity.id && path ==~ “${commentEntity?.path}%" }.list() QUERY ANCESTORS AND SUBTREES QUERY DESCENDANTS OF comment #4
40. objectcomputing.com/grails INSERT INTO comment (author, comment) VALUES (‘Ollie’, ‘Good job!’); SELECT path FROM Comments WHERE comment_id = 7; UPDATE comment SET path = $parent_path || LAST_INSERTED_ID() || ‘/‘ WHERE comment_id = LAST_INSERT_ID(); ADD A NEW CHILD of #7
41. PATH ENUMERATION DEMO
42. NESTED SETS
43. objectcomputing.com/grails CREATE TABLE comment ( comment_id BIGINT PRIMARY KEY, nsleft BIGINT, nsright BIGINT, author VARCHAR(255) NOT NULL, comment TEXT NOT NULL ); NESTED SET
44. objectcomputing.com/grails class Comment { String comment Integer nsleft Integer nsright String author static mapping = { id column: 'comment_id' version false comment type: 'text' } static constraints = { comment nullable: false author nullable: false nsleft nullable: true nsright nullable: true } } NESTED SETS - GORM
45. objectcomputing.com/grails 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 number are between all numbers used by the comment’s ancestors.
46. objectcomputing.com/grails
47. objectcomputing.com/grails
48. objectcomputing.com/grails
49. objectcomputing.com/grails
50. objectcomputing.com/grails
51. objectcomputing.com/grails
52. objectcomputing.com/grails
53. objectcomputing.com/grails
54. objectcomputing.com/grails
55. objectcomputing.com/grails
56. objectcomputing.com/grails
57. objectcomputing.com/grails
58. objectcomputing.com/grails
59. objectcomputing.com/grails
60. objectcomputing.com/grails
61. objectcomputing.com/grails
62. objectcomputing.com/grails ID NSLEFT NSRIGHT AUTHOR COMMENT 1 1 14 FRAN WHAT’S THE CAUSE OF THIS BUG? 2 2 5 OLLIE I THINK IT IS A NULL POINTER 3 3 4 FRAN NO, I CHECKED FOR THAT 4 6 13 KUKLA WE NEED TO CHECK FOR 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 FIXED IT
63. objectcomputing.com/grails ID NSLEFT NSRIGHT AUTHOR COMMENT 1 1 14 FRAN WHAT’S THE CAUSE OF THIS BUG? 2 2 5 OLLIE I THINK IT IS A NULL POINTER 3 3 4 FRAN NO, I CHECKED FOR THAT 4 6 13 KUKLA WE NEED TO CHECK FOR 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 FIXED IT These are not foreign keys
64. objectcomputing.com/grails SELECT * FROM comment AS child JOIN comment AS ancestor ON child.nsleft BETWEEN ancestor.nsleft AND ancestor.nsright WHERE child.comment_id = 7 QUERY ANCESTORS OF 7
65. objectcomputing.com/grails SELECT * FROM comment AS parent JOIN comment AS descendant ON descendant.nsleft BETWEEN parent.nsleft AND parent.nsright WHERE parent.comment_id = 4 QUERY SUTREE UNDER #4
66. objectcomputing.com/grails UPDATE comment SET nsleft = CASE WHEN nsleft >= 8 THEN nsleft+2 ELSE nsleft END, nsright = nsright+2 WHERE nsright >= 7; INSERT INTO comment (nsleft, nsright, author, comment) VALUES (8,9,’Fran’, ‘I agree!’); INSERT NEW CHILD of #5 Recalculate left values for all nodes to the right of the new child. Recalculate right values for all nodes above to the right.
67. objectcomputing.com/grails 7 8 9 6 10 11 12 13 14 15 16
68. objectcomputing.com/grails SELECT parent.* FROM comment AS C JOIN comment AS parent ON (ns.left BETWEEN parent.nsleft and parent.nsright) LEFT OUTER JOIN comment AS in_between ON (c.nsleft BETWEEN in between.nsleft AND in_between.nsright AND in_between.nsleft BETWEEN in parent.nsleft AND parent.nsright) WHERE c.comment_id = 6 AND in_between.comment_id IS NULL; QUERY INMEDIATE PARENT of #6 Parent of #6 is an ancestor who has no descendant who is also no ancestor of #6
69. CLOUSURE TABLE
70. objectcomputing.com/grails CREATE TABLE comment ( comment_id BIGINT PRIMARY KEY, author VARCHAR(255) NOT NULL, comment TEXT NOT NULL ); CREATE TABLE treepath ( ancestor BIGINT NOT NULL, descendant BIGINT NOT NULL, PRIMARY KEY (ancestor, descendant), FOREIGN KEY (ancestor) REFERENCES comment(comment_id), FOREIGN KEY (descendant) REFERENCES comment(comment_id) ); CLOSURE TABLE
71. objectcomputing.com/grails class CommentTreePath implements Serializable { CommentGormEntity ancestor CommentGormEntity descendant int length static mapping = { version false table 'treepath' descendant column: 'descendant' ancestor column: 'ancestor' id composite: ['ancestor', 'descendant'] } … } class CommentGormEntity { String comment String author static mapping = { table 'comment' id column: 'comment_id' comment type: 'text' version false } static constraints = { comment nullable: false author nullable: false } String toString() { "${author}: $comment" } }
72. objectcomputing.com/grails CLOSURE TABLE - Many to many table - Stores every path from each node to each of its descendants. - A node even connect to itself
73. objectcomputing.com/grails
74. objectcomputing.com/grails COMMENT_ID AUTHOR COMMENT 1 FRAN WHAT’S THE CAUSE OF THIS BUG? 2 OLLIE I THINK IT IS A NULL POINTER 3 FRAN NO, I CHECKED FOR THAT 4 KUKLA WE NEED TO CHECK FOR VALID INPUT 5 OLLIE YES, THAT’S A BUG 6 FRAN YES, PLEASE ADD A CHECK 7 KUKLA THAT FIXED IT ANCESTOR DESCENDANT 1 1 1 2 1 3 1 4 1 5 1 6 1 7 2 2 2 3 3 3 4 4 4 5 4 6 4 7 5 5 6 6 6 7 7 7 CLOSURE TABLE
75. objectcomputing.com/grails SELECT c.* FROM comment c JOIN treepath t ON (c.comment_id = t.descendant) WHERE t.ancestor = 4; QUERY DESCENDANTS OF #4
76. objectcomputing.com/grails PATHS STARTING FROM #4
77. objectcomputing.com/grails SELECT C.* FROM Comments c JOIN TreePaths t ON (c.comment_id = t.ancestor) WHERE t.descendant = 6; QUERY ANCESTORS OF #6
78. objectcomputing.com/grails PATHS TERMINATING AT#6
79. objectcomputing.com/grails 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; INSERT NEW CHILD of #5
80. objectcomputing.com/grails DELETE FROM TreePaths WHERE descendant = 7; DELETE CHILD #7
81. objectcomputing.com/grails Delete FROM TreePaths WHERE descendant IN (SELECT descendant FROM TreePaths WHERE ancestor = 4); DELETE SUBTREE UNDER #4
82. objectcomputing.com/grails ANCESTOR DESCENDANT LENGTH 1 1 0 1 2 1 1 3 2 1 4 1 1 5 2 1 6 2 1 7 3 2 2 0 2 3 1 3 3 0 4 4 0 4 5 1 4 6 1 4 7 2 5 5 0 6 6 0 6 7 1 7 7 0 PATH LENGTH - Add a length column - MAX (length) is the depth of the tree - Makes it easier to query immediate parent or child SELECT c.* FROM Comments c JOIN TreePaths t ON (c.comment_id = t.descendant) WHERE t.ancestor = 4 AND t.length = 1;
83. objectcomputing.com/grails ANCESTOR DESCENDANT LENGTH 1 1 0 1 2 1 1 3 2 1 4 1 1 5 2 1 6 2 2 2 3 2 3 0 3 3 0 4 4 0 4 5 1 4 6 1 4 7 2 5 5 0 6 6 0 6 7 1 7 7 0 PATH LENGTH - Add a length column - MAX (length) is the depth of the tree - Makes it easier to query immediate parent or child SELECT c.* FROM Comments c JOIN TreePaths t ON (c.comment_id = t.descendant) WHERE t.ancestor = 4 AND t.length = 1;
84. CLOSURE TABLE DEMO
85. objectcomputing.com/grails DESIGN # TABLES QUERY CHILD QUERY SUBTREE DELETE NODE INSERT NODE MOVE SUBTREE REF. INTEG. ADJACENSY LIST 1 EASY HARD EASY EASY EASY YES PATH ENUMERATION 1 HARD EASY EASY EASY EASY NO NESTED SETS 1 HARD EASY HARD HARD HARD NO CLOUSURE TABLE 2 EASY EASY EASY EASY EASY YES CHOSING THE RIGHT DESIGN
86. “A hierarchy consists of entries and relationships. Model both of these to suit your work.”
87. objectcomputing.com/grails
88. ?
89. OCI 12140 Woodcrest Exec. Dr., Ste. 250 Saint Louis, MO 63141 USA © 2017, All Rights Reserved. No part of this publication may be photocopied or reproduced in any form without written permission from OCI. Nor shall the OCI logo or copyright information be removed from this publication. No part of this publication may be stored in a retrieval system, transmitted by any means, recorded or otherwise, without written permission from OCI. While every precaution has been taken in preparing this material, including research, development and testing, OCI assumes no responsibility for errors or omissions. No liability is assumed by OCI for any damages resulting from the use of this information.