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.
Free download
The model
Suppose we have modeled an inheritance hierarchy of animals as such
And our animals are being used as the classifier of Activity partitions in various Activity diagrams
And on the occasional Sequence diagram
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
By dragging the column UsedElement up to the grouping header you get an even better result
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.
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?
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.
That sounds pretty good! 🙂
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.
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