search via sql

[marcelloh]

I like to see more examples on how to use this.

I tried some things, but I can't just keep on trying.
Is there a database structure somewhere that would help me?

I would like to search on objects of a specific type, with some specific tagged values. I did look in this forum, but didn't find much help.

[qwerty]

There's no "real" documentation but "real programmers" won't need that anyway. What you can do is to import the DB structure of EA by Code Engineering/Import DB. That way you have all tables and columns as a class model. It will help you to find the required data.

Of course you need to guess a lot. Your concrete question: t_objectproperties contains the tagged values along with the corresonding object id.

Good luck,

q.

[marcelloh]

Thanx for your answer,
I found that out allready, but i can't figure out how to map the tagged value of a requirement to the requirement  itself.

Guessing is not good... what if i guess wrong and I display some unpredictible data and assume I did good.
No... I must be 100% sure.
The manual is very unclear on this matter as well...

[Geert Bellekens]

Marcel,

This query returns requirements that have a certain tagged value:

Code: [Select]

select o.ea_guid as CLASSGUID, o.Object_Type as ClassType, o.Name as Name,tv.Property as Key,tv.Value as [Value],package.name as [Package Name]
from ((t_object  o
inner join t_objectproperties  tv on tv.Object_ID = o.Object_ID)
inner join t_package  package on o.package_id = package.package_id)
where tv.Property like '*<Search Term>*'
and o.Object_Type = 'Requirement'
Geert

[qwerty]

Take care of wild chars. They are depending on the database.

q.

[Geert Bellekens]

Q. is right, this query will work on MSAccess .eap files.
If used on a "real" database you should use the (SQL Standard) '%' wildcard.

Geert

[Aaron B]

If you are saving this as a SQL Search in EA, you can use the #WC# macro to automatically insert the correct wildcard character for the database type you are connected to. See:
http://www.sparxsystems.com/enterprise_architect_user_guide/8.0/navigate_search_and_trace/creating_filters.html#sql

[Geert Bellekens]

Thanks Aaron, I didn't know that.

This proves it, we can always learn something new  

Geert

[marcelloh]

So i did try Geert's option (Thanks/Bedankt!).
I see however no requirements, only other stuff.
The result-view (inside the model-search window) doesn't show me
the coloms for the query. Am I doing wrong?
Can a EA expert give some working examples on this matter?

The trick is, that I want  to find certain requirements with the same tagged value, So i have a kind of #d model of all my requirements, and I just can look at them the way I want to.
Basically , I just make a kind of vertical slice through all of them, and focussing on a certain keyword. Let's say "Legal". So I could easily find all requirements that have to do with "legal".

[marcelloh]

Code: [Select]

select o.ea_guid as CLASSGUID, o.Object_Type as ClassType, o.Name as Name,tv.Property as Key,tv.Value as [Value]
from (t_object  o
inner join t_objectproperties  tv on tv.Object_ID = o.Object_ID)
where o.Object_Type='Requirement'
Just a simple one... That give a good result in MsAccess, but a strange result in EA. (I see all kinds of notes, and only one requirement.)

I just found out, that this was because I pressed RUN (instead of another option to run the sql statement).

So this is one that works:

Code: [Select]

inner join t_objectproperties  tv on tv.Object_ID = o.Object_ID)
where o.Object_Type = 'Requirement'
and tv.Value like '*legal*'
(If I change the *-sign into the suggested #WC# then it doesn't work.)

So after testing it, I changed the hard coded value "legal" into <Search Term>. Saved the query and it works. Hopefully I saved some people some time.

[Geert Bellekens]

Marcel,

I tried the #WC# macro on one of my other searches, and it seems to work just fine. (maybe its a version thing? I'm on 8.0.856 now)
Here's the SQL code I tried:

Code: [Select]

select a.ea_guid as CLASSGUID,'Attribute' as CLASSTYPE,a.name as Name, a.Type as 'Type',  class.name as 'Class Name'
,package.name as 'Package Name' ,package_p1.name as 'Package level -1',package_p2.name as 'Package level -2',package_p3.name as 'Package level -3'
from (((((t_attribute  a
inner join t_object class on a.object_id = class.object_id)
inner join t_package package on class.package_id = package.package_id)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where a.Name like '#WC#<Search Term>#WC#'
Geert

[Geert Bellekens]

I just published a blog post about
Harvesting the power of EA’s SQL searches

Geert

[marcelloh]

I tried the #WC# and it works. (Must have been a typo).

I've just read your blog Geert. These are some nice initiatives.
If someone has other nice sql searches, then please publish :-)
So people with limitid sql knowledge could benefit.

Speaking of which:
t_propertytypes has all my default tagged values.
How can I find out, if there are requirements that are missing those.
(So i would like a view, where he would mention a tagged value, and the requirment that hasn't got that tagged value)

Has anyone done this?

[Geert Bellekens]

Try this one:

Code: [Select]

select o.ea_guid as CLASSGUID, o.Object_Type as ClassType, o.Name as Name,package.name as [Package Name]
from (t_object o
inner join t_package  package on o.package_id = package.package_id)
where
not exists (
select * from (t_object  o2
inner join t_objectproperties  tv on tv.Object_ID = o2.Object_ID )
where tv.Property like '#WC#<Search Term>#WC#'
and o.Object_Type = 'Requirement'
and o2.Object_ID = o.Object_ID )
and o.Object_type = 'Requirement'
Geert

[marcelloh]

Hello Geert... This one is also a beauty, but gives me
the requirement with the packagename.

I need something like this:
t_propertytypes.property  t_object.name
The first is the standard tagged value
the second is the requirement that is missing that tagged value

So I have in this overview all the Req's that are not complete :-)