Ben, your message bolstered my courage to tackle this - kinda' humorous that a discussion of hierarchical data and a nested-sets implementation might "inspire" someone. So, late yesterday, I got with it and made some real progress. I was particularly intrigued by some of the (seemingly easy SQL) things one could do with this, per Celko's examples, once the table was built.
However, I'm having a problem. I have most of Celko's books and I grabbed the article you linked to in your message. I followed the logic (mostly) and the (pseudo-)SQL. No problem loading my 2 hierarchical data into the NODE_TREE table. Then I looked at the code used to build the NODE_STACK table, which, unfortunately, are not exactly equivalent in my 2 books containing it as well as the article. I "ported" it to RBase as best I could, testing each statement as I went. (Table def's follow with my naming conventions.) Table: NODE_TREE No Lock(s) Descr: -0- No. Column Name Attributes --- ------------------ ------------------------------------------------------ 1 NodeID Type : TEXT 10 2 NodeParentID Type : TEXT 10 Current number of rows: 0 Table: NODE_STACK Cursor Lock(s) Descr: -0- No. Column Name Attributes --- ------------------ ------------------------------------------------------ 1 NodeStackTop Type : INTEGER 2 NodeID Type : TEXT 10 3 NodeLeftSide Type : INTEGER 4 NodeRightSide Type : INTEGER Current number of rows: 537 When it executes though, something goes awry. All my NODE_TREE records are INSERTed INTO NODE_STACK, just fine. Their NodeLeftSide values also appear correct. Initially, via a BROWSE, the values for both NodeStackTop and NodeRightSide appeared to have been UPDATEd correctly. Then, the loop never ends, ultimately decrementing NodeStackTop and incrementing NodeRightSide as it runs. I can't figure it out as of yet, but I'm gonna' stay with it until I do. Might you have any ideas or suggestions? Maybe you would know which version of Celko's code is the correct one? Thanks, Steve in Memphis -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Ben Petersen Sent: Tuesday, March 11, 2008 10:07 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Hierarchical Data ... Steve, I've found Celko's "nested sets" logic perfectly usable, and has the advantage over most models of being able to "prune and graft" your tree if you have the need. I agree with his comment that arrays make this *very* easy, but they are by no means a requirement. An example is here: http://www.intelligententerprise.com/001020/celko.jhtml Ben Wills, Steve wrote: > Almost hate to open this can of worms, Pandora's Box, whatever, but playing > around with one of Razzak's examples related to the DB Treeview Form Control, > I feel compelled. So, lemme' ask, what do y'all do w/in RBase when dealing > with hierarchical data, such as organizational charts for units, staff, > biological classification, etc? > > For example: > > UnitID UnitName ParentUnitID HasChildren > ------ --------------- ------------ ----------- > 000000 Global NULL||000000 Y -- Either NULL or > UnitID=ParentUnitID would serve to indicate root node > 110000 Europe 000000 Y > 111000 Great Britain 110000 Y > 111100 York 111000 N > 120000 Germany 110000 Y > 121000 Hesse 120000 Y > 121100 Darmstadt 121000 N > 122000 Bavaria 120000 Y > 122100 Würzburg 122000 N > 122200 München 122000 Y > 122210 Bierhalle 122200 Y > 122211 Augustiner 122210 N > 122212 Englische 122210 N > 200000 North America 000000 Y > 220000 USA 200000 Y > 221000 Tennessee 220000 Y > 221100 Memphis 221000 Y > 221110 Sports Arenas 221100 Y > 221111 Autozone Park 221110 N > 221112 Fedex Forum 221110 N > > (NOTE: There is no implicit||explicit embedded intelligence in the ID values; > that would be a different can of worms.) > > Given the above (rather arbitrary) hierarchy, I would like to be able to > gather data, including aggregating functions (COUNT, SUM, etc) starting at > any level/node and look either up or down from that starting point. Using > the example, I might want to know how many children roll-up to Bavaria or, > from Memphis, how many sports arenas are in the data. Of course, the real > usefulness would occur when I also used data associated with, say Bierhalle, > as in how many gallons (well, liters) of beer were sold or with Memphis, > Sports Arenas, how many seats are available. > > I've been reading some Celko about this and even he - whether you > hate/love/don't care about Celko - says that his solution, sans > special/proprietary hierarchical operators, is functional but inelegant and > not terribly efficient. It also appears that some DBMS vendors have > implemented special hierarchical operators, such as WITH (DB2), CONNECT > (Oracle), etc. > > Having played with the DB Treeview control yesterday and having quickly > mapped our own organization table (in)to it, I thought, "This is really > cool." Now, however, I'm looking for a way to select a node and walk up/down > from that point, grabbing and/or aggregating data associated with the node > and direction in order to display it on the same form. This isn't yet an > enhancement request, but given RBTI's implementation of this control, it > seems an obvious next step. I also suspect that it would take Razzak and > RTeam little time to do this in a fashion compliant with current SQL > standards. I also have no doubt that at least one among us would be EVER SO > GRATEFUL ... > > Thanks, > Steve in Memphis > > > J. Stephen Wills > Program Manager, Research Informatics > Office of the Vice Chancellor for Research > University of Tennessee Health Science Center > 62 S. Dunlap, Suite 400 > Memphis, TN 38163 > Office: 901-448-2389 > FAX : 901-448-7133 > > > >
