Sparx Systems Forum
Enterprise Architect => General Board => Topic started by: Roche Pso on June 13, 2025, 01:34:40 am
-
I have built a number of system architectures in our EA system model. Each architecture is a hierarchy of objects showing how the system decomposes. Pretty normal stuff. People call these hierarchies product breakdown structures, but all differ on which one is *the* PBS and which ones are other similar views of the system architectures.
Due to working with mechanical engineers I have a need to export various architectures or parts of architectures as PBS tables, they can't cope with diagrams. This is where I am having trouble.
If all the objects in the hierarchy are nested in the project browser then it is easy to make a standard report using the element/child thing in the report template and it comes out with a nice hierarchical list in the report:
If the objects in the hierarchy are linked via some kind of aggregation/composition type relationship, but are not nested in the project browser, I have a working recursive query that can start at any object and give me a table containing all the linked objects beneath it to the bottom of the structure. So I have used this as the basis for a report template and it works fine. HOWEVER... this query and report does not seem to be able to be ordered into the nice hierarchy list I get with the basic report template method. Instead I can choose, in the query, a field to order by, so I can order alphabetically by name, or I can order by the level within the tree etc. None of these give me what I need. I have resorted to outputting to excel and manually reordering the lines into the structure I need.
How do I achieve a proper hierarchy table when things are not nested in the project browser?
-
With a query you should be able to do that.
I usually don't use a recursive query, but instead opt for a "fixed" query with enough levels.
That allows you to keep track of the "fully qualified name" to use as the order by field.
Geert
-
Interesting topic and a problem that I face regularly.
What would you put in the query? I am assuming that by using the query a document fragment will be required.
I have a similar requirement and I can only think of one field I could use to order the results of a query per the requirements I need to meet: populate the Alias with an alphanumerical series matching the order I need.
Do you have any other suggestions?
-
Suppose I have the following structure
package1
- Package1.1
- Element1.1.1
- Element1.1.2
- Package1.2
package2
I would make a query that contains a field with the fully qualified name of each element
package1
package1.Package1.1
package1.Package1.1.Element1.1.1
package1.Package1.1.Element1.1.2
package1.Package1.2
package2
And sort on that field.
Geert
-
I'll give that a go and see what happens
I like the recursive query because I don't have to guess how many levels I will have and I can use it on any object at any level without having to modify it, it's just the output isn't the way I want it :(
-
I'll give that a go and see what happens
I like the recursive query because I don't have to guess how many levels I will have and I can use it on any object at any level without having to modify it, it's just the output isn't the way I want it :(
I understand. Maybe this output is possible with recursive queries as well, but I don't have enough experience with those (they tend to hurt my head when I try to use them)
Geert
-
Suppose I have the following structure
package1
- Package1.1
- Element1.1.1
- Element1.1.2
- Package1.2
package2
I would make a query that contains a field with the fully qualified name of each element
package1
package1.Package1.1
package1.Package1.1.Element1.1.1
package1.Package1.1.Element1.1.2
package1.Package1.2
package2
And sort on that field.
Geert
This does not take into consideration elements linked relationships such aggregations or compositions, elements not nested in the browser - i.e., it does not address visual nesting. This something that Roche mentioned in his original post and caught my attention.
I also agree with Roche that a recursive query factoring in relationships should address the requirement but the problem is getting the thing sorted correctly.
-
As far as I recall, EA does not like recursive queries. Is that still the case?
Henrik
-
As far as I recall, EA does not like recursive queries. Is that still the case?
Henrik
Depends on the RDBMS and how they are implemented. We have written plenty of recursive queries as views and expose them as simple SELECT statements with a WHERE clause.
Generally speaking Sparx EA does not like anything that does not start with a SELECT statement, this immediately eliminates Common Table Expressions. In my opinion, it is already time that limitation is addressed.
-
Geert's suggestion of creating a path / fully qualified name works perfectly
A heavily simplified version of my query is:
select * from (
with recursive hierarchy as (
select
parent.Object_ID as ParentID,
parent.name as ParentName,
child.Object_ID as ChildID,
child.name as ChildName,
parent.Object_ID || '.' || child.Object_ID as pth,
'' as indent
from
t_object as parent
join
t_connector as connector on parent.Object_ID = connector.End_Object_ID
join
t_object as child on connector.Start_Object_ID = child.Object_ID
where
parent.Object_ID = #CurrentElementID# and connector.Connector_Type = 'Aggregation'
union all
select
h.ChildID as ParentID,
h.ChildName as ParentName,
child.Object_ID as ChildID,
child.name as ChildName,
h.pth || '.' || child.Object_ID as pth,
h.indent || '-' as indent
from
hierarchy as h
join
t_connector as connector on h.ChildID = connector.End_Object_ID
join
t_object as child on connector.Start_Object_ID = child.Object_ID
where
connector.Connector_Type = 'Aggregation'
)
select
indent || Childname as rowName
from
hierarchy
order by
pth
)
pth (path seems to be a keyword so I avoided it) and indent are the bits I have added to the base recursive query: sorting by pth gets everything in the right order and then concatenating indent onto the front of the Childname gives you the kind of indenting people are used to in PBS tables (I would like to use space not "-" as the indenting character but for some reason that didnt work)
Thanks Geert!
-
Really nice query, that looks like a PostgreSQL, Oracle, and SQLite query and elegant idea. Will love to see a SQL Server version of it.
-
I am very pleased with it, but I can't take any real credit for it. Co-Pilot gave me the initial query which didn't quite work but had the basic structure right and only needed a small bit of tweaking to make it work in EA. Then Geert provided the last bit of inspiration needed to give the correctly formatted output.
-
At least you should take credit for combining your idea with Geert’s suggestion. I’ll see if Copilot can help converting your query to a SQL Server query/view.
-
Hi,
Sorry to break up the party, but SQL Server does not support CTE ( Common Table Expressions).
When I first saw this solution, my first thought was "that ain't gonna work", until I read on that this is for Oracle or some other DB.
I used to use CTE very often (in DB2). It makes the queries so much easier to understand, test and debug. It is a pity that Microsoft chooses not to support it.
Shimon
-
P.S. You can ask copilot to give you fix it so that it works on SQL server. It won't be so elegant, but it'll probably work.
Shimon
-
Hi,
Sorry to break up the party, but SQL Server does not support CTE ( Common Table Expressions).
When I first saw this solution, my first thought was "that ain't gonna work", until I read on that this is for Oracle or some other DB.
I used to use CTE very often (in DB2). It makes the queries so much easier to understand, test and debug. It is a pity that Microsoft chooses not to support it.
Shimon
SQL Server supports CTEs (Common Table Expressions) and has done so since version 2005 - i.e., 20 years.
The issue with Roche's query is that Sparx EA does not support any SQL Statement that does not start with a SELECT statement - i.e., it does not support a SQL starting with "WITH". In my opinion, it is already time Sparx Systems addresses this issue and does not wait until Microsoft introduces support for the syntax used by Roche.
I have taken Roche's query and refactor it into a SQL Server view which I currently cannot post here and can be used in a ModelView.
Happy to share it after Sparx Systems fixes the security issue with this forum preventing creating posts with code samples including SQL Statements.
-
Happy to share it after Sparx Systems fixes the security issue with this forum preventing creating posts with code samples including SQL Statements.
I have nothing to do with the administration for our website, but from what I'm aware what you're describing as a security issue is that the Cloudflare SQL injection protection is stronger than you would like in this particular instance.
Yes, it's technically an issue that you are experiencing with the security for the forum. But what you seem to be asking for is removing/weakening that security.
-
Yes, it's technically an issue that you are experiencing with the security for the forum. But what you seem to be asking for is removing/weakening that security.
Just to be clear, I am not asking and will never ask for weaking security. However, Reddit and Stack Overflow support including SQL statements and do not class it as a SQL Injection, and, from a user functionality point of view, I do not see why this forum should no longer support the inclusion of SQL Statements in posts.
Furthermore, the implication is that the software running the forum, which was not written by Sparx Systems, is unsafe, subject to SQL injections.
-
Thanks Modesto,
You are indeed right. It is Access that does not support CTE, and I thought that it was MS-SQL.
-
Just to be clear, I am not asking and will never ask for weaking security. However, Reddit and Stack Overflow support including SQL statements and do not class it as a SQL Injection, and, from a user functionality point of view, I do not see why this forum should no longer support the inclusion of SQL Statements in posts.
You are explicitly advocating to remove a security function. It may be excessive in its implementation but that doesn't change that it is still a security function.
Furthermore, the implication is that the software running the forum, which was not written by Sparx Systems, is unsafe, subject to SQL injections.
No, I'm stating that the cautious approach is to assume that unknown vulnerabilities may exist in any software.
-
Thanks Modesto,
You are indeed right. It is Access that does not support CTE, and I thought that it was MS-SQL.
Thank you for confirming, Shimon.
-
Just to be clear, I am not asking and will never ask for weaking security. However, Reddit and Stack Overflow support including SQL statements and do not class it as a SQL Injection, and, from a user functionality point of view, I do not see why this forum should no longer support the inclusion of SQL Statements in posts.
You are explicitly advocating to remove a security function. It may be excessive in its implementation but that doesn't change that it is still a security function.
Furthermore, the implication is that the software running the forum, which was not written by Sparx Systems, is unsafe, subject to SQL injections.
No, I'm stating that the cautious approach is to assume that unknown vulnerabilities may exist in any software.
11 June 2025 there were at least 2 posts with SQL Statements: https://sparxsystems.com/forums/smf/index.php/topic,49043.msg283626.html#msg283626 (https://sparxsystems.com/forums/smf/index.php/topic,49043.msg283626.html#msg283626) and https://sparxsystems.com/forums/smf/index.php/topic,49042.msg283625.html#msg283625 (https://sparxsystems.com/forums/smf/index.php/topic,49042.msg283625.html#msg283625), with the 2nd one having a post with a SQL Statement dated 13 June 2025. Since then, the security was tightened, intentionally or accidentally. If intentionally the result is that some forum functionality was removed.
I am arguing for having functionality used by many of us for years restored.