1. Preface

Enterprise Architect¹ (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 EA².

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 attention³. Probably I should mention a couple of other guys⁴ 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.

2. 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 information⁵. 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.

3. 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.

3.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 press Ctrl-F to open the search window. Click the Builder button and select the SQL tab. Type the text

1 	SELECT * FROM t_

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.

3.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 (t_object) the best way to retrieve an element is by

1 	elem = Repository.GetElementByID(4711);

which yields the ‘same’ element as

1 	SELECT * FROM t_object WHERE Object_ID = 4711

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:

1 	SELECT * FROM t_object WHERE stereotype = "stereo"

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.

Note:

This window accepts only SELECT statements. Any other SQL (like e.g. UPDATE) is silently ignored!

3.3 A List of All Tables

4. 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 Column.

4.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 pendant⁷ 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.

4.1.1 Key links

4.1.1.1 Classifier_guid

For ActivityParameter elements the Classifier_guid encodes the type of the parameter. This can eventually be one of

And now guess…

Another use came with the introduction of connectors from connectors. These create elements with object type ProxyConnector. Here the Classifier_guid holds the GUID of the corresponding connector.

4.1.2 Details

4.1.3 Dock

4.1.4 Appearance

4.1.5 Object_Type and NType

Another classical database design anti-pattern can be found in the two columns Object_Type and NType. While Object_Type is a string value, NType adds forgotten salt to any of the expressions depending on some additional context informtion.

The meaning of NType per Object_Type is hard to structure due to its context sensitivity. So here are a couple of statements:

• If Object_Type is one of Activity, Artifact, Class, Interaction, Requirement, State, StateMachine, UseCase (and probably some more) and NType is 8 and PDATA1 has a number greater then zero then PDATA1 corresponds to t_diagram.Diagram_ID of the composite diagram.
• Artifact and 32 means that it renders an image. Unfortunately I have no idea where that image comes from.
• NType has 0 or 1 for lots of plain elements. It’s not obvious what might be the difference.
• Constraint and MessageEndpoint seem to have 2 for NType.
• The following table lists a couple of further combinations⁸:

4.1.6 Misc

I have no idea why the column Style is called by that name since only StyleEx really has some character style information. Alas, this is a semi-colon separated list of attribute assignments. 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. Another on indicates a linked document if = MDoc=1; There are some more options used like ShowBeh, EScrpt and LinkOpen which seem to be too exotic to be explained here (currently).

4.1.7 Unknown or heritage

… omitted …

5. Element Feature Tables

Mainly the element features comprise attributes and methods. Both are stored in the tables detailed below.

… omitted …

6. Tagged Value Tables

Tagged values are not stored in t_taggedvalue but a couple of different tables.

… omitted …

7. 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 …

8. 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 …

9. 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 some bits from this marvelous table. Currently they are about stereotypes and MDG profiles but there’s a lot more hidden.

… omitted …

10. API Cross References

This chapter contains a cross reference from table columns to object properties in the API⁹.

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 t_package and t_object.

10.1 t_package — EaPackage

… omitted …

11. 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.

11.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 croak¹⁰ that an illegal char is used. Well, it’s EA.

… omitted …

12. User Settings

There are a couple of locations where EA stores information locally per user. These can be found in

• Registry
• %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.

12.1 Registry

In former EA versions the registry keys were just a handfull, but now there are tons. The EA relevant keys are found at

• HKEY_CURRENT_USER\Software\Sparx Systems\EA400
• HKEY_CURRENT_USER\Software\Sparx Systems\EAAddins
• HKEY_LOCAL_MACHINE\SOFTWARE\Sparx Systems\EAAddins

Obviously the 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 WinMerge).

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/Author and 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 EA branch:

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 enumeration¹¹. 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.

12.2 APPDATA

… omitted …

13. 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.

13.1 Element

Element related property windows also apply to packages due to the dualism of packages being also elements.

13.1.1 Dockable Properties Window

References:

13.1.2 General/Main Properties Window

References:

Note that some properties are ambiguous as they appear in both t_object and t_package for package elements.

… omitted …

14. 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 …

15. SQL Search Builder

Once you worked out the previous chapters you are ready to provide your EA users with some fancy searches (available as download¹²). You find the search builder when navigating from Ctrl-F/Builder and pressing the left New Search icon.

… omitted …

16. Further Reading

Finally I would like to add a few links where you will get further help.

16.1 Feedback

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:

http://leanpub.com/InsideEA

The page where you bought the book has a small discussion forum enabled. Here you can also post.

thomas.kilian@me.com

You can mail me directly if you have specific questions. Or maybe you have information regarding the ⁈ markers.

16.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.

16.3 Sparx Forum

Probably not necessary to name this source, but anyway:

https://www.sparxsystems.com/forums/smf/index.php

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.

16.4 Sparx Community

A not so well known source as Sparx itself does not link this on its forum pages:

http://community.sparxsystems.com

Here you will find a variety of articles and resources around EA.

16.5 SQL in General

Of course: Google is your friend. But sometimes it’s nice to have a direct link:

http://www.1keydata.com/sql/sql-commands.html

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.

http://www.w3schools.com/sql/sql_syntax.asp

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…

16.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:

https://bellekens.com

You’ll also find entries dedicated to SQL usage in EA

https://bellekens.com/#SQL

with some more advanced SQL stuff.

Notes