Oracle Tree Relationships August 17, 2007
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:1Making 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;'>
</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> "; echo "</td>"; echo "<td valign=center>"; if ($list[$j][DELEGATE] == 1) { echo "<img src='icons/hand.png'>"; echo " </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 " </td>"; } else { echo "<img src='icons/x.png'>"; echo " </td>"; echo "<td style='width:10px;height:100%;' valign=center>"; echo " </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> "; echo "</td>"; echo "<td valign=center>"; if ($list[$i][DELEGATE] == 1) { echo "<img src='icons/hand.png'>"; echo " </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 " </td>"; } else { echo "<img src='icons/x.png'>"; echo " </td>"; echo "<td style='width:10px;height:100%;' valign=center>"; echo " </td>"; } echo "<td valign=center>"; $thischild = generateChildList_tree($list[$i][ID]); echo "</td>"; echo "</tr>"; echo "<tr><td></td></tr>"; } echo "</table>"; ?>
I need definition of entity relationship