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. Files and More
- 12. GUI References
- 13. Query Caveats
- 14. SQL Search Builder
- 15. 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.
Copyright and Disclaimer
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.
1. Accessing the Database
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.
1.1 Inspecting EA’s Tables
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.
1.2 Ways to Query Tables
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!
1.3 A List of All Tables
|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_objecttrx||Auto counters defined with
|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; more ⁈|
|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 ⁈|
2. Most Important Tables
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
2.1 More things than you find in the Project Browser: t_object
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
2.1.1 Key links
|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[^color] 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 a some more options used like
LinkOpen which seem to be too exotic to be explained here (currently).
|Classifier||NULL or 0 where not defined. If > 0 then it is the|
|primary key of the element which classifies the element|
|Effort||Always 0 ⁈|
|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|
|NType||8: When the element is composite. The meaning of the other numbers is unclear|
|0,1: Plain elements|
|2: Event, Class, Constraint|
|3: Class, StateNode|
|4: InteractionFragment, StateNode, MessageEndpoint|
|5: InteractionFragment, Object|
|18: Diagram Notes|
|1001: pseudo-port boundary|
|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 UMLDiagrams: Diagram_ID of the underlying diagram;|
|here NType == 0 means Frame and 1 Diagram Reference|
|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|
2.1.6 Unknown or heritage
|Visibility||Always NULL ⁈|
|StateFlags||Always NULL ⁈|
|PackageFlags||Always NULL ⁈|
|ActionFlags||Always NULL ⁈|
… omitted …
3. Element Feature Tables
Mainly the element features comprise attributes and methods. Both are stored in the tables detailed below.
… omitted …
4. Tagged Value Tables
Tagged values are not stored in
t_taggedvalue but a couple of different tables.
… omitted …
5. Security Related 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.
… omitted …
6. Rarely Used Tables
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.
… omitted …
7. Marvelous References
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.
… omitted …
8. API Cross References
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
8.1 t_package — EaPackage
… omitted …
9. Bits and Pieces
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.
9.1 CSV Lists
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.
… omitted …
10. User Settings
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.
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.
… omitted …
11. Files and More
While repositories keep the most important information, namely the models, EA uses further resources which are placed in files and the registry. Both tend to inflate exponentially over time and EA versions and can not be completely covered here. However, a couple of landmarks are worth documentation.
… omitted …
12. GUI References
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.
12.1.1 Dockable Properties Window
||t_object.Name (Name of parent package)|
12.1.2 General/Main Properties Window
||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|
… omitted …
13. Query Caveats
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.
… omitted …
14. SQL Search Builder
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.
… omitted …
15. Further Reading
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:
- This thread on Sparx’ forum is a good place to ask questions of common interest. E.g. if you are missing certain information or you don’t understand something in this book which might be explained in more detail.
- 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.
15.2 Scripting Enterprise Architect
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.
15.3 Sparx Forum
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.
15.4 Sparx Community
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.
15.5 SQL in General
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…
15.6 Geert Bellekens
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