Modell-Suche in Package-Branch

The Enterprise Architect model repository is a database. Databases can be easily queried with SQL. This results in a wealth of possibilities. A minor problem is recursions! See this blog article.

The structure in the ProjectBrowser – the model tree – corresponds to a parentID, which is stored in the line of the child. To create a recursive query here, we need recursive SQL, which supports some databases.

Example of recursive SQL in MSSQL Server:

GO
WITH MyCTE( Object_ID, Name) AS
  SELECT t_object.Object_ID, t_object.Name
  FROM t_object
  WHERE t_object.Name = 'HorstKargl'
UNION ALL
  SELECT A.Object_ID, A.Name
  FROM t_object A INNER JOIN MyCTE B
  ON B.Object_ID = A.ParentID
)

SELECT
*
FROM MyCTE

Unfortunately, no recursive SQL can be called from an EA model search. Too bad. But there is a remedy!

The EA provides some keywords that will be replaced with a model search before it is executed:

  • #Author#
  • #Branch#:  Gets the IDs of the child Packages of the currently-selected Package, working recursively down to the lowest level of sub-Package. For example: t_object.Package_ID IN (#Branch#)
  • #Concat value1, value2 …#
  • #Datepart <field>, column#
  • #CurentElementGUID#
  • #CurrentElementID#
  • #DB=<DBNAME>#
  • #Now#
  • #Package#
  • #UserName#
  • #WC#. Gets the appropriate wild card for the current database, so the search can be performed on models on different databases. 

So we can:

  1. Create DB SQL syntax independent SQL.
  2. Especially with # Branch # search a special package branch.

Each model element stores the ID of the package in which it resides. The t_object.Package_ID is thus the parent in which the model element lies. If we use the keyword #Branch#, the PackageIds of the package currently selected in the Project Browser will be calculated and returned.

The SQL:

SELECT * FROM t_object WHERE t_object.Package_ID IN (#Branch#) WHERE t_object.Status = 'Proposed'

returns all model elements with the status 'Proposed', which are contained in the currently selected package in the Project Browser.

With the following query, we can populate a model chart in the EA with the restriction to only consider Model-Elements within a Package branch:

SELECT t_object.Status AS GroupName, t_object.Author AS Series FROM t_object WHERE t_object.Package_ID IN (#Branch#)

As a result, we get the following charts, depending on what is currently selected in the Project Browser. Even if no package is selected, the package_ID of the package in which the selected Model-Element or Diagram is currently located is used:

 

Currently it is not possible to set a specific package as a root!
# Branch = {PACKAGE_GUID} # is currently not possible.

Therefore, we can not yet create a dashboard in which we show results from different package branches.
A workaround is to manually create the list of Package_IDs and use them in the SQL query.
If the model structure is rebuilt, we will have to adjust the query as well.

Using script or add-in, we could remedy this situation and even calculate the list of IDs. We would have that
even more options!

Posted in Model Search
Tags: