Topic - how do I go about building a tree

new topic post reply	Resource Models -> XML -> how do I go about building a tree
moderators: pjr tab

how do I go about building a tree

Gynwer

Joined: 22-April-2007

Posts: 79

Location: Belgium

Posted: 16-July-2008 18:45

My database contains a bill-of-material structure that I want to represent onscreen. Example : a server A consists of parts A1, A2, A3, each of which consist of sub-parts ... and so on. The table on database has rows like this :
A, A1
A, A2
A, A3
A1, A11
A1, A12
A1, A13
and so on

What I'd like to show onscreen is the structure ...
A
A1
A11
A12
A13
A2
A3
and so on.
Now, how would I go about this ? I've been studying the Fibonacci example and doing this recursively would be very elegant.
Can anyone give me some pointers how to go about this ?

Regards,
Tom

extra requirement

Gynwer

Joined: 22-April-2007

Posts: 79

Location: Belgium

Posted: 17-July-2008 10:28

I might mention that I know how to do this in Oracle since there's a bill-of-material walk available in there. However, I'd like to do this with standard SQL which doesn't have that functionality. So I'm stuck with having to handle the intermediate results in some way ...

Regards,
Tom

found "a" solution, can anyone do better ?

Gynwer

Joined: 22-April-2007

Posts: 79

Location: Belgium

Posted: 18-July-2008 10:19

Well, it took me most of a day, but here's my solution for running up a bill-of-material. All the code below needs is an xlst transformation (that'll be for the weekend :-)) and it does as I require. Am still testing it for cache issues though (both for improving it and making sure I don't get "old" data).

<idoc>
 
 <!-- **************************************************************  --> 
 <!-- ***                                                        ***  -->
 <!-- *** start - impact_tree.idoc                               ***  -->
 <!-- ***                                                        ***  -->
 <!-- **************************************************************  -->
 
 <seq>
 
  <!-- *************************************************************  --> 
  <!-- ***                                                       ***  -->
  <!-- *** Param or no Param, this code takes care of it         ***  -->
  <!-- ***                                                       ***  -->
  <!-- *************************************************************  -->
  <instr>
   <type>copy</type>
   <operand>this:param</operand>
   <target>var:param</target>
  </instr>
   <exception>
    <instr>
     <type>copy</type>
     <operand>
      <nvp>
       <description>XXX</description>
      </nvp>
     </operand>
     <target>var:param</target>
    </instr>
  </exception>

  <!-- *************************************************************  --> 
  <!-- ***                                                       ***  -->
  <!-- *** Conditional statement to handle the counter           ***  -->
  <!-- ***                                                       ***  -->
  <!-- *************************************************************  -->
  <if>
   <cond>
    <instr>
     <type>xpatheval</type>
     <operand>var:param</operand>
     <operator>
      <xpath>/nvp/counter</xpath>
     </operator>
     <target>this:cond</target>
    </instr>
   </cond>
   <then>
    <instr>
     <type>stm</type>
     <operand>var:param</operand>
     <operator>
      <stm:group xmlns:stm="http://1060.org/stm">
       <stm:set-xpath-eval xpath="/nvp/counter"> ( . + 1 ) </stm:set-xpath-eval>
      </stm:group>
     </operator>
     <target>var:param</target>
    </instr>
   </then>
   <else>
    <instr>
     <type>stm</type>
     <operand>var:param</operand>
     <operator>
      <stm:group xmlns:stm="http://1060.org/stm">
       <stm:insert-before xpath="/nvp/description[1]">
        <counter>0</counter>
       </stm:insert-before>
      </stm:group>
     </operator>
     <target>var:param</target>
    </instr>
   </else>
  </if>

  <!-- *************************************************************  --> 
  <!-- ***                                                       ***  -->
  <!-- *** Conditional statement to handle the uri               ***  -->
  <!-- ***                                                       ***  -->
  <!-- *************************************************************  -->
  <if>
   <cond>
    <instr>
     <type>xpatheval</type>
     <operand>var:param</operand>
     <operator>
      <xpath>/nvp/uri</xpath>
     </operator>
     <target>this:cond</target>
    </instr>
   </cond>
   <then>
    <instr>
     <type>stm</type>
     <operand>var:param</operand>
     <operator>
      <stm:group xmlns:stm="http://1060.org/stm">
       <stm:get-fragment xpath="/nvp/uri" />
      </stm:group>
     </operator>
     <target>var:uri</target>
    </instr>
   </then>
   <else>
    <seq>
     <instr>
      <type>stm</type>
      <operand>var:param</operand>
      <operator>
       <stm:group xmlns:stm="http://1060.org/stm">
        <stm:insert-before xpath="/nvp/description[1]">
         <uri>ffcpl:/data/impact_tree_<stm:param xpath="/nvp/description/text()"/>.xml</uri>
        </stm:insert-before>
       </stm:group>
      </operator>
      <param>var:param</param>
      <target>var:param</target>
     </instr>
     <instr>
      <type>stm</type>
      <operand>var:param</operand>
      <operator>
       <stm:group xmlns:stm="http://1060.org/stm">
        <stm:get-fragment xpath="/nvp/uri" />
       </stm:group>
      </operator>
      <target>var:uri</target>
     </instr>
    </seq>
   </else>
  </if>

  <!-- *************************************************************  --> 
  <!-- ***                                                       ***  -->
  <!-- *** Conditional statement to initialize the outputfile    ***  -->
  <!-- ***                                                       ***  -->
  <!-- *************************************************************  -->
  <if>
   <cond>
    <instr>
     <type>xpatheval</type>
     <operand>var:param</operand>
     <operator>
      <xpath>/nvp/counter = '0'</xpath>
     </operator>
     <target>this:cond</target>
    </instr>
   </cond>
   <then>
    <instr>
     <type>copy</type>
     <operand>
      <output>
       <element>
        <level>0</level>
        <description>Impact List</description>
       </element>
      </output>
     </operand>
     <target>curi:var:uri</target>
    </instr>
   </then>
  </if>

  <!-- *************************************************************  --> 
  <!-- ***                                                       ***  -->
  <!-- *** Here we write the element for the current run ...     ***  -->
  <!-- ***                                                       ***  -->
  <!-- *************************************************************  -->
  
  <instr>
   <type>stm</type>
   <operand>curi:var:uri</operand>
   <operator>
    <stm:group xmlns:stm="http://1060.org/stm">
     <stm:append xpath="/output">
      <element>
       <level><stm:param xpath="/nvp/counter/text()"/></level>
       <description><stm:param xpath="/nvp/description/text()"/></description>
      </element>
     </stm:append>
    </stm:group>
   </operator>
   <param>var:param</param>
   <target>curi:var:uri</target>
  </instr>  
  

  <!-- *************************************************************  --> 
  <!-- ***                                                       ***  -->
  <!-- *** Here we call the impact_tree for the elements higher  ***  -->
  <!-- *** up in the tree                                        ***  -->
  <!-- ***                                                       ***  -->
  <!-- *************************************************************  -->

  <!-- *************************************************************  --> 
  <!-- ***                                                       ***  -->
  <!-- *** Preparation of the SQL code                           ***  -->
  <!-- ***                                                       ***  -->
  <!-- *************************************************************  -->
  <instr>
   <type>stm</type>
   <operand>
    <sql/>
   </operand>
   <operator>
    <stm:group xmlns:stm="http://1060.org/stm">
     <stm:set xpath="/sql">
     SELECT "shortdesc_owner" "description" 
      FROM "assetbom" 
      WHERE "shortdesc_member" = upper('<stm:param xpath="/nvp/description/text()"/>');
     </stm:set>
    </stm:group>
   </operator>
   <param>var:param</param>
   <target>var:sql</target>
  </instr>

  <!-- *************************************************************  --> 
  <!-- ***                                                       ***  -->
  <!-- *** Execute the SQL code                                  ***  -->
  <!-- ***                                                       ***  -->
  <!-- *************************************************************  -->  

  <instr>
   <type>sqlQuery</type>
   <operand>var:sql</operand>
   <target>var:sqlresult</target>
  </instr>

  <!-- *************************************************************  --> 
  <!-- ***                                                       ***  -->
  <!-- *** Process the result                                    ***  -->
  <!-- ***                                                       ***  -->
  <!-- *************************************************************  -->
  <while>
   <cond>
    <instr>
     <type>xpatheval</type>
     <operator>
      <xpath>count(row)</xpath>
     </operator>
     <operand>var:sqlresult</operand>
     <target>this:cond</target>
    </instr>
   </cond>

   <seq>
    <instr>
     <type>stm</type>
     <operator>
      <stm:group xmlns:stm="http://1060.org/stm">
       <stm:replace xpath="/nvp/description">
        <stm:param xpath="/results/row[1]/description" />
       </stm:replace>
      </stm:group>
     </operator>
     <param>var:sqlresult</param>
     <operand>var:param</operand>
     <target>var:param</target>       
    </instr>
     
    <instr>
     <type>dpml</type>
     <operand>impact_tree.idoc</operand>
     <param>var:param</param>
    </instr>
     
    <instr>
     <type>stm</type>
     <operator>
      <stm:group xmlns:stm="http://1060.org/stm">
        <stm:delete xpath="row[1]" />
      </stm:group>
     </operator>
     <operand>var:sqlresult</operand>
     <target>var:sqlresult</target>
    </instr>  
   </seq>
   
  </while>

  <!-- *************************************************************  --> 
  <!-- ***                                                       ***  -->
  <!-- *** End of a run. In case of the top level, display the   ***  -->
  <!-- *** output ...                                            ***  -->
  <!-- ***                                                       ***  -->
  <!-- *************************************************************  -->
  <if>
   <cond>
    <instr>
     <type>xpatheval</type>
     <operand>var:param</operand>
     <operator>
      <xpath>/nvp/counter = '0'</xpath>
     </operator>
     <target>this:cond</target>
    </instr>
   </cond>
   <then>
    <instr>
     <type>copy</type>
     <operand>curi:var:uri</operand>
     <target>this:response</target>
    </instr>
   </then>
  </if>
  
 </seq>
 
 <!-- **************************************************************  --> 
 <!-- ***                                                        ***  -->
 <!-- *** stop - impact_tree.idoc                                ***  -->
 <!-- ***                                                        ***  -->
 <!-- **************************************************************  -->

</idoc>

If anyone has a better solution ... I'd still love to hear/see it.
Regards,
Tom

Define "better"...

hickst

Joined: 3-August-2006

Posts: 48

Location: Tucson, AZ

Posted: 23-July-2008 01:37

I suspect you had fun and learned a lot (a lot!!) about DPML.

I couldn't help but think, however, as I read this humongous program, that you could probably have written this in any of the provided scripting languages (my favorite is Groovy) and it would have been about 10% of the current size.

One should remember that, with the separation of logical and physical addressing inherent in NetKernel, the choice of language becomes much less important. Programmer time and effort far outway other considerations. If you have time, as a learning exercise, you should try to translate this into one of the scripting languages and see how much more concise and readable it becomes.
regards,
-tom

will try

Gynwer

Joined: 22-April-2007

Posts: 79

Location: Belgium

Posted: 23-July-2008 12:51

I definitely learned a lot in trying to make a "simple" application that handles assets (hardware or software) and generates an impact or dependency tree (which is the code you can see above). I am not in the habit of making applications (system engineering / database administration is my stuff of life) and I was trying to prove a point to our java developers.

That point has been well proven ;-) although I had to pick up dpml and all those x-terms (xpath, xquery, xslt) in the progress. My initial idea included not using a conventional language, but just pushing data around.

What I meant with "better" solution was more like a "did I miss/overlook something" that might have made the above somewhat easier / more performant. It wasn't meant as a challenge :-). As you suggest I'll try using a scripting language (python in my case) and see where that takes me.

Regards (and thanks for the feedback),
Tom

new topic post reply

To find out about new replies to this post as they occur
please subscribe to one of these feeds:

moderate