Author Topic: WITH RECURSIVE support in Model Search / RTF templates  (Read 628 times)

potterm

  • EA User
  • **
  • Posts: 115
  • Karma: +0/-0
    • View Profile
WITH RECURSIVE support in Model Search / RTF templates
« on: June 13, 2022, 05:19:48 pm »
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

  • EA Guru
  • *****
  • Posts: 11935
  • Karma: +464/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: WITH RECURSIVE support in Model Search / RTF templates
« Reply #1 on: June 13, 2022, 06:27:03 pm »
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

  • EA User
  • **
  • Posts: 115
  • Karma: +0/-0
    • View Profile
Re: WITH RECURSIVE support in Model Search / RTF templates
« Reply #2 on: June 13, 2022, 06:38:06 pm »
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

  • EA Guru
  • *****
  • Posts: 11935
  • Karma: +464/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: WITH RECURSIVE support in Model Search / RTF templates
« Reply #3 on: June 13, 2022, 06:41:37 pm »
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

  • EA User
  • **
  • Posts: 115
  • Karma: +0/-0
    • View Profile
Re: WITH RECURSIVE support in Model Search / RTF templates
« Reply #4 on: June 13, 2022, 11:28:27 pm »
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

  • EA User
  • **
  • Posts: 409
  • Karma: +15/-7
    • View Profile
Re: WITH RECURSIVE support in Model Search / RTF templates
« Reply #5 on: June 14, 2022, 09:36:11 pm »
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
« Last Edit: June 14, 2022, 09:40:37 pm by Richard Freggi »

potterm

  • EA User
  • **
  • Posts: 115
  • Karma: +0/-0
    • View Profile
Re: WITH RECURSIVE support in Model Search / RTF templates
« Reply #6 on: June 15, 2022, 12:58:44 am »
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....
« Last Edit: June 15, 2022, 01:48:41 am by potterm »

wivel

  • EA User
  • **
  • Posts: 150
  • Karma: +11/-1
  • Driven by Models
    • View Profile
Re: WITH RECURSIVE support in Model Search / RTF templates
« Reply #7 on: June 15, 2022, 01:35:40 am »
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

  • EA User
  • **
  • Posts: 115
  • Karma: +0/-0
    • View Profile
Re: WITH RECURSIVE support in Model Search / RTF templates
« Reply #8 on: August 04, 2022, 06:32:14 pm »
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

  • EA User
  • **
  • Posts: 855
  • Karma: +23/-8
    • View Profile
Re: WITH RECURSIVE support in Model Search / RTF templates
« Reply #9 on: August 04, 2022, 08:03:11 pm »
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.