Rob Thompson

rob.sun3.org

Home · Oracle

Article Categories:

New Articles:

Contact:

New comments:

Oracle Tree Relationships

Working with parent-child relationships between rows in tables can be a headache to deal with, especially if you need to display those relationships graphically. However, Oracle provides some great constructs that make this much easier to deal with. Here are some of the challenges that I ran into and the solutions I came up with:

1) Maintaining and managing integrity of the tree table.
   Solution: Oracle CONNECT BY, LEVEL and START WITH clauses

2) Enforcing business rules based on the relationships.
   Solution: Oracle functions and procedures

3) Displaying the parent-child relationships graphically.
   Solution: Recursive functions in ColdFusion and PHP

The stuff below is largely suited to the application that I worked on, the way it was solved may be useful to you if you have any table with a tree relationship and a need to show users that tree.

The Data

Here is the table structure I worked with. The ID field is the parent ID of the node and the field PARENT_ID is the reference to the parent.




 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER(10)

 OBJECT_ID                                 NOT NULL NUMBER(10)

 ACCESS                                    NOT NULL VARCHAR2(6)

 DELEGATE                                  NOT NULL NUMBER(1)

 PARENT_ID                                          NUMBER(10)

 ENABLED                                   NOT NULL NUMBER(1)

 CREATED                                   NOT NULL DATE

 CREATED_BY                                NOT NULL VARCHAR2(6)

 MODIFIED                                           DATE

 MODIFIED_BY                                        VARCHAR2(6)

 NOTE                                               VARCHAR2(400)

The data in the table looks like the following. The ACCESS field is a system user and the delegate field is a row-specific attribute that determines in this case whether the user can delegate authority to other users for OBJECT_ID 1:




ACCESS         ID  OBJECT_ID  PARENT_ID   DELEGATE

------ ---------- ---------- ---------- ----------

zz1116        174          1        173          0

zz1117        175          1        173          1

zz1112        172          1        164          0

zz1113        173          1        164          1

ab5602          8          1                     1

ak1520         87          1          8          1

zz1111        164          1          8          1

ab7096        165          1          8          1

aa7648        166          1          8          1

af0449        167          1          8          1

Maintaining Tree Integrity: Oracle CONNECT BY, START WITH and LEVEL

Oracle functions and procedures are an excellent way to accomplish this. By using a function, you can ensure that all data that is inserted into the tree table is done properly and with the business rules that you want to enforce. You can grant access to this function, and not to the table itself if you are working with several people who need access to the function. The key to dealing with tree relationships in oracle are the CONNECT BY and START WITH commands. Here is a select that uses these in my table:




SELECT 

    ID, 

    OBJECT_ID, 

    ACCESS, 

    DELEGATE, 

    PARENT_ID, 

    LEVEL, 

    SYS_CONNECT_BY_PATH(access||':'||delegate, '/') as PATH

FROM YOUR_TABLE

    WHERE

    OBJECT_ID=1

    CONNECT BY PARENT_ID = PRIOR ID 

    START WITH lower(trim(ACCESS))='ab5602'

Notice that the PATH column shows the tree relationship between the different rows and the LEVEL column has the tree level that the user is at in the tree!




   ID  OBJECT_ID ACCESS   DELEGATE  PARENT_ID      LEVEL PATH

----- ---------- ------ ---------- ---------- ---------- ----------------------------------------

    8          1 ab5602          1                     1 /ab5602:1

   87          1 ak1520          1          8          2 /ab5602:1/ak1520:1

  164          1 zz1111          1          8          2 /ab5602:1/zz1111:1

  172          1 zz1112          0        164          3 /ab5602:1/zz1111:1/zz1112:0

  173          1 zz1113          1        164          3 /ab5602:1/zz1111:1/zz1113:1

  174          1 zz1116          0        173          4 /ab5602:1/zz1111:1/zz1113:1/zz1116:0

  175          1 zz1117          1        173          4 /ab5602:1/zz1111:1/zz1113:1/zz1117:1

  165          1 ab7096          1          8          2 /ab5602:1/ab7096:1

  166          1 aa7648          1          8          2 /ab5602:1/aa7648:1

  167          1 af0449          1          8          2 /ab5602:1/af0449:1

Making Graphical Trees

Although the above information will really help while developing the procedures/functions that will be necessary to determine who is above and below a specific user in the tree when enforcing business rules, you will still need to write some recursive functions for your web page if you want to display this information graphically. Here is what the recursive functions I wrote provides. The hand function denotes that the user has DELEGATE=1:

Here is the script to generate the visual in tree in ColdFusion. The way I implemented it was to create a function that begins table structures and iterates throughout the tree by calling WHERE PARENT_ID=[this child]. The HTML tables do not get their endtags until the script reaches the end of the tree.




<cffunction name="GenerateTree" access="private" returntype="string" output="yes">

        <cfargument name="object" type="numeric" required="true">

        <cfargument name="startwith" type="string" required="true">

        <cfargument name="parent_id" type="numeric" required="false">

        <cfargument name="thislevel" type="string" required="false">

        <cfargument name="thisiteration" type="string" required="false">



            <cfif NOT IsDefined("thisiteration")>

                <cfset thisiteration=1>

                <cfset thislevel=1>

            </cfif>

        

            <cfquery name="DATreeCol#thisiteration##thislevel#"  datasource="#application.m_datasource#">

                    select id, 

                        object_id, 

                        access, 

                        delegate, 

                        parent_id, 

                        enabled, 

                        created, 

                        created_by, 

                        modified, 

                        modified_by, 

                        note, 

                        level, 

                        SYS_CONNECT_BY_PATH(access||':'||delegate, '/') as Path

                    from your_table

                      WHERE

                      object_id = #object# AND

                        level = #thislevel# AND

                        <cfif thisiteration GT 1>

                            parent_id = #parent_id# AND

                        </cfif>

                      enabled=1

                      connect by parent_id = prior id 

                      start with lower(trim(access))='#startwith#'

            </cfquery>





            <cfloop query="DATreeCol#thisiteration##thislevel#">

                    <table border=0>

                        <tr>

                            <td nowrap width=5 valign=center style='font-size: .75em;

                                                         width:10px;height:100%;border-top:1px solid ##AAAAAA;

                                                         border-bottom:1px solid ##AAAAAA;

                                                         border-left:1px solid ##AAAAAA;'>

                                                         &nbsp;</td><td>

                                                         <span class='fixeduid' style='cursor:pointer; 

                                                         font-size:.75em;' 

                                                         onclick="javascript:userinfo('#access#');">#access#

                                                         </span>

                            </td>

                            <td>                

                            <cfif #delegate# EQ 1>

                                <img src='icons/hand.jpg'>

                            <cfelse>

                                <img src='icons/x.jpg'>

                            </cfif>

                            </td>

                            <td>

                            <cfset thislevel=#level# + 1>

                            <cfset thisiteration=thisiteration + 1>

                            <CFINVOKE object="#object#" thislevel="#thislevel#"

                                                                startwith="#startwith#"

                                                                thisiteration="#thisiteration#"

                                                                parent_id=#id# RETURNVARIABLE="foo" 

                                                                METHOD="GenerateTree">

                            </CFINVOKE>    

                            </td>

                        </tr>

                    </table>

            </cfloop>



        <cfreturn "test">

    </cffunction>

    

<CFINVOKE object=#URL.object# startwith="#session.access#" METHOD="GenerateTree">

</CFINVOKE>

Here is a similar script that produces the same output written in PHP:




<?



function generateList_tree($obj)

        {

        global $oracle_server, $oracle_user, $oracle_password, $oracle_tns;



        $conn = ocilogon($oracle_user,$oracle_password,$oracle_tns);



        $query ="SELECT A.ID, A.OBJECT_ID, A.ACCESS, A.DELEGATE, A.PARENT_ID, B.OBJECT_NAME FROM MSP_SEC_DELEGATE A

        LEFT JOIN MSP_SEC_OBJECTS B ON A.OBJECT_ID = B.ID

        WHERE A.PARENT_ID is NULL AND A.OBJECT_ID=" . $obj . " AND A.ENABLED=1 ORDER BY A.ACCESS";



        $statement = ociparse ($conn, $query);

        ociexecute ($statement);

        oci_fetch_all($statement,$res, 0, -1 ,OCI_ASSOC);



        for ($i=0;$i<sizeof($res[ID]);$i++)

                {

                $list[$i][ID] = $res[ID][$i];

                $list[$i][ACCESS] = $res[ACCESS][$i];

                $list[$i][DELEGATE] = $res[DELEGATE][$i];

                $list[$i][OBJECT_NAME] = $res[OBJECT_NAME][$i];

                }



        return $list;

        }



function generateChildList_tree($root)

        {

        global $oracle_server, $oracle_user, $oracle_password, $oracle_tns;



        $conn = ocilogon($oracle_user,$oracle_password,$oracle_tns);



        $query ="SELECT A.ID, A.OBJECT_ID, A.ACCESS, A.DELEGATE, A.PARENT_ID FROM MSP_SEC_DELEGATE A

        LEFT JOIN MSP_SEC_OBJECTS B ON A.OBJECT_ID = B.ID

        WHERE A.PARENT_ID=" . $root . " AND A.ENABLED=1 ORDER BY A.ACCESS";



        $statement = ociparse ($conn, $query);

        ociexecute ($statement);

        oci_fetch_all($statement,$res, 0, -1 ,OCI_ASSOC);



        for ($i=0;$i<sizeof($res[ID]);$i++)

                {

                $list[$i][ID] = $res[ID][$i];

                $list[$i][ACCESS] = $res[ACCESS][$i];

                $list[$i][DELEGATE] = $res[DELEGATE][$i];

                }



        if (sizeof($list) == 0)

                {

                return false;

                }

        else 

                {

                echo "<table border=0 cellpadding=0 cellspacing=0>";



                for ($j=0;$j<sizeof($list);$j++)

                        {



                        echo "<tr>";

                        echo "<td valign=center style='font-family:courier;'>";

                        echo "<a href=''>" . $list[$j][ACCESS]. "</a>&nbsp;";

                        echo "</td>";



                        echo "<td  valign=center>";

                        if ($list[$j][DELEGATE] == 1)

                                {

                                echo "<img src='icons/hand.png'>";

                                echo "&nbsp;</td>";

                                echo "<td style='width:10px;height:100%;border-top:1px solid

                                      #AAAAAA;border-bottom:1px solid #AAAAAA;border-left:1px solid 

                                      #AAAAAA;' valign=center>";

                                echo "&nbsp;</td>";

                                }

                        else 

                                {

                                echo "<img src='icons/x.png'>";

                                echo "&nbsp;</td>";

                                echo "<td style='width:10px;height:100%;' valign=center>";

                                echo "&nbsp;</td>";

                                }



                        echo "<td  valign=center>";

                        generateChildList_tree($list[$j][ID]);

                        echo "</td>";

                        echo "</tr>";

                        echo "<tr><td></td></tr>";

                        }

                echo "</table>";

                return $list[$j][ID];

                }

        }





$list = generateList_tree($obj);

echo "<h4>Tree For: " . $list[0][OBJECT_NAME] . "</h4>";

echo "<table border=0 cellpadding=0 cellspacing=0>";





for ($i=0;$i<sizeof($list);$i++)

        {



        echo "<tr>";

        echo "<td valign=center style='font-family:courier;'>";

        echo "<a href=''>" . $list[$i][ACCESS]. "</a>&nbsp;";

        echo "</td>";



        echo "<td  valign=center>";

        if ($list[$i][DELEGATE] == 1)

                {

                echo "<img src='icons/hand.png'>";

                echo "&nbsp;</td>";

                echo "<td style='width:10px;height:100%;border-top:1px solid #AAAAAA;

                       border-bottom:1px solid #AAAAAA;border-left:1px solid #AAAAAA;' 

                       valign=center>&nbsp;";

                echo "&nbsp;</td>";

                }

        else 

                {

                echo "<img src='icons/x.png'>";

                echo "&nbsp;</td>";

                echo "<td style='width:10px;height:100%;' valign=center>";

                echo "&nbsp;</td>";

                }





        echo "<td  valign=center>";

        $thischild = generateChildList_tree($list[$i][ID]);

        echo "</td>";

        echo "</tr>";

        echo "<tr><td></td></tr>";



        }



echo "</table>";



?>

Comments (0) 08/17/2007 04:41am