Excel, Oracle and Sparx EA Part 2 of 5: Maintaining Hierarchy

In my previous post I described how I’ve started pulling data from a Sparx EA repository hosted on an Oracle Database.   The next step was to determine how to maintain the hierarchy for the Packages and Elements so they would appear in the same order in Excel as they appear in my Project Browser in Sparx EA.

The following generic SQL and proprietary Oracle SQL functions were used:

  1. Join Elements to their Package Object in t_object
  2. Connect by prior, to tree through the hierarchy
  3. Order siblings by, to keep siblings grouped together

1.0: Join Elements to their Package Object in t_object

The first thing I had to do was determine how to tree through the hierarchy.   Initially I thought I’d have to use a combination of the package information in t_package and the element information in t_object… then I remembered that Sparx EA pairs each package with an stores additional information about each package in t_object (where t_object.Pdata1 = t_package.packageID), so I could limit my query to the t_object table.

The following SQL query links Element record in t_object to the Package record in t_object for the Elements parent package.

SELECT t2.Object_ID AS Package_Object_ID, t1.* 
FROM t_object t1 
JOIN t_object t2 ON TO_CHAR(t1.Package_ID) = t2.PDATA1
WHERE t_object.ea_guid = '{a GUID}'

2.0: Connect by prior, to tree through the hierarchy

The next step was using the connect by prior feature in Oracle to traverse the hierarchy of all package’s and elements contain within a specified package.

SELECT t2.Object_ID AS Package_Object_ID, t1.* 
FROM t_object t1 
JOIN t_object t2 ON to_char(t1.Package_ID) = t2.PDATA1
START WITH t1.Object_ID =
(
  SELECT t_object.OBJECT_ID
  FROM t_object
  WHERE t_object.PDATA1 = 
  TO_CHAR((
    SELECT t_package.Package_ID
    FROM t_package
    WHERE t_package.ea_guid = '{FFEEA7B5-9468-49c2-A69C-B294266C2C0B}'
  ))
  AND ROWNUM=1
)
CONNECT BY PRIOR 
  t1.Object_ID = (CASE WHEN t1.ParentID = 0 THEN t2.Object_ID ELSE t1.ParentID END)

The ea_guid = {FFEEA7B5-9468-49c2-A69C-B294266C2C0B}, is the GUID of the starting package.

The CASE statement handles Elements that are in the root of a Package (ParentID = 0) vs. those that are children of other Elements (ParentID = another Element’s ID).

  • If ParentID = 0, the current Element is a child of a Package, the Parent in the hierarchy is the Package’s object record, t2.Object_ID (Package_Object_ID)
  • Else, the current Element is a child of another Element

3.0: Order siblings by, to keep siblings grouped together

The previous step retrieved all the records, but they were not in the order I wanted, after a bit more research I read about Order siblings by to preserve ordering within the hierarchy.

SELECT t2.Object_ID AS Package_Object_ID, t1.* 
FROM t_object t1 
JOIN t_object t2 ON to_char(t1.Package_ID) = t2.PDATA1
START WITH t1.Object_ID =
(
  SELECT t_object.OBJECT_ID
  FROM t_object
  WHERE t_object.PDATA1 = 
  TO_CHAR((
    SELECT t_package.Package_ID
    FROM t_package
    WHERE t_package.ea_guid = '{FFEEA7B5-9468-49c2-A69C-B294266C2C0B}'
  ))
  AND ROWNUM=1
)
CONNECT BY PRIOR 
  t1.Object_ID = (CASE WHEN t1.ParentID = 0 THEN t2.Object_ID ELSE t1.ParentID END)
ORDER SIBLINGS BY CONCAT(TO_CHAR(NVL(t1.tpos,0),'0000'),t1.name)

Originally I thought to just order siblings by their name, but then remembered that Sparx EA allows you to manually specify the order of Elements (saved in t_object.tpos), so I used the CONCAT function and TO_CHAR to prefix the name with the tpos value to force the correct sort order.

  • If t1.tpos = null (not specified), the text used for sorting is 0000Name
  • Else if t1.tpos = 22 for example, the text used for sorting is 0022Name

That’s it, if you’re using Sparx EA with Oracle you can now query the repository with the results in the same order as the hierarchy you have in your project browser.   In my next post I describe how I added taggedvalues as columns in the results.

 

Leave a Reply

Your email address will not be published. Required fields are marked *