Harvesting the power of EA’s SQL searches
Enterprise Architect from Sparx Systems is the UML CASE tool I use day-to-day to get my analysis done.
One of the great features of EA is that it allows you to define your own searches using SQL. Because EA runs on pretty much any type of database (the standard for local models is MS Access) defining your searches in SQL can be very powerful.
On of the problems with working on a big (as in 50.000 elements) model is the ability to quickly find the elements you need.
Out of the box EA comes with some search options. You can search in the project browser, one by one, or you can search the model with one of the predefined searches.
Free download
Searching in the project browser is sometimes useful, but when you need a specific class or attribute, it can take quite a while to find it since EA selects each candidate one by one, and you have to click the “next” button to search further.
Using the predefined searches like “simple” or “extended” works to some extend, but they are slow as hell, only give you Elements as a result, and they will probably show too much noise.
But as mentioned, EA has provided us with an alternative. In my day-to-day modeling I never use of the search options provided by EA, but only my own defined SQL searches because they are much faster and much more precise then the predefined searches.
It also allows me to control exactly what information is shown in the result list:
You can find the full explanation of EA’s search feature in the manual which you conveniently can find online.
Once defined you can also export one or more searches into an xml file that your colleagues can import into their copy of EA.
I’ll just highlight some of the special tricks to make SQL searches work.
First of all the first two columns in your resultset always have to be the same.
- CLASSGUID should contain the ea_guid column of whatever you are searching for. This column is hidden from the results, but will be used to allow you to select the element in the browser.
- CLASSTYPE should contain the type of the element you are searching for. This column is used to show the correct icon for this type of element.
To be able to use the content of the search box you need to use the magic string “<Search Term>”. Each occurrence of the <Search Term> is replaced by EA with the content of the search box before the query is executed on the repository.
The usage of wild-cards is another issue. The ansi SQL standard for 0..n characters is %, but of course Microsoft thought it would be better to define their own “standard” for MS Access, and they decided to use * instead.
If you want your SQL searches to be able to run on both local .eap files and DBMS repositories you can use the macro #WC# in the query. Each occurrence of this macro will be replaced by the proper wildcard for the database you are working on.
You don’t have to use the #WC# in the query itself however. You can also type “firstpart%” or “firstpart*” into the searchbox if you prefer to have more control about the search results.
Here are some of my favorite searches
Find Classes or Interfaces by name:
select o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, o.name as 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_object o
inner join t_package package on o.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
o.Object_Type in ('Class','Interface')
and o.Name like '#WC#<Search Term>#WC#'
Find Elements by stereotype:
select o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, o.name as 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_object o left join t_xref stereo on stereo.Client = o.ea_guid) inner join t_package package on o.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 o.Stereotype like '#WC#<Search Term>#WC#' or stereo.description like '@STEREO;Name=#WC#<Search Term>#WC#;#WC#'
Find Attributes by Type:
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.Type like '#WC#<Search Term>#WC#'
Find attributes by name:
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#'
Find Operations by Parameter type:
select o.ea_guid as CLASSGUID,'Operation' as CLASSTYPE,o.name as Name,p.Name as 'Parameter Name',p.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_operation o inner join t_operationparams p on o.operationID = p.operationID) inner join t_object class on o.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 p.type like '#WC#<Search Term>#WC#'
Find Operations by name:
select o.ea_guid as CLASSGUID,'Operation' as CLASSTYPE,o.name as Name, 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_operation o inner join t_object class on o.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 o.name like '#WC#<Search Term>#WC#'
Thanks Geert
One of EA’s powerfull pre-defined searches is to find all orphans in you project. I’m trying to create a SQL eqivalent for this search. Would perhaps have any pointers as to in which tables I would find this information?
Francois,
My first question would be: why? What’s wrong with the predefined search?
If you really want to create something equivalent I would start looking at the table t_object. That table contains all “Elements” in ea.
Other useful tables to look at are t_connector -> all relations, t_attribute -> all attributes and t_method -> all operations.
I think the predefined search defines an “orphan” as an element that isn’t shown on a diagram.
To figure this out I would left join t_object with t_diagramObject and select those that don’t have a row in t_diagramObject.
If you are working on a DBMS repository you could union searches on different tables and still present them as one list. On an .eap file (MS-Access) the union results in an error.
Thanks Geert,
Figured it out, this is indeed a very powerfull feature EA has, can make basic admin tasks very simple (if you understand the database structure that is!)
As for the question why, I’m building a custom report with unions, case logic, etc.
Solution (for those interested):
SELECT
t_object.ea_guid AS guid,
t_object.name AS object,
t_object.Object_Type AS type,
t_object.Stereotype AS stereotype,
t_object.Status AS status,
t_object.CreatedDate AS created,
t_object.ModifiedDate AS modified,
t_object.Author AS author
FROM t_object
WHERE t_object.Object_Type ‘Package’
AND t_object.Object_ID NOT IN (
SELECT Object_ID
FROM t_diagramobjects
)
ORDER BY modified ASC
Thanks for this Geert. I use EA a lot but my sql skills are lacking. Sparx documentation isn’t so helpful in this respect so seeing real life examples is really helpful
Is there any possibility to use UPDATE query through our Add-In? I tried to use UPDATE query, but it doesn’t work. I know it’s dangerous, so I tried this command to Notes column which will not affect to another table.
Yes, as it happens I’ve just replied to the exact same question on the EA forum: http://www.sparxsystems.com/cgi-bin/yabb/YaBB.cgi?num=1349248753/14#14
Geert
Thanks for this valuable input, Geert!
Maybe you can help me with this: The results order does not at all seem to be the ‘natural order’ of the project tree (as teh manual promises…). But that would exactly be what I need. I need to CSV export all Feature and Requirement items in exactly the tree order (otherwise their sequence won’t make any sense). Any idea would be greatly apprciated!
Hermann
Hey Geert,
I am new to Ea but recently discovered the use of Link to Element Feature and would like to know whether how I am able to extract this out of SQL or any means other than looking at the diagram?
I see the feature incorporated within the EA navigator tool so would of thought it can be done in the backend.
Any information would be much apprecaited.
Many Thanks,
Garrick Kong
Hi Garrick,
In fact the EA Navigator also gets the info via queries to the database as this information is not available through the API.
For this type of information you best check the EA forum. There are a number of topics about this, but the most interesting for you is probably this one:
http://www.sparxsystems.com/cgi-bin/yabb/YaBB.cgi?num=1373484277/1#1
Geert
Hi Geert,
Good explaination.
The only problem I have with the SQL searches is that I can’t select the object and drag them from the result set on the diagram.
If we use a query builder search it is possible.
Do you know how a query search SQL should be constructed to have this behaviour?
Jurgen
Hi Jurgen,
I just tested it in version 11 with the first search on the blog post (Classes and Interfaces by Name), and I was able to drag an element from my SQL search results to a floating diagram.
I’m pretty sure it only works with elements (results from t_object)
Just make sure you include the CLASSGUID and CLASSTYPE as the first two columns in your results
IIRC it only works in the most recent versions; probably from version 10 somewhere.
Geert
Very useful blog, Geert. Thanks!
For those still on ver. 10 (I am at 10.0.1004), there are some peculiarities to making it possible to drag onto a diagram from a custom SQL query.
EA Help states that you can make it work by including one of two things in your SELECT statement:
– t_object.Object_ID, t_object.Object_Type
– t_object.ea_guid AS CLASSGUID
[a couple of typos in the second line in Help corrected above]
If you use the first technique (Object_ID, Object_Type), all works well (although your search outputs look a little peculiar). You can make it look a nicer by using
t_object.Object_Type as CLASSTYPE
… drag still works.
If you use the second technique (ea_guid), problems can arise. In a query with a WHERE clause that uses the , AND when you run it with a non-blank value for the search term, you cannot drag search results onto a diagram. For what reason I don’t know and haven’t the time or patience to figure out. Argh.
(Specifics: my query included this:
o.Name like ‘#WC##WC#’)
Correction to previous post: The keyword for search term in EA custom SQL queries got removed, I guess because of the angle brackets. I will use square brackets to correct …
In a query with a WHERE clause that uses the [Search Term]
o.Name like ‘#WC#[Search Term]#WC#’
Geert, I’m hoping you can help me find what probably is a simple error. I’m trying to rewrite the OV-3 query in Sparx to add in some tagged values. I can’t get it to work when I try to use a LEFT JOIN. I keep getting the error “Syntax error in JOIN operation.” I’ve pared it down to one JOIN right now. I’ve tried moving the parentheses around but that hasn’t made a difference yet. Otherwise, it looks right to me. Thank you for any guidance you can provide.
SELECT CONNECTOR.ea_guid AS CLASSGUID,
CONVEYED.Name AS Name,
PRODUCER.Name AS Sending_Organization,
CONSUMER.Name AS Receiving_Organization,
tag_classification.value AS Classification
FROM t_xref XCONVEYED,
t_xref XABSTRACTION,
t_object CONVEYED,
t_connector FLOW,
t_object PRODUCER,
t_object CONSUMER,
t_diagramlinks,
t_diagram,
(t_connector CONNECTOR
LEFT JOIN t_connectortag AS tag_classification
ON t_connector.Connector_ID = tag_classification.ElementID AND tag_classification.Property = ‘Classification’)
WHERE XCONVEYED.Behavior=’conveyed’
AND XCONVEYED.Description LIKE ‘#WC#’ + CONVEYED.ea_guid + ‘#WC#’
AND XCONVEYED.Client=FLOW.ea_guid
AND FLOW.Start_Object_ID=PRODUCER.Object_ID
AND FLOW.End_Object_ID=CONSUMER.Object_ID
AND XCONVEYED.Client=XABSTRACTION.Description
AND XABSTRACTION.Client=CONNECTOR.ea_guid
AND CONNECTOR.Connector_ID=t_diagramlinks.ConnectorID
AND t_diagramlinks.DiagramID=t_diagram.Diagram_ID
AND t_diagram.StyleEx LIKE ‘#WC#MDGDgm=UPDM2_OV#WC#’
Karen,
I’m guessing you are writing this query on a .eap model and thus you need to be using MS-Access SQL Syntax.
The first thing I noticed is the ‘AS’ in the left join statement : LEFT JOIN t_connectortag AS tag_classification
Although SQL-Server doesn’t have any problems with that MS-Access doesn’t like the AS when assigning an alias to a table, only when assigning aliases to resultset columns.
Another well difference is the fact that MS-Access doesn’t allow the shortcuts “left join” and “join”. It only accepts “left outer join” and “inner join”.
Another thing is that, written like this, it is very hard to understand. I would really suggest using joins for each table you join, it makes the whole thing a lot easier to read.
The sample searches provided are very helpful. But I feel like it’s a guessing game when trying to construct a new SQL search. Does anyone have knowledge of a database definition for Sparx EA? it would be so helpful !
Lisa,
There’s a book written by Thomas Kilian that explains the internal database structure: Inside Enterprise Architect
How can i retreive all “Information Items Convedyed” for selected “InformationFlow”?
Check http://www.sparxsystems.com/cgi-bin/yabb/YaBB.cgi?num=1375058207/3#3
thanks very much !
Hi Geert,
You mention that you can create your own SQL-searches and export / import them to share with your collegues. Do you know if it is possible to create a new search group (like the Common group) within the EA project model that is directly available for everyone (without exporting and importing the SQL search by each individual)?
With kind regards,
Niels Koreman
Hi Niels,
You can if you include the searches in an MDG technology file.
Use Tools|Generate MDG Technology file and select the searches you would like to be distributed.
then you can distribute the MDG file either though a file location, url, or import it into a model.
The users will then see a new category in the searches with the name of the MDG.
Geert
Hi Geert,
I tried to import the MDG technology file. At first I imported the MDG file using ‘Project’ > ‘MDG Technology import’. In the model search my SQL-search was available. Unfortunately when I closed EA and reopened EA with the model, the search had disappeared. I found out that via ‘Resources’ you can add an MDG into the model itself. If I choose ‘Extensions’ > ‘MDG Technologies’ I can see the added MDG as being active and being available from the model. My colleges can see the MDG too. Unfortunately I cannot see the SQL-search that is included in the MDG in the model search window. Do you have any idea?
With kind regards,
Niels Koreman
Hi Niels,
You encountered an annoying bug that got fixed in version 12.1.
I’m guessing you are still at version 12 or below?
I’m waiting for the upgrade to 12.1 at some of my clients to be able to distribute the SQL Searches through MDG.
Thanks a lot. I’m going to try 12.1!
Hi Geert,
What do you suggest as the best way to import your file, Geerts-favorite-sql-searches.xml file for EA 13.0?.
And thanks for this!
The same way as it the previous versions, open the model search, click on the down arrow on the icon on the right, and choose Import Searches.
See also http://sparxsystems.com/enterprise_architect_user_guide/13.0/model_navigation/search_view.html
Thank you!
Also would like to ask permission to turn this into a simple MDG for 12.1/13.0 distribution for my current client site, as part of future EA SQL class. I’m trying to stimulate a small group of users to look beyond the immediate needs of EA. (I’ll gladly give appropriate credits.)
Hi Patrick,
No problem at all. All of my code, including the code in the SQL Searches is open source and can be used by anyone for any purpose.
Hi Geert,
I’m new on this things for EA thru the database,,
i have a big question, at least for me.
We had defined an structure to create our diagrams for architecture layers…, some people is following so far so good the methodology and the elements, but others not as well i want.
i can not validate manually all the diagrams to make sure they are following this rules…
¿How can i validate all diagrams have the structure and methodology they should have using a sql query, instead of validate one per one in the schema browser in EA?