Model Search: define package scope

[Rouven]

Hello everyone,

let me divide the posting into two sections, one describing the problem, the other showing my initial approach.

Problem:
I have an EAP file with different root models inside. For one of these models I want to return all the contained Issue/Change elements so I can look at them and transfer them to e.g. Excel for further processing.

Solution Approach:
I know that the Documentation features can start their recursive search in a selected package. Now I was looking into the possibilities of the Model Search.
Seems that the query builder can't help me at all so I turned to the SQL-based queries. Since I wasn't online I made the mistake of looking into the HTML help file delivered with EA. There I find several query "macro" expression, such as "%WC%" for the current database wildcard, but also "%Branch%" for all packages in the currently selected branch or "%Package%" for the currently selected package. Trying them out lead to a dead end, syntax error.
Looking into the online documentation http://www.sparxsystems.com/uml_tool_guide/modeling_tool_features/creating_filters.htm I can no longer find these elements...

Question: What is the best way to achieve this recursive search? The changes/issues do not have a separate stereotype or any other means of identification other than the package they are located in...

Thanks in advance,
Cheers,
Rouven

[qwerty]

Try

Code: [Select]

WHERE Package_ID IN (#Branch#) in your query (builder). This will match all packages in the currently selected package. You can find a sample query in my e-book (see link below).

q.

[Geert Bellekens]

AFAIK that still isn't recursive.
In theory I think you should be able to use a recursive query, but last time I tried it with SQL-server I got syntax errors.
So I just joined enough t_package enough times to cover the maximum nested dept.

This is a recursive query I used recently to find all classes without attributes in a certain package tree:

Code: [Select]

with packages  (Package_ID, Name)as (
select p.Package_ID, p.Name from t_package p
where p.Package_ID = 2104662583
union all
select p.Package_ID,p.Name from t_package p
join packages on p.Parent_ID = packages.Package_ID
)
select o.Object_ID, o.Name, o.ea_guid,p.Name as PackageName from t_object o
join packages p on o.Package_ID = p.Package_ID
left join t_attribute at on at.Object_ID = o.Object_ID
where
o.Object_Type = 'Class'
and at.ID is nullthis is the code I had to use (for something similar) because the recursi ve sql query above wouldn't play nice with EA

Code: [Select]

string sqlQuery =
                " SELECT o.Object_ID as ID,o.Object_Type as Type,isNull(o.Name,'') as Name,ISNULL(t.value,0) as ImportanceLevel,isnull(o.Author,'') as Author,o.CreatedDate as Created,o.ModifiedDate as Modified,isnull(o.Note,'') as Documentation,isnull(o.Package_ID,'') as ParentID,isnull(p.name,'') as ParentName  " +
                " ,isnull(p9.Name +'.','') + isnull(p8.Name+'.','')+ isnull(p7.Name+'.','')+ isnull(p6.Name+'.','') " +
                " + isnull(p5.Name+'.','')+ isnull(p4.Name+'.','')+ isnull(p3.Name+'.','')+ isnull(p2.Name+'.','')+ isnull(p.Name,'') as QualifiedName " +
                " FROM ((((((((((t_object as o  " +
                " left join t_objectproperties t on t.Object_ID = o.Object_ID )" +
                " left join t_package p on o.Package_ID = p.Package_ID )" +
                " left join t_package p2 on p.Parent_ID = p2.Package_ID )" +
                " left join t_package p3 on p2.Parent_ID = p3.Package_ID )" +
                " left join t_package p4 on p3.Parent_ID = p4.Package_ID )" +
                " left join t_package p5 on p4.Parent_ID = p5.Package_ID )" +
                " left join t_package p6 on p5.Parent_ID = p6.Package_ID )" +
                " left join t_package p7 on p6.Parent_ID = p7.Package_ID )" +
                " left join t_package p8 on p7.Parent_ID = p8.Package_ID )" +
                " left join t_package p9 on p8.Parent_ID = p9.Package_ID )" +

                " where (t.Property = 'ImportanceLevel' or t.Property is null)" +
                " and o.ModifiedDate > '" + sqlDateModified + "' " +
                " and (p.package_ID = " + this.getPackageID() + "  " +
                    " or p2.package_ID = " + this.getPackageID() + "  " +
                    " or p3.package_ID = " + this.getPackageID() + "  " +
                    " or p4.package_ID = " + this.getPackageID() + "  " +
                    " or p5.package_ID = " + this.getPackageID() + "  " +
                    " or p6.package_ID = " + this.getPackageID() + "  " +
                    " or p7.package_ID = " + this.getPackageID() + "  " +
                    " or p8.package_ID = " + this.getPackageID() + "  " +
                    " or p9.package_ID = " + this.getPackageID() + " ) " +
                " order by QualifiedName";
Geert

[Rouven]

Hi everyone,

thanks for your answers:

@Geert: that query could be the way out, just assuming that I don't have too many levels to consider, but I should be able to narrow that down sufficiently...

@qwerty: that was what I tried, but my EA 9.2 wont accept the query:

Code: [Select]

SELECT Object_Type, Package_ID, Name, Status, Stereotype, Note, ea_guid FROM t_object WHERE object_type IN ('Issue', 'Change')  AND Status <> 'Closed'  AND Package_ID IN (#Branch#) ORDER BY Package_ID, Name, object_type returns DAO.Database [3075] Syntax error in date in query expression 'object type IN ('Issue', 'Change') And Status <> 'Closed' And Package_ID IN (#Branch#)'.
=> So apparently the macro "#Branch#" is not being evaluated so the Jet-engine is confronted with the "#" which leads it to believe I am talking about dates.
The following query works fine:

Code: [Select]

SELECT Object_Type, Package_ID, Name, Status, Stereotype, Note, ea_guid FROM t_object WHERE object_type IN ('Issue', 'Change')  AND Status <> 'Closed'  ORDER BY Package_ID, Name, object_type
Any ideas?

Cheers,
Rouven

[Geert Bellekens]

I just tested your query and it works on my machine :-/
(EA 9.3.931 on SQL-Server 2008 repository)

Also, apparently the #Branch# macro is now fully recursive (which it wasn't the first time I tested it)

Geert

[Rouven]

Damn, so it appears that the standalone MS-Access database in the background is the problem...

[Geert Bellekens]

No, I don't think so. I just tested the exact same sql search on a .eap file and it worked fine there as well.

Geert

[qwerty]

Me to. Works as designed...

You need to be focused on the top level package when you run the search (you did create that in the search builder?).

q.

[Rouven]

Hmh, this is very weird. Which JET Engine are you using, explicitly 4.0 or the default?

My EAP File has the following structure
Model A
+- Package A
    +- Package B
Model B
Model C
Model D

If I select 'Model A', 'Package A', or 'Package B' in the Project Browser (is this wrong?) and the run the query from the query builder I get the aforementioned error.
I may also note that I did not provide any search criteria.

My EA is:
- 9.2.921 Corporate Edition
- Database Version 4.01

Other Macros like the wildcard character seem to work fine but neither Branch nor Package work.

[qwerty]

Hm. I use 9.3 (like Geert). Maybe the #Branch# was added after 9.2?

q.

[edit] in 8.0 the #branch# did not work (I have only 9.3). Maybe you try to upgrade?

[Alfred81]

Hello,

A bit old thread but did you find a solution to the problems related to #branch#? I am running on version 10.0.1009 so the version wouldn't be the cause

The same problems goes for #Package#

BR Alfred

[qwerty]

Which problem are you actually talking of

q.

[Alfred81]

Hi,
sorry for the vagueness.

I am not able to use #Package# and #Branch# macros in my SQL searches.
I have tried a couple of queries both from your book and from this thread and I always get the DAO=.3075 exception.

The simplest query i tried is:
select * from t_object where package_id = #Package#

Have I missunderstood the syntax completly?

In one of the previous posts you are considering if the reason could be an old version but since I am using a later version it should not have to do with it?

Alfred

[qwerty]

I had no problem running that search. Of course (to emphasize that!!) you need to create a search with the search builder

and not having run that with the (native)  SQL tab.


q.

[Alfred81]

Of course...  8-)

Works like a charm!

Thank you very much!

Alfred