Sparx Systems Forum
Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: tagem0 on November 10, 2009, 11:08:32 pm
-
Hello,
I have been asked to write an sql that would show related elements of an element, similar to how the Hierarchy Window works in EA. So basically if I have the following relationships (e.g agregations of requirements)
A -> B, B -> C, C -> D, D -> E. Then if I am asked to show the related elements of C, then the SQL should give me A, B, D and E.
Has anyone got a script or may be any ideas on how to accomplish this. The goal is to use the data from the sql to produce an RTF document.
Thanks
Tagem
-
I guess you just forgot to include "C" in the desired results?
In theory you would need a recursive query to be sure you get the whole hierarchy. Unfortunately recursive queries are not (yet) available in some of the common databases EA works on.
In practice you could probably do with a regular query, given that you have an idea of the deepest level of your hierarchy.
You will basically have to have as many joins to the t_object (if that is where the requirements are stored) as you have levels.
Since you want to find those related items through a relation, you'll have to include the t_connector in the joins as well.
I don't have anything ready to use for you, but I hope this already helps a little.
Geert
-
Thanks Geert.
However I am struggling to do this recursive queries or Transitive Closure in Access as our EA is based on Access. I am an Oracle guy and in Oracle I would have a choice of Oracle SQL or stored procedure using a cursor to loop through.
I am currently learning C#. Is it possible to do this in C# and use it in EA and it so how?. The idea is to use it in search and eventually produce an RTF document of the search results.
Thanks for your anticipated help.
Tagem
.
-
Tagem,
Yes, doing it in C# is an option. Search for addin-search in the help files.
Just be careful what you want to search using C# because it will be a lot (as in orders of magnitude) slower then a well written sql query.
Geert
PS. You know EA can be set up to run on top of Oracle?
-
Hi Geert,
Thanks for your help so far.
I decided to go with your recommendation to do this via sql. After some tedious effort I came up with an sql query developed in Access 2007 that produces the information that I am after.
However when I took this over to EA to create a Custom SQL search, it came up with error “DAO. QueryDef[3131] Syntax error in FROM clause. The EA editor does not seem to like Inline views.
An example of what I am trying to do is below.
Select t_object.name ............
from t_object, (
Select end_object_id
From t_connector
where start_object_id = 7419
) temp INNER JOIN t_object t ON temp.end_object_id = t.Object_ID
It works fine in Access. My version of Ea is 7.5.848 and the Database version is 4.01.
Do you have any ideas on how I could go round this?
Thanks
-
I have now set EA to Use JET 4.0.
My sql now works
Thanks
-
Hmm yeah, that was what I was going to suggest :)
Geert
-
Hi,
Sorry to bring up and old topic again but this is exacly what I am trying to do now.
I'm looking for an SQL query to find the transitive closure of an element - that is, all other elements a certain element relates to. In this case, the direction of an association does not matter, eg. A -> B -> C or A <- B <- C still would yield the same result, A is related to itself, B and C.
The query posted here does almost that, with the exception that when the element in question does not contain an association in which it is the source, nothing is returned.
I am trying to get a query that works in SQL because doing that using the EA repository object model is pretty slow - it's easy to write a method to do that, but the performance was not the best.
A last comment: can the query be written using only the t_connector table, or one of its views? Since the query posted here will return also the element's folders, diagrams and etc, which in my case do not matter.
Thanks!
-
You'll probably want something like
select * from t_connector c
join t_object source_object on c.Start_Object_ID = source_object.Object_ID
join t_object target_object on c.End_Object_ID = target_object.Object_ID
where c.Connector_Type = 'Association'
and (source_object.Name = '<Search Term>' OR target_object.Name = '<Search Term>')
geert
-
Thanks for the help! The query works fine, except that it is not transitive - in the case of having A -> B -> C, and looking for what's connected to A, I'd get A and B, C is missing. Any thought on how I can achieve that?
-
Add another left join with t_connector and then left joins with t_object, so something like this:
select * from t_connector c
join t_object source_object on c.Start_Object_ID = source_object.Object_ID
join t_object target_object on c.End_Object_ID = target_object.Object_ID
left join t_connector c2 on
(
(c2.End_Object_ID = source_object.Object_ID and source_object.Name <> '<Search Term>'
or
c2.Start_Object_ID = source_object.Object_ID and source_object.Name <> '<Search Term>'
)
or
(c2.End_Object_ID = target_object.Object_ID and target_object.Name <> '<Search Term>'
or
c2.Start_Object_ID = target_object.Object_ID and target_object.Name <> '<Search Term>'
)
)
left join t_object source_object2 on c2.Start_Object_ID = source_object2.Object_ID
left join t_object target_object2 on c2.End_Object_ID = target_object2.Object_ID
where c.Connector_Type = 'Association'
and (source_object.Name = '<Search Term>' OR target_object.Name = '<Search Term>')
-
Thanks again Geert!
There's some error in the sintax but I'll try to figure it out...
-
Yes,
My example is MS SQL Server syntax.
If you use .eap files you'll have to use MS Access syntax.
Geert
-
I figured I need to change the "JOIN" to "INNER JOIN", but still get an error in the FROM clause. Everything seem to be ok, the parenthesis match, so as for right now I'm still stuck...
-
I'm getting "join expression not supported" when i try it on an eap file.
Apparently left joins are not supported :(
Geert
-
I am trying here as well and still couldn't figure it out. I think the left ones are not a problem, they worked for me. Anyone else out there has any clues?
-
Normally it is mainly a question of adding enough brackets, but I can't seem to get this one working.
Geert
-
I appreciate your help a lot, Geert. It's the same for me here. I run out of ideas on what to do.
I payed attention for possible problems with the brackets but they seem fine. MS Access does accept left joins. I really am stuck now.
Of course, having EA run on top of MySQL is an option, but I don't want to assume all of the people using the add-in I am working on will have their EA set up like that. I'll keep on trying. I still wonder if the search I was looking for could be done with t_connector only, just for making it more compact.
Again, thank you very much! Hope we'll hear from someone who managed to get this right.
-
you can try out our SQLRepository in modus number 1 (no full database checkout)
http://www.sparxsystems.com/cgi-bin/yabb/YaBB.cgi?num=1322259105
I am trying to get a query that works in SQL because doing that using the EA repository object model is pretty slow - it's easy to write a method to do that, but the performance was not the best.
the code will be the same. you only have to use the created SQLRepository for you search.
for a specific database search of ours the duration decreased from 0.91sec to 0.22sec. (depends on you code).
-
Hi,
I've you are not planning to use the sql query in an sql search then there's more possible of course.
I guess you then only need to the object id's, and you don't need to join with t_object at all.
Geert