Parent/Child - Linked List
Running mysql(5.5) /linux/ php/python Got a situation with a test env, where I'm dealing with a number of spawned processes, that might also spawn processes, so I have a tree where I'm looking to determine when the spawned processes have completed. To manage this cluster/tree of processes, I'm considering using a tree kind of DB representation: The tables would be: ParentChildTBL ParentID, int ChildID int ItemStatusTBL Name, varchar ID, int Status int ItemStatusTBL.ID -- ParentID/ChildID Tree Graph: top(1) | _ | | | itemA(2) itemB(3) itemC(4) | -- | | | itemD(5) itemE(6) itemF(7) pseudo tbl representation ItemStatusTBL top, 1, 0 itemA, 2, 0 itemB, 3, 0 itemC, 4, 0 itemD, 5, 0 itemE, 6, 0 itemF, 7, 0 ParentChildTBL ,1 1,2 1,3 1,4 2,5 2,6 2,7 I've got a test app that spawns off child processes, where each process then updates the status of the corresponding given item upon completion. So the status in the itemStatusTBl will change from 0 to 1. I'm trying to determine how to efficiently be able to determine when the children of a given top/root node in the parentChildTBL are complete, ie, have the status set to '1'. (There could be multiple top level/root nodes, each with their own independent set of children) I could try to simply look at all the children each time I examine the tbl, but that might result in a lot of recursive function/processing in order to get to all the levels... I've looked at various articles, but not sure which is the best approach to this kind of issue. A complete run is determined by: when all children of the top level/root node != '0' Is this a self join,left join situation? A sample query/pointers would be helpful Thoughts/Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Parent/Child - Linked List
See the piece on trees at www.artfulsoftware.com. It goes into several variations of how to handle hierarchies. HTH, -- Arthur Cell: 647.710.1314 Prediction is difficult, especially of the future. -- Neils Bohr
Re: Parent/Child - Linked List
On Mon, Jan 16, 2012 at 1:52 PM, bruce badoug...@gmail.com wrote: Hey Authur. Should have been more clear. I've looked over a number of sites. And with the exception of the the articles that talk about using the Nested List approach, nowhere did I find data on how to get a complete list of the child descendants of a given 'root'/top item from the parent/child TBL. Chunks of code/pointers would be seriously useful. Thanks On Mon, Jan 16, 2012 at 12:18 PM, Arthur Fuller fuller.art...@gmail.com wrote: See the piece on trees at www.artfulsoftware.com. It goes into several variations of how to handle hierarchies. HTH, -- Arthur Cell: 647.710.1314 Prediction is difficult, especially of the future. -- Neils Bohr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Parent/Child - Linked List
On 1/16/2012 12:53 PM, bruce wrote: On Mon, Jan 16, 2012 at 1:52 PM, brucebadoug...@gmail.com wrote: Hey Authur. Should have been more clear. I've looked over a number of sites. And with the exception of the the articles that talk about using the Nested List approach, nowhere did I find data on how to get a complete list of the child descendants of a given 'root'/top item from the parent/child TBL. Look again, eg listings 7 through 7d in http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html. PB - Chunks of code/pointers would be seriously useful. Thanks On Mon, Jan 16, 2012 at 12:18 PM, Arthur Fullerfuller.art...@gmail.com wrote: See the piece on trees at www.artfulsoftware.com. It goes into several variations of how to handle hierarchies. HTH, -- Arthur Cell: 647.710.1314 Prediction is difficult, especially of the future. -- Neils Bohr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Parent/Child - Linked List
hi Peter. Sorry.. Been looking at this for awhile. In the sample data/tbl I provided, it has two top level root/parents. Ie, I have two entries that don't have a parentID. I use 0 to be null. The items are (0,1), and (0,8). The (0,1) item, has a number of descendants. The (0,8) only has a single descendant. For my app, I'm going to have a number of top level items, and they're each going to have a number of descendants, where I don't know the number of descendant rows, or the number of actual descendants. But either way, once I get the descendant list, I still need some way of linking the childID of the descendant to the linked ID of the statusTBL so I can get the status of the childID/app. And like I said, I'm not quite sure how to proceed in an efficient manner on this. Thanks On Mon, Jan 16, 2012 at 4:08 PM, Peter Brawley peter.braw...@earthlink.net wrote: On 1/16/2012 2:08 PM, bruce wrote: Hi Peter. Not a mysql guru... so I've never used stored procedures/sub-queries.. But it sort of makes sense. What I'm really trying to get is to be able to take a test table like below LOCK TABLES `parentChildTBL` WRITE; /*!4 ALTER TABLE `parentChildTBL` DISABLE KEYS */; INSERT INTO `parentChildTBL` VALUES (0,1,1), (1,2,2), (1,3,3), (1,4,4), (2,5,5), (2,6,6), (2,7,7), (0,8,8), (8,9,9); UNLOCK TABLES; and to be able to generate the child/descendant list of the top two/2 items (1,8) I don't understand top two(1,8). In general a non-procedural query of n recursion levels requires n-1 joins. If the number of recursive references is unknown beforehand, the only way to query the tree is via a stored procedure. PB - if I only have a single top level item.. and can do a left join.. but I'm not sure how to accomplish this with two top items, unless I take a look at the approach you provided. I'm looking at being able to compare a 'status' from a linked tbl, that links on the childID... thanks On Mon, Jan 16, 2012 at 2:33 PM, Peter Brawley peter.braw...@earthlink.net wrote: On 1/16/2012 12:53 PM, bruce wrote: On Mon, Jan 16, 2012 at 1:52 PM, brucebadoug...@gmail.com wrote: Hey Authur. Should have been more clear. I've looked over a number of sites. And with the exception of the the articles that talk about using the Nested List approach, nowhere did I find data on how to get a complete list of the child descendants of a given 'root'/top item from the parent/child TBL. Look again, eg listings 7 through 7d in http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html. PB - Chunks of code/pointers would be seriously useful. Thanks On Mon, Jan 16, 2012 at 12:18 PM, Arthur Fullerfuller.art...@gmail.com wrote: See the piece on trees at www.artfulsoftware.com. It goes into several variations of how to handle hierarchies. HTH, -- Arthur Cell: 647.710.1314 Prediction is difficult, especially of the future. -- Neils Bohr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Parent/Child - Linked List
On 1/16/2012 4:14 PM, bruce wrote: hi Peter. Sorry.. Been looking at this for awhile. In the sample data/tbl I provided, it has two top level root/parents. Ie, I have two entries that don't have a parentID. I use 0 to be null. Mistake. use Null. The items are (0,1), and (0,8). Then the table has two trees. See Multiple trees in one table at http://www.artfulsoftware.com/infotree/queries.php. PB - The (0,1) item, has a number of descendants. The (0,8) only has a single descendant. For my app, I'm going to have a number of top level items, and they're each going to have a number of descendants, where I don't know the number of descendant rows, or the number of actual descendants. But either way, once I get the descendant list, I still need some way of linking the childID of the descendant to the linked ID of the statusTBL so I can get the status of the childID/app. And like I said, I'm not quite sure how to proceed in an efficient manner on this. Thanks On Mon, Jan 16, 2012 at 4:08 PM, Peter Brawley peter.braw...@earthlink.net wrote: On 1/16/2012 2:08 PM, bruce wrote: Hi Peter. Not a mysql guru... so I've never used stored procedures/sub-queries.. But it sort of makes sense. What I'm really trying to get is to be able to take a test table like below LOCK TABLES `parentChildTBL` WRITE; /*!4 ALTER TABLE `parentChildTBL` DISABLE KEYS */; INSERT INTO `parentChildTBL` VALUES (0,1,1), (1,2,2), (1,3,3), (1,4,4), (2,5,5), (2,6,6), (2,7,7), (0,8,8), (8,9,9); UNLOCK TABLES; and to be able to generate the child/descendant list of the top two/2 items (1,8) I don't understand top two(1,8). In general a non-procedural query of n recursion levels requires n-1 joins. If the number of recursive references is unknown beforehand, the only way to query the tree is via a stored procedure. PB - if I only have a single top level item.. and can do a left join.. but I'm not sure how to accomplish this with two top items, unless I take a look at the approach you provided. I'm looking at being able to compare a 'status' from a linked tbl, that links on the childID... thanks On Mon, Jan 16, 2012 at 2:33 PM, Peter Brawleypeter.braw...@earthlink.net wrote: On 1/16/2012 12:53 PM, bruce wrote: On Mon, Jan 16, 2012 at 1:52 PM, brucebadoug...@gmail.com wrote: Hey Authur. Should have been more clear. I've looked over a number of sites. And with the exception of the the articles that talk about using the Nested List approach, nowhere did I find data on how to get a complete list of the child descendants of a given 'root'/top item from the parent/child TBL. Look again, eg listings 7 through 7d in http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html. PB - Chunks of code/pointers would be seriously useful. Thanks On Mon, Jan 16, 2012 at 12:18 PM, Arthur Fullerfuller.art...@gmail.com wrote: See the piece on trees at www.artfulsoftware.com. It goes into several variations of how to handle hierarchies. HTH, -- Arthur Cell: 647.710.1314 Prediction is difficult, especially of the future. -- Neils Bohr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
