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:
- Create DB SQL syntax independent SQL.
- 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#) AND 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:
There are three permutations of the wild-card #Branch#:
- #Branch#: gets the ID of each child Package of the parent Package selected by the user.
- #Branch=<GUID># oder #Branch=<ID>#: gets the ID of each child Package of the parent Package specified by the GUID or ID.
For example: #Branch={97C2EAE0-3C4D-4d0d-94DD-E7719B6119A2}# - #Branch=<ID>, <ID>,<ID> #: gets the ID of each child Package under each parent Package specified by its ID.