Efficient scripting alternative for #Branch#

[Geert Bellekens]

Very often when writing scripts I have to do something with a selection of elements in the selected package or or nested packages.
Because iterating all elements of all packages recursively is very slow, I usually try to get the elements I need using an SQL query.

Now when using SQL searches you can use the macro #Branch# to get all package ID's of the currently selected package and all nested packages. Because that macro can't be used in scripting I'm making the package ID string myself. This works ok, but it is not super fast. In the model I was using it on this function alone took about 4 minutes (of the 11 minutes of the whole script)
Does anyone have an idea for a more efficient implementation?
I guess I could bypass the API completely and rely only on database queries to get the ID's of all the packages.

Code: [Select]

'get the package id string of the given package tree
function getPackageTreeIDString(package)
'initialize at "0"
getPackageTreeIDString = "0"
dim packageTree
dim currentPackage as EA.Package
if not package is nothing then
'get the whole tree of the selected package
set packageTree = getPackageTree(package)
' get the id string of the tree
getPackageTreeIDString = makePackageIDString(packageTree)
end if
end function

'returns an ArrayList of the given package and all its subpackages recursively
function getPackageTree(package)
dim packageList
set packageList = CreateObject("System.Collections.ArrayList")
addPackagesToList package, packageList
set getPackageTree = packageList
end function

'add the given package and all subPackges to the list (recursively
function addPackagesToList(package, packageList)
dim subPackage as EA.Package
'add the package itself
packageList.Add package
'add subpackages
for each subPackage in package.Packages
addPackagesToList subPackage, packageList
next
end function

'make an id string out of the package ID of the given packages
function makePackageIDString(packages)
dim package as EA.Package
dim idString
idString = ""
dim addComma
addComma = false
for each package in packages
if addComma then
idString = idString & ","
else
addComma = true
end if
idString = idString & package.PackageID
next
'if there are no packages then we return "0"
if idString = "" then
idString = "0"
end if
'return idString
makePackageIDString = idString
end function
Geert

[PeterHeintz]

Hi Geert
I let some time ago a working student write a SQL query/view returning all packages enriched with 3 package path fields as ID, GUID, Name.
This view I use in EA to join e.g. with t_object and by doing so, I can filter out things on package level in a very flexible way. In fact this query is my backbone for many question I like to answer with SQL.

However if you focus on EAP files this will not work because it uses SQL WITH.
How this SQL Query looks like, see below:

Code: [Select]

WITH PathBuilder(Parent_ID, Package_ID, Package_Name, GUIDPath, NamePath, IDPath) AS (SELECT  Parent_ID, Package_ID, Name,
                           CAST(ea_guid AS VARCHAR(1000)) AS GUIDPath,
                           CAST(Name AS VARCHAR(1000)) AS NamePath,
                           CAST(Package_ID AS VARCHAR(1000)) AS IDPath
FROM   dbo.t_package AS pkg
WHERE (Parent_ID = 0)
UNION ALL
SELECT pkg.Parent_ID, pkg.Package_ID, pkg.Name,
            CAST(pb.GUIDPath + '.' + pkg.ea_guid AS VARCHAR(1000)) AS GUIDPath,
            CAST(pb.NamePath + '.' + pkg.Name AS VARCHAR(1000))  AS NamePath,
            CAST(pb.IDPath + '.' + CAST(pkg.Package_ID AS VARCHAR(1000)) AS VARCHAR(1000)) AS IDPath
FROM    dbo.t_package AS pkg INNER JOIN PathBuilder AS pb ON pkg.Parent_ID = pb.Package_ID)
            SELECT Parent_ID, Package_ID, Package_Name, GUIDPath, NamePath, IDPath FROM PathBuilder AS pb


[Geert Bellekens]

Thanks Peter, that is definitely useful.
I could maybe check the database type and depending on that use the recursive query solution (fast) or the regular solution (slow)

I'm still looking for a good .eap compatible solution.

Geert

[PeterHeintz]

Maybe your search engine gives you some alternatives when searching for MS Access and SQL WITH.

[Geert Bellekens]

I already searched for it, but recursive queries are not supported by MS Access.

Geert

[Paolo F Cantoni]

I already searched for it, but recursive queries are not supported by MS Access.

Geert

We use Pass-Through queries in MS Access, but we're going to SQL Server repositories.

Some research suggests the using of CTE (Common Table Expressions) is related to the question of Adjacency Lists vs Nested Sets (https://explainextended.com/2009/09/28/adjacency-list-vs-nested-sets-oracle/ and https://explainextended.com/2009/09/25/adjacency-list-vs-nested-sets-sql-server/)

If you look at the SQL Server version, it relies on “breadcrumbs” to provide the Nesting (which is provided directly from Oracle).

This may help others.

Paolo

[tsondreal]

I hear conflicting information regarding the use of CTE in EA.  I understand that if EA is running on SQL Server, then CTE is a function that can be leveraged, such as the following: (however this seems to get no response from EA when invoked)
 
WITH
       cte_tree (t_package.Parent_ID, t_package.Name, t_package.Package_ID)
       AS
       (
              SELECT t_package.Parent_ID, t_package.Name, t_package.Package_ID
              FROM t_package
              WHERE t_package.Package_ID = '11487'
              UNION ALL
              SELECT c.Parent_ID, c.Name, c.Package_ID
              FROM t_package c
              INNER JOIN cte_tree p on p.Parent_ID = c.Package_ID
       )
SELECT *
FROM cte_tree
WHERE t_package.Package_ID <> '11487'
 
 
My objective is to retrieve all Packages that are within a hierarchy through a SQL statement.  The #Branch# function works great when there is a requirement to have a User select a Package to start from.  However in a Model View, the goal is for no User interaction regarding selection.

[PeterHeintz]

Hi,
you cannot use WITH directly in EA because EA just ignores it for whatever reason.
In principle all but SELECT statements are ignored.

I use WITH in a MSSQL view and SELECT that view in EA.

Just do it this way, and you can meet your objectives.

[Paolo F Cantoni]

Hi,
you cannot use WITH directly in EA because EA just ignores it for whatever reason.
In principle, all but SELECT statements are ignored.

I use WITH in a MSSQL view and SELECT that view in EA.

Just do it this way, and you can meet your objectives.

+1  This is one of the strengths of SQL - you can substitute a view for a table.

A decade ago, I "lifted" EA up one level by defining all the EA tables as views with my own DB structure underneath that supplied all the views and reacted to updates on the views to maintain our own, more consistent structure.

EA itself appeared to be none the wiser at the time.

Never took it any further though, pity.

Paolo

[Uffe]

you cannot use WITH directly in EA because EA just ignores it for whatever reason.
In principle all but SELECT statements are ignored.

I may be wrong here -- I'm no DBA -- but I think that's because EA basically uses a lowest-common-denominator SQL dialect, and Access doesn't support 'with' clauses.

Either of those contentions may be wrong.

/Uffe

[PeterHeintz]

I may be wrong here as well; but I belief it is some kind of pseudo security to not allow e.g. things like DROP,… (Just a guess).

The SQL statements are at the end, performed by the DB behind anyway, and the different SQL SELECT dialects, are transmitted as far as I see.

[fp123]

So finally i've got the same problem like geert but in a different context.
I want to modify the value of the status of all elements in the currently selected package tree in the project browser via a jscript. The project is stored in a mysql database.

The currently used script consists of a recoursivly called function that runs trough all packages, modifies the element and searches the subfolders for more subfolders and elements....and so on.

Now and then it seems to get into a deathloop and i'd like to do the same thing in an sql-statement.

I have tried the following:

Code: [Select]

var sqlQuery = new String;
sqlQuery = "select * from t_object where t_object.Package_ID in (#Branch#)";
var elements = Repository.GetElementSet(sqlQuery,2);

When executed EA throws a database error [0x00000c03] - Syntax error in 't_object.Package_ID in (#Branch#)'.

Has anyone found a way to use (#Branch#) in an sql statement inside a jscript? The Syntax should be correct, because in the modelsearch it runs as expected.

[qwerty]

#Branch# only works in EA queries. They translate it internally into a recursive search loop for nested packages.

q.

[Geert Bellekens]

In the meantime I made better alternative, see github: https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/blob/master/Framework/Utils/Util.vbs

I have a function getPackageTreeIDString(package)

Code: [Select]

'get the package id string of the given package tree
function getPackageTreeIDString(package)
dim allPackageTreeIDs
set allPackageTreeIDs = CreateObject("System.Collections.ArrayList")
dim parentPackageIDs
set parentPackageIDs = CreateObject("System.Collections.ArrayList")
if not package is nothing then
parentPackageIDs.Add package.PackageID
end if
'get the actual package ids
getPackageTreeIDsFast allPackageTreeIDs, parentPackageIDs
'return
getPackageTreeIDString = Join(allPackageTreeIDs.ToArray,",")
end function
That uses getPackageTreeIDsFast(allPackageTreeIDs, parentPackageIDs)

Code: [Select]

function getPackageTreeIDsFast(allPackageTreeIDs, parentPackageIDs)
if parentPackageIDs.Count = 0 then
if allPackageTreeIDs.Count = 0 then
'make sure there is at least a 0 in the allPackageTreeIDs
allPackageTreeIDs.Add "0"
end if
'then exit
exit function
end if
'add the parent package ids
allPackageTreeIDs.AddRange(parentPackageIDs)
'get the child package IDs
dim sqlGetPackageIDs
sqlGetPackageIDs = "select p.Package_ID from t_package p where p.Parent_ID in (" & Join(parentPackageIDs.ToArray, ",") & ")"
dim queryResult
set queryResult = getVerticalArrayListFromQuery(sqlGetPackageIDs)
if queryResult.Count > 0 then
dim childPackageIDs
set childPackageIDs = queryResult(0)
'call recursive function with child package id's
getPackageTreeIDsFast allPackageTreeIDs, childPackageIDs
end if
end function]function getPackageTreeIDsFast(allPackageTreeIDs, parentPackageIDs)
if parentPackageIDs.Count = 0 then
if allPackageTreeIDs.Count = 0 then
'make sure there is at least a 0 in the allPackageTreeIDs
allPackageTreeIDs.Add "0"
end if
'then exit
exit function
end if
'add the parent package ids
allPackageTreeIDs.AddRange(parentPackageIDs)
'get the child package IDs
dim sqlGetPackageIDs
sqlGetPackageIDs = "select p.Package_ID from t_package p where p.Parent_ID in (" & Join(parentPackageIDs.ToArray, ",") & ")"
dim queryResult
set queryResult = getVerticalArrayListFromQuery(sqlGetPackageIDs)
if queryResult.Count > 0 then
dim childPackageIDs
set childPackageIDs = queryResult(0)
'call recursive function with child package id's
getPackageTreeIDsFast allPackageTreeIDs, childPackageIDs
end if
end function
this function builds the list of packageID's and calls itself recursively to get the next level of packageID's with an SQL Query.

It is faster then the regular way because

- It doesn't instantiate EA.Package objects or iterate EA.Collections
- If the package tree is 5 levels deep, it only needs 5 SQL Queries to get all the packageID's of that tree, regardless of the width of the tree.

I believe this is about as fast as using #Branch# in an SQL Search.

We had a topic not too long ago (couple of weeks, maybe few months ago) about this very topic, where some others posted their solutions to this same problem.

Geert

[chrislro]

In the meantime I made better alternative, see github

Hm, nice, I also like to use an alternative for #Branch# in scripts. Is there also a JScript alternative for this somewhere available? Then I would not need to rewrite it ...