Table of Contents
- Copyright and Disclaimer
- 1. Accessing the Database
- 2. Most Important Tables
- 3. Element Feature Tables
- 4. Tagged Value Tables
- 5. Security Related Tables
- 6. Rarely Used Tables
- 7. Marvelous References
- 8. API Cross References
- 9. Bits and Pieces
- 10. User Settings
- 11. GUI References
- 12. Query Caveats
- 13. SQL Search Builder
- 14. Further Reading
Enterprise Architect1 (EA) offers a wealth of API functions to support automated manipulation of UML models. However, quite a number of tasks require actions not directly supported by the API. Here the fact comes handy that EA is based on a database model which has proven to be very stable with respect to its structure. The last major change was introduced with audit functionality which added a couple of new tables but left the structure of the existing tables untouched. So you can assume that your add-ins will run in future versions of EA if you follow a few rules.
The contents of this book is the essence of a continuous work with EA since end 2003. It surely lacks prose but likely you won’t need that anyway. I’d call it a hacker’s guide into EA2.
Special thanks to Peter Doomen who inspired me to write this book. You likely might be interested in his book Fifty Enterprise Architect Tricks. Also I like to thank Helmut Ortmann for supplying me with most of the query examples and a couple of hints which had passed my attention3. Probably I should mention a couple of other guys4 but I’m not going to bother you with my family history.
This book starts with a short introduction on how to query EA’s database. This is followed by a concise list of all available tables and details for the most important ones. The details contain cross references into more details as well as screen shots of the GUI where the appropriate elements appear. Vice versa the screen shots point to the according table columns. The final sections conclude with a practical approach to using SQL in Enterprise Architect.
Also all of the information in this book has been tested by me in many circumstances I can not hold any liability for use of the here presented information5. However, I’d be glad to receive any kind of feedback to correct future updates of this book which you will receive for free in turn. Having said this, all information presented here is subject to change without notice.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
Important note: this book is about querying EA’s database. You might think that updating the database is easy with an UPDATE statement. Sure. But that’s playing with a loaded and unsecured weapon! You might shoot yourself in your knee or even in the middle of your heart. If you are going to change your repository: use the API.
The lowest layer in EA is that of its database. When you first start with EA you will most likely deal with EAP files. A simple though not official fact is: EAP is MS Access. So if you want to play around just open one of those EAP files and see what MS Access is telling you. If you are using a Corporate license you will most likely use a more advanced SQL server. Be it MS SQL Server, Oracle, MySQL or whatever. In that case you need some client software to perform manipulations.
Before doing so you should get familiar with the database in a more simple way.
The most simple way is to open the respective EA repository with EA itself.
Use the EAExample.EAP which comes with your EA installation. Now
Ctrl-F to open the search window. Click the Builder button and
select the SQL tab. Type the text
and press Ctrl-Blank. You should now be presented with the following:
As you can see this is a list of tables which reside in EA’s
database. For a start let’s choose one of the important tables:
t_object. After pressing the Run button or the little triangle top
left you will get a list of all elements present in the repository.
Obviously the column
Object_Type is the type of an element and Name its
name. Simple! You might go on with
t_package to see details of all
packages in the repository.
The clean way to query the tables is the API. This is recommended for most cases. However, there’s also a demand to be able to access these data where the API is simply too slow. EA is kind of object oriented in how it queries its database. That means for a global change (like changing the status) it will issue single UPDATEs instead of a compound. Of course you can query the database much faster with an intelligent query than any iterative API calls.
Anticipating a simple structure element of the table containing the elements (
the best way to retrieve an element is by
which yields the ‘same’ element as
where just the API object data are cooked while those of the SQL are raw. So that’s no wizardry. But imagine you need all elements with a certain stereotype. While in the API this would need quite some programming effort, the SQL is simple:
Now it’s upon your imagination what you can do by joining different tables in SQL. A few sample can be found in this chapter at the end of this book.
- This window accepts only SELECT statements. Any other SQL (like e.g. UPDATE) is silently ignored!
|t_attribute||Attributes defined for elements|
|t_attributeconstraints||Constraints for attributes|
|t_attributetag||Tagged values for attributes|
|t_authors||List of authors defined with
|t_cardinality||List of cardinalities defined with
|t_clients||List of authors defined with
|t_connector||Connectors between elements|
|t_connectorconstraint||Constraints for connectors|
|t_connectortag||Tagged values for connectors|
|t_connectortypes||This table is used for the connector metatypes shown in the profile dialog. Also t_connector.Connector_Type can contain just elements from this table.|
|t_constants||Various key/value pairs in misc. dialogues|
|t_constrainttypes||List of constraint types defined with
|t_diagramlinks||Non-standrad links appearing in diagrams|
|t_document||Contents of linked documents, baselines and more|
|t_ecf||List of complexity factors defined with
|t_efforttypes||List of effort factors defined with
|t_glossary||The system glossary|
|t_html||Some HTLM strings for the doc generation|
|t_image||The alternate pictures defined with
|t_issues||The system issues defined with
|t_lists||Status Types defined with various
|t_metrictypes||List of metric factors defined with
|t_object||Basic UML elements|
|t_objectconstraint||Constraints for elements|
|t_objecteffort||Something related to project estimation|
|t_objectfiles||The files linked in the properties/files for elements|
|t_objectmetrics||Something related to project estimation|
|t_objectrequires||The internal requirements defined for elements|
|t_objectresource||Something related to project estimation|
|t_objectrisks||Something related to project estimation|
|t_objectscenarios||The use case scenarios|
|t_objecttests||Tests defined for elements|
|t_objecttypes||EA internal rendering support|
|t_ocf||Something related to project estimation|
|t_operation||Operations for elements|
|t_operationparams||Parameters for operations|
|t_operationposts||Postconditions for operations|
|t_operationpres||Preconditions for operations|
|t_operationtag||Tagged values for operations|
|t_primitives||Code primitive types|
|t_problemtypes||Problem types defined with
|t_propertytypes||Predefined tagged values|
|t_requiretypes||List of requirement types defined with
|t_resources||List of resource defined with
|t_risktypes||List of risk factors defined with
|t_rtf||EA internal doc generation settings|
|t_rtfreport||Some doc generation settings|
|t_rules||Related to model validation ⁈|
|t_scenariotypes||List of scenario types defined with
|t_secgrouppermission||Security group permissions|
|t_secusergroup||Security user/group assignments|
|t_secuserpermission||Security user permissions|
|t_statustypes||List of status types defined with
|t_taggedvalue||Smorgasbord for WSDL model elements. These are NOT the tagged values. Instead use t_objectproperties|
|t_tasks||The system tasks|
|t_tcf||List of complexity factors defined with
|t_template||RTF templates ⁈|
|t_testplans||This table is not currently used by EA6|
|t_testtypes||List of test factors defined with
|t_trxtypes||Matrix Profile; Painter Settings; Auto counters defined with
|t_umlpattern||UML patterns imported via
|t_xref||This and that|
|usys_system||Key-value pairs for repository wide settings|
|usystables||A list of all the tables above along with the version where they were introduced. This table is needed (only?) during a project transfer.|
|usysoldtables||I have not the faintest idea ⁈|
|usysqueries||I have not the faintest idea ⁈|
In this chapter we are going into quite some details of the most important tables. Namely these are that for elements, packages, diagrams, diagram objects, connectors and tagged values. Those are the ones you most likely need to retrieve often.
The single columns have a short description of what I think is their meaning. Some are obvious, some are just smoke signals. A reference to the GUI screen shots is placed where this is possible.
To improve readability and reference the properties were either split into several sub-tables or the table has an indicator on top of a logical section. Each sub-table is sorted alphabetically according to the name in
As you already know, this table holds all elements stored in the repository. That is any element you can see in the project browser plus those not shown like notes, boundaries and a couple of other elements. Please note that the Package element is a pendant7 to the Package itself. Part of the information in both is redundant and both link to each other.
The following table lists the most important properties also to be found in the General properties window. The remaining properties are listed subsequently in logical groups.
|Valid values are: 1 for Easy, 2 for Medium, 3 for Difficult|
|GenType||Language property String value. Note that this value does not|
|appear unless it is defined as Product in the Language Datatypes|
|Phase||Phase property String value|
|Scope||Scope property String value|
Status property corresponds to values in
|Diagram_ID||Only for Text elements; reference to primary key of the diagram|
|ea_guid||A global UID shown here|
|Object_ID||Primary, unique key for the element|
|Package_ID||Primary key of the package where the element is located|
|ParentID||Only for nested elements: primary key of the object|
|Classifier_guid||Redundant GUID for the Classifier property|
|Cardinality||Cardinality property String value|
|Concurrency||String equivalent of the Concurrency property for class elements|
|IsActive||Boolean values for the Is* properties|
|Persistence||Persistence property String value|
|GenFile||Filename property String value|
|Multiplicity||Multiplicity property String value|
|Backcolor||Background Color property RGB values in decimal|
|Bordercolor||Border Color property RGB values in decimal|
|BorderStyle||For frame-like elements (boundaries etc.).|
|Corresponds to the style (0..3) where 3 = solid line|
|BoderWidth||Border Width property|
|Fontcolor||Font Color property RGB values in decimal|
The StyleEx property once had been just the individual font settings. Once. But now it’s a Sparxian zoo where all sorts of homeless animals can gather. For example the
Locked=TRUE; appears whenever an element has been marked on a diagram with the
Is Locked context. The element is now protected from manual changes in the GUI (and even the API, though it does not tell the reason for an update failure in
GetLastError) and the element shows a red excalamtion mark in the browser. There are some more options used like
LinkOpen which seem to be too exotic to be explained here (currently).
Another classical database design anti-pattern can be found in the two columns
Object_Type is a string value,
NType adds forgotten salt to any of the expressions depending on some additional context informtion.
The meaning of
Object_Type is hard to structure due to its context sensitivity. So here are a couple of statements:
Object_Typeis one of
UseCase(and probably some more) and
NTypeis 8 and
PDATA1has a number greater then zero then
PDATA1corresponds to t_diagram.Diagram_ID of the composite diagram.
Artifactand 32 means that it renders an image. Unfortunately I have no idea where that image comes from.
NTypehas 0 or 1 for lots of plain elements. It’s not obvious what might be the difference.
MessageEndpointseem to have 2 for
- The following table lists a couple of further combinations:
|Classifier||NULL or 0 where not defined. If > 0 then it is the|
|primary key of the element which classifies the element|
|EventFlags||semi-colon separated list of attributes with links into the Risk/Metrics/etc. tables|
|GenOptions||Some very nasty semi-colon separated list of attributes (used for code generation⁈)|
|GenLinks||String value of the class which this one is specialized from|
|Usually only set where classes are reverse engineered and the general class is missing|
|Header1/2||Used for code generation|
|PDATA1||For Package elements: primary key of the package|
|For Elements: Same as the Status column|
|For Parts/Instances: GUID of the classifier|
|For UseCase: #EXP#=<ep>; semi-colon separated list of Extension Points <ep>|
|For Notes: linked element feature name|
|For Text displaying as hyperlink: t_diagram
|For Requirements: Status property|
|For UMLDiagram: Diagram_ID of the underlying diagram;|
|PDATA2||For Elements: Same as the Priority column|
|For Notes: Object_ID of the linked feature element|
|For Requirements: Priority property|
|PDATA3||For Elements: Same as the Difficulty column|
|For Notes: Reference name into the linked feature element|
|For Ports in classified Parts: the GUID of the corresponding Port in the Classifier.|
|For State: t_diagram
|For Requirements: Difficulty property|
|PDATA4||For Note elements: Yes if the note is linked to an element feature and idref=<val>; list where <val> is the primary key of the connector(s) to which the note is linked|
|For elements: If > 0 this is the primary key of the connector for which this element is defines as association class|
|RunState||For objects a list of run state variables|
|Style||A semi-colon separated list of attribute assignments.|
|Besides others indicates a linked document if = MDoc=1;|
|Tagged||Flag to show that an element it bookmarked (little red triangle in diagram)|
|TPos||Tree order of the element in the project browser|
|Effort||Always 0 ⁈|
|Visibility||Always NULL ⁈|
|StateFlags||Always NULL ⁈|
|PackageFlags||Always NULL ⁈|
|ActionFlags||Always NULL ⁈|
Mainly the element features comprise attributes and methods. Both are stored in the tables detailed below.
Tagged values are not stored in
t_taggedvalue but a couple of different tables.
The following tables are only relevant if user security has been turned on.
In order to check whether security is turned on in the repository the
t_secpolicies table must be queried.
Here you will find some details about tables which are not of major importance. However, from time to time you will also need to deal with them. Note that this section is going to be populated with more information during the next near future.
This chapter might be the start of a new book. Or maybe it will stay thin because the treasures inside the
t_xref table are too secret to be uncovered. We will see.
However, here are the bits from this marvelous table. Currently they are about stereotypes and MDG profiles but there’s a lot more hidden.
This chapter contains a cross reference from table columns to object properties in the API8.
The references are presented for both directions. As you will notice not all columns map to an API property and vice versa. The table also omits EaCollections as those are a result of a query itself and not a simple column.
I have currently only included the two most important table
This section contains a couple of details for selected columns from where they are referenced. A back reference is included at the end of each chapter. Note that most of the following descriptions do not detail the contents but give a sample of the contents only. For those having dug that far it will be obvious how to decode the contents.
A number of columns contain semi-colon separated lists in the format
<key>=<value>; where these pairs can appear more than once thus forming a list of key-value pairs. Usually
<key> is alphanumeric including ‘_’ (underscore). Value itself can contain any chars except ‘=’ and ‘;’.
I found that EA often does not check this constraint and if people enter e.g. a semi-colon in a name it will simply confuse EA in it’s later behavior but will not croak9 that an illegal char is used. Well, it’s EA.
There are a couple of locations where EA stores information locally per user. These can be found in
- %APPDATA%Sparx SystemsEA
- %PROGRAMFILES%Sparx SystemsEA
These locations are mainly used to hold user options (those from
Tools/Options), Layout information, MDG data and more.
In former EA versions the registry keys were just a handfull, but now there are tons. The EA relevant keys are found at
EAAddins keys are used for addins and the usualy suspects will know how to deal with them.
The first key contains all the marvelous settings which EA needs to show up. Quite some of the
Tools/Options are hidden here besides all the layout stuff. So when you need to find certain settings and don’t find any of the files being changed you should export the EA registry part, change the settings and export the registry again. Now you can compare the exports (e.g. with
If you need to change any of
Tools/Options you likely have to go through the registry. And in order to take the changes effect you have to restart EA. I can’t go into details for each single option but instead give you a simple receipt how to find the right one in case you need it.
As an example we want to change the
General/General/Double Click. Per default they are set to Administrator and Shows Properties on my machine. So the first thing to do is to locate them. You can simply open the registry by entering
regedit in the command line of the Windows
Start menu. Next navigate to
HKEY_CURRENT_USER\Software\Sparx Systems\EA400\EA which should look like this:
Now you need to export the current contents of the
In order to spot the difference the options must be changed. Setting the author to Someone and checking Opens Branch will modify the registry. The
EA branch must be saved once more to another file. When comparing the two exports with WinMerge you can easily spot the differences.
Obviously the key
Author holds the
Author option as plain string and
TDCLICK is an enumeration10. Since both values were added as new they will have defaults when not in the registry. So to get the default back you would need to remove the keys. In case of the
TDCLICK you could also set it to 0.
Using the above schema you should be able to figure out any option/key pair.
This section contains snapshots of various property windows. Many properties are highlighted with a red rectangle. For those a reference into the according table if given below the snapshot. In most cases the name of the property and the column name are identical. Some have just an additional blank for user readability. However, some properties differ from the column name. In that case the according user readable label is specified in parentheses right after the column name.
Element related property windows also apply to packages due to the dualism of packages being also elements.
||t_object.Name (Name of parent package)|
||t_object.Name / t_package.Name|
||t_object.Notes / t_package.Notes|
||t_object.Version / t_package.Version|
||t_object.Package_ID -> t_package.Name|
Once you start using direct SQL you need to know:
All SQL are equal
But some SQL are more equal than others
And that’s unfortunately true. A SQL for MS Access (EAP) is different to that of its ‘big brother’ MS SQL Server.
Once you worked out the previous chapters you are ready to provide your EA users with some fancy searches
(available as download11).
You find the search builder when navigating from
Ctrl-F/Builder and pressing the left
New Search icon.
Finally I would like to add a few links where you will get further help.
Any questions you have are important. So you should ask them. Feedback is important for you, me and of course all the other readers. So you are encouraged to send these to one of the below links:
- The page where you bought the book has a small discussion forum enabled. Here you can also post.
- You can mail me directly if you have specific questions. Or maybe you have information regarding the ⁈ markers.
I have not touched scripting much in this book. Basically because it would simply break the scope of this book. However, you might be interested in EA’s automation interface. My book Scripting Enterprise Architect which is available at http://leanpub.com/ScriptingEA will introduce you in that matter. Even if you are already firm with the API its references and a couple of not well known hints will make this a valuable guide for you.
Probably not necessary to name this source, but anyway:
When you post your questions here you should select the right forum and only post once. Getting help here is most likely the fastest lane you can find. I also post regularly.
A not so well known source as Sparx itself does not link this on its forum pages:
Here you will find a variety of articles and resources around EA.
Of course: Google is your friend. But sometimes it’s nice to have a direct link:
This is nice place to get a quick reference to most of SQL. This info is available in different languages if you enter via the main site.
is a bit more responsive and condensed.
I once downloaded a compact HTML page with all information in it, but that site has ceased. So to get something similar see my remark above…
Geert has become a kind of institution at Sparx’ forum. He is a regular poster. But beyond that he also has an excellent blog dealing with UML in general and Enterprise Architect in particular. Check it out:
You’ll also find entries dedicated to SQL usage in EA
with some more advanced SQL stuff.
1The EA version used to create this book was actually 9.3 (build 930). However, most of the references are also valid for earlier versions of EA. ↩
2Not all tables/columns are clear in their meaning (to me). A ⁈ mark is placed where this is the case. Comments about clarification of their meaning are welcome! Just send me a mail to firstname.lastname@example.org.↩
3The latest finding by Colin Wood has been fixed December 2017. Thanks for notifying me.↩
4Cheers to Paolo and all the supporters at Sparx.↩
5I really loathe writing such legal blurb since it should be obvious. By the way: German Law applies! (Does that change anything?)↩
6As of EA version 9.3↩
9Just try this with a stereotype. Enter abc;def as stereotype. Save, close and re-open the element. Now it shows just abc. However, using the ellipsis will show abc;def as possible (but unchecked) stereotype. I already reported that as bug years ago. It’s still not fixed in V12…↩
10You would find that out when comparing after setting
Double Click to Open Branch & Diagram which would result in a value of 2 for