Sparx Systems Forum
Enterprise Architect => General Board => Topic started by: martin1 on August 05, 2024, 10:46:05 pm
-
Dear all, I try to accomplish the following:
* I want to write an SQL query returning all elements (e.g. requirements, classes, blocks) of a package hierarchy
* The only parameter is the Package_ID of the "root" node
* No recursion mechanisms, no macros
* The number of levels to go down the hierarchy can be fixed and set to e.g. 3
My current idea was to use t_object and join t_package multiple times and get the elements. But it doesn't work yet.
SELECT *
FROM ((((t_object AS o
INNER JOIN t_package AS p0 ON o.Package_ID = p0.Package_ID)
LEFT JOIN t_package AS p1 ON p1.Parent_ID = p0.Package_ID)
LEFT JOIN t_package AS p2 ON p2.Parent_ID = p1.Package_ID)
LEFT JOIN t_package AS p3 ON p3.Parent_ID = p2.Package_ID)
WHERE p0.Package_ID = <number>
Any ideas what's missing and how to realize this?
-
p0 ON o.Package_ID = p0.Package_IDMeans you only want elements that are directly under p0.
What you need is to end with t_object o and join that with any of the package ID's.
inner join t_object o on o.package_ID in (p0.package_ID, p1.package_ID, p2.package_ID, p3.Package_ID)
Geert
-
So in total, this would read:
SELECT *
FROM ((((t_object AS p0
LEFT JOIN t_package AS p1 ON p1.Parent_ID = p0.Package_ID)
LEFT JOIN t_package AS p2 ON p2.Parent_ID = p1.Package_ID)
LEFT JOIN t_package AS p3 ON p3.Parent_ID = p2.Package_ID)
INNER JOIN t_object AS o on o.Package_ID in (p0.Package_ID, p1.Package_ID, p2.Package_ID, p3.Package_ID))
WHERE p0.Package_ID = <number>
I get a syntax error on that one? I am not so sure if IN can be used within JOIN?
-
t_object AS p0 should be t_package as p0 I guess.
And you shouldn't use the "AS" keyword for table aliasses.
You can definitely use IN in a join. I'm not sure which database you are working with, but if the IN doesn't work, you can replace that with OR's.
Geert
-
What's wrong with recursive, I find it easier and safer.
You can get all package GUIDs then from there get all elements within that package
WITH RECURSIVE pkgbranch (eaguid, pkgid, pkgname, parentid, lvl) AS
(SELECT p.ea_guid, Package_ID, p.Name, p.Parent_ID, 1
FROM t_package p
WHERE p.ea_guid = '{xxxx}'
UNION ALL
SELECT p.ea_guid, p.Package_ID, p.Name, p.Parent_ID, pb.lvl+1
FROM t_package p
JOIN pkgbranch pb ON pb.pkgid = p.Parent_ID)
SELECT * FROM pkgbranch;
-
What's wrong with recursive, I find it easier and safer.
You can get all package GUIDs then from there get all elements within that package
WITH RECURSIVE pkgbranch (eaguid, pkgid, pkgname, parentid, lvl) AS
(SELECT p.ea_guid, Package_ID, p.Name, p.Parent_ID, 1
FROM t_package p
WHERE p.ea_guid = '{xxxx}'
UNION ALL
SELECT p.ea_guid, p.Package_ID, p.Name, p.Parent_ID, pb.lvl+1
FROM t_package p
JOIN pkgbranch pb ON pb.pkgid = p.Parent_ID)
SELECT * FROM pkgbranch;
You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select
Geert
-
t_object AS p0 should be t_package as p0 I guess.
Of course you're right.
You can definitely use IN in a join. I'm not sure which database you are working with, but if the IN doesn't work, you can replace that with OR's.
What is the goal of your last INNER JOIN, is the IN statement a shortcut to perform a JOIN 4x? Or JOIN once, and filter results?
Btw, INNER JOIN doesn't seem to work here ("expression not supported"). LEFT JOIN seems to bypass this.
-
You can definitely use IN in a join. I'm not sure which database you are working with, but if the IN doesn't work, you can replace that with OR's.
When I replace X with 0,1,2,3 it seems to work for each individual level. Any ideas how to achieve this in one query?
SELECT *
FROM ((((t_package p0
LEFT JOIN t_package p1 ON p1.Parent_ID = p0.Package_ID)
LEFT JOIN t_package p2 ON p2.Parent_ID = p1.Package_ID)
LEFT JOIN t_package p3 ON p3.Parent_ID = p2.Package_ID)
LEFT JOIN t_object o ON o.Package_ID = pX.Package_ID)
WHERE p0.Package_ID = <number>
Edit: 4x UNION seems to do the trick, but I think this is the least elegant solution.
-
LEFT JOIN t_object o ON (o.Package_ID = p0.Package_ID or o.package_ID = p1.Package_ID or...)
Geert
-
LEFT JOIN t_object o ON (o.Package_ID = p0.Package_ID or o.package_ID = p1.Package_ID or...)
I already tried that, this gives me a syntax error in EA14.
Thanks for your support!
-
SELECT
CLASSGUID = o.ea_guid
,CLASSTYPE = o.object_type
, o.*
FROM ((((t_package AS p0
LEFT JOIN t_package AS p1 ON p1.Parent_ID = p0.Package_ID)
LEFT JOIN t_package AS p2 ON p2.Parent_ID = p1.Package_ID)
LEFT JOIN t_package AS p3 ON p3.Parent_ID = p2.Package_ID)
INNER JOIN t_object AS o on o.Package_ID in (p0.Package_ID, p1.Package_ID, p2.Package_ID, p3.Package_ID))
WHERE 1=1
AND p0.Package_ID = <number>
-
SELECT
CLASSGUID = o.ea_guid
,CLASSTYPE = o.object_type
, o.*
FROM ((((t_package AS p0
LEFT JOIN t_package AS p1 ON p1.Parent_ID = p0.Package_ID)
LEFT JOIN t_package AS p2 ON p2.Parent_ID = p1.Package_ID)
LEFT JOIN t_package AS p3 ON p3.Parent_ID = p2.Package_ID)
INNER JOIN t_object AS o on o.Package_ID in (p0.Package_ID, p1.Package_ID, p2.Package_ID, p3.Package_ID))
WHERE 1=1
AND p0.Package_ID = <number>
Thanks. In EA14 this does not work, EA reports a redundant bracket "(".
-
a bit more simplified in brackets (is always the same result)
I would genuinely be surprised if they ever did updates on the SQL editor (I would be happy, but it's definitely not on the priority list of sparx devs)
SELECT
CLASSGUID = o.ea_guid
,CLASSTYPE = o.object_type
, o.*
FROM t_package AS p0
LEFT JOIN t_package AS p1 ON p1.Parent_ID = p0.Package_ID
LEFT JOIN t_package AS p2 ON p2.Parent_ID = p1.Package_ID
LEFT JOIN t_package AS p3 ON p3.Parent_ID = p2.Package_ID
INNER JOIN t_object AS o on o.Package_ID in (p0.Package_ID, p1.Package_ID, p2.Package_ID, p3.Package_ID)
WHERE 1=1
AND p0.Package_ID = <number>
-
In this old version you have to use brackets for multiple JOINs, to avoid confusion of the SQL engine.
-
I never realized the SQL engine used to be worse as the one we currently have to use.
-
The old version used .eap, which is a ms access database and thus requires MS Access SQL Syntax (which is horrible and requires a lot of parentheses)
Recently the default file format is .qea which is sqlite. This database uses a lot more standard SQL compared to Access.
Geert
-
Where, honestly, SQL has not really achieved a standard but different flavors in each machine. It's just that some have more in common than others. M$Access is clearly an outsider.
q.
-
You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select
But, (surprisingly?) you can use recursive queries in Repository.GetElementSet (checked in EA 16.1). However, there are limitations of such a queries, too (seems to be imposed by EA pre-processing of the SQL). E.g. you can't use square brackets in sql syntax (as, for example, in ARRAY expression in PostgreSQL).
-
You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select
You can cheat EA with internal query syntax, like below:
SELECT q.* FROM (
<any_query_not_starting_with_select_keyword>
) q;
It works on PostgreSQL repository, and I'm pretty sure, this syntax (or similar) is possible in many other SQL engines.
-
You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select
You can cheat EA with internal query syntax, like below:
SELECT q.* FROM (
<any_query_not_starting_with_select_keyword>
) q;
It works on PostgreSQL repository, and I'm pretty sure, this syntax (or similar) is possible in many other SQL engines.
I know I tried this a few years ago and couldn't get it to work for searches in SQL Server. I never really bothered anymore once the #Branch# macro became available.
Geert
-
You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select
You can cheat EA with internal query syntax, like below:
SELECT q.* FROM (
<any_query_not_starting_with_select_keyword>
) q;
It works on PostgreSQL repository, and I'm pretty sure, this syntax (or similar) is possible in many other SQL engines.
I know I tried this a few years ago and couldn't get it to work for searches in SQL Server. I never really bothered anymore once the #Branch# macro became available.
Geert
Tried it in EA 16.1 on local SQLite project: EA crashed... too bad...
-
You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select
Geert
I have not tried this yet, but couldn't a view be used with a "proper" database - e.g., SQL Server or Oracle - to wrap the any queries not starting with a select?
-
You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select
Geert
I have not tried this yet, but couldn't a view be used with a "proper" database - e.g., SQL Server or Oracle - to wrap the any queries not starting with a select?
The problem with a view, is that you are starting to change the "standard" structure of the database as provided by Sparx. So whenever you project transfer, you have to remember to drag your database customizations with you.
I generally try to avoid this whenever possible, and in this case there are other alternatives.
Geert