WITH RECURSIVE support in Model Search / RTF templates

[potterm]

I want to construct a query that returns all of the parent package names from a given leaf package in the model.  My model is running on MariaDB, so the easiest way to do this is to use the WITH RECURSIVE syntax.  The following SQL should work (it does when I test it in an SQL client against my model database), but it doesn't in EA from Model Search:

Code: [Select]

WITH RECURSIVE ancestors AS (
SELECT * FROM t_package
WHERE t_package.ea_guid = "<leaf package guid>"
UNION ALL
SELECT p.*
FROM t_package AS p, ancestors AS a
WHERE p.Package_ID = a.Parent_ID
)
SELECT name FROM ancestors;
I know the syntax is correct because I can see the results I expect returned from within my SQL client, just not in EA (which returns nothing).  Is there anything I can do to make this work in EA?  I know EA is a bit sensitive to SQL syntax in places (for example "as" in lower case seems not to work in some situations unless it's written as "AS").

Thanks,
Martin

[Geert Bellekens]

You can't do any SQL queries in EA that don't start with "Select"

You'll have to figure out a different way. There might be a way to use this recursive query by wrapping it in a subquery, but I'm not sure. I know I gave up at one time, but that was on SQL Server.

The easiest in this case is probably to join t_package enough times.
Enough would be the current maximum package depth +2 or something?

Geert

[potterm]

Thanks Geert.  I suspected that would probably be the answer, but worth asking nonetheless.  Thanks for your suggestion of using join - I was contemplating a bunch of UNIONS but join should result in a much less verbose statement.

Martin

[Geert Bellekens]

Thanks Geert.  I suspected that would probably be the answer, but worth asking nonetheless.  Thanks for your suggestion of using join - I was contemplating a bunch of UNIONS but join should result in a much less verbose statement.

Martin

Depends on whether you want a separate row for each parent package or not I guess.

Geert

[potterm]

Hi Geert,

No I want to report on the full path from a leaf package, so JOINS work quite well.  The only complication is dealing with null returns when you reach the top of the tree (as you suggest I've coded in enough for future expansion).  A shame WITH RECURSIVE isn't supported as that would make it completely future proof, but I've got something working at least.

Thanks again.
Martin

[Richard Freggi]

You should be able to access the EA QEA file with an SQLite frontend (DBeaver, SQLite studio... even the CLI) and run common table expressions which should support recursive queries

[potterm]

You should be able to access the EA QEA file with an SQLite frontend (DBeaver, SQLite studio... even the CLI) and run common table expressions which should support recursive queries

Thanks for the suggestion Richard.  My database (MariaDB) supports recursive queries, its just that they don't work when used in the EA client.  The ultimate objective is to include this information in a Report Template through a SQL template fragment, so at the moment JOINS look to be the only option....

[wivel]

Hi

EA does not supports recursive SQL queries, as far as I remember. One of the reasons Sparx have introduced SQL Macros as #Branch#.
https://sparxsystems.com/enterprise_architect_user_guide/16.0/the_application_desktop/creating_filters.html

Henrik

[potterm]

You can't do any SQL queries in EA that don't start with "Select"

You'll have to figure out a different way. There might be a way to use this recursive query by wrapping it in a subquery, but I'm not sure. I know I gave up at one time, but that was on SQL Server.

The easiest in this case is probably to join t_package enough times.
Enough would be the current maximum package depth +2 or something?

Geert

Just to follow up on this, I solved my immediate problem with a bunch of left joins in the end (ugly but it worked).  However I've had reason to revisit this problem as I wanted a query that only returned rows if the element I was selecting was inside a specific sub-tree in the package hierarchy.  You were spot on Geert - putting WITH RECURSIVE in a sub-select worked in this particular case...

[Modesto Vega]

I am not proficient on MariaDB. In the past with other RDBMS - e.g., SQL Server - we have used a workaround for problems such as this: we have created a view in the database and use a select statement to query the view from the templates.

[RobTScot]

Hi

I found this thread trying to solve the same issue - using recursive SQL in sparx.

I appreciate this is a bit late to the table (no pun intended) but I solved this dilemma by creating a table function and then using it in Sparx. Here is an example function that takes a parameter

1. returns all objects that contain the characters.
2. Returns the package name hierarchy and the depth of each object.

You can right-click any object returned and locate in diagrams/browser etc.

Code: [Select]

--drop function dbo.ufn_find_name
create function dbo.ufn_find_name (@pName nvarchar(255))
returns @retSPack table
(
CLASSGUID nvarchar(40),
object_type nvarchar(255),
Name nvarchar(255) not null,
Stereotype nvarchar(255) null,
PackageName varchar(8000) not null,
level int not null
)
-- Returns the set of objects that match the contain @pName in the object name
-- and the package hierarchy they reside in
as
begin
with package_levels (ea_guid,object_type,Name,parent_id,Stereotype,PackageName,level)
as(
select c.ea_guid,c.object_type,c.name as Name, package.parent_id, c.stereotype as Stereotype,cast(package.name as varchar(8000)) as PackageName,0
from t_object c inner join t_package as package on c.package_id = package.package_id
where (c.NAME like '%' + @pName + '%')
union all
select c.ea_guid,c.object_type,c.name as Name, package.parent_id, Stereotype,cast(concat(package.name,' / ',c.PackageName ) as varchar(8000)) as PackageName, level + 1
from package_levels c inner join t_package as package on c.parent_id = package.package_id
)
insert @retSPack
select distinct ea_guid,object_type,Name,Stereotype,PackageName,level
from package_levels pl
where pl.parent_id=0
return
end;

To use the function in sparx:

Code: [Select]

select *
from dbo.ufn_find_name('employer')
where packagename not like 'sand%'
/*and stereotype like '%role%'*/
order by name, packagename

HTH
Rob