Author Topic: Efficient scripting alternative for #Branch#  (Read 230 times)

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7740
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Efficient scripting alternative for #Branch#
« on: December 06, 2017, 10:46:51 pm »
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

  • EA User
  • **
  • Posts: 549
  • Karma: +37/-14
    • View Profile
Re: Efficient scripting alternative for #Branch#
« Reply #1 on: December 06, 2017, 11:19:38 pm »
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
Best regards,

Peter Heintz

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7740
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Efficient scripting alternative for #Branch#
« Reply #2 on: December 06, 2017, 11:38:28 pm »
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

  • EA User
  • **
  • Posts: 549
  • Karma: +37/-14
    • View Profile
Re: Efficient scripting alternative for #Branch#
« Reply #3 on: December 06, 2017, 11:52:32 pm »
Maybe your search engine gives you some alternatives when searching for MS Access and SQL WITH.
Best regards,

Peter Heintz

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7740
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Efficient scripting alternative for #Branch#
« Reply #4 on: December 07, 2017, 12:04:16 am »
I already searched for it, but recursive queries are not supported by MS Access.

Geert

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 5882
  • Karma: +71/-78
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Efficient scripting alternative for #Branch#
« Reply #5 on: December 07, 2017, 10:41:55 am »
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
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

tsondreal

  • EA Novice
  • *
  • Posts: 4
  • Karma: +0/-0
    • View Profile
Re: Efficient scripting alternative for #Branch#
« Reply #6 on: December 11, 2017, 05:22:41 am »
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

  • EA User
  • **
  • Posts: 549
  • Karma: +37/-14
    • View Profile
Re: Efficient scripting alternative for #Branch#
« Reply #7 on: December 11, 2017, 08:35:02 am »
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.
Best regards,

Peter Heintz

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 5882
  • Karma: +71/-78
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Efficient scripting alternative for #Branch#
« Reply #8 on: December 11, 2017, 10:49:09 am »
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
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

Uffe

  • EA Practitioner
  • ***
  • Posts: 1072
  • Karma: +81/-5
  • Flutes: 1; Clarinets: 1; Saxes: 5 and counting
    • View Profile
Re: Efficient scripting alternative for #Branch#
« Reply #9 on: December 11, 2017, 09:01:29 pm »
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
My theories are always correct, just apply them to the right reality.

PeterHeintz

  • EA User
  • **
  • Posts: 549
  • Karma: +37/-14
    • View Profile
Re: Efficient scripting alternative for #Branch#
« Reply #10 on: December 11, 2017, 09:11:51 pm »
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.
Best regards,

Peter Heintz