Sparx Systems Forum
Enterprise Architect => Suggestions and Requests => Topic started by: PeterHeintz on June 27, 2016, 10:47:37 pm
-
Searching recursively children of a parent is not really simple in SQL. EA already provides the #Branch# macro which simplifies to find all children of a selected package.
However I have several use cases where this feature would be very useful if I could define the package without selecting it.
Example: a Kanban related to a higher level package could be updated by sql selecting elements under the higher level package.
Due to the fact that the feature is in principle already there, the implementation seems to me to be easy.
-
+1
Paolo
-
Hi,
If I understand you right:
Select a Diagram, Element,.. and use #Branch# in SQL than you get the comma seperated list of Package IDs which you can use in SQL.
If you have this in mind you can get help by using hoTools and the SQL features. It provides this.
See:
http://community.sparxsystems.com/community-resources/977-hotools-sql-with-tabbed-editing-macros-templates (http://community.sparxsystems.com/community-resources/977-hotools-sql-with-tabbed-editing-macros-templates) or
https://github.com/Helmut-Ortmann/EnterpriseArchitect_hoTools/wiki/hoTools (https://github.com/Helmut-Ortmann/EnterpriseArchitect_hoTools/wiki/hoTools)
I you have something else in mind please explain it.
Regards,
Helmut
-
No, he wants it explicitly, using a parameter rather than setting a manual focus.
q.
-
Yes, I confirm I want an explicit parameter rather than selection something in the browser.
This would allow filtering things under a package without selection it.
-
Yes, I confirm I want an explicit parameter rather than selection something in the browser.
This would allow filtering things under a package without selection it.
This is important for us because we have a standard repository structure where the same package in various snapshots and sub-set repositories has the same GUID. These are hard-wired into our SQL queries and it would be good to be able to do the same in our searches.
Paolo
-
Hi,
thanks, I got the picture.
If you need such a feature in hoTools as a SQL macro just drop me a line. It's no big deal and it's a simple extension of the existing #Branch# macro / concept.
Regards,
Helmut
-
Helmut,
where I get this hoTools from?
Is it open source?
-
ho = Helmut Ortmann :-) It's his origin and he made it open source on github.
q.
-
Hi PeterHeintz,
where pkg.package_ID in (#Branch={0E702249-BFFA-43cd-86C9-41D3324A7617}#) is now part of hoTools.
There is also a Context Menu to insert this with the current selected package.
You find it at (2.03):
https://github.com/Helmut-Ortmann/EnterpriseArchitect_hoTools/releases (https://github.com/Helmut-Ortmann/EnterpriseArchitect_hoTools/releases)
Possible Macros / WiKi:
https://github.com/Helmut-Ortmann/EnterpriseArchitect_hoTools/wiki/EaSqlMacros (https://github.com/Helmut-Ortmann/EnterpriseArchitect_hoTools/wiki/EaSqlMacros)
Regards,
Helmut
-
Hello Helmut,
that is super!
I will try it today.
Thank you!
-
Hi all, cool thing! I didn't know about the macros yet.
However, the simple query SELECT * FROM t_object WHERE Package_ID IN (#Branch={D2B412CB-E1C6-4d59-A6B2-ECF22F7C8418}#)
fails for me with the following DAO error
DAO.Database [3075]
Syntax error in date in query expression 'Package_ID IN (#Branch={D2B412CB-E1C6-4d59-A6B2-ECF22F7C8418}#)'.
Context:
SQL: SELECT * FROM t_object WHERE Package_ID IN (#Branch={D2B412CB-E1C6-4d59-A6B2-ECF22F7C8418}#)
The package exists for sure with this GUID and the macro is exactly how Helmut described it.
Any ideas?
-
Did you install the hoTools on you EA installation?
-
Yeah, I have it installed, but I thought the macros are processed by EA as well? It seems to me as EA simply passes the query as is to the database and doesn't resolve the macro, because in Access ## indicates date literals.
-
Hi,
you have to install hoTools and you have to run the SQL from the hoTools Addin in the AddinWindow, Tab 'SQL'.
The Macros are converted before it is sent to EA and then by EA to the Database / Repository. As benefit: If you get SQL errors hoTools automatically reads the EA error file and outputs it together with the error message.
To install it you have to have the rights to install a Windows dll (local administrator).
Kind regards,
Helmut
-
Thanks for the enlightenment, Helmut! I thought this is a hidden EA feature that I can use in my add-ins as well :-)
-
Hi McMannus,
hoTools is a Windows COM Object. If you need the feature to run advanced SQL from another Addin or even Script (vbScript, JavaScript, JScript) you should be able to use the functionality.
The interface would be:
- Repository
- SQL String
- Search Term (can handle wild cards)
There could be two features:
If you are interested let me know.
On GitHub you can get the newest release and the current documentation. I don't always update the EA Community.
Regards,
Helmut
-
Helmut, you could save yourself quite some work by just linking the community site to your github page :-)
q.
-
Thanks to Helmut’s hoTools the functionality is available, however I think to have the feature build in EA would be good e.g. build “self-maintained” Kanbans having elements underneath a defined package. A more general implementation would be to have the possibility to call script methods from SQL returning a SQL fragment as string.