Mapping a Tree with GORM and Grails

Mapping a Tree with GORM and Grails

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

30995fcc876f40073628b63ea5cfab59?s=128

Sergio del Amo

June 01, 2017
Tweet

Transcript

  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.
  8. None
  9. None
  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.