SQL Search within model - to find packages hierarchy of Diagrams (+ its GUID)

[SystemsTinkerer]

Hi EA Users,

I have had valuable input from Geert on package level search but I am stuck on modifying his script to:

• Get search results beyond 3 levels of packages
• Get the details of diagrams in each of those packages such as diagram name & it's GUID#

from (t_package p
inner join t_object o on o.Package_ID = p.Package_ID)
where
p.Package_ID = #Package#
union
select p.name as PackageName ,p2.Name as PackageLevel2,null as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from ((t_package p
inner join t_package p2 on p2.Parent_ID = p.Package_ID)
inner join t_object o on o.Package_ID = p2.Package_ID)
where
p.Package_ID = #Package#
union
select p.name as PackageName ,p2.Name as PackageLevel2,p3.Name as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from (((t_package p
inner join t_package p2 on p2.Parent_ID = p.Package_ID)
inner join t_package p3 on p3.Parent_ID = p2.Package_ID)
inner join t_object o on o.Package_ID = p3.Package_ID)
where
p.Package_ID = #Package#
order by 1, 2, 3

Any help to get me going would be really appreciated AND/OR if I could get an idea of the tables related to my model, how they are accessed, viewed and parsed using SQL it will be an even bigger help.

Thank you for your consideration.

Best,
Oz

[Geert Bellekens]

You can join t_diagram on t_diagram.Package_ID = t_package.Package_ID
Adding more levels is simply a matter of adding another union with a extra join to t_package.

If you want to know more about the database structure you can simply reverse engineer it in EA itself.

More explanation in Thomas' book https://leanpub.com/InsideEA

Geert

[SystemsTinkerer]

Hi Geert,

Many thanks for your guidance & the book reference. I will this a go & consult that book.

Cheers,
Oz

[qwerty]

Should you find to not find something give me a note :-)

q.

[SystemsTinkerer]

Hello!

The package hierarchy search throws up some strange results along with the good stuff.  The block and elements are coming up as packages, instead  of only real package/folder types - see the following screenshot of my result:
https://ibb.co/f4WNfGV


Thanks,

[Geert Bellekens]

I guess you must have used o.name in one of your selects.

Hard to tell without seeing the actual query you are using.

Geert

[SystemsTinkerer]

Sorry for not being clear - I am using your example code Geert from the discussion:
https://sparxsystems.com/forums/smf/index.php/topic,46543.0.html

Here is the exact SQL code I am running on my EA:

Code: [Select]

select p.name as PackageName ,null as PackageLevel2,null as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from (t_package p
inner join t_object o on o.Package_ID = p.Package_ID)
where
p.Package_ID = #Package#
union
select p.name as PackageName ,p2.Name as PackageLevel2,null as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from ((t_package p
inner join t_package p2 on p2.Parent_ID = p.Package_ID)
inner join t_object o on o.Package_ID = p2.Package_ID)
where
p.Package_ID = #Package#
union
select p.name as PackageName ,p2.Name as PackageLevel2,p3.Name as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from (((t_package p
inner join t_package p2 on p2.Parent_ID = p.Package_ID)
inner join t_package p3 on p3.Parent_ID = p2.Package_ID)
inner join t_object o on o.Package_ID = p3.Package_ID)
where
p.Package_ID = #Package#
order by 1, 2, 3

[qwerty]

Actually I don't see anything duplictate in the picture.

q.

[Geert Bellekens]

This query is mainly looking at the elements, not only packages.

If you need only packages you should only look at t_package, not at t_object.

Geert

[SystemsTinkerer]

Thank you for the input gents! Geert, the object information & particularly the diagram (name & GUID) is of interest too, BUT I want the result gained from the search to clearly show:

• the package names & hierarchy
• Within each of those packages, to show the diagrams located there, and block elements.
• Finally, showing which diagram is using which block elements

Thanks again for your guidance. 

[qwerty]

You would need to join the package.name from the package connected via the object.package_id. Will probably make the query even more bloated.

You can't create a tree view.

q.

[SystemsTinkerer]

Hi q,

Thank you for your comments. I will play around & try out your suggestion. However, are you referring to my need to have a hierarchical package as a tree view, which is not doable? Why is it not doable using SQL? Would you suggest going down the route of JavaScript instead then?

Cheers,
Oz

[qwerty]

Well, SQL only returns a list and not a hierarchy. You could decompose it programmatically. But the search window from EA only shows a list. You would need to write an add-in ith your own window/content.

q.

[SystemsTinkerer]

Got it q! I will start getting acquainted with the add-in approach in parallel. Thank you for the chat & input.

Cheers!

[qwerty]

Don't miss Geert's tutorial about add-ins in 10 minutes (that's not a lie since I did it this way to). Likely the *real* stuff is more complicated then.

q.