Recursive Query for Package Tree Contents

[Luc De Graef]

Hello,
I am using EA-8 with a SQL Server based repository.
I want to extract information related to elements in a package and in lower packages.
For this purpose I need to determine all packages in a specific tree.

A hard-coded query (going only 2 levels deep) is

Code: [Select]

select t_package.Name Package, t_package.Package_ID, t_package.Parent_ID
from  t_package where
  t_package.Package_ID in (select  t_package.Package_ID from  t_package where t_package.Name = '00-000 F Information')
or t_package.Parent_ID in (select  t_package.Package_ID from  t_package where t_package.Name = '00-000 F Information')
or t_package.Parent_ID in (select  t_package.Package_ID  from  t_package
 where t_package.Parent_ID in (select  t_package.Package_ID from  t_package where t_package.Name = '00-000 F Information')
)

But this is of course not scalable.

Can one use some kind  of Recursive Query syntax? Does anybody have an example?

Thanks

Luc

[Geert Bellekens]

Luc,

You can't use recursive queries in EA (I tried it when we upgraded from SQL Server 2000 to 2008)
But your query doesn't seem optimal.
I would do something like:

Code: [Select]

select * from t_object o
join t_package package1 on o.package_id = package1.package_id
left join t_package package2 on package2.package_id = package1.parent_id
left join t_package package3 on package3.package_id = package2.parent_id
left join t_package package4 on package4.package_id = package3.parent_id
left join t_package package5 on package5.package_id = package4.parent_id
left join t_package package6 on package6.package_id = package5.parent_id
left join t_package package7 on package7.package_id = package6.parent_id
left join t_package package8 on package8.package_id = package7.parent_id
where
package1.name = 'MyPackageName'
or package2.name = 'MyPackageName'
or package3.name = 'MyPackageName'
or package4.name = 'MyPackageName'
or package5.name = 'MyPackageName'
or package6.name = 'MyPackageName'
or package7.name = 'MyPackageName'
or package8.name = 'MyPackageName'This returns all objects in the package with the name "MyPackageName" and all its subpackages for eight levels down.
Add levels as you see apppropriate.

If you want a "real" recursive solution, you'll have to go with an add-in search.

Geert

[Eve]

If you're writing a custom sql search, try the #Branch# substitution.

[Geert Bellekens]

Simon,

%Branch% gives you the id's of the packages directly under the selected package (and not deeper), so I don't think that is really what Luc was looking for.

Geert

[Eve]

You may read that from the documentation, but the purpose of it was for a full recursive list of package ids.

Have you tried it?

[RoyC]

To remove that ambiguity, I have changed the description to:

- gets the IDs of the child packages of the currently-selected package, working recursively down to the lowest level of sub-package

in the Create Search Definitions topic.

[stevesavage]

For Oracle the search would be:
select  package_ID from t_package start with ea_guid = '{9F54BF3D-0F7A-4ec7-979C-7001A37FF9FC}' connect by parent_ID = prior Package_ID

Where the ea_guid is the GUID of the parent package.

For SQL you could try (untested):

WITH recursiveQ(Package_ID) AS (SELECT Package_ID FROM t_package AS parent WHERE ea_guid = '{9F54BF3D-0F7A-4ec7-979C-7001A37FF9FC}' UNION ALL SELECT child.Package_ID FROM recursiveQ AS parent, t_package AS child WHERE child.Parent_ID = parent.Package_ID) SELECT Package_ID FROM recursiveQ

Sorry I don't have an SQL server to test this with, the version is based on info found at: http://www.ibm.com/developerworks/data/library/techarticle/dm-0510rielau/

[stevesavage]

@Geert, don't know why the SQL version doesn't work. the Oracle version works perfectly.  Is it an EA problem or the default permissions on the server?

A possible work around would be to set a tagged value for all elements in the package to indicate the "root" package, and then only include elements with that tagged value.