Mapping a Tree with GORM and Grails

Slide 1

Slide 1 text

MAPPING A TREE WITH GRAILS objectcomputing.com/grails

Slide 2

Slide 2 text

http://sergiodelamo.es Sergio del Amo @sdelamo

Slide 3

Slide 3 text

objectcomputing.com/grails http://groovycalamari.com

Slide 4

Slide 4 text

http://guides.grails.org

Slide 5

Slide 5 text

objectcomputing.com/grails

Slide 6

Slide 6 text

objectcomputing.com/grails PROBLEM STORE & QUERY HIERARCHICAL DATA

Slide 7

Slide 7 text

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.

Slide 8

Slide 8 text

No content

Slide 9

Slide 9 text

No content

Slide 10

Slide 10 text

objectcomputing.com/grails

Slide 11

Slide 11 text

objectcomputing.com/grails EXAMPLE: BUG REPORT COMMENTS

Slide 12

Slide 12 text

objectcomputing.com/grails SOLUTIONS • ADJACENCY LIST • PATH ENUMERATION • NESTED SETS • CLOSURE TABLE

Slide 13

Slide 13 text

ADJACENCY LIST

Slide 14

Slide 14 text

objectcomputing.com/grails ADJANCENY LIST • NAIVE SOLUTION EVERYONE USES • EACH ENTRY KNOWS ITS IMMEDIATE PARENT

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

objectcomputing.com/grails INSERT INTO comment ( parent_id, author, comment) VALUES (5, ‘FRAN’, ‘I agree!’) INSERT A NEW NODE

Slide 19

Slide 19 text

objectcomputing.com/grails INSERT INTO comment ( parent_id, author, comment) VALUES (5, ‘FRAN’, ‘I agree!’) INSERT A NEW NODE

Slide 20

Slide 20 text

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 }

Slide 21

Slide 21 text

objectcomputing.com/grails UPDATE comment SET parent_id=3 WHERE comment_id=6; MOVE A NODE OR SUBTREE

Slide 22

Slide 22 text

objectcomputing.com/grails UPDATE comment SET parent_id=3 WHERE comment_id=6; MOVE A NODE OR SUBTREE

Slide 23

Slide 23 text

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 }

Slide 24

Slide 24 text

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);

Slide 25

Slide 25 text

objectcomputing.com/grails List childsOfComment(Comment commentEntity) { Comment.where { parent == commentEntity }.list() } QUERY INMEDIATE CHILD / PARENT QUERY A NODE’S CHILDREN QUERY A NODE’S PARENT List parentsOfComment(Comment commentEntity) { if ( commentEntity.parent == null ) { return [] } [commentEntity.parent] }

Slide 26

Slide 26 text

objectcomputing.com/grails List ancestorsOfComment(Comment commentEntity) { def comments = [] for ( Comment comment : parentsOfComment(commentEntity)) { comments << comment comments += ancestorsOfComment(comment) } comments } List parentsOfComment(Comment commentEntity) { if ( commentEntity.parent == null ) { return [] } [commentEntity.parent] } QUERY ALL ANCESTORS

Slide 27

Slide 27 text

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

Slide 30

Slide 30 text

RECURSIVE QUERY

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

PATH ENUMERATION

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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/%’

Slide 38

Slide 38 text

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/%’

Slide 39

Slide 39 text

objectcomputing.com/grails Comment.where { id != commentEntity.id && path ==~ “${commentEntity?.path}%" }.list() QUERY ANCESTORS AND SUBTREES QUERY DESCENDANTS OF comment #4

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

PATH ENUMERATION DEMO

Slide 42

Slide 42 text

NESTED SETS

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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.

Slide 46

Slide 46 text

objectcomputing.com/grails

Slide 47

Slide 47 text

objectcomputing.com/grails

Slide 48

Slide 48 text

objectcomputing.com/grails

Slide 49

Slide 49 text

objectcomputing.com/grails

Slide 50

Slide 50 text

objectcomputing.com/grails

Slide 51

Slide 51 text

objectcomputing.com/grails

Slide 52

Slide 52 text

objectcomputing.com/grails

Slide 53

Slide 53 text

objectcomputing.com/grails

Slide 54

Slide 54 text

objectcomputing.com/grails

Slide 55

Slide 55 text

objectcomputing.com/grails

Slide 56

Slide 56 text

objectcomputing.com/grails

Slide 57

Slide 57 text

objectcomputing.com/grails

Slide 58

Slide 58 text

objectcomputing.com/grails

Slide 59

Slide 59 text

objectcomputing.com/grails

Slide 60

Slide 60 text

objectcomputing.com/grails

Slide 61

Slide 61 text

objectcomputing.com/grails

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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.

Slide 67

Slide 67 text

objectcomputing.com/grails 7 8 9 6 10 11 12 13 14 15 16

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

CLOUSURE TABLE

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

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" } }

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

objectcomputing.com/grails

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

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

Slide 76

Slide 76 text

objectcomputing.com/grails PATHS STARTING FROM #4

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

objectcomputing.com/grails PATHS TERMINATING AT#6

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

objectcomputing.com/grails DELETE FROM TreePaths WHERE descendant = 7; DELETE CHILD #7

Slide 81

Slide 81 text

objectcomputing.com/grails Delete FROM TreePaths WHERE descendant IN (SELECT descendant FROM TreePaths WHERE ancestor = 4); DELETE SUBTREE UNDER #4

Slide 82

Slide 82 text

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;

Slide 83

Slide 83 text

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;

Slide 84

Slide 84 text

CLOSURE TABLE DEMO

Slide 85

Slide 85 text

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

Slide 86

Slide 86 text

“A hierarchy consists of entries and relationships. Model both of these to suit your work.”

Slide 87

Slide 87 text

objectcomputing.com/grails

Slide 88

Slide 88 text

Slide 89

Slide 89 text

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.