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