Hierarchical usage search in Enterprise Architect with SQL

How can I find out where an element or one of its specialized elements is used on a diagram in Enteprise Architect?

With this SQL search you can answer that question immediately and in a useful way.

Hierarchical usage search regults

Free download

 The model

Suppose we have modeled an inheritance hierarchy of animals as such

Animals Hierarchy

And our animals are being used as the classifier of Activity partitions in various Activity diagrams

Activity2

Activity1

And on the occasional Sequence diagram

Interaction1

Even with a simple example like this one it is not trivial to find all diagrams that use a rodent, or a bird.

The query

The query should return all diagrams that use an element either as link or as classifier. It should also include the diagrams for the sub-classes of the element we are looking for.

In theory a search like this should be carried out recursively, traversing the hierarchy until it reaches the bottom of the hierarchy. Unfortunately SQL and recursive are not the best friends, so we will have to limit the search to a pre-defined number of levels. In this case we go three levels down, so from the parent, to the the children, the grandchildren and the great-grandchildren.

Each of these levels is queried individually and each of the queries are added together with unions.

The first part of the query is to find diagrams that use the parent element as a link:

select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  po.Name as UsedElement, 'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((t_object po
inner join t_diagramObjects do on do.Object_ID = po.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'

Then we get the diagrams that use the parent element as a classifier

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  po.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((t_object po
inner join t_object poi on po.Object_ID = poi.Classifier)
inner join t_diagramObjects do on do.Object_ID = poi.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'

Then descending a level we get the diagrams showing the children as link. Note that we include all possible variations of Generalization and Realization just to be sure we have everything. EA has been know to be not too consistent with these connector types in the past.

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c1.Name as UsedElement,'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_diagramObjects do on do.Object_ID = c1.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

And we do the same for the children being used a classifier.

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c1.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_object c1i on c1.Object_ID = c1i.Classifier)
inner join t_diagramObjects do on do.Object_ID = c1i.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

Descending another level to the grandchildren

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c2.Name as UsedElement,'Link' as UsageType,
 package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
 from ((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_diagramObjects do on do.Object_ID = c2.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

And the grandchildren as classifier

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c2.Name as UsedElement,'Classifier' as UsageType,
 package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
 from (((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_object c2i on c2.Object_ID = c2i.Classifier)
inner join t_diagramObjects do on do.Object_ID = c2i.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

Then to the last level, the great-grandchildren as link

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c3.Name as UsedElement,'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_connector poc3 on poc3.End_Object_ID = c2.Object_ID)
inner join t_object c3 on c3.Object_ID = poc3.Start_Object_ID)
inner join t_diagramObjects do on do.Object_ID = c3.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc3.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

An the last one, the great-grandchildren as classifier

union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c3.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_connector poc3 on poc3.End_Object_ID = c2.Object_ID)
inner join t_object c3 on c3.Object_ID = poc3.Start_Object_ID)
inner join t_object c3i on c3.Object_ID = c3i.Classifier)
inner join t_diagramObjects do on do.Object_ID = c3i.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc3.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

The complete query then becomes

select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  po.Name as UsedElement, 'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((t_object po
inner join t_diagramObjects do on do.Object_ID = po.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  po.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((t_object po
inner join t_object poi on po.Object_ID = poi.Classifier)
inner join t_diagramObjects do on do.Object_ID = poi.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c1.Name as UsedElement,'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_diagramObjects do on do.Object_ID = c1.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c1.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_object c1i on c1.Object_ID = c1i.Classifier)
inner join t_diagramObjects do on do.Object_ID = c1i.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c2.Name as UsedElement,'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_diagramObjects do on do.Object_ID = c2.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c2.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_object c2i on c2.Object_ID = c2i.Classifier)
inner join t_diagramObjects do on do.Object_ID = c2i.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c3.Name as UsedElement,'Link' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from ((((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_connector poc3 on poc3.End_Object_ID = c2.Object_ID)
inner join t_object c3 on c3.Object_ID = poc3.Start_Object_ID)
inner join t_diagramObjects do on do.Object_ID = c3.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc3.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
union
select d.ea_guid AS CLASSGUID, d.Diagram_Type as CLASSTYPE, d.Name as DiagramName,  c3.Name as UsedElement,'Classifier' as UsageType,
package.name as PackageName ,package_p1.name as Package_level1,package_p2.name as Package_level2 ,package_p3.name as Package_level3
from (((((((((((((t_object po
inner join t_connector poc1 on poc1.End_Object_ID = po.Object_ID)
inner join t_object c1 on c1.Object_ID = poc1.Start_Object_ID)
inner join t_connector poc2 on poc2.End_Object_ID = c1.Object_ID)
inner join t_object c2 on c2.Object_ID = poc2.Start_Object_ID)
inner join t_connector poc3 on poc3.End_Object_ID = c2.Object_ID)
inner join t_object c3 on c3.Object_ID = poc3.Start_Object_ID)
inner join t_object c3i on c3.Object_ID = c3i.Classifier)
inner join t_diagramObjects do on do.Object_ID = c3i.Object_ID)
inner join t_diagram d on d.Diagram_ID = do.Diagram_ID)
inner join t_package package on package.Package_ID = d.Package_ID)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where po.Name = '<Search Term>'
and poc1.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc2.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')
and poc3.Connector_Type in ('Generalization', 'Realization', 'Generalisation', 'Realisation')

The results

If you execute the search for Rodent you get something like following

Hierarchical usage search results_raw

By dragging the column UsedElement up to the grouping header you get an even better result

Hierarchical usage search regults

Although the icon for diagrams in search results is a bit weird, you can still use them as you would other search results. You can double-click a line to open the diagram, or you can right click and select it in the project browser.

More about SQL Searches

6 thoughts on “Hierarchical usage search in Enterprise Architect with SQL

  1. Hi Geert, do you think this query may have severe impact on the performance of EA models containing thousands of elements in an RDBMS, or the LEFT joins should limit computational needs sufficiently?

    1. I’m pretty sure they query works pretty fast on even the largest databases.
      Unions of smaller queries often work a lot faster then a lot of “OR’s” in the where clause.

  2. On wikipedia there is an article about ‘hierarchical and recursive queries in sql’. It gives an introduction to the ‘common table expression’ which is part of the sql:1999 standard and supported by most rdbms.

    1. Hi Ben,

      Thanks for the info. Unfortunately there are a few drawbacks for using it in EA searches.
      – CTE is not supported by MS-Access SQL syntax, which is used when dealing with .eap files
      – CTE syntax is blocked by EA when doing SQL searches. If the query doesn’t start with SELECT it won’t get executed.

      Geert

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.